How to match multiple columns in pandas DataFrame for an "interval"?
<p>I have the following pandas DataFrame:</p>

<pre><code>import pandas as pd
df = pd.DataFrame('filename.csv')
print(df)

order    start    end    value    
1        1342    1357    category1
1        1459    1489    category7
1        1572    1601    category23
1        1587    1599    category2
1        1591    1639    category1
....
15        792     813    category13
15        892     913    category5
....
</code></pre>

<p>So, there is an <code>order</code> column encompasses many rows each, and then a range/interval from <code>start</code> to <code>end</code> for each row. Each row then is labeled by a certain <code>value</code> (e.g. category1, category2, etc.)</p>

<p>Now I have another dataframe called <code>key_df</code>. It is basically the exact same format:</p>

<pre><code>import pandas as pd
key_df = pd.DataFrame(...)
print(key_df)

order    start    end    value    
1        1284    1299    category4
1        1297    1309    category9
1        1312    1369    category3
1        1345    1392    category29
1        1371    1383    category31
....
1        1471    1501    category31
...
</code></pre>

<p>My goal is to take the <code>key_df</code> dataframe and check whether the intervals <code>start:end</code> match any of the rows in the original dataframe <code>df</code>. If it does, this row in <code>df</code> should be labeled with the <code>key_df</code> dataframe's <code>value</code> value. </p>

<p>In our example above, the dataframe <code>df</code> would end up like this:</p>

<pre><code>order    start    end    value        key_value
1        1342    1357    category1    category29
1        1459    1489    category7    category31
....
</code></pre>

<p>This is because if you look at <code>key_df</code>, the row</p>

<pre><code>1        1345    1392    category29
</code></pre>

<p>with interval <code>1::1345-1392</code> falls in the interval <code>1::1342-1357</code> in the original <code>df</code>. Likewise, the <code>key_df</code> row:</p>

<pre><code>1        1471    1501    category31
</code></pre>

<p>corresponds to the second row in <code>df</code>:</p>

<pre><code>1        1459    1489    category7    category31
</code></pre>

<p>I'm not entirely sure</p>

<p>(1) how to accomplish this task in pandas </p>

<p>(2) how to scale this efficiently in pandas</p>

<p>One could begin with an if statement, e.g. </p>

<pre><code>if df.order == key_df.order:
    # now check intervals...somehow
</code></pre>

<p>but this doesn't take advantage of the dataframe structure. One then must check by interval, i.e. something like <code>(df.start =&lt; key_df.start) &amp;&amp; (df.end =&gt; key_df.end)</code></p>

<p>I'm stuck. What is the most efficient way to match multiple columns in an "interval" in pandas? (Creating a new column if this condition is met is then straightforward)</p>

<p>You can use <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.merge.html" rel="nofollow"><code>merge</code></a> with <a href="http://pandas.pydata.org/pandas-docs/stable/indexing.html#boolean-indexing" rel="nofollow"><code>boolean indexing</code></a>, but if <code>DataFrames</code> are large, scaling is problematic:</p>

<pre><code>df1 = pd.merge(df, key_df, on='order', how='outer', suffixes=('','_key'))
df1 = df1[(df1.start &lt;= df1.start_key) &amp; (df1.end &lt;= df1.end_key)]
print (df1)
    order  start   end      value  start_key  end_key   value_key
3       1   1342  1357  category1     1345.0   1392.0  category29
4       1   1342  1357  category1     1371.0   1383.0  category31
5       1   1342  1357  category1     1471.0   1501.0  category31
11      1   1459  1489  category7     1471.0   1501.0  category31
</code></pre>

<p>EDIT by comment:</p>

<pre><code>df1 = pd.merge(df, key_df, on='order', how='outer', suffixes=('','_key'))
df1 = df1[(df1.start &lt;= df1.start_key) &amp; (df1.end &lt;= df1.end_key)]
df1 = pd.merge(df, df1, on=['order','start','end', 'value'], how='left')
print (df1)
   order  start   end       value  start_key  end_key   value_key
0      1   1342  1357   category1     1345.0   1392.0  category29
1      1   1342  1357   category1     1371.0   1383.0  category31
2      1   1342  1357   category1     1471.0   1501.0  category31
3      1   1459  1489   category7     1471.0   1501.0  category31
4      1   1572  1601  category23        NaN      NaN         NaN
5      1   1587  1599   category2        NaN      NaN         NaN
6      1   1591  1639   category1        NaN      NaN         NaN
7     15    792   813  category13        NaN      NaN         NaN
8     15    892   913   category5        NaN      NaN         NaN
</code></pre>

python pandas dataframe match intervals