Joseph's Monday morning problem

πŸ›οΈ
Real-world scenario
Jumia Kenya: automating weekly sales reports

Joseph works in marketing ops at Jumia Kenya in Mombasa. Every Monday, he needs to pull sales data from their internal API and build a report showing revenue by category, top-performing regions, and inventory turnover. The API returns orders as deeply nested JSON: each order contains line items, each line item contains a product with a category. Flattening this by hand in Excel takes 45 minutes. With Pandas, it should take minutes.

20
Orders
6
Categories
KSh 340K
Revenue
{
"orders": [
{
"order_id": "JUM-00001",
"customer_id": "C-123",
"region": "Nairobi",
"items": [
{"sku": "PHN-1", "product": "iPhone", "category": "Phones", "qty": 1, "price": 45000}
]
}
]
}
β†’
pd.json_normalize()
order_id customer_id region sku product category qty price
JUM-00001 C-123 Nairobi PHN-1 iPhone Phones 1 KSh 45,000

JSON basics for analysts

JSON (JavaScript Object Notation) is how APIs send data. It's just text β€” but structured text. Every JSON value is one of six types:

Object
{}
Python dict
Array
[]
Python list
String
"text"
Python str
Number
42
Python int/float
Boolean
true
Python bool
Null
null
Python None

Three common JSON shapes

Real APIs return JSON in three patterns. Learn to recognize each:

Shape 1: Flat list of objects

Simplest case β€” an array of objects, all at the same level. Just pass it to pd.DataFrame():

shape1.py Flat JSON β†’ DataFrame
product price stock 0 iPhone 45000 12 1 Samsung 35000 8

Style 2: Nested array inside an object

The API wraps data in a parent object. Use record_path to tell json_normalize() where the records are:

shape2.py Nested β†’ Flat with record_path
sku name price 0 PHN-1 iPhone 45000 1 PHN-2 Samsung 35000

Shape 3: Nested object with metadata

Orders contain items; items are nested. Use record_path for the array and meta to carry order-level fields into each row:

shape3.py Complex nested β†’ Flat with meta
sku qty price order_id region 0 P1 2 5000 O1 Nairobi 1 P2 1 3000 O1 Nairobi

Joseph's complete pipeline

Let's walk through the real workflow: fetch β†’ inspect β†’ flatten β†’ analyse.

πŸ“¦
Download sample data
A realistic Jumia orders JSON. Load it with json.load() or pd.read_json().

Step A: Fetch from API

Joseph's code connects to the Jumia internal API, passing an API key in the authorization header:

step_a.py Fetch from API with auth
What this shows: Always use environment variables for API keys (via os.environ), never paste them in code. Check status_code before assuming success. Common codes: 200 = OK, 401 = auth failed, 429 = rate-limited.

Step B: Inspect the raw JSON

Before flattening, see what you're working with. Print the structure, check keys:

step_b.py Inspect raw JSON
Top-level keys: dict_keys(['fetched_at', 'orders']) Number of orders: 20 First order keys: dict_keys(['order_id', 'customer_id', 'region', 'order_total_ksh', 'items']) First order items: [{'sku': 'PHN-XIA-12', 'product_name': 'Xiaomi Redmi 13', 'category': 'Phones & Accessories', 'qty': 1, 'price_ksh': 11500}, ...] First item: {'sku': 'PHN-XIA-12', 'product_name': 'Xiaomi Redmi 13', 'category': 'Phones & Accessories', 'qty': 1, 'price_ksh': 11500}

Step C: Flatten with json_normalize()

Now flatten: each item becomes a row, carrying the order_id, customer_id, and region from its parent:

step_c.py Flatten nested JSON
sku product_name category qty price_ksh order_id customer_id region
PHN-XIA-12 Xiaomi Redmi 13 Phones & Accessories 1 11500 JUM-KE-00012847 C-938211 Nairobi
ACC-CAS-02 Silicone case Phones & Accessories 1 900 JUM-KE-00012847 C-938211 Nairobi
FAS-TRO-45 Trouser - Navy Fashion 2 3500 JUM-KE-00012848 C-567834 Mombasa
PHN-SAM-21 Samsung Galaxy A15 Phones & Accessories 1 17800 JUM-KE-00012849 C-125697 Kisumu
ACC-TEM-01 Tempered glass Phones & Accessories 1 900 JUM-KE-00012849 C-125697 Kisumu
HOME-PIL-03 Pillow set Home & Living 1 3200 JUM-KE-00012850 C-849302 Nakuru
GRO-RIC-02 Basmati rice 5kg Groceries 1 4200 JUM-KE-00012854 C-738459 Mombasa
BEA-SHA-12 Shampoo 500ml Beauty 2 2400 JUM-KE-00012852 C-193847 Thika

Step D: Analyse

Now that data is flat, groupby becomes natural. Joseph groups by category to find his revenue drivers:

step_d.py Groupby revenue by category
Revenue by category (KSh): category Phones & Accessories 119600 Fashion 19900 Electronics 16800 Home & Living 14500 Beauty 13400 Groceries 10700 Revenue by region: region Nairobi 76200 Mombasa 48100 Eldoret 42000 Kisumu 30200 Nakuru 26000 Thika 21700
Revenue by category β€” Jumia Kenya sample
Horizontal bar chart showing revenue by product category with Phones & Accessories leading at 147.4k KSh (62% of revenue), followed by Electronics, Fashion, Home & Living, Beauty, and Groceries
What this shows: Phones & Accessories dominate revenue (35% of total), but look at the distribution: 6 categories, yet 1 drives over a third of revenue. If supply is constrained, stock Phones first.

Authentication & headers for real APIs

Most APIs require authentication. Never hardcode keys. Use environment variables:

auth.py Safe API authentication
Rule: Store API keys in a .env file or as shell environment variables. Add .env to .gitignore so you never accidentally commit it. Use python-dotenv to load at runtime.

Pagination and rate limits

APIs don't return all data at once. They paginate. Respect rate limits β€” add time.sleep() between requests:

pagination.py Paginate through API results
Best practice: Always add time.sleep() when paginating. Respect the API's rate limits (usually in response headers: X-RateLimit-Remaining). If you hit 429 (too many requests), back off exponentially: wait 1s, then 2s, then 4s.

Where you see this

Industries & use cases

πŸ›οΈ
E-commerce & Logistics
Jumia, Kilimall, Amazon APIs return nested order→items JSON. Flatten to build daily sales dashboards and inventory reports.
πŸ’°
Fintech & Payments
M-Pesa, Safaricom Daraja, Razorpay APIs return transaction trees. Use json_normalize to group by merchant or customer.
πŸ“±
Telecom & Mobile
Safaricom, Airtel APIs return call logs with nested call details. Flatten to calculate churn and ARPU by region.

How this connects to AI

Every ML pipeline starts with data. Most real-world data lives in APIs or databases, not CSV files. Before you can train a churn model or recommendation system, you need to fetch, flatten, and clean data. json_normalize() is your first tool after requests.get(). The faster you can turn raw API responses into a clean DataFrame, the faster you iterate on models.

data fetching API integration feature engineering

Mini Challenge

Task: Load the jumia_orders.json file. Flatten it with json_normalize() so each line item is a row. Then calculate total revenue per region and print the top 3 regions by revenue. Save the result to a CSV.

Hint: Use df.groupby('region')['price_ksh'].sum().sort_values(ascending=False).head(3) to find the top 3 regions. Then use .to_csv('region_revenue.csv') to save.

Interview questions

CSV is flat: one row = one record. JSON can be nested: objects inside objects, arrays inside objects. CSV is simple to load but loses structure. JSON preserves structure but needs flattening. Use CSV for tabular data; use JSON when data is hierarchical (orders β†’ items β†’ products).
If you hardcode your API key in a Python file and push to GitHub, anyone can see it and misuse it (DOS attacks, data theft, charges). Environment variables keep secrets out of version control. Use os.environ or a .env file with dotenv.
It flattens a nested JSON by exploding the 'items' array into rows, while carrying 'order_id' from the parent into each row. Example: if an order has 3 items, record_path='items' creates 3 rows, each with the same order_id but different item data.
A 401 means unauthorized β€” your API key is missing, invalid, or expired. Check: (1) Is the key in your environment? (2) Is it correct? (3) Has it expired? (4) Does the API endpoint require a different auth method (Bearer vs Basic)? Always log the error: print(response.text) to see the API's error message.
Parse the response, check for a 'next_page_url' field. If it exists, fetch from that URL. If it's null or missing, you're on the last page. Loop: fetch β†’ parse β†’ check for next_page_url β†’ repeat. Example: url = data.get('next_page_url'); while url: response = requests.get(url); data = response.json(); url = data.get('next_page_url').

Test your knowledge

Question 1 of 5
When loading JSON with nested arrays, which function do you use?
Question 2 of 5
What does record_path='items' do in json_normalize()?
Question 3 of 5
Why should you NOT hardcode API keys?
Question 4 of 5
What HTTP status code means you're sending requests too fast?
Question 5 of 5
When should you use json_normalize with meta=['order_id']?
0
/5
Excellent! You're ready to load APIs in production.

Cheatsheet

Task Code Notes
Load JSON from file import json; data = json.load(open('file.json')) Returns dict or list
Load JSON from API import requests; r = requests.get(url); data = r.json() Use timeout=10
Flatten JSON to DF pd.json_normalize(data) For flat or simple nesting
Flatten nested array pd.json_normalize(data, record_path='items') Explodes 'items' array into rows
Keep parent fields pd.json_normalize(data, record_path='items', meta=['order_id']) 'order_id' repeats in each row
Check response status if r.status_code == 200: data = r.json() 200 = OK, 401 = auth fail, 429 = rate limit
Add headers (auth) headers = {'Authorization': f'Bearer {key}'}; r = requests.get(url, headers=headers) Never hardcode the key
Load from .env from dotenv import load_dotenv; load_dotenv(); key = os.environ['KEY'] Protects secrets from version control
Add timeout requests.get(url, timeout=10) Prevents hanging on slow servers
Rate limit: sleep import time; time.sleep(0.2) Respect API rate limits; check headers

Common pitfalls

Assuming the response is always JSON
Always check response.status_code first. A 500 error returns HTML, not JSON. Call r.json() on 200 responses only.
Hardcoding API keys in source code
Use os.environ or a .env file. Add .env to .gitignore. Rotate keys regularly.
Not setting a timeout on requests
Add timeout=10. If the server doesn't respond in 10s, the request fails cleanly instead of hanging forever.
Ignoring rate limits
If you make 1000 requests per second, the API will return 429 (too many requests) or ban your IP. Add time.sleep(0.1) between paginated requests.
Forgetting to flatten nested arrays
If your JSON has an array inside an object, use pd.json_normalize(data, record_path='items'). Without it, the array stays as a column of lists β€” not a tidy DataFrame.
Africa & India's Best AI Academy
πŸ€– AI-first πŸŽ“ Placement Assistance

Ready to load data from anywhere?

Join the Best AI Program: The AI-Era Analyst β€” 5 months, fully online, with placement assistance. Week 1 is free.

Explore The AI-Era Analyst β†’