Reputation: 503
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.
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
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
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