Qfyilyi

I have 2 dataframes as below:

df1:

ID   col1   col2    

1     A1     B1    

2     A2     B2     

3     A3     B3   

4     A4     B4   

5     A5     B5    

6     A6     B6    

df2:

col1   col2   

 A1     B1     

 A2     O5   

 H3     B3     

 A4     B4    

 A5     66     

 A6     C6     

Expected Result: I would like to generate a result df based on the condition - Each value in col1,col2 of df1 should exist in col1,col2 values of df2

Expected Result df:

ID   col1   col2     Error

1     A1     B1      No mismatch with df2

2     A2     B2      col2 mismatch with df2

3     A3     B3      col1 mismatch with df2

4     A4     B4      No mismatch with df2

5     A5     B5      col2 mismatch with df2

6     A6     B6      col2 mismatch with df2

Create helper DataFrame with dictionary comprehension and comparing with isin:

m = pd.DataFrame({c: ~df1[c].isin(df2[c]) for c in ['col1','col2']})

print (m)

    col1   col2

0  False  False

1  False   True

2   True  False

3  False  False

4  False   True

5  False   True

And then numpy.where with mask by any for test at least one True per rows and dot with matrix multiplication for get column names:

df1['Error'] = np.where(m.any(axis=1), 

                        m.dot(m.columns + ', ').str.rstrip(', ') + ' mismatch with df2', 

                       'No mismatch with df2')

print (df1)

   ID col1 col2                   Error

0   1   A1   B1    No mismatch with df2

1   2   A2   B2  col2 mismatch with df2

2   3   A3   B3  col1 mismatch with df2

3   4   A4   B4    No mismatch with df2

4   5   A5   B5  col2 mismatch with df2

5   6   A6   B6  col2 mismatch with df2

Something like this should do the trick but there may be an easier way.

diff = pd.concat([df1[col] == df2[col] for col in df1], axis=1)



def m(row):

    mismatches = 

    for col in diff.columns:

        if not row[col]:

            mismatches.append(col)

    if mismatches == :

        return 'No mismatch'

    return 'Mismatches: ' + ', '.join(mismatches)



df1['Error'] = diff.apply(m, axis=1)