How to remove multilevel index in pandas pivot table
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
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