1

I have a dataset that is employee's history. What I want to do is map the managers ID to the job position being held by that manager.

Here is a sample of what I have:

 ID      Name     Job_Title        ManagerID       ManagerName
 101     Adam     Sales Rep           102             Ben
 102     Ben      Sales Supervisor    105             Chris 
 103     David    Sales Rep           102             Ben 
 104     Paul     Tech Manager        107             Kenny 
 105     Chris    Sales Manager       110             Hank

What I want is to make a new column that maps ManagerID to the job they hold.

Desired Output:

 ManagerID     Name     Mgr_Title
   102         Ben      Sales Supervisor
   104         Paul     Tech Manager 
   105         Chris    Sales Manager 
   110         Hank     Sales Director

I have tried getting it by using this as my code

job_table = df[['ManagerID', 'Job_Title']
mgr_job_dict = dict(job_table.values)
df['Mgr_Title'] = df['ManagerName'].map(mgr_job_dict)

What this gets me is just running through the top to bottom not actually selecting just manager jobs. Any suggestions?

Coding_Nubie
  • 415
  • 8
  • 2
    Assuming you input is only the first DataFrame, where would you find the `Sales Director` info? – mozway Apr 14 '23 at 12:03
  • @mozway I wanted to show continuation of data. The real data is about 150,000+ rows – Coding_Nubie Apr 14 '23 at 12:06
  • OK, then `df['Mgr_Title'] = df['ManagerName'].map(df.set_index('Name')['Job_Title'])`, or maybe better `df['Mgr_Title'] = df['ManagerID'].map(df.set_index('ID')['Job_Title'])` as Names can be ambiguous. – mozway Apr 14 '23 at 12:08
  • Don't forget to use `drop_duplicates`: `df[['ManagerID', 'ManagerName']].assign(Mgr_Title=df['ManagerName'].map(df.set_index('Name')['Job_Title'])).drop_duplicates('ManagerID')` – Corralien Apr 14 '23 at 12:11
  • @mozway I get a InvalidIndexError: Reindexing only valid with uniquely valued Index objects – Coding_Nubie Apr 14 '23 at 12:17
  • @Corralien I get that same error when I drop duplicates – Coding_Nubie Apr 14 '23 at 12:18
  • @Coding_Nubie then please provide a fully minimal reproducible example (that triggers this issue) – mozway Apr 14 '23 at 12:19
  • @Coding_Nubie. In this case use `merge` rather than `map`. – Corralien Apr 14 '23 at 12:22
  • @mozway I am not sure I know what you are asking for sorry. – Coding_Nubie Apr 14 '23 at 12:34
  • @mozway could I create a for loop with a nested elif statment that iterates through the ID column and when finds the manager IDs then pull the Job title they hold as well? and append the results to a new column? – Coding_Nubie Apr 14 '23 at 12:39

0 Answers0