Skip to content
Bamboo Weekly

pandas read_json

Read a rectangular JSON file — a list of objects, one per row — into a data frame.

Your data arrived as JSON. Is that a one-line read, or an afternoon of unpacking? It depends on the shape of the file, not its size.

read_json wants a rectangle: a list of objects that all carry the same keys, whose values are scalars. Give it that, and there is nothing to configure. Give it anything else — an API envelope with the records buried inside, a record with a dictionary in one of its fields, a GeoJSON feature collection — and it will either raise an error you have to decode or hand you a data frame full of dictionaries.

That boundary is the whole story of this function, and choosing the wrong side of it is the mistake I see most often. Flat JSON goes to read_json; nested JSON goes to json_normalize.

Official documentation: pandas.read_json

The arguments that earn their keep

pd.read_json(source,
             orient='records',                # how the JSON is laid out
             lines=True,                      # one JSON object per line
             convert_dates=['created_date'],  # columns to parse as datetimes
             dtype_backend='pyarrow',         # Arrow dtypes rather than NumPy
             nrows=5000)                      # stop early; needs lines=True

As with read_csv, source can be a path or a URL, so the example below downloads nothing first.

A worked example, on real data

New York City publishes its 311 complaints through an API that returns precisely the shape read_json is built for — a list of flat objects:

import pandas as pd

url = ('https://data.cityofnewyork.us/resource/erm2-nwe9.json'
       '?$select=created_date,agency,complaint_type,borough'
       '&$order=created_date&$limit=5000')

df = pd.read_json(url)
df.dtypes

created_date      str
agency            str
complaint_type    str
borough           str
dtype: object

Five thousand rows, four columns, no arguments. But look at created_date: it came back as text. read_json does parse dates on its own, and this is where people get caught, because it recognizes a column as date-like only if the name is date, is modified, begins with timestamp, or ends in _at or _time. A column called created_date matches none of those. Name it yourself:

df = pd.read_json(url, convert_dates=['created_date'])
df.dtypes

created_date      datetime64[us]
agency                       str
complaint_type               str
borough                      str
dtype: object

df['complaint_type'].value_counts().head()

complaint_type
Noise - Residential                    1163
HEAT/HOT WATER                          690
Illegal Parking                         426
Request Large Bulky Item Collection     408
Blocked Driveway                        400
Name: count, dtype: int64

orient=, and the silent transpose

JSON has no single convention for storing a table, so Pandas supports five, and orient= says which one you have:

df = pd.DataFrame({'agency': ['DOT', 'NYPD'], 'n': [12, 30]}, index=['a', 'b'])

for style in ['records', 'index', 'columns', 'split', 'values']:
    print(style, '->', df.to_json(orient=style))

records -> [{"agency":"DOT","n":12},{"agency":"NYPD","n":30}]
index -> {"a":{"agency":"DOT","n":12},"b":{"agency":"NYPD","n":30}}
columns -> {"agency":{"a":"DOT","b":"NYPD"},"n":{"a":12,"b":30}}
split -> {"columns":["agency","n"],"index":["a","b"],"data":[["DOT",12],["NYPD",30]]}
values -> [["DOT",12],["NYPD",30]]

The default for a data frame is orient='columns', and columns and index look enough alike that Pandas cannot tell them apart. Read an index file with the default and nothing complains — you get your data frame on its side:

from io import StringIO

j = df.to_json(orient='index')
pd.read_json(StringIO(j))

          a     b
agency  DOT  NYPD
n        12    30

Rows became columns, quietly. If you did not write the file, check orient= against its first few characters before you trust the frame.

lines=True, for JSONL

One JSON object per line, no enclosing brackets, no commas between records — that is JSONL, what logs, exports, and streaming APIs produce. Pandas reads it only when you say so:

df.to_json('complaints.jsonl', orient='records', lines=True)

pd.read_json('complaints.jsonl')

ValueError: Trailing data

pd.read_json('complaints.jsonl', lines=True).shape

(5000, 4)

"Trailing data" means the parser read one complete object and then found more file. It always means lines=True.

Where read_json stops

The USGS earthquake feed is GeoJSON: a dictionary whose features key holds the records, each with nested properties and geometry.

url = 'https://earthquake.usgs.gov/earthquakes/feed/v1.0/summary/2.5_month.geojson'
pd.read_json(url)

ValueError: All arrays must be of the same length

That error is not about your data. It is read_json trying to treat four top-level keys of different lengths as four columns. Load the JSON yourself, pick the list out of it, and flatten:

import json, urllib.request

with urllib.request.urlopen(url) as f:
    data = json.load(f)

quakes = pd.json_normalize(data['features'])
quakes.shape, list(quakes.columns)[:5]

((2241, 30), ['type', 'id', 'properties.mag', 'properties.place', 'properties.time'])

Thirty flat columns with dotted names, out of a structure read_json could not touch. That is the hand-off, and it is worth learning as a reflex: if the JSON has a dictionary or a list inside a record, you want json_normalize. The feed covers a rolling month, so your row count will differ from mine.

Four mistakes people make

Passing a JSON string instead of a filename. In Pandas 3 this no longer works, and the error is baffling, because Pandas reads your data back to you as a path: FileNotFoundError: File {"a":{"agency":"DOT","n":12}} does not exist. Wrap the string in io.StringIO, as above.

Reading JSONL without lines=True. See "Trailing data" above. The mirror image bites too: pass lines=True to an ordinary JSON array and you get ValueError: Expected object or value, because the first line is only [.

Assuming your dates were parsed. The default heuristic covers a handful of column names and nothing else. Check .dtypes, and pass convert_dates=.

Looking for index_col. read_json does not have one, unlike every other read_* function. Call .set_index() after the fact.

Where it shows up in Bamboo Weekly

Four solutions use read_json, and between them they cover the range.

Bamboo Weekly #61: Solar eclipse is the easy case in one line: pd.read_json(filename) on a flat file of cities in the path of totality.

Bamboo Weekly #48: Aviation accidents reads five NTSB files at once, with a list comprehension over glob.glob feeding pd.concat, and passes convert_dates=['cm_eventDate']. It is also where I noticed out loud that read_json has no index_col.

Bamboo Weekly #120: Pennies is the hand-off in a single expression: read_json loads the file, and pd.json_normalize(df['issuer']) unpacks the one column that came back holding dictionaries.

Bamboo Weekly #160: Strait of Hormuz takes the other route out of nesting: the EIA's JSON keeps its records inside one field, so the chain begins pd.read_json(filename).explode('data').

Practice it

Work through a read_json() exercise, with instant feedback and no signup required: practice.lernerpython.com/bamboo-weekly/read-json/

Go deeper

json_normalize is the page to read next, because half of the JSON you meet will need it. read_csv covers the same job when the data arrives as text, and the Pandas user guide's IO chapter has the full table of orient= behaviors.

Bamboo Weekly is the practice. If you want the structured version — full courses with downloadable Jupyter notebooks, plus live Pandas office hours when you get stuck — that is LernerPython+Data. A paid Bamboo Weekly subscription is included with it.

See it on real data

Below are the 4 Bamboo Weekly exercises that use read_json on real-world data — try each one, then study the worked solution.

Part of the Pandas Methods Index. See also practice by skill.