Pandas CSV Chunking: Correct Totals and Averages

To aggregate a CSV in pandas chunks, carry forward each group's sum and nonmissing count, then calculate its average once at the end. Also count every input row, including rows with missing amounts. A smaller chunk changes how much data you hold at once; it must not change the answer.

This worked example produces exact integer-cent totals and averages for a small number of teams. It rejects invalid input, limits the number of retained groups, and checks the same result with four chunk sizes. You can use the pattern when a recurring CSV report has become too large to load comfortably in one DataFrame.

Why averaging chunk means is wrong

Suppose one chunk contains amounts of 100 and 300 cents. Its mean is 200. Another chunk contains one amount of 900 cents. Averaging the two chunk means gives 550, but the correct overall mean is (100 + 300 + 900) / 3 = 1300 / 3, approximately 433.33 cents.

The chunks have different numbers of observations. Missing amounts create the same problem even when chunks have equal row counts. A chunk with ten rows but one recorded amount contributes one observation to the amount average, not ten.

Carry three values for each group: input rows, nonmissing amounts, and total cents. Add those values across chunks. Divide the final total by the final nonmissing count. For a group with no recorded amounts, return an unknown average rather than zero.

Define the input and expected answer

The fictional CSV has one row per transaction. Amounts are whole cents in one currency; negative amounts represent adjustments. Blank amounts mean unknown, while 0 is a recorded zero. Team names are case-sensitive after trimming surrounding whitespace. A blank team is invalid. Only blank amount fields are accepted as missing; strings such as NA require an explicit source agreement before changing that rule.

team,amount_cents
North,1000
South,2000
North,
West,
South,-500
North,0
South,500
North,600
West,

North has four rows, three recorded amounts, and 1,600 cents: its average is exactly 1600 / 3. South has three rows and three recorded amounts totaling 2,000 cents: its average is 2000 / 3. West has two rows and no recorded amounts. Its observed sum is zero and its average is missing; that sum does not establish that its actual transactions were worth zero.

The file has nine data rows, six recorded amounts, and 3,600 observed cents. Its overall recorded-amount average is 600 cents. These independent expected values will catch errors that row counts alone cannot detect.

A complete chunked aggregation function

pandas.read_csv returns an iterable reader when you specify chunksize. The example selects two columns and reads them as strings, avoiding inferred types. Its explicit missing-value settings preserve a literal team called NA. Missing required columns raise an error.

The amount check accepts signed integer text and rejects decimal or scientific notation. Conversion to pandas' nullable Int64 type retains missing amounts and rejects values outside signed 64-bit range. Each amount is converted to a Python integer before addition, so a group total can exceed that range without overflowing an integer accumulator. Fraction keeps the final teaching average exact; None marks an unavailable average.

from fractions import Fraction

import pandas as pd


def summarize_csv(source, chunksize=100_000, max_groups=1000,
                  expected_rows=None):
    for name, value in (("chunksize", chunksize), ("max_groups", max_groups)):
        if isinstance(value, bool) or not isinstance(value, int) or value < 1:
            raise ValueError(f"{name} must be a positive integer")
    if expected_rows is not None:
        if (isinstance(expected_rows, bool)
                or not isinstance(expected_rows, int) or expected_rows < 0):
            raise ValueError("expected_rows must be a nonnegative integer")
    state = {}  # team -> [rows, nonmissing amounts, total cents]
    rows_read = 0
    with pd.read_csv(
        source, usecols=["team", "amount_cents"], dtype="string",
        keep_default_na=False, skip_blank_lines=False,
        chunksize=chunksize, engine="c", on_bad_lines="error",
    ) as reader:
        for chunk in reader:
            team = chunk["team"].str.strip()
            if team.isna().any() or team.eq("").any():
                raise ValueError("Blank team: correct the source before analysis")
            raw = chunk["amount_cents"].str.strip()
            missing = raw.isna() | raw.eq("")
            invalid = ~missing & ~raw.str.fullmatch(r"[+-]?[0-9]+", na=False)
            if invalid.any():
                raise ValueError("amount_cents must contain integer cents or blanks")
            # Reject values outside signed Int64; never pass through float.
            chunk["amount_cents"] = raw.mask(missing).astype("Int64")
            chunk["team"] = team
            rows_read += len(chunk)
            for key, amounts in chunk.groupby("team", sort=False)["amount_cents"]:
                if key not in state:
                    if len(state) >= max_groups:
                        raise ValueError("Group limit exceeded; use disk-backed aggregation")
                    state[key] = [0, 0, 0]
                subtotal = sum(int(value) for value in amounts.dropna())
                state[key][0] += len(amounts)
                state[key][1] += int(amounts.count())
                state[key][2] += subtotal
    if sum(values[0] for values in state.values()) != rows_read:
        raise RuntimeError("Group row counts do not reconcile")
    if expected_rows is not None and rows_read != expected_rows:
        raise ValueError(f"Expected {expected_rows} rows, read {rows_read}")
    result = {
        key: (rows, count, total, Fraction(total, count) if count else None)
        for key, (rows, count, total) in sorted(state.items())
    }
    return rows_read, result

The returned tuple contains total rows read and a dictionary of groups. Each group's tuple contains (rows, nonmissing_count, total_cents, mean_cents). No list of chunks is accumulated or concatenated. A group appearing in the first and last chunks updates the same state entry.

Keep the file unchanged during a run. Validate the export's field structure separately; selected-column parsing is not a complete CSV schema check. Supply expected_rows from a trusted export manifest when available. Counting the grouped rows checks internal reconciliation; comparing against a separately supplied count can catch a truncated export. Neither proves that every amount is correct. Add the source's known control total and any required period or currency checks before using a real report.

Verify across chunk boundaries

Run this block after the function. It creates the fixture in memory, independently aggregates the complete small DataFrame, checks the known answer, and compares chunk sizes 1, 2, 3, and 100. The two-row chunks leave an uneven final chunk; groups and missing amounts span boundaries.

from io import StringIO

FIXTURE = """team,amount_cents
North,1000
South,2000
North,
West,
South,-500
North,0
South,500
North,600
West,
"""

EXPECTED = {
    "North": (4, 3, 1600, Fraction(1600, 3)),
    "South": (3, 3, 2000, Fraction(2000, 3)),
    "West": (2, 0, 0, None),
}

def full_memory_fixture():
    full = pd.read_csv(StringIO(FIXTURE), dtype={"amount_cents": "Int64"})
    reference = {}
    for team, part in full.groupby("team"):
        values = part["amount_cents"]
        count = int(values.count())
        total = int(values.sum())  # Small fixture: sum fits in signed Int64.
        reference[team] = (
            len(part), count, total, Fraction(total, count) if count else None
        )
    return len(full), reference


def check_article_fixture():
    full_rows, full_result = full_memory_fixture()
    assert (full_rows, full_result) == (9, EXPECTED)
    for size in (1, 2, 3, 100):
        rows, result = summarize_csv(StringIO(FIXTURE), chunksize=size,
                                     expected_rows=9)
        assert rows == full_rows and result == full_result == EXPECTED
    assert sum(item[1] for item in EXPECTED.values()) == 6
    assert sum(item[2] for item in EXPECTED.values()) == 3600
    print("All four chunk sizes match the known full-memory result.")

check_article_fixture()

The output should be All four chunk sizes match the known full-memory result. For a real file, call summarize_csv("amounts.csv", chunksize=100_000, max_groups=1000, expected_rows=export_row_count). The values shown are starting parameters, not a promise that this chunk size fits every machine.

Before adapting the function, also test a missing header, blank team, malformed amount, an unexpected row count, and a group count above the cap. Test amounts near the signed 64-bit limit: two individually valid amounts can produce a larger total. These failures must stop processing; silently dropping malformed input creates plausible but incomplete reports.

Which operations can cross chunks?

Sums and counts combine by addition. Means combine through their sum and count. Minima and maxima can retain one value per group. An exact median needs more information, and adding per-chunk distinct counts is wrong when the same value occurs in multiple chunks.

Similarly, drop_duplicates() inside each chunk does not remove duplicates crossing a boundary. An exact global distinct set can grow with the entire input. A join may require a complete lookup table or disk-backed processing, plus checks that its keys have the intended uniqueness. See the guide to pandas merges with repeated keys before combining transaction data.

Chunking is therefore an algorithm choice. If the required state is unbounded, change the processing strategy rather than repeatedly reducing the chunk size. The SQL versus Python workflow guide helps decide where the aggregation belongs.

Measure memory and time locally

The working state depends on both the current chunk and the number of distinct groups. The cap prevents accidentally retaining millions of customer IDs as “teams.” It does not impose a byte-level memory limit: long group labels, strings, temporary conversions, and parser buffers also use memory.

Measure a representative chunk with chunk.memory_usage(deep=True).sum(), and observe peak process memory while running the complete workflow. That DataFrame measurement excludes the dictionary, parser, and temporary objects. Time reading and aggregation separately with time.perf_counter(); repeating a read may benefit from the operating system's file cache. CSV size on disk is not a reliable estimate of peak RAM.

This example deliberately uses Python integer summation for correctness. That loop trades some throughput for exact totals. Benchmark it on your data before increasing chunk size or replacing it with a fixed-width vectorized sum. If throughput is insufficient, use an appropriate database numeric type and verify identical fixture results.

FAQ

Does low_memory=True enable streaming?

No. The read_csv reference distinguishes internal parsing from returning chunks: without chunksize or an iterator, the result is still a single DataFrame.

Can read_excel use chunksize?

pandas.read_excel has no chunksize parameter. Prefer a correctly exported CSV for this example. If a workbook is the only source, choose a workbook-reading strategy separately and reconcile its export before aggregating.

How should I display the average?

Retain the exact cents total and observation count. Round only the final displayed mean using the report's agreed rounding rule, and label missing averages explicitly. For more file-loading basics, use Pandas read and write files; for familiar SQL operations, use Pandas for SQL users.