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>