dataframe - Calculations between two columns in a data frame in R

Question

I want to create a new expected column based on two other columns. A new column is created by adding the value from column const and subtracting the value from column value.

My data:

df<-data.frame(product = rep(c('A','B'),each=4), data = seq(as.Date("2020-01-01"), as.Date("2020-01-04"), by = "day"),
               value = c(10, 15, 0, 5, 20, 5, 10, 0), const = c(100, 0, 10, 0, 100, 0, 0, 10), 
               expected = c(90, 75, 85, 80, 80, 75, 65, 75))

> df
  product       data value const expected
1       A 2020-01-01    10   100       90
2       A 2020-01-02    15     0       75
3       A 2020-01-03     0    10       85
4       A 2020-01-04     5     0       80
5       B 2020-01-01    20   100       80
6       B 2020-01-02     5     0       75
7       B 2020-01-03    10     0       65
8       B 2020-01-04     0    10       75

Edit data:

TD<-data.frame(product = rep("A",4), data = seq(as.Date("2020-01-01"), as.Date("2020-01-04"), by = "day"),
               value = c(15, 1, 2, 1, 0), value2 = c(10, 0, 10, 0, 100))

TD <- TD %>% group_by(product) %>%  mutate(expected1 = cumsum(value2) - cumsum(value))

TD
  product data       value value2 expected1
  <fct>   <date>     <dbl>  <dbl>     <dbl>
1 A       2020-01-01    15     10        -5
2 A       2020-01-02     1      0        -6
3 A       2020-01-03     2     10         2
4 A       2020-01-04     1      0         1
5 A       2020-01-05     0    100       101

TD_expected
 product       data value value2 expected1
1       A 2020-01-01    15     10        -5
2       A 2020-01-02     1      0        -6
3       A 2020-01-03     2     10         8
4       A 2020-01-04     1      0         7
5       A 2020-01-05     0    100       107

NOTE: When the value2 is greater than the value1, we assign the value2 to the expected

Answer 1

You can use ave and cumsum.

df$expected <- ave(df$const - df$value, df$product, FUN=cumsum)
df
#  product       data value const expected
#1       A 2020-01-01    10   100       90
#2       A 2020-01-02    15     0       75
#3       A 2020-01-03     0    10       85
#4       A 2020-01-04     5     0       80
#5       B 2020-01-01    20   100       80
#6       B 2020-01-02     5     0       75
#7       B 2020-01-03    10     0       65
#8       B 2020-01-04     0    10       75