'Merge two dfs with multiple entries of same value in joining column

I have two data frames. The first is input which looks like the following:

Merchant SKU    Quantity Per Box    NOB Shipment Status id_using_regex  prepped_by_initials
2508406 - Freud Pilot Bit - 5pk 76  1   Complete    2508406 - Freud Pilot Bit - 5pk w
28046 - Arrow Fastener 276 - 12pk   90  1   Complete    28046 - Arrow Fastener 276 - 12pk   RH
28046 - Arrw Fstnr 276 Stpls - 10pk 55  1   Update AMZ Shipment 28046 - Arrw Fstnr 276 Stpls - 10pk w
28046- Arrow 3/8 staples 2 pk   24  1   Complete    28046- Arrow 3/8 staples 2 pk   w

enter image description here

And the second df called SKU_df.

Merchant SKU    merchant_sku_filtered   
2508406 -...Bit - 5pk   2508406 
28046 - Ar...276 - 12pk 28046   
2690493 -...ex Key Set  2690493 
28046 - Ar...pls - 10pk 28046   
2690493 -...ex Key Set  2690493 
28046- Arr...aples 2 pk 28046   

enter image description here

I'm trying to merge them using following code:

input['merchant_SKU_filtered'] = input['Merchant SKU'].str.split(' ').apply(lambda x: x[0])
input['merchant_SKU_filtered'] = input['merchant_SKU_filtered'].replace('-', '', regex=True)
input['merchant_SKU_filtered'] = input['merchant_SKU_filtered'].astype(int)
SKU_df['merchant_SKU_filtered'] = SKU_df['merchant_SKU_filtered'].astype(int)

key_list = list(SKU_df['merchant_SKU_filtered'])
dict_lookup = dict(zip(input['merchant_SKU_filtered'], input['prepped_by_initials']))
SKU_df['prepped_by_initials'] = [dict_lookup[item] for item in key_list]

But, since there are multiple values of merchant_sku_filtered' the prepped_by_initials` column is getting messed up.

This is the output I'm getting.

Merchant SKU    merchant_sku_filtered   prepped_by_initials
2508406 -...Bit - 5pk   2508406 w
28046 - Ar...276 - 12pk 28046   w
2690493 -...ex Key Set  2690493 w
28046 - Ar...pls - 10pk 28046   w
2690493 -...ex Key Set  2690493 w
28046- Arr...aples 2 pk 28046   w

How shall I solve this problem? Any help will be appreciated!



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source