skip to content

Group Transforms and Filters

transform returns a result aligned to every row, filter keeps or drops whole groups, and apply runs any function per group. Interviewers ask which to use and why apply is slow.

on this pageshow

questions

6

In pandas, how does groupby transform('sum') put each store's total on every row, and how does transform with a function differ?

level: middleimportance: must knowfreq 70%

answer

  1. same index as the input
  2. aggregate alias broadcast per group
  3. UDF runs group by group
  4. scalar or same length
  5. transformation aliases pass through

basics

~20 s

transform returns a result with the grouped frame's index and length. transform('sum') broadcasts each store's total onto its rows for a share column; a function runs per group in Python and must return a scalar or a group-length result.

solid answer

~40 s

`sales.groupby('store')['amount'].transform('sum')` returns a Series as long as `sales` and indexed like it, holding each store's total on every one of its rows, so `sales['amount'] / that` is the share of store sales with no merge back. A string names a GroupBy method: an aggregation such as `'sum'`, `'mean'` or `'size'` is computed per group and broadcast, and a transformation such as `'cumsum'` or `'rank'` is passed through. A function is called on each group's Series (or column by column for a frame) and must return a scalar or something the group's length; it runs in Python per group, so it is slower.

code

python · 14 lines
python
import pandas as pd

sales = pd.DataFrame({
    "store": ["S1", "S2", "S1", "S2", "S1"],
    "dept": ["toys", "toys", "food", "food", "food"],
    "amount": [100, 50, 300, 150, 600],
})
g = sales.groupby("store")["amount"]
sales["store_total"] = g.transform("sum")
sales["share"] = sales["amount"] / sales["store_total"]

d = sales.groupby("dept")["amount"]
sales["z"] = (sales["amount"] - d.transform("mean")) / d.transform("std")
print(sales.round(3))

go deeper

for a junior

Recall that transform keeps every row and the original index, so a store total from transform('sum') can be divided into the amount column directly.

for a middle

Explain the difference between an aggregation alias that is broadcast and a UDF that runs per group, and the rule that a UDF must return a scalar or group-length result.

for a senior

Show that you replace per-group lambdas with string transforms and column arithmetic, and that you can say which aliases are broadcast and which pass through.

for a principal

Set a team convention of group-relative columns built from transform aliases and column arithmetic rather than per-group lambdas or agg-then-merge, so the code stays fast and aligned.

## What transform returns `DataFrameGroupBy.transform` and `SeriesGroupBy.transform` produce a result **indexed the same as the object being grouped**: same length, same row labels, same row order. The docstring states it plainly: a DataFrame "having the same indexes as the original object filled with the transformed values". That one property is what makes it the tool for **group-relative columns**, values computed per group but needed on each row: - the share of a store's sales each sale represents, - each row's deviation from its department's mean, - a flag for stores above a threshold, kept as a row-level mask. Because the index matches, `sales['store_total'] = g.transform('sum')` lands each total on the correct rows even if the frame is not sorted by store. The grouping column itself is excluded from the result, and `as_index` and `sort` on `groupby` have no effect on it. ## String aliases A string passed to `transform` must be the **name of a GroupBy method**. Two families are accepted: 1. **Aggregations** such as `'sum'`, `'mean'`, `'std'`, `'min'`, `'max'`, `'count'`, `'size'`, `'nunique'`, `'first'`: pandas computes one value per group with the grouped implementation and **broadcasts** it to every row of that group. 2. **Transformations** such as `'cumsum'`, `'rank'`, `'shift'`, `'cumcount'`, `'ngroup'`: these already return one value per row, so `transform('cumsum')` is the same as calling `.cumsum()`. The pandas user guide notes that when the aggregation has an efficient implementation, the broadcast is efficient too. ```python g = sales.groupby("store")["amount"] sales["share"] = sales["amount"] / g.transform("sum") d = sales.groupby("dept")["amount"] sales["z"] = (sales["amount"] - d.transform("mean")) / d.transform("std") ``` ## Functions: the contract a UDF must meet A **user-defined function (UDF)** passed to `transform` receives each group in turn. On a `SeriesGroupBy` it gets that group's Series; on a `DataFrameGroupBy` it is tried column by column, with a faster whole-frame path used from the second group when the function supports it. Its return value must be either: - a **scalar**, which is broadcast to every row of the group (`lambda x: x.max() - x.min()`), or - a result **the same length as the group** (`lambda x: (x - x.mean()) / x.std()`). A NumPy array of any other length raises a `ValueError` about mismatched lengths. Each group also carries a `name` attribute holding its key, and the function must not modify the group in place. The resulting dtype follows what the function returns. ## String or function | aspect | `transform('mean')` | `transform(lambda x: x.mean())` | |---|---|---| | where it runs | pandas' grouped implementation | a Python call per group | | speed with many groups | fast | grows with the number of groups | | what it can express | GroupBy methods only | any per-group logic | | result shape | input's length and index | input's length and index | The user guide's own advice is to rebuild a UDF from built-ins where possible: the z-score above uses two string transforms and plain column arithmetic instead of one lambda. A NumPy callable such as `np.sum` counts as a function here, not as an alias; how pandas 3.0 treats such callables is covered in the lesson on grouping and aggregating. ## Missing values and dtypes Three details decide what the new column looks like: - **Missing keys.** With the default `dropna=True` on `groupby`, a row whose store is missing belongs to no group. `transform` still returns a value for that row, but it is `NaN`, and an `int64` total therefore comes back as `float64`. Pass `dropna=False` to `groupby` if missing keys should form a group of their own. - **Missing values.** Aggregation aliases skip `NaN` as they do elsewhere, so `transform('mean')` averages only the known amounts while the result still covers every row, the missing ones included. - **Result dtype.** `transform('sum')` on an `int64` column stays `int64` when no key is missing; the division for the share produces `float64` as usual. ## Pitfalls - Using `agg('sum')` and then trying to assign the store-indexed result to a row-indexed column; it no longer lines up with the rows, which is why `transform` exists. - Returning a filtered or reordered piece of the group from a UDF; the result must be scalar or group-length. - Wrapping logic in `transform(lambda ...)` for speed: it is still a Python call per group. Picking one-row-per-group aggregation with `agg` and its options is a separate lesson on grouping and aggregating in pandas.

  • Why not compute the totals with agg and merge them back onto the rows?
    It works, but `agg` returns one row per store indexed by the key, so you need a `merge` or `map` on the store column to bring the figure back, and you risk a mismatch if keys were dropped. `transform('sum')` returns the per-row figure already aligned to the original index, in one step.
  • What does transform('size') give compared with transform('count')?
    Both broadcast a per-group number onto every row. `'size'` counts the rows in the group, missing values included; `'count'` counts the non-missing values of the selected column. On a column with gaps the two differ, and `'size'` is the one that matches the number of sales.
  • Can a transform function see the group's key?
    Yes. pandas sets a `name` attribute on each group before calling the function, so `lambda x: x / targets[x.name]` can look up a per-store target from a dictionary. The grouping column itself is not among the columns passed.

A teacher who writes each class's average next to every pupil's own mark, instead of on a separate summary sheet, lets each pupil compare against the class without looking anything up: transform writes the group figure on every row.

  • transform('sum') returns one row per store, the same as agg('sum')
  • A transform function may return a top-n subset of each group
  • Wrapping the logic in transform(lambda ...) makes it as fast as a built-in
  • transform('cumsum') broadcasts one running total per store
  • The frame must be sorted by store for transform results to line up
  • transform with a string accepts any NumPy function name

Assumes pandas 3.0, where 'kurt' joined the aggregation aliases transform accepts; the string and function contracts shown here are otherwise unchanged from 2.x.

open as a page

In pandas, why is a per-department z-score computed with groupby apply slow when there are many departments, and what do you replace it with?

level: seniorimportance: must knowfreq 65%

basics

~20 s

groupby apply calls a Python function once per group and then concatenates the pieces and infers the result's shape, so its cost grows with the number of groups. (x - g.transform('mean')) / g.transform('std') computes the same z-score with grouped built-ins.

open as a page

In pandas, how do you add a running sales total, the previous sale and a sale number within each store using groupby?

level: juniorimportance: should knowfreq 50%

basics

~20 s

Call cumsum, shift(1) and cumcount on df.groupby('store'). Each returns one value per input row, aligned to the original index and restarting at every store, in the frame's current row order, so sort by date first.

open as a page

In pandas, what does groupby filter return when dropping stores with fewer than five sales, and when is a transform mask better?

level: middleimportance: should knowfreq 55%

basics

~20 s

df.groupby('store').filter(lambda g: len(g) >= 5) returns the original rows of every store whose function returned True, with their original labels, order and columns. A mask from transform('size') >= 5 selects the same rows without a Python call per store.

open as a page

In pandas 3.0, why does a function passed to df.groupby('dept').apply() raise KeyError on g['dept'], and how do you fix it?

level: seniorimportance: should knowfreq 50%

basics

~10 s

In pandas 3.0, DataFrameGroupBy.apply passes each group without the grouping columns, and include_groups=True raises ValueError. Read the key from g.name, or select the columns you need, key included, right after groupby.

open as a page

In pandas, how do you keep the top two products by revenue in each store, and why prefer sort_values then groupby head over apply with nlargest?

level: seniorimportance: should knowfreq 60%

basics

~10 s

Sort by revenue descending, then call groupby('store').head(2). head is a vectorized filtration that returns the original rows, labels and columns; apply(lambda g: g.nlargest(2, 'revenue')) runs Python per store and adds a store index level.

open as a page