Answers.Body: <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>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-10-23T12:34:45.370Z
Answers.Text: 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 :)
Answers.UserDisplayName: The Unfun Cat
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-07-19T12:42:10.350Z
Answers.LastActivityDate: 2019-07-19T12:42:10.350Z
Answers.UserDisplayName: Daniel Standage
Answers.Body: <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>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-01-11T04:34:26.240Z
Answers.Text: Thanks for the answer! It's been awhile since I've tried `pybedtools`. Isn't it heavy with respect to RAM?
Answers.UserDisplayName: EB2127
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-01-12T09:19:06.910Z
Answers.Text: 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
Answers.UserDisplayName: Kapara newbie
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-01-09T21:21:33.833Z
Answers.LastActivityDate: 2021-01-09T21:21:33.833Z
Answers.UserDisplayName: Kapara newbie
Answers.Body: <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>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-07-22T11:19:44.303Z
Answers.Text: 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.
Answers.UserDisplayName: M__
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-07-19T08:47:29.890Z
Answers.LastActivityDate: 2019-07-21T11:24:12.680Z
Answers.LastEditDate: 2019-07-21T11:24:12.680Z
Answers.UserDisplayName: M__
Answers.Body: <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>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-10-23T13:33:19.540Z
Answers.Text: 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 ..
Answers.UserDisplayName: StupidWolf
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-10-23T19:19:53.767Z
Answers.Text: 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!
Answers.UserDisplayName: EB2127
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-10-24T06:54:52.390Z
Answers.Text: @StupidWolf Use `gr1.join(gr2)` for that :)
Answers.UserDisplayName: The Unfun Cat
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-10-24T10:02:21.590Z
Answers.Text: 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.
Answers.UserDisplayName: Jay Moore
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-04-19T11:19:45.050Z
Answers.Text: Agreed pyranges is a brillant solution by @TheUnfunCat
Answers.UserDisplayName: M__
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-10-23T12:31:05.670Z
Answers.LastActivityDate: 2019-10-24T07:00:45.290Z
Answers.LastEditDate: 2019-10-24T07:00:45.290Z
Answers.UserDisplayName: The Unfun Cat
Body: <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>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2019-10-23T12:24:02.270Z
Comments.Text: https://github.com/biocore-ntnu/pyranges is likely what you want. I'll write an answer :)
Comments.UserDisplayName: The Unfun Cat
Tags: <python><bedtools><interval><pybedtools><pandas>
Title: How to do `bedtools intersection` using pandas alone?
ThreadText: # 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 @username_1 , 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**: @username_5 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 @username_1  
  
  ---
  
  ### 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](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.Interval.html) and [IntervalIndex](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.IntervalIndex.html) classes), but I've never used these. I *have* used the excellent [intervaltree](https://github.com/chaimleib/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.
  
  ```py
  >>> 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](https://daler.github.io/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`. @username_1  
  
  ---
  
  ### 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.  
  
  ---
  
  