JSON to CSV in Python: the pandas recipe that survives nested data

You pasted the five-line recipe and got a CSV with an unnamed 0, 1, 2 column and cells full of {'name': 'Ana', ...}. Or pandas complained about a file you never opened. The recipe is fine. Your JSON just isn't flat.

TL;DR

• Look up your exact error in the table below; six of the eight fixes are a single parameter.
• Flat array of objects: read_json plus to_csv(index=False). Nested: json_normalize with record_path and meta.
• A list inside a record is a choice: explode() for one row per item, json.dumps for one cell per record.

Look up your error first

Symptom	Cause	Fix
Unnamed first column of 0, 1, 2	pandas writes its row index	to_csv(..., index=False)
Cells full of {'sku': ...} or [{...}]	nested values written as Python text	json_normalize, see nested section
FileNotFoundError: File [{"id": 1}] does not exist	pandas 3.0 reads a string as a path	wrap it in StringIO(...)
ValueError: Trailing data	the file is NDJSON, one object per line	read_json(..., lines=True)
KeyError: "Key 'coupon' not found..."	a record lacks a meta field	json_normalize(..., errors="ignore")
ValueError: dict contains fields not in fieldnames	DictWriter header came from too few records	extrasaction="ignore" or build the key union
Blank line between rows on Windows	file opened without newline=''	open(..., newline="")
Garbled accents in Excel	no byte-order mark in the file	encoding="utf-8-sig"

Check the shape before you pick a method

Three questions decide everything: is the top level a list of objects, do all objects share the same keys, and is any value a list or a dict? The examples below use this orders.json. Order 2 has no coupon, on purpose.

[
  {"id": 1, "coupon": "SPRING",
   "customer": {"name": "Ana", "address": {"city": "Lisbon"}},
   "items": [{"sku": "A1", "qty": 2}, {"sku": "B2", "qty": 1}]},
  {"id": 2,
   "customer": {"name": "Ben", "address": {"city": "Oslo"}},
   "items": [{"sku": "C3", "qty": 5}]}
]

Try this: run it on your own file. It answers all three questions and survives a top-level object or an empty list.

import json
data = json.load(open("orders.json"))
if isinstance(data, dict):
    print("one object, keys:", list(data))
elif data:
    keys = [set(r) for r in data]
    print(len(data), "records")
    print("keys missing from some:", set.union(*keys) - set.intersection(*keys))
    print("nested keys:", {k for r in data for k, v in r.items()
                          if isinstance(v, (list, dict))})

On orders.json it prints 2 records, {'coupon'} and {'customer', 'items'}. A single object that is one record can go straight to pd.json_normalize(data). If it only wraps the records ({"rows": [...]}), pass the inner list.

Flat JSON takes two calls, plus one pandas 3.0 trap

If the nested-keys set is empty, you are done in two lines:

import pandas as pd

df = pd.read_json("users.json")
df.to_csv("users.csv", index=False)

The trap is in older tutorials, which hand read_json a literal string. Everything in this post ran on pandas 3.0.6 and Python 3.11, and there the string is read as a file path:

pd.read_json('[{"id": 1}]')
# FileNotFoundError: File [{"id": 1}] does not exist

from io import StringIO
pd.read_json(StringIO('[{"id": 1}]'))   # works

The message talks about a file because pandas thinks you gave it one. Real file paths are unaffected.

Nested JSON: record_path picks the rows, meta keeps the parents

Run the recipe on orders.json and nothing fails, which is the problem:

,id,coupon,customer,items
0,1,SPRING,"{'name': 'Ana', 'address': {'city': 'Lisbon'}}","[{'sku': 'A1', 'qty': 2}, {'sku': 'B2', 'qty': 1}]"
1,2,,"{'name': 'Ben', 'address': {'city': 'Oslo'}}","[{'sku': 'C3', 'qty': 5}]"

Each nested value was written as its Python text. Excel sees a blob, not columns. json_normalize fixes it:

df = pd.json_normalize(
    data,
    record_path="items",
    meta=["id", ["customer", "name"], ["customer", "address", "city"]],
    sep="_",
)
df.to_csv("items.csv", index=False)

  sku  qty id customer_name customer_address_city
0  A1    2  1           Ana                Lisbon
1  B2    1  1           Ana                Lisbon
2  C3    5  2           Ben                  Oslo

Point record_path at the list that should become rows, and name the parent fields you want repeated in meta. Nested dicts such as customer flatten without help. Ana and Lisbon repeat on both rows of order 1, which is what you want if anyone will filter or pivot by item.

Choose sep once, at export time. The default . gives customer.address.city, which causes trouble in SQL imports and some spreadsheet formulas. Add "coupon" to meta and you get the KeyError from the table, because order 2 lacks it. errors="ignore" fills that cell with NaN. The json_normalize reference documents all three parameters.

Arrays inside records: one row per item or one cell

Normalize the orders without record_path and items stays a list in one column. pandas can't guess what you want, so you pick.

Option A, one row per item:

flat = pd.json_normalize(data, sep="_")
rows = flat.explode("items").reset_index(drop=True)
items = pd.json_normalize(rows.pop("items"))
out = pd.concat([rows, items], axis=1)
out.to_csv("a.csv", index=False)

id,coupon,customer_name,customer_address_city,sku,qty
1,SPRING,Ana,Lisbon,A1,2
1,SPRING,Ana,Lisbon,B2,1
2,,Ben,Oslo,C3,5

Option B, one cell per record:

flat["items"] = flat["items"].apply(json.dumps)
flat.to_csv("b.csv", index=False)

id,coupon,items,customer_name,customer_address_city
1,SPRING,"[{""sku"": ""A1"", ""qty"": 2}, {""sku"": ""B2"", ""qty"": 1}]",Ana,Lisbon
2,,"[{""sku"": ""C3"", ""qty"": 5}]",Ben,Oslo

A gives 3 rows, B gives 2. Default to A when a person will filter or pivot the CSV; use B only when the row count must match the source file. json.loads turns a B cell back into a list.

Try this: read the CSV back and compare shapes. A mismatch, or a cell that starts with { or [, means a nested value slipped through as text.

back = pd.read_csv("a.csv")
print(out.shape, back.shape)    # (3, 6) (3, 6)
print(back.astype(str).apply(lambda c: c.str.startswith(("{", "["))).any().any())

On a.csv that prints False. On b.csv it prints True, as expected, since B stores JSON text on purpose. A hit on real data can also be a legitimate text field, so look at the cell.

No pandas: csv.DictWriter handles flat records

For a cron job or Lambda where pandas is a hassle, json plus csv.DictWriter covers flat records:

import csv, json

with open("users.json") as f:
    records = json.load(f)

fields = list(dict.fromkeys(k for r in records for k in r))
with open("users.csv", "w", newline="", encoding="utf-8-sig") as f:
    w = csv.DictWriter(f, fieldnames=fields)
    w.writeheader()
    w.writerows(records)

Taking fields from every record is what prevents the fieldnames error: a record with an extra coupon key would otherwise stop the write. Missing keys become empty cells. A list or dict value is written as its str(), exactly the blob from the naive recipe above, so nested data needs pandas. The csv docs say it is always safe to pass newline=''.

Big files: lines=True first, chunks second

Many files called "huge JSON" are NDJSON, and lines=True was the whole fix. If memory is the issue, add chunksize and append each chunk:

reader = pd.read_json("big.ndjson", lines=True, chunksize=10_000)
for i, chunk in enumerate(reader):
    chunk.to_csv("big.csv", mode="w" if i == 0 else "a",
                 header=(i == 0), index=False)

A single giant JSON array can't be chunked this way. For that case the third-party ijson library streams records one at a time.

Next time a recipe fails: run the shape check, normalize with record_path and meta, decide A or B for any arrays, and read the CSV back.