How to merge two datasets of different shapes with different column names in python

Question

I have a 2 datasets with two different shapes and column names and I need to merge them to fill the blanks in for all the NaNs. When I try and doesn't work and I am left with NaNs still.

Here is a sample:

DF1:

 ID      Mgr_name  Reports   EmpType      mgr_pos_num
 101      NaN       3       Manager         1234
 102      Brian     4       Manager         4567
 103      Mary      7       Manger          9876
 104      NaN       1       Manager         3456
 ...                                          
 201      Ashely    2       Manager         4291
 202      Blake     5       Manager         7215

DF2:

             emp_Name        emp_pos_num      
    0         Adam              5678
    1         Amanda            1122
    2         Brian             4567
    3         Chris             7654
    4         Dave              5564
    5         John              1234
    6         Lisa              3346
    7         Mary              9876
    8         Sarah             3456
    ....  
    210       Greg              0123
    211       Blake             7215

DF1 shows most the info and I skipped most the lines to show that their are many rows in this dataset. DF2 has all the names and more because it deals with all employees but does have the position number. Which is unique to an ID + Name

Here is a Desired output:

  ID       Name        Reports        EmpType       emp_name    emp_pos_num
  101      NaN           3               MGR          John         1234
  102      Brian         4               MGR          Brian        4567
  103      Mary          7               MGR          Mary         9876
  104      NaN           1               MGR          Sarah        3456
  ...
  201      Ashely        2               MGR          Ashely       4291
  202      Blake         5               MGR          Blake        7215

I know I just wanted to show I would be using the other column for naming but would at the end end up deleting the name column sorry for the confusion. — Coding_Nubie, Apr 28 '23 at 12:45

score 2 · Accepted Answer · answered Apr 28 '23 at 12:37

2

You could left join DF2 onto DF1, assuming you want to join on mgr_pos_num and emp_pos_num, using

DF1 = pd.merge(DF1, DF2, how='left', left_on='mgr_pos_num', right_on='emp_pos_num')

answered Apr 28 '23 at 12:37

renzo21

96
3

How to merge two datasets of different shapes with different column names in python

1 Answers1