Pandas Drop Level From Multi-Level Column Index

By using DataFrame.droplevel() or DataFrame.columns.droplevel() you can drop a level from multi-level column index from pandas DataFrame. Note that when you have multi-index columns, DataFrame.columns return pandas.Multiindex.

A multi-level index DataFrame is a type of DataFrame that contains multiple level or hierarchical indexing. Dropping a level of a multi-level column index in a pandas DataFrame removes the entire column level.

1. Quick Examples of Drop Level From Columns

Below are some most used examples of how to drop level from multi-level column index from pandas DataFrame.


# Below are quick examples

# Using droplevel() To drop level From Multi Level Column Labels
df = df.droplevel(0, axis=1) 

# Drop First Level of Column Label
df.columns = df.columns.droplevel(0)

# Alternate way
df.columns = [col[1] for col in df.columns]

# By assigning new columns
df.columns = ['Toyoto', 'Ford','Tesla','Nio']

# Drops second level
df.columns = df.columns.droplevel(1)

# Merge on level name with another level
df.columns = ['_'.join(col) for col in df.columns]

Now let’s see the syntax of these methods, execute with some test data and validate results.


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

cols = pd.MultiIndex.from_tuples([("Gasoline", "Toyoto"), 
                                  ("Gasoline", "Ford"), 
                                  ("Electric", "Tesla"),
                                  ("Electric", "Nio")])
data=[[100,300, 900,400 ], [200,500, 300,600]]

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

# Output:
#            Gasoline      Electric     
#              Toyoto Ford    Tesla  Nio
# Courses Fee                            
# r0      rA       100  300      900  400
# r1      rB       200  500      300  600

2. Pandas DataFrame.droplevel() Syntax & Examples

Below is the syntax of the DataFrame.droplevel() method.


# DataFrame.droplevel() Syntax
DataFrame.droplevel(level, axis=0)
  • level – Used with MultiIndex. Takes Integer value, str, or list-like. Default set to None.
  • axis – Value can be either 0 or index | 1 or columns. Default set to ‘0’.

3. Drop a Level From Multi-Level Column Index in Pandas

Use DataFrame.droplevel() to drop single or more levels from multi-level column labels. Multi-level columns are used when you wanted to group columns together. This droplevel() method can be used to drop levels from columns and rows. Use axis=1 param to drop columns. To drop row-level use axis=0


# Using droplevel() To drop level From Multi Level Column Labels
df = pd.DataFrame(data, columns=cols,index=new_index)
df = df.droplevel(0, axis=1) 
print(df)

Yields below output.


# Output:
             Toyoto  Ford  Tesla  Nio
Courses Fee                          
r0      rA      100   300    900  400
r1      rB      200   500    300  600

4. Drop Level Using MultiIndex.droplevel()

You can also use pandas.MultiIndex.droplevel() to drop columns level. When you have Multi-level columns DataFrame.columns return MultiIndex object and use droplevel() on this object to drop level.


# Drop First Level of Column Label
df = pd.DataFrame(data, columns=cols,index=new_index)
df.columns = df.columns.droplevel(0)
print(df)

Yields same output as above.

5. Alternate Ways to Drop Level From Multi Level Columns

Below are some other alternative ways to drop levels from Multi-Level Columns of pandas DataFrame.


# Alternate ways to drop level from
# multilevel columns
df = pd.DataFrame(data, columns=cols,index=new_index)
df.columns = [col[1] for col in df.columns]
print(df)

By assigning new columns. In order to use this, you need to know all column names from DataFrame.


# By Assigning new columns
df = pd.DataFrame(data, columns=cols,index=new_index)
df.columns = ['Toyoto', 'Ford','Tesla','Nio']
print(df)

In case if you wanted to prefix the level you are dropping to the next level, use the below approach.


# Drop First Level and add level label as prefix
df = pd.DataFrame(data, columns=cols,index=new_index)
df.columns = ['_'.join(col) for col in df.columns]
print(df)

Yields below output


# Output:
             Gasoline_Toyoto  Gasoline_Ford  Electric_Tesla  Electric_Nio
Courses Fee                                                              
r0      rA               100            300             900           400
r1      rB               200            500             300           600

5. Drop Two Levels Using Multi-Level Column Index

If you have multiple levels (3 or more), you may need to drop multiple levels, you can do this by chaining droplevel().


# Drop two levels from multi-level column index
df = pd.DataFrame(data, columns=cols,index=new_index)
df.columns = df.columns.droplevel(0)
df.columns = df.columns.droplevel(1)
print(df)

6. Drop level from multi-index Row

If you have multiple indexes, you can use the below examples to drop one index.


# Drop Level From Index
df = pd.DataFrame(data, columns=cols,index=new_index)
df.index = df.index.droplevel(1) 
print(df)

# Using droplevel() For Row index
df = pd.DataFrame(data, columns=cols,index=new_index)
df=df.droplevel(0, axis=0) 
print(df)

Yields below output


# Output:
    Gasoline      Electric     
      Toyoto Ford    Tesla  Nio
Fee                            
rA       100  300      900  400
rB       200  500      300  600

7. Complete Example For Drop Multi-Level Column Index


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

cols = pd.MultiIndex.from_tuples([("Gasoline", "Toyoto"), 
                                  ("Gasoline", "Ford"), 
                                  ("Electric", "Tesla"),
                                  ("Electric", "Nio")])
data=[[100,300, 900,400 ], [200,500, 300,600]]

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

# Using droplevel() To drop level From Multi Level Column Labels
df = pd.DataFrame(data, columns=cols,index=new_index)
df=df.droplevel(0, axis=1) 
print(df)

# Drop First Level of Column Label
df = pd.DataFrame(data, columns=cols,index=new_index)
df.columns = df.columns.droplevel(0)
print(df)

# Alternate ways
df = pd.DataFrame(data, columns=cols,index=new_index)
df.columns = [col[1] for col in df.columns]
print(df)

# Another Alternate way
df = pd.DataFrame(data, columns=cols,index=new_index)
df.columns = ['Toyoto', 'Ford','Tesla','Nio']
print(df)

# Drop second Level
df = pd.DataFrame(data, columns=cols,index=new_index)
df.columns = df.columns.droplevel(1)
print(df)

# Drop First Level and add level label as prefix
df = pd.DataFrame(data, columns=cols,index=new_index)
df.columns = ['_'.join(col) for col in df.columns]
print(df)


# Drop Level From Index
df = pd.DataFrame(data, columns=cols,index=new_index)
df.index = df.index.droplevel(1) 
print(df)

# Using droplevel() For Row index
df = pd.DataFrame(data, columns=cols,index=new_index)
df=df.droplevel(0, axis=0) 
print(df)

Conclusion

In this article, you have learned how to drop level from multi-level column index in pandas DataFrame using DataFrame.columns.droplevel() and pandas.MultiIndex.droplevel() method.

Happy Learning !!

References

Naveen (NNK)

Naveen (NNK) is a Data Engineer with 20+ years of experience in transforming data into actionable insights. Over the years, He has honed his expertise in designing, implementing, and maintaining data pipelines with frameworks like Apache Spark, PySpark, Pandas, R, Hive and Machine Learning. Naveen journey in the field of data engineering has been a continuous learning, innovation, and a strong commitment to data integrity. In this blog, he shares his experiences with the data as he come across. Follow Naveen @ @ LinkedIn

Leave a Reply

You are currently viewing Pandas Drop Level From Multi-Level Column Index