Mathew K Analytics

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…

📓 Full notebook

Download .ipynb

Pandas 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)
(8399, 4)
Order ID Sales Province Product Category
0 3 261.54 Nunavut Office Supplies
1 293 10123.02 Nunavut Office Supplies
2 293 244.57 Nunavut Office Supplies

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)
---------------------------------------------------------------------------
AttributeError                            Traceback (most recent call last)
Cell In[3], line 3
      1 import json
      2 PAGE1 = "data/pandas/superstore_api_page1.json"
----> 3 json.load(PAGE1)

File ~\AppData\Local\Programs\Python\Python312\Lib\json\__init__.py:293, in load(fp, cls, object_hook, parse_float, parse_int, parse_constant, object_pairs_hook, **kw)
    274 def load(fp, *, cls=None, object_hook=None, parse_float=None,
    275         parse_int=None, parse_constant=None, object_pairs_hook=None, **kw):
    276     """Deserialize ``fp`` (a ``.read()``-supporting file-like object containing
    277     a JSON document) to a Python object.
    278 
   (...)
    291     kwarg; otherwise ``JSONDecoder`` is used.
    292     """
--> 293     return loads(fp.read(),
    294         cls=cls, object_hook=object_hook,
    295         parse_float=parse_float, parse_int=parse_int,
    296         parse_constant=parse_constant, object_pairs_hook=object_pairs_hook, **kw)

AttributeError: 'str' object has no attribute 'read'
with open(PAGE1) as f:
    page = json.load(f)
print(type(page))
print(list(page.keys()))
<class 'dict'>
['page', 'per_page', 'total_pages', 'total_orders', 'next', 'results']
for key in ["page", "per_page", "total_pages", "total_orders",
            "next"]:
    print(key, page[key])
page 1
per_page 100
total_pages 3
total_orders 300
next superstore_api_page2.json
orders_p1 = page["results"]
print(len(orders_p1))
print(json.dumps(orders_p1[0], indent=2))
100
{
  "order_id": 13729,
  "order_date": "1/1/2009",
  "priority": "Not Specified",
  "customer": {
    "name": "Matt Collister",
    "segment": "Home Office",
    "location": {
      "province": "Quebec",
      "region": "Quebec"
    }
  },
  "shipping": {
    "mode": "Express Air",
    "ship_date": "1/3/2009",
    "cost": 35.0
  },
  "items": [
    {
      "product": "Safco Industrial Wire Shelving",
      "category": "Office Supplies",
      "sub_category": "Storage & Organization",
      "quantity": 9,
      "unit_price": 95.99,
      "discount": 0.08,
      "sales": 872.48,
      "profit": -342.91
    }
  ]
}
first = orders_p1[0]
print(first["customer"]["name"])
print(first["customer"]["location"]["province"])
print(first["items"][0]["product"])
Matt Collister
Quebec
Safco Industrial Wire Shelving

Quick check: how would you reach the shipping cost of the second order on this page?

print(orders_p1[1]["shipping"]["cost"])
4.69

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"]))
13729 Quebec 1
28774 Ontario 1
710 Manitoba 3
39841 British Columbia 1
59174 Manitoba 1

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]))
(100, 6)
['page', 'per_page', 'total_pages', 'total_orders', 'next', 'results']
<class 'dict'>
orders = pd.json_normalize(page["results"])
print(orders.shape)
print(orders.columns.tolist())
(100, 11)
['order_id', 'order_date', 'priority', 'items', 'customer.name', 'customer.segment', 'customer.location.province', 'customer.location.region', 'shipping.mode', 'shipping.ship_date', 'shipping.cost']
cols = ["order_id", "customer.name",
        "customer.location.province", "shipping.mode"]
orders[cols].head()
order_id customer.name customer.location.province shipping.mode
0 13729 Matt Collister Quebec Express Air
1 28774 Jessica Myrick Ontario Regular Air
2 710 Susan MacKendrick Manitoba Regular Air
3 39841 Benjamin Venier British Columbia Regular Air
4 59174 Emily Ducich Manitoba Express Air

Common mistake: dot access on a dotted column name

orders.shipping.mode.head(3)
---------------------------------------------------------------------------
AttributeError                            Traceback (most recent call last)
~\AppData\Local\Temp\ipykernel_26436\1701555337.py in ?()
----> 1 orders.shipping.mode.head(3)

~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\generic.py in ?(self, name)
   6190             and name not in self._accessors
   6191             and self._info_axis._can_hold_identifiers_and_holds_name(name)
   6192         ):
   6193             return self[name]
-> 6194         return object.__getattribute__(self, name)

AttributeError: 'DataFrame' object has no attribute 'shipping'
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())
['customer_name', 'customer_segment', 'customer_location_province', 'customer_location_region']
['Express Air', 'Regular Air', 'Regular Air']
cost = flat_p1.groupby("shipping_mode")["shipping_cost"].mean()
cost.round(2)
shipping_mode
Delivery Truck    44.45
Express Air       11.26
Regular Air        8.00
Name: shipping_cost, dtype: float64
shallow = pd.json_normalize(page["results"], max_level=1)
shallow[["order_id", "customer.location"]].head(3)
order_id customer.location
0 13729 {'province': 'Quebec', 'region': 'Quebec'}
1 28774 {'province': 'Ontario', 'region': 'Ontario'}
2 710 {'province': 'Manitoba', 'region': 'Prarie'}
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)
shipping_mode
Delivery Truck    1.8
Express Air       2.6
Regular Air       1.8
Name: ship_days, dtype: float64

Your turn#

Using flat_p1, count the orders in each customer segment on page 1.

flat_p1["customer_segment"].value_counts()
customer_segment
Corporate         36
Small Business    23
Home Office       21
Consumer          20
Name: count, dtype: int64

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)
(162, 8)
product quantity sales profit
0 Safco Industrial Wire Shelving 9 872.48 -342.91
1 Perma STOR-ALLª Hanging File Box, 13 1/8"W x ... 32 180.36 -111.80
2 Avery Poly Binder Pockets 42 161.77 -150.37

Common mistake: a dotted name inside meta

pd.json_normalize(page["results"], record_path="items",
                  meta=["order_id", "customer.name"])
---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\io\json\_normalize.py:485, in json_normalize.<locals>._pull_field(js, spec, extract_record)
    484             raise KeyError(field)
--> 485         result = result[field]
    486 else:

KeyError: 'customer.name'

The above exception was the direct cause of the following exception:

KeyError                                  Traceback (most recent call last)
Cell In[20], line 1
----> 1 pd.json_normalize(page["results"], record_path="items",
      2                   meta=["order_id", "customer.name"])

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\io\json\_normalize.py:625, in json_normalize(data, record_path, meta, meta_prefix, record_prefix, errors, sep, max_level)
    622                 meta_vals[key].append(meta_val)
    623             records.extend(recs)
--> 625 _recursive_extract(data, record_path, {}, level=0)
    627 result = DataFrame(records)
    629 if record_prefix is not None:

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\io\json\_normalize.py:621, in json_normalize.<locals>._recursive_extract(data, path, seen_meta, level)
    619         meta_val = seen_meta[key]
    620     else:
--> 621         meta_val = _pull_field(obj, val[level:])
    622     meta_vals[key].append(meta_val)
    623 records.extend(recs)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\io\json\_normalize.py:497, in json_normalize.<locals>._pull_field(js, spec, extract_record)
    495         return np.nan
    496     else:
--> 497         raise KeyError(
    498             f"Key {e} not found. To replace missing values of {e} with "
    499             f"np.nan, pass in errors='ignore'"
    500         ) from e
    502 return result

KeyError: "Key 'customer.name' not found. To replace missing values of 'customer.name' with np.nan, pass in errors='ignore'"
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)
order_id customer_name product sales
0 13729 Matt Collister Safco Industrial Wire Shelving 872.48
1 28774 Jessica Myrick Perma STOR-ALLª Hanging File Box, 13 1/8"W x ... 180.36
2 710 Susan MacKendrick Avery Poly Binder Pockets 161.77
3 710 Susan MacKendrick Deflect-o EconoMat Studded, No Bevel Mat for L... 447.09

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())
41 5

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)
sub_category
Office Machines       109213.0
Chairs & Chairmats     79375.0
Copiers and Fax        51878.0
Name: sales, dtype: float64

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"])
superstore_api_page2.json
---------------------------------------------------------------------------
FileNotFoundError                         Traceback (most recent call last)
Cell In[24], line 2
      1 print(page["next"])
----> 2 open(page["next"])

File ~\AppData\Roaming\Python\Python312\site-packages\IPython\core\interactiveshell.py:324, in _modified_open(file, *args, **kwargs)
    317 if file in {0, 1, 2}:
    318     raise ValueError(
    319         f"IPython won't let you open fd={file} by default "
    320         "as it is likely to crash IPython. If you know what you are doing, "
    321         "you can use builtins' open."
    322     )
--> 324 return io_open(file, *args, **kwargs)

FileNotFoundError: [Errno 2] No such file or directory: 'superstore_api_page2.json'
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))
1 100 superstore_api_page2.json
2 100 superstore_api_page3.json
3 100 None
300

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}))
True
300
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)
[162, 159, 156] (477, 8)
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:]
(477, 15)
priority customer_name customer_segment customer_location_province shipping_mode
0 Not Specified Matt Collister Home Office Quebec Express Air
1 High Jessica Myrick Small Business Ontario Regular Air
2 Low Susan MacKendrick Corporate Manitoba Regular Air

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)
shipping_mode
Regular Air       445296.0
Delivery Truck    421588.0
Express Air        49935.0
Name: sales, dtype: float64

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()
n_items
1    176
2     84
3     29
4      9
5      2
Name: count, dtype: int64
long = all_orders[["order_id", "items"]].explode("items")
print(long.shape)
long.head(3)
(477, 2)

order_id items
0 13729 {'product': 'Safco Industrial Wire Shelving', ...
1 28774 {'product': 'Perma STOR-ALLª Hanging File Box...
2 710 {'product': 'Avery Poly Binder Pockets', 'cate...
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))
(477, 9) 916819.45
916819.45
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()
710 ['Office Supplies', 'Furniture', 'Technology']
cats
Office Supplies    272
Technology         105
Furniture          100
Name: count, dtype: int64

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))
96 105

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"]]
product sales
57 Panasonic KX-P3200 Dot Matrix Printer 4531.34
58 Tensor "Hersey Kiss" Styled Floor Lamp 574.50
59 Holmes Odor Grabber 262.94
60 Eldon Image Series Black Desk Accessories 138.17
61 Xerox 188 216.87

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)
sales profit margin
customer_location_province
Ontario 168733.5 19774.1 11.7
Quebec 142379.2 24990.4 17.6
British Columbia 112839.5 323.5 0.3
Nova Scotia 104058.0 17413.5 16.7
Saskachewan 103247.5 16300.0 15.8
by_cat = sales.groupby("category")[["sales", "profit"]].sum()
by_cat["margin"] = by_cat["profit"] / by_cat["sales"] * 100
by_cat.round(1)
sales profit margin
category
Furniture 362701.5 30378.1 8.4
Office Supplies 225106.9 33155.0 14.7
Technology 329011.1 40981.2 12.5
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()
No description has been provided for this image
bc = sales[sales[prov] == "British Columbia"]
loss = bc.groupby("sub_category")["profit"].sum().sort_values()
loss.head(3).round(0)
sub_category
Office Machines      -7919.0
Chairs & Chairmats    -756.0
Office Furnishings    -196.0
Name: profit, dtype: float64
cols = ["order_id", "product", "sales", "profit"]
bc.nsmallest(2, "profit")[cols]
order_id product sales profit
224 35139 Okidata Pacemark 4410N Wide Format Dot Matrix ... 10380.34 -8389.47
264 37537 Hon Valutaskª Swivel Chairs 4083.19 -1049.85
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))
BC: sales 112,840, profit 323, margin 0.3%
8.5

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#

  1. Add shipping cost to META and total it by shipping mode.
  2. Find the margin for each customer segment across all 300 orders.
  3. 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.