How to calculate moving average from previous rows in data.table?-CodePudding

I've a data like this;

library(data.table)

set.seed(1)

df <- data.table(store = sample(LETTERS[1:2],size = 10,replace = T),
                 week = sample(1:10),
                 demand = round(sample(rnorm(10,mean = 20,sd=2)),2))

random_na_index <- sample(1:nrow(df),3)

df[random_na_index,demand := NA]

setorder(df,store,week)

    store week demand
 1:     A    3  19.18
 2:     A    5     NA
 3:     A    6     NA
 4:     A    8  19.55
 5:     A    9  20.50
 6:     A   10     NA
 7:     B    1  20.75
 8:     B    2  17.70
 9:     B    4  19.40
10:     B    7  17.52

I need to calculate moving average using the 2 weeks before the current week. I couldn't do it because zoo's and data.table's frollmean uses current row also while calculating moving average. I don't also know how to handle NA's while applying a rolling function.

The desired output should look like;

    store week demand desired_column
 1:     A    3  19.18             NA
 2:     A    5     NA         19.180
 3:     A    6     NA         19.180
 4:     A    8  19.55             NA
 5:     A    9  20.50         19.550
 6:     A   10     NA         20.025
 7:     B    1  20.75             NA
 8:     B    2  17.70         20.750
 9:     B    4  19.40         19.225
10:     B    7  17.52         18.550

CodePudding user response：

You could shift the values before applying frollmean with na.rm = TRUE argument:

df[order(store,week),desired:=frollmean(shift(demand),n=2,na.rm=T),by=.(store)][]

     store  week demand desired
    <char> <int>  <num>   <num>
 1:      A     3  19.18      NA
 2:      A     5     NA  19.180
 3:      A     6     NA  19.180
 4:      A     8  19.55     NaN
 5:      A     9  20.50  19.550
 6:      A    10     NA  20.025
 7:      B     1  20.75      NA
 8:      B     2  17.70  20.750
 9:      B     4  19.40  19.225
10:      B     7  17.52  18.550