data.table join + update with mult='first' 给出了意想不到的结果

在下面的示例中，我有一个用户表和一个事务表，其中一个用户可以有 0 个、1 个或多个事务。我执行连接+更新mult='first'在 users 表上尝试插入一列，指示每个用户第一次发生事务的日期。

library(data.table)  # v1.10.4

# Download data
users <- fread("https://raw.githubusercontent.com/ben519/DataWrangling/master/Data/users.csv")
transactions <- transactions <- fread("https://raw.githubusercontent.com/ben519/DataWrangling/master/Data/transactions.csv")

# Convert date columns to Date type
    users[, `:=`(Registered = as.Date(Registered), Cancelled = as.Date(Cancelled))]
    transactions[, TransactionDate := as.Date(TransactionDate)]

users
   UserID     User Gender Registered  Cancelled FirstTransactionDate
1:      1  Charles   male 2012-12-21       <NA>           2012-08-26
2:      2    Pedro   male 2010-08-01 2010-08-08           2013-12-23
3:      3 Caroline female 2012-10-23 2016-06-07           2016-05-08
4:      4  Brielle female 2013-07-17       <NA>                 <NA>
5:      5 Benjamin   male 2010-11-25       <NA>                 <NA>

transactions
    TransactionID TransactionDate UserID ProductID Quantity
 1:             1      2010-08-21      7         2        1
 2:             2      2011-05-26      3         4        1
 3:             3      2011-06-16      3         3        1
 4:             4      2012-08-26      1         2        3
 5:             5      2013-06-06      2         4        1
 6:             6      2013-12-23      2         5        6
 7:             7      2013-12-30      3         4        1
 8:             8      2014-04-24     NA         2        3
 9:             9      2015-04-24      7         4        3
10:            10      2016-05-08      3         4        4

##### For each user, insert the TransactionDate of the first matching row
users[transactions, FirstTransactionDate := i.TransactionDate, on="UserID", mult="first"]

# Unexpected result
users[UserID == 2]
   UserID  User Gender Registered  Cancelled FirstTransactionDate
1:      2 Pedro   male 2010-08-01 2010-08-08           2013-12-23  # <- shouldn't this be 2013-06-06?

为什么 FirstTransactionDate2013-12-23当交易表中较早的交易与用户 2 绑定时，是否为该用户设置？这是一个错误吗？

阅读文档data.table's mult更仔细地说，它说：

When i is a list (or data.frame or data.table) 和多行x与中的行匹配i, mult返回的控件："all"（默认），"first" or "last".

所以如果有多行x（“用户”）匹配i（“交易”），那么mult将返回第一行x。但是，在您的情况下，中没有多行x匹配到i，而是有多行i匹配到x.

正如@Arun建议的，最好的选择是改变你的周围，这样mult = "first"是相关的：

users[, FirstTransactionDate := transactions[users, TransactionDate, on="UserID", mult = "first"]]

users
#   UserID     User Gender Registered  Cancelled FirstTransactionDate
#1:      1  Charles   male 2012-12-21       <NA>           2012-08-26
#2:      2    Pedro   male 2010-08-01 2010-08-08           2013-06-06
#3:      3 Caroline female 2012-10-23 2016-06-07           2011-05-26
#4:      4  Brielle female 2013-07-17       <NA>                 <NA>
#5:      5 Benjamin   male 2010-11-25       <NA>                 <NA>

另一种选择是稍微改变你的合并：

users[transactions[,FirstTransactionDate := min(TransactionDate), by = UserID],
      FirstTransactionDate := FirstTransactionDate, on="UserID"]

我只是在其中创建第一个交易日期transactions数据集。这会被合并多次，但应该没问题，因为它始终是相同的值UserID.