具有先前行依赖性的R data.table计算 [英] R data.table calculations with previous row dependencies
问题描述
下面是一些当前我在Excel中计算的数据。
Below is some data which I currently calculate in Excel.
col_A col _B col_C col_D col_E col_F col_G
-1.5% 0.010 1.00 1 1.00 - -
-5.4% 0.024 1.00 1 1.00 0.01 -0.00
-7.9% 0.036 1.00 1 1.00 0.02 -0.00
-12.7% 0.052 0.99 1 0.99 0.06 -0.01
-4.6% 0.049 0.98 1 0.98 0.19 -0.01
-8.3% 0.051 0.95 1 0.95 0.39 -0.03
-7.3% 0.052 0.88 1 0.88 1.00 -0.07
-9.2% 0.055 0.69 1 0.69 2.31 -0.21
-7.9% 0.055 0.38 1 0.38 5.63 -0.44
-2.2% 0.051 0.29 1 0.29 11.13 -0.24
我一直在尝试使用data.table在R中执行计算。我的问题是data.table按列执行计算。由于依赖于先前行值的结果,我需要按行执行计算。下面给出了计算列的Excel公式,其中 T表示当前行, T-1表示上一行
I have been trying to perform the calculations in R using data.table. The problem I have is that data.table performs calculation column-wise. I need the calculations to be performed row-wise, because of dependencies on the results of previous row values. The Excel-formulas for the calculated columns are given below, with "T" indicating "current row" and "T-1" indication "previous row"
col_C:( col_C.T-1)*(1 + col_G.T)
col_C: (col_C.T-1) * (1 + col_G.T)
col_D:最大值(Col_C.T,col_D.T-1)
col_D: max (Col_C.T, col_D.T-1)
col_E:(col_C.T / col_D.T)
col_E: (col_C.T / col_D.T)
col_F:max((1-(col_C.T-1 / col_D。 T-1))/ col BT-1),0.01)
col_F: max ((1 - (col_C.T-1 / col_D.T-1)) / col B.T-1), 0.01)
col_G:col_A * col_F
col_G: col_A * col_F
任何
推荐答案
如果没有其他条件需要使用 data.table
我建议使用矩阵实现按行计算:
If there are no other conditions which require to use data.table
I suggest to implement the rowwise calculations using a matrix:
m <- data.matrix(dt)
m[, 3:7] <- NA
for (i in seq.int(nrow(m))) {
if (i == 1L) {
m[i, "col_F"] <- 0
m[i, "col_G"] <- 0
m[i, "col_C"] <- 1
m[i, "col_D"] <- 1
} else {
m[i, "col_F"] <- max((1 - (m[i-1, "col_C"] / m[i-1, "col_D"])) / m[i-1, "col_B"], 0.01)
m[i, "col_G"] <- m[i, "col_A"] * m[i, "col_F"]
m[i, "col_C"] <- m[i-1, "col_C"] * (1 + m[i, "col_G"])
m[i, "col_D"] <- max(m[i, "col_C"], m[i-1, "col_D"])
}
m[i, "col_E"] <- m[i, "col_C"] / m[i, "col_D"]
}
m
col_A col_B col_C col_D col_E col_F col_G
[1,] -0.015 0.010 1.0000000 1 1.0000000 0.00000000 0.000000000
[2,] -0.054 0.024 0.9994600 1 0.9994600 0.01000000 -0.000540000
[3,] -0.079 0.036 0.9976835 1 0.9976835 0.02250000 -0.001777500
[4,] -0.127 0.052 0.9895302 1 0.9895302 0.06434834 -0.008172239
[5,] -0.046 0.049 0.9803653 1 0.9803653 0.20134322 -0.009261788
[6,] -0.083 0.051 0.9477596 1 0.9477596 0.40070748 -0.033258721
[7,] -0.073 0.052 0.8768905 1 0.8768905 1.02432085 -0.074775422
[8,] -0.092 0.055 0.6858958 1 0.6858958 2.36749020 -0.217809099
[9,] -0.079 0.055 0.3764416 1 0.3764416 5.71098585 -0.451167882
[10,] -0.022 0.051 0.2825483 1 0.2825483 11.33742486 -0.249423347
col_F
的最后4行与OP的预期结果之间的偏差可能是由于过帐值的精度有限 col_A
和 col_B
。
The deviations in the last 4 rows of col_F
from OP's expected result might be due to the limited precision of the posted values of col_A
and col_B
.
library(data.table)
dt <- fread("col_A col_B col_C col_D col_E col_F col_G
-1.5% 0.010 1.00 1 1.00 - -
-5.4% 0.024 1.00 1 1.00 0.01 -0.00
-7.9% 0.036 1.00 1 1.00 0.02 -0.00
-12.7% 0.052 0.99 1 0.99 0.06 -0.01
-4.6% 0.049 0.98 1 0.98 0.19 -0.01
-8.3% 0.051 0.95 1 0.95 0.39 -0.03
-7.3% 0.052 0.88 1 0.88 1.00 -0.07
-9.2% 0.055 0.69 1 0.69 2.31 -0.21
-7.9% 0.055 0.38 1 0.38 5.63 -0.44
-2.2% 0.051 0.29 1 0.29 11.13 -0.24 ", na.strings = "-")
# convert percent string to numeric
dt[, col_A := readr::parse_number(col_A) / 100]
这篇关于具有先前行依赖性的R data.table计算的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!