Home / Guides / JSON to CSV Python

JSON to CSV Python

There are four common ways to do JSON to CSV Python, and they do not write the same file. This page runs all four over the same 27 edge-case shapes and shows the literal output bytes — so you can see which method gives you the CSV you actually wanted, and which four shapes quietly change your data on the way through.

[
  { "id": 1,
    "user": { "city": "NYC" } },
  { "id": 2,
    "user": { "city": "LA" } }
]

id,user
1,{'city': 'NYC'}
2,{'city': 'LA'}

That second column is Python's dictionary repr, not JSON — single quotes, and no parser on the far side will read it. It is not even quoted, because {'city': 'NYC'} happens to contain no comma; add a second key and pandas will quote the cell, which is exactly why a diff against a browser converter is noisy in a way that is hard to predict. The rest of this page is about the three other methods and the four shapes where the differences are worse than cosmetic.

What the conversion actually does

JSON is a tree; CSV is a grid. The conversion is the act of deciding what happens to everything that does not fit in a grid. Three decisions get made, whether you make them or your library makes them for you:

1. Which objects become rows. An array of objects maps one-to-one. A single object needs to be wrapped in a list first. A JSON Lines file needs to be read line by line, not as one document.
2. What happens to nested objects and arrays. Either they are expanded into more columns (user.city), or they are serialised into one cell as text. Every method below picks a different default, and one of them picks Python's repr.
3. Which columns exist. If row 1 has a,b and row 2 has b,c, the header has to be the union. The order of that union is not standardised, and it is the first thing two methods will disagree about.

Everything below was produced by running the real code on this machine, not by reading documentation. The input shapes are the 27 fixtures published in the open edge-case corpus, so you can download them and reproduce every number on this page.

Method A — the pandas one-liner

This is the answer you will find first, and for flat data it is fine:

import pandas as pd

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

Two things to know about it. index=False is not optional — without it pandas writes an unnamed first column holding the row number, which is the single most common complaint about this snippet. And read_json on a JSON Lines file raises ValueError: Trailing data unless you pass lines=True.

Measured over the 27 published shapes, this method matches the browser converter on 10 of the 25 shapes it can produce at all. It fails to produce a file for three of them: a single object (If using all scalar values, you must pass an index), a JSON Lines file, and an array with a trailing comma.

Method B — pandas with json_normalize

If your objects nest, read_json gives you a column full of dictionary reprs. json_normalize is the fix, and it produces the same dot-path columns a flattening converter does:

import json
import pandas as pd

data = json.load(open("data.json", encoding="utf-8"))
if isinstance(data, dict):
    data = [data]

df = pd.json_normalize(data)
df.to_csv("out.csv", index=False)

That takes the match rate from 10/25 to 16/25. What it still gets wrong: arrays stay as Python lists (['red', 'blue'] rather than ["red","blue"]), booleans come out as True rather than true, a missing key turns a column into floats, and an array of bare scalars raises All items in data must be of type dict or NA-like, found int.

Method C — the standard-library recipe

If you want output that a diff will not complain about, use json and csv directly. The naive version — read the JSON, hand the rows to csv.DictWriter — matches on 16/25. Adding the two missing pieces, flattening and JSON-serialising the nested values, takes it to 22/25:

import csv, io, json

def flatten(obj, prefix=""):
    out = {}
    for k, v in obj.items():
        key = f"{prefix}.{k}" if prefix else k
        if isinstance(v, dict):
            out.update(flatten(v, key))
        else:
            out[key] = v
    return out

def cell(v):
    if v is None:
        return ""
    if isinstance(v, bool):
        return "true" if v else "false"
    if isinstance(v, (dict, list)):
        return json.dumps(v, ensure_ascii=False, separators=(",", ":"))
    return str(v)

data = json.load(open("data.json", encoding="utf-8"))
if isinstance(data, dict):
    only = next(iter(data.values())) if len(data) == 1 else None
    data = only if isinstance(only, list) else [data]

rows = []
for r in data:
    rows.append(flatten(r) if isinstance(r, dict) else {"_value": r})

cols = []
for r in rows:
    for k in r:
        if k not in cols:
            cols.append(k)

with open("out.csv", "w", newline="", encoding="utf-8") as fh:
    w = csv.writer(fh, lineterminator="\r\n", quoting=csv.QUOTE_MINIMAL)
    w.writerow(cols)
    for r in rows:
        w.writerow([cell(r.get(c)) for c in cols])

Three details in there are load-bearing, and each one is a shape that otherwise diverges. lineterminator="\r\n" matches the CRLF a browser download produces. The isinstance(v, bool) branch has to come before any numeric handling, because in Python True is an int. And _value is how a bare scalar line — a JSON Lines file whose rows are just 1, 2, 3 — gets a column instead of raising 'int' object is not iterable.

One thing this recipe does not reproduce, and cannot: the browser converter writes no trailing newline, while every Python path above ends the file with \r\n. That is the reason a diff between a downloaded CSV and a script-generated one never comes back clean, even when every other byte matches.

What actually comes out: 27 shapes, four methods, measured

Each cell below is a byte-for-byte comparison against the output of the converter on this site, for the same input, with the trailing newline ignored (it differs everywhere, for the reason above). "Match" means the two files are identical apart from that final \r\n. Measured 2026-09-28 on Python 3.13 with pandas 3.0.

Two of the 27 shapes are rejected by every method, including this site's converter, and they are rejected for the same reason: 23-trailing-comma is not valid JSON (a trailing comma before ] is a JavaScript habit, not a JSON feature), and 24-empty-array has no rows to write. The third shape nobody matches is 21-jsonlines, where every Python path fails on json.loads until you read it line by line.

The four shapes where your data changes

The four cases below are not formatting differences. In each one, a value that went into the pipeline comes out different, and nothing warns you.

1. A missing key turns the column into floats

Input [{"a":1,"b":2},{"b":3,"c":4}]. pandas has to put NaN in the gaps, so the column becomes float64, and the integers are promoted:

a,b,c
1.0,2,
,3,4.0

The browser converter writes 1,2 and ,3,4. If those columns are order numbers, customer ids or invoice references, 1.0 is a downstream bug that will be found by somebody else. Force dtype=object, or use the stdlib recipe.

2. Leading zeros disappear

Input [{"zip":"02116"}]. The value is a JSON string, and pandas still infers the column as numeric:

zip
2116

ZIP codes, phone numbers, SKUs and zero-padded account numbers all lose their padding. The stdlib recipe keeps them, because csv.writer never guesses a type. The same thing happens to "007", and to any column where the values happen to look numeric.

3. Booleans come out capitalised

Input [{"id":1,"active":true}]. Python's str(True) is True; JSON's is true. pandas writes the first one:

id,active
1,True

The browser converter writes 1,true instead. Any consumer doing a case-sensitive comparison against "true" will read every row as false.

4. Big integers: Python is right and the browser is wrong

This is the one case that runs the other way, and it is worth knowing before you trust either tool. Input [{"order_id":9007199254740993}]:

# Python
9007199254740993

# browser converter
9007199254740992

2^53 is the largest integer JavaScript can hold exactly. A JSON number above it is already lossy the moment the browser parses the document, before any CSV exists — so this is a browser limitation, not a converter bug, and the fix belongs in the source data. The same happens with a 19-digit id: Python prints 12345678901234567890, the browser prints 12345678901234567000. If your identifiers are snowflake ids or database bigints, do the conversion in Python, or quote them as strings in the JSON.

A 60-second check before you trust the file

1. Count the rows. sum(1 for _ in open("out.csv")) - 1 should equal the number of objects. If it is short, your input was a single object or a wrapper object and got read as one row.
2. Diff the header against the keys you expect. Union order is not standardised, so a column can be present but in a position your downstream code does not expect.
3. Look for 1.0 in a column that should hold integers. That is the missing-key float promotion, and it is the failure that survives longest.
4. Check the first three characters of the file for a BOM. A UTF-8 BOM makes Excel open the file correctly and makes jq, psql and csv.reader treat the first column name as \ufeffid. Every method on this page writes without a BOM, which is the right default for code and the wrong-looking one for a locale-guessing Excel — fix that on the import side, not by changing the file.
5. Open it in a text editor, not a spreadsheet. Excel will silently reinterpret a long digit string, and you will blame the converter.

When pandas is the wrong tool

pandas is the right answer when the JSON is large, flat-ish, and destined for analysis. It is the wrong answer in three cases:

• You are generating a fixture or a golden file. pandas makes no promise about byte output, and it will change between versions. Use the stdlib recipe and pin your expectations to bytes.
• Your identifiers are large integers or zero-padded strings. Both are type-inference failures, and both are silent.
• You need to hand someone a file they will open in Excel. pandas is fine for the writing; the problem is that the recipient's Excel will re-parse the columns on open. Give them a CSV plus a note, or an .xlsx with explicit column types.

If you just need the table once, without installing anything, the browser converter does the same mapping with the same flattening rules, and the file never leaves your machine.

Questions people ask next