dplyr row sum on selected rows

Question

I have the following data:

library(dplyr)
library(purrr)

 d <- data.frame(
  Type= c("d", "e", "d", "e"),
  "2000"= c(1, 5, 1, 5),
  "2001" = c(2, 5 , 6, 4),
  "2002" = c(8, 9, 6, 3))

I would like to use rowsum and mutate to generate a new row which is the sum of 'd' and another row which is the sum of 'e' so that the data looks like this:

d2 <- data.frame(
Type= c("d", "e", "d", "e", "sum_of_d", "Sum_of_e"),
"2000"= c(1, 5, 1, 5, 2, 10),
"2001" = c(2, 5 , 6, 4, 8, 9),
"2002" = c(8, 9, 6, 3, 14, 12))

I think the code should look something like this:

d %>% 
 dplyr::mutate(sum_of_d = rowSums(d[1,3], na.rm = TRUE)) %>% 
 dplyr::mutate(sum_of_e = rowSums(d[2,4], na.rm = TRUE)) -> d2

however this does not quite work. Any ideas?

Thanks

I guess you need: `rowSums(.[c(1,3)], na.rm = TRUE)` and `rowSums(.[c(2,4)], na.rm = TRUE)`? — Jaap, Nov 26 '18 at 14:13

score 5 · Answer 1 · answered Nov 26 '18 at 14:15

5

You're looking for the sum by Type across all other columns, so..

library(dplyr)
d %>% 
   group_by(Type) %>% 
   summarise_all(sum) %>% 
   mutate(Type = paste0("sum_of_", Type)) %>% 
   rbind(d, .)

      Type X2000 X2001 X2002
1        d     1     2     8
2        e     5     5     9
3        d     1     6     6
4        e     5     4     3
5 sum_of_d     2     8    14
6 sum_of_e    10     9    12

answered Nov 26 '18 at 14:15

talat

68,970
21
126
157

2

LOL - I just posted the exact same solution... – iod Nov 26 '18 at 14:17
Yeah that's prefect, thanks - I've not used summarise_all before so that'll be really useful – Mrmoleje Nov 26 '18 at 14:17

score 0 · Answer 2 · answered Nov 26 '18 at 14:16

d %>% 
  group_by(Type) %>% 
  summarize_all(sum) %>% 
   mutate(Type=paste0("sum_of_",Type)) %>% 
   bind_rows(d,.)
      Type X2000 X2001 X2002
1        d     1     2     8
2        e     5     5     9
3        d     1     6     6
4        e     5     4     3
5 sum_of_d     2     8    14
6 sum_of_e    10     9    12

dplyr row sum on selected rows

2 Answers2