Pandas Groupby with calculating ewm not working as expected

Question

Let's say I have a dataframe like below

import pandas as pd

data = {'team': ['team1','team1','team1','team1','team1','team1','team1','team1','team1','team1','team1','team1','team1','team1',
              'team2','team2','team2','team2','team2','team2','team2','team2','team2','team2','team2','team2','team2','team2',],
     'score': [1,2,3,4,5,6,7,8,9,10,11,12,13,14,1,2,3,4,5,6,7,8,9,10,11,12,13,14],
     'yards': [10,20,30,40,50,60,70,80,90,100,110,120,130,140,10,20,30,40,50,60,70,80,90,100,110,120,130,140]}

df = pd.DataFrame.from_dict(data)

I am trying to calculate ewm using this manual method found on this post,(Does Pandas calculate ewm wrong?), for the 'score' and 'yards' columns, but I notice my span does not work as intended for each grouped team. This is what I have for my code so far

ema_features = df[['team']].copy()

for feature_name in df[['score','yards']]:
    span=10
    feature_ema = (df.groupby('team')[feature_name].rolling(window=span, min_periods=span).mean()[:span])
    rest = df[feature_name][span:]
    x = pd.concat([feature_ema, rest]).ewm(span=span, adjust=False).mean()


    ema_features[feature_name] = x

the output of this is as follows

ema_features

    team    score   yards
0   team1   NaN NaN
1   team1   NaN NaN
2   team1   NaN NaN
3   team1   NaN NaN
4   team1   NaN NaN
5   team1   NaN NaN
6   team1   NaN NaN
7   team1   NaN NaN
8   team1   NaN NaN
9   team1   NaN NaN
10  team1   6.500000    65.000000
11  team1   7.500000    75.000000
12  team1   8.500000    85.000000
13  team1   9.500000    95.000000
14  team2   7.954545    79.545455
15  team2   6.871901    68.719008
16  team2   6.167919    61.679189
17  team2   5.773752    57.737518
18  team2   5.633070    56.330696
19  team2   5.699784    56.997843
20  team2   5.936187    59.361871
21  team2   6.311426    63.114258
22  team2   6.800257    68.002575
23  team2   7.382029    73.820289
24  team2   8.039842    80.398418
25  team2   8.759871    87.598706
26  team2   9.530803    95.308032
27  team2   10.343384   103.433844

My question is, how do I make my span apply to team 2 as well? Rather than the above output where team 2 ewm is calculated with team 1. I would like each team's ewm calculated individually from one another which requires the correct span to be applied and then calculated on, like what I am expecting below.

   ema_features

        team    score   yards
    0   team1   NaN NaN
    1   team1   NaN NaN
    2   team1   NaN NaN
    3   team1   NaN NaN
    4   team1   NaN NaN
    5   team1   NaN NaN
    6   team1   NaN NaN
    7   team1   NaN NaN
    8   team1   NaN NaN
    9   team1   NaN NaN
    10  team1   6.500000    65.000000
    11  team1   7.500000    75.000000
    12  team1   8.500000    85.000000
    13  team1   9.500000    95.000000
    14  team2   NaN NaN
    15  team2   NaN NaN
    16  team2   NaN NaN
    17  team2   NaN NaN
    18  team2   NaN NaN
    19  team2   NaN NaN
    20  team2   NaN NaN
    21  team2   NaN NaN
    22  team2   NaN NaN
    23  team2   6.500000    65.000000
    24  team2   7.500000    75.000000
    25  team2   8.500000    85.000000
    26  team2   9.500000    95.000000

It looks like you're only using the offset from the first group, so it just continues on calculating. You probably need to calculate for each group — user3483203, Sep 22 '18 at 18:22
sorry I kind of get what you are saying but not 100% as I am quite new to using Groupby and Pandas in general. Would you be able to explain a little bit more with code? Thank you in advance. — Cam, Sep 22 '18 at 18:25
I'm on my phone right now and can't put something together. I think you're using `groupby` too late. Groupby before you start the calculation and perform it on each group. — user3483203, Sep 22 '18 at 18:33
Ok no worries thank you. Before posting this, I actually tried assigning the Groupby a variable and iterating over it but I got an error saying Python cannot iterate over objects? — Cam, Sep 22 '18 at 18:35

score 3 · Accepted Answer · answered Sep 22 '18 at 18:51

You could try using GroupBy.apply with a custom function. So adapting your for loop, try something like this:

def team_ema(team, span=10):
    feature_ema = team.rolling(window=span, min_periods=span).mean()[:span]
    rest = team[span:]
    return pd.concat([feature_ema, rest]).ewm(span=span, adjust=False).mean()

df.groupby('team').apply(team_ema)

Pandas Groupby with calculating ewm not working as expected

1 Answers1