Processing CSVs Larger Than RAM: A pandas Chunking and Memory-Mapping Guide

Desired Outcome: Stream a CSV Larger Than RAM

When a CSV file exceeds physical memory, pd.read_csv() either raises MemoryError or forces the operating system into heavy swap. The goal is to stream the file through pandas in bounded-memory blocks, rely on the OS to page data on demand instead of copying the whole file into Python’s heap, and write a compact intermediate format for downstream analysis.

Prerequisites and Environment Checks

• pandas version: The memory_map parameter in read_csv is available in current pandas 2.x releases. Verify with pd.__version__. If you are on an older version, inspect the live signature with help(pd.read_csv) because parameter names have changed across major releases.
• Filesystem: Memory mapping works best on local disks. Network shares (NFS, SMB) or some FUSE mounts may reject mmap calls. Test with a small file before running on production data.
• Permissions: The process needs read access to the source CSV and write access to the output directory.
• Monitoring: Install psutil (pip install psutil) to observe resident memory (RSS) during the run.

Run these shell commands to profile the file (requires read permission on the path):

head -n 5 /path/to/large.csv
wc -l /path/to/large.csv

Expected check: The header line reveals the delimiter and column names. The line count gives an upper bound on rows.

Procedure: Chunked Memory-Mapped Ingestion

Step 1: Inspect a Small Sample

Never read the full file to discover its schema. Pull the first few rows to decide on dtype, usecols, and delimiter handling.

import pandas as pd

sample = pd.read_csv(
    "/path/to/large.csv",
    nrows=1000,
    sep=",",  # adjust if the header shows a different delimiter
)
print(sample.dtypes)

Expected check: Column names and inferred types match the source system. If numeric columns appear as object, investigate mixed types or thousands separators before scaling up.

Step 2: Configure the Chunked Iterator

Use chunksize to return a TextFileReader iterator. Set memory_map=True so the OS pages the file on demand rather than copying it into Python buffers. Pin dtypes explicitly to prevent chunk-to-chunk drift.

import pandas as pd
import psutil

process = psutil.Process()
csv_path = "/path/to/large.csv"
out_path = "/path/to/aggregates.parquet"

dtype_map = {
    "id": "int64",
    "amount": "float64",
    "category": "category",
}

reader = pd.read_csv(
    csv_path,
    sep=",",
    header=0,
    dtype=dtype_map,
    usecols=["id", "amount", "category", "timestamp"],
    parse_dates=["timestamp"],
    chunksize=500_000,
    memory_map=True,
    on_bad_lines="skip",  # replaces deprecated error_bad_lines
)

total_rows = 0
chunks = []

for i, chunk in enumerate(reader):
    assert chunk["id"].dtype == "int64", f"Chunk {i}: unexpected dtype"
    total_rows += len(chunk)

    agg = chunk.groupby("category", observed=True)["amount"].sum().reset_index()
    agg["chunk_idx"] = i
    chunks.append(agg)

    rss_gb = process.memory_info().rss / (1024 ** 3)
    print(f"Chunk {i}: rows={len(chunk)}, total={total_rows}, RSS={rss_gb:.2f} GB")

reader.close()

Where to run: Execute in a terminal, Jupyter kernel, or scheduled job with read access to csv_path and write access to the directory containing out_path.

Expected checks:

• RSS remains roughly flat across chunks rather than climbing toward total file size.
• total_rows matches the line count from wc -l minus the header (if no lines were skipped).
• Each chunk respects dtype_map.

Step 3: Combine and Persist

Concatenate the per-chunk aggregates and write to Parquet. Parquet is preferred over HDF5 for portability; HDF5 support depends on the pandas version and the availability of PyTables.

if chunks:
    result = pd.concat(chunks, ignore_index=True)
    final = result.groupby("category", observed=True)["amount"].sum().reset_index()
    final.to_parquet(out_path, index=False)
    print(f"Wrote {len(final)} rows to {out_path}")
else:
    print("No data processed.")

Expected check: The output file exists and is smaller than the source CSV. Reload it with pd.read_parquet(out_path) to confirm it is valid.

Expected Checks and Validation

1. Memory ceiling: Track psutil.Process().memory_info().rss before and after the loop. If RSS grows linearly with file size, memory_map may not be active or chunk sizes are too large.
2. Row reconciliation: Compare total_rows against the source line count. A mismatch indicates skipped malformed lines or a truncated read.
3. Schema stability: Print chunk.dtypes for the first and last chunk. They should be identical.
4. Output integrity: Read back the Parquet file and verify aggregate values against a manual spot-check on a small slice of the original CSV.

Recovery Options and Fallbacks

• Memory mapping fails: If memory_map=True raises ValueError or OSError (common on network filesystems or restricted Windows configurations), catch the exception and retry with memory_map=False. Memory use will be higher, so reduce chunksize.

  try:
      reader = pd.read_csv(csv_path, chunksize=500_000, memory_map=True, dtype=dtype_map)
  except (ValueError, OSError) as exc:
      print(f"mmap unavailable ({exc}); falling back to standard chunked read")
      reader = pd.read_csv(csv_path, chunksize=100_000, memory_map=False, dtype=dtype_map)

• Malformed rows: Use on_bad_lines="skip" to bypass unparseable lines. Compare row totals before and after to detect data loss.
• Concurrent writes: If the CSV might be appended to while you read, copy it to a snapshot first (cp /path/to/large.csv /path/to/snapshot.csv) or acquire an advisory lock. Chunked iterators do not protect against mid-read mutations.
• Type drift: If a later chunk contains values that violate the pinned dtype, pandas raises. Either clean the source or use convert_dtypes() cautiously, understanding that nullable types increase memory.

Limitations

Memory mapping reduces Python-side allocation but does not eliminate all overhead; extremely wide rows or high-cardinality categorical conversions can still spike memory. memory_map behavior is platform-dependent and should be verified on the target OS. Chunked aggregation requires that your reduction operation be associative and commutative (sums, counts, min/max); operations like median or exact distinct counts need a second pass or an external engine. Finally, on_bad_lines="skip" silently discards data—use it only when downstream consumers tolerate gaps.