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
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
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")