随时间间隔合并记录

Question

首先，我想说这个问题涉及到R（统计编程语言），但我也愿意接受其他环境的直接建议。

目标是将数据框（df）A的结果合并到df B的子元素中。这是一对多的关系，但是有一个曲折，即一旦记录通过键匹配，它们还必须在特定的时间帧内匹配，该时间由开始时间和持续时间给出。

例如，df A中的一些记录：

    OBS ID StartTime Duration Outcome 
    1   01 10:12:06  00:00:10 Normal
    2   02 10:12:30  00:00:30 Weird
    3   01 10:15:12  00:01:15 Normal
    4   02 10:45:00  00:00:02 Normal

同时从df B中：

    OBS ID Time       
    1   01 10:12:10  
    2   01 10:12:17  
    3   02 10:12:45  
    4   01 10:13:00  

合并的期望结果应该是:

    OBS ID Time     Outcome  
    1   01 10:12:10 Normal 
    3   02 10:12:45 Weird 

希望的结果是：从A记录中合并结果到数据框B中。请注意，观测值2和4被删除，因为尽管它们与A中的记录ID匹配，但它们没有落在任何给定的时间间隔内。

问题：

在R中是否可以执行此类操作，如何开始？如果不能，请建议另一种工具。

- bnjmn

Answer 1

设置数据

首先设置输入数据框。我们创建两个版本的数据框：A和B，只使用字符列来表示时间，At和Bt使用chron包中的"times"类来表示时间（相对于"character"类，它具有可以进行加减运算的优点）：

LinesA <- "OBS ID StartTime Duration Outcome 
    1   01 10:12:06  00:00:10 Normal
    2   02 10:12:30  00:00:30 Weird
    3   01 10:15:12  00:01:15 Normal
    4   02 10:45:00  00:00:02 Normal"

LinesB <- "OBS ID Time       
    1   01 10:12:10  
    2   01 10:12:17  
    3   02 10:12:45  
    4   01 10:13:00"

A <- At <- read.table(textConnection(LinesA), header = TRUE, 
               colClasses = c("numeric", rep("character", 4)))
B <- Bt <- read.table(textConnection(LinesB), header = TRUE, 
               colClasses = c("numeric", rep("character", 2)))

# in At and Bt convert times columns to "times" class

library(chron) 

At$StartTime <- times(At$StartTime)
At$Duration <- times(At$Duration)
Bt$Time <- times(Bt$Time)

使用times类的sqldf

现在我们可以使用sqldf包进行计算。我们使用method="raw"（不会将类分配给输出），因此必须自己为输出的"Time"列分配"times"类：

library(sqldf)

out <- sqldf("select Bt.OBS, ID, Time, Outcome from At join Bt using(ID)
   where Time between StartTime and StartTime + Duration",
   method = "raw")

out$Time <- times(as.numeric(out$Time))

结果是：

> out
      OBS ID     Time Outcome
1   1 01 10:12:10  Normal
2   3 02 10:12:45   Weird

使用sqldf的开发版本，可以在不使用method="raw"的情况下完成此操作，并且通过sqldf类分配启发式算法，"Time"列将自动设置为"times"类：

library(sqldf)
source("http://sqldf.googlecode.com/svn/trunk/R/sqldf.R") # grab devel ver 
sqldf("select Bt.OBS, ID, Time, Outcome from At join Bt using(ID)
    where Time between StartTime and StartTime + Duration")

使用字符类的sqldf

实际上可以通过在SQLite中使用strftime函数，将所有时间计算都转换为字符字符串来避免使用"times"类。不幸的是，SQL语句会更加复杂:

sqldf("select B.OBS, ID, Time, Outcome from A join B using(ID)
    where strftime('%s', Time) - strftime('%s', StartTime)
       between 0 and strftime('%s', Duration) - strftime('%s', '00:00:00')")

编辑：

一系列编辑修正了语法，增加了额外的方法，并修复/改进了 read.table 语句。

编辑：

简化/改进了最终的 sqldf 语句。

- G. Grothendieck

Answer 2

以下是一个例子：

# first, merge by ID
z <- merge(A[, -1], B, by = "ID")

# convert string to POSIX time
z <- transform(z,
  s_t = as.numeric(strptime(as.character(z$StartTime), "%H:%M:%S")),
  dur = as.numeric(strptime(as.character(z$Duration), "%H:%M:%S")) - 
    as.numeric(strptime("00:00:00", "%H:%M:%S")),
  tim = as.numeric(strptime(as.character(z$Time), "%H:%M:%S")))

# subset by time range
subset(z, s_t < tim & tim < s_t + dur)

输出：

  ID StartTime Duration Outcome OBS     Time        s_t dur        tim
1  1  10:12:06 00:00:10  Normal   1 10:12:10 1321665126  10 1321665130
2  1  10:12:06 00:00:10  Normal   2 10:12:15 1321665126  10 1321665135
7  2  10:12:30 00:00:30   Weird   3 10:12:45 1321665150  30 1321665165

OBS #2看起来在范围内。这有意义吗？

- kohske

Answer 3

使用merge()将两个数据框合并在一起。然后，使用条件time >= startTime & time <= startTime + Duration或其他有意义的规则对结果数据框进行subset()筛选。

- JD Long