MultiIndex, stack and unstack

Group by two keys and pandas hands you a MultiIndex. It looks exotic and isn't: it's an index whose labels are tuples instead of single values.

s = orders.groupby(["city", "item"])["cups"].sum()
s.index          # MultiIndex([("Delhi", "elaichi"), ("Delhi", "ginger"), ...])
s.loc[("Delhi", "ginger")]

You'll meet them from groupby with several keys, from pivot_table, from concat(keys=...), and from agg with a list of functions.

unstack moves a level up

s.unstack()             # innermost level becomes the columns
s.unstack(level=0)      # or name which one
s.unstack(fill_value=0) # for combinations that never happened

Two levels down the side become one down and one across — which is exactly what pivot_table produced, arrived at from the other direction. They're the same operation with different entry points.

Going wide always materialises the combinations that never occurred. Pune never sold ginger, so unstacking invents that cell and fills it with NaN — and promotes the column to float in the process.

stack moves it back

wide.stack()                  # columns fold down into the index
wide.stack(dropna=False)      # keep the empty combinations

By default stack drops the NaN cells on the way down. That's usually helpful and occasionally surprising: a round trip through unstack().stack() can come back shorter than it went, having quietly removed combinations that were never there.

Selecting

s.loc[("Delhi", "ginger")]        # one cell — the label is a tuple
s.loc["Delhi"]                     # the whole outer group
s.loc["Delhi":"Mumbai"]            # a slice of the outer level
s.xs("masala", level="item")       # cut ACROSS — every city's masala
s.index.get_level_values("city")   # one level as a flat Index

.xs is the one worth remembering, because plain .loc can't express "every city's masala" — that cuts across the outer level rather than down it.

Almost all level slicing wants a sorted index. If you get UnsortedIndexError, or lexsort warnings, the fix is .sort_index() — do it as soon as you build the MultiIndex.

Rearranging levels

s.swaplevel().sort_index()     # put item on the outside instead
s.reorder_levels(["item", "city"])
s.groupby(level="city").sum()  # aggregate one level away

Or just flatten it

s.reset_index()                # levels become ordinary columns

Honestly, this is the right move most of the time. A MultiIndex earns its keep when you'll genuinely slice by level or the hierarchy is real — otherwise flat columns are easier to think about, easier to merge, and easier for the next person to read.

The same goes for MultiIndexed columns, which agg with a list of functions produces. Flatten them and move on:

df.columns = ["_".join(c).strip("_") for c in df.columns]