'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
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
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 |
|---|


