How to convert JSON to CSV in Python

The five-line pandas version works for flat JSON

An array of objects that all share the same keys takes two pandas calls. We ran every block here on pandas 3.0.6.

import pandas as pd

df = pd.read_json("users.json")
with open("users.csv", "w", newline="", encoding="utf-8") as f:
    df.to_csv(f, index=False)

The top answer to Stack Overflow's "How can I convert JSON to CSV?" is these two commands. The second answer names the catch: nested objects can't be converted directly.

Old snippets hide one more trap. pandas 3.0 stopped accepting raw JSON strings in read_json (GH 53767), so pd.read_json('[{"id": 1}]') now fails. On 3.0.6 we got FileNotFoundError: File [{"id": 1}] does not exist, because pandas reads the string as a path. Wrap it: pd.read_json(StringIO(json_string)) with from io import StringIO. File paths are unaffected.

Try this: check your file's shape in ten seconds. Load it with json.load and ask three questions. Is the top level a list of objects? Do all objects share the same keys? Does any value hold a list or a dict? If the top level is one object instead of a list, pd.json_normalize(data) is already your table. For the last question, [k for k, v in data[0].items() if isinstance(v, (list, dict))] names the nested keys of the first record. A dict value sends you to the next section, a list value to the one after.

Nested JSON needs json_normalize with record_path and meta

pd.json_normalize flattens nested dicts into columns, and its two parameters handle everything below a list. record_path names the list that holds your records, and meta re-attaches the parent fields to each of them.

import json
import pandas as pd

with open("orders.json", encoding="utf-8") as f:
    data = json.load(f)

# one row per order, nested dicts flattened
orders = pd.json_normalize(data, sep="_")

# one row per item, parent fields attached
items = pd.json_normalize(
    data,
    record_path="items",
    meta=["id", ["customer", "name"], ["customer", "address", "city"]],
    sep="_",
)
# utf-8-sig: Excel-safe BOM, explained in the errors section
items.to_csv("items.csv", index=False, encoding="utf-8-sig")

In our sample, two orders with three items become three rows:

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

Ana and Lisbon repeat on both of order 1's rows; that's meta. Nested dicts flatten on their own, but a list stays in one cell until you point record_path at it. That's the situation behind a 175,000-view Stack Overflow question: json_normalize "only flattens first level".

sep is the column-name knob. The default '.' gives customer.address.city. Dots cause trouble in SQL imports and some spreadsheet formulas, so sep='_' is the safer default for exports.

Arrays inside records become repeat rows or one cell, and you choose

Every converter picks what an array turns into. explode() gives one row per item, apply(pd.Series) widens the items into columns, and doing nothing leaves Python's repr text of the list in one cell.

flat = pd.json_normalize(data, sep="_")

# option A: one row per item (row count grows)
rows = flat.explode("items").reset_index(drop=True)
rows = rows.join(pd.json_normalize(rows.pop("items")))

# option B: one cell per record (row count stays stable)
flat["items"] = flat["items"].apply(json.dumps)

The two-order sample becomes three rows with option A, stays at two with option B. Excel readers who filter and pivot usually want option A; an export that has to match your source row for row wants option B. Option B's cell holds the array as one JSON text string, so Excel shows a long {...}; a json.loads pass over the column brings the items back. Our own converter exposes the same choice as a toggle.

Try this: read the CSV back and compare. pd.read_csv("items.csv").shape should match the shape of the DataFrame you wrote. If any cell starts with { or [, a list or dict slipped through as repr text and you skipped a flatten step.

The csv module works without pandas

json.load plus csv.DictWriter converts flat records with zero dependencies, and newline='' goes in the open() call from the first line.

import csv
import json

with open("users.json", encoding="utf-8") as f:
    records = json.load(f)

fieldnames = sorted({key for record in records for key in record})

with open("users.csv", "w", newline="", encoding="utf-8-sig") as f:
    writer = csv.DictWriter(f, fieldnames=fieldnames)
    writer.writeheader()
    writer.writerows(records)

DictWriter needs fieldnames up front, so the code collects the union of keys across all records. Our converter builds its header the same way. Missing keys become empty strings. It also runs str() on lists and dicts (documented), so nested data comes out as repr text. For anything nested, use pandas.

The errors you will actually hit, and the one-parameter fix for each

Most of these come from documented defaults, so each fix is one parameter.

• An unnamed first column of 0, 1, 2... has no traceback. to_csv writes the index by default. Pass index=False.
• ValueError: dict contains fields not in fieldnames: 'name' means a record has a key your fieldnames list lacks. Pass extrasaction='ignore' to drop the extras, or build fieldnames from the union as above.
• KeyError: "Key 'coupon' not found. To replace missing values of 'coupon' with np.nan, pass in errors='ignore'" comes from json_normalize when a meta key is missing in some records. Pass errors='ignore' and those cells become NaN.
• Blank lines between every row on Windows: text mode adds a second line ending to the one csv writes. Pass newline=''. The Python docs say: "It should always be safe to specify newline=''."
• Accents or non-Latin text look like garbage in Excel: plain utf-8 writes no byte order mark (BOM), so Excel guesses a legacy encoding. encoding='utf-8-sig' writes the BOM (0xef 0xbb 0xbf). A 92,000-view Stack Overflow thread is this exact case: Bangla text, unreadable as utf-8 and fine as utf-8-sig.
• ValueError: Trailing data from read_json usually means the file is newline-delimited JSON, not one JSON document. We reproduced it on 3.0.6. Add lines=True. Each line is one complete object:

{"id": 1, "user": "ana", "action": "signup"}
{"id": 2, "user": "sam", "action": "login"}

Which method for which JSON shape