Mapping a dataframe based on the columns from other dataframe
<p>I have two data frames one looks like this,</p>

<pre><code>df1.head()
#CHR    Start   End Name
chr1    141474  173862  SAP
chr1    745489  753092  ARB
chr1    762988  794826  SAS
chr1    1634175 1669127 ETH
chr1    2281853 2284259 BRB
</code></pre>

<p>And second datafarme looks as follows,</p>

<pre><code>df2.head()
#chr    start   end
chr1    141477  173860
chr1    745500  753000
chr16   56228385    56229180
chr11   101785507   101786117
chr7    101961796   101962267
</code></pre>

<p>I am looking to map the first 3 columns from two data frame and create a new data frame, df3. ie if #chr from both df1 and df2 are equal then look for <code>df2.start &gt;= df1.start</code> and <code>df2.end &lt;= df1.end</code>,  if so print it out as the following, </p>

<pre><code> df3.head()
#chr    start   end Name
chr1    141477  173860  SAP
chr1    745500  753000  ARB
</code></pre>

<p>So far I tried to make it a function as following,</p>

<pre><code>def start_smaller_than_end(df1,df2):
    if df1.CHR == df2.CHR:
        df2.start &gt;= df1.Start
        df2.End &lt;= df2.End

    return df3
</code></pre>

<p>and when I run it I have the following error,</p>

<blockquote>
  <p>df3(df1, df2)
  name 'df3' is not defined</p>
</blockquote>

<p>Any suggestions and help are greatly appreciated. </p>

<p>I think you can use <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.merge.html" rel="nofollow"><code>merge</code></a> with <a href="http://pandas.pydata.org/pandas-docs/stable/indexing.html#boolean-indexing" rel="nofollow"><code>boolean indexing</code></a>:</p>

<pre><code>df = pd.merge(df1, df2, how='outer', left_on='#CHR', right_on='#chr')

df = df[(df.start &gt;= df.Start) &amp; (df.end &lt;= df.End)]
df = df[['#chr','start','end','Name']]
print (df)
   #chr   start     end Name
0  chr1  141477  173860  SAP
3  chr1  745500  753000  ARB
</code></pre>

<p>EDIT by comment:</p>

<p>Function <code>start_smaller_than_end</code>:</p>

<pre><code>def start_smaller_than_end(df1,df2):
    df = pd.merge(df1, df2, how='outer', left_on='#CHR', right_on='#chr')
    df = df[(df.start &gt;= df.Start) &amp; (df.end &lt;= df.End)]
    df = df[['#chr','start','end','Name']]
    return df

df3 = start_smaller_than_end(df1,df2)
print (df3)    
</code></pre>

python pandas numpy indexing merge