CODEWITHSUNDEEP
rtime-seriesgroupingintervalscut
Meldamos
Meldamos

Reputation: 43

How to group Time Series data into round intervals of 5 minutes in R?

I have a time series data frame looking like this:

  Time                 Source  Value
1 2016-01-20 15:10:04  C04     OPEN
2 2016-01-20 15:09:57  M04     true
3 2016-01-20 15:09:53  M02     true
4 2016-01-20 15:09:53  M03     true
5 2016-01-20 14:44:54  M04     true

now I would like to group them in intervals of 5 minutes starting from 00:00:00, so that I get intervals of 0-5-10-15-20... and so on. The intervals shall be used later as a group identifier:

  Time                 Source  Value  Group
1 2016-01-20 15:10:04  C04     OPEN   10
2 2016-01-20 15:09:57  M04     true   5
3 2016-01-20 15:09:53  M02     true   5
4 2016-01-20 15:09:53  M03     true   5
5 2016-01-20 14:44:54  M04     true   40

I already tried to cut() the dates using breaks="5 min" but instead of getting round start and end values, the result looks like this:

 > table(cut.POSIXt(df.formatted$Time, breaks="5 min"))[1:5]
 2015-12-31 12:49:00 2015-12-31 12:54:00 2015-12-31 12:59:00 2015-12-31 13:04:00 2015-12-31 13:09:00 
              4                   0                   0                   1                  15

Is there a way to tell cut() to use round time intervals? I also tried the grouping using xts package, but got more confused by the OHLC than it helped me. Also I tried using the heR.Misc package (see time.factor documentation but due to the poor documentation I couldn't get it to run properly.

Does someone maybe know how to solve this problem?

Upvotes: 1

Views: 1367

Answers (1)

vck
vck

Reputation: 837

First, you need to install "chron" package. This package has minutes() function that gives you the minute of your time.

I have to paste first and second column together but i don't think you have to do too. Use just tmpTime <- tmp[,1]

library(chron)

tmp <- read.table(text="Time  Source  Value
 2016-01-20 15:10:04  C04     OPEN
 2016-01-20 15:09:57  M04     true
 2016-01-20 15:09:53  M02     true
 2016-01-20 15:09:53  M03     true
 2016-01-20 14:44:54  M04     true", header=T, row.names= NULL)

tmpTime <- paste(tmp[,1], tmp[,2])

group <- seq(0,55,5)

sapply(tmpTime, function(x){
  x <- minutes(x)
  for(i in 2:length(group)){
    if(x < group[i]) {return(group[i-1]); break}
    else if(x >= group[length(group)]) return(group[length(group)])
  }
})

[1] 10  5  5  5 40

Upvotes: 1

Related Questions