I have the following dataframe and would like to calculate the sum of column "toSum" for all rows within the group ("id") for which another column ("index") has smaller values than the current row.
id <- c("A", "A", "A", "B", "B", "B")
index <- c(5,7,11,2,5,8)
toSum <- c(0.5,0.4,0.2,0.1,0.9,0.8)
data <- data.frame(id, index, toSum)
| id | index | toSum |
|---|---|---|
| A | 5 | 0.5 |
| A | 7 | 0.4 |
| A | 11 | 0.2 |
| B | 2 | 0.1 |
| B | 5 | 0.9 |
| B | 8 | 0.8 |
I would like to add the results as a column like this:
| id | index | toSum | priorSum |
|---|---|---|---|
| A | 5 | 0.5 | 0 |
| A | 7 | 0.4 | 0.5 |
| A | 11 | 0.2 | 0.9 |
| B | 2 | 0.1 | 0 |
| B | 5 | 0.9 | 0.1 |
| B | 8 | 0.8 | 1 |
I am able to calculate the number of rows within the group for which the value is lower with this code: data <- data %>% group_by(id) %>% mutate(priorSum = map_int(index, ~ sum(.x > index)))
However, I cannot sum over a different variable.
Thank you very much for your help!
