One call from raw JSON to a working DataFrame — if you survive the dtype inference.
Your API returns perfect JSON, pandas returns an int where your zip code used to be — read_json() is the handshake, and it has opinions.
pd.read_json() parses JSON into a Series or DataFrame in a single call. It reads a path, URL, file-like object, or a raw JSON string, auto-detects gzip/bzip2/zstd compression, and understands six layouts (orients): split, records, index, columns, values, table. With lines=True it streams line-delimited JSON (JSONL) record by record — the format every structured-log pipeline ships — and chunksize turns that stream into an iterator of DataFrames so a 40 GB log never has to fit in RAM. It is the reader half of the to_json/read_json pair.
JSON is how machines talk: REST payloads, log lines, cloud exports, config dumps. Where json.load() gives you lists and dicts you then reshape by hand, read_json() hands you a typed DataFrame with an index, ready for filtering, grouping, and joins. The catch is inference: by default it converts numeric-looking strings to numbers and axes to their 'proper' dtypes, so zip codes lose their leading zero and string keys silently become an Int64Index. Reading the docstring once — the parameters exist precisely because of these traps — is cheaper than debugging them in production.
import pandas as pd, io
# a REST endpoint returned records JSON
payload = '''
[{"city":"Vienna","orders":182,"revenue":5390},
{"city":"Graz","orders":97,"revenue":2810},
{"city":"Linz","orders":64,"revenue":1755},
{"city":"Salzburg","orders":51,"revenue":1980}]
'''
df = pd.read_json(io.StringIO(payload))
print(df)
print(df.dtypes)city orders revenue 0 Vienna 182 5390 1 Graz 97 2810 2 Linz 64 1755 3 Salzburg 51 1980 city str orders int64 revenue int64 dtype: object
The default layout is orient='records': one JSON object per row, keys become columns. Note city comes back as pandas' modern str dtype — this is pandas 3.0.
# the roundtrip worth memorizing: to_json / read_json with orient='split'
df = pd.DataFrame({"city": ["Vienna", "Graz", "Linz"],
"orders": [182, 97, 64]})
s = df.to_json(orient="split")
print(s)
back = pd.read_json(io.StringIO(s), orient="split")
print(back)
print(back.dtypes){"columns":["city","orders"],"index":[0,1,2],"data":[["Vienna",182],["Graz",97],["Linz",64]]}
city orders
0 Vienna 182
1 Graz 97
2 Linz 64
city str
orders int64
dtype: object
orient='split' serializes dtypes, columns and index explicitly, so the roundtrip is faithful. For archives that must reload identically, this is the layout I pick.
# a sensor log in JSONL (one JSON object per line)
jsonl = io.StringIO(
'{"sensor":"A1","temp":21.5,"ok":true}\n'
'{"sensor":"A2","temp":23.1,"ok":true}\n'
'{"sensor":"B1","temp":19.8,"ok":false}\n')
df = pd.read_json(jsonl, lines=True)
print(df)sensor temp ok 0 A1 21.5 True 1 A2 23.1 True 2 B1 19.8 False
lines=True is the flag for log pipelines: filebeat, vector, Kafka sinks, newline-delimited dumps. Without it, pandas tries to parse the whole file as one document.
# the classic trap: numeric-looking strings get converted
zip_json = '''
[{"city":"Vienna","zip":"1010"},
{"city":"Graz","zip":"8010"}]
'''
dfz = pd.read_json(io.StringIO(zip_json))
print(dfz["zip"].dtype, list(dfz["zip"]))
fixed = pd.read_json(io.StringIO(zip_json), dtype={"zip": str})
print(fixed["zip"].dtype, list(fixed["zip"]))int64 [1010, 8010] str ['1010', '8010']
Leading zeros are gone in the first line — '01234' would even round-trip as 1234. Pin dtypes at load time with dtype={'col': str}; don't try to repair them afterwards.
# keys as an index: typ='series' on a flat object
flat = '{"1010": 812, "8010": 431, "5020": 96}'
s = pd.read_json(io.StringIO(flat), typ="series")
print(s.index.tolist(), s.tolist())
s2 = pd.read_json(io.StringIO(flat), typ="series", convert_axes=False)
print(s2.index.tolist(), s2.tolist())[1010, 8010, 5020] [812, 431, 96] ['1010', '8010', '5020'] [812, 431, 96]
By default the axes are converted too — string keys became int64. convert_axes=False keeps them as strings, which is what a zip-code index actually is.
# stream a big JSONL file in chunks and process per chunk
buf = io.StringIO()
for i in range(1, 8):
buf.write('{"run":%d,"ms":%.1f}\n' % (i, 100 + i * 0.7))
buf.seek(0)
sizes = [len(ch) for ch in pd.read_json(buf, lines=True, chunksize=3)]
print(sizes)[3, 3, 1]
With lines=True, chunksize returns a JsonReader iterator — each step is a small DataFrame, so memory stays flat no matter how long the log is. Last chunk may be short.
# read_json opens .gz transparently — no gzip module needed
df = pd.DataFrame({"city": ["Vienna", "Graz"], "orders": [182, 97]})
with gzip.open("/tmp/orders.json.gz", "wb") as f:
f.write(df.to_json(orient="records").encode())
back = pd.read_json("/tmp/orders.json.gz")
print(back)city orders 0 Vienna 182 1 Graz 97
Compression is inferred from the suffix (.gz, .bz2, .zip, .zst, .tar); pass compression= explicitly for exotic extensions. I verified this roundtrip end to end.
# the date heuristics: anything ending in _at becomes datetime
payload = '''
[{"event":"deploy","at":1757880000000,"checked_at":1757883600000},
{"event":"rollback","at":1757966400000,"checked_at":1757970000000}]
'''
df = pd.read_json(io.StringIO(payload))
print(df["at"].dtype, df["checked_at"].dtype)int64 datetime64[ms]
checked_at matched the '_at' suffix heuristic and was converted; plain at was not. keep_default_dates=False plus convert_dates=['at'] makes this explicit — always pin dates in production.
| Flag | Meaning |
|---|---|
orient | Expected JSON layout: split | records | index | columns | values | table — must match how the JSON was written |
lines=True | Parse line-delimited JSON (JSONL) — one object per line, the standard log format |
chunksize=n | With lines=True, return a JsonReader iterator yielding n rows at a time |
dtype={'col': str} | Pin per-column dtypes at load time — the fix for zip codes and IDs mangled by inference |
convert_axes=False | Stop pandas from converting index/column labels to 'proper' dtypes (string keys stay strings) |
convert_dates / keep_default_dates | Control epoch→datetime conversion; keep_default_dates auto-converts columns ending in _at, _time, etc. |
compression | 'infer' by default — .gz/.bz2/.zip/.zst files are decompressed on the fly, no extra module |
pandas 0.12 added a whole module for JSON IO — pandas.io.json — with pd.read_json() for reading and df.to_json() for writing, part of the push that rounded out the read_* family (read_csv, read_excel, read_hdf, read_sql, read_html, read_stata). Before that release there was no JSON story at all.
For years read_json only spoke whole-document JSON. pandas 0.19 (October 2016) added lines=True for line-delimited JSON — the format log shippers standardized on — and later releases extended it with chunksize and file URLs. The lesson: the parameter arrived because JSON moved from config files to log streams, and the API followed.
read_json's default engine is ujson — the C-accelerated parser from the ultrajson project, once vendored into pandas itself. Parsing is only half the work though: after the C pass, pandas walks the raw values and infers dtypes column by column (int -> float on NaN, str on anything else), and separately decides whether to convert the axes. orient='split' is the cheapest to load because the layout is already tabular — records has to be reshaped from dicts into columns, which is more allocation. If you truly need bit-exact decimals, precise_float=True trades speed for full float64 precision — at scale, prefer reading with keep_default_dates=False and converting dates yourself.