Answers.Body: <pre><code># pip install pyranges or conda install -c bioconda pyranges
  
  import pyranges as pr
  
  g1 = pr.from_string(&quot;&quot;&quot;Chromosome Start End
  chr1   222 223
  chr1   223 224
  chr1   233 234
  chr1   235 236
  chr1   2237 238
  chr1  2123 2124
  chr2   244 245&quot;&quot;&quot;)
  
  g2 = pr.from_string(&quot;&quot;&quot;Chromosome Start End
  chr1    221  223
  chr1    230  240
  chr2    0 1000&quot;&quot;&quot;)
  
  r = g2.count_overlaps(g1)
  r
  # +--------------+-----------+-----------+------------------+
  # | Chromosome   |     Start |       End |   NumberOverlaps |
  # | (category)   |   (int32) |   (int32) |          (int64) |
  # |--------------+-----------+-----------+------------------|
  # | chr1         |       221 |       223 |                1 |
  # | chr1         |       230 |       240 |                2 |
  # | chr2         |         0 |      1000 |                1 |
  # +--------------+-----------+-----------+------------------+
  # Unstranded PyRanges object has 3 rows and 4 columns from 2 chromosomes.
  # For printing, the PyRanges was sorted on Chromosome.
  
  r.df
  # 0       chr1    221   223               1
  # 1       chr1    230   240               2
  # 2       chr2      0  1000               1
  </code></pre>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-02-24T21:42:23.503Z
Answers.Text: How did I not know about pyranges!? Thanks!
Answers.UserDisplayName: Liam McIntyre
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-02-24T20:43:36.393Z
Answers.LastActivityDate: 2021-02-24T20:43:36.393Z
Answers.UserDisplayName: The Unfun Cat
Body: <p>can anyone help me improve this pandas code?</p>
  <pre><code>import pandas as pd
  
  df = pd.DataFrame(
      [
          [
              'chr1', 222
          ],
          [
              'chr1', 233
          ],
          [
              'chr1', 2123
          ],
          [
              'chr2', 244
          ]
      ], columns = ['chrom', 'pos']
  )
  
  df2 = pd.DataFrame(
      [
          [
              'chr1', 221, 223
          ],
          [
              'chr1', 230, 240
          ],
      ], columns = ['chrom', 'start', 'end']
  )
  </code></pre>
  <p>Gives me 2 dfs with genomic coordinates. The first one is an exact position:</p>
  <pre><code>    chrom   pos
  0   chr1    222
  1   chr1    233
  2   chr1    2123
  3   chr2    244
  </code></pre>
  <p>and the second is a range:</p>
  <pre><code>    chrom   start   end
  0   chr1    221 223
  1   chr1    230 240
  </code></pre>
  <p>I need to find the count of exact coordinates that are in one of the ranges (in the same chrom)</p>
  <p>This works but is slow:</p>
  <pre><code>c=0
  for chrom, data in df.groupby('chrom'):
      tmp = df2.query(f'chrom == &quot;{chrom}&quot;')
      for p in data.pos:
          for s, e in zip(tmp.start, tmp.end):
              if s &lt; p &lt; e:
                  c+=1
  </code></pre>
  <p>Then c = 2</p>
  <p>I think I can use agg to do this without iteration (and hopefully faster) but I can't get it working. Can anyone show me how?</p>
  <p>PS I am also asking this on stackoverflow.</p>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2021-02-12T09:36:53.800Z
Comments.Text: https://codereview.stackexchange.com/ is probably the best place to ask questions about optimising code.
Comments.UserDisplayName: user438383
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2021-02-12T10:35:12.117Z
Comments.Text: That actually a really good question. A bioinformatics question. It's self contained and we'll explained. If U don't know just don't answer. No need for negatively.
Comments.UserDisplayName: Liam McIntyre
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2021-02-12T11:24:43.677Z
Comments.Text: I'm actually trying to help you, since code review (hint) is a much better place to get your code reviewed than either here or SO. You seem to be new here - so different sites have different functions. It's not about negativity / whether or not I can answer the question, it's about the correct questions are on the correct sites.
Comments.UserDisplayName: user438383
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2021-02-12T11:26:07.307Z
Comments.Text: Ok fair enough.
Comments.UserDisplayName: Liam McIntyre
Tags: <python><pandas><chromosomes>
Title: How can I use pandas agg here to avoid iteration?
ThreadText: # How can I use pandas agg here to avoid iteration?
  
  (Asked by: username_0 on 2021-02-12)
  
  can anyone help me improve this pandas code?
  
  ```
  import pandas as pd
  
  df = pd.DataFrame(
      [
          [
              'chr1', 222
          ],
          [
              'chr1', 233
          ],
          [
              'chr1', 2123
          ],
          [
              'chr2', 244
          ]
      ], columns = ['chrom', 'pos']
  )
  
  df2 = pd.DataFrame(
      [
          [
              'chr1', 221, 223
          ],
          [
              'chr1', 230, 240
          ],
      ], columns = ['chrom', 'start', 'end']
  )
  
  ```
  
  Gives me 2 dfs with genomic coordinates. The first one is an exact position:
  
  ```
      chrom   pos
  0   chr1    222
  1   chr1    233
  2   chr1    2123
  3   chr2    244
  
  ```
  
  and the second is a range:
  
  ```
      chrom   start   end
  0   chr1    221 223
  1   chr1    230 240
  
  ```
  
  I need to find the count of exact coordinates that are in one of the ranges (in the same chrom)
  
  This works but is slow:
  
  ```
  c=0
  for chrom, data in df.groupby('chrom'):
      tmp = df2.query(f'chrom == "{chrom}"')
      for p in data.pos:
          for s, e in zip(tmp.start, tmp.end):
              if s < p < e:
                  c+=1
  
  ```
  
  Then c = 2
  
  I think I can use agg to do this without iteration (and hopefully faster) but I can't get it working. Can anyone show me how?
  
  PS I am also asking this on stackoverflow.
  
  **Tags:** python, pandas, chromosomes
  
  > **Comments:**
  > **username_1**: https://codereview.stackexchange.com/ is probably the best place to ask questions about optimising code.  
  > **username_0**: That actually a really good question. A bioinformatics question. It's self contained and we'll explained. If U don't know just don't answer. No need for negatively.  
  > **username_1**: I'm actually trying to help you, since code review (hint) is a much better place to get your code reviewed than either here or SO. You seem to be new here - so different sites have different functions. It's not about negativity / whether or not I can answer the question, it's about the correct questions are on the correct sites.  
  > **username_0**: Ok fair enough.  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_2 on 2021-02-24. Score: 3 | Accepted answer
  
  ```
  # pip install pyranges or conda install -c bioconda pyranges
  
  import pyranges as pr
  
  g1 = pr.from_string("""Chromosome Start End
  chr1   222 223
  chr1   223 224
  chr1   233 234
  chr1   235 236
  chr1   2237 238
  chr1  2123 2124
  chr2   244 245""")
  
  g2 = pr.from_string("""Chromosome Start End
  chr1    221  223
  chr1    230  240
  chr2    0 1000""")
  
  r = g2.count_overlaps(g1)
  r
  # +--------------+-----------+-----------+------------------+
  # | Chromosome   |     Start |       End |   NumberOverlaps |
  # | (category)   |   (int32) |   (int32) |          (int64) |
  # |--------------+-----------+-----------+------------------|
  # | chr1         |       221 |       223 |                1 |
  # | chr1         |       230 |       240 |                2 |
  # | chr2         |         0 |      1000 |                1 |
  # +--------------+-----------+-----------+------------------+
  # Unstranded PyRanges object has 3 rows and 4 columns from 2 chromosomes.
  # For printing, the PyRanges was sorted on Chromosome.
  
  r.df
  # 0       chr1    221   223               1
  # 1       chr1    230   240               2
  # 2       chr2      0  1000               1
  
  ```
  
  > **Comments:**
  > **username_0**: How did I not know about pyranges!? Thanks!  
  
  ---
  
  