CODEWITHSUNDEEP
pythonpandas
chicagobeast12
chicagobeast12

Reputation: 695

Vlookup in python with 2 dataframes

I have the df's below:

d1 = {'id': ['a','b','c'], 'ref': ['apple','orange','banana']}
df1 = pd.DataFrame(d1)

d2 = {'id': ['a','b','c'], 'apple': [1,2,3], 'orange': [4,5,6], 'banana':[7,8,9]}
df2 = pd.DataFrame(d2)

Need help. What I would like to do is create a new column in df1 that populates the values from df2 for each ref by id

Upvotes: 1

Views: 671

Answers (1)

BENY
BENY

Reputation: 323226

Let us do melt then merge

out = df1.merge(df2.melt('id',var_name='ref'),how='left')
Out[72]: 
  id     ref  value
0  a   apple      1
1  b  orange      5
2  c  banana      9

More like vlookup

df2 = df2.set_index('id')
df1['new'] = df2.values[df2.index.get_indexer(df1.id),df2.columns.get_indexer(df1.ref) ]
df1
Out[76]: 
  id     ref  new
0  a   apple    1
1  b  orange    5
2  c  banana    9

Upvotes: 3

Related Questions