# Part 6: 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
``````

Article Contents

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