Home > Enterprise >  Get average of 'x' days prior/after specific dates
Get average of 'x' days prior/after specific dates

Time:10-15

In the following example, how can I get the average value in data of the previous 3 days prior to the dates listed in dates.

set.seed(250)
data <- data.frame(Date = seq(as.Date("2020/1/1"), as.Date("2020/1/25"), "day"), Value = rnorm(25))
dates <- c("2020-01-24", "2020-01-13", "2020-01-05")
dates$expected <- c(0.8039, -0.1550, 0.0006)

Thanks!

CodePudding user response:

Is this what you're looking for?

data$means <- c(rep_len(NA, 2), rowMeans(embed(data$Value, 3)))

Which produces

       Date       Value       means
1  2020-01-01  1.27200548          NA
2  2020-01-02 -1.12854285          NA
3  2020-01-03  1.50075972  0.54807411
4  2020-01-04  0.54235338  0.30485675
5  2020-01-05 -0.86076637  0.39411558
6  2020-01-06  0.14604265 -0.05745678
7  2020-01-07 -1.26594728 -0.66022367
8  2020-01-08  0.75936562 -0.12017967
9  2020-01-09  0.37525882 -0.04377428
10 2020-01-10 -0.17140506  0.32107313
11 2020-01-11 -0.07772297  0.04204360
12 2020-01-12 -0.65247375 -0.30053393
13 2020-01-13 -0.35725157 -0.36248277
14 2020-01-14  0.13947094 -0.29008479
15 2020-01-15 -2.08799946 -0.76859336
16 2020-01-16 -1.26800576 -1.07217809
17 2020-01-17  2.42589479 -0.31003681
18 2020-01-18  0.74278927  0.63355943
19 2020-01-19  2.57310796  1.91393067
20 2020-01-20  0.03318109  1.11635944
21 2020-01-21 -0.29903505  0.76908467
22 2020-01-22  0.08292441 -0.06097652
23 2020-01-23 -0.80788614 -0.34133226
24 2020-01-24  0.40801046 -0.10565042
25 2020-01-25  1.28472209  0.29494880

So for row 4 the mean of the previous 3 dates are in the means column on row 3

CodePudding user response:

Assuming dates as shown below define mean3 which given an index ix gets the average of the 3 prior values or NA if there are not 3 prior values and sapply that to the indexes matched by dates.

dates <- c("2020-01-24", "2020-01-13", "2020-01-05")

mean3 <- function(ix) if (ix > 3) mean(data$Value[ix - 1:3]) else NA
sapply(match(dates, format(data$Date)), mean3)
  • Related