Pandas: Merge on 2 different keys with same value

Question

I have two dataframes below:

dfA = pd.DataFrame([[3,"here",34]],columns = ["comp_id","mail","mean"])
dfB = pd.DataFrame([[23,3,"there"]], columns = ["alt","name_id","serv"])

dfA

        comp_id  mail  mean
   0          3 "here"   34

dfB

          alt  name_id   serv
   0       23        3  "there"

I want to join both data frames on comp_id = name_id

Output:

        mail  mean   alt     serv
 0     "here"   34    23   "there"

The output should not include comp_id or name_id.

Any suggestions on how I can do this?

Does this answer your question? [Pandas Merging 101](https://stackoverflow.com/questions/53645882/pandas-merging-101) — MrSoLoDoLo, Jul 29 '20 at 17:16

score 2 · Answer 1 · answered Jul 29 '20 at 17:15

2

Use merge with left_on and right_on:

pd.merge(dfA,dfB,left_on="comp_id", right_on = "name_id", how="inner")

answered Jul 29 '20 at 17:15

Let's try

This doesn't work, I still see comp_id and name_id in the output. The output should exclude those keys – swing1234 Jul 29 '20 at 17:59
@swapnil123, you can just select the column of interest after merge ``pd.merge(..)[columns]`` – sushanth Jul 29 '20 at 18:33

score 1 · Accepted Answer · answered Jul 29 '20 at 18:47

1

Use merge after setting index for first data frame and drop column from second data frame

dfA = dfA.set_index('comp_id')
res = dfA.merge(dfB,left_index=True,right_on='name_id').drop('name_id',axis=1)
print(res)

answered Jul 29 '20 at 18:47

Jaswant Singh

score 1 · Answer 3 · answered Jul 29 '20 at 18:55

1

Drop you join keys after the merge:

dfA.merge(dfB, left_on='comp_id', right_on='name_id').drop(['comp_id','name_id'], axis=1)

Output:

   mail  mean  alt   serv
0  here    34   23  there

answered Jul 29 '20 at 18:55

Scott Boston

3 Answers3