将dplyr连接语法转换为纯粹的data.table语法

Question

我正在学习data.table。我在转换dplyr的连接语法时遇到了困难。您能否为以下测试用例推荐相应的data.table等效方法？

library(data.table)
library(dplyr)

dtProduct <- data.table(
    ProductID  = c(6, 33, 17, 88, 44, 51),
    ProductName= c("Shirt", "Helmet", "Gloves", "Towel", "Chair", "Detergent"),
    Price= c(25, 60, 10, 7.5, 135, 16),
    key = 'ProductID'
)

set.seed(20141216)
dtOrder <- data.table(
    OrderID    = sample(1001:9999, 12),
    CustomerID = sample(271:279, 12, replace=TRUE),
    # NOTE: some non-existent ProductID intentionally introduced
    ProductID  = sample(c(dtProduct[, ProductID], 155, 439), 12, replace=TRUE),
    Qty = sample(1:3, 12, replace=TRUE),
    key = 'OrderID'
)

> tables()
     NAME      NROW NCOL MB COLS                             KEY      
[1,] dtOrder     12    4  1 OrderID,CustomerID,ProductID,Qty OrderID  
[2,] dtProduct    6    3  1 ProductID,ProductName,Price      ProductID

> dtProduct
   ProductID ProductName Price
1:         6       Shirt  25.0
2:        17      Gloves  10.0
3:        33      Helmet  60.0
4:        44       Chair 135.0
5:        51   Detergent  16.0
6:        88       Towel   7.5
> dtOrder
    OrderID CustomerID ProductID Qty
 1:    1651        275         6   3
 2:    2726        272        88   2
 3:    3079        275        88   2
 4:    3168        274        17   1
 5:    4816        277        88   1
 6:    4931        278        51   1
 7:    5134        274       439   2
 8:    5265        272        33   3
 9:    7702        275        33   2
10:    7727        279       155   2
11:    8412        273        88   2
12:    9130        271        17   3

案例1：显示订单详细信息，不匹配的产品ID被隐藏

dtOrder %>%
    inner_join(dtProduct, by="ProductID") %>%
    transmute(OrderID, ProductID, ProductName, Qty, Price, ExtPrice=Qty*Price)

   OrderID ProductID ProductName Qty Price ExtPrice
1     1651         6       Shirt   3  25.0     75.0
2     3168        17      Gloves   1  10.0     10.0
3     9130        17      Gloves   3  10.0     30.0
4     5265        33      Helmet   3  60.0    180.0
5     7702        33      Helmet   2  60.0    120.0
6     4931        51   Detergent   1  16.0     16.0
7     2726        88       Towel   2   7.5     15.0
8     3079        88       Towel   2   7.5     15.0
9     4816        88       Towel   1   7.5      7.5
10    8412        88       Towel   2   7.5     15.0

案例2：显示订单详细信息，包括未匹配的产品ID

dtOrder %>%
    left_join(dtProduct, by="ProductID") %>%
    transmute(OrderID, ProductID, ProductName, Qty, Price, ExtPrice=Qty*Price)

   OrderID ProductID ProductName Qty Price ExtPrice
1     1651         6       Shirt   3  25.0     75.0
2     3168        17      Gloves   1  10.0     10.0
3     9130        17      Gloves   3  10.0     30.0
4     5265        33      Helmet   3  60.0    180.0
5     7702        33      Helmet   2  60.0    120.0
6     4931        51   Detergent   1  16.0     16.0
7     2726        88       Towel   2   7.5     15.0
8     3079        88       Towel   2   7.5     15.0
9     4816        88       Towel   1   7.5      7.5
10    8412        88       Towel   2   7.5     15.0
11    7727       155          NA   2    NA       NA
12    5134       439          NA   2    NA       NA

案例3：显示订单错误（仅限不匹配的产品ID）

dtOrder %>%
    left_join(dtProduct, by="ProductID") %>%
    filter(is.na(ProductName)) %>%
    select(OrderID, ProductID, ProductName, Qty)

  OrderID ProductID ProductName Qty
1    7727       155          NA   2
2    5134       439          NA   2

案例4：按产品ID分类的各种聚合，按总销售额降序排序结果。

dtOrder %>%
    inner_join(dtProduct, by="ProductID") %>%
    group_by(ProductID) %>%
    summarize(OrderCount=n(), TotalQty=sum(Qty), TotalSales=sum(Qty*Price)) %>%
    arrange(desc(TotalSales))

  ProductID OrderCount TotalQty TotalSales
1        33          2        5      300.0
2         6          1        3       75.0
3        88          4        7       52.5
4        17          2        4       40.0
5        51          1        1       16.0

案例5：按产品ID分类的各种聚合，结果按总销售额降序排序。

• NOTE1: This time, ProductName is displayed along with ProductID

• NOTE2: sort by descending TotalSales no longer working (BUG?)

  dtOrder %>%
     inner_join(dtProduct, by="ProductID") %>%
     group_by(ProductID, ProductName) %>%
     summarize(OrderCount=n(), TotalQty=sum(Qty), TotalSales=sum(Qty*Price)) %>%
     arrange(desc(TotalSales))
  
    ProductID ProductName OrderCount TotalQty TotalSales
  1         6       Shirt          1        3       75.0
  2        17      Gloves          2        4       40.0
  3        33      Helmet          2        5      300.0
  4        51   Detergent          1        1       16.0
  5        88       Towel          4        7       52.5
  

非常感谢您提前的任何帮助。

- Polymerase

---

嗨，David，感谢您的编辑。我不知道为什么Case5的代码无法正确显示。您是如何解决的？此外，我在顶部添加了“Hi datatable experts”，但不知何故，这段文本从未被接受。帖子中是否有最大长度限制？ - Polymerase

嘿，你只需要添加足够的空格：）。我不知道为什么你的编辑没有被接受，也许是因为我们同时在编辑？无论如何，在我看来，没有真正必要将这个句子添加到问题中。 - David Arenburg

@Polymerase，你想学习data.table，但我在这里没有看到任何data.table代码（除了创建表格的代码），你到目前为止尝试过什么？（原文已经很通俗易懂，翻译保持原意） - agstudy

@agstudy ，我不明白你为什么要发表这样苛刻的评论。我在这里呈现的测试案例都是经过精心准备的。如果你真正阅读了我的帖子，你会看到每个测试案例都配有一个使用dplyr编写的可工作代码。该dplyr查询是使用底层datatable对象进行的。我想知道是否有更好的“原生”datatable语法。如果你有比我或eddi提供的其他解决方案，请友好地分享你的经验。 - Polymerase

@Polymerase，agtudy的评论并不是很苛刻。根据SO标准，这个问题可以被视为“没有研究努力”——你展示了一个工具/包中的代码，并要求另一个工具/包的代码，这可能被视为离题。你的意图是诚实的，我理解，但他也是如此。 - Arun

Answer 1

您应该查看?data.table并阅读其中的示例。这是学习的一种非常好的方式。我们正在编写更多详细vignettes FR＃944，预计于1.9.8发布。但在此之前：

---

- data.table格式

data.table的语法形式为：

x[i, j, by, ...] # i = where, j = select|modify|update, by = group by

- 子集操作

当i是一个整数或逻辑表达式时，我们称其为一个子集 操作。例如：

x[a > 1]

这是做什么的？从数据表x中检查列a是否满足条件> 1，结果是一个逻辑向量= length(a)。然后确定评估为TRUE的行，并返回与那些行对应的所有列。

- 连接作为子集的扩展

概念

在data.table中，连接可以被看作子集的一种自然扩展。也就是说，我们可以将连接视为使用另一个数据表的子集操作。这就是我们所说的具有一致的语法——形式x[i, j, by]保持不变的含义。

进行data.table连接的第一步是设置关键字。这可以通过setkey()函数来实现，其目的有两个：

• 按提供的列以升序方式重新排列数据表的行。这是通过引用来完成的，以达到内存效率的目的。

• 标记提供的列为关键列，可以在其中进行连接（如果和何时进行连接）。

请注意，目前对于形式为x[i]的连接，必须绝对设置x的关键列。可以在 i 上设置它的键或不设置。

• 如果 i 也设置了其关键列，则通过匹配 i 的第一个关键列与 x 的第一个关键列、第二个关键列与第二个关键列等进行连接。

• 如果 i 没有设置关键列，则将 i 的第一列与 x 的第一个关键列匹配，将 i 的第二列与 x 的第二个关键列匹配，依此类推。

是的，我们知道如果 i 没有关键列，按列名进行匹配会很好，但我们还没有时间去做它。

第二步也是最后一步就是执行连接 :-).

但是，连接操作如何扩展子集？当i为data.table时，对于i中的每一行，它通过匹配我们设置的x的关键列，在x中找到相匹配的行索引。这将返回每个i中的行对应的x的行索引集合（如果未找到匹配，则返回NA）。

现在我们有了匹配的行索引。我们要做的就是返回列。但是由于i也是一个data.table，它可能还有其他列。因此，我们将返回x和i的列，用于这些匹配的行索引。

例子

以下是一个小例子，帮助您在继续之前内化该概念。考虑下面显示的两个数据表X和Y：

X = data.table(a=c(1,1,1,2,2,5,6), b=1:7, key="a")
#    a b
# 1: 1 1
# 2: 1 2
# 3: 1 3
# 4: 2 4
# 5: 2 5
# 6: 5 6
# 7: 6 7

key(X)
# [1] "a"

Y = data.table(a=c(6,2), c=letters[1:2])
#    a c
# 1: 6 a
# 2: 2 b

key(Y)
# NULL

# join
X[Y]
#    a b c
# 1: 6 7 a
# 2: 2 4 b
# 3: 2 5 b

请注意，在data.table（）函数中使用了key = 参数直接设置键列。 或者，我们可以只创建没有键的X，然后使用setkey（X，a）设置键。

如果没有设置键，则key（）函数返回键列。 如果没有设置键，则返回NULL。 Y没有键列，而X只有一个键列。 因此，使用Y的第一列a和X的第一键列a进行连接。 在Y中，a=6与X的第7行匹配，在第4和第5行上的a=2也是如此。

您可以通过使用参数which = TRUE来检查这一点：

X[as.data.table(6), which=TRUE] # [1] 7
X[as.data.table(2), which=TRUE] # [1] 4 5

这也是一种方便（而且快速）的方式来对数据表进行子集操作，但是使用data.table的快速二分搜索。由于这个操作非常有用，data.table提供了一种简单的方法来代替每次都要写as.data.table()。

# faster way of doing X[a == 6] on data.table with 'a' as key column
X[J(6)] # J for Join
X[J(2)]

# (or)

X[.(6)] # . is an alias for J
X[.(2)]

我认为这会进一步帮助理解我们所谓的子集是连接的扩展。

---

回到你的问题

现在，让我们暂时忘记所有这些“左”，“右”，“内”，“外”等，看看你想执行的实际操作。您有两个数据表 - dtP和dtO（为方便起见缩短）。

情况1：

对于dtO中ProductID列的每一行，您想要在dtP中查找匹配行，但您不想返回NA。您还想选择要输出的列以及一些计算。

也就是说，i = dtO，x = dtP。设置了dtP的键列。但是dtO的键列是orderID。如果按此方式连接，将从dtP的productID对dtO的orderID进行连接，这是错误的。

我们可以将dtO的键设置为productID，或者将dtO的键设置为NULL，并将列productID移动到第一列（直到按名称匹配）。让我们在这里将键设置为productID：

# set key
setkey(dtO, ProductID)
# join
dtP[dtO, .(OrderID, ProductID, ProductName, Qty, Price, ExtPrice=Qty*Price), nomatch=0L]

现在这个操作应该很明显了。只匹配行索引，提取所有这些列（包括表达式）。

为什么我们要先连接再选择/聚合呢？

第二种情况：

与第一种情况相同，但您甚至需要不匹配的行。键已经从第一种情况正确设置。

# join
dtP[dtO, .(OrderID, ProductID, ProductName, Qty, Price, ExtPrice=Qty*Price)]

即使没有匹配，返回orderID的所有行和指定的所有列（包括表达式）。

案例三：

您希望获取dtO中与dtP没有匹配的所有行。

not-join or anti-join
dtO[!dtP]

查找所有与dtO匹配的dtP的关键列的行。从dtO返回其他所有行。如果必要，也可以在j中指定所有必要的列。

案例4：

了解有关by=.EACHI的信息，请参阅此帖子。

您正在按productID连接，然后通过相同的列进行聚合。但是，我们为什么需要那个中间结果？这完全是不必要的，浪费内存和计算时间！相反，我们可以利用by=.EACHI，它将在每个i行上对匹配行评估j表达式。

dtO[dtP, .(.N, sQty = sum(Qty), sSales = sum(Qty*Price)), by=.EACHI, nomatch=0L][order(-sSales)]

为了测试您的理解力，请尝试找出我们为什么没有在这里执行dtP [dtO，...]。

案例5：

与@eddi相同。

---

我个人认为，按照我想要执行的实际任务进行思考比找出与我想要执行的任务相关联的连接函数类型更自然（我永远无法记住哪个数据表是“左”和哪个是“右”......顺便说一下，“inner”，“outer”和“full outer” join到底是什么意思？）。

希望对您有所帮助。

- Arun

---

在data.table中，这些概念是通过不同的语法实现的。具有相同的相反副作用（nomatch对于“传统”的数据库人员没有意义）。非常感谢您对by = .EACHI的详细解释，我已经完全理解了。您可以放心，我会成为一个好学生。您在此处写下的每个单词都被认真吸收。 - Polymerase

有没有办法将这篇帖子也标记为答案？而不会取消eddi早期帖子的答案状态？ - Polymerase

没问题。很高兴能帮到你。我是共同开发者。Matt 是主要作者。一个问题只能有一个答案。保留 Eddi 的作为答案也没问题。 - Arun

假设有X[Y]，其中X和Y数据表具有许多共同的键和重复的键（几行具有相同的键值）。结果X[Y]是一个集合，实际上比X或Y单独要大。如果我们挑剔词汇，这怎么能被称为子集呢？在幕后，我想data.table引擎执行一种交集操作。类似于dplyr或SQL查询引擎。无论我们将该交集结果称为子集还是连接，这只是一种术语约定。各种类型的连接，只是一种表示nomatch=0或1的语法。 - Polymerase

@Polymerase，我确实说过并集可以被看作是子集的自然扩展，它们是相关的，但不一定相同。我完全理解子集和并集之间的区别。但我的观点是要强调它们之间的关系（在我看来，具有不相关名称的分离函数似乎将其抽象为不同的概念）。无论如何，我完全可以同意不同意见。 - Arun

Answer 2

setkey(dtOrder, ProductID)

(1-2)

# this will be literally what you wrote

dtProduct[dtOrder,
          list(OrderID, ProductID, ProductName, Qty, Price, ExtPrice=Qty*Price),
          nomatch = 0 # or omit this to get (2)
         ]

# but I think you'd be better off with this
dtProduct[dtOrder][, ExtPrice := Qty*Price][]

(3)

# you can again take the literal direction:
dtProduct[dtOrder][!is.na(ProductName)][,
          list(OrderID, ProductID, ProductName, Qty)]

# but again I think you'd be better off with
dtOrder[!dtProduct]

(4-5)

dtProduct[dtOrder, nomatch = 0][,
          list(OrderCount=.N, TotalQty=sum(Qty), TotalSales=sum(Qty*Price)),
          by = list(ProductID, ProductName)][
          order(-TotalSales)]

- eddi

---

eddi，哇塞，这真是太神奇了。值得深入研究datatable。我对DT语法印象深刻。不过还有几个问题：Q1.如果DT已经定义了键，setkey(dtOrder, ProductID)的目的是什么？Q2.如果两个DT在多个列上连接，是否使用相同的语法（即通过列名自动检测）？Q3.SJ()和J()运算符是什么？你可以不使用它们解决所有情况，为什么？ - Polymerase

@Polymerase Q1: 你需要正确设置键才能进行正确匹配（你的键被设置为不同的列）；Q2：如果为两个表设置了键，则一切都应该按原样进行；Q3：查看?J和其中的示例，您还可以比较dtProduct[J(dtOrder$ProductID)]与dtProduct[dtOrder]。 - eddi