使用条件将Panda Dataframe中的列值替换为另一Panda Dataframe中的列

Question

我有下面的3只熊猫数据。我想替换公司和部门列的ID从他们各自的公司和部门的数据。

pd_staff:

id    name      company         division
P001  John      Sunrise         Headquarter
P002  Jane      Falcon Digital  Research & Development
P003  Joe       Ashford         Finance
P004  Adam      Falcon Digital  Sales
P004  Barbara   Sunrise         Human Resource


pd_company:

id  name
1   Sunrise
2   Falcon Digital
3   Ashford


pd_division:

id  name
1   Headquarter
2   Research & Development
3   Finance
4   Sales
5   Human Resource

这是我正在努力产生的最终结果。

id    name      company   division
P001  John      1         1
P002  Jane      2         2
P003  Joe       3         3
P004  Adam      2         4
P004  Barbara   1         5

我尝试使用此代码将员工和公司结合起来

pd_staff.loc[pd_staff['company'].isin(pd_company['name']), 'company'] = pd_company.loc[pd_company['name'].isin(pd_staff['company']), 'id']

产

id    name      company   
P001  John      1.0        
P002  Jane      NaN         
P003  Joe       NaN         
P004  Adam      NaN       
P004  Barbara   NaN     

Answer 1

你可以：

pd_staff['company'] = pd_staff['company'].map(pd_company.set_index('name')['id'])
pd_staff['division'] = pd_staff['division'].map(pd_division.set_index('name')['id'])

打印(Pd_staff)：

     id     name  company  division
0  P001     John        1         1
1  P002     Jane        2         2
2  P003      Joe        3         3
3  P004     Adam        2         4
4  P004  Barbara        1         5

Answer 2

这将达到预期的效果。

df_merge = df.merge(df2, how = 'inner', right_on = 'name', left_on = 'company', suffixes=('', '_y'))
df_merge = df_merge.merge(df3, how = 'inner', left_on = 'division', right_on = 'name', suffixes=('', '_z'))
df_merge = df_merge[['id', 'name', 'id_y', 'id_z']]
df_merge.columns = ['id', 'name', 'company', 'division']
df_merge.sort_values('id')

Answer 3

首先，让我们修改一下df公司和df部门。

df2.rename(columns={'name':'company'},inplace=True)
df3.rename(columns={'name':'division'},inplace=True)

然后

df1=df1.merge(df2,on='company',how='left').merge(df3,on='division',how='left')
df1=df1[['id_x','name','id_y','id']]
df1.rename(columns={'id_x':'id','id_y':'company','id':'division'},inplace=True)