data.table - Find consecutive sequence of zeros in R

Question

I have a data.frame really big (actually a data.table). Now, to simplify things, let's assume my data.frame is just as follow:

x <- c(1, 1, 0, 0, 1, 0, 0, NA, NA, 0) 
y <- c(1 ,0 ,NA, NA, 0, 0, 0, 1, 1, 0)
mydf <- data.frame(rbind(x,y))

I'd like to identify in which row (if any) the last sequence is formed by three consecutive zeros, not considering NAs. So, in the example above, the first row has three consecutive zeros in the last sequence, but not the second one.

I know how to do that if only I have a vector (not a data.frame):

runs <-  rle(x[is.na(x)==F])

runs$lengths[length(runs$lengths)] > 2 & runs$values[length(runs$lengths)]==0

I obviously can do a loop and I'll have what I want. But it'll be incredibly inefficient and my actual data.frame is quite big. So, any ideas on how to do in a fastest way?

I guess apply can be useful, but I'm not able to thinking of using it right now. Also, maybe there is a data.table way of doing this?

ps.: Actually, this data.frame is a reshaped version of my original data.table. If somehow I can do the job with the data.frame in the original format, it's ok. To see how is my data.frame originally, just think of it as:

x <- c(1, 1, 0, 0, 1, 0, 0, 0) 
y <- c(1 ,0 , 0, 0, 0, 1, 1, 0)

myOriginalDf <- data.frame(value=c(x,y), id=rep(c('x','y'), c(length(x), length(y))))

Answer 1

Using data.table, as your question suggests you actually want to, as far I a can see, this is doing what you want

DT <- data.table(myOriginalDf)

# add the original order, so you can't lose it
DT[, orig := .I]

# rle by id, saving the length as a new variables

DT[, rleLength := {rr <- rle(value); rep(rr$length, rr$length)}, by = 'id']

# key by value and length to subset 

setkey(DT, value, rleLength)

# which rows are value = 0 and length > 2

DT[list(0, unique(rleLength[rleLength>2])),nomatch=0]

##    value rleLength id orig
## 1:     0         3  x    6
## 2:     0         3  x    7
## 3:     0         3  x    8
## 4:     0         4  y   10
## 5:     0         4  y   11
## 6:     0         4  y   12
## 7:     0         4  y   13