Pandas Pivot Creating NaN

Question

Given this sample dataframe,

         Cents      Date
MN                                         
Shop        0.03  01012019
Shop        0.22  01012019
Shop        0.12  01012019
Shop        0.08  02012019
Shop        0.02  02012019
Shop        0.02  02012019
Shop        0.02  03012019
Shop        0.09  03012019
Shop        0.11  03012019
Shop        0.02  04012019
Shop        0.03  04012019
Shop        0.04  04012019

I will like to reshape my dataframe to become

What I have tried so far,

1. Drop the index of the original sample dataframe

   df1 = df.reset_index(drop=True)
   

2. Pivot the sample dataframe,

   df1.pivot(index=None, columns='Date', values='Cents')
   

I am unable to get the desired result I want after pivoting, this is what I get

Can someone advise me on why is this happening? I am pretty sure it has something to do with how the pivot works with my dataframe (maybe I need a unique index which I can't have in this case?). Will appreciate if you can let me know how I should continue.

Thank you.

Answer 1

You may need a groupby + cumcount create an additional key

Yourdf= df.assign(key=df.groupby('Date').cumcount()).pivot(index='key',columns='Date',values='Cents')
Yourdf
Out[37]: 
Date  1012019  2012019  3012019  4012019
key                                     
0        0.03     0.08     0.02     0.02
1        0.22     0.02     0.09     0.03
2        0.12     0.02     0.11     0.04