All lessons

Module 4: Grouping and aggregating · Lesson 4.1 · about 20 min

groupby: split, apply, combine

Summarise a table by group with groupby: split the rows by a key, apply a sum, mean or count to each group, and combine the results into one table. Group by one or several columns and keep the keys as columns.

Key points, meanings and formulas on one page: choose "Save as PDF" in the print box.

Module 4 · about 20 minutes · uses Lesson 2.5. You’ll be able to answer “how much per store / month / category?” with one line.

groupby splits the table into groups by a key, applies a calculation to each group, and combines the answers into one small table.

In everyday terms: sorting receipts into one pile per store, adding up each pile, and writing the totals on a single sheet. Split, apply, combine.

1. Split, apply, combine

groupby does three things: split the rows into groups by a key, apply a calculation to each group, and combine the answers into one table.

Pythonorders.csv

Read it as: group the orders by store, take revenue, sum it. One row per store.

2. Common calculations

Pythonorders.csv

sum, mean, median, min, max, count, size, nunique, std, first, last all work.

3. Several keys and flat results

Pythonorders.csv, products.csv

With as_index=False the keys stay as ordinary columns: easier to sort, filter, join or save.

groupby('store')
Split: one group per distinct store.
['qty'].sum()
Apply: one total per group.
Result
Combine: a Series or table indexed by the key.
as_index=False / reset_index()
Keep the key as a normal column.
Physical meaning
groupby moves you from the grain of the rows (order lines) to the grain of the key (stores). Every row lands in exactly one group, so the group totals always add back to the grand total.

“groupby changes the table.” It returns a new, smaller table and leaves the original alone. If the group totals do not add up to the grand total, a key had missing values (dropped by default: use dropna=False).

Watch it happen, one step at a time:

The rows.
storeqty
S012
WEB1
S013
S024
WEB5
Split. S01: 2, 3. S02: 4. WEB: 1, 5.
Apply. Sum each pile: S01 → 5, S02 → 4, WEB → 6.
Combine.
storeqty
S015
S024
WEB6
Check. 5 + 4 + 6 = 15 = the sum of all rows.

4. Your turn

Your turnorders.csv
Your turnorders.csv
How it works

Principle: groupby builds a map from each key to the row positions in that group, then runs the calculation once per group in compiled code. It’s the pandas version of SQL’s GROUP BY and a pivot table’s rows.

Grain warning: group the table at the right grain. Counting rows in orders counts order lines; counting distinct order_id counts orders. “Average order value” needs revenue summed per order first, then averaged:

Pythonorders.csv

5. Practice

  1. Average unit price per store.
  2. How many lines had a discount, per store? (Hint: the mean of a True/False column is a share; the sum is a count.)
  3. Why might groupby(...).count() and .size() differ?
Answers
  1. orders.groupby('store_id')['unit_price'].mean().
  2. orders.assign(promo=orders['discount'] > 0).groupby('store_id')['promo'].sum().
  3. count skips missing values in each column; size counts all rows.

Try it on your own data: grouped data gives counts, means and spreads by group for any CSV without code.

New words: groupby, split-apply-combine, aggregate, as_index, grain.

  • groupby = split by key, apply a calculation, combine.
  • Common: sum, mean, count, size, nunique, min, max.
  • Several keys give one row per combination; reset_index flattens.
  • Group totals add up to the grand total (unless keys are missing).

Check yourself

1What does orders.groupby('store_id')['qty'].sum() return?
2What is the difference between count() and size() in a groupby?
3Why add as_index=False (or .reset_index())?

Sources

Your ticks stay in this browser. Save them as a file on the course home.

Updated 2026-11-22. All datasets are made up. Python runs in your browser (the first run downloads it once, about 20 MB); nothing you type is sent anywhere.