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
>Solution :
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