CODEWITHSUNDEEP
rdata.table
Vit
Vit

Reputation: 77

Delete columns containing only NA with data table

Is there a better way than 

DT <- DT[,!apply(DT,2,function(x) all(is.na(x))), with = FALSE]

to subset with data table only on columns which are not entirely filled with NAs?

Thanks

Upvotes: 3

Views: 602

Answers (1)

MichaelChirico
MichaelChirico

Reputation: 34703

The basic idea is to find the all-NA columns with something like:

na_idx = sapply(DT, function(x) all(is.na(x)))

To apply this to subsetting your table, the answer depends on whether you'd like to remove these columns from your table, or whether you plan to create a separate, derivative table;

In the former case, you should set these columns to NULL:

DT[ , which(sapply(DT, function(x) all(is.na(x)))) := NULL]

In the latter case, there are several options:

idx = sapply(DT, function(x) !all(is.na(x)))
DT = DT[ , idx, with = FALSE] # or DT = DT[ , ..idx]

DT = DT[ , lapply(.SD, function(x) if (all(is.na(x))) NULL else x)]

apply and colSums approaches will involve matrix conversion which is likely to be inefficient.

Here's a benchmark of the cases laid out here and by @DavidArenburg in the comments above:

          method   time
1: which := NULL  1.434
2:  for set NULL  3.432
3:   lapply(.SD) 16.041
4:         ..idx 10.343
5:    with FALSE  4.896

Code:

library(data.table)

NN = 1e7
kk = 50
n_na = 5

set.seed(021349)
DT = setDT(replicate(kk, rnorm(NN), simplify = FALSE))
DT[ , (sample(kk, n_na)) := NA_real_]

DT2 = copy(DT)

t1 = system.time(
  DT2[ , which(sapply(DT2, function(x) all(is.na(x)))) := NULL]
)

rm(DT2)
DT2 = copy(DT)

t2 = system.time({
  for (col in copy(names(DT2))) 
    if (all(is.na(DT2[[col]]))) set(DT2, , col, NULL)
})

rm(DT2)
DT2 = copy(DT)

t3 = system.time({
  DT3 = DT2[ , lapply(.SD, function(x) if (all(is.na(x))) NULL else x)]
})

rm(DT3)

t4 = system.time({
  idx = sapply(DT2, function(x) !all(is.na(x)))
  DT3 = DT2[ , ..idx]
})

rm(DT3)

t5 = system.time({
  idx = sapply(DT2, function(x) !all(is.na(x)))
  DT3 = DT2[ , idx, with = FALSE]
})

data.table(
  method = c('which := NULL', 'for set NULL', 
             'lapply(.SD)', '..idx', 'with FALSE'),
  time = sapply(list(t1, t2, t3, t4, t5), `[`, 'elapsed')
)

Upvotes: 7

Related Questions