Grouping and Aggregation
Split-apply-combine, the workhorse of summaries
GroupBy splits data by key, applies a function to each slice, and combines results — the engine of per-segment, per-cohort and per-date analytics.
What you will learn
- Group by single and multiple keys
- Aggregate with multiple functions per column
- Use transform to keep the original shape
What is this?
df.groupby(['city']).sales.sum() implements split-apply-combine: split rows by key, apply a function to each group, combine into a result indexed by key. Multiple aggregations come from .agg({'sales': ['sum','mean'], 'qty': 'count'}). transform() preserves the input shape, filling each row with its group's statistic for comparisons.
Why do you need it?
- Every per-X breakdown (city, plan, month, cohort) is a groupby.
- One pass computes means, sums, counts and medians without reinventing loops.
- transform enables 'how does this row compare to its group?' questions.
- Grouped results feed straight into charts, reports and dashboards.
Mental model
Sorting mail into slots, then summarizing each slot
GroupBy
The clerk splits letters by zip code (split), computes each slot's weight or count (apply), and posts a summary board per slot (combine). If you need per-letter context ('this letter is heavier than its slot's median') that's transform — the slot's number back on every envelope.
How it works
- grouped = df.groupby('city') produces a GroupBy object holding the split.
- .sum(), .mean(), .count(), .nunique(), .median() collapse each group to one row.
- .agg({col: ['sum','count']}) applies multiple functions, possibly per column.
- .groupby(['city','plan']) creates a multi-level result index (or use as_index=False).
- .transform('sum') returns a Series aligned to the input so you can add 'share of city' columns.
- .filter() drops whole groups by an aggregated condition.
Split-apply-combine.
Examples
Per-city share of revenue
import pandas as pddf = pd.DataFrame({"city": ["austin", "austin", "houston", "houston", "houston"], "sales": [120, 80, 200, 300, 40]})g = df.groupby("city", as_index=False).agg(sales_sum=("sales", "sum"))df["city_share"] = df.groupby("city")["sales"].transform(lambda s: s / s.sum())print(g)print(df)agg builds the city summary; transform maps each row's share of its own city so comparisons stay row-aligned.
In production
- A churn dashboard computes monthly per-plan retention by groupby('plan').agg(...).
- A revenue team tracks share-of-city with transform to expose concentration shifts.
- A support review filters groups.where median response time exceeds SLA.
Best practices
- Use as_index=False or reset_index() to keep the key as a column.
- Prefer agg with named tuples (col=('sales', 'sum')) for readable, typed summaries.
- Compute group stats once and merge back rather than recomputing in loops.
- For per-group comparison, transform keeps alignment and avoids joins.
Common mistakes
- Calling .mean() on mixed columns and getting meaningless averages.
- Grouping and then column-selecting wrong (df.groupby('city')['sales'] vs the whole frame).
- Mutating the original while a transform runs, desyncing the result index.
- Forgetting nulls: groupby drops NaN keys silently and counts can mislead.
Debugging tips
- A missing key in output means NaN keys were dropped — check isna on the group column.
- If a group stat looks off, df.groupby('city').describe() shows the distribution inside each group.
- transform index misalignment → compare df.index after the op.
- When count vs sum confuse, print the groupby object briefly (head()) to see composition.
Interview checkpoint
Hands-on lab
- Group by date and plan keys
- Aggregate with multiple functions and named outputs
- Compute share-of-plan transformations
Monthly Revenue by Plan
30 min · beginner
Quick quiz
Which keeps the original row count while adding a group statistic?
groupby silently drops rows whose key is NaN.