Conditional rowwise sumif

Question

Having this tibble:

col1 <- c(1,0,1)
col2 <- c(0,1,0)
col3 <- c(1,0,1)
val <- c('a','b','c')
data <- data.frame(col1,col2,col3,val)

How can I get a rowwise sumif based on conditional val column to get this tibble:

    id col1 col2 col3 val
     1  1     0   1    a 
     2  0     1   0    b
     3  1     0   1    a

 Sum_a  2     0   2   
 Sum_b  1     0   1

Your data is inconsistent ... is it `c('a','b','a')` or `c('a','b','c')`? — r2evans, Oct 09 '19 at 17:28

score 1 · Answer 1 · answered Oct 09 '19 at 17:27

Here is one option where we group by 'val', get the sum of all columns with summarise_all, create the 'id' column by pasteing the 'Sum_' with the 'val', remove the val column and bind the dataset rowwise with the original dataset and

library(dplyr)
data %>%
   group_by(val) %>%
   summarise_all(sum) %>%
   mutate(id = str_c("Sum_", val)) %>%
   select(-val) %>% 
   bind_rows(data, .) %>%
   select(id, names(data)) %>%
   mutate(id = case_when(is.na(id) ~ as.character(row_number()), TRUE ~ id))
#    id col1 col2 col3  val
#1     1    1    0    1    a
#2     2    0    1    0    b
#3     3    1    0    1    a
#4 Sum_a    2    0    2 <NA>
#5 Sum_b    0    1    0 <NA>

Conditional rowwise sumif

1 Answers1