Turn nested JSON into a flat, rectangular data frame.
What it does
What do you do when the data you asked for arrives as JSON, and the JSON looks nothing like a table? An API hands you a list of records, inside each record is another dict, and inside that is a list of yet more dicts. Pandas data frames are two-dimensional. JSON is a tree. Something has to give.
json_normalize is the tool for flattening that tree. Hand it a list of dicts and it returns a data frame with one row per dict, walking down through the nested dicts and giving each leaf a column named for the path that got you there: id.bioguide, name.last, bio.birthday. Hand it a dict with a list buried inside, tell it where that list lives, and it gives you one row per element of the list.
Two things surprise people. The first is that it is pd.json_normalize, a top-level function, not a method: there is no df.json_normalize(). You call it on Python data — a dict, a list of dicts, or a series of dicts — and a new data frame comes back. The second is that it has nothing to do with reading files. read_json gets the bytes; json_normalize reshapes what is already in memory, usually the result of calling .json() on a requests response.
Official documentation: pandas.json_normalize
The arguments that earn their keep
pd.json_normalize(data) # flatten every nested dict
pd.json_normalize(data, record_path='terms') # one row per list element
pd.json_normalize(data, record_path='terms',
meta=[['id', 'bioguide']]) # carry parent fields down
pd.json_normalize(data, sep='_') # id_bioguide, not id.bioguide
pd.json_normalize(data, max_level=1) # stop descending after level 1
pd.json_normalize(data, record_path='terms',
meta=['leadership_roles'],
errors='ignore') # NaN instead of KeyError
record_path is the one that changes the shape of the answer. Without it you get one row per record; with it, Pandas descends into the named list and gives you one row per element, so a legislator with six terms becomes six rows. Pass a list of strings — record_path=['result', 'orders'] — for a list several levels down.
meta is the companion to record_path, and you almost never want one without the other, because descending into a list throws away everything outside it. meta names the parent fields to copy onto each of the new rows. Each entry is either a string, for a top-level key, or a list of strings, for a path: ['name', 'last'] reaches into the name dict and pulls out last.
sep sets the character that joins path components; the default is a period. max_level stops the descent at a given depth, leaving anything deeper as a dict in the cell. And errors='ignore' turns a meta key that some records lack into NaN rather than an exception.
A worked example, on real data
The unitedstates/congress-legislators project publishes every current member of the US Congress as JSON, and it is the data behind Bamboo Weekly #76. Start by looking at what arrived:
import pandas as pd
import requests
url = 'https://unitedstates.github.io/congress-legislators/legislators-current.json'
data = requests.get(url).json()
type(data), len(data)
(<class 'list'>, 537)
A list of 537 dicts, one per legislator. And each of those dicts:
sorted(data[0].keys())
['bio', 'id', 'name', 'terms']
Four keys, and not one of them holds a plain value. id, name, and bio are dicts; terms is a list of dicts. Pass the whole thing to json_normalize and the three dicts come apart:
df = pd.json_normalize(data)
df.shape
(537, 27)
df[['id.bioguide', 'name.last', 'bio.birthday']].head()
id.bioguide name.last bio.birthday
0 C000127 Cantwell 1958-10-13
1 K000367 Klobuchar 1960-05-25
2 S000033 Sanders 1941-09-08
3 W000802 Whitehouse 1955-10-20
4 B001261 Barrasso 1952-07-21
Four keys became 27 columns, each named for the path that produced it. That is the whole idea, and for most API data it is where the story ends. But not here, because one column resisted:
df['terms'].head(3)
0 [{'type': 'rep', 'start': '1993-01-05', 'end':...
1 [{'type': 'sen', 'start': '2007-01-04', 'end':...
2 [{'type': 'rep', 'start': '1991-01-03', 'end':...
Name: terms, dtype: object
A list of dicts cannot become a set of columns, because different legislators served different numbers of terms. It has to become rows. That is what record_path is for:
terms = pd.json_normalize(data, record_path='terms')
terms.shape
(2785, 19)
537 legislators, 2,785 terms among them, one per row. Look closely, though, and something is missing:
terms[['type', 'state', 'party', 'start', 'end']].head(3)
type state party start end
0 rep WA Democrat 1993-01-05 1995-01-03
1 sen WA Democrat 2001-01-03 2007-01-03
2 sen WA Democrat 2007-01-04 2013-01-03
Whose terms are these? Pandas descended into the terms list and left the legislator behind; every column outside the list is gone. meta is the fix — it names the parent fields to carry down onto each new row:
terms = pd.json_normalize(data,
record_path='terms',
meta=[['id', 'bioguide'], ['name', 'last']])
terms[['id.bioguide', 'name.last', 'type', 'state', 'start']].head(7)
id.bioguide name.last type state start
0 C000127 Cantwell rep WA 1993-01-05
1 C000127 Cantwell sen WA 2001-01-03
2 C000127 Cantwell sen WA 2007-01-04
3 C000127 Cantwell sen WA 2013-01-03
4 C000127 Cantwell sen WA 2019-01-03
5 C000127 Cantwell sen WA 2025-01-03
6 K000367 Klobuchar sen MN 2007-01-04
Maria Cantwell's six terms, each labeled with her ID and her surname, the first one in the House and the rest in the Senate. Now the frame answers questions. How many current members of Congress have served in both chambers?
(
terms
.assign(House=pd.col('type').eq('rep'), Senate=pd.col('type').eq('sen'))
.groupby('id.bioguide')[['House', 'Senate']]
.any()
.value_counts()
)
House Senate
True False 437
False True 57
True True 43
Name: count, dtype: int64
Forty-three of today's 537 legislators have sat in both chambers, and only 57 senators have never been a representative. One call to json_normalize and one groupby got us from a tree of JSON to an answer.
Two smaller arguments are worth meeting. sep sets the character that joins the path components, which matters if you plan to use query later, since a period in a column name is awkward there:
list(pd.json_normalize(data, sep='_').columns)[1:6]
['id_bioguide', 'id_thomas', 'id_lis', 'id_govtrack', 'id_opensecrets']
And max_level controls how far down to go. At level zero, nothing is flattened, and you get the original four keys back as columns of dicts — plus two more that only some legislators have:
pd.json_normalize(data, max_level=0).columns.tolist()
['id', 'name', 'bio', 'terms', 'leadership_roles', 'family']
Those last two are exactly where errors earns its keep, which brings us to the mistakes.
Five mistakes people make
Passing the whole response instead of the list inside it. Most APIs wrap their results in an envelope — a count, a status, a page number, and then the records under some key. Numista's coin API, used by Bamboo Weekly #120, puts them under types. Hand json_normalize the envelope and it does exactly what you asked, which is not what you wanted:
response = {'count': 3,
'types': [{'id': 1, 'title': '1 Cent'},
{'id': 2, 'title': '1 Penny'},
{'id': 3, 'title': '1 Centime'}]}
pd.json_normalize(response)
count types
0 3 [{'id': 1, 'title': '1 Cent'}, {'id': 2, 'titl...
One row, and all your data crammed into a single cell. Pass response['types'], or say record_path='types'. Nothing raises, so the mistake usually surfaces several cells later.
Letting record_path and meta collide on a name. If a key exists both inside the exploded records and in the parent, Pandas refuses to guess which one you meant:
order = {'id': 'A-1',
'placed': '2026-08-01',
'items': [{'id': 'sku-9', 'qty': 2},
{'id': 'sku-4', 'qty': 1}]}
pd.json_normalize(order, record_path='items', meta=['id', 'placed'])
ValueError: Conflicting metadata name id, need distinguishing prefix
The error names the cure. meta_prefix renames the incoming parent fields, and record_prefix renames the ones from the list:
pd.json_normalize(order, record_path='items',
meta=['id', 'placed'], meta_prefix='order_')
id qty order_id order_placed
0 sku-9 2 A-1 2026-08-01
1 sku-4 1 A-1 2026-08-01
Assuming it recurses all the way down. It does by default, but max_level quietly stops it, and a value that is still a dict in a cell is easy to miss:
coin = {'id': 7,
'value': {'text': '1 cent',
'currency': {'name': 'US dollar', 'code': 'USD'}}}
list(pd.json_normalize(coin).columns)
['id', 'value.text', 'value.currency.name', 'value.currency.code']
list(pd.json_normalize(coin, max_level=1).columns)
['id', 'value.text', 'value.currency']
value.currency looks like a column of strings until you try .str.upper() on it. Use max_level deliberately, to keep a deeply nested blob intact for later, and not as a performance reflex.
Expecting a missing meta key to be forgiven. It is not. Only 285 of those 2,785 terms belong to someone with a leadership_roles key, and the default errors='raise' treats the other records as an error:
pd.json_normalize(data, record_path='terms',
meta=[['id', 'bioguide'], 'leadership_roles'])
KeyError: "Key 'leadership_roles' not found. To replace missing values of
'leadership_roles' with np.nan, pass in errors='ignore'"
An unusually helpful error message, and errors='ignore' does the obvious thing. Optional fields are the norm in real API data, so reach for it early.
Reaching for it when the JSON is already flat. If your list of dicts has no nesting at all, json_normalize gives you precisely what pd.DataFrame would:
flat = [{'city': 'Modiin', 'country': 'IL'},
{'city': 'Chicago', 'country': 'US'}]
pd.json_normalize(flat).equals(pd.DataFrame(flat))
True
The reverse mistake costs more. A series whose cells hold lists of dicts is not something json_normalize will accept:
df = pd.json_normalize(data, max_level=0)
pd.json_normalize(df['terms'])
TypeError: All items in data must be of type dict or NA-like, found list
Call explode first to get one dict per cell, or go back to the original JSON and use record_path.
Where it shows up in Bamboo Weekly
Bamboo Weekly #76: Aging legislators is the fullest use of it in the archive, and it is free to read. The data is the same congress-legislators JSON as above, but the solution takes the other route: one call per dict column — pd.json_normalize(df['id']), pd.json_normalize(df['bio']), pd.json_normalize(df['name']) — glued back on with pd.concat(..., axis='columns') and stripped of the originals with drop. The terms column gets explode and then a fourth json_normalize, taking the frame from 12,684 rows and 7 columns to 45,035 rows and 46.
Bamboo Weekly #120: Pennies asks how many countries still mint a one-cent coin, using Numista's API. There the JSON arrives through read_json, which leaves the issuer and value columns full of dicts, and json_normalize raises them to the top level so they can be grouped and counted.
Both were written against older Pandas. In Pandas 3, json_normalize keeps the index of the series you hand it, so the reset_index dance that #76 needed before its final concat is no longer necessary.
Practice it
Work through a json_normalize exercise, with instant feedback and no signup required: practice.lernerpython.com/bamboo-weekly/json-normalize/
Go deeper
json_normalize rarely works alone. explode turns a list of dicts into rows before they can become columns, concat glues the flattened columns back onto the frame they came from, and set_index puts the ID you just unpacked where it belongs. Once the frame is rectangular, groupby and value_counts are usually what you were after all along.
More Pandas videos on Python and Pandas with Reuven Lerner.
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.
Related methods
.explode()— when a cell holds a list and you want one row per element.str.get()— when only one key is worth pulling out of the dictspd.DataFrame()— when the records are flat and a constructor is enough
See it on real data
Below are the 2 Bamboo Weekly exercises that use json_normalize on real-world data — try each one, then study the worked solution.
Part of the Pandas Methods Index. See also practice by skill.