title: How to combine two pandas dataframes with a conditional?
body: <p>There are two pandas dataframes I have which I would like to combine with a rule.</p>
  
  <p>This is the first dataframe</p>
  
  <pre><code>import pandas as pd
  df1 = pd.Dataframe()
  
  df1 
  
  rank    begin    end     labels
  first   30953   31131    label1
  first   31293   31435    label2
  first   31436   31733    label4
  first   31734   31754    label1
  first   32841   33037    label3
  second  33048   33456    label4
  ....
  </code></pre>
  
  <p>The second dataframe is only two columns, <code>rank</code> and <code>start</code></p>
  
  <pre><code>df2
  
  rank    start 
  first   31333     
  first   31434     
  first   33039    
  first   33123     
  first   33125     
  </code></pre>
  
  <p>In the first dataframe <code>df1</code>, the data has a <code>begin</code> and <code>end</code>. I would like to check whether the integer for start in <code>df2</code> is within this range.</p>
  
  <p>Here is the end result it should look like:</p>
  
  <pre><code>result
  
  rank    start     labels
  first   31333     label2
  first   31434     label2
  first   33039     NaN
  first   33123     label4
  first   33125     label4
  </code></pre>
  
  <p>The <code>start==31333</code> is between the range <code>31293</code> to <code>31435</code> in <code>df1</code> with <code>label = label2</code>. The integer <code>31434</code> is also between the range <code>31293:31435</code>, so it also gets annotated with <code>label2</code>. The value <code>33039</code> is not between any interval in <code>df2</code>, so it gets a <code>NaN</code> value.</p>
  
  <p>The rule by which these dataframes are combined is this:</p>
  
  <pre><code>(df2.start &gt;= df1.begin) &amp; (df2.start &lt;= df1.end)
  </code></pre>
  
  <p>But also, each row must match the same rank value, e.g. each row must match the string first or second for this conditional.</p>
  
  <p>Here is the code I was using to combine these two dataframes, but it doesn't scale very well at all:</p>
  
  <pre><code>from numpy import nan
  
  def between_range(row):
      subset = df1.loc[(row["rank"] == df1.rank) &amp; (row.start &gt;= repeats.start) &amp; (row.start &lt;= repeats.end), :]
      if subset.empty:
          return np.nan
      return subset.labels
  </code></pre>
  
  <p>Is there another way to do this with merging (maybe on rank)? Any other pandas-based solution?</p>