Flattening Nested JSON with pandas json_normalize

This walkthrough tackles one recurring problem from Parsing JSON and XML API Responses: collapsing a nested API payload into flat rows you can write to a CSV, a Parquet file or a database table.

pandas.json_normalize is the fastest route from nested JSON to a table. Point it at a list of records and it walks each object, joining nested keys into column names like user.address.city. Three arguments do almost all the work: record_path selects an inner list to explode into one row per element, meta names the parent fields to carry down onto those rows, and sep chooses the delimiter. Get those right and most API responses become a clean DataFrame in a single call — no manual recursion, no nested loops.

How json_normalize Builds Column Names

The function performs a depth-first walk of each record. Every path from the root to a scalar value becomes one column, and the path segments are joined with sep, which defaults to .. A dict nested three levels deep therefore produces a column name with two separators in it.

import pandas as pd

records = [
    {"id": 7, "user": {"name": "Ada", "address": {"city": "Bath", "zip": "BA1"}}},
    {"id": 8, "user": {"name": "Bo", "address": {"city": "York", "zip": "YO1"}}},
]

df = pd.json_normalize(records)
print(df.columns.tolist())
# ['id', 'user.name', 'user.address.city', 'user.address.zip']

Two consequences follow from that walk. First, a value that is itself a list of dicts is not flattened — it lands in the cell as a Python list, because there is no way to represent several rows' worth of data in one cell. That is what record_path exists to solve. Second, records that disagree about which keys exist produce the union of all columns, with NaN filling the gaps. This is convenient until a typo in a key name silently creates an all-null column that nothing complains about.

If you prefer a different delimiter, pass sep="_". This is worth doing routinely: a dotted name such as user.name cannot be reached with pandas attribute access, and it collides awkwardly with query() expressions and with column names in SQL targets.

Exploding Nested Lists with record_path

The harder case is a record containing a list of sub-items — an order with line items, a product with variants, a post with comments. You usually want one row per sub-item with the parent fields repeated, which is exactly what record_path and meta produce together.

import pandas as pd

payload = {
    "order_id": "A-100",
    "customer": "Ada",
    "shipping": {"country": "GB", "method": "standard"},
    "items": [
        {"sku": "AER-001", "qty": 1, "price": 1395.0},
        {"sku": "MAT-009", "qty": 2, "price": 49.5},
    ],
}

df = pd.json_normalize(
    payload,
    record_path="items",                          # explode this list -> one row each
    meta=["order_id", "customer", ["shipping", "country"]],
)
print(df.to_string(index=False))
#     sku  qty   price order_id customer shipping.country
# AER-001    1  1395.0    A-100      Ada               GB
# MAT-009    2    49.5    A-100      Ada               GB

Note the shape of the third meta entry. A plain string reaches a top-level key; a list of strings walks a path, so ["shipping", "country"] reaches shipping.country and names the resulting column with the same dotted form. Mixing the two in one meta list is legal and common.

record_path also accepts a list for nested lists — record_path=["data", "orders", "items"] descends three levels before exploding. What it will not do is explode two sibling lists at once: json_normalize produces a single rectangle, so if a record has both items and payments, normalise it twice and join the results on the order id.

End to End: API Response to Parquet

Putting it together with a real request — fetch JSON with explicit headers, normalise the nested results, coerce the types, and write a columnar file ready for analysis.

import pandas as pd
import requests

HEADERS = {
    "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 "
                  "(KHTML, like Gecko) Chrome/124.0.0.0 Safari/537.36",
    "Accept": "application/json",
}


def orders_to_parquet(url: str, path: str) -> pd.DataFrame:
    resp = requests.get(url, headers=HEADERS, timeout=15)
    resp.raise_for_status()
    payload = resp.json()

    df = pd.json_normalize(
        payload["orders"],
        record_path="items",
        meta=["order_id", "customer", ["shipping", "country"]],
        sep="_",
        errors="ignore",              # tolerate records missing a meta key
        record_prefix="item_",        # avoid a parent/child name collision
    )
    df["item_price"] = pd.to_numeric(df["item_price"], errors="coerce")
    df["item_qty"] = pd.to_numeric(df["item_qty"], errors="coerce").astype("Int64")
    df.to_parquet(path, index=False)
    return df


if __name__ == "__main__":
    frame = orders_to_parquet("https://api.example.com/v1/orders", "orders.parquet")
    print(frame.dtypes)

record_prefix deserves more attention than it usually gets. If a line item has a country field and meta also carries shipping.country, the two collide and pandas raises ValueError: Conflicting metadata name country, need distinguishing prefix. Prefixing the exploded columns removes the whole class of collision in advance.

The Int64 cast (capital I) is the nullable integer dtype. Without it, a single missing quantity forces the column to float64 and your quantities come back as 1.0 and 2.0, which then round-trip into a database as floats. From here the DataFrame drops into any sink covered in Storing and Exporting Scraped Data, and the format trade-offs are compared in Exporting Scraped Data to CSV and Parquet.

When json_normalize Is the Wrong Tool

json_normalize is a convenience wrapper, not a high-performance path. It walks every record in Python, builds an intermediate list of flat dicts, and only then constructs the DataFrame. Three situations argue for writing the extraction by hand instead.

You want a small, known subset of fields. Flattening a 60-key record to pull out four columns does 15 times more work than necessary. A comprehension is both faster and self-documenting:

rows = [
    {
        "order_id": order["order_id"],
        "customer": order.get("customer"),
        "country": (order.get("shipping") or {}).get("country"),
        "sku": item["sku"],
        "qty": item["qty"],
    }
    for order in payload["orders"]
    for item in order.get("items", [])
]
df = pd.DataFrame(rows)

This version also fails loudly on a missing order_id and quietly on an optional customer, which is usually the behaviour you want — errors="ignore" cannot express that distinction because it applies to every meta field at once.

The payload does not fit in memory. json_normalize needs the whole parsed structure plus the intermediate rows plus the DataFrame resident at the same time, so peak usage is roughly three times the parsed size. For a multi-gigabyte export, stream the records and append to a Parquet writer in batches instead of materialising one frame.

The shape varies between records. If half your records nest items under items and the other half under line_items, no single record_path covers both. Normalise the key names in a pre-pass and then flatten, rather than running json_normalize twice and concatenating frames with mismatched columns.

Where json_normalize genuinely wins is exploratory work and irregular payloads: one call gives you every column that exists across a heterogeneous batch, which is exactly what you want when you are still learning the shape of an API you did not design.

Edge Cases and Caveats

• Missing meta keys raise KeyError. If some records lack a field named in meta, pandas raises KeyError: 'shipping' and the whole batch dies. Pass errors="ignore" so the column is filled with NaN instead. Be aware this also masks genuine typos, so check the resulting null rate.
• A missing record_path list is fatal, not ignorable. errors="ignore" covers meta only. A record with no items key raises KeyError: 'items' regardless, so filter the input first: [r for r in orders if r.get("items")].
• Lists of scalars do not explode cleanly. record_path expects a list of dicts. A list of plain strings or numbers lands as a single object-dtype column; explode it afterwards with df.explode("tags").
• Very deep nesting is slow and wide. json_normalize is a Python-level recursion, not a vectorised operation — expect roughly 20,000–60,000 records per second on a modest object, and hundreds of columns if you flatten everything. Select the record_path and meta you need rather than the entire tree.
• max_level caps the depth. max_level=1 stops flattening below the first nested level and leaves deeper objects intact as dict-valued cells, which is often what you want when a sub-object is really a JSON blob you plan to store whole.
• Dotted column names need bracket access. After flattening, use df["user.address.city"], not df.user.address.city; the dotted string is one column label, not an attribute chain. Using sep="_" avoids the trap entirely.
• Empty records produce an empty frame with no columns. Downstream code that assumes a column exists then fails with a confusing KeyError. Reindex against an expected column list before writing.
• Duplicate column names cannot happen, but silent shadowing can. If a nested key flattens to a name that a meta field also produces, pandas raises ValueError: Conflicting metadata name. It does not warn when two different source paths flatten to the same name under a custom sep — user_id from user.id and a literal user_id key collide silently, so pick a separator that does not occur in your key names.
• Types are inferred per column, not declared. A price that is a string in one record and a number in another yields an object-dtype column. Coerce explicitly with pd.to_numeric(..., errors="coerce"), and enforce the schema properly using Validating Scraped Data with Pydantic. Locale-dependent values such as "1.395,00" need normalising before any cast — see Normalizing Prices, Dates and Units.

Writing Batches Without Holding Everything in Memory

A crawl that paginates through 500 API pages should not build one 500-page DataFrame. Normalise each page, append it to a Parquet writer, and let the page go. Resident memory then stays proportional to one page rather than to the whole run.

import pandas as pd
import pyarrow as pa
import pyarrow.parquet as pq


def write_pages(pages: list[dict], path: str) -> int:
    writer: pq.ParquetWriter | None = None
    rows = 0
    try:
        for payload in pages:
            df = pd.json_normalize(
                payload.get("orders", []),
                record_path="items",
                meta=["order_id", "customer"],
                sep="_",
                errors="ignore",
                record_prefix="item_",
            )
            if df.empty:
                continue
            table = pa.Table.from_pandas(df, preserve_index=False)
            if writer is None:
                writer = pq.ParquetWriter(path, table.schema)
            writer.write_table(table)
            rows += len(df)
    finally:
        if writer is not None:
            writer.close()
    return rows

The one hazard is schema drift between pages. If page 7 contains a field page 1 did not, write_table raises ValueError: Table schema does not match schema used to create file. Fix it by declaring the schema up front from a known column list and reindexing each frame to match — df = df.reindex(columns=EXPECTED) — rather than letting pandas infer it from whichever page happened to come first.

Frequently Asked Questions

What does json_normalize do that json.loads does not?json.loads parses text into nested Python dicts and lists; json_normalize takes those already-parsed objects and flattens them into a tabular DataFrame with joined column names. They are sequential steps rather than alternatives — parse first, then normalise.

How do I get one row per item in a nested list? Set record_path to the list you want to explode and name the parent fields you want repeated in meta. Each element of that list becomes its own row, and the meta values are copied onto every row produced from the same parent.

How do I avoid a KeyError when some records are missing a field? Pass errors="ignore", which fills missing meta keys with NaN instead of raising. This does not cover record_path — a record missing the list you are exploding still raises, so filter those records out before the call.

Can I change the dot separator in the flattened column names? Yes, pass sep with your preferred delimiter, for example sep="_" to produce user_address_city. This is generally the better default because underscore names work with pandas attribute access, query() expressions and SQL column names, while dotted names do not.

Related

• Parsing JSON and XML Responses — the parent topic, covering the parse step that comes first
• Parsing XML Sitemaps with Python — the XML counterpart to this workflow
• Cleaning and Validating Scraped Data — what to do with the flat rows once you have them
• Saving Scraped Data to PostgreSQL — loading the resulting table into a database