SyntaxPilot
Courses/Data Analytics with Python/Lesson 25

Data Analytics with Python · Lesson 25 of 90 · 13 min

GroupBy 2: transform, filter and MultiIndex

transformfilterapply on groupsMultiIndexhierarchical indexxsunstackgroup-wise percentagecumsum per groupcumcountrank within groupgroupby with pd.Grouper

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=False gives 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

  1. Difference between agg and transform? agg returns one row per group; transform returns a result with the same index and length as the input, the group's answer repeated on each row.
  2. How do you compute each row's percentage of its group total? df["v"] / df.groupby("g")["v"].transform("sum").
  3. 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.
  4. 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.
  5. When would you use apply on 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.
  6. What is a MultiIndex? An index with several levels (e.g. city, item); created by grouping by several keys, set_index with a list, or concat(keys=...).
  7. How do you select a value on an inner level? s.xs("Chai", level="item"), or s.loc[(slice(None), "Chai")].
  8. stack vs unstack? unstack moves an index level into columns (long → wide); stack moves column labels into the index (wide → long).
  9. Running total per customer? df.groupby("customer")["amount"].cumsum() after sorting by date.
  10. Top 3 products per category? df[df.groupby("cat")["sales"].rank(ascending=False, method="first") <= 3], or groupby("cat").apply(lambda g: g.nlargest(3, "sales")).
  11. Monthly totals from a date column? df.groupby(pd.Grouper(key="date", freq="ME"))["amount"].sum() (or resample, episode 28).
  12. 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; transform puts the group's answer on every row.
  • Group-wise percentage: value / groupby(key)[value].transform("sum").
  • filter keeps or drops whole groups (one bool per group); apply runs any custom function per group (flexible, slower).
  • Grouping by two keys gives a MultiIndex: s["outer"], s.loc[(outer, inner)], xs for inner levels, unstack for a grid.
  • cumsum, cumcount and rank work 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.