将带时间戳的数据与另一个数据集中的最近时间相匹配.正确矢量化?更快的方法? [英] Matching timestamped data to closest time in another dataset. Properly vectorized? Faster way?
问题描述
我在一个数据帧中有一个时间戳,我试图将它与第二个数据帧中最近的时间戳相匹配,目的是从第二个数据帧中提取数据.有关我的方法的通用示例,请参见下文:
I have a timestamp in one data frame that I am trying to match to the closest timestamp in a second dataframe, for the purpose of extracting data from the second dataframe. See below for a generic example of my approach:
library(lubridate)
data <- data.frame(datetime=ymd_hms(c('2015-04-01 12:23:00 UTC', '2015-04-01 13:49:00 UTC', '2015-04-01 14:06:00 UTC' ,'2015-04-01 14:49:00 UTC')),
value=c(1,2,3,4))
reference <- data.frame(datetime=ymd_hms(c('2015-04-01 12:00:00 UTC', '2015-04-01 13:00:00 UTC', '2015-04-01 14:00:00 UTC' ,'2015-04-01 15:00:00 UTC', '2015-04-01 16:00:00 UTC')),
refvalue=c(5,6,7,8,9))
data$refvalue <- apply(data, 1, function (x){
differences <- abs(as.numeric(difftime(ymd_hms(x['datetime']), reference$datetime)))
mindiff <- min(differences)
return(reference$refvalue[differences == mindiff])
})
data
# datetime value refvalue
# 1 2015-04-01 12:23:00 1 5
# 2 2015-04-01 13:49:00 2 7
# 3 2015-04-01 14:06:00 3 7
# 4 2015-04-01 14:49:00 4 8
这很好用,只是速度很慢,因为在我的实际应用程序中参考数据框非常大.这段代码是否正确矢量化?有没有更快、更优雅的方式来执行这个操作?
This works fine, except it is very slow, because the reference dataframe is quite large in my real-world application. Is this code properly vectorized? Is there a faster, more elegant way of performing this operation?
推荐答案
您可以使用nearest"选项尝试data.table
的滚动连接
You can try data.table
s rolling join using the "nearest" option
library(data.table) # v1.9.6+
setDT(reference)[data, refvalue, roll = "nearest", on = "datetime"]
# [1] 5 7 7 8
这篇关于将带时间戳的数据与另一个数据集中的最近时间相匹配.正确矢量化?更快的方法?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!