Skip to content
Tutorials Center
My learning

Topics

Lesson 9 of 64 ยท Getting Started

JSON and Nested Data

Turn JSON from APIs into tables with pandas read_json, json_normalize, record_path, meta, explode and flattening of nested dicts, step by step.

Web APIs, configuration files and log systems speak JSON, and JSON is rarely a flat table. A single record may hold nested objects (an address inside a customer) and lists (several orders per customer). pandas gives you a small toolkit for turning that shape into rows and columns: read_json, json_normalize, explode and a few tricks for nested dictionaries.

In the browser, JSON comes from strings, parsed either by pd.read_json(io.StringIO(text)) or by the standard json module. On a real project the string would come from a file or an HTTP response.

flowchart LR
    A["Nested JSON<br/>objects inside objects,<br/>lists inside records"] --> B{"Shape?"}
    B -->|"flat list of objects"| C["read_json"]
    B -->|"nested dicts"| D["json_normalize"]
    B -->|"list of items per record"| E["json_normalize with record_path<br/>or explode"]
    C --> F["DataFrame"]
    D --> F
    E --> F

Pick the tool by the shape of the data: flat, nested objects, or nested lists.

read_json for flat data

A JSON array of objects maps directly to a table: each object is a row, each key a column. Wrap the text in io.StringIO; recent pandas versions want a file-like object, not a raw JSON string:

Python read_json on records

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

read_json guesses the layout and types. If your JSON was written with another orient (see Writing and Formatting Data), pass the same value when reading, for example orient="split".

Nested objects: json_normalize

Suppose each record has an object inside it. read_json would leave that object in a cell as a Python dictionary, which is hard to analyze. pd.json_normalize flattens nested dictionaries into columns, joining the keys with a dot:

Python Flattening nested dicts

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The columns are name, address.city, address.geo.lat and address.geo.lon. Change the separator with sep="_" to get names that are easier to type, like address_city. Use max_level=1 to stop flattening after one level and leave deeper dictionaries in the cells.

Python sep and max_level

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Nested lists: record_path and meta

Often each record owns a list of child items, such as a customer with several orders. You want one output row per child, and you also want some parent fields repeated on every row. That is what record_path (where the list is) and meta (which parent fields to carry along) do:

flowchart TB
    P1["customer Ana<br/>orders: [o1, o2]"] --> R1["row: o1 + Ana"]
    P1 --> R2["row: o2 + Ana"]
    P2["customer Ben<br/>orders: [o3]"] --> R3["row: o3 + Ben"]

record_path creates one row per child; meta copies parent fields onto each row.

Python record_path and meta

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Cleo has no orders, so she produces no rows at all. If you need to keep customers without children, use explode instead (shown below). Nested meta fields are given as paths: meta=[["address", "city"]] would pick the city inside address.

If some records lack a meta key, pass errors="ignore" and pandas fills NaN instead of failing.

Lists in cells: explode

Sometimes the list is just a simple column, such as tags, and you want one row per element. Series.explode() (or DataFrame.explode("column")) repeats the other columns for each element. Empty lists become a single missing value, so the row is kept:

Python explode

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Note that value_counts ignores the missing tag for "Draft". This is the opposite of record_path, which drops empty children. Choose based on whether you need to keep parents without children. Exploding is also an important reshaping tool, revisited in Reshaping: melt, stack, wide to long.

Dictionaries stored in a column

Sometimes you already have a DataFrame, and one column holds dictionaries (for example after reading a database JSON column). Turn the column into a DataFrame, then join it back:

Python Expanding a column of dicts

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Missing keys become NaN, and the set of columns is the union of all keys. The same result with the pandas constructor is pd.DataFrame(df["attrs"].tolist()), which is fine for dictionaries that are only one level deep.

From pandas back to nested JSON

DataFrame.to_dict(orient="records") creates the list of flat dictionaries. To rebuild nesting you need a bit of Python, for example grouping rows and collecting the children:

Python Rebuilding nested JSON

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

You will learn groupby properly in groupby Basics. The pattern here is the important part: flatten with json_normalize for analysis, and nest again only at the edge when an API demands it.

Tip: do your analysis on the flat table and keep it "tidy": one row per thing you want to count. Nested structures are for transport, not for calculating.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which function flattens nested dictionaries into columns such as address.city?

    json_normalize walks nested dictionaries and creates one column per leaf, joining keys with a separator (a dot by default).
  2. 2. In json_normalize, what does record_path="orders" do?

    record_path points to a list of child records. Each child becomes a row, and meta fields add parent information.
  3. 3. A record has an empty orders list. What happens with record_path="orders"?

    With no child items there is nothing to turn into rows, so that parent disappears from the result. Use explode if you must keep it.
  4. 4. What does DataFrame.explode("tags") do with a row whose tags cell is a list of three items?

    explode turns each list element into its own row and repeats the values of the other columns.
  5. 5. Why do the lessons use pd.read_json(io.StringIO(text)) rather than pd.read_json(text)?

    Passing raw JSON strings is deprecated or removed in recent pandas. Wrapping the text in StringIO makes the intent clear and works everywhere.

Summary

  • pd.read_json(io.StringIO(text)) reads a flat JSON array of objects into a table; match orient= to how the JSON was written.
  • pd.json_normalize flattens nested dictionaries; control names with sep= and depth with max_level=.
  • record_path creates one row per child item and meta carries parent fields onto those rows; empty child lists vanish.
  • explode turns list cells into rows and keeps parents with empty lists (as a missing value).
  • A column of dicts can be expanded with pd.json_normalize(col.tolist()) and joined back with concat.
  • Analyze flat, tidy tables; rebuild nested JSON only when an API requires it.

Next: Selecting Columns and Rows, the first step into choosing exactly the data you need.