Identify ids with count > 1 within a date window in R

user_6866511 0
Viewed 26

I need to write an R function to indicate whether a specific id has been seen more than once in an n-day window. For example, if n=1, I want to know which ids were seen more than once on a given day. If n = 2, I want to know if an id was seen more than once within a two day period (could be yesterday and today, or it could be today and tomorrow).

I've tried reshaping the data in different ways but can't figure it out. How do I identify these duplicate ids?

Example code and desired output:

data <- data.frame(date = c(1,1,1,2,2,2,3,3,3), id = c(1,1,2,1,3,4,1,2,5))
> data
#   date id
# 1    1  1
# 2    1  1
# 3    1  2
# 4    2  1
# 5    2  3
# 6    2  4
# 7    3  1
# 8    3  2
# 9    3  5

# function should return 1 if the person is seen more than once in an `n` day period, 0 otherwise
dupe_detect <- function(data, date, id, n){
  # code #
}

data$dupe <- dupe_detect(data, date, id, n = 1)
> data
#   date id dupe
# 1    1  1    1
# 2    1  1    1
# 3    1  2    0
# 4    2  1    0
# 5    2  3    0
# 6    2  4    0
# 7    3  1    0
# 8    3  2    0
# 9    3  5    0

data$dupe <- dupe_detect(data, date, id, n = 2)
> data
#   date id dupe
# 1    1  1    1
# 2    1  1    1
# 3    1  2    1
# 4    2  1    1
# 5    2  3    0
# 6    2  4    0
# 7    3  1    1
# 8    3  2    1
# 9    3  5    0
r
1 Answers

A data.table attempt, based on making some variables for the +/- dates, then doing some joins looking for the same id within these ranges:

library(data.table)
setDT(data)

data[, c("p1","p2","m1","m2") := lapply(c(1,2,-1,-2), \(x) date + x)]
data[, c("dup1","dup2") := 0]

data[data, on=c("id","date<date","p1>=date"), dup1 := 1]
data[data, on=c("id","date<date","p2>=date"), dup2 := 1]

data[data, on=c("id","date>date","m1<=date"), dup1 := 1]
data[data, on=c("id","date>date","m2<=date"), dup2 := 1]

data[, c("p1","p2","m1","m2") := NULL]
data

Result:

#   date id dup1 dup2
#1:    1  1    1    1
#2:    1  1    1    1
#3:    1  2    0    1
#4:    2  1    1    1
#5:    2  3    0    0
#6:    2  4    0    0
#7:    3  1    1    1
#8:    3  2    0    1
#9:    3  5    0    0
user_4968030
Related