Loop to check if date contains some time interval in R

Question

A=c("a","b","c","d","e","f","g","h","h","j")
B=c(2016-01-04 08:00:00, 2016-01-04 08:02:26, 2016-01-04 09:15:15, 
    2016-01-04 15:16:03, 2016-01-05 12:35:12, 2016-01-05 16:35:05,
    2016-01-06 08:20:35, 2016-01-06 08:20:36, 2016-01-07 03:09:00,
    2016-01-07 07:16:00)
a=as.data.frame(A)
b=as.data.frame(B)
c=cbind(a,b)

A1=c(2016-01-04 07:59:59, 2016-01-05 12:35:12, 2016-01-06 16:36:00,
     2016-01-07 03:08:00, 2016-01-08 09:00:00, 2016-01-09 09:00:00)
B1=c(2016-01-04 10:00:00, 2016-01-05 12:40:00, 2016-01-06 16:38:53,
     2016-01-07 07:10:00, 2016-01 08 23:50:42, 2016-01-09 17:45:32)
a1=as.data.frame(A1)
b1=as.data.frame(B1)
c1=cbind(a1,b1)

z=0
E=matrix(NA,nrow=length(c[,1]),ncol=length(c1[,1]))
E


for(i in 1:length(c[,1]))
{
  N<-c[i,2] 
  for(j in 1:length(c1[,1]))
  {

    z[j]=(N <= c1[j,2] & N >= c1[j,1])
  }
  E[,i]=z

}  
E

g=which(E == 1,arr.ind=T)
n=g[,2]
s<-c[n,]

I need to check if dates from data frame c contains some interval from data frame c1 and then create new data frame which those data where dates contains one of time intervals.

I tried that code with simple numbers and it worked but just when matrix was n*n, I need that it could work on different c and c1 lenghts.

So maybe I need some library for time and change loop for lenght?

Thanks in advance!

you can check [foverlaps](http://stackoverflow.com/questions/25815032/finding-overlaps-between-interval-sets-efficient-overlap-joins) from `data.table`. — akrun, Feb 08 '16 at 10:06

lukeA · Accepted Answer · 2016-02-08T11:53:32.620

Here's one approach:

library(data.table)
setDT(c1, key = c("A1", "B1"))
setDT(c)[, C:=B]
foverlaps(c, c1, by.x=c("C", "B"))[!is.na(A1) & !is.na(B1)][, C:=NULL][]
#                     A1                  B1 A                   B
# 1: 2016-01-04 07:59:59 2016-01-04 10:00:00 a 2016-01-04 08:00:00
# 2: 2016-01-04 07:59:59 2016-01-04 10:00:00 b 2016-01-04 08:02:26
# 3: 2016-01-04 07:59:59 2016-01-04 10:00:00 c 2016-01-04 09:15:15
# 4: 2016-01-05 12:35:12 2016-01-05 12:40:00 e 2016-01-05 12:35:12
# 5: 2016-01-07 03:08:00 2016-01-07 07:10:00 h 2016-01-07 03:09:00

Data

A=c("a","b","c","d","e","f","g","h","h","j")
B=c("2016-01-04 08:00:00", "2016-01-04 08:02:26", "2016-01-04 09:15:15", 
    "2016-01-04 15:16:03", "2016-01-05 12:35:12", "2016-01-05 16:35:05",
    "2016-01-06 08:20:35", "2016-01-06 08:20:36", "2016-01-07 03:09:00",
    "2016-01-07 07:16:00")
a=as.data.frame(A)
b=as.data.frame(B)
c=cbind(a,b)

A1=c("2016-01-04 07:59:59", "2016-01-05 12:35:12", "2016-01-06 16:36:00",
     "2016-01-07 03:08:00", "2016-01-08 09:00:00", "2016-01-09 09:00:00")
B1=c("2016-01-04 10:00:00", "2016-01-05 12:40:00", "2016-01-06 16:38:53",
     "2016-01-07 07:10:00", "2016-01-08 23:50:42", "2016-01-09 17:45:32")
a1=as.data.frame(A1)
b1=as.data.frame(B1)
c1=cbind(a1,b1)
c$B <- as.POSIXct(c$B)
c1$B1 <- as.POSIXct(c1$B1)
c1$A1 <- as.POSIXct(c1$A1)

Error in if (any(x[[xintervals[2L]]] - x[[xintervals[1L]]] < 0L)) stop("All entries in column ", : missing value where TRUE/FALSE needed In addition: Warning message: In Ops.factor(x[[xintervals[2L]]], x[[xintervals[1L]]]) : ‘-’ not meaningful for factors — Greta Juknaitė, Feb 08 '16 at 11:50
You have to set up the data correctly - I added it to the post. You also might want to correct you original posting, because `A1=c(2016-01-04 07:59:59...)` does not work. — lukeA, Feb 08 '16 at 11:55

Loop to check if date contains some time interval in R

1 Answers1