8
votes

I have 2 dataFrames and want to compare them and return rows from the first one (df1) that are not in the second one (df2). I found a way to compare them and return the differences, but can't figure out how to return only missing ones from df1.

import pandas as pd
from pandas import Series, DataFrame

df1 = pd.DataFrame( { 
"City" : ["Chicago", "San Franciso", "Boston"] , 
"State" : ["Illinois", "California", "Massachusett"] } )

df2 = pd.DataFrame( { 
"City" : ["Chicago",  "Mmmmiami", "Dallas" , "Omaha"] , 
"State" : ["Illinois", "Florida", "Texas", "Nebraska"] } )



df = pd.concat([df1, df2])
df = df.reset_index(drop=True)

df_gpby = df.groupby(list(df.columns))
idx = [x[0] for x in df_gpby.groups.values() if len(x) == 1]
blah = df.reindex(idx)
4
You could be a little more specific. What is the question? Where are the problem? ...lrleon

4 Answers

12
votes

Building on @EdChum's suggestion:

df = pd.merge(df1, df2, how='outer', suffixes=('','_y'), indicator=True)
rows_in_df1_not_in_df2 = df[df['_merge']=='left_only'][df1.columns]

rows_in_df1_not_in_df2

|Index |City        |State       |
|------|------------|------------|
|1     |San Franciso|California  |
|2     |Boston      |Massachusett|

EDIT: incorporate @RobertPeters suggestion

8
votes

IIUC then if you're using pandas version 0.17.0 then you can use merge and set indicator=True:

In [80]:
df1 = pd.DataFrame( { 
"City" : ["Chicago", "San Franciso", "Boston"] , 
"State" : ["Illinois", "California", "Massachusett"] } )
​
df2 = pd.DataFrame( { 
"City" : ["Chicago",  "Mmmmiami", "Dallas" , "Omaha"] , 
"State" : ["Illinois", "Florida", "Texas", "Nebraska"] } )
pd.merge(df1,df2, how='outer', indicator=True)

Out[80]:
           City         State      _merge
0       Chicago      Illinois        both
1  San Franciso    California   left_only
2        Boston  Massachusett   left_only
3      Mmmmiami       Florida  right_only
4        Dallas         Texas  right_only
5         Omaha      Nebraska  right_only

This adds a column to indicator whether the rows are only present in either lhs or rhs

2
votes

If you're on pandas < 0.17.0

You could work your way up like

In [182]: df = pd.merge(df1, df2, on='City', how='outer')

In [183]: df
Out[183]:
           City       State_x   State_y
0       Chicago      Illinois  Illinois
1  San Franciso    California       NaN
2        Boston  Massachusett       NaN
3      Mmmmiami           NaN   Florida
4        Dallas           NaN     Texas
5         Omaha           NaN  Nebraska

In [184]: df.ix[df['State_y'].isnull(),:]
Out[184]:
           City       State_x State_y
1  San Franciso    California     NaN
2        Boston  Massachusett     NaN
2
votes

You can also use a list comprehension and compare the rows to return the missing elements:

dif_list = [x for x in list(df1['City'].unique()) if x not in list(df2['City'].unique())]

returns:

['San Franciso', 'Boston']

You could then get a dataframe with just the rows that are different:

dfdif = df1[(df1['City'].isin(dif_list))]