Loading JSON Into a pandas DataFrame the Right Way

pandas.read_json is the obvious way to get JSON into a DataFrame, and it is the loader that does the most guessing. It guesses which strings are numbers, which are dates, and which columns to leave alone. Most of the time the guess is right, and the times it is wrong are quiet and expensive: a zip code loses its leading zero, a SKU becomes an integer, an identifier that was text is now a float. This article shows exactly what the defaults did to a small test record, how to take control of types, which layouts orient handles, how to read NDJSON in chunks, and what changed in pandas 3.

Pass text through StringIO

In pandas 3, read_json treats a string argument as a path or URL only. Older versions accepted a literal JSON string, with a deprecation warning. Now:

pd.read_json('[{"id": 1}]')
# FileNotFoundError: [Errno 2] No such file or directory: '[{"id": 1}]'
pd.read_json(io.StringIO('[{"id": 1}]'))      # works

Wrap JSON text in io.StringIO. For files, pass the path (pd.read_json("data.json")), and compressed files (.gz, .zst) are handled from the extension.

What the default inference does

I loaded two records and printed the dtypes:

id          int64
zip         int64            <- "01234" became 1234
sku         int64            <- "007" became 7
created_at  datetime64[us, UTC]
placed      str              <- "2026-10-26" stayed text
when        str
n           uint64           <- 12345678901234567890 exact
amt         float64          <- 19.9 and null
flag        bool
tags        object
o           object
nul         float64          <- all null

Three things deserve a closer look.

Strings that look like numbers become numbers. read_json tries to convert every column that could be numeric, and "01234" is, as far as int() is concerned. The leading zero is gone and cannot be recovered; "007" became 7. For zip codes, phone numbers, SKUs, account numbers and anything else where the text is the identifier, that is data corruption. It happens silently.

Dates are guessed from the column name. created_at was parsed as a UTC datetime64 because the name ends in _at; placed and when were left as text. By default pandas converts columns whose names look like dates (_at, _time, date, modified, timestamp), which can surprise in both directions. Parse dates yourself, in a step you can see: pd.to_datetime(df["placed"], format="%Y-%m-%d") and format="%d/%m/%Y" for the day-first column.

Missing data forces floats. amt is float64 because of the null, and the all-null column nul is float64 too. Integer columns with a null would become floats; use nullable types where you can.

Taking control

dtype=False turns off conversion for the data columns, and everything text stays text:

df = pd.read_json(io.StringIO(text), dtype=False)

After it, zip, sku, created_at, placed and when were all str (and id stayed int64, flag bool, since those are typed in the JSON itself). It is the safest default for data you do not control: convert deliberately afterwards. To fix particular columns, give a mapping:

pd.read_json(io.StringIO(text), dtype={"zip": "string", "sku": "string", "id": "int32"})

That kept 01234 and 007 as text and made id a 32-bit integer. A third route is the Arrow backend: dtype_backend="pyarrow" produced string[pyarrow] for zip, sku, placed and when, int64[pyarrow] for id, a timestamp[us, tz=UTC][pyarrow] for created_at, double[pyarrow] for amt and null[pyarrow] for the all-null column. No leading zeros were lost, and missing values stay missing without turning integers into floats. For new code on pandas 2 and later, the Arrow backend is the cleaner choice.

The convert_dates keyword is deprecated in pandas 3.1 (a Pandas4Warning suggests passing dtype=False and calling pd.to_datetime yourself), which is the direction to follow anyway. Use precise_float=True if you need accurate parsing of floats with many digits.

Layouts: orient

orient says how the JSON is laid out. I wrote a two-row frame in each layout and read it back:

records   [{"a":1,"b":"x"},{"a":2,"b":"y"}]                         list of row objects (the common API shape)
split     {"columns":["a","b"],"index":[0,1],"data":[[1,"x"],[2,"y"]]}
index     {"0":{"a":1,"b":"x"},"1":{"a":2,"b":"y"}}                 dict of rows keyed by index
columns   {"a":{"0":1,"1":2},"b":{"0":"x","1":"y"}}                 dict of columns (the default for objects)
values    [[1,"x"],[2,"y"]]                                         no labels at all
table     {"schema": {...}, "data": [...]}                          with a Table Schema describing types

All six round-tripped to the same data. For JSON that arrives from an API, it is nearly always records, which read_json infers for a top-level array of objects. table is useful for exchange because the schema carries the types, so the reader does not guess. The to_json method of a DataFrame writes any of them: df.to_json(orient="records").

A flat object with scalar values, like {"a": 1, "b": 2}, is not a table: pd.read_json(io.StringIO('{"a": 1, "b": 2}')) raised ValueError: If using all scalar values, you must pass an index. Read it as a Series with typ="series" (it gave {'a': 1, 'b': 2}), or wrap it in a list.

NDJSON and large files

For newline-delimited JSON use lines=True:

df = pd.read_json("events.ndjson", lines=True)          # 200,000 rows, 1.1 s

The 36 MB file loaded into 7 columns in about a second. The nested objects user and props were left as dict objects in object columns, because read_json does not flatten; use pd.json_normalize for that (flattening nested JSON with pandas json_normalize), or pull fields out of the dict column with .str accessors or a comprehension. ts came back as a string in the default pandas backend and as a datetime64[s] with the pyarrow engine.

With chunksize, lines=True returns an iterator and memory stays bounded:

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

It computed 2,494,844.56 in 1.4 s. The chunk loop is the pattern for files larger than memory, with each chunk a DataFrame you aggregate and discard.

engine="pyarrow" is faster on clean data and strict on dirty data. On the full events file it raised ArrowInvalid: JSON parse error: Column(/props/ms) changed from number to string in row 999, because the field props.ms is a number except for 200 strings. The default engine took it in stride (as an object column). Strictness is good when you want to know about the drift, and a reason to have a fallback.

When the DataFrame is the wrong destination

DuckDB reads the same file and aggregates it in about a tenth of a second without holding Python objects (querying JSON files with DuckDB), and can hand the result to pandas with .df(). For records with irregular nesting, load into json_normalize or keep the JSON in a column and extract with SQL. For validated, typed input use pydantic models and build the frame from them (parsing JSON into typed objects with pydantic). A look at the raw shape first is easy in the JSON formatter.

When read_json is not the right loader

Identifiers are strings. Use dtype=False or explicit dtypes, or the Arrow backend, every time.

The JSON is deeply nested or ragged. read_json leaves dicts in cells; json_normalize or SQL flattens.

The file is large. Chunk it, or use a tool that streams; the 859 MB memory figure for the same data in plain Python applies here too (handling large JSON files in Python with ijson).

Types matter downstream. Write the dtypes down in code, not in the data.

You need exact decimals. read_json gives floats; parse with parse_float=Decimal through the json module and build the frame from that.

The parameters are documented in the pandas read_json reference and the IO tools guide.