Pandas dataframe comparison hangs without error message
<p>This is my first pandas attempt, so I was wondering what is the problem. I am trying to compare two dataframe of about 30.000 rows each. My first intuition led me to iterate both dataframes, so for every entry in the df1, we iterate all the rows in the df2 to see if itÂ´s there. </p>

<p>Maybe is not necessary at all and there are easier alternatives. Here is what I did. The problem is, it simply hangs without outputting any error message, but I cannot identify what is making it hang...  </p>

<pre><code>import pandas as pd


dfOld = pd.read_excel("oldfile.xlsx", header=0)
dfNew = pd.read_excel("newfile.xlsx", header=0)
columns = ["NAME","ADDRESS","STATUS","DATE"]
result = pd.DataFrame(columns=columns)

for index, rowOld in dfOld.iterrows():

    for index, rowNew in dfNew.iterrows():

        if rowOld.all() != rowNew.all():
            result.loc[len(result)] = rowOld.all()


writer = pd.ExcelWriter('Deletions.xlsx', engine='xlsxwriter')
result.to_excel(writer, sheet_name='Deleted')
writer.save()
</code></pre>

<p>Sample data for each dataframe:</p>

<pre><code>$1 &amp; UP STORE CORP.142A | N FRANKLIN ST | 409 408 | 31/07/2014
$1 store | 110 n martin ave | 408 | 07/01/2015
0713, LLC | 1412 N. County Road West | 405 408 413 | 16/07/2015
1 2 3 MONEY EXCHANGE LLC | 588 N MAIN ST | 405 409 408 | 22/05/2015


$1 store premium | 110 n martin ave | 408 | 07/01/2015
0713, LLC | 1412 N. County Road West | 405 408 413 | 16/07/2015
1 2 3 MONEY EXCHANGE LLC | 588 N MAIN ST | 405 409 408 | 22/05/2015
1145 Parsons Inc | 1145 Parsons Ave | 405 408 | 19/11/2013
</code></pre>

<p>The desired output is that the dataframe <code>results</code> gets populated with the rows from <code>dfOld</code> that do not exist in <code>dfNew</code>. So, the new <code>results</code> dataframe would be comprised of:</p>

<pre><code>$1 &amp; UP STORE CORP.142A | N FRANKLIN ST | 409 408 | 31/07/2014
$1 store | 110 n martin ave | 408 | 07/01/2015
</code></pre>

<p>The problem is that it doesn't work with a large amount (30.000 entries per dataframe), so even if it can work with a smaller sample, I wonder if this is the way to proceed with that many entries. </p>

<p>You can use <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.merge.html" rel="nofollow"><code>merge</code></a> with parameter <code>indicator=True</code> and then filter by <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(dfOld, dfNew, how='outer', indicator=True)
print (df)
                       NAME                   ADDRESS       STATUS  \
0   $1 &amp; UP STORE CORP.142A             N FRANKLIN ST      409 408   
1                  $1 store          110 n martin ave          408   
2                 0713, LLC  1412 N. County Road West  405 408 413   
3  1 2 3 MONEY EXCHANGE LLC             588 N MAIN ST  405 409 408   
4          $1 store premium          110 n martin ave          408   
5          1145 Parsons Inc          1145 Parsons Ave      405 408   

         DATE      _merge  
0  31/07/2014   left_only  
1  07/01/2015   left_only  
2  16/07/2015        both  
3  22/05/2015        both  
4  07/01/2015  right_only  
5  19/11/2013  right_only 

print (df[df._merge == 'left_only'])
                      NAME           ADDRESS   STATUS        DATE     _merge
0  $1 &amp; UP STORE CORP.142A     N FRANKLIN ST  409 408  31/07/2014  left_only
1                 $1 store  110 n martin ave      408  07/01/2015  left_only
</code></pre>

<p>Last delete helper column <code>_merge</code>:</p>

<pre><code>print (df[df._merge == 'left_only'].drop('_merge', axis=1))
                      NAME           ADDRESS   STATUS        DATE
0  $1 &amp; UP STORE CORP.142A     N FRANKLIN ST  409 408  31/07/2014
1                 $1 store  110 n martin ave      408  07/01/2015
</code></pre>

python pandas