Module 6: Reshaping · Lesson 6.4 · about 15 min
MultiIndex without the fear
Understand the two-level row and column labels that groupby and pivot_table create, select from them with loc and xs, and flatten them back to plain columns when you are done.
Module 6 · about 15 minutes · uses Lesson 6.3. You’ll be able to read, select from and flatten tables with two-level labels.
In everyday terms: an address: the street alone gives a block of houses, street plus number gives one house. loc[‘S01’] is the street, loc[(‘S01’, ‘P101’)] the house.
1. Where they come from
Grouping by two keys, or aggregating several columns with several functions, gives labels with two levels:
2. Selecting
3. Flatten when done
Better still, avoid the column MultiIndex from the start with named aggregations (Lesson 4.2) and as_index=False.
- Where it comes from
- groupby on two columns, pivot_table with several values, set_index([a, b]).
- df.loc['S01']
- All rows under the outer label S01.
- df.loc[('S01', 'P101')]
- One row: both labels.
- xs('P101', level='product')
- All rows with that inner label, any outer.
- Physical meaning
- The labels are a sorted tree: outer level, then inner level inside each. Nothing new is stored; reset_index() turns the levels back into ordinary columns, the shape for joins and exports.
“MultiIndex is advanced, avoid it.” You meet it after every two-column groupby. Two moves cover nearly every case: loc with a tuple to select, reset_index() to flatten.
Watch it happen, one step at a time:
df.groupby(['store', 'product'])['qty'].sum()| store | product | qty |
|---|---|---|
| S01 | P101 | 5 |
| P102 | 2 | |
| S02 | P101 | 3 |
.loc['S01'] → P101 5, P102 2..loc[('S02', 'P101')] → 3..reset_index()| store | product | qty | |
|---|---|---|---|
| 0 | S01 | P101 | 5 |
| 1 | S01 | P102 | 2 |
| 2 | S02 | P101 | 3 |
4. Your turn
How it works
Principle: a MultiIndex is a list of label tuples, sorted level by level, so pandas can jump to “all rows for S01” quickly. It’s compact for display and for unstack, but plain columns are easier for joins, filters and saving. A good habit: use MultiIndex while reshaping, then reset_index() before the next step.
5. Practice
- How do you list the values of the second index level?
- Flatten columns
('sales', 'sum')style intosales_sum. - Select all stores for 2026 from a (store, year) index.
Answers
t.index.get_level_values(1).unique()(or by name).t.columns = ['_'.join(map(str, c)) for c in t.columns].t.xs(2026, level='year').
New words: MultiIndex, level, tuple label, xs, flatten.
- Two-column groupby and pivot_table make MultiIndexes.
- loc[outer] for a block; loc[(outer, inner)] for one row.
- xs(value, level=…) selects by an inner label.
- reset_index() flattens back to plain columns.
Check yourself
Sources
- pandas documentation, User Guide: MultiIndex / advanced indexing https://pandas.pydata.org/docs/user_guide/advanced.html
- McKinney, W. (2022), Python for Data Analysis, 3rd edition, O'Reilly (open access) https://wesmckinney.com/book/
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.