Replace NaNs with NA

Assume the following datatable:

DT <- data.table(a=c(1,2,3,4,5,6),b=c(NaN,NaN,NaN,4,5,6),c=c(NaN,3,3,3,NaN,NaN)) 

How can I replace all NaN values by NA, i.e. in each column? Looking for an elegant (short) way to do this, but I am also wondering if it is possible using lapply, like I tried below.

My approach so far:

DT[,lapply(SD,function(x){x[is.nan(x)] := NA}),.SDcols=c("a","b","c")] 

Actual result of my code is:

Error in :=(x[is.nan(x)], NA) : Check that is.data.table(DT) == TRUE. Otherwise, := and :=(...) are defined for use in j, once only and in particular ways. See help(":=").

2 Answers

You can do it easily using dplyr's mutate_all function.

DT <- DT %>% mutate_all(~ifelse(is.nan(.), NA, .)) print(DT) #a b c # 1 NA NA # 2 NA 3 # 3 NA 3 # 4 4 3 # 5 5 NA # 6 6 NA 

The code above is equivalent to

DT %>% mutate_all(function(x) ifelse(is.nan(x), NA, x)) 

Here's one way to do it using data.table syntax. I'm not sure if there is a simpler way.

DT[, names(DT) := lapply(.SD, function(x) ifelse(is.nan(x), NA, x))] 

EDIT: An alternative way to do this, as posted by markus in the comments beow.

DT[, lapply(.SD, function(x) replace(x, is.nan(x), NA))] 

You want something like this:

DT[DT == "NaN"] <- NA 

This should ideally find all the NaN that you have and replace it with NA. If anyone knows a better way please let me know!

Hope this helps!