2

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?

Craig
  • 1,929
  • 5
  • 30
  • 51

3 Answers3

4

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
BENY
  • 317,841
  • 20
  • 164
  • 234
  • Thanks Wen but the problem now is that when `site` and `iso` change, the initial values of the moving average for the new `site`/`iso` include `hits` values from the previous `site`/`iso` – Craig Dec 20 '17 at 16:43
  • @Craig you just need groupby `df.groupby('site').apply(lambda x : x.hits.shift().rolling(3,min_periods=1).mean().fillna(x.hits))` – BENY Dec 20 '17 at 16:49
  • how would I assign this back to the dataframe? This works as a standalone statement but I'd like the values in a new column in the df. Trying to assign to a new column results in `TypeError: incompatible index of inserted column with frame index ` – Craig Dec 20 '17 at 17:03
  • @Craig `df['new']=df.groupby('site').hits.apply(lambda x : x.shift().rolling(3,min_periods=1).mean().fillna(x))`, this is assign it back – BENY Dec 20 '17 at 17:06
  • @Craig yw~ :-) happy coding – BENY Dec 20 '17 at 17:28
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/161644/discussion-between-craig-and-wen). – Craig Dec 20 '17 at 18:07
3

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
Anton vBR
  • 18,287
  • 5
  • 40
  • 46
1

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
)
James Leslie
  • 11
  • 1