Treat NA as zero only when adding a number
10,636
Solution 1
You can define your own function to act as you want
plus <- function(x) {
if(all(is.na(x))){
c(x[0],NA)} else {
sum(x,na.rm = TRUE)}
}
rbind(dt1, dt2)[,lapply(.SD, plus), by = Name]
Solution 2
dtsum <- rbind(dt1, dt2)[, lapply(.SD, function(x) ifelse(all(is.na(x)), as.numeric(NA), sum(x, na.rm=T))), by=Name]
(includes @Arun's suggestion)
na.rm=TRUE
is very useful to remember
Author by
R-obert
Updated on August 11, 2022Comments
-
R-obert almost 2 years
When calculating the sum of two data tables,
NA+n=NA
.> dt1 <- data.table(Name=c("Joe","Ann"), "1"=c(0,NA), "2"=c(3,NA)) > dt1 Name 1 2 1: Joe 0 3 2: Ann NA NA > dt2 <- data.table(Name=c("Joe","Ann"), "1"=c(0,NA), "2"=c(2,3)) > dt2 Name 1 2 1: Joe 0 2 2: Ann NA 3 > dtsum <- rbind(dt1, dt2)[, lapply(.SD, sum), by=Name] > dtsum Name 1 2 1: Joe 0 5 2: Ann NA NA
I don't want to substitute all NA's with 0. What I want is
NA+NA=NA
andNA+n=n
to get the following result:Name 1 2 1: Joe 0 5 2: Ann NA 3
How is this done in R?
UPDATE: removed typo in dt1