How to compare the two time intervals in R?

Question

I have a data of uneven time intervals like this.

x=data.frame(date=rep('2014-07-24',5),from=c("14:12","14:12","14:30","14:24","14:32"),to=c("15:25","15:40","15:35","15:50","15:55"),Load=c(2,2,1,1,1))

the 'from' and 'to' columns represent the start and end time for corresponding fluctuation in load for that time interval. I want to convert this data into 15 minutes interval (96 blocks) for corresponding dates. So if the interval 14:15-14:30 is present in that interval (from-to), it will be assigned that value of load. If it is present in another interval too, the value of load will be added further for this interval.

Is there any way in R through which I can compare if this interval 00:00-00:15 (and others) is present in uneven intervals like 12:40-13:45, so that I can accordingly arrange the data above like this.

y=data.frame(date=rep('2014-07-24'),block=c("14:15-14:30","14:30-14:45","14:45-15:00","15:00-15:15","15:15-15:30"),load=c(4,7,7,7,7))

Please help. Thanks a lot

Please add your solution for future users with the same issue. — zx8754, Sep 10 '15 at 10:13

score 1 · Answer 1 · answered Sep 10 '15 at 17:16

I am able to solve the problem I posted. Thanks to the suggestion of foverlaps by @zx8754

x=data.table(date=rep('2014-07-24',5),from=c("14:12","14:12","14:30","14:24","14:32"),
         to=c("15:25","15:40","15:35","15:50","15:55"),Load=c(2,2,1,1,1))

 library(chron)

 x$from=times(paste0(as.character(x$from),":00"))
 x$to=times(paste0(as.character(x$to),":00"))

 min=15
 interval=min/(60*24)
 a=seq(from=times('14:15:00'),to=times('15:15:00'),by=interval)
 b=seq(from=times('14:30:00'),to=times('15:30:00'),by=interval)

 x2=data.table(from=a,to=b)

 setkey(x2,from,to)

 f=foverlaps(x,x2,type='any',which=TRUE)

 #### following loop is to obtain the load

 x2$load=0

 for (i in unique(f$yid)){

    xid=f$xid[f$yid==i]

        for (j in xid) {
          x2$load[i]=x2$load[i]+x$Load[j]
    }

  }

the output comes to be as:

    y=data.frame(date=rep('2014-07-24'),block=c("14:15-14:30","14:30-
    14:45","14:45-15:00","15:00-15:15","15:15-15:30"),load=c(7,7,7,7,7))

I didn't get why foverlaps counted 14:15-14:30 into 14:32-15:55. That's why the load of first row came to be 7.

Suggestions to improve this solution are welcome.

Posted an answer which doesn't need a for-loop. HTH. If you have further questions, just let me know. — Jaap, Jan 30 '16 at 15:19

Jaap · Answer 2 · 2016-01-30T15:18:00.577

Using foverlaps from data.table I would approach it as follows:

1) Get proper datetime columns for both datatables:

x[, `:=` (from = as.POSIXct(paste(date,from)), to = as.POSIXct(paste(date,to)), date = NULL)]
y[, c("start","end") := tstrsplit(block, "-", fixed=TRUE)
  ][, `:=` (start = as.POSIXct(paste(date,start)), 
            end = as.POSIXct(paste(date,end)), 
            block = NULL, date = NULL)]

2) Set the keys:

setkey(x, from, to)
setkey(y, start, end)

3) Look for overlaps between x and y and get the maximum values:

x.new <- foverlaps(y, x, type = "within")[, .(load.new = max(pmax(Load,load))),
                                          by = .(from, to)]

These steps result in:

> x.new
                  from                  to load.new
1: 2014-07-24 14:12:00 2014-07-24 15:25:00        7
2: 2014-07-24 14:12:00 2014-07-24 15:40:00        7
3: 2014-07-24 14:24:00 2014-07-24 15:50:00        7
4: 2014-07-24 14:30:00 2014-07-24 15:35:00        7
5: 2014-07-24 14:32:00 2014-07-24 15:55:00        7

Used data:

x <- data.table(date=rep('2014-07-24',5),
                from=c("14:12","14:12","14:30","14:24","14:32"),
                to=c("15:25","15:40","15:35","15:50","15:55"),
                Load=c(2,2,1,1,1))
y <- data.table(date=rep('2014-07-24'),
                block=c("14:15-14:30","14:30-14:45","14:45-15:00","15:00-15:15","15:15-15:30"),
                load=c(4,7,7,7,7))

How to compare the two time intervals in R?

2 Answers2