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, **kwSource：https://dev.to/dan_lumatic/json-to-table-in-python-10-working-recipes-214b
My favorites
PreviousMaking Hindsight Answers Checkable in a Streamlit AppNext忘却の分子機構が明らかになった今、タンパク質を「中から見る」意味が変わる理由

Related posts

Call Databricks Genie from C# with LakeSpeak

Call Databricks Genie from C# with LakeSpeak

dev.to
AMR Mechanical and Electrical Engineering — Ch.12: Contents {#contents .TOC-Heading} [KR]

AMR Mechanical and Electrical Engineering — Ch.12: Contents {#contents .TOC-Heading} [KR]

dev.to
AMR Mechanical and Electrical Engineering — Ch.14: Contents {#contents .TOC-Heading} [KR]

AMR Mechanical and Electrical Engineering — Ch.14: Contents {#contents .TOC-Heading} [KR]

dev.to
Wrap or Rewrite? Reviving the 1989 GlobalMinimum Bayesian Global-Optimization Library for R and Python

Wrap or Rewrite? Reviving the 1989 GlobalMinimum Bayesian Global-Optimization Library for R and Python

arXiv stat.CO
Snowflake Just Made Business Ontology an AI Priority. Here’s What Comes Next.

Snowflake Just Made Business Ontology an AI Priority. Here’s What Comes Next.

dev.to Data
December 14 Is the Newest Week on the WARN Calendar: 6,274 Workers Scheduled

December 14 Is the Newest Week on the WARN Calendar: 6,274 Workers Scheduled

dev.to News