Flatten Nested JSON with pandas | Snippets

API responses often return deeply nested JSON — objects within objects, arrays of records with sub-documents. pandas.json_normalize recursively flattens these structures into tabular DataFrames, handling nested dicts and arrays with a single call. This is essential for turning raw API data into something you can filter, aggregate, or load into a database.

import pandas as pd

def flatten_api_response(data: list[dict], record_path: str | None = None) -> pd.DataFrame:
    """Flatten nested JSON records into a clean DataFrame."""
    if record_path:
        # Flatten nested array (e.g., items within each order)
        df = pd.json_normalize(
            data,
            record_path=record_path,        # Path to nested records
            meta=["id", "status",            # Top-level fields to keep
                  ["customer", "name"],       # Nested meta fields (dot notation)
                  ["customer", "email"]],
            meta_prefix="parent_",           # Prefix to avoid column name conflicts
            sep="_",                          # Separator for nested keys
            errors="ignore",                 # Skip missing fields instead of failing
        )
    else:
        # Simple flatten — recursively unpack all nested dicts
        df = pd.json_normalize(data, sep="_", errors="ignore")
    return df

# Example: nested e-commerce API response
api_data = [
    {
        "id": "ord_001",
        "status": "shipped",
        "customer": {"name": "Alice", "email": "[email protected]"},
        "items": [
            {"sku": "WIDGET-A", "qty": 2, "price": 29.99},
            {"sku": "GADGET-B", "qty": 1, "price": 49.99},
        ],
        "shipping": {"carrier": "FedEx", "tracking": "FX123456"},
    },
    {
        "id": "ord_002",
        "status": "pending",
        "customer": {"name": "Bob", "email": "[email protected]"},
        "items": [
            {"sku": "WIDGET-A", "qty": 5, "price": 29.99},
        ],
        "shipping": {"carrier": "UPS", "tracking": "UP789012"},
    },
]

# Flatten with items as the record path
items_df = flatten_api_response(api_data, record_path="items")
print(items_df.to_string(index=False))
# Output: one row per item, with parent order fields prefixed

# Simple full flatten (no record_path)
orders_df = flatten_api_response(api_data)
print(orders_df.columns.tolist())
# Output: ['id', 'status', 'customer_name', 'customer_email',
#          'shipping_carrier', 'shipping_tracking', ...]

When to use this

Use json_normalize whenever you’re ingesting nested JSON from REST APIs and need flat, tabular data — for loading into SQL databases, CSV export, or downstream analysis. It replaces the tedious manual loop-and-extract pattern. For deeply nested or irregular JSON, combine it with record_path and meta to control exactly which level gets flattened.