26. How to Read APIs, JSON and Zipped CSVs into pandas

26.3 JSON into a DataFrame

By Ravindra Bagale, trainer · LinkedIn · Last updated: 24 October 2026

Why. JSON rarely arrives as a perfect rectangle. You need a clear path from nested dicts to columns.

What.

• pd.DataFrame(list_of_dicts) when the payload is already a list of flat records.
• pd.json_normalize(records) when records have nested dicts (customer.city).
• pd.read_json(path_or_url) for JSON files that are tables or records.

How.

Worked example A — List under orders.

import json
from pathlib import Path
import pandas as pd

payload = json.loads(Path("data/raw/status_sample.json").read_text(encoding="utf-8"))
df = pd.json_normalize(payload["orders"])
print(df.shape)
print(df)

Worked example B — Nested customer dict.

import pandas as pd

records = [
    {"order_id": "BLK-10", "amount_inr": 120, "customer": {"name": "Ruhi Bagale", "city": "Pune"}},
    {"order_id": "BLK-11", "amount_inr": 90, "customer": {"name": "Amir", "city": "Nashik"}},
]
df = pd.json_normalize(records)
print(df.columns.tolist())
print(df)

Worked example C — read_json records file.

from pathlib import Path
import pandas as pd

Path("data/raw/orders_records.json").write_text(
    '[{"order_id":"BLK-20","city":"Solapur","amount_inr":70},'
    '{"order_id":"BLK-21","city":"Nagpur","amount_inr":150}]',
    encoding="utf-8",
)
df = pd.read_json("data/raw/orders_records.json")
print(df)