close

How to remove multilevel index in pandas pivot table

Hello Guys, How are you all? Hope You all Are Fine. Today We Are Going To learn about How to remove multilevel index in pandas pivot table in Python. So Here I am Explain to you all the possible Methods here.

Without wasting your time, Let’s start This Article.

Table of Contents

How to remove multilevel index in pandas pivot table

  1. How to remove multilevel index in pandas pivot table

    You can add parameter values:
    df = pd.pivot_table(df,index="CNTRY",columns="TYPE", values='VALUE').reset_index() print (df)

  2. remove multilevel index in pandas pivot table

    You can add parameter values:
    df = pd.pivot_table(df,index="CNTRY",columns="TYPE", values='VALUE').reset_index() print (df)

Method 1

You can add parameter values:

df = pd.pivot_table(df,index="CNTRY",columns="TYPE", values='VALUE').reset_index()
print (df)
TYPE CNTRY  Advisory  Advisory1  Advisory2  Advisory3
0      FRN       NaN        2.0        NaN        4.0
1      IND       1.0        NaN        3.0        NaN

And for remove columns name rename_axis:

df = pd.pivot_table(df,index="CNTRY",columns="TYPE", values='VALUE') \
       .reset_index().rename_axis(None, axis=1)
print (df)
  CNTRY  Advisory  Advisory1  Advisory2  Advisory3
0   FRN       NaN        2.0        NaN        4.0
1   IND       1.0        NaN        3.0        NaN

But maybe is necessary only pivot:

df = df.pivot(index="CNTRY",columns="TYPE", values='VALUE') \
       .reset_index().rename_axis(None, axis=1)
print (df)
  CNTRY  Advisory  Advisory1  Advisory2  Advisory3
0   FRN       NaN        2.0        NaN        4.0
1   IND       1.0        NaN        3.0        NaN

because pivot_table aggregate duplicates by default aggregate function mean:

df = {'TYPE' : pd.Series(['Advisory','Advisory1','Advisory2','Advisory1']),
 'CNTRY' : pd.Series(['IND','FRN','IND','FRN']),
 'VALUE' : pd.Series([1., 4., 3., 4.])}
df = pd.DataFrame(df)
print (df)
  CNTRY       TYPE  VALUE
0   IND   Advisory    1.0
1   FRN  Advisory1    1.0 <-same FRN and Advisory1 
2   IND  Advisory2    3.0
3   FRN  Advisory1    4.0 <-same FRN and Advisory1 

df = df.pivot_table(index="CNTRY",columns="TYPE", values='VALUE')
       .reset_index().rename_axis(None, axis=1)
print (df)
TYPE   Advisory  Advisory1  Advisory2
CNTRY                                
FRN         0.0        2.5        0.0
IND         1.0        0.0        3.0

Alternative with groupby, aggregate function and unstack:

df = df.groupby(["CNTRY","TYPE"])['VALUE'].mean().unstack(fill_value=0)
      .reset_index().rename_axis(None, axis=1)
print (df)
  CNTRY  Advisory  Advisory1  Advisory2
0   FRN       0.0        2.5        0.0
1   IND       1.0        0.0        3.0

Method 2

You can use set_index with unstack

df.set_index(['CNTRY', 'TYPE']).VALUE.unstack().reset_index()

TYPE CNTRY  Advisory  Advisory1  Advisory2  Advisory3
0      FRN       NaN        2.0        NaN        4.0
1      IND       1.0        NaN        3.0        NaN

Summery

It’s all About this issue. Hope all Methods helped you a lot. Comment below Your thoughts and your queries. Also, Comment below which Method worked for you? Thank You.

Also, Read