R中的复杂非等式合并 [英] Complexe non-equi merge in R

问题描述

我正在尝试在两个表之间进行复杂的非 equi 连接.我从上一次 userR2016 的演示中得到启发(https://channel9.msdn.com/events/useR-international-R-User-conference/user2016/Efficient-in-memory-non-equi-joins-using-datatable) 这让我相信这将是 data.table 的合适任务.我的表 1 看起来像:

I'm trying to do a complexe non-equi join between two tables. I got inspired by a presentation in the last useR2016 (https://channel9.msdn.com/events/useR-international-R-User-conference/useR2016/Efficient-in-memory-non-equi-joins-using-datatable) which made me believe it would be a suitable task for data.table. My table 1 looks like:

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

这是一个带有 dbh 的树列表.我的第二个表(如下)给出了一个通用表，它为每个树种提供了一个 dbh 范围来对大小等级或树进行分类:

It's a list of trees with their dbh. My second table (below) gives a generic table that gives for each tree species a range of dbh to classify the size class or the tree:

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 范围内的两个表的连接.这将使我的桌子看起来像:

I want my final table to be the join of the two tables by species ("sp" field) and within the range of dhb stated by "DBH_MIN" and "DBH_MAX". That would make my table looks like:

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)]

这给出了太多的行...

which gives too many rows...

感谢您的帮助

推荐答案

所以我非常接近.我有 2 个问题，首先是 data.table 包的安装错误(数据表错误找不到函数.")导致了一个模糊的错误.

So I was very close. I had 2 problems, first a bad installation of the data.table package (Data table error could not find function ".") caused an obscure error.

解决了这个问题后，我走近发现:

After having fixed that, I got closer an found that :

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

用糟糕的 dbh 列给了我想要的东西.使用以下命令反转命令:

gave me what I wanted with a bad dbh column. Inverting the command with:

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

修复了只有一个无用的额外列的问题.

fixed the problem with only one useless extra column.

这篇关于R中的复杂非等式合并的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！