Python Doc & Data Automation

Fix pandas MemoryError Reading a Large CSV

The read never returns. Either Python raises:

MemoryError: Unable to allocate 8.94 GiB for an array with shape (29847221, 40) and data type object

or, worse, nothing is raised at all and the process disappears:

$ python load.py
Killed
$ dmesg | tail -1
Out of memory: Killed process 40912 (python) total-vm:31904512kB

The second form is the Linux OOM killer, and it looks like a crash with no traceback. Both mean the same thing: pandas asked the allocator for more than the machine had.

Root Cause

A CSV on disk is a compact text encoding. In memory, pandas materialises every column as a typed NumPy or Arrow array, and the default choices are generous. Unconstrained integers become int64 at 8 bytes per value regardless of range. Any column containing a single non-numeric token becomes object — an array of pointers to individual Python strings, each carrying roughly 50 bytes of interpreter overhead before its characters. A 12-character account code that occupies 13 bytes in the file costs about 62 bytes in RAM.

Peak usage is also higher than the final frame. The C parser builds column blocks and then consolidates them, so allocation briefly holds both. A frame that settles at 8 GB can peak near 12 GB, which is why a machine with 16 GB fails on a file that "should" fit.

Why 150 MB on disk needs a gigabyte of RAM A single value is traced through three representations. On disk the twelve character account code costs thirteen bytes. Loaded as object dtype it costs about sixty two bytes, made up of a pointer, the Python string header and the characters. Loaded as category dtype it costs about one byte, an index into a shared dictionary. The ratio between the object and category representations is roughly sixty to one. One value, three representations On disk (text) ACC000417, 13 bytes object dtype 8 ptr + 49 header + 12 ~62 bytes category dtype int8 code + shared dict ~1 byte 30 million rows: 390 MB on disk becomes 1.86 GB as object, or 30 MB as category Peak allocation is higher still — the C parser holds blocks and the consolidated frame at once so a frame that settles at 8 GB can spike past 12 GB during the read

Minimal Diagnostic

Do not guess which columns are expensive. Sample, measure, and rank.

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

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

def cost_ranking(csv_path: Path, sample_rows: int = 20_000) -> pd.DataFrame:
    """Rank columns by projected full-file memory, worst first."""
    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

    rows_total = sum(1 for _ in csv_path.open("rb")) - 1
    scale = rows_total / max(len(sample), 1)

    usage = sample.memory_usage(deep=True).drop("Index", errors="ignore")
    report = pd.DataFrame({
        "dtype": sample.dtypes.astype(str),
        "projected_mb": (usage * scale / 1e6).round(1),
        "distinct_in_sample": sample.nunique(),
    })
    report["category_would_help"] = report["distinct_in_sample"] < len(sample) * 0.5
    return report.sort_values("projected_mb", ascending=False)

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

              dtype  projected_mb  distinct_in_sample  category_would_help
account      object        1843.2                4991                 True
description  object         921.7               19884                False
status       object         610.4                   3                 True
txn_id        int64         238.8               20000                False
amount      float64         238.8               19112                False

Read it top-down: account and status are cheap wins, description is genuinely high-cardinality text, and the numeric columns are already compact but can be halved.

Fix: Read Less, and Read It Narrower

Three changes, applied in this order, resolve most cases without any restructuring.

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

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

NEEDED = ["txn_id", "account", "region", "amount", "status", "booked_on"]
DTYPES = {
    "txn_id": "int32",       # was int64: half the bytes
    "account": "category",   # was object: ~60x smaller
    "region": "category",
    "status": "category",
    "amount": "float64",     # money stays 64-bit on purpose
}

def read_lean(csv_path: Path) -> pd.DataFrame:
    """Load only the needed columns, with explicit minimal dtypes."""
    try:
        return pd.read_csv(
            csv_path,
            usecols=NEEDED,            # 1. drop columns the job never touches
            dtype=DTYPES,              # 2. stop 64-bit and object defaults
            parse_dates=["booked_on"], # 3. datetime64 beats object strings
        )
    except MemoryError as exc:
        raise MemoryError(
            f"{csv_path} still does not fit — switch to a chunked read"
        ) from exc

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

Each lever is worth stating separately. usecols is pure subtraction — a 40-column export where six columns matter costs 15% of the memory and parses roughly three times faster. Declaring dtypes stops both the int64 default and, more importantly, the object fallback. parse_dates converts date strings into 8-byte datetime64 values, which is a 5–8× saving on any column of ISO dates.

Variant Fix 1: The pyarrow Backend

On pandas 2.x, Arrow-backed strings remove the per-string Python overhead entirely and often make chunking unnecessary.

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

frame = pd.read_csv(
    "data/transactions.csv",
    engine="pyarrow",              # multi-threaded parser
    dtype_backend="pyarrow",       # Arrow arrays instead of NumPy object arrays
)
print(frame.dtypes.head())
print(f"{frame.memory_usage(deep=True).sum() / 1e6:.0f} MB")

The cost is behavioural, not numeric: Arrow dtypes use pd.NA rather than NaN, and a few third-party libraries still assume NumPy arrays. Convert at the boundary with frame.astype(...) when handing data to something that cannot cope, and keep the Arrow representation for the heavy middle of the pipeline.

Variant Fix 2: Stream It

When the file cannot fit however narrow the dtypes, stop trying to hold it. The chunked pattern — read a slice, reduce it, discard it — runs in constant memory.

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

def total_by_status(csv_path: Path, chunk_rows: int = 250_000) -> pd.Series:
    """Aggregate without ever holding more than one chunk."""
    partials = []
    for chunk in pd.read_csv(
        csv_path,
        chunksize=chunk_rows,
        usecols=["status", "amount"],
        dtype={"status": "category"},
    ):
        partials.append(chunk.groupby("status", observed=True)["amount"].sum())
    if not partials:
        raise ValueError(f"{csv_path} had no data rows")
    return pd.concat(partials).groupby(level=0).sum()

if __name__ == "__main__":
    print(total_by_status(Path("data/transactions.csv")))

The rule that makes this work — reduce inside the loop, never append raw chunks — plus boundary handling and chunk-size tuning is covered in handling large CSV files with chunking.

Variant Fix 3: Let a Query Engine Do It

If the work is a filter or an aggregate, DuckDB reads the CSV directly, spills to disk when needed, and hands back a small frame.

# pip install duckdb
import duckdb

frame = duckdb.sql("""
    SELECT account, SUM(amount) AS total
    FROM read_csv_auto('data/transactions.csv')
    WHERE status = 'settled'
    GROUP BY account
    HAVING SUM(amount) > 10000
""").df()
print(len(frame), "account(s) above threshold")

Peak memory here is the size of the result, not of the input. For repeated interrogation of the same file, convert once to Parquet and query that — the parse cost disappears and columnar reads touch only the columns named.

Escalation order when read_csv runs out of memory Five options ranked by effort. Selecting columns with usecols is a one line change with a large payoff. Declaring dtypes is a few lines with the largest payoff on string columns. Switching to the pyarrow backend is one argument and removes Python string overhead. Chunking requires restructuring the code into a streaming loop. Moving to a query engine such as DuckDB requires rewriting the logic as SQL. The advice is to stop at the first option that fits. Try in this order — stop when it fits 1. usecols=[...] one line · drops whole columns · also speeds up parsing 2. dtype={...} a few lines · category and int32 · biggest single win 3. pyarrow backend one argument · no Python string overhead · pd.NA semantics 4. chunksize= restructure into a streaming loop · constant memory 5. DuckDB / Polars rewrite as a query · spills to disk · best for joins and sorts Peak allocation is higher than the finished frame A profile across a single read_csv call. Memory climbs as column blocks are parsed, spikes when the blocks and the consolidated frame both exist, then settles to the final frame size of about eight gigabytes with a peak near twelve. A machine sized only for the settled figure fails during the spike, which is why a file that should fit does not. Memory during one read_csv call 16 GB machine limit peak ~12 GB settled ~8 GB parsing blocks consolidating frame ready

Verification

Prove the fix rather than assuming it: measure peak memory, and confirm the values did not change when the dtypes did.

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

def measure_peak(loader, *args) -> tuple[pd.DataFrame, float]:
    """Return the loaded frame and the peak allocation in megabytes."""
    tracemalloc.start()
    frame = loader(*args)
    _current, peak = tracemalloc.get_traced_memory()
    tracemalloc.stop()
    return frame, peak / 1e6

def assert_values_unchanged(narrow: pd.DataFrame, csv_path: Path, sample_rows: int = 5000) -> None:
    """Narrowing dtypes must not change any value."""
    baseline = pd.read_csv(csv_path, nrows=sample_rows)
    for column in narrow.columns:
        if column not in baseline.columns:
            continue
        left = narrow[column].head(sample_rows).astype(str).tolist()
        right = baseline[column].astype(str).tolist()
        assert left == right, f"{column}: values changed after dtype narrowing"
    print(f"{len(narrow.columns)} column(s) verified identical")

The string comparison is deliberate — comparing typed values would hide exactly the failure being tested, where float32 rounds a total or int32 silently wraps an identifier that exceeded two billion. If a column does differ, widen that one dtype rather than abandoning the approach.

FAQ

Why does the process get killed with no traceback? The Linux OOM killer terminates the process at the kernel level, so Python never gets to raise. Check dmesg or journalctl -k for the confirmation, and treat it exactly like a MemoryError.

Does low_memory=False help? No — it does the opposite. That flag makes the parser read the file in one pass to improve dtype inference, which raises peak memory. It exists to silence DtypeWarning, and the correct fix for that warning is an explicit dtype.

Is int32 safe for identifiers? Up to 2,147,483,647. Order numbers and row counters are usually fine; anything derived from a timestamp, a hash, or an external system's 64-bit key is not. When in doubt keep identifiers as category or string — arithmetic on them is meaningless anyway.

My file has 300 columns and I need 8 of them. Is usecols enough? Usually yes, and it is the single most effective change available. Pass the list by name rather than by position so a column reorder upstream fails loudly instead of silently loading the wrong data.

How much memory does a chunked read actually use? Roughly one chunk plus the accumulated partial results. At 250,000 rows and 6 narrow columns that is tens of megabytes, regardless of whether the file is 1 GB or 100 GB.

Sizing the Machine Against the File

A useful rule of thumb before touching any code: a CSV of mostly short strings expands to roughly six to eight times its file size in memory with default dtypes, and to roughly one to one-and-a-half times with narrowed ones. Peak allocation during the read is about half as much again.

So a four-gigabyte file needs somewhere near thirty-six gigabytes to load naively, about eight with dtypes declared, and about twelve at peak. Comparing those three numbers against the machine decides immediately whether this is a configuration problem or a design one, and it takes less time than a single failed read.

Part of Handling Large CSV Files with Chunking.