CODEWITHSUNDEEP
rdplyrdata.table
Philippe Massicotte
Philippe Massicotte

Reputation: 1529

Rolling mean (varying lag)

library(tidyverse)

How to calculate the means for the following depths: 1-2, 1-3, 1-4, …, 1-10 for variables y and z. Note that in my real data I do not have equally space depth, so I can’t really use rollapply directly.

set.seed(123)
df <- data.frame(depth = seq(1, 10, length.out = 100), y = rnorm(100), z = rnorm(100))

head(df)
#>      depth           y           z
#> 1 1.000000 -0.56047565 -0.71040656
#> 2 1.090909 -0.23017749  0.25688371
#> 3 1.181818  1.55870831 -0.24669188
#> 4 1.272727  0.07050839 -0.34754260
#> 5 1.363636  0.12928774 -0.95161857
#> 6 1.454545  1.71506499 -0.04502772

Example of desired ouputs

df %>% 
  filter(between(depth, 1, 2)) %>% 
  summarise_at(vars(y, z), mean) %>% 
  mutate(start_depth = 1, end_depth = 2)
#>           y          z start_depth end_depth
#> 1 0.1941793 -0.3271552           1         2

df %>% 
  filter(between(depth, 1, 3)) %>% 
  summarise_at(vars(y, z), mean) %>% 
  mutate(start_depth = 1, end_depth = 3)
#>            y          z start_depth end_depth
#> 1 0.02263796 -0.3699128           1         3

df %>% 
  filter(between(depth, 1, 4)) %>% 
  summarise_at(vars(y, z), mean) %>% 
  mutate(start_depth = 1, end_depth = 4)
#>            y          z start_depth end_depth
#> 1 0.01445704 -0.1993295           1         4

And so on… Created on 2018-10-23 by the reprex package (v0.2.1)

Upvotes: 0

Views: 213

Answers (1)

Frank
Frank

Reputation: 66819

OP already has code to create output one at a time, so I guess the request is to do it all at once:

library(data.table)
setDT(df)

cols = c("y", "z")
mDT = data.table(start_depth = 1, end_depth = as.numeric(1:10))
res = df[mDT, on=.(depth >= start_depth, depth <= end_depth), 
  lapply(.SD, mean), by=.EACHI, .SDcols=cols]    
setnames(res, c(names(mDT), cols))

    start_depth end_depth           y           z
 1:           1         1 -0.56047565 -0.71040656
 2:           1         2  0.19417934 -0.32715522
 3:           1         3  0.02263796 -0.36991283
 4:           1         4  0.01445704 -0.19932946
 5:           1         5  0.06702734 -0.27118566
 6:           1         6  0.08145323 -0.21811183
 7:           1         7  0.03197788 -0.13311881
 8:           1         8  0.01918313 -0.10335488
 9:           1         9  0.03956002 -0.08520866
10:           1        10  0.09040591 -0.10754680

This is a non-equi join. The extra setnames step may change soon.

A non-equi join may be suitable if your ranges are arbitrary, but in the OP's case, it is just a growing range so the natural solution is a rolling computation (eg with RcppRoll).

Upvotes: 2

Related Questions