Count NAs in huge datatable

user_9609758 0
Viewed 57

I have a large datatable that contains several hundred millions of data. I am trying to eliminate entries that have missing data for many observed variables. I am using the following code:

DATA[, NR := Reduce("+", data.table(!is.na(.SD))),.SDcols = c("var1","var","var3","var4","var5","var6"),by=c("ID","day")]

While I cannot share the data, I am wondering if the above code is fastest in datatable, or if there is anything else I am missing? The code runs for a good while and does not seem to finish.

Thanks for any hint.

data.table r performance
1 Answers

The is.na(.SD) creates a logical matrix, then we are converting it to data.table, instead, it can be looped with lapply and then do the + with Reduce

DATA[, NR := Reduce(`+`, lapply(.SD, is.na)),
            .SDcols = paste0('var', 1:6), by = .(ID, day)]

Benchmarks

set.seed(24)
DATA <- data.table(ID = rep(1:500, each =  50), day = rep(1:25, length.out = 25000), var1=  sample(c(1:5, NA), 25000, replace = TRUE),
        var1=  sample(c(1:40, NA), 25000, replace = TRUE), var2 =  sample(c(1:25, NA), 25000, replace = TRUE), var3 =  sample(c(1:35, NA), 25000, replace = TRUE),
        var4=  sample(c(1:100, NA), 25000, replace = TRUE), var5 =  sample(c(1:50, NA), 25000, replace = TRUE), var6 =  sample(c(1:10, NA), 25000, replace = TRUE))

DATA1 <- copy(DATA)
system.time(DATA[, NR := Reduce("+", data.table(!is.na(.SD))),.SDcols = c("var1","var2","var3","var4","var5","var6"),by=c("ID","day")])
# user  system elapsed 
# 6.451   0.148   5.535 

system.time(DATA1[, NR := Reduce(`+`, lapply(.SD, is.na)), .SDcols = paste0('var', 1:6), by = .(ID, day)])
# user  system elapsed 
#  1.307   0.046   0.353

Or another option is rowSums

system.time(DATA1[, NR2 := rowSums(is.na(.SD)),
       .SDcols = paste0('var', 1:6), by = .(ID, day)])
#   user  system elapsed 
#  1.434   0.028   0.456
user_37322710
Related