Flatten Nested JSON to CSV in Python: Two Ways Compared

Q: Should I use json_normalize or write my own flattener?

Use json_normalize to explore and when you're already in pandas: it flattens every nested object in one call. Write your own when you need a rule for lists, such as counts or joined strings, or want no pandas dependency. For loading, prefer a fixed list of selected columns over either full flattening.

Q: How do I flatten a list of objects into rows?

Use explode on the list column to get one row per item, then pd.jsonnormalize on the exploded items if they're objects, keeping the parent ID on every row. Or use jsonnormalize with record_path pointing at the list and meta naming the parent fields to carry along. Either way, write the result as a separate table.

31 Jul 2026Perceptlayer6 min read

To flatten nested JSON to CSV in Python, turn every nested object into dotted column names, decide what to do with lists, and write the rows with csv or pandas. pandas.json_normalize does the objects in one call but leaves lists as Python lists inside cells. On a recorded Zillow search response it produced 60 columns with 4 holding lists; a plain-Python flattener produced 63 by turning lists into counts and joined strings. This tutorial runs both, then builds the CSV you'd actually use.

Step 1: What does the nested JSON look like?

The input is a Zillow search response: a data.listings array where each listing nests objects several levels deep and carries lists of photo URLs. Get it with one call to /v1/zillow/search, 1 credit on success. This is the request behind our recorded response:

curl "https://api.perceptlayer.com/v1/zillow/search?latitude=47.6062&longitude=-122.3321&radius_mi=5&listing_category=category1&page_size=20" \
  -H "X-API-Key: $API_KEY" -o zillow.json

The recording is trimmed to 3 listings, which keeps the counts below easy to check. One listing, cut down with …:

{
  "zpid": 2068074790,
  "location": { "latitude": 47.592564, "longitude": -122.386925 },
  "address": { "streetAddress": "1124 Sunset Avenue SW", "zipcode": "98116", "city": "Seattle", "state": "WA" },
  "media": {
    "propertyPhotoLinks": { "mediumSizeLink": "…", "highResolutionLink": "…" },
    "allPropertyPhotos": {
      "medium": ["https://photos.zillowstatic.com/fp/4218b6d4de5511b0d968347ddf298954-p_c.jpg", "…", "…"],
      "highResolution": ["https://photos.zillowstatic.com/fp/4218b6d4de5511b0d968347ddf298954-p_f.jpg", "…", "…"]
    },
    …
  },
  "bathrooms": 6,
  "bedrooms": 4,
  "livingArea": 7870,
  "yearBuilt": 2015,
  "propertyType": "singleFamily",
  "price": { "value": 14000000, "pricePerSquareFoot": 1779 },
  "estimates": { "zestimate": 13068300 },
  "listing": { "listingStatus": "forSale", "marketingStatus": "active", … },
  …
}

Three shapes need handling: nested objects (address, price), lists of strings (the photo URLs), and objects that appear on some listings and not others (estimates). The search response has no price history; Zillow's property endpoint takes zpid[] for detail, so treat any list it returns the way Step 4 treats photos. Get a free API key to record your own, and see the Zillow API page for the search parameters.

Step 2: How does pandas json_normalize flatten it?

pd.json_normalize takes a list of dicts and turns nested keys into column names joined by sep, a dot by default. With no record_path it treats the input as an array of records, and max_level caps how many levels it flattens (pandas documentation, 2026). One call handles every nested object in the listings.

import json
import pandas as pd

listings = json.load(open("zillow.json"))["data"]["listings"]
df = pd.json_normalize(listings)
list_cols = [c for c in df.columns if df[c].map(lambda v: isinstance(v, list)).any()]
print(len(df), "rows x", len(df.columns), "columns;", len(list_cols), "hold lists:", list_cols)

On the recorded response this prints:

3 rows x 60 columns; 4 hold lists: ['richMedia', 'media.allPropertyPhotos.medium', 'media.allPropertyPhotos.highResolution', 'listCardRecommendation.flexFieldRecommendations']

Every object became columns, and every list stayed a list. Write df.to_csv() now and those four columns hold Python reprs such as ['https://…', '…'], which a spreadsheet shows as text and a database can't query. json_normalize can expand one list into rows with record_path, but only one at a time, and each listing here has several.

Step 3: How do you flatten it with plain Python?

Recurse through each object, build dotted names, and choose a rule for lists. The rule below writes a .count column for every list, and also a |-joined column when the list holds plain values such as URLs. Lists of objects get only the count, since joining them would produce unreadable text.

import csv

def flatten(obj, prefix="", out=None):
    out = {} if out is None else out
    for key, value in obj.items():
        name = f"{prefix}{key}"
        if isinstance(value, dict):
            flatten(value, f"{name}.", out)
        elif isinstance(value, list):
            out[f"{name}.count"] = len(value)
            if value and all(isinstance(v, (str, int, float)) for v in value):
                out[name] = "|".join(str(v) for v in value)
        else:
            out[name] = value
    return out

rows = [flatten(item) for item in listings]
columns = sorted({k for r in rows for k in r})
with open("listings_flat.csv", "w", newline="") as f:
    writer = csv.DictWriter(f, fieldnames=columns)
    writer.writeheader()
    writer.writerows(rows)
print(len(rows), "rows x", len(columns), "columns")

It prints 3 rows x 63 columns. The three extra columns over pandas are the counts: four lists, each with a .count column, while richMedia, a list of objects, gets only its count. Columns come from the union of all rows' keys, so a field that exists on one listing still gets a column, and DictWriter leaves the other rows' cells empty.

Approach	Columns	Lists	Missing fields
`pd.json_normalize`	60	4 columns hold Python lists	`NaN`, and integer columns become floats
Plain Python `flatten`	63	`.count` plus a `	`-joined column
Selected columns (Step 5)	14	Photo count only	`<NA>` with nullable types

Step 4: Where should the lists go?

Into their own table, one row per item, with the parent's ID. A listing's photos are a one-to-many relationship, and a CSV cell is the wrong place for one. pandas does it with explode, which turns each list element into its own row and repeats the other columns.

photos = (df[["zpid", "media.allPropertyPhotos.highResolution"]]
          .explode("media.allPropertyPhotos.highResolution")
          .rename(columns={"media.allPropertyPhotos.highResolution": "photo_url"})
          .dropna())
photos.to_csv("listing_photos.csv", index=False)
print(len(photos), "photo rows")

The recording has 3 high-resolution photos per listing, so this writes 9 rows, each zpid and one URL. Load both files into a database and join on zpid when you need photos with listings. Do the same for any other list you need, such as a property's price history from a detail endpoint.

Step 5: How do you build the CSV you'll actually use?

Pick the fields your analysis reads, give them short names, and set types that survive missing values. Full flattening is a good way to see what's there; a fixed column list is what you should load, because it doesn't change when the response gains a field. It also exposes a pandas trap: estimates.zestimate exists on only 1 of the 3 listings.

KEEP = {"zpid": "zpid", "address.streetAddress": "street", "address.zipcode": "zip",
        "price.value": "price", "price.pricePerSquareFoot": "price_per_sqft",
        "bedrooms": "beds", "bathrooms": "baths", "livingArea": "sqft",
        "yearBuilt": "year_built", "propertyType": "type",
        "estimates.zestimate": "zestimate", "location.latitude": "lat", "location.longitude": "lon"}

slim = df[[c for c in KEEP if c in df.columns]].rename(columns=KEEP)
slim["zestimate"] = slim["zestimate"].astype("Int64")
slim["photo_count"] = df["media.allPropertyPhotos.highResolution"].map(
    lambda v: len(v) if isinstance(v, list) else 0)
slim.to_csv("listings_slim.csv", index=False)
print(slim[["zpid", "zip", "price", "beds", "sqft", "zestimate", "photo_count"]].to_string(index=False))

      zpid   zip    price  beds  sqft  zestimate  photo_count
2068074790 98116 14000000     4  7870   13068300            3
  49046647 98122  1050000     4  2100       <NA>            3
  49147344 98112   585000     1   760       <NA>            3

Without the astype("Int64") line, the zestimate column prints as 13068300.0, NaN, NaN. pandas' docs explain why: NaN is a float, so an integer column with any missing value becomes floating point unless you ask for the nullable integer type (pandas user guide, 2026). That matters most for IDs, where a float can silently change the value.

How do you append each day's pull to one CSV?

Write against a fixed column list, not the columns of the day. A plain flatten builds its header from whatever keys that day's listings have, so two days' files can have different columns in a different order, and appending one to the other misaligns every row. Use the selected KEEP names as the header, add a date column, and write the header only when the file is new.

import datetime as dt
import os

def append_day(slim, path="listings_daily.csv"):
    out = slim.assign(pulled_on=dt.date.today().isoformat())
    cols = ["pulled_on", *KEEP.values(), "photo_count"]
    out.reindex(columns=cols).to_csv(path, mode="a", index=False,
                                     header=not os.path.exists(path))

reindex adds any missing column as empty and drops unexpected ones, so the file keeps one shape even when a day's response doesn't. Keep the full flattened file alongside it if you want a record of everything the response contained.

What should you check in the result?

Three things. Column count: compare it with the last run, since a new column means the response changed shape. The guide to detecting schema changes in API JSON automates that. Sparse columns: in this recording, 6 of the 60 json_normalize columns are missing on some listings, including title, estimates.zestimate and both taxAssessment fields. Sparse doesn't mean broken, but know which columns your analysis can rely on.

Third, types after the round trip. CSV stores text, so zip codes such as 98116 should stay strings when you read the file back (dtype={"zip": str} in pd.read_csv), and booleans come back as True/False text. If the data is going into a database rather than a spreadsheet, skip CSV and load the JSON directly; the tutorial on loading API JSON into Postgres keeps the raw response next to typed columns.

What does it cost?

Flattening is local, so the only cost is the API calls that fetch the JSON: 1 credit per successful Zillow search, and nothing for failed calls. A daily pull of 5 search pages for one area is 5 × 30 = 150 credits a month, inside the 1,000 free monthly credits. The credits docs explain the balance, and pricing has what packs cost. To get your own response to flatten, run a search in the playground on the Zillow search endpoint page and save the JSON.

APIs in this post

Zillow1 credit per call · 10 endpoints

Frequently asked questions

Should I use json_normalize or write my own flattener?

Use json_normalize to explore and when you're already in pandas: it flattens every nested object in one call. Write your own when you need a rule for lists, such as counts or joined strings, or want no pandas dependency. For loading, prefer a fixed list of selected columns over either full flattening.

How do I flatten a list of objects into rows?

Use explode on the list column to get one row per item, then pd.json_normalize on the exploded items if they're objects, keeping the parent ID on every row. Or use json_normalize with record_path pointing at the list and meta naming the parent fields to carry along. Either way, write the result as a separate table.

Why did my integer column turn into floats?

Because some rows are missing the value, and pandas represents missing values in a default integer column as NaN, which is a float. Cast the column with astype("Int64"), or call convert_dtypes() on the frame, to get nullable integers that print <NA> for missing values.

How do I keep zip codes from losing leading zeros?

Treat them as strings end to end. The Zillow response already returns zipcode as a string, such as "98116", so the problem appears when reading the CSV back. Pass dtype={"zip": str} to pd.read_csv, or set the column type to text in your spreadsheet before importing.

Does flattening cost credits?

No. Flattening runs on a response you've already fetched. Only the API call costs credits, 1 per successful Zillow search, and failed calls cost nothing. Store the raw JSON so you can re-flatten it with new rules later without calling the API again.

pythonjsoncsvpandasdata engineering