How to use rowSums() in "dplyr" when including missing data?

Question

I want to use the function rowSums in dplyr and came across some difficulties with missing data. The example data is mtcars. The objective is to estimate the sum of three variables of mpg, cyl and disp by row. However, the results seems incorrect with the following R code when there are missing values within a specific row (see variable new1 and new2 in the output). Any comments and suggestions are appreciated!

data<-mtcars%>%
mutate(
  mpg=case_when(mpg>25~NA_real_,TRUE~as.numeric(mpg)), # generate missing data in "mpg"
  new1=rowSums(.[c("mpg","cyl","disp")],na.rm=FALSE), # method1: row sum, treat NA as NA?
  new2=rowSums(.[c("mpg","cyl","disp")],na.rm=TRUE), # method2: row sum, treat NA as zero?
  new3=mpg+cyl+disp # method3: row sum, by hand
    )

data

The output is listed below:

    mpg cyl  disp  hp drat    wt  qsec vs am gear carb  new1  new2  new3
1  21.0   6 160.0 110 3.90 2.620 16.46  0  1    4    4 187.0 187.0 187.0
2  21.0   6 160.0 110 3.90 2.875 17.02  0  1    4    4 187.0 187.0 187.0
3  22.8   4 108.0  93 3.85 2.320 18.61  1  1    4    1 134.8 134.8 134.8
4  21.4   6 258.0 110 3.08 3.215 19.44  1  0    3    1 285.4 285.4 285.4
5  18.7   8 360.0 175 3.15 3.440 17.02  0  0    3    2 386.7 386.7 386.7
6  18.1   6 225.0 105 2.76 3.460 20.22  1  0    3    1 249.1 249.1 249.1
7  14.3   8 360.0 245 3.21 3.570 15.84  0  0    3    4 382.3 382.3 382.3
8  24.4   4 146.7  62 3.69 3.190 20.00  1  0    4    2 175.1 175.1 175.1
9  22.8   4 140.8  95 3.92 3.150 22.90  1  0    4    2 167.6 167.6 167.6
10 19.2   6 167.6 123 3.92 3.440 18.30  1  0    4    4 192.8 192.8 192.8
11 17.8   6 167.6 123 3.92 3.440 18.90  1  0    4    4 191.4 191.4 191.4
12 16.4   8 275.8 180 3.07 4.070 17.40  0  0    3    3 300.2 300.2 300.2
13 17.3   8 275.8 180 3.07 3.730 17.60  0  0    3    3 301.1 301.1 301.1
14 15.2   8 275.8 180 3.07 3.780 18.00  0  0    3    3 299.0 299.0 299.0
15 10.4   8 472.0 205 2.93 5.250 17.98  0  0    3    4 490.4 490.4 490.4
16 10.4   8 460.0 215 3.00 5.424 17.82  0  0    3    4 478.4 478.4 478.4
17 14.7   8 440.0 230 3.23 5.345 17.42  0  0    3    4 462.7 462.7 462.7
18   NA   4  78.7  66 4.08 2.200 19.47  1  1    4    1 115.1 115.1    NA
19   NA   4  75.7  52 4.93 1.615 18.52  1  1    4    2 110.1 110.1    NA
20   NA   4  71.1  65 4.22 1.835 19.90  1  1    4    1 109.0 109.0    NA
21 21.5   4 120.1  97 3.70 2.465 20.01  1  0    3    1 145.6 145.6 145.6
22 15.5   8 318.0 150 2.76 3.520 16.87  0  0    3    2 341.5 341.5 341.5
23 15.2   8 304.0 150 3.15 3.435 17.30  0  0    3    2 327.2 327.2 327.2
24 13.3   8 350.0 245 3.73 3.840 15.41  0  0    3    4 371.3 371.3 371.3
25 19.2   8 400.0 175 3.08 3.845 17.05  0  0    3    2 427.2 427.2 427.2
26   NA   4  79.0  66 4.08 1.935 18.90  1  1    4    1 110.3 110.3    NA
27   NA   4 120.3  91 4.43 2.140 16.70  0  1    5    2 150.3 150.3    NA
28   NA   4  95.1 113 3.77 1.513 16.90  1  1    5    2 129.5 129.5    NA
29 15.8   8 351.0 264 4.22 3.170 14.50  0  1    5    4 374.8 374.8 374.8
30 19.7   6 145.0 175 3.62 2.770 15.50  0  1    5    6 170.7 170.7 170.7
31 15.0   8 301.0 335 3.54 3.570 14.60  0  1    5    8 324.0 324.0 324.0
32 21.4   4 121.0 109 4.11 2.780 18.60  1  1    4    2 146.4 146.4 146.4

user_7465462 · Answer

The results are not what you expect because the insertion of NAs in mpg is in the same mutate statement as variables new*; hence, R still considers the previous values that were in variable mpg. By using another mutate step before, we manage to obtain the desided results

mtcars %>%
  mutate(
    mpg = case_when(mpg > 25 ~ NA_real_, TRUE ~ as.numeric(mpg)) # generate missing data in "mpg"
  ) %>% 
  mutate(
    new1 = rowSums(.[c("mpg","cyl","disp")], na.rm = FALSE), # method1: row sum, treat NA as NA?
    new2 = rowSums(.[c("mpg","cyl","disp")], na.rm = TRUE), # method2: row sum, treat NA as zero?
    new3 = mpg + cyl + disp # method3: row sum, by hand
  )

Output

    mpg cyl  disp  hp drat    wt  qsec vs am gear carb  new1  new2  new3
1  21.0   6 160.0 110 3.90 2.620 16.46  0  1    4    4 187.0 187.0 187.0
2  21.0   6 160.0 110 3.90 2.875 17.02  0  1    4    4 187.0 187.0 187.0
3  22.8   4 108.0  93 3.85 2.320 18.61  1  1    4    1 134.8 134.8 134.8
4  21.4   6 258.0 110 3.08 3.215 19.44  1  0    3    1 285.4 285.4 285.4
5  18.7   8 360.0 175 3.15 3.440 17.02  0  0    3    2 386.7 386.7 386.7
6  18.1   6 225.0 105 2.76 3.460 20.22  1  0    3    1 249.1 249.1 249.1
7  14.3   8 360.0 245 3.21 3.570 15.84  0  0    3    4 382.3 382.3 382.3
8  24.4   4 146.7  62 3.69 3.190 20.00  1  0    4    2 175.1 175.1 175.1
9  22.8   4 140.8  95 3.92 3.150 22.90  1  0    4    2 167.6 167.6 167.6
10 19.2   6 167.6 123 3.92 3.440 18.30  1  0    4    4 192.8 192.8 192.8
11 17.8   6 167.6 123 3.92 3.440 18.90  1  0    4    4 191.4 191.4 191.4
12 16.4   8 275.8 180 3.07 4.070 17.40  0  0    3    3 300.2 300.2 300.2
13 17.3   8 275.8 180 3.07 3.730 17.60  0  0    3    3 301.1 301.1 301.1
14 15.2   8 275.8 180 3.07 3.780 18.00  0  0    3    3 299.0 299.0 299.0
15 10.4   8 472.0 205 2.93 5.250 17.98  0  0    3    4 490.4 490.4 490.4
16 10.4   8 460.0 215 3.00 5.424 17.82  0  0    3    4 478.4 478.4 478.4
17 14.7   8 440.0 230 3.23 5.345 17.42  0  0    3    4 462.7 462.7 462.7
18   NA   4  78.7  66 4.08 2.200 19.47  1  1    4    1    NA  82.7    NA
19   NA   4  75.7  52 4.93 1.615 18.52  1  1    4    2    NA  79.7    NA
20   NA   4  71.1  65 4.22 1.835 19.90  1  1    4    1    NA  75.1    NA
21 21.5   4 120.1  97 3.70 2.465 20.01  1  0    3    1 145.6 145.6 145.6
22 15.5   8 318.0 150 2.76 3.520 16.87  0  0    3    2 341.5 341.5 341.5
23 15.2   8 304.0 150 3.15 3.435 17.30  0  0    3    2 327.2 327.2 327.2
24 13.3   8 350.0 245 3.73 3.840 15.41  0  0    3    4 371.3 371.3 371.3
25 19.2   8 400.0 175 3.08 3.845 17.05  0  0    3    2 427.2 427.2 427.2
26   NA   4  79.0  66 4.08 1.935 18.90  1  1    4    1    NA  83.0    NA
27   NA   4 120.3  91 4.43 2.140 16.70  0  1    5    2    NA 124.3    NA
28   NA   4  95.1 113 3.77 1.513 16.90  1  1    5    2    NA  99.1    NA
29 15.8   8 351.0 264 4.22 3.170 14.50  0  1    5    4 374.8 374.8 374.8
30 19.7   6 145.0 175 3.62 2.770 15.50  0  1    5    6 170.7 170.7 170.7
31 15.0   8 301.0 335 3.54 3.570 14.60  0  1    5    8 324.0 324.0 324.0
32 21.4   4 121.0 109 4.11 2.780 18.60  1  1    4    2 146.4 146.4 146.4

user_5668968 · Answer

did you try cbind in place of c:

data<-mtcars%>%
  mutate(
    mpg=case_when(mpg>25~NA_real_,TRUE~as.numeric(mpg)), # generate missing data in "mpg"
    new1=rowSums(cbind(mpg,cyl,disp),na.rm=FALSE), # method1: row sum, treat NA as NA?
    new2=rowSums(cbind(mpg,cyl,disp),na.rm=TRUE), # method2: row sum, treat NA as zero?
    new3=mpg+cyl+disp # method3: row sum, by hand
  )

data seems what you'd expect:

    mpg cyl  disp  hp drat    wt  qsec vs am gear carb  new1  new2  new3
1  21.0   6 160.0 110 3.90 2.620 16.46  0  1    4    4 187.0 187.0 187.0
2  21.0   6 160.0 110 3.90 2.875 17.02  0  1    4    4 187.0 187.0 187.0
3  22.8   4 108.0  93 3.85 2.320 18.61  1  1    4    1 134.8 134.8 134.8
4  21.4   6 258.0 110 3.08 3.215 19.44  1  0    3    1 285.4 285.4 285.4
5  18.7   8 360.0 175 3.15 3.440 17.02  0  0    3    2 386.7 386.7 386.7
6  18.1   6 225.0 105 2.76 3.460 20.22  1  0    3    1 249.1 249.1 249.1
7  14.3   8 360.0 245 3.21 3.570 15.84  0  0    3    4 382.3 382.3 382.3
8  24.4   4 146.7  62 3.69 3.190 20.00  1  0    4    2 175.1 175.1 175.1
9  22.8   4 140.8  95 3.92 3.150 22.90  1  0    4    2 167.6 167.6 167.6
10 19.2   6 167.6 123 3.92 3.440 18.30  1  0    4    4 192.8 192.8 192.8
11 17.8   6 167.6 123 3.92 3.440 18.90  1  0    4    4 191.4 191.4 191.4
12 16.4   8 275.8 180 3.07 4.070 17.40  0  0    3    3 300.2 300.2 300.2
13 17.3   8 275.8 180 3.07 3.730 17.60  0  0    3    3 301.1 301.1 301.1
14 15.2   8 275.8 180 3.07 3.780 18.00  0  0    3    3 299.0 299.0 299.0
15 10.4   8 472.0 205 2.93 5.250 17.98  0  0    3    4 490.4 490.4 490.4
16 10.4   8 460.0 215 3.00 5.424 17.82  0  0    3    4 478.4 478.4 478.4
17 14.7   8 440.0 230 3.23 5.345 17.42  0  0    3    4 462.7 462.7 462.7
18   NA   4  78.7  66 4.08 2.200 19.47  1  1    4    1    NA  82.7    NA
19   NA   4  75.7  52 4.93 1.615 18.52  1  1    4    2    NA  79.7    NA
20   NA   4  71.1  65 4.22 1.835 19.90  1  1    4    1    NA  75.1    NA
21 21.5   4 120.1  97 3.70 2.465 20.01  1  0    3    1 145.6 145.6 145.6
22 15.5   8 318.0 150 2.76 3.520 16.87  0  0    3    2 341.5 341.5 341.5
23 15.2   8 304.0 150 3.15 3.435 17.30  0  0    3    2 327.2 327.2 327.2
24 13.3   8 350.0 245 3.73 3.840 15.41  0  0    3    4 371.3 371.3 371.3
25 19.2   8 400.0 175 3.08 3.845 17.05  0  0    3    2 427.2 427.2 427.2
26   NA   4  79.0  66 4.08 1.935 18.90  1  1    4    1    NA  83.0    NA
27   NA   4 120.3  91 4.43 2.140 16.70  0  1    5    2    NA 124.3    NA
28   NA   4  95.1 113 3.77 1.513 16.90  1  1    5    2    NA  99.1    NA
29 15.8   8 351.0 264 4.22 3.170 14.50  0  1    5    4 374.8 374.8 374.8
30 19.7   6 145.0 175 3.62 2.770 15.50  0  1    5    6 170.7 170.7 170.7
31 15.0   8 301.0 335 3.54 3.570 14.60  0  1    5    8 324.0 324.0 324.0
32 21.4   4 121.0 109 4.11 2.780 18.60  1  1    4    2 146.4 146.4 146.4

How to use rowSums() in "dplyr" when including missing data?

2 Answers