CODEWITHSUNDEEP
rdataframe
tassones
tassones

Reputation: 1692

How to subtract rolling average from each observation with multiple groups in R

I have a time series of water temperature (Wtemp) measurements from two sites (SiteID A & B). I would like to calculate a centered seven-day moving average of Wtemp then subtract that moving seven-day average from each of the Wtemp observations for each group.

Here is an example data frame:

df <- data.frame(matrix(ncol = 3, nrow = 28))
x <- c("siteID", "Date", "Wtemp")
colnames(df) <- x
df[1:14,1] <- rep("A",14)
df[15:28,1] <- rep("B",14)
df$Date <- rep(seq(from = as.Date("2020-01-01"), to = as.Date("2020-01-14"), by = 1),2)  
set.seed(123)
df[1:14,3] <- rnorm(14, 10, 1)
df[15:28,3] <- rnorm(14, 20, 2)

The output would ideally look like this:

   siteID       Date Wtemp WtempMean WtempDiff
1       A 2020-01-01  8.86        NA        NA
2       A 2020-01-02 11.25        NA        NA
3       A 2020-01-03 10.43        NA        NA
4       A 2020-01-04  9.70     10.41     -0.71
5       A 2020-01-05 10.90     10.67      0.23
6       A 2020-01-06 10.88     10.57      0.31
7       A 2020-01-07 10.82     10.50      0.32
8       A 2020-01-08 10.69     10.50      0.19
9       A 2020-01-09 10.55     10.31      0.24
10      A 2020-01-10  9.94     10.09     -0.15
11      A 2020-01-11  9.69      9.94     -0.25
12      A 2020-01-12  9.62        NA        NA
13      A 2020-01-13  9.31        NA        NA
14      A 2020-01-14  9.79        NA        NA
15      B 2020-01-01 17.47        NA        NA
16      B 2020-01-02 24.34        NA        NA
17      B 2020-01-03 22.42        NA        NA
18      B 2020-01-04 17.75     20.26     -2.51
19      B 2020-01-05 19.19     20.59     -1.40
20      B 2020-01-06 19.07     20.05     -0.98
21      B 2020-01-07 21.56     19.69      1.87
22      B 2020-01-08 19.83     20.00     -0.17
23      B 2020-01-09 20.51     20.51      0.00
24      B 2020-01-10 19.94     20.58     -0.64
25      B 2020-01-11 19.91     20.79     -0.88
26      B 2020-01-12 22.74        NA        NA
27      B 2020-01-13 19.55        NA        NA
28      B 2020-01-14 23.03        NA        NA

I think this can be done using sapply and the function rollmean from the zoo package but I'm not sure how I could do efficiently when there are multiple groups (i.e., siteID)

Upvotes: 0

Views: 75

Answers (1)

Ronak Shah
Ronak Shah

Reputation: 388982

Calculate 7-day rolling average with rollmean for each siteID and subtract the value with Wtemp.

library(dplyr)
library(zoo)

df %>%
  group_by(siteID) %>%
  mutate(WtempMean = rollmean(Wtemp, 7, fill = NA), 
         WtempDiff = Wtemp - WtempMean) %>%
  ungroup

#   siteID Date       Wtemp WtempMean WtempDiff
#   <chr>  <date>     <dbl>     <dbl>     <dbl>
# 1 A      2020-01-01  9.44      NA      NA    
# 2 A      2020-01-02  9.77      NA      NA    
# 3 A      2020-01-03 11.6       NA      NA    
# 4 A      2020-01-04 10.1       10.4    -0.379
# 5 A      2020-01-05 10.1       10.3    -0.219
# 6 A      2020-01-06 11.7       10.3     1.43 
# 7 A      2020-01-07 10.5       10.0     0.464
# 8 A      2020-01-08  8.73      10.2    -1.43 
# 9 A      2020-01-09  9.31      10.2    -0.881
#10 A      2020-01-10  9.55      10.0    -0.453
# … with 18 more rows

PS - I got different random numbers with the same seed.

Upvotes: 2

Related Questions