title: Merging two DataFrames in Pandas, based on conditions
body: <p>I have 2 DataFrames:</p>
  <pre><code>      siccd   date    retp  
  0     2892  31135 -0.036296   
  1     2892  31226  0.144768   
  2     2892  31320  0.063924   
  3     1650  31412 -0.009190   
  4     1299  31502  0.063326   
  </code></pre>
  <p>and</p>
  <pre><code>    start   end  ind indname
  0     100   999    1   Agric
  1    1000  1299    2   Mines
  2    1300  1399    3     Oil
  3    1400  1499    4   Stone
  4    1500  1799    5   Cnstr
  5    2000  2099    6    Food
  6    2100  2199    7   Smoke
  7    2200  2299    8   Txtls
  8    2300  2399    9   Apprl
  9    2400  2499   10    Wood
  10   2500  2599   11   Chair
  11   2600  2661   12   Paper
  12   2700  2799   13   Print
  13   2800  2899   14   Chems
  14   2900  2999   15   Ptrlm
  15   3000  3099   16   Rubbr
  16   3100  3199   17   Lethr
  17   3200  3299   18   Glass
  18   3300  3399   19  Metal 
  </code></pre>
  <p>The task is to take the <code>df1['siccd']</code> column, compare it to the <code>df2['start']</code> and <code>df2['end']</code> column. If (start &lt;= siccd &lt;= end), assign the ind and indname values of that respective index in the second DataFrame to the first DataFrame. The output would look something like:</p>
  <pre><code>      siccd   date    retp   ind  indname
  0     2892  31135 -0.036296  14   Chems
  1     2892  31226  0.144768  14   Chems
  2     2892  31320  0.063924  14   Chems
  3     1650  31412 -0.009190  5    Cnstr
  4     1299  31502  0.063326  2    Mines
  </code></pre>
  <p>I've tried doing this with crude nested for loops, and it provides me with the correct lists that I can append to the end of the DataFrame, however this is extremely inefficient and given the data set's length it is inadequate.</p>
  <pre><code>siccd_lst = list(tmp['siccd'])
  ind_lst = []
  indname_lst = []
  def categorize(siccd, df, index):
      if (siccd &gt;= df.iloc[index]['start']) and (siccd &lt;= df.iloc[index]['end']):
          ind_lst.append(df.iloc[index]['ind'])
          indname_lst.append(df.iloc[index]['indname'])
      else:
          pass
  
  for i in range(0, len(ff38.index)-1):
      [categorize(x, ff38, i) for x in siccd_lst]
  </code></pre>
  <p>I have also attempted to vectorize the problem, however, I could not figure out how to iterate through the entire df2 when &quot;searching&quot; for the correct ind and indname to assign to the first DataFrame.</p>