python pandas - creating a column after matching keys with another data frame

Question

I have two data frames. for the sake of simpleness, I will provide two dummy data frames here.

A = pd.DataFrame({'id':[1,2,3], 'name':['a','b','c']})
B = pd.DataFrame({'id':[1,1,1,3,2,3,1]})

Now, I want to create a column on the data frame B with the names that match the ids. In this case, my desire output will be:

B = pd.DataFrame({'id':[1,1,1,3,2,3,1], 'name':['a','a','a','c','b','c','a'})

I was trying to use .apply and lambda or try to come up with other ideas, but I could not make it work.

Thank you for your help.

score 2 · Accepted Answer · answered Jan 25 '20 at 02:52

pd.merge or .map we use your id column as the key and return all matching values on your target dataframe.

df = pd.merge(B,A,on='id',how='left')

#or

B['name'] = B['id'].map(A.set_index('id')['name'])

print(df)

   id name
0   1    a
1   1    a
2   1    a
3   3    c
4   2    b
5   3    c
6   1    a

print(B)

   id name
0   1    a
1   1    a
2   1    a
3   3    c
4   2    b
5   3    c
6   1    a

1 Answers1