JSON to Table in Python: 10 Working Recipes

You called pd.json_normalize on an API response. One line, a DataFrame, done. Then you looked at the items column and found a whole list of dicts crammed into each cell.

Nested objects flatten on their own, but a JSON array to table conversion needs a row shape you choose, and that choice decides whether the table is right.

TL;DR

• Join each array into one cell when a person will read the table. Use one row per item (record_path or explode) when you analyze. If unsure, use explode, because it never loses a parent.
• record_path silently drops any parent whose list is empty. Deeper nesting takes a list path, dicts keyed by id take DataFrame.from_dict(..., orient="index"), and max_level stops flattening early.
• to_html escapes cell text. to_markdown needs tabulate, leaves | unescaped and prints missing values as nan.

Every pandas JSON to table recipe below ran on Python 3.11.16, pandas 3.1.1 and tabulate 0.10.0, and each output block was copied from a real run.

Which shape should each array take?

Join the array into one cell when a person will read the table. Use one row per item when someone will sum, count or group by item.

If you are unsure, use explode (Recipe 3): it never loses a parent. The recipes run on three orders (the data is in Recipe 1). Order 1003 has an empty items list, which is what real APIs send for "nothing yet". The three array recipes treat it differently:

Recipe	Rows from our 3 orders	Order 1003	Pick it when
2: record_path	3, one per line item	dropped	you analyze line items
3: explode	4, one per line item plus one for 1003	kept, with empty sku and qty	you analyze line items and still need every order counted
4: joined string	3, one per order	kept, with an empty items cell	a person will read the table

Recipe 1: nested objects flatten into dotted columns

pd.json_normalize names each nested key with a dot between the levels (pandas docs). It wants parsed Python objects, so run json.load or json.loads first: a raw string raises NotImplementedError with an empty message.

import pandas as pd

orders = [
    {
        "order_id": 1001,
        "customer": {"name": "Ada", "city": "London"},
        "items": [{"sku": "A1", "qty": 2}, {"sku": "B2", "qty": 1}],
    },
    {
        "order_id": 1002,
        "customer": {"name": "Linus", "city": "Helsinki"},
        "items": [{"sku": "A1", "qty": 5}],
    },
    {
        "order_id": 1003,
        "customer": {"name": "Grace", "city": "New York"},
        "items": [],
    },
]
df = pd.json_normalize(orders)
print(df.to_string())

   order_id                                               items customer.name customer.city
0      1001  [{'sku': 'A1', 'qty': 2}, {'sku': 'B2', 'qty': 1}]           Ada        London
1      1002                           [{'sku': 'A1', 'qty': 5}]         Linus      Helsinki
2      1003                                                  []         Grace      New York

customer.name and customer.city came from the nested object. items kept its list. Pass sep="_" to get customer_name, which saves you from bracket access like df["customer.name"].

Try this: list the array columns in your own file before you pick a recipe. If it prints [], Recipe 1 is all you need. If it prints ['data'], the whole response sits inside an envelope: pass record_path="data" to get one row per record.

import json
import pandas as pd

with open("orders.json", encoding="utf-8") as f:   # your file here
    data = json.load(f)

df = pd.json_normalize(data)
list_cols = [c for c in df.columns if df[c].map(lambda v: isinstance(v, list)).any()]
print(list_cols)

On our orders file this prints:

['items']

Recipes 2 to 4: turn the array column into rows or a cell

Recipe 2: child rows with record_path

Name the array, and list the parent fields you want carried along in meta. A nested parent field takes a list path.

# orders: the list from Recipe 1
import pandas as pd

lines = pd.json_normalize(
    orders,
    record_path="items",
    meta=["order_id", ["customer", "name"]],
    sep="_",
)
print(lines.to_string())

  sku  qty order_id customer_name
0  A1    2     1001           Ada
1  B2    1     1001           Ada
2  A1    5     1002         Linus

Order 1003 is gone: record_path builds rows from the items, so an order without items produces no rows, and nothing warns you. If you count orders from this frame, you undercount. The row-count check at the end of this page catches it.

Recipe 3: exploded rows with explode

Normalize the whole thing first, give every list element its own row, then normalize the exploded column.

# orders: the list from Recipe 1
import pandas as pd

flat = pd.json_normalize(orders, sep="_")
rows = flat.explode("items", ignore_index=True)
items = pd.json_normalize(rows["items"])
out = pd.concat([rows.drop(columns="items"), items], axis=1)
out["qty"] = out["qty"].astype("Int64")
print(out.to_string())

   order_id customer_name customer_city  sku   qty
0      1001           Ada        London   A1     2
1      1001           Ada        London   B2     1
2      1002         Linus      Helsinki   A1     5
3      1003         Grace      New York  NaN  <NA>

Order 1003 survives as a row of empty cells. That row is also why the astype("Int64") line exists: without it, the missing quantity turns qty into floats (2.0, 1.0, 5.0).

Recipe 4: one joined cell per order

Collapse each array into a string and keep one row per parent.

# orders: the list from Recipe 1
import pandas as pd

flat = pd.json_normalize(orders, sep="_")
flat["items"] = flat["items"].map(
    lambda xs: ", ".join(f"{i['sku']} x{i['qty']}" for i in xs)
)
print(flat.to_string())

   order_id         items customer_name customer_city
0      1001  A1 x2, B2 x1           Ada        London
1      1002         A1 x5         Linus      Helsinki
2      1003                       Grace      New York

HTML and Markdown tables are for eyes, so Recipe 8 starts from this joined frame.

Recipes 5 to 7: deeper nesting

Real payloads go past one level. These three recipes cover arrays inside arrays, dicts keyed by id, and objects you want to leave unflattened.

Recipe 5: arrays inside arrays, and arrays of scalars

Here each order has a list of tags (scalars) and shipments that each hold their own boxes. A list path walks arrays inside arrays, and meta can reach a field of the parent array, here the shipment id.

import pandas as pd

deep = [
    {
        "order_id": 1001,
        "tags": ["gift", "rush"],
        "shipments": [
            {"id": "S1", "boxes": [{"sku": "A1", "qty": 2}, {"sku": "B2", "qty": 1}]},
            {"id": "S2", "boxes": [{"sku": "A1", "qty": 1}]},
        ],
    },
    {"order_id": 1002, "tags": [], "shipments": []},
]
boxes = pd.json_normalize(
    deep, record_path=["shipments", "boxes"], meta=["order_id", ["shipments", "id"]]
)
print(boxes.to_string())

tags = pd.json_normalize(deep)[["order_id", "tags"]]
tags["tags"] = tags["tags"].map(", ".join)
print(tags.to_string())

  sku  qty order_id shipments.id
0  A1    2     1001           S1
1  B2    1     1001           S1
2  A1    1     1001           S2
   order_id        tags
0      1001  gift, rush
1      1002            

Order 1002 has no shipments, so it vanishes from the box rows just as 1003 did in Recipe 2. An array of scalars needs no new recipe: ", ".join makes it a cell, the joined shape one level down.

Recipe 6: a dict keyed by id

A dict keyed by id is not a list of records, so json_normalize gives you one wide row. DataFrame.from_dict with orient="index" gives one row per key:

import pandas as pd

users = {"u1": {"name": "Ada", "plan": "pro"}, "u2": {"name": "Linus", "plan": "free"}}
print(pd.json_normalize(users).to_string())
print(pd.DataFrame.from_dict(users, orient="index").to_string())

  u1.name u1.plan u2.name u2.plan
0     Ada     pro   Linus    free
     name  plan
u1    Ada   pro
u2  Linus  free

Recipe 7: stop flattening early with max_level

Objects flatten all the way down by default. max_level stops early and leaves the rest as a dict in the cell:

import pandas as pd

cust = [{"order_id": 1, "customer": {"name": "Ada", "address": {"city": "London", "geo": {"lat": 51.5, "lon": -0.1}}}}]
print(pd.json_normalize(cust).to_string())
print(pd.json_normalize(cust, max_level=1).to_string())

   order_id customer.name customer.address.city  customer.address.geo.lat  customer.address.geo.lon
0         1           Ada                London                      51.5                      -0.1
   order_id customer.name                                       customer.address
0         1           Ada  {'city': 'London', 'geo': {'lat': 51.5, 'lon': -0.1}}

Recipe 8: to_html escapes cell text for you

DataFrame.to_html returns a <table> string and escapes <, > and & by default, so a stray tag in your data cannot break the page. border=0 drops the default border="1" attribute and classes adds a CSS class. Here are orders 1002 and 1003 from the joined frame:

# flat: the joined frame from Recipe 4
print(flat.iloc[1:][["order_id", "items"]].to_html(index=False, border=0, classes="orders"))

<table class="dataframe orders">
  <thead>
    <tr style="text-align: right;">
      <th>order_id</th>
      <th>items</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>1002</td>
      <td>A1 x5</td>
    </tr>
    <tr>
      <td>1003</td>
      <td></td>
    </tr>
  </tbody>
</table>

Order 1003 is a row with an empty cell, so the count stays right. Escaping, on a cell that holds <b>hi</b> & bye:

import pandas as pd

note = pd.DataFrame({"note": ["<b>hi</b> & bye"]})
for escape in (True, False):
    html = note.to_html(index=False, header=False, escape=escape)
    print(escape, [ln.strip() for ln in html.splitlines() if "<td>" in ln][0])

True <td>&lt;b&gt;hi&lt;/b&gt; &amp; bye</td>
False <td><b>hi</b> & bye</td>

Use escape=False only for HTML you wrote yourself.

Recipe 9: to_markdown needs tabulate, and it mangles pipes and missing values

DataFrame.to_markdown prints a pipe table you can paste into a README or a pull request (pandas docs). It depends on the optional tabulate package. Without it you get:

ImportError: `Import tabulate` failed.  Use pip, conda, or your preferred package management tool to install the tabulate package.

After pip install tabulate it works, but real text has two traps, and pandas guards against neither:

import pandas as pd

notes = pd.DataFrame([
    {"sku": "A1", "note": "fits a|b"},
    {"sku": "B2", "note": None},
])
print(notes.to_markdown(index=False))

| sku   | note     |
|:------|:---------|
| A1    | fits a|b |
| B2    | nan      |

The first row now has three cells under a two-column header, because the pipe inside a|b splits the cell. The second row says nan where the note was empty. Fill the gaps and escape the pipes before you render:

import pandas as pd

notes = pd.DataFrame([
    {"sku": "A1", "note": "fits a|b"},
    {"sku": "B2", "note": None},
])
clean = notes.astype(object).where(notes.notna(), "")
clean = clean.apply(lambda col: col.map(lambda v: str(v).replace("|", "\\|")))
print(clean.to_markdown(index=False))

| sku   | note      |
|:------|:----------|
| A1    | fits a\|b |
| B2    |           |

Recipe 10: HTML and Markdown tables with the standard library only

Skip this one if pandas is installed. When you cannot install it (a Lambda layer, a build script, a pre-commit hook), json and html are enough. This module flattens nested objects into dotted keys and builds the header from the union of every record's keys. Its list_cell argument decides what an array becomes, and the default is compact JSON. Pass a function that joins sku x qty strings to get the joined shape of Recipe 4.

# orders: the list from Recipe 1
import json
from html import escape


def compact(xs):
    return json.dumps(xs, separators=(",", ":"))


def flatten(obj, prefix="", list_cell=compact):
    out = {}
    for key, value in obj.items():
        name = f"{prefix}{key}"
        if isinstance(value, dict):
            out.update(flatten(value, name + ".", list_cell))
        elif isinstance(value, list):
            out[name] = list_cell(value)
        else:
            out[name] = "" if value is None else str(value)
    return out


def table_rows(records, list_cell=compact):
    rows = [flatten(r, list_cell=list_cell) for r in records]
    cols = list(dict.fromkeys(k for r in rows for k in r))  # union of keys
    return cols, rows


def to_html(records, **kw):
    cols, rows = table_rows(records, **kw)
    head = "".join(f"<th>{escape(c, quote=False)}</th>" for c in cols)
    body = "".join(
        "<tr>" + "".join(f"<td>{escape(r.get(c, ''), quote=False)}</td>" for c in cols) + "</tr>"
        for r in rows
    )
    return f"<table><thead><tr>{head}</tr></thead><tbody>{body}</tbody></table>"


def to_markdown(records, **kw):
    cols, rows = table_rows(records, **kw)

    def cell(text):
        return text.replace("|", "\\|").replace("\n", " ")

    lines = ["| " + " | ".join(cell(c) for c in cols) + " |",
             "| " + " | ".join("---" for _ in cols) + " |"]
    for r in rows:
        lines.append("| " + " | ".join(cell(r.get(c, "")) for c in cols) + " |")
    return "\n".join(lines)


print(to_html(orders[2:]))
print(to_markdown(orders + [{"order_id": 1004, "coupon": "SPRING|5"}]))

<table><thead><tr><th>order_id</th><th>customer.name</th><th>customer.city</th><th>items</th></tr></thead><tbody><tr><td>1003</td><td>Grace</td><td>New York</td><td>[]</td></tr></tbody></table>
| order_id | customer.name | customer.city | items | coupon |
| --- | --- | --- | --- | --- |
| 1001 | Ada | London | [{"sku":"A1","qty":2},{"sku":"B2","qty":1}] |  |
| 1002 | Linus | Helsinki | [{"sku":"A1","qty":5}] |  |
| 1003 | Grace | New York | [] |  |
| 1004 |  |  |  | SPRING\|5 |

Order 1003 shows up as [], so no row is lost. Building the header from the first record alone would have dropped coupon, which exists only on order 1004.

The errors you will actually hit

Every message below is copied from a real run on pandas 3.1.1.

• KeyError: "Key 'rows' not found. If specifying a record_path, all elements of data should have the path." means some record lacks the record_path key, including a record with no items key at all (an empty list is fine). Use Recipe 3 instead: a missing key becomes a row of empty cells.
• KeyError: "Key 'customer' not found. To replace missing values of 'customer' with np.nan, pass in errors='ignore'" means a record lacks a meta field. errors="ignore" fills those cells with NaN. The cost: a mistyped meta key now gives a column of NaN where you used to get this error.
• No error, one row: if the payload is {"status": "ok", "data": [...]}, pd.json_normalize(payload) returns a single row whose data cell holds the whole list. Pass record_path="data".
• FileNotFoundError: [Errno 2] No such file or directory: '[{"id": 1, "name": "Ada"}, {"id": 2, "name": "Linus"}]' comes from pd.read_json on a JSON string, which pandas reads as a file path. Pass a path, or wrap the text in io.StringIO(raw).
• ValueError: Trailing data at position 24 means the file is NDJSON, one object per line. Add lines=True.
• json.decoder.JSONDecodeError: Extra data: line 2 column 1 (char 25) is json.loads on that same NDJSON text. Parse line by line: [json.loads(line) for line in text.splitlines()].
• ValueError: If using all scalar values, you must pass an index comes from pd.read_json or pd.DataFrame on a single object. pd.json_normalize(obj) accepts a lone dict and returns one row.

A display gotcha, not an error: print(df) collapses the middle columns when the frame is wider than your terminal and shows order_id ... customer.city. Nothing is lost. Use print(df.to_string()), as the blocks above do.

How do I convert nested JSON to a table in Python?

Parse it with json.loads (or json.load for a file) and pass the result to pd.json_normalize. Nested objects become dotted columns and arrays stay as lists in a cell, as the output in Recipe 1 shows. Use Recipe 2 or 3 for one row per array element.

How do I turn JSON into a Markdown table in Python?

Flatten with pd.json_normalize, join the arrays, fill missing values, escape | and call df.to_markdown(index=False) after pip install tabulate (Recipe 9). Without pandas, the to_markdown function in Recipe 10 does the same.

How do I flatten JSON that contains arrays?

Pick a shape from the table at the top: record_path or explode for one row per element, a joined string for one row per parent. Arrays inside arrays take a list path, as in Recipe 5.

When you only need the table once

If you have a single file and no script to maintain, a client-side converter is enough: one that runs entirely in your browser, so nothing is uploaded. Before you trust one, check three things. It keeps each object as one row and each array in its cell as compact JSON, so the row count matches your record count and no empty array disappears. It flattens nested objects into dotted columns, as json_normalize does. Its Markdown view is ready to copy. Most converters do not explode line items into child rows. For one row per item, use Recipe 2 or 3 above.

Whichever route you take, compare the number of distinct parents in your table with the number of records you started with. On the Recipe 2 frame:

# orders: the list from Recipe 1
# lines: the frame from Recipe 2
print(lines["order_id"].nunique(), "of", len(orders), "orders")

2 of 3 orders

A number below your record count means the shape you chose for an empty array dropped a parent. Recipe 3 or Recipe 4 brings it back.

Sourcing note: every recipe above ran on Python 3.11.16, pandas 3.1.1 and tabulate 0.10.0 on 2026-11-09, and each output block was copied from a real run; the ImportError string in Recipe 9 came from the same check with tabulate not yet installed.