Grouping & Aggregation
Grouping answers questions such as "What is the total revenue per county?" or "What is the average order per product?". pandas does this with groupby(), which splits the data into groups, applies a calculation to each group and combines the results into a neat summary table.
Split, apply, combine
Every groupby() follows three steps:
- Split the rows into groups by the values in one or more columns (for example, one group per county).
- Apply a calculation to each group (sum, mean, count…).
- Combine the answers into a new Series or DataFrame, with one row per group.
We'll use the same shop data as the previous page:
import pandas as pd
sales = pd.DataFrame({
"product": ["Maize flour", "Sugar", "Cooking oil", "Rice", "Sugar", "Rice"],
"county": ["Nairobi", "Kisumu", "Nairobi", "Nakuru", "Nairobi", "Kisumu"],
"units": [30, 12, 8, 15, 20, 25],
"price": [180, 160, 350, 220, 160, 220],
})
sales["revenue"] = sales["units"] * sales["price"]
One column, one calculation
print(sales.groupby("county")["revenue"].sum())
Read it as: "group the sales by county, take the revenue column, and sum it". The group names become the index, sorted alphabetically. Kisumu's 7420 is 1920 + 5500, the two Kisumu rows.
groupby("county") splits the six rows into three groups, sum() is applied to each group's revenue, and the results are combined into one Series indexed by county.| Method | Gives |
|---|---|
sum(), mean(), median() | Total, average, middle value |
min(), max() | Smallest and largest |
count() | Number of non-missing values in each group |
size() | Number of rows in each group, including missing values |
nunique() | Number of different values in each group |
Several calculations at once with agg()
print(sales.groupby("product")["units"].agg(["sum", "mean", "count"]))
The result is a DataFrame. The name of the grouping column, product, appears on its own line above the group labels.
Named aggregation
For a tidy report, give each result column a clear name with the form new_name=("column", "function"):
summary = sales.groupby("county").agg(
total_units=("units", "sum"),
total_revenue=("revenue", "sum"),
orders=("product", "count"),
)
print(summary)
You can chain more methods onto the result, for example summary.sort_values("total_revenue", ascending=False) to rank the counties.
Grouping by two columns
Pass a list of columns to group by every combination. Adding as_index=False keeps the group columns as ordinary columns, which is easier to read, sort and save.
by_both = sales.groupby(["county", "product"], as_index=False)["units"].sum() print(by_both)
Without as_index=False you get a Series with a two-level index (a MultiIndex). Calling .reset_index() on that result turns it back into ordinary columns.
Pivot tables
A pivot table shows the same kind of summary as a grid, just like a pivot table in Excel: one variable down the side, another across the top.
grid = sales.pivot_table(index="county", columns="product",
values="units", aggfunc="sum", fill_value=0)
print(grid)
fill_value=0 puts 0 where a county sold none of a product, instead of NaN. The grid instantly shows gaps, such as cooking oil only selling in Nairobi.
transform(): group results on every row
Sometimes you want each row to know its group's total, for example to work out each sale's share of its county's revenue. transform() returns a result the same length as the original data.
sales["county_total"] = sales.groupby("county")["revenue"].transform("sum")
sales["share"] = (sales["revenue"] / sales["county_total"] * 100).round(1)
print(sales[["county", "revenue", "share"]])
Reporting the mean per group when a few huge values distort it. If one Nairobi customer placed a KES 500,000 order, Nairobi's average order would look enormous even if every other order was small. Check the median too (agg(["mean", "median"])), and report the group size, because an average of one or two rows is not reliable.
Grouping on text that hasn't been cleaned. "Nairobi", "nairobi" and "Nairobi " become three separate groups. Tidy the column first with .str.strip().str.title() (see Cleaning Data).
Key points
groupby()splits rows into groups, applies a calculation and combines the results.- Use
agg()with named aggregation for clear multi-column summaries. - Group by a list of columns for combinations; use
as_index=Falseorreset_index()for flat tables. pivot_table()gives an Excel-style grid;transform()puts group results back on each row.
Tip: use the ← and → keys to move between pages.
