title: R: conditional join based on timestamp interval
body: <p>I have two data.frames:  </p>
  
  <pre><code># limits
     ID    Start_1      End_1    Start_2      End_2
  1  A 2013-04-23 2013-06-09 2013-04-26 2017-02-06
  2  B 2013-05-12 2013-08-08 2013-04-26 2017-02-06
  3  C 2013-04-24 2013-04-26 2017-02-05 2017-02-08
  </code></pre>
  
  <p>and</p>
  
  <pre><code># df (header shown)
     Timestamp          ID
  1 2013-04-24 14:01:21  A
  2 2013-04-24 14:01:46  B
  3 2013-04-24 14:01:50  C
  4 2013-04-25 00:02:19  A
  5 2013-04-25 02:02:48  B
  6 2013-04-25 04:02:04  C
  </code></pre>
  
  <p>I want to populate a column <code>Pop</code> in data.frame <code>df</code> based on the timestamp of its observations (<code>df$Timestamp</code>): if <code>df$Timestamp</code> is between two time limits (stored in data.frame, <code>limits</code>: <code>limits$Start_1</code> and <code>limits$End_1</code>), the <code>Pop</code> column is populated with 'Yes', if not, 'No'.   </p>
  
  <p>If <code>df$Timestamp</code> is between the two next time limits (<code>limits$Start_2</code> and <code>limits$End_2</code>), the <code>Pop</code> column is populated with 'Maybe', overwriting any 'Yes" or 'No'.</p>
  
  <p>The set-up looks like this:</p>
  
  <pre><code># main data.frame
  df&lt;-structure(list(Timestamp = structure(c(1366826481, 1366826506,
                                             1366826510, 1366862539, 1366869768, 1366876924, 1366948927, 1366948963,
                                             1367013725, 1367107304, 1367107308, 1367107316, 1486342833, 1486350011,
                                             1486350026, 1486429233, 1486436435, 1486436459, 1486515633, 1486522816,
                                             1486522834, 1486530052, 1486537217, 1486537251),
                                           class = c("POSIXct","POSIXt"), tzone = ""),
                     ID = structure(c(1L, 2L, 3L, 1L, 2L, 3L, 1L, 2L, 3L, 1L, 2L, 3L, 1L, 2L, 3L, 1L, 2L, 3L, 1L, 2L, 3L,1L, 2L, 3L),
                                    .Label = c("A", "B", "C"), class = "factor")),
                .Names = c("Timestamp", "ID"), row.names = c(NA, -24L), class = "data.frame")  
  
  # data.frame with time limits
  limits&lt;- structure(list(ID = structure(1:3, .Label = c("A", "B", "C"), class = "factor"),
                      Start_1 = structure(c(1366689600, 1368331200, 1366776000), class = c("POSIXct","POSIXt"), tzone = ""),
                      End_1 = structure(c(1370750400, 1375934400,1366948800), class = c("POSIXct", "POSIXt"), tzone = ""),
                      Start_2 = structure(c(1366948800, 1366948800, 1486270800), class = c("POSIXct", "POSIXt"), tzone = ""),
                      End_2 = structure(c(1486357200, 1486357200, 1486530000), class = c("POSIXct", "POSIXt"), tzone = "")),
                 .Names = c("ID","Start_1", "End_1", "Start_2", "End_2"),
                 row.names = c(NA,-3L), class = "data.frame")
  </code></pre>
  
  <p>I have two methods that seem to work but they are cumbersome and, for a real-life dataset (several thousands rows, hundreds of <code>ID</code>, and several <code>$Start</code> and <code>$End</code> periods for a same <code>ID</code>), it's hard to "trust" they work.  </p>
  
  <pre><code>###### Method 1 ######
  df1&lt;-df
  df1&lt;-left_join(df1, limits, by="ID")
  
  df1$Pop&lt;-ifelse(df1$Timestamp&gt;as.POSIXct(df1$Start_1) &amp;
                   df1$Timestamp&lt;as.POSIXct(df1$End_1), "Yes", "No")
  df1$Pop&lt;-ifelse(df1$Timestamp&gt;as.POSIXct(df1$Start_2) &amp;
                           df1$Timestamp&lt;as.POSIXct(df1$End_2), "Maybe", df1$Pop)
  df1$Pop&lt;-as.factor(df1$Pop)
  df1&lt;-df1[,-c(3,6)]
  
  ###### Method 2 ######
  df2&lt;-df
  df2&lt;-df2[with(df2, order(ID, Timestamp)), ]
  ids&lt;-as.factor(levels(droplevels(df2$ID)))
  tmp&lt;-NULL
  for(i in 1:length(ids)) {
    tmp[[i]]&lt;-ifelse(df2$Timestamp[which(df2$ID==ids[i])]&gt; as.POSIXct(limits$Start_1[i]) &amp;
                       df2$Timestamp[which(df2$ID==ids[i])]&lt; as.POSIXct(limits$End_1[i]), "Yes", "No") }
  tmp&lt;-data.frame(Pop = unlist(tmp)) # tmp is a list - this turns it into a data-frame
  df2&lt;-cbind(df2,tmp)
  
  # add 'Maybe'
  tmp2&lt;-NULL
  for(i in 1:length(ids)) {
    tmp2[[i]]&lt;-df2$Timestamp[which(df2$ID==ids[i])]&gt; as.POSIXct(limits$Start_2[i]) &amp; 
      df2$Timestamp[which(df2$ID==ids[i])]&lt; as.POSIXct(limits$End_2[i]) }
  tmp2&lt;-data.frame(Pop = unlist(tmp2))
  df2$Pop&lt;-as.character(df2$Pop)
  df2$Pop[which(tmp2$Pop==TRUE)]&lt;-'Maybe'
  df2$Pop&lt;-as.factor(df2$Pop)
  df2&lt;-df2[with(df2, order(Timestamp)), ]
  </code></pre>
  
  <p>Is there a more elegant way (using a function, package) to do this kind of join?  </p>
  
  <p>EDIT:<br>
  In Method 1's <code>ifelse()</code>, I was using <code>limits$Start_1</code>, <code>limits$End_1</code>, etc instead of <code>df1$Start1</code>, <code>df1$End_1</code>, etc.  </p>