使用 data.table 中的多个键来获得条件搜索

我有一个data.table首先，我想根据某些条件获得一个子集，例如，我有

library(data.table)
dt <- data.table(rn=1:10, B=rep(1:2, 5))
dt
#    rn B
# 1:  1 1
# 2:  2 2
# 3:  3 1
# 4:  4 2
# 5:  5 1
# 6:  6 2
# 7:  7 1
# 8:  8 2
# 9:  9 1
#10: 10 2`

我知道第一列名称，但我事先不知道第二列的名称，而是存储在字符向量中：nameAsVect <- "B"

假设我想获得以下内容：

dt[rn>5 & B==2, ]
#   rn B
#1:  6 2
#2:  8 2
#3: 10 2`

我想我可以这样做：

setkeyv(dt, c("rn", nameAsVect))
max.count <- max(dt[, nameAsVect, with=FALSE])
dt[J(5:max(rn), max.count), ]
#   rn B
#1:  5 2
#2:  6 2
#3:  7 2
#4:  8 2
#5:  9 2
#6: 10 2

但我不明白为什么列中的值是 5、7 和 9rn被包含在内。我可以通过以下方式获得我想要的东西：dt[rn>=5 & get(nameAsVect) == max.count]但我认为第一种方法如果有效的话，对于大表来说会更快。

有什么见解吗？

Thanks

OP 方法还有其他替代方法，不需要预先设置密钥

矢量扫描&get()

dt[rn >= 5 & get(nameAsVect) == max(get(nameAsVect))]

   rn B
1:  6 2
2:  8 2
3: 10 2

矢量扫描&eval(parse())

马特·道尔 (Matt Dowle) 在他的回答选择/分配给 data.table 变量，其名称存储在字符向量中 https://stackoverflow.com/a/12392269/3817004:

eval(parse(text = sprintf("dt[rn >= 5 & %s == max(%s)]", nameAsVect, nameAsVect)))

   rn B
1:  6 2
2:  8 2
3: 10 2

非等值连接

版本 v1.9.8（CRAN 2016 年 11 月 25 日发布），data.table已经获得了做事的能力非等值连接.

max.count <- dt[, max(get(nameAsVect))] 
dt[dt[.(5, max.count), on = c("rn>=V1", paste0(nameAsVect, "==V2")), which = TRUE]]

   rn B
1:  6 2
2:  8 2
3: 10 2

或者（我喜欢的方式）

mdt <- dt[, c(.(rn = 5), lapply(.SD, max)), .SDcols = nameAsVect] 
dt[dt[mdt, on = c("rn>=rn", nameAsVect), which = TRUE]]

   rn B
1:  6 2
2:  8 2
3: 10 2

基准

创建基准数据：

n_row <- 1e6L
set.seed(123L)
DT <- data.table(
  rn = sample(1:10, n_row, TRUE),
  B  = sample(1:2,  n_row, TRUE)
)

运行基准测试：

library(microbenchmark)
bm <- microbenchmark(
  vec_scan_hard_coded = {
    dt <- copy(DT)
    dt[rn >= 5L & B == 2L]
  },
  OP_keyed = {
    dt <- copy(DT)
    setkeyv(dt, c("rn", nameAsVect))
    max.count <- max(dt[, nameAsVect, with=FALSE])
    dt[J(5:max(rn), max.count), nomatch = 0L]
  },
  vec_scan_get = {
    dt <- copy(DT)
    dt[rn >= 5 & get(nameAsVect) == max(get(nameAsVect))]
  },
  vec_scan_eval_parse = {
    dt <- copy(DT)
    eval(parse(text = sprintf("dt[rn >= 5 & %s == max(%s)]", nameAsVect, nameAsVect)))
  },
  nej1 = {
    dt <- copy(DT)
    max.count <- dt[, max(get(nameAsVect))] 
    dt[dt[.(5, max.count), on = c("rn>=V1", paste0(nameAsVect, "==V2")), which = TRUE]]
  },
  nej1_keyed = {
    dt <- copy(DT)
    setkeyv(dt, c("rn", nameAsVect))
    max.count <- dt[, max(get(nameAsVect))] 
    dt[dt[.(5, max.count), on = c("rn>=V1", paste0(nameAsVect, "==V2")), which = TRUE]]
  },
  nej2 = {
    dt <- copy(DT)
    mdt <- dt[, c(.(rn = 5), lapply(.SD, max)), .SDcols = nameAsVect] 
    dt[dt[mdt, on = c("rn>=rn", nameAsVect), which = TRUE]]
  },
  nej2_keyed = {
    dt <- copy(DT)
    setkeyv(dt, c("rn", nameAsVect))
    mdt <- dt[, c(.(rn = 5), lapply(.SD, max)), .SDcols = nameAsVect] 
    dt[dt[mdt, on = c("rn>=rn", nameAsVect), which = TRUE]]
  },
  times = 100L
)
print(bm)

对于 1 M 行和大约 30 万行的结果集，矢量扫描方法是最快的：

Unit: milliseconds
                expr      min       lq     mean   median       uq      max neval cld
 vec_scan_hard_coded 19.03159 20.86890 42.70820 24.38040 27.57417 219.5682   100  a 
            OP_keyed 31.49025 34.50825 52.46168 37.74204 40.84953 194.7676   100  a 
        vec_scan_get 20.60384 25.75461 46.37579 27.29287 29.55892 185.5867   100  a 
 vec_scan_eval_parse 20.81188 23.92598 36.81940 26.69742 29.27687 183.5323   100  a 
                nej1 53.85361 59.32608 85.32623 62.12509 65.15083 227.1221   100   b
          nej1_keyed 52.89946 58.37457 77.38969 61.03312 64.32072 221.3292   100   b
                nej2 53.25590 59.69762 88.92513 61.98481 65.05738 285.2495   100   b
          nej2_keyed 53.25061 58.61453 81.22925 61.14885 63.56159 274.0207   100   b