HowToCodePage

The index in depth: MultiIndex, set_index and reindex

Hierarchical indexes, stack and unstack, reindex, and the alignment rules that quietly decide whether your numbers land on the right rows.

Making the index mean something

import pandas as pd

df = pd.DataFrame({
    "region": ["north", "north", "south", "south"],
    "month": ["jan", "feb", "jan", "feb"],
    "sales": [10, 12, 7, 9],
})

t = df.set_index(["region", "month"])     # two levels: a MultiIndex
t.index.names                             # FrozenList(['region', 'month'])
t.loc["north"]                            # everything for one region
t.loc[("north", "feb"), "sales"]          # 12 - exact label path
t.loc[("north", "feb"):, :]               # slice from that point
t.xs("feb", level="month")                # cross-section: keep other levels
t.droplevel("month")                      # drop a level, keep the rest

t.reset_index()                           # back to flat columns
df.set_index("region", drop=False)        # keep the column as well
OperationEffect
set_indexMoves columns into the index, removing them from the data
reset_indexMoves index levels back out as ordinary columns
xsSelects one value on one level, keeping the rest
swaplevelExchanges two levels, then sort_index() to make it searchable
sort_indexRequired before slicing a MultiIndex with : works predictably

stack and unstack

wide = t.unstack(level="month")     # the month level becomes columns
wide.columns                        # MultiIndex: (sales, feb), (sales, jan)

long = wide.stack(level="month")    # columns back into the index
long = wide.stack(future_stack=True)  # future behaviour, no silent row dropping

flat = wide.copy()
flat.columns = ["_".join(map(str, c)) for c in flat.columns]   # flatten to strings
flat.columns.name = None
flat = flat.reset_index()
  • stack is the inverse of unstack, but not perfectly: missing combinations are dropped or become NaN depending on the setting.
  • Newer pandas warns about the implicit dropna in stack; pass future_stack=True to get the documented behaviour now.
  • Column labels become a MultiIndex as soon as you unstack a MultiIndex column level. Flatten them before exporting, or the next reader gets tuple column names.

reindex and alignment

s = pd.Series([1, 2, 3], index=["a", "b", "c"])
s.reindex(["a", "b", "c", "d"])                    # d -> NaN
s.reindex(["c", "a"])                              # reordered, not filtered randomly
s.reindex(["a", "b", "d"], fill_value=0)           # missing filled with 0

# a fully defined rectangular index: every region x every month
idx = pd.MultiIndex.from_product([["north", "south"], ["jan", "feb", "mar"]])
t.reindex(idx, fill_value=0)

left  = pd.DataFrame({"v": [1, 2]}, index=["x", "y"])
right = pd.DataFrame({"v": [10, 20]}, index=["y", "x"])
left + right          # matched by label, so x = 11 and y = 22 despite the order
⚠️
Reindexing and arithmetic never raise on a missing label - they produce NaN. After any reindex, check the null count for the column you rely on, and remember that reindex on an axis with duplicate labels raises ValueError: deduplicate first.

FAQ

When is a MultiIndex worth it?
When you genuinely have a hierarchy you group and slice by - region and month, product and channel. If you mostly read rows flat, keep the columns and use groupby instead.
Why did reindex give me NaN instead of an error?
Reindexing is defined as "align to this axis": labels that do not exist are introduced as missing. Verify with df.isna().sum() or use fill_value when zero is the honest answer.

Pivot tables and crosstab Dates, times and time series

Last refreshed 2026-09-18.