Definition
Which city brings in the most revenue? Which product's sales grew fastest? What is the average order size per customer type? Each is the same pattern: split the data into groups, calculate something for each, and combine the results.
Sorting orders rows by one or more columns with sort_values. Grouping splits a DataFrame into groups that share a value in one or more columns, applies a calculation to each group, such as sum, mean or count, and combines the results into a new table, a pattern called split-apply-combine. In pandas this is groupby followed by an aggregation, agg for several at once, and pivot_table for two-way summaries.
In simple words
Sort the receipts into piles by city, add up each pile, then write the totals in a small table. That is groupby: split into piles, calculate per pile, combine.
Overview
- Sorting orders rows by one or more columns with sort_values.
- Grouping follows split-apply-combine: split rows into groups, apply a calculation, combine the results.
- groupby("col") creates groups; sum, mean, count, min, max and size summarise them.
- agg applies several functions at once and can name the output columns.
- pivot_table and crosstab make spreadsheet-style summary tables.
- This is the same idea as GROUP BY in SQL.
Why it matters
Business reports, dashboards and much of exploratory analysis are summaries by group. In machine learning, group statistics become features, such as a customer's average spend, and comparing groups is essential for checking fairness.
Key terms
- sort_values
- Orders rows by the values in one or more columns.
- groupby
- Splits rows into groups sharing a key value.
- Aggregation
- Reducing each group to a summary, such as sum, mean, count, min or max.
- Split-apply-combine
- The pattern of splitting data into groups, applying a function, and combining results.
- Pivot table
- A two-way summary with one key down the rows and another across the columns.
- transform
- Applying a group calculation but returning a value for every original row.
Types
Summarising data uses several types of operation, from simple ordering to multi-level summary tables.
1. Sorting and ranking
Ordering rows, or giving each row its rank.
- How it works: sort_values(["class_total", "name"], ascending=[False, True]) sorts by several keys; rank(method="min") gives tied rows the same rank; nlargest(5, "marks") gets the top five.
- Example: Listing the ten cities with the highest average PM2.5.
- When to use it: Use it for leaderboards, top-k lists and preparing data for reports.
2. Simple group aggregation
One summary statistic per group.
- How it works: df.groupby("city")["sales"].sum() returns a Series indexed by city.
- Example: Total sales per city or average marks per section.
- When to use it: Use it to compare groups on one measure. Add .sort_values(ascending=False) to rank the groups, and .reset_index() to turn the result back into a table that can be saved or plotted.
3. Multiple and named aggregations (agg)
Several statistics per group, possibly for different columns.
- How it works: df.groupby("city").agg(orders=("id", "count"), avg_value=("value", "mean"), max_time=("minutes", "max")).
- Example: A city report with order count, average order value and slowest delivery.
- When to use it: Use it to build summary tables for dashboards in one step. Named outputs keep the result readable when it is shared, so nobody has to guess what a column such as value_mean_2 means.
4. Transform and filter on groups
Group calculations that keep the original rows.
- How it works: transform("mean") returns the group mean on every row; filter(lambda g: len(g) > 30) keeps only large groups.
- Example: df["vs_section_avg"] = df["marks"] - df.groupby("section")["marks"].transform("mean").
- When to use it: Use it to build group-based features and to drop groups too small to trust. Such features often help models, because a value means more when compared with its own group.
5. Pivot tables and crosstabs
Two-way summary tables with one variable as rows and another as columns.
- How it works: pivot_table(index="city", columns="month", values="sales", aggfunc="sum", margins=True) adds totals; crosstab counts combinations of two categories.
- Example: Monthly sales per city in one grid; a crosstab of gender by pass/fail.
- When to use it: Use them for report tables and to see patterns across two dimensions.
Explained in detail
Sorting
df.sort_values("amount", ascending=False) puts the largest orders first. Sort by several columns with a list: sort_values(["city", "amount"], ascending=[True, False]) groups by city alphabetically and orders each city's rows from largest to smallest. df.nlargest(5, "amount") is a fast shortcut for the top five. Sorting returns a new DataFrame; the index labels travel with their rows, so call reset_index(drop=True) if you want clean 0, 1, 2 labels afterwards.
The split-apply-combine pattern
Hadley Wickham named this pattern in a 2011 paper, and it describes much of data analysis. In pandas, df.groupby("city")["amount"].sum() splits orders by city, sums each group's amounts and returns one total per city. Group by several keys with a list, such as ["city", "month"], to get a total for every combination. Use .agg to compute several things at once with readable names: df.groupby("city").agg(orders=("amount", "count"), revenue=("amount", "sum"), average=("amount", "mean")). The result is itself a DataFrame, ready to sort, chart or join.
Common aggregations
| Aggregation | Answers |
|---|---|
| count / size | How many rows per group (count ignores missing values; size does not) |
| sum | Total per group, such as revenue |
| mean / median | Typical value per group |
| min / max | Extremes per group |
| nunique | Distinct values per group, such as unique customers per city |
| first / last | The first or last row in each group, after sorting |
Pivot tables and transform
- df.pivot_table(index="city", columns="month", values="amount", aggfunc="sum") gives cities down the side and months across the top, like a spreadsheet pivot table.
- pd.crosstab(df["city"], df["plan"]) counts combinations of two categories.
- transform returns one value per original row: df["city_avg"] = df.groupby("city")["amount"].transform("mean") adds each row's city average, useful for features such as "this order compared with its city's average".
- When building model features from groups, compute them on training data only, or the target can leak.
Grouping also connects directly to fairness checks in AI. Computing a model's accuracy, error rate or approval rate grouped by region, gender or age band takes one groupby line, and it often reveals that an average hides poor performance for a particular group. The same split-apply-combine pattern appears in SQL as GROUP BY, in spreadsheets as pivot tables and in big-data tools such as Spark, so learning it once pays off everywhere you work with data.
How it works
- Decide the grouping key or keys.
- Choose the column and the summary you want for each group.
- Write df.groupby(keys).agg(name=(column, function)).
- Sort the result to find the top or bottom groups.
- Check group sizes: an average from three rows is unreliable.
In code
import numpy as np
import pandas as pd
rng = np.random.default_rng(11)
n = 200
df = pd.DataFrame({
"city": rng.choice(["Pune", "Delhi", "Chennai"], n),
"month": rng.choice(["Oct", "Nov", "Dec"], n),
"amount": rng.integers(300, 5000, n),
})
summary = (df.groupby("city")
.agg(orders=("amount", "count"), revenue=("amount", "sum"), average=("amount", "mean"))
.sort_values("revenue", ascending=False))
print(summary.round(0))
pivot = df.pivot_table(index="city", columns="month", values="amount", aggfunc="sum")
print(pivot[["Oct", "Nov", "Dec"]])
df["vs_city_avg"] = df["amount"] / df.groupby("city")["amount"].transform("mean")
print(df.nlargest(3, "vs_city_avg").round(2))Output
orders revenue average
city
Chennai 73 197608 2707.0
Pune 69 172416 2499.0
Delhi 58 155414 2680.0
month Oct Nov Dec
city
Chennai 55554 77268 64786
Delhi 40996 57336 57082
Pune 78479 46196 47741
city month amount vs_city_avg
96 Pune Oct 4872 1.95
133 Pune Oct 4847 1.94
32 Pune Oct 4731 1.89Try it yourself
The city with the best average
A sales head asks which city has the highest average order.
Split, apply, combine
Put the steps of a groupby in order.
- 1Apply an aggregation to each group
- 2Sort and check group sizes
- 3Combine the results into a table
- 4Choose the grouping key
- 5Split rows into groups by that key
Common mistakes
- Reporting group averages without group sizes.
- Forgetting that count ignores missing values while size does not.
- Building group features on all data and leaking the target.
Check yourself
Summary
- sort_values and nlargest order and rank rows.
- groupby with agg performs split-apply-combine; pivot_table gives two-way summaries.
- Show group sizes, and use transform for per-row group features.
Finished reading?
Sources: pandas: Group by: split-apply-combine · Python for Data Analysis, 3rd edition (Wes McKinney)