r - 查找日期差异

Question

我有以下数据:

ID GROUP     DATE 
A   GR1   12/01/2013
A   GR1   09/04/2014
A   GR1   01/03/2015
A   GR2   04/04/2015
A   GR2   08/21/2015
A   GR1   01/05/2016
A   GR1   06/28/2016
B   GR2   11/01/2013
B   GR2   06/04/2014
B   GR2   04/15/2015
B   GR3   11/04/2015
B   GR2   03/21/2016
B   GR2   07/05/2016
B   GR1   06/28/2016
C   GR2   01/16/2014
C   GR2   06/04/2014
C   GR2   04/15/2015
C   GR3   11/04/2015
C   GR2   03/21/2016
C   GR2   06/05/2016
C   GR1   06/28/2016

我想知道这个人在每个组中留下的差异。因此，新表将如下所示:

ID GROUP     DATE      Diff
A   GR1   12/01/2013
A   GR1   09/04/2014
A   GR1   01/03/2015    398
A   GR2   04/04/2015
A   GR2   08/21/2015    139
A   GR1   01/05/2016
A   GR1   06/28/2016    175
B   GR2   11/01/2013
B   GR2   06/04/2014
B   GR2   04/15/2015    530
B   GR3   11/04/2015
B   GR2   03/21/2016
B   GR2   07/05/2016    106
B   GR1   06/28/2016
C   GR2   01/16/2014
C   GR2   06/04/2014    
C   GR2   04/15/2015    454
C   GR3   11/04/2015
C   GR2   03/21/2016
C   GR2   01/05/2016    76
C   GR1   06/28/2016

“差异”398 列中的值是通过取差异 '01/03/2015' - '12/1/2013' 得出的。同样所有其他差异。

现在我的问题是如何获得这种差异？我不能在每个组中取 max(date)-min(date)，因为组在不同的时间段重复。同样，我不能像在 SAS 中那样取第一个点和最后一个点。

如果有人帮助我解决问题，我将不胜感激。我更喜欢 SAS 中的解决方案，因为数据量非常大。 SO 不会保存在内存中。

问候，

Answer 1

library(dplyr)
library(data.table)
df$xxx = rleidv(df[, c("ID","GROUP"),with = FALSE ])
df$DATE = as.Date(df$DATE, format = "%m/%d/%Y")
df %>% group_by(xxx) %>% mutate(diff = max(DATE) - min(DATE)) %>%
       ungroup(xxx) %>% mutate(xxx = NULL)
#     ID GROUP       DATE     diff
#   <chr> <chr>     <date>   <time>
#1      A   GR1 2013-12-01 398 days
#2      A   GR1 2014-09-04 398 days
#3      A   GR1 2015-01-03 398 days
#4      A   GR2 2015-04-04 139 days
#5      A   GR2 2015-08-21 139 days
#6      A   GR1 2016-01-05 175 days
#7      A   GR1 2016-06-28 175 days
#8      B   GR2 2013-11-01 530 days
#9      B   GR2 2014-06-04 530 days
#10     B   GR2 2015-04-15 530 days

仅使用 data.table :

library(data.table)
df[, diff := max(DATE)-min(DATE),by = c("xxx")][,xxx:=NULL]