如何基于另一数据框的时间区间移除时序数据框的行？

有一个记录事件起止时间的smaller数据框，需要从更大的时序数据框larger中移除所有处于smaller各时间区间内（且对应Valve匹配）的行。尝试了一段代码但无法完整移除目标行，想知道是否可以用%in%解决这个问题？

数据结构 #

smaller数据框 #

smaller <- structure(list(MinDate = structure(c(1664433052, 1664433041), class = c("POSIXct", "POSIXt"), tzone = ""), MaxDate = structure(c(1664433054, 1664433047), class = c("POSIXct", "POSIXt"), tzone = ""), Valve = c("FSV-999", "FSV-001")), row.names = c(NA, -2L), class = "data.frame")

larger数据框 #

larger <- structure(list(DateTime = structure(c(1664433040, 1664433041, 
1664433042, 1664433043, 1664433044, 1664433045, 1664433046, 1664433047, 
1664433048, 1664433049, 1664433050, 1664433050, 1664433051, 1664433051, 
1664433052, 1664433052, 1664433053, 1664433053, 1664433054, 1664433054, 
1664433055, 1664433056, 1664433057, 1664433058, 1664433059, 1664433060
), tzone = "", class = c("POSIXct", "POSIXt")), Valve = c("FSV-001", 
"FSV-001", "FSV-001", "FSV-001", "FSV-001", "FSV-001", "FSV-001", 
"FSV-001", "FSV-001", "FSV-001", "FSV-001", "FSV-999", "FSV-001", 
"FSV-999", "FSV-001", "FSV-999", "FSV-001", "FSV-999", "FSV-001", 
"FSV-999", "FSV-001", "FSV-001", "FSV-001", "FSV-001", "FSV-001", 
"FSV-001")), row.names = c(NA, -26L), class = "data.frame")

期望结果（end数据框） #

end <- structure(list(DateTime = structure(c(1664433040, 1664433048, 
1664433049, 1664433050, 1664433050, 1664433051, 1664433051, 1664433052, 
1664433053, 1664433054, 1664433055, 1664433056, 1664433057, 1664433058, 
1664433059, 1664433060), class = c("POSIXct", "POSIXt"), tzone = ""), 
    Valve = c("FSV-001", "FSV-001", "FSV-001", "FSV-001", "FSV-999", 
    "FSV-001", "FSV-999", "FSV-001", "FSV-001", "FSV-001", "FSV-001", 
    "FSV-001", "FSV-001", "FSV-001", "FSV-001", "FSV-001")), row.names = c(1L, 
9L, 10L, 11L, 12L, 13L, 14L, 15L, 17L, 19L, 21L, 22L, 23L, 24L, 
25L, 26L), class = "data.frame")

尝试过的代码 #

larger %>%
  mutate(Status = "Keep") %>%
  mutate(Status = case_when(DateTime >= smaller$MinDate & DateTime <= smaller$MaxDate ~ "Throw", TRUE ~ as.character(Status))) %>%
  filter(Status != "Throw") %>%
  mutate(Status = NULL)

原代码失效原因 #

原代码直接用smaller$MinDate和smaller$MaxDate做向量匹配，触发了R的循环补齐机制，仅用smaller的第一行区间匹配所有larger行，既没考虑Valve的对应关系，也没遍历所有smaller的时间区间，因此无法完整移除目标行。

关于%in%的可行性 #

%in%仅用于判断元素是否存在于某个向量中，无法直接处理「时间落在区间内+Valve匹配」的复合条件，因此单独用%in%解决不了这个问题。

正确实现方法 #

方法1：用dplyr::rowwise()逐行判断 #

逐行检查larger的每一行是否匹配smaller中任意一组Valve+时间区间的条件，过滤掉符合条件的行：

library(dplyr)

result <- larger %>%
  rowwise() %>%
  filter(!any(Valve == smaller$Valve & DateTime >= smaller$MinDate & DateTime <= smaller$MaxDate)) %>%
  ungroup()

# 验证与期望结果一致
all.equal(result, end)
# [1] TRUE

方法2：用fuzzyjoin包做模糊匹配后过滤 #

先通过模糊匹配找到所有需要移除的行，再从larger中剔除：

library(fuzzyjoin)

to_remove <- fuzzy_inner_join(
  larger,
  smaller,
  by = c("Valve" = "Valve", "DateTime" = "MinDate", "DateTime" = "MaxDate"),
  match_fun = list(`==`, `>=`, `<=`)
)

result <- anti_join(larger, to_remove, by = c("DateTime", "Valve"))

all.equal(result, end)
# [1] TRUE

方法3：用purrr遍历区间生成过滤标记 #

library(purrr)

# 生成需要移除的逻辑标记
remove_flag <- pmap_lgl(smaller, function(MinDate, MaxDate, Valve) {
  larger$Valve == Valve & larger$DateTime >= MinDate & larger$DateTime <= MaxDate
}) %>%
  rowSums() > 0

result <- larger[!remove_flag, ]

all.equal(result, end)
# [1] TRUE

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