0

I would like to know if it would be possible to merge two table with non unique merging key into as concise shape as possible?

My problem is as follows: I have two tables

First_name Last_name Reviewer Score1 Score2
John Terry Bob 5 3
John Terry Alice 4 4

and

First_name Last_name Reviewer Score3 Score4
John Terry Dylan 1 1
John Terry Mary 4 3
John Terry Richa 5 5

I would like to merge it into

First_name Last_name Reviewer_x Score1 Score2 Reviewer_y Score3 Score4
John Terry Bob 5 3 Dylan 1 1
John Terry Alice 4 4 Mary 4 3
John Terry NaN NaN NaN Richa 5 5

I tried this

df1.merge(df2, on = ['First_name', 'Last_name'])

but pandas does not know how to deal with the duplicates in First_name Last_name combination and joins all rows from the right table to each row of the left table, hence it creates table with 6 rows.

Is it possible to do the transformation that I need?

Cool School
  • 191
  • 1
  • 4
  • do you have any names other than John Terry? –  Feb 11 '22 at 19:43
  • I think you're looking for something like [this](https://stackoverflow.com/a/68443465/15497888) `df1.merge(df2, left_on=['First_name', 'Last_name', df1.groupby(['First_name', 'Last_name']).cumcount()], right_on=['First_name', 'Last_name', df2.groupby(['First_name', 'Last_name']).cumcount()], how='right')` assuming you are looking to positional join. – Henry Ecker Feb 11 '22 at 19:49
  • Yes, I have more name than John Terry – Cool School Feb 11 '22 at 20:38

0 Answers0