These are the notes for Data Analytics with Python, episode 24. The data is clean; now we ask it questions. The most common business question is "how much, per something?": sales per item, orders per city, visitors per day. Here you invent split-apply-combine, use groupby with sum, mean and count, run several functions with agg, name the results with named aggregation, learn size vs count, group by several columns, and turn results back into plain tables with as_index=False and reset_index, plus sort=False. Every term is defined the first time it appears and collected in the Glossary. Every program below was run for real (Python 3.13, pandas 3.0.5); outputs are unedited.
The data: two stalls, ten orders
Ramesh now has stalls in Pune and Nashik. Order 7 has no amount recorded (a missing value).
import sys; sys.tracebacklimit = 0
import pandas as pd, io
raw = """order_id,city,item,qty,amount
1,Pune,Chai,2,40
2,Pune,Samosa,3,45
3,Nashik,Chai,1,20
4,Pune,Coffee,1,30
5,Nashik,Samosa,2,30
6,Pune,Chai,4,80
7,Nashik,Coffee,2,
8,Pune,Vada Pav,2,50
9,Nashik,Chai,3,60
10,Pune,Samosa,1,15
"""
orders = pd.read_csv(io.StringIO(raw))
The obvious way: one filter per item
His accountant asks: how much did each item sell? With filtering (episode 15) you'd loop over the items you remember:
found = 0
for item in ["Chai", "Samosa", "Coffee"]:
total = orders.loc[orders["item"] == item, "amount"].sum()
print(item, total)
found += total
print("items:", found, " whole table:", orders["amount"].sum())
Chai 200.0
Samosa 90.0
Coffee 30.0
items: 320.0 whole table: 370.0
320 ≠ 370: Vada Pav (started last week) is missing from the hard-coded list. A hard-coded list of groups goes stale the day the business changes, and 40 items × 5 cities would need 200 filters. We want the data itself to decide the piles.
Split, apply, combine
Real-life picture: a teacher with a stack of answer sheets from several sections.
| Step | Teacher | pandas |
|---|---|---|
| Split | sort sheets into piles by section, whatever sections appear | orders.groupby("item") |
| Apply | add up the marks in each pile | ["amount"].sum() |
| Combine | write one line per section on the board | one row per group in the result |
This pattern is called split-apply-combine. In pandas it's groupby. Each pile is a group; the column that decides the piles is the key (grouping key).
groupby with sum, mean, count
print(orders.groupby("item")["amount"].sum())
item
Chai 200.0
Coffee 30.0
Samosa 90.0
Vada Pav 50.0
Name: amount, dtype: float64
All four items, including Vada Pav, with nobody remembering it. The result is a Series whose index is the group keys, sorted alphabetically. Turning many values into one number per group is called aggregation; sum is an aggregation function. The sum skips missing values.
Several functions: agg
print(orders.groupby("item")["amount"].agg(["sum", "mean", "count"]))
sum mean count
item
Chai 200.0 50.0 4
Coffee 30.0 30.0 1
Samosa 90.0 30.0 3
Vada Pav 50.0 50.0 1
One row per group, one column per function. Function names must be ones pandas knows:
print(orders.groupby("item")["amount"].agg("average"))
AttributeError: 'SeriesGroupBy' object has no attribute 'average'
Common aggregation functions:
| Name | Gives |
|---|---|
"sum" |
total |
"mean" |
average (arithmetic mean) |
"median" |
middle value |
"min", "max" |
smallest, largest |
"count" |
number of non-missing values |
"size" |
number of rows |
"std" |
standard deviation |
"nunique" |
number of distinct values |
"first", "last" |
first / last value in each group |
Named aggregation
A report needs clear column names and different functions for different columns. Named aggregation: inside agg, write new_name=("column", "function").
report = orders.groupby("item").agg(
total=("amount", "sum"),
orders=("order_id", "count"),
cups=("qty", "sum"))
print(report)
total orders cups
item
Chai 200.0 4 10
Coffee 30.0 2 3
Samosa 90.0 3 6
Vada Pav 50.0 1 2
Coffee shows 2 orders here, but count of amount said 1. Why?
agg with a dictionary (older style)
You'll see this in other people's code: a dictionary mapping each column to a function or a list of functions.
r = orders.groupby("city").agg({"amount": ["sum", "mean"], "qty": "sum"})
print(r.round(1))
amount qty
sum mean sum
city
Nashik 110.0 36.7 8
Pune 260.0 43.3 13
It works, but the result has a two-level column header (amount above sum/mean), which is awkward to select and export. Prefer named aggregation: one flat row of names you chose.
size vs count
g = orders.groupby("item")
print(pd.DataFrame({"size": g.size(), "count": g["amount"].count()}))
size count
item
Chai 4 4
Coffee 2 1
Samosa 3 3
Vada Pav 1 1
sizecounts rows in each group (missing values included).countcounts non-missing values in a column.
Order 7 (Coffee) has no amount, so size 2, count 1. The gap is a free missing-value report.
Don't aggregate everything
print(orders.groupby("item").sum())
order_id city qty amount
item
Chai 19 PuneNashikPuneNashik 10 200.0
Coffee 11 PuneNashik 3 30.0
Samosa 17 PuneNashikPune 6 90.0
Vada Pav 8 Pune 2 50.0
No error, but order_id summed to meaningless numbers, and adding text glues it together ("PuneNashikPune"). Select the columns that make sense to aggregate first.
Grouping by several columns
Pass a list of keys; each group is a combination:
print(orders.groupby(["city", "item"])["amount"].sum())
city item
Nashik Chai 80.0
Coffee 0.0
Samosa 30.0
Pune Chai 120.0
Coffee 30.0
Samosa 60.0
Vada Pav 50.0
Name: amount, dtype: float64
The index now has two levels (city, item): a MultiIndex, covered in episode 25. Look at Nashik Coffee: 0.0. Its only amount is NaN, and the sum of no values is 0, which is a lie: the amount is unknown, not zero. min_count=1 means "need at least one real value, otherwise NaN":
print(orders.groupby(["city", "item"])["amount"].sum(min_count=1))
city item
Nashik Chai 80.0
Coffee NaN
Samosa 30.0
Pune Chai 120.0
Coffee 30.0
Samosa 60.0
Vada Pav 50.0
Name: amount, dtype: float64
Grouping by a calculated key
The key doesn't have to be a column of the table: any Series with one value per row works. Do big orders (3 items or more) spend more?
size = orders["qty"].ge(3).map({True: "3+ items", False: "1-2 items"})
print(orders.groupby(size)["amount"].agg(["count", "mean"]).round(1))
print(orders.groupby("city")["item"].nunique())
count mean
qty
1-2 items 6 30.8
3+ items 3 61.7
city
Nashik 3
Pune 4
Name: item, dtype: int64
ge(3) means "greater than or equal to 3" (same as >= 3), and map turns True/False into labels. count is 6 for the seven small orders because one amount is missing. nunique counts distinct values: Pune sells 4 different items, Nashik 3. With episode 22's tools, groupby(df["ordered"].dt.day_name()) gives sales per weekday and groupby(df["ordered"].dt.to_period("M")) sales per month.
as_index=False, reset_index and sort=False
Group keys go into the index. For charts, merges or Excel exports you usually want them as ordinary columns: use as_index=False inside groupby, or reset_index() afterwards (episode 12).
t = orders.groupby(["city", "item"], as_index=False)["amount"].sum(min_count=1)
print(t)
print(orders.groupby("city")["amount"].sum().reset_index())
city item amount
0 Nashik Chai 80.0
1 Nashik Coffee NaN
2 Nashik Samosa 30.0
3 Pune Chai 120.0
4 Pune Coffee 30.0
5 Pune Samosa 60.0
6 Pune Vada Pav 50.0
city amount
0 Nashik 110.0
1 Pune 260.0
groupby sorts groups by key; sort=False keeps the order in which they first appear (and is slightly faster on big data):
print(orders.groupby("item")["amount"].sum().index.tolist())
print(orders.groupby("item", sort=False)["amount"].sum().index.tolist())
['Chai', 'Coffee', 'Samosa', 'Vada Pav']
['Chai', 'Samosa', 'Coffee', 'Vada Pav']
Keys must match column names exactly, including capitals:
print(orders.groupby("Item")["amount"].sum())
KeyError: 'Item'
Mini project: item_report()
def item_report(df):
r = df.groupby("item", as_index=False).agg(
orders=("order_id", "size"),
cups=("qty", "sum"),
revenue=("amount", "sum"),
avg_order=("amount", "mean"))
r["share_%"] = (100 * r["revenue"] / r["revenue"].sum()).round(1)
return r.sort_values("revenue", ascending=False)
print(item_report(orders))
item orders cups revenue avg_order share_%
0 Chai 4 10 200.0 50.0 54.1
2 Samosa 3 6 90.0 30.0 24.3
3 Vada Pav 1 2 50.0 50.0 13.5
1 Coffee 2 3 30.0 30.0 8.1
Chai brings 54.1% of revenue. Coffee's 2 orders but only 30 rupees shows the missing amount instead of hiding it. The share column divides each group's revenue by the grand total: a derived column after aggregation.
Common mistakes
| Code / habit | Real error / symptom | Why | Fix |
|---|---|---|---|
| A loop over a hard-coded list of items | no error; 320 of 370 rupees | the list forgot Vada Pav | groupby("item") |
.agg("average") |
AttributeError: 'SeriesGroupBy' object has no attribute 'average' |
the function is mean |
.agg("mean") |
orders.groupby("item").sum() |
no error; city = "PuneNashikPune" | text columns get glued | select columns: ["amount"] |
count to count orders |
Coffee shows 1 instead of 2 | count skips NaN |
size for rows |
.sum() on an all-NaN group |
shows 0.0 | the sum of no values is 0 | .sum(min_count=1) |
groupby("Item") |
KeyError: 'Item' |
names are case-sensitive | groupby("item") |
| Keys left in the index for export | key column missing in Excel/merge | keys are the index | as_index=False / reset_index() |
Practice
All practice questions use this marks table:
import pandas as pd, io
raw = """student,section,subject,marks
Asha,A,Maths,78
Ravi,A,Maths,64
Meena,B,Maths,91
Asha,A,Science,82
Ravi,A,Science,
Meena,B,Science,88
Kiran,B,Maths,55
"""
m = pd.read_csv(io.StringIO(raw))
1. Average marks per section, rounded to 1 decimal.
Solution
print(m.groupby("section")["marks"].mean().round(1))
section
A 74.7
B 78.0
Name: marks, dtype: float64
2. Min, max and median marks per subject.
Solution
print(m.groupby("subject")["marks"].agg(["min", "max", "median"]))
min max median
subject
Maths 55.0 91.0 71.0
Science 82.0 88.0 85.0
3. Per section: how many rows, and how many have marks?
Solution
g = m.groupby("section"); print(pd.DataFrame({"rows": g.size(), "marked": g["marks"].count()}))
rows marked
section
A 4 3
B 3 3
Section A has one unmarked paper (Ravi, Science).
4. A plain table per section and subject with the average mark and the number of distinct students.
Solution
print(m.groupby(["section", "subject"], as_index=False).agg(avg=("marks", "mean"), students=("student", "nunique")))
section subject avg students
0 A Maths 71.0 2
1 A Science 82.0 2
2 B Maths 73.0 2
3 B Science 88.0 1
5. Total marks per student, highest first, without turning a missing mark into 0.
Solution
print(m.groupby("student")["marks"].sum(min_count=1).sort_values(ascending=False))
student
Meena 179.0
Asha 160.0
Ravi 64.0
Kiran 55.0
Name: marks, dtype: float64
Ravi still has 64, because one real mark is enough for min_count=1. Comparing totals is unfair when students have different numbers of papers: the mean is often better.
Quick quiz
df = pd.DataFrame({"k": list("aab"),
"v": [1, None, 3]})
g = df.groupby("k")
s, c = g.size(), g["v"].count()
print(s["a"], c["a"])
A. 2 2 B. 2 1 C. 1 1 D. 1 2
Answer
B. 2 1. Group a has 2 rows (size), but one v is missing, so count is 1.
Interview questions
- What is split-apply-combine? Split the data into groups by a key, apply a function to each group, combine the results into one table.
groupbyimplements it. - What does
df.groupby("city")["sales"].sum()return? A Series indexed by city with the total sales of each city. sizevscount?sizecounts rows per group including missing values;countcounts non-missing values per column.- What is named aggregation?
agg(new_name=("column", "func"), ...): choose the output column names and a function per column. - How do you get group keys as columns instead of the index?
as_index=Falseingroupby, or.reset_index()afterwards. - What happens when you group by two columns? Each unique combination becomes a group; the result has a two-level MultiIndex (unless
as_index=False). - Why can
groupby().sum()be misleading? It may aggregate columns that shouldn't be summed (ids, text glued together), and an all-missing group sums to 0 unlessmin_count=1. - What does
sort=Falsedo? Keeps groups in order of first appearance instead of sorting by key; slightly faster. - How do you compute each group's share of the total? Aggregate, then divide each group's value by the column total (
r["rev"] / r["rev"].sum()). - SQL equivalent of
groupby?SELECT city, SUM(amount) FROM orders GROUP BY city.
Glossary
| Term | Meaning | Example |
|---|---|---|
| Split-apply-combine | Split into groups, apply a function to each, combine the results | sales per item |
groupby |
pandas' split-apply-combine | orders.groupby("item") |
| Group | The rows sharing one key value | all Chai rows |
| Key (grouping key) | The column whose values decide the groups | "item" |
| Aggregation | Turning many values into one number per group | total per item |
| Aggregation function | A function that does the aggregating | sum, mean, count |
agg |
Run one or several aggregation functions | agg(["sum", "mean"]) |
| Named aggregation | agg(name=("column", "func")) with your column names |
total=("amount", "sum") |
size |
Rows per group, missing values included | Coffee → 2 |
count |
Non-missing values per group | Coffee amount → 1 |
nunique |
Number of distinct values | Pune sells 4 items |
| Calculated key | A Series you compute (one value per row) used as the grouping key | big vs small orders |
agg with a dict |
{"column": func or [funcs]}; gives a two-level header |
{"amount": ["sum", "mean"]} |
min_count=1 |
A sum needs at least one real value, else NaN | Nashik Coffee → NaN |
| MultiIndex | An index with more than one level | (city, item) |
as_index=False |
Keep group keys as ordinary columns | plain table |
reset_index() |
Move the index back into columns | after groupby |
sort=False |
Keep groups in first-seen order | Chai, Samosa, Coffee… |
| Share | A group's value divided by the total, often in % | Chai 54.1% |
Summary
- One filter per group misses new groups;
groupbylets the data decide the groups. - Split-apply-combine:
df.groupby(key)[column].function(). aggruns several functions; named aggregation gives clear names and per-column functions.sizecounts rows,countcounts non-missing values.- Select columns before aggregating; use
min_count=1for honest sums. - Group by a list for combinations;
as_index=False/reset_indexfor a plain table;sort=Falsefor first-seen order. - Next episode: GroupBy 2, transform, filter and MultiIndex.