Comparing two DataFrames by one column with a return of three different outputs with Panadas
<p>I am beginner in Python and coding. I need help comparing two dataframes of different lengths and with different column labels except one. The column that is the same between the two datasets is the column I want to compare the dataframe by. My data looks like this:</p>

<pre><code>    df:  'fruits'  'trees'      'sports'    'countries'  

          bananas   mongolia     basketball    Spain
          grapes    Oak          rugby         Thailand
          oranges   Osage Orange baseball      Egypt
          apples    Maple        golf          Chile

    df2: 'cars'  'flowers'     'countries'    'vegetables'

          Audi    Rose          Spain           Carrots
          BMW     Tulip         Nigeria         Celery
          Honda   Dandelion     Egypt           Onion
</code></pre>

<p>I would to compare these two dataframes based on the column 'countries'and create three separate outputs each in their own dataframe. I have been using Pandas and have used pd.concat to combine df1 and df2 into one. I would also like to keep the rows of the rest of the dataframe even though they don't match. </p>

<p>Here are my desired outputs:</p>

<p>Output# 1: Values in df NOT in df2:</p>

<pre><code>    d3:  'fruits'  'trees'      'sports'    'countries'  

          grapes    Oak            rugby         Thailand
          apples    Maple          golf          Chile
</code></pre>

<p>Output# 2: Values in df2 NOT in df</p>

<pre><code>        df4: 'cars'  'flowers'   'countries'    'vegetables'

              BMW     Tulip       Nigeria         Celery
</code></pre>

<p>Output# 3: Values in both df AND df2 (with the columns from the different dataframes combined.) </p>

<pre><code>df5: 'fruits'  'trees' 'sports'  'cars' 'flowers' 'countries' 'vegetables'  

  bananas   mongolia  basketball   Audi    Rose      Spain    Carrots 
Oranges  Osage Orange baseball    Honda   Dandelion  Egypt    Onion
</code></pre>

<p>Hope this all makes sense. I have tried so many different things (isin, DataFrame.diff and .difference, df-df2, numpy arrays, etc.) I have looked all over and I can't find exactly what I'm looking for. Any help would be greatly appreciated!
Thank you! </p>

<p><strong><em>Setup Reference</em></strong></p>

<pre><code>from StringIO import StringIO
import pandas as pd

txt1 = """fruits,trees,sports,countries
bananas,mongolia,basketball,Spain
grapes,Oak,rugby,Thailand
oranges,Osage,Orange baseball,Egypt
apples,Maple,golf,Chile"""

txt2 = """cars,flowers,countries,vegetables
Audi,Rose,Spain,Carrots
BMW,Tulip,Nigeria,Celery
Honda,Dandelion,Egypt,Onion"""

df = pd.read_csv(StringIO(txt1))

df2 = pd.read_csv(StringIO(txt2))
</code></pre>

<hr>

<h3>Solution</h3>

<pre><code>def outer_parts(df1, df2):
    df3 = df1.merge(df2, indicator=True, how='outer')
    return {n: g.drop('_merge', 1) for n, g in df3.groupby('_merge')}


dfs = outer_parts(df, df2)
</code></pre>

<hr>

<h3>Demonstration</h3>

<pre><code>dfs['both']
</code></pre>

<p><a href="http://i.stack.imgur.com/qsDiA.png" rel="nofollow"><img src="http://i.stack.imgur.com/qsDiA.png" alt="enter image description here"></a></p>

<pre><code>dfs['left_only']
</code></pre>

<p><a href="http://i.stack.imgur.com/Z7ccG.png" rel="nofollow"><img src="http://i.stack.imgur.com/Z7ccG.png" alt="enter image description here"></a></p>

<pre><code>dfs['right_only']
</code></pre>

<p><a href="http://i.stack.imgur.com/4Lle9.png" rel="nofollow"><img src="http://i.stack.imgur.com/4Lle9.png" alt="enter image description here"></a></p>

python pandas numpy dataframe