CODEWITHSUNDEEP
pythonpandasdataframe
2shar
2shar

Reputation: 111

Pandas Merge on dataframe while keeping common number of rows

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?

Upvotes: 3

Views: 299

Answers (2)

Serkan Arslan
Serkan Arslan

Reputation: 13393

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

Upvotes: 3

park ji young
park ji young

Reputation: 21

i think you should use the merge() function 

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

Upvotes: 2

Related Questions