0

I know there is a lot about merging on here, I have tried countless times and have not gotten the results I need I have 2 datasets and what want to merge them on job_pos_num to fill the NaNs.

DF1

ID     Name      job_title         job_pos_num    manager_id      manager_name
1     Adam       Cashier              121            12             Michael 
1     Adam       Lead Cashier         122            12             Michael 
2     Ben        Stocker              NaN             4             Victor
2     Ben        Backroom Lead        NaN             4             Victor 
2     Ben        Backroom Supervisor  199             4             Victor 
3     Cathy      Technician           145             17            Alex
3     Cathy      Consultant           NaN             11            Bailey

DF2

 job_pos_num       job_title 
     100          Store Manager
     105          Backroom Manager 
     107          Front Side Manager 
     121          Cashier 
     122          Lead Cashier 
     139          Key Holder 
     142          Apprentice     
     145          Technician 
     165          Stocker 
     187          Backroom Lead 
     189          Consultant 
     199          Backroom Supervisor

Desired output:

  ID       Name   job_title          job_pos_num    manager_id    manager_name   
  1        Adam    Cashier              121            12           Michael         
  1        Adam    Lead Cashier         122            12           Michael     
  2        Ben     Stocker              165             4           Victor          
  2        Ben     Backroom Lead        187             4           Victor     
  2        Ben     Backroom Supervisor  199             4           Victor      
  3        Cathy   Technician           145            17           Alex         
  3        Cathy   Consultant           189            11           Bailey

code used:

  df1 = pd.merge(df1,df2, how= 'left', left_on='job_pos_num', right_on='job_pos_num')
Coding_Nubie
  • 415
  • 8
  • 1
    `df1['job_title'].fillna(df1['job_title'].map(df2.set_index('job_title')['job_pos_num']))` – mozway May 03 '23 at 12:21

0 Answers0