Working with MultiIndex in pandas DataFrame

MiltiIndex is also referred to as Hierarchical/multi-level index/advanced indexing in pandas enables us to create an index on multiple columns and store data in an arbitrary number of dimensions. MultiIndex gives us a way to see and process data that we have never seen before and opens the door to sophisticated data analysis and manipulation with Series and DataFrame.

In this article, I will explain working on MultiIndex pandas DataFrame with several examples like creating Multi index DataFrame, converting Multi index to columns, dropping level from multi-index e.t.c

pandas MultiIndex Key Points –

• MultiIndex is an array of tuples where each tuple is unique.
• You can create MultiIndex from list of arrays, arry of tuples, dataframe e.t.c
• The Index constructor will attempt to return a MultiIndex when it is passed a list of tuples.
• You can have Multi-level for both Index and Column labels.
• Multi-level columns are used when you wanted to group columns together.

1. Create MultiIndex pandas DataFrame (Multi level Index)

A multi-level index DataFrame is a type of DataFrame that contains multiple level or hierarchical indexing. You can create a MultiIndex (multi-level index) in the following ways.

• From a list of arrays using  MultiIndex.from_arrays()
• From an array of tuples using MultiIndex.from_tuples()
• From a crossed set of iterables using MultiIndex.from_product()
• From DataFrame using MultiIndex.from_frame()

The following example demonstrates steps to create MultiIndexes DataFrame for both index and columns using pandas.MultiIndex.from_tuples().

Step 1: Create MultiIndex for Index

import pandas as pd
multi_index = pd.MultiIndex.from_tuples([("r0", "rA"),
                                       ("r1", "rB")],
                                       names=['Courses','Fee'])

Step 2: Create Create MultiIndex for Column

cols = pd.MultiIndex.from_tuples([("Gasoline", "Toyoto"), 
                                  ("Gasoline", "Ford"), 
                                  ("Electric", "Tesla"),
                                  ("Electric", "Nio")])

Step 3: Create DataFrame

data=[[100,300, 900,400 ], [200,500, 300,600]]

df = pd.DataFrame(data, columns=cols,index=multi_index)
print(df)

Yields below DataFrame with Multilevel index for rows and columns.

2. pandas MultiIndex to Columns

Use pandas DataFrame.reset_index() function to convert/transfer MultiIndex (multi-level index) indexes to columns. The default setting for the parameter is drop=False which will keep the index values as columns and set the new index to DataFrame starting from zero.

# Convert Multi-index to Columns
df2=df.reset_index()
print(df2)

Yields below output.

  indx1 indx2 Gasoline      Electric     
                Toyoto Ford    Tesla  Nio
0    r0    rA      100  300      900  400
1    r1    rB      200  500      300  600

If you have column names the same as Index, you will get an error. You can get over this by changing the multi-index names first.

df.index = df.index.set_names(['new_index1', 'new_index2'])

3. MultiIndex to Single Index

Sometimes you may require to convert MultiIndex (multi-level) to a single Index. You can do this either by keeping one Index and converting the rest to columns or dropping them.

# MultiIndex to Single Index
df2 = df.reset_index(level=[1])
print(df2)

Yields below output. This example keeps one Index indx1 and transforms the indx2 to column. If you have many indexes, you can pass them as a list to level param.

      indx2 Gasoline      Electric     
              Toyoto Ford    Tesla  Nio
indx1                                  
r0       rA      100  300      900  400
r1       rB      200  500      300  600

You can also drop the Index.

# MultiIndex to Single Index by dropping
df2 = df.reset_index(level=[1], drop=True)
print(df2)

4. pandas Flatten MultiIndex Columns

If you noticed, our pandas DataFrame contains MultiIndex columns, you can flatten this to a single level by accessing the level and assigning it to columns.

# Flattern MultiIndex columns
df.columns = df.columns.get_level_values(1)
print(df)

Yields below output.

             Toyoto  Ford  Tesla  Nio
indx1 indx2                          
r0    rA        100   300    900  400
r1    rB        200   500    300  600

5. Drop Multilevel Index

You can drop levels from multi-level row or column index using DataFrame.columns.droplevel() and MultiIndex.droplevel() methods.

Using MultiIndex.droplevel() you can drop single or more levels from multi-level rows/column index. Use axis=1 param to drop columns. To drop row-level use axis=0. The below example drops first index from DataFrame.

#Drop Index from MultiIndex
df=df.droplevel(0, axis=0) 
print(df)

Yields below output.

       Toyoto  Ford  Tesla  Nio
indx2                          
rA        100   300    900  400
rB        200   500    300  600

6. Complete Example of pandas MultiIndex

import pandas as pd
# Create Row Level MultiIndex 
new_index = pd.MultiIndex.from_tuples([("r0", "rA"),
                                       ("r1", "rB")],
                                       names=['indx1','indx2'])

# Create Column Level MultiIndex 
cols = pd.MultiIndex.from_tuples([("Gasoline", "Toyoto"), 
                                  ("Gasoline", "Ford"), 
                                  ("Electric", "Tesla"),
                                  ("Electric", "Nio")])

# Create MultiIndex DataFrame
data=[[100,300, 900,400 ], [200,500, 300,600]]
df = pd.DataFrame(data, columns=cols,index=new_index)
print(df)

# Convert MultiIndex to Columns
df2=df.reset_index()
print(df2)

# Convert MuliIndex to Single index
df2 = df.reset_index(level=[1])
print(df2)

# Drop Index
df2 = df.reset_index(level=[1], drop=True)
print(df2)

# Flattern MultiIndex columns
df.columns = df.columns.get_level_values(1)
print(df)

#Drop Index from MultiIndex
df=df.droplevel(0, axis=0) 
print(df)

Conclusion

In this article, you have learned what is pandas MultiIndex, how to create it, converting muli index to columns, flatten MultiIndex columns, drop the index, and transform to a Single index with examples.

Happy Learning !!

Related Articles

• Drop Rows From Pandas DataFrame Examples
• Drop Single & Multiple Columns From Pandas DataFrame
• Change the Order of Pandas DataFrame Columns
• Pandas groupby() and sum() With Examples
• Difference Between loc and iloc in Pandas DataFrame
• How to Plot Columns of Pandas DataFrame
• Pandas Window Functions Explained
• Pandas Handle Missing Data in Dataframe

References

• https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.droplevel.html?