title: R - Speeding up approximate date match. idata.frame?
body: <p>I am struggling to efficiently perform a "close" date match between two data frames.  This question explores a solution using <code>idata.frame</code> from the <code>plyr</code> package, but I would be very happy with other suggested solutions as well.</p>
  
  <p>Here is a very simplistic version of the two data frames:</p>
  
  <pre><code>sampleticker&lt;-data.frame(cbind(ticker=c("A","A","AA","AA"),
    date=c("2005-1-25","2005-03-30","2005-02-15","2005-04-21")))
  sampleticker$date&lt;-as.Date(sampleticker$date,format="%Y-%m-%d")
  
  samplereport&lt;-data.frame(cbind(ticker=c("A","A","A","AA","AA","AA"),
    rdate=c("2005-2-15","2005-03-15","2005-04-15",
    "2005-03-01","2005-04-20","2005-05-01")))
  samplereport$rdate&lt;-as.Date(samplereport$rdate,format="%Y-%m-%d")
  </code></pre>
  
  <p>In the actual data, <code>sampleticker</code> is over 30,000 rows with 40 columns, and <code>samplereport</code> almost 300,000 rows with 25 columns.</p>
  
  <p>What I would like to do is to merge the two data frames so that each row in <code>sampleticker</code> is combined with the closest date match in <code>samplereport</code> which occurs AFTER the date in <code>sampleticker</code>.  I have solved similar problems in the past by doing a simple merge on the ticker field, sorting ascending, and then selecting unique combinations of ticker and date.  However, due to the size of this dataset, the merge blows up extremely quickly.</p>
  
  <p>As near as I can tell, <code>merge</code> does not allow this sort of approximate matching.  I have seen some solutions which use <code>findInterval</code>, but since the distance between the dates will vary, I am not sure that I can specify an interval that will work for all rows.</p>
  
  <p>Following another post <a href="https://stackoverflow.com/questions/3480813/mapping-over-the-rows-of-a-data-frame">here</a>,  I have written the following code to use <code>adply</code> on each row and to perform the join:</p>
  
  <pre><code>library(plyr)
  merge&lt;-adply(sampleticker,1,function(x){
    y&lt;-subset(samplereport,ticker %in% x$ticker &amp; rdate &gt; x$date)
    y[which.min(y$rdate),]
    }))
  </code></pre>
  
  <p>This works quite nicely: for the sample data, I get the below, which is what I want.</p>
  
  <pre><code>   date       ticker      rdate
   1 2005-01-25  A          2005-02-15
   2 2005-03-30  A          2005-04-15
   3 2005-02-15  AA         2005-03-01
   4 2005-04-21  AA         2005-05-01
  </code></pre>
  
  <p>However, since the code performs 30,000+ subsetting operations, it is extremely slow: I ran the above query for more than a day before finally killing it.</p>
  
  <p>I see <a href="https://stackoverflow.com/questions/3685492/r-speeding-up-group-by-operations">here</a> that plyr 1.0 has a structure, <code>idata.frame</code>, which calls the dataframe by reference, dramatically speeding up the subsetting operation.  However, I cannot get the following code to work:</p>
  
  <pre><code>isamplereport&lt;-idata.frame(samplereport)
  adply(sampleticker,1,function(x){
    y&lt;-subset(isamplereport,isamplereport$ticker %in% x$ticker &amp; 
      isamplereport$rdate &gt; x$date)
    y[which.min(y$rdate),]
  })
  </code></pre>
  
  <p>I get the error</p>
  
  <pre><code>Error in list_to_dataframe(res, attr(.data, "split_labels")) : 
  Results must be all atomic, or all data frames
  </code></pre>
  
  <p>This makes sense to me, since the operation returns an <code>idata.frame</code> (I assume).  However, changing the last line to:</p>
  
  <pre><code>as.data.frame(y[which.min(y$rdate),]) 
  </code></pre>
  
  <p>also throws an error: </p>
  
  <pre><code>Error in `[.data.frame`(x$`_data`, x$`_rows`, x$`_cols`) : 
  undefined columns selected.
  </code></pre>
  
  <p>Note that calling <code>as.data.frame</code> on the plain old <code>samplereport</code> returns the original data frame, as expected.</p>
  
  <p>I know that <code>idata.frame</code> is experimental, so I didn't necessarily expect it to work properly.  However, if anyone has an idea on how to fix this, I would appreciate it.  Alternately, if anyone could suggest a completely different approach that runs more efficiently, that would be fantastic.</p>
  
  <p>Matt</p>
  
  <p><strong>UPDATE</strong> Data.table is the right way to go about this.  See below.</p>