datetime hour component to column python pandas-CodePudding

I have a dataframe as such

Date                         Value
2022-01-01 10:00:00           7
2022-01-01 10:30:00           5
2022-01-01 11:00:00           3
....
....
2022-02-15 21:00:00           8

I would like to convert it into a day by row and hour by column format. The hours are the columns in this case. and the value column is now filled as cell values.

Date             10:00  10:30  11:00 11:30............21:00
2022-01-01        7       5      3    4                11
2022-01-02        8       2      4    4                13

How can I achieve this? I have tried pivot table but no success

CodePudding user response：

Use pivot_table:

df['Date'] = pd.to_datetime(df['Date'])
out = df.pivot_table('Value', df['Date'].dt.date, df['Date'].dt.time, fill_value=0)
print(out)

# Output
Date        10:00:00  10:30:00  11:00:00  21:00:00
Date                                              
2022-01-01         7         5         3         0
2022-02-15         0         0         0         8

To remove Date labels, you can use rename_axis:

for the top Date label: out.rename_axis(columns=None)
for the bottom Date label: out.rename_axis(index=None)
for both: out.rename_axis(index=None, columns=None)

You can change None by any string to rename axis.