基于relations子集匹配值筛选objects数据表的技术问题

数据定义 #

library(data.table)

objects <- data.table("ID" = c(5,4,3,2,1), "Perimeter" = c(500,400,300,200,100))
# objects数据表：
#    ID Perimeter
# 1:  5       500
# 2:  4       400
# 3:  3       300
# 4:  2       200
# 5:  1       100

relations <- data.table("ID.x" = c(1,1,1,1,2,2,2,2,3,3,3,3,4,4,4,4,5,5,5,5), 
                        "ID.y" = c(2,3,4,5,1,3,4,5,1,2,4,5,1,2,3,5,1,2,3,4), 
                        "Border" = c(20,40,60,80,20,70,10,60,40,70,150,20,60,10,150,90,80,60,20,90))
# relations数据表：
#    ID.x ID.y Border
# 1:    1    2     20
# 2:    1    3     40
# 3:    1    4     60
# 4:    1    5     80
# 5:    2    1     20
# 6:    2    3     70
# 7:    2    4     10
# 8:    2    5     60
# 9:    3    1     40
#10:    3    2     70
#11:    3    4    150
#12:    3    5     20
#13:    4    1     60
#14:    4    2     10
#15:    4    3    150
#16:    4    5     90
#17:    5    1     80
#18:    5    2     60
#19:    5    3     20
#20:    5    4     90

需求说明 #

筛选出所有Perimeter减去与ID=4的对象的共享Border长度小于180的对象，预期结果为ID=1和ID=3的对象。

错误尝试分析 #

最初尝试的代码因长度不匹配报错：

objects[ID!=4][Perimeter - relations[ID.x==4 & ID.y==ID]$Border < 180]

错误信息：

Error: RHS of == is length 4 which is not 1 or nrow (20). For robustness, no recycling is allowed (other than of length 1 RHS). Consider %in% instead.

改用%in%后，因relations返回的行顺序与objects[ID!=4]的顺序不匹配，导致Border值对应错误：

print(objects[ID!=4][, border_length_with_obj_4 := relations[ID.x==4 & ID.y%in%ID]$Border])
#    ID Perimeter border_length_with_obj_4
# 1:  5       500                       60  # 应为90
# 2:  3       300                       10  # 应为150
# 3:  2       200                      150  # 应为10
# 4:  1       100                       90  # 应为60

最终得到错误的筛选结果。

正确解法 #

方法1：合并数据表后筛选 #

先从relations中提取ID=4对应的边界数据，再与objects关联，确保ID与Border值一一对应：

# 提取ID=4作为ID.x时的边界数据，重命名ID.y为ID
border_with_4 <- relations[ID.x == 4, .(ID = ID.y, Border_with_4 = Border)]
# 关联objects（排除ID=4）与border_with_4
result <- objects[ID != 4][border_with_4, on = "ID"]
# 筛选满足条件的行，保留ID和Perimeter列
result[Perimeter - Border_with_4 < 180, .(ID, Perimeter)]

方法2：按ID分组匹配边界值 #

通过by=ID分组，为每个ID匹配对应的Border值后筛选：

objects[ID != 4][, 
  .(ID, Perimeter, Border_with_4 = relations[ID.x == 4 & ID.y == .BY$ID, Border]), 
  by = ID
][Perimeter - Border_with_4 < 180, .(ID, Perimeter)]

预期输出 #

ID Perimeter
1:  3       300
2:  1       100

内容的提问来源于stack exchange，提问作者Wander Demuynck