I need to populate a column based on a match in a different column.

DataFrame:

df = pd.DataFrame(np.array([[1000, 'Jerry', 'BR1','BR1'], 
                             [1001, 'Sal', 'BR2', 'BR1'], 
                             [1002, 'Buck', 'BR3', 'BR2'],
                             [1003, 'Perry','BR4','BR1']]),
                   columns=['ID', 'Name', 'Branch', 'Member of'])

I need to create a "bosses" column.

• A boss is defined as the Name returned after matching Member of to some field in column Branch.

I can do it this way:

df_bosses = df[['Name', 'Branch']].copy()
df_bosses.rename(columns={'Name':'Boss'},inplace=True)
df.merge(df_bosses, how="left", left_on="Member of", right_on="Branch")

This gets me where I want to be -- just need to rename a column and drop another.

Is there a more efficient way?

CodePudding user response：

Try this:

df['Boss'] = df['Member of'].map(df.set_index('Branch')['Name'])

Output:

>>> df
     ID   Name Branch Member of   Boss
0  1000  Jerry    BR1       BR1  Jerry
1  1001    Sal    BR2       BR1  Jerry
2  1002   Buck    BR3       BR2    Sal
3  1003  Perry    BR4       BR1  Jerry

CodePudding user response：

I will do replace

df['new'] = df['Member of'].replace(df.set_index('Branch')['Name'])
df
     ID   Name Branch Member of    new
0  1000  Jerry    BR1       BR1  Jerry
1  1001    Sal    BR2       BR1  Jerry
2  1002   Buck    BR3       BR2    Sal
3  1003  Perry    BR4       BR1  Jerry