Using Hierarchical Indexes With Pandas

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))

<!--kg-card-end: code--><!--kg-card-begin: html-->

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 is referring to the 2010-2011 season). We also have columns such as team_name and game_id , which are fine candidates for indexes.

The key to having 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'm going to be looking at this data by drilling into the season 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'm going to organize our data by team_name to see team X's performance in a given season. A team's performance is determined by games, thus game_id will be our third and final index.

Here's how we'd set that index:

# Create Multiindex
nhlDF.set_index(['season', 'team_name', 'game_id'], inplace=True)
nhlDF.sort_index(inplace=True)
print(nhlDF.head(5))
print(nhlDF.tail(5))

Let's see how things have changed:

This is already looking more organized, but there's more to multiindexes than just 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 as to 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': they're nice 1-dimensional columns of data without any funny business:

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

<!--kg-card-end: code--><!--kg-card-begin: code-->

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 nhlDF['date'], so what's with the 4-columns? Like I mentioned earlier, the relationships we created with our hierarchical index makes 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 ( nhlDF['date'][0] still gets us the first value, etc) but now we have associated metadata about each row.

As mentioned, there are a lot of other methods for defining a multiindex in Pandas. Pandas has some methods such as MultiIndex.from_arrays and MultiIndex.from_tuples which give us some flexibility, but I personally have no interest in boring anybody with these. If you're feeling boring, read the docs.

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(nhlDF.index)

<!--kg-card-end: code--><!--kg-card-begin: code-->

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(nhlDF.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)

<!--kg-card-end: code--><!--kg-card-begin: code-->

[(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 nhlDF.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 considering 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 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(nhlDF.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(nhlDF.xs(20102011).head(10))

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

# Select Blackhawks Games
print(nhlDF.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 multiindex 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
lastSeasonDF = nhlDF.xs(20182019, level='season') # Group by last season
lastSeasonDF = lastSeasonDF.groupby(level=0).sum() # Leader board of teams
lastSeasonDF.sort_values(by=['won'], ascending=False, inplace=True)
print(lastSeasonDF)

Notice how we were able to group by index level using groupby(level=0). 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 which 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 multiindex DataFrames all along:

import pandas as pd

playersDF = pd.read_csv('flyers_players_20182019.csv') # Load CSV
playersDF.fillna(method='ffill', inplace=True) # Clean empty values
pivotDF = playersDF.pivot_table(index=['month', 'game_id'],
                                columns='name',
                                aggfunc='sum',
                                fill_value=0).swaplevel(axis=1).sort_index(1)
print(pivotDF)

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
girouxDF = pivotDF.loc['October', 'Claude Giroux'].sum()
print(girouxDF)

<!--kg-card-end: code--><!--kg-card-begin: code-->

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