Efficiently counting non-NA elements in data.table

Question

Sometimes I need to count the number of non-NA elements in one or another column in my data.table. What is the best data.table-tailored way to do so?

For concreteness, let's work with this:

DT <- data.table(id = sample(100, size = 1e6, replace = TRUE),
                 var = sample(c(1, 0, NA), size = 1e6, replace = TRUE), key = "id")

The first thing that comes to my mind works like this:

DT[!is.na(var), N := .N, by = id]

But this has the unfortunate shortcoming that N does not get assigned to any row where var is missing, i.e. DT[is.na(var), N] = NA.

So I work around this by appending:

DT[!is.na(var), N:= .N, by = id][ , N := max(N, na.rm = TRUE), by = id] #OPTION 1

However, I'm not sure this is the best approach; another option I thought of and one suggested by the analog to this question for data.frames would be:

DT[ , N := length(var[!is.na(var)]), by = id] # OPTION 2

and

DT[ , N := sum(!is.na(var)), by = id] # OPTION 3

Comparing computation time of these (average over 100 trials), the last seems to be the fastest:

OPTION 1 | OPTION 2 | OPTION 3
  .075   |   .065   |   .043

Does anyone know a speedier way for data.table?

Answer 1

Yes the option 3rd seems to be the best one. I've added another one which is valid only if you consider to change the key of your data.table from id to var, but still option 3 is the fastest on your data.

library(microbenchmark)
library(data.table)

dt<-data.table(id=(1:100)[sample(10,size=1e6,replace=T)],var=c(1,0,NA)[sample(3,size=1e6,replace=T)],key=c("var"))

dt1 <- copy(dt)
dt2 <- copy(dt)
dt3 <- copy(dt)
dt4 <- copy(dt)

microbenchmark(times=10L,
               dt1[!is.na(var),.N,by=id][,max(N,na.rm=T),by=id],
               dt2[,length(var[!is.na(var)]),by=id],
               dt3[,sum(!is.na(var)),by=id],
               dt4[.(c(1,0)),.N,id,nomatch=0L])
# Unit: milliseconds
#                                                         expr      min       lq      mean    median        uq       max neval
#  dt1[!is.na(var), .N, by = id][, max(N, na.rm = T), by = id] 95.14981 95.79291 105.18515 100.16742 112.02088 131.87403    10
#                     dt2[, length(var[!is.na(var)]), by = id] 83.17203 85.91365  88.54663  86.93693  89.56223 100.57788    10
#                             dt3[, sum(!is.na(var)), by = id] 45.99405 47.81774  50.65637  49.60966  51.77160  61.92701    10
#                        dt4[.(c(1, 0)), .N, id, nomatch = 0L] 78.50544 80.95087  89.09415  89.47084  96.22914 100.55434    10