title: merge two dataframes by nearest preceding date while aggregating
body: <p>I am trying to match two datasets by nearest preceding date, by group.
  So within a group, I would like to add the variables of a second dataset (d2) to that of the first (d1) when the date of the first is the nearest date on or before the date in the second. If two rows in the second dataset are matched with one row in the first I would like to add the larger of the values. (there will always be at least one date in d1 less then the date in d2, by group)</p>
  
  <p>Here is an example, which hopefully makes it clearer</p>
  
  <pre><code> d1 = data.frame(id=c(1,1,1,2,2), 
                   ref=as.Date(c("2013-12-07", "2014-12-07", "2015-12-07", "2013-11-07", "2014-11-07" )))
   d1
  #   id        ref
  # 1  1 2013-12-07
  # 2  1 2014-12-07
  # 3  1 2015-12-07
  # 4  2 2013-11-07
  # 5  2 2014-11-07
  
   d2 = data.frame(id=c(1,1,2),                
                   date=as.Date(c("2014-05-07","2014-12-05", "2015-11-05")),
                   x1 = factor(c(1,2,2), ordered = TRUE), 
                   x2 = factor(c(2, NA ,2), ordered=TRUE))
   d2
  #   id    date x1   x2
  # 1  1 2014-05-07  1    2
  # 2  1 2014-12-05  2 &lt;NA&gt;
  # 3  2 2015-11-05  2    2
  </code></pre>
  
  <p>With the expected outcome</p>
  
  <pre><code> output = data.frame(id=c(1,1,1,2,2),
                       ref=as.Date(c("2013-12-07", "2014-12-07", "2015-12-07", "2013-11-07", "2014-11-07" )),
                       x1 = c(2, NA, NA, NA, 2),
                       x2 = c(2, NA, NA, NA, 2))
   output
  #   id        ref x1 x2
  # 1  1 2013-12-07  2  2
  # 2  1 2014-12-07 NA NA
  # 3  1 2015-12-07 NA NA
  # 4  2 2013-11-07 NA NA
  # 5  2 2014-11-07  2  2
  </code></pre>
  
  <p>So for example, the first two observations of d2,  id=1, with dates <code>"2014-05-07","2014-12-05"</code>, are matched to the earlier date <code>"2013-12-07"</code> in d1. As there are two rows matched to one row in d1,
  then the highest level is selected.</p>
  
  <p>I could do this in base R by looping the following calculations through 
  each group but I was hoping for something more efficient.
  I would love to see a data.table approach (but I am limited to R v3.1 and data.table v1.9.4). Thanks</p>
  
  <p>real dataset: <br>
   d1: rows 1M / 100K groups <br>
   d2: rows 11K / 4K groups</p>
  
  <pre><code># for one group
  x = d1[d1$id==1, ]
  y = d2[d2$id==1, ]
  id = apply(outer(x$ref, y$date, "-"), 2, which.min)
  temp = cbind(y, ref=x$ref[id])
  # aggregate variables by ref
  temp = merge(aggregate(x1 ~ ref, data=temp, max),
  aggregate(x2 ~ ref, data=temp, max)
  )
  merge(x, temp, all=T)
  </code></pre>
  
  <p>ps: I had looked at <a href="https://stackoverflow.com/questions/23342647/how-to-match-by-nearest-date-from-two-data-frames">How to match by nearest date from two data frames?</a> and <a href="https://stackoverflow.com/questions/11341557/join-data-table-on-exact-date-or-if-not-the-case-on-the-nearest-less-than-date">Join data.table on exact date or if not the case on the nearest less than date</a> with no success.</p>