GroupBy
Python · GroupBy & SQL-Style Analytics
Theory
groupby() answers questions like 'total sales per city'. It follows split-apply-combine: split the rows into groups by the values of a key column, apply an aggregation to each group separately, then combine the results into a new Series or DataFrame indexed by the group keys, sorted by key. df.groupby('city')['amount'].sum() is SQL's SELECT city, SUM(amount) ... GROUP BY city.
Group by several columns with a list, groupby(['city', 'month']). Named aggregation gives clear output columns: df.groupby('city').agg(orders=('amount', 'count'), total=('amount', 'sum'), avg=('amount', 'mean')). agg(['sum', 'mean']) applies several functions to one column. size() counts rows per group including NaN, while count() counts non-missing values per column. Add .reset_index() or pass as_index=False to get the keys back as normal columns; rows whose key is NaN are left out unless dropna=False.
Why we use it
Most reporting is per category - sales by region, headcount by department, revenue by month. groupby() does this in a single readable line, just like GROUP BY in SQL.
When to use it
Use groupby whenever you need a summary per category. For a cross-tab with one key down the rows and another across the columns, pivot_table() is more convenient; to keep every row and add the group value next to it, use transform().
How it works
- df.groupby('city').agg(...)
- Split: collect the rows for each city value
- Apply: run each aggregation on each group
- Combine: one row per city, indexed by city
- Need city as a normal column? Yes: reset_index() or as_index=False. No: Keep city as the index.
- Summary table
Syntax
df.groupby('key')['col'].sum()
df.groupby(['k1', 'k2']).agg(new=('col', 'func'), ...)
df.groupby('key', as_index=False).agg(...)
df.groupby('key').size()Example
print(df.groupby('city').agg(orders=('amount', 'count'), total=('amount', 'sum')))Key points
- Follows the split-apply-combine pattern
- Named aggregation: agg(total=('amount', 'sum')) gives clean column names
- Group keys become the index - use reset_index() or as_index=False
- size() counts rows; count() counts non-missing values per column
- Rows with a NaN key are dropped unless groupby(..., dropna=False)
- SQL equivalent: SELECT city, COUNT(*), SUM(amount) FROM t GROUP BY city