Fastest way to replace NAs in a large data.table


Answers

Here is a solution using NAToUnknown in the gdata package. I have used Andrie's solution to create a huge data table and also included time comparisons with Andrie's solution.

# CREATE DATA TABLE
dt1 = create_dt(2e5, 200, 0.1)

# FUNCTIONS TO SET NA TO ZERO   
f_gdata  = function(dt, un = 0) gdata::NAToUnknown(dt, un)
f_Andrie = function(dt) remove_na(dt)

# COMPARE SOLUTIONS AND TIMES
system.time(a_gdata  <- f_gdata(dt1))

user  system elapsed 
4.224   2.962   7.388 

system.time(a_andrie <- f_Andrie(dt1))

 user  system elapsed 
4.635   4.730  20.060 

identical(a_gdata, g_andrie)  

TRUE
Question

I have a large data.table, with many missing values scattered throughout its ~200k rows and 200 columns. I would like to re code those NA values to zeros as efficiently as possible.

I see two options:
1: Convert to a data.frame, and use something like this
2: Some kind of cool data.table sub setting command

I'll be happy with a fairly efficient solution of type 1. Converting to a data.frame and then back to a data.table won't take too long.




library(data.table)

DT = data.table(a=c(1,"A",NA),b=c(4,NA,"B"))

DT
    a  b
1:  1  4
2:  A NA
3: NA  B

DT[,lapply(.SD,function(x){ifelse(is.na(x),0,x)})]
   a b
1: 1 4
2: A 0
3: 0 B

Just for reference, slower compared to gdata or data.matrix, but uses only the data.table package and can deal with non numerical entries.




My understanding is that the secret to fast operations in R is to utilise vector (or arrays, which are vectors under the hood.)

In this solution I make use of a data.matrix which is an array but behave a bit like a data.frame. Because it is an array, you can use a very simple vector substitution to replace the NAs:

A little helper function to remove the NAs. The essence is a single line of code. I only do this to measure execution time.

remove_na <- function(x){
  dm <- data.matrix(x)
  dm[is.na(dm)] <- 0
  data.table(dm)
}

A little helper function to create a data.table of a given size.

create_dt <- function(nrow=5, ncol=5, propNA = 0.5){
  v <- runif(nrow * ncol)
  v[sample(seq_len(nrow*ncol), propNA * nrow*ncol)] <- NA
  data.table(matrix(v, ncol=ncol))
}

Demonstration on a tiny sample:

library(data.table)
set.seed(1)
dt <- create_dt(5, 5, 0.5)

dt
            V1        V2        V3        V4        V5
[1,]        NA 0.8983897        NA 0.4976992 0.9347052
[2,] 0.3721239 0.9446753        NA 0.7176185 0.2121425
[3,] 0.5728534        NA 0.6870228 0.9919061        NA
[4,]        NA        NA        NA        NA 0.1255551
[5,] 0.2016819        NA 0.7698414        NA        NA

remove_na(dt)
            V1        V2        V3        V4        V5
[1,] 0.0000000 0.8983897 0.0000000 0.4976992 0.9347052
[2,] 0.3721239 0.9446753 0.0000000 0.7176185 0.2121425
[3,] 0.5728534 0.0000000 0.6870228 0.9919061 0.0000000
[4,] 0.0000000 0.0000000 0.0000000 0.0000000 0.1255551
[5,] 0.2016819 0.0000000 0.7698414 0.0000000 0.0000000





Links