Calculate Rolling 12 Hours by Group in R

Question

I am working on a project where I have to only include patients who had lab tests ordered at least 12 hours apart, and to keep the timestamp of each included lab test. The issue is that many patients get several labs done within the 12 hour window, but the client has asked to not include those tests. I have made it this far:

#Create dummy dataset
df = data.frame(
  "Encounter" = c(rep("12345", times=16), rep("67890", times = 5)),
  "Timestamp" = c("01/06/2022 04:00:00", "01/07/2022 08:00:00",
                   "01/08/2022 00:00:00", "01/08/2022 04:00:00",
                   "01/08/2022 08:00:00", "01/08/2022 20:00:00",
                   "01/09/2022 04:00:00", "01/09/2022 08:00:00",
                   "01/09/2022 20:00:00", "01/09/2022 23:26:00",
                   "01/10/2022 00:00:00", "01/10/2022 08:00:00",
                   "01/10/2022 20:00:00", "01/11/2022 00:00:00",
                   "01/11/2022 20:00:00", "01/12/2022 04:00:00",
                   "11/10/2021 11:00:00", "11/10/2021 12:00:00",
                   "11/10/2021 13:00:00", "11/10/2021 14:00:00",
                   "11/11/2021 00:00:00"))

#Convert timestamp to POSIXlt format
df$Timestamp <- strptime(as.character(df$Timestamp), format="%m/%d/%Y %H:%M")

#Calculate time (in hours) between each previous timestamp by Encounter
df <- df %>% 
  group_by(Encounter) %>% 
  arrange(Encounter, Timestamp) %>% 
  mutate(difftime(Timestamp, lag(Timestamp), units="hours"))

I can't seem to figure out what to do next. It seems like I need to calculate a rolling 12-hours that then resets to 0 once a row hits 12 hours, but I'm not sure how to go about it. Below is my ideal result:

df$Keep.Row <- c(1,1,1,0,0,1,0,1,1,0,0,1,1,0,1,0,1,0,0,0,1)

Answer 1

Here is an alternative option using accumulate. Here, you can use you differences, and once they exceed the threshold of 12 hours, reset by just using the diff value (starting over) instead of using the cumulative sum. To include the first time for each Encounter, you can either make that diff 12 hours, or add a separate mutate and check where Timestamp == first(Timestamp) and in those cases set keep to 1.

library(tidyverse)

thresh <- 12

df %>%
  group_by(Encounter) %>% 
  arrange(Encounter, Timestamp) %>% 
  mutate(diff = difftime(Timestamp, lag(Timestamp, default = first(Timestamp) - (thresh * 60 * 60)), units = "hours"),
         keep = +(accumulate(diff, ~if_else(.x >= thresh, .y, .x + .y)) >= thresh))

Output

   Encounter Timestamp           diff              keep
   <chr>     <dttm>              <drtn>           <int>
 1 12345     2022-01-06 04:00:00 12.0000000 hours     1
 2 12345     2022-01-07 08:00:00 28.0000000 hours     1
 3 12345     2022-01-08 00:00:00 16.0000000 hours     1
 4 12345     2022-01-08 04:00:00  4.0000000 hours     0
 5 12345     2022-01-08 08:00:00  4.0000000 hours     0
 6 12345     2022-01-08 20:00:00 12.0000000 hours     1
 7 12345     2022-01-09 04:00:00  8.0000000 hours     0
 8 12345     2022-01-09 08:00:00  4.0000000 hours     1
 9 12345     2022-01-09 20:00:00 12.0000000 hours     1
10 12345     2022-01-09 23:26:00  3.4333333 hours     0
11 12345     2022-01-10 00:00:00  0.5666667 hours     0
12 12345     2022-01-10 08:00:00  8.0000000 hours     1
13 12345     2022-01-10 20:00:00 12.0000000 hours     1
14 12345     2022-01-11 00:00:00  4.0000000 hours     0
15 12345     2022-01-11 20:00:00 20.0000000 hours     1
16 12345     2022-01-12 04:00:00  8.0000000 hours     0
17 67890     2021-11-10 11:00:00 12.0000000 hours     1
18 67890     2021-11-10 12:00:00  1.0000000 hours     0
19 67890     2021-11-10 13:00:00  1.0000000 hours     0
20 67890     2021-11-10 14:00:00  1.0000000 hours     0
21 67890     2021-11-11 00:00:00 10.0000000 hours     1

Answer 2

Probably missing something, but wouldn't this work:

library(dplyr)

df %>% 
  group_by(Encounter) %>% 
  arrange(Encounter, Timestamp) %>% 
  mutate(time_dif = difftime(Timestamp, lag(Timestamp), units="hours")) %>% 
  filter(time_dif > 12)