Read a large CSV in chunks with consistent aggregates

Use read_csv(chunksize=...) to limit the dataframe batch loaded at one time, then combine aggregates according to their mathematical properties. Sums and counts can be accumulated; means require sums and counts; exact distinct counts and cross-chunk deduplication require additional state.

A correct result should not depend on where a chunk boundary happens to fall. Test several chunk sizes, including sizes that separate duplicate records.

Reproduce cross-chunk duplicate handling

The pandas quality lab contains a repeated P03 near the end of its nine-row source. Deduplicating each chunk independently can miss the earlier copy.

This example retains a global key dictionary and computes the Paid known subtotal. It streams dataframe batches but does not claim bounded memory for the key dictionary.

import pandas as pd
from build_and_verify import ROOT, ORDER_FIELDS

def aggregate_chunks(size):
    seen = {}
    raw_count = replays = paid_orders = missing_paid = subtotal = 0
    with pd.read_csv(ROOT / 'orders_raw.csv', dtype='string',
                     keep_default_na=False, chunksize=size) as reader:
        for chunk in reader:
            if list(chunk.columns) != ORDER_FIELDS:
                raise ValueError('unexpected_schema')
            amount = chunk['amount_paise']
            if not (amount.eq('') | amount.str.fullmatch(r'0|[1-9][0-9]*')).all():
                raise ValueError('invalid_amount')
            pd.to_datetime(chunk['order_date'], format='%Y-%m-%d', errors='raise')
            if not chunk['status'].isin(['Paid', 'Pending']).all():
                raise ValueError('invalid_status')
            for row in chunk.itertuples(index=False, name=None):
                raw_count += 1
                order_id, customer_id, date, amount_text, status, category = row
                if not order_id or not customer_id:
                    raise ValueError('missing_key')
                if order_id in seen:
                    if seen[order_id] != row:
                        raise ValueError('conflicting_order_key')
                    replays += 1
                    continue
                seen[order_id] = row
                if status == 'Paid':
                    paid_orders += 1
                    if amount_text == '':
                        missing_paid += 1
                    else:
                        subtotal += int(amount_text)
    return raw_count, len(seen), replays, paid_orders, missing_paid, subtotal

expected = (9, 8, 1, 7, 1, 57000)
for size in [1, 2, 4, 100]:
    assert aggregate_chunks(size) == expected
print('Four chunk sizes produce identical controls')

The read_csv reference documents chunk iteration and dtype options. Explicit string loading prevents a different chunk from inferring a different identifier type.

Combine means through their components

Suppose one chunk contains one observed amount and another contains five. Averaging the two chunk means gives each chunk equal weight, not each order. Accumulate the observed-value sum and observed-value count, then divide once.

For the fixture, six observed Paid amounts sum to 57,000 paise, giving an observed mean of 9,500 paise. The seventh Paid amount is missing and must remain visible in coverage.

Identify state that still grows

The seen dictionary above stores each unique order's validated source tuple so a conflicting replay can be detected. Its size grows with unique orders. Replacing it with a set saves some information but prevents checking whether a repeated key carries changed values.

For a larger production source, use a persistent staging table with key constraints or an external sorting strategy appropriate to the ingestion contract. Exact distinct-customer counts likewise need global identity state; summing per-chunk distinct counts is generally incorrect.

Keep output provisional until completion

A malformed late chunk can fail after earlier totals were accumulated. Do not publish those totals as complete. The example returns only after the reader finishes, but a production workflow that writes incremental output needs an explicit completion marker or transactional publication design.

Chunk size is a performance parameter, not a business rule. Changing it should leave accepted records, rejected outcomes and aggregates unchanged under the same contract.

Exercise: move the repeated P03 to several positions and add a conflicting amount under that key. Confirm that chunk size does not affect replay detection and that every conflicting version causes failure.

NeuraPath's Data Analytics with Generative AI course connects pandas file processing with reconciliation. A scalable report preserves correctness across batches and states honestly which structures still require memory.

Continue learning

This article is part of the Pandas wrangling and data checks sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in Categorical data: reduce memory without losing unknown values.
• Continue with Pandas resample: make absent days visible.