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

Untangling Nested JSON: the pandas way

Dr Jon Cardoso-Silva

LSE Data Science Institute

🗓️ 23 Jul 2026

1️⃣ Python Functions

10:00 – 10:45

How to give a name to code you want to run more than once

The data for this section

We will use this data for the first part of the lecture:

hero = {"name": "Aria", "role": "Warrior", "strength": 10, "magic": 3}

monsters = [
    {"name": "Giant Slug", "power": 4},
    {"name": "Fire Drake", "power": 9},
    {"name": "Shadow Wraith", "power": 14},
]

What is a function?

A reusable block of code that takes inputs and produces an output.

Key components:

def starts the definition
function name: you choose it
parameters: the inputs
docstring: explains what it does
return sends the result back

def add(a, b):
    """Return the sum of a and b."""
    result = a + b
    return result

print(add(3, 5))

`describe(hero)`

A function that formats a hero dictionary into a readable string.

def describe(hero):
    """hero must have 'name' and 'role' keys."""
    return f"{hero['name']} the {hero['role']}"

print(describe(hero))

Aria the Warrior

`combat_power(hero)`

A function whose output feeds the next one.

def combat_power(hero):
    """hero must have 'strength' and 'magic' keys."""
    return hero["strength"] + hero["magic"]

print(combat_power(hero))

`fight(hero, monster)`

A function that calls the other two inside its body.

def fight(hero, monster):
    """hero needs name, role, strength, magic.
    monster needs name, power."""
    power = combat_power(hero)
    if power > monster["power"]:
        return f"{describe(hero)} defeated the {monster['name']}!"
    elif power == monster["power"]:
        return "A draw. Both withdraw."
    else:
        return f"The {monster['name']} was too strong!"

Three monsters, three outcomes

The same three functions, different input each time.

for monster in monsters:
    print(fight(hero, monster))

Aria the Warrior defeated the Giant Slug!
Aria the Warrior defeated the Fire Drake!
The Shadow Wraith was too strong!

The three functions produced different outcomes because the monster changed, not the code.

From a for loop to a list comprehension

The same three parts, rearranged into one line.

Before (for loop):

results = []
for monster in monsters:
    hero = fight(hero, monster)
    results.append(hero)

After (list comprehension):

results = [
    fight(hero, monster)
    for monster
    in monsters
]

The expression moves to the front, the variable and iterable stay in the same order, and the result list is built for you.

The same pieces in one line:

results = [fight(hero, monster) for monster in monsters]

`lambda`: a function without a name

A one-expression function you can write inline.

When a function is a single expression, you can skip the def and write it as a lambda.

When the logic grows beyond one line, use a named def instead.

Named version:

def combat_power(hero):
    return hero["strength"] + hero["magic"]

Lambda equivalent:

combat_power = lambda hero: hero["strength"] + hero["magic"]

These lambda functions are very useful inside pandas methods like .agg() and .apply().

🎲 Aside: what if the hero were random?

The random module can generate data. The functions that process it do not need to change.

import random

def create_random_hero():
    name = random.choice(["Aria", "Borin", "Cleo", "Dax", "Elara"])
    role = random.choice(["Warrior", "Mage", "Rogue"])
    strength = random.randint(1, 12)
    magic = random.randint(1, 12)
    return {"name": name, "role": role, "strength": strength, "magic": magic}

fight, describe, and combat_power take a dict with the right keys regardless of where it came from.

2️⃣ Reshaping JSON with Functions

10:45 – 11:30

How to turn the JSON shapes you have been collecting into tables

pd.DataFrame handles both shapes

I don’t know if you noticed when you were working on your midterm, but pd.DataFrame() accepts either shape.

Dictionary of lists (Open-Meteo):

data = {
    "dt":   ["2025-07-01", "2025-07-02",
            "2025-07-03"],
    "temp": [22.1, 25.8, 19.4],
}
df = pd.DataFrame(data)

List of dictionaries (OpenWeather):

data = [
    {"dt": "2025-07-01", "temp": 22.1},
    {"dt": "2025-07-02", "temp": 25.8},
    {"dt": "2025-07-03", "temp": 19.4},
]
df = pd.DataFrame(data)

Both calls produce the same table:

dt	temp
2025-07-01	22.1
2025-07-02	25.8
2025-07-03	19.4

Converting between the two shapes

Even though pandas handles both, let’s practise writing the conversion ourselves.

Input (list of dicts):

records = [
  {"dt": "2025-07-01",
   "temp": 22.1,
   "wind_speed": 3.2,
   "condition": "Clear"},
  {"dt": "2025-07-02",
   "temp": 25.8,
   "wind_speed": 5.1,
   "condition": "Clouds"},
  {"dt": "2025-07-03",
   "temp": 19.4,
   "wind_speed": 7.8,
   "condition": "Rain"},
]

→

Target (dict of lists):

{
  "dt":        ["2025-07-01",
                "2025-07-02",
                "2025-07-03"],
  "temp":      [22.1, 25.8, 19.4],
  "wind_speed": [3.2, 5.1, 7.8],
  "condition":  ["Clear",
                 "Clouds",
                 "Rain"],
}

Open ME204_W02D04_Lecture.ipynb run the code up until the section titled “🎯 Challenge: Convert between the two shapes” and write a for loop that produces the target. Work in 👥 Pairs.

Appending key by key

Solution shown live in the notebook.

Make it a function

Take your working loop and place it inside this skeleton.

def records_to_columns(records):
    """Convert a list of flat dicts to a dict of lists.

    records: a list like [{"dt": ..., "temp": ...}, ...]

    Returns: a dict like {"dt": [...], "temp": [...]}
    """

    # your code here

    return result

Then test it:

records_to_columns(records)

Solution: `records_to_columns`

Solution shown live in the notebook.

Can the function handle different keys?

A list of dictionaries with "name" and "grade" instead of weather fields.

students = [
    {"name": "Alice", "grade": 72},
    {"name": "Bob",   "grade": 85},
    {"name": "Charlie", "grade": 61},
]

records_to_columns(students)

This breaks with the current version of records_to_columns because the keys "dt", "temp", "wind_speed", and "condition" are written into the function body.

Try to update that same function so it would work for both inputs. Here is how to test it:

records_to_columns(records)   # should still work
records_to_columns(students)  # should now work too

The question is whether one function can reshape any list of flat dictionaries, regardless of what the keys are called.

Reading the keys from the data

Solution shown live in the notebook.

Can the function handle nested JSON?

OpenWeather One Call 4.0 wraps the records inside a "data" key and adds lat, lon, and timezone at the top level.

response = {
    "lat": 51.5,
    "lon": -0.1,
    "timezone": "Europe/London",
    "data": [
        {"dt": 1777452300, "temp": 287.95, "humidity": 48},
        {"dt": 1777455900, "temp": 288.10, "humidity": 46},
    ]
}

Try to update records_to_columns so it handles this too. Here is how to test it:

records_to_columns(records)    # should still work
records_to_columns(students)   # should still work
records_to_columns(response)   # should now work too

Finding the list inside the response

Solution shown live in the notebook.

pandas has a built-in for this

Unnesting JSON is common enough that pandas has dedicated functions for it.

Flat list of dicts:

df = pd.DataFrame(records)

Nested response with metadata:

df = pd.json_normalize(
    response,
    record_path="data",
    meta=["lat", "lon", "timezone"]
)

The exercises you worked through this morning (finding the list, attaching metadata, looping over keys) are the same steps json_normalize takes internally. That practice will help you debug when something goes wrong with the real function.

☕ Coffee Break

11:30 – 11:45

3️⃣ pd.json_normalize

11:45 – 12:35

What each parameter does and when to use a different tool instead

The simplest call

When every record is a flat dictionary, pd.json_normalize and pd.DataFrame produce the same table.

pd.json_normalize(records)

pd.DataFrame(records)

Both produce:

dt	temp	wind_speed	condition
2025-07-01	22.1	3.2	Clear
2025-07-02	25.8	5.1	Clouds
2025-07-03	19.4	7.8	Rain

json_normalize becomes useful when the JSON is not flat.

`record_path`: where is the list of rows?

record_path tells pandas which key holds the list. Each item in that list becomes one row.

response = {
    "lat": 51.5,
    "lon": -0.1,
    "timezone": "Europe/London",
    "data": [
        {"dt": 1777452300,
         "temp": 287.95,
         "humidity": 48},
        {"dt": 1777455900,
         "temp": 288.10,
         "humidity": 46},
    ]
}

pd.json_normalize(
    response,
    record_path="data"
)

dt	temp	humidity
1777452300	287.95	48
1777455900	288.10	46

record_path="data" points to the “data” array. Each key inside those dicts becomes a column with the same colour. Notice how the other keys in the outer dictionary (lat, lon, timezone) are not in the output. We’ll see how to include them in the next section.

`meta`: what to stamp on every row?

meta tells pandas which keys from outside the list to copy onto every row.

response = {
    "lat": 51.5,
    "lon": -0.1,
    "timezone": "Europe/London",
    "data": [
        {"dt": 1777452300,
         "temp": 287.95,
         "humidity": 48},
        {"dt": 1777455900,
         "temp": 288.10,
         "humidity": 46},
    ]
}

df = pd.json_normalize(
    response, record_path="data",
    meta=["lat", "lon", "timezone"]
)

dt	temp	humidity	lat	lon	timezone
1777452300	287.95	48	51.5	-0.1	Europe/London
1777455900	288.10	46	51.5	-0.1	Europe/London

Each green key must hold a single value, not another list. That value gets pasted onto every row.

What if my data is REALLY nested?

Sometimes APIs have even deeper levels of nesting inside each record. json_normalize handles some of these automatically, but not all.

Dicts inside each record: automatic flattening

OpenWeather’s daily endpoint nests temp and feels_like as dicts inside each record.

If you had data like this:

response = {
    "lat": 51.5,
    "lon": -0.1,
    "timezone": "Europe/London",
    "data": [
        {"dt": 1777452300,
         "temp": {"day": 22.1,
                  "min": 15.3,
                  "max": 24.8},
         "feels_like": {"day": 21.5,
                        "night": 14.8}},
        ...
    ]
}

The same kind of code would work:

pd.json_normalize(
    response,
    record_path="data",
    meta=["lat", "lon", "timezone"]
)

json_normalize flattens nested dicts automatically!

The yellow sub-keys become temp.day, temp.min, temp.max.*
The purple sub-keys become feels_like.day, feels_like.night.

And the table would look like this:

dt	temp.day	temp.min	temp.max	feels_like.day	feels_like.night	lat	lon
1777452300	22.1	15.3	24.8	21.5	14.8	51.5	-0.1

Lists inside each record: not flattened

Some fields inside each record are lists, not single values or dicts.

If you had data like this:

response = {
    "lat": 51.5,
    "lon": -0.1,
    "data": [
        {"dt": 1777452300,
         "temp": 287.95,
         "weather": [
             {"id": 800, "main": "Clear"}]},
        {"dt": 1777455900,
         "temp": 288.10,
         "weather": [
             {"id": 802, "main": "Clouds"}]},
    ]
}

The same kind of code would work:

pd.json_normalize(
    response,
    record_path="data",
    meta=["lat", "lon"]
)

But json_normalize does not unpack lists inside records.

The red column contains the raw list stuffed into a single cell. You still need a loop or a comprehension to extract those values.

And the table would look like this:

dt	temp	weather	lat	lon
1777452300	287.95	[{‘id’: 800, “main”: “Clear”}]	51.5	-0.1
1777455900	288.10	[{‘id’: 802, “main”: “Clouds”}]	51.5	-0.1

The solution: break it into smaller pieces

The trick is to normalize each nested part separately, then join them later.

The records (without weather):

response["data"]

[{"dt": 1777452300,
  "temp": 287.95,
  "weather": [...]},
 {"dt": 1777455900,
  "temp": 288.10,
  "weather": [...]}]

The nested list inside each record:

response["data"] with record_path="weather"

[{"id": 800,
  "main": "Clear"},
 {"id": 802,
  "main": "Clouds"}]

Normalize each piece with json_normalize, then stitch the columns together with pd.concat.

Normalize each piece

Two calls to json_normalize, one per piece.

The main records:

df = pd.json_normalize(
    response,
    record_path="data",
    meta=["lat", "lon"]
).drop(columns=["weather"])

dt	temp	lat	lon
1777452300	287.95	51.5	-0.1
1777455900	288.10	51.5	-0.1

The weather list:

weather_df = pd.json_normalize(
    response["data"],
    record_path="weather",
    record_prefix="weather_",
)

weather_id	weather_main
800	Clear
802	Clouds

The second call uses response["data"] as input (the list of records) and record_path="weather" to point at the nested list inside each one.

Stitch them together with `pd.concat`

Both tables have the same number of rows in the same order. pd.concat with axis=1 puts the columns side by side.

result = pd.concat([df, weather_df],axis=1)

yielding:

dt	temp	lat	lon	weather_id	weather_main
1777452300	287.95	51.5	-0.1	800	Clear
1777455900	288.10	51.5	-0.1	802	Clouds

Here, axis=1 means we want to concatenate columns, not rows.

This only works when each row in weather_df matches exactly one row in df in the same position. If the nested lists have different lengths per record, you need a more robust technique like pd.merge() (next week).

Picking the right tool 🧰

Three shapes, three entry points. Look at the JSON first.

Dict of flat lists The Open-Meteo shape. Keys are column names, values are lists of equal length.

pd.DataFrame(data)

List of flat dicts Each dict is one row. All keys are simple values.

pd.DataFrame(records)

pd.json_normalize(records)

Metadata wrapping a list Top-level keys hold single values. One key holds the list of records.

pd.json_normalize(
    response,
    record_path="data",
    meta=["lat", "lon",
          "timezone"]
)

Look at the JSON first. The shape tells you which function to call.

4️⃣ Loops, Comprehensions, and pandas

12:35 – 12:55

How we write code in ME204 from now on

Code conventions going forward

You proved in the midterm that you can collect, reshape, and analyse data with loops and dictionaries. From now on, we write code that builds on that.

Collection (calling APIs, saving files):

Wrap the work in a named function, then loop over your inputs calling that function.

def collect_weather(city, coords, year, data_folder="./data"):
    """Fetch data per city and year."""
    params = {
        "lat": coords["lat"],
        "lon": coords["lon"],
        "start_date": f"{year}-01-01",
        "end_date":   f"{year}-12-31",
        "hourly": "temperature_2m",
    }
    resp = requests.get(url, params=params)

    if resp.status_code != 200:
        return f"Error: {resp.status_code} for {city} {year}"

    
    path = f"{data_folder}/{city}_{year}.json"
    with open(path, "w") as f:
        json.dump(resp.json(), f)

    return "success"

Transformation and analysis

For everything after the JSON is on disk, use pandas functions and methods.

# Reading JSON into a table
df = pd.json_normalize(
    response, record_path="data",
    meta=["lat", "lon"]
)

# New columns
df = df.assign(temp_c=df["temp"] - 273.15)


# Filtering
warm = df[df["temp_c"] > 20].copy()

# Summarising
warm.groupby("city")["temp_c"].agg("mean")

No more raw for loops over rows any more!

When you are about to write a loop, ask yourself

Am I calling an API or writing files? A for loop with a named function is fine.

for year in years:
    for city in cities:
        collect_weather(city, year)

Am I building a new derived column? .assign(), a list comprehension, or lambda inside .apply().

df = df.assign(
    temp_c=df["temp"] - 273.15
)

Am I filtering, grouping, or summarising? pandas methods.

warm = df[df["temp_c"] > 20]
warm.groupby("city").agg("mean")

This afternoon

The lab notebook picks up where these slides leave off.

You will practise:

pd.read_json(), pd.json_normalize(), and pd.DataFrame() on JSON files
pd.concat() for combining DataFrames from different cities or years into one table
The final project brief will be announced

LSE Summer School 2026 | ME204 Week 02 Day 04

Untangling Nested JSON: the pandas way

1️⃣ Python Functions

The data for this section

What is a function?

describe(hero)

combat_power(hero)

fight(hero, monster)

Three monsters, three outcomes

From a for loop to a list comprehension

lambda: a function without a name

🎲 Aside: what if the hero were random?

2️⃣ Reshaping JSON with Functions

pd.DataFrame handles both shapes

Converting between the two shapes

Appending key by key

Make it a function

Solution: records_to_columns

Can the function handle different keys?

Reading the keys from the data

Can the function handle nested JSON?

Finding the list inside the response

pandas has a built-in for this

☕ Coffee Break

3️⃣ pd.json_normalize

The simplest call

record_path: where is the list of rows?

meta: what to stamp on every row?

What if my data is REALLY nested?

Dicts inside each record: automatic flattening

Lists inside each record: not flattened

The solution: break it into smaller pieces

Normalize each piece

Stitch them together with pd.concat

Picking the right tool 🧰

4️⃣ Loops, Comprehensions, and pandas

Code conventions going forward

When you are about to write a loop, ask yourself

This afternoon

`describe(hero)`

`combat_power(hero)`

`fight(hero, monster)`

`lambda`: a function without a name

Solution: `records_to_columns`

`record_path`: where is the list of rows?

`meta`: what to stamp on every row?

Stitch them together with `pd.concat`