子集数据。基于时间间隔+或 - 日期列表 [英] subset data.frame base on a time interval + or - list of dates
问题描述
我有一个大(20,000 obs)data.frame包含小时值,并按唯一ID分组。我还有一个日期列表(每个日期出现在data.frame中)。我试图匹配的日期与data.frame,然后提取在+或 - 从匹配的日期一定的时间间隔之间的数据时间。例如,在以下data.frame中:
I have a large (20,000 obs) data.frame containing hourly values and grouped by unique id. I also have a list of dates (each of the dates occurs in the data.frame). I am trying to match the dates to the data.frame, and then extract datetimes that are between + or – a certain time interval from the matching date. For example, in the following data.frame:
setAs("character","myDate", function(from) as.POSIXct(from, "%m/%e/%Y %H:%M", tz="UTC"))
# previous function formats date input as UTC
df <- read.table(textConnection("datetimeUTC id value
'5/1/2013 5:00' 153 0.53
'5/1/2013 6:00' 153 0.46
'5/1/2013 7:00' 153 0.53
'5/1/2013 8:00' 153 0.46
'5/1/2013 9:00' 153 0.44
'5/1/2013 10:00' 153 0.48
'5/1/2013 11:00' 153 0.49
'5/1/2013 12:00' 153 0.49
'5/1/2013 13:00' 153 0.51
'5/1/2013 14:00' 153 0.53
'11/24/2013 9:00' 154 0.45
'11/24/2013 10:00' 154 0.46
'11/24/2013 11:00' 154 0.49
'11/24/2013 12:00' 154 0.55
'11/24/2013 13:00' 154 0.61
'11/24/2013 14:00' 154 0.7
'11/24/2013 15:00' 154 0.74
'11/24/2013 16:00' 154 0.78
'11/24/2013 17:00' 154 0.77
'11/24/2013 18:00' 154 0.79
'8/2/2015 1:00' 240 0.2
'8/2/2015 2:00' 240 0.2
'8/2/2015 3:00' 240 0.2
'8/2/2015 4:00' 240 0.22
'8/2/2015 5:00' 240 0.22
'8/2/2015 6:00' 240 0.27
'8/2/2015 7:00' 240 0.23
'8/2/2015 8:00' 240 0.21
'8/2/2015 9:00' 240 0.22
'8/2/2015 10:00' 240 0.22
'8/2/2015 11:00' 240 0.21
'8/2/2015 12:00' 240 0.21
'8/2/2015 13:00' 240 0.21
'8/2/2015 14:00' 240 0.22
'8/2/2015 15:00' 240 0.24
'8/2/2015 16:00' 240 0.25
'8/2/2015 17:00' 240 0.12
'8/2/2015 18:00' 240 0.32
"), header=TRUE, colClasses=c("myDate", "character", "numeric"))
我想为每个id提取这个键匹配datetime之前或之后2小时的所有观察值:
I want to extract, for each id, all observations that are 2 hours before or after the matching datetime from this key:
key <-read.table(textConnection("
datetimeUTC id
'5/1/2013 9:00' 153
'11/24/2013 14:00' 154
'8/2/2015 5:00' 240
'8/2/2015 15:00' 240"), header=TRUE, colClasses=c("myDate", "character"))
如下:
result <- read.table(textConnection("datetimeUTC id value
'5/1/2013 7:00' 153 0.53
'5/1/2013 8:00' 153 0.46
'5/1/2013 9:00' 153 0.44
'5/1/2013 10:00' 153 0.48
'5/1/2013 11:00' 153 0.49
'11/24/2013 12:00' 154 0.55
'11/24/2013 13:00' 154 0.61
'11/24/2013 14:00' 154 0.7
'11/24/2013 15:00' 154 0.74
'11/24/2013 16:00' 154 0.78
'8/2/2015 3:00' 240 0.2
'8/2/2015 4:00' 240 0.22
'8/2/2015 5:00' 240 0.22
'8/2/2015 6:00' 240 0.27
'8/2/2015 7:00' 240 0.23
'8/2/2015 13:00' 240 0.21
'8/2/2015 14:00' 240 0.22
'8/2/2015 15:00' 240 0.24
'8/2/2015 16:00' 240 0.25
'8/2/2015 17:00' 240 0.12
"), header=TRUE, colClasses=c("myDate", "character", "numeric"))
看起来像一个简单的任务,但我似乎不能得到我想要的。我尝试过的几件事。
Seems like a simple task but I can't seem to get what I want. A couple of things that I have tried.
result <-df[which(df$id == key$id &(df$datetimeUTC >= key$datetimeUTC -2*60*60 |df$datetimeUTC <= key$datetimeUTC + 2*60*60 )),]
library(data.table)
dt <- setDT(df)
dt[dt$datetimeUTC %between% c(dt$datetimeUTC - 2*60*60,dt$datetimeUTC + 2*60*60) ]
推荐答案
两个 data.table
为您提供解决方案
1。笛卡尔加入
将所有加入,然后过滤出您不想要的
join it all together, then filter out the ones you don't want
library(data.table)
dt <- as.data.table(df)
dt_key <- as.data.table(key)
dt_join <- dt[ dt_key, on="id", allow.cartesian=T][difftime(i.datetimeUTC, datetimeUTC, units="hours") <= 2 & difftime(i.datetimeUTC, datetimeUTC, units="hours") >= -2]
# datetimeUTC id value i.datetimeUTC
#1: 2013-05-01 07:00:00 153 0.53 2013-05-01 09:00:00
#2: 2013-05-01 08:00:00 153 0.46 2013-05-01 09:00:00
#3: 2013-05-01 09:00:00 153 0.44 2013-05-01 09:00:00
#4: 2013-05-01 10:00:00 153 0.48 2013-05-01 09:00:00
... etc
2。每个条件I
使用答案指向 j
中的 EACHI
在连接中必须满足的条件。
Making use of an answer to one of my previous questions, specify the condition in j
that EACHI
has to meet in the join.
dt[ dt_key,
{ idx = difftime(i.datetimeUTC, datetimeUTC, units="hours") <= 2 & difftime(i.datetimeUTC, datetimeUTC, units="hours") >= -2
.(datetime = datetimeUTC[idx],
value = value[idx])
},
on=c("id"),
by=.EACHI]
这篇关于子集数据。基于时间间隔+或 - 日期列表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!