python - 如何将一个 pandas 数据帧列中的字符串连接和匹配到另一个数据帧

Question

我正在处理两个 pandas 数据帧，并希望匹配来自 df1 的字符串(字符串是通过连接三列派生的)。如何将生成的字符串与 df2 中的列相匹配，如果存在，那么我想提取匹配单元格中的剩余字符串。

df1

id c_id code1  code2  status
1    9    ceo    ceo    ?
2    9    ned    ned
3    9    ned    chair
4    2    ed     ned
5    2    ned    ed
6    9    chair  ceo
7    2    chair  chair

df2

c_id   gender  role        ex                code
2      male     chair   ,ceo,ned,          2.male.chair.,ceo,ned,
2      male     cahir   ,chair,ned,        2.male.ned.,chair,ned,  
2      female   ed      ,ned,              2.female.ed.,ned,
6      female   ed      ,ceo,chair,        6.female.ed.,ceo,chair,
6      male     chair   ,ed,ceo,           6.male.chair.,ed,ceo,
9      female   ed      ,ceo,chair,        9.female.ed.,ceo,chair,
9      female   chair   ,ceo,ned,          9.female.chair.,ceo,ned,
9      male     ceo     ,chair,(in ft10),  9.male.ceo.,chair,(in ft10),

目标是通过连接 ['c_id', 'code1', 'code2'] 与 df2 匹配来填充列 df1['status'] ['代码']

连接:df1['string'] = df1.apply(lambda x: f'{v["c_id"]}.female.{v["code1"]}.,{v["code2""]},', axis=1)

打印 df1['字符串']

0  9.female.ceo.,ceo,
1  9.female.ned.,ned,
2  9.female.ned.,chair,
3  2.female.ed.,ned,
4  2.female.ned.,ed,
5  9.female.chair.,ceo,
6  2.female.chair.,chair,

当前解决方案:df1['c_id'].map(df2[df2['gender'].str.contains('female')]['ex'])

如果 df['code'] 上有匹配项，df2['ex'] 值基本上就是我需要的值

预期结果: df1

id c_id code1  code2      status
1    9    ceo    ceo    
2    9    ned    ned
3    9    ned    chair
4    2    ed     ned     ,ned,
5    2    ned    ed
6    9    chair  ceo     ,ceo,ned,
7    2    chair  chair

我在这方面遇到了严重的障碍，因此非常感谢任何建议或帮助。

Answer 1

您可以根据您的情况使用所需的列进行合并

df1.merge(df2[['c_id','role', 'ex']], left_on = ['c_id', 'code1'], right_on = ['c_id','role'], how = 'left').fillna('')

    id  c_id    code1   code2   role    ex
0   1   9       ceo     ceo     ceo     ,chair,(in ft10),
1   2   9       ned     ned     
2   3   9       ned     chair       
3   4   2       ed      ned     ed      ,ned,
4   5   2       ned     ed      
5   6   9       chair   ceo     chair   ,ceo,ned,
6   7   2       chair   chair   chair   ,ceo,ned,