Reshaping Pandas DataFrame

Question

I have the following DataFrame

           A
0  2012-01-13 10:00:06
1  2012-01-13 11:09:04
2  2012-01-13 12:07:05
3  2012-01-13 13:03:04
4  2012-01-16 10:00:10
5  2012-01-16 11:09:04
6  2012-01-16 12:01:05
7  2012-01-16 13:09:04
8  2012-01-17 10:01:04
9  2012-01-17 11:05:06
10 2012-01-17 12:01:05
11 2012-01-17 13:04:04

where the index is 0,1,..etc

Is there a way to transpose data based on the day? for example the new DataFrame should look like:

          A                     B                   C                   D
0   2012-01-13 10:00    2012-01-13 11:09    2012-01-13 12:07    2012-01-13 13:03
1   2012-01-16 10:00    2012-01-16 11:09    2012-01-16 12:01    2012-01-16 13:09
2   2012-01-17 10:01    2012-01-17 11:05    2012-01-17 12:01    2012-01-17 13:04

Answer 1

I think you need create column of days by dt.day, then create groups by cumcount, use pivot with reset_index. Last assign new column names:

#if dtype of column is not datetime
df.A = pd.to_datetime(df.A)

df['day'] = df.A.dt.day
df['groups'] = df.groupby('day').cumcount()

df = df.pivot(index='day', columns='groups', values='A').reset_index(drop=True)
df.columns = list('ABCD')
print (df)
                    A                   B                   C  \
0 2012-01-13 10:00:06 2012-01-13 11:09:04 2012-01-13 12:07:05   
1 2012-01-16 10:00:10 2012-01-16 11:09:04 2012-01-16 12:01:05   
2 2012-01-17 10:01:04 2012-01-17 11:05:06 2012-01-17 12:01:05   

                    D  
0 2012-01-13 13:03:04  
1 2012-01-16 13:09:04  
2 2012-01-17 13:04:04