Calculate the maximum of the next 3 rows of a specific column in each row in R

Question

I want to create a column “Y” that gives a maximum of the next three rows of a specific column “X” in each row in R.

eg -

X  Y
1  4 (4=max(2,3,4))
2  7 (7= max(3,4,7))
3  9 (9=max(4,7,9))
4
7
9
2
4

Can anyone help with this?

Answer 1

Given:

x <- c(1, 2, 3, 4, 7, 9, 2, 4)

You can see the function embed:

embed(x, 3)
#      [,1] [,2] [,3]
# [1,]    3    2    1
# [2,]    4    3    2
# [3,]    7    4    3
# [4,]    9    7    4
# [5,]    2    9    7
# [6,]    4    2    9

Please note that this is not the correct length, and that we are not interested in the first line, so let's make a modification:

embed(c(x[-1], 0, 0, 0), 3)
#      [,1] [,2] [,3]
# [1,]    4    3    2
# [2,]    7    4    3
# [3,]    9    7    4
# [4,]    2    9    7
# [5,]    4    2    9
# [6,]    0    4    2
# [7,]    0    0    4
# [8,]    0    0    0

From there it should be simple:

apply(embed(c(x[-1], 0, 0, 0), 3), 1, max)
# [1] 4 7 9 9 9 4 4 0

---

For convenience, as a function:

this_by_n <- function(invec, n = 3, pad_val = NA, FUN = sum) {
  FUN <- match.fun(FUN)
  apply(embed(c(invec[-1], rep(pad_val, n)), n), 1, {
    function(x) if (all(is.na(x))) NA else FUN(x[!is.na(x)])
  })
}

Try:

this_by_n(x, 3, NA, mean)
this_by_n(x, 2, NA, max)
this_by_n(x, 4, NA, min)

Answer 2

We can do this with help shiftfrom data.tablealong with pmax.

library(data.table)
do.call(pmax, c(shift(x, 1:3, type='lead'), na.rm=TRUE))
#[1]  4  7  9  9  9  4  4 NA

, pmin min

do.call(pmin, c(shift(x, 1:3, type='lead'), na.rm=TRUE))
#[1]  2  3  4  2  2  2  4 NA

mean

rowMeans(do.call(cbind,shift(x, 1:3, type='lead')), na.rm=TRUE)

 x <- c(1, 2, 3, 4, 7, 9, 2, 4)