Lookup value between data frames

Question

Suppose I have two data frames as following:

df1 = pd.DataFrame([('k01', 'p01'), ('k02', 'p02'), ('k03', 'p03')], columns=['kid_id','parent_id'])


df2 = pd.DataFrame([('k01', 'andy'), ('p03', 'kyle'), ('k03', 'danny'), ('p01', 'bob'), ('p02', 'karen'),  ('k02', 'teddy'), ('p04', 'sussy'), ('k05', 'emily')], columns=['id','name'])

I want to add two new columns, kid_name and parent_name to df1, something like this, by looking up the id in df2 and return name:

df1[['kid_name', 'parent_name']] = df2.....

How to do this in one clean line?

Update: following @Anurag Dabas's suggestion below, here is what I come up with:

df3 = df1.merge(df2, left_on='kid_id', right_on='id', how='left')\
      .rename(columns={'name':'kid_name'}).drop('id', axis=1)\
      .merge(df2, left_on='parent_id', right_on='id', how='left')\
      .rename(columns={'name':'parent_name'}).drop('id', axis=1)

Still, that seems awfully messy (what if I need to do this for more than 2 columns). If anyone can improve this, thanks?

Expected output:

  kid_id parent_id kid_name parent_name
0    k01       p01     andy         bob
1    k02       p02    teddy       karen
2    k03       p03    danny        kyle

`df1.rename(columns={'kid_id':'id'}).merge(df2,on='id',how='left')` you can also specify left_on and right_on parameter — Anurag Dabas, Jul 29 '21 at 17:58
Does this answer your question? [Pandas Merging 101](https://stackoverflow.com/questions/53645882/pandas-merging-101) — Anurag Dabas, Jul 29 '21 at 17:58
Thanks. I do the same for ```parent_id```? i.e. in another line? — Tristan Tran, Jul 29 '21 at 17:59
you can also specify left_on and right_on parameter see the dupe — Anurag Dabas, Jul 29 '21 at 18:00

score 2 · Accepted Answer · edited Jul 30 '21 at 04:48

For more simplicity you can map values by creating a dictionary and map() method:

#created a dictionary
d=dict(df2[['id','name']].values)
#Finally map value from that dictionary:
df1['kid_name']=df1['kid_id'].map(d)
df1['parent_name']=df1['parent_id'].map(d)

output of df1:

  kid_id    parent_id   kid_name    parent_name
0   k01     p01         andy        bob
1   k02     p02         teddy       karen
2   k03     p03         Danny       kyle

Another option, without creating a dictionary, is to use a Series, when mapping, along with assign :

mapping = df2.set_index('id').name

df1.assign(kid_name    = df1.kid_id.map(mapping), 
           parent_name = df1.parent_id.map(mapping)
           )

  kid_id parent_id kid_name parent_name
0    k01       p01     andy         bob
1    k02       p02    teddy       karen
2    k03       p03    danny        kyle

For more information regarding mapping and replacing see this thread

Lookup value between data frames

1 Answers1