rolling average per categorie in one dataframe with python

Question

I have the following dataframe:

YYYYMM	avg(monthly)	categorie
202001	0.6666666666666666	cat1
202002	0.75	cat1
202003	1.0	cat1
202004	1.0	cat1
202005	1.0	cat1
202006	1.0	cat1
202007	1.0	cat1
202008	1.0	cat1
202009	0.3333333333333333	cat1
202010	0.375	cat1
202011	0.4	cat1
202012	0.8	cat1
202101	0.8333333333333334	cat1
202102	1.0	cat1
202103	0.8571428571428571	cat1
202104	0.5714285714285714	cat1
202105	1.0	cat1
202106	0.8333333333333334	cat1
202107	0.6666666666666666	cat1
202001	0.5294117647058824	cat2
202002	0.6666666666666666	cat2
202003	0.6842105263157895	cat2
202004	0.4	cat2
202005	0.7916666666666666	cat2
202006	0.48	cat2
202007	0.5789473684210527	cat2
202008	0.4117647058823529	cat2
202009	0.4666666666666667	cat2
202010	0.5454545454545454	cat2
202011	0.4583333333333333	cat2
202012	0.7241379310344828	cat2
202101	0.6111111111111112	cat2
202102	0.5135135135135135	cat2
202103	0.56	cat2
202104	0.35	cat2
202105	0.5333333333333333	cat2
202106	0.625	cat2
202107	0.0	cat2

...

with many more categories

I now want to calculate a 12 month rolling average per categorie. The problem ive had with the pd.rolling function is that it takes data from cat1 when calculating the rolling average in cat2. And data from cat 2 in the calculation for cat 3 and so on.

Kind regards,

I have 600 categories, and this could increase. So i'm looking for a dynamic solution. — wokter, Jul 23 '21 at 07:57

score 3 · Accepted Answer · answered Jul 23 '21 at 07:57

To calculate rolling average per categorie, you have to first group the dataframe on categorie

df['roll_avg'] = df.groupby('categorie')['avg(monthly)'].rolling(12).mean().droplevel(0)

    YYYYMM  avg(monthly) categorie  roll_avg
0   202001      0.666667      cat1       NaN
1   202002      0.750000      cat1       NaN
2   202003      1.000000      cat1       NaN
3   202004      1.000000      cat1       NaN
4   202005      1.000000      cat1       NaN
5   202006      1.000000      cat1       NaN
6   202007      1.000000      cat1       NaN
7   202008      1.000000      cat1       NaN
8   202009      0.333333      cat1       NaN
9   202010      0.375000      cat1       NaN
10  202011      0.400000      cat1       NaN
11  202012      0.800000      cat1  0.777083
12  202101      0.833333      cat1  0.790972
13  202102      1.000000      cat1  0.811806
14  202103      0.857143      cat1  0.799901
15  202104      0.571429      cat1  0.764187
16  202105      1.000000      cat1  0.764187
17  202106      0.833333      cat1  0.750298
18  202107      0.666667      cat1  0.722520
19  202001      0.529412      cat2       NaN
20  202002      0.666667      cat2       NaN
21  202003      0.684211      cat2       NaN
22  202004      0.400000      cat2       NaN
23  202005      0.791667      cat2       NaN
24  202006      0.480000      cat2       NaN
25  202007      0.578947      cat2       NaN
26  202008      0.411765      cat2       NaN
27  202009      0.466667      cat2       NaN
28  202010      0.545455      cat2       NaN
29  202011      0.458333      cat2       NaN
30  202012      0.724138      cat2  0.561438
31  202101      0.611111      cat2  0.568247
32  202102      0.513514      cat2  0.555484
33  202103      0.560000      cat2  0.545133
34  202104      0.350000      cat2  0.540966
35  202105      0.533333      cat2  0.519439
36  202106      0.625000      cat2  0.531522
37  202107      0.000000      cat2  0.483276

rolling average per categorie in one dataframe with python

1 Answers1