r nested sum by groups

September 21, 2022

I am trying to implement this logic:

  i   j   Eij
  1   1   -1.5
  1   2   -1.5
  1   3    0.5
  1   4    0.5
  2   1   -0.5
  2   2   -0.5
  2   3    1.5
  2   4    1.5

Each value in column Eij , by i, should be multiplied by the sum of values after that value.

For example, for i=1, the first value -1.5 (i=1, j=1). This value -1.5 should be multiplied by the sum of -1.5, 0.5, 0.5 all these values occur after the initial value -1.5 (i=1, j=1)

Similarly next value in i=1, i.e -1.5(i=1, j=2). This value -1.5 should be multiplied by the sum of 0.5, 0.5 all these values occur after the value -1.5 (i=1, j=2)

So on.

 ro = -1.5*(-1.5 + 0.5 + 0.5) + 
          -1.5*(0.5 + 0.5) + 
            0.5*(0.5) +
      -0.5*(-0.5 + 1.5 + 1.5) + 
          -0.5*(1.5 + 1.5) + 
            1.5*(1.5)

>Solution :

Here’s a solution with dplyr:

library(dplyr)
df %>%
  group_by(i) %>%
  arrange(desc(j), .by_group = TRUE) %>%
  mutate(
    multiplier = lag(cumsum(Eij), default = 0),
    result = Eij * multiplier
  ) %>%
  arrange(j, .by_group = TRUE) %>%
  ungroup
# # A tibble: 8 × 5
#       i     j   Eij multiplier result
#   <int> <int> <dbl>      <dbl>  <dbl>
# 1     1     1  -1.5       -0.5   0.75
# 2     1     2  -1.5        1    -1.5 
# 3     1     3   0.5        0.5   0.25
# 4     1     4   0.5        0     0   
# 5     2     1  -0.5        2.5  -1.25
# 6     2     2  -0.5        3    -1.5 
# 7     2     3   1.5        1.5   2.25
# 8     2     4   1.5        0     0