I've been wandering into a lot of awkward conversations lately, most of them being about how I spend my free time. Apparently "rifling through Python library documentation in hopes of finding dope features" isn't considered a relatable hobby by most people. At least you're here, so I must be doing something right occasionally.

Today we'll be venturing off into the world of Pandas indexes. Not just any old indexes... hierarchical indexes. Hierarchical indexes take the idea of having identifiers for rows, and extends this concept by allowing us to set multiple identifiers with a twist: these indexes hold parent/child relationships to one another. These relationships enable us to do cool things like instantly organize our data into groups without performing groupbys. When used correctly, these relationships can be immensely helpful when we need to do some really powerful analysis.

Hierarchical indexes (AKA multiindexes) help us to organize, find, and aggregate information faster at almost no cost. Organizing data in this way is super cool, but also quite tricky to get the hang of at first. We'll take it one step at a time.

Creating a DataFrame With a Hierarchical Index

There are many ways to declare multiple indexes on a DataFrame - probably way more than you'll ever need. The most straightforward approach is just like setting a single index; we pass an array of columns to index= instead of a string!

To demonstrate the art of indexing, we're going to use a dataset containing a few years of NHL game data. Let's load it up:

import pandas as pd

nhlDF = pd.read_csv('data/nhl.csv')
nhlDF['date'] = pd.to_datetime(nhlDF['date'])
print(nhlDF.head(5))
print(nhlDF.tail(5))

Each row in our dataset contains information regarding the outcome of a hockey match. We have a row called season, with values such as 20102011. This integer represents the NHL season in which the game was played (in this example, 20102011 refers to the 2010-2011 season). We also have columns such as team_name and game_idsuccess with hierarchical indexes is first considering how we want to look at our data, which are fine index candidates.

The key to success with hierarchical indexes is to first consider how we want to look at our data and which questions we're trying to answer. Off the bat, I already know that I will be looking at this data by drilling into the seasonGames determine a team's performance first: teams change drastically from season to season, and I'm more interested to see how our teams did on a per-season basis than a period of 8 years. Next, I will organize our data by team_name to see Team X's performance in a given season. Games determine a team's performance; thus game_id will be our third and final index.

Here's how we'd set that index:

# Create Multiindex
nhl_df.set_index(['season', 'team_name', 'game_id'], inplace=True)
nhl_df.sort_index(inplace=True)

# Print a preview of our data
print(nhl_df.head(5))
print(nhlDF.tail(5))

Let's see how things have changed:

Our data already looks more organized, but there's more to multiindexes than looks. We've created a hierarchy of relationships in our data! With these indexes, we've silently made small distinctions in our data. For instance, we've distinguished that the Golden Knights of 2018-2019 are a different entity than the Golden Knights, which made it to the Stanley Cup in the 2017-2018 season. We also associated all the games played by the Golden Knights this season t0 this season's Golden Knights, which is, in turn, a child of 2018-2019. As a result, we've gained insight into which season each game belongs to.

While this hierarchy of indexes is in place, the information stored across all 3 indexes is inseparable from the values in each row. I'll show you what I mean.

2D Series

Let's look at a series in our newly indexed data. You know all about Pandas' Series datatype: they're nicely keyed columns of data without any funny business:

# Demonstrate 2D Series
print(nhl_df['date'].head(10))

season    team_name  game_id    
20102011  Avalanche  2010020004   2010-10-08
                     2010020031   2010-10-11
                     2010020034   2010-10-12
                     2010020048   2010-10-15
                     2010020057   2010-10-16
                     2010020070   2010-10-18
                     2010020090   2010-10-22
                     2010020108   2010-10-24
                     2010020122   2010-10-27
                     2010020136   2010-10-29
Name: date, dtype: datetime64[ns]

Whoa, what's all this funny business?! We printed a single column with nhl_df['date'], so what's with the 4-columns?

As mentioned earlier, the relationships we create with hierarchical indexes make these relationships inseparable: no matter how we mess with our data, the indexes will follow. In essence, this makes our series 2-dimensional. This is really cool because we can manipulate our series as normal ( nhl_df['date'][0] still gets us the first value, etc.), but now we have associated metadata about each row.

Inspecting and Modifying DataFrame Indexes

Before we get too crazy, let's quickly cover a few fundamental multiindex bases. Check out what happens when we inspect the indexes on our DataFrame:

# Inspect indexes
print(nhl_df.index)

Output:

MultiIndex(levels=[[20102011, 20112012, 20122013, 20132014, 20142015, ...],
                   ['Avalanche', 'Blackhawks', 'Blue Jackets', ...],
                   [2010020001, 2010020002, 2010020003, 2010020004, ...]],
           labels=[[0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, ...]],
           names=['season', 'team_name', 'game_id'])

A wild MultiIndex appears! In Pandas, MultiIndex is a datatype in itself. This can be useful if you need to deal with creating indexes with highly complex logic dynamically. It's pretty cool that we can rip the indexing scheme out of any DataFrame, as well as pass that scheme into a new DataFrame.

I've truncated the values returned by print(nhl_df.index) to avoid pasting thousands of results. Let's break this down. The names attribute reiterates our hierarchical structure: ['season', 'team_name', 'game_id']. We also see the values of our indexes being populated into levels.

A level refers to the name of one of the indexes in our hierarchy. Our left-most index is our highest-level index and can be referred to as level 0. In our example, season is level 0. Levels can also be referred to by their name, thus level=0 is interchangeable with level='season'. Modifying levels has the same syntax as working with columns.

Let's look at our index values tho:

# Inspect index values
print(nhlDF.index.values)

[(20102011, 'Avalanche', 2010020004) (20102011, 'Avalanche', 2010020031)
 (20102011, 'Avalanche', 2010020034) ... (20182019, 'Wild', 2018021234)
 (20182019, 'Wild', 2018021250) (20182019, 'Wild', 2018021267)]

Oh snap, tuples! Generally speaking, hierarchical indexes like to be tossed around as tuple values. Not really useful for now, but maybe for later.

Sorting Data by Index

You may have noticed that I blew past nhl_df.sort_index(inplace=True) when we created our indexes. Sorting our index is very important after setting a hierarchical index; if we hadn't done so, selecting and aggregating our data could actually result in errors. If we consider the way indexes are "grouped," it makes sense as to why this would happen: how can we group our data in a visually pleasing way if it's still scattered everywhere?

Resetting Indexes

Remember that columns and indexes are not the same, even if they may seem similar. When a column becomes an index, the original "column" is dropped, and an index is added to our DataFrame with the values that were contained in said column. While we still have values for season, team_name, and game_id, our DataFrame is technically 3 columns shorter than our original import. What have I done?! Does this mean I secretly destroyed the dataset you've been following along with? How could I!

Chill. Everything we've done thus far can be immediately undone at any time using nhlDF.reset_index(inplace=True). Resetting the index on a multiindex DataFrame unstacks our data and re-adds the original columns.

It's worth considering how powerful this can be if used correctly. I was able to index 22,694 rows on a free cluster in the blink of an eye and can disregard this just as quickly and easily. If we wanted, we could gun-sling DataFrame indexes all we want just for the purpose of answering a few questions, and revert back whenever we wanted. Let's see how these indexes might help us solve such questions.

Other Index Modifications

There's more we can do with index customization. Picking up on the usefulness of these things comes with a bit of time (and StackOverflow), but it doesn't hurt to know about these:

• Swap Index Levels: df.swaplevel(i='level_name_1', j='level_name_2')
• Rename Indexes: df.index.names = ['name1', 'name2']
• Remove a single index level: df.unstack(level=0)

Selecting Data in a Hierarchical Index

Now we can start doing something worthwhile. Let's see how using hierarchical indexes can help us find the data we're looking for.

Using .loc()

It's our good friend .loc()! Let's use .loc() to find all games in the 2010-2011 season:

# Select 2010-2011 season using iloc
print(nhl_df.loc[20102011, :].head(10))

We're searching for instances of 20102011 here. We pass : to specify "all columns" for each row matched. If we wanted, we could replace : with column labels to find "all values in [columns] where row label is 20102011".

Boom. Those are all the games in 2010-2011. Notice how our season index appears to be missing? When we select and/or aggregate into an index, the level of index we're working against is omitted from the result to avoid being redundant.

Using .xs()

.xs() stands for cross section: it accepts a value to be found in an index, allowing for easier selection of rows by index. The below snippet will yield the same result as what we accomplished with .loc():

# Select 2010-2011 season using .xs()
print(nhl_df.xs(20102011).head(10))

What if we tried this using a team name instead of a season?

# Select Blackhawks Games
print(nhl_df.xs('Blackhawks').head(10))

I've got a bad feeling about this...

KeyError 'Blackhawks' 
------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
pandas/_libs/index.pyx in pandas._libs.index.IndexEngine.get_loc()

pandas/_libs/hashtable_class_helper.pxi in pandas._libs.hashtable.Int64HashTable.get_item()

TypeError: an integer is required

...

What went wrong? Notice the error: TypeError: an integer is required. By default, .xs() always searches against level 0 unless otherwise stated. We accidentally looked for Blackhawks against our season index, as opposed to our team_name index. We can fix this by setting our level attribute:

# Select Blackhawks (in level 2) using .xs()
print(nhlDF.xs('Blackhawks', level=1).head(10))

Or alternatively...

# Select Blackhawks (in level 2) using .xs()
print(nhlDF.xs('Blackhawks', level='team_name').head(10))

Either of those two lines will end up with this same result:

Aggregating with Multiindexes

You've probably started to consider the parallels between a multi-index DataFrame and a DataFrame with values grouped using .groupby(). Good! Let's play with aggregates by pulling each team's stats from last season:

# Get team stats for last season
last_season_df = nhl_df.xs(20182019, level='season')  # Group by last season
last_season_df = last_season_df.groupby(level=0).sum()  # Leaderboard of teams
last_season_df.sort_values(by=['won'], ascending=False, inplace=True)
print(last_season_df)

Notice how we were able to group by index level using groupby(level=0). Notice we're grouping on team_name which is normally level 1, but we passed level 0: that's because running .xs() on the line above omits season from this selection's index, thus team_name becomes the new level 0.

Columns with Hierarchical Indexes

It's all been fun and games until now... that's about to change. It's time to take the gloves off. Until now, we've been speaking as though rows are the only elements that can be indexed in Pandas. Not only can we also index columns, but we can create a `DataFrame `with a hierarchal index across both rows and columns simultaneously.

I'm going to use a dataset of NHL player performance per game to demonstrate (I've limited this to players on the Flyers). Here's a preview of the raw data:

How do we set multiindexes across two axes at once? A pivot table, of course! That's right, you've been using playing in multi-index DataFrames all along:

import pandas as pd

players_df = pd.read_csv('flyers_players_20182019.csv')  # Load CSV
players_df.fillna(method='ffill', inplace=True)  # Clean empty values

# Create a pivot table
pivot_df = playersDF.pivot_table(
    index=['month', 'game_id'],
    columns='name',
    aggfunc='sum',
    fill_value=0
).swaplevel(axis=1).sort_index(1)
print(pivot_df)

We're able to set multiple row indexes on a pivot table the very same way we did earlier: by passing a list of columns via index=['month', 'game_id']. The rest of our pivot table looks standard, but then we do a bit of magic with .swaplevel(). Here's what we get:

Each of our columns now has two labels! By repeating each stat per player on the team, it's much easier for us to draw conclusions about individual player performance! For instance, let's see what kind of numbers Giroux put up in October:

# Get Claude Giroux's stats for October 2018
girouxDF = pivotDF.loc['October', 'Claude Giroux']
print(girouxDF)

This gives us the cross-section of values where row labels match October and column labels match Giroux:

That's beautiful. The awesome thing about what we've just accomplished is we didn't destroy or modify any data to get to this point. We're still using the same dataset we started with: the only difference is the relationships we've added with indexes.

Finally, let's aggregate these values to give us totals for the month of October, just for kicks:

# Aggregate Giroux's stats
giroux_df = pivot_df.loc['October', 'Claude Giroux'].sum()
print(giroux_df)

Output:

assists      10
blocked       1
goals         3
hits          4
shots        44
takeaways     5
dtype: int64