← All guides

A practice REST API for pandas & Jupyter — read_json 403s, real pagination, typed DataFrames

Every pandas course reaches the same lesson: "now load data from an API." And then reality intrudes. The tutorial API is a fixed dataset that doesn't match your schema, writes are faked, half the "free JSON APIs" are dead, and the very first line — pd.read_json(url) — blows up with an error that has nothing to do with your code. This page is the version of that lesson where everything actually runs: a live API you can read, filter, paginate, write to, break on purpose, and replace with your own data via one CSV upload. Every snippet below was executed verbatim against the live service before publishing (verified note).

1. The trap first: why pd.read_json(url) throws FileNotFoundError or HTTP 403

You will hit this on many real APIs, so let's hit it on purpose. Point read_json straight at a URL that works fine in your browser and in curl:

import pandas as pd
df = pd.read_json("https://mockbird.mockbird.workers.dev/m/demo/products")

On pandas 3.x this raises the spectacularly misleading

FileNotFoundError: File https://mockbird.mockbird.workers.dev/m/demo/products does not exist

and on pandas 2.x the slightly-more-honest

urllib.error.HTTPError: HTTP Error 403: Forbidden

(We reproduced both, on pandas 3.0.6 and 2.3.3.) The URL exists. Nothing is forbidden about your data. What's happening: read_json fetches URLs through Python's urllib, whose default User-Agent is Python-urllib/3.x — and the bot protection on many CDN-fronted hosts (including the Cloudflare edge our own demo sits behind) rejects that UA outright with a 403, which pandas 3 then wraps as "file does not exist". Two one-line fixes, both verified:

# Fix A — tell read_json to send a real User-Agent
df = pd.read_json(
    "https://mockbird.mockbird.workers.dev/m/demo/products",
    storage_options={"User-Agent": "pandas-tutorial"},
)

# Fix B — fetch with requests (browser-grade defaults), parse from memory
import io, requests
r = requests.get("https://mockbird.mockbird.workers.dev/m/demo/products")
df = pd.read_json(io.StringIO(r.text))

Fix B is the one to internalize: the moment you need query params, headers, status-code checks or pagination — i.e. immediately — requests is the right layer, and pandas is happy to build a frame from r.json() directly: pd.DataFrame(r.json()).

2. A live DataFrame in 10 seconds (no signup)

The public demo project serves 30 seeded products. In a notebook cell:

import pandas as pd, requests

BASE = "https://mockbird.mockbird.workers.dev/m/demo"

rows = requests.get(f"{BASE}/products", params={"limit": 100}, timeout=10).json()
df = pd.DataFrame(rows)
df.dtypes

id               int64
name            string
description     string
price          float64
image           string
category        string
inStock           bool
rating         float64

Note what did not happen: no astype() incantations, no "True" strings pretending to be booleans, no prices arriving as text. JSON carries types, the API stores real ints/floats/bools, and pandas infers the rest. If you learned data loading from CSVs, this is the workflow difference an API teaches: typed records over the wire. (Dates arrive as ISO-8601 strings — convert with pd.to_datetime(df["createdAt"]) as usual.)

3. Push work to the server before pandas ever sees it

A real API lets you filter, sort and project server-side — worth practicing, because on a 10-million-row production API "download everything, then df[df.price > 300]" is not a plan:

r = requests.get(f"{BASE}/products", params={
    "price_gte": 300,          # range filters: _gte/_lte/_gt/_lt/_ne/_like
    "sortBy": "price",
    "order": "desc",
    "select": "name,price,category",   # column projection at the source
    "limit": 5,
})
pd.DataFrame(r.json())

Verified: five rows, prices descending from 966.08, and only id/name/price/category columns come back. The full query toolkit (search, filters on any field, pagination) is in the docs.

4. The pagination loop every API course skips

Real APIs page. The classic beginner bug is analyzing page 1 and believing it's the dataset. Every list response here carries X-Total-Count, so you can write the honest loop:

frames, page = [], 1
while True:
    r = requests.get(f"{BASE}/products", params={"page": page, "limit": 10})
    batch = r.json()
    if not batch:
        break
    frames.append(pd.DataFrame(batch))
    total = int(r.headers["X-Total-Count"])
    if page * 10 >= total:
        break
    page += 1

df = pd.concat(frames, ignore_index=True)
len(df)   # 30 — three pages of 10, verified

5. The payoff: real analysis on live data

df.groupby("category")["price"].agg(["count", "mean", "min", "max"]).round(2)

             count    mean     min     max
category
beauty           2  677.52  435.06  919.98
books            5  769.83  346.21  966.08
clothing         6  485.96  150.11  886.64
electronics      5  465.74   72.21  787.27
home             6  656.84  396.31  873.65
office           2  280.94   34.50  527.38
sports           2  513.60  344.42  682.77
toys             2  305.05  114.28  495.82

That table is the actual output from the run that verified this page. The demo reseeds daily with fresh random values, so your numbers will differ — which is itself useful: re-running a notebook against changing data is what production feels like. Need numbers that don't move between runs? Create your own project and pin a snapshot.

6. Joined data and json_normalize

APIs return nested objects; pandas wants flat columns. Ask the API to join orders to their customers (_expand), then flatten:

rows = requests.get(f"{BASE}/orders", params={"_expand": "customer", "limit": 3}).json()
flat = pd.json_normalize(rows)
[c for c in flat.columns if "." in c][:4]
# ['customer.id', 'customer.firstName', 'customer.lastName', 'customer.email']

7. Writes that actually persist (POST a DataFrame)

Fixed tutorial APIs fake their writes — POST returns an id, then the record was never stored. Here writes are real, which means you can practice the other half of the job: pushing cleaned rows back out.

new = pd.DataFrame([
    {"name": "Test Widget", "price": 19.99, "category": "office", "inStock": True},
])
for row in new.to_dict("records"):
    r = requests.post(f"{BASE}/products", json=row)
    r.raise_for_status()
    created = r.json()          # 201, {'id': 31, ...} in our run

requests.get(f"{BASE}/products/{created['id']}").json()["name"]   # 'Test Widget'

Verified: created id 31, read it back, then deleted it with requests.delete(...) — tidy up after yourself in the shared demo, or better, use your own project (next section) where nobody else's notebook is writing.

8. Your own schema: df.to_csv() → a typed API, one request

The demo is our schema. The real unlock is practicing against yours. Any DataFrame becomes a hosted, typed REST API in one request — no signup:

df = pd.DataFrame({
    "city":     ["Lisbon", "Oslo", "Kyoto", "Quito"],
    "temp_c":   [24.5, 11.2, 19.8, 15.0],
    "humidity": [61, 72, 55, 80],
    "coastal":  [True, False, False, False],
})

r = requests.post(
    "https://mockbird.mockbird.workers.dev/api/projects/import?resource=readings",
    data=df.to_csv(index=False),
    headers={"Content-Type": "text/csv"},
)
proj = r.json()                  # 201 → {'id': ..., 'adminKey': ..., ...}
api = f"https://mockbird.mockbird.workers.dev/m/{proj['id']}"

Column types are inferred per-column from the values — our run came back with temp_c: float64, humidity: int64, coastal: bool (not strings!), plus an auto id. And it's a full API immediately: GET {api}/readings?humidity_gte=60 returned exactly the 3 matching rows. Save proj["adminKey"] — it's the only credential for managing the project. CSV details and typing rules: CSV → REST API guide. Prefer a UI? Paste the CSV in the dashboard.

9. Failure drills: the notebook cells that save you in production

Query params flip the API into misbehaving on demand, so error handling stops being theoretical:

# 500s — practice raise_for_status
r = requests.get(f"{BASE}/products", params={"mock_status": 500})
r.status_code            # 500
r.raise_for_status()     # requests.HTTPError: 500 Server Error

# Slow responses — practice timeouts (3s delay vs 1.5s budget)
requests.get(f"{BASE}/products", params={"mock_delay": 3000}, timeout=1.5)
# requests.exceptions.Timeout after ~1.5s

# Deterministic retry drill: fails twice, then succeeds — every run
import time
def fetch_with_retry(url, params, tries=4):
    for attempt in range(tries):
        r = requests.get(url, params=params)
        if r.status_code < 500:
            return r
        time.sleep(2 ** attempt * 0.5)   # 0.5s, 1s, 2s...
    r.raise_for_status()

r = fetch_with_retry(f"{BASE}/products",
                     {"mock_seq": "503,503,200", "mock_seq_key": "notebook-1"})
r.status_code            # 200, after exactly two 503s — verified

mock_seq serves that status sequence in order per key, so your backoff logic is testable instead of "run it and hope". Full simulation toolkit (jitter, chaos percentages, per-request scenarios): loading & error states guide.

10. Honest comparison: where you should practice instead

To be clear about where others win: for learning pandas itself — groupby semantics, reshaping, plotting — offline built-in datasets are strictly better (no network, no flakiness, richer data). Kaggle beats everyone for real-world mess. Use this when the thing you're practicing is the pipeline: fetch, paginate, validate, handle failure, write back — with data shaped like the project you're actually building.

Verified: every snippet on this page was executed verbatim against the production service on Sep 22, 2026, with pandas 3.0.6 + requests 2.31 on Python 3.12 (the §1 error shapes additionally reproduced on pandas 2.3.3): both read_json failures and both fixes, the dtype listing, server-side filter/sort/select outputs, the 3-page pagination loop (30 rows, header-checked), the groupby table (pasted from output), json_normalize columns, the POST→read-back→DELETE cycle (id 31), the CSV import round-trip (typed float64/int64/bool, _gte filter returning 3 rows, project deleted after), and the timeout + mock_seq 503,503,200 retry drills. The only substitution: our own runs carry a self-test header so they're excluded from usage stats.