Grouping is where most of the value in a table is extracted. The basic form is one line; the five patterns below are what turn it from a sum into an analysis.
The three shapes of a group operation
Every groupby does one of three things, and knowing which you want decides the method:
import numpy as np
import pandas as pd
rng = np.random.default_rng(4)
orders = pd.DataFrame({
'region': rng.choice(['North', 'South', 'East'], 30),
'channel': rng.choice(['web', 'phone'], 30),
'revenue': rng.integers(100, 900, 30),
})
# aggregate: one row OUT per group.
print(orders.groupby('region')['revenue'].sum())
print()
# transform: one value out per INPUT ROW, broadcast from the group.
print(orders.groupby('region')['revenue'].transform('sum').head(4).tolist())
print()
# filter: whole GROUPS kept or dropped, rows unchanged.
big = orders.groupby('region').filter(lambda g: len(g) > 8)
print(len(orders), '->', len(big), sorted(big['region'].unique()))
Open this example in the editor and change it. Nothing you type is saved.
Aggregate shrinks, transform preserves the shape, filter removes whole groups. Almost every "how do I do X by group" question is one of those three.
Named aggregations
import numpy as np
import pandas as pd
rng = np.random.default_rng(4)
orders = pd.DataFrame({
'region': rng.choice(['North', 'South', 'East'], 200),
'channel': rng.choice(['web', 'phone', 'store'], 200),
'units': rng.integers(1, 60, 200),
'revenue': rng.normal(500, 180, 200).round(2).clip(20),
})
summary = orders.groupby('region', as_index=False).agg(
orders=('revenue', 'size'),
revenue=('revenue', 'sum'),
mean_order=('revenue', 'mean'),
median_order=('revenue', 'median'),
biggest=('revenue', 'max'),
units=('units', 'sum'),
channels=('channel', 'nunique'),
)
print(summary.round(2))
Open this example in the editor and change it. Nothing you type is saved.
The name=(column, function) form is the one to use. It names the output columns, it lets one column be aggregated several ways, and it avoids the MultiIndex that the older agg(['sum', 'mean']) form produces.
A custom function works wherever a name does:
import numpy as np
import pandas as pd
rng = np.random.default_rng(4)
orders = pd.DataFrame({
'region': rng.choice(['North', 'South'], 100),
'revenue': rng.normal(500, 180, 100).round(2),
})
def iqr(values: pd.Series) -> float:
return float(values.quantile(0.75) - values.quantile(0.25))
print(orders.groupby('region', as_index=False).agg(
spread=('revenue', iqr),
p90=('revenue', lambda s: s.quantile(0.9)),
).round(2))
Open this example in the editor and change it. Nothing you type is saved.
A named function beats a lambda here, because the lambda's name in an error message is <lambda>.
Ranking inside a group
import numpy as np
import pandas as pd
rng = np.random.default_rng(9)
orders = pd.DataFrame({
'order_id': np.arange(1, 13),
'region': ['North'] * 4 + ['South'] * 4 + ['East'] * 4,
'revenue': rng.integers(100, 900, 12),
})
orders['rank'] = orders.groupby('region')['revenue'].rank(
ascending=False, method='dense').astype(int)
orders['percentile'] = orders.groupby('region')['revenue'].rank(pct=True).round(2)
print(orders.sort_values(['region', 'rank']))
Open this example in the editor and change it. Nothing you type is saved.
The method argument decides what happens to ties: 'min' gives 1, 1, 3; 'dense' gives 1, 1, 2; 'first' breaks ties by position, which is what you want when you need exactly n rows per group.
Top n per group
import numpy as np
import pandas as pd
rng = np.random.default_rng(9)
orders = pd.DataFrame({
'order_id': np.arange(1, 25),
'region': rng.choice(['North', 'South', 'East'], 24),
'revenue': rng.integers(100, 900, 24),
})
# The readable way, and the fast way for a small n.
top2 = (orders.sort_values('revenue', ascending=False)
.groupby('region', as_index=False)
.head(2)
.sort_values(['region', 'revenue'], ascending=[True, False]))
print(top2)
print()
# nlargest per group, which says what it means.
print(orders.groupby('region')['revenue'].nlargest(2))
print()
# And with a rank, when you also want the rank column.
orders['rank'] = orders.groupby('region')['revenue'].rank(
ascending=False, method='first').astype(int)
print(orders[orders['rank'] <= 2].sort_values(['region', 'rank'])
[['region', 'rank', 'order_id', 'revenue']])
Open this example in the editor and change it. Nothing you type is saved.
sort_values then groupby().head(n) is the idiom to remember. groupby().apply(lambda g: g.nlargest(n)) does the same thing far more slowly.
Cumulative and rolling
import numpy as np
import pandas as pd
rng = np.random.default_rng(13)
daily = pd.DataFrame({
'date': pd.date_range('2026-01-01', periods=20, freq='D').repeat(2),
'region': ['North', 'South'] * 20,
'revenue': rng.integers(100, 400, 40),
}).sort_values(['region', 'date'])
# Running total within each region.
daily['cumulative'] = daily.groupby('region')['revenue'].cumsum()
# A 7-day rolling mean within each region. The index juggling is the
# usual annoyance; transform avoids it entirely.
daily['rolling7'] = (daily.groupby('region')['revenue']
.transform(lambda s: s.rolling(7, min_periods=1).mean())
.round(1))
# Change from the previous row, within the group.
daily['change'] = daily.groupby('region')['revenue'].diff()
daily['pct_change'] = (daily.groupby('region')['revenue']
.pct_change().round(3))
print(daily[daily['region'] == 'North'].head(8)
[['date', 'revenue', 'cumulative', 'rolling7', 'change']])
Open this example in the editor and change it. Nothing you type is saved.
min_periods=1 makes the first few rolling values the mean of however many rows exist, rather than NaN. Which you want depends on the chart; decide, rather than letting the default decide.
The grouping matters enormously here. daily['revenue'].cumsum() without the groupby runs the running total straight through the region boundary, which is a wrong number that looks plausible.
Filtering whole groups
import numpy as np
import pandas as pd
rng = np.random.default_rng(17)
orders = pd.DataFrame({
'customer': rng.choice(list('ABCDEFGH'), 60),
'revenue': rng.integers(50, 500, 60),
})
# Customers with at least 8 orders AND more than 2000 total.
loyal = orders.groupby('customer').filter(
lambda g: len(g) >= 8 and g['revenue'].sum() > 2000)
print(f'{orders["customer"].nunique()} customers -> '
f'{loyal["customer"].nunique()} loyal')
print(sorted(loyal['customer'].unique().tolist()))
print()
# The same thing without apply, which is faster on a large frame.
counts = orders.groupby('customer')['revenue'].agg(['size', 'sum'])
keep = counts[(counts['size'] >= 8) & (counts['sum'] > 2000)].index
print(sorted(orders[orders['customer'].isin(keep)]['customer'].unique().tolist()))
Open this example in the editor and change it. Nothing you type is saved.
Both give the same answer. The second avoids calling a Python function once per group, which matters when there are fifty thousand groups rather than eight.
Apply, and why it is last
import numpy as np
import pandas as pd
rng = np.random.default_rng(21)
orders = pd.DataFrame({
'region': rng.choice(['North', 'South'], 2000),
'revenue': rng.integers(100, 900, 2000),
})
import time
start = time.perf_counter()
by_apply = orders.groupby('region').apply(
lambda g: pd.Series({'total': g['revenue'].sum(), 'n': len(g)}),
include_groups=False)
apply_seconds = time.perf_counter() - start
start = time.perf_counter()
by_agg = orders.groupby('region').agg(total=('revenue', 'sum'), n=('revenue', 'size'))
agg_seconds = time.perf_counter() - start
print(by_agg)
print(f'apply {apply_seconds * 1000:.2f}ms, agg {agg_seconds * 1000:.2f}ms')
print('agg is faster:', agg_seconds < apply_seconds)
Open this example in the editor and change it. Nothing you type is saved.
apply calls a Python function once per group, which defeats the vectorisation that makes pandas fast. It is the right tool when the operation genuinely needs the whole sub-frame and has no vectorised equivalent - fitting a model per group, say. For anything expressible as agg or transform, use those.
The order to try things: agg, then transform, then a vectorised two-step, then apply.
A worked example
import numpy as np
import pandas as pd
rng = np.random.default_rng(seed=31)
n = 2000
orders = pd.DataFrame({
'order_id': np.arange(1, n + 1),
'customer': rng.choice([f'C{i:03d}' for i in range(60)], n),
'region': rng.choice(['North', 'South', 'East', 'West'], n, p=[.4, .3, .2, .1]),
'date': pd.Timestamp('2026-01-01') + pd.to_timedelta(rng.integers(0, 90, n), 'D'),
'revenue': rng.gamma(shape=4.0, scale=120.0, size=n).round(2),
})
# 1. Aggregate: the summary table.
by_region = (
orders.groupby('region', as_index=False)
.agg(orders=('order_id', 'size'),
customers=('customer', 'nunique'),
revenue=('revenue', 'sum'),
mean_order=('revenue', 'mean'),
median_order=('revenue', 'median'),
p90=('revenue', lambda s: s.quantile(0.9)))
.sort_values('revenue', ascending=False)
.round(2)
)
print(by_region.to_string(index=False))
print()
# The mean is well above the median in every region, which is what a
# long right tail looks like. Report the median.
print('mean above median everywhere:',
bool((by_region['mean_order'] > by_region['median_order']).all()))
print()
# 2. Transform: each order's share of its region, no join.
orders['region_revenue'] = orders.groupby('region')['revenue'].transform('sum')
orders['share_of_region'] = orders['revenue'] / orders['region_revenue']
print('shares sum to 1 per region:',
bool(np.allclose(orders.groupby('region')['share_of_region'].sum(), 1.0)))
print()
# 3. Rank, and the top 2 per region.
orders['rank'] = (orders.groupby('region')['revenue']
.rank(ascending=False, method='first').astype(int))
top = orders[orders['rank'] <= 2].sort_values(['region', 'rank'])
print(top[['region', 'rank', 'order_id', 'customer', 'revenue']].to_string(index=False))
print()
# 4. Filter whole groups: customers worth paying attention to.
counts = orders.groupby('customer')['revenue'].agg(orders='size', total='sum')
loyal = counts[(counts['orders'] >= 40) & (counts['total'] > 18000)]
print(f'{len(counts)} customers, {len(loyal)} meet both thresholds')
print(loyal.sort_values('total', ascending=False).head(3).round(2))
print()
# 5. Cumulative and rolling, per region, over time.
daily = (orders.groupby(['region', 'date'], as_index=False)['revenue'].sum()
.sort_values(['region', 'date']))
daily['cumulative'] = daily.groupby('region')['revenue'].cumsum().round(2)
daily['rolling7'] = (daily.groupby('region')['revenue']
.transform(lambda s: s.rolling(7, min_periods=1).mean()).round(2))
daily['vs_last_week'] = (daily.groupby('region')['revenue']
.transform(lambda s: s / s.shift(7) - 1).round(3))
north = daily[daily['region'] == 'North'].head(10)
print(north[['date', 'revenue', 'cumulative', 'rolling7', 'vs_last_week']].to_string(index=False))
print()
# The running total must not cross a region boundary.
last_per_region = daily.groupby('region')['cumulative'].last()
print('cumulative ends at the region total:',
bool(np.allclose(last_per_region.sort_index(),
by_region.set_index('region')['revenue'].sort_index())))
print()
# 6. Two keys, flattened back into columns for the report.
pivot = (orders.pivot_table(index='region', columns=orders['date'].dt.to_period('M'),
values='revenue', aggfunc='sum')
.round(0))
pivot.columns = pivot.columns.astype(str)
print(pivot)
Open this example in the editor and change it. Nothing you type is saved.
The assertion in step five is the habit to copy. A per-group cumulative sum that ends at the group total is correct; one that does not has leaked across a boundary, and that is a wrong number no chart will reveal.
When it goes wrong
| Symptom | Cause |
|---|---|
| A running total crossed a group boundary | cumsum without the groupby |
| The group keys became an index | Pass as_index=False |
agg(['sum','mean']) gave a MultiIndex |
Use named aggregations |
apply is slow on many groups |
One Python call per group; use agg |
| A per-row group value needed a join | Use transform |
| Ties produced the wrong number of rows | Choose method= on rank |
The first rolling values are NaN |
Pass min_periods= |
| Groups with missing keys vanished | groupby drops NaN keys; pass dropna=False |
A check you can run
import numpy as np
import pandas as pd
df = pd.DataFrame({'g': ['a', 'a', None, 'b'], 'v': [1, 2, 3, 4]})
print(df.groupby('g')['v'].sum())
print(df.groupby('g', dropna=False)['v'].sum())
print(df['v'].sum())
Open this example in the editor and change it. Nothing you type is saved.
The first drops the row with a missing key, so the group totals do not add up to the overall total. The second keeps it.
Any time a set of group totals is less than the whole, that is the first thing to check - and it is the reason a dashboard's categories can sum to less than its headline number without anyone noticing for a year.