How To Transform A Time Series Pandas Dataframe Using The Index Attributes?

Given a dataframe with time series that looks like this: Close 2015-02-20 14:00:00 1200.1 2015-02-20 14:10:00 1199.8 2015-02-21 14:00:00 1199.3 2015-02-21

Solution 1:

you can pivot on the date and time components of the index:

Create the frame:

i =pd.to_datetime(['2015-02-20 14:00:00','2015-02-20 14:10:00','2015-02-21 14:20:00'\
               ,'2015-02-21 14:30:00','2015-02-22 14:40:00','2015-02-22 14:50:00'])
df =pd.DataFrame(index=i, data={'Close':[1200.1,1199.8,1199.3,1199.0,1198.4,1199.7]})

pivot:

pd.pivot_table(df, index= df.index.date, columns=df.index.time, values = 'Close')

returns:

14:00:0014:10:0014:20:0014:30:0014:40:0014:50:002015-02-20  1200.1  1199.8      NaNNaNNaNNaN2015-02-21  NaNNaN1199.3      1199        NaNNaN2015-02-22  NaNNaNNaNNaN1198.4     1199.7

use aggfunc as an argument of pivot_table to determine how data is aggregated if necessary