Merge table output includes duplicate records pandas

Question

i am trying to merge(Left) two tables but i end up getting a output with lots of duplicate rows and below is the code that i have used so far

**file1 table**
TableA                                  Desired output
date volume Key       key1 NR/HL        date Volume NR/HL
jan  100     a         a    10           jan  100     10
jan  200     b         b    20           jan  200     10 
feb  300     a                           feb  300     20 
feb  400     b                           feb  400     20 

TableA=TableA.merge(TableB,left_on="Key",right_on="Key1",how="left")
TableA.drop_duplicates("Key",keep="first")

Does TableB contains duplicates on Key1? Otherwise, you don't even need to do drop_duplicates. — Quang Hoang
– Quang Hoang, Commented May 20, 2020 at 19:56
I removed duplicates from TableB Key1 the only duplicates we have is in TableA but will be unique with Month added. — Biplab1985
– Biplab1985, Commented May 21, 2020 at 11:50

Mayank Porwal · Accepted Answer · 2020-05-20 19:57:09Z

2

If going by the desired output that you've put in the question, you don't need to drop_duplicates at all.

Just drop the column Key1 from merge output, like this:

TableA = TableA.merge(TableB,left_on="Key",right_on="Key1",how="left").drop('Key1', 1)

You will get your desired output:

Out[1120]: 
  Month  Volumes   NR Key  NR/HL
0   jan      200  100   A     10
1   Jan      300  200   B     20
2   feb      400  300   A     10
3   Feb      500  400   B     20

answered May 20, 2020 at 19:57

Mayank Porwal

34.2k9 gold badges45 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Merge table output includes duplicate records pandas

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related