title: R: Merge 2 Data Frame by Multiple Condition Using Dates & ID
body: <p>I am trying to merge 2 data frame using multiple conditions and have used merge command but unable to get a successful output.</p>
  
  <pre><code>#Data Frame df1#
  ID&lt;- c("A1", "A2","A3", "A4")
  Location &lt;- c("012A","234B","012A","238C" )
  startdate &lt;- as.Date(c("2014-11-01","2014-01-01","2015-10-01", "2015-01-01"))
  enddate &lt;- as.Date(c("2014-12-31","2014-08-31","2015-12-31","2015-12-31"))
  df1&lt;- data.frame(ID,Location, startdate, enddate)
  
  #Data Frame df2#
  ID&lt;-c("A1", "A1", "A4")
  N&lt;- c(2,1,2)
  Loss_Date &lt;- as.Date(c("2014-11-15", "2015-12-25", "2015-11-30"))
  Amt&lt;-c("2200","1000", "500")
  df2&lt;- data.frame(ID, N, Loss_Date,Amt)
  </code></pre>
  
  <p><strong>I want to merge these 2 dataframe by using Location as common column and  Loss_Date in df2 lies between (inclusive) Start_Date and End_Date in df2.</strong> You can see that second entry in df2 doesnt get mapped as the date is not in range of df1</p>
  
  <pre><code>#Required Output
  ID&lt;- c("A1", "A2","A3", "A4")
  Location &lt;- c("012A","234B","012A","238C" )
  startdate &lt;- as.Date(c("2014-11-01","2014-01-01","2015-10-01", "2015-01-01"))
  enddate &lt;- as.Date(c("2014-12-31","2014-08-31","2015-12-31","2015-12-31"))
  N&lt;-c(2,0,0,2)
  Loss_Date &lt;- c("2014-11-15", "NA", "NA", "2015-11-30")
  Amt&lt;-c("2200","0","0", "500")
  Output&lt;- data.frame(ID,Location, startdate, enddate,N, Loss_Date,Amt)
  </code></pre>
  
  <p>I created a common ID using the Year and ID but get the wrong mapping. Tried various ways to use merge and match but the command doesn work. I need this to run on over 170K observations. Both data frame are of unequal length. Any help would be really appreciated. </p>