Flattening, Formatting, and Exporting Nested JSON APIs to pandas DataFrames: Handling json_normalize, Nested Lists, and Missing Keys
Real-world APIs return deeply nested JSON that breaks naive DataFrame loading. Learn how to use json_normalize, explode, and defensive sanitization to flatten any nested structure—including lists of sub-objects and missing keys—into a clean, analysis-ready DataFrame. Includes a complete, production-ready pipeline function.
⚡ Practitioner21 min readSep 22, 2026Updated Sep 23, 2026
Flattening, Formatting, and Exporting Nested JSON APIs to pandas DataFrames: Handling json_normalize, Nested Lists, and Missing Keys
Real-world APIs don't hand you a tidy spreadsheet. They hand you a deeply nested blob of JSON where your actual data lives three levels down, some records are missing fields that others have, and what should be a single row is actually a list of sub-objects that needs to be exploded before it makes any sense. If you've ever stared at a response from a REST API and thought "how do I turn this into a DataFrame I can actually analyze?"—this lesson is for you.
We'll work through the complete pipeline from raw API response to a clean, analysis-ready DataFrame using a realistic e-commerce orders API as our anchor scenario. You'll learn not just the mechanics of json_normalize and explode, but why nested JSON breaks naive approaches and how to build defensive code that handles missing keys without crashing on production data.
What you'll learn:
How json_normalize flattens nested dicts and where it falls short
How to handle records containing nested lists (arrays of sub-objects)
How to safely handle missing or inconsistent keys across records
How to rename, reorder, and type-cast columns after flattening
How to export the final result to Excel and CSV in a stakeholder-ready format
Before writing a single line of pandas code, you need to understand what kind of nesting you're dealing with. JSON nesting comes in two fundamentally different forms, and they require different solutions.
Here, customer is a dict. To access name, you'd write record["customer"]["name"]. When you flatten this, you want customer.name and customer.email as columns alongside order_id.
Here, items is a list of dicts. This is fundamentally different—it means one order maps to multiple rows. You can't just flatten these into columns because there's a variable number of them per record. This is the harder case, and it's where most people get stuck.
Production APIs typically give you both at once. Let's build a realistic dataset that mirrors what you'd actually get from an e-commerce API.
Notice the deliberate inconsistencies: ORD-1002 has no discount key, and ORD-1003's customer has no tier. This is normal in real APIs—never assume every record has every field.
Step 1: Flattening Nested Dicts with json_normalize
json_normalize is pandas' purpose-built tool for flattening nested dicts. Let's start by applying it naively and see what we get.
# Naive first attempt
df_naive = json_normalize(raw_orders)
print(df_naive.columns.tolist())
print(df_naive.shape)
That's already impressive. json_normalize traversed multiple levels deep—notice shipping.address.city came out correctly even though address was nested inside shipping, which was itself nested. The dotted column names reflect the path through the JSON tree.
json_normalize left the nested list intact as a Python list object in a single cell. This is correct behavior—it can't flatten a list into columns because the number of items varies per record. We'll handle that separately.
Also notice something important about the missing keys. ORD-1002 had no discount key, but our DataFrame still has discount.code and discount.amount columns—those values are just NaN for that row. And ORD-1003's missing customer.tier also becomes NaN. That's exactly what you want.
Key insight
json_normalize is tolerant of missing keys by default. When a key is absent from one record but present in others, it fills with NaN. This behavior means your DataFrame schema is driven by the union of all keys across all records—which is usually what you want.
Controlling Depth and Separator
By default, json_normalize uses a . as the separator between levels. You can change this—some teams prefer underscores to avoid confusion with method calls on column names:
You can also control nesting depth with the max_level parameter. If you only want to go one level deep (useful when the inner structures are complex and you want to handle them separately):
df_shallow = json_normalize(raw_orders, sep='_', max_level=1)
# Now 'shipping_method' and 'shipping_cost' are flat,
# but 'shipping_address' stays as a dict object
For our use case, letting it go all the way is the right call since we know the structure.
Step 2: Handling Nested Lists with explode and json_normalize Together
This is where the real complexity lives. Our items column contains lists of dicts—each order can have one, two, or three items. We need to expand this so each item becomes its own row, with the order-level fields repeated.
The pandas approach has two steps:
explode() to turn the list into one-row-per-item (each cell becomes a dict)
Another json_normalize() call to flatten those dicts into columns
# Start from our flat DataFrame (with items still as a list)
df = json_normalize(raw_orders, sep='_')
# Step 1: explode the items column
df_exploded = df.explode('items').reset_index(drop=True)
print(df_exploded[['order_id', 'items']].head(6))
Now we have 6 rows (2 items for ORD-1001, 1 for ORD-1002, 3 for ORD-1003) with each items cell containing a single dict. The order-level columns like customer_name and shipping_method are correctly repeated.
Now we normalize those item dicts into proper columns:
# Step 2: normalize the items column
items_normalized = json_normalize(df_exploded['items'])
items_normalized.columns = ['item_' + col for col in items_normalized.columns]
print(items_normalized.head())
Always reset_index(drop=True) after explode(). Without it, rows that came from the same original record share the same index value, which causes subtle bugs when you use pd.concat() with axis=1—the join will align on index and produce unexpected NaN values.
The json_normalize record_path Approach (Alternative)
json_normalize also has a built-in record_path parameter designed specifically for this use case. It's worth knowing because it can be more concise for simpler structures:
The record_path tells json_normalize which list to explode. The meta parameter specifies which top-level or nested fields to bring along as repeated metadata on each row. The errors='ignore' parameter is essential—without it, missing keys will raise a KeyError.
Warning
The record_path approach works elegantly for one level of nesting, but it breaks down when you have multiple lists at the same level (e.g., items and returns both being lists in the same record). In those cases, the explode-then-normalize pattern gives you more control.
Step 3: Safely Handling Missing and Inconsistent Keys
We touched on this briefly, but it deserves its own section because missing keys are the single most common source of crashes when processing real APIs.
The Three Failure Modes
1. The missing top-level key — A field that's entirely optional in the API spec. Our discount field is an example. json_normalize handles this gracefully with NaN.
2. The missing nested key — A field that exists in the parent dict but has a key missing inside it. Our customer.tier is an example. Also handled with NaN.
3. The None/null value that breaks further processing — Sometimes an API returns "shipping": null (Python None) rather than omitting the key entirely. This is nastier because json_normalize can't traverse None as if it were a dict.
Let's add a fourth order that demonstrates the third failure mode:
raw_orders_v2 = raw_orders + [
{
"order_id": "ORD-1004",
"created_at": "2024-03-15T16:00:00Z",
"status": "cancelled",
"customer": {
"id": "CUST-220",
"name": "Felix Ramos",
"email": "felix@example.com",
"tier": "standard"
},
"shipping": None, # API returned null for cancelled order
"items": [], # Empty list
"discount": None
}
]
# This will crash:
# df_bad = json_normalize(raw_orders_v2, sep='_')
When shipping is None, json_normalize will try to iterate over it and fail. You need to sanitize before normalizing.
Defensive Sanitization Function
The right approach is a preprocessing function that normalizes your records before handing them to json_normalize:
def sanitize_record(record: dict, defaults: dict = None) -> dict:
"""
Replace None values for dict-type fields with empty dicts,
and None values for list-type fields with empty lists.
Optionally fill in default values for missing keys.
"""
if defaults is None:
defaults = {}
# Apply defaults for missing keys
record = {**defaults, **record}
# Replace None dicts with empty dicts
dict_fields = ['shipping', 'customer', 'discount']
list_fields = ['items']
for field in dict_fields:
if record.get(field) is None:
record[field] = {}
for field in list_fields:
if record.get(field) is None:
record[field] = []
return record
# Apply sanitization
clean_orders = [sanitize_record(r) for r in raw_orders_v2]
# Now normalization is safe
df = json_normalize(clean_orders, sep='_')
print(df[['order_id', 'shipping_method', 'shipping_cost']].to_string())
Output:
order_id shipping_method shipping_cost
0 ORD-1001 express 12.50
1 ORD-1002 standard 5.99
2 ORD-1003 standard 5.99
3 ORD-1004 NaN NaN
ORD-1004's shipping_method and shipping_cost are now NaN rather than causing a crash.
Note
The {**defaults, **record} dict merge syntax ensures that keys in record always win over defaults. This is the Pythonic way to apply defaults without overwriting actual data. If you're on Python 3.8 or earlier, this syntax works fine—it's been available since Python 3.5.
Handling Empty Lists After explode
The empty items list on ORD-1004 also creates a subtle issue after explode():
df_exploded = df.explode('items').reset_index(drop=True)
# Check what happened to ORD-1004
print(df_exploded[df_exploded['order_id'] == 'ORD-1004'][['order_id', 'items']])
Output:
order_id items
6 ORD-1004 NaN
When you explode an empty list, pandas converts it to NaN. That's actually fine for our use case—it means ORD-1004 appears as one row with NaN item fields, which preserves the order in our dataset without creating phantom rows.
But when you run json_normalize(df_exploded['items']), it will fail on that NaN. The fix:
# Only normalize non-null items
mask = df_exploded['items'].notna()
items_normalized = json_normalize(df_exploded.loc[mask, 'items'].tolist())
items_normalized.index = df_exploded.loc[mask].index
items_normalized.columns = ['item_' + col for col in items_normalized.columns]
df_final = df_exploded.drop(columns=['items'])
df_final = df_final.join(items_normalized) # Join on index
print(df_final[['order_id', 'item_sku', 'item_qty']].to_string())
ORD-1004 appears with NaN item fields—the order is preserved, no crash.
Step 4: Cleaning, Renaming, and Typing the Flattened DataFrame
After flattening, you typically have column names that reflect the JSON path but aren't ideal for analysis or reporting. Let's do a proper post-normalization cleanup.
Use pd.to_numeric(series, errors='coerce') instead of direct casting when you're not 100% sure the column is clean. errors='coerce' turns unparseable values into NaN rather than raising an exception. You can then decide how to handle those NaN values downstream. This is especially important for data coming from APIs where numeric fields occasionally contain strings like "N/A".
Notice we used pandas' nullable integer type 'Int64' (capital I) rather than the standard int64. This is important because item_qty has NaN values for ORD-1004's row, and standard int64 can't hold NaN. See Cleaning Messy Data with pandas: Missing Values, Duplicates, and Data Types for a full treatment of nullable dtypes.
Step 5: Exporting to Excel and CSV
Now that your DataFrame is clean and structured, exporting is the final step. For most stakeholders, Excel is the preferred delivery format. For downstream systems or archiving, CSV or JSON works better.
Putting It All Together: A Production-Ready Pipeline Function
In real projects, you'll call this pipeline repeatedly—whenever you pull a fresh batch from the API. Wrapping it in a function makes it reusable and testable.
import pandas as pd
from pandas import json_normalize
from typing import Optional
def flatten_orders_api(
raw_records: list[dict],
include_cancelled: bool = True
) -> pd.DataFrame:
"""
Flatten a list of raw order records from the e-commerce API
into a tidy, one-row-per-line-item DataFrame.
Parameters
----------
raw_records : list of dicts
The raw JSON response from the orders API.
include_cancelled : bool
If False, drops rows where order_status == 'cancelled'.
Returns
-------
pd.DataFrame
"""
# --- 1. Sanitize records ---
def sanitize(record):
record = dict(record) # shallow copy to avoid mutating original
for field in ['shipping', 'customer', 'discount']:
if record.get(field) is None:
record[field] = {}
if record.get('items') is None:
record['items'] = []
# Fill missing nested keys
record['customer'].setdefault('tier', None)
return record
clean_records = [sanitize(r) for r in raw_records]
# --- 2. Normalize the top-level structure ---
df = json_normalize(clean_records, sep='_')
# --- 3. Explode and normalize items ---
df = df.explode('items').reset_index(drop=True)
mask = df['items'].notna()
if mask.any():
items_df = json_normalize(df.loc[mask, 'items'].tolist())
items_df.index = df.loc[mask].index
items_df.columns = ['item_' + c for c in items_df.columns]
df = df.drop(columns=['items']).join(items_df)
else:
df = df.drop(columns=['items'])
# --- 4. Rename columns ---
rename_map = {
'order_id': 'order_id',
'created_at': 'created_at',
'status': 'order_status',
'customer_id': 'customer_id',
'customer_name': 'customer_name',
'customer_email': 'customer_email',
'customer_tier': 'customer_tier',
'shipping_method': 'shipping_method',
'shipping_cost': 'shipping_cost',
'shipping_address_city': 'ship_city',
'shipping_address_state': 'ship_state',
'shipping_address_country': 'ship_country',
'discount_code': 'discount_code',
'discount_amount': 'discount_amount',
'item_sku': 'item_sku',
'item_name': 'item_name',
'item_qty': 'item_qty',
'item_unit_price': 'item_unit_price',
}
# Only rename columns that exist (guards against schema drift)
rename_map = {k: v for k, v in rename_map.items() if k in df.columns}
df = df.rename(columns=rename_map)
# --- 5. Type conversions ---
df['created_at'] = pd.to_datetime(df['created_at'], utc=True, errors='coerce')
df['shipping_cost'] = pd.to_numeric(df['shipping_cost'], errors='coerce')
df['item_qty'] = pd.to_numeric(df['item_qty'], errors='coerce').astype('Int64')
df['item_unit_price'] = pd.to_numeric(df['item_unit_price'], errors='coerce')
df['discount_amount'] = pd.to_numeric(df['discount_amount'], errors='coerce').fillna(0.0)
df['customer_tier'] = df['customer_tier'].fillna('standard')
# --- 6. Calculated columns ---
df['item_total'] = df['item_qty'] * df['item_unit_price']
# --- 7. Optional filter ---
if not include_cancelled:
df = df[df['order_status'] != 'cancelled'].reset_index(drop=True)
# --- 8. Column ordering (only include columns that exist) ---
desired_order = [
'order_id', 'created_at', 'order_status',
'customer_id', 'customer_name', 'customer_email', 'customer_tier',
'ship_city', 'ship_state', 'ship_country',
'shipping_method', 'shipping_cost',
'discount_code', 'discount_amount',
'item_sku', 'item_name', 'item_qty', 'item_unit_price', 'item_total'
]
final_cols = [c for c in desired_order if c in df.columns]
return df[final_cols]
# Usage
df_orders = flatten_orders_api(raw_orders_v2, include_cancelled=False)
print(df_orders.to_string())
Key insight
Building your pipeline as a function—rather than a notebook of sequential cells—makes it dramatically easier to reuse. You can call it inside a scheduled job, pass fresh API responses to it, and write unit tests against it. If you're thinking about structuring your project files properly, Structuring a Reusable Data Analysis Project: Functions, Modules, Notebooks, and Scripts covers exactly how to organize this kind of code.
Hands-On Exercise
Here's a dataset from a fictional support ticket API. Apply the full pipeline—sanitize, normalize, explode, clean, export.
Write a sanitize_ticket() function that handles None dicts and empty lists
Use json_normalize with sep='_' to flatten the top-level + nested dicts
Explode the tags column and normalize the tag dicts
Rename columns, fix data types (parse submitted_at as datetime), and add a is_resolved boolean column based on whether resolution_status is "resolved"
Export the result to a CSV file named tickets_flat.csv
Expected output shape: 4 rows × ~15 columns (TKT-5003 with no tags should appear as one row with NaN tag fields)
Common Mistakes & Troubleshooting
Mistake 1: Forgetting reset_index After explode
# WRONG - will cause NaN-filled rows when joined
df_exploded = df.explode('items')
items_df = json_normalize(df_exploded['items'].dropna().tolist())
df_final = pd.concat([df_exploded.drop(columns='items'), items_df], axis=1)
# RIGHT - reset index first so concat aligns correctly
df_exploded = df.explode('items').reset_index(drop=True)
Mistake 2: Using json_normalize on a Series Directly
# WRONG - json_normalize expects a list of dicts, not a Series of dicts
items_df = json_normalize(df_exploded['items'])
# RIGHT - convert to list first, then handle the index separately
items_df = json_normalize(df_exploded['items'].dropna().tolist())
items_df.index = df_exploded[df_exploded['items'].notna()].index
Actually in recent versions of pandas, json_normalize does accept a Series—but it ignores the index, which causes silent misalignment. Being explicit with .tolist() and manually setting the index is safer.
Mistake 3: Mutating the Original Records
# WRONG - modifies raw_orders in place
for record in raw_orders:
if record.get('shipping') is None:
record['shipping'] = {}
# RIGHT - work on a copy
clean_records = [dict(r) for r in raw_orders]
for record in clean_records:
if record.get('shipping') is None:
record['shipping'] = {}
If raw_orders came from requests.json() or a database, mutating it in place can cause hard-to-trace bugs if the same object is used elsewhere in your code.
Mistake 4: Assuming Column Names After Normalization
The json_normalize output columns depend on what keys exist in the data. If you hardcode column names downstream without checking:
# This crashes if 'discount_code' doesn't exist (e.g., no records had discounts)
df['discount_applied'] = df['discount_code'].notna()
# RIGHT - guard first
if 'discount_code' in df.columns:
df['discount_applied'] = df['discount_code'].notna()
else:
df['discount_applied'] = False
This is especially important when building pipelines that process data incrementally—early batches might not contain all fields.
Warning
Schema drift is a real risk with live APIs. If the API team adds a new nested field or renames one, your pipeline's rename map will silently produce wrong or missing columns. Add a validation step after flattening that checks for the minimum expected set of columns and raises an informative error if any are missing. The approaches covered in Validating and Profiling a New Dataset with pandas: Row Counts, Distributions, and Outlier Checks Before You Analyze apply equally well to API data.
Mistake 5: Not Handling Deeply Nested Lists Within Lists
Some APIs go further—a record might have a list of orders, each containing a list of items, each containing a list of attributes. json_normalize + explode works one level at a time. For two levels of nesting, you need to chain the process:
# Level 1: explode orders
df = df.explode('orders').reset_index(drop=True)
df = pd.concat([df.drop(columns='orders'),
json_normalize(df['orders'].dropna().tolist())
.set_index(df[df['orders'].notna()].index)], axis=1)
# Level 2: explode items within orders
df = df.explode('items').reset_index(drop=True)
# ... and so on
For APIs with truly deep nesting, consider writing a recursive flattening function, or look at libraries like flatten_json that automate this.
Summary & Next Steps
You now have a complete toolkit for turning nested JSON API responses into clean, analysis-ready DataFrames. The key principles to remember:
json_normalize handles nested dicts by creating dotted column names—it's tolerant of missing keys by default
Nested lists require explode + a second json_normalize—don't try to flatten them into columns
Sanitize before you normalize—replace None dicts with {} and None lists with [] to avoid crashes
Set and restore the index carefully when using explode + concat or join
Guard against schema drift with defensive column checks in your pipeline
With your data now in a proper tabular structure, you're ready to do the interesting analytical work. Some natural next steps from here:
The pattern you learned here—sanitize, normalize, explode, normalize again, clean, export—applies to virtually any nested JSON source, whether it's a REST API, a NoSQL database dump, or a webhook payload. Master this shape and you'll be able to handle the messy, real-world data that most analysts find overwhelming.
