在R中高效左连接时间戳范围内的大型时间序列数据集

数据集说明 #

1. 秒级心率时间序列数据 allsecsHR #

包含140万条秒级记录，示例：

> allsecsHR
timestamp               HRbpm
2023-03-22 09:04:53     101
2023-03-22 09:04:54     124
2023-03-22 09:04:55     103
2023-03-22 09:04:56     111
2023-03-22 09:04:57     112
2023-03-22 09:04:58     143
2023-03-22 09:04:59     109
2023-03-22 09:05:00     129
2023-03-22 09:05:01     122
2023-03-22 09:05:02     125
2023-03-22 09:05:03     110

2. 行为区间记录数据集 bhr #

每条记录包含行为类型及起止时间，示例：

> bhr
Behaviour  BhrTimeStart         BhrTimeEnd
Forage     2023-03-22 09:04:53  2023-03-22 09:04:58
Vigilance  2023-03-22 09:04:58  2023-03-22 09:05:03
Forage     2023-03-22 09:05:03  2023-03-22 09:05:10

期望输出 #

将每条心率记录匹配到对应的行为区间，得到秒级带行为标签的数据集，示例：

timestamp           HRbpm   Bhr         BhrTimeStart        BhrTimeEnd
2023-03-22 09:04:53 101     Forage      2023-03-22 09:04:53   2023-03-22 09:04:58
2023-03-22 09:04:54 124     Forage      2023-03-22 09:04:53   2023-03-22 09:04:58
2023-03-22 09:04:55 103     Forage      2023-03-22 09:04:53   2023-03-22 09:04:58
2023-03-22 09:04:56 111     Forage      2023-03-22 09:04:53   2023-03-22 09:04:58
2023-03-22 09:04:57 112     Forage      2023-03-22 09:04:53   2023-03-22 09:04:58
2023-03-22 09:04:58 143     Vigilance   2023-03-22 09:04:58   2023-03-22 09:05:03
2023-03-22 09:04:59 109     Vigilance   2023-03-22 09:04:58   2023-03-22 09:05:03
2023-03-22 09:05:00 129     Vigilance   2023-03-22 09:04:58   2023-03-22 09:05:03
2023-03-22 09:05:01 122     Vigilance   2023-03-22 09:04:58   2023-03-22 09:05:03
2023-03-22 09:05:02 125     Vigilance   2023-03-22 09:04:58   2023-03-22 09:05:03
2023-03-22 09:05:03 110     Forage      2023-03-22 09:05:03   2023-03-22 09:05:10

已尝试方案及问题 #

MySQL/sqldf方案 #

library(sqldf)
sqldf("select * from allsecsHR
              left join bhr
              on allsecsHR.timestamp between bhr.BhrTimeStart and bhr.BhrTimeEnd")

问题：运行2小时后MySQL连接超时，未得到结果。

powerjoin方案 #

library(powerjoin)
power_left_join(
  allsecsHR, bhr, 
  by = ~.x$timestamp > .y$BhrTimeStart & 
    (.x$timestamp < .y$BhrTimeEnd | is.na(.y$BhrTimeEnd)),
  keep = "left")

问题：R程序崩溃，或返回Error: vector memory exhausted (limit reached?)。

高效解决方案 #

1. data.table方案（推荐，性能最优） #

data.table的非等连接在处理大数据时效率极高，内存占用低。

library(data.table)

# 转换为data.table，确保时间列是POSIXct类型（若未转换）
setDT(allsecsHR)
setDT(bhr)

# 为时间列设置索引，加速连接
setkey(allsecsHR, timestamp)
setkey(bhr, BhrTimeStart, BhrTimeEnd)

# 执行非等左连接
result_dt <- allsecsHR[bhr, 
                       on = .(timestamp >= BhrTimeStart, timestamp <= BhrTimeEnd),
                       .(timestamp = x.timestamp, HRbpm, Behaviour, BhrTimeStart, BhrTimeEnd)]

# 重命名列以匹配期望输出
setnames(result_dt, "Behaviour", "Bhr")

注：若存在某秒同时属于多个行为区间的情况，会生成多条记录，可根据需求调整逻辑。

2. tidyverse方案（dplyr + fuzzyjoin） #

使用fuzzyjoin的区间匹配功能，结合dplyr管道语法，代码易读，适合熟悉tidy风格的用户。

library(dplyr)
library(fuzzyjoin)

# 确保时间列是POSIXct类型
allsecsHR <- allsecsHR %>% mutate(timestamp = as.POSIXct(timestamp))
bhr <- bhr %>% mutate(across(c(BhrTimeStart, BhrTimeEnd), as.POSIXct))

# 执行模糊左连接
result_tidy <- fuzzy_left_join(
  allsecsHR,
  bhr,
  by = c("timestamp" = "BhrTimeStart", "timestamp" = "BhrTimeEnd"),
  match_fun = list(`>=`, `<=`)
) %>%
  select(timestamp, HRbpm, Bhr = Behaviour, BhrTimeStart, BhrTimeEnd)

性能优化提示 #

• 确保所有时间列都是POSIXct类型，避免字符类型时间比较，大幅提升效率
• data.table方案中，设置索引是关键，能显著减少连接时间
• 若内存仍紧张，可分批次处理行为区间后再合并结果

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