问题描述

我是 R 新手，这是我关于 stackoverflow 的第一个问题。

我在尝试

通过引用分配新列
对于每一行
使用同一组行中第一下一行的值
满足一个条件。

示例数据：

    id code  date_down    date_up
 1:  1    p 2019-01-01 2019-01-02
 2:  1    f 2019-01-02 2019-01-03
 3:  2    f 2019-01-02 2019-01-02
 4:  2    p 2019-01-03       <NA>
 5:  3    p 2019-01-04       <NA>
 6:  4 <NA> 2019-01-05 2019-01-05
 7:  5    f 2019-01-07 2019-01-08
 8:  5    p 2019-01-07 2019-01-08
 9:  5    p 2019-01-09 2019-01-09
10:  6    f 2019-01-10 2019-01-10
11:  6    p 2019-01-10 2019-01-10
12:  6    p 2019-01-10 2019-01-11

我想做的是

子集（组）由id
并且对于每一行
找到date_up更下方的第一行，
其中code = 'p'和date-up（找到的行）大于date-down我正在更新的行。

我的预期结果应该是：

    id code  date_down    date_up  founddate
 1:  1    p 2019-01-01 2019-01-02       <NA>
 2:  1    f 2019-01-02 2019-01-03       <NA>
 3:  2    f 2019-01-02 2019-01-02       <NA>
 4:  2    p 2019-01-03       <NA>       <NA>
 5:  3    p 2019-01-04       <NA>       <NA>
 6:  4 <NA> 2019-01-05 2019-01-05       <NA>
 7:  5    f 2019-01-07 2019-01-08 2019-01-08
 8:  5    p 2019-01-07 2019-01-08 2019-01-09
 9:  5    p 2019-01-09 2019-01-09       <NA>
10:  6    f 2019-01-10 2019-01-10 2019-01-11
11:  6    p 2019-01-10 2019-01-10 2019-01-11
12:  6    p 2019-01-10 2019-01-11       <NA>

我尝试了许多变体，使用.SD,.N创建一个新列 DT[, idcount:= seq_leg(.N),by=id]，但没有真正得到任何地方。非常感谢任何帮助。

还有对 data.table 的任何好的参考 :) 非常感谢

编辑： 我已经编辑了提供的原始数据以给出一个更微妙的示例，其中第 10 行使用第 12 行的数据进行更新，因为第 12 行在 id 子集中并且符合资格标准。第 11 行不符合资格标准，因此数据不用于更新第 10 行。还包括我第一次使用dput!

示例数据作为dput代码：

dt <- structure(list(
id        = c(1L, 1L, 2L, 2L, 3L, 4L, 5L, 5L, 5L, 6L, 6L, 6L),
code      = c("p", "f", "f", "p", "p", "<NA>", "f", "p", "p", "f", "p", "p"),
date_down = structure(c(17897, 17898, 17898, 17899, 17900, 17901, 17903, 17903, 17905, 17906, 17906, 17906), class = "Date"),
date_up   = structure(c(17898, 17899, 17898, NA, NA, 17901, 17904, 17904, 17905, 17906, 17906, 17907), class = "Date")),
class     = c("data.table", "data.frame"),
row.names = c(NA, -12L))
setDT(dt)  # to reinit the internal self ref pointer (known issue)

标签： rdata.table

解决方案

按组将 data.table 连接到其自身的子集，以从匹配非相等条件的行中获取值。

概括：

下面我展示了 5 个可行data.table的解决方案，它们是针对 OP 的实际数据集（140 万条记录）进行性能测试的候选方案。
on所有 5 种解决方案都在子句中使用“非 equi”连接（使用不等式来比较连接的列）。
每个解决方案都只是一个小的渐进式代码更改，因此应该很容易比较不同的data.table选项和语法选择。

方法

为了解决data.table这个问题，我将其分解为 OP 问题的以下步骤：

将 dt 加入其自身的子集（或另一个 data.table ）。
从 dt 或子集中选择（并重命名）所需的列。
根据 dt 中的列与子集中的列进行比较来定义连接条件，包括使用“non-equi”（不相等）比较。
（可选）定义在子集中找到多个匹配记录时是选择第一个匹配还是最后一个匹配。

解决方案1：

# Add row numbers to all records in dt (only because you 
# have criteria based on comparing sequential rows):
dt[, row := .I] 

# Compute result columns (  then standard assignment into dt using <-  )
dt$found_date  <- 
            dt[code=='p'][dt,   # join dt to the data.table matching your criteria, in this case dt[code=='p']
                          .( x.date_up ),   # columns to select, x. prefix means columns from dt[code=='p'] 
                          on = .(id==id, row > row, date_up > date_down),   # join criteria: dt[code=='p'] fields on LHS, main dt fields on RHS
                          mult = "first"]   # get only the first match if multiple matches

注意上面的连接表达式：

i在这种情况下是您的主要 dt。这样您就可以从主 data.table 中获取所有记录。
x是要从中查找匹配值的子集（或任何其他 data.table）。

结果匹配请求的输出：

dt

    id code  date_down    date_up row found_date
 1:  1    p 2019-01-01 2019-01-02   1       <NA>
 2:  1    f 2019-01-02 2019-01-03   2       <NA>
 3:  2    f 2019-01-02 2019-01-02   3       <NA>
 4:  2    p 2019-01-03       <NA>   4       <NA>
 5:  3    p 2019-01-04       <NA>   5       <NA>
 6:  4 <NA> 2019-01-05 2019-01-05   6       <NA>
 7:  5    f 2019-01-07 2019-01-08   7 2019-01-08
 8:  5    p 2019-01-07 2019-01-08   8 2019-01-09
 9:  5    p 2019-01-09 2019-01-09   9       <NA>
10:  6    f 2019-01-10 2019-01-10  10 2019-01-11
11:  6    p 2019-01-10 2019-01-10  11 2019-01-11
12:  6    p 2019-01-10 2019-01-11  12       <NA>

注意：您可以根据需要删除该row列dt[, row := NULL]。

解决方案2：

与上述相同的逻辑来加入和查找结果列，但现在使用“通过引用分配”:=found_date来创建dt：

dt[, row := .I] # add row numbers (as in all the solutions)

# Compute result columns (  then assign by reference into dt using :=  

# dt$found_date  <- 
dt[, found_date :=   # assign by reference to dt$found_date 
            dt[code=='p'][dt, 
                          .( x.date_up ), 
                          on = .(id==id, row > row, date_up > date_down),
                          mult = "first"]]

在解决方案 2 中，将我们的结果“通过引用”分配给 dt 的微小变化应该比解决方案 1 更有效。解决方案 1 以完全相同的方式计算结果 - 唯一的区别是解决方案 1 使用标准分配<-来创建dt$found_date（效率较低）。

解决方案3：

与解决方案 2 类似，但现在使用.(.SD)代替dt来引用原始 dt 而不直接命名它。

dt[, row := .I] # add row numbers (as in all the solutions)
setkey(dt, id, row, date_down)  #set key for dt 

# For all rows of dt, create found_date by reference :=
dt[, found_date := 
            # dt[code=='p'][dt, 
            dt[code=='p'][.(.SD),   # our subset (or another data.table), joined to .SD (referring to original dt)
                          .( x.date_up ), 
                          on = .(id==id, row > row, date_up > date_down),  
                          mult = "first"] ]

.SD 上面引用了我们分配回的原始 dt。它对应于 data.table 的子集，其中包含第一个选择的行，dt[,这是所有行，因为我们没有过滤它。

注意：在解决方案 3 中，我曾经setkey()设置密钥。我应该在解决方案 1 和解决方案 2 中做到这一点- 但是在 @OllieB 成功测试它们之后我不想更改这些解决方案。

解决方案4：

与解决方案 3 类似，但比以前多次使用 .SD。我们的主要 data.table 名称dt现在在整个表达式中只出现一次！

# add row column and setkey() as previous solutions

dt[, found_date :=
            # dt[code=='p'][.(.SD), 
            .SD[code=='p'][.SD,   # .SD in place of dt at left!  Also, removed .() at right (not sure on this second change)
                           .(found_date = x.date_up),
                           on = .(id==id, row > row, date_up > date_down),
                           mult = "first"]]

通过上面的更改，我们的 data.table 名称dt只出现一次。我非常喜欢它，因为它可以很容易地在其他地方复制、改编和重用。

另请注意：我以前使用过的地方.(SD)现在已经删除了 .() ，.SD 因为它似乎不需要它。但是对于该更改，我不确定它是否具有任何性能优势，或者它是否是 data.table 首选语法。如果有人可以就这一点添加评论以提供建议，我将不胜感激。

解决方案 5：

像以前的解决方案一样，但在加入时使用by明确地将子集分组到操作上

# add row column and setkey() as previous solutions

dt[, found_date :=
       .SD[code=='p'][.SD,
                      .(found_date = x.date_up),
                      # on = .(id==id, row > row, date_up > date_down),
                      on = .(row > row, date_up > date_down),  # removed the id column from here
                      mult = "first"]
   , by = id]   # added by = id to group the .SD subsets

在最后一个解决方案中，我将其更改为使用该by子句将 .SD 子集显式分组到id.

注意：与解决方案 1 - 4 相比，解决方案 5 与 OllieB 的实际数据相比表现不佳。但是，在测试我自己的模拟数据时，我发现当id列中唯一组的数量较少时，解决方案 5 可以表现良好：
- 只有 6 150 万条记录中的组此解决方案的运行速度与其他解决方案一样快。
- 在 150 万条记录中有 40k 组，我看到了与 OllieB 报告的类似的糟糕表现。

结果

解决方案 1 - 4 表现良好：

根据 OllieB 的反馈，对于 OllieB 的实际数据中的 145 万条记录，解决方案 1 到 4 中的每一个都是 2.42 秒或更短的“经过”时间。对于具有“elapsed=1.22”秒的 OllieB，解决方案 3 似乎工作得最快。
我个人更喜欢解决方案 4，因为它的语法更简单。

解决方案 5

解决方案 5（usingby子句）在 OllieB 对其真实数据的测试中花费了 577 秒，表现不佳。

使用的版本

数据表版本：1.12.0

R 版本 3.5.3 (2019-03-11)

可能的进一步改进：

将日期字段更改为整数可能有助于更有效地加入。请参阅 as.IDate() 将日期转换为 data.tables 中的整数。
可能不再需要 setkey() 步骤：正如@Arun 在此处解释的那样，由于调用on[经常] 更有效的二级索引和自动索引。

对 data.table 的引用

作为您问题的一部分，您已要求“对 data.table 的任何良好参考”。我发现以下内容很有帮助：

data.table 入门 GitHub 上的 Wiki是开始的地方。
特别是对于这个问题，值得一读：
- .SD 在 R 中的 data.table 中代表什么
- 二级索引和自动索引的 HTML 小插图

重要的是注意@Arun 的这个答案，它解释了“实现 on= 参数的原因”表明可能不再需要设置键：

因此，必须弄清楚花在重新排序整个 data.table 上的时间是否值得花时间进行高效缓存的连接/聚合。通常，除非在同一个键控 data.table 上执行重复的分组/连接操作，否则应该不会有明显的差异。

因此，在大多数情况下，不再需要设置密钥。我们建议尽可能使用 on=，除非设置 key 可以显着提高您想要利用的性能。

这个 SO 问题似乎是有关不同data.table连接的信息中心：如何连接（合并）数据帧（内、外、左、右）？
最后，data.table 备忘单是一个很好的参考（来自 GitHub 上的 data.table 入门 Wiki 上的链接）。

与往常一样，如果有人提出建议，我将不胜感激，因为也许这可以进一步改进。

如果您可以添加任何内容，请随时评论，更正或发布其他解决方案。

r - 使用同一组中满足条件的第一行设置列值