Replace column values using mask and multiple mappings

user_14075924 0
Viewed 120

I have two dataframes. One is v_df and looks like this:

VENDOR_ID VENDOR_NAME
123 APPLE
456 GOOGLE
987 FACEBOOK

The other is n_df and looks like this:

Vendor_Name GL_Transaction_Description
AMEX HELLO 345
Not assigned BYE 456
Not assigned THANKS 123

I want to populate the 'Vendor_Name' column in n_df on the condition that the 'GL_Transaction_Description' on the same row contains any VENDOR_ID value from v_df. So the resulting n_df would be this:

Vendor_Name GL_Transaction_Description
AMEX HELLO 345
GOOGLE BYE 456
APPLE THANKS 123

So far I have written this code:

v_list = v_df['VENDOR_ID'].to_list()
mask_id = list(map((lambda x: any([(y in x) for y in v_list])), n_df['GL_Transaction_Description']))

n_df['Vendor_Name'].mask((mask_id), other = 'Solution Here', inplace=True)

I am just not able to grasp what to write in the 'other' condition of the final masking. Any ideas? (n_df has more than 100k rows, so the execution speed of the solution is of high importance)

pandas mask mapping python
1 Answers

Series.str.extract + map

i = v_df['VENDOR_ID'].astype(str)
m = v_df.set_index(i)['VENDOR_NAME']
s = n_df['GL_Transaction_Description'].str.extract(r'(\d+)', expand=False)

n_df['Vendor_Name'].update(s.map(m))

Explanations

Create a mapping series m from the v_df dataframe by setting the VENDOR_ID column as the index and selecting the VENDOR_NAME column

>>> m

VENDOR_ID
123       APPLE
456      GOOGLE
987    FACEBOOK
Name: VENDOR_NAME, dtype: object

Now extract the vendor id from the strings in GL_Transaction_Description column

>>> s

0    345
1    456
2    123
Name: GL_Transaction_Description, dtype: object

Map the extracted vendor id with the mapping series m and update the mapped values in Vendor_Name column

>>> n_df

  Vendor_Name GL_Transaction_Description
0        AMEX                  HELLO 345
1      GOOGLE                    BYE 456
2       APPLE                 THANKS 123
user_128331660
Related