Build a Weather Data Collector

Fetch live weather data, store it in SQLite, and analyze it with pandas.

Introduction

30 Second Summary

Every time you check the weather on your phone, an app is pulling live data from a server somewhere in the world. That data travels over the internet, gets transformed, and lands on your screen in milliseconds.

In this project, you will build that same kind of pipeline from scratch. You will fetch real weather data from an API, store it in a database, and analyze it with code.

What You'll Build

A Python weather data collector that fetches current conditions for multiple cities from the OpenWeatherMap API, stores the results in a SQLite database, and uses pandas to analyze and compare the data.

By the end of this project, you'll have:

  • A working script that makes authenticated HTTP requests to a live weather API and parses the JSON responses.
  • A SQLite database with a structured table holding weather records for cities around the world.
  • A pandas-powered analysis script that queries your database and reveals insights about the collected data.
  • Secret Mission: Add an interactive feature that fetches weather for any city and compares it against your database averages.

Are there any prerequisites?

You should be comfortable with basic Python syntax (variables, functions, loops). No experience with APIs, databases, or pandas is needed.

Before We Start

Before We Start...

Set Up Your Environment

Before you can fetch live weather data, you need a few tools in place. Your script will rely on external packages to make web requests, analyze data, and manage secrets.

In this step, you will confirm your Python version, install three packages, and create a free account to access a weather API.

In this step, get ready to:
  • Verify Python is installed at version 3.10 or higher and create your project folder.
  • Install the requests, pandas, and python-dotenv packages.
  • Create a free OpenWeatherMap account and store your API key in a .env file.
Verify your Python version and create the project folder

The packages you will install require Python 3.10 or higher. You need to confirm your version before proceeding.

  • Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
  • Type Visual Studio Code and press Enter to open it.
  • Open the integrated terminal by pressing Ctrl+` (macOS and Windows).
  • Check your Python version by running this command:
python --version

You should see output like Python 3.10.x or higher (e.g. Python 3.12.4).

✔️ I see version 3.10 or higher

Your Python version is ready. Continue to the next instruction below.

ⓧ I see a version lower than 3.10

You need Python 3.10 or higher for the packages in this project. Visit the official Python downloads page to install the latest version:

  • Go to https://www.python.org/downloads/ and download the latest Python installer for your operating system.
  • Run the installer. On Windows, check the box that says Add Python to PATH before clicking Install Now.
  • Close and reopen VS Code, then run python --version again to confirm.

macOS users seeing an older version

macOS may ship with an older Python. After installing a newer version, try running python3 --version instead. If that shows 3.10 or higher, use python3 and pip3 for all commands in this project.

  • Move to your Desktop by running this command:
cd ~/Desktop
  • Create your project folder and move into it by running these commands:
mkdir weather-collector
cd weather-collector
  • In VS Code, click File in the top menu bar.
  • Click Open Folder.
  • Navigate to your Desktop, select the weather-collector folder, and click Open.

Why open the folder in VS Code?

Opening the folder (instead of individual files) tells VS Code to treat weather-collector as your workspace. The integrated terminal will start in this folder automatically, and file creation becomes easier through the Explorer sidebar.

If VS Code asks whether you trust the authors of this folder, click Yes, I trust the authors.

  • Confirm the folder is open by checking the Explorer sidebar on the left. You should see WEATHER-COLLECTOR as the workspace name.
Install the required packages

Your project needs three packages. requests handles HTTP calls to the weather API. pandas provides DataFrames for data analysis. python-dotenv loads environment variables from a file so your API key stays out of your code.

  • Open a new terminal in VS Code by pressing Ctrl+` (macOS and Windows).
  • Install all three packages by running this command:
pip install requests==2.34.2 pandas==2.3.3 python-dotenv==1.2.2

What does this command do?

`pip install` downloads packages from the Python Package Index (PyPI) and installs them on your machine. The == syntax pins each package to an exact version so your project behaves the same way every time.

Seeing 'pip not found'?

On macOS or Linux, try pip3 instead of pip. If that still fails, run python -m pip install followed by the package names.

Help me fix my pip installation

  • Verify all packages installed correctly by running this command:
python -c "import requests; import pandas; from dotenv import load_dotenv; print('All packages installed!')"

You should see All packages installed! printed in your terminal.

Seeing a ModuleNotFoundError?

This usually means pip installed the packages for a different Python version than the one running the command. Try replacing python with python3 in the verification command. Make sure you use the same Python binary for both installing and running.

Help me debug my ModuleNotFoundError

Create your OpenWeatherMap account and .env file

Your script will pull live weather data from the OpenWeatherMap API. To authenticate your requests, you need a free API key.

  • Open your browser and go to https://openweathermap.org/.
  • Click Sign In in the top navigation, then click Create an Account.
  • Fill in your username, email, and password.
  • Check the boxes to confirm you are 16 or older and agree to the terms.
  • Click Create Account.
  • Check your email inbox and click the verification link OpenWeatherMap sends you.
  • Once verified, log in and click your username in the top-right corner.
  • Click My API keys.
  • You will see a default API key already generated. Copy it.

What is an API key?

An API key is a unique string that identifies your account when making requests to a service. OpenWeatherMap uses it to track your usage and enforce rate limits (60 calls per minute on the free plan).

Your API key may take up to 2 hours to activate

OpenWeatherMap activates new API keys automatically, but it can take up to 2 hours after registration. If you get authentication errors in Step 2, come back and try again later.

Now store your key in a .env file so your script can load it without hardcoding secrets.

  • In VS Code's Explorer sidebar, hover over the WEATHER-COLLECTOR heading and click the New File icon.
  • Name the file .env and press Enter.
  • Paste the following content into your .env file, replacing your-api-key-here with the API key you copied from OpenWeatherMap:
WEATHER_API_KEY=your-api-key-here

Why use a .env file?

Storing secrets in a .env file keeps them out of your source code. If you ever share or upload your project, your API key stays private as long as you add .env to your .gitignore.

  • Save the file with Cmd+S (macOS) or Ctrl+S (Windows).
  • Confirm .env appears in the Explorer sidebar under your weather-collector workspace.

✔️ Awesome, I've got everything!

Great. Double check you have saved your .env file.

ⓧ I'd like to double check the full code

Your .env file should contain exactly this (with your own key replacing the placeholder):

WEATHER_API_KEY=your-api-key-here

Your environment is ready. You have Python, three powerful packages, and a secure API key. Next up, you will write your first API call and see real weather data appear in your terminal.

Make Your First API Call

Your environment is set up and your API key is ready. Now it's time to actually talk to the OpenWeatherMap servers and pull back live weather data.

In this step, you'll write a Python script that sends an HTTP request to a weather API, receives a JSON response full of nested data, and extracts the specific fields you care about.

In this step, get ready to:
  • Create a Python script that loads your API key and makes a GET request to the OpenWeatherMap API.
  • Parse the JSON response and extract temperature, humidity, and weather description.
  • Add error handling so your script responds gracefully to invalid requests.
Load your API key and configure the request

Before your script can talk to the API, it needs to load your secret API key from the .env file. This keeps the key out of your source code.

  • Create a new file called weather_collector.py in your weather-collector folder. In VS Code, click File then New File, and save it as weather_collector.py.
  • Add the following code to set up your imports and load the API key:
import requests
import os
from dotenv import load_dotenv

# Load environment variables from .env file
load_dotenv()

API_KEY = os.getenv("WEATHER_API_KEY")
BASE_URL = "https://api.openweathermap.org/data/2.5/weather"

What does this code do?

  • load_dotenv() reads your .env file and loads its contents as environment variables. This means your API key stays in a separate file instead of being hardcoded into your script.
  • os.getenv("WEATHER_API_KEY") retrieves the value you stored in .env and assigns it to API_KEY.
  • BASE_URL stores the OpenWeatherMap endpoint. Every API call you make will go to this address.
  • Save weather_collector.py.
  • You won't see any output yet. The next section sends the actual request and prints the response.

Seeing a ModuleNotFoundError?

If Python says it can't find requests or dotenv, make sure you installed the packages from Step 1. Re-run the install command from your terminal.

Also check that your terminal is in the weather-collector folder, not a different directory.

Help me fix a ModuleNotFoundError in my weather collector script

Send a request and explore the response

An API call works like ordering at a restaurant. You send a request with exactly what you want (city, units, your authentication key), and the server sends back a structured response.

  • Add the following code below the BASE_URL line in weather_collector.py to make the API call and print the results:
params = {
    "q": "London",
    "appid": API_KEY,
    "units": "metric"
}

response = requests.get(BASE_URL, params=params)

if response.status_code == 200:
    data = response.json()
    print(data)
    print(f"City: {data['name']}")
    print(f"Temperature: {data['main']['temp']}C")
    print(f"Humidity: {data['main']['humidity']}%")
    print(f"Description: {data['weather'][0]['description']}")

What does this code do?

  • The params dictionary defines what you're asking the API for: weather in London (q), authenticated with your key (appid), in Celsius (units=metric).
  • requests.get() sends an HTTP GET request to the API endpoint. The response comes back as a JSON object.
  • The JSON response is deeply nested. Temperature lives inside data["main"]["temp"], and the weather description is inside an array at data["weather"][0]["description"].
  • Save weather_collector.py.
  • Run the script in your terminal by running:
python weather_collector.py
  • You should see a large JSON blob printed first, followed by clean lines showing the city name, temperature in Celsius, humidity percentage, and a description like clear sky or overcast clouds.

Seeing a 401 error or no output?

If you see a 401 status code, your API key hasn't activated yet. OpenWeatherMap keys can take up to 2 hours to activate after registration. Wait a bit and try again.

If you see no output at all, check that your .env file is in the same folder as weather_collector.py, and that the variable name is exactly WEATHER_API_KEY.

Help me debug my OpenWeatherMap API call

Why is the JSON response so big?

The API sends back everything it knows about the weather in that city: coordinates, wind, pressure, visibility, sunrise/sunset times, and more. You only extracted four fields, but you can explore the full response to see what else is available.

Add error handling for failed requests

Right now your script only handles the success case. If the API key is wrong or the city doesn't exist, the script silently does nothing. Error handling gives you clear messages about what went wrong.

  • In weather_collector.py, find the closing line of your if response.status_code == 200: block (the last print statement for Description).
  • Add the following error handling code directly below it:
elif response.status_code == 401:
    print("Error: Invalid API key. Check your .env file.")
elif response.status_code == 404:
    print("Error: City not found.")
else:
    print(f"Error: Request failed with status code {response.status_code}")

What does this code do?

  • Status code 401 means "unauthorized." The API rejected your key. This is the most common error when a key hasn't activated yet.
  • Status code 404 means "not found." The city name you passed doesn't match anything in the API's database.
  • The else block catches any other unexpected error code and prints it, so you always know what went wrong.
  • Save weather_collector.py and run the script again:
python weather_collector.py
  • You should see the same London weather output as before. The error handling runs silently in the background and only activates when something goes wrong.

Seeing a SyntaxError?

Make sure the elif lines are at the same indentation level as the original if statement. Python uses indentation to determine which code belongs to which block.

Also check there are no blank lines between the end of the if block and the elif. They must be part of the same if/elif/else chain.

Help me fix a SyntaxError in my if/elif block

✔️ Awesome, I've got everything!

Great. Double check you've saved your file before moving on.

ⓧ I'd like to double check the full code

Here's what your complete weather_collector.py should look like:

import requests
import os
from dotenv import load_dotenv

# Load environment variables from .env file
load_dotenv()

API_KEY = os.getenv("WEATHER_API_KEY")
BASE_URL = "https://api.openweathermap.org/data/2.5/weather"

params = {
    "q": "London",
    "appid": API_KEY,
    "units": "metric"
}

response = requests.get(BASE_URL, params=params)

if response.status_code == 200:
    data = response.json()
    print(data)
    print(f"City: {data['name']}")
    print(f"Temperature: {data['main']['temp']}C")
    print(f"Humidity: {data['main']['humidity']}%")
    print(f"Description: {data['weather'][0]['description']}")
elif response.status_code == 401:
    print("Error: Invalid API key. Check your .env file.")
elif response.status_code == 404:
    print("Error: City not found.")
else:
    print(f"Error: Request failed with status code {response.status_code}")

You've made your first successful API call and parsed real weather data from the response. Next up, you'll store that data in a database so you can collect weather from multiple cities and analyze it later.

Store Data in SQLite

In the previous step, you fetched live weather data from the OpenWeatherMap API and printed it to the terminal. That's useful for a quick peek, but the data disappears the moment your script finishes running.

In this step, you'll store every API response in a SQLite database. SQLite saves data to a single file on your computer, so your weather records persist between runs. You'll also restructure your script into reusable functions and fetch data for five cities at once.

In this step, get ready to:
  • Create a SQLite database and define the weather table schema.
  • Write functions to fetch and save weather data.
  • Collect weather for five cities in a loop and store it all in the database.
Create the database function

Your current script runs all its logic at the top level of the file. To support multiple cities and keep things organized, you'll restructure the entire file into functions.

The sqlite3 module is built into Python, so no installation is needed.

  • In weather_collector.py, select all the existing code and delete it.
  • Replace the file contents with this new foundation that sets up your imports, constants, and a database-creation function:
import requests
import sqlite3
import pandas as pd
import os
from dotenv import load_dotenv

# Load environment variables from .env file
load_dotenv()

API_KEY = os.getenv("WEATHER_API_KEY")
BASE_URL = "https://api.openweathermap.org/data/2.5/weather"


def create_database():
    """Create the SQLite database and weather table."""
    conn = sqlite3.connect("weather_data.db")
    cursor = conn.cursor()
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS weather (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            city TEXT NOT NULL,
            country TEXT NOT NULL,
            temperature REAL NOT NULL,
            feels_like REAL NOT NULL,
            humidity INTEGER NOT NULL,
            description TEXT NOT NULL,
            wind_speed REAL NOT NULL,
            timestamp TEXT DEFAULT CURRENT_TIMESTAMP
        )
    """)
    conn.commit()
    conn.close()

What does this code do?

The imports bring in sqlite3 for database access and pandas for data analysis (which you'll use in the next step). The constants API_KEY and BASE_URL stay the same as before.

The create_database() function connects to a file called weather_data.db (creating it if it doesn't exist), then runs a CREATE TABLE IF NOT EXISTS statement. Each column has a type constraint (TEXT, REAL, INTEGER) that ensures only valid data goes in.

The timestamp column automatically records when each row was inserted using DEFAULT CURRENT_TIMESTAMP.

  • Save weather_collector.py.
  • You won't see a visible change yet. The next chunk adds the functions that fetch and save weather data, then the final chunk runs everything together.
Write the fetch and save functions

Now you need two data-access functions. The first refactors your previous API call logic into a reusable function that accepts any city name. The second inserts the fetched data into your SQLite table.

  • In weather_collector.py, add the following two functions below the create_database() function:
def fetch_weather(city):
    """Fetch current weather data for a city from OpenWeatherMap API."""
    params = {
        "q": city,
        "appid": API_KEY,
        "units": "metric"
    }

    response = requests.get(BASE_URL, params=params)

    if response.status_code == 200:
        data = response.json()
        weather_info = {
            "city": data["name"],
            "country": data["sys"]["country"],
            "temperature": data["main"]["temp"],
            "feels_like": data["main"]["feels_like"],
            "humidity": data["main"]["humidity"],
            "description": data["weather"][0]["description"],
            "wind_speed": data["wind"]["speed"]
        }
        return weather_info
    elif response.status_code == 401:
        print(f"Error: Invalid API key. Check your .env file.")
        return None
    elif response.status_code == 404:
        print(f"Error: City '{city}' not found.")
        return None
    else:
        print(f"Error: Request failed with status code {response.status_code}")
        return None


def save_weather(weather_info):
    """Save weather data to the SQLite database."""
    conn = sqlite3.connect("weather_data.db")
    cursor = conn.cursor()
    cursor.execute("""
        INSERT INTO weather (city, country, temperature, feels_like, humidity, description, wind_speed)
        VALUES (?, ?, ?, ?, ?, ?, ?)
    """, (
        weather_info["city"],
        weather_info["country"],
        weather_info["temperature"],
        weather_info["feels_like"],
        weather_info["humidity"],
        weather_info["description"],
        weather_info["wind_speed"]
    ))
    conn.commit()
    conn.close()

What do these functions do?

fetch_weather(city) takes a city name, sends a GET request to the OpenWeatherMap API, and returns a dictionary with the parsed weather data. If anything goes wrong (invalid key, city not found), it prints an error and returns None.

save_weather(weather_info) opens a connection to weather_data.db, then runs a parameterized INSERT statement. The ? placeholders prevent SQL injection by keeping user data separate from the SQL command itself.

  • Save weather_collector.py.
  • You won't see a change yet. The next chunk wires these functions together and runs the full pipeline.
Collect weather for multiple cities

Now you'll add the orchestration layer. A helper function combines fetching and saving into one call, and a main block drives the whole pipeline for five cities.

  • Add the following code below the save_weather() function in weather_collector.py:
def fetch_and_store(city):
    """Fetch weather for a city and store it in the database."""
    weather_info = fetch_weather(city)
    if weather_info:
        save_weather(weather_info)
        print(f"Saved weather for {weather_info['city']}: {weather_info['temperature']}C, {weather_info['description']}")
    return weather_info


if __name__ == "__main__":
    # Step 1: Create the database
    create_database()

    # Step 2: Fetch and store weather for multiple cities
    cities = ["London", "Tokyo", "New York", "Sydney", "Cairo"]
    print("Fetching weather data...")
    for city in cities:
        fetch_and_store(city)

What does this code do?

fetch_and_store(city) calls fetch_weather() to get the data, then passes it to save_weather() to insert it into the database. It also prints a confirmation so you can see progress in the terminal.

The if __name__ == "__main__": block is Python's way of saying "only run this code when the file is executed directly." It creates the database, then loops through five cities and fetches weather for each one.

  • Save weather_collector.py.
  • Run the script by executing this command in your terminal:
python weather_collector.py

You should see output like this:

Fetching weather data...
Saved weather for London: 14.2C, overcast clouds
Saved weather for Tokyo: 26.8C, clear sky
Saved weather for New York: 22.1C, scattered clouds
Saved weather for Sydney: 12.5C, light rain
Saved weather for Cairo: 35.4C, clear sky

What should I see?

Your exact temperatures and descriptions will differ because you're pulling live data. The key thing is that you see "Saved weather for" followed by each city name, a temperature in Celsius, and a weather description.

Not seeing output for all five cities?

If you see an "Invalid API key" error, your key may not have activated yet. OpenWeatherMap keys can take up to 2 hours after registration. Wait a bit and try again.

If you see "City not found" for a specific city, double check you spelled it exactly as shown in the cities list (e.g. "New York" with a space, not "NewYork").

If you get a ModuleNotFoundError, make sure you installed all packages with pip install requests pandas python-dotenv in Step 1.

help me debug my weather_collector.py script

  • Confirm the database file was created by running:
ls weather_data.db

You should see weather_data.db listed in the output. This file is your SQLite database containing all the weather records you just fetched.

✔️ Awesome, I've got everything!

Great. Double check you've saved weather_collector.py and you're ready for the next step.

ⓧ I'd like to double check the full code

import requests
import sqlite3
import pandas as pd
import os
from dotenv import load_dotenv

# Load environment variables from .env file
load_dotenv()

API_KEY = os.getenv("WEATHER_API_KEY")
BASE_URL = "https://api.openweathermap.org/data/2.5/weather"


def create_database():
    """Create the SQLite database and weather table."""
    conn = sqlite3.connect("weather_data.db")
    cursor = conn.cursor()
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS weather (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            city TEXT NOT NULL,
            country TEXT NOT NULL,
            temperature REAL NOT NULL,
            feels_like REAL NOT NULL,
            humidity INTEGER NOT NULL,
            description TEXT NOT NULL,
            wind_speed REAL NOT NULL,
            timestamp TEXT DEFAULT CURRENT_TIMESTAMP
        )
    """)
    conn.commit()
    conn.close()


def fetch_weather(city):
    """Fetch current weather data for a city from OpenWeatherMap API."""
    params = {
        "q": city,
        "appid": API_KEY,
        "units": "metric"
    }

    response = requests.get(BASE_URL, params=params)

    if response.status_code == 200:
        data = response.json()
        weather_info = {
            "city": data["name"],
            "country": data["sys"]["country"],
            "temperature": data["main"]["temp"],
            "feels_like": data["main"]["feels_like"],
            "humidity": data["main"]["humidity"],
            "description": data["weather"][0]["description"],
            "wind_speed": data["wind"]["speed"]
        }
        return weather_info
    elif response.status_code == 401:
        print(f"Error: Invalid API key. Check your .env file.")
        return None
    elif response.status_code == 404:
        print(f"Error: City '{city}' not found.")
        return None
    else:
        print(f"Error: Request failed with status code {response.status_code}")
        return None


def save_weather(weather_info):
    """Save weather data to the SQLite database."""
    conn = sqlite3.connect("weather_data.db")
    cursor = conn.cursor()
    cursor.execute("""
        INSERT INTO weather (city, country, temperature, feels_like, humidity, description, wind_speed)
        VALUES (?, ?, ?, ?, ?, ?, ?)
    """, (
        weather_info["city"],
        weather_info["country"],
        weather_info["temperature"],
        weather_info["feels_like"],
        weather_info["humidity"],
        weather_info["description"],
        weather_info["wind_speed"]
    ))
    conn.commit()
    conn.close()


def fetch_and_store(city):
    """Fetch weather for a city and store it in the database."""
    weather_info = fetch_weather(city)
    if weather_info:
        save_weather(weather_info)
        print(f"Saved weather for {weather_info['city']}: {weather_info['temperature']}C, {weather_info['description']}")
    return weather_info


if __name__ == "__main__":
    # Step 1: Create the database
    create_database()

    # Step 2: Fetch and store weather for multiple cities
    cities = ["London", "Tokyo", "New York", "Sydney", "Cairo"]
    print("Fetching weather data...")
    for city in cities:
        fetch_and_store(city)

Your weather data is now safely stored in a database file that persists between script runs. Next up, you'll use pandas to load this data back out of SQLite and analyze it to find patterns across all five cities.

Analyze with Pandas

Your database now holds real weather data for five cities around the world. Raw data sitting in a database is useful, but the real value comes from turning it into insights.

In this step, you'll use pandas to load your SQLite data into a DataFrame. From there, you'll calculate which cities are hottest and coldest, rank them, and generate summary statistics.

In this step, get ready to:
  • Load weather data from SQLite into a pandas DataFrame.
  • Calculate key findings like hottest city, coldest city, and averages.
  • Rank cities by temperature and generate full summary statistics.
Load weather data into a DataFrame

A DataFrame is like a spreadsheet in your code. It organizes data into rows and columns, making it easy to filter, sort, and calculate statistics without writing raw SQL for every question.

  • In weather_collector.py, find the fetch_and_store function. Add the following analyze_data function below it:
def analyze_data():
    """Load data from SQLite and analyze with pandas."""
    conn = sqlite3.connect("weather_data.db")
    df = pd.read_sql_query("SELECT * FROM weather", conn)
    conn.close()

    if df.empty:
        print("No data in database yet. Run the collector first!")
        return

    print("\n" + "=" * 50)
    print("WEATHER DATA ANALYSIS")
    print("=" * 50)

    print("\n--- All Collected Data ---")
    print(df[["city", "country", "temperature", "humidity", "description"]].to_string(index=False))

What does this code do?

  • pd.read_sql_query("SELECT * FROM weather", conn) runs a SQL query and loads the results directly into a pandas DataFrame. No manual row-by-row parsing needed.
  • df.empty is a safety check that prevents errors if the database has no data yet.
  • .to_string(index=False) prints the DataFrame as a clean table without the row index numbers cluttering the output.
  • Save weather_collector.py.

You won't see output yet. The next chunk adds the calculations that make this function useful.

Find the hottest and coldest cities

pandas makes it simple to answer questions about your data. Instead of writing complex SQL queries, you can use built-in methods like .idxmax() and .mean() to get answers in a single line.

  • In weather_collector.py, find the line that says print(df[["city", "country", "temperature", "humidity", "description"]].to_string(index=False)) inside your analyze_data function. Add the following code below it:
    hottest = df.loc[df["temperature"].idxmax()]
    coldest = df.loc[df["temperature"].idxmin()]
    avg_temp = df["temperature"].mean()
    avg_humidity = df["humidity"].mean()

    print(f"\n--- Key Findings ---")
    print(f"Hottest city: {hottest['city']} at {hottest['temperature']}C")
    print(f"Coldest city: {coldest['city']} at {coldest['temperature']}C")
    print(f"Average temperature: {avg_temp:.1f}C")
    print(f"Average humidity: {avg_humidity:.1f}%")

What does this code do?

  • df["temperature"].idxmax() finds the row index of the highest temperature. df.loc[...] then grabs the full row, so you can access all columns for that city.
  • .mean() calculates the average across all values in a column. One method call replaces what would be a manual loop summing values and dividing by count.
  • The :.1f format specifier rounds the output to one decimal place for cleaner display.
  • Save weather_collector.py.

You won't see output yet. The next chunk completes the function and wires it into your main script.

Rank cities and generate summary statistics

Sorting a DataFrame reorders the rows by any column you choose. The .describe() method gives you count, mean, standard deviation, min, max, and percentiles for every numeric column in one call.

  • Still inside the analyze_data function, add the following code below the print(f"Average humidity: {avg_humidity:.1f}%") line:
    print("\n--- Cities Ranked by Temperature ---")
    ranked = df.sort_values("temperature", ascending=False)
    print(ranked[["city", "temperature", "humidity"]].to_string(index=False))

    print("\n--- Summary Statistics ---")
    print(df[["temperature", "feels_like", "humidity", "wind_speed"]].describe().to_string())

What does this code do?

  • df.sort_values("temperature", ascending=False) returns a new DataFrame with cities ordered from hottest to coldest.
  • .describe() produces a statistical summary for each numeric column: count, mean, std, min, 25th percentile, median, 75th percentile, and max.

Now you need to call this function from your main block so it runs after collecting data.

  • Scroll down to the if __name__ == "__main__": block at the bottom of your file. Find the for city in cities: loop and add the following code below it (outside the loop, at the same indentation level as the for statement):
    # Step 3: Analyze the collected data
    analyze_data()
  • Save weather_collector.py.
  • Run the script by entering this command in your terminal:
python weather_collector.py

You should see the weather fetching messages followed by a formatted analysis report. The report shows a table of all collected data, key findings (hottest city, coldest city, averages), a temperature ranking, and full summary statistics.

Not seeing the analysis report?

  • Make sure analyze_data() is inside the if __name__ == "__main__": block but OUTSIDE the for loop. It should be at the same indentation as the for city in cities: line.
  • Check for an indentation error. The lines inside analyze_data() must use exactly 4 spaces of indentation relative to the def line.
  • If you see "No data in database yet", your API key may not be active. Delete weather_data.db and re-run the script after confirming your API key works.

Why isn't my pandas analysis printing any output?

✔️ Awesome, I've got everything!

Great. Double check you've saved your file before moving on.

ⓧ I'd like to double check the full code

Here is the complete weather_collector.py file at this point:

import requests
import sqlite3
import pandas as pd
import os
from dotenv import load_dotenv

# Load environment variables from .env file
load_dotenv()

API_KEY = os.getenv("WEATHER_API_KEY")
BASE_URL = "https://api.openweathermap.org/data/2.5/weather"


def create_database():
    """Create the SQLite database and weather table."""
    conn = sqlite3.connect("weather_data.db")
    cursor = conn.cursor()
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS weather (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            city TEXT NOT NULL,
            country TEXT NOT NULL,
            temperature REAL NOT NULL,
            feels_like REAL NOT NULL,
            humidity INTEGER NOT NULL,
            description TEXT NOT NULL,
            wind_speed REAL NOT NULL,
            timestamp TEXT DEFAULT CURRENT_TIMESTAMP
        )
    """)
    conn.commit()
    conn.close()


def fetch_weather(city):
    """Fetch current weather data for a city from OpenWeatherMap API."""
    params = {
        "q": city,
        "appid": API_KEY,
        "units": "metric"
    }

    response = requests.get(BASE_URL, params=params)

    if response.status_code == 200:
        data = response.json()
        weather_info = {
            "city": data["name"],
            "country": data["sys"]["country"],
            "temperature": data["main"]["temp"],
            "feels_like": data["main"]["feels_like"],
            "humidity": data["main"]["humidity"],
            "description": data["weather"][0]["description"],
            "wind_speed": data["wind"]["speed"]
        }
        return weather_info
    elif response.status_code == 401:
        print(f"Error: Invalid API key. Check your .env file.")
        return None
    elif response.status_code == 404:
        print(f"Error: City '{city}' not found.")
        return None
    else:
        print(f"Error: Request failed with status code {response.status_code}")
        return None


def save_weather(weather_info):
    """Save weather data to the SQLite database."""
    conn = sqlite3.connect("weather_data.db")
    cursor = conn.cursor()
    cursor.execute("""
        INSERT INTO weather (city, country, temperature, feels_like, humidity, description, wind_speed)
        VALUES (?, ?, ?, ?, ?, ?, ?)
    """, (
        weather_info["city"],
        weather_info["country"],
        weather_info["temperature"],
        weather_info["feels_like"],
        weather_info["humidity"],
        weather_info["description"],
        weather_info["wind_speed"]
    ))
    conn.commit()
    conn.close()


def fetch_and_store(city):
    """Fetch weather for a city and store it in the database."""
    weather_info = fetch_weather(city)
    if weather_info:
        save_weather(weather_info)
        print(f"Saved weather for {weather_info['city']}: {weather_info['temperature']}C, {weather_info['description']}")
    return weather_info


def analyze_data():
    """Load data from SQLite and analyze with pandas."""
    conn = sqlite3.connect("weather_data.db")
    df = pd.read_sql_query("SELECT * FROM weather", conn)
    conn.close()

    if df.empty:
        print("No data in database yet. Run the collector first!")
        return

    print("\n" + "=" * 50)
    print("WEATHER DATA ANALYSIS")
    print("=" * 50)

    print("\n--- All Collected Data ---")
    print(df[["city", "country", "temperature", "humidity", "description"]].to_string(index=False))

    hottest = df.loc[df["temperature"].idxmax()]
    coldest = df.loc[df["temperature"].idxmin()]
    avg_temp = df["temperature"].mean()
    avg_humidity = df["humidity"].mean()

    print(f"\n--- Key Findings ---")
    print(f"Hottest city: {hottest['city']} at {hottest['temperature']}C")
    print(f"Coldest city: {coldest['city']} at {coldest['temperature']}C")
    print(f"Average temperature: {avg_temp:.1f}C")
    print(f"Average humidity: {avg_humidity:.1f}%")

    print("\n--- Cities Ranked by Temperature ---")
    ranked = df.sort_values("temperature", ascending=False)
    print(ranked[["city", "temperature", "humidity"]].to_string(index=False))

    print("\n--- Summary Statistics ---")
    print(df[["temperature", "feels_like", "humidity", "wind_speed"]].describe().to_string())


if __name__ == "__main__":
    # Step 1: Create the database
    create_database()

    # Step 2: Fetch and store weather for multiple cities
    cities = ["London", "Tokyo", "New York", "Sydney", "Cairo"]
    print("Fetching weather data...")
    for city in cities:
        fetch_and_store(city)

    # Step 3: Analyze the collected data
    analyze_data()

Your data pipeline is now complete. From a single command, your script fetches live weather data over HTTP, stores it in SQL, and analyzes it with pandas. Next up, you'll take this further with an interactive city comparison in the Secret Mission.

Secret mission

Add an Interactive City Comparison

What if you could compare any city in the world against the data you've already collected? In this challenge, you'll add an interactive feature that lets a user enter any city name, fetches live weather data for it, and compares it against your database averages.

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely locally with a free API tier. There are no ongoing costs or running processes to worry about.

Resources you used:

  • weather-collector folder (contains weather_collector.py, .env, and weather_data.db).
  • Python packages: requests, pandas, python-dotenv.
  • OpenWeatherMap free account (no ongoing cost, 60 API calls/minute, 1,000,000 calls/month).

Keep everything running

No action needed. Choose this if you plan to keep collecting weather data or want to experiment with your script further.

  • Your weather_data.db file persists locally. You can re-run the script anytime to add more data.
  • Your OpenWeatherMap account is completely free with no charges.
  • The installed Python packages remain available for other projects.

Pause - I'll come back to this later

This project has no running processes or active cloud resources. Everything is stored locally on your machine.

  • Your files and database stay on your machine until you choose to delete them.
  • Your OpenWeatherMap API key remains active. You can return and run the script whenever you like.

Delete - I don't want to use this again

Remove all project files and packages from your machine.

  • Delete the weather-collector folder by running this command in your terminal.

On macOS/Linux:

cd ~/Desktop
rm -rf weather-collector

On Windows:

cd ~\Desktop
rmdir /s /q weather-collector
  • Uninstall the Python packages by running:
pip uninstall requests pandas python-dotenv -y
  • Your OpenWeatherMap account is free and incurs no charges. You can keep it for future projects at no cost.

Nice Work!

Nice Work!

You just built a complete data pipeline in Python. From a single command, your script fetches live weather data from an API, stores it in a database, and analyzes it with pandas.

You've learned how to:

  • Make authenticated HTTP GET requests to a live REST API and parse nested JSON responses into structured Python data.
  • Design a SQLite database schema, write parameterized SQL queries, and store structured weather records safely.
  • Load data from a database into pandas DataFrames and perform data analysis to reveal insights across cities.
  • Secret Mission: Added an interactive comparison feature that fetches weather for a user-entered city and compares it against database averages.

Ready to quiz yourself?