在R中按组检查重叠开始和结束时间 [英] Check overlap begin and end time by group in R

问题描述

我想检查数据的重叠，这里是数据

I want to check overlap of data, here is data

ID <- c(rep(1,3), rep(3, 5), rep(4,4),rep(5,5))
Begin <- c(0,2.5,3,7,8,7,25,25,10,15,17,20,1,NA,10,11,13)
End <- c(1.5,3.5,6,12,8,11,29,35, 12,19,NA,28,5,20,30,20,25)
df <- data.frame(ID, Begin, End)
df
   ID Begin  End
1   1   0.0  1.5
2   1   2.5  3.5
3   1   3.0  6.0*
4   3   7.0 12.0
5   3   8.0  8.0*
6   3   7.0 11.0*
7   3  25.0 29.0
8   3  25.0 35.0*
9   4  10.0 12.0
10  4  15.0 19.0
11  4  17.0   NA*
12  4  20.0 28.0
13  5   1.0  5.0
14  5    NA 20.0
15  5  10.0 30.0
16  5  11.0 20.0*
17  5  13.0 25.0*

* 表示它重叠：

<对于第3行，ID = 1，Begin = 3.0小于3.5，因此设置Begin_New = 3.5，但

• ，而ID = 3，则不同，第5行Begin = 8.0小于12.0，我们设置Begin_New = 12，但它继续，如果我们比较Begin = 7.0和End = 8.0，它是不正确的，因为现在End是12是更高的下一个值。

这是我的输出设计

   ID Begin  End Begin_New1
1   1   0.0  1.5        0.0
2   1   2.5  3.5        2.5
3   1   3.0  6.0        3.5*
4   3   7.0 12.0        7.0
5   3   8.0  8.0       12.0*
6   3   7.0 11.0       12.0*
7   3  25.0 29.0       25.0
8   3  25.0 35.0       29.0*
9   4  10.0 12.0       10.0
10  4  15.0 19.0       15.0
11  4  17.0   NA       19.0*
12  4  20.0 28.0       20.0
13  5   1.0  5.0        1.0
14  5    NA 20.0         NA
15  5  10.0 30.0       20.0*
16  5  11.0 20.0       30.0*
17  5  13.0 25.0       30.0*

这个代码，我没有得到我想要的输出，它只移动1行并比较每行

When I use this code, I don't get the output I want, it shift only 1 row and compare each row

setDT(df)[, Begin_New := shift(End), by = ID][!which(Begin < Begin_New), Begin_New:= Begin]
    ID Begin  End Begin_New
 1:  1   0.0  1.5       0.0
 2:  1   2.5  3.5       2.5
 3:  1   3.0  6.0       3.5
 4:  3   7.0 12.0       7.0
 5:  3   8.0  8.0      12.0
 6:  3   7.0 11.0       8.0
 7:  3  25.0 29.0      25.0
 8:  3  25.0 35.0      29.0
 9:  4  10.0 12.0      10.0
10:  4  15.0 19.0      15.0
11:  4  17.0   NA      19.0
12:  4  20.0 28.0      20.0
13:  5   1.0  5.0       1.0
14:  5    NA 20.0        NA
15:  5  10.0 30.0      20.0
16:  5  11.0 20.0      30.0
17:  5  13.0 25.0      20.0

这是我不想要的输出

推荐答案

我认为你的代码是正确的，你只需要使用 cummax

I think your code is pretty much right, you just need to use cummax:

df[, Begin_New := {
  high_so_far = shift(cummax(End), fill=Begin[1L])
  w           = which(Begin < high_so_far)
  Begin[w]    = high_so_far[w]

  Begin
}, by=ID]

这篇关于在R中按组检查重叠开始和结束时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！