JSON and APIs: Turn an API Response into a Table

JSON and APIs - From Raw Text to a Clean Table 📦

8:40 a.m. The morning note goes out at nine, and your desk head wants yesterday's closing prices and volumes for the watchlist. The market-data vendor's API has already answered: one block of text full of braces and brackets. Copying numbers out of it by hand is how a typo ends up in front of a client. A few lines of Python turn that text into a clean table, every morning, the same way.

What it is: JSON (JavaScript Object Notation) is a plain-text format for structured data, standardised in RFC 8259. It has two containers: objects in curly braces (name/value pairs, every name a double-quoted string) and arrays in square brackets (ordered lists of values). Everything else is a simple value: a string in double quotes, a number, true, false or null. It is the usual format for web API responses.

🧠 The mental model: boxes inside boxes

A JSON response is a set of labelled boxes packed inside each other. To reach one value you write its path: a key for every object you open, a position (counting from 0) for every array you step into. payload["data"]["quotes"][0]["close"] reads aloud as: open data, open quotes, take the first record, read its close.

What the API actually sends

{
  "status": "ok",
  "meta": {"provider": "demo-feed", "currency": "EUR"},
  "data": {
    "quotes": [
      {"symbol": "ACME", "close": 42.5, "volume": 120000},
      {"symbol": "GLBX", "close": 118.2, "volume": 45000}
    ]
  }
}

The feed, tickers and prices are made up for this lesson, but the shape is the one you will meet everywhere: an envelope (status, metadata, sometimes paging) wrapped around the records you actually want. Your first job with any new API is to find the path to the records.

One call, one translation table

json.loads() (think "load string") parses the text into ordinary Python objects. Its partner json.dumps() goes the other way, from Python objects back to JSON text.

Navigating: keys for objects, positions for arrays

payload = json.loads(raw)
print(payload["meta"]["currency"])                # EUR
print(payload["data"]["quotes"][0]["symbol"])     # ACME, the first record

When the path is wrong, the error message names the rule you broke:

🔑 Check the envelope first. Some APIs report a problem inside the body rather than with an HTTP error code, so read payload["status"] before you trust the data. For a field that is sometimes missing, record.get("field") returns None instead of raising a KeyError.

From records to a table

A list of dicts is a table in disguise: each dict is a row, each key a column. f-string format specs line the columns up: :<8 left-aligns in 8 characters, :>8.2f right-aligns a number with 2 decimals, and :>10, right-aligns with a thousands separator.

for q in quotes:
    print(f"{q['symbol']:<8}{q['close']:>8.2f}{q['volume']:>10,}")

In the pandas track the same list becomes a DataFrame in one call, pd.DataFrame(quotes), but the navigation step is identical: you still have to reach the list first.

🔧 Try it for real

# pretty-print a real JSON response, the public GitHub API needs no key
curl -s https://api.github.com | python3 -m json.tool

With the requests library, response.json() does the json.loads() step for you and hands back the same dicts and lists.

Your Task

The vendor's response is already in raw. Parse it, reach the list of quotes, and print an aligned table plus the total volume. Predict first: after json.loads, what Python type is payload["data"]["quotes"], and what type is each item inside it?