I am trying to implement Exponential Moving Average calculation on a DataFrame. The formula is

An additional complication is that my table is grouped and there is a unique bin number per group. This is what I tried

import numpy as np
import numpy.random as rand

n = 5
groups = np.array(['one', 'two', 'three'])
data = pd.DataFrame({
    'price': rand.random(3 * n) * 10,
    'group': np.repeat(groups, n),
    'bin': np.tile(np.arange(n),3)}, index=np.arange(3 * n))

print(data)

price group bin 0 1.601310 one 0 1 3.190662 one 1 2 4.419421 one 2 3 3.817510 one 3 4 2.440774 one 4 5 6.832265 two 0 6 5.636502 two 1 7 4.630515 two 2 8 5.856423 two 3 9 0.916452 two 4 10 4.247134 three 0 11 7.146746 three 1 12 8.049161 three 2 13 7.852168 three 3 14 0.246720 three 4

This is how I am trying to implement the EMA calculation;

data['EMA'] = np.zeros(len(data.index))
data.loc[data['bin'] == 0, 'EMA'] = data.loc[data['bin'] == 0, 'price']

a = 2 / (n + 1)
for _, group in data.groupby('group'):
    for index, row in group.iloc[1:].iterrows():
        prev = group[group['bin'] == row['bin'] - 1].iloc[0]
        row['EMA'] = a * row['price'] + (1 - a) * prev['EMA'] # nope
        data.loc[index, 'EMA'] = a * row['price'] + (1 - a) * prev['EMA'] # nope

Unfortunately neither of these last lines update the values in the group. On the second iteration, the value of prev['EMA'] is still 0. What is the correct way to assign the values back to the table such that it is updated dynamically? Do I need to write out to a temporary array and write it back afterwards?

Additionally, I could not think of an elegant way to do this using assign or transform. If someone can solve that it might be a very good alternative.

Response

Thank you @DaFanat for your help. Unfortunately your code doesn't work. I tried the following

data.loc[:, 'EMA2'] = map(lambda x, y: x if pd.isnull(y) else x*a + (1-a) * y, 
data['price'], data.groupby('group')['price'].shift(1))

But I get an error TypeError: object of type 'map' has no len(). I tried changing it to this

data['EMA2'] = list(map(lambda x, y: x if pd.isnull(y) else x*a + (1-a) * y, 
data['price'], data.groupby('group')['price'].shift(1)))

And I do indeed get some results, but they do not look correct;

price group bin EMA EMA2 0 5.407722 one 0 5.407722 5.407722 1 0.495734 one 1 3.770393 3.770393 2 7.911491 one 2 5.150759 2.967653 3 1.085836 one 3 3.795785 5.636272 4 7.326432 one 4 4.972667 3.166035

I am inclined to believe my implementation, since the price goes up from 0.5 to 7.9, how can the moving average go down? I think the indices are getting lost and it is putting the values against the wrong cells. How do I retain the indices when performing this calculation?

Solution

Thanks @DaFanat, you took me a long way. I finally figured out how to do it with a slight modification of your original method;

data['EMA2'] = data.groupby('group') \
                   .apply(lambda x: x['price'] * a + x['EMA'].shift(1) * (1-a)) \
                   .reset_index(level=0, drop=True)
data.loc[data['bin'] == 0, 'EMA2'] = data.loc[data['bin'] == 0, 'price']

print(data)

price group bin EMA EMA2 0 3.556171 one 0 3.556171 3.556171 1 5.637241 one 1 4.249861 4.249861 2 3.278771 one 2 3.926164 3.926164 3 7.343718 one 3 5.065349 5.065349 4 6.128884 one 4 5.419861 5.419861

Not using the list(map()) ensures that the result is a DataFrame with indices retained, so it knows where to insert the individual rows.