👨💻
👨💻
👨💻
👨💻
Notes by Taha Maddam
Search
⌃K

Hierarchical Indexing In Pandas

While Pandas does provide Panel and Panel4D objects to natively handle three-dimensional and four-dimensional data, a far more common practice is to use hierarchical indexing (also known as multi-indexing) to incorporate multiple index levels within a single index. Doing this, higher-dimensional data can be compactly represented with the familiar one-dimensional Series and two-dimensional DataFrame objects​
import numpy as np
import pandas as pd

1. CREATING MULTI-INDEXED SERIES

First, let’s create a multi-index data from the tuples as follows:
# defining multi-index in form of tuples
index = [('City A', 2018),('City A', 2019),
('City B', 2018), ('City B', 2019),
('City C', 2018), ('City C', 2019)]
​
index
[('City A', 2018),
('City A', 2019),
('City B', 2018),
('City B', 2019),
('City C', 2018),
('City C', 2019)]
Second, provide the above multi-index data to Pandas pd.MultiIndex.from_tuples() function:
# creating multi-index in Pandas from Tuples (we created above)
index_pd = pd.MultiIndex.from_tuples(index)
​
index_pd
MultiIndex([('City A', 2018),
('City A', 2019),
( 'City B', 2018),
( 'City B', 2019),
('City C', 2018),
('City C', 2019)],
)
Third, define the data, pop for our multi-index series, in the form of list:
# Defining data for the multi-index
pop = [33871648, 37253956, 18976457, 19378102, 20851820, 25145561]
Fourth, use pd.Series constructor with data and index as arguments:
# Linking multi-index with population data
pop = pd.Series(pop, index=index_pd)
​
pop
City A 2018 33871648
2019 37253956
City B 2018 18976457
2019 19378102
City C 2018 20851820
2019 25145561
dtype: int64
  • In the above example, the first two columns of the Series representation show the multiple index values, while the third column shows the data
  • Notice that some entries are missing in the first column: in this multi-index representation, any blank entry indicates the same value as the line above it
➞ Indexing and Slicing syntax will be the same as we covered in Indexing Pandas Series and DataFrame​
Population of City A, for all years:
pop['City A']
2018 33871648
2019 37253956
dtype: int64
Population of all cities, for year 2018
pop[:,2018]
City A 33871648
City B 18976457
City C 20851820
dtype: int64
Population of City A for year 2018
pop['City A',2018]
33871648

1.1. Stack and Unstack

a. unstack Method

We could easily have stored the same data using a simple DataFrame with index and column labels. The unstack() method will quickly convert a multi-indexed Series into a conventionally indexed DataFrame
pop_df = pop.unstack()
print(pop_df)
year 2018 2019
city
City B 18976457 19378102
City A 33871648 37253956
City C 20851820 25145561

b. stack

The stack() method provides the opposite operation than unstack() — converts DataFrame to multi-indexed Series
pop_df = pop_df.stack()
pop_df
City B 2018 18976457
2019 19378102
City A 2018 33871648
2019 37253956
City C 2018 20851820
2019 25145561
dtype: int64

1.2. Handling three or more Dimensions

Just as we were able to use multi-indexing to represent two-dimensional DataFrame within a one-dimensional Series, we can also use it to represent data of three or more dimensions in a Series or DataFrame. Each extra level in a multi-index represents an extra dimension of data.
pop_df = pd.DataFrame({'total': pop,
'under18': [9267089, 9284094, 4687374, 4318033, 5906301, 6879014]})
print(pop_df)
total under18
city year
City B 2018 18976457 9267089
2019 19378102 9284094
City A 2018 33871648 4687374
2019 37253956 4318033
City C 2018 20851820 5906301
2019 25145561 6879014

1.3. Applying UFunc

Let’s find the percentage of under 18 population in each city, each year:
pop_u18_percent = pop_df['under18'] / pop_df['total']
pop_u18_percent
City A 2018 0.273594
2019 0.249211
City B 2018 0.247010
2019 0.222831
City C 2018 0.283251
2019 0.273568
dtype: float64
print(pop_u18_percent.unstack())
year 2018 2019
city
City B 0.247010 0.222831
City A 0.273594 0.249211
City C 0.283251 0.273568

2. VARIOUS METHODS OF MULTI-INDEX CREATION

In Section 1, we studied one way to create a multi-index object using tuples. In this section, we will study various methods/techniques for creating multi-index object and using it to create Series and DataFrame:
➞ The most straightforward way to construct a multi-indexed Series or DataFrame is to simply pass a list of two or more index arrays to the pd.Series() or pd.DataFrame constructor. The number of data points should be equal to number of indices.
create_mi_df = pd.DataFrame(np.random.rand(4,2),
index=[['a','a','b','b'],[1,2,1,2]],
columns=['data1','data2'])
print(create_mi_df)
data1 data2
a 1 0.695555 0.776309
2 0.696634 0.502602
b 1 0.322619 0.127614
2 0.293457 0.415007
➞ We can also create multi-index Series by passing dictionary with appropriate tuples as keys, Pandas will automatically recognize the indices and data values:
data_for_series = {('California', 2000): 33871648,
('California', 2010): 37253956,
('Texas', 2000): 20851820,
('Texas', 2010): 25145561,
('New York', 2000): 18976457,
('New York', 2010): 19378102}
pd.Series(data_for_series)
California 2000 33871648
2010 37253956
Texas 2000 20851820
2010 25145561
New York 2000 18976457
2010 19378102
dtype: int64

2.1. Explicit Multi-index constructors

We can use the class method available in the pd.MultiIndex

a. from_arrays

pd.MultiIndex.from_arrays([['a','a','b','b'],[1,2,1,2]])
MultiIndex([('a', 1),
('a', 2),
('b', 1),
('b', 2)],
)

b. from_tuples

pd.MultiIndex.from_tuples([('a',1),('a',2),('b',1),('b',2)])
MultiIndex([('a', 1),
('a', 2),
('b', 1),
('b', 2)],
)

c. from_product

This one is easiest of all three, needs to input least amount of data:
pd.MultiIndex.from_product([['a','b'],[1,2]])
MultiIndex([('a', 1),
('a', 2),
('b', 1),
('b', 2)],
)

2.2. Multi-Index level names

In this sub-section, we will learn various methods to name the multi-index:

a. Directly as argument in Explicit Multi-Index constructor

In sub-section 2.1, we studied three explicit multi-index constructor. In them, we can provide keyword argument names=[] to define the name of each index level:
pd.MultiIndex.from_arrays([['a','a','b','b'],[1,2,1,2]], names=['alpha','num'])
MultiIndex([('a', 1),
('a', 2),
('b', 1),
('b', 2)],
names=['alpha', 'num'])

b. By setting index.name for Series or DataFrame

If we have already created a multi-index Series or DataFrame object without index level names, we can use the method index.names=[] to explicitly set the names of each index level
# reproducing Series that don't have index name
pop
City A 2018 33871648
2019 37253956
City B 2018 18976457
2019 19378102
City C 2018 20851820
2019 25145561
dtype: int64
# setting index level names
pop.index.names = ['city','year']
pop
city year
City A 2018 33871648
2019 37253956
City B 2018 18976457
2019 19378102
City C 2018 20851820
2019 25145561
dtype: int64

2.3. Multi-levels for Columns

In a DataFrame, the rows and columns are completely symmetric, and just as the rows can have multiple levels of indices, the columns can also have multiple levels
# creating hierarchical (multiple) indices and columns
indices = pd.MultiIndex.from_product([[2018,2019],[1,2]],
names=['year','exam'])
​
columns = pd.MultiIndex.from_product([['Tom', 'Harry', 'John'], ['HR', 'Marketing']],
names=['student', 'marks'])
​
# mock data
# how do we know that we need to have 4*6 = 24 data points?
data = np.random.randint(50,100, size=(4,6))
​
# create Dataframe with multiple indices and colums
df_multi = pd.DataFrame(data,
index=indices,
columns=columns)
# result
print(df_multi)
student Tom Harry John
marks HR Marketing HR Marketing HR Marketing
year exam
2018 1 58 85 54 91 73 69
2 70 98 64 70 79 96
2019 1 67 79 87 79 81 70
2 81 72 90 88 80 66

3. INDEXING AND SLICING A MULTI-INDEX

3.1. Multi-Index Series

Remember that [] method of indexing and slicing on Series object, applies on the index labels
# multi-index series to perform indexing and slicing
pop
city year
City A 2018 33871648
2019 37253956
City B 2018 18976457
2019 19378102
City C 2018 20851820
2019 25145561
dtype: int64
Let’s use indexing techniques to answer some basic question about the above multi-indexed Series, pop
→ What is population of City A in 2018
pop['City A', 2018]
33871648
→ What is population of City A in all available years
pop['City A']
year
2018 33871648
2019 37253956
dtype: int64
→ To use .loc, make sure that index is sorted. If it is not, use .index.sort() on Series or DataFrame object. Now, let ask question, what is the population of City A and City B, in all available year
# using .loc
pop.loc['City A':'City B']
city year
City A 2018 33871648
2019 37253956
City B 2018 18976457
2019 19378102
dtype: int64
→ We can also use the integer based indexing. Let’s fetch first two rows using [:2]
# using integer value of index
pop[:2]
city year
City A 2018 33871648
2019 37253956
dtype: int64
→ What is population of all cities, for year 2018
pop[:,2018]
city
City A 33871648
City B 18976457
City C 20851820
dtype: int64
→ What is population of City A and City B in all available years:
# fancy indexing
# pay attention that we use the array here
pop[['City A','City B']]
city year
City B 2018 18976457
2019 19378102
City A 2018 33871648
2019 37253956
dtype: int64

3.2. Multi-Index DataFrame

Remember that [] method of indexing and slicing on DataFrame object, applies on the column labels. Therefore to apply indexing on index level, we can use .iloc[] and loc[]
# reproduing dataframe we will work on
print(df_multi)
student Tom Harry John
marks HR Marketing HR Marketing HR Marketing
year exam
2018 1 58 85 54 91 73 69
2 70 98 64 70 79 96
2019 1 67 79 87 79 81 70
2 81 72 90 88 80 66
Let’s use indexing techniques to answer some basic question about the above multi-indexed DataFrame, df_multi
→ What are marks of student, Tom, in all the subjects , for all available years and exams:
print(df_multi['Tom'])
marks HR Marketing
year exam
2018 1 58 85
2 70 98
2019 1 67 79
2 81 72
→ Tom marks in HR, for all available years and exams:
df_multi['Tom','HR']
year exam
2018 1 81
2 80
2019 1 79
2 55
Name: (Tom, HR), dtype: int64
➞ Fetching first row of a multi-index DataFrame using iloc[] method
print(df_multi.iloc[:1])
student Tom Harry John
marks HR Marketing HR Marketing HR Marketing
year exam
2018 1 58 85 54 91 73 69
➞ Fetching first two rows and first two columns using iloc[,] method. The integers for slicing that we provide before the , in iloc[ , ] applies to row and after the , applies to column
print(df_multi.iloc[:2,:2])
student Tom
marks HR Marketing
year exam
2018 1 58 85
2 70 98
→ We can also use the explicit values of index and column labels using .loc[] For example, let’s get score of all students, in all the subjects, for all the exams, but only in year 2018:
print(df_multi.loc[2018])
student Tom Harry John
marks HR Marketing HR Marketing HR Marketing
exam
1 58 85 54 91 73 69
2 70 98 64 70 79 96
→ We can also use .loc[ , ] to slice at both index and column levels. Let’s fetch scores of Tom, in all subjects and all exams, but only in year 2018:
print(df_multi.loc[2018,'Tom'])
marks HR Marketing
exam
1 58 85
2 70 98

4. REARRANGING MULTI-INDICES

We saw few examples of this concept, sub-section 1.1. under stack() and unstack() methods, but there are many more ways to finely control the rearrangement of data between hierarchical indices and columns

4.1. Sorted and Unsorted indices

We can sort the index of a Series or DataFrame object using .sort_index() method:
# defining index of Series
index_series = pd.MultiIndex.from_product([['c','a','b'],[1,2,3]])
​
# defining data
data_series = np.random.randint(100, size=9)
​
# constructing Series object
series_object = pd.Series(data_series, index=index_series)
​
# printing Series in unordered (original) form
print(series_object)
​
# printing Series in ordered (alphabetically) form
print(series_object.sort_index())
c 1 34
2 72
3 98
a 1 40
2 40
3 74
b 1 48
2 19
3 11
dtype: int64
a 1 40
2 40
3 74
b 1 48
2 19
3 11
c 1 34
2 72
3 98
dtype: int64

4.2. Stacking and Unstacking indices

Earlier, in sub-section 1.1, we applied stack and unstuck on Pandas Series object. Let’s us apply the same methods on DataFrame (in the example below, we intentionally edit the DataFrame by removing “John” so that the DataFrame is easy to read and understand)
# reproducing multi-index DataFrame
print(df_multi)
student Tom Harry
marks HR Marketing HR Marketing
year exam
2018 1 65 69 87 84
2 76 98 53 87
2019 1 58 62 70 93
2 62 71 83 66

a. Unstack

Let unstack the results, which by default applies to level=-1, i.e, the last index in the multi-index series.
# unstack
df_multi_unstack = df_multi.unstack()
print(df_multi_unstack)
As we can see, the index name ‘exam’ is now unstacked and become part of another level in the column:
student Tom Harry
marks HR Marketing HR Marketing
exam 1 2 1 2 1 2 1 2
year
2018 65 76 69 98 87 53 84 87
2019 58 62 62 71 70 83 93 66
Let’s unstack with level=0 which will unstack the index, name year into another level in the column
# unstack with 'level=0'
print(df_multi.unstack(level=0))
student Tom Harry
marks HR Marketing HR Marketing
year 2018 2019 2018 2019 2018 2019 2018 2019
exam
1 65 58 69 62 87 70 84 93
2 76 62 98 71 53 83 87 66

b. Stack

Let’s stack one of the DataFrame columns into index. By default, it applies to the last level in the column, which is exam in our example:
df_multi_unstack.stack()
student Harry Tom
marks HR Marketing HR Marketing
year exam
2018 1 87 84 65 69
2 53 87 76 98
2019 1 70 93 58 62
2 83 66 62 71
Let suppose, we would like to stack the student column instead of exam To do that we can provide level=0 because student is the at position of 0
print(df_multi_unstack.stack(level=0))
marks HR Marketing
exam 1 2 1 2
year student
2018 Harry 87 53 84 87
Tom 65 76 69 98
2019 Harry 70 83 93 66
Tom 58 62 62 71

4.3. Index Resetting and Setting

a. Index Reset

Index to column: We can use reset_index method to turn the index labels into columns. We can also fine control the result using various parameters of this method :
# reproducing multi-index series, pop
pop
city year
City B 2018 18976457
2019 19378102
City A 2018 33871648
2019 37253956
City C 2018 20851820
2019 25145561
dtype: int64
Let’s apply reset_index() which will turn the old indices into columns and new integer based sequential index is used:
print(pop.reset_index())
city year 0
0 City B 2018 18976457
1 City B 2019 19378102
2 City A 2018 33871648
3 City A 2019 37253956
4 City C 2018 20851820
5 City C 2019 25145561
Last column has no name, so let’s give it a name to make the results both presentable and meaningful:
# let give name to the column
pop_resetindex = pop.reset_index(name='population')
print(pop_resetindex)
city year population
0 City B 2018 18976457
1 City B 2019 19378102
2 City A 2018 33871648
3 City A 2019 37253956
4 City C 2018 20851820
5 City C 2019 25145561
If we don’t want to reset all indices to columns, we can use the argument level= to fine tune our results

b. Set Index

Column-to-index: We can use set_index() method to build a multi-index Series or DataFrame by providing the list of column labels that we would like to convert into indices:
print(pop_resetindex.set_index(['city','year']))
population
city year
City B 2018 18976457
2019 19378102
City A 2018 33871648
2019 37253956
City C 2018 20851820
2019 25145561

5. DATA AGGREGATIONS ON MULTI-INDICES

In this section, we will perform sum(), mean(), max() kind of aggregation on multi-index DataFrame
# reproducing multi-index dataframe
print(df_multi)
student Tom Harry John
marks HR Marketing HR Marketing HR Marketing
year exam
2018 1 58 85 54 91 73 69
2 70 98 64 70 79 96
2019 1 67 79 87 79 81 70
2 81 72 90 88 80 66

a. Along rows

Let suppose we would like to find the mean scores in each year, for each subject and each student. To accomplish this, we will use the keyword argument level=year
df_mean = df_multi.mean(level='year')
print(df_mean)
student Tom Harry John
marks HR Marketing HR Marketing HR Marketing
year
2018 64.0 91.5 59.0 80.5 76.0 82.5
2019 74.0 75.5 88.5 83.5 80.5 68.0

b. Along column and rows

Let suppose we would like to find the mean scores each year for each subject, for all subjects and exams. To accomplish this, we will use two keyword arguments level='marks and axis=1 (to tell Pandas to look for level under column)
print(df_mean.mean(axis=1, level='marks'))
marks HR Marketing
year
2018 66.333333 84.833333
2019 81.000000 75.666667
Previous
Handling Missing Data In Pandas
Next
Combining Datasets In Pandas
Last modified 4mo ago