I2 · Working with Data: NumPy, pandas, SQL and Visualisation

Sorting, Grouping and Aggregating

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.

12 min Intermediate · Sublevel Beginner Jump to the activity

What you need: The earlier topics of this module.

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

AggregationAnswers
count / sizeHow many rows per group (count ignores missing values; size does not)
sumTotal per group, such as revenue
mean / medianTypical value per group
min / maxExtremes per group
nuniqueDistinct values per group, such as unique customers per city
first / lastThe 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

  1. Decide the grouping key or keys.
  2. Choose the column and the summary you want for each group.
  3. Write df.groupby(keys).agg(name=(column, function)).
  4. Sort the result to find the top or bottom groups.
  5. Check group sizes: an average from three rows is unreliable.

In code

groupby.py
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.89

Try 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.

  1. 1Apply an aggregation to each group
  2. 2Sort and check group sizes
  3. 3Combine the results into a table
  4. 4Choose the grouping key
  5. 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

Question 1

1. What does df.groupby("city")["amount"].sum() return?

Question 2

2. Which gives a value for every original row?

Question 3

3. A city has the top average from only two orders. You should…

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)