How to check column value in multiple columns in two dataframes (Pandas)?

user_976513 0
Viewed 118

DataFrame 1:

     Email            Mobile    
  0  test1@test1.com  98989892     
  1  test4@test4.com  98989895
  2  test5@test5.com  98989894 
  3  Otheruser@mail.com 98438348342343

DataFrame 2:

     Name        Email1           Email2          Email 3    
  0  x_person    test1@test1.com  hello@hello.com Hello@Hello.com    
  1  y_person    test4@test4.com                  test2@test2.com
  2  z_person.   test5@test5.com  asasas@asas.com

What is the best way to check Email column value of data frame 1 exist in any of email columns in data frame 2 ? if exists then i want to combine (left join) the records as matches..

Expected result:

 Email               Mobile                Name       Email1    
 test1@test1.com     98989892              x_person   test1@test1.com 
 test4@test4.com     98989895              y_person.  test4@test4.com  
 test5@test5.com     98989894              z_person.  test5@test5.com 
 Otheruser@mail.com  98438348342343
data-science pandas python
1 Answers

I think you need DataFrame.melt with DataFrame.merge and left join:

df22 = df2.melt('Name', value_name='Email').drop('variable', axis=1)
df = df1.merge(df22, on='Email', how='left')
print (df)
                Email          Mobile      Name
0     test1@test1.com        98989892  x_person
1     test4@test4.com        98989895  y_person
2     test5@test5.com        98989894  z_person
3  Otheruser@mail.com  98438348342343       NaN
user_29010020
Related