1
$\begingroup$

Given: I have Pandas Dataframe as shown below

| Employee_ID | Manager_ID |
|:-----------:|:----------:|
| E068        | E067       |
| E071        | E067       |
| E229        | E069       |
| E248        | E144       |
| E226        | E223       |
| E236        | E241       |
| E066        | E001       |
| E067        | E001       |
| E144        | E001       |
| E223        | E001       |
| E069        | E066       |

Problem Statement:

This problem is to identify the Head of Manager by using Employee and their Manager data.

About:

We have an Employee ID and their Manager ID. Please note that Manager ID are from Employee ID. Since each manager has one Manager above their level.

STEPS:

  1. First, we'll take all UNIQUE ID in Manager ID column.
  2. Then for each ID from Manager ID column, we will look for their respective Manager ID(Manager)
  3. Then we will create a new column say Level 1 we will put manager for each Manager ID on their respective cell.
  4. Similarly, we will repeat the above 3 processes again till there is no Manager ID for that particular ID.
  5. This way we can identify the Head of Manager.

I am able to solve the problem in EXCEL. By using =IFERROR(VLOOKUP(C2,$A:$B,2,FALSE),"")

But this approach lead me to create new column in excel for each level hierarchy. And putting the formula on first cell of that particular column and then dragging the result for each manager

But incase of big companies there would be n no. of level of hierarchy. So creating the new column in excel for each level of hierarchy would be time consuming task. Hence, I am looking for an optimal solution.

Expected Output:

| Employee ID | Manager ID | Level 1 | Level 2 | Head of Manager |
|:-----------:|:----------:|---------|---------|-----------------|
| E068        | E067       | E001    |         | E001            |
| E071        | E067       | E001    |         | E001            |
| E229        | E069       | E066    | E001    | E001            |
| E248        | E144       | E001    |         | E001            |
| E226        | E223       | E001    |         | E001            |
| E236        | E241       |         |         | E241            |
| E066        | E001       |         |         | E001            |
| E067        | E001       |         |         | E001            |
| E144        | E001       |         |         | E001            |
| E223        | E001       |         |         | E001            |

The Employee ID column contain UNIQUE ID while Manager ID contain DUPLICATES ID.

Thank you for your time and consideration.

$\endgroup$
0
$\begingroup$

Let's call the dataframe "manager" with column names "Emp" and "Man". First you get the people who are always head mananagers:

head_man = set(manager["Man"]) - set(manager["Emp"])

This returns

{'E001', 'E241'}

and then use the following function to find the head managers of the rest:

def manager_of(emp):
  if emp in head_man:
    return emp
  else:
    return manager_of(manager[manager["Emp"] == emp]["Man"].values[0])

Complete Code:

import pandas as pd

head_man = set(hrms_data["Manager ID"]) - set(hrms_data["Employee ID"])

def manager_of(emp):
  if emp in head_man:
    return emp
  else:
    return manager_of(hrms_data[hrms_data["Employee ID"] == emp]["Manager ID"].values[0])

for index, row in hrms_data.iterrows():
  hrms_data.loc[index,"Head of Manager"] = manager_of(row["Employee ID"])

Edit: hrms_data["Head of Manager"] = hrms_data["Employee ID"].map(manager_of) instead of the for loop is better.

$\endgroup$
  • $\begingroup$ Thanks a lot! I have DataFrame named hrms_data. With column name as hrms_data['Employee ID'] and hrms_data['Manager ID']. Can you help me to implement this code. $\endgroup$ – Maqsud Inamdar Nov 9 at 12:28
  • $\begingroup$ Check the edit. That should do it. $\endgroup$ – serali Nov 9 at 13:05
  • $\begingroup$ Thanks a lot! @serali $\endgroup$ – Maqsud Inamdar Nov 9 at 13:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.