Skip to content
Kuza Ndoto Academy

Grouping & Aggregation

Beginner → IntermediateChapter 3 · Page 11 of 204 min readUpdated 15 Nov 2026

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:

  1. Split the rows into groups by the values in one or more columns (for example, one group per county).
  2. Apply a calculation to each group (sum, mean, count…).
  3. 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())
county Kisumu 7420 Nairobi 11400 Nakuru 3300 Name: revenue, dtype: int64

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.

Data Split by county Apply sum(), combine Nairobi5400 Kisumu1920 Nairobi2800 Nakuru3300 Nairobi3200 Kisumu5500 Kisumu1920 Kisumu5500 Nairobi5400 Nairobi2800 Nairobi3200 Nakuru3300 Kisumu7420 Nairobi11400 Nakuru3300
Figure 1. 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.
MethodGives
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"]))
sum mean count product Cooking oil 8 8.0 1 Maize flour 30 30.0 1 Rice 40 20.0 2 Sugar 32 16.0 2

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)
total_units total_revenue orders county Kisumu 37 7420 2 Nairobi 58 11400 3 Nakuru 15 3300 1

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)
county product units 0 Kisumu Rice 25 1 Kisumu Sugar 12 2 Nairobi Cooking oil 8 3 Nairobi Maize flour 30 4 Nairobi Sugar 20 5 Nakuru Rice 15

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)
product Cooking oil Maize flour Rice Sugar county Kisumu 0 0 25 12 Nairobi 8 30 0 20 Nakuru 0 0 15 0

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"]])
county revenue share 0 Nairobi 5400 47.4 1 Kisumu 1920 25.9 2 Nairobi 2800 24.6 3 Nakuru 3300 100.0 4 Nairobi 3200 28.1 5 Kisumu 5500 74.1
Common mistake

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.

Common mistake

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=False or reset_index() for flat tables.
  • pivot_table() gives an Excel-style grid; transform() puts group results back on each row.
Up next · New chapter: VisualisationMatplotlib BasicsLine, bar and scatter charts.
→

Tip: use the ← and → keys to move between pages.