These are the notes for Data Analytics with Python, episode 25. In episode 24 you invented groupby: split the rows into groups, apply a function, combine one answer per group. Many real questions are about each row inside its group: what share of its city's sales is this order? Which items are popular enough to keep? What was the biggest order in each city? Here you invent transform (a group's answer on every row), the group-wise percentage, filter (keep or drop whole groups), apply on groups, the MultiIndex (hierarchical index) with xs and unstack, cumsum, cumcount and rank within groups, and pd.Grouper for weekly or monthly buckets. 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, two weeks
Ramesh's stalls in Pune and Nashik. This time each order has a date. parse_dates=["date"] (episode 13) turns the text dates into real datetime values (episode 22), which we need for weekly totals later.
import sys; sys.tracebacklimit = 0
import pandas as pd, io
raw = """order_id,date,city,item,amount
1,2026-11-02,Pune,Chai,40
2,2026-11-02,Pune,Samosa,45
3,2026-11-02,Nashik,Chai,20
4,2026-11-03,Pune,Coffee,30
5,2026-11-03,Nashik,Samosa,30
6,2026-11-04,Pune,Chai,80
7,2026-11-09,Nashik,Coffee,50
8,2026-11-09,Pune,Vada Pav,50
9,2026-11-10,Nashik,Chai,60
10,2026-11-11,Pune,Samosa,15
"""
orders = pd.read_csv(io.StringIO(raw), parse_dates=["date"])
sys.tracebacklimit = 0 only shortens error messages to their last line, so the errors below are easy to read.
The problem: each order's share of its city
Ramesh asks: for each order, what share of its city's sales is it? Order 1 was ₹40 and Pune sold ₹260 in all, so about 15%. He wants one number per order (per row), not one per city.
The obvious attempt: divide by the groupby result
city_total = orders.groupby("city")["amount"].sum()
print(city_total)
orders["share"] = orders["amount"] / city_total
print(orders.head(3))
city
Nashik 160
Pune 260
Name: amount, dtype: int64
order_id date city item amount share
0 1 2026-11-02 Pune Chai 40 NaN
1 2 2026-11-02 Pune Samosa 45 NaN
2 3 2026-11-02 Nashik Chai 20 NaN
The totals are right, but share is NaN (Not a Number, pandas' marker for a missing value) on every row, with no error.
Why: pandas arithmetic uses index alignment (episode 11): values are paired by matching index labels, not by position. The orders have labels 0 … 9; city_total has labels Nashik and Pune. No label matches, so every result is missing.
Compare the sizes:
g = orders.groupby("city")["amount"]
print(g.sum().shape, g.transform("sum").shape)
(2,) (10,)
An aggregation such as sum shrinks the data to one value per group (2). What we need is 10 values, one per order, each carrying its own city's total.
transform: the group's answer on every row
transform runs a function per group and then broadcasts (copies) each group's answer back onto every row of that group. The result has the same length and the same index as the original table, so row-by-row arithmetic just works.
Real-life picture: a class average written on the board is aggregation (one number per section). Printing that same section average on every student's report card is transform. Then each student can compute "my marks ÷ my section's average".
Aggregation g.sum() |
g.transform("sum") |
|
|---|---|---|
| Rows in the result | one per group | one per original row |
| Index | the group keys | the original index |
| Typical use | a summary table | compare a row with its group |
orders["city_total"] = orders.groupby("city")["amount"].transform("sum")
orders["share_%"] = (100 * orders["amount"] / orders["city_total"]).round(1)
print(orders[["city", "item", "amount", "city_total", "share_%"]])
city item amount city_total share_%
0 Pune Chai 40 260 15.4
1 Pune Samosa 45 260 17.3
2 Nashik Chai 20 160 12.5
3 Pune Coffee 30 260 11.5
4 Nashik Samosa 30 160 18.8
5 Pune Chai 80 260 30.8
6 Nashik Coffee 50 160 31.2
7 Pune Vada Pav 50 260 19.2
8 Nashik Chai 60 160 37.5
9 Pune Samosa 15 260 5.8
Each Pune row shows 260 and each Nashik row 160. This is the group-wise percentage (share of the group total): value / transform("sum"). A city's shares add up to 100.
transform takes any aggregation name: "mean", "max", "min", "count", "nunique", "median", "std", or your own function. For example, each order's city average, used to flag orders above their city's average:
avg = orders.groupby("city")["amount"].transform("mean").round(1)
print(avg.tolist())
# [43.3, 43.3, 40.0, 43.3, 40.0, 43.3, 40.0, 43.3, 40.0, 43.3]
above = orders[orders["amount"] > avg]
filter: keep or drop whole groups
Ramesh wants a short menu: show all orders, but only for items ordered 3 or more times.
The obvious attempt: a mask built from counts
counts = orders.groupby("item")["order_id"].count()
print(orders[counts >= 3])
<string>:17: UserWarning: Boolean Series key will be reindexed to match DataFrame index.
pandas.errors.IndexingError: Unalignable boolean Series provided as indexer (index of the boolean Series and of the indexed object do not match).
Why: counts has 4 rows (one per item, indexed by item), orders has 10 rows (indexed 0–9). A boolean mask (a Series of True/False used to pick rows, episode 15) must line up with the rows it filters. A per-group answer can't.
The fix: groupby(...).filter(function)
filter passes each group (as a small DataFrame) to your function. The function must return one True (keep every row of this group) or False (drop the whole group). The kept rows come back unchanged, with their original index.
busy = orders.groupby("item").filter(lambda g: len(g) >= 3)
print(busy[["order_id", "item", "amount"]])
order_id item amount
0 1 Chai 40
1 2 Samosa 45
2 3 Chai 20
4 5 Samosa 30
5 6 Chai 80
8 9 Chai 60
9 10 Samosa 15
lambda g: len(g) >= 3 is a lambda (episode 7): a small function without a name, written in one line. g is one group; len(g) is its number of rows.
The function must return a single scalar (one value, not a Series):
print(orders.groupby("item").filter(lambda g: g["amount"] > 40))
TypeError: filter function returned a Series, but expected a scalar bool
To filter single rows, use boolean indexing. To filter whole groups, return one bool. Any group-level rule works, for example total sales above ₹100:
big = orders.groupby("item").filter(lambda g: g["amount"].sum() > 100)
print(big["item"].unique())
<StringArray>
['Chai']
Length: 1, dtype: str
| Tool | Returns | Rows in result |
|---|---|---|
aggregation (sum, agg) |
one row per group | number of groups |
transform |
one value per original row | same as the table |
filter |
the original rows of the kept groups | fewer or equal |
apply |
whatever your function returns, glued together | depends |
apply on groups
The single biggest order in each city is neither one number per group nor one per row: it's a few rows per group. apply hands each group to your function and glues the results together. It's the most flexible groupby tool, and the slowest, so prefer built-ins (sum, transform, nlargest) when they exist.
def top_order(g):
return g.nlargest(1, "amount")
cols = ["order_id", "item", "amount"]
print(orders.groupby("city")[cols].apply(top_order))
order_id item amount
city
Nashik 8 9 Chai 60
Pune 5 6 Chai 80
nlargest(1, "amount") (episode 16) returns the row with the largest amount. Selecting the columns first ([cols]) means the key column isn't passed into the function. The result's index has two levels: the city, then the original row label (8 and 5). That is a MultiIndex.
MultiIndex: an index with several levels
A MultiIndex is an index with more than one level. It's also called a hierarchical index because the levels form a hierarchy: items inside cities. Think of a postal address: first the city, then the street inside that city. The outer level is level 0, the next one level 1.
s = orders.groupby(["city", "item"])["amount"].sum()
print(s)
print(s.index.nlevels, s.index.names)
city item
Nashik Chai 80
Coffee 50
Samosa 30
Pune Chai 120
Coffee 30
Samosa 60
Vada Pav 50
Name: amount, dtype: int64
2 ['city', 'item']
s.index.nlevels is the number of levels; s.index.names their names.
Selecting from a MultiIndex
s = orders.groupby(["city", "item"])["amount"].sum()
print(s["Pune"])
print(s.loc[("Nashik", "Chai")])
item
Chai 120
Coffee 30
Samosa 60
Vada Pav 50
Name: amount, dtype: int64
80
s["Pune"]with one label looks at the outer level and returns everything under Pune.s.loc[("Nashik", "Chai")]with a tuple (an ordered, unchangeable group of values, episode 5) gives the full address and returns one value.
Square brackets with an inner-level label fail:
s = orders.groupby(["city", "item"])["amount"].sum()
print(s["Chai"])
KeyError: 'Chai'
The above exception was the direct cause of the following exception:
KeyError: 'Chai'
xs: a cross-section of one level
xs (cross-section) selects all rows with a given value in any level you name:
s = orders.groupby(["city", "item"])["amount"].sum()
print(s.xs("Chai", level="item"))
city
Nashik 80
Pune 120
Name: amount, dtype: int64
The item level is dropped from the result because it's the same everywhere.
unstack: an inner level becomes columns
A long two-level list is hard to read. unstack moves an index level (by default the innermost) into the columns, turning the list into a grid:
s = orders.groupby(["city", "item"])["amount"].sum()
print(s.unstack())
print(s.unstack(fill_value=0))
item Chai Coffee Samosa Vada Pav
city
Nashik 80.0 50.0 30.0 NaN
Pune 120.0 30.0 60.0 50.0
item Chai Coffee Samosa Vada Pav
city
Nashik 80 50 30 0
Pune 120 30 60 50
Nashik never sold Vada Pav, so that pair doesn't exist and shows NaN; because NaN is a float, the whole table becomes floats. fill_value=0 fills those gaps (only correct when "no row" really means zero). The opposite move, columns back into an index level, is stack (episode 27).
Running totals, numbering and ranking inside groups
Three more tools that return one value per row (like transform), computed within each group:
cumsum(cumulative sum): a running total that restarts for each group.cumcount: numbers the rows of each group 0, 1, 2… in their order (add 1 to start at 1).rank: the position of each value inside its group.ascending=Falsegives rank 1 to the largest;method="min"gives tied values the same, best rank (other methods:"average"(default),"max","first","dense").
g = orders.groupby("city")
orders["running"] = g["amount"].cumsum()
orders["nth"] = g.cumcount() + 1
orders["rank"] = g["amount"].rank(ascending=False, method="min").astype(int)
print(orders[["date", "city", "amount", "running", "nth", "rank"]])
date city amount running nth rank
0 2026-11-02 Pune 40 40 1 4
1 2026-11-02 Pune 45 85 2 3
2 2026-11-02 Nashik 20 20 1 4
3 2026-11-03 Pune 30 115 3 5
4 2026-11-03 Nashik 30 50 2 3
5 2026-11-04 Pune 80 195 4 1
6 2026-11-09 Nashik 50 100 3 2
7 2026-11-09 Pune 50 245 5 2
8 2026-11-10 Nashik 60 160 4 1
9 2026-11-11 Pune 15 260 6 6
Pune's running total reaches 260 and Nashik's 160, each restarting at its own first order. Order 9 is Nashik's 4th order and its biggest (rank 1).
Weekly and monthly totals with pd.Grouper
Grouping by the date column gives one group per day. pd.Grouper(key="date", freq="W") groups dates into time buckets (episode 28 goes deeper). key is the date column; freq is the bucket size, called a frequency alias:
| Alias | Bucket |
|---|---|
"D" |
day |
"W" |
week (ending Sunday, shown as W-SUN) |
"ME" |
month end |
"QE" |
quarter end |
"YE" |
year end |
w = orders.groupby(pd.Grouper(key="date", freq="W"))["amount"].sum()
print(w)
date
2026-11-08 245
2026-11-15 175
Freq: W-SUN, Name: amount, dtype: int64
Each label is the last day of its week; 8 November 2026 is a Sunday. Combine a normal key and a Grouper in a list, then unstack the city:
w = orders.groupby(["city", pd.Grouper(key="date", freq="W")])["amount"].sum()
print(w.unstack(level="city"))
city Nashik Pune
date
2026-11-08 50 195
2026-11-15 110 65
Pune fell from 195 to 65 while Nashik grew from 50 to 110: a trend worth a question.
freq only works on dates:
print(orders.groupby(pd.Grouper(key="city", freq="W"))["amount"].sum())
TypeError: Only valid with DatetimeIndex, TimedeltaIndex or PeriodIndex, but got an instance of 'Index'
If your dates are text, convert them first with pd.to_datetime (episode 22).
Mini project: best_item()
The best-selling item in each city and its share of that city's sales: aggregate per (city, item) into a plain table, then group by city again to get the share (transform) and the position (rank), and keep rank 1.
def best_item(df):
s = df.groupby(["city", "item"], as_index=False)["amount"].sum()
city = s.groupby("city")["amount"]
s["share_%"] = (100 * s["amount"] / city.transform("sum")).round(1)
s["rank"] = city.rank(ascending=False, method="min").astype(int)
return s[s["rank"] == 1]
print(best_item(orders))
city item amount share_% rank
0 Nashik Chai 80 50.0 1
3 Pune Chai 120 46.2 1
Chai wins in both cities: half of Nashik's sales and 46.2% of Pune's.
Common mistakes
| Code / habit | Real error / symptom | Why | Fix |
|---|---|---|---|
orders["amount"] / orders.groupby("city")["amount"].sum() |
no error; NaN on every row | 2 city labels vs 10 row labels | / groupby(...)["amount"].transform("sum") |
orders[counts >= 3] with per-item counts |
pandas.errors.IndexingError: Unalignable boolean Series provided as indexer (index of the boolean Series and of the indexed object do not match). |
a per-group mask on per-row data | groupby("item").filter(lambda g: len(g) >= 3) |
filter(lambda g: g["amount"] > 40) |
TypeError: filter function returned a Series, but expected a scalar bool |
filter needs one True/False per group | return one bool, e.g. g["amount"].sum() > 100 |
s["Chai"] on a (city, item) index |
KeyError: 'Chai' |
[] looks at the outer level |
s.xs("Chai", level="item") |
unstack() then summing NaN as zero without thinking |
missing pairs show NaN, ints become floats | the pair never existed | unstack(fill_value=0) when "missing" means 0 |
pd.Grouper(key="city", freq="W") |
TypeError: Only valid with DatetimeIndex, TimedeltaIndex or PeriodIndex, but got an instance of 'Index' |
time buckets need dates | pd.to_datetime first, Grouper on the date column |
apply for things built-ins do |
slow on big data | Python function per group | transform, agg, nlargest, cumsum |
Practice
All practice questions use this marks table:
import sys; sys.tracebacklimit = 0
import pandas as pd, io
raw = """student,section,subject,marks,date
Asha,A,Maths,78,2026-10-05
Ravi,A,Maths,64,2026-10-05
Meena,B,Maths,91,2026-10-06
Asha,A,Science,82,2026-10-12
Ravi,A,Science,70,2026-10-12
Meena,B,Science,88,2026-10-13
Kiran,B,Maths,55,2026-10-06
Kiran,B,Science,61,2026-10-13
"""
m = pd.read_csv(io.StringIO(raw), parse_dates=["date"])
1. Add a column section_avg with the average marks of the student's section, and a column vs_avg = marks − section_avg (rounded to 1 decimal).
Solution
m["section_avg"] = m.groupby("section")["marks"].transform("mean").round(1)
m["vs_avg"] = (m["marks"] - m["section_avg"]).round(1)
print(m[["student", "section", "marks", "section_avg", "vs_avg"]])
student section marks section_avg vs_avg
0 Asha A 78 73.5 4.5
1 Ravi A 64 73.5 -9.5
2 Meena B 91 73.8 17.2
3 Asha A 82 73.5 8.5
4 Ravi A 70 73.5 -3.5
5 Meena B 88 73.8 14.2
6 Kiran B 55 73.8 -18.8
7 Kiran B 61 73.8 -12.8
2. Each mark as a percentage of the student's own total (all subjects).
Solution
m["pct_of_own"] = (100 * m["marks"] / m.groupby("student")["marks"].transform("sum")).round(1)
print(m[["student", "subject", "marks", "pct_of_own"]])
student subject marks pct_of_own
0 Asha Maths 78 48.8
1 Ravi Maths 64 47.8
2 Meena Maths 91 50.8
3 Asha Science 82 51.2
4 Ravi Science 70 52.2
5 Meena Science 88 49.2
6 Kiran Maths 55 47.4
7 Kiran Science 61 52.6
3. Keep only the students whose lowest mark is at least 60 (all their rows).
Solution
ok = m.groupby("student").filter(lambda g: g["marks"].min() >= 60)
print(ok[["student", "subject", "marks"]])
student subject marks
0 Asha Maths 78
1 Ravi Maths 64
2 Meena Maths 91
3 Asha Science 82
4 Ravi Science 70
5 Meena Science 88
4. The top scorer of each subject (full row), using apply with nlargest, then the same with rank.
Solution
cols = ["student", "marks"]
print(m.groupby("subject")[cols].apply(lambda g: g.nlargest(1, "marks")))
m["r"] = m.groupby("subject")["marks"].rank(ascending=False, method="min")
print(m.loc[m["r"] == 1, ["subject", "student", "marks"]])
student marks
subject
Maths 2 Meena 91
Science 5 Meena 88
subject student marks
2 Maths Meena 91
5 Science Meena 88
5. Average marks per (section, subject) as a MultiIndex Series; then get Science for every section with xs, and make a grid with unstack.
Solution
s = m.groupby(["section", "subject"])["marks"].mean()
print(s)
print(s.xs("Science", level="subject"))
print(s.unstack())
section subject
A Maths 71.0
Science 76.0
B Maths 73.0
Science 74.5
Name: marks, dtype: float64
section
A 76.0
B 74.5
Name: marks, dtype: float64
subject Maths Science
section
A 71.0 76.0
B 73.0 74.5
6. Number each student's tests in date order (1, 2, …) and keep a running total of marks per student.
Solution
m = m.sort_values(["student", "date"])
g = m.groupby("student")
m["test_no"] = g.cumcount() + 1
m["running"] = g["marks"].cumsum()
print(m[["student", "date", "marks", "test_no", "running"]])
student date marks test_no running
0 Asha 2026-10-05 78 1 78
3 Asha 2026-10-12 82 2 160
6 Kiran 2026-10-06 55 1 55
7 Kiran 2026-10-13 61 2 116
2 Meena 2026-10-06 91 1 91
5 Meena 2026-10-13 88 2 179
1 Ravi 2026-10-05 64 1 64
4 Ravi 2026-10-12 70 2 134
7. Total marks per week, and per section per week as a grid.
Solution
print(m.groupby(pd.Grouper(key="date", freq="W"))["marks"].sum())
print(m.groupby(["section", pd.Grouper(key="date", freq="W")])["marks"].sum().unstack(level="section"))
date
2026-10-11 288
2026-10-18 301
Freq: W-SUN, Name: marks, dtype: int64
section A B
date
2026-10-11 142 146
2026-10-18 152 149
Quick quiz
df = pd.DataFrame({"k": list("aabbb"),
"v": [1, 2, 3, 4, 5]})
out = df.groupby("k")["v"].transform("max")
print(len(out))
A. 2 B. 3 C. 5 D. 1
Answer
C. 5. transform returns one value per original row: 2, 2, 5, 5, 5. Without transform, max() would give 2 values.
Interview questions
- Difference between
aggandtransform?aggreturns one row per group;transformreturns a result with the same index and length as the input, the group's answer repeated on each row. - How do you compute each row's percentage of its group total?
df["v"] / df.groupby("g")["v"].transform("sum"). - Why does dividing by
groupby().sum()give NaN? Index alignment: the sum is indexed by group keys, the rows by their own labels, so no labels match. - What does
groupby().filter()do? Keeps or drops entire groups based on a function that returns one bool per group; kept rows are returned unchanged. - When would you use
applyon groups, and what's the cost? For custom per-group logic that returns a table or several rows (top N per group). It calls a Python function per group, so it's slower than built-ins. - What is a MultiIndex? An index with several levels (e.g. city, item); created by grouping by several keys,
set_indexwith a list, orconcat(keys=...). - How do you select a value on an inner level?
s.xs("Chai", level="item"), ors.loc[(slice(None), "Chai")]. stackvsunstack?unstackmoves an index level into columns (long → wide);stackmoves column labels into the index (wide → long).- Running total per customer?
df.groupby("customer")["amount"].cumsum()after sorting by date. - Top 3 products per category?
df[df.groupby("cat")["sales"].rank(ascending=False, method="first") <= 3], orgroupby("cat").apply(lambda g: g.nlargest(3, "sales")). - Monthly totals from a date column?
df.groupby(pd.Grouper(key="date", freq="ME"))["amount"].sum()(orresample, episode 28). - SQL equivalent of transform("sum")? A window function:
SUM(amount) OVER (PARTITION BY city).
Glossary
| Term | Meaning | Example |
|---|---|---|
| Aggregation | Many values → one value per group (the result shrinks) | total per city |
| Index alignment | pandas pairs values by matching index labels, not position | 0..9 vs Nashik/Pune → NaN |
transform |
Runs a function per group and puts each group's answer on every row of the group | g["amount"].transform("sum") |
| Broadcast (here) | Copy one value onto many rows | 260 on every Pune row |
| Group-wise percentage | A row's value as a % of its own group's total | order 1 = 15.4% of Pune |
filter (groupby) |
Keep or drop whole groups using a function returning one bool | filter(lambda g: len(g) >= 3) |
| Boolean mask | A Series of True/False that picks rows | orders["amount"] > 40 |
| Scalar | A single value, not a Series or list | True, 42 |
lambda |
A one-line function without a name | lambda g: len(g) >= 3 |
apply (groupby) |
Calls your function on each group and glues the results | top order per city |
nlargest |
The n rows with the largest values in a column | g.nlargest(1, "amount") |
| MultiIndex | An index with more than one level | (city, item) |
| Hierarchical index | Another name for MultiIndex: inner levels sit inside outer ones | items inside cities |
| Level | One part of a MultiIndex; level 0 is the outermost | city = level 0 |
| Tuple | An ordered, unchangeable group of values | ("Nashik", "Chai") |
xs (cross-section) |
Select all rows with a value in a chosen level | s.xs("Chai", level="item") |
unstack |
Move an index level into the columns (long → wide) | items become columns |
stack |
Move column labels into the index (wide → long) | episode 27 |
fill_value |
What to put in empty cells after a reshape | unstack(fill_value=0) |
cumsum (cumulative sum) |
Running total; per group it restarts for each group | 40, 85, 115… |
cumcount |
Numbers rows inside each group from 0 | Nashik's 4th order → 3 |
rank |
Position of a value in its group | biggest order → 1 |
method="min" |
Ties share the best rank | two 50s → both rank 2 |
pd.Grouper |
Groups by a column with extra options, e.g. time buckets | pd.Grouper(key="date", freq="W") |
| Frequency alias | A short code for a time bucket | "D", "W", "ME", "QE" |
W-SUN |
Weekly buckets ending on Sunday | label 2026-11-08 |
parse_dates |
read_csv option that converts columns to datetimes |
parse_dates=["date"] |
Summary
- An aggregation gives one row per group and can't line up with the rows;
transformputs the group's answer on every row. - Group-wise percentage:
value / groupby(key)[value].transform("sum"). filterkeeps or drops whole groups (one bool per group);applyruns any custom function per group (flexible, slower).- Grouping by two keys gives a MultiIndex:
s["outer"],s.loc[(outer, inner)],xsfor inner levels,unstackfor a grid. cumsum,cumcountandrankwork inside each group.pd.Grouper(key="date", freq="W")buckets dates into weeks, months or quarters.- Next episode: combining data with concat, merge and join.