I suggest creating an extra column week with dt.isocalendar().week, and then creating a pivot table with weeks as columns. The only issue is how to handle the timestamps, as I can imagine you'd like to keep them as index. A solution would be to split the timestamps by dt.dayofweek and dt.hour and to use these as indexes:
import pandas as pd
data = [ { "timestamp": "2021-01-04 00:00:00", "value": 32896.22 }, { "timestamp": "2021-01-04 01:00:00", "value": 33229.4 }, { "timestamp": "2021-01-04 02:00:00", "value": 33508.78 }, { "timestamp": "2021-01-04 03:00:00", "value": 33611.6 }, { "timestamp": "2021-01-04 04:00:00", "value": 33345.25 }, { "timestamp": "2021-04-23 19:00:00", "value": 50963.6 }, { "timestamp": "2021-04-23 20:00:00", "value": 50563 }, { "timestamp": "2021-04-23 21:00:00", "value": 50361.37 }, { "timestamp": "2021-04-23 22:00:00", "value": 50524.46 }, { "timestamp": "2021-04-23 23:00:00", "value": 51187.27 } ]
df= pd.DataFrame(data)
df['timestamp'] = pd.to_datetime(df['timestamp'])
df['week'] = df['timestamp'].dt.isocalendar().week
df["day_of_week"] = df["timestamp"].dt.dayofweek
df["hour"] = df["timestamp"].dt.hour
final_df = pd.pivot_table(df, values='value', index=['day_of_week', 'hour'], columns=['week'])
This will result in:
|
1 |
16 |
| (0, 0) |
32896.2 |
nan |
| (0, 1) |
33229.4 |
nan |
| (0, 2) |
33508.8 |
nan |
| (0, 3) |
33611.6 |
nan |
| (0, 4) |
33345.2 |
nan |
| (4, 19) |
nan |
50963.6 |
| (4, 20) |
nan |
50563 |
| (4, 21) |
nan |
50361.4 |
| (4, 22) |
nan |
50524.5 |
| (4, 23) |
nan |
51187.3 |
