title: How to match two data.frames with an inexact matching identifier (one identifier has to be in the range of the other)
body: <p>I have the following matching problem: I have two data.frames, one with an observation every month (per company ID), and one with an observation every quarter (per company ID; note that quarter means fiscal quarter; therefore 1Q = Jan, Feb, Mar is not necessarily correct and also, a fiscal quarter is not necessarily 3 month long).</p>
  
  <p>For every month and company, I want to get the correct value of that quarter. Consequently, several months have the same value for one quarter. As an example see the code below:</p>
  
  <pre><code>monthlyData &lt;- data.frame(ID = rep(c("A", "B"), each = 5),
                    Month = rep(1:5, times = 2),
                    MonValue = 1:10)
  monthlyData
     ID Month MonValue
  1   A     1        1
  2   A     2        2
  3   A     3        3
  4   A     4        4
  5   A     5        5
  6   B     1        6
  7   B     2        7
  8   B     3        8
  9   B     4        9
  10  B     5       10
  
  #Quarterly data, i.e. the value of every quarter has to be matched to several months in d1
  #However, I want to match fiscal quarters, which means that one quarter is not necessarily 3 month long
  qtrData &lt;- data.frame(ID = rep(c("A", "B"), each = 2),
                    startMonth = c(1, 4, 1, 3),
                    endMonth   = c(3, 5, 2, 5),
                    QTRValue   = 1:4)
  qtrData
    ID startMonth endMonth QTRValue
  1  A          1        3        1
  2  A          4        5        2
  3  B          1        2        3
  4  B          3        5        4
  
  #Desired output
     ID Month MonValue QTRValue
  1   A     1        1        1
  2   A     2        2        1
  3   A     3        3        1
  4   A     4        4        2
  5   A     5        5        2
  6   B     1        6        3
  7   B     2        7        3
  8   B     3        8        4
  9   B     4        9        4
  10  B     5       10        4
  </code></pre>
  
  <p>Note: This question was posted on R-help months ago, but I didn't get any answer then and found a solution myself (see <a href="https://stat.ethz.ch/pipermail/r-help/2011-April/275299.html" rel="nofollow noreferrer">R-help</a>). Now, however, I posted a question on stackoverflow where I have a question regarding the <code>data.table</code> where this problem was mentioned as well and there, Andrie asked me to post this question again because he apparently has a good solution for it (see <a href="https://stackoverflow.com/questions/8009900/is-my-way-of-duplicating-rows-in-data-table-efficient/8010526#8010526">Question on SO</a>)</p>
  
  <p><strong>UPDATE: See Matthew Dowle's comment: how does the real data look?</strong></p>
  
  <p>This data is a more realistic one. I added a few rows, but the only main part that changed is column <code>endMonth</code> in <code>qtrData</code>. More precisely, the <code>startMonth</code> is not necessarily the <code>endMonth</code> of the previous quarter plus one month anymore. Therefore, using the <code>roll</code> option, I think that you need another line of code (if not, you get 20 rows back, but with Andrie's solution, which is the desired one, you get 17 rows back). Then there is no performance difference anymore, if I don't miss anything here.</p>
  
  <pre><code>monthlyData_new &lt;- data.table(ID = rep(c("A", "B"), each = 10),
                    Month = rep(1:10, times = 2),
                    MonValue = 1:20)
  
  qtrData_new &lt;- data.table(ID = rep(c("A", "B"), each = 3),
                    startMonth = c(1, 4, 7, 1, 3, 8),
                    endMonth   = c(3, 5, 10, 2, 5, 10),
                    QTRValue   = 1:6)
  
  setkey(qtrData_new, ID)
  setkey(monthlyData_new, ID)
  
  qtrData1 &lt;- qtrData_new
  setkey(qtrData1, ID, startMonth)
  monthlyData1 &lt;- monthlyData_new
  setkey(monthlyData1, ID, Month)
  
  withTable1 &lt;- function(){
    xx &lt;- qtrData1[monthlyData1, roll=TRUE]
    xx &lt;- xx[startMonth &lt;= endMonth]
  
  }
  
  withTable2 &lt;- function(){
    yy &lt;- monthlyData_new[qtrData_new][Month &gt;= startMonth &amp; Month &lt;= endMonth]
  
  }
  
  benchmark(withTable1, withTable2, replications=1e6)
          test replications elapsed relative user.self sys.self user.child sys.child
  1 withTable1      1000000   4.244 1.028599     4.232    0.008          0         0
  2 withTable2      1000000   4.126 1.000000     4.096    0.028          0         0
  </code></pre>