vlookup equivalent to join 2 tables using pandas

Question

Scenario:

df1 has all possible auction ID values
df2 has a subset of possible auction ID values
df2 contains the object ID present in the auction (can be 0 or positive int)

Goal:

Create a new df1 column "object_id" populated with corresponding values in df2

Example...

df1 = pd.DataFrame(columns=['auction_id'], data=[1,2,3,4,5,6,7,8,9])

auction_id
1  
2  
3  
4  
5  
6  
7  
8  
9  


df2 = pd.DataFrame({'auction_id':[1,2,4,6,7,8,9], 'object_id': [640,0,332,332,0,58,332]})

auction_id  object_id  
1             640  
2             0  
4             332  
6             332  
7             0  
8             58  
9             332

My best effort so far

using vlookup in Pandas using join):

df1['object_id'] = df1.auction_id.map(df2.object_id)

Which yields:

df1  
auction_id  object_id
1             0  
2             332  
3             332  
4             0  
5             58  
6             332  
7             NaN  
8             NaN  
9             NaN

@EdChum - I referenced the post you suggest as duplicative, and tried to apply the .map() function it recommends. My results from doing so are listed above under "My best effort so far". I realize I must be missing something; can you help my understand how to use the .map() function in this example? — Justin Pines, Jun 18 '15 at 16:54
You can do this `df1['object_id'] = df1.auction_id.map(df2.set_index('auction_id').object_id)` if you pass a series then the index must be the set to what you're trying to perform the lookup on — EdChum, Jun 18 '15 at 16:58

score 1 · Answer 1 · edited Jul 07 '18 at 19:07

You can use the merge function from the pandas library as follows:

import pandas as pd

df1 = pd.DataFrame(columns=['auction_id'], data=[1,2,3,4,5,6,7,8,9])    
df2 = pd.DataFrame({'auction_id':[1,2,4,6,7,8,9], 'object_id': [640,0,332,332,0,58,332]})    

new_df = pd.merge(df1,df2,how='left',on='auction_id')

You can read about the merge function here: http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.merge.html

vlookup equivalent to join 2 tables using pandas

Scenario:

Goal:

Example...

My best effort so far

1 Answers1