I have following DT (data.table) in R.
dt <- fread("
id| rowids | charge | payment | balance
a | 1 | 7.1 | 0 |
a | 2 | 1.2 | 3 |
a | 3 | 1.7 | 1 |
b | 1 | 8.1 | 0 |
b | 2 | 2.5 | 4 |
b | 3 | 2.3 | 2 |
b | 4 | 3.2 | 1 |
",
sep = "|",
colClasses = c("character", "numeric", "numeric", "numeric",
"numeric"))
The "balance" is should be computed, within each id group, as "balance <- previous.row.balance + charge - payment", where the "previous.row.balance" is the previous row entry of "balance".
I initially underestimate the difficulty to compute the running balance. I was thinking about dt[,previous.row.balance := (shift(balance,1),by=id]. But R does vectorized computation. I did not have values in "balance" available for me to perform shift() since "balance" will be computed through row-by-row iteration.
I searched on StackOverflow and found a similar question and its first answer greatly helped me to think through the whole process. I adapted the code in the first answer to my problem and got the following code working wonderfully to generate the running balance by group.
dt[rowids == 1, balance := charge, by=.(id)]
dt[rowids != 1, balance :=
dt[,
{
balance1 <- balance[1L]
.SD[rowids != 1,
{balance1 <- balance1 + charge - payment
.(balance1)
},
by=.(rowids)]
},
by=.(id)][, -1L:-2L]
]
Here are my questions.
- I still cannot understand how
by=.(id)][, -1L:-2L], the chained brackets worked the iteration out. Since the code does not employshift() by = group, I guess[, -1L:-2L]does the trick here to perform the iteration. But how? What does[, -1L:-2L]actually do here?
Sorry that I have to ask this question here, instead of commenting or asking under that question . The reason is that I am brand new to StackOverflow with only 1 point of reputation. I am not allowed to comment on the original answer to that question. I also would like to vote up for that answer. Before I can do that, I have to earn more points.
- Is there any other way, using data.table and R vectorizing computation to achieve this running balance goal, without wrapping any loop for row iteration?
Any insight or thought is appreciated!