Pandas GroupBy: agg, transform, filter, pivot_table & Top-N

Q: What's the difference between agg and transform?

agg reduces each group to a single value per column, returning one row per group. transform returns a value for every original row, aligned to the input's index, such as each row's group total. Use agg for summaries and transform for adding group-level features to rows.

Q: How do I get the top N rows per group?

Sort by the ranking column, then take groupby(key).head(N), for example df.sort_values("total", ascending=False).groupby("country").head(3). Alternatively, rank within groups using groupby(...).rank() and filter.

Q: Why is my groupby slow?

Common causes: custom lambdas or apply running Python per group, object-dtype string keys (convert them to category), a very high number of groups with sorting enabled, or categorical keys without observed=True. Use built-in aggregations and efficient dtypes. For very large data, consider Polars or DuckDB. See pandas performance.

Q: How do I group by time periods?

Use pd.Grouper(key="timestamp", freq="D") (or "W", "MS", "h") in groupby, or set a DatetimeIndex and use resample. Combine with other keys for per-category time series. See pandas time series.

Pandas GroupBy & Aggregation

groupby is how pandas answers questions like "revenue per country", "average order value per customer segment", "each user's first purchase date", or "the top 3 products per category". It implements split-apply-combine: split rows into groups by one or more keys, apply a function to each group (aggregate, transform, or filter), and combine the results into a new Series or DataFrame.

It's pandas' equivalent of SQL's GROUP BY (see SQL GROUP BY), plus window-like operations via transform. Knowing the difference between agg, transform, filter, and apply, and preferring the built-in, vectorized ones, makes groupby code both clearer and dramatically faster.

TL;DR

df.groupby("key") creates a lazy GroupBy object; nothing is computed until you aggregate, transform, or filter.
agg reduces each group to one row: .agg(revenue=("total", "sum"), orders=("order_id", "count")) (named aggregation).
transform returns a result aligned to the original rows, which is ideal for group-level features (share of total, z-scores).
filter keeps or drops whole groups based on a condition.
apply is flexible but slow; prefer built-in aggregations and vectorized operations.
pivot_table and crosstab reshape grouped results into matrices; use observed=True with categorical keys.

Quick Example

Core Concepts

Split-Apply-Combine

Split: rows are assigned to groups by keys (columns, index levels, functions, pd.Grouper for time bins).
Apply: an operation runs per group.
Combine: results are assembled into a new object.

The kind of operation determines the output shape:

Aggregation With agg

Named aggregations produce clean, flat column names. Built-in string aggregations ("sum", "mean", "count", "size", "nunique", "first", "last", "min", "max", "std", "median") run in optimized code. Custom lambdas run per group in Python, which is slower.

Note the difference between count (non-null values per column) and size (rows per group, including nulls).

transform: Group Features on Every Row

transform returns values aligned to the original index, perfect for:

Group totals and shares: df["total"] / df.groupby("customer_id")["total"].transform("sum").
Normalization within groups (z-scores per segment).
Filling missing values with group means.
Running calculations: cumsum, cumcount, rank, shift, and diff (days since previous order).

This mirrors SQL window functions, such as SUM(total) OVER (PARTITION BY customer_id).

filter, head, and nth

groupby(...).filter(func) keeps entire groups where func(group) is true.
groupby(...).head(n) / .tail(n) / .nth(k) pick rows per group without aggregating. Combined with sorting, that's the standard top-N-per-group pattern.

apply and Its Costs

apply calls a Python function on each group's sub-DataFrame, which is flexible but slow with many groups, and its output shape can be surprising. Prefer, in order: built-in aggregations, agg with named functions, transform, then vectorized logic across the whole frame, and only then apply. If apply is unavoidable, keep the function vectorized within each group.

Group Keys and Options

as_index=False returns keys as columns rather than the index (handy for further merging).
dropna=False keeps groups where the key is NaN (dropped by default).
sort=False skips sorting group keys, which is faster for many groups.
Categorical keys: use observed=True to include only categories actually present, which avoids exploding Cartesian products of unused categories (it's the default in newer pandas).

Reshaping: pivot_table and crosstab

unstack() and stack() move index levels to columns and back after a multi-key groupby.

Best Practices

Use Named Aggregations

They produce readable output columns and make intent explicit, avoiding hard-to-use MultiIndex columns.

Prefer Built-In Functions Over Lambdas

"mean" beats lambda s: s.mean() by a wide margin on large data. The same goes for transform("sum") versus transform(lambda s: s.sum()).

Reset or Flatten Indexes Before Exporting

Grouped results often carry MultiIndexes. reset_index() or as_index=False gives tidy tables for CSV, Parquet, or plotting.

Validate Group Counts

Check groupby(...).size() for unexpected groups: typos in categories, NaN keys dropped silently, or duplicated entities inflating counts.

Common Mistakes

Averaging Averages

Computing per-day mean order values, then averaging them, weights each day equally regardless of order count. Compute from sums and counts: revenue.sum() / orders.sum().

Unintended NaN Group Drops

Rows with missing keys disappear from groupby results by default. Use dropna=False, or fill keys ("unknown") when they should be counted.

Using apply for Simple Aggregations

FAQ

What's the difference between agg and transform?

agg reduces each group to a single value per column, returning one row per group. transform returns a value for every original row, aligned to the input's index, such as each row's group total. Use agg for summaries and transform for adding group-level features to rows.

How do I get the top N rows per group?

Sort by the ranking column, then take groupby(key).head(N), for example df.sort_values("total", ascending=False).groupby("country").head(3). Alternatively, rank within groups using groupby(...).rank() and filter.

Why is my groupby slow?

Common causes: custom lambdas or apply running Python per group, object-dtype string keys (convert them to category), a very high number of groups with sorting enabled, or categorical keys without observed=True. Use built-in aggregations and efficient dtypes. For very large data, consider Polars or DuckDB. See pandas performance.

How do I group by time periods?

Use pd.Grouper(key="timestamp", freq="D") (or "W", "MS", "h") in groupby, or set a DatetimeIndex and use resample. Combine with other keys for per-category time series. See pandas time series.