在R中基于时间区间合并两个数据框的实现方案

要实现将df1（完整时间序列）与df2（活动时段）合并，拆分部分重叠区间并新增活动code列的需求，我们可以用dplyr、lubridate和fuzzyjoin工具包处理，具体步骤如下：

1. 安装并加载所需包 #

# 首次运行时安装依赖包
install.packages(c("dplyr", "lubridate", "fuzzyjoin"))

# 加载包
library(dplyr)
library(lubridate)
library(fuzzyjoin)

2. 预处理数据 #

把两个数据框的时间列转换为标准datetime格式，清理df2中冗余的date列：

# 处理df1的时间列
df1_clean <- df1 %>%
  mutate(
    START = ymd_hms(START),
    END = ymd_hms(END)
  )

# 处理df2的时间列，移除无用列
df2_clean <- df2 %>%
  mutate(
    start_time = ymd_hms(start_time),
    endtime = ymd_hms(endtime)
  ) %>%
  select(id, code, start_time, endtime)

3. 匹配重叠的时间区间 #

用模糊连接找到id相同且时间区间有重叠的行：

overlaps <- fuzzy_inner_join(
  df1_clean,
  df2_clean,
  by = c("id" = "id", "START" = "endtime", "END" = "start_time"),
  match_fun = list(`==`, `<`, `>`)
)

4. 拆分部分重叠的区间 #

针对每个重叠行，将原区间拆分为「无活动前半段」「活动重叠段」「无活动后半段」，过滤掉起始时间≥结束时间的无效区间：

split_rows <- overlaps %>%
  rowwise() %>%
  mutate(
    intervals = list(
      tibble(
        START = c(START, start_time, endtime),
        END = c(start_time, endtime, END),
        STREAM_3 = c(NA, code, NA)
      ) %>% filter(START < END)
    )
  ) %>%
  unnest(intervals) %>%
  select(id, STREAM_1, STREAM_2, STREAM_3, START, END)

5. 补充无活动的完整行 #

把df1中完全没有匹配到活动的行补充进来，活动code列设为NA：

non_overlaps <- df1_clean %>%
  anti_join(overlaps, by = c("id", "START", "END")) %>%
  mutate(STREAM_3 = NA) %>%
  select(id, STREAM_1, STREAM_2, STREAM_3, START, END)

6. 合并并排序结果 #

将拆分后的行和无活动行合并，按id和时间排序，得到最终的完整时间序列：

final_result <- bind_rows(split_rows, non_overlaps) %>%
  arrange(id, START)

# 查看前几行结果
head(final_result)

关键逻辑说明 #

• 模糊连接：精准匹配跨区间的重叠记录，不会遗漏部分重叠的情况。
• 区间拆分：确保每个子区间要么完全包含活动，要么完全无活动，符合需求中的拆分要求。
• 结果整合：保留原df1的所有时间节点，新增的STREAM_3列对应活动code，无活动时为NA。

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