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?

Answer 1

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