Flatten Nested JSON with pandas: A Troubleshooting Guide

The Problem: Real JSON Is Never Flat

If you’ve ever tried to load a nested JSON into a pandas DataFrame, you’ve probably seen something like this:

A column filled with dictionaries. Useless for analysis. Enter json_normalize() — but it’s not magic. This guide tackles the five most frustrating failure modes, with fixes you can copy-paste today.

1. Nested Dictionaries: The Basic Case (and the Trap)

Problem: A simple nested dict seems easy, but json_normalize() flattens only one level by default.

Example:

data = [{'name': 'Alice', 'address': {'street': '123 Main', 'city': 'NYC'}}]

Fix: The function works out of the box for one level of nesting.

import pandas as pd
pd.json_normalize(data)

Trap: If your nested dict has variable keys, json_normalize will still work but may produce NaN for missing keys. That’s expected.

2. Lists of Dictionaries: Use record_path

Problem: When your data has an array of objects, e.g., orders with line items.

data = [{'order_id': 1, 'items': [{'product': 'A', 'qty': 2}, {'product': 'B', 'qty': 1}]}]

Fix: Specify record_path to the array key.

pd.json_normalize(data, record_path='items')

Problem: Parent fields (like order_id) are lost.

Fix: Add meta parameter.

pd.json_normalize(data, record_path='items', meta=['order_id'])

Trap: If meta key doesn’t exist in every record, you’ll get a KeyError. Use errors='ignore'.

3. Multiple Levels: Nested Arrays Inside Arrays

Problem: Imagine a structure like:

data = [{'store': 'NY', 'sales': [{'month': 'Jan', 'details': [{'item': 'shirt', 'revenue': 100}]}]}]

Fix: You cannot flatten more than one record_path directly. Instead, flatten the innermost first, then merge.

# Step 1: Flatten innermost
inner = pd.json_normalize(data, record_path=['sales', 'details'], meta=[['sales', 'month']])

# Step 2: Add store from outer with merge (not shown for brevity)

Note: record_path accepts a list of keys to navigate deeper: ['sales', 'details'].

4. Missing Keys: KeyError and NaN Handling

Problem: JSON from APIs often has optional fields.

data = [{'name': 'Alice', 'age': 30}, {'name': 'Bob'}]

Fix: json_normalize handles missing keys gracefully — it fills with NaN. But if you use meta with a missing key, you get KeyError.

pd.json_normalize(data, meta=['name', 'age'])  # No error; age missing becomes NaN

Trap: If the missing key is in a nested dict, e.g., address.street, the errors='ignore' parameter does NOT help. You must handle missing nested values with .get() in a pre-processing step.

5. Arrays That Need explode()

Problem: When record data is not objects but simple values (e.g., tags).

data = [{'id': 1, 'tags': ['a', 'b']}]

Fix: json_normalize with record_path works for lists of dicts, not simple lists. Use explode() after normalization.

df = pd.DataFrame(data).explode('tags')

Better: If you have multiple columns to keep, normalize first then explode.

6. API Response with a results Key

Problem: APIs commonly wrap data in results.

response = {'status': 'ok', 'results': [{'name': 'Alice'}, {'name': 'Bob'}]}

Fix: Access the list directly.

pd.json_normalize(response['results'])

But wait — parent metadata? Use meta from the outer dict.

pd.json_normalize(response['results'], meta=[['status']])  # Wrong: status is not in results

Correct: meta pulls from the same record, not the outer dict. You need to attach status to each record manually.

results = response['results']
for r in results:
    r['status'] = response['status']
pd.json_normalize(results)

Data Table: Common Error Patterns

Key Takeaways

1. Use record_path for lists of dictionaries. Without it, you get one row per outer record, with dicts in cells.
2. Use meta to preserve parent fields. Pass the list of parent keys you want.
3. Handle missing keys early. Use Python .get() or try/except before normalizing if you expect missing nested keys.
4. For multiple nesting levels, flatten innermost first, then merge. One json_normalize per level.
5. Simple arrays need explode(), not record_path.
6. Wrapped API results require manual attachment of outer metadata. meta only works within the same record.
7. max_level controls depth. Default is None (unlimited). Set to 1 to prevent deep flattening.

How to Apply This

Next time you fetch data from an API:

1. Inspect the JSON structure — print keys and types.
2. If there’s a list of objects, that’s your record_path.
3. List the keys you want to keep from parent levels as meta.
4. Run pd.json_normalize(data, record_path=..., meta=...).
5. Check for NaN — if unexpected, add error handling.

FAQ

1. Why am I getting a KeyError even though the key exists?

Check if the key is in every record. If one record is missing the key, record_path fails. Use errors='ignore' or filter out those records first.

2. How do I flatten deeply nested JSON with multiple arrays?

Flatten each level sequentially. For example, first flatten the outermost array, then flatten the inner arrays and merge using pd.merge() on a common key.

3. Does json_normalize work with JSON strings?

No, it expects Python dictionaries/lists. Use json.loads() first.

4. Can I flatten only certain fields?

Yes, use max_level to limit depth, then manually select columns. Alternatively, specify meta to include only desired parent fields.

5. What’s the difference between json_normalize and pd.json_normalize?

pd.json_normalize is the modern API (pandas 1.0+). json_normalize is legacy but still works. Use pd.json_normalize.

Conclusion

pd.json_normalize() is powerful but unforgiving. By understanding how record_path, meta, and missing keys interact, you can turn any nested JSON into a clean DataFrame. The next time your API returns a nested mess, you’ll know exactly how to flatten it — without the Google search.

Want more? Check out the official pandas documentation for advanced parameters like sep and max_level.