I have these two dfs. In the first dataframes, some rows are missing "merchant_type" codes. Though from the dataframe one can see that the "merchant_type_desc" with the missing merchant_type codes can also be deduced from the df

df1

df2

The second dataframe has the distinct merchant_type codes and their corresponding descriptions. Note the "NEW MCC CODE" merchant type description.

Merging the two dfs on merchant_type_desc column would loose the different/distinct merchant_types of the "NEW MCC CODE"

To lookup the missing merchant_type codes in df1 In excel I would have used an IF Function to first check the value of the "Merchant_type_description" if it equals "NEW MCC CODE" I would retain the present code, otherwise I would do vlookup on df2 and return the code from it. How do I achive this in Pandas dataframe.

Expected Output:

CodePudding user response：

you can use

df3 = df2.loc[df2.merchant_type_desc == 'NEW MCC CODE']

to get just the rows with merchant type NEW MCC CODE

then gather all rows

df = pd.concat([df1, df2, df3])

remove the blanks, this will depend on exactly what the blanks are, in this case I've assumed they are empty strings

df = df.loc[~df.merchant_type == '']

then remove duplicate rows

df = df.drop_duplicates()

CodePudding user response：

You can use this if you prefer a one line solution:

df1.loc[df1.merchant_type.isna(), 'merchant_type']=df1.merge(df2[df2.merchant_type_desc!="NEW MCC CODE"], how='left', on='merchant_type_desc').loc[df1.merchant_type.isna(), 'merchant_type_y']