Do loops by group in R

Question

Newbie question. I have a dataset that I am using to calculate some accounting values. The dataset looks like this


    ID   Period   Amount Interest O_Bal   C_Bal
    1     1        1234    0.07     1234  (1234+(1234*0.07)) = 1321
    1     2        1244    0.07     1321  (1321-1234+(1321*0.07))
    1     3           .....
    2     1            ......

...

Please ignore the actual values in the table. What I have at the moment is the first row for each ID. I need to create a loop for each ID group so that from period 2 onwards, it calculates the closing balance as per the formula in the second row. Any ideas?

I have tried the following

   fin_calc$C_Bal = fin_calc$O_Bal + (fin_calc$O_Bal*0.07)

if (fin_calc$Period==1) {
  fin_calc$Cl_Bal = fin_calc$C_Bal
}

fin_c <- fin_calc %>%
  group_by(ID) %>%
  mutate(Cl_Bal = lag(Cl_Bal) - Amount_PA + (lag(Cl_Bal) - Amount_PA)*0.07)

What this is doing is its able to continue from period 1 to period 2 but then subsequent periods are calculated incorrectly

 Data <- data.frame(
    ID = 1,
    Period = c(1,2,3),
    Amount_PA = c(0,2946737,3035139),
    O_Bal = c(30979981.5,0,0),
    C_Bal = c(33148580.2,0,0),
    Cl_Bal = c(33148580.2,32221382.2,-3345026.6)

  )

[See here](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) on making an R question that folks can help with. That includes a sample of data we can work with, all necessary code, and a clear explanation of what you're trying to do and what hasn't worked. — camille, Apr 16 '19 at 13:23
Ok. Updated. Hopefully this is the suitable format for data. — user2641784, Apr 16 '19 at 14:33

Aaron Parrilla · Answer 1 · 2019-04-16T15:02:25.187

0

If, like you say, you have the first row of all ID's and all ID's follow that exact same pattern, maybe you could try:

library(dplyr)
df%>%
group_By(ID)%>%
mutate(Closing_Bal = ifelse(is.na(lag(Opening_Bal),
Opening_Bal + (Opening_bal*Interest),
Opening_Bal - lag(Opening_Bal) + (Opening_bal*Interest))))

EDIT:

df <- df %>%
  group_by(ID)%>%
  mutate(Cl_Bal = lag(Cl_Bal) - Amount + (lag(Cl_Bal) - Amount)*0.07) %>% 
  mutate(Cl_Bal = ifelse(Cl_Bal < 0,lag(Cl_Bal) - Amount + (lag(Cl_Bal) - Amount)*0.07,Cl_Bal))

edited Apr 16 '19 at 15:02

answered Apr 16 '19 at 12:41

Aaron Parrilla

522
3
13

Thanks for this Aaron. I am still struggling to make it work. Have updated my original question to show the issue. – user2641784 Apr 16 '19 at 14:00
@user2641784 I think the problem comes from the fact that the column you're basing your calculations on doesn't exist at the moment of calculation (The value it's picking is 0) which gives the wrong result – Aaron Parrilla Apr 16 '19 at 14:51
Thanks @Aaron. But that still did not do the trick. I have 2 periods being calculated correctly but then the subsequent periods are coming as negative – user2641784 Apr 16 '19 at 21:23

Do loops by group in R

1 Answers1