Grouping similar times together

user_12004043 0
Viewed 47

I have a data frame with systolic blood pressure and date/time. The readings were sometimes taken within minutes of each other, sometimes not. An example is below:

library(tidyverse)

date_time <- c("Jan 29 2020 13:46:08" , 
              "Jan 29 2020 13:42:53" , 
              "Jan 29 2020 12:13:27" ,
              "Jan 29 2020 12:11:19" , 
              "Jan 29 2020 12:09:21" , 
              "Jan 28 2020 12:22:26" , 
              "Jan 27 2020 8:22:20")

systol <- c(132  , 132  , 118  , 115  , 110 , 148 , 120)

df <- data.frame(date_time , systol) %>%
  mutate(dtetime = lubridate::mdy_hms(date_time)

I want to group the systolic readings taken near to each other in time--arbitrarily, with the last reading taken within 10 minutes of the first reading in any group--and average the readings (and ideally average the date/time of the reading as well). I've tried thinking it through using lag and lead functions and then group_by, and could not devise a way to do that if there are not 2 readings in a group. I'm most familiar with the tidyverse so prefer that approach but am interested in any way of doing this. I'm relatively new to R. Thank you for any help!

grouping r datetime
2 Answers

here's an approach using data.table:

library(data.table)
dt <- as.data.table(df)

> dt[, .(mean_value = mean(systol)), by = .(mean_time = lubridate::round_date(x = dtetime, unit = '10 min'))]
             mean_time mean_value
1: 2020-01-29 13:50:00   132.0000
2: 2020-01-29 13:40:00   132.0000
3: 2020-01-29 12:10:00   114.3333
4: 2020-01-28 12:20:00   148.0000
5: 2020-01-27 08:20:00   120.0000

I'm using round_date, which, as the name suggests, rounds time to the nearest 10 min, you could also look at floor_date or other options to get your desired output.

user_26582280

Using cut basically. After ordering the readings increasingly, make a vector of differences from the first reading,

d <- d[order(d$dtetime), ]  ## order readings increasingly
dff <- with(d, as.numeric(dtetime) - as.numeric(dtetime)[1])  ## calc. diff from 1st reading

allowing to create 10 min bins (in seconds).

bins <- seq(min(dff), max(dff)*1.24, 10 * 60)  ## 10 min bins

Now we can cut at these bins to divide into groups and calculating means of time and systol while aggregateing along them.

d$group <- cut(dff, breaks=bins, include.lowest=TRUE)
res <- aggregate(cbind(dtetime, systol) ~ group, d, mean)[-1]
res$dtetime <- as.POSIXct(res$dtetime, origin="1970-01-01")  ## converting back to POSIX 
res
#               dtetime systol
# 1 2020-01-27 08:22:20  120.0
# 2 2020-01-28 12:22:26  148.0
# 3 2020-01-29 12:10:20  112.5
# 4 2020-01-29 12:13:27  118.0
# 5 2020-01-29 13:44:30  132.0

Data:

date_time <- c("Jan 29 2020 13:46:08", "Jan 29 2020 13:42:53", "Jan 29 2020 12:13:27", 
               "Jan 29 2020 12:11:19", "Jan 29 2020 12:09:21", "Jan 28 2020 12:22:26", 
               "Jan 27 2020 8:22:20")
systol <- c(132, 132, 118, 115, 110, 148, 120)
d <- data.frame(dtetime=strptime(date_time, format="%b %e %Y %H:%M:%S") , systol)
user_65740380
Related