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)