<p>I don't think Pandas has this implemented functionality out-of-the-box. Even if it did, solutions not designed specifically for bioinformatics probably rarely handle intervals on different chromosomes correctly unless you split the intervals by chromosome first.</p>

<p>Pandas does <em>handle</em> intervals (see docs for the <a href="https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.Interval.html" rel="nofollow noreferrer">Interval</a> and <a href="https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.IntervalIndex.html" rel="nofollow noreferrer">IntervalIndex</a> classes), but I've never used these. I <em>have</em> used the excellent <a href="https://github.com/chaimleib/intervaltree/" rel="nofollow noreferrer">intervaltree</a> package. Whatever approach and tools you decide to use, there's a good chance you'll have to write some code. Computing interval intersections with <code>intervaltree</code> would look something like this.</p>

<pre class="lang-py prettyprint-override"><code>&gt;&gt;&gt; from collections import defaultdict
&gt;&gt;&gt; from intervaltree import IntervalTree
&gt;&gt;&gt; from pandas import DataFrame
&gt;&gt;&gt; 
&gt;&gt;&gt; dict1 = {
...     0:['chr1','chr1','chr1','chr1','chr2'],
...     1:[1, 100, 150, 900, 1],
...     2:[100, 200, 500, 950, 100],
...     3:['feature1', 'feature2', 'feature3', 'feature4', 'feature4'],
...     4:[0, 0, 0, 0, 0],
...     5:['+','+','-','+','+']
... }
&gt;&gt;&gt; dict2 = {
...     0:['chr1','chr1'],
...     1:[155, 800],
...     2:[200, 901],
...     3:['feature5', 'feature6'],
...     4:[0, 0],
...     5:['-','+']
... }
&gt;&gt;&gt; df1 = DataFrame(dict1)
&gt;&gt;&gt; df2 = DataFrame(dict2)
&gt;&gt;&gt; 
&gt;&gt;&gt; 
&gt;&gt;&gt; # Construct an interval index from one of the dataframes,
&gt;&gt;&gt; #   one tree per chromosome
&gt;&gt;&gt; index = defaultdict(IntervalTree)
&gt;&gt;&gt; for n, row in df2.iterrows():
...     index[row[0]].addi(row[1], row[2], row)
... 
&gt;&gt;&gt; 
&gt;&gt;&gt; # Query the index from the other dataframe to build a 3rd
&gt;&gt;&gt; #   dataframe representing the intersection
&gt;&gt;&gt; dict3 = { i: list() for i in range(6) }
&gt;&gt;&gt; for n, row in df1.iterrows():
...     overlapping = index[row[0]].overlap(row[1], row[2])
...     for itvl in overlapping:
...         itsct_start = max(row[1], itvl.begin)
...         itsct_end = min(row[2], itvl.end)
...         dict3[0].append(row[0])
...         dict3[1].append(itsct_start)
...         dict3[2].append(itsct_end)
...         dict3[3].append(row[3])
...         dict3[4].append(row[4])
...         dict3[5].append(row[5])
... 
&gt;&gt;&gt; df3 = DataFrame(dict3)
&gt;&gt;&gt; df3
      0    1    2         3  4  5
0  chr1  155  200  feature2  0  +
1  chr1  155  200  feature3  0  -
2  chr1  900  901  feature4  0  +
&gt;&gt;&gt;
</code></pre>

CC BY-SA 4.0
2019-10-23T12:34:45.370Z
The Intervaltree package is great for some things, but if you want something > 100 x faster that works directly with pandas data please look into my https://github.com/biocore-ntnu/ncls library :)
The Unfun Cat
CC BY-SA 4.0
2019-07-19T12:42:10.350Z
2019-07-19T12:42:10.350Z
Daniel Standage
<p>As mentioned by OP, another option is to use <a href="https://daler.github.io/pybedtools/" rel="nofollow noreferrer">pybedtools</a>, which in my opinion is pretty convenient for people already familiar with BedTools.</p>
<p>Let's even say df1's format is slightly different than df2:</p>
<pre><code>import pandas as pd

dict1 = {0: ['chr1', 'chr1', 'chr1', 'chr1', 'chr2'], 1: [1, 100, 150, 900, 1], 
         2: [100, 200, 500, 950, 100], 
         3: ['feature1', 'feature2', 'feature3', 'feature4', 'feature4'], 
         4: [0, 0, 0, 0, 0], 5: ['+', '+', '-', '+', '+'], 
         6: [&quot;remember&quot;, &quot;the&quot;, &quot;5th&quot;, &quot;of&quot;, &quot;november&quot;]}

df1 = pd.DataFrame(dict1)

print(df1)

      0    1    2         3  4  5         6
0  chr1    1  100  feature1  0  +  remember
1  chr1  100  200  feature2  0  +       the
2  chr1  150  500  feature3  0  -       5th
3  chr1  900  950  feature4  0  +        of
4  chr2    1  100  feature4  0  +  November

dict2 = {0: ['chr1', 'chr1'], 1: [155, 800], 2: [200, 901], 
         3: ['feature5', 'feature6'], 4: [0, 0], 5: ['-', '+']}

df2 = pd.DataFrame(dict2)

print(df2)

      0    1    2         3  4  5
0  chr1  155  200  feature5  0  -
1  chr1  800  901  feature6  0  +
</code></pre>
<p>Using pybedtools we get:</p>
<pre><code>from pybedtools import BedTool

bed1 = BedTool.from_dataframe(df1)
bed2 = BedTool.from_dataframe(df2)

intersected_bed = bed1.intersect(bed2)

print(intersected_bed)

chr1    155 200 feature2    0   +   the
chr1    155 200 feature3    0   -   5th
chr1    900 901 feature4    0   +   of
</code></pre>
<p>We can then keep using the intersected BedTool object or convert it back into a DataFrame:</p>
<pre><code># create a pandas.DataFrame, passing args and kwargs to pandas.read_table
intersected_df = intersected_bed.to_dataframe(header=None, 
                                              names=[&quot;chrom&quot;, &quot;start&quot;, &quot;end&quot;, &quot;name&quot;, 
                                                     &quot;score&quot;, &quot;strand&quot;, &quot;whatever&quot;])

print(intersected_df)

  chrom  start  end      name  score strand whatever
0  chr1    155  200  feature2      0      +      the
1  chr1    155  200  feature3      0      -      5th
2  chr1    900  901  feature4      0      +       of
</code></pre>

CC BY-SA 4.0
2021-01-11T04:34:26.240Z
Thanks for the answer! It's been awhile since I've tried `pybedtools`. Isn't it heavy with respect to RAM?
EB2127
CC BY-SA 4.0
2021-01-12T09:19:06.910Z
I'm definitely not an expert at this, so please correct me if I'm mistaken, but according to this [performance test](https://github.com/biocore-ntnu/pyranges#performance) it seems that while their memory usage is pretty much the same, running time is where `pyranges` beats `pybedtools`.
@TheUnfunCat
Kapara newbie
CC BY-SA 4.0
2021-01-09T21:21:33.833Z
2021-01-09T21:21:33.833Z
Kapara newbie
<p>I implemented pandas "Intervals" and  ... it should be a few lines, clearly there are limitations. For non-overlapping data it is very cool however. It will work for overlapping data, BUT if the data you are using as the interval data is overlapping, it falls over. It could work if an independent (non-overlapping) interval was constructed.</p>

<p>Anyway, the point of the excercise was to keep it all in pandas,  "start_stop" should not be needed. This can't be done via Intervals, you've got to drop out, and that will slow it down and harder to parse. </p>

<p>The query contig <code>df3.iloc[1,]</code>, is still a bug (hence not printed), should be [x,] where x is the value_counts hit, but this is just a pilot. </p>

<p>The script would be fine if it the df was wrangled using <code>groupby</code> rather than <code>value_counts</code> because <code>value_counts</code> trashes important data, which is what I'm trying to recover via <code>df3.iloc[1,]</code>. I like pandas elegant solutions, but you know another day another dollar. </p>

<p>Note: I changed 200 to 199 otherwise there's too much overlap.</p>

<p><strong>Overlap Output</strong> (not labeled very well)</p>

<pre><code>                 start  stop
index                      
(1.0, 100.0]        0     0
(100.0, 199.0]      1     0
(150.0, 500.0]      1     1
(900.0, 950.0]      0     1
</code></pre>

<hr>

<pre><code>import pandas as pd

def tableit (dict):
    df = pd.DataFrame(dict)
    table = pd.pivot_table(df, values=['start', 'stop'], columns=['chromosome'], index=['feature', 'misc', 'misc2'], fill_value = '-')
    return table

def start_stop(point, table2, bin):
    df3 = pd.cut(table2[point,'chr1'], bin[0]).value_counts()
    df3 = df3.to_frame(point).reset_index()
    for x in bin:
            if not (x == bin[0]):
                df3_tmp = pd.cut(table2[point,'chr1'], x).value_counts()
                df3_tmp = df3_tmp.to_frame(point).reset_index()
                df3 = pd.concat([df3, df3_tmp]                                  
            else:
                continue
    df3.set_index('index', inplace=True)
    return df3, df3.iloc[1,] # [1,] is a bug and the query locus needs correctly reading from df3_tmp

def main():
    dict1 = {'chromosome':['chr1','chr1','chr1','chr1','chr2'], 'start':[1, 100, 150, 900, 1], 'stop':[100, 199, 500, 950, 100], 'feature':['feature1', 'feature2', 'feature3', 'feature4', 'feature4'], 'misc':[0, 0, 0, 0, 0], 'misc2':['+','+','-','+','+']}
    dict2 = {'chromosome':['chr1','chr1'], 'start':[155, 800], 'stop':[200, 901], 'feature':['feature5', 'feature6'], 'misc':[0, 0], 'misc2':['-','+']}

    table1 = tableit(dict1)
    table2 = tableit(dict2)

    bin = [[i, j] for i, j in zip(table1['start', 'chr1'], table1['stop', 'chr1'])]

    df_start, locus = start_stop('start', table2, bin)
# locus.name gets to the data
    df_stop, _ = start_stop('stop', table2, bin)
    df_merge = pd.merge(df_start, df_stop, on="index", how='outer')
    print (df_merge)

if __name__ == "__main__":
    main()
</code></pre>

<hr>

<p>The following would have been better,</p>

<pre><code>table_join = pd.concat([table1, table2]).fillna('-')
</code></pre>

<p>and then sort them.. perhaps including a new column to specify the original dataframe and screen for the overlap with table_join.iloc[i, column] - table_join.iloc[1-i, other_column].</p>

<p>The key step in all approaches is to convert from "long format" to "wide format" and thats the most important thing I've done in all code and makes pandas work.</p>

<p><strong>Output Table_join</strong></p>

<pre><code>                     start        stop     
chromosome            chr1 chr2   chr1 chr2
feature  misc misc2                        
feature1 0    +        1.0    -  100.0    -
feature2 0    +      100.0    -  199.0    -
feature3 0    -      150.0    -  500.0    -
feature4 0    +      900.0    1  950.0  100
feature5 0    -      155.0    -  200.0    -
feature6 0    +      800.0    -  901.0    -
</code></pre>

CC BY-SA 4.0
2019-07-22T11:19:44.303Z
At some point in the future I'll finish the script (its easier than starting over in this instance). The issue is parsing "pd.cut(table2[point,'chr1'], x)", assigning a forth index, which is unique to all rows, and then reading the output against the new index of the original table2 dataframe to obtain the dessired result. The +1 nucleotide issue is a pain, but anyway. Its only approx. 4 lines of code.
M__
CC BY-SA 4.0
2019-07-19T08:47:29.890Z
2019-07-21T11:24:12.680Z
2019-07-21T11:24:12.680Z
M__
<p>Your setup:</p>

<pre><code>import pandas as pd

dict1 = {0:['chr1','chr1','chr1','chr1','chr2'], 
    1:[1, 100, 150, 900, 1], 2:[100, 200, 500, 950, 100], 
    3:['feature1', 'feature2', 'feature3', 'feature4', 'feature4'], 
    4:[0, 0, 0, 0, 0], 5:['+','+','-','+','+']}

df1 = pd.DataFrame(dict1)

print(df1)

##       0    1    2         3  4  5
## 0  chr1    1  100  feature1  0  +
## 1  chr1  100  200  feature2  0  +
## 2  chr1  150  500  feature3  0  -
## 3  chr1  900  950  feature4  0  +
## 4  chr2    1  100  feature4  0  +

dict2 = {0:['chr1','chr1'], 1:[155, 800], 2:[200, 901], 
    3:['feature5', 'feature6'], 4:[0, 0], 5:['-','+']}

df2 = pd.DataFrame(dict2)
</code></pre>

<p>Now use pyranges (<a href="https://github.com/biocore-ntnu/pyranges" rel="noreferrer">https://github.com/biocore-ntnu/pyranges</a>):</p>

<pre><code># pip install pyranges
# conda install -c bioconda pyranges

import pyranges as pr
cols = "Chromosome Start End Name Score Strand".split()
# Add column names to the dfs so PyRanges knows which 
# represent Chromosomes, Starts, Ends and optionally Strand

df1.columns = cols
df2.columns = cols

# create PyRanges-objects from the dfs
gr1, gr2 = pr.PyRanges(df1), pr.PyRanges(df2)

# intersect the two
gr = gr1.intersect(gr2)

# get the result as a   df
df = gr.df
</code></pre>

<p>Results:</p>

<pre><code>print(gr)
# +--------------+-----------+-----------+------------+-----------+--------------+
# | Chromosome   |     Start |       End | Name       |     Score | Strand       |
# | (category)   |   (int32) |   (int32) | (object)   |   (int64) | (category)   |
# |--------------+-----------+-----------+------------+-----------+--------------|
# | chr1         |       155 |       200 | feature2   |         0 | +            |
# | chr1         |       900 |       901 | feature4   |         0 | +            |
# | chr1         |       155 |       200 | feature3   |         0 | -            |
# +--------------+-----------+-----------+------------+-----------+--------------+
# Stranded PyRanges object has 3 rows and 6 columns from 1 chromosomes.
# For printing, the PyRanges was sorted on Chromosome and Strand.
print(df)
#   Chromosome  Start  End      Name  Score Strand
# 0       chr1    155  200  feature2      0      +
# 1       chr1    900  901  feature4      0      +
# 2       chr1    155  200  feature3      0      -
</code></pre>

<p>See the docs for more info: <a href="https://biocore-ntnu.github.io/pyranges/" rel="noreferrer">https://biocore-ntnu.github.io/pyranges/</a></p>

CC BY-SA 4.0
2019-10-23T13:33:19.540Z
Hi @TheUnfunCat , looks very promising. I haven't try out the full package. I look forward to reading the publication. In your example, ever thought of including what are the intersecting features from gr2? In this case should be feature5, feature6, feature5 ..
StupidWolf
CC BY-SA 4.0
2019-10-23T19:19:53.767Z
Thanks for this answer! I feel this is a really useful contribution, so I marked it as correct---naturally, others can ask as well. `pyranges` looks great! 

One request: to improve your answer, could you add comments? That would be helpful for new readers unfamiliar with the `pyranges` package. Thanks!
EB2127
CC BY-SA 4.0
2019-10-24T06:54:52.390Z
@StupidWolf Use `gr1.join(gr2)` for that :)
The Unfun Cat
CC BY-SA 4.0
2019-10-24T10:02:21.590Z
pyranges looks good, I see your benchmarks have it orders of magnitude faster than GenomicRanges in R.  I almost want to try python instead of using GenomicRanges in R, almost.
Jay Moore
CC BY-SA 4.0
2020-04-19T11:19:45.050Z
Agreed pyranges is a brillant solution by @TheUnfunCat
M__
CC BY-SA 4.0
2019-10-23T12:31:05.670Z
2019-10-24T07:00:45.290Z
2019-10-24T07:00:45.290Z
The Unfun Cat
<p>I have two pandas Dataframes, using python3.x:</p>

<pre><code>import pandas as pd

dict1 = {0:['chr1','chr1','chr1','chr1','chr2'], 
    1:[1, 100, 150, 900, 1], 2:[100, 200, 500, 950, 100], 
    3:['feature1', 'feature2', 'feature3', 'feature4', 'feature4'], 
    4:[0, 0, 0, 0, 0], 5:['+','+','-','+','+']}

df1 = pd.DataFrame(dict1)

print(df1)

##       0    1    2         3  4  5
## 0  chr1    1  100  feature1  0  +
## 1  chr1  100  200  feature2  0  +
## 2  chr1  150  500  feature3  0  -
## 3  chr1  900  950  feature4  0  +
## 4  chr2    1  100  feature4  0  +

dict2 = {0:['chr1','chr1'], 1:[155, 800], 2:[200, 901], 
    3:['feature5', 'feature6'], 4:[0, 0], 5:['-','+']}

df2 = pd.DataFrame(dict2)
print(df2)
##       0    1    2         3  4  5
## 0  chr1  155  200  feature5  0  -
## 1  chr1  800  901  feature6  0  +
</code></pre>

<p>The columns to focus on in these dataframes are the first three columns: location, start, and end. Each start:end value represents a distance on location (e.g. <code>chr1</code>, <code>chr2</code>, <code>chr3</code>). </p>

<p>I would like to output the intersection of <code>df1</code> against <code>df2</code>. Here is the correct output:</p>

<pre><code>chr1    155 200 feature2    0   +
chr1    155 200 feature3    0   -
chr1    900 901 feature4    0   +
</code></pre>

<ul>
<li><p>What is the most efficient (in terms of runtime and RAM) to find the intersections using pandas?</p></li>
<li><p>There are python wrappers around BEDTools: <a href="https://daler.github.io/pybedtools/intersections.html" rel="nofollow noreferrer">https://daler.github.io/pybedtools/intersections.html</a></p></li>
</ul>

<p>I was hoping there was a pandas only solution...</p>

CC BY-SA 4.0
2019-10-23T12:24:02.270Z
https://github.com/biocore-ntnu/pyranges is likely what you want. I'll write an answer :)
The Unfun Cat
<python><bedtools><interval><pybedtools><pandas>
How to do `bedtools intersection` using pandas alone?
# How to do `bedtools intersection` using pandas alone?

(Asked by: username_0 on 2019-07-18)

I have two pandas Dataframes, using python3.x:
import pandas as pd

dict1 = {0:['chr1','chr1','chr1','chr1','chr2'], 
    1:[1, 100, 150, 900, 1], 2:[100, 200, 500, 950, 100], 
    3:['feature1', 'feature2', 'feature3', 'feature4', 'feature4'], 
    4:[0, 0, 0, 0, 0], 5:['+','+','-','+','+']}

df1 = pd.DataFrame(dict1)

print(df1)

##       0    1    2         3  4  5
## 0  chr1    1  100  feature1  0  +
## 1  chr1  100  200  feature2  0  +
## 2  chr1  150  500  feature3  0  -
## 3  chr1  900  950  feature4  0  +
## 4  chr2    1  100  feature4  0  +

dict2 = {0:['chr1','chr1'], 1:[155, 800], 2:[200, 901], 
    3:['feature5', 'feature6'], 4:[0, 0], 5:['-','+']}

df2 = pd.DataFrame(dict2)
print(df2)
##       0    1    2         3  4  5
## 0  chr1  155  200  feature5  0  -
## 1  chr1  800  901  feature6  0  +

The columns to focus on in these dataframes are the first three columns: location, start, and end. Each start:end value represents a distance on location (e.g. chr1, chr2, chr3). 
I would like to output the intersection of df1 against df2. Here is the correct output:
chr1    155 200 feature2    0   +
chr1    155 200 feature3    0   -
chr1    900 901 feature4    0   +


What is the most efficient (in terms of runtime and RAM) to find the intersections using pandas?
There are python wrappers around BEDTools: https://daler.github.io/pybedtools/intersections.html

I was hoping there was a pandas only solution...

**Tags:** python, bedtools, interval, pybedtools, pandas
> **Comments:**
> **username_1**: https://github.com/biocore-ntnu/pyranges is likely what you want. I'll write an answer :)  

---

## 4 answers

### Answer by username_1 on 2019-10-23. Score: 9 | Accepted answer

Your setup:
import pandas as pd

dict1 = {0:['chr1','chr1','chr1','chr1','chr2'], 
    1:[1, 100, 150, 900, 1], 2:[100, 200, 500, 950, 100], 
    3:['feature1', 'feature2', 'feature3', 'feature4', 'feature4'], 
    4:[0, 0, 0, 0, 0], 5:['+','+','-','+','+']}

df1 = pd.DataFrame(dict1)

print(df1)

##       0    1    2         3  4  5
## 0  chr1    1  100  feature1  0  +
## 1  chr1  100  200  feature2  0  +
## 2  chr1  150  500  feature3  0  -
## 3  chr1  900  950  feature4  0  +
## 4  chr2    1  100  feature4  0  +

dict2 = {0:['chr1','chr1'], 1:[155, 800], 2:[200, 901], 
    3:['feature5', 'feature6'], 4:[0, 0], 5:['-','+']}

df2 = pd.DataFrame(dict2)

Now use pyranges (https://github.com/biocore-ntnu/pyranges):
# pip install pyranges
# conda install -c bioconda pyranges

import pyranges as pr
cols = "Chromosome Start End Name Score Strand".split()
# Add column names to the dfs so PyRanges knows which 
# represent Chromosomes, Starts, Ends and optionally Strand

df1.columns = cols
df2.columns = cols

# create PyRanges-objects from the dfs
gr1, gr2 = pr.PyRanges(df1), pr.PyRanges(df2)

# intersect the two
gr = gr1.intersect(gr2)

# get the result as a   df
df = gr.df

Results:
print(gr)
# +--------------+-----------+-----------+------------+-----------+--------------+
# | Chromosome   |     Start |       End | Name       |     Score | Strand       |
# | (category)   |   (int32) |   (int32) | (object)   |   (int64) | (category)   |
# |--------------+-----------+-----------+------------+-----------+--------------|
# | chr1         |       155 |       200 | feature2   |         0 | +            |
# | chr1         |       900 |       901 | feature4   |         0 | +            |
# | chr1         |       155 |       200 | feature3   |         0 | -            |
# +--------------+-----------+-----------+------------+-----------+--------------+
# Stranded PyRanges object has 3 rows and 6 columns from 1 chromosomes.
# For printing, the PyRanges was sorted on Chromosome and Strand.
print(df)
#   Chromosome  Start  End      Name  Score Strand
# 0       chr1    155  200  feature2      0      +
# 1       chr1    900  901  feature4      0      +
# 2       chr1    155  200  feature3      0      -

See the docs for more info: https://biocore-ntnu.github.io/pyranges/

> **Comments:**
> **username_5**: Hi @TheUnfunCat , looks very promising. I haven't try out the full package. I look forward to reading the publication. In your example, ever thought of including what are the intersecting features from gr2? In this case should be feature5, feature6, feature5 ..  
> **username_0**: Thanks for this answer! I feel this is a really useful contribution, so I marked it as correct---naturally, others can ask as well. `pyranges` looks great!   One request: to improve your answer, could you add comments? That would be helpful for new readers unfamiliar with the `pyranges` package. Thanks!  
> **username_1**: @StupidWolf Use `gr1.join(gr2)` for that :)  
> **username_6**: pyranges looks good, I see your benchmarks have it orders of magnitude faster than GenomicRanges in R.  I almost want to try python instead of using GenomicRanges in R, almost.  
> **username_4**: Agreed pyranges is a brillant solution by @TheUnfunCat  

---

### Answer by username_2 on 2019-07-19. Score: 4

I don't think Pandas has this implemented functionality out-of-the-box. Even if it did, solutions not designed specifically for bioinformatics probably rarely handle intervals on different chromosomes correctly unless you split the intervals by chromosome first.
Pandas does handle intervals (see docs for the Interval and IntervalIndex classes), but I've never used these. I have used the excellent intervaltree package. Whatever approach and tools you decide to use, there's a good chance you'll have to write some code. Computing interval intersections with intervaltree would look something like this.
>>> from collections import defaultdict
>>> from intervaltree import IntervalTree
>>> from pandas import DataFrame
>>> 
>>> dict1 = {
...     0:['chr1','chr1','chr1','chr1','chr2'],
...     1:[1, 100, 150, 900, 1],
...     2:[100, 200, 500, 950, 100],
...     3:['feature1', 'feature2', 'feature3', 'feature4', 'feature4'],
...     4:[0, 0, 0, 0, 0],
...     5:['+','+','-','+','+']
... }
>>> dict2 = {
...     0:['chr1','chr1'],
...     1:[155, 800],
...     2:[200, 901],
...     3:['feature5', 'feature6'],
...     4:[0, 0],
...     5:['-','+']
... }
>>> df1 = DataFrame(dict1)
>>> df2 = DataFrame(dict2)
>>> 
>>> 
>>> # Construct an interval index from one of the dataframes,
>>> #   one tree per chromosome
>>> index = defaultdict(IntervalTree)
>>> for n, row in df2.iterrows():
...     index[row[0]].addi(row[1], row[2], row)
... 
>>> 
>>> # Query the index from the other dataframe to build a 3rd
>>> #   dataframe representing the intersection
>>> dict3 = { i: list() for i in range(6) }
>>> for n, row in df1.iterrows():
...     overlapping = index[row[0]].overlap(row[1], row[2])
...     for itvl in overlapping:
...         itsct_start = max(row[1], itvl.begin)
...         itsct_end = min(row[2], itvl.end)
...         dict3[0].append(row[0])
...         dict3[1].append(itsct_start)
...         dict3[2].append(itsct_end)
...         dict3[3].append(row[3])
...         dict3[4].append(row[4])
...         dict3[5].append(row[5])
... 
>>> df3 = DataFrame(dict3)
>>> df3
      0    1    2         3  4  5
0  chr1  155  200  feature2  0  +
1  chr1  155  200  feature3  0  -
2  chr1  900  901  feature4  0  +
>>>

> **Comments:**
> **username_1**: The Intervaltree package is great for some things, but if you want something > 100 x faster that works directly with pandas data please look into my https://github.com/biocore-ntnu/ncls library :)  

---

### Answer by username_3 on 2021-01-09. Score: 3

As mentioned by OP, another option is to use pybedtools, which in my opinion is pretty convenient for people already familiar with BedTools.
Let's even say df1's format is slightly different than df2:
import pandas as pd

dict1 = {0: ['chr1', 'chr1', 'chr1', 'chr1', 'chr2'], 1: [1, 100, 150, 900, 1], 
         2: [100, 200, 500, 950, 100], 
         3: ['feature1', 'feature2', 'feature3', 'feature4', 'feature4'], 
         4: [0, 0, 0, 0, 0], 5: ['+', '+', '-', '+', '+'], 
         6: ["remember", "the", "5th", "of", "november"]}

df1 = pd.DataFrame(dict1)

print(df1)

      0    1    2         3  4  5         6
0  chr1    1  100  feature1  0  +  remember
1  chr1  100  200  feature2  0  +       the
2  chr1  150  500  feature3  0  -       5th
3  chr1  900  950  feature4  0  +        of
4  chr2    1  100  feature4  0  +  November

dict2 = {0: ['chr1', 'chr1'], 1: [155, 800], 2: [200, 901], 
         3: ['feature5', 'feature6'], 4: [0, 0], 5: ['-', '+']}

df2 = pd.DataFrame(dict2)

print(df2)

      0    1    2         3  4  5
0  chr1  155  200  feature5  0  -
1  chr1  800  901  feature6  0  +

Using pybedtools we get:
from pybedtools import BedTool

bed1 = BedTool.from_dataframe(df1)
bed2 = BedTool.from_dataframe(df2)

intersected_bed = bed1.intersect(bed2)

print(intersected_bed)

chr1    155 200 feature2    0   +   the
chr1    155 200 feature3    0   -   5th
chr1    900 901 feature4    0   +   of

We can then keep using the intersected BedTool object or convert it back into a DataFrame:
# create a pandas.DataFrame, passing args and kwargs to pandas.read_table
intersected_df = intersected_bed.to_dataframe(header=None, 
                                              names=["chrom", "start", "end", "name", 
                                                     "score", "strand", "whatever"])

print(intersected_df)

  chrom  start  end      name  score strand whatever
0  chr1    155  200  feature2      0      +      the
1  chr1    155  200  feature3      0      -      5th
2  chr1    900  901  feature4      0      +       of

> **Comments:**
> **username_0**: Thanks for the answer! It's been awhile since I've tried `pybedtools`. Isn't it heavy with respect to RAM?  
> **username_3**: I'm definitely not an expert at this, so please correct me if I'm mistaken, but according to this [performance test](https://github.com/biocore-ntnu/pyranges#performance) it seems that while their memory usage is pretty much the same, running time is where `pyranges` beats `pybedtools`. @TheUnfunCat  

---

### Answer by username_4 on 2019-07-19. Score: 2

I implemented pandas "Intervals" and  ... it should be a few lines, clearly there are limitations. For non-overlapping data it is very cool however. It will work for overlapping data, BUT if the data you are using as the interval data is overlapping, it falls over. It could work if an independent (non-overlapping) interval was constructed.
Anyway, the point of the excercise was to keep it all in pandas,  "start_stop" should not be needed. This can't be done via Intervals, you've got to drop out, and that will slow it down and harder to parse. 
The query contig df3.iloc[1,], is still a bug (hence not printed), should be [x,] where x is the value_counts hit, but this is just a pilot. 
The script would be fine if it the df was wrangled using groupby rather than value_counts because value_counts trashes important data, which is what I'm trying to recover via df3.iloc[1,]. I like pandas elegant solutions, but you know another day another dollar. 
Note: I changed 200 to 199 otherwise there's too much overlap.
Overlap Output (not labeled very well)
                 start  stop
index                      
(1.0, 100.0]        0     0
(100.0, 199.0]      1     0
(150.0, 500.0]      1     1
(900.0, 950.0]      0     1


import pandas as pd

def tableit (dict):
    df = pd.DataFrame(dict)
    table = pd.pivot_table(df, values=['start', 'stop'], columns=['chromosome'], index=['feature', 'misc', 'misc2'], fill_value = '-')
    return table

def start_stop(point, table2, bin):
    df3 = pd.cut(table2[point,'chr1'], bin[0]).value_counts()
    df3 = df3.to_frame(point).reset_index()
    for x in bin:
            if not (x == bin[0]):
                df3_tmp = pd.cut(table2[point,'chr1'], x).value_counts()
                df3_tmp = df3_tmp.to_frame(point).reset_index()
                df3 = pd.concat([df3, df3_tmp]                                  
            else:
                continue
    df3.set_index('index', inplace=True)
    return df3, df3.iloc[1,] # [1,] is a bug and the query locus needs correctly reading from df3_tmp

def main():
    dict1 = {'chromosome':['chr1','chr1','chr1','chr1','chr2'], 'start':[1, 100, 150, 900, 1], 'stop':[100, 199, 500, 950, 100], 'feature':['feature1', 'feature2', 'feature3', 'feature4', 'feature4'], 'misc':[0, 0, 0, 0, 0], 'misc2':['+','+','-','+','+']}
    dict2 = {'chromosome':['chr1','chr1'], 'start':[155, 800], 'stop':[200, 901], 'feature':['feature5', 'feature6'], 'misc':[0, 0], 'misc2':['-','+']}

    table1 = tableit(dict1)
    table2 = tableit(dict2)

    bin = [[i, j] for i, j in zip(table1['start', 'chr1'], table1['stop', 'chr1'])]

    df_start, locus = start_stop('start', table2, bin)
# locus.name gets to the data
    df_stop, _ = start_stop('stop', table2, bin)
    df_merge = pd.merge(df_start, df_stop, on="index", how='outer')
    print (df_merge)

if __name__ == "__main__":
    main()


The following would have been better,
table_join = pd.concat([table1, table2]).fillna('-')

and then sort them.. perhaps including a new column to specify the original dataframe and screen for the overlap with table_join.iloc[i, column] - table_join.iloc[1-i, other_column].
The key step in all approaches is to convert from "long format" to "wide format" and thats the most important thing I've done in all code and makes pandas work.
Output Table_join
                     start        stop     
chromosome            chr1 chr2   chr1 chr2
feature  misc misc2                        
feature1 0    +        1.0    -  100.0    -
feature2 0    +      100.0    -  199.0    -
feature3 0    -      150.0    -  500.0    -
feature4 0    +      900.0    1  950.0  100
feature5 0    -      155.0    -  200.0    -
feature6 0    +      800.0    -  901.0    -

> **Comments:**
> **username_4**: At some point in the future I'll finish the script (its easier than starting over in this instance). The issue is parsing "pd.cut(table2[point,'chr1'], x)", assigning a forth index, which is unique to all rows, and then reading the output against the new index of the original table2 dataframe to obtain the dessired result. The +1 nucleotide issue is a pain, but anyway. Its only approx. 4 lines of code.  

---

