I have a pandas dataframe with a minute datetime index:
| Index | Col1 |
|---|---|
| 2022-12-25 09:01:00 | 5 |
| 2022-12-25 09:10:00 | 15 |
| 2022-12-25 11:12:00 | 10 |
| 2022-12-26 10:05:00 | 2 |
| 2022-12-26 12:29:00 | 2 |
| 2022-12-26 13:56:00 | 5 |
I want to remove the daily average from this data, resulting in this dataframe (here 10 for the first day and 3 for the second day):
| Index | Col1 |
|---|---|
| 2022-12-25 09:01:00 | -5 |
| 2022-12-25 09:10:00 | 5 |
| 2022-12-25 11:12:00 | 0 |
| 2022-12-26 10:05:00 | -1 |
| 2022-12-26 12:29:00 | -1 |
| 2022-12-26 13:56:00 | 2 |
>Solution :
Assuming df is your dataframe, this should do the trick:
for day, df_group in df.groupby(by=df.index.day):
df.loc[df_group.index,"Col1"] -= df_group["Col1"].mean()