dplyr group by RunID carry values to next group

Question

I have data of which I want to group by, perform calculations then the final result, use that for calculations in the next group.

We use conditional statements and perform calculations per group for example:

# Example Data 
condition <- c(0,0,0,1,1,1,0,0,0,1,1,1,1,0,0,0,0,0,0,0,0,1,1,0)
col_a <- c(0,0,0,2,3,4,0,0,0,2,4,5,6,0,0,0,0,0,0,0,0,1,2,0)
col_b <- c(0,0,0,10,131,14,0,0,0,22,64,75,96,0,0,0,0,0,0,0,0,41,52,0)
df <- data.frame(condition,col_a,col_b)

This is the code to do the calculations, group by RunID

# Group by RunID
# Perform calculations 
# Last value, brought forward to next group
require(dplyr) 
output <- df %>%
  dplyr::mutate(RunID = data.table::rleid(condition)) %>%
  group_by(RunID) %>%
  dplyr::mutate(calculation = ifelse(condition == 0,0, ifelse(row_number() == n(),first(col_a) * last(col_b),0))) %>%

dplyr::mutate(last.tag = ifelse(condition == 0,0, ifelse(row_number() == n(),2,0))) %>% # Add helper ID no. to aid with for loop below in answer ungroup() %>% select(-RunID) output <- data.frame(output) head(output,15)

With the output:

      condition col_a col_b calculation
1          0     0     0           0
2          0     0     0           0
3          0     0     0           0
4          1     2    10           0
5          1     3   131           0
6          1     4    14          28
7          0     0     0           0
8          0     0     0           0
9          0     0     0           0
10         1     2    22           0
11         1     4    64           0
12         1     5    75           0
13         1     6    96         192
14         0     0     0           0
15         0     0     0           0

What I want to do is. On the first outcome, the result in calculation column is 28. I want to carry that value over to the next group and insert in col_a, row number 10 (28 replaces, 2). Then as that value is updated. The second group calculation result will be 96 * 28 = 2688 versus (96*2 = 192)

The carry forward will always insert on the first row of each group as example above.

Expected output:

      condition col_a col_b calculation
1          0     0     0           0
2          0     0     0           0
3          0     0     0           0
4          1     2    10           0
5          1     3   131           0
6          1     4    14          28
7          0     0     0           0
8          0     0     0           0
9          0     0     0           0
10         1     28    22           0
11         1     4    64           0
12         1     5    75           0
13         1     6    96         2688
14         0     0     0           0
15         0     0     0           0

Other solution:

I subsetted to remove all 0,s. Added a 2 number identified to bottom of each group to make a continuous run, then use a for loop to go through grab and replace. Probably not most elegant but seems to work:

# Subset to remove all 0 
subset.no.zero <- subset(output,condition >0)
# Loop to move values
for (i in 1:nrow(subset.no.zero)) {
  temp <- ifelse(subset.no.zero$last.tag[i-1] == 2, subset.no.zero$calculation[i-1],subset.no.zero$col_a[i])
  subset.no.zero$new_col_a[i] <- data.frame(temp)
}

# Re join by index no.
final_out <- full_join(output,subset.no.zero, by="index")

How did you get `28`? When I run your code I get 20 (rownumber: 6) and 44 (rownumber: 13) — Sotos, Jan 04 '18 at 13:31
Im not sure, I re run and get the same as example. The statement is doing: first(col_a) * last(col_b) so that should be whats happening? — Andrew Bannerman, Jan 04 '18 at 13:34
Ohh..you have an error. You have `first(col_a) * first(col_b)` instead of ... * last()... — Sotos, Jan 04 '18 at 13:36
Did you have a look at [recursive functions](https://stackoverflow.com/questions/5273857/are-recursive-functions-used-in-r)? — Sotos, Jan 04 '18 at 13:38
@Roland You need to get the result of the multiplication and multiply it again with next value. Then that result with the next and so on...Isn't that recursive? or did I completely misunderstood that? — Sotos, Jan 04 '18 at 17:04
@Sotos Well, I guess you could call this recursive in the broader sense, but I don't think anybody would actually use recursion to solve this. Usually, I'd use recursion when each iteration doesn't need substantial outside input. And of course, recursion should be avoided in R if you can't be sure that the nesting doesn't become too deep. — Roland, Jan 05 '18 at 07:11
@Roland, Ahh, ok, I get your point(s). Especially after seeing your answer. — Sotos, Jan 05 '18 at 07:36

score 4 · Accepted Answer · answered Jan 04 '18 at 16:41

I can only offer a data.table solution but perhaps you can translate the logic into dplyr:

library(data.table)
setDT(df)

#first group multiply 2 and 14
df[rleid(condition) %in% 1:2 & condition != 0, 
   calculation := {
     res <- rep(NA_real_, .N)
     res[.N] <- col_b[.N] * col_a[1]
     res
   }
   ]

#all groups other than first copy col_b
df[, calculation := if (condition[.N] != 0) {
  if(is.na(calculation[.N])) {
    res <- rep(NA_real_, .N)
    res[.N] <- col_b[.N]
    res
  } else calculation
} else NA_real_,
by = rleid(condition)
]    

#cumulative product
df[!is.na(calculation), 
   calculation := cumprod(calculation)] 

#copy values into col_a
df[i = df[, .(condition = condition[1], i = .I[1]), 
          by = rleid(condition)][condition == 1L][-1, i], #finds rows to replace values
   col_a := head(df[!is.na(calculation), calculation], -1) 
   ]

#    condition col_a col_b calculation
# 1:         0     0     0          NA
# 2:         0     0     0          NA
# 3:         0     0     0          NA
# 4:         1     2    10          NA
# 5:         1     3   131          NA
# 6:         1     4    14          28
# 7:         0     0     0          NA
# 8:         0     0     0          NA
# 9:         0     0     0          NA
#10:         1    28    22          NA
#11:         1     4    64          NA
#12:         1     5    75          NA
#13:         1     6    96        2688
#14:         0     0     0          NA
#15:         0     0     0          NA
#16:         0     0     0          NA
#17:         0     0     0          NA
#18:         0     0     0          NA
#19:         0     0     0          NA
#20:         0     0     0          NA
#21:         0     0     0          NA
#22:         1  2688    41          NA
#23:         1     2    52      139776
#24:         0     0     0          NA
#    condition col_a col_b calculation

dplyr group by RunID carry values to next group

Other solution:

1 Answers1