Pandas Merge on dataframe while keeping common number of rows

Question

I have two pandas dataframe in python I want to concatenate on common column (eg. id)

First Source dataframe is something like this

id  | col 
---------
1   | h1
2   | h2
3   | h3 
3   | h33
3   | h333
4   | h4 
6   | h6

Target dataframe is

id  | col 
---------
1   | h11
2   | h2
3   | h%
3   | h3
4   | h4 
6   | h6

Here, the row with id=3 has duplicates. Source dataframe with id=3 has three rows & target dataframe with id=3 has two rows. I want to be able to retain the first common number of rows (i.e two), something like this

id  | col 
---------
1   | h1  | h11
2   | h2  | h2 
3   | h3  | h%
3   | h33 | h3
4   | h4  | h4 
6   | h6  | h6

I have tried simple merge in pandas like

pd.concat(source_df , target_df, on="id")

Is there anything else I can do to achieve this logic?

Serkan Arslan · Accepted Answer · 2019-12-10 06:04:50Z

3

you can merge with left or inner depends on your need but before this, you should group by id and give row number with rank for each id group.

import pandas as pd

source_df = pd.DataFrame({'id' : [1,2,3,3,3,4,6] , 'col' : ['h1','h2','h3','h33','h333','h4','h6']})
target_df = pd.DataFrame({'id' : [1,2,3,3,4,6] , 'col' : ['h11', 'h2','h%','h3','h4','h6']})

source_df["rn"] = source_df.groupby('id')['id'].rank(method='first')

target_df["rn"] = target_df.groupby('id')['id'].rank(method='first')

new_df = target_df.merge(source_df, on=['id','rn'] , how='left')

Result:

   id col_x   rn col_y
0   1   h11  1.0    h1
1   2    h2  1.0    h2
2   3    h%  1.0    h3
3   3    h3  2.0   h33
4   4    h4  1.0    h4
5   6    h6  1.0    h6

answered Dec 10, 2019 at 6:04

Serkan Arslan

13.4k4 gold badges33 silver badges46 bronze badges

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

Comments

park ji young · Accepted Answer · 2019-12-10 06:02:57Z

2

i think you should use the merge() function

pd.merge(source_df, target_df, on="id", how='inner')

answered Dec 10, 2019 at 6:02

park ji young

211 bronze badge

Collectives™ on Stack Overflow

Pandas Merge on dataframe while keeping common number of rows

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest