Read JSON and API Data into pandas

You’ve just pulled a shiny new dataset from a REST API — maybe it’s user activity, stock prices, or weather logs — and it arrives as a tangled mess of nested JSON. Your first instinct might be to write a loop and manually extract every field. But that’s slow, error-prone, and completely unnecessary. When you learn how to read JSON and API data into pandas, you turn raw, nested API responses into clean tabular DataFrames in seconds. This lesson shows you exactly how to do that, step by step, so you can stop wrestling with JSON and start analyzing.

The problem this lesson solves

Every data scientist hits the same wall: the world doesn’t hand you tidy CSVs. Modern applications communicate through JSON — flexible, nested, and everywhere. REST APIs return JSON payloads that often look like a deep hierarchy of dictionaries and lists. You need to analyze that data, but pandas expects rows and columns, not nested chaos.

Manually extracting fields with loops is a trap: it’s brittle, and it breaks the moment the API changes its structure. You also lose the power of pandas — vectorized operations, filtering, groupby, and visualization — unless you get the data into a DataFrame. The pain is real, but the solution is surprisingly straightforward. This lesson teaches you how to read JSON and API data into pandas using pd.read_json() and related tools, turning raw API responses into analysis-ready DataFrames.

Core concept / mental model

Think of JSON as a tree. At the root, you have a single object (a Python dictionary) or an array (a Python list). Each node is either a scalar value (string, number, boolean), another dictionary, or a list of dictionaries. Your goal is to flatten that tree into a rectangular table — rows are records, columns are fields.

pandas gives you two main helpers: - pd.read_json() — reads a JSON string or file directly into a DataFrame. - pd.json_normalize() — flattens semi-structured JSON (nested dicts/lists) into a flat table.

Here’s the mental model: read_json is like a magic carpet that works when your JSON is already “tabular-like” (array of objects with flat keys). But the moment you have nesting — like {"user": {"name": "Alice"}} — you need json_normalize to unroll those inner branches into columns like user.name. It’s like unfolding a map: each nested level becomes a new set of columns.

Pro tip: Always inspect the JSON structure first. Use type() and print(json.dumps(data, indent=2)) to see the shape before choosing your loading method.

How it works step by step

Reading JSON into pandas is a three-step process:

1. Obtain the JSON — either from an API response, a local file, or a raw string.
2. Load it into pandas — use pd.read_json() for simple cases or pd.json_normalize() for nested structures.
3. Inspect and clean — check the resulting DataFrame’s shape, columns, and dtypes. Handle missing values if needed.

Let’s break down each step with a practical example.

Step 1: From API response to JSON string

When you call an API with requests.get(), you get a Response object. The .json() method parses it into Python dictionaries and lists. But you can also pass the response text directly to pd.read_json(), which accepts a JSON string.

Step 2: Simple JSON to DataFrame

If your JSON is an array of objects with flat fields, pd.read_json() works out of the box:

import pandas as pd

# Simulated API response (flat JSON)
json_string = '''
[
  {"city": "Austin", "temp_f": 72, "humidity": 45},
  {"city": "Denver", "temp_f": 68, "humidity": 52},
  {"city": "Miami", "temp_f": 85, "humidity": 80}
]
'''

df = pd.read_json(json_string)
print(df)

Output:

     city  temp_f  humidity
0  Austin      72        45
1  Denver      68        52
2  Miami       85        80

Step 3: From an actual API call

Here’s a realistic example using the JSONPlaceholder API (a free test API):

import pandas as pd
import requests

url = "https://jsonplaceholder.typicode.com/posts"
response = requests.get(url)
response.raise_for_status()  # Raise an error for bad status codes

df = pd.read_json(response.text)
print(df.head())  # posts have id, userId, title, body

Output (first few rows):

   userId  id  ...  body
0       1   1  ...  "quia et suscipit..."
1       1   2  ...  "est rerum tempore..."
2       1   3  ...  "et ea vero quia..."

Hands-on walkthrough

Let’s go deeper with a nested JSON example — the kind that breaks pd.read_json(). We’ll use json_normalize to flatten it.

Nesting: When read_json Almost Works

Consider an API that returns users with nested address and company objects:

{
  "users": [
    {"id": 1, "name": "Leanne Graham", "address": {"city": "Gwenborough", "zipcode": "92998-3874"}, "company": {"name": "Romaguera-Crona"}},
    {"id": 2, "name": "Ervin Howell", "address": {"city": "Wisokyburgh", "zipcode": "90566-7771"}, "company": {"name": "Deckow-Crist"}}
  ]
}

If you just call df = pd.read_json(json_data), you get a DataFrame with a column users containing dictionaries — not useful. Instead, target the users key and normalize:

import pandas as pd
import requests

# Fetch sample nested data
response = requests.get("https://jsonplaceholder.typicode.com/users")
users_json = response.json()  # This is already a list of dicts with nested fields

df = pd.json_normalize(users_json)

# Now columns are flattened with dot notation
print(df.columns.tolist())
print(df[['name', 'address.city', 'company.name']].head())

Output:

['id', 'name', 'username', 'email', 'address.street', 'address.suite', 'address.city', 'address.zipcode', 'address.geo.lat', 'address.geo.lng', 'phone', 'website', 'company.name', 'company.catchPhrase', 'company.bs']

               name address.city     company.name
0  Leanne Graham   Gwenborough  Romaguera-Crona
1  Ervin Howell   Wisokyburgh    Deckow-Crist

Handling arrays inside JSON

Sometimes a field is a list of primitive values or even a list of objects. For a list of objects, you might need record_path:

# Simulated JSON with orders, each order has items (list of dicts)
orders_json = [
    {"order_id": 1, "customer": "Alice", "items": [{"sku": "A1", "qty": 2}, {"sku": "B2", "qty": 1}]},
    {"order_id": 2, "customer": "Bob", "items": [{"sku": "C3", "qty": 5}]}
]

# Normalize the main order info, then explode items
orders = pd.json_normalize(orders_json, sep='_')
items = orders.explode('items').reset_index(drop=True)
items = pd.json_normalize(items['items']).add_prefix('item_')

# Combine if needed
final = pd.concat([orders.drop('items', axis=1), items], axis=1)

print(final)

Output:

   order_id customer item_sku  item_qty
0         1    Alice       A1         2
1         1    Alice       B2         1
2         2      Bob       C3         5

Compare options / when to choose what

Not all JSON is the same. Here’s a quick table to help you pick the right tool:

Pro tip: If your JSON is deeply nested and you need only a subset, consider passing record_path and meta parameters to pd.json_normalize() to control exactly what gets flattened.

Troubleshooting & edge cases

Even with the right tools, you’ll hit snags. Here are the most common ones:

• ValueError: Arrays must be all same length — This happens when pd.read_json() tries to infer tabular structure from JSON where records have varying keys. Solution: switch to pd.json_normalize() or pre-process the JSON to ensure consistent fields.

• TypeError: string indices must be integers — You passed a JSON string instead of a parsed dict/list to pd.json_normalize(). Remember to call .json() on the response first.

• Nested lists become object columns — If you see columns like items containing lists, you need to explode() them or normalize separately.

• API returns a single object instead of an array — Wrap it in a list: pd.json_normalize([data]) to treat it as a single row.

• Character encoding issues — Some APIs return JSON with UTF-8 BOM. Use response.json() with encoding or pass encoding='utf-8-sig' if reading from a file.

What you learned & what's next

You now know how to read JSON and API data into pandas. You mastered pd.read_json() for flat data and pd.json_normalize() for nested structures, and you practiced handling real API responses. That’s the core of turning raw web data into analysis-ready tables.

Next up in the Python for data science track: you’ll move from data loading to data cleaning and reshaping. You’ll apply these skills to merge, filter, and transform your DataFrames. Keep this lesson in mind — every future dataset might come from a JSON endpoint, and you’ll know exactly how to tame it.