JSON to CSV with Python 3: A Practical Developer Guide
Learn how to convert JSON to CSV in Python 3 using the built-in csv module and pandas. This technical guide covers flattening nested data, streaming for large files, and best practices from MyDataTables to streamline data pipelines.
Converting JSON to CSV in Python 3 is straightforward with the built-in csv module or with pandas. Load the JSON data, flatten nested structures as needed, then write rows to CSV with headers. This two-path approach gives you a reliable, repeatable workflow for data pipelines. Ideal for both quick ad-hoc conversions and integrated ETL processes.
json to csv python 3: Overview and approach
According to MyDataTables, json to csv python 3 is a common data engineering task across analytics, data science, and integration workloads. The typical approach splits into two forks: a lightweight path using the standard library csv module for flat records, and a heavier, more flexible path using pandas for nested or irregular JSON. In practice, most pipelines start with a simple case and evolve to handle complexity. Below you’ll see concrete code for both routes, plus practical tips for handling nested data and large files.
import json, csv
# Path to your input JSON file
with open("data.json","r", encoding="utf-8") as f:
data = json.load(f)
# Basic CSV export for a flat list of dictionaries
with open("data.csv","w", newline="", encoding="utf-8") as fcsv:
writer = csv.DictWriter(fcsv, fieldnames=data[0].keys())
writer.writeheader()
writer.writerows(data)# Flatten nested records into flat dictionaries
def flatten(record, parent_key="", sep="_"):
items = {}
for k, v in record.items():
new_key = f"{parent_key}{sep}{k}" if parent_key else k
if isinstance(v, dict):
items.update(flatten(v, new_key, sep=sep))
else:
items[new_key] = v
return items
flattened = [flatten(item) for item in data]# Convert flattened data to CSV in one pass
with open("flat_data.csv","w", newline="", encoding="utf-8") as fcsv:
writer = csv.DictWriter(fcsv, fieldnames=flattened[0].keys())
writer.writeheader()
writer.writerows(flattened)confidence":null}
prerequisites
commandReference
stepByStep
tipsList
keyTakeaways
faqSection
mainTopicQuery
Steps
Estimated time: 60-120 minutes
- 1
Assess your JSON structure
Open the input JSON and inspect whether it’s a flat array of objects or contains nested objects. If nested, plan on flattening keys or using json_normalize in pandas. This step reduces guesswork when defining headers.
Tip: Prefer an explicit header set to ensure column order remains stable across runs. - 2
Choose your conversion path
If the data is flat, the csv module is fast and dependency-free. For nested data, pandas with json_normalize provides more flexibility and easier schema handling.
Tip: Starting with the csv module for simple cases can save you time and reduce mental overhead. - 3
Load and normalize JSON
Load the JSON into a Python structure, then flatten nested dictionaries if needed. For large datasets, consider streaming approaches or chunking to avoid high memory usage.
Tip: Use a dedicated flatten function to keep headers predictable. - 4
Write CSV with headers
Build a writer with the exact header order, then write all rows. Ensure encoding is UTF-8 and newline handling is correct to avoid extra blank lines on Windows.
Tip: Always write a header row first for compatibility with downstream tools. - 5
Validate output
Inspect the CSV with a quick read-back to confirm column alignment and data integrity. Validate a few rows manually or via a small Python check.
Tip: Check for NaN or missing values that may indicate nested structures not fully flattened.
Prerequisites
Required
- Required
- pip package managerRequired
- Familiarity with JSON data structuresRequired
Optional
- Optional
- Optional
Commands
| Action | Command |
|---|---|
| Convert JSON to CSV with csv module (flat data)Uses csv.DictWriter with keys() for header alignment | — |
| Convert JSON to CSV with pandas (nested support)Read as records; write to CSV with no index | — |
| Flatten nested JSON with json_normalizeUseful for deeply nested structures | — |
| CLI: Quick one-liner with PythonSingle-command conversion for small files | — |
| CLI: JSON processing with jq (optional)Requires jq to be installed; good for quick field extraction | — |
People Also Ask
Can I convert very large JSON files without loading them entirely into memory?
Yes. Use streaming or chunked processing with libraries like ijson or iterating over a JSON decoder. This approach minimizes peak memory usage and is essential for big data workflows.
Yes. You can handle large JSON files by streaming; it prevents loading the whole file into memory at once.
What if JSON contains nested arrays instead of objects?
Flatten with a structured approach: json_normalize in pandas or a custom recursive function that flattens arrays by index or by expanding elements into separate rows where appropriate.
Flatten nested arrays carefully, often by normalizing with pandas or writing a small flatten routine.
Is there a recommended order for CSV headers when exporting from JSON?
Yes. Define a fixed header order in advance and apply it when writing the CSV so downstream tools expect consistent columns.
Yes, fix the header order to ensure compatibility downstream.
When should I prefer pandas over the csv module?
Choose pandas when dealing with nested data, missing values, or when you need built-in data cleaning. Use the csv module for small, flat datasets and ultra-lightweight scripts.
Use pandas for complex data and quick cleaning; csv module for simple, flat data.
How can I verify the conversion succeeded end-to-end?
Read the resulting CSV back into Python or another tool and compare a sample of rows to the source JSON to confirm headers and data alignment.
Verify by re-reading the CSV and spot-checking data against the JSON source.
Main Points
- Plan by inspecting JSON structure before coding
- Choose csv module for flat data and pandas for nested data
- Flattened headers should be stable and explicit
- Test by reading back the CSV to verify integrity
- UTF-8 encoding prevents character issues