您的位置: 首页  >  技术问答  >  子集数据。基于时间间隔+或 - 日期列表

子集数据。基于时间间隔+或 - 日期列表

分类: 技术问答 • 2022-04-23 18:49:27
问题描述：

我有一个大（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]

相关推荐