How to compare pandas dataframe using given keys

user_18962965 0
Viewed 126

I have two datasets. I want to compare using id, name and need to write in different data frame with mismatched values as "Mismatched" and mismatched rows as it is.

df1             
                
Index   id  name     dept     addr
0        1  Jeff     IT      Delhi
1        2  Tom     Support  Bangalore
2        3  Peter   Admin    Pune
3        4  Kaif    IT       Pune
4        5  Lee     Dev     Delhi


df2             
                
Index   id  name    dept     addr
0      1    Jeff    IT       Delhi
1      2    Tom     QA      Bangalore
2      3    Peter   Admin   Pune
3      4    Kaif    IT      Hyderabad

And I need result like,

Result              
                
Index   id  name       dept            addr
0      2    Tom         Mismatched      Bangalore
1      4    Kaif        IT              Mismatched
2      5    Lee         Dev             Delhi
pandas dataframe python-3.x python
1 Answers

One way to do what you intend to do (if 'id' and 'name' already match as in the case you show) is to do an inner merge according to the 'name' column and then change the 'Dept' value to 'mismatch' if the 'dept_x' and 'dept_y' value of the merged dataframe don't match.

A = pd.merge(df1,df2, on='name', how='inner')
# It creates new columns
print(A)
   id_x   name   dept_x     addr_x  id_y dept_y     addr_y
0     1   Jeff       IT      Delhi     1     IT      Delhi
1     2    Tom  Support  Bangalore     2     QA  Bangalore
2     3  Peter    Admin       Pune     3  Admin       Pune
3     4   Kaif       IT       Pune     4     IT  Hyderabad

B = A.copy()
B['dept_x'] = A.apply(lambda x : 'mismatch' if x.dept_x!=x.dept_y else x.dept_x, axis=1)
print(B)
   id_x   name    dept_x     addr_x  id_y dept_y     addr_y
0     1   Jeff        IT      Delhi     1     IT      Delhi
1     2    Tom  mismatch  Bangalore     2     QA  Bangalore
2     3  Peter     Admin       Pune     3  Admin       Pune
3     4   Kaif        IT       Pune     4     IT  Hyderabad

Then you can do the same for the address column, filter the rows with mismatch if you intend to only keep them, and rename or delete the columns that you need/don't need accordingly.

If you have many columns, you can use a function inside the .apply() to make it more general :

# the columns that you intend to check the mismatch for
cols = ['dept','addr']

# or if you want to do it on all columns except the first two because there's too many
cols = [a for a in df1.columns if a not in ['name','id']]

# define a function that compares for all columns
def is_mismatch(x) :
    L = ['mismatch' if x[cols[i]+'_x']!=x[cols[i]+'_y'] else x[cols[i]+'_x'] for i in range(len(cols))]
    return pd.Series(L)

C = A.copy()
C[cols] = C.apply(is_mismatch, axis=1) # don't forget that axis=1 here !
print(C)

   id_x   name   dept_x     addr_x  id_y dept_y     addr_y      dept  \
0     1   Jeff       IT      Delhi     1     IT      Delhi        IT   
1     2    Tom  Support  Bangalore     2     QA  Bangalore  mismatch   
2     3  Peter    Admin       Pune     3  Admin       Pune     Admin   
3     4   Kaif       IT       Pune     4     IT  Hyderabad        IT   

        addr  
0      Delhi  
1  Bangalore  
2       Pune  
3   mismatch  

# if you want to clean the columns
C = C[['id_x','name']+cols]
print(C)

   id_x   name      dept       addr
0     1   Jeff        IT      Delhi
1     2    Tom  mismatch  Bangalore
2     3  Peter     Admin       Pune
3     4   Kaif        IT   mismatch
user_153672740
Related