Reputation: 11
I have the following dataframe with daily data:
day value
2017-08-04 0.832
2017-08-05 0.892
2017-08-06 0.847
2017-08-07 0.808
2017-08-08 0.922
2017-08-09 0.894
2017-08-10 2.332
2017-08-11 0.886
2017-08-12 0.973
2017-08-13 0.980
... ...
2022-03-21 0.821
2022-03-22 1.121
2022-03-23 1.064
2022-03-24 1.058
2022-03-25 0.891
2022-03-26 1.010
2022-03-27 1.023
2022-03-28 1.393
2022-03-29 2.013
2022-03-30 3.872
[1700 rows x 1 columns]
I need to generate pooled averages using moving windows. I explain it group by group:
The first group must contain the data from 2017-08-04 to 2017-08-08, but also the data from 2018-08-04 to 2018-08-08, and so on until the last year. As shown below:
2017-08-04 0.832
2017-08-05 0.892
2017-08-06 0.847
2017-08-07 0.808
2017-08-08 0.922
---------- -----
2018-08-04 2.125
2018-08-05 2.200
2018-08-06 2.339
2018-08-07 2.035
2018-08-08 1.953
... ...
2020-08-04 0.965
2020-08-05 0.941
2020-08-06 0.917
2020-08-07 0.922
2020-08-08 0.909
---------- -----
2021-08-04 1.348
2021-08-05 1.302
2021-08-06 1.272
2021-08-07 1.258
2021-08-08 1.281
The second group must run one day the temporary window. That is, data from 2017-08-05 to 2017-08-09, from 2018-08-05 to 2018-08-09, and so on until the last year. As shown below:
2017-08-05 0.892
2017-08-06 0.847
2017-08-07 0.808
2017-08-08 0.922
2017-08-09 1.823
---------- -----
2018-08-05 2.200
2018-08-06 2.339
2018-08-07 2.035
2018-08-08 1.953
2018-08-09 2.009
... ...
2020-08-05 0.941
2020-08-06 0.917
2020-08-07 0.922
2020-08-08 0.909
2020-08-09 1.934
---------- -----
2021-08-05 1.302
2021-08-06 1.272
2021-08-07 1.258
2021-08-08 1.281
2021-08-09 2.348
And the following groups must follow the same dynamic. Finally, I need to form a DataFrame, where the indices are the central date of each window (the length of the DataFrame will be 365 days of the year) and the values are the average of each of the groups mentioned above.
I have been trying Groupby and Rolling at the same time. But any solution based on other pandas methods is completely valid.
Upvotes: 1
Views: 30