r - 优化代码以根据 data.table 的条件更新组中的以下行

Question

这里有一些数据可以阐明所需的输入/输出。

dt = structure(list(ticker = c("FTNT", "FTNT", "FTNT", "FTNT", "FTNT", "FTNT", "MDB", "MDB", "MDB", "MDB", "MDB", "MDB", "ZS", "ZS", "ZS", "ZS", "ZS", "ZS"), 
                    date = structure(c(18631, 18632, 18633, 18634, 18635, 18638, 18631, 18632, 18633, 18634, 18635, 18638, 18631, 18632, 18633, 18634, 18635, 18638), tzone = "UTC", tclass = "Date", class = "Date"), 
                    R = c(-0.0199959672793103, -0.0262434257521769, -0.025610299646878,  0.0372167038069893, 0.0341361186736473, 0.000202504389108293, -0.0240363323965936, 0.0146970518570011, -0.0569805661884384,  0.0488220267994761, 0.0232887982973971, 0.00444619037040206,  -0.0181263375550329, -0.0133102149688453, -0.0468265360104722,  0.0467953860405097, 0.022377580589833, 0.00673862306172723),
                    R_acum = c(-0.0199959672793103, -0.0457146303488496, -0.0701541646142473,  -0.0355483675725332, -0.00262573219299644, -0.00242375952618179, -0.0240363323965936, -0.00969254376327744, -0.066120823320278,  -0.0205269491289479, 0.00228380119052418, 0.00674014577578741, -0.0181263375550329, -0.0311952870744229, -0.0765610558513475, -0.0333483739750707, -0.0117170493114047, -0.00505738302838277
                    )), row.names = c(NA, -18L), class = c("data.table", "data.frame"))

数据示例:

目标:对于每个代码，如果 R_acum 满足逻辑条件，我希望将以下行的 R 变量值设置为零。在这种情况下，如果 R_acum 低于 -0.03，我想将以下行的 R 值设置为零，不包括满足条件时的行.

这是本示例所需的输出:

此代码生成所需的输出，但我确信有更好/更快的方法来实现它。

threshold = -0.03
ls_dt = lapply(split(dt, dt$ticker), function(d){
  idx = d[, ifelse(R_acum < threshold, .I, 0)]
  idx = setdiff(idx, 0)
  if(length(idx > 0)){
    min_idx = min(idx)
    d[, idx:=ifelse(.I > min_idx, 0, 1), by=ticker]
    d[, R:=R * idx]
    d[, idx:=NULL]
  }
  d
})
ls_dt

Answer 1

使用 shift + cumsum 的 data.table 选项

dt[, R := R * (cumsum(shift(R_acum < -0.03, fill = FALSE)) == 0), ticker]

给出

> dt
    ticker       date           R       R_acum
 1:   FTNT 2021-01-04 -0.01999597 -0.019995967
 2:   FTNT 2021-01-05 -0.02624343 -0.045714630
 3:   FTNT 2021-01-06  0.00000000 -0.070154165
 4:   FTNT 2021-01-07  0.00000000 -0.035548368
 5:   FTNT 2021-01-08  0.00000000 -0.002625732
 6:   FTNT 2021-01-11  0.00000000 -0.002423760
 7:    MDB 2021-01-04 -0.02403633 -0.024036332
 8:    MDB 2021-01-05  0.01469705 -0.009692544
 9:    MDB 2021-01-06 -0.05698057 -0.066120823
10:    MDB 2021-01-07  0.00000000 -0.020526949
11:    MDB 2021-01-08  0.00000000  0.002283801
12:    MDB 2021-01-11  0.00000000  0.006740146
13:     ZS 2021-01-04 -0.01812634 -0.018126338
14:     ZS 2021-01-05 -0.01331021 -0.031195287
15:     ZS 2021-01-06  0.00000000 -0.076561056
16:     ZS 2021-01-07  0.00000000 -0.033348374
17:     ZS 2021-01-08  0.00000000 -0.011717049
18:     ZS 2021-01-11  0.00000000 -0.005057383