我应该如何使用R代码计算余额

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

新答案：您可以执行相同的操作组，并考虑不同类型的事务，如下所示：

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)))

或：

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

给：

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

使用data.table，您可以执行以下操作：

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

---

旧答案：如果我理解正确，您想从Trs_Amount计算Balance，其中Transaction_Type==85表示传入交易，Transaction_Type==55传出交易。为此，您可以使用条件 cumsum，如下所示：

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

这给了：

> 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

---

使用 dplyr 软件包，您可以执行以下操作：

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

这给出了相同的结果。稍微替代的实现是：

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

这给了：

   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