0

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.

Community
  • 1
  • 1
AC11
  • 75
  • 2
  • 13
  • 1
    HI `AC11`, I couldn't find "hourly.apply" using ?RSiteSearch. Did you tried `period.apply`? – akrun Jun 26 '14 at 12:20

2 Answers2

3

"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
Tony Ladson
  • 3,539
  • 1
  • 23
  • 30
  • I have tried using your custom apply.hourly, however it seems to apply daily... It was working and applying hourly a few days ago, but now I'm trying to create repeatable processes and it's going to daily. :/ – Swift Arrow Jan 23 '20 at 19:34
  • Are you applying the function to time values that have a sub-hourly component? – Tony Ladson Jan 23 '20 at 20:53
1
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
GSee
  • 48,880
  • 13
  • 125
  • 145
Madhumita
  • 489
  • 1
  • 7
  • 15
  • Hi There, I am not able to post the answer in the proper format. But, I think this method will solve your problem. – Madhumita Aug 07 '14 at 10:22
  • I fixed the formatting (by selecting the code and clicking the `{}` button), but the OP asked for the `mean`, not the `sum` – GSee Aug 07 '14 at 11:48
  • In that case just change the sum to mean in the code (Fun=mean), the code will still work fine. – Madhumita Aug 08 '14 at 05:53