r - 使用 R 根据逻辑条件和它们发生的时间接近程度来识别和分组相关观察

Question

我有一个数据集(“出价”)，它由一系列观察值(约 500 万)组成，每个观察值代表购买产品(在下面的简化示例中，一本书或游戏)的出价。对于每次观察，我都有(参见下面的示例数据):

1. 提交投标的日期
2. 提交出价的时间
3. 投标人投标的产品名称

4. 投标人姓名

   observation date        time        product bidder
   1           1/1/2016    9:00:00 AM  book    AB
   2           1/1/2016    9:01:00 AM  book    CD
   3           1/1/2016    9:02:00 AM  book    EF
   4           1/1/2016    9:03:00 AM  book    CD
   5           1/1/2016    9:00:00 AM  game    AB
   6           1/1/2016    9:01:00 AM  game    CD
   7           1/1/2016    9:02:00 AM  game    CD
   8           1/1/2016    9:07:00 AM  game    CD
   9           1/2/2016    9:00:00 AM  book    AB
   10          1/2/2016    9:06:00 AM  book    CD
   11          1/2/2016    9:02:00 AM  book    EF
   12          1/2/2016    9:03:00 AM  book    EF
   13          1/2/2016    9:00:00 AM  game    EF
   14          1/2/2016    8:59:00 AM  game    CD
   15          1/2/2016    9:00:00 AM  game    GH
   16          1/2/2016    9:01:00 AM  game    AB
   17          1/2/2016    10:00:00 AM game    AB
   18          1/2/2016    10:06:00 AM game    CD
   19          1/2/2016    10:06:00 AM game    EF
   20          1/2/2016    10:06:00 AM game    GH
   21          1/2/2016    3:00:00 PM  game    AB
   

在某些情况下，一个投标人对某一特定产品进行了一次投标，而没有其他投标人对该产品进行过一次投标(例如，观察 #21)。然而，在大多数情况下，同一产品的多个投标人的多个投标在时间上非常接近(例如，观察 1-4 组成一个组；观察 14-16 组成一个组)。要研究这些组，我需要能够对它们进行分组并使用唯一标识符来标识每个组。最终，我还需要能够计算出每组中的出价总数以及每组中唯一/不同的出价者的数量。如果我弄清楚如何创建组，我可能可以自己解决这个问题，但我提到它是为了防止有更简单/更集成的方法。

我遇到的问题是“及时关闭”参数。如果“及时关闭”意味着同一天，我很清楚我可以在 plyr 中使用 id 并创建一个新列(“bidgrp”)(或其他几种方法中的任何一种)。像这样的东西:

bids$bidgrp <- id(bids[c("date", "product")], drop = TRUE)

但是，“及时关闭”实际上是指在 5 分钟内。换句话说，例如，观察值 9、11 和 12 是组的一部分，但 10 - 因为它比组中最早的成员 (9) 晚 5 分钟以上，所以不属于该组。挑战的一部分是弄清楚如何确定组中的第一个(最早)成员是什么(我没有可靠的指标，所以 [this] ( Grouping observations based on first row value ) 解决方案不起作用，但这可能是通过在尝试任何分组之前对数据进行排序来完成(尽管再次强调，如果有更聪明、更有效的方法来做到这一点，我会欢迎他们)

通过查看 SO 和其他地方的其他条件分组问题，我的直觉是通过一系列类似 ifelse 循环的步骤来解决这个问题，如下所示:

1. 先按日期排序数据集，然后按产品排序，然后按时间排序
2. 为第一个观察分配一个组 ID 号 i
3. 在下一次观察中查看正在投标的产品；如果它是对与先前观察中的产品不同的产品的出价，则为其分配 i+1 的组 ID；如果是同一产品的出价，请查看日期。
4. 如果日期与先前的观察不同，则为其分配组 ID i+1；如果是同一天，看时间
5. 如果时间距离之前的观察时间超过5分钟，则为其分配一个组id为i+1；如果它在 组中最早观察的 5 分钟内(这就是使问题特别棘手的原因 - 不仅仅是查看最后一次观察的问题，而是在确定时知道要关闭哪个观察时间距离)，给它分配一个组号 i 并查看下一个观察结果

结果(对于上面的示例数据)，将确定 9 个组，如下所示:

observation date    time        product bidder  grpid
1       1/1/2016    9:00:00 AM  book    AB      1
2       1/1/2016    9:01:00 AM  book    CD      1
3       1/1/2016    9:02:00 AM  book    EF      1
4       1/1/2016    9:03:00 AM  book    CD      1
5       1/1/2016    9:00:00 AM  game    AB      2
6       1/1/2016    9:01:00 AM  game    CD      2
7       1/1/2016    9:02:00 AM  game    CD      2
8       1/1/2016    9:07:00 AM  game    CD      3
9       1/2/2016    9:00:00 AM  book    AB      4
11      1/2/2016    9:02:00 AM  book    EF      4
12      1/2/2016    9:03:00 AM  book    EF      4
10      1/2/2016    9:06:00 AM  book    CD      5
14      1/2/2016    8:59:00 AM  game    CD      6
13      1/2/2016    9:00:00 AM  game    EF      6
15      1/2/2016    9:00:00 AM  game    GH      6
16      1/2/2016    9:01:00 AM  game    AB      6
17      1/2/2016    10:00:00 AM game    AB      7
18      1/2/2016    10:06:00 AM game    CD      8
19      1/2/2016    10:06:00 AM game    EF      8
20      1/2/2016    10:06:00 AM game    GH      8
21      1/2/2016    3:00:00 PM  game    AB      9

而且，我最终需要做这样的事情:

grpid   bids    uniquebidders
1       4       3
2       3       2
3       1       1
4       3       2
5       1       1
6       4       4
7       1       1
8       3       3
9       1       1

很抱歉问了这么长的问题。我知道这里的几个子问题(与时间一起工作；类似循环的操作)已经涵盖在 SO 中(我已经审查了其中的许多)，但是正是这些问题的结合使这对我来说特别具有挑战性(并且希望对其他人有用)。

在此先感谢您提供的任何帮助。

Answer 1

您可以为日期时间比较创建一个特定的函数。我知道它是否适用于 500 万行，但它适用于示例数据集。我按照你的步骤。它使用 rleid 创建游程类型 id 列。使用它两次，您将获得所需的组 ID。有意按部就类，但可以写得更简洁。

library(data.table)
setDT(DT)

# this function compare each datetime of the vector with the first one
# If > 5 mins then a new time reference is set for next group and group 
# is incremented
func_perso <- function(vec){
  time1 <- vec[1]
  grp <- 1
  res <- vector("integer", length(vec))
  for(i in 1:length(vec)){
    time <- vec[i]
    if(difftime(time, time1, units = "secs") > 5*60){
      grp <- grp + 1
      time1 <- time
    }
    res[i] <- grp
  }
  res
}

# Create a datetime object (POSIXct) for easier comparaison
DT[, dtime := as.POSIXct(strptime(paste(date, time), "%d/%m/%Y %I:%M:%S %p", tz = "UTC"))]
# order data as you mentionned
setorder(DT, date, product, dtime)
# Apply func on column dtime by data and product
DT[, grp1 := .SD[, func_perso(dtime)], by = .(date, product)]
# use rleid to count identify the group
DT[, grp2 := paste0(product, rleid(grp1)), by = .(date, product)]
# count the group
DT[, grpid := rleid(grp2)]
# delete non necessary column
DT[, `:=`(
  dtime = NULL, 
  grp1 = NULL, 
  grp2 = NULL
)]
# the result
DT
#>     Observation     date        time product bidder grpid
#>  1:           1 1/1/2016  9:00:00 AM    book     AB     1
#>  2:           2 1/1/2016  9:01:00 AM    book     CD     1
#>  3:           3 1/1/2016  9:02:00 AM    book     EF     1
#>  4:           4 1/1/2016  9:03:00 AM    book     CD     1
#>  5:           5 1/1/2016  9:00:00 AM    game     AB     2
#>  6:           6 1/1/2016  9:01:00 AM    game     CD     2
#>  7:           7 1/1/2016  9:02:00 AM    game     CD     2
#>  8:           8 1/1/2016  9:07:00 AM    game     CD     3
#>  9:           9 1/2/2016  9:00:00 AM    book     AB     4
#> 10:          11 1/2/2016  9:02:00 AM    book     EF     4
#> 11:          12 1/2/2016  9:03:00 AM    book     EF     4
#> 12:          10 1/2/2016  9:06:00 AM    book     CD     5
#> 13:          14 1/2/2016  8:59:00 AM    game     CD     6
#> 14:          13 1/2/2016  9:00:00 AM    game     EF     6
#> 15:          15 1/2/2016  9:00:00 AM    game     GH     6
#> 16:          16 1/2/2016  9:01:00 AM    game     AB     6
#> 17:          17 1/2/2016 10:00:00 AM    game     AB     7
#> 18:          18 1/2/2016 10:06:00 AM    game     CD     8
#> 19:          19 1/2/2016 10:06:00 AM    game     EF     8
#> 20:          20 1/2/2016 10:06:00 AM    game     GH     8
#> 21:          21 1/2/2016  3:00:00 PM    game     AB     9
#>     Observation     date        time product bidder grpid