r - 按组检查是否存在基于其他列的先前条目

Question

我想在我的 data.table 中创建一个列，该列与另一个 ID 列相同，如果在第三个日期列中存在相同 ID 的前一年条目。

我非常低效的解决方案:

library(data.table)
set.seed(123)
DT = data.table(
  ID = c("b","b","b","a","a","c"),
  dates = sample(seq(as.Date('2016/01/01'), as.Date('2019/01/01'), by="day"), 12)
)

setorder(DT, ID, dates)
DT[, Desired_Column:=DT[ID == .BY[[1]] & year(dates) < year(.BY[[2]]), ID[.N]], by=.(ID, dates)]

我的问题:
为什么在大型数据集上速度很慢，有什么方法可以更快地做到这一点？

编辑:初始版本没有解决整个问题。我很惊讶，过滤器 year( dates ) > min( year( dates ) )按组工作，但实际上并非如此。我换了 dates -column，所以年份日期 2016是可能的。现在群 a没有早于 2017 的条目，这应该是 Desired_Column 的第一个条目 NA .

这是我想得到的输出:

      ID      dates Desired_Column
 1:  a 2017-05-11           <NA>
 2:  a 2018-08-24              a
 3:  a 2018-10-24              a
 4:  a 2018-11-06              a
 5:  b 2016-11-11           <NA>
 6:  b 2017-03-23              b
 7:  b 2017-07-30              b
 8:  b 2017-08-23              b
 9:  b 2018-05-13              b
10:  b 2018-08-30              b
11:  c 2016-02-19           <NA>
12:  c 2017-05-07              c

Answer 1

我的方法

DT[ DT[, .I[ year(dates) > min(year(dates))], by = "ID"]$V1, Desired_Column := ID][]

#     ID      dates Desired_Column
#  1:  a 2017-05-11           <NA>
#  2:  a 2018-08-24              a
#  3:  a 2018-10-24              a
#  4:  a 2018-11-06              a
#  5:  b 2016-11-11           <NA>
#  6:  b 2017-03-23              b
#  7:  b 2017-07-30              b
#  8:  b 2017-08-23              b
#  9:  b 2018-05-13              b
# 10:  b 2018-08-30              b
# 11:  c 2016-02-19           <NA>
# 12:  c 2017-05-07              c

基准测试

microbenchmark::microbenchmark( 
  my_solution = DT[ DT[, .I[ year( dates ) > min( year( dates ) ) ], by = "ID"]$V1, Desired_Column := ID][],
  your_solution = DT[, Desired_Column:=DT[ID == .BY[[1]] & year(dates) < year(.BY[[2]]), ID[.N]], by=.(ID, dates)][],
  akrun = {
    DT[, yr := year(dates)]
    DT[DT[, .(yr = first(yr)), ID],  Desired_Column := ID, on = .(ID, yr > yr)]
  }
)

# Unit: milliseconds
#          expr      min       lq     mean   median       uq       max neval
#   my_solution 1.349660 1.470769 1.670500 1.612211 1.836653  2.764091   100
# your_solution 4.317707 4.510213 4.877906 4.656327 4.893572 21.164655   100
#         akrun 3.637755 3.812187 4.320189 4.197804 4.675306 10.018512   100

并且在长度为 1,000 的数据集上

# Unit: milliseconds
#          expr        min         lq       mean     median         uq       max neval
#   my_solution   1.635860   1.787998   2.323437   2.038197   2.504854  10.82108   100
# your_solution 342.582218 352.706475 367.424500 359.987257 375.076633 467.85023   100
#         akrun   3.749825   4.291949   5.448715   4.949456   5.368815  39.72218   100

和长度为 1,000,000 的数据集

# Unit: milliseconds
#          expr      min       lq     mean   median       uq      max neval
#   my_solution 270.8044 280.4150 324.1195 284.5502 390.1511 393.2282    10
# your_solution   - I did not dare to run ;-)
#         akrun 166.2049 167.8109 209.5945 178.2247 291.4220 297.0243    10

结论

我的 subsetting-answer 最有效的 data.tables 最多约 50,000 行，超过这个大小，@akrun 的非对等连接解决方​​案是性能赢家。