CODEWITHSUNDEEP
pythonpandasdataframevlookup
Ussu20
Ussu20

Reputation: 199

Vlookup based on multiple columns in Python DataFrame

I have two dataframes. I am trying to Vlookup 'Hobby' column from the 2nd dataframe and update the 'Interests' column of the 1st dataframe. Please note that the columns: Key, Employee and Industry should match exactly between the two dataframes but in case of City, even if the first part of the city matches between the two dataframe it should be acceptable. Though, it is starightforward in Excel, it looks a bit complicated to implement it on Python. Any cue on how to proceed will be really helpful. (Please see below the screenshot for the expected output.)

data1=[['AC32456','NYC-URBAN','JON','BANKING','SINGING'],['AD45678','WDC-RURAL','XING','FINANCE','DANCING'],
     ['DE43216', 'LONDON-URBAN','EDWARDS','IT','READING'],['RT45327','SINGAPORE-URBAN','WOLF','SPORTS','WALKING'],
     ['Rs454457','MUMBAI-RURAL','NEMBIAR','IT','ZUDO']]


data2=[['AH56245','NYC','MIKE','BANKING','BIKING'],['AD45678','WDC','XING','FINANCE','TREKKING'],
     ['DE43216', 'LONDON-URBAN','EDWARDS','FINANCE','SLEEPING'],['RT45327','SINGAPORE','WOLF','SPORTS','DANCING'],
     ['RS454457','MUMBAI','NEMBIAR','IT','ZUDO']]

List1=['Key','City','Employee', 'Industry', 'Interests']
List2=['Key','City','Employee', 'Industry', 'Hobby']

df1=pd.DataFrame(data1, columns=List1)
df2=pd.DataFrame(data2,columns=List2)

enter image description here

Upvotes: 1

Views: 1437

Answers (1)

It_is_Chris
It_is_Chris

Reputation: 14093

Set in index of df1 to Key (you can set the index to whatever you want to match on) and the use update:

# get the first part of the city
df1['City_key'] = df1['City'].str.split('-', expand=True)[0]
df2['City_key'] = df2['City'].str.split('-', expand=True)[0]
# set index
df1 = df1.set_index(['Key', 'Employee', 'Industry', 'City_key'])
# update
df1['Interests'].update(df2.set_index(['Key', 'Employee', 'Industry', 'City_key'])['Hobby'])
# reset index and drop the City_key column
new_df = df1.reset_index().drop(columns=['City_key'])

        Key Employee Industry             City Interests
0   AC32456      JON  BANKING        NYC-URBAN   SINGING
1   AD45678     XING  FINANCE        WDC-RURAL  TREKKING
2   DE43216  EDWARDS       IT     LONDON-URBAN   READING
3   RT45327     WOLF   SPORTS  SINGAPORE-URBAN   DANCING
4  Rs454457  NEMBIAR       IT     MUMBAI-RURAL      ZUDO

Upvotes: 1

Related Questions