r - 通过匹配多个条件在一个数据框中基于另一个数据过滤记录

Question

我有以下两个数据帧dat1和dat2:

library(tidyverse)
dat1 <- tribble(
  ~"subj", ~"drive", ~"measure",
  "A", 1, 1,
  "A", 1, 2,
  "A", 1, 3,
  "A", 1, 4,
  "A", 1, 5,
  "A", 2, 1,
  "A", 2, 2,
  "A", 2, 3,
  "A", 2, 4,
  "A", 2, 5,
  "B", 1, 1,
  "B", 1, 2,
  "B", 1, 3,
  "B", 1, 4,
  "B", 1, 5,
  "B", 2, 1,
  "B", 2, 2,
  "B", 2, 3,
  "B", 2, 4,
  "B", 2, 5,
)

dat2 <- tribble(
  ~"subj", ~"drive", ~"measure",
  "A", 1, 3,
  "B", 2, 4
)

我正在尝试根据以下条件过滤 dat1中的记录:
subj的

drive和dat1列应与subj和

的 drive和 dat2列匹配 measure中的

dat1值应在measure中dat2值的范围内。

对于此示例，说范围相隔一个单位。因此，我的结果数据框将如下所示:

result <- tribble(
  ~"subj", ~"drive", ~"measure",
  "A", 1, 2,
  "A", 1, 3,
  "A", 1, 4,
  "B", 2, 3,
  "B", 2, 4,
  "B", 2, 5
)

我知道 dplyr::semi_join()，但它不允许我根据范围进行过滤。有什么想法可以解决这个问题吗？基于 Tidyverse的解决方案将会很棒!

Answer 1

一种选择是先执行inner_join，然后使用between

library(dplyr)
inner_join(dat1, dat2, by = c('subj', 'drive')) %>% 
    group_by(subj, drive) %>% 
    filter(between(measure.x, first(measure.y)-1, first(measure.y) + 1)) %>% 
    select(measure = measure.x)
# A tibble: 6 x 3
# Groups:   subj, drive [2]
#  subj  drive measure
#  <chr> <dbl>   <dbl>
#1 A         1       2
#2 A         1       3
#3 A         1       4
#4 B         2       3
#5 B         2       4
#6 B         2       5

或使用 data.table

library(data.table)
setDT(dat1)[setDT(dat2), .SD[between(measure, i.measure -1,
          i.measure + 1)], on = .(subj, drive), by = .EACHI]
#    subj drive measure
#1:    A     1       2
#2:    A     1       3
#3:    A     1       4
#4:    B     2       3
#5:    B     2       4
#6:    B     2       5