Calculate mean time of day in %H:%M:%S in R

Question

I am trying to calculate the mean time of day a particular activity occurs per participant in a long dataframe. Each participant has 7 timepoints of data, and I need to calculate the mean time of day across these timepoints. The 'time' variable is in 24hr time and the time of day is important for the output.

Create some example data:

data <- data.frame(
  ID = c(1, 1, 1, 1, 1, 1),
  time = c("23:49:47", "23:49:37", "23:39:02", "23:46:37", "00:27:40", "00:10:22", "    
00:41:22"))

Try to calculate mean:

format(mean(strptime(data$time, "%H:%M:%S")), "%H:%M:%S")

This keeps giving output of "13:46:21" (1:46pm) which is not correct given all the time values are around midnight, hence the average should be somewhere around midnight.

I have also tried solutions from ChatGPT, converting HH:MM:SS to total minutes and back to HH:MM:SS but that also is giving the same answer. I am really stuck. Any help is greatly appreciated.

Answer 1

If you have presumed that all the times instances are around mid-night, you should specify that 00:MM:SS denotes the times in the "next day", instead of "today" (as the reference date).

You can try the following workaround for example

with(
    data,
    format(
        mean(as.POSIXct(paste0(Sys.Date() + startsWith(time, "0"), time))),
        "%H:%M:%S"
    )
)

which shows

"00:03:29"