How to update one dataframe column based on other dataframe value in pandas?

Question

Hi I am trying to update df2 based on the value of df1 but the value is not updating correctly. Please help me what I am doing wrong?

Example-

df1:

                            job                                              e_msg
0  f_maf_crm_customer_request_global_jgvcc  permission denied for relation f_maf_custome

df2:

                 master_job                                          error_msg
0    JGCC_Genesys_Conversations_Aggregates
1  f_maf_crm_customer_request_global_jgvcc  [Errno 2] No such file or directory: '/medaff/

Just want to df2 'error_msg with the value of df1 e_msg.

I have written below code-

df2.loc[(df1['job'].isin(df2['master_job'])) & (df1['e_msg'] != '') & (df2['error_msg'] != ''), ['error_msg']] = df1['e_msg']

Expected Output: df2:

                 master_job                                          error_msg
0    JGCC_Genesys_Conversations_Aggregates
1  f_maf_crm_customer_request_global_jgvcc  permission denied for relation f_maf_custome

Answer 1

Use Series.isin with df.loc:

In [43]: msg = df1[df1.job.isin(df2.master_job)]['e_msg'].squeeze()
In [45]: df2.loc[df2[df2.master_job.isin(df1.job)].index, 'error_msg'] = msg

In [46]: df2
Out[46]: 
                                master_job                                     error_msg
0    JGCC_Genesys_Conversations_Aggregates                                              
1  f_maf_crm_customer_request_global_jgvcc  permission denied for relation f_maf_custome

OR

Use df.merge:

In [54]: df1.columns = df2.columns
In [57]: res = df2.merge(df1, on='master_job', how='left').drop('error_msg_x', 1).rename(columns={'error_msg_y':'error_msg'})

In [58]: res
Out[58]: 
                                master_job                                     error_msg
0    JGCC_Genesys_Conversations_Aggregates                                           NaN
1  f_maf_crm_customer_request_global_jgvcc  permission denied for relation f_maf_custome