Browse cheat sheets · Python
Read and Write CSV, Parquet and JSON
Read and write CSV, Parquet, and JSON with pandas while controlling schema, dates, nulls, compression, encoding, and production-safe file behavior.
On this page
- Problem
- Quick answer
- Code / commands
- 1. Read a CSV with explicit types and dates
- 2. Read only required CSV columns
- 3. Control CSV null handling
- 4. Preserve empty strings when required
- 5. Read CSV with an explicit encoding
- 6. Read a large CSV in chunks
- 7. Write CSV without the DataFrame index
- 8. Write CSV with UTF-8 encoding
- 9. Write selected CSV columns in a controlled order
- 10. Format timestamps when writing CSV
- 11. Read Parquet
- 12. Read selected Parquet columns
- 13. Read partitioned Parquet with filters when supported
- 14. Write Parquet
- 15. Write compressed Parquet
- 16. Write a partitioned Parquet dataset
- 17. Read a standard JSON array/object structure
- 18. Read JSON Lines
- 19. Write JSON records
- 20. Write JSON Lines
- 21. Control JSON timestamp formatting
- 22. Read JSON from an API-like nested structure
- 23. Normalize nested arrays with metadata
- 24. Inspect schema immediately after reading
- 25. Validate key columns after ingestion
- 26. Validate row counts after round-trip writes
- 27. Write to a temporary path before replacing a production file
- 28. Avoid appending CSV blindly
- 29. Add headers only when creating a CSV
- 30. Compare file-format choice by workload
- What the result means
- What to look for
- IDs lose leading zeros
- Date column remains text
- CSV round-trip changes dtypes
- Parquet file is much smaller than CSV
- Parquet read is still slow
- JSON produces unexpected columns or row counts
- Null counts change after reading a CSV
- Production notes
- CSV has no real schema
- Parquet is usually better for repeated analytics
- Parquet dependencies matter
- JSON is not inherently tabular
- File writes are not transactions
- Object storage behaves differently from local disk
- Avoid many tiny Parquet files
- Compression trades CPU for storage/I/O
- Common mistakes
- 1. Letting pandas infer identifier types
- 2. Writing the DataFrame index accidentally
- 3. Treating CSV as schema-preserving
- 4. Using errors="ignore"-style thinking for bad files
- 5. Assuming JSON means one row per object
- 6. Appending to CSV from multiple processes
- 7. Partitioning Parquet by high-cardinality columns
- 8. Writing directly over a production file
- 9. Assuming Parquet predicate pushdown always happens
- Next step
- Related deep dives
Problem
You need to load or export data with pandas and want predictable behavior across CSV, Parquet, and JSON.
The hard part is not calling read_csv() or to_parquet().
Production problems usually come from:
- wrong type inference
- date parsing differences
- lost leading zeros
- encoding issues
- null-value inconsistencies
- unexpectedly large files
- row-oriented JSON vs JSON Lines confusion
- schema drift
- unsafe overwrite behavior
- CSV files that look correct but change meaning when reopened
The goal is to choose the right format and make ingestion/output behavior explicit.
Quick answer
Use CSV when interoperability matters most:
import pandas as pd
df = pd.read_csv(
"orders.csv",
dtype={"customer_id": "string"},
parse_dates=["order_time"],
)
Use Parquet when you want typed, columnar analytics storage:
df = pd.read_parquet(
"orders.parquet",
columns=["order_id", "customer_id", "amount", "order_time"],
)
Write Parquet for efficient analytical reuse:
df.to_parquet(
"orders.parquet",
index=False,
compression="snappy",
)
Use JSON Lines for streaming/event-style records:
df.to_json(
"events.jsonl",
orient="records",
lines=True,
date_format="iso",
)
Choose the format based on the next consumer, not habit.
Code / commands
1. Read a CSV with explicit types and dates
import pandas as pd
df = pd.read_csv(
"orders.csv",
dtype={
"order_id": "string",
"customer_id": "string",
"country": "string",
},
parse_dates=["order_time"],
)
Use explicit dtype= for identifiers that must not be interpreted numerically.
This prevents values such as:
000123
from becoming:
123
2. Read only required CSV columns
df = pd.read_csv(
"orders.csv",
usecols=[
"order_id",
"customer_id",
"amount",
"order_time",
],
parse_dates=["order_time"],
)
This reduces:
- memory usage
- parse work
- accidental dependency on irrelevant columns
Use usecols= when the source is wider than the workload needs.
3. Control CSV null handling
pandas recognizes several common null-like values by default.
You can add source-specific values:
df = pd.read_csv(
"orders.csv",
na_values=[
"",
"NULL",
"N/A",
"-9999",
],
)
Only classify source values as missing when you know their meaning.
If a literal "NULL" is a valid business value, do not automatically convert it.
4. Preserve empty strings when required
If empty strings must remain distinct from missing values:
df = pd.read_csv(
"orders.csv",
keep_default_na=False,
)
This changes default null detection behavior.
Use it only when source semantics require that distinction.
5. Read CSV with an explicit encoding
df = pd.read_csv(
"customers.csv",
encoding="utf-8",
)
If the source uses another known encoding:
df = pd.read_csv(
"legacy_customers.csv",
encoding="cp1252",
)
Do not guess encodings silently in a production pipeline.
6. Read a large CSV in chunks
for chunk in pd.read_csv(
"large_orders.csv",
chunksize=100_000,
):
process(chunk)
Use chunking when the full CSV does not fit comfortably in memory.
Remember that chunk-by-chunk processing can change algorithm design.
For example, global deduplication or global ranking needs state across chunks.
7. Write CSV without the DataFrame index
df.to_csv(
"orders.csv",
index=False,
)
This avoids unintentionally creating a column such as:
Unnamed: 0
when the file is read later.
8. Write CSV with UTF-8 encoding
df.to_csv(
"orders.csv",
index=False,
encoding="utf-8",
)
For Excel-oriented workflows that need a UTF-8 BOM:
df.to_csv(
"orders.csv",
index=False,
encoding="utf-8-sig",
)
Use utf-8-sig only when the downstream tool benefits from it.
9. Write selected CSV columns in a controlled order
df.to_csv(
"orders.csv",
columns=[
"order_id",
"customer_id",
"amount",
"order_time",
],
index=False,
)
This makes the export schema deliberate.
10. Format timestamps when writing CSV
df.to_csv(
"orders.csv",
index=False,
date_format="%Y-%m-%dT%H:%M:%S",
)
Be careful with timezone-aware timestamps.
A formatted string can lose timezone information if the format does not include it.
If the consumer needs timezone offsets, prefer an ISO-aware representation.
11. Read Parquet
df = pd.read_parquet(
"orders.parquet",
)
Parquet preserves schema information better than CSV.
Commonly preserved types include:
- integers
- floating-point values
- booleans
- strings
- timestamps
Exact behavior depends on the Parquet engine and underlying schema.
12. Read selected Parquet columns
df = pd.read_parquet(
"orders.parquet",
columns=[
"order_id",
"customer_id",
"amount",
"order_time",
],
)
This is one of Parquet’s key advantages.
Column pruning can reduce I/O substantially when you need only part of a wide dataset.
13. Read partitioned Parquet with filters when supported
df = pd.read_parquet(
"orders_dataset/",
filters=[
("country", "==", "US"),
("year", "==", 2026),
],
)
Filter pushdown depends on:
- storage layout
- Parquet metadata
- engine support
- the filter expression
Do not assume every filter avoids reading every file.
14. Write Parquet
df.to_parquet(
"orders.parquet",
index=False,
)
For analytical data that will be reused, Parquet is usually a better default than CSV.
15. Write compressed Parquet
df.to_parquet(
"orders.parquet",
index=False,
compression="snappy",
)
Other supported compression options depend on the installed Parquet engine.
Common choices include:
snappygzipbrotlizstd
Choose based on compatibility, compression ratio, and CPU cost.
Do not assume the strongest compression is best for every workload.
16. Write a partitioned Parquet dataset
df.to_parquet(
"orders_dataset/",
index=False,
partition_cols=[
"year",
"month",
],
)
Use partitioning when downstream readers frequently filter on those columns.
Avoid extremely high-cardinality partition columns.
Bad partition examples often include:
- customer ID
- transaction ID
- exact timestamp
These can create too many small files/directories.
17. Read a standard JSON array/object structure
df = pd.read_json(
"records.json",
)
Use this only when the JSON structure matches what read_json() expects.
Inspect unfamiliar JSON before assuming it maps directly to rows.
18. Read JSON Lines
JSON Lines contains one JSON object per line.
df = pd.read_json(
"events.jsonl",
lines=True,
)
This format is common for:
- event logs
- streaming exports
- APIs
- append-style data pipelines
19. Write JSON records
df.to_json(
"records.json",
orient="records",
date_format="iso",
)
Conceptually:
[
{"order_id":"A1","amount":10.5},
{"order_id":"A2","amount":20.0}
]
Use orient="records" when downstream systems expect a list of row-like objects.
20. Write JSON Lines
df.to_json(
"events.jsonl",
orient="records",
lines=True,
date_format="iso",
)
This produces one record per line.
It is usually easier to stream and append-process than one large JSON array.
21. Control JSON timestamp formatting
Prefer ISO timestamps for human-readable interchange:
df.to_json(
"events.json",
orient="records",
date_format="iso",
)
Without explicit control, JSON serialization choices may differ from what downstream consumers expect.
Verify:
- timezone representation
- precision
- date-only vs timestamp semantics
22. Read JSON from an API-like nested structure
Nested JSON often needs normalization.
Example:
payload = {
"results": [
{
"id": 1,
"customer": {
"name": "Ana",
"country": "US",
},
}
]
}
Normalize:
df = pd.json_normalize(
payload["results"]
)
Resulting columns can include:
id
customer.name
customer.country
Use json_normalize() when nested objects are part of the record structure.
23. Normalize nested arrays with metadata
payload = {
"orders": [
{
"order_id": "A1",
"items": [
{"sku": "X", "qty": 2},
{"sku": "Y", "qty": 1},
],
}
]
}
Normalize item rows:
items = pd.json_normalize(
payload["orders"],
record_path="items",
meta=["order_id"],
)
Understand the resulting grain:
one row per item
not:
one row per order
Nested expansion can change row counts significantly.
24. Inspect schema immediately after reading
print(df.shape)
print(df.dtypes)
df.info()
Then check:
print(df.head())
print(df.isna().sum().sort_values(ascending=False).head(20))
A file loading successfully does not mean it loaded correctly.
25. Validate key columns after ingestion
required = [
"order_id",
"customer_id",
]
missing_required = df[required].isna().sum()
print(missing_required)
Check uniqueness when appropriate:
assert not df["order_id"].duplicated().any()
Only enforce uniqueness when the business grain says the key must be unique.
26. Validate row counts after round-trip writes
Example:
df.to_parquet(
"orders.parquet",
index=False,
)
check = pd.read_parquet(
"orders.parquet",
)
assert len(check) == len(df)
For critical pipelines, also validate:
- key counts
- null counts
- dtypes
- important aggregates
Row count alone is not enough.
27. Write to a temporary path before replacing a production file
Avoid partially written final files when possible.
Example local pattern:
from pathlib import Path
target = Path("orders.parquet")
temp = Path("orders.parquet.tmp")
df.to_parquet(
temp,
index=False,
)
temp.replace(target)
On local filesystems, replacing after a successful write is safer than writing directly over the final file.
Atomicity and overwrite behavior differ on remote/object-storage systems.
28. Avoid appending CSV blindly
Appending:
df.to_csv(
"orders.csv",
mode="a",
header=False,
index=False,
)
can be valid, but only if:
- schema is identical
- column order is identical
- encoding matches
- no concurrent writer exists
- partial writes are acceptable
CSV append has no schema enforcement.
Use it carefully.
29. Add headers only when creating a CSV
from pathlib import Path
path = Path("orders.csv")
df.to_csv(
path,
mode="a",
header=not path.exists(),
index=False,
)
This avoids repeated header rows in simple local append workflows.
Still avoid this pattern for concurrent production writers.
30. Compare file-format choice by workload
Use this mental model:
Human/tool interoperability -> CSV
Typed analytical storage -> Parquet
API/event/document interchange -> JSON / JSON Lines
This is not absolute, but it is a strong starting point.
What the result means
| Format | Strength | Main risk |
|---|---|---|
| CSV | Universal and human-readable | No embedded schema; type inference can change meaning |
| Parquet | Typed, columnar, compressed, efficient for analytics | Less human-readable; engine/tool compatibility matters |
| JSON | Flexible hierarchical interchange | Verbose; nested structure can complicate tabular loading |
| JSON Lines | One record per line, stream-friendly | Still verbose; schema may vary between records |
Important pandas options:
| Option | Why it matters |
|---|---|
dtype= |
Prevents unwanted type inference |
parse_dates= |
Parses known date columns while reading |
usecols= |
Reads only required columns |
na_values= |
Adds source-specific missing-value markers |
keep_default_na= |
Controls pandas’ built-in null recognition |
columns= |
Restricts Parquet read/write columns |
filters= |
May enable Parquet predicate pushdown |
compression= |
Controls output compression |
orient= |
Defines JSON shape |
lines=True |
Reads/writes JSON Lines |
What to look for
IDs lose leading zeros
The source was probably inferred as numeric.
Use:
dtype={"customer_id": "string"}
Date column remains text
Either:
- it was not parsed
- source values are inconsistent
- format is unexpected
Inspect parse failures before replacing the original data.
CSV round-trip changes dtypes
This is normal unless you explicitly restore schema.
CSV does not store a pandas schema.
Parquet file is much smaller than CSV
This is often expected because Parquet is compressed and columnar.
Do not use file size alone to judge correctness.
Parquet read is still slow
Check:
- number of files
- small-file problem
- selected columns
- filter pushdown
- storage latency
- compression codec
- partition layout
JSON produces unexpected columns or row counts
The source may be nested.
Inspect the JSON hierarchy and define the intended output grain before normalizing.
Null counts change after reading a CSV
Check:
na_valueskeep_default_na- empty strings
- literal values such as
"NA"
CSV null interpretation is configurable and can change business meaning.
Production notes
CSV has no real schema
A CSV file does not inherently distinguish:
00123
from:
123
or:
2026-10-04
from a plain string.
Persist schema separately or specify it at read time.
Parquet is usually better for repeated analytics
Parquet supports:
- typed columns
- compression
- column pruning
- predicate pushdown in supported readers
- efficient scans
It is usually a stronger internal analytical format than CSV.
Parquet dependencies matter
pandas relies on a Parquet engine such as:
pyarrowfastparquet
Available features and compatibility can differ.
For modern pandas workloads, pyarrow is commonly used.
JSON is not inherently tabular
Nested objects and arrays require an explicit flattening strategy.
Before normalization, define:
What should one output row represent?
File writes are not transactions
Writing a local or remote file does not automatically provide database-style atomicity.
For critical output:
- write to a temporary location
- validate
- promote/rename when supported
- avoid concurrent writers
- use a storage format/table layer with transaction semantics when required
Object storage behaves differently from local disk
Paths such as:
s3://...
abfss://...
gs://...
may be supported through filesystem integrations, but:
- rename semantics differ
- credentials matter
- retries matter
- eventual behavior depends on the service
- concurrent writes need deliberate design
Do not assume local filesystem behavior.
Avoid many tiny Parquet files
Small files create metadata and scheduling overhead in analytical systems.
Batch data into appropriately sized files for the downstream engine instead of writing one file per row/event.
Compression trades CPU for storage/I/O
The best codec depends on:
- read frequency
- write frequency
- storage cost
- CPU budget
- downstream compatibility
Measure with your workload.
Common mistakes
1. Letting pandas infer identifier types
IDs are often strings even when they contain only digits.
Use explicit dtypes.
2. Writing the DataFrame index accidentally
This commonly produces unwanted columns when the file is reloaded.
Use:
index=False
unless the index is intentionally part of the data contract.
3. Treating CSV as schema-preserving
It is not.
Define types on read.
4. Using errors="ignore"-style thinking for bad files
Silent coercion or permissive parsing can hide data-quality problems.
Inspect rejected or malformed records deliberately.
5. Assuming JSON means one row per object
Nested arrays can multiply records.
Define the target grain before normalizing.
6. Appending to CSV from multiple processes
Concurrent append can corrupt or interleave output.
Use a coordinated writer or a storage system designed for concurrency.
7. Partitioning Parquet by high-cardinality columns
This creates too many small partitions/files.
Partition on fields that support common pruning patterns and have manageable cardinality.
8. Writing directly over a production file
A failed write can leave incomplete output.
Use a staged write-and-promote pattern when the filesystem/storage model supports it.
9. Assuming Parquet predicate pushdown always happens
Filtering support depends on layout, metadata, engine, and expression.
Verify behavior for important workloads.
Was this helpful?
What could be better?
Thanks — that helps me improve Javi Builds.
Stay in the loop
Get the Javi Builds updates you actually care about.
Check your inbox
I sent you a confirmation link. Your subscription will be active after you confirm it.