GroupBy transform (PARTITION BY)
Python · GroupBy & SQL-Style Analytics
Theory
agg() returns one row per group, but transform() returns one value per original row: each row gets the result computed over its own group. df.groupby('city')['amount'].transform('sum') gives every Jaipur row the Jaipur total and every Pune row the Pune total, in the same order and index as df, so you can assign it straight to a new column. This is exactly SQL's window aggregate SUM(amount) OVER (PARTITION BY city).
Any aggregation works: 'mean', 'max', 'min', 'count', 'size' or 'nunique', and several keys can be partitions: groupby(['city', 'month']). Typical uses are comparing each row with its group (amount - city_avg), share of group total (amount / city_total), flagging the group max (amount == transform('max')) and filling missing values with the group mean (s.fillna(s.groupby(key).transform('mean'))). transform also accepts a function, such as lambda s: (s - s.mean()) / s.std(), but built-in names are much faster.
Why we use it
Many calculations need both the detail row and a group-level number - each sale versus its city's average, each employee's share of department salary. transform keeps every row while adding the group value, with no merge needed.
When to use it
Use transform when the result must line up with the original rows. Use agg when you want one row per group. In SQL terms: GROUP BY collapses rows (agg), PARTITION BY keeps them (transform).
How it works
- df.groupby('city')['amount'].transform('mean')
- Split rows into city groups
- Compute the mean for each group
- Broadcast the group mean back to every row of that group
- Compare row with group? Yes: e.g. amount - city_avg, amount / city_total. No: Keep as a column.
- New column aligned with the original rows
Syntax
df['grp_total'] = df.groupby('key')['col'].transform('sum')
df.groupby(['k1', 'k2'])['col'].transform('mean')
s.fillna(s.groupby(df['key']).transform('mean'))Example
df['city_avg'] = df.groupby('city')['amount'].transform('mean')
df['vs_avg'] = df['amount'] - df['city_avg']Key points
- transform returns a result with the same index and length as the input
- agg collapses to one row per group; transform keeps every row
- Pass built-in names ('sum', 'mean', 'max') - much faster than lambdas
- Great for share of group, difference from group average and group-wise fillna
- SQL equivalent: AVG(amount) OVER (PARTITION BY city)