Pandas check if row exist in another dataframe and append index
<p>I'm having one problem to iterate over my dataframe. The way I'm doing is taking a loooong time and I don't have that many rows (I have like 300k rows)</p>

<p>What am I trying to do? </p>

<ol>
<li><p>Check if one DF (A) contains the value of two columns of the other DF (B). You can think this as a multiple key field</p></li>
<li><p>If True, get the index of DF.B and assign to one column of DF.A</p></li>
<li><p>If False, two steps:</p>

<p>a. append to DF.B the two columns not found</p>

<p>b. assign the new ID to DF.A (I couldn't do this one)</p></li>
</ol>

<p>This is my code, where:</p>

<ol>
<li><p>df is DF.A and df_id is DF.B:</p></li>
<li><p>SampleID and ParentID are the two columns I am interested to check if they exist in both dataframes</p></li>
<li><p>Real_ID is the column which I want to assign the id of DF.B (df_id)</p>

<pre><code>for index, row in df.iterrows():
    #check if columns exist in the other dataframe
    real_id = df_id[(df_id['SampleID'] == row['SampleID']) &amp; (df_id['ParentID'] == row['ParentID'])]

    if real_id.empty:
        #row does not exist, append to df_id
        df_id = df_id.append(row[['SampleID','ParentID']])
    else:
        #row exists, assign id of df_id to df
        row['Real_ID'] = real_id.index
</code></pre></li>
</ol>

<p>EXAMPLE:</p>

<p>DF.A (df)</p>

<pre><code>   Real_ID   SampleID   ParentID  Something AnotherThing
0             20          21          a          b      
1             10          11          a          b      
2             40          51          a          b       
</code></pre>

<p>DF.B (df_id)</p>

<pre><code>   SampleID   ParentID  
0    10          11         
1    20          21     
</code></pre>

<p><strong>Result</strong>:</p>

<pre><code>   Real_ID   SampleID   ParentID  Something AnotherThing
0      1      10          11          a          b      
1      0      20          21          a          b      
2      2      40          51          a          b      


   SampleID   ParentID  
0    20          21         
1    10          11    
2    40          51
</code></pre>

<p>Again, this solution is very slow. I'm sure there is a better way to do this and that's why I'm asking here. Unfortunately this was what I got after some hours...</p>

<p>Thanks</p>

<p>you can do it this way:</p>

<p>Data (pay attention at the index in the <code>B</code> DF):</p>

<pre><code>In [276]: cols = ['SampleID', 'ParentID']

In [277]: A
Out[277]:
   Real_ID  SampleID  ParentID Something AnotherThing
0      NaN        10        11         a            b
1      NaN        20        21         a            b
2      NaN        40        51         a            b

In [278]: B
Out[278]:
   SampleID  ParentID
3        10        11
5        20        21
</code></pre>

<p><strong>Solution:</strong></p>

<pre><code>In [279]: merged = pd.merge(A[cols], B, on=cols, how='outer', indicator=True)

In [280]: merged
Out[280]:
   SampleID  ParentID     _merge
0        10        11       both
1        20        21       both
2        40        51  left_only


In [281]: B = pd.concat([B, merged.ix[merged._merge=='left_only', cols]])

In [282]: B
Out[282]:
   SampleID  ParentID
3        10        11
5        20        21
2        40        51

In [285]: A['Real_ID'] = pd.merge(A[cols], B.reset_index(), on=cols)['index']

In [286]: A
Out[286]:
   Real_ID  SampleID  ParentID Something AnotherThing
0        3        10        11         a            b
1        5        20        21         a            b
2        2        40        51         a            b
</code></pre>

python pandas