How Should i calculate the balance Using R code

Question

   ID   Type    Type_Description    Amount  Balance
41  85  incoming transaction        100.0    100.0
41  55  outgoing transaction         76.6     23.4
41  55  outgoing transaction         23.4      0
41  90  incoming transaction         24.1     24.1
41  55  outgoing transaction         14.19     9.91
41  63  Sweep Off Amount              9.91     0
42  85  incoming transaction        100.0     100
42  55  outgoing transaction         76.6     23.4
42  55  outgoing transaction         23.4      0
42  90  incoming transaction         24.1     24.1
42  55  outgoing transaction         14.19    9.91
42  63  Sweep Off Amount              9.91    0

Answer 1

New answer: You can do same operation group and take into account for the different type of transactions as follows:

df1 <- transform(df1, Amount = Amount*c(-1,1,0)[(Type %in% c(85,90)) + 1L])
df1 <- transform(df1, Balance = ave(df1$Amount, df1$ID, FUN = function(x) round(cumsum(x),2)))

Or:

library(dlyr)
df1 %>%
  group_by(ID) %>%
  mutate(Amount = Amount*c(-1,1)[(Type %in% c(85,90)) + 1L],
         Balance = round(cumsum(Amount),2))

gives:

Source: local data frame [12 x 5]
Groups: ID [2]

      ID  Type     Type_Description Amount Balance
   (int) (int)               (fctr)  (dbl)   (dbl)
1     41    85 incoming_transaction 100.00  100.00
2     41    55 outgoing_transaction -76.60   23.40
3     41    55 outgoing_transaction -23.40    0.00
4     41    90 incoming_transaction  24.10   24.10
5     41    55 outgoing_transaction -14.19    9.91
6     41    63     Sweep_Off_Amount  -9.91    0.00
7     42    85 incoming_transaction 100.00  100.00
8     42    55 outgoing_transaction -76.60   23.40
9     42    55 outgoing_transaction -23.40    0.00
10    42    90 incoming_transaction  24.10   24.10
11    42    55 outgoing_transaction -14.19    9.91
12    42    63     Sweep_Off_Amount  -9.91    0.00

With data.table you can do:

library(data.table)
setDT(df1)[, Amount := Amount*c(-1,1)[(Type %in% c(85,90)) + 1L]
           ][, Balance := round(cumsum(Amount),2), by = ID][]

---

Old answer: If I understand you correctly, you want to calculate the Balance from Trs_Amount where Transaction_Type==85 denotes an incoming transaction and Transaction_Type==55 an outgoing transaction. To achieve that, you could use a conditional cumsum as follows:

mydf <- transform(mydf, Balance = round(cumsum(ifelse(Transaction_Type==85,
                                                      Trs_Amount, 
                                                      -1*Trs_Amount)),2))

which gives:

> mydf
   ID Transaction_Type Trs_Amount Balance
1 121               85     100.00  100.00
2 121               55      21.52   78.48
3 121               55      36.01   42.47
4 121               55      15.57   26.90
5 121               55       2.02   24.88
6 121               55      23.49    1.39
7 121               55       1.39    0.00

---

With the dplyr package you could do:

library(dplyr)
mydf %>%
  mutate(Balance = round(cumsum(ifelse(Transaction_Type==85,
                                       Trs_Amount, 
                                       -1*Trs_Amount)),
                         2))

which gives the same result. A slightly alternative implementation would be:

mydf %>%
  mutate(Trs_Amount = Trs_Amount*c(-1,1)[(Transaction_Type==85) + 1L],
         Balance = round(cumsum(Trs_Amount),2))

which gives:

   ID Transaction_Type Trs_Amount Balance
1 121               85     100.00  100.00
2 121               55     -21.52   78.48
3 121               55     -36.01   42.47
4 121               55     -15.57   26.90
5 121               55      -2.02   24.88
6 121               55     -23.49    1.39
7 121               55      -1.39    0.00