基于聚合值过滤数据表的最有效方法

Question

在性能方面，基于对数据表进行聚合计算的条件来过滤data.table，推荐使用哪种方法或最佳方式呢？

一个reprex胜过千言万语：

library(data.table)
DT <- data.table(grp = rep(LETTERS[1:3], each = 3), x = 1:9)
setkey(DT, "grp")
DT[DT[, .(nok = any(x == 4)), by = grp][nok == FALSE]]
DT[DT[, .GRP[all(x != 4)], by = grp]]

我能立刻想到这两种解决方案，我的直觉告诉我第二种形式应该更好（存储较小的中间表，无需链接结果），但我想知道是否有一种规范的做法？

也许我一开始不需要使用连接，可以为i参数使用分组过滤器？

这显然不能按预期工作（by显然只对j产生影响）：

DT[all(x != 4), by = grp]

---

虽然这个SO答案展示了另一种做法，但我的主要关注点是性能。因此，如果我想进一步处理过滤后的data.table（即在过滤结果上使用另一个j表达式），我想知道这些选项中哪个通常适用于大型表。

在我的实际情况下，我有大约1600万行，大约40k个唯一键和14列。

因此，基准数据集可能如下所示：

bench <- data.table(keys = rep(paste0("k", 1:40000), 400))
bench[, paste0("cols", 1:13) := replicate(13, sample(40000 * 400, TRUE), 
                                          simplify = FALSE)]

虽然我正在寻找通用答案（如果可能的话），不考虑最终选择的过滤器，但实际上的过滤器将是查找包含任何NA值的群组。

- thothal

---

这个回答解决了你的问题吗？使用“by”过滤数据表中的行 - arg0naut91

部分地，它确实进行了过滤，但它并没有回答哪个选项显示出最高的性能。 - thothal

然后我会用一些实际的基准测试和数据来更新问题，以便进行比较。目前还没有提到"性能"这个词。 - arg0naut91

嗯，实际上在第一行已经有了：“关于性能，有什么推荐的/“最佳”的方法”，但我添加了一个更新以使其更加清晰明了。 - thothal

我撤回之前的说法，不过总的帖子内容并不是很清楚。如果你能提供一个代表性的数据集就更好了 - “性能”这个词在多少列/行/组和不同变量下都可能是相对的。 - arg0naut91

添加了一个基准数据集，并明确了我的实际案例场景的维度。 - thothal

Answer 1

我从这篇文章中学到，你可以做到这一点。

DT[DT[,.I[all(x!=4)],by=.(grp)]$V1,]

- Frank Zhang

Answer 2

我不认为有一种适用于所有情况的通用方法。它取决于数据集的特征和过滤条件。数据集可能有多个小组或整数键，而过滤可以使用更快的低级编程方法来实现。

以下是几种解决您实际问题（即筛选具有某列NA值的组）的选项：

DT_keys <- copy(DT)
system.time(setkey(DT_keys, keys))
#   user  system elapsed 
#   1.50    0.67    1.32 

DT_cols1 <- copy(DT)
system.time(setkey(DT_cols1, cols1))
#   user  system elapsed 
#   4.21    0.21    1.30 

microbenchmark::microbenchmark(times=1L,
    m0 = DT_keys[, keys[is.na(cols1)], keys]$keys,
    m1 = DT_keys[, if (anyNA(cols1)) keys, keys]$keys,
    m2 = DT_cols1[.(NA_integer_)]$keys
)

1600万行虚拟数据的时间：

Unit: milliseconds
 expr       min        lq      mean    median        uq       max neval
   m0 90.675005 90.675005 90.675005 90.675005 90.675005 90.675005     1
   m1 56.548620 56.548620 56.548620 56.548620 56.548620 56.548620     1
   m2  4.010301  4.010301  4.010301  4.010301  4.010301  4.010301     1

对于您实际数据集的规模来说，时间非常快。除非您运行过滤器数百次，否则不会节省太多显著的时间。也许您想保存运行时间以外的其他类型的时间。

数据：

library(data.table)
set.seed(0L)
nk <- 4e4L
nn <- 400L
DT <- data.table(keys = rep(paste0("k", 1L:nk), nn))
DT[, paste0("cols", 1L:13L) := 
    replicate(13L, sample(c(NA_integer_, 1L:nk), nk * nn, TRUE), simplify = FALSE)]

- chinsoon12

---

感谢您详细的回答+1。为了我充分理解：您是否建议最终筛选原始数据集的“最佳”方法是使用聚合表与原始表 DT[DT_cols1[.(NA_integer_)]$keys] 进行连接？ - thothal

你会多次运行过滤吗？如果我们复制数据集两次，你会遇到内存问题吗？ - chinsoon12

实际上，过滤操作只会运行一次。内存可能会成为一个问题。因此，也许使用相同的键来解决问题更可取。谢谢你的回答，它帮助我更加熟悉data.table（尽管我更倾向于使用dplyr）。 - thothal

如果我要提建议的话，我会建议你先使用setkey(DT, keys)，然后再使用DT[DT[, if (anyNA(cols1)) keys, keys]$keys]。 - chinsoon12