## 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.

```text
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:

```python
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.

2. Chunk the read and aggregate per chunk:

```python
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).

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

```python
# 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.

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

```sql
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/pst_hcxh36Xt_Vgx270Z5MPnoA