Groupby/reshape The Data In Pandas

Im looking for a way to transform my data into a different shape. Right now Data.head() looks as follows: Time A B C

Solution 1:

I think you can use pivot_table with dt.date, dt.time or alternatively dt.strftime:

df['time'] = df['Time'].dt.time
df['date'] = df['Time'].dt.date

print pd.pivot_table(df, index='date', columns='time')
                   A                             B                      \
time        11:00:00  12:00:00  13:00:00  11:00:00  12:00:00  13:00:00   
date                                                                     
2009-02-20 -0.003776 -0.004145 -0.007896 -0.001606  0.007597  0.017419   
2009-02-23 -0.015349 -0.002748 -0.007760  0.010237  0.004150  0.011192   

                   C                      
time        11:00:00  12:00:00  13:00:00  
date                                      
2009-02-20 -0.000150 -0.000054 -0.000241  
2009-02-23 -0.000328 -0.000070 -0.000270  

You can remove Multiindex from columns:

df['time'] = df['Time'].dt.strftime('%H:%M')
df['date'] = df['Time'].dt.date

df = pd.pivot_table(df, index='date', columns='time')
df.columns = ['_'.join(col) for col in df.columns]
printdf
             A_11:00   A_12:00   A_13:00   B_11:00   B_12:00   B_13:00  \
date                                                                     
2009-02-20 -0.003776 -0.004145 -0.007896 -0.001606  0.007597  0.017419   
2009-02-23 -0.015349 -0.002748 -0.007760  0.010237  0.004150  0.011192   

             C_11:00   C_12:00   C_13:00  
date                                      
2009-02-20 -0.000150 -0.000054 -0.000241  
2009-02-23 -0.000328 -0.000070 -0.000270