使用唯一列名或根据特定条件合并或映射数据帧

Question

我需要将'CITY'名称从df2复制到df1

df2 = 
S_ID CITY
XY   AA
XY   AA
XY   AA
XZ   AA

ZX   BB
ZX   BB
ZX   BB
ZY   BB

AS   CC
AS   CC
AS   CC
AD   CC
df1 =

S_ID P_ID
XY   1,2,3
XZ   5
ZX   4,6,7
ZY   9
AS   3,4,9
AD   1

预期输出为

df1 = 
S_ID P_ID   CITY
XY   1,2,3  AA
XZ   5      AA
ZX   4,6,7  BB
ZY   9      BB
AS   3,4,9  CC
AD   1      CC

我试图与这个代码合并

df = df1.merge(df2[['S_ID', 'CITY']], on='S_ID', how='left')
print(df)
S_ID P_ID   CITY
XY   1,2,3  AA
XY   1,2,3  AA
XY   1,2,3  AA
XZ   5      AA
ZX   4,6,7  BB
ZX   4,6,7  BB
ZX   4,6,7  BB
ZY   9      BB
AS   3,4,9  CC
AS   3,4,9  CC
AS   3,4,9  CC
AD   1      CC

我得到的DataFramedf并不是我所期望的。

有人能帮忙吗？

Answer 1

df.drop_duplicates()您几乎做到了。