Process Large CSV Files with pandas chunked reading

Desired outcome

Read a CSV file that exceeds available RAM, apply transformations to each row, and compute aggregated results (such as sums, counts, or averages) without loading the whole file into memory.

Prerequisites

• Python 3.8 or newer.
• pandas library installed (version ≥ 1.0). Install with pip install pandas.
• The target CSV file accessible from the script’s working directory.
• Optional: tqdm for a progress bar (pip install tqdm).

Procedure

1. Choose a chunk size. Estimate the memory you can spare per iteration. A rough guideline is chunksize = int(available_RAM_bytes / (bytes_per_row * number_of_columns)). Start with a value like 50 000 rows and adjust based on observed memory usage.
2. Create the chunk iterator. Use pd.read_csv with the chunksize argument.
3. Initialize accumulators. For each metric you need, declare a variable that will hold the partial result (e.g., total_sum = 0.0, total_count = 0).
4. Iterate over chunks. Inside the loop:
   • Apply any per‑row transformations (filtering, deriving new columns, etc.).
   • Update the accumulators using vectorized operations on the chunk (e.g., total_sum += chunk['value'].sum()).
   • If you need to keep intermediate data (e.g., for a later join), store it in a list and concatenate after the loop, but be aware of memory impact.
5. Optionally show progress. Wrap the iterator with tqdm to see how many chunks have been processed.
6. Combine results. After the loop finishes, compute final metrics from the accumulators (e.g., average = total_sum / total_count).
7. Write or return the result. Save to a file, print, or pass to downstream code.

Expected checks

• Row count consistency: keep a running total of rows processed (total_rows += len(chunk)) and verify it matches the known line count of the CSV (or a quick wc -l on Unix).
• Metric sanity: run the same aggregation on a small subset (e.g., first 10 000 rows) without chunking and compare the result to the chunk‑based outcome; they should match within floating‑point tolerance.
• Memory usage: monitor with top, htop, or a Python profiler (memory_profiler) and confirm that RSS stays low and stable throughout the loop.

Recovery options

If the script stops unexpectedly (I/O error, keyboard interrupt, etc.), you can resume from the last successfully processed chunk by:

1. Logging the cumulative row count after each iteration (app.log.info(f'Processed {total_rows} rows')).
2. On restart, open the CSV and skip already‑processed rows using skiprows (note: this works only if the file is not being modified). Example:

import pandas as pd

chunk_size = 50000
processed_rows = 0  # load from log or checkpoint
iterator = pd.read_csv('large.csv', chunksize=chunk_size, skiprows=range(1, processed_rows+1))

for chunk in iterator:
    # … process chunk …
    processed_rows += len(chunk)
    # checkpoint processed_rows here

When the file is being appended to, you can instead record the file’s byte offset using tell() on the file object, but that requires a custom iterator; the simple row‑count method suffices for static files.

Limitations

• Operations that depend on the order of rows across chunks (e.g., cumulative sum, shift, rolling windows) cannot be performed directly; you would need to carry over state manually or avoid chunking for those calculations.
• Very small chunk sizes increase Python loop overhead and can slow down processing; very large chunks may exceed memory. Tune based on empirical memory profiling.
• The CSV must be readable sequentially; compressed formats (e.g., .gz) work with pandas but the chunk iterator still reads the decompressed stream, so memory considerations remain.