title: Merging times between start and stop events in R
body: <p>I am trying to merge two datasets where one dataset contains a datetime variable (<code>dataA</code>) and another dataset contains start and stop times (<code>dataB</code>).  I want to take all datetimes between the start and stop times.  I came up with a simple solution, but it takes over 3 hours for a dataset with around a million observations.  Can someone please provide a faster solution?  Below is an example with my solution; it works well for small sample sizes, but reducing <code>secBetwMeas</code> greatly increases the computation time (setting <code>secBetwMeas=5</code> takes almost 40s) .</p>
  
  <pre><code>secBetwMeas &lt;- 5*60
  dataA &lt;- data.frame(id=c(rep("A",length(seq(as.POSIXct("2014-01-01 01:00:00", format="%Y-%m-%d %H:%M:%S"),as.POSIXct("2014-01-02 04:00:00", format="%Y-%m-%d %H:%M:%S"), by=secBetwMeas))),
                           rep("B",length(seq(as.POSIXct("2014-06-01 04:00:00", format="%Y-%m-%d %H:%M:%S"),as.POSIXct("2014-06-02 05:00:00", format="%Y-%m-%d %H:%M:%S"), by=secBetwMeas)))),
                      dataDtTm=c(seq(as.POSIXct("2014-01-01 01:00:00", format="%Y-%m-%d %H:%M:%S"),
                                   as.POSIXct("2014-01-02 04:00:00", format="%Y-%m-%d %H:%M:%S"), by=secBetwMeas),
                                 seq(as.POSIXct("2014-06-01 04:00:00", format="%Y-%m-%d %H:%M:%S"),
                                     as.POSIXct("2014-06-02 05:00:00", format="%Y-%m-%d %H:%M:%S"), by=secBetwMeas)))
  dataB &lt;- data.frame(id=rep(c("A","B"),each=2),
                      startDtTm=c(as.POSIXct("2014-01-01 01:10:00", format="%Y-%m-%d %H:%M:%S"),
                                  as.POSIXct("2014-01-02 03:05:00", format="%Y-%m-%d %H:%M:%S"),
                                  as.POSIXct("2014-06-01 04:30:00", format="%Y-%m-%d %H:%M:%S"),
                                  as.POSIXct("2014-06-01 22:10:00", format="%Y-%m-%d %H:%M:%S")),
                      endDtTm=c(as.POSIXct("2014-01-01 08:30:00", format="%Y-%m-%d %H:%M:%S"),
                                as.POSIXct("2014-01-02 07:05:00", format="%Y-%m-%d %H:%M:%S"),
                                as.POSIXct("2014-06-01 08:30:00", format="%Y-%m-%d %H:%M:%S"),
                                as.POSIXct("2014-06-02 04:05:00", format="%Y-%m-%d %H:%M:%S")))
  
  
  ### Trying to optimize this solution: ###
  dataA$endDtTm &lt;- dataA$startDtTm &lt;- as.POSIXct(NA)
  for(i in 1:NROW(dataA)){
    index &lt;- (dataA$id[i]==dataB$id &amp; dataA$dataDtTm[i] &gt;= dataB$startDtTm &amp; dataA$dataDtTm[i] &lt;= dataB$endDtTm)
    stopifnot(sum(index)==0 || sum(index)==1)
    if(any(index)){
      dataA$startDtTm[i] &lt;- dataB$startDtTm[index]
      dataA$endDtTm[i] &lt;- dataB$endDtTm[index]
    }
  }
  dataA &lt;- na.omit(dataA)
  head(dataA)  #This is the dataset I want to see
  </code></pre>