To compare a String Column of a Dataframe 'df1' with another String Column of a Dataframe 'df2' based on which other columns are compared

user_7818560 0
Viewed 580

Input Explained: I have two dataframe df1 and df2, which holds columns as mentioned below.

df1

Description   Col1    Col2
AAA           1.2     2.5
BBB           1.3     2.0
CCC           1.1     2.3

df2

Description   Col1    Col2
AAA           1.2     1.3
BBB           1.3     2.0

Scenario: Have to compare df1['Description'] and df2['Description'], when both equals then have to compare df1['Col1'] with df2['Col1'] and df1['Col2'] with df2['Col2'] and produce result as expected below.

Expected Output:

Description    Col1    Col2   Col1_Result           Col2_Result
AAA            1.2     2.5    Pass                  Fail
BBB            1.3     2.0    Pass                  Pass
CCC            1.1     2.3    Not found in df2      Not found in df2

Tried Code: Have tried out the below mentioned codeline for above mentioned scenario but doesn't works. Throughs error "ValueError: Can only compare identically-labeled Series objects"

df1['Col1_Result'] = np.where(df1['Description']== df2['Description'],np.where(df1['Col1'] == df2['Col1'], 'Pass', 'Fail'),'Not found in df2')
df1['Col2_Result'] = np.where(df1['Description']== df2['Description'],np.where(df1['Col2'] == df2['Col2'], 'Pass', 'Fail'),'Not found in df2')

Thanks in Advance!

pandas dataframe numpy python string
2 Answers

Alternative, code below works with the example given. If there are edge cases, it could be modified as needed.

# Import libraries
import pandas as pd

# Create DataFrame
df1 = pd.DataFrame({
    'Description':['AaA', 'BBB','CCC'],
    'Col1': [1.2,1.3,1.1],
    'Col2':[2.5,2.0,2.3]
})
df2 = pd.DataFrame({
    'Description': ['AAA', 'BBB'],
    'Col1': [1.2, 1.3],
    'Col2': [1.3, 2.0]
})

# Convert to lower case
df1['Description'] = df1['Description'].str.lower()
df2['Description'] = df2['Description'].str.lower()

# Merge df
df = df1.merge(df2, on='Description', how='left')


# Compare
df['Col1_result'] = df.apply(lambda x: 'Not found in df2' if (pd.isna(x['Col1_y'])) else
                                        'Pass' if x['Col1_x']==x['Col1_y'] else
                                        'Fail', axis=1)
df['Col2_result'] = df.apply(lambda x: 'Not found in df2' if (pd.isna(x['Col2_y'])) else
                                        'Pass' if x['Col2_x']==x['Col2_y'] else
                                        'Fail', axis=1)

# Keep only columns from df1
df = df.drop(['Col1_y', 'Col2_y'], axis=1)
# Remove '_x' from column names
df.columns = df.columns.str.replace(r'_x$', '')

# Change to upper case
df['Description'] = df['Description'].str.upper()

Output

df

  Description  Col1  Col2       Col1_result       Col2_result
0         AAA   1.2   2.5              Pass              Fail
1         BBB   1.3   2.0              Pass              Pass
2         CCC   1.1   2.3  Not found in df2  Not found in df2
user_60052060

Use DataFrame.merge with left join for output DataFrame, then select added columns by DataFrame.filter and create output by compare values first for missing values and then columns each other in numpy.select:

df1['desc'] = df1['Description'].str.lower() 
df2['desc'] = df2['Description'].str.lower()
df = (df1.merge(df2, on='desc', suffixes=['', '_Result'], how='left')
         .drop(['Description_Result','desc'], axis=1))

df3 = df.filter(like='_Result')
new = df3.rename(columns=lambda x: x.replace('_Result',''))

df[df3.columns] = np.select([new.isna(), 
                             df[new.columns].eq(new)], 
                            ['Not found in df2', 'Pass'], 'Fail')
print (df)
  Description  Col1  Col2       Col1_Result       Col2_Result
0         AAA   1.2   2.5              Pass              Fail
1         BBB   1.3   2.0              Pass              Pass
2         CCC   1.1   2.3  Not found in df2  Not found in df2

Details:

print (df3)
   Col1_Result  Col2_Result
0          1.2          1.3
1          1.3          2.0
2          NaN          NaN

print (new)
   Col1  Col2
0   1.2   1.3
1   1.3   2.0
2   NaN   NaN
user_29010020
Related