title: Best way to join / merge by range in pandas
body: <p>I'm frequently using pandas for merge (join) by using a range condition.</p>
  
  <p>For instance if there are 2 dataframes:</p>
  
  <p><strong>A</strong> (A_id, A_value)</p>
  
  <p><strong>B</strong> (B_id,B_low, B_high, B_name)</p>
  
  <p>which are big and approximately of the same size (let's say 2M records each).</p>
  
  <p>I would like to make an inner join between A and B, so A_value would be between B_low and B_high.</p>
  
  <p>Using SQL syntax that would be:</p>
  
  <pre><code>SELECT *
  FROM A,B
  WHERE A_value between B_low and B_high
  </code></pre>
  
  <p>and that would be really easy, short and efficient.</p>
  
  <p>Meanwhile in pandas the only way (that's not using loops that I found), is by creating a dummy column in both tables, join on it (equivalent to cross-join) and then filter out unneeded rows. That sounds heavy and complex:</p>
  
  <pre><code>A['dummy'] = 1
  B['dummy'] = 1
  Temp = pd.merge(A,B,on='dummy')
  Result = Temp[Temp.A_value.between(Temp.B_low,Temp.B_high)]
  </code></pre>
  
  <p>Another solution that I had is by applying on each of A value a search function on B by using<code>B[(x&gt;=B.B_low) &amp; (x&lt;=B.B_high)]</code> mask, but it sounds inefficient as well and might require index optimization.</p>
  
  <p>Is there a more elegant and/or efficient way to perform this action?</p>