CODEWITHSUNDEEP
rdate
Jordan
Jordan

Reputation: 614

Comparing overlap within sequences of dates within a data frame

I have a data frame with 2 columns of starting dates and 2 columns of ending dates. I want to count the number of overlapping dates between the two sets of series (ie., how many days in the start2/end2 series fall within start1/end1 (as seen in the "output" column here)). 

      start1       end1     start2       end2 output
 2011-02-10 2011-02-11 2011-02-10 2011-02-10      1
 2009-07-25 2009-07-27 2009-07-26 2009-07-27      2
 2007-12-02 2007-12-07 2007-12-08 2007-12-10      0

start1 <- as.Date(c("2011-02-10", "2009-07-25", "2007-12-02"))
end1 <- as.Date(c("2011-02-11", "2009-07-27", "2007-12-07")) 
start2 <- as.Date(c("2011-02-10", "2009-07-26", "2007-12-08"))
end2 <- as.Date(c("2011-02-10", "2009-07-27", "2007-12-10"))

my.dat <- data.frame(start1,end1,start2,end2)

I should probably be using a list of sorts here in order to supply vectors of starting and ending values to seq.Date, but I can't quite seem to get that to work. Instead, my non-list approach yields the error that the "from" argument should equal 1 (because it can't handle the vector of starting dates). 

my.dat$output <- length(seq(my.dat$start1,my.dat$end1,by=1) %in% seq(my.dat$start2,my.dat$end2,by=1))

There are plenty of discussions about matching dates on SO, but I can't seem to translate any of that to multiple columns of dates.

Thanks!!

Upvotes: 1

Views: 137

Answers (2)

MichaelChirico
MichaelChirico

Reputation: 34703

Using data.table:

library(data.table)
setDT(my.dat)[,output:=pmin(end2,end1)-pmax(start2,start1)+1]

       start1       end1     start2       end2 output
1: 2011-02-10 2011-02-11 2011-02-10 2011-02-10 1 days
2: 2009-07-25 2009-07-27 2009-07-26 2009-07-27 2 days
3: 2007-12-02 2007-12-07 2007-12-08 2007-12-10 0 days
4: 2007-12-02 2007-12-07 2007-11-01 2007-12-01 0 days

Can be converted to integers by wrapping RHS of := in as.integer.

Upvotes: 2

Jordan
Jordan

Reputation: 614

Of course, I figured it out right after posting. But I'm curious if others have more elegant solutions. Here was mine:

apply(my.dat[,c("start1","end1","start2","end2")],1,function(x)length((seq.Date(as.Date(x[1]),as.Date(x[2]),by=1) %in% (seq.Date(as.Date(x[3]),as.Date(x[4]),by=1)))))

Upvotes: 0

Related Questions