pandas groupby rolling behaviour

user_4730164 0
Viewed 62

Here my pandas:

df = pd.DataFrame({
'location': ['USA','USA','USA','USA', 'France','France','France','France'],
'date':['2020-11-20','2020-11-21','2020-11-22','2020-11-23', '2020-11-20','2020-11-21','2020-11-22','2020-11-23'],
'dm':[5.,4.,2.,2.,17.,3.,3.,7.]
})

For a precise location (so groupby is needed) I want the mean of dm over 2 days. If I use this :

df['rolling']=df.groupby('location').dm.rolling(2).mean().values

I obtain this incorrect pandas

    location    date    dm  rolling
0   USA     2020-11-20  5.0     NaN
1   USA     2020-11-21  4.0     10.0
2   USA     2020-11-22  2.0     3.0
3   USA     2020-11-23  2.0     5.0
4   France  2020-11-20  17.0    NaN
5   France  2020-11-21  3.0     4.5
6   France  2020-11-22  3.0     3.0
7   France  2020-11-23  7.0     2.0

While it should be:

    location    date    dm  rolling
0   USA     2020-11-20  5.0     NaN
1   USA     2020-11-21  4.0     4.5
2   USA     2020-11-22  2.0     3.0
3   USA     2020-11-23  2.0     2.0
4   France  2020-11-20  17.0    NaN
5   France  2020-11-21  3.0     10
6   France  2020-11-22  3.0     3.0
7   France  2020-11-23  7.0     5.0

Two questions:

  • what my syntax is actually doing ?
  • what is the correct way to proceed ?
pandas-groupby pandas python
1 Answers

Here is problem groupby create new level of MultiIndex, so for matching original index values is necessary remove it by Series.reset_index with drop=True, if use .value then is no alignemnt by index, so order should be different like here:

df['rolling']=df.groupby('location').dm.rolling(2).mean().reset_index(level=0, drop=True) 
print (df)
  location        date    dm  rolling
0      USA  2020-11-20   5.0      NaN
1      USA  2020-11-21   4.0      4.5
2      USA  2020-11-22   2.0      3.0
3      USA  2020-11-23   2.0      2.0
4   France  2020-11-20  17.0      NaN
5   France  2020-11-21   3.0     10.0
6   France  2020-11-22   3.0      3.0
7   France  2020-11-23   7.0      5.0

Details:

print (df.groupby('location').dm.rolling(2).mean())
location   
France    4     NaN
          5    10.0
          6     3.0
          7     5.0
USA       0     NaN
          1     4.5
          2     3.0
          3     2.0
Name: dm, dtype: float64

print (df.groupby('location').dm.rolling(2).mean().reset_index(level=0, drop=True))
4     NaN
5    10.0
6     3.0
7     5.0
0     NaN
1     4.5
2     3.0
3     2.0
Name: dm, dtype: float64
user_29010020
Related