Pandas · Module 18 of 22 · 4 concepts · about 4 min

Indexes and MultiIndex

The index as a tool rather than a nuisance — and hierarchical indexes, which is where group-by output actually lives.

At work: Keeping labelled data lined up, and reading reports broken down by two things at once, like store and month.

set_index and reset_index

What it is

set_index promotes columns to the index; reset_index demotes them back to columns, with drop=True to discard rather than keep.

Why it matters

Operations that align, join or slice by label are all faster and clearer with a meaningful index — particularly time series.

In the real world: Making customer ID the index for fast lookups during analysis, then turning it back into a column before saving the result for a dashboard.

ts = df.set_index("ordered_at").sort_index()
ts.reset_index()                    # datetime becomes a column again
df.set_index(["country", "city"])   # a MultiIndex

Reflexively calling reset_index() after every group-by. It is often right, but doing it without looking is how people end up with a stray index column in their output.

Practise it: Average Order Value Per Customer, Count Orders Per Customer, Filter Rows and Keep Two Columns

Index alignment in assignment

What it is

Assigning a Series to a column aligns on the index, not on position. Values whose labels are absent become NaN.

Why it matters

It is the mechanism behind the single most common 'where did these NaNs come from' question.

In the real world: Adding each store's target to a sales table only works if both use the same store labels; otherwise targets land on the wrong stores or go missing.

scores = df[df["active"]]["total"] * 2   # index is a subset
df["doubled"] = scores                   # inactive rows get NaN, correctly
df["doubled"] = scores.to_numpy()        # WRONG: pairs by position

Using .to_numpy() or .values to silence a length-mismatch error. That discards the labels that were protecting you and pairs rows by position.

Practise it: Add a Line Total Column, Each Product's Share of Its Category

MultiIndex

What it is

A hierarchical index with several levels, produced by grouping on multiple keys. Select with tuples, .xs(), or level= arguments.

Why it matters

Most 'how do I flatten this' confusion after a group-by is a MultiIndex that has not been recognised as one.

In the real world: A report of sales by country and then by city, where you can pull out “all of India” or “just Pune” with one lookup.

g = df.groupby(["country", "tier"])["total"].sum()
g.loc[("US", "high")]            # one cell, by tuple
g.xs("high", level="tier")       # every country at one tier
g.unstack("tier")                # tiers become columns

Selecting from a MultiIndex by position after unstacking. Name the levels and select by name; positions shift when a level's contents change.

Duplicate index labels

What it is

An index may contain repeats. .loc[label] then returns every matching row rather than one, changing the shape of what comes back.

Why it matters

Code written against a unique index breaks in confusing ways once duplicates appear — often after a concat.

In the real world: A product ID that appears twice after a messy import makes a price lookup return two rows, and the next calculation breaks.

df.index.is_unique            # check before relying on .loc returning one row
df.index.duplicated().sum()   # how many repeats
df = df.reset_index(drop=True)  # if the labels carried no meaning

Assuming uniqueness rather than checking it. df.index.is_unique is one call.