CODEWITHSUNDEEP
rdplyr
johnlee14
johnlee14

Reputation: 65

R dplyr - running total with row-wise calculations

I have a dataframe that keeps track of the activities associated with a bank account (example below).

The initial balance is $5,000 (type "initial). If type is "in", that means a cash deposit. In this example each deposit is $1,000. If type is "out", that means a withdrawal from the account. In this example each withdrawal is 10% of the account balance. 

data <- tibble(
  activity=1:6,
  type=c("initial","in","out","out","in","in"),
  input=c(5000,1000,10,10,1000,1000))

Is there a dplyr solution to keep track of the balance after each activity?? I have tried several ways but I can't seem to find a way to efficiently calculate running totals and the withdrawal amount (which depends on the running total).

For this example the output should be:

result <- tibble(
  activity=1:6,
  type=c("initial","in","out","out","in","in"),
  input=c(5000,1000,10,10,1000,1000),
  balance=c(5000,6000,5400,4860,5860,6860))

Thanks in advance for any suggestions or recommendations!

Upvotes: 1

Views: 183

Answers (1)

lroha
lroha

Reputation: 34441

You can use purrr::accumulate2() to condition the calculation on the value of type:

library(dplyr)
library(purrr)
library(tidyr)

data %>%
  mutate(balance = accumulate2(input, type[-1], .f = function(x, y, type) if(type == "out") x - x * y/100 else x + y)) %>%
  unnest(balance) 

# A tibble: 6 x 4
  activity type    input balance
     <int> <chr>   <dbl>   <dbl>
1        1 initial  5000    5000
2        2 in       1000    6000
3        3 out        10    5400
4        4 out        10    4860
5        5 in       1000    5860
6        6 in       1000    6860

Upvotes: 2

Related Questions