Pandas - Comparing two Dataframe using sub-string of one Dataframe column

user_9776699 0
Viewed 63

I am trying to compare two Dataframes using a sub-string in one column with another Dataframe.

Given below is how my data looks like:

Dataframe 1

prod_name, prod_id, prod_category
prod_1, cate_1000101, category_1 
prod_2, cate_123001, category_2
prod_3, cate_900, category_3
prod_4, cate_808, category_4

Dataframe 2

bill_id, bill_date, prod_ref
101, 2021-01-01, 3001
102, 2021-01-01, 5001
103, 2021-01-01, 8080

I am trying to compare if any part of prod_id from Dataframe 1 is available in prod_ref in Dataframe 2

Expected output:

prod_name, prod_id, bill_id, bill_date, prod_ref
prod_2, cate_123001, 101, 2021-01-01, 3001
prod_4, cate_808, 103, 2021-01-01, 8080
pandas
1 Answers

I was able to get the required output using the below

df1.merge(df2, left_on = df2.prod_ref.str.extract('(\d+)', expand = False), right_on = df1.prod_id.str.extract('(\d+)', expand = False), how = 'left')
user_97766990
Related