data agent ran out of memory on pandas groupby: chunking fix

TL;DR

The agent loads the full frame and groupbys in memory, which explodes when the group key has high cardinality. Fix it by chunking: process the file in chunks, aggregate per chunk, then combine the partial aggregates. Better yet, push the groupby into the database or use an out-of-core frame.

data agent ran out of memory on pandas groupby: chunking fix

Use this when

• A data agent OOMs on a pandas groupby
• The group key has millions of distinct values
• The agent reads the whole file before aggregating

Not for this skill when

• The groupby logic is wrong (thats the aggregation, not memory)
• It is slow but fits (thats performance tuning)
• The data lives in a warehouse (aggregate there, not in pandas)

Steps

1. Confirm the cardinality is the problem:

print(df["user_id"].nunique(), len(df))

Expected output: nunique close to len(df) means nearly every row is its own group; the result is as big as the input plus overhead.

1. Chunk the read and aggregate per chunk:

partials = []
for chunk in pd.read_csv("huge.csv", chunksize=200000):
    partials.append(chunk.groupby("user_id")["amount"].agg(["sum", "count"]))
combined = pd.concat(partials).groupby(level=0).sum()

Expected output: memory stays flat at roughly one chunk plus the growing aggregate. The second groupby merges partial sums correctly for sum/count (recompute means from them, dont average averages).

1. For non-additive aggregations, collect per-chunk pieces and finalize after:

# per chunk: store sum and count; finalize mean = total_sum / total_count

Expected output: correct results for mean, std, and distinct counts via the two-pass pattern. Chunking only works when the aggregation decomposes.

1. If the agent controls the query layer, push the groupby down:

SELECT user_id, SUM(amount), COUNT(*) FROM events GROUP BY user_id

Expected output: the database does the aggregation and pandas sees only the result. This beats any chunking scheme.

Variant phrasings

agent OOMs even with chunksize

The partial aggregates themselves are too big (cardinality near row count). Push to the database or use DuckDB/Polars streaming.

groupby works on a sample, dies on full data

Cardinality scales with data. The sample hid it; the fix is structural, not a bigger machine.

Why it happens

pandas groupby materializes groups in memory, and high-cardinality keys make the intermediate structures enormous. Agents default to read-everything-then-groupby because it is the simplest code, and it works right up until the data outgrows RAM. Chunking works because most aggregations are decomposable: partial results combine into the exact final answer.

Edge cases

• Median and quantiles dont decompose by chunk; use approximate methods or a database.
• observed=True on categorical groupers avoids materializing unused categories, which alone can fix the OOM.
• Downcasting dtypes before grouping (int64 to int32, object to category) shrinks every chunk.

Provenance

Resolved from the public thread: https://vectle.com/posts/psthcxh36XtVgx270Z5MPnoA