19

I have a dataframe as given: 

df = {'TYPE' : pd.Series(['Advisory','Advisory1','Advisory2','Advisory3']),
 'CNTRY' : pd.Series(['IND','FRN','IND','FRN']),
 'VALUE' : pd.Series([1., 2., 3., 4.])}
df = pd.DataFrame(df)
df = pd.pivot_table(df,index=["CNTRY"],columns=["TYPE"]).reset_index()

After pivoting, how can I get the dataframe having columns and df to be like the below; removing the multilevel index, VALUE 

Type|CNTRY|Advisory|Advisory1|Advisory2|Advisory3
0     FRN     NaN      2.0      NaN     4.0 
1     IND     1.0      NaN      3.0     NaN
abhi
  • 1,760
  • 1
  • 24
  • 40
Shivpe_R
  • 1,022
  • 2
  • 20
  • 31

3 Answers3

40

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
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
4

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
piRSquared
  • 285,575
  • 57
  • 475
  • 624
1

df.columns = df.columns.droplevel(level =1)

Change the level depending on your requirement.

Vaibhav Kabdwal
  • 81
  • 6