Skip to content
Tutorials Center
My learning

Topics

Lesson 31 of 64 ยท Grouping and Reshaping

agg, transform and filter

Master groupby agg with named aggregation, transform for group-relative values such as shares and z-scores, filter groups, group ranks, and when to use apply.

After groupby Basics you can sum or average each group. Real questions need more: several statistics at once, a column that shows each row as a share of its group, or "keep only the regions with enough sales". pandas has a method for each of these jobs, and the difference between them comes down to what shape the result has.

  • agg reduces each group to one row (a smaller table).
  • transform returns a value for every original row (same size as the input).
  • filter keeps or drops whole groups (a subset of the original rows).

Learn to ask "what shape do I need?" and the right method follows.

flowchart LR
    D["8 input rows<br/>3 groups"] --> A["agg"] --> A2["3 rows<br/>one per group"]
    D --> T["transform"] --> T2["8 rows<br/>same index as input"]
    D --> F["filter"] --> F2["rows of the groups<br/>that pass the test"]

The same grouped data produces three differently shaped results.

agg with named aggregation

agg (short for aggregate) accepts a function name, a list of names, a dictionary, or, best of all, named aggregation: keyword arguments of the form result_name=("column", "function"). You name each output column yourself and get flat, readable columns.

Python Named aggregation

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Each argument reads like a sentence: "total_revenue is the sum of revenue". The function can be a string such as "sum", "mean", "size", "nunique", a NumPy function, or your own function.

Several functions, or a dictionary

The older styles still appear in real code, so recognise them:

Python Lists and dictionaries of functions

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The two-level column header is awkward to select from. Either flatten it as shown or prefer named aggregation, which avoids the problem.

Custom functions

Any function that takes a Series and returns a single value works as an aggregation. Use def for anything non-trivial and a lambda for a one-liner. Note that a lambda gets an unhelpful automatic name; with named aggregation you choose the name.

Python Your own aggregation functions

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

mode() returns a Series (there can be ties), so the lambda takes the first element with .iloc[0]. A custom function runs in Python once per group, which is fine for dozens of groups and slower for millions; built-in names like "sum" use a fast path.

transform: group-relative values

transform applies a function per group but returns one value per original row, with the original index. That is exactly what you need to put a group statistic next to each row so you can compare the two.

Python
df["region_total"] = df.groupby("region")["revenue"].transform("sum")

Compare with .sum(): it would return 3 numbers, one per region; transform("sum") returns 8 numbers, the region total repeated on every row of that region. Because the index matches, you can assign it straight back as a new column.

Python Shares, deviations and z-scores per group

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The first line shows the whole idea: (3,) versus (8,). Then three typical uses:

  • Share of group: divide a row by its group total.
  • Difference from the group average: subtract the group mean.
  • Standardising (z-score) within each group: pass a function that works on one group's Series and returns a Series of the same length. Within-group z-scores put regions with different scales on one comparable scale.

The within-group version of vectorised cumulative and shifted columns also lives here: g.cumsum(), g.shift(1), g.diff() and g.rank() all restart at each group's first row and return a full-length Series, so they behave like transform without needing the word.

Python Group-wise rank and running total

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

rank gives 1 to the largest revenue in each region (ascending=False), and the running total restarts at every region. Sorting first and taking head(1) per group is the standard way to get "top 1 per group"; use head(n) for the top n.

filter: keep or drop whole groups

filter takes a function that receives each group's sub-frame and returns True or False. Groups that return True are kept with all their original rows; the others vanish. It is how you express "only regions with more than 100 in sales" or "only customers with at least 3 orders".

Python Keep only groups that pass a test

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The result is a filtered version of the original DataFrame, not a summary. Note the contrast with a row filter: df[df["revenue"] > 40] tests rows, while filter tests a property of the group. A fast alternative for large data is to build a mask with transform: df[df.groupby("region")["revenue"].transform("sum") > 90].

apply: the last resort

groupby(...).apply(func) is the most flexible method: the function receives each group as a DataFrame and may return a scalar, a Series or a DataFrame, and pandas stitches the results together. Because it can do anything, its output shape is harder to predict, and it runs Python code per group. Reach for it only when the job is not one of these:

You want Use
One row of statistics per group agg
A value per original row transform (or cumsum, rank, shift)
To keep or drop whole groups filter
The top n rows of each group sort_values + head
Something that needs several columns of the group at once apply

If you do use apply, select the columns it needs first (df.groupby("region")[["units", "revenue"]].apply(...)) so it does not work with, or depend on, the grouping key.

Older code often passes the group key column into apply and lets it appear in the result. Recent pandas versions exclude grouping columns from the frames passed to apply, so choose columns explicitly and test the result shape on a small example.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. You want one new column holding each row's share of its region's revenue. Which method fits best?

    transform('sum') repeats each group's total on every row of the group, so dividing by it gives a share and the result lines up with the original index.
  2. 2. What does df.groupby('region').agg(total=('revenue', 'sum')) return?

    Named aggregation produces a flat DataFrame indexed by the group key, with the output column names you chose.
  3. 3. What does groupby(...).filter(func) return?

    filter keeps entire groups, with all their rows, whenever the function returns True for that group.
  4. 4. Which call returns the 1-based position of each row's revenue within its region, largest first?

    rank restarts for each group and returns one value per row. With ascending=False the largest revenue gets rank 1.
  5. 5. Why is agg with a lambda or custom function often slower than agg('sum')?

    String names like 'sum' map to compiled routines. A custom function is called from Python for every group, which is fine for a few groups and slow for very many.

Summary

  • agg reduces each group to one row; use named aggregation, agg(name=("column", "function")), for flat, readable output.
  • transform returns one value per original row, aligned to the original index: ideal for shares, deviations from the group mean and within-group z-scores.
  • cumsum, rank, shift and diff on a grouped column also restart at each group.
  • filter keeps or drops entire groups based on a test on each group.
  • "Top n per group" is sort_values(...).groupby(...).head(n).
  • apply is the flexible last resort; prefer agg, transform and filter when they fit.

Next: Pivot Tables and Crosstabs.