Lesson 49 · Pandas Full Course
Turn API JSON into a Pandas DataFrame with json_normalize | Pandas Course #49
In this lesson you'll be able to: Load a saved API response with json.load and read its page metadata Flatten nested objects into columns with…
- CoursePandas Full Course
- Lesson49 of 65
- FormatJupyter notebook · 40 code cells
- Data4 datasets
What you'll learn
Datasets used in this lesson
Save these next to the notebook. In Google Colab, upload them with the 📁 icon on the left first.
- superstore.csv preview1.7 MB
- superstore_api_page1.json preview76.1 KB
- superstore_api_page2.json preview75.9 KB
- superstore_api_page3.json preview74.6 KB
📓 Full notebook
Download .ipynbPandas 49: Working with API Data and JSON Normalization#
In this lesson you'll be able to:
- Load a saved API response with json.load and read its page metadata
- Flatten nested objects into columns with pd.json_normalize
- Unpack lists of records with record_path and meta
- Follow the next link through every page of a response
- Turn list columns into rows with explode Data: a saved superstore API response, 3 pages of real orders (data/pandas/superstore_api_page1-3.json)
Warm-up: the store as a flat file#
Lesson 48 loaded lean data with usecols and dtype. Same idea, on the superstore CSV.
import pandas as pd
use = ["Order ID", "Province", "Product Category", "Sales"]
flat = pd.read_csv("data/pandas/superstore.csv", usecols=use,
dtype={"Province": "category"})
print(flat.shape)
flat.head(3)
Load a saved API response#
Problem: an API reply is nested, so first we load it and find our way around.
Common mistake: passing a file name to json.load
import json
PAGE1 = "data/pandas/superstore_api_page1.json"
json.load(PAGE1)
with open(PAGE1) as f:
page = json.load(f)
print(type(page))
print(list(page.keys()))
for key in ["page", "per_page", "total_pages", "total_orders",
"next"]:
print(key, page[key])
orders_p1 = page["results"]
print(len(orders_p1))
print(json.dumps(orders_p1[0], indent=2))
first = orders_p1[0]
print(first["customer"]["name"])
print(first["customer"]["location"]["province"])
print(first["items"][0]["product"])
Quick check: how would you reach the shipping cost of the second order on this page?
print(orders_p1[1]["shipping"]["cost"])
Your turn#
Loop over the first 5 orders and print each order id, province and number of items.
for order in orders_p1[:5]:
where = order["customer"]["location"]["province"]
print(order["order_id"], where, len(order["items"]))
Flatten nested objects with json_normalize#
Problem: turn dictionaries inside dictionaries into ordinary columns.
Common mistake: read_json on a whole API response
raw = pd.read_json(PAGE1)
print(raw.shape)
print(raw.columns.tolist())
print(type(raw["results"].iloc[0]))
orders = pd.json_normalize(page["results"])
print(orders.shape)
print(orders.columns.tolist())
cols = ["order_id", "customer.name",
"customer.location.province", "shipping.mode"]
orders[cols].head()
Common mistake: dot access on a dotted column name
orders.shipping.mode.head(3)
flat_p1 = pd.json_normalize(page["results"], sep="_")
print([c for c in flat_p1.columns if c.startswith("customer")])
print(flat_p1.shipping_mode.head(3).tolist())
cost = flat_p1.groupby("shipping_mode")["shipping_cost"].mean()
cost.round(2)
shallow = pd.json_normalize(page["results"], max_level=1)
shallow[["order_id", "customer.location"]].head(3)
fmt = "%m/%d/%Y"
for c in ["order_date", "shipping_ship_date"]:
flat_p1[c] = pd.to_datetime(flat_p1[c], format=fmt)
days = flat_p1["shipping_ship_date"] - flat_p1["order_date"]
flat_p1["ship_days"] = days.dt.days
flat_p1.groupby("shipping_mode")["ship_days"].mean().round(1)
Your turn#
Using flat_p1, count the orders in each customer segment on page 1.
flat_p1["customer_segment"].value_counts()
Unpack lists of records with record_path and meta#
Problem: each order holds a list of items; we want one row per item.
items = pd.json_normalize(page["results"], record_path="items")
print(items.shape)
items[["product", "quantity", "sales", "profit"]].head(3)
Common mistake: a dotted name inside meta
pd.json_normalize(page["results"], record_path="items",
meta=["order_id", "customer.name"])
items = pd.json_normalize(page["results"], record_path="items",
meta=["order_id", ["customer", "name"],
["customer", "location", "province"]],
sep="_")
items[["order_id", "customer_name", "product", "sales"]].head(4)
Quick check: how many orders on page 1 contain more than one item?
per_order = items.groupby("order_id").size()
print((per_order > 1).sum(), per_order.max())
Your turn#
Which 3 sub-categories brought in the most sales on page 1?
top = items.groupby("sub_category")["sales"].sum()
top.sort_values(ascending=False).head(3).round(0)
Follow next through every page#
Problem: the API split 300 orders into pages; we need them all.
Common mistake: opening the next file without its folder
print(page["next"])
open(page["next"])
FOLDER = "data/pandas/"
name = "superstore_api_page1.json"
results = []
while name is not None:
with open(FOLDER + name) as f:
page = json.load(f)
results.extend(page["results"])
print(page["page"], len(page["results"]), page["next"])
name = page["next"]
print(len(results))
Quick check: did we get every order the API promised?
print(len(results) == page["total_orders"])
print(len({o["order_id"] for o in results}))
frames = []
name = "superstore_api_page1.json"
while name is not None:
with open(FOLDER + name) as f:
pg = json.load(f)
frames.append(pd.json_normalize(pg["results"], record_path="items"))
name = pg["next"]
both = pd.concat(frames, ignore_index=True)
print([len(f) for f in frames], both.shape)
META = ["order_id", "order_date", "priority",
["customer", "name"], ["customer", "segment"],
["customer", "location", "province"],
["shipping", "mode"]]
sales = pd.json_normalize(results, record_path="items",
meta=META, sep="_")
print(sales.shape)
sales.iloc[:3, -5:]
Your turn#
Using sales, find the total sales for each shipping mode.
by_mode = sales.groupby("shipping_mode")["sales"].sum()
by_mode.sort_values(ascending=False).round(0)
Turn list columns into rows with explode#
Problem: when a column holds lists, explode gives each element its own row.
all_orders = pd.json_normalize(results)
all_orders["n_items"] = all_orders["items"].str.len()
all_orders["n_items"].value_counts().sort_index()
long = all_orders[["order_id", "items"]].explode("items")
print(long.shape)
long.head(3)
detail = pd.json_normalize(long["items"].tolist())
detail["order_id"] = long["order_id"].to_numpy()
print(detail.shape, round(detail["sales"].sum(), 2))
print(round(sales["sales"].sum(), 2))
def get_cats(items):
return [i["category"] for i in items]
all_orders["cats"] = all_orders["items"].apply(get_cats)
print(all_orders.loc[2, "order_id"], all_orders.loc[2, "cats"])
all_orders["cats"].explode().value_counts()
Your turn#
How many of the 300 orders include at least one Technology item?
per_cat = all_orders[["order_id", "cats"]].explode("cats")
tech = per_cat[per_cat["cats"] == "Technology"]
print(tech["order_id"].nunique(), len(tech))
Quick check: which order holds the most items, and what's in it?
big = all_orders.loc[all_orders["n_items"].idxmax(), "order_id"]
detail.loc[detail["order_id"] == big, ["product", "sales"]]
Mini-project: which provinces sell, and which ones profit?#
Plan: total by province, add a margin, chart it, then dig into the weak spot.
prov = "customer_location_province"
by_prov = sales.groupby(prov)[["sales", "profit"]].sum()
by_prov["margin"] = by_prov["profit"] / by_prov["sales"] * 100
by_prov.sort_values("sales", ascending=False).head(5).round(1)
by_cat = sales.groupby("category")[["sales", "profit"]].sum()
by_cat["margin"] = by_cat["profit"] / by_cat["sales"] * 100
by_cat.round(1)
import matplotlib.pyplot as plt
big7 = by_prov.nlargest(7, "sales")["margin"].sort_values()
ax = big7.plot(kind="barh", figsize=(7, 4))
ax.set_title("Profit margin, 7 biggest provinces by sales")
ax.set_xlabel("Margin (% of sales)")
ax.set_ylabel("Province")
plt.show()
bc = sales[sales[prov] == "British Columbia"]
loss = bc.groupby("sub_category")["profit"].sum().sort_values()
loss.head(3).round(0)
cols = ["order_id", "product", "sales", "profit"]
bc.nsmallest(2, "profit")[cols]
row = by_prov.loc["British Columbia"]
print(f"BC: sales {row.sales:,.0f}, profit {row.profit:,.0f}, "
f"margin {row.margin:.1f}%")
rest = bc.drop(bc["profit"].idxmin())
print(round(rest["profit"].sum() / rest["sales"].sum() * 100, 1))
Recap#
- json.load reads an open file into dicts and lists, metadata included
- json_normalize flattens nested objects; sep sets the separator
- record_path unpacks lists of records; meta keeps parent fields
- Follow next in a while loop until it is None
- explode turns list cells into rows
Challenges#
- Add shipping cost to META and total it by shipping mode.
- Find the margin for each customer segment across all 300 orders.
- Save sales to output/pandas/l49_sales.json with orient='records' and reload it.
What's next#
Lesson 50: Encoding Categorical Variables Turn text labels into numbers that machine learning models can use.
Found this useful?
All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.



