Python Doc & Data Automation

Fix Wrong Totals When Aggregating CSV Chunks

The chunked summary of a 6 GB orders file looks plausible and is wrong. Revenue matches the source system, but average order value is off by a few percent, the customer count is far too high, the median is nonsense, and the "share of cancelled orders" figure differs from the one in the dashboard:

parts = []
for chunk in pd.read_csv(path, chunksize=500_000):
    parts.append(chunk.groupby("region").agg(
        revenue=("amount", "sum"),
        avg_order=("amount", "mean"),
        customers=("customer_ref", "nunique"),
        median_order=("amount", "median"),
    ))
summary = pd.concat(parts).groupby(level=0).mean()      # or .sum() — both are wrong

Root Cause

Aggregations differ in whether they can be computed from partial results. Sums and counts are additive: the sum of per-chunk sums equals the total sum, whatever the chunk boundaries. Means are not — the mean of per-chunk means weights each chunk equally, so a chunk containing 12 rows counts as much as one containing 500,000. Distinct counts are not additive either: a customer appearing in three chunks is counted three times when the per-chunk counts are summed. Medians and other quantiles cannot be derived from per-chunk medians at all. Minimum and maximum do combine; variance and standard deviation combine only with a specific formula using counts and sums of squares. The chunk loop hides this because every aggregation looks the same in the code — the failure is silent, produces plausible numbers, and is discovered when someone compares the report with another system.

Minimal Diagnostic

Compute the same aggregations on the whole file (on a subset small enough to load) and via chunks, then compare. The columns that differ are the ones that do not combine.

# pip install "pandas>=2.2"
from pathlib import Path
import pandas as pd

SOURCE = Path("in/orders-sample.csv")          # a subset that fits in memory
AGGS = {"revenue": ("amount", "sum"), "orders": ("amount", "size"),
        "avg_order": ("amount", "mean"), "customers": ("customer_ref", "nunique"),
        "median_order": ("amount", "median"), "max_order": ("amount", "max")}

def compare_paths(path: Path, chunksize: int = 25_000) -> pd.DataFrame:
    whole = pd.read_csv(path, dtype={"customer_ref": "string"}).groupby("region").agg(**AGGS)
    parts = [chunk.groupby("region").agg(**AGGS)
             for chunk in pd.read_csv(path, chunksize=chunksize, dtype={"customer_ref": "string"})]
    naive = pd.concat(parts).groupby(level=0).agg(
        revenue=("revenue", "sum"), orders=("orders", "sum"), avg_order=("avg_order", "mean"),
        customers=("customers", "sum"), median_order=("median_order", "mean"),
        max_order=("max_order", "max"))
    diff = (naive - whole) / whole.replace(0, pd.NA)
    return pd.DataFrame({"whole_file": whole.iloc[0], "chunked": naive.iloc[0],
                         "relative_error": diff.iloc[0].round(4)})

if __name__ == "__main__":
    print(compare_paths(SOURCE).to_string())

               whole_file       chunked  relative_error
revenue       18904221.55   18904221.55          0.0000
orders             812043        812043          0.0000
avg_order           23.28         21.94         -0.0576
customers           44120        118904          1.6950
median_order        14.90         18.02          0.2094
max_order          998.00        998.00          0.0000

Revenue, order count and maximum are exact. Average order value is 5.8 percent low, the customer count is nearly three times too high, and the median is wrong by a fifth.

Which aggregations combine across chunks Sum, count, minimum and maximum combine directly. Mean requires carrying the sum and the count per chunk and dividing at the end. Variance and standard deviation require count, sum and sum of squares. Distinct count requires a set or a sketch of the values seen. Median and percentiles cannot be combined from per-chunk values and need either a full pass, a histogram or an approximate digest. Aggregation Combines directly Carry per chunk Finish with sum, count, min, max yes the value itself add or take extremes mean no sum and count sum / count var, std no count, sum, sum of squares pooled formula nunique no a set or sketch union then size median, quantile no histogram or digest interpolate

Fix: Carry Combinable State per Chunk

Accumulate the pieces each aggregation actually needs — sums, counts, sets — and derive the final figures once at the end. Changed lines carry comments.

# pip install "pandas>=2.2"
from pathlib import Path
import numpy as np
import pandas as pd

SOURCE = Path("in/orders-2026.csv")

def chunked_summary(path: Path, chunksize: int = 500_000) -> pd.DataFrame:
    totals: dict[str, pd.DataFrame] = {}
    customer_sets: dict[str, set[str]] = {}
    for chunk in pd.read_csv(path, chunksize=chunksize,
                             dtype={"customer_ref": "string", "region": "category"},
                             usecols=["region", "customer_ref", "amount", "status"]):
        grouped = chunk.groupby("region", observed=True).agg(                 # changed: additive parts only
            revenue=("amount", "sum"),
            orders=("amount", "size"),
            sum_sq=("amount", lambda s: float((s ** 2).sum())),               # changed: for variance
            max_order=("amount", "max"),
            min_order=("amount", "min"),
            cancelled=("status", lambda s: int((s == "cancelled").sum())),    # changed: count, not share
        )
        totals["frame"] = grouped if "frame" not in totals else \
            pd.concat([totals["frame"], grouped]).groupby(level=0).agg(       # changed: combine additively
                revenue=("revenue", "sum"), orders=("orders", "sum"), sum_sq=("sum_sq", "sum"),
                max_order=("max_order", "max"), min_order=("min_order", "min"),
                cancelled=("cancelled", "sum"))
        for region, refs in chunk.groupby("region", observed=True)["customer_ref"]:
            customer_sets.setdefault(str(region), set()).update(refs.dropna())  # changed: union of sets
    summary = totals["frame"]
    summary["avg_order"] = summary["revenue"] / summary["orders"]              # changed: derive at the end
    summary["cancelled_share"] = summary["cancelled"] / summary["orders"]      # changed: ratio of totals
    variance = summary["sum_sq"] / summary["orders"] - summary["avg_order"] ** 2
    summary["std_order"] = np.sqrt(variance.clip(lower=0) * summary["orders"] /
                                   (summary["orders"] - 1).clip(lower=1))      # sample std
    summary["customers"] = pd.Series({k: len(v) for k, v in customer_sets.items()})
    return summary.drop(columns=["sum_sq"]).sort_values("revenue", ascending=False)

if __name__ == "__main__":
    print(chunked_summary(SOURCE).round(2).to_string())

Every derived figure is computed from totals, never from per-chunk derived figures. The exact set of customer references is memory-bounded by the number of distinct customers, not by rows — 44,000 short strings is a few megabytes, which is fine; ten million would not be, and needs the sketch in the next section. The variance formula from sums and sums of squares is numerically acceptable for business amounts but can lose precision with very large values; Welford's method is the alternative when precision matters more than simplicity.

What to carry between chunks Each chunk contributes a sum of amounts, a row count, a sum of squares, a minimum, a maximum, a count of cancelled rows and the set of customer references it contains. These are combined additively into running totals and a union of the sets. After the last chunk, average order value, cancelled share, standard deviation and distinct customer count are derived once from the totals. Read chunk selected columns Additive parts sum, count, sumsq, min, max Distinct keys union of sets Combine add and union Derive metrics mean, share, std One summary per group

Variant Fix 1: Too Many Distinct Values for a Set

When the distinct count is over millions of keys, exact sets become expensive. A HyperLogLog sketch gives a distinct count within about 1 percent using a few kilobytes per group:

# pip install datasketch "pandas>=2.2"
from datasketch import HyperLogLog
import pandas as pd

def sketched_distinct(chunks, group_col: str, key_col: str, precision: int = 14) -> pd.Series:
    sketches: dict[str, HyperLogLog] = {}
    for chunk in chunks:
        for group, keys in chunk.groupby(group_col, observed=True)[key_col]:
            hll = sketches.setdefault(str(group), HyperLogLog(p=precision))
            for key in keys.dropna().astype(str):
                hll.update(key.encode("utf-8"))                  # sketches merge; sets are exact but larger
    return pd.Series({group: int(hll.count()) for group, hll in sketches.items()})

Use a sketch only where an approximate answer is acceptable — audience sizes, distinct product views — and never for figures that must reconcile with another system, such as customer counts on an invoice. State in the report which figures are approximate; a footnote costs nothing and prevents a long investigation into a 0.6 percent discrepancy.

Variant Fix 2: Medians and Percentiles

Quantiles need either a second pass or a digest structure. For moderate cardinality, a histogram of the values with fine bins gives a median accurate to the bin width in one pass:

# pip install "pandas>=2.2" numpy
import numpy as np
import pandas as pd

BINS = np.concatenate([np.arange(0, 100, 0.5), np.arange(100, 1000, 5), np.arange(1000, 100_001, 100)])

def histogram_quantile(chunks, group_col: str, value_col: str, q: float = 0.5) -> pd.Series:
    counts: dict[str, np.ndarray] = {}
    for chunk in chunks:
        for group, values in chunk.groupby(group_col, observed=True)[value_col]:
            hist, _ = np.histogram(values.dropna(), bins=BINS)
            counts[str(group)] = counts.get(str(group), np.zeros(len(BINS) - 1)) + hist
    out = {}
    for group, hist in counts.items():
        cumulative = np.cumsum(hist)
        target = q * cumulative[-1]
        idx = int(np.searchsorted(cumulative, target))
        lo, hi = BINS[idx], BINS[idx + 1]
        before = cumulative[idx - 1] if idx else 0
        within = (target - before) / max(hist[idx], 1)
        out[group] = lo + within * (hi - lo)                  # linear interpolation inside the bin
    return pd.Series(out)

Bin widths determine accuracy: fine bins where the data is dense, coarse bins in the long tail. For exact quantiles, sort the column once — or let a query engine do it, which is the simplest answer at this scale, as shown in query large CSV files with DuckDB.

Relative error by aggregation in the naive chunk loop Combining per-chunk results naively leaves revenue and order count exact with zero error and the maximum exact. Average order value is 5.8 percent low because chunk means were averaged. The median is 20.9 percent wrong because chunk medians were averaged. The distinct customer count is 169 percent too high because per-chunk distinct counts were summed. naive combination versus whole-file computation revenue (sum) 0.0% orders (count) 0.0% avg_order (mean) 5.8% median_order 20.9% customers (nunique) 169.5%

Making the Trap Visible in Code

The underlying problem is that the chunk loop lets any aggregation be written where only additive ones are valid. Encode that rule so the wrong thing cannot be written by accident:

# pip install "pandas>=2.2"
import pandas as pd

ADDITIVE = {"sum", "size", "count", "min", "max"}

def combine_partials(parts: list[pd.DataFrame], how: dict[str, str]) -> pd.DataFrame:
    unsupported = {col: fn for col, fn in how.items() if fn not in ADDITIVE}
    if unsupported:
        raise ValueError(
            f"these columns cannot be combined across chunks: {unsupported}. "
            "Carry the additive parts instead (sum and count for a mean, sets for nunique)."
        )
    combined = pd.concat(parts)
    return combined.groupby(level=0).agg(**{col: (col, fn) for col, fn in how.items()})

A helper that refuses to combine a mean is worth more than a comment: the next person to add a metric to the report gets an error that explains the fix, at the moment they write the code rather than after finance queries the numbers.

Verification

Reconcile chunked results against a whole-file computation on the largest sample that fits in memory, and against control totals from the source system.

# pip install "pandas>=2.2"
from pathlib import Path
import pandas as pd

def verify_chunked(path: Path, sample_rows: int = 300_000, tolerance: float = 1e-9) -> None:
    sample = pd.read_csv(path, nrows=sample_rows, dtype={"customer_ref": "string"})
    sample_path = path.with_name("sample-" + path.name)
    sample.to_csv(sample_path, index=False)
    chunked = chunked_summary(sample_path, chunksize=25_000)
    whole = sample.groupby("region", observed=True).agg(
        revenue=("amount", "sum"), orders=("amount", "size"),
        customers=("customer_ref", "nunique"), avg_order=("amount", "mean"))
    for col in ("revenue", "orders", "customers"):
        pd.testing.assert_series_equal(chunked[col].sort_index().astype(float),
                                       whole[col].sort_index().astype(float), check_names=False)
    assert ((chunked["avg_order"].sort_index() - whole["avg_order"].sort_index()).abs() < 1e-6).all(), \
        "average order value differs between chunked and whole-file computation"
    sample_path.unlink(missing_ok=True)
    print(f"chunked aggregation matches whole-file results on {len(sample):,} rows")

Run it in the test suite with a deliberately small chunk size, so the sample is split into many chunks — a bug in combination logic that a two-chunk test misses shows up immediately with twelve.

FAQ

Is concat then groupby always wrong? No — it is exactly right when every aggregation is additive. The error is applying it to means, distinct counts and quantiles.

Does Categorical dtype affect group results? Only the set of groups that appear; pass observed=True so empty categories do not create zero rows, as covered in fix pandas pivot_table missing categories.

Can I use Dask or Polars instead? Yes. Both implement correct distributed aggregations, so the combination logic is handled for you — as does DuckDB, which needs no rewrite of the data pipeline.

How do I handle weighted averages? Carry the sum of value * weight and the sum of weight per chunk, and divide at the end — the same pattern as the mean.

Part of Handling Large CSV Files with Chunking.