0
votes

I have a file with date/time data and its measured values for said date and time. The values were measured every ten minutes for the course of one month, and I am attempting to do a time series analysis eventually. Before that however, I wanted to aggregate the 10 minute intervals to hourly intervals by calculating the mean measurement of every 60 minutes. Here is a sample of my data(a total of 4319 observations):

Date/Time                 Value

2013-01-01 00:00:00     31,439999   
2013-01-01 00:10:00     33,439999   
2013-01-01 00:20:00     39         
2013-01-01 00:30:00     35,279999   
2013-01-01 00:40:00     37,279999   
2013-01-01 00:50:00     32,199997   
2013-01-01 01:00:00     35,279999   
2013-01-01 01:10:00     38,199997

My date/time data is of the type POSIXlt and the values measured are of the type factor. I have searched on this site and I have found several topics posted by other users but they do not all apply to me, or I can not recreate the same results using suggestions given on their posts.

For example, another user asked almost the exact same question as me: Aggregate values of 15 minute steps to values of hourly steps and I followed exactly the steps that their answers provided.

library(xts)
dat.xts <- xts(data$values,
           as.POSIXct(data$datetime))
hourly.apply(dat.xts,mean)

but for the last line I get the following error message:

Error: could not find function "hourly.apply"

Although I did already install the xts package as well as the zoo package, which the hourly.apply function appears to stem from. What could be the reason for this? Thank you in advance.

2
HI AC11, I couldn't find "hourly.apply" using ?RSiteSearch. Did you tried period.apply?akrun

2 Answers

3
votes

"hourly.apply" doesn't seem to exist but looking at the 'apply.daily' function in the xts package it seems straightforward to create.

see xts::apply.daily. I've changed 'days' to 'hours' to produce the following

apply.hourly <- function(x, FUN,...) {
  ep <- endpoints(x, 'hours')
  period.apply(x, ep, FUN, ...)
}

Try it out

my.time <- seq(from = as.POSIXct('2000-01-01 00:00:00'),
           to = as.POSIXct('2000-01-01 2:00:00'),
           by = '10 min')

my.data <- rep(10, length = length(my.time))
my.data <- as.xts(my.data, order.by = my.time)

apply.hourly(my.data, sum)

                [,1]
2000-01-01 00:50:00   60
2000-01-01 01:50:00   60
2000-01-01 02:00:00   10
1
votes
sales1512<-read.csv(file.choose())
head(sales1512)
             sdate hsales
1 2011-01-06 01:00      0
2 2011-01-06 02:00      0
3 2011-01-06 03:00      0
4 2011-01-06 04:00      0
5 2011-01-06 05:00      0
6 2011-01-06 06:00      0

library(xts)
dat.xts <- xts(sales1512$hsales,as.POSIXct(sales1512$sdate))
head(dat.xts)
                    [,1]
2011-01-06 00:00:00    0
2011-01-06 01:00:00    0
2011-01-06 02:00:00    0
2011-01-06 03:00:00    0
2011-01-06 04:00:00    0
2011-01-06 05:00:00    0

ep<-endpoints(dat.xts, on="hours", k=4)  #on=”how your data is spaced”,k=how you want to club it
dat.xts.4hourly<-period.apply(dat.xts, FUN=sum,INDEX=ep)
head(dat.xts.4hourly)
                    [,1]
2011-01-06 01:00:00    0
2011-01-06 05:00:00    0
2011-01-06 09:00:00   14
2011-01-06 13:00:00  112
2011-01-06 17:00:00  112
2011-01-06 21:00:00   42

tail(dat.xts.4hourly)
                     [,1]
2013-10-04 05:00:00 275.8
2013-10-04 09:00:00 551.6
2013-10-04 13:00:00 551.6
2013-10-04 17:00:00 551.6
2013-10-04 21:00:00 551.6
2013-10-04 23:00:00 275.8

nrow(dat.xts.4hourly)    
[1] 5523