I have the following df: [date is the index]
print(df)
country values
date
2022-06-01 ES 2
2022-07-01 ES 2
2022-08-01 ES 3
2022-09-01 ES 3
2022-10-01 ES 5
2022-11-01 ES 5
2022-12-01 ES 6
2023-01-01 ES 6
2023-02-01 ES NaN
2022-06-01 IT 1
2022-07-01 IT 1
2022-08-01 IT 2
2022-09-01 IT 2
2022-10-01 IT 3
2022-11-01 IT 3
2022-12-01 IT 4
2023-01-01 IT 4
2023-02-01 IT NaN
I need to generate a new column called 'moving_average' that is the average of the last 6 month by country (considering current month). The values that don't have 6 months information should be NaN. And this new value should replace the NaN of the corresponding month. This should be the output:
country values moving_average
date
2022-06-01 ES 2 NaN
2022-07-01 ES 2 NaN
2022-08-01 ES 3 NaN
2022-09-01 ES 3 NaN
2022-10-01 ES 5 NaN
2022-11-01 ES 5 ---> AVG(2,2,3,3,5,5) = 3.33
2022-12-01 ES 6 ---> AVG(2,3,3,5,5,6) = 4
2023-01-01 ES 6 ---> AVG(3,3,5,5,6,6) = 4.67 --> Should replace 2023-02-01 value (that is NaN) with this AVG
2023-02-01 ES 4.67 ---> AVG(3,5,5,6,6,4.67) = 4.945
2022-06-01 IT 1 NaN
2022-07-01 IT 1 NaN
2022-08-01 IT 2 NaN
2022-09-01 IT 2 NaN
2022-10-01 IT 3 NaN
2022-11-01 IT 3 ---> AVG(1,1,2,2,3,3) = 2
2022-12-01 IT 4 ---> AVG(1,2,2,3,3,4) = 2.5
2023-01-01 IT 4 ---> AVG(2,2,3,3,4,4) = 3 --> Should replace 2023-02-01 value (that is NaN) with this AVG
2023-02-01 IT 3 ---> AVG(2,3,3,4,4,3) = 3.167
How we can achieve this?
