--- title: "Flatten Nested JSON to CSV in Python: Two Ways Compared" description: "Flatten a recorded Zillow response to CSV with pandas json_normalize (60 columns) and plain Python (63), split photo lists into a child table, fix integers." url: https://perceptlayer.com/blog/web-data-to-csv-python --- [Blog](https://perceptlayer.com/blog) / [Data engineering](https://perceptlayer.com/blog/category/data-engineering) # Flatten Nested JSON to CSV in Python: Two Ways Compared 31 Jul 2026 Perceptlayer 6 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. ## Key takeaways - `pd.json_normalize(listings)` flattened the 3 recorded Zillow listings into 60 columns, such as `address.zipcode` and `price.value`. Four columns still held lists. - A 20-line recursive function gave 63 columns: each list became a `.count` column, plus a `|`\-joined column when its items are plain values. - Lists of things, such as photos, belong in a child table keyed on the parent ID. Exploding 3 photos each for 3 listings gives 9 rows. - Six of the 60 columns are missing on some listings. `estimates.zestimate` exists on 1 of 3, so pandas turned it into a float column until we cast it to `Int64`. - Selecting and renaming 13 fields, plus a photo count, gives a 14-column CSV that's easier to load and check than either full flattening. ## 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: ```bash 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 `…`: ```json { "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](https://perceptlayer.com/signup) to record your own, and see the [Zillow API page](https://perceptlayer.com/apis/zillow) 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](https://pandas.pydata.org/docs/reference/api/pandas.json_normalize.html), 2026). One call handles every nested object in the listings. ```python 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: ```text 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. ```python 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 | `` 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. ```python 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. ```python 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)) ``` ```text zpid zip price beds sqft zestimate photo_count 2068074790 98116 14000000 4 7870 13068300 3 49046647 98122 1050000 4 2100 3 49147344 98112 585000 1 760 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](https://pandas.pydata.org/docs/user_guide/integer_na.html), 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. ```python 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](https://perceptlayer.com/blog/web-data-api-json-schema-changes) 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](https://perceptlayer.com/blog/load-web-data-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](https://docs.perceptlayer.com/credits) explain the balance, and [pricing](https://perceptlayer.com/pricing) has what packs cost. To get your own response to flatten, run a search in the playground on the [Zillow search endpoint page](https://perceptlayer.com/apis/zillow/search) and save the JSON. ## APIs in this post - [Zillow **1 credit per call** · 10 endpoints](https://perceptlayer.com/apis/zillow) ## 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 `` 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. python json csv pandas data engineering ## More from the blog 1. [Data engineering](https://perceptlayer.com/blog/category/data-engineering) 29 Sept 2026 Perceptlayer 6 min read ## [Save API JSON to Parquet in Python](https://perceptlayer.com/blog/save-api-json-to-parquet-python) Write API JSON to date-partitioned Parquet with pyarrow and a fixed schema, so a price that arrives as 441 one day and 393.73 the next doesn't break it. 2. [Data engineering](https://perceptlayer.com/blog/category/data-engineering) 20 Oct 2026 Perceptlayer 6 min read ## [Detect Schema Changes in API JSON Responses](https://perceptlayer.com/blog/web-data-api-json-schema-changes) Infer a path-and-type schema from a recorded Google Search response (71 paths), diff each new response against it and alert on removed or retyped fields. 3. [Data engineering](https://perceptlayer.com/blog/category/data-engineering) 12 Oct 2026 Perceptlayer 7 min read ## [Incremental API Data Pulls: Fetch Only New Records](https://perceptlayer.com/blog/incremental-api-data-pulls) Pull only new listings: sort newest first, keep a high-water mark or a seen-ID set per query, and stop paging early. Python tested on Craigslist and eBay data.