How to calculate moving average for each subsets of rows in pandas dataframe?

Question

My dataframe looks something like this:

Region  ...  Value
  'A'   ...    1
  'A'   ...    2
  'A'   ...    3
  'A'   ...    4
  'B'   ...    1
  'B'   ...    5
  'B'   ...    4

and what I need to do is to add a new column, 'MA', containing the moving average calculated considering only rows relative to the same 'Region':

Region  ...  Value  MA
  'A'   ...    1    NaN
  'A'   ...    2    1.5
  'A'   ...    3    2.5
  'A'   ...    4    3.5
  'B'   ...    1    NaN
  'B'   ...    5     3
  'B'   ...    4    4.5

I tried a loop like this, given that i have a list names containing all the possible regions' names:

for i in name:
    dataframe['MA'] = dataframe.loc[dataframe['Region'] == str(i), 'Value'].rolling(window=2).mean()

But I get a column of only NaN.

score 0 · Accepted Answer · answered Apr 02 '21 at 02:26

Try with rolling + groupby

df['new'] = df.groupby('Region').rolling(2).mean().reset_index(level=0,drop=True)
df
  Region  ...  Value  new
0    'A'  ...      1  NaN
1    'A'  ...      2  1.5
2    'A'  ...      3  2.5
3    'A'  ...      4  3.5
4    'B'  ...      1  NaN
5    'B'  ...      5  3.0
6    'B'  ...      4  4.5

How to calculate moving average for each subsets of rows in pandas dataframe?

1 Answers1