Converting from long to wide in python

Question

I think this a pretty simple question. I am new to python and I am unable to find the perfect answer.

I have a dataframe :

A          B       C       D       E
203704     WkDay   00:00   0.247   2015
203704     WkDay   00:30   0.232   2015
203704     Wkend   00:00   0.102   2015
203704     Wkend   00:30   0.0907  2015
203704     WkDay   00:00   0.28    2016
203704     WkDay   00:30   0.267   2016
203704     Wkend   00:00   0.263   2016
203704     Wkend   00:30   0.252   2016

I need :

A       B      00:00   00:30    E
203704  Wkday  0.247   0.232   2015
203704  Wkend  0.102   0.0907  2015
203704  Wkday  0.28    0.267   2016
203704  Wkday  0.263   0.252   2016

I have gone through various links like this and this. However, implementing them I am getting various errors.

I was able to run this successfully

pandas.pivot_table(df,values='D',index='A',columns='C')

but it does not give what exactly I want.

Any help on this would be helpful.

Answer 1

You can add multiple columns to list as argument of parameter index:

print (pd.pivot_table(df,index=['A', 'B', 'E'], columns='C',values='D').reset_index())
C       A      B     E  00:00   00:30
0  203704  WkDay  2015  0.247  0.2320
1  203704  WkDay  2016  0.280  0.2670
2  203704  Wkend  2015  0.102  0.0907
3  203704  Wkend  2016  0.263  0.2520

If need change order of columns:

#reset only last level of index
df1 = pd.pivot_table(df,index=['A', 'B', 'E'], columns='C',values='D').reset_index(level=-1)
#reorder first column to last
df1.columns = df1.columns[-1:] | df1.columns[:-1]
#reset other columns
print (df1.reset_index())
C       A      B  00:00  00:30       E
0  203704  WkDay   2015  0.247  0.2320
1  203704  WkDay   2016  0.280  0.2670
2  203704  Wkend   2015  0.102  0.0907
3  203704  Wkend   2016  0.263  0.2520