Converting JSON to CSV With Python and pandas Safely

CSV is where JSON goes to be opened in a spreadsheet, loaded in bulk or handed to someone who does not read braces. The conversion looks like one line of pandas, and for flat records it is. For real documents it has traps at every cell: nested values come out as Python code instead of JSON, booleans change spelling, empty strings and nulls merge, and a cell that starts with an equals sign becomes a formula in the next person's Excel. This article converts the order data and a handful of deliberately awkward records, shows what each tool writes, and ends with the checks that tell you the file means what the JSON meant.

The short version

For records that are flat or flattenable:

import json
import pandas as pd

orders = json.load(open("orders.json"))
df = pd.json_normalize(orders, sep="_")
df.to_csv("orders_flat.csv", index=False)

json_normalize turns nested objects into columns (customer_address_city) and to_csv(index=False) writes them without the row numbers pandas would otherwise add as a first column. For 20,000 orders this took 0.20 s. If the interesting part is an array, flatten that instead, one row per item:

items = pd.json_normalize(orders, record_path="items", meta=["id"], meta_prefix="order_")
items.to_csv("items.csv", index=False)       # 50,000 rows: sku, qty, price, order_id

Choosing which of the two to write is a modeling decision, covered in turning nested JSON into flat tables. A document with an array usually becomes two CSV files, not one.

What pandas writes for values that are not scalars

My first conversion of the full orders looked fine in the first column and wrong in the items column:

id,placed,paid,items,customer_id,...
1,2026-02-02,False,"[{'sku': 'S2', 'qty': 3, 'price': 0.01}, {'sku': 'S3', 'qty': 4, 'price': 0.02}]",1,...

The cell is the Python repr of a list of dicts: single quotes, False with a capital. It is not JSON, and no JSON parser reads it. It happens because to_csv calls str() on any object in a cell. If a nested value has to travel inside a CSV, convert it first:

df["items"] = df["items"].map(json.dumps)

The cell then holds [{"sku": "S2", "qty": 3, "price": 0.01}, ...], valid JSON that the CSV quoting protects (the double quotes inside are doubled). The reader on the other side can parse the column with json.loads, or a database can cast it to jsonb (a jsonb column is the usual home for it). The same applies to every object-typed column, whether it holds lists (tags) or dicts (props). Test with a two record file to make sure you did not miss one: after df.to_csv, grep the output for [ and {.

Booleans, floats and nulls

The conversion also changes the spelling of simple values. In a record file with true, false, null, 0.1 + 0.2 and 1e21, pandas wrote:

b      -> True / False
n      -> (empty)
f      -> 0.30000000000000004 / 1e+21

True and False are Python's names. Excel and most databases accept them, but a strict loader that expects true or 1 may not, so decide on a convention. DuckDB wrote false and true in lowercase in its COPY. Floats keep Python's shortest exact representation, which for 0.1 + 0.2 is the unrounded 0.30000000000000004; if the number is money, round it first or format as a decimal string. Very large floats switch to exponent notation (1e+21), which spreadsheets read fine and some importers do not.

Null becomes an empty field, and so does an empty string. When I wrote {"note": ""} and {"note": null} through pandas, both came out as nothing between the commas, and pd.read_csv returned NaN for the empty string on the way back. If the difference matters, pass na_rep to write something explicit for null (na_rep="NULL"), and make sure the reader treats that token the same way. Booleans, nulls and numbers all lose their JSON types in a CSV, since everything is text. A typed target (a database column, a Parquet file) is better served by going straight from the JSON, not through CSV (converting between JSON and Parquet).

Quoting and awkward text

The quoting itself was right. A name with a comma and quotes, Smith, "Bob", and a note with a line break were written as proper RFC 4180 fields:

1,"Smith, ""Bob""","line1
line2",12345678901234567890,0.30000000000000004,True,,café,...

The line break lives inside the quoted field, so the file has more physical lines than records, which breaks tools that count with wc -l (what RFC 4180 says about CSV). Reading it back with pandas restored everything: the multi-line string intact, the 20 digit integer as uint64 12345678901234567890, and the accented and Japanese text unchanged. The id survived because pandas typed the column as an unsigned 64-bit integer; with a 25 digit id it would become a float or a string, so write long identifiers as quoted text in the first place.

Encoding is where Excel differs. pandas writes UTF-8 without a byte order mark by default. Excel on Windows opens such a file as the local code page unless it sees a BOM, and café and 日本 come out garbled. df.to_csv(path, index=False, encoding="utf-8-sig") writes the BOM (EF BB BF, which I confirmed at the start of the file). Do that for files meant for Excel users and nowhere else, because other tools treat the BOM as part of the first column name (fixing garbled text in CSV files goes through the encodings).

Cells that become formulas

A spreadsheet treats a cell that starts with =, +, - or @ as a formula. JSON data from users can contain such strings. I wrote four values, =1+1, +cmd, @SUM(A1) and -2, and pandas wrote them unchanged. Opened in Excel, the first becomes a computed cell and, with a malicious payload, can run a command or send data out. This is called CSV injection. If the file will be opened in a spreadsheet, neutralize the values that start with one of the four characters by prefixing a single quote ('=1+1) or a space, in a function applied to every string column. It changes the data, so keep the unmodified JSON, and do this only on the export meant for people (converting JSON to an Excel workbook has the same issue and an option to prevent it).

Records with different keys

If the records do not share keys, pd.json_normalize builds the union and leaves NaN, which to_csv writes as empty fields. Without pandas, the standard csv module needs the columns declared. A union of keys in first-seen order does it:

import csv
rows = [{"a": 1}, {"b": 2}, {"a": 3, "c": 4}]
keys = list(dict.fromkeys(k for r in rows for k in r))
w = csv.DictWriter(open("out.csv", "w", newline=""), fieldnames=keys)
w.writeheader(); w.writerows(rows)

Without the union, DictWriter stops at the first record with an unknown key: ValueError: dict contains fields not in fieldnames: 'b'. Pass restval="" for missing keys (the default) and extrasaction="ignore" if you want to drop unknown ones, though silently dropping data is rarely right. The census in handling JSON records with different keys shows how to decide which keys deserve a column.

The same conversion with jq, DuckDB and Miller

When Python is not around, the other tools do it too. In jq the CSV filter wants an array of scalars:

jq -r '(["order","sku","qty","price"] | @csv), (.[] | .id as $o | .items[] | [$o, .sku, .qty, .price] | @csv)' orders.json

That wrote the header and 50,000 rows in 0.21 s. A row that contains an array stops it: jq: error: array (["a","b"]) is not valid in a csv row, so wrap nested values in tojson, which yields a string that @csv then quotes ("[""a"",""b""]"). @tsv escapes tabs and newlines as the two characters \t and \n instead of quoting, which keeps the file one record per line.

DuckDB is the shortest for typed output:

COPY (SELECT id AS order_id, i.* FROM (SELECT id, unnest(items) AS i FROM read_json('orders.json')))
TO 'items.csv' (HEADER);

It wrote 50,000 data rows in 0.15 s, the same row count as the pandas file. Miller (mlr --ijson --ocsv flatten) flattens nested keys with dots (customer.address.city) and wrote the same columns. The row counts agreeing across the four tools is the best check that the conversion is right (converting JSON arrays to CSV with jq has the jq patterns in more depth).

Check the file against the JSON

Three checks, in order of cost. Count: the CSV has as many data rows as the JSON has records (or array elements for the exploded file), allowing for line breaks inside quoted fields. Read it back with a CSV parser and compare a total, such as the sum of qty * price, with the one computed from the JSON; both gave 6,375,490 for the orders. And open the first few rows in a spreadsheet, once, to see what your recipients will see.

When CSV is the wrong target

The data is nested and stays nested. If the next system reads JSON, converting to CSV flattens, then something has to rebuild the tree. Send JSON Lines.

Types matter. CSV carries no types. For a typed destination use Parquet or a database load.

Cells are large or full of text. Free text with newlines and quotes works and is fragile in every tool downstream. Consider a format with proper escaping, like JSON Lines itself (JSON Lines versus CSV for event data compares them).

The pandas options are listed in the reference for DataFrame.to_csv, the format is RFC 4180, and the injection risk is described on the OWASP page for CSV injection. To preview what a record looks like before converting, use the JSON formatter.