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
See Question&Answers more detail:
os