0
$\begingroup$

I have a data frame like this.

EMPLOYEE_ID NAME MANAGER_EMPLOYEE_ID
0 42 S 40
1 40 G NaN
2 T M 40
3 0c H 42

I want a separate column that outputs the Name of the Manager of every person. So for S manager would be G for G it would be empty (Non NaN, if possible) for M it would be G for H it would be S

Here is my attempt:

name = list(df3[df3["MANAGER_EMPLOYEE_ID"].isin(list(df3["EMPLOYEE_ID"]))]["MANAGER_EMPLOYEE_ID"].unique())
df3.loc[:, "MANAGER NAME"] = False
b_match_idx = df3[df3["MANAGER_EMPLOYEE_ID"].isin(name)].index
df3.at[np.array(b_match_idx),"MANAGER NAME"] = df3["NAME"]
df3

But the result is not what I want. Can someone point out the flaw in the code?

$\endgroup$

2 Answers 2

0
$\begingroup$

You can just do a join with the dataframe on itself by using the MANAGER_EMPLOYEE_ID and EMPLOYEE_ID columns like this:

(
    df3
    .merge(df3[["EMPLOYEE_ID", "NAME"]], how="left", left_on="MANAGER_EMPLOYEE_ID", right_on="EMPLOYEE_ID", suffixes=(None, "_y"))
    .rename({"NAME_y": "MANAGER_NAME"})
    .drop("EMPLOYEE_ID_y", axis=1)
)

Which will give the following resulting dataframe:

EMPLOYEE_ID NAME MANAGER_EMPLOYEE_ID MANAGER_NAME
42 S 40 G
40 G nan nan
T M 40 G
0c H 42 S
$\endgroup$
4
  • $\begingroup$ Thank you for the response, this is giving me an error KeyError: 'EMPLOYEE_ID $\endgroup$ Aug 23, 2021 at 15:28
  • $\begingroup$ Apologies, used the wrong column name, see the edit. $\endgroup$
    – Oxbowerce
    Aug 23, 2021 at 15:47
  • $\begingroup$ I believe this is the correct code? @Oxbowerce merged_df3.merge(merged_df3[["EMPLOYEE_ID", "NAME"]], left_on="MANAGER_EMPLOYEE_ID", right_on="EMPLOYEE_ID", how='left').rename({"NAME_y": "MANAGER_NAME"}).drop("EMPLOYEE_ID_y", axis=1) $\endgroup$ Aug 23, 2021 at 15:48
  • $\begingroup$ That's indeed correct. $\endgroup$
    – Oxbowerce
    Aug 23, 2021 at 15:48
0
$\begingroup$

You can try this as well:

import pandas as pd
import numpy as np

df = pd.DataFrame({'EMPLOYEE_ID':['42', '40', 'T', 'Oc'], 'NAME': ['S', 'G', 'M', 'H'], 'MANAGER_EMPLOYEE_ID': ['40', np.NaN, '40', '42']})

def get_mgr_name(x):
    try:
        return df.query('EMPLOYEE_ID == @x')['NAME'].values[0]
    except:
        return np.NaN        


df['Manager_Name'] = df['MANAGER_EMPLOYEE_ID'].apply(lambda x: get_mgr_name(x))
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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