1

I'm having trouble calculating the average of a variable "count" for every hour over a few days. I have a dataset called data which looks like this:

    Time                count
1   2019-06-30 05:00:00 17
2   2019-06-30 06:00:00 18
3   2019-06-30 07:00:00 26
4   2019-06-30 08:00:00 15
5   2019-07-01 00:00:00 13
6   2019-07-01 01:00:00 23
7   2019-07-01 02:00:00 13
8   2019-07-01 03:00:00 22

It contains values for every hour for a few days. Now i want to calculate a value for each hour which is the average value for that hour over all the days. Something like this:

    Time        count
1   00:00       22
2   01:00       13
3   02:00       11
4   03:00       9

I'm new to R and only came as far as calculating the daily average:

DF2 <- data.frame(data, Day = as.Date(format(data$Time)))
aggregate(cbind(count) ~ Day, DF2, mean)

    Time        count
1   2019-06-30  22
2   2019-07-01  13
3   2019-07-02  11
4   2019-07-03  9

But i can't get it working with the hourly average. I tried to find a solution in other posts, but they eiher didn't work or seem to require a lot of unique calculations. There must be a simple way to do this in R.

Here is the output of dput(droplevels(head(data, 4))):

structure(list(Time = structure(1:4, .Label = c("2019-06-30 05:00:00", 
"2019-06-30 06:00:00", "2019-06-30 07:00:00", "2019-06-30 08:00:00"
), class = "factor"), count = c(17L, 18L, 26L, 15L)), row.names = c(NA, 
4L), class = "data.frame")

Any suggestion? Thank you in advance!

Maxi

creerow
  • 69
  • 1
  • 8

2 Answers2

1

use lubridate and dplyr: group by hour value of time

generate data

library(dplyr)
library(lubridate)

df <- data.frame(Time=seq(as.POSIXct('2019-06-30 00:00:00'), as.POSIXct('2019-07-03 23:00:00'), by=3600),
  count = floor(runif(96, 12,71))
)

group by hour val, take mean, and pretty print

df %>% mutate(hour = lubridate::hour(Time)) %>%
  group_by(hour) %>% summarise(count=mean(count)) %>%
  # pretty print
  mutate(hour = sprintf("%02d:00", hour)) %>%
  print(n=24)
travismark
  • 51
  • 2
  • When i try to run it, i get this error: Error in UseMethod("mutate_") : no applicable method for 'mutate_' applied to an object of class "function" – creerow Jul 10 '19 at 18:51
1

Just take the hours as substrings and aggregate over them.

d$hour <- substring(d$time, 12)
d.2 <- aggregate(count ~ substring(d$time, 12), d, mean)
head(d.2)
#        hour count
# 1  00:00:00 35.00
# 2  01:00:00 73.50
# 3  02:00:00 45.50
# 4  03:00:00 61.75
# 5  04:00:00 65.25
# 6  05:00:00 40.00

Or use ave to get the hourly averages as a new column.

d <- transform(d, h.average=ave(count, substring(time, 12)))
head(d)
#                  time count h.average
# 1 2019-06-30 00:00:00    40    35.00
# 2 2019-06-30 01:00:00    67    73.50
# 3 2019-06-30 02:00:00    34    45.50
# 4 2019-06-30 03:00:00    49    61.75
# 5 2019-06-30 04:00:00    67    65.25
# 6 2019-06-30 05:00:00    43    40.00

Data

d <- structure(list(time = structure(c(1561845600, 1561849200, 1561852800, 
1561856400, 1561860000, 1561863600, 1561867200, 1561870800, 1561874400, 
1561878000, 1561881600, 1561885200, 1561888800, 1561892400, 1561896000, 
1561899600, 1561903200, 1561906800, 1561910400, 1561914000, 1561917600, 
1561921200, 1561924800, 1561928400, 1561932000, 1561935600, 1561939200, 
1561942800, 1561946400, 1561950000, 1561953600, 1561957200, 1561960800, 
1561964400, 1561968000, 1561971600, 1561975200, 1561978800, 1561982400, 
1561986000, 1561989600, 1561993200, 1561996800, 1562000400, 1562004000, 
1562007600, 1562011200, 1562014800, 1562018400, 1562022000, 1562025600, 
1562029200, 1562032800, 1562036400, 1562040000, 1562043600, 1562047200, 
1562050800, 1562054400, 1562058000, 1562061600, 1562065200, 1562068800, 
1562072400, 1562076000, 1562079600, 1562083200, 1562086800, 1562090400, 
1562094000, 1562097600, 1562101200, 1562104800, 1562108400, 1562112000, 
1562115600, 1562119200, 1562122800, 1562126400, 1562130000, 1562133600, 
1562137200, 1562140800, 1562144400, 1562148000, 1562151600, 1562155200, 
1562158800, 1562162400, 1562166000, 1562169600, 1562173200, 1562176800, 
1562180400, 1562184000, 1562187600), class = c("POSIXct", "POSIXt"
), tzone = ""), count = c(40L, 67L, 34L, 49L, 67L, 43L, 58L, 
37L, 22L, 97L, 3L, 78L, 16L, 74L, 27L, 72L, 87L, 9L, 99L, 98L, 
38L, 98L, 48L, 30L, 89L, 94L, 73L, 37L, 81L, 20L, 98L, 67L, 17L, 
88L, 75L, 8L, 39L, 53L, 20L, 92L, 61L, 23L, 56L, 33L, 60L, 19L, 
80L, 50L, 10L, 74L, 19L, 77L, 87L, 40L, 53L, 39L, 60L, 39L, 37L, 
65L, 51L, 56L, 98L, 50L, 23L, 38L, 53L, 36L, 61L, 12L, 6L, 81L, 
1L, 59L, 56L, 84L, 26L, 57L, 83L, 56L, 3L, 45L, 19L, 50L, 84L, 
95L, 14L, 98L, 97L, 22L, 46L, 7L, 47L, 55L, 38L, 43L)), row.names = c(NA, 
-96L), class = "data.frame")
Community
  • 1
  • 1
jay.sf
  • 60,139
  • 8
  • 53
  • 110
  • The values i get are all the values of count added together for each time of day and not the average for every hour over all the days – creerow Jul 10 '19 at 18:54
  • @creerow Eh, sure?? Add a `dput` of your data to your question, [help here](https://stackoverflow.com/a/5963610/6574038) and an exact expected output please! – jay.sf Jul 10 '19 at 20:11
  • Edited the question, output of dput was: structure(list(Time = structure(1:4, .Label = c("2019-06-30 05:00:00", "2019-06-30 06:00:00", "2019-06-30 07:00:00", "2019-06-30 08:00:00" ), class = "factor"), count = c(17L, 18L, 26L, 15L)), row.names = c(NA, 4L), class = "data.frame") – creerow Jul 11 '19 at 08:49
  • @creerow I can't find the error. Can you confirm that it works with my example data provided? Where's the difference between yours and mine? – jay.sf Jul 11 '19 at 13:52
  • 1
    Can confirm, with the provided data it seems to work just fine. I just realised your second solution with ave seems to work on my data too, don't know why the first won't work. Thanks! – creerow Jul 11 '19 at 14:19