R - 通过具有附加"between values"条件的两列合并数据帧？

Question

我有两个数据帧，如下所示：

数据帧A:

code1 code2 element1 from to
c1a    c2a     e1a     1    15
c1a    c2a     e1b     17   50
c1a    c2b     e1c     14   67
c1b    c2c     e1d     1    20
c1b    c2d     e1e    40   60

数据帧B:

code1 code2 element2 number
c1a    c2a     e2a    7
c1a    c2a     e2b    10
c1a    c2a     e2c    35

如果from =< number <= to，我基本上需要加入他们，以获得类似的东西

结果数据帧

(片段，我没有足够的模型数据。我希望对完整数据帧A和B进行合并(。

code1 code2 element1 element2 from   to   number
c1a    c2a     e1a    e2a      1     15     7
c1a    c2a     e1a    e2b      1     15     10
c1a    c2a     e1b    e2c      17    50     35

我可以用for循环来做这件事，并手动检查，但我想知道是否有更"优雅"的方法来做这项工作？

Answer 1

您可以连接数据，然后filter范围内的值。

您可以在dplyr中执行此操作

library(dplyr)
left_join(B, A, by = c('code1', 'code2')) %>% 
filter(number >= from & number <= to)
#  code1 code2 element2 number element1 from to
#1   c1a   c2a      e2a      7      e1a    1 15
#2   c1a   c2a      e2b     10      e1a    1 15
#3   c1a   c2a      e2c     35      e1b   17 50

或在基地R:

subset(merge(B, A, by = c('code1', 'code2')), number >= from & number <= to)

Answer 2

下面是一个使用fuzzyjoin::fuzzy_inner_join的例子。我从您的输出中了解到，除了from =< number <= to的标准外，您还希望通过code1和code2加入。

1. 相等地加入code1和code2
2. 通过第一个不等式将from与number连接起来，即from <= number
3. 通过第二个不等式将to与number连接起来，即number <= to

fuzzy_join的问题是它们输出两个数据帧中的所有列。

-

library(fuzzyjoin)
fuzzy_inner_join(
df_A, df_B,
by = c(
"code1" = "code1",
"code2" = "code2",
"from" = "number",
"to" = "number"),
match_fun = c(
"code1" = function(l, r) l == r,
"code2" = function(l, r) l == r,
"from" = function(l, r) l <= r,
"to" = function(l, r) r <= l))
# code1.x code2.x element1 from to code1.y code2.y element2 number
# 1     c1a     c2a      e1a    1 15     c1a     c2a      e2a      7
# 2     c1a     c2a      e1a    1 15     c1a     c2a      e2b     10
# 3     c1a     c2a      e1b   17 50     c1a     c2a      e2c     35

数据

df_A <- structure(list(code1 = c("c1a", "c1a", "c1a", "c1b", "c1b"), 
code2 = c("c2a", "c2a", "c2b", "c2c", "c2d"), element1 = c("e1a", 
"e1b", "e1c", "e1d", "e1e"), from = c(1L, 17L, 14L, 1L, 40L
), to = c(15L, 50L, 67L, 20L, 60L)), class = "data.frame", row.names = c(NA, -5L))
df_B <- structure(list(code1 = c("c1a", "c1a", "c1a"), code2 = c("c2a", 
"c2a", "c2a"), element2 = c("e2a", "e2b", "e2c"), number = c(7L, 
10L, 35L)), class = "data.frame", row.names = c(NA, -3L))