R中的复杂非等合并

Question

我正在尝试在两个表之间进行复杂的非等连接。我受到了上次useR2016中的一个演示视频的启发(https://channel9.msdn.com/events/useR-international-R-User-conference/useR2016/Efficient-in-memory-non-equi-joins-using-datatable)，这让我相信使用data.table可以完成这项任务。我的第一个表格如下：

library(data.table)
sp <- c("SAB","SAB","SAB","SAB","EPN","EPN","BOP","BOP","BOP","BOP","BOP","PET","PET","PET")
dbh <- c(10,12,16,22,12,16,10,12,14,20,26,12,16,18)
dt1 <- data.table(sp,dbh)
dt1
     sp dbh
 1: SAB  10
 2: SAB  12
 3: SAB  16
 4: SAB  22
 5: EPN  12
 6: EPN  16
 7: BOP  10
 8: BOP  12
 9: BOP  14
10: BOP  20
11: BOP  26
12: PET  12
13: PET  16
14: PET  18

这是一份列出树木直径的清单。下面是我的第二个表格，提供了每种树木的直径范围以分类树木的大小级别：

gr_sp <- c("RES","RES","RES","RES","RES","RES", "DEC", "DEC", "DEC", "DEC", "DEC", "DEC")
sp <- c("SAB","SAB", "SAB", "EPN", "EPN", "EPN", "BOP", "BOP", "BOP", "PET", "PET", "PET")
dbh_min <- c(10, 16, 22, 10, 14, 20, 10, 18, 24, 10, 20, 26)
dbh_max <- c(14, 20, 30, 12, 18, 30, 16, 22, 30, 18, 24, 30)
dhb_clas <- c("s", "m", "l", "s", "m", "l", "s", "m", "l", "s", "m", "l")

dt2 <- data.table(gr_sp, sp, dbh_min, dbh_max, dhb_clas)
dt2
    gr_sp  sp dbh_min dbh_max dhb_clas
 1:   RES SAB      10      14        s
 2:   RES SAB      16      20        m
 3:   RES SAB      22      30        l
 4:   RES EPN      10      12        s
 5:   RES EPN      14      18        m
 6:   RES EPN      20      30        l
 7:   DEC BOP      10      16        s
 8:   DEC BOP      18      22        m
 9:   DEC BOP      24      30        l
10:   DEC PET      10      18        s
11:   DEC PET      20      24        m
12:   DEC PET      26      30        l

我希望我的最终表格是由两个表格按物种（“sp”字段）连接，并且在“DBH_MIN”和“DBH_MAX”指定的dhb范围内。这将使我的表格如下所示：

data.table(dt1, gr_sp = c("RES","RES","RES","RES","RES","RES","DEC","DEC","DEC","DEC","DEC","DEC","DEC","DEC"), dhb_clas = c("s","s","m","l","s","m","s","s","s","m","l","s","s","s"))
     sp dbh gr_sp dhb_clas
 1: SAB  10   RES        s
 2: SAB  12   RES        s
 3: SAB  16   RES        m
 4: SAB  22   RES        l
 5: EPN  12   RES        s
 6: EPN  16   RES        m
 7: BOP  10   DEC        s
 8: BOP  12   DEC        s
 9: BOP  14   DEC        s
10: BOP  20   DEC        m
11: BOP  26   DEC        l
12: PET  12   DEC        s
13: PET  16   DEC        s
14: PET  18   DEC        s

我已经尝试过类似以下的东西：

dt1[dt2, on=.(sp=sp, dbh>=dbh_min, dbh<=dbh_max)]

会出现太多行...

谢谢您的帮助

- Bastien

Answer 1

所以我很接近了。我有两个问题，首先是data.table包的安装有问题(Data table error could not find function ".")导致了一个晦涩的错误。

在解决了这个问题之后，我更接近了并发现：

dt1[dt2, on=.(sp=sp, dbh>=dbh_min, dbh<=dbh_max), nomatch=0]

使用一个坏的dbh列，给了我我想要的东西。通过反转该命令：

dt2[dt1, on=.(sp=sp, dbh_min<=dbh, dbh_max>=dbh)]

修复了仅存在一个无用额外列的问题。

- Bastien

---

使用第一种方法，您可以使用 x.* 和 i.* 前缀列举您想要的列：dt1[dt2, on=.(sp, dbh >= dbh_min, dbh <= dbh_max), .(sp, dbh = x.dbh, gr_sp, dhb_clas)] - Frank

请遵循@Frank的建议，使用x.前缀。这在非等值连接中特别有用，因为由于与基本R的一致性，您会得到在连接中使用的重命名列。这在#1615中讨论。 - jangorecki

谢谢@Frank，你的评论让我更接近完美了！在你的调用中添加nomatch=0使它变得完美！ - Bastien

这是一种非等值连接的方法，可以完成同样的任务。dt1[dt2, on="sp", allow.cartesian=T][dbh>=dbh_min & dbh<=dbh_max, -c("dbh_min","dbh_max")] 非等值连接功能很棒... 但根据你的数据类型，另一种方法可能会更快。最近我遇到了几种有数千万行的情况，我会用两种方式写出来，然后发现非等值连接的方法要慢两倍。它可能在内存使用方面略微少一些。 - ironv

这是一个关于非等值连接的好帖子，尽管有点老了。@ironv如果两个数据表之间没有共同的单列，你如何避免使用非等值连接呢？在这个例子中，有一个共同的列sp，所以使用非等值连接是有意义的。 - Lazarus Thurston

Answer 2

使用 dplyr 1.1.0 中提供的 join_by，您还可以使用辅助函数between。

library(dplyr)
left_join(dt1, dt2, by = join_by(sp, dbh >= dbh_min, dbh <= dbh_max))

#or, with between():
left_join(dt1, dt2, by = join_by(sp, between(dbh, dbh_min, dbh_max)))

输出

   sp dbh gr_sp dbh_min dbh_max dhb_clas
1  SAB  10   RES      10      14        s
2  SAB  12   RES      10      14        s
3  SAB  16   RES      16      20        m
4  SAB  22   RES      22      30        l
5  EPN  12   RES      10      12        s
6  EPN  16   RES      14      18        m
7  BOP  10   DEC      10      16        s
8  BOP  12   DEC      10      16        s
9  BOP  14   DEC      10      16        s
10 BOP  20   DEC      18      22        m
11 BOP  26   DEC      24      30        l
12 PET  12   DEC      10      18        s
13 PET  16   DEC      10      18        s
14 PET  18   DEC      10      18        s

- Maël

Answer 3

对于像这样的“between”连接，也可以使用data.table::foverlaps，它将两个重叠范围的data.table连接起来，而不是使用非等连接。

以相同的示例为例，以下代码将产生所需的结果。

"最初的回答"

# foverlap tests the overlap of two ranges.  Create a second column,
# dbh2, as the end point of the range.
dt1[, dbh2 := dbh]

# foverlap requires the second argument to be keyed
setkey(dt1, sp, dbh, dbh2)

# find rows where dbh falls between dbh_min and dbh_max, and drop unnecessary
# columns afterwards
foverlaps(dt2, dt1, by.x = c("sp", "dbh_min", "dbh_max"), by.y = key(dt1),
          nomatch = 0)[
  ,
  -c("dbh2", "dbh_min", "dbh_max")
]

#  sp dbh gr_sp dhb_clas
#  1: SAB  10   RES        s
#  2: SAB  12   RES        s
#  3: SAB  16   RES        m
#  4: SAB  22   RES        l
#  5: EPN  12   RES        s
#  6: EPN  16   RES        m
#  7: BOP  10   DEC        s
#  8: BOP  12   DEC        s
#  9: BOP  14   DEC        s
# 10: BOP  20   DEC        m
# 11: BOP  26   DEC        l
# 12: PET  12   DEC        s
# 13: PET  16   DEC        s
# 14: PET  18   DEC        s

- Jake Fisher