Is there any way to use Groupby and Rollong together?

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

Answers (0)

Related Questions