How to Group Time data into hour sections in R

Question

I have a data set with time and heart rate data. The time intervals are currently about 5 seconds apart and each have data for hear rate (i.e 7:21AM = Time, 97 = Value... 7:21:05 = Time, 102 = Value.)I want to group the time into hour so that I can create a line graph of how the heart rate changes from hour to hour. When I attempt to load in the data with this many data points the graph does not load properly. How would I go about doing this to create a clean graph that shows how heart rate changes from 0000 to 0100 to 0200 to 0300.. etc etc?

update -- Okay thank you for suggestions, I have added the visualization I am working with. Essentially I just want to add values across the x axis which represent 7 Am to 8pm.

structure(list(Id = c("user_1", "user_1", "user_1", "user_1", 
"user_1", "user_1", "user_1", "user_1", "user_1", "user_1"), 
    Time = c("4/12/2016 7:21:00 AM", "4/12/2016 7:21:05 AM", 
    "4/12/2016 7:21:10 AM", "4/12/2016 7:21:20 AM", "4/12/2016 7:21:25 AM", 
    "4/12/2016 7:22:05 AM", "4/12/2016 7:22:10 AM", "4/12/2016 7:22:15 AM", 
    "4/12/2016 7:22:20 AM", "4/12/2016 7:22:25 AM"), Value = c(97L, 
    102L, 105L, 103L, 101L, 95L, 91L, 93L, 94L, 93L)), row.names = c(NA, 
10L), class = "data.frame")

enter link description here

It will be much easier for people to help if you can provide code and sample data (fake is just fine) that people can run. It's unclear to me what format your data is in, so I wouldn't know where to start. Great advice here: https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example It's often simplest to edit your question to include the output of `dput(head(YOUR_DATA))` — Jon Spring, Apr 05 '22 at 01:27
I've provided one possible solution, but unsure what your expected output is. Also, as @JonSpring mentions, providing the `dput` would help as dealing with time data is highly dependent upon the format. — AndrewGB, Apr 05 '22 at 04:29
Okay thank you all, I have updated an example(as it is a very large string of data) and the visualization I am currently working on. Check original post for the update. — Stackstudent_09, Apr 05 '22 at 21:07

AndrewGB · Accepted Answer · 2022-04-09T15:46:56.687

1

Updated

We can convert into a POSIXct format, then plot the data. We can use scale_x_datetime to specify plotting at 1 hour intervals showing just hour, minute, and AM/PM.

library(tidyverse)
library(lubridate)

df %>%
  mutate(Time = as.POSIXct(strptime(Time, "%m/%d/%Y %I:%M:%S %p"), format = "%m/%d/%Y %H:%M:%OS %p")) %>%
  ggplot(aes(x = Time, y = Value)) +
  geom_line() +
  theme_bw() +
  scale_x_datetime(breaks = "1 hour", date_labels =  "%I:%M %p")

Output

Data

df <- structure(list(Id = c("user_1", "user_1", "user_1", "user_1", 
"user_1", "user_1", "user_1", "user_1", "user_1", "user_1", "user_1", 
"user_1", "user_1"), Time = c("4/12/2016 6:50:00 AM", "4/12/2016 7:21:00 AM", 
"4/12/2016 7:21:05 AM", "4/12/2016 7:21:10 AM", "4/12/2016 7:21:20 AM", 
"4/12/2016 7:21:25 AM", "4/12/2016 7:22:05 AM", "4/12/2016 7:22:10 AM", 
"4/12/2016 7:22:15 AM", "4/12/2016 7:22:20 AM", "4/12/2016 7:22:25 AM", 
"4/12/2016 7:55:20 AM", "4/12/2016 8:05:25 AM"), Value = c(105L, 
97L, 102L, 105L, 103L, 101L, 95L, 91L, 93L, 94L, 93L, 96L, 98L
)), row.names = c(NA, 13L), class = "data.frame")

Original Answer

If you want to summarise for each hour, then we could just convert the time to the hour, then get the mean of values for that hour, then convert back to a time format for plotting.

library(tidyverse)
library(lubridate)

df %>% 
  mutate(Time = hour(hms(format(strptime(Time, "%I:%M:%S %p"), "%H:%M:%S")))) %>% 
  group_by(Time) %>% 
  summarise(Value = mean(Value)) %>% 
  mutate(Time = paste0(Time, ":00"),
         Time = as_datetime(hm(Time))) %>%
  ggplot(aes(x = Time, y = Value)) +
  geom_line() + 
  theme_bw() + 
  scale_x_datetime(breaks = "1 hour", date_labels =  "%H:%M %p")

Output

Data

set.seed(200)
time.seq = format(seq(from=as.POSIXct("04:00:00", format="%H:%M:%OS",tz="UTC"),    
               to=as.POSIXct("08:59:59", format="%H:%M:%OS", tz="UTC"), by = 5), "%I:%M:%S%p")
df <- data.frame(Time = time.seq, Value = round(runif(3600, 50, 150), digits = 0))

edited Apr 09 '22 at 15:46

answered Apr 05 '22 at 04:22

AndrewGB

16,126
5
18
49

Okay thank you for the example and thinking of a suggestion. This would work great however I am just looking to add the times to the x axis now (as updated in my original question.) Would I just need to use the code time.seq = format(seq(from=as.POSIXct("07:00:00", format="%H:%M:%OS",tz="UTC"), to=as.POSIXct("19:00:00", format="%H:%M:%OS", tz="UTC), by = 12), "%I:%M:%S%p") – Stackstudent_09 Apr 05 '22 at 21:14
In addition - what is the last bit of code referring to? the "%I:%M:%S%p"? – Stackstudent_09 Apr 05 '22 at 21:17
@Stackstudent_09 I've updated using your data (though I added a time point before 7 am and after 8 am). Is that what you are looking for? – AndrewGB Apr 05 '22 at 21:47
Thank you yes, however there is still a problem within the graph. The time label is set from 1:00AM to 13:00 PM. I have added the graph to the original question! – Stackstudent_09 Apr 09 '22 at 13:57
The data is the same from 7:21 AM to 7:50 PM however the label is showing from 1:00 AM to 1PM. – Stackstudent_09 Apr 09 '22 at 14:00
1

@Stackstudent_09 I updated the code in the `mutate` statement to convert to 24 hour format. Then, we can just set the format as AM/PM for the x-axis still in the `scale_x_datetime` function for the plot. This should fix the issue for afternoon times. – AndrewGB Apr 09 '22 at 15:48
Nice thank you, is there a way to convert 13:00PM through 20:00 to normal time and not millitary? – Stackstudent_09 Apr 09 '22 at 17:45
1

@Stackstudent_09 Yeah, use the new code that I have up. I had also changed the last line to use ‘I’ instead of ‘H’. Using ‘I’ will make it not military time for the x axis. – AndrewGB Apr 10 '22 at 04:50
1

Great! this is perfect – Stackstudent_09 Apr 10 '22 at 20:26

How to Group Time data into hour sections in R

1 Answers1