R non equi join 返回不存在的列名

发布于05月17日

我无法在两个数据中进行基本的非等联接.R中没有错误的表:

显示错误的最小示例.

library(data.table)
set.seed(1); dt1 <- data.table(id= sample(letters[1:3],size = 10,replace = T),val = rnorm(10,mean = 5,sd = 2))
set.seed(2); dt2 <- data.table(id= sample(letters[1:3],size = 10,replace = T),val = rnorm(10,mean = 5,sd = 2))
dt1[dt2,on = .(id,abs(x.val - i.val) <1),nomatch=0]
#> Error in colnamesInt(x, names(on), check_dups = FALSE): argument specifying columns specify non existing column(s): cols[2]='abs(x.val - i.val)'

^{由reprex package(v2.0.1)于2022年5月17日创建}

这一定是我错过的最基本的东西.

Note: I have deliberately kept column names identical in both data tables to test the x. and i. functionality of data.table.

推荐答案

在两个步骤中，as on仅接受=<或>:

dt1[dt2,on = .(id),nomatch=0,allow.cartesian=T][abs(val - i.val)<1]

   id      val    i.val
1:  a 3.142866 3.175512
2:  b 4.421077 3.494669
3:  b 4.401570 3.494669
4:  a 4.988466 4.428577
5:  a 3.142866 2.931430
6:  a 4.988466 4.943710
7:  a 4.988466 4.267357
8:  a 3.142866 2.769503

或者为了避免笛卡尔积:

dt2[,`:=`(val_min=val-1,val_max=val+1)]
dt1[dt2,.(id,x.val,i.val),on = .(id,val>val_min,val<val_max),nomatch=0]

   id    x.val    i.val
1:  a 3.142866 3.175512
2:  b 4.421077 3.494669
3:  b 4.401570 3.494669
4:  a 4.988466 4.428577
5:  a 3.142866 2.931430
6:  a 4.988466 4.943710
7:  a 4.988466 4.267357
8:  a 3.142866 2.769503