如何基于不同时间间隔条件合并两个数据集？

需求说明 #

需要将Dataset2左连接至Dataset1，具体规则如下：

• Dataset1：每日包含不同CB，每个CB下有IV1至IV3（部分IV可能因异常值移除而缺失）
• Dataset2：包含1小时、15分钟、5分钟等不同时间间隔的数据
• 连接规则：
  • 若Dataset2是1小时时间间隔：直接将对应的Value_y匹配到Dataset1相关CB的每一行
  • 若Dataset2是15分钟/5分钟时间间隔：根据Dataset1中每个CB的时间范围，计算落在该范围内的Value_y的平均值，再添加到对应行

示例数据 #

Dataset1示例（dput输出） #

Dataset1 <- structure(list(Date = structure(c(19358, 19358, 19358, 19359, 19359), class = "Date"),
                           CB = c("CB01", "CB01", "CB02", "CB01", "CB02"),
                           IV = c(1, 3, 2, 1, 2),
                           Start_Time = structure(c(1667289600, 1667289600, 1667293200, 1667376000, 1667379600), class = c("POSIXct", "POSIXt"), tzone = "UTC"),
                           End_Time = structure(c(1667293200, 1667293200, 1667296800, 1667379600, 1667383200), class = c("POSIXct", "POSIXt"), tzone = "UTC")),
                      row.names = c(NA, -5L), class = "data.frame")

Dataset2示例（dput输出） #

Dataset2 <- structure(list(Date = structure(c(19358, 19358, 19358, 19358, 19359, 19359), class = "Date"),
                           CB = c("CB01", "CB01", "CB02", "CB02", "CB01", "CB02"),
                           Time_Interval = c("1H", "15MIN", "5MIN", "1H", "15MIN", "5MIN"),
                           Time = structure(c(1667289600, 1667289600, 1667293200, 1667293200, 1667376000, 1667379600), class = c("POSIXct", "POSIXt"), tzone = "UTC"),
                           Value_y = c(25.3, 24.8, 31.2, 30.5, 22.1, 28.7)),
                      row.names = c(NA, -6L), class = "data.frame")

解决方案（R代码） #

library(dplyr)
library(lubridate)

# 统一时间时区，避免匹配出错
Dataset1 <- Dataset1 %>% mutate(across(c(Start_Time, End_Time), ~force_tz(., tzone = "UTC")))
Dataset2 <- Dataset2 %>% mutate(Time = force_tz(Time, tzone = "UTC"))

# 按规则完成左连接与计算
result <- Dataset1 %>%
  left_join(Dataset2 %>% select(Date, CB, Time_Interval, Time, Value_y), by = c("Date", "CB")) %>%
  mutate(in_range = between(Time, Start_Time, End_Time)) %>%
  group_by(Date, CB, IV, Start_Time, End_Time, Time_Interval) %>%
  summarise(
    Value_y = case_when(
      Time_Interval == "1H" ~ first(Value_y[in_range]),
      Time_Interval %in% c("15MIN", "5MIN") ~ mean(Value_y[in_range], na.rm = TRUE)
    ),
    .groups = "drop"
  ) %>%
  replace_na(list(Value_y = NA))

print(result)

代码说明 #

• 时区统一：强制两个数据集的时间字段为UTC时区，避免跨时区导致的时间匹配偏差
• 基础左连接：按Date和CB做左连接，确保Dataset1的所有行都能保留，不会丢失数据
• 时间范围判断：用between快速筛选出落在Dataset1时间区间内的Dataset2数据
• 规则计算：
  • 1小时间隔数据：直接取匹配范围内的对应值（通常1小时间隔与Dataset1的时间范围完全对齐）
  • 15/5分钟间隔数据：计算所有落在当前时间范围内的Value_y平均值
• 缺失值处理：对未匹配到Dataset2数据的行，将Value_y设为NA，保证结果完整性

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