Yes! I was facing the same issue. In the file, the data is in every minute, but after uploading in R, it was showing like as you mentioned in your response.
that's why I tried to generate the new_date via
magee$new_date <- as.POSIXct(as.character(paste(magee$Date, magee$Time)), format="%Y-%m-%d %H:%M:%S")
maybe the date was not correct, so I changed the date more accurate way!
Corrected data can be found here
https://www.dropbox.com/t/rGiVvmj51ZcboljN
So, This time the data shows like, after applying
magee$date <- as.POSIXct(as.character(paste(magee$date)), format="%Y-%m-%d %H:%M:%S")
date BC6 BC1
<dttm> <dbl> <dbl>
1 2022-01-01 00:00:00 1.59 1.61
2 2022-01-01 00:01:00 1.57 1.57
3 2022-01-01 00:02:00 1.64 1.63
4 2022-01-01 00:03:00 2.42 2.27
5 2022-01-01 00:04:00 3.13 3.04
6 2022-01-01 00:05:00 3.44 3.40
Then I tried
magee %>%
mutate(date_time = floor_date(date, "hour")) %>%
group_by(date_time) %>%
summarise(BC6_h = mean(BC6, na.rm = TRUE))
But it is showing the total mean of the data, not each hour!
BC6_h
1 1.31546
I hope I am clear this time!