Compare 2 columns in different dataframes with a primary key condition without merge

user_10544237 0
Viewed 1857

I have 2 different dataframe Ex:

Df1:

User_id    User_name     User_phn
1          Alex          1234123
2          Danny         4234123
3          Bryan         5234123

Df2:

User_id    User_name     User_phn
1          Alex          3234123
2          Chris         4234123
3          Bryan         5234123
4          Bexy          6234123

user_id is the primary key in both the tables and I need to compare both the dataframes using the user_id as a condition and result me with values which are having matching and mismatch values without merging the dataframes into a new dataframe. We will be processing more than 100 millions of records with huge datasets, that why I do not want to merge again in a new dataframe which I think would consume memory again.

Result:

User_id    User_name     User_phn
1          Alex          Mismatch
2          Mismatch      4234123
3          Bryan         5234123
4          Mismatch      Mismatch
pandas dataframe validation python-3.x python
4 Answers

Not easy, but possible by comparing Series of tuples created by combinations of columns and comparing by isin:

s11 = pd.Series(list(map(tuple, Df1[['User_id','User_name']].values.tolist())))
s12 = pd.Series(list(map(tuple, Df2[['User_id','User_name']].values.tolist())))

s21 = pd.Series(list(map(tuple, Df1[['User_id','User_phn']].values.tolist())))
s22 = pd.Series(list(map(tuple, Df2[['User_id','User_phn']].values.tolist())))


Df2.loc[~s12.isin(s11), 'User_name'] = 'Mismatch'
Df2.loc[~s22.isin(s21), 'User_phn'] = 'Mismatch'

print (Df2)
   User_id User_name  User_phn
0        1      Alex  Mismatch
1        2  Mismatch   4234123
2        3     Bryan   5234123
3        4  Mismatch  Mismatch

Solution with merge with test unmatched pairs (missing values) by isna:

s1 = Df2.merge(Df1, how='left', on=['User_id','User_name'], suffixes=('_',''))['User_phn']
print (s1)
0    1234123.0
1          NaN
2    5234123.0
3          NaN
Name: User_phn, dtype: float64

s2 = Df2.merge(Df1, how='left', on=['User_id','User_phn'], suffixes=('_',''))['User_name']
print (s2)
0      NaN
1    Danny
2    Bryan
3      NaN
Name: User_name, dtype: object

Df2.loc[s1.isna(), 'User_name'] = 'Mismatch'
Df2.loc[s2.isna(), 'User_phn'] = 'Mismatch'

print (Df2)
   User_id User_name  User_phn
0        1      Alex  Mismatch
1        2  Mismatch   4234123
2        3     Bryan   5234123
3        4  Mismatch  Mismatch
user_29010020

You could also try this approach by using series map:

Df_new = Df2.copy()

cond1 = Df_new['User_phn'].isin(Df1['User_phn'])
cond2 = Df_new['User_name'].isin(Df1['User_name'])

Df_new.loc[~cond1, 'User_phn'] = Df_new.loc[~cond1, 'User_phn'].map(Df1['User_phn']).fillna('Mismatch')
Df_new.loc[~cond2, 'User_name'] = Df_new.loc[~cond2, 'User_name'].map(Df1['User_name']).fillna('Mismatch')
user_61306020

I wrote the code and tackled the problem in a rather simple manner. I just compared every row of the two databases and did the comparison and appended resultant row to a result database. Let me know if this works. 

import pandas as pd
data = [[1,'Alex','1234123'],[2,'Danny','4234123'],[3,'Bryan','5234123']]
df = pd.DataFrame(data,columns=['User_id','User_name','User_phn'])
print (df)
data = [[1,'Alex','3234123'],[2,'Chris','4234123'],[3,'Bryan','5234123'],[4,'Bexy','6234123']]
df_2 = pd.DataFrame(data,columns=['User_id','User_name','User_phn'])
print (df_2)
l=max(len(df.index),len(df_2.index))
df_res = pd.DataFrame(columns=['User_id','User_name','User_phn'])
df_mat = df.as_matrix()
df_2_mat = df_2.as_matrix()
for i in range(0,l):
    try:
            arr=[]
            arr.append(df_mat[i][0])
            for k in range(1,3):
                if df_mat[i][k] == df_2_mat[i][k]:
                    arr.append(df_mat[i][k])
                else:
                    arr.append("Mismatch")
            df_res.loc[i] = arr

    except:
        df_res.loc[i] = [i+1,"Mismatch","Mismatch"]
print(df_res)
user_57978230

Hi Narayana Kandukuri,

I guess my code may be simple, have a look.

import pandas as pd

df1 = pd.DataFrame([[1,'Alex',1234123],[2,'Danny',4234123],[3,'Bryan',5234123]],columns=['User_id','User_name','User_phn'])
df2 = pd.DataFrame([[1,'Alex',3234123],[2,'Chris',4234123],[3,'Bryan',5234123],[4,'Bexy',6234123]],columns=['User_id','User_name','User_phn'])

temp = df2[['User_id']] #Saving this for later use.
Bool_Data = (df1==df2[:df1.shape[0]]) #This will give you a boolean frame
df2 = df2[Bool_Data].fillna('mismatch') #Keep this boolean frame to df2
df2['User_id'] = temp['User_id'] #Assign the before temp.

df2 =
   User_id User_name     User_phn
0        1      Alex     mismatch
1        2  mismatch     423412
2        3     Bryan     523412
3        4  mismatch     mismatch
user_101132130
Related