如何实现df2起止时间完全落入df1区间的R数据框精确连接？

我尝试用interval_left_join对两个带时间区间的数据框进行连接，但结果不符合预期。默认的interval_left_join只要df2的时间区间与df1的区间有端点重合就会匹配，而我需要的是df2的整个时间区间完全落入df1的对应区间内（即df2的Starttime_ms ≥ df1的Starttime_ms，且df2的Endtime_ms ≤ df1的Endtime_ms）。

数据框定义 #

df1 <- structure(list(Utterance = c("(5.127)", ">like how old 's your mom¿ ", 
                                "(0.855)", "eh six:ty:::-one= ", "(0.101)", "(0.166)", "=NOW ", 
                                "(0.622)", "!how! this always plays out  ", "(0.726)", "[when was] that¿= ", 
                                "[yes] ", "(0.163)", "=!this! was on °Wednesday° ", "(0.273)", 
                                "oka[y] "), Starttime_ms = c(0, 5127, 6830, 7685, 9889, 176800, 
                                                             176966, 177372, 177994, 179328, 180054, 180135, 180668, 180831, 
                                                             181720, 181993), Endtime_ms = c(5127, 6830, 7685, 9889, 9990, 
                                                                                             176966, 177372, 177994, 179328, 180054, 180668, 180555, 180831, 
                                                                                             181720, 181993, 182398)), row.names = c(NA, -16L), class = c("tbl_df", 
                                                                                                                                                          "tbl", "data.frame"))

df2 <- structure(list(Q_segment = c(">like I don 't understand< ", "sorry like how old 's your mom¿", 
                                    "[when was] that¿=", "=[did she let you in?]", "=holy [sh:]i:[:t", 
                                    "does it happen] often? ", "[like] ", "[what] type of:: tours is it ", 
                                    "is it [(like a long] ti:me¿", "[or]"), Starttime_ms = c(5127, 
                                                                                             5963, 180054, 253226, 345450, 347454, 348459, 478500, 480669, 
                                                                                             481763), Endtime_ms = c(5963, 6830, 180668, 254156, 347454, 348459, 
                                                                                                                     348724, 480669, 481763, 482000)), row.names = c(NA, -10L), class = c("tbl_df", 
                                                                                                                                                                                          "tbl", "data.frame"))

默认连接的问题 #

使用interval_left_join的默认逻辑会得到不符合需求的结果：

# 安装依赖包（如果未安装）
# if (!requireNamespace("BiocManager", quietly = TRUE))
#   install.packages("BiocManager")
# BiocManager::install("IRanges")
library(BiocManager)
library(fuzzyjoin)
library(data.table)

interval_left_join(x = df1,
                   y = df2,
                   by = c("Starttime_ms", "Endtime_ms"))

结果中出现了不需要的匹配，比如df1的(5.127)区间[0,5127]匹配了df2的>like...区间[5127,5963]，以及df1的(0.855)区间[6830,7685]匹配了df2的how old...区间[5963,6830]——这些仅端点重合但区间并不包含的情况都被错误匹配了。

期望结果 #

仅保留df2区间完全落入df1区间内的匹配项：

Utterance                       Starttime_ms.x Endtime_ms.x Q_segment            Starttime_ms.y Endtime_ms.y
   <chr>                                    <dbl>        <dbl> <chr>                         <dbl>        <dbl>
 1 "(5.127)"                                    0         5127 NA                               NA           NA
 2 ">like how old 's your mom¿ "             5127         6830 >like                          5127         5963
 3 ">like how old 's your mom¿ "             5127         6830 how old 's your mom¿           5963         6830
 4 "(0.855)"                                 6830         7685 NA                               NA           NA
 5 "eh six:ty:::-one= "                      7685         9889 NA                               NA           NA
 6 "(0.101)"                                 9889         9990 NA                               NA           NA
 7 "(0.166)"                               176800       176966 NA                               NA           NA
 8 "=NOW "                                 176966       177372 NA                               NA           NA
 9 "(0.622)"                               177372       177994 NA                               NA           NA
10 "!how! this always plays out  "         177994       179328 NA                               NA           NA
11 "(0.726)"                               179328       180054 NA                               NA           NA
12 "[when was] that¿= "                    180054       180668 [when was] that¿=            180054       180668
13 "[yes] "                                180135       180555 NA                               NA           NA
14 "(0.163)"                               180668       180831 NA                               NA           NA
15 "=!this! was on °Wednesday° "           180831       181720 NA                               NA           NA
16 "(0.273)"                               181720       181993 NA                               NA           NA
17 "oka[y] "                               181993       182398 NA                               NA           NA

解决方案：自定义模糊匹配 #

使用fuzzy_left_join明确指定匹配条件，确保df2的区间完全被df1的区间包含：

library(fuzzyjoin)

fuzzy_left_join(
  x = df1,
  y = df2,
  by = c("Starttime_ms", "Endtime_ms"),
  match_fun = list(`<=`, `>=`)
)

这里的match_fun参数定义了严格的匹配规则：

• 要求df2$Starttime_ms >= df1$Starttime_ms（对应df1$Starttime_ms <= df2$Starttime_ms）
• 要求df2$Endtime_ms <= df1$Endtime_ms（对应df1$Endtime_ms >= df2$Endtime_ms）

运行这段代码后，就能得到符合期望的结果，仅保留df2区间完全落入df1区间内的匹配项。

内容的提问来源于stack exchange，提问作者Chris Ruehlemann