dplyr mutate find max of n next values in column

Question

Given the following tibble :

library(tidyverse)

set.seed(1)

my_tbl = tibble(x = rep(words[1:5], 50) %>% sort(),
                y = 1:250,
                z = sample(seq(from = 30 , to = 90, by = 0.1), size = 250, replace = T)) 

i’m trying to create a new column which will populate the max value of the next 3 values in column z

for example

for row 1 max_3_next should be 84.5 (of row 4)

for row 5 max_3_next should be 86.7 (of row 7)

here is what I try to do:

my_tbl %>%
  mutate(max_next_3 =  max(.$z[(y + 1):(y + 3)])) 

> my_tbl %>%
+   mutate(max_3_next =  max(.$z[(y + 1):(y + 3)])) 
# A tibble: 250 x 4
   x         y     z max_3_next
   <chr> <int> <dbl>      <dbl>
 1 a         1  45.9       84.5
 2 a         2  52.3       84.5
 3 a         3  64.4       84.5
 4 a         4  84.5       84.5
 5 a         5  42.1       84.5
 6 a         6  83.9       84.5
 7 a         7  86.7       84.5
 8 a         8  69.7       84.5
 9 a         9  67.8       84.5
10 a        10  33.7       84.5
# ... with 240 more rows
Warning messages:
1: In (y + 1):(y + 3) :
  numerical expression has 250 elements: only the first used
2: In (y + 1):(y + 3) :
  numerical expression has 250 elements: only the first used

I get the above warnings

How can I change the code to achieve the desired result?

My preference is for a dplyer solution But i’ll be happy to learn other solutions alongside as well since performance is an issue since the original dataset may have 1 M ~ rows

Thanks Rafael

Answer 1

We can use rollmax from zoo library with align="left", to instruct the window from the current observation along with the following two observations

library(zoo)
my_tbl %>%
   mutate(max_3_next = rollmax(z,3, fill = NA, align = "left"))


# A tibble: 250 x 4
    x        y    z     max_3_next
  <chr>    <int> <dbl>    <dbl>
 1 a         1  45.9       64.4
 2 a         2  52.3       84.5
 3 a         3  64.4       84.5
 4 a         4  84.5       84.5
 5 a         5  42.1       86.7
 6 a         6  83.9       86.7
 7 a         7  86.7       86.7
 8 a         8  69.7       69.7
 9 a         9  67.8       67.8
10 a        10  33.7       42.3   
# ... with 240 more rows

Sorry, I believe that I misunderstand the OP correctly. So here is the correct solution -inspired from Joshua Ulrich answer's at this question- I hope. I will keep the previous answer just in case needed by future readers.

my_tbl %>% 
       mutate(max_3_next = rollapply(z, list((1:3)), max, fill=NA, align = "left", partial=TRUE))  

  # A tibble: 250 x 4
  x         y     z   max_3_next
  <chr> <int> <dbl> <dbl>
1 a         1  45.9  84.5
2 a         2  52.3  84.5
3 a         3  64.4  84.5
4 a         4  84.5  86.7
5 a         5  42.1  86.7
6 a         6  83.9  86.7
7 a         7  86.7  69.7
8 a         8  69.7  67.8
9 a         9  67.8  42.3
10 a        10  33.7  71.2
 # ... with 240 more rows