R - Exceeding a threshold of row repetitions in a given period of time-CodePudding

I would like to know which IDs are repeated at least a certain amount of times (eg: ≥3) in a given period of time (eg: ≤3 years). I have the following table as an example:

ID  Date
1   2001-01-03
2   2001-02-28
3   2001-06-13
4   2002-04-05
5   2002-09-12
1   2002-12-12
3   2003-05-05
3   2003-05-06
4   2003-05-07
1   2003-06-04
2   2006-12-29
3   2007-04-05
1   2007-04-08
4   2007-09-12
1   2008-12-12
2   2009-01-23
3   2009-01-30
2   2009-04-05
1   2009-12-08
2   2010-01-04
2   2010-05-07
4   2012-01-02
5   2013-03-03
6   2014-01-01

I would like to obtain the following result:

ID  Rep
1   TRUE
2   TRUE
3   TRUE
4   FALSE
5   FALSE
6   FALSE

If the ID is repeated at least 3 times in less than 3 years, no matter how many times it did so and when it did so, I want to get a TRUE result. If the ID is repeated less than 3 times, or more than 3 times but never in a period of less than 3 years, I would like to get a FALSE result.

I imagine this might be a fairly simple question for many of you. However, I will highly appreciate your help.

CodePudding user response：

You can use data.table or dplyr combined with diff(); count the number of differences that are less than years(3) * 365.25, by ID. If this meets or exceeds num, return TRUE

yrs <- num <- 3

library(data.table)
setDT(data)[order(ID,Date)][,.("Rep" = sum(diff(Date)<(yrs*365.25))>=num),by="ID"]

# OR

library(dplyr)
data %>% 
  arrange(Date) %>% 
  group_by(ID) %>% 
  summarize(Rep = sum(diff(Date)<(yrs*365.25))>=num)

      ID    Rep
   <num> <lgcl>
1:     1   TRUE
2:     2   TRUE
3:     3   TRUE
4:     4  FALSE
5:     5  FALSE
6:     6  FALSE