LSE ME204 · Data Engineering Principles for the Social Sciences
🖥️ Week 02 Day 02 Lecture

Code Makeovers with pandas

Jon Cardoso-Silva

Dr Jon Cardoso-Silva

LSE Data Science Institute

🗓️ 21 Jul 2026

0️⃣ Before You Submit Tonight

10:00 – 10:15

Your midterm is due at 8 pm tonight. These slides start with a few things to check before you submit.

Your API key is worth money 🤑💰💰

I know you want to check your API key is not empty, but do not print it out in the notebook.

We use .env precisely to hide the key from others. If you already printed it, clear that cell output before you submit.

Also check pagination URLs. If you followed next or prev links from the API, those URLs contain your key as a query parameter. Clear those outputs too.

Loops and dictionaries can reach Good-Very Good

I am going to show you some code makeovers and some pandas tricks this morning.

If the pandas way of doing things feels like too much right now, do not feel pressured to update your code.

It is totally fine to use for loops, lists, and dictionaries. You can reach Good-Very Good (~70%) with what you already have.

If you use a new tool, show you understand it

If you picked up something that was not in the course, whether from the docs, from an AI, or from a friend:

Write a short tutorial section before the first time you use it. Show your data, explain what the tool does, and show what changed. The same way I introduce a new concept in the lectures before running the code.

That way I know you understood the tool, not just pasted it.

Example: say you found pd.merge() online.

combined = pd.merge(
    hourly, sunrises,
    on=["city", "date"],
)

Before you call it, add a tutorial section.

The next two slides show what that looks like.

A good tutorial cell (part 1)

New tool: pd.merge()

I have two tables. I need the sunrise hour on every hourly row so I can filter to “hours before sunrise.” I tried a for loop that copies the value row by row, but it kept breaking when the indices did not match.

hourly.head(3)
city date hour temp
Leeds 2025-07-01 0 14.2
Leeds 2025-07-01 1 13.8
Leeds 2025-07-01 2 13.1 
sunrises.head(3)
city date sunrise_hour
Leeds 2025-07-01 5
Leeds 2025-07-02 5
Leeds 2025-07-03 5

A good tutorial cell (part 2)

pd.merge() joins two tables on shared columns (pandas docs). Here is what it does to my data:

combined = pd.merge(hourly, sunrises, on=["city", "date"])
combined.head(3)
city date hour temp sunrise_hour
Leeds 2025-07-01 0 14.2 5
Leeds 2025-07-01 1 13.8 5
Leeds 2025-07-01 2 13.1 5

Now every hourly row has the sunrise hour for that city and date. I can filter with combined[combined["hour"] >= combined["sunrise_hour"]].

🔔 A tool that appears with no tutorial tells us you did not understand what it does. Show your working and you get the credit!

Notebook hygiene checklist

Before 8 pm, go through this list.

☐ Three separate notebooks, each with a clear job:

NB01 – Collection Fetch from the API, save raw JSON to data/.

NB02 – Transformation Read JSON, build a tidy table, save CSV to data/.

NB03 – Analysis Read CSV, define your measure, compute, chart.

☐ README filled in (title, methodology, findings, final chart). Remove the [ and ] from the template.

☐ LSE ID filled in. Remove the [ and ] there too.

☐ Cell outputs cleared where you do not need them. Large JSON dumps, printed API keys, pagination URLs.

☐ Dead-end cells removed. Cells with unresolved errors, backup variables, abandoned attempts.

☐ Restart and Run All on each notebook. If it does not run top to bottom, fix it before submitting.

1️⃣ Code Makeover A

10:15 – 10:25

From duplicated blocks to a loop.

From duplicated blocks to a loop

Before (copy-paste, one block per city)

params_london = {
    "lat": 51.51, "lon": -0.13,
    "hourly": "temperature_2m",
}
resp_london = requests.get(url, params=params_london)

params_paris = {
    "lat": 48.85, "lon": 2.35,
    "hourly": "temperature_2m",
}
resp_paris = requests.get(url, params=params_paris)

params_berlin = {
    "lat": 52.52, "lon": 13.41,
    "hourly": "temperature_2m",
}
resp_berlin = requests.get(url, params=params_berlin)
# ... and again for every city

After (one dictionary, one loop)

cities = {
    "london": {"lat": 51.51, "lon": -0.13},
    "paris":  {"lat": 48.85, "lon": 2.35},
    "berlin": {"lat": 52.52, "lon": 13.41},
}

for city, coords in cities.items():
    params = {
        "lat": coords["lat"],
        "lon": coords["lon"],
        "hourly": "temperature_2m",
    }
    response = requests.get(url, params=params)
    with open(f"data/{city}.json", "w") as f:
        json.dump(response.json(), f)

Same result, a fraction of the lines. Add a city by adding one line to the dictionary.

What if you also loop over years?

The same idea works with a second dictionary. Everything that varies goes into the data, not into the code:

cities = {
    "edinburgh": {"lat": 55.95, "lon": -3.19},
    "glasgow":   {"lat": 55.86, "lon": -4.25},
}

years = {
    "2023": {"start": "2023-06-01",
             "end":   "2023-08-31"},
    "2024": {"start": "2024-06-01",
             "end":   "2024-08-31"},
    "2025": {"start": "2025-06-01",
             "end":   "2025-08-31"},
}
for city, coords in cities.items():
    for year, period in years.items():
        params = {
            "latitude":   coords["lat"],
            "longitude":  coords["lon"],
            "start_date": period["start"],
            "end_date":   period["end"],
            "hourly":     "temperature_2m",
        }
        response = requests.get(url, params=params)
        with open(f"data/{city}_{year}.json", "w") as f:
            json.dump(response.json(), f)

Two cities and three years: six API calls from six lines of loop code. Add a city or a year by adding one line to the matching dictionary. No if city == "edinburgh": needed.

2️⃣ A Few New pandas Tricks

10:25 – 10:50

Tools for your NB02 and NB03, if you want them. Everything here is optional for tonight’s submission. for loops, lists, and dictionaries will still be fine.

.assign(): adding a column

You have been adding columns with df["new_col"] = .... Here is another way.

Before

time temperature_2m
2025-07-01T00:00 14.2
2025-07-01T01:00 13.8
2025-07-01T02:00 13.5

After .assign(city="Edinburgh")

time temperature_2m city
2025-07-01T00:00 14.2 Edinburgh
2025-07-01T01:00 13.8 Edinburgh
2025-07-01T02:00 13.5 Edinburgh

A new column appears. The original columns stay the same.

.assign() with a computed value

df = df.assign(
    temp_f=df["temperature_2m"] * 9/5 + 32
)

You will see .assign() used in the next few slides to add datetime columns, label cities, and compute new values.

df.head(3)

time temp_2m city temp_f
2025-07-01T00:00 14.2 Edinburgh 57.6
2025-07-01T01:00 13.8 Edinburgh 56.8
2025-07-01T02:00 13.5 Edinburgh 56.3

pd.to_datetime: from integers to dates

Weather APIs often give you timestamps as large integers (seconds since 1 Jan 1970).

What you have

dt temp
1784512800 14.2
1784516400 13.8
1784520000 13.5

Those integers are not readable. You need dates and hours.

After pd.to_datetime

df = df.assign(
    datetime=pd.to_datetime(df["dt"], unit="s")
)
dt temp datetime
1784512800 14.2 2026-07-19 02:00
1784516400 13.8 2026-07-19 03:00
1784520000 13.5 2026-07-19 04:00

One line, every row at once. The raw dt column stays.

Keep the raw epoch column. Add the datetime as a new column with .assign(). Do not overwrite your original data.

.dt accessors: pull out the piece you need

Once you have a datetime column, .dt gives you the parts:

df = df.assign(
    hour=df["datetime"].dt.hour,
    date=df["datetime"].dt.date,
    month=df["datetime"].dt.month,
)

df.head(3)

dt datetime hour date
1784555232 2026-07-20 13:47:12 13 2026-07-20
1784558832 2026-07-20 14:47:12 14 2026-07-20
1784562432 2026-07-20 15:47:12 15 2026-07-20

No more str(dt)[:10] for the date or dt.hour inside a loop.

Boolean filtering: start from the table

You have this table. You only want the warm afternoon hours.

city hour temp
Edinburgh 5 12.1
Edinburgh 8 14.5
Edinburgh 11 17.3
Edinburgh 12 18.6
Edinburgh 14 19.8
Edinburgh 17 18.2
Edinburgh 18 16.9
Edinburgh 20 15.3
Edinburgh 23 13.7

You want to keep hours 12 through 17 and throw away the rest. How?

Step 1: one condition, True/False

You already saw this on the W01D04 slides with df["rain_sum"] > 0.

df["hour"] >= 12

This produces a column of True and False, one per row:

hour hour >= 12
5 False
8 False
11 False
12 True
14 True
17 True
18 True
20 True
23 True

That gets the afternoon (12, 14, 17) but also keeps the evening (18, 20, 23). You need an upper bound too.

Step 2: filter with square brackets

Put the condition inside df[...] to keep only the True rows:

df[df["hour"] >= 12]
city hour temp
Edinburgh 12 18.6
Edinburgh 14 19.8
Edinburgh 17 18.2
Edinburgh 18 16.9
Edinburgh 20 15.3
Edinburgh 23 13.7

Six rows. But you only wanted 12 through 17, not the whole evening. You need a second condition.

Step 3: two conditions at once

You need: “hour is 12 or above” AND “hour is 17 or below.”

In Python you would write and. In pandas, it is & instead. Here is the reference:

English Python (if) pandas (DataFrames)
and and &
or or |
not not ~ 
df[(df["hour"] >= 12) & (df["hour"] <= 17)]

The parentheses are not optional. Each condition needs its own (...). Without them, Python reads the & before the >= and gives you an error.

Step 4: check the result

city hour temp
Edinburgh 12 18.6
Edinburgh 14 19.8
Edinburgh 17 18.2

Three afternoon rows. The morning, evening, and late hours are gone.

If you find the bracket syntax hard to read, .query() does the same thing:

df.query("hour >= 12 & hour <= 17")

Both give you the same rows.

Without pandas, this is what you would write

With loops and lists

afternoon_rows = []
for i in range(len(hours)):
    if hours[i] >= 12 and hours[i] <= 17:
        afternoon_rows.append({
            "city": cities[i],
            "hour": hours[i],
            "temp": temps[i],
        })

Three parallel lists kept in sync by position. If one is shorter, the loop breaks with no warning.

With pandas

afternoon = df[
    (df["hour"] >= 12) & (df["hour"] <= 17)
]

One line. Same result. No parallel lists to manage.

pd.concat: combining city DataFrames

If you have multiple DataFrames, you can combine them into one with pd.concat:

dfs = []

for city, coords in cities.items():
    with open(f"data/{city}.json") as f:
        data = json.load(f)
    df = pd.DataFrame(data["hourly"])
    df = df.assign(city=city)
    dfs.append(df)

big_df = pd.concat(dfs)

big_df.head(6)

time temperature_2m city
2025-07-01T00:00 14.2 edinburgh
2025-07-01T01:00 13.8 edinburgh
2025-07-01T02:00 13.5 edinburgh
2025-07-01T00:00 15.1 glasgow
2025-07-01T01:00 14.6 glasgow
2025-07-01T02:00 14.0 glasgow

All cities in one table. The city column tells you which rows belong to which city.

3️⃣ Code Makeover B

10:50 – 11:25

A W01 pipeline refactored to pandas, step by step.

The W01 version (before)

A pipeline for one city, built with the tools you had last week. It works.

import json
from datetime import datetime

with open("data/edinburgh.json") as f:
    data = json.load(f)

rows = []
for i in range(len(data["hourly"]["time"])):
    dt = datetime.fromisoformat(data["hourly"]["time"][i])
    rows.append({
        "date": str(dt)[:10],
        "hour": dt.hour,
        "temp": data["hourly"]["temperature_2m"][i],
    })

This builds a list of dictionaries, one per hour. The next slides swap each step for the pandas equivalent.

First: load and label with .assign()

Replace the loop that appends rows with pd.DataFrame plus .assign().

df = (
    pd.DataFrame(data["hourly"])
    .assign(city="Edinburgh")
)

df.head(3)

time temperature_2m city
2025-07-01T00:00 14.2 Edinburgh
2025-07-01T01:00 13.8 Edinburgh
2025-07-01T02:00 13.5 Edinburgh 
print(df.shape)   # check: how many rows and columns?

Next: convert timestamps with pd.to_datetime

The time column is a string like "2025-07-01T00:00". Turn it into a proper datetime, then pull out hour and date.

df["datetime"] = pd.to_datetime(df["time"])
df["hour"] = df["datetime"].dt.hour
df["date"] = df["datetime"].dt.date

df.head(3)

time temperature_2m city datetime hour date
2025-07-01T00:00 14.2 Edinburgh 2025-07-01 00:00:00 0 2025-07-01
2025-07-01T01:00 13.8 Edinburgh 2025-07-01 01:00:00 1 2025-07-01
2025-07-01T02:00 13.5 Edinburgh 2025-07-01 02:00:00 2 2025-07-01

Three new columns, no loop.

Then: filter rows

Say you want only the morning hours, 6:00 to 12:00.

morning = df[(df["hour"] >= 6) & (df["hour"] <= 12)]
print(morning.shape)   # fewer rows than df
print(morning.head())  # check: are the hours right?

The W01 version would be a loop with an if inside. This is one line. The & means AND: both conditions must be true.

Then: groupby and summarise

What is the mean morning temperature per city per day?

summary = (
    morning
    .groupby(["city", "date"], as_index=False)["temperature_2m"]
    .mean()
)

summary.head(3)

city date temperature_2m temp_round
Edinburgh 2025-07-01 15.871 15.9
Edinburgh 2025-07-02 16.543 16.5
Edinburgh 2025-07-03 14.986 15.0 
print(summary.shape)   # one row per city per date?

Finally: check your work

At every step, print .shape, .head(), or .describe() and check the output makes sense.

print(summary.shape)        # expected rows?
print(summary.head())       # columns look right?
print(summary.describe())   # temperature range plausible?

If you used AI to generate a groupby or a chain: break it into steps, print each intermediate table, and check the row count matches the number of groups you expect.

What a good refactoring note looks like

Good ✓

“After Tuesday’s lecture I replaced my loops with pd.to_datetime and groupby. I could check each step with .shape and .head(), which helped me catch a filtering mistake I had in the loop version.”

Weak ✗

“I switched to pandas because it is better.”

If you refactor after this lecture, add a cell like the one on the left. It shows you understood what changed and why.

4️⃣ What Are You Stuck On?

Remaining time

Show me what is not working and we will figure it out together.

Thanks!

Good luck with the midterm! This afternoon is Super Tech Support with your class teacher.

LSE Summer School 2026 | ME204 Week 02 Day 02