GroupBy — split, apply, combine

The last lesson left you with a clean, trustworthy table — and the observation that the questions worth answering are almost always per group: revenue per region, sessions per user, conversion per campaign. groupby is the verb that answers all of them, and if you read a day of random pandas on GitHub it is the operation that shows up more than any other. Behind its many uses sits one tiny, universal pattern: split, apply, combine.

The mental model: split, apply, combine

df.groupby("user_id")["revenue"].sum() does three things:

1. Split — divide the rows into groups by user_id. Each unique value gets its own sub-DataFrame.
2. Apply — run the function (sum) independently on each group’s revenue column.
3. Combine — stitch the per-group results back into one Series indexed by user_id.

That’s it. Every groupby fits this template — which is why “per-user total” and “per-country average” have identical syntax; the only thing that changes is which column decides the groups.

df.groupby("region")["sales"].sum()

The setup: an events table

import pandas as pd

events = pd.DataFrame({
    "user_id":  ["u-1","u-2","u-1","u-3","u-2","u-1","u-3","u-1"],
    "event":    ["view","view","purchase","view","purchase","purchase","view","purchase"],
    "ts":       pd.to_datetime([
        "2026-05-20 09:00","2026-05-20 09:10","2026-05-20 09:15",
        "2026-05-21 10:00","2026-05-21 10:05","2026-05-21 11:30",
        "2026-05-22 08:00","2026-05-22 14:20",
    ]),
    "revenue":  [0.0, 0.0, 24.99, 0.0, 49.99, 14.50, 0.0, 99.00],
    "country":  ["IN","US","IN","DE","US","IN","DE","IN"],
})
print(events)

  user_id     event                  ts  revenue country
0     u-1      view 2026-05-20 09:00:00     0.00      IN
1     u-2      view 2026-05-20 09:10:00     0.00      US
2     u-1  purchase 2026-05-20 09:15:00    24.99      IN
3     u-3      view 2026-05-21 10:00:00     0.00      DE
4     u-2  purchase 2026-05-21 10:05:00    49.99      US
5     u-1  purchase 2026-05-21 11:30:00    14.50      IN
6     u-3      view 2026-05-22 08:00:00     0.00      DE
7     u-1  purchase 2026-05-22 14:20:00    99.00      IN

A small event log. We’ll pull per-user, per-country, and per-day metrics from it.

One column, one aggregation

import pandas as pd

events = pd.DataFrame({
    "user_id":  ["u-1","u-2","u-1","u-3","u-2","u-1","u-3","u-1"],
    "revenue":  [0.0, 0.0, 24.99, 0.0, 49.99, 14.50, 0.0, 99.00],
})

# Total revenue per user
print(events.groupby("user_id")["revenue"].sum())
print()

# Average order value per user (just considering purchases > 0)
print(events[events["revenue"] > 0].groupby("user_id")["revenue"].mean().round(2))

user_id
u-1    138.49
u-2     49.99
u-3      0.00
Name: revenue, dtype: float64

user_id
u-1    46.16
u-2    49.99
Name: revenue, dtype: float64

The result is a Series indexed by user_id. Notice the second call filtered to purchases first, so u-3 (zero purchases) drops out of the average entirely. Pass as_index=False to get a DataFrame with user_id as a regular column — handy when piping into a join.

Multiple aggregations with agg

You’ll usually want more than one number per group. The cleanest syntax is named aggregations:

import pandas as pd

events = pd.DataFrame({
    "user_id":  ["u-1","u-2","u-1","u-3","u-2","u-1","u-3","u-1"],
    "ts":       pd.to_datetime([
        "2026-05-20 09:00","2026-05-20 09:10","2026-05-20 09:15",
        "2026-05-21 10:00","2026-05-21 10:05","2026-05-21 11:30",
        "2026-05-22 08:00","2026-05-22 14:20",
    ]),
    "revenue":  [0.0, 0.0, 24.99, 0.0, 49.99, 14.50, 0.0, 99.00],
})

# Per-user metrics: total revenue, first order date, LTV (sum of purchases)
metrics = events.groupby("user_id").agg(
    total_revenue   = ("revenue", "sum"),
    first_seen      = ("ts",      "min"),
    last_seen       = ("ts",      "max"),
    event_count     = ("ts",      "count"),
    avg_revenue     = ("revenue", "mean"),
).round(2)

print(metrics)

         total_revenue          first_seen           last_seen  event_count  avg_revenue
user_id                                                                                 
u-1             138.49 2026-05-20 09:00:00 2026-05-22 14:20:00            4        34.62
u-2              49.99 2026-05-20 09:10:00 2026-05-21 10:05:00            2        25.00
u-3               0.00 2026-05-21 10:00:00 2026-05-22 08:00:00            2         0.00

The name=(column, func) syntax is the modern way: each line names the output column and specifies which input column and which aggregation to use. The result is a flat DataFrame — no hierarchical column index to fight. func can be a string ("sum", "mean", "min", "max", "count", "nunique", "first", "last") or any callable.

Grouping by multiple columns

import pandas as pd

events = pd.DataFrame({
    "user_id":  ["u-1","u-2","u-1","u-3","u-2","u-1","u-3","u-1"],
    "country":  ["IN","US","IN","DE","US","IN","DE","IN"],
    "revenue":  [0.0, 0.0, 24.99, 0.0, 49.99, 14.50, 0.0, 99.00],
})

# Revenue by country, then by user
print(events.groupby(["country", "user_id"])["revenue"].sum())

country  user_id
DE       u-3          0.00
IN       u-1        138.49
US       u-2         49.99
Name: revenue, dtype: float64

The result has a MultiIndex (country, user_id). Call .reset_index() to flatten it back into a regular DataFrame with those as columns.

transform — broadcast back to original shape

agg collapses each group to a single row. transform instead returns a result the same shape as the input, broadcasting the per-group value to every row in that group — perfect for per-group normalisation:

import pandas as pd

events = pd.DataFrame({
    "user_id":  ["u-1","u-2","u-1","u-3","u-2","u-1","u-3","u-1"],
    "revenue":  [0.0, 0.0, 24.99, 0.0, 49.99, 14.50, 0.0, 99.00],
})

# Add a column: how much THIS user has spent in total
events["user_total"] = events.groupby("user_id")["revenue"].transform("sum")

# What fraction of this user's total was this row?
events["share_of_user"] = (events["revenue"] / events["user_total"]).round(2).fillna(0)

print(events)

  user_id  revenue  user_total  share_of_user
0     u-1     0.00      138.49           0.00
1     u-2     0.00       49.99           0.00
2     u-1    24.99      138.49           0.18
3     u-3     0.00        0.00           0.00
4     u-2    49.99       49.99           1.00
5     u-1    14.50      138.49           0.10
6     u-3     0.00        0.00           0.00
7     u-1    99.00      138.49           0.71

Every row kept its place, but now carries its user’s total (138.49 for all three u-1 rows) and its share of it (the 99.00 purchase is 0.71 of u-1’s spend). transform is what you reach for when you want a per-group statistic right beside the original row — “how far above this user’s average is this purchase?”, “rank within group”, and so on.

filter — keep or drop entire groups

filter runs a predicate on each group and keeps all rows of the groups that pass:

import pandas as pd

events = pd.DataFrame({
    "user_id":  ["u-1","u-2","u-1","u-3","u-2","u-1","u-3","u-1"],
    "revenue":  [0.0, 0.0, 24.99, 0.0, 49.99, 14.50, 0.0, 99.00],
})

# Only keep rows from users who spent more than $50 total
big_spenders = events.groupby("user_id").filter(
    lambda g: g["revenue"].sum() > 50
)
print(big_spenders)

  user_id  revenue
0     u-1     0.00
2     u-1    24.99
5     u-1    14.50
7     u-1    99.00

Only u-1 cleared $50 total (138.49), so all four of its rows survive — including the two zero-revenue views. That is the point of filter: a whole-group decision, all-or-nothing. (Don’t confuse it with df.filter(...), which selects columns by name pattern.)

apply — the escape hatch (use sparingly)

apply runs any Python function on each group. It is flexible, but slow — pandas can’t vectorize it. Reach for it only when agg, transform, and built-ins genuinely can’t express what you need.

import pandas as pd

events = pd.DataFrame({
    "user_id":  ["u-1","u-2","u-1","u-3","u-2","u-1","u-3","u-1"],
    "revenue":  [0.0, 0.0, 24.99, 0.0, 49.99, 14.50, 0.0, 99.00],
})

# Top-2 purchases per user (something agg can't do directly)
top_per_user = (
    events
    .groupby("user_id", group_keys=False)
    .apply(lambda g: g.nlargest(2, "revenue"))
)
print(top_per_user)

  user_id  revenue
7     u-1    99.00
2     u-1    24.99
4     u-2    49.99
1     u-2     0.00
3     u-3     0.00
6     u-3     0.00

Each user kept its two highest rows — a per-group top-N that agg (which collapses to one row) simply cannot express. This is the legitimate use of the escape hatch.

A complete worked example — LTV per user

Here is how those pieces combine into something you would ship to a real BI dashboard:

import pandas as pd

events = pd.DataFrame({
    "user_id":  ["u-1","u-2","u-1","u-3","u-2","u-1","u-3","u-1","u-2"],
    "event":    ["view","view","purchase","view","purchase","purchase","view","purchase","purchase"],
    "ts":       pd.to_datetime([
        "2026-05-20","2026-05-20","2026-05-20","2026-05-21",
        "2026-05-21","2026-05-21","2026-05-22","2026-05-22","2026-05-23",
    ]),
    "revenue":  [0,0,24.99,0,49.99,14.50,0,99.00,29.00],
})

purchases = events[events["event"] == "purchase"]

ltv = purchases.groupby("user_id").agg(
    first_order_date = ("ts",      "min"),
    last_order_date  = ("ts",      "max"),
    order_count      = ("ts",      "count"),
    total_revenue    = ("revenue", "sum"),
    avg_order_value  = ("revenue", "mean"),
).round(2)

# Days between first and last order — "customer lifespan"
ltv["lifespan_days"] = (ltv["last_order_date"] - ltv["first_order_date"]).dt.days

print(ltv)

        first_order_date last_order_date  order_count  total_revenue  avg_order_value  lifespan_days
user_id                                                                                             
u-1           2026-05-20      2026-05-22            3         138.49            46.16              2
u-2           2026-05-21      2026-05-23            2          78.99            39.50              2

Filter to purchases, group by user, name five aggregates, then derive a sixth from two of them — one row per customer, every metric a BI team asks for. (u-3, with no purchases, correctly never appears.)

In one breath

groupby is split–apply–combine: split rows by a key, apply a function per group, combine the results. df.groupby("k")["v"].sum() gives one number per group; named aggregations agg(name=(col, func)) give several flat columns at once (and group by a list of keys for a MultiIndex). The four verbs: agg collapses each group to one row; transform broadcasts the per-group value back to every original row (per-group normalisation/ranking); filter keeps or drops whole groups by a predicate; apply is the slow, flexible escape hatch (per-group top-N) — prefer the others. The everyday output shape is one row per entity with aggregated metric columns.

Practice

A question to carry forward

groupby is enormously powerful, but notice its one hard limit: it only ever works within a single table. Every metric we computed lived in the columns of events already. Real questions rarely stay inside one table. Your orders sit in one place, your customer names and countries in another; your events reference a device_id whose details live in a separate file. To answer “revenue by country” you first have to bring the country over from the customers table onto the orders.

That stitching-together of two tables on a shared key is merge — SQL joins, in pandas. What are the four join flavours (inner, left, right, outer), why can an innocent-looking join silently double your row count and quietly inflate every total downstream, and what single argument — validate — stops that from ever happening unnoticed?