Pandas GroupBy, Explained the Way You Wish It Had Been

Most people meet groupby the same way: they copy a snippet from Stack Overflow, it produces a table that looks roughly right, and from that day on they treat it as an incantation. Then one day they need the group average next to each original row instead of in a summary table, and the incantation stops working.

The fix is to understand that GroupBy is not one operation. It is three, chained together: split, apply, combine. Once you can name which of the three you're customizing, every variant becomes predictable.

The mental model: split → apply → combine

Take a concrete table:

import pandas as pd

df = pd.DataFrame({
    "city":  ["NY", "NY", "LA", "LA", "LA", "CHI"],
    "rep":   ["Ana", "Ben", "Ana", "Cal", "Ben", "Cal"],
    "sales": [120,   90,    200,   40,    155,   80],
})

df.groupby("city") does the split: it partitions the rows into three invisible sub-tables — one for NY, one for LA, one for CHI. Nothing is computed yet. If you print the groupby object you get something unhelpful like <DataFrameGroupBy object>, which is pandas telling you: "I've made the piles, now tell me what to do with them."

The apply step is whatever you call next — mean(), sum(), agg(...). It runs once per pile.

The combine step is how pandas stitches the per-pile results back together — and this is where agg and transform part ways.

agg: one row per group

df.groupby("city")["sales"].mean()
# city
# CHI     80.0
# LA     131.7
# NY     105.0

agg (and its shortcuts like .mean()) collapses each group to a single row. Six input rows became three output rows. This is the "summary table" shape — great for reporting.

The most readable way to do several aggregations at once is named aggregation, which too few people use:

df.groupby("city").agg(
    total_sales=("sales", "sum"),
    avg_sales=("sales", "mean"),
    n_reps=("rep", "nunique"),
)

Each keyword becomes a column name; each tuple is (source_column, function). No MultiIndex headaches, no renaming afterwards.

transform: same shape as the input

Now the situation that breaks the incantation: you want each row to carry its own city's average, so you can compute how each sale compares to its local norm.

df["city_avg"] = df.groupby("city")["sales"].transform("mean")
df["vs_avg"]   = df["sales"] / df["city_avg"]

transform runs the same per-group computation but broadcasts the result back to the original rows — the output has six rows, aligned to the input's index, so it drops straight into a new column. That's the entire difference: agg collapses, transform preserves shape.

A remarkable amount of intermediate pandas code is people doing an agg, then merging the summary back onto the original frame. That's transform in two extra steps.

The mistakes everyone makes

1. Forgetting reset_index(). After a groupby-agg, your group keys live in the index, not in columns. If the next step is a merge or a plot that expects columns, call .reset_index(). If you'd rather never think about this, pass as_index=False to groupby.

2. count vs size. size() counts rows per group, full stop. count() counts non-null values per column per group. On clean data they agree; on real data they quietly don't.

3. Aggregating what you should have filtered. If you only want big sales, filter first: df[df.sales > 100].groupby("city").... Filtering after aggregation usually means you wanted a different question entirely.

4. Reaching for .apply() too early. groupby().apply(custom_fn) is the slow path — pandas calls your Python function once per group with no vectorization. It's sometimes necessary, but check whether a combination of built-in aggs and transform gets you there first; it's routinely 10–100× faster.

A worked example, end to end

Question: which sales rep beats their city's average most consistently?

df["city_avg"] = df.groupby("city")["sales"].transform("mean")
df["beat"] = df["sales"] > df["city_avg"]

result = (
    df.groupby("rep")
      .agg(times_beat=("beat", "sum"), deals=("beat", "count"))
      .assign(rate=lambda t: t.times_beat / t.deals)
      .sort_values("rate", ascending=False)
)

Notice the shape of the reasoning: a transform to attach group context to every row, a plain vectorized comparison, then an agg to summarize — split-apply-combine used twice, each time on purpose.

Where to practice

The Pandas module on QuraLabz has interactive lessons where this exact pattern — groupby, named aggregation, transform — runs live in your browser, with datasets you can break and fix. Reading builds familiarity; running code builds instinct.