Browse cheat sheets · Python

Technology: PythonCategory: Data I/ODifficulty: IntermediateUpdated

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
  1. Problem
  2. Quick answer
  3. Code / commands
  4. 1. Read a CSV with explicit types and dates
  5. 2. Read only required CSV columns
  6. 3. Control CSV null handling
  7. 4. Preserve empty strings when required
  8. 5. Read CSV with an explicit encoding
  9. 6. Read a large CSV in chunks
  10. 7. Write CSV without the DataFrame index
  11. 8. Write CSV with UTF-8 encoding
  12. 9. Write selected CSV columns in a controlled order
  13. 10. Format timestamps when writing CSV
  14. 11. Read Parquet
  15. 12. Read selected Parquet columns
  16. 13. Read partitioned Parquet with filters when supported
  17. 14. Write Parquet
  18. 15. Write compressed Parquet
  19. 16. Write a partitioned Parquet dataset
  20. 17. Read a standard JSON array/object structure
  21. 18. Read JSON Lines
  22. 19. Write JSON records
  23. 20. Write JSON Lines
  24. 21. Control JSON timestamp formatting
  25. 22. Read JSON from an API-like nested structure
  26. 23. Normalize nested arrays with metadata
  27. 24. Inspect schema immediately after reading
  28. 25. Validate key columns after ingestion
  29. 26. Validate row counts after round-trip writes
  30. 27. Write to a temporary path before replacing a production file
  31. 28. Avoid appending CSV blindly
  32. 29. Add headers only when creating a CSV
  33. 30. Compare file-format choice by workload
  34. What the result means
  35. What to look for
  36. IDs lose leading zeros
  37. Date column remains text
  38. CSV round-trip changes dtypes
  39. Parquet file is much smaller than CSV
  40. Parquet read is still slow
  41. JSON produces unexpected columns or row counts
  42. Null counts change after reading a CSV
  43. Production notes
  44. CSV has no real schema
  45. Parquet is usually better for repeated analytics
  46. Parquet dependencies matter
  47. JSON is not inherently tabular
  48. File writes are not transactions
  49. Object storage behaves differently from local disk
  50. Avoid many tiny Parquet files
  51. Compression trades CPU for storage/I/O
  52. Common mistakes
  53. 1. Letting pandas infer identifier types
  54. 2. Writing the DataFrame index accidentally
  55. 3. Treating CSV as schema-preserving
  56. 4. Using errors="ignore"-style thinking for bad files
  57. 5. Assuming JSON means one row per object
  58. 6. Appending to CSV from multiple processes
  59. 7. Partitioning Parquet by high-cardinality columns
  60. 8. Writing directly over a production file
  61. 9. Assuming Parquet predicate pushdown always happens
  62. Next step
  63. 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:

  • snappy
  • gzip
  • brotli
  • zstd

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_values
  • keep_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:

  • pyarrow
  • fastparquet

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.

Next step

Use the file behavior to choose the next action.

  • IDs changed type or lost formatting → define dtype= explicitly.
  • Dates parsed incorrectly → parse with explicit rules and inspect failures.
  • CSV is too large or slow for repeated analytics → move to Parquet.
  • Parquet reads too much data → select columns, review filters, and inspect partitioning.
  • JSON is nested → define the target row grain and use json_normalize().
  • Null counts changed unexpectedly → review CSV null parsing options.
  • Output is consumed by another team/system → define the schema and format contract explicitly.
  • Writes must be atomic/concurrent → use staged writes or a transactional storage layer instead of simple file overwrite.
  • Dataset no longer fits comfortably in memory → push reading, filtering, and transformation to a larger execution engine.

← All cheat sheets