Reorder the information on pandas DataFrame having the dates and time on a row basis-CodePudding

I have a small doubt.

I have a dataframe where I have one column displaying the hourly time and the columns with the dates, is there a way to put all this together? (In this case using pandas)

actual dataframe

The desired output

The dataset

https://docs.google.com/spreadsheets/d/1BNPmSZlFHmEkGJC--iBgZiCdM81a5Dt4wj8C8J1pH3A/edit?usp=sharing

CodePudding user response：

This looks like a good use of pd.melt

import pandas as pd
df = pd.DataFrame({'August': ['00:00 - 01:00', '01:00 - 02:00', '02:00 - 03:00'], '1/ aug/': ['273,285', '2,708,725', '2,702,913'], '2/ aug/': ['310,135', '2,876,725', '28,409'], '3/ aug/': ['3,077,438', '3,076,075', '307,595'], '4/ aug/': ['2,911,175', '2,876,663', '2,869,738'], '5/ aug/': ['289,075', '2,842,425', '2,839,088']})

df = df.melt(id_vars='August', var_name='date', value_name='count').rename(columns={'August':'time'})

df = df[['date','time','count']]

print(df)

Output

       date           time      count
0   1/ aug/  00:00 - 01:00    273,285
1   1/ aug/  01:00 - 02:00  2,708,725
2   1/ aug/  02:00 - 03:00  2,702,913
3   2/ aug/  00:00 - 01:00    310,135
4   2/ aug/  01:00 - 02:00  2,876,725
5   2/ aug/  02:00 - 03:00     28,409
6   3/ aug/  00:00 - 01:00  3,077,438
7   3/ aug/  01:00 - 02:00  3,076,075
8   3/ aug/  02:00 - 03:00    307,595
9   4/ aug/  00:00 - 01:00  2,911,175
10  4/ aug/  01:00 - 02:00  2,876,663
11  4/ aug/  02:00 - 03:00  2,869,738
12  5/ aug/  00:00 - 01:00    289,075
13  5/ aug/  01:00 - 02:00  2,842,425
14  5/ aug/  02:00 - 03:00  2,839,088

CodePudding user response：

You can also achieve it with stack():

df.set_index('August').stack().reset_index().sort_values('level_1').rename(
    {'August':'time','level_1':'date',0:'count'},axis=1)

             time     Date      count
0   00:00 - 01:00  1/ aug/    273,285
5   01:00 - 02:00  1/ aug/  2,708,725
10  02:00 - 03:00  1/ aug/  2,702,913
1   00:00 - 01:00  2/ aug/    310,135
6   01:00 - 02:00  2/ aug/  2,876,725
11  02:00 - 03:00  2/ aug/     28,409
2   00:00 - 01:00  3/ aug/  3,077,438
7   01:00 - 02:00  3/ aug/  3,076,075
12  02:00 - 03:00  3/ aug/    307,595
3   00:00 - 01:00  4/ aug/  2,911,175
8   01:00 - 02:00  4/ aug/  2,876,663
13  02:00 - 03:00  4/ aug/  2,869,738
4   00:00 - 01:00  5/ aug/    289,075
9   01:00 - 02:00  5/ aug/  2,842,425
14  02:00 - 03:00  5/ aug/  2,839,088