Comparing 2 dataframes without iterating-CodePudding

Considering I have 2 dataframes as shown below (DF1 and DF2), I need to compare DF2 with DF1 such that I can identify all the Matching, Different, Missing values for all the columns in DF2 that match columns in DF1 (Col1, Col2 & Col3 in this case) for rows with same EID value (A, B, C & D). I do not wish to iterate on each row of a dataframe as it can be time consuming.

DF1

    EID Col1 Col2 Col3 Col4
0   A   a1   b1   c1   d1
1   B   a2   b2   c2   d2
2   C   None b3   c3   d3
3   D   a4   b4   c4   d4
4   G   a5   b5   c5   d5

DF2

    EID Col1 Col2 Col3
0   A   a1   b1   c1
1   B   a2   b2   c9
2   C   a3   b3   c3
3   D   a4   b4   None

Expected output dataframe

    EID Col1 Col2 Col3 New_Col
0   A   a1   b1   c1   Match
1   B   a2   b2   c2   Different
2   C   None b3   c3   Missing in DF1
3   D   a4   b4   c4   Missing in DF2

CodePudding user response：

Underneath it all, you're still going to be doing iterations. However, what you can do is merge the two columns on the EID, perform and outer join, and then use an apply function to generate your new_col.

df3 = pd.merge(df1, df2, on='EID', how='outer', lsuffix='df1_', rsuffix='df2_')
df3['comparison'] = df3.apply(lambda x: comparison_function(x), axis=1)
# your comparison_function will have your checks that result in missing in df1, df2, etc

You can then use

CodePudding user response：

try:

#1. 
DF1 = DF1.drop('Col4', axis=1)
df= pd.merge(DF2, DF1.loc[df['EID'].ne('G')], on=['Col1','Col2', 'Col3', 'EID'], how='left', indicator='New Col')
df['New Col'] = np.where(df['New Col'] == 'left_only', "Missing in DF1", df['New Col'])

df = df.merge(pd.merge(DF2.loc[:, ['EID','Col1','Col2']], DF1.loc[DF1['EID'].ne('G'), [ 'EID', 'Col1','Col2',]], on=['EID', 'Col1','Col2', ], how='left', indicator='col1_col2'), on=['EID','Col1','Col2'], how='left')
df = df.merge(pd.merge(DF2.loc[:, ['EID','Col2','Col3']], DF1.loc[DF1['EID'].ne('G'), [ 'EID', 'Col2','Col3',]], on=['EID', 'Col2','Col3', ], how='left', indicator='col2_col3'), on=['EID','Col2','Col3'], how='left')
df = df.merge(pd.merge(DF2.loc[:, ['EID','Col1','Col3']], DF1.loc[DF1['EID'].ne('G'), [ 'EID', 'Col1','Col3',]], on=['EID', 'Col1','Col3', ], how='left', indicator='col1_col3'), on=['EID','Col1','Col3'], how='left')

a1 = df['New Col'].eq('both') #match
a2 = df['col1_col2'].eq('both') & df['New Col'].eq('Missing in DF1') #same by Col1 & Col2 --> Different
a3 = df['col2_col3'].eq('both') & df['New Col'].eq('Missing in DF1') #same by Col2 & Col3 --> Different
a4 = df['col1_col3'].eq('both') & df['New Col'].eq('Missing in DF1') #same by Col1 & Col3 --> Different
df['New Col'] = np.select([a1, a2, a3, a4], ['match', 'Different/ same Col1 & Col2', 'Different/ same Col2 & Col3', 'Different/ same Col1 & Col3'], df['New Col'])
df = df.drop(columns=['col1_col2', 'col2_col3', 'col1_col3'])

    EID Col1    Col2    Col3    New Col
0   A   a1      b1      c1      match
1   B   a2      b2      c9      Different/ same Col1 & Col2
2   C   a3      b3      c3      Different/ same Col2 & Col3
3   D   a4      b4      None    Different/ same Col1 & Col2

#2.
DF1 = DF1.drop('Col4', axis=1)
df= pd.merge(DF2, DF1.loc[df['EID'].ne('G')], on=['Col1','Col2', 'Col3', 'EID'], how='left', indicator='New Col')
df['New Col'] = np.where(df['New Col'] == 'left_only', "Missing in DF1", df['New Col'])

df = df.merge(pd.merge(DF2.loc[:, ['EID','Col1','Col2']], DF1.loc[DF1['EID'].ne('G'), [ 'EID', 'Col1','Col2',]], on=['EID', 'Col1','Col2', ], how='left', indicator='col1_col2'), on=['EID','Col1','Col2'], how='left')

a1 = df['New Col'].eq('both') #match
a2 = df['col1_col2'].eq('both') & df['New Col'].eq('Missing in DF1') #Different

df['New Col'] = np.select([a1, a2], ['match', 'Different'], df['New Col'])

df = df.drop(columns=['col1_col2'])

    EID Col1    Col2    Col3    New Col
0   A   a1      b1      c1      match
1   B   a2      b2      c9      Different
2   C   a3      b3      c3      Missing in DF1
3   D   a4      b4      None    Different

Note1: no iteration
Note2: goal of this solution: compare DF2 with DF1 such that you can identify all the Matching, Different, Missing values for all the columns in DF2 that match columns in DF1 (Col1, Col2 & Col3 in this case) for rows with same EID value (A, B, C & D)