How to get previous two years max value in R

Question

Hi I have data frame as

How to create column max_value with max from last 2 years max value

dt <- 
structure(list(Name = c("A", "A", "A", "A", "A", "A", "A", "B", 
"B", "B", "B", "B", "B"), year = c(2012L, 2012L, 2013L, 2014L, 
2015L, 2016L, 2017L, 2012L, 2013L, 2013L, 2014L, 2015L, 2016L
), value = c(22L, 99L, 12L, 1L, 23L, 40L, 12L, 12L, 33L, 40L, 
NA, 20L, 20L), max_value = c(NA, NA, 99L, 99L, 12L, 23L, 40L, 
NA, 12L, 12L, 40L, 40L, 20L)), .Names = c("Name", "year", "value", 
"max_value"), row.names = c(NA, -13L), class = c("data.table", 
"data.frame"))

Name   year   value   *max_value*
A      2012    22        NA
A      2012    99        NA
A      2013    12        99
A      2014    01        99
A      2015    23        12
A      2016    40        23
A      2017    12        40
B      2012    12        NA
B      2013    33        12
B      2013    40        12
B      2014    NA        40
B      2015    20        40
B      2016    20        20

Thanks in advance

Answer 1

Here is an other data.table approach, using a self-join by .EACHI

library(data.table)
# temporary rowwise id
setDT(dt)[, id := .I]
# set key
setkey(dt, id)
# self join, set infinite values back to NA
dt[dt, max_val2 := {
  dt[Name == i.Name & year >= (i.year - 2) & year < i.year, max(value, na.rm = TRUE)]
}, by = .EACHI][is.infinite(max_val2), max_val2 := NA][, id := NULL]

    Name year value max_value max_val2
 1:    A 2012    22        NA       NA
 2:    A 2012    99        NA       NA
 3:    A 2013    12        99       99
 4:    A 2014     1        99       99
 5:    A 2015    23        12       12
 6:    A 2016    40        23       23
 7:    A 2017    12        40       40
 8:    B 2012    12        NA       NA
 9:    B 2013    33        12       12
10:    B 2013    40        12       12
11:    B 2014    NA        40       40
12:    B 2015    20        40       NA
13:    B 2016    20        20       NA