Skip to content
Advertisement

Pivot matrix to time-series – Python

I’ve got a dataframe with date as first column and time as the name of the other columns.

Date 13:00 14:00 15:00 16:00
2022-01-01 B R M M
2022-01-02 B B B M
2022-01-03 R B B M

How could I transform that matrix into a datetime time-series? My objective its something like this:

Date Data
2022-01-01 13:00 B
2022-01-01 14:00 R
2022-01-01 15:00 M
2022-01-01 16:00 M

I think it could be done using pivot. I would really appreciate any help you could give me. Thanks in advance!!

Advertisement

Answer

An alternative:

df = pd.DataFrame({'Date': ['2022-01-01', '2022-01-02', '2022-01-03'], '13:00': ['B', 'B', 'R'], '14:00': ['R', 'B', 'R'], '15:00': ['M', 'B', 'B'], '16:00': ['M', 'M', 'M']})
df = df.melt(id_vars='Date', var_name='Time', value_name='Data')
df['Date'] = df['Date'] + ' ' + df['Time']
df = df[['Date', 'Data']]

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement