Using Hierarchical Indexes With Pandas
Use Panda's multi-index to create smarter datasets. Speed up your workflow by easily selecting and aggregating related data.
- Comparing Rows Between Two Pandas DataFrames
- Using Hierarchical Indexes With Pandas
- Reshaping Pandas DataFrames
- Data Visualization With Seaborn and Pandas
- Parse Data from PDFs with Tabula and Pandas
- Lazy Pandas and Dask
- Automagically Turn JSON into Pandas DataFrames
- Connecting Pandas to a Database with SQLAlchemy
- Dropping Rows of Data Using Pandas
- Merge Sets of Data in Python Using Pandas
- Another 'Intro to Data Analysis in Python Using Pandas' Post
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))
|20102011||Avalanche||2010020004||TRUE||OT||home||3||4||Blackhawks||Avalanche||October 8, 2010|
|20102011||Avalanche||2010020031||FALSE||REG||away||2||4||Avalanche||Flyers||October 11, 2010|
|20102011||Avalanche||2010020034||TRUE||SO||away||5||4||Avalanche||Red Wings||October 12, 2010|
|20102011||Avalanche||2010020048||TRUE||REG||away||3||2||Avalanche||Devils||October 15, 2010|
|20102011||Avalanche||2010020057||FALSE||REG||away||2||5||Avalanche||Islanders||October 16, 2010|
|20182019||Wild||2018021202||TRUE||REG||away||3||2||Wild||Golden Knights||March 30, 2019|
|20182019||Wild||2018021217||FALSE||REG||away||0||4||Wild||Coyotes||March 31, 2019|
|20182019||Wild||2018021234||TRUE||REG||home||1||5||Jets||Wild||April 3, 2019|
|20182019||Wild||2018021250||FALSE||REG||home||3||0||Bruins||Wild||April 5, 2019|
|20182019||Wild||2018021267||FALSE||REG||away||0||3||Wild||Stars||April 7, 2019|
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.
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))
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'] still gets us the first value, etc) but now we have associated metadata about each row.
MultiIndex.from_tupleswhich 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)
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'])
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)
[(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?
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:
- Rename Indexes:
df.index.names = ['name1', 'name2']
- Remove a single index level:
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.
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.
.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
# 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))
# 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). 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 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:
|January||2018020613||James van Riemsdyk||0||2||0||0||2||0|
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)
assists 10 blocked 1 goals 3 hits 4 shots 44 takeaways 5 dtype: int64