Data Analysis with Python (pandas, polars, DuckDB) Module 3 – Loading data: CSV, Excel, JSON, Parquet and databases

JSON, APIs and nested records

Load JSON, JSON Lines and saved API responses into pandas: flatten nested records with json_normalize, explode lists and keep large IDs exact.

Intermediate
30 minutes
Examples run with Python 3.14.8, pandas 3.0.6, duckdb 1.5.6 and polars 1.44.2
By MySmartCoPilot

What you will learn

Load JSON arrays, JSON Lines and saved API responses
Convert nested records into flat tables with json_normalize, record_path and meta
Split list columns into rows with explode while keeping the parent key
Decide when to keep nested data as structs in polars or DuckDB instead of flattening

Before you start

Reading messy CSV files with pandas

What you’ll learn · Before you start

What you will learn

Load JSON arrays, JSON Lines and saved API responses
Convert nested records into flat tables with json_normalize, record_path and meta
Split list columns into rows with explode while keeping the parent key
Decide when to keep nested data as structs in polars or DuckDB instead of flattening

Before you start

Reading messy CSV files with pandas

On this page

JSON from an API is a tree: objects inside objects, and lists of records inside records. A DataFrame is a flat table. Getting from one to the other takes a few tools. pd.json_normalize turns nested objects into columns with dotted names such as customer.address.city. With record_path and meta, it turns a list inside each record, such as an order’s items, into a table of its own that keeps the parent’s key on every row. DataFrame.explode turns a column of lists into one row per element. JSON Lines files, one object per line, read with pd.read_json(..., lines=True).

Two traps catch most people: integer IDs larger than about nine quadrillion can silently change their value, and records with an empty list disappear from the flattened table. This lesson flattens one saved API response into three tables, checks them against each other, and shows when it is better not to flatten at all.

The shapes JSON arrives in

An array of records, [{...}, {...}]: the friendliest shape, one object per row.
An envelope: an object with paging fields such as page and next_page around the array of records, which is how many APIs answer.
JSON Lines: one complete object per line, with no surrounding array. Log exports and data dumps use it because a program can append a line or read the file a few lines at a time.
Nesting inside a record: an object (a customer with an address) or a list of records (an order’s items).

A saved API response

Every example here reads the same response, saved to a file. Working from a saved copy is a good habit in any case: the analysis can be repeated exactly, nobody’s rate limit is spent while you experiment, and the examples on this page never touch the network. This is page 1 of an invented shop’s orders endpoint:

orders_response.json JSON · orders_response.json

{
  "page": 1,
  "next_page": null,
  "orders": [
    {
      "order_id": "A-1001",
      "status": "delivered",
      "customer": {"id": 9007199254740993, "name": "Asha", "address": {"city": "Pune", "postcode": "411001"}},
      "items": [
        {"sku": "TEA-250", "qty": 2, "price": 180.0},
        {"sku": "MUG-01", "qty": 1, "price": 350.0}
      ],
      "payments": [{"method": "card", "amount": 710.0}]
    },
    {
      "order_id": "A-1002",
      "status": "delivered",
      "customer": {"id": 9007199254740995, "name": "Ben", "address": {"city": "Leeds"}},
      "items": [{"sku": "COF-500", "qty": 1, "price": 620.0}],
      "payments": [
        {"method": "voucher", "amount": 300.0},
        {"method": "card", "amount": 320.0}
      ]
    },
    {
      "order_id": "A-1003",
      "status": "cancelled",
      "customer": {"id": null, "name": "Chen", "address": {"city": "Lima", "postcode": "15001"}},
      "items": [],
      "payments": []
    }
  ]
}

Load it with Python’s json module and pass the list of records to pd.DataFrame:

A DataFrame of dictionaries Python · first_look.py

import json

import pandas as pd

with open("orders_response.json", encoding="utf-8") as f:
    response = json.load(f)

print(list(response))  # the envelope: paging fields around the records
orders = pd.DataFrame(response["orders"])
print(orders.dtypes, end="\n\n")
print(orders[["order_id", "customer"]].to_string(max_colwidth=60))

Output

['page', 'next_page', 'orders']
order_id       str
status         str
customer    object
items       object
payments    object
dtype: object

  order_id                                                     customer
0   A-1001  {'id': 9007199254740993, 'name': 'Asha', 'address': {'ci...
1   A-1002  {'id': 9007199254740995, 'name': 'Ben', 'address': {'cit...
2   A-1003  {'id': None, 'name': 'Chen', 'address': {'city': 'Lima',...

Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 first_look.py

The envelope’s paging fields are not records, so the DataFrame is built from response["orders"] only. Flat fields such as order_id became proper str columns, but customer, items and payments are object columns holding whole dictionaries and lists. You cannot filter by city or add up prices in that state.

Nested objects become columns: json_normalize

pd.json_normalize walks into nested objects and makes one column per leaf, joining the keys with a dot:

Dotted column names Python · normalize_orders.py

import json

import pandas as pd

with open("orders_response.json", encoding="utf-8") as f:
    orders = json.load(f)["orders"]

flat = pd.json_normalize(orders)
print(flat.columns.tolist(), end="\n\n")
print(flat[["order_id", "customer.name", "customer.address.city", "customer.address.postcode"]], end="\n\n")

# max_level stops the flattening early; sep chooses the separator in the new names.
shallow = pd.json_normalize(orders, max_level=1, sep="_")
print(shallow.columns.tolist())

Output

['order_id', 'status', 'items', 'payments', 'customer.id', 'customer.name', 'customer.address.city', 'customer.address.postcode']

  order_id customer.name customer.address.city customer.address.postcode
0   A-1001          Asha                  Pune                    411001
1   A-1002           Ben                 Leeds                       NaN
2   A-1003          Chen                  Lima                     15001

['order_id', 'status', 'items', 'payments', 'customer_id', 'customer_name', 'customer_address']

Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 normalize_orders.py

Ben’s address has no postcode, and the flattened table simply shows NaN there: a key that some records lack does not stop the flattening. Lists are left alone; items and payments still hold lists. max_level=1 stops after one level of nesting, which keeps customer_address as a dictionary, and sep="_" gives names that are easier to type than dotted ones.

One nested response, three flat tables

Text description of the diagram

The diagram starts with orders_response.json, whose envelope holds page, next_page and a list of orders. Each order has an order_id, a status, a nested customer object with a nested address, and two lists: items and payments.

Three tables come out of it:

The orders table, one row per order, from json_normalize(orders): order_id, status, customer.name and customer.address.city.
The items table, one row per item, from json_normalize with record_path="items" and meta=["order_id"]: sku, qty, price and the order_id of the parent order.
The payments table, one row per payment, from record_path="payments" with the same meta: method, amount and order_id.

The items and payments tables are then reconciled by order_id: for every order, the sum of qty times price must equal the sum of the payment amounts.

Lists of records become tables: record_path and meta

record_path="items" makes one row per element of each order’s items list, and meta copies chosen fields of the parent onto each of those rows, so every item still knows its order. A nested parent field is written as a list, such as ["customer", "name"]:

Items, payments and a check that they agree Python · items_and_payments.py

import json

import pandas as pd

with open("orders_response.json", encoding="utf-8") as f:
    orders = json.load(f)["orders"]

# One row per item, with fields of the parent order copied onto each row.
items = pd.json_normalize(orders, record_path="items", meta=["order_id", ["customer", "name"]])
print(items, end="\n\n")

# One row per payment; record_prefix keeps the names apart from the order's own fields.
payments = pd.json_normalize(orders, record_path="payments", meta=["order_id"], record_prefix="pay_")
print(payments, end="\n\n")

# Reconcile: every order's items must add up to what was paid for it.
order_list = pd.json_normalize(orders)[["order_id", "status"]]
items["line_total"] = items["qty"] * items["price"]
check = (
    order_list
    .merge(items.groupby("order_id", as_index=False)["line_total"].sum(), on="order_id", how="left")
    .merge(payments.groupby("order_id", as_index=False)["pay_amount"].sum(), on="order_id", how="left")
    .fillna({"line_total": 0, "pay_amount": 0})
)
check["matches"] = check["line_total"] == check["pay_amount"]
print(check)

Output

       sku  qty  price order_id customer.name
0  TEA-250    2  180.0   A-1001          Asha
1   MUG-01    1  350.0   A-1001          Asha
2  COF-500    1  620.0   A-1002           Ben

  pay_method  pay_amount order_id
0       card       710.0   A-1001
1    voucher       300.0   A-1002
2       card       320.0   A-1002

  order_id     status  line_total  pay_amount  matches
0   A-1001  delivered       710.0       710.0     True
1   A-1002  delivered       620.0       620.0     True
2   A-1003  cancelled         0.0         0.0     True

Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 items_and_payments.py

Look at what is missing: order A-1003 was cancelled, its items list is empty, so it has no row in the items table and none in the payments table. That is correct for those tables, but a report built from them alone would never mention the order. The reconciliation therefore starts from the list of all orders and joins the item and payment totals onto it, filling the gaps with 0. Every order’s items match its payments, and the check would flag the first order where they did not.

Two more options matter in real responses. record_prefix="pay_" renames the record’s columns, so a field called amount cannot collide with a parent field of the same name. And when a meta field is missing from some records, json_normalize raises a KeyError that names the field; errors="ignore" fills it with NaN instead. Use it only once you know the field is optional.

Lists in cells become rows: explode

explode is the general tool for a column whose cells hold lists. Each element becomes a row, and the other columns are repeated:

One row per item, then one column per field Python · explode_lists.py

import json

import pandas as pd

with open("orders_response.json", encoding="utf-8") as f:
    orders = pd.DataFrame(json.load(f)["orders"])

# explode: one row per list element; the parent's index repeats, and an empty list gives one row of NaN.
rows = orders[["order_id", "items"]].explode("items", ignore_index=True)
print(rows, end="\n\n")

# The items are still dictionaries. Flatten them and put them beside the parent key.
details = pd.json_normalize(rows["items"].dropna().tolist())
lines = rows.dropna(subset=["items"]).reset_index(drop=True)[["order_id"]].join(details)
print(lines)

Output

  order_id                                         items
0   A-1001  {'sku': 'TEA-250', 'qty': 2, 'price': 180.0}
1   A-1001   {'sku': 'MUG-01', 'qty': 1, 'price': 350.0}
2   A-1002  {'sku': 'COF-500', 'qty': 1, 'price': 620.0}
3   A-1003                                           NaN

  order_id      sku  qty  price
0   A-1001  TEA-250    2  180.0
1   A-1001   MUG-01    1  350.0
2   A-1002  COF-500    1  620.0

Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 explode_lists.py

An empty list becomes one row with NaN, so A-1003 is still visible after explode. That differs from record_path, which drops it.
Without ignore_index=True, the original index labels repeat (0, 0, 1, 2), and later joins on the index can attach values to the wrong rows. Number the rows afresh, or join on a key column.
After exploding, the cells hold dictionaries; json_normalize on that list of dictionaries gives the columns, and join puts them beside the parent key.

JSON Lines

A JSON Lines file reads with lines=True. Every line is one record, so the result is a flat table whenever the records are flat, as in this event log:

events.jsonl JSONL · events.jsonl

{"event_id": 1, "user": "u17", "type": "view", "screen": "home", "ms": 412}
{"event_id": 2, "user": "u17", "type": "click", "screen": "home", "ms": 95}
{"event_id": 3, "user": "u08", "type": "view", "screen": "menu", "ms": 1310}
{"event_id": 4, "user": "u08", "type": "purchase", "screen": "cart", "ms": 2440, "amount": 640.0}
{"event_id": 5, "user": "u21", "type": "view", "screen": "home", "ms": 388}
{"event_id": 6, "user": "u21", "type": "click", "screen": "menu", "ms": 120}
{"event_id": 7, "user": "u17", "type": "purchase", "screen": "cart", "ms": 1985, "amount": 350.0}
{"event_id": 8, "user": "u33", "type": "view", "screen": "home", "ms": 505}

The same totals in pandas and DuckDB Python · json_lines.py

import duckdb
import pandas as pd

# JSON Lines: one JSON object per line, so a file can be appended to and read in pieces.
events = pd.read_json("events.jsonl", lines=True)
print(events.dtypes, end="\n\n")
in_pandas = events.groupby("type").agg(events=("event_id", "count"), amount=("amount", "sum"))
print(in_pandas, end="\n\n")

in_duckdb = duckdb.sql("""
    SELECT type, count(*) AS events, coalesce(sum(amount), 0) AS amount
    FROM read_json_auto('events.jsonl')
    GROUP BY type ORDER BY type
""").df().set_index("type")
print(in_duckdb, end="\n\n")
print("same totals:", in_pandas["events"].tolist() == in_duckdb["events"].tolist()
      and in_pandas["amount"].tolist() == in_duckdb["amount"].tolist())

# A big JSON Lines file can be read in chunks, like a CSV.
with pd.read_json("events.jsonl", lines=True, chunksize=3) as reader:
    print("rows per chunk:", [len(chunk) for chunk in reader])

Output

event_id      int64
user            str
type            str
screen          str
ms            int64
amount      float64
dtype: object

          events  amount
type                    
click          2     0.0
purchase       2   990.0
view           4     0.0

          events  amount
type                    
click          2     0.0
purchase       2   990.0
view           4     0.0

same totals: True
rows per chunk: [3, 3, 2]

Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 json_lines.py

Only the purchase events have an amount, and the other rows got NaN in that column. pandas and DuckDB agree on every count and every total; such a cross-check with a second engine costs one query and catches parsing surprises early. Like read_csv, read_json can read a large file in pieces with chunksize, but only together with lines=True.

Large integer IDs change silently

JSON has a single number type, and many programs store every JSON number as a 64-bit floating-point value, which holds integers exactly only up to 2**53, about nine quadrillion. RFC 8259, the JSON standard, says as much: integers beyond that range are not guaranteed to mean the same in every implementation. pandas keeps such IDs exact as long as a column has no missing values. One null turns the column into float64, and the IDs change:

An ID that changes by one Python · big_ids.py

import io
import json

import duckdb
import pandas as pd
import polars as pl

# Customer ids from an API: larger than 2**53, and one is missing.
text = (
    '{"customer_id": 9007199254740993, "event": "open"}\n'
    '{"customer_id": null, "event": "open"}\n'
    '{"customer_id": 9007199254740995, "event": "buy"}\n'
)
with open("events_ids.jsonl", "w", encoding="utf-8") as f:
    f.write(text)

print("read_json:          ", pd.read_json(io.StringIO(text), lines=True)["customer_id"].tolist())
print('dtype="Int64":      ', pd.read_json(io.StringIO(text), lines=True, dtype={"customer_id": "Int64"})["customer_id"].tolist())

records = [json.loads(line) for line in text.splitlines()]
exact = pd.DataFrame(records, dtype="object").astype({"customer_id": "Int64"})
print("json module + Int64:", exact["customer_id"].tolist())

print("DuckDB .df():       ", duckdb.sql("SELECT * FROM read_json_auto('events_ids.jsonl')").df()["customer_id"].tolist())
events = pl.read_ndjson("events_ids.jsonl")
print("polars:             ", events["customer_id"].to_list())
print("polars .to_pandas():", events.to_pandas()["customer_id"].tolist())

Output

read_json:           [9007199254740992.0, nan, 9007199254740996.0]
dtype="Int64":       [9007199254740992, <NA>, 9007199254740996]
json module + Int64: [9007199254740993, <NA>, 9007199254740995]
DuckDB .df():        [9007199254740993, <NA>, 9007199254740995]
polars:              [9007199254740993, None, 9007199254740995]
polars .to_pandas(): [9007199254740992.0, nan, 9007199254740996.0]

Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 big_ids.py

read_json changed …993 to …992 and …995 to …996. Asking for dtype="Int64" does not help, because the values were already floats when the dtype was applied; the wrong numbers merely lose their decimal point.
Python’s json module reads integers exactly. Building the frame with dtype="object" and converting the column to pandas’ nullable Int64 keeps every digit; so does keeping IDs as text.
DuckDB reads the column as BIGINT and hands pandas a nullable Int64 column, exactly. polars reads it exactly too, but its plain to_pandas() converts to float64 again.

The safest rule is the one many APIs follow: treat IDs as strings. You never do arithmetic on an ID, and a string cannot lose a digit.

When to keep the data nested

Flattening is not always the goal. Both polars and DuckDB store nested JSON as typed nested columns: a Struct (an object with named fields) and a List. They flatten only what a query needs, so the data stays in one table and no join can duplicate or lose rows:

Nested columns in DuckDB and polars Python · keep_nested.py

import json

import duckdb
import polars as pl

# DuckDB reads the nested response as STRUCT and LIST columns and flattens only what a query needs.
print(duckdb.sql("""
    WITH o AS (SELECT unnest(orders) AS o FROM read_json_auto('orders_response.json'))
    SELECT o.order_id,
           o.customer.address.city AS city,
           len(o.items) AS n_items,
           coalesce(list_sum(list_transform(o.items, i -> i.qty * i.price)), 0) AS items_total
    FROM o ORDER BY o.order_id
"""))

# polars keeps the same shapes as Struct and List columns.
with open("orders_response.json", encoding="utf-8") as f:
    orders = pl.DataFrame(json.load(f)["orders"])
print(orders.schema["items"])
# empty_as_null=False: an order with no items gives no row (polars 2.0's default; 1.x makes a row of nulls).
items = orders.select("order_id", "items").explode("items", empty_as_null=False).unnest("items")
print(items)

Output

┌──────────┬─────────┬─────────┬─────────────┐
│ order_id │  city   │ n_items │ items_total │
│ varchar  │ varchar │  int64  │   double    │
├──────────┼─────────┼─────────┼─────────────┤
│ A-1001   │ Pune    │       2 │       710.0 │
│ A-1002   │ Leeds   │       1 │       620.0 │
│ A-1003   │ Lima    │       0 │         0.0 │
└──────────┴─────────┴─────────┴─────────────┘

List(Struct({'sku': String, 'qty': Int64, 'price': Float64}))
shape: (3, 4)
┌──────────┬─────────┬─────┬───────┐
│ order_id ┆ sku     ┆ qty ┆ price │
│ ---      ┆ ---     ┆ --- ┆ ---   │
│ str      ┆ str     ┆ i64 ┆ f64   │
╞══════════╪═════════╪═════╪═══════╡
│ A-1001   ┆ TEA-250 ┆ 2   ┆ 180.0 │
│ A-1001   ┆ MUG-01  ┆ 1   ┆ 350.0 │
│ A-1002   ┆ COF-500 ┆ 1   ┆ 620.0 │
└──────────┴─────────┴─────┴───────┘

Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 keep_nested.py

DuckDB reaches into a struct with dots (o.customer.address.city), counts list elements with len, and computes each order’s total with list functions without creating a row per item. polars shows the items column as a List(Struct(...)), and explode followed by unnest gives the same three item rows as record_path. In the pinned polars 1.44, explode turns an empty list into a row of nulls by default and warns that polars 2.0 will change that default; empty_as_null=False asks for the new behaviour, so the cancelled order gives no row.

Three ways to handle nested JSON
Criterion	json_normalize	explode	Nested columns
Objects	Dotted columns	Left as dicts	Struct fields
Lists	A table each	A row each	List columns
Empty list	No row	A NaN row	Your choice
Big IDs	Can change	As loaded	Exact
When to choose	Tables for pandas	One list column	Big or deep files

Your turn: courses, batches and students

A training institute’s API returns courses, each with batches, each with students. Write flatten_courses(path) so that it returns one row per student with the columns course_id, title, batch_id, mode, roll_no, name and email:

exercise/courses.json JSON · exercise/courses.json

[
  {"course_id": "PY101", "title": "Python basics", "batches": [
    {"batch_id": "PY101-A", "mode": "online", "students": [
      {"roll_no": "0042", "name": "Anil", "email": "anil@example.com"},
      {"roll_no": "0107", "name": "Bea"}
    ]},
    {"batch_id": "PY101-B", "mode": "campus", "students": []}
  ]},
  {"course_id": "DA201", "title": "Data analysis", "batches": [
    {"batch_id": "DA201-A", "mode": "online", "students": [
      {"roll_no": "0215", "name": "Chidi", "email": "chidi@example.com"},
      {"roll_no": "0009", "name": "Divya", "email": "divya@example.com"}
    ]}
  ]},
  {"course_id": "ML301", "batches": [
    {"batch_id": "ML301-A", "mode": "campus", "students": [
      {"roll_no": "0311", "name": "Emre"}
    ]}
  ]}
]

exercise/flatten_courses.py (your starting point) Python · exercise/flatten_courses.py

import json

import pandas as pd


def flatten_courses(path):
    """Return one row per student, with the student's course and batch."""
    with open(path, encoding="utf-8") as f:
        courses = json.load(f)
    return pd.json_normalize(courses)  # flattens the top level only: the batches stay lists

exercise/check_courses.py, run on the starting point Python · exercise/check_courses.py

import pandas as pd

from flatten_courses import flatten_courses

students = flatten_courses("courses.json")
COLUMNS = ["course_id", "title", "batch_id", "mode", "roll_no", "name", "email"]


def check(name, test):
    try:
        ok = bool(test())
    except Exception:  # a missing column, for example
        ok = False
    print("PASS" if ok else "FAIL", name)
    return ok


results = [
    check("one row per student (5)", lambda: len(students) == 5),
    check("columns " + ", ".join(COLUMNS), lambda: list(students.columns) == COLUMNS),
    check("roll numbers keep their zeros", lambda: students["roll_no"].tolist()[:2] == ["0042", "0107"]),
    check("a course without a title gives a missing title", lambda: students.loc[students["course_id"] == "ML301", "title"].isna().all()),
    check("2 students have no email", lambda: students["email"].isna().sum() == 2),
    check("the empty batch PY101-B gives no row", lambda: "PY101-B" not in set(students["batch_id"])),
    check("DA201-A has 2 students", lambda: (students["batch_id"] == "DA201-A").sum() == 2),
]
print(f"{sum(results)} of {len(results)} checks pass")

Output

FAIL one row per student (5)
FAIL columns course_id, title, batch_id, mode, roll_no, name, email
FAIL roll numbers keep their zeros
PASS a course without a title gives a missing title
FAIL 2 students have no email
FAIL the empty batch PY101-B gives no row
FAIL DA201-A has 2 students
1 of 7 checks pass

Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 check_courses.py

With the three files together in one directory, work on flatten_courses.py and nothing else; python check_courses.py shows how many of its 7 checks pass. Pointers: record_path can be a list that walks through two levels, ["batches", "students"]; meta can name fields of both levels; one course has no title; rename the dotted batch columns at the end.

Interview questions

Fresher · What is the difference between JSON and JSON Lines, and how does pandas read each?

A JSON file holds one JSON value, usually an array of objects or an object that wraps one, so a reader must parse the whole file before it has a single record. JSON Lines puts one complete JSON object on each line, with no array around them: a program can append records without rewriting the file, and a reader can process it line by line. In pandas, pd.read_json(path) reads a JSON array of records, while pd.read_json(path, lines=True) reads JSON Lines, and only with lines=True can it read the file in pieces with chunksize or stop early with nrows. Responses wrapped in an envelope, or nested records, are usually loaded with the json module first and flattened with pd.json_normalize.

Key takeaways

Save API responses to files and analyse the files: the work becomes repeatable and the examples need no network.
pd.json_normalize turns nested objects into dotted columns; missing keys become NaN, and max_level and sep control the result.
record_path plus meta turns a list inside each record into its own table that keeps the parent’s key; records with an empty list have no row there, so reconcile from the full list of parents.
explode gives one row per list element, a NaN row for an empty list, and repeated index labels unless ignore_index=True.
read_json(lines=True) reads JSON Lines, and only then do chunksize and nrows work.
One null turns a column of large integer IDs into float64 and changes them; read them with the json module into Int64, or as strings, or through DuckDB.
polars and DuckDB keep nested JSON as Struct and List columns and flatten only what a query needs.

References

pandas.json_normalize (pandas API reference) (pandas development team (NumFOCUS))
pandas.read_json (pandas API reference) (pandas development team (NumFOCUS))
pandas.DataFrame.explode (pandas API reference) (pandas development team (NumFOCUS))
IO tools: JSON (pandas user guide) (pandas development team (NumFOCUS))
RFC 8259: The JavaScript Object Notation (JSON) Data Interchange Format (RFC Editor (IETF))
JSON Lines (JSON Lines project)
json: JSON encoder and decoder (The Python Standard Library) (Python Software Foundation)
Loading JSON (DuckDB documentation) (DuckDB Foundation)
Unnesting (DuckDB documentation) (DuckDB Foundation)
JSON files (Polars user guide) (Polars)
polars.DataFrame.explode (Polars API reference) (Polars)

Report a problem with this lesson

In this module: Loading data: CSV, Excel, JSON, Parquet and databases

Reading messy CSV files with pandas Free: 35 minutes
Excel workbooks: sheets, headers, dates, writing Free: 30 minutes
JSON, APIs and nested records Free: 30 minutes
Parquet, Arrow and choosing a file format Free: 30 minutes
Reading from SQL databases safely Free: 25 minutes

Your country

JSON, APIs and nested records

What you will learn

Before you start

What you will learn

Before you start

The shapes JSON arrives in

A saved API response

Nested objects become columns: json_normalize

Lists of records become tables: record_path and meta

Lists in cells become rows: explode

JSON Lines

Large integer IDs change silently

When to keep the data nested

Your turn: courses, batches and students

Check yourself

Interview questions

Key takeaways

References

Related tools