Checking for condition present in multiple rows

Question

I have a dataframe with columns LastPrice and KCT. The code then creates a third column, SignalBinary, and records on it a 1 when LastPrice > KCT for three consecutive rows.

(e.g. records a 1 in SignalBinary[1] when LastPrice[1] > KCT[1] AND LastPrice[2] > KCT[2] AND LastPrice[3] > KCT[3], and so on for every row)

df <- data.frame(LastPrice = c( 1221, 1220, 1220, 1217, 1216,  1218 , 1216, 1216, 1217, 1220, 1219, 1218, 1220, 1216, 1217, 1218, 1218, 1207, 1206, 1205), KCT = c( 1218, 1218, 1219, 1218, 1221,  1217 , 1217, 1216, 1219, 1216, 1217, 1216, 1219, 1217, 1218, 1217, 1217, 1217, 1219, 1217))

for(j in 1:nrow(df)) {
  df$SignalBinary[j] <- ifelse (  
    df$LastPrice[j] > df$KCT[j] 
    & df$LastPrice[j+1] > df$KCT[j+1] 
    & df$LastPrice[j+2] > df$KCT[j+2],
    1, 0)}

It works fine. But it is very basic. If I wanted to check for example LastPrice > KCT on 100 consecutive rows, I would need to write down the condition 100 times. Not ideal. Hence I would like to rewrite the code to be able to specify variable RowsToCheck = X, so as to check for the condition in X rows, without having to write the condition X times.

A for loop would work if I were to be checking for LastPrice > KCT in any 3 consecutive rows (i.e. using OR operators), rather than in 3 consecutive rows (i.e. using AND operators).

Answer 1

Can do this simply (and fast!) with data.table:

library(data.table)
setDT(df)
df[, check := as.integer(LastPrice > KCT)]
df[, Roll := Reduce('+',shift(check, 0:2L, type = "lead")) >= 3]

Just change the 0:2 to 0:99 or 0:n, and the >=3 condition, and you are good to go.

EDIT: As Frank points out, you can also use:

 df[, Roll := do.call(pmin, shift(check, 0:2, type="lead"))]

I'm inclined to prefer this method as you only need to change one input as you vary the number of rows.

I should also point out that this is extendable to the lagging case by changing the type argument to "lag"