How to calculate the sum of rows in group only when the condition is met

Question

I have a data frame similar to this:

data.frame(Group1 = c("A", "A", "A", "A"),
           Group2 = c("X", "X", "X", "Y"),
           ValueA = c(20, 40, 50, 80),
           ValueB = c(0, 0, 70, 60))

I want to calculate the sum of rows in ValueA within group by Group1 and Group2, only when value in ValueB is 0.

My expected output is:

data.frame(Group1 = c("A", "A", "A", "A"),
           Group2 = c("X", "X", "X", "Y"),
           ValueA = c(20, 40, 50, 80),
           ValueB = c(0, 0, 70, 60),
           SumA_whenBis0 = c(60, 60, 0, 0))

score 2 · Answer 1 · answered Feb 14 '23 at 09:49

We can use a grouped_by mutate and only sum ValueA when ValueB == 0 by subsetting it sum(ValueA[ValueB == 0]):

library(dplyr)
df <-  data.frame(Group1 = c("A", "A", "A", "A"),
             Group2 = c("X", "X", "X", "Y"),
             ValueA = c(20, 40, 50, 80),
             ValueB = c(0, 0, 70, 60))

df %>% 
  group_by(Group1, Group2) %>% 
  mutate(SumA_whenBis0 = sum(ValueA[ValueB == 0]))

#> # A tibble: 4 x 5
#> # Groups:   Group1, Group2 [2]
#>   Group1 Group2 ValueA ValueB SumA_whenBis0
#>   <chr>  <chr>   <dbl>  <dbl>         <dbl>
#> 1 A      X          20      0            60
#> 2 A      X          40      0            60
#> 3 A      X          50     70            60
#> 4 A      Y          80     60             0

^{Created on 2023-02-14 by the reprex package (v2.0.1)}

How to calculate the sum of rows in group only when the condition is met

1 Answers1