17  Multi-level indexes

Warning

Any changes you make to the code on this page, including your solutions to exercises, are temporary. If you leave or reload the page, all changes will be lost. If you would like to keep your work, copy and paste your code into a separate file or editor where it can be saved permanently.

Multi-level (hierarchical) indexes allow us to organize data with multiple levels of labels. This is especially useful when working with grouped or hierarchical data.

Consider the following DataFrame:

Here, the combination of city and department uniquely identifies each row, so it makes sense to use them as a multi-level index:

We can visualize the hierarchy as Index level 0 (city) → Index level 1 (department) → row data (revenue, employees).

17.1 Selecting rows

To select a single row, we use the loc indexer with a tuple:

We can select all rows for a specific city (top-level index) using .loc:

To select rows based on a lower-level index (department in our case), we can either use a cross section:

Pandas also offers df.loc(axis=0)[:, ["Web", "Apps"]] for selecting on lower levels while preserving all index levels; we will not need it in this book.

17.2 Swapping index levels

If we need to swap the order of index levels, we can use the .swaplevel method:

17.3 Summary functions at different index levels

We can group data based on a specific level, for example summarizing per city (level 0):

Or per department (level 1):

17.4 Resetting indexes

Recall the .reset_index method. When applied to a multi-level index, all levels are moved into a DataFrame as separate columns:

We can also use this method to reset only a specific level of the index:

Exercise. Using the DataFrame from this chapter, calculate the total revenue for each city, and then the average number of employees for each department (across cities).

Sample solution

display(df.groupby(level=0)["revenue"].sum())

display(df.groupby(level=1)["employees"].mean())

Exercise. Select all Support rows using a cross-section. What happened to the department level? Then swap the index levels so that department becomes the top level, sort the index, and select all Support rows using .loc instead.

Sample solution

display(df.xs("Support", level=1))  # The department level is dropped from the result

df_swapped = df.swaplevel().sort_index()
display(df_swapped.loc["Support"])

17.5 Summary

df = df.set_index(["level0_col", "level1_col"])   # build a MultiIndex

df.loc[("x", "y")]              # a single row
df.loc["x"]                     # all rows for a top-level label
df.xs("y", level=1)             # cross-section on a lower level

df.swaplevel(), df.sort_index()
df.groupby(level=0).sum()       # summarize per index level
df.reset_index(), df.reset_index(level=0)