Reading JSON Lines With pandas Without the Usual Surprises

pandas reads JSON Lines in one call, and for a first look at a file that is usually all you need. The call hides decisions that matter once the file is large or the records are not uniform: what happens to nested objects, how types are chosen, how much memory the load takes, and what a bad line does. I ran the same 200,000 event file through each variant, and the results below show where the default is fine and where to reach for an option.

The basic call

import pandas as pd

df = pd.read_json("events.ndjson", lines=True)

lines=True tells pandas the file is one JSON value per line. Without it pandas expects a single document and raises ValueError: Trailing data at position 168. The load took 0.68 s and the DataFrame had the shape (200000, 7). The inferred types were:

id          int64
ts            str
user       object
event         str
amount    float64
tags       object
props      object

Four observations. id is an integer. amount is a float because null became NaN; 150,000 of the 200,000 values were missing, as expected. event and ts are strings (pandas 3 has a dedicated string dtype, where earlier versions show object). And user, tags and props are object columns holding Python values: the first user is the dict {'id': 1, 'plan': 'pro'} and tags is a list. The timestamp column stayed a string, so convert it yourself with pd.to_datetime(df["ts"], utc=True). Relying on pandas to guess date columns is unreliable, since it only converts columns whose names look like dates, and a keyword for it, convert_dates, is deprecated in pandas 3.

The process peaked at 580 MB while the finished DataFrame took 102 MB (memory_usage(deep=True)). The reader holds the text, the parsed objects and the frame at the same time, so plan for memory of about fifteen times the file size at load.

Reading in chunks

For files that do not fit, chunksize turns the call into an iterator of DataFrames:

total = 0.0
for chunk in pd.read_json("events.ndjson", lines=True, chunksize=20000):
    total += chunk["amount"].sum()

This computed 2,494,844.56 in 0.64 s with a peak of 176 MB, the same sum as DuckDB gets on the file, in less than a third of the memory. Each chunk is a separate DataFrame with its own index starting at the line offset, and its types are inferred per chunk, so a column that is all null in one chunk comes out as object and in another as float. If you concatenate the chunks at the end you are back to the full memory cost, so the pattern fits reductions: sums, filters that keep a fraction, aggregations that you append to a result frame. Setting dtype explicitly makes chunks consistent.

For a bounded sample, nrows=1000 reads the first thousand lines only. It is the fastest way to see what a file contains before choosing options, and I used it to check the date behavior below.

Nested fields

The nested objects stay as dicts, which is rarely what you want to analyze. Two ways to flatten them:

# 1. Normalize the already loaded column and join it back
df = pd.read_json("events.ndjson", lines=True)
df = df.join(pd.json_normalize(df["user"]).add_prefix("user_")).drop(columns="user")

# 2. Parse in Python and normalize everything
rows = [json.loads(line) for line in open("events.ndjson")]
flat = pd.json_normalize(rows)

The first took 1.02 s in total and produced columns user_id and user_plan; props stayed a dict because I only expanded user. The second took 1.71 s, expanded every level using dots in the names (user.id, props.page, props.ms, props.experiment), and gave a frame of 200,000 by 10 columns. Two cautions with the normalized frame. props.experiment was present in 20,000 records, so it is NaN for the other 180,000, and the dtype is a string column with missing values. And props.ms stayed object, because 200 records hold the string "slow" among numbers. That mixed column will break df["props.ms"].mean(), so convert it with pd.to_numeric(..., errors="coerce") and look at what turned into NaN. The broader topic is in flattening nested JSON with pandas and exploding a JSON array into one row per item.

If speed matters more than convenience, build the DataFrame from decoded records directly. pd.DataFrame([orjson.loads(l) for l in open("events.ndjson", "rb")]) took 0.54 s, a little faster than read_json, and gives the same nested object columns.

Big integers and exact types

Identifiers are the classic loss. I read two lines, {"id":12345678901234567890,"v":1.0} and {"id":2,"v":null}:

df = pd.read_json("bigint.ndjson", lines=True)
df.dtypes          # id uint64, v float64
df["id"].tolist()  # [12345678901234567890, 2]

pandas kept the 20 digit number exactly, because it fits in an unsigned 64 bit integer. Values above 18,446,744,073,709,551,615 do not fit, and identifiers that large are better read as strings: dtype={"id": str} returned ['12345678901234567890', '2']. The safe habit for ids of any size is to declare them as strings at read time. It also protects values like 00123, which a numeric type would turn into 123. (If the producer is JavaScript, the rounding happens before the data reaches pandas; see how JSON handles big numbers.)

The pyarrow engine is stricter

read_json(..., engine="pyarrow") is faster on big, regular files, and it is stricter. On the event file it stopped with:

pyarrow.lib.ArrowInvalid: JSON parse error: Column(/props/ms) changed from number to string in row 186

The default engine read the same file without complaint and left a mixed column. Arrow wants one type per column, so use it on data you know is uniform, or after fixing the offending field. The error is useful in its own right: it names the field and the first row where the type changed.

Bad lines

A bad line stops the load. With the 200,000 line file that has two damaged lines, pandas raised ValueError: Expected object or value at position 22444874, a character offset from the start of the file, not a line number. Find the line with a per-line parse first (finding one bad line in a huge JSON Lines file), or read leniently yourself:

good = []
for line in open("events_bad.ndjson"):
    try:
        good.append(json.loads(line))
    except json.JSONDecodeError:
        pass
df = pd.DataFrame(good)

Writing back is symmetrical: df.to_json(orient="records", lines=True). Be aware that it escapes the forward slash, so /p/1 is written as "\/p\/1". That is valid JSON and every parser reads it back, but it is not what json.dumps writes, so a byte comparison with other tools will differ. NaN is written as null.

Compressed files

read_json infers the codec from the extension: pd.read_json("events.ndjson.gz", lines=True) returned the same shape, (200000, 7). Zstd needs the zstandard package. Details of the codecs are in compressing JSON Lines for storage and reading them back.

When pandas is the wrong reader

The file is bigger than memory and you need a join or a group. Chunking gets awkward as soon as the result depends on all the data. DuckDB queries the same file in place, in 0.14 s for a count and a sum in my other test, and hands a DataFrame back with .df() (reading newline-delimited JSON in DuckDB).

The records have no common shape. A DataFrame wants columns. For heterogeneous documents, loop in Python or store them in a document column first.

You need typed validation. pandas infers types, it does not enforce them. Validate with a schema before analysis (validating JSON in Python with a schema).

The options are listed in the pandas reference for read_json and json_normalize. To inspect one record in readable form, use the JSON formatter.