Handling Large CSV Files with Chunking

A 4 GB export does not need 4 GB of RAM to process — it needs roughly ten times that if you load it naively, and then the interpreter dies:

MemoryError: Unable to allocate 12.4 GiB for an array with shape (41231044, 38) and data type object

The multiplier is not a pandas defect. A CSV is text; in memory every string column becomes a Python object array of pointers to individual str objects, and every unconstrained numeric column becomes 64-bit. A 4 GB file of mostly short strings and small integers routinely expands past 30 GB. Fixing that is a matter of reading less, reading it in pieces, and telling pandas exactly what each column is.

Generic advice — "add more RAM", "use a database" — misses the practical middle ground. Most document and data automation work is a single pass: filter, aggregate, write a report. That shape fits a chunked read perfectly, needs no infrastructure, and runs in constant memory.

Prerequisites

python -m venv .venv
source .venv/bin/activate

pip install "pandas>=2.2,<3" pyarrow

# Optional out-of-core engines, for the escalation section
pip install duckdb polars

# A large test file, if you do not have one to hand
python - <<'PY'
import csv, random, pathlib
pathlib.Path("data").mkdir(exist_ok=True)
with open("data/transactions.csv", "w", newline="") as fh:
    w = csv.writer(fh)
    w.writerow(["txn_id", "account", "region", "amount", "status", "booked_on"])
    for i in range(2_000_000):
        w.writerow([i, f"ACC{i % 50000:06d}", random.choice(["EMEA", "AMER", "APAC"]),
                    round(random.uniform(1, 5000), 2),
                    random.choice(["settled", "pending", "failed"]), "2026-07-15"])
PY

A requirements.txt for the pipeline:

pandas==2.2.3
pyarrow==18.1.0
duckdb==1.1.3

Step 1: Measure Before Choosing a Strategy

Read a small sample, measure what the full file would cost, and decide from a number rather than a hunch.

# pip install "pandas>=2.2,<3"
from pathlib import Path
import pandas as pd

CSV = Path("data/transactions.csv")

def memory_estimate(csv_path: Path, sample_rows: int = 50_000) -> dict:
    """Project full-file memory from a sample, per column."""
    if not csv_path.exists():
        raise FileNotFoundError(f"No such file: {csv_path}")
    try:
        sample = pd.read_csv(csv_path, nrows=sample_rows)
    except Exception as exc:
        raise RuntimeError(f"Could not sample {csv_path}: {exc}") from exc

    file_bytes = csv_path.stat().st_size
    sample_bytes = sample.memory_usage(deep=True).sum()
    # Bytes of raw CSV consumed by the sample, used to scale the estimate
    with csv_path.open("rb") as fh:
        head = b"".join(next(fh) for _ in range(sample_rows + 1))
    ratio = file_bytes / max(len(head), 1)

    per_column = (sample.memory_usage(deep=True) / sample_bytes * 100).round(1)
    return {
        "file_mb": round(file_bytes / 1e6, 1),
        "projected_ram_mb": round(sample_bytes * ratio / 1e6, 1),
        "expansion_factor": round(sample_bytes * ratio / file_bytes, 1),
        "column_share_pct": per_column.drop("Index", errors="ignore").to_dict(),
    }

if __name__ == "__main__":
    import pprint
    pprint.pprint(memory_estimate(CSV))

{'file_mb': 148.2,
 'projected_ram_mb': 1043.6,
 'expansion_factor': 7.0,
 'column_share_pct': {'txn_id': 6.1, 'account': 31.4, 'region': 27.8,
                      'amount': 6.1, 'status': 22.5, 'booked_on': 6.1}}

Three columns account for 80% of the footprint, and all three are low-cardinality strings — the classic profile, and the one that category dtype fixes almost for free.

Step 2: Narrow the dtypes

Before reaching for chunking, try making the data smaller. Three levers, in order of payoff.

# pip install "pandas>=2.2,<3"
from pathlib import Path
import pandas as pd

CSV = Path("data/transactions.csv")

DTYPES = {
    "txn_id": "int32",        # 2.1 billion rows would be needed to overflow
    "account": "category",    # 50k distinct values across 2M rows
    "region": "category",     # 3 distinct values
    "amount": "float32",      # 7 significant digits: fine for money to the penny below 100k
    "status": "category",
}

def read_narrow(csv_path: Path) -> pd.DataFrame:
    """Read the whole file with explicit, minimal dtypes."""
    return pd.read_csv(
        csv_path,
        dtype=DTYPES,
        usecols=list(DTYPES) + ["booked_on"],   # never read columns you will not use
        parse_dates=["booked_on"],
        engine="c",
    )

if __name__ == "__main__":
    frame = read_narrow(CSV)
    print(f"{len(frame):,} rows, {frame.memory_usage(deep=True).sum() / 1e6:.1f} MB")

usecols is the cheapest win and the most often forgotten: a 38-column export where the job needs six columns costs six columns' memory and roughly a sixth of the parse time. category collapses repeated strings to an integer code plus one dictionary. Downcasting numerics halves or quarters their cost, with the caveat that float32 carries about seven significant digits — fine for line-item amounts, not for cumulative balances in the billions, where the rounding is visible.

If the narrowed frame fits, stop here. Chunking adds complexity and should be reached for only when the answer to "does it fit?" is no.

Step 3: Read in Chunks

chunksize turns read_csv into an iterator of DataFrames. Memory stays flat at roughly one chunk, and the loop body decides what to keep.

# pip install "pandas>=2.2,<3"
from pathlib import Path
import pandas as pd

CSV = Path("data/transactions.csv")

def summarise_by_region(csv_path: Path, chunk_rows: int = 250_000) -> pd.DataFrame:
    """Aggregate a file larger than RAM in one streaming pass."""
    if not csv_path.exists():
        raise FileNotFoundError(f"No such file: {csv_path}")

    partials = []
    try:
        reader = pd.read_csv(
            csv_path,
            chunksize=chunk_rows,
            dtype={"region": "category", "status": "category", "amount": "float32"},
            usecols=["region", "status", "amount"],
        )
        for chunk in reader:
            # Reduce inside the loop: never append raw chunks to a list.
            partials.append(
                chunk.groupby(["region", "status"], observed=True)["amount"]
                     .agg(["sum", "count"])
            )
    except Exception as exc:
        raise RuntimeError(f"Chunked read failed on {csv_path}: {exc}") from exc

    combined = pd.concat(partials)
    return (
        combined.groupby(level=[0, 1], observed=True)
                .sum()
                .rename(columns={"sum": "total_amount", "count": "transactions"})
    )

if __name__ == "__main__":
    print(summarise_by_region(CSV))

The discipline that makes chunking work is in one line of that loop: reduce inside the loop. Appending each raw chunk to a list and concatenating at the end uses exactly as much memory as reading the file whole — a mistake that looks like chunking and is not. What may be appended is a partial result that is orders of magnitude smaller than its chunk: a groupby, a filtered subset, a running count.

Aggregations that combine associatively — sum, count, min, max — reduce cleanly per chunk. Ones that do not, such as median or exact distinct counts, need either a second pass or an approximation; that limitation is the main reason to escalate to an engine that spills to disk.

Step 4: Filter Early, Write Incrementally

When the output is itself large — a cleaned copy rather than a summary — write each chunk as it is processed instead of building one frame.

# pip install "pandas>=2.2,<3"
from pathlib import Path
import pandas as pd

def filter_to_csv(src: Path, dest: Path, region: str, chunk_rows: int = 250_000) -> int:
    """Stream a filtered copy out, one chunk at a time, in constant memory."""
    dest.parent.mkdir(parents=True, exist_ok=True)
    written = 0
    first = True
    for chunk in pd.read_csv(src, chunksize=chunk_rows, dtype={"region": "category"}):
        kept = chunk[chunk["region"] == region]
        if kept.empty:
            continue
        kept.to_csv(
            dest,
            mode="w" if first else "a",   # header once, then append
            header=first,
            index=False,                  # never write the index into a data file
        )
        written += len(kept)
        first = False
    return written

if __name__ == "__main__":
    rows = filter_to_csv(Path("data/transactions.csv"), Path("out/emea.csv"), "EMEA")
    print(f"{rows:,} row(s) written")

index=False matters more here than usual, because a chunked write with the default index=True produces a file whose index restarts at zero in every chunk — the pathology described in fix pandas to_csv extra index column. Writing Parquet instead of CSV avoids both that and the re-parse cost on the next read; the export trade-offs are covered in exporting data to CSV formats.

Edge Cases and Variants

A Chunk Boundary Splits a Logical Group

Chunk boundaries fall on row counts, not on group boundaries. Any per-group calculation that needs all of a group's rows at once — a running balance, a first-and-last comparison — will be wrong if the group straddles a boundary. Two robust answers: sort the file by the grouping key first so groups are contiguous and carry the tail rows forward, or compute associatively and combine, as the region summary above does.

# pip install "pandas>=2.2,<3"
import pandas as pd

def running_totals(reader) -> pd.DataFrame:
    """Carry the unfinished tail group across the chunk boundary."""
    carry = pd.DataFrame()
    results = []
    for chunk in reader:
        chunk = pd.concat([carry, chunk], ignore_index=True)
        last_key = chunk["account"].iloc[-1]
        complete = chunk[chunk["account"] != last_key]
        carry = chunk[chunk["account"] == last_key]     # may continue into the next chunk
        if not complete.empty:
            results.append(complete.groupby("account")["amount"].sum())
    if not carry.empty:
        results.append(carry.groupby("account")["amount"].sum())
    return pd.concat(results).groupby(level=0).sum().to_frame("total")

This is only correct on a file already sorted by account. Sorting a file too large to hold is itself a chunked operation, and at that point an out-of-core engine is usually the better answer.

Mixed Types in a Column

Chunked reads infer dtypes per chunk, so a column that is integer for the first million rows and picks up a "N/A" later produces different dtypes per chunk and a DtypeWarning. Declaring dtype= removes the ambiguity; the full diagnosis is in fix pandas DtypeWarning mixed types.

Compressed Input

read_csv decompresses transparently, and chunking still works — the decompressor is streamed, not buffered whole:

# pip install "pandas>=2.2,<3"
reader = pd.read_csv("data/transactions.csv.gz", chunksize=250_000, compression="gzip")

Gzip cannot be read in parallel, so a compressed file is single-threaded no matter what engine consumes it. When files are re-read often, decompress once to Parquet and keep that.

Validation

A streaming job that silently drops rows is worse than one that fails. Reconcile counts against the file itself.

# pip install "pandas>=2.2,<3"
from pathlib import Path
import pandas as pd

def line_count(csv_path: Path) -> int:
    """Count data rows without parsing them."""
    with csv_path.open("rb") as fh:
        return sum(1 for _ in fh) - 1        # minus the header

def assert_complete(csv_path: Path, processed_rows: int, tolerance: int = 0) -> None:
    """Fail when a chunked pass did not see every row."""
    expected = line_count(csv_path)
    delta = abs(expected - processed_rows)
    assert delta <= tolerance, (
        f"row mismatch: file has {expected:,}, pipeline saw {processed_rows:,} (delta {delta:,})"
    )
    print(f"all {expected:,} row(s) accounted for")

Two further checks earn their keep on financial data. Compare the chunked aggregate against a full-file aggregate on a sampled subset of keys — they must agree to the cent. And assert that no column silently became object mid-run, which is the fingerprint of a type inference difference between chunks.

Performance and Scale Notes

Chunk size trades per-chunk overhead against peak memory. Below roughly 50,000 rows the fixed cost per chunk starts to dominate; above roughly a million rows the memory saving stops mattering. Between 100,000 and 500,000 rows is the useful band for typical widths, and the right number is the one that keeps peak resident memory comfortably under the machine's limit.

The pyarrow engine parses faster than the default C engine on wide files and supports dtype_backend="pyarrow", which stores strings without Python object overhead:

# pip install "pandas>=2.2,<3" pyarrow
frame = pd.read_csv("data/transactions.csv", engine="pyarrow", dtype_backend="pyarrow")

That single change often removes the need for chunking entirely on string-heavy files, because Arrow-backed strings cost a fraction of object dtype. It is the first thing to try on pandas 2.x.

When the work is a query rather than a pass — joins, sorts, window functions, or repeated interrogation of the same file — hand it to an engine designed for it:

# pip install duckdb
import duckdb

result = duckdb.sql("""
    SELECT region, status, SUM(amount) AS total, COUNT(*) AS transactions
    FROM read_csv_auto('data/transactions.csv')
    GROUP BY region, status
    ORDER BY total DESC
""").df()

DuckDB streams from disk, spills to disk when a query exceeds memory, and returns a small pandas frame at the end. Polars offers a similar escape hatch through scan_csv and lazy evaluation. Both are a better answer than an increasingly baroque chunk loop.

Troubleshooting

Complete Working Script

#!/usr/bin/env python3
"""Summarise a large CSV in constant memory.

    pip install "pandas>=2.2,<3"
    python summarise_csv.py data/transactions.csv --group region status --value amount
"""
from __future__ import annotations

import argparse
import sys
from pathlib import Path

import pandas as pd

def summarise(csv_path: Path, group: list[str], value: str, chunk_rows: int) -> pd.DataFrame:
    if not csv_path.exists():
        raise FileNotFoundError(f"No such file: {csv_path}")

    dtypes = {col: "category" for col in group}
    dtypes[value] = "float64"

    partials, seen = [], 0
    reader = pd.read_csv(csv_path, chunksize=chunk_rows, usecols=group + [value], dtype=dtypes)
    for chunk in reader:
        seen += len(chunk)
        partials.append(chunk.groupby(group, observed=True)[value].agg(["sum", "count"]))

    if not partials:
        raise ValueError(f"{csv_path} contained no data rows")

    result = pd.concat(partials).groupby(level=list(range(len(group))), observed=True).sum()
    result.attrs["rows_read"] = seen
    return result.rename(columns={"sum": f"total_{value}", "count": "rows"})

def main(argv: list[str] | None = None) -> int:
    parser = argparse.ArgumentParser(description="Chunked CSV aggregation.")
    parser.add_argument("csv", type=Path)
    parser.add_argument("--group", nargs="+", required=True)
    parser.add_argument("--value", required=True)
    parser.add_argument("--chunk-rows", type=int, default=250_000)
    parser.add_argument("--out", type=Path)
    args = parser.parse_args(argv)

    try:
        frame = summarise(args.csv, args.group, args.value, args.chunk_rows)
    except (FileNotFoundError, ValueError) as exc:
        print(f"error: {exc}", file=sys.stderr)
        return 1

    print(f"read {frame.attrs['rows_read']:,} row(s)")
    print(frame.sort_values(frame.columns[0], ascending=False).head(20))
    if args.out:
        args.out.parent.mkdir(parents=True, exist_ok=True)
        frame.to_csv(args.out)
        print(f"wrote {args.out}")
    return 0

if __name__ == "__main__":
    raise SystemExit(main())

Scheduled behind the logging patterns in scheduling and logging automation jobs, this becomes the ingest stage of a pipeline whose later stages — cleaning, merging, reporting — never see the full file at all.

FAQ

How do I choose chunksize? Start at 250,000 rows and watch peak resident memory. Halve it if the process approaches the machine's limit; double it if the job is I/O-bound and CPU is idle. Row width matters more than row count, so a 60-column file wants a smaller number than a 6-column one.

Is category always the right dtype for strings? Only when cardinality is low relative to row count — a rule of thumb is under about half the rows being distinct. A unique transaction ID stored as category costs more than a plain string, because the dictionary is as long as the column.

Can I parallelise a chunked read? Not usefully with read_csv alone: parsing is already C-level and the file is read sequentially. Parallelism pays when the per-chunk work is heavy — hand chunks to a ProcessPoolExecutor then. For everything else, DuckDB or Polars parallelise better than hand-rolled code.

Why does float32 change my totals? It carries about seven significant digits, so summing millions of values accumulates visible error. Keep money in float64 — or better, integer pence — when the total feeds a report someone reconciles.

When should I stop chunking and use a database? When you need joins, sorts or repeated queries over the same file. A single streaming pass suits chunking; anything that has to look at the whole dataset more than once is cheaper to load into DuckDB, SQLite or Parquet first.

Measuring Before Optimising

The strategies here differ by an order of magnitude in effort, and choosing between them from intuition usually picks the most complex one that would have worked. Two numbers settle it in under a minute.

The first is the projected memory from the sample-and-scale estimate. If it fits comfortably in the machine with the dtypes narrowed, nothing else on this page is needed and a chunk loop is pure added complexity.

The second is how many times the file will be read. A file processed once favours streaming; a file interrogated repeatedly favours converting it to Parquet first and paying the parse cost once. The break-even is around two reads, which is lower than most people expect.

Write both numbers down when the pipeline is built. When somebody proposes moving it to a distributed engine eighteen months later, those figures are what make the conversation about evidence rather than preference.

Related

• Fix pandas MemoryError Reading a Large CSV — the immediate crash and its fixes
• Fix pandas DtypeWarning Mixed Types — per-chunk inference disagreements
• Cleaning Messy CSV Data with pandas — the transforms that run inside the loop
• Exporting Data to CSV Formats — writing results without index and encoding surprises
• Merging Multiple Spreadsheets — combining many files rather than splitting one

Part of Python for Excel & CSV Data Processing.