How to quickly group time columns in a data area into intervals?

Question

Assuming in R, I have data.frame with the first column representing the time (like POSIXct). The remaining columns (e.g. columns 2) are numeric data.

I would like to group time in 3 minute intervals. Each interval will have an average value that falls within this specific interval.

Right now, I have a for loop that iterates over a time column and generates an interval on the fly. I am wondering if there is a more elegant way to do the same?

Thanks in advance.

Derek

Answer 1

I think a command like the following will return a list of values ​​that fall into 3 minute intervals. ( v is the name of the data frame, and datecol is the name of the date column)

 library(plyr) v<-data.frame(datecol=as.POSIXct(c( "2010-01-13 03:02:38 UTC", "2010-01-13 03:03:14 UTC", "2010-01-13 03:05:52 UTC", "2010-01-13 03:07:42 UTC", "2010-01-13 03:09:38 UTC", "2010-01-13 03:10:14 UTC", "2010-01-13 03:12:52 UTC", "2010-01-13 03:13:42 UTC", "2010-01-13 03:15:42 UTC", "2010-01-13 03:16:38 UTC", "2010-01-13 03:18:14 UTC", "2010-01-13 03:21:52 UTC", "2010-01-13 03:22:42 UTC", "2010-01-13 03:24:19 UTC", "2010-01-13 03:25:19 UTC" )), x = cumsum(runif(15)*10),y=cumsum(runif(15)*20)) dlply(v,.(cut(datecol,"3 min")),"[") 

Answer 2

The zoo and xts packages outperform this and have extensive documentation. Here is the pre-canned to.minutes3 , but I also used aggregate.zoo() with custom functions doing the same thing manually:

 > library(xts) > x <- xts(cumsum(abs(rnorm(20))), Sys.time()+60*(0:19)) > x [,1] 2010-05-27 14:44:25 1.2870 2010-05-27 14:45:25 3.3187 2010-05-27 14:46:25 4.0976 2010-05-27 14:47:25 5.3304 2010-05-27 14:48:25 6.9415 2010-05-27 14:49:25 7.4508 2010-05-27 14:50:25 8.5281 2010-05-27 14:51:25 8.7145 2010-05-27 14:52:25 9.0120 2010-05-27 14:53:25 10.5063 2010-05-27 14:54:25 11.6312 2010-05-27 14:55:25 11.9813 2010-05-27 14:56:25 13.8883 2010-05-27 14:57:25 14.1696 2010-05-27 14:58:25 14.3269 2010-05-27 14:59:25 14.6768 2010-05-27 15:00:25 15.4926 2010-05-27 15:01:25 16.8408 2010-05-27 15:02:25 18.7739 2010-05-27 15:03:25 19.7815 > to.minutes3(x) x.Open x.High x.Low x.Close 2010-05-27 14:44:25 1.2870 1.2870 1.2870 1.2870 2010-05-27 14:47:25 3.3187 5.3304 3.3187 5.3304 2010-05-27 14:50:25 6.9415 8.5281 6.9415 8.5281 2010-05-27 14:53:25 8.7145 10.5063 8.7145 10.5063 2010-05-27 14:56:25 11.6312 13.8883 11.6312 13.8883 2010-05-27 14:59:25 14.1696 14.6768 14.1696 14.6768 2010-05-27 15:02:25 15.4926 18.7739 15.4926 18.7739 2010-05-27 15:03:25 19.7815 19.7815 19.7815 19.7815 >