Gabriel Gabriel - 1 month ago 18
Python Question

Reshaping Pandas DataFrame

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

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