How do I turn nested JSON into a flat DataFrame in pandas?

The short version

Use pd.json_normalize to flatten nested JSON into a table. Nested dictionaries become dotted column names like loc.city. When a record contains a nested list, use record_path to expand it into rows and meta to carry the parent fields down onto each row.

Flattening nested dictionaries

import pandas as pd

data = [
    {"id": 1, "loc": {"city": "Leeds", "zone": "N"}},
    {"id": 2, "loc": {"city": "Bath", "zone": "S"}},
]

df = pd.json_normalize(data)
print(df.columns.tolist())   # ['id', 'loc.city', 'loc.zone']

Each level of nesting is joined with a dot, so the structure is visible in the column name. Change the joiner with sep="_" if dots are awkward to reference.

Why not just DataFrame(data)

pd.DataFrame(data) would give you a loc column containing raw dictionaries, which you cannot group, filter or aggregate on. json_normalize unpacks them into real columns you can work with, which is the whole point.

Expanding a nested list into rows

When a record contains a list of sub-records, you usually want one row per sub-record.

data = [
    {"order": 1, "city": "Leeds", "items": [{"sku": "pen"}, {"sku": "mug"}]},
]

pd.json_normalize(
    data,
    record_path="items",     # expand this list into rows
    meta=["order", "city"],  # carry these parent fields onto each row
)

This gives two rows, one per item, each carrying the order and city. Without meta, you would get the items but lose the parent context, which is a common frustration.

Controlling depth

max_level limits how far down it flattens, which is useful when deep levels are irrelevant and would create a forest of columns.

pd.json_normalize(data, max_level=1)

Reading from a file

Load the JSON first, then normalise, so you control the parsing.

import json
with open("data.json") as f:
    raw = json.load(f)
df = pd.json_normalize(raw)

pd.read_json also exists and is fine for already-flat records, but json_normalize gives you the control you need for nested structures.

The ragged-data reality

Real JSON is often inconsistent: some records have a field, others do not. json_normalize handles this by filling the missing ones with NaN, which is exactly right. Check the missing proportions afterwards, because a column that is 90 per cent NaN usually means the field only appears on a certain kind of record, which is worth understanding before you use it.

Keep the raw copy

Flattening is lossy in the sense that it commits to one interpretation of the structure. Keep the original JSON until you are confident the flattened table captures what you need, so you can renormalise differently without re-fetching the data.