Using `:=` in data.table to sum the values of two columns in R, ignoring NAs

Question

I have what I think is a very simple question related to the use of data.table and the `:=` function. I don't think I quite understand the behaviour of `:=` and often I run into similar problems. Here is some example data ``` mat <- structure(list( col1 = c(NA, 0, -0.015038, 0.003817, -0.011407), col2 = c(0.003745, 0.007463, -0.007407, -0.003731, -0.007491)), .Names = c("col1", "col2"), row.names = c(NA, 10L), class = c("data.table", "data.frame")) ``` which gives ``` > mat col1 col2 1: NA 0.003745 2: 0.000000 0.007463 3: -0.015038 -0.007407 4: 0.003817 -0.003731 5: -0.011407 -0.007491 ``` I want to create a column called col3 which gives the sum of col1 and col2. If I use ``` mat[,col3 := col1 + col2] # col1 col2 col3 #1: NA 0.003745 NA #2: 0.000000 0.007463 0.007463 #3: -0.015038 -0.007407 -0.022445 #4: 0.003817 -0.003731 0.000086 #5: -0.011407 -0.007491 -0.018898 ``` then I get an NA for the first row, but I want NAs to be ignored. So I tried instead ``` mat[,col3 := sum(col1,col2,…

Accepted Answer

It's not a lack of understanding of data.table but rather one regarding vectorized functions in R. You can define a dyadic operator that will behave differently than the "+" operator with regard to missing values:

```
 `%+na%` <- function(x,y) {ifelse( is.na(x), y, ifelse( is.na(y), x, x+y) )}

mat[ , col3:= col1 %+na% col2]
#-------------------------------
        col1      col2      col3
1:        NA  0.003745  0.003745
2:  0.000000  0.007463  0.007463
3: -0.015038 -0.007407 -0.022445
4:  0.003817 -0.003731  0.000086
5: -0.011407 -0.007491 -0.018898
```

You can use mrdwad's comment to do it with `sum(... , na.rm=TRUE`):

```
mat[ , col4 := sum(col1, col2, na.rm=TRUE), by=1:NROW(mat)]
```

Using `:=` in data.table to sum the values of two columns in R, ignoring NAs

Solution

Problem

Related problems