TopolearnMulti-key groupbys and pivots return MultiIndexes, so you need to read and tame them even if you never build one.
A MultiIndex labels each row (or column) with a tuple, one value per level, such as (region, store) or (year, month). You usually get one from groupby on several keys, set_index(["a", "b"]), pivot_table with several columns, or agg with several functions per column (which gives MultiIndex columns).
Selecting:
df.loc["north"]selects everything under the first-level label, dropping that level.df.loc[("north", "s1")]selects a full key;df.loc[("north", "s1"), "sales"]a cell.df.xs("s1", level="store")takes a cross-section on an inner level.idx = pd.IndexSlice; df.loc[idx[:, ["s1", "s2"]], :]slices inner levels; this requires a lexsorted index, so callsort_index()first (otherwise you get aPerformanceWarningorUnsortedIndexError).
Reshaping: unstack("store") moves a level into the columns, stack() moves it back, swaplevel() and reorder_levels() change order, droplevel(0) removes one.
Aggregating by level: df.groupby(level="region").sum().
Flattening, which you often want before export or plotting:
df.columns = ["_".join(map(str, c)) for c in df.columns] # ('sales', 'sum') -> 'sales_sum'
df = df.reset_index() # index levels -> columnsNamed aggregation (agg(total=("sales", "sum"))) avoids MultiIndex columns in the first place.
MultiIndexes are compact and powerful for panel-like data, but many people find flat tables with ordinary key columns easier to reason about. A good habit: use MultiIndexes transiently (for unstack, level-wise selection) and reset_index() before handing data on.
Example
import pandas as pd
df = pd.DataFrame({
"region": ["N", "N", "S", "S", "S"],
"store": ["s1", "s2", "s1", "s3", "s3"],
"month": [1, 1, 1, 1, 2],
"sales": [100, 80, 60, 90, 30],
})
g = df.groupby(["region", "store"])["sales"].sum().sort_index()
print(g.index.nlevels, g.loc["S"].to_dict()) # 2 {'s1': 60, 's3': 120}
print(g.loc[("N", "s2")]) # 80
print(g.xs("s1", level="store").to_dict()) # {'N': 100, 'S': 60}
idx = pd.IndexSlice
print(g.loc[idx[:, ["s1", "s3"]]].tolist()) # [100, 60, 120]
print(g.unstack("store", fill_value=0))
# store s1 s2 s3
# region
# N 100 80 0
# S 60 0 120
wide = df.groupby("region").agg({"sales": ["sum", "max"]})
print(wide.columns.tolist()) # [('sales', 'sum'), ('sales', 'max')]
wide.columns = ["_".join(c) for c in wide.columns]
print(wide.reset_index().columns.tolist()) # ['region', 'sales_sum', 'sales_max']Remember
- A MultiIndex labels rows or columns with tuples across levels
- Select with
loctuples,xs(level=...)andpd.IndexSlice - Sort the index before slicing inner levels
- Flatten columns and
reset_index()before exporting
Watch out for
- Slicing an unsorted MultiIndex and hitting UnsortedIndexError or a PerformanceWarning.