CODEWITHSUNDEEP
pythonpandasmoving-average
Craig
Craig

Reputation: 1985

Pandas - moving averages - use values of previous X entries for current row

So my dataset looks like this:

date,site,iso,id,hits
2017-08-25,google,1,7012,14225.0
2017-08-26,google,1,7012,14565.0
2017-08-27,google,1,7012,14580.0
2017-08-28,google,1,7012,14227.0
2017-08-29,google,1,7012,14568.0
2017-08-30,google,1,7012,14582.0
2017-08-31,google,1,7012,14214.0
2017-09-01,google,1,7012,14053.0
2017-08-25,facebook,2,7019,21225.0
2017-08-26,facebook,2,7019,21565.0
2017-08-27,facebook,2,7019,31580.0
2017-08-28,facebook,2,7019,13227.0
2017-08-29,facebook,2,7019,22568.0
2017-08-30,facebook,2,7019,44582.0
2017-08-31,facebook,2,7019,32214.0
2017-09-01,facebook,2,7019,44053.0

I need to find the 3 day moving average of hits using the previous 3 days of data. What I've done so far is this:

#sort values
df_sorted = df.sort_values(['site', 'iso', 'id', 'date'], ascending=[1, 1, 1, 1])
#group
df_grouped_sorted = df_sorted.groupby(['site', 'iso', 'id'], as_index=False)
df_sorted['mov_av_hits'] = df_grouped_sorted[['hits']].rolling(3, min_periods=3).mean().fillna(0).reset_index(
    0, drop=True)

And now my dataset looks like this:

date,site,iso,id,hits,hits_avg
2017-08-25,google,1,7012,14225.0,14146.0
2017-08-26,google,1,7012,14565.0,14338.6666667
2017-08-27,google,1,7012,14580.0,14456.6666667
2017-08-28,google,1,7012,14227.0,14457.3333333
2017-08-29,google,1,7012,14568.0,14458.3333333
2017-08-30,google,1,7012,14582.0,14459.0
2017-08-31,google,1,7012,14214.0,14454.6666667
2017-09-01,google,1,7012,14053.0,14283.0

Now the problem with this approach is that the current value of hits is included in the moving average.

Take for example, 2017-09-01. The value I need for hits_avg is (14568+14582+14214)/3 = 14454.6 but what I get is (14582+14214+14053)/3 = 14283.0

How can I get the moving average to take values from the previous 3 days?

Upvotes: 2

Views: 5120

Answers (3)

James Leslie
James Leslie

Reputation: 11

Here is a solution where you can calculate multiple different moving averages in one go:

df = df.assign(
    avg_hits_3=df_sorted.groupby('site')['hits'].rolling(3).mean().shift().values,
    avg_hits_5=df_sorted.groupby('site')['hits'].rolling(5).mean().shift().values,
    avg_hits_10=df_sorted.groupby('site')['hits'].rolling(10).mean().shift().values
)

Upvotes: 1

BENY
BENY

Reputation: 323226

You can try this .. 

df.hits.shift().rolling(3,min_periods=1).mean().fillna(df.hits)
Out[692]: 
0    14225.000000
1    14225.000000
2    14395.000000
3    14456.666667
4    14457.333333
5    14458.333333
6    14459.000000
7    14454.666667
Name: hits, dtype: float64

Update 

df['new']=df.groupby('site').hits.apply(lambda x : x.shift().rolling(3,min_periods=1).mean().fillna(x))


df
Out[712]: 
          date      site  iso    id     hits           new
0   2017-08-25    google    1  7012  14225.0  14225.000000
1   2017-08-26    google    1  7012  14565.0  14225.000000
2   2017-08-27    google    1  7012  14580.0  14395.000000
3   2017-08-28    google    1  7012  14227.0  14456.666667
4   2017-08-29    google    1  7012  14568.0  14457.333333
5   2017-08-30    google    1  7012  14582.0  14458.333333
6   2017-08-31    google    1  7012  14214.0  14459.000000
7   2017-09-01    google    1  7012  14053.0  14454.666667
8   2017-08-25  facebook    2  7019  21225.0  21225.000000
9   2017-08-26  facebook    2  7019  21565.0  21225.000000
10  2017-08-27  facebook    2  7019  31580.0  21395.000000
11  2017-08-28  facebook    2  7019  13227.0  24790.000000
12  2017-08-29  facebook    2  7019  22568.0  22124.000000
13  2017-08-30  facebook    2  7019  44582.0  22458.333333
14  2017-08-31  facebook    2  7019  32214.0  26792.333333
15  2017-09-01  facebook    2  7019  44053.0  33121.333333

Upvotes: 4

Anton vBR
Anton vBR

Reputation: 18906

Try to add shift() to move the comparing window one step:

df_sorted['mov_av_hits'] = df_grouped_sorted[['hits']].shift().rolling(3, min_periods=3).mean().fillna(0).reset_index(
    0, drop=True)

I get:

         date    site  iso    id     hits   mov_av_hits
0  2017-08-25  google    1  7012  14225.0      0.000000
1  2017-08-26  google    1  7012  14565.0      0.000000
2  2017-08-27  google    1  7012  14580.0      0.000000
3  2017-08-28  google    1  7012  14227.0  14456.666667
4  2017-08-29  google    1  7012  14568.0  14457.333333
5  2017-08-30  google    1  7012  14582.0  14458.333333
6  2017-08-31  google    1  7012  14214.0  14459.000000
7  2017-09-01  google    1  7012  14053.0  14454.666667

Upvotes: 3

Related Questions