在R语言中基于ID与日期范围合并DataFrame的技术实现

嘿，这个需求我经常碰到！要在R里把两个DataFrame按SiteID匹配，同时让df1的DateTime落在df2对应SiteID的日期区间里，我给你两种实用的方法，结合你的示例数据一步一步来：

先准备示例数据 #

首先得把你给出的示例数据转换成R里的DataFrame，并且确保日期时间列是正确的类型：

library(dplyr)

# 构建df1
df1 <- tibble(
  DateTime = as.POSIXct(c("2010-07-25 01:06:55", "2011-05-10 23:52:14", "2011-09-17 01:14:30",
                          "2012-04-04 02:55:29", "2013-01-05 23:03:06", "2011-03-09 20:39:46",
                          "2012-07-25 23:17:19", "2011-03-03 00:46:45")),
  SiteID = c("B04", "B04", "B04", "B05", "B05", "B06", "B07", "B08")
)

# 构建df2
df2 <- tibble(
  SiteID = c("B04", "B04", "B04", "B04", "B05", "B05", "B05", "B06", "B06", "B06", "B07", "B07", "B08", "B08"),
  Start.date = as.Date(c("2010-07-18", "2011-02-22", "2011-08-30", "2012-10-20",
                         "2011-08-30", "2012-12-08", "2013-02-08", "2010-07-20", "2011-02-12", "2011-05-13",
                         "2011-10-24", "2011-12-29", "2011-02-12", "2011-10-24")),
  End.date = as.Date(c("2010-08-24", "2011-07-23", "2011-08-30", "2012-10-03",
                       "2012-08-21", "2013-01-21", "2013-04-08", "2010-09-03", "2011-04-18", "2011-05-16",
                       "2011-11-29", "2012-12-02", "2011-04-01", "2011-12-24"))
)

方法一：用dplyr实现（直观易懂） #

这种方法先按SiteID做内连接，再筛选出DateTime在对应区间内的行。注意要把df2的日期转换成带时间的格式，确保当天的所有时间都能被包含进去：

library(lubridate)

# 转换df2的日期，将End.date设置为当天最后一秒
df2_processed <- df2 %>%
  mutate(
    Start_datetime = as.POSIXct(Start.date),  # 转成当天00:00:00
    End_datetime = as.POSIXct(End.date) + days(1) - seconds(1)  # 转成当天23:59:59
  )

# 合并并筛选符合条件的行
result <- df1 %>%
  inner_join(df2_processed, by = "SiteID") %>%
  filter(DateTime >= Start_datetime, DateTime <= End_datetime) %>%
  select(DateTime, SiteID, Start.date, End.date)  # 保留需要的列

# 查看结果
print(result)

方法二：用fuzzyjoin高效匹配（适合大数据） #

如果你的数据量很大，全连接再过滤会比较慢，这时候用fuzzyjoin包的模糊匹配更高效，它会直接按条件匹配，不用生成中间的全连接表：

library(fuzzyjoin)
library(lubridate)

# 先处理df2的日期格式
df2_fuzzy <- df2 %>%
  mutate(
    Start.date = as.POSIXct(Start.date),
    End.date = as.POSIXct(End.date) + days(1) - seconds(1)
  )

# 模糊内连接：按SiteID相等，且DateTime在Start.date和End.date之间
result_fuzzy <- fuzzy_inner_join(
  df1,
  df2_fuzzy,
  by = c("SiteID" = "SiteID", "DateTime" = "Start.date", "DateTime" = "End.date"),
  match_fun = list(`==`, `>=`, `<=`)  # 匹配规则
) %>%
  select(DateTime, SiteID, Start.date = Start.date.y, End.date = End.date.y)

# 查看结果
print(result_fuzzy)

两种方法都能生成你期望的结果，dplyr的方法更适合新手理解，fuzzyjoin则在处理大规模数据时性能更优。

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