Use mapping between two columns to create chain in pandas dataframe

user_7612040 0
Viewed 665

Here is a test dataframe. I want to use the relationship between EmpID and MgrID to further map the manager of MgrID in a new column.

Test_df = pd.DataFrame({'EmpID':['1','2','3','4','5','6','7','8','9','10'], 
                    'MgrID':['4','4','4','6','8','8','10','10','10','12']})
Test_df

If I create a dictionary for the initial relationship, I will be able to create the first link of the chain, but I affraid I need to loop through each of the new columns to create a new one.

ID_Dict = {'1':'4',
           '2':'4',
           '3':'4',
           '4':'6',
           '5':'8',
           '6':'8',
           '7':'10',
           '8':'10',
           '9':'10',
          '10':'12'}
Test_df['MgrID_L2'] = Test_df['MgrID'].map(ID_Dict)
Test_df

What is the most efficient way to do this? Thank you!

pandas dataframe python
1 Answers

Here's a way with a simple while loop. Note I changed the name of MgrID to MgrID_1

Test_df = pd.DataFrame({'EmpID':['1','2','3','4','5','6','7','8','9','10'], 
                        'MgrID_1':['4','4','4','6','8','8','10','10','10','12']})

d = Test_df.set_index('EmpID').MgrID_1.to_dict()

s = 2
while s:
    Test_df['MgrID_'+str(s)] =  Test_df['MgrID_'+str(s-1)].map(d)
    if Test_df['MgrID_'+str(s)].isnull().all():
        Test_df = Test_df.drop(columns='MgrID_'+str(s))
        s = 0
    else:
        s+=1

Ouptut: Test_df

  EmpID MgrID_1 MgrID_2 MgrID_3 MgrID_4 MgrID_5
0     1       4       6       8      10      12
1     2       4       6       8      10      12
2     3       4       6       8      10      12
3     4       6       8      10      12     NaN
4     5       8      10      12     NaN     NaN
5     6       8      10      12     NaN     NaN
6     7      10      12     NaN     NaN     NaN
7     8      10      12     NaN     NaN     NaN
8     9      10      12     NaN     NaN     NaN
9    10      12     NaN     NaN     NaN     NaN
user_43333590
Related