CODEWITHSUNDEEP
rdataframedplyrcount
Zizou
Zizou

Reputation: 503

Sum rows in a column based on a condition in R

I would like to sum up the rows in Col1 for which the Col2 is equal to 0. And add the sum to the first value after the zeros. I show an example below. I have different products in the data frame.

enter image description here

Date <- seq(as.Date("2021-01-01"), as.Date("2021-01-07"), by = "day")
Product<-rep("A",7)
Col1 <- c(2, 5, 1, 3, 2, 1, 2)
Col2 <- c(40, 0, 0, 0, 0, 0, 50)
Expected <- c(40, 0, 0, 0, 0, 0, 62)
TD <- data.frame(Date, Product, Col1, Col2, Expected)

Upvotes: 1

Views: 833

Answers (2)

Karthik S
Karthik S

Reputation: 11546

Will this work: I've grouped by Product in case your actual data has such structure.

library(dplyr)
TD %>% group_by(Product) %>% mutate(sumval = sum(Col1[Col2 == 0])[1]) %>% 
mutate(Expected = Col2) %>%  
mutate(Expected = case_when((Col2 != 0 & lag(Col2) == 0) ~  Expected + sumval, TRUE ~ Expected )) %>% 
select(-sumval)
# A tibble: 7 x 5
# Groups:   Product [1]
  Date       Product  Col1  Col2 Expected
  <date>     <chr>   <dbl> <dbl>    <dbl>
1 2021-01-01 A           2    40       40
2 2021-01-02 A           5     0        0
3 2021-01-03 A           1     0        0
4 2021-01-04 A           3     0        0
5 2021-01-05 A           2     0        0
6 2021-01-06 A           1     0        0
7 2021-01-07 A           2    50       62

Upvotes: 1

tmfmnk
tmfmnk

Reputation: 40171

One dplyr option could be:

TD %>%
 group_by(Product) %>%
 mutate(Expected = if_else(row_number() == which(Col2 != 0 & lag(Col2, default = first(Col2)) == 0),
                           Col2 + sum(Col1[Col2 == 0]),
                           Col2))

  Date       Product  Col1  Col2 Expected
  <date>     <fct>   <dbl> <dbl>    <dbl>
1 2021-01-01 A           2    40       40
2 2021-01-02 A           5     0        0
3 2021-01-03 A           1     0        0
4 2021-01-04 A           3     0        0
5 2021-01-05 A           2     0        0
6 2021-01-06 A           1     0        0
7 2021-01-07 A           2    50       62

Upvotes: 1

Related Questions