I want my dataframe to be grouped by calendar weekly, like Monday to Sunday.
timestamp value
# before time
...
# this is a Friday
2021-10-01 13:00:00 2204.0
2021-10-01 13:30:00 3262.0
...
# this is next Monday
2021-10-04 16:00:00 254.0
2021-10-04 16:30:00 990.0
2021-10-04 17:00:00 1044.0
2021-10-04 17:30:00 26.0
...
# time continues
The result I'm expecting, hope this is clear enough.
timestamp value weekly_max
# this is a Friday
2021-10-01 13:00:00 2204.0 3262.0 # assume 3262.0 is the maximum value during 2021-09-27 to 2021-10-03
2021-10-01 13:30:00 3262.0 3262.0
...
# this is next Monday
2021-10-04 16:00:00 254.0 1044.0
2021-10-04 16:30:00 990.0 1044.0
2021-10-04 17:00:00 1044.0 1044.0
2021-10-04 17:30:00 26.0 1044.0
...
CodePudding user response：
get week number:
df['week'] = df.datetime.dt.isocalendar().week
get max for each week
df_weeklymax = df.groupby('week').agg(max=('value', 'max')).reset_index()
merge 2 tables
df.merge(df_weeklymax, on='week', how='left')
example output:
| datetime | value | week | max | |
|---|---|---|---|---|
| 0 | 2021-01-01 00:00:00 | 20 | 53 | 69 |
| 1 | 2021-01-01 13:36:00 | 69 | 53 | 69 |
| 2 | 2021-01-02 03:12:00 | 69 | 53 | 69 |
| 3 | 2021-01-02 16:48:00 | 57 | 53 | 69 |
| 4 | 2021-01-03 06:24:00 | 39 | 53 | 69 |
| 5 | 2021-01-03 20:00:00 | 56 | 53 | 69 |
| 6 | 2021-01-04 09:36:00 | 73 | 1 | 92 |
| 7 | 2021-01-04 23:12:00 | 76 | 1 | 92 |
| 8 | 2021-01-05 12:48:00 | 92 | 1 | 92 |
| 9 | 2021-01-06 02:24:00 | 4 | 1 | 92 |