Handling Out-of-Memory CSV Processing with Pandas Chunking

When you attempt to load a 10GB CSV file on a machine with 8GB of RAM, Python will typically crash with a MemoryError. The default behavior of pandas is to load the entire dataset into heap memory as a single DataFrame. However, many data engineering tasks do not require the entire dataset at once to perform transformation, filtering, or aggregation.

The solution is using the chunksize parameter in read_csv(). Instead of returning a massive DataFrame, pandas returns a TextFileReader object that allows you to iterate through the file in manageable segments, keeping your memory footprint stable regardless of the total file size.

Understanding the TextFileReader

When you specify an integer for chunksize, pandas changes its execution strategy. The resulting object is an iterator. Each time you loop through this iterator, pandas yields a standard DataFrame containing the number of rows specified. Once the loop iteration finishes, the memory used by that chunk can be garbage collected, preventing the linear memory growth that leads to crashes.

Practical Example: Filtered Aggregation

In this scenario, we have a massive transaction log file. We want to filter for "completed" transactions and calculate the total revenue, but the file is too large to fit in memory. We will process it in chunks.

import pandas as pd

# Configuration
input_file = 'massive_transactions.csv'
output_file = 'filtered_transactions.csv'
chunk_size = 100000 # Process 100k rows at a time

total_revenue = 0

# Initialize the TextFileReader
reader = pd.read_csv(input_file, chunksize=chunk_size)

for i, chunk in enumerate(reader):
    # 1. Filter the chunk immediately
    filtered_chunk = chunk[chunk['status'] == 'completed']

    # 2. Perform a partial aggregation
    total_revenue += filtered_chunk['amount'].sum()

    # 3. Stream the filtered data to a new file
    # Use mode='w' for the first chunk and 'a' for subsequent
    header = True if i == 0 else False
    filtered_chunk.to_csv(output_file, mode='a', index=False, header=header)

    print(f"Processed chunk {i+1}...")

print(f"Processing complete. Total Revenue: {total_revenue}")

The Critical Trade-offs and Bottlenecks

While chunking solves the memory problem, it introduces logic complexities that you must account for:

• Global Operations: You cannot perform a sort_values() or median() on the whole dataset because pandas only "sees" the current chunk. To sort, you must implement a multi-pass strategy (e.g., sorting individual chunks and then merging them).
• Type Inconsistency: pandas guesses data types by looking at the data. If a column contains integers in the first chunk but a string in the tenth chunk, your code may fail during concatenation. Always define the dtype argument in read_csv to ensure consistency.
• I/O Overhead: Setting a chunksize too small (e.g., 100 rows) creates massive overhead because the Python loop and frequent disk I/O will slow down the execution significantly. Aim for chunks between 50,000 and 500,000 rows depending on your row width.

Verification Strategy

To verify your chunking logic is working correctly, use a system monitor (like top or Task Manager) while the script runs. If the memory usage stays flat throughout the process rather than climbing steadily, your chunking strategy is successfully implemented. You can also verify the output by comparing the row count of the processed output CSV against the expected filtered count.