Streaming CSV with chunksize

When a CSV file is larger than available RAM — say, a 50 GB log file on a machine with 16 GB of memory — calling pd.read_csv('file.csv') fails with a MemoryError or causes the system to swap heavily, making it unusably slow. The solution is chunked reading: instead of loading the entire file at once, you process it in fixed-size pieces, accumulating results without ever holding everything in memory simultaneously.

Passing chunksize=N to pd.read_csv() returns a TextFileReader iterator rather than a DataFrame. Each iteration yields a DataFrame of at most N rows. The file is read lazily — no data is loaded until you ask for the next chunk. This iterator can be used in a for loop or passed to pd.concat(). Choose a chunksize large enough for efficient I/O (e.g., 10,000–100,000 rows) but small enough to fit comfortably in memory.

The most common pattern is to perform a transformation or filter on each chunk and collect results in a list, then concatenate. For example, you might filter rows matching a condition, compute per-chunk statistics, or select only the columns you need. Working on subsets means only the current chunk occupies memory, and the rest of the file is untouched. After the loop, a single pd.concat(results) assembles the final DataFrame.

Sometimes you do not need to keep any rows at all — you just need a running aggregate. Track a running sum, count, or min/max across chunks without building up a list of DataFrames. This is the most memory-efficient pattern because the memory usage stays constant regardless of file size. At the end, compute the final statistic from your accumulators.

By default, Pandas infers column dtypes from the data, which requires scanning each chunk twice (once to infer, once to parse). Providing the dtype argument avoids this overhead and also prevents dtype inconsistencies between chunks. For example, a column containing mostly integers but one empty cell might infer as float64 in one chunk and object in another. Explicitly specifying dtypes ensures consistent, faster reading across all chunks.

Use the usecols parameter to load only the columns your analysis needs. If a CSV has 50 columns but your aggregation only uses 3, there is no reason to parse the other 47. Combining usecols with chunksize dramatically reduces both I/O time and memory usage. This is one of the simplest and most impactful optimisations for large CSV processing.

Performing a groupby aggregation across chunks requires accumulating partial results. Compute the groupby within each chunk, then combine using a second groupby on the concatenated partial results. For example, to get total sales by region across a 10 GB file, collect per-chunk region sums in a list, then concatenate and group again. This two-pass aggregation pattern is sometimes called a map-reduce approach.

Large CSV files from external sources often have malformed rows — extra commas, wrong encoding, or truncated lines. Use on_bad_lines='skip' (Pandas 1.3+) or error_bad_lines=False (older Pandas) to silently skip bad rows, and encoding='latin-1' if UTF-8 parsing fails. Track which chunks produced errors with a try-except around each chunk's processing to build a robust pipeline that does not crash on a single bad row in a 10 million-row file.

When your processed output is also large, write results incrementally rather than accumulating everything in memory and writing at the end. Open a CSV file and append each processed chunk using mode='a' and header=False for subsequent chunks. This keeps the output pipeline memory-constant and allows you to inspect partial results before the full run completes.

Choosing chunksize is a balance: too small means many Python loop iterations and high overhead; too large means chunks don't fit in RAM. A practical approach is to load one chunk, measure its memory with chunk.memory_usage(deep=True).sum(), and set chunksize so each chunk uses roughly 10–20% of available RAM. Python's psutil.virtual_memory().available gives available RAM at runtime, enabling adaptive chunksize calculation.

When accumulating many chunk DataFrames in a list and then concatenating, be aware that calling pd.concat on hundreds of small frames is slow due to repeated memory allocation. A better pattern is to aggregate within each chunk and only store the small aggregated result, not the full chunk. If you truly need all rows, writing to a Parquet file incrementally (using pyarrow) is faster than pd.concat at the end.

Test your understanding of Data Analysis concepts from this lesson.

In this lesson you learned: chunksize in pd.read_csv returns an iterator of DataFrames enabling memory-efficient large-file processing, usecols and dtype arguments reduce per-chunk memory and speed up parsing, and running accumulators (sum, count, partials) avoid building up a list of all chunks. Next up we look at incremental aggregation patterns across chunks in more depth.