Pandas: How to match the multiple column values with another dataframe and generate output

user_13838014 0
Viewed 39
df1 = pd.DataFrame({'id_imp': ['a', 'b', 'c','d','e','f','g'], 
   'name': ['jon', 'jon', 'tom', 'ber', 'gary','gary', 'zul'],
   'state' : ['ca', 'ny', 'tn','ca','tn','tn','il'],
   'county': ['wood','wood','fair','bridge','rosewelt','rosewelt','lili']})

df2 = pd.DataFrame({'id_sal': ['h', 'i', 'j','k','l'], 
   'name': ['jon', 'zolie', 'tom', 'ber', 'gary'],
   'state' : ['ca', 'ch', 'tn','ca','tn'],
   'county': ['wood','plas','fair','bridge','rosewelt']})

I have two dataframes and I would like to match df2 with df1 based on 'name' and 'county' only. The final output should have common rows based on 'name' and 'county' along with 'id_imp' from df1. The objective is to know which id from df1 has been matched. I followed the below steps to get the unique combination of 'name' and 'county' and then I do not how to proceed further.

new = df1.groupby(['name','county']).size().reset_index().rename(columns={0:'count'})

The final expected output is

final = pd.DataFrame({'id_sal': ['h', 'j', 'k','l'], 
   'name': ['jon', 'tom', 'ber', 'gary'],
   'state' : ['ca', 'tn','ca','tn'],
   'county': ['wood','fair','bridge','rosewelt'],
   'id_imp': ['a','c','d','f']})
pandas-groupby pandas
1 Answers

I modified the first part with drop_duplicates, then do merge

final=df2.merge(df1.drop_duplicates(['name','county']).drop('state',1), how='left',indicator=True, on=['name','county'])
final=final[final['_merge']=='both'].drop('_merge',1)
final
Out[12]: 
  id_sal  name state    county id_imp
0      h   jon    ca      wood      a
2      j   tom    tn      fair      c
3      k   ber    ca    bridge      d
4      l  gary    tn  rosewelt      e
user_79645270
Related