CODEWITHSUNDEEP
pythonpandasdataframe
Muralitharan Sathyamoorthy
Muralitharan Sathyamoorthy

Reputation: 287

Comparing Two columns of Two different data frame and create new column with If condition

I need to compare two column in two different data frame and need an output as mentioned below:

Data frame 1:

file_id FileName
UC1    Action
UC1    Patient
UC1    Auth
UC1    DAO
UC1   Transaction
UC1   Validator
UC2    HCP
UC2  Personnel
UC2   Auth DAO

Data frame 2:

file_id FileName
UC0     Action
UC0    Patient   
UC1     Auth
UC1     Bean
UC1     Validator
UC2      HCP
UC2   Auth DAO

Output Need in format:

file_id FileName    Output
UC0 Action           No
UC0 Patient          No
UC1 Auth             Yes
UC1 Bean             No
UC1 Validator       Yes
UC2 HCP             Yes
UC3 Auth DAO        No

Upvotes: 1

Views: 208

Answers (1)

jezrael
jezrael

Reputation: 862521

Use DataFrame.merge with left join and indicator:

mask = (df2.merge(df1, 
                  on=['file_id', 'FileName'], 
                  how='left', 
                  indicator=True)['_merge'].eq('both'))

Or compare MultiIndex by Index.isin:

mask = (df2.set_index(['file_id', 'FileName']).index
                  .isin(df1.set_index(['file_id', 'FileName']).index))

And create new column by numpy.where:

df2['Output'] = np.where(mask, 'Yes', 'No')
print (df2)
  file_id   FileName Output
0     UC0     Action     No
1     UC0    Patient     No
2     UC1       Auth    Yes
3     UC1       Bean     No
4     UC1  Validator    Yes
5     UC2        HCP    Yes
6     UC2   Auth DAO    Yes

Upvotes: 4

Related Questions