dplyr::mutate (assign na.rm =TRUE)

Question

I have a data.frame that has 100 variables. I want to get the sum of three variables only using mutate (not summarise).

If there is NA in any of the 3 variables, I still want to get the sum. In order to do this using mutate, I replaced all NA values with 0 using ifelse then I got the sum.

library(dplyr)
df %>% mutate(mod_var1 = ifelse(is.na(var1), 0, var1),
              mod_var2 = ifelse(is.na(var2), 0, var2),
              mod_var3 = ifelse(is.na(var3), 0, var3),
              sum = (mod_var1+mod_var2+mod_var3))

Is there any better (shorter) way to do this?

DATA

df <- read.table(text = c("
var1    var2    var3
4   5   NA
2   NA  3
1   2   4
NA  3   5
3   NA  2
1   1   5"), header =T)

`replace(df, is.na(df), 0) %>% mutate(sum = var1 + var2 + var3)` — Rich Scriven, Jan 08 '17 at 06:18
@RichScriven Your answer is great. However, as I mentioned in the question I have a data.frame that have 100 variables and your answer will replace NA in all 100 variables with zero (not only var1,var2, and var3) which I don't want — shiny, Jan 08 '17 at 23:32

score 10 · Answer 1 · answered Jan 08 '17 at 06:58

10

rowwise() is my go-to function. It's like group_by() but it treats each row as an individual group.

df %>% rowwise() %>% mutate(Sum = sum(c(var1, var2, var3), na.rm = TRUE))

answered Jan 08 '17 at 06:58

Phil

7,287
3
36
66

Many thanks for your time and help. rowwise is great but it takes long time to get the result especially I have 1.3 million rows. – shiny Jan 08 '17 at 07:41

akrun · Accepted Answer · 2017-01-08T07:13:52.133

We can use Reduce with +

df %>% 
     mutate_each(funs(replace(., is.na(.), 0)), var1:var3) %>% 
     mutate(Sum = Reduce(`+`, .))      
#   var1 var2 var3 Sum
#1    4    5    0   9
#2    2    0    3   5
#3    1    2    4   7
#4    0    3    5   8
#5    3    0    2   5
#6    1    1    5   7

Or with rowSums

df %>% 
   mutate(Sum = rowSums(.[names(.)[1:3]], na.rm = TRUE))
#   var1 var2 var3 Sum
#1    4    5   NA   9
#2    2   NA    3   5
#3    1    2    4   7
#4   NA    3    5   8
#5    3   NA    2   5
#6    1    1    5   7

Benchmarks

set.seed(24)
df1 <- as.data.frame(matrix(sample(c(NA, 1:5), 1e6 *3, replace=TRUE),
                dimnames = list(NULL, paste0("var", 1:3)), ncol=3))
system.time({
df1 %>% rowwise() %>% mutate(Sum = sum(c(var1, var2, var3), na.rm = TRUE))
})
# user  system elapsed 
#  21.50    0.03   21.66 

system.time({
df1 %>%
    mutate(rn = row_number()) %>%
    gather(var, varNum, var1:var3) %>%
    group_by(rn) %>%
    mutate(sum = sum(varNum, na.rm = TRUE)) %>% 
    spread(var, varNum)})
 # user  system elapsed 
 #  5.96    0.39    6.37 


system.time({
replace(df1, is.na(df1), 0) %>% mutate(sum = var1 + var2 + var3)
})

# user  system elapsed 
#   0.17    0.01    0.19 

system.time({
df1 %>% 
     mutate_each(funs(replace(., is.na(.), 0)), var1:var3) %>% 
     mutate(Sum = Reduce(`+`, .))      
})
# user  system elapsed 
#   0.10    0.02    0.11 

system.time({
df1 %>% 
   mutate(Sum = rowSums(.[names(.)[1:3]], na.rm = TRUE))
   })
# user  system elapsed 
#   0.04    0.00    0.03

Many thanks for your time and help. It seems from your answer that rowSums is the best and fastest way to do it. However, as I mentioned in the question the data.frame has 100 variables not only 3 variables and these 3 variables (var1 to var3) have different names and the are far away from each other like (column 3, 7 and 76). Is there anyway to have the variable name instead of 1:3 in rowSums? — shiny, Jan 08 '17 at 07:45
@aelwan If you know the positions of those variables, you can use the `names(df)[c(3, 7, 76)]` or you can use the actual column names i.e. `rowSums(.[c("somename", "another", "var5")]` — akrun, Jan 08 '17 at 07:47

score 2 · Answer 3 · answered Jan 08 '17 at 07:11

2

Where better = tidyr:

df %>%
    mutate(rn = row_number()) %>%
    gather(var, varNum, var1:var3) %>%
    group_by(rn) %>%
    mutate(sum = sum(varNum, na.rm = TRUE)) %>% 
    spread(var, varNum)

In case your dataset is poised to grow...

answered Jan 08 '17 at 07:11

leerssej

14,260
6
48
57

dplyr::mutate (assign na.rm =TRUE)

3 Answers3

Benchmarks

Linked