How to compute rolling maximum or minimum of a dataframe column using dplyr subject to multiple cond-CodePudding

Using dplyr I am trying to calculate a maximum value in each row of a dataframe column (dataframe = array), based on multiple conditions, but on a "rolling basis" as shown and described in the below image. By "rolling basis" I for purposes of calculations, the array is anchored at the top of the range (row 4) and extends to the current row where the calculation is occurring (and not the bottom of the array). The blue highlights show the dataframe created by running the below reproducible code, and the yellow highlights shows the values I am trying to recreate using dplyr with Excel code and explanations to the right.

Any recommendations for how to do this?

Below is my unsuccessful attempt to do this, using code that worked for the equivalent of sumifs() on a floating-array basis. But it doesn't work for maxifs()/minifs().

library(dplyr)

myDF <- 
  data.frame(
    Names = c("R","R","B","R","X","X"),
    Group = c(0,1,1,2,2,0),
    Code1 = c(1,3,8,2,4,5)
  )

myDF %>% mutate(maxIfs = sapply(1:n(), function(x) max(Code1[1:x][(Names[1:x] == Names[x]) & (Group[1:x] == Group[x])])))

Edit OP dplyr code to reflect the correction caught by cnbrownlie:

myDF %>% mutate(maxIfs = sapply(1:n(), function(x) max(Code1[1:x][(Names[1:x] == Names[x]) & (Group[1:x] == 0)], 0)))

CodePudding user response：

Here is one approach using data.table:

f <- function(d,i,n) {
  d[id<=i & Names==n & Group==0, if(.N>0) {max(Code1)} else {0}]
}

library(data.table)
setDT(myDF)

myDF[, id:=.I][,r_maxifs:=f(myDF, .BY$id, .BY$Names), .(id, Names)][,id:=NULL]

Output:

   Names Group Code1 r_maxifs
1:     R     0     1        1
2:     R     1     3        1
3:     B     1     8        0
4:     R     2     2        1
5:     X     2     4        0
6:     X     0     5        5