[Solved] Calculations between two columns in a data frame in R

by


Here is a solution with base R, where ave() and cumsum() are applied to get the expected:

  • For original data df:
dfs <- split(df,df$product)
df <- Reduce(rbind,lapply(dfs, function(x) {
  within(x, expected <- ave(const-value,
                             ave(const-value,
                                 cumsum(const>value),FUN = cumsum)>0,FUN = cumsum))
}))

such that

> 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
  • For edited data DT:
    you can use 
TDs <- split(TD,TD$product)
TD <- Reduce(rbind,lapply(dfs, function(x) {
  within(x, expected <- ave(value2-value,
                             ave(value2-value,
                                 cumsum(value2>value),FUN = cumsum)>0,FUN = cumsum))
}))

such that 

> TD
  product       data value value2 expected
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

8

solved Calculations between two columns in a data frame in R