Grouping and aggregating

groupby, agg, and getting a clean DataFrame back rather than a strange index.

What you'll be able to do

Group and aggregate
Apply several aggregations at once
Flatten the result into a usable DataFrame

The basic pattern

df.groupby('region')['revenue'].sum()
df.groupby('region')['revenue'].mean()
df.groupby(['region', 'year'])['revenue'].sum()

The shape is always the same: group by something, select a column, aggregate it.

Several aggregations

df.groupby('region').agg(
    total_revenue = ('revenue', 'sum'),
    avg_revenue   = ('revenue', 'mean'),
    order_count   = ('order_id', 'count'),
    customers     = ('customer_id', 'nunique'),
)

This named form is worth using every time — you get sensible column names instead of a multi-level index that then needs flattening.

Available aggregations include sum, mean, median, count, nunique, min, max, std, first, last.

reset_index

Almost always reset the index

groupby puts the grouping column into the index, which then behaves oddly when you plot, merge or export.

result = (df.groupby('region')
            .agg(total=('revenue', 'sum'))
            .reset_index())

Now region is an ordinary column again. Alternatively pass as_index=False to groupby.

Grouped calculations that keep every row

df['region_total'] = df.groupby('region')['revenue'].transform('sum')
df['pct_of_region'] = df['revenue'] / df['region_total']

transform returns a result the same length as the original, so it aligns back onto every row — the direct equivalent of a SQL window function, where agg is the equivalent of GROUP BY.

Filtering groups

big = df.groupby('region').filter(lambda g: g['revenue'].sum() > 100000)

Keeps every row belonging to a group that passes the test — the equivalent of SQL's HAVING.

Key points

Use the named agg form to get clean column names
reset_index() after groupby, or the grouping column stays in the index
transform keeps every row; agg collapses them

Check yourself