如何基于最接近ms值合并加速度计与磁力计数据集？

问题说明 #

现有两个数据集：ACCL_20220217_01_（加速度计数据）和MAG_20220217_01_（磁力计数据），二者毫秒级测量时间点不匹配，需要基于最接近的ms值完成合并，期望输出格式如下：

time                ms_acc  x_acc   y_acc   z_acc   ms_mag  x_mag   y_mag   z_mag
1   2022-02-17 01:13    0       12800   4480    1600    0       -305    735     24
2   2022-02-17 01:13    20      12096   3200    2048    0       -305    735     24
3   2022-02-17 01:13    41      11776   3008    1856    0       -305    735     24
4   2022-02-17 01:13    61      15872   5952    1408    96      -308    765     63
5   2022-02-17 01:13    81      17216   9856    1664    96      -308    765     63
6   2022-02-17 01:13    102     20096   14464   2176    96      -308    765     63

此前尝试的问题分析 #

1. fuzzy_left_join报错原因
   执行代码：

   fuzzy_left_join(
     MAG_20220217_01_, ACCL_20220217_01_,
     by = c(
       "time" = "time",
       "ms" = "ms"), match_fun = list(`==`, `>=`, `<=`))
   

   触发错误：Error: Length of match_fun not equal to columns specified in 'by'.
   问题出在by指定了2列匹配，但match_fun传入了3个函数，数量不匹配导致报错。

2. difference_left_join行重复问题
   执行代码：

   temp <- difference_left_join(ACCL_20220217_01_, MAG_20220217_01_, by= c("time", "ms"), max_dist = 100)
   

   该函数会匹配所有在max_dist范围内的记录，导致单条加速度计数据对应多条磁力计数据，出现行重复。

可行解决方案 #

方法1：data.table滚动连接（高效推荐） #

滚动连接可精准匹配每个加速度计数据点对应的最接近磁力计数据点：

library(data.table)

# 转换为data.table格式
setDT(ACCL_20220217_01_)
setDT(MAG_20220217_01_)

# 重命名列避免合并时冲突
ACCL_20220217_01_ <- ACCL_20220217_01_[, .(time, ms_acc = ms, x_acc, y_acc, z_acc)]
MAG_20220217_01_ <- MAG_20220217_01_[, .(time, ms_mag = ms, x_mag, y_mag, z_mag)]

# 按time分组，对ms_acc执行滚动匹配最接近的ms_mag
merged_data <- MAG_20220217_01_[ACCL_20220217_01_, on = .(time, ms_mag = ms_acc), roll = "nearest"]

# 调整列顺序为期望格式
setcolorder(merged_data, c("time", "ms_acc", "x_acc", "y_acc", "z_acc", "ms_mag", "x_mag", "y_mag", "z_mag"))

方法2：dplyr结合which.min匹配 #

通过计算每个ms_acc与所有ms_mag的差值绝对值，找到最接近的磁力计数据：

library(dplyr)

# 为磁力计数据添加索引
mag_data <- MAG_20220217_01_ %>%
  mutate(mag_idx = row_number())

# 逐行匹配最接近的磁力计数据
merged_data <- ACCL_20220217_01_ %>%
  rowwise() %>%
  mutate(
    closest_idx = which.min(abs(ms - mag_data$ms)),
    ms_mag = mag_data$ms[closest_idx],
    x_mag = mag_data$x_mag[closest_idx],
    y_mag = mag_data$y_mag[closest_idx],
    z_mag = mag_data$z_mag[closest_idx]
  ) %>%
  ungroup() %>%
  select(time, ms_acc = ms, x_acc, y_acc, z_acc, ms_mag, x_mag, y_mag, z_mag)

数据集dput信息 #

加速度计数据 #

dput(ACCL_20220217_01_)
structure(list(time = structure(c(1645056800, 1645056800.02, 1645056800.041, ...), class = c("POSIXct", "POSIXt"), tzone = "Europe/Paris"), ms = c(0, 20, 41, ...), ms_to_on_acc = c(551185497, 551185517, 551185538, ...), x_acc = c(12800, 12096, 11776, ...), y_acc = c(4480, 3200, 3008, ...), z_acc = c(1600, 2048, 1856, ...)), row.names = c(NA, 196L), class = "data.frame")

磁力计数据 #

dput(MAG_20220217_01_)
structure(list(time = structure(c(1645056800, 1645056800.096, 1645056800.192, ...), class = c("POSIXct", "POSIXt"), tzone = "Europe/Paris"), ms = c(0, 96, 192, ...), ms_to_on_mag = c(551175445, 551175541, 551175637, ...), x_mag = c(-305, -308, -301, ...), y_mag = c(735, 765, 752, ...), z_mag = c(24, 63, 24, ...)), row.names = c(NA, 42L), class = "data.frame")

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