VLookup Merge in Pandas

user_11610155 0
Viewed 69

I have two df's with a common key, "API". I want to "VLookup" the "API" and fill in the column with the corresponding answer. I am confused on how to utilize the merge to mimic a VLookup.

col_1 = ['Route', 'API', 'Owner']
data_1 = [['', '123456', ''],
          ['', '789123', ''],
          ['', '456789', ''],
          ['', '256709', '']
         ]
df1 = pd.DataFrame(data=data_1, columns=col_1)

col_2 = ['Name', 'Route', 'API', 'Owner']
data_2 = [['Hay', 'Route 12', '789123', 'Jon Doe'],
          ['Pirate', 'Route 03', '123456', 'Mary Smith'],
          ['Oat', 'Route 01', '256709', 'Jane Vick'],
          ['Pop', 'Route 04', '456789', 'Jack Roe']
         ]
df2 = pd.DataFrame(data=data_2, columns=col_2)

So df1 has empty columns that need to be filled based off the API match in df2. What I've tried is the following 

df1 = df2.merge(df2, on='API')

or

df1 = df2.merge(df2, on='API', how='outer')

I've switched the how to use 'inner', 'right', 'left' But all methods create new columns, instead of just filling in the blank already existing columns.

pandas merge python
2 Answers

You could do it without merge. isin does the lookup

df2[df1['API'].isin(df2['API'])].drop(['Name'], axis=1)

      Route     API       Owner
0  Route 12  789123     Jon Doe
1  Route 03  123456  Mary Smith
2  Route 01  256709   Jane Vick
3  Route 04  456789    Jack Roe
user_60319950

If you are using merge, use it like this:

df = pd.merge(df1,df2, on='API')[['Route_y','API','Owner_y']].rename(columns={'Route_y':'Route','Owner_y':'Owner'})

Output:

      Route     API       Owner
0  Route 03  123456  Mary Smith
1  Route 12  789123     Jon Doe
2  Route 04  456789    Jack Roe
3  Route 01  256709   Jane Vick
user_58208140
Related