Python Foundation for Data Engineers

Chapter 23 — From API to DataFrame to File

Lesson 23.1 — Flattening Nested API Responses into a DataFrame

Back in Chapter 20.4, you pulled data from the /users endpoint and flattened its nested address field by hand, one dictionary lookup at a time. That was genuinely good, honest work — but it doesn't scale well to records with many nested fields, or nesting several levels deep. This lesson introduces the proper tool for the job — one you actually already met, back in Week 2, Chapter 16.2.

python
import requests
import pandas as pd

response = requests.get("https://jsonplaceholder.typicode.com/users")
users = response.json()

df = pd.json_normalize(users)
print(df.columns.tolist())
Output / Note

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

Look closely at what just happened, in one line: every nested field — address, and even the doubly-nested address.geo — got automatically flattened into its own properly named column, using dots to show where each one came from. Compare this to Chapter 20.4's hands-on exercise, where you manually pulled out just user["address"]["city"] for one single field. pd.json_normalize() does that same kind of extraction for every nested field, all at once, without you writing a single manual lookup.

Let's build a clean, focused DataFrame from it, keeping only the columns genuinely worth working with:

python
df_clean = df[["id", "name", "email", "address.city", "company.name"]]
df_clean = df_clean.rename(columns={
    "address.city": "city",
    "company.name": "company",
})
print(df_clean)
Output / Note

id name email city company 0 1 Leanne Graham Sincere@april.biz Gwenborough Romaguera-Crona 1 2 Ervin Howell Shanna@melissa.tv Wisokyburgh Deckow-Crist ...

Notice .rename() — a genuinely useful tool for exactly this situation, turning the slightly awkward dotted names pd.json_normalize() produces into clean, readable column names, ready for the rest of your pandas toolkit from Week 2 — filtering, grouping, sorting, all of it, applied directly to real, live API data.

Try It Yourself: Pull data from https://jsonplaceholder.typicode.com/posts, flatten it with pd.json_normalize(), and print its .columns.tolist(). Notice this data has no nested fields at all — confirm that pd.json_normalize() still works correctly on flat data, producing the same result you'd get from a plain pd.DataFrame() call.

Lesson 23.2 — Landing the Result: Saving to Parquet

Once your API data is a clean DataFrame, the final step should feel genuinely familiar — it's exactly Week 2, Chapter 16's lesson on Parquet, just with data that arrived over the network instead of from a CSV file.

python
import requests
import pandas as pd

response = requests.get("https://jsonplaceholder.typicode.com/users")
users = response.json()

df = pd.json_normalize(users)
df_clean = df[["id", "name", "email", "address.city", "company.name"]]
df_clean = df_clean.rename(columns={"address.city": "city", "company.name": "company"})

df_clean.to_parquet("api_users.parquet", index=False)
print("Saved successfully")
Output / Note

Saved successfully

Reading it back works exactly as you'd expect from Week 2:

python
reloaded = pd.read_parquet("api_users.parquet")
print(reloaded.dtypes)
print(reloaded.head(3))
Output / Note

id int64 name object email object city object company object dtype: object

The genuinely important idea to take from this short lesson: the moment API data becomes a clean DataFrame, it's no different from any other DataFrame you've worked with all course. Every tool from Week 2 — saving, reloading, grouping, filtering — applies identically, regardless of whether the data started life in a CSV file or arrived live from a server a moment ago. That's exactly the payoff of learning pandas properly before this week: the destination format never changes, only the source does.

Lesson 23.3 — Hands-On: A Small End-to-End Pipeline — API to Parquet

Let's bring this chapter together into one small, complete pipeline — pulling real, paginated data from a live API, resiliently, flattening it, and landing it as Parquet, using tools built across this entire week.

python
import requests
import pandas as pd
import time

def fetch_all_pages(base_url, page_size=25, max_pages=50):
    """
    Fetch every page of results from a paginated API endpoint.
    Direct callback to Chapter 21.4 — reused here without changes.
    """
    all_records = []
    page = 1

    while page <= max_pages:
        response = requests.get(base_url, params={"_page": page, "_limit": page_size})

        if response.status_code != 200:
            print(f"Stopping — page {page} returned status {response.status_code}")
            break

        records = response.json()
        if len(records) == 0:
            break

        all_records.extend(records)
        page += 1
        time.sleep(0.2)

    return all_records

Now, the end-to-end pipeline, pulling from the /comments endpoint you worked with back in Chapter 21:

python
raw_comments = fetch_all_pages("https://jsonplaceholder.typicode.com/comments", page_size=25)
print(f"Fetched {len(raw_comments)} comments")

df = pd.json_normalize(raw_comments)
print(df.columns.tolist())
print(df.head(3))
Output / Note

Fetched 500 comments ['postId', 'id', 'name', 'email', 'body'] postId id name email body 0 1 1 id labore ex et quam laborum Eliseo@gardner.biz laudantium enim quasi est... 1 1 2 quo vero reiciendis velit... Jayne_Kuhic@sydney.com est natus enim... 2 1 3 odio adipisci rerum aut... Nikita@garfield.biz quia molestiae reprehenderit...

Notice this data happens to be flat already — no nested fields — so pd.json_normalize() behaves exactly like a plain pd.DataFrame() call would, exactly what you confirmed in Lesson 23.1's "Try It Yourself." It's still worth calling pd.json_normalize() out of habit on any new API response, since you won't always know in advance whether nesting is hiding somewhere in it.

Let's clean it up slightly and land it:

python
df["email"] = df["email"].str.lower()
df = df.drop_duplicates(subset=["id"])

df.to_parquet("comments_from_api.parquet", index=False)
print(f"Saved {len(df)} rows to comments_from_api.parquet")

reloaded = pd.read_parquet("comments_from_api.parquet")
print(reloaded.shape)
Output / Note

Saved 500 rows to comments_from_api.parquet (500, 5)

Look at everything folded into this one short pipeline: a resilient, paginated API client from Chapter 21, feeding into a flattening step from this chapter, a small cleaning pass using tools from Week 2, Chapter 14, and a proper landing step in Parquet — the same format Week 2, Chapter 16 taught you real pipelines actually rely on. Nothing here is a new idea on its own; it's the connection between five weeks of separate lessons, working together in one script.

Try It Yourself: Extend this pipeline to also pull data from https://jsonplaceholder.typicode.com/posts, and merge it with the comments DataFrame using .merge() from Week 2, Chapter 15.2 — matching on postId in the comments data against id in the posts data. Save the merged result as a second Parquet file.