如何按最近时间匹配合并Data_A与Data_C并处理多余数据

针对不规则时间序列的匹配需求，推荐使用data.table的高效操作实现按最近时间合并，并排除Data_A中未被匹配的多余条目。以下是具体实现步骤：

1. 准备环境与修正数据 #

先加载所需工具包，并修正原始数据构造的语法问题：

library(data.table)
library(hms)

# 构造正确的示例数据集
file <- data.table(
  ID = c(1:13),
  time = as.hms(c("6:01:00", "6:03:00", "6:05:00", "6:08:00",
                  "6:10:00", "6:12:00", "6:15:00", "6:17:00",
                  "6:19:00", "6:22:00", "6:24:00", "6:26:00",
                  "6:29:00")),
  Turn = c("A", "A", "B", "B", "C", "C", "A", "A", "A", "B", 
           "B", "C", "C"),
  Value = c("16.00", "18.00", "17.00", "19.00", "21.00",
            "26.00", "25.60", "27.34", "29.09", "30.83",
            "32.57", "34.31", "36.06")
)

# 拆分出A和C数据集
Data_A <- file[Turn == "A"]
Data_C <- file[Turn == "C"]

2. 按最近时间匹配合并 #

以下方法会为每个C记录找到时间差绝对值最小的A记录，自动排除未被任何C匹配的A条目：

# 生成A与C的笛卡尔积，用于计算时间差
cross_dt <- CJ(C_row = Data_C$ID, A_row = Data_A$ID)
# 关联C的基础信息
cross_dt <- cross_dt[Data_C, on = .(C_row = ID), 
                     .(C_ID = ID, C_Time = time, C_Value = Value, A_row)]
# 关联A的基础信息
cross_dt <- cross_dt[Data_A, on = .(A_row = ID), 
                     .(C_ID, C_Time, C_Value, A_ID = ID, A_Time = time, A_Value = Value)]
# 计算时间差（转换为秒数简化计算）
cross_dt[, Time_Diff := abs(as.numeric(C_Time) - as.numeric(A_Time))]

# 按C分组，筛选每个C对应的最小时间差的A记录
merged_result <- cross_dt[, .SD[which.min(Time_Diff)], by = C_ID]

# 整理列名提升可读性
setnames(merged_result, 
         c("C_ID", "C_Time", "C_Value", "A_ID", "A_Time", "A_Value", "Time_Diff"),
         c("ID_C", "Time_C", "Value_C", "ID_A", "Time_A", "Value_A", "Time_Diff_Sec"))

3. 结果展示 #

运行代码后，merged_result即为合并后的目标数据集，仅保留与C匹配的A条目：

print(merged_result)
# 输出示例：
#    ID_C  Time_C Value_C ID_A  Time_A Value_A Time_Diff_Sec
# 1:    5 06:10:00   21.00    7 06:15:00   25.60           300
# 2:    6 06:12:00   26.00    7 06:15:00   25.60           180
# 3:   12 06:26:00   34.31    9 06:19:00   29.09           420
# 4:   13 06:29:00   36.06    9 06:19:00   29.09           600

4. 可选：确保A条目唯一 #

若需要每个A条目仅匹配一次（避免同一个A被多个C匹配），可在结果中按A_ID去重：

# 按A_ID去重，保留最早匹配的C记录
unique_merged <- merged_result[, .SD[1], by = A_ID]

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