Calculate Rolling 12 Hours by Group in R

user_11056971 0
Viewed 105

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)

Screenshot of desired final product

rolling-computation r timestamp
3 Answers

There is absolutely nothing elegant about this, but I believe it gives you what you’re looking for. I use a temporary variable to store the “rolling” sum before it’s reset once the hours between is 12 or greater.

library(tidyverse)

df <- df %>% 
  group_by(Encounter) %>% 
  arrange(Encounter, Timestamp) %>% 
  mutate(time_diff = difftime(Timestamp, lag(Timestamp), units="hours")) %>%
  replace_na(list(time_diff = 0)) %>%
  mutate(temp = ifelse(time_diff < 12 & lag(time_diff) >= 12, time_diff, lag(time_diff) + time_diff),
         temp = ifelse(is.na(temp), 0, temp),
         hours_between = ifelse(time_diff >= 12, time_diff,
                        ifelse(time_diff < 12 & lag(time_diff) >= 12, time_diff, lag(temp) + time_diff)),
         keep = ifelse(hours_between >= 12 | is.na(hours_between), 1, 0)) %>%
  select(-temp)

Created on 2022-01-27 by the reprex package (v2.0.1)

user_175200400

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
user_34606700

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)
user_94927200
Related