python - 合并具有多个值的 pandas 数据框

Question

我有 pandas 数据框，例如:

_data_orig = [
        [1, 3.2],
        [3, 3.9],
        [4, 1.2],
        [5, 2.2]
    ]
_columns1 = ["ID", "GPA"]

_data_new = [
    [1, "Bob"],
    [2, "Sam"],
    [3, "Jane"],
    [3, "Sanoj"]
]
_columns2 = ["ID", "Name"]


df_orig = pd.DataFrame(data=_data_orig, columns=_columns1)
df_new = pd.DataFrame(data=_data_new, columns=_columns2)

当我这样做时:

df_merge = pd.merge(df_orig, df_new, how='left')

我得到:

    ID  GPA Name
0   1   3.2 Bob
1   3   3.9 Jane
2   3   3.9 Sanoj
3   4   1.2 NaN
4   5   2.2 NaN

可以看到ID:3是重复的。我想要这种格式，以便 ID:3 不会从 df_orig 重复:

    ID  GPA Name    Name_1
0   1   3.2 Bob 
1   3   3.9 Jane    Sanoj
2   4   1.2 NaN 
4   5   2.2 NaN 

Answer 1

考虑使用 merge 对 groupby().cumcount 进行pivot:

df_new['IDcount'] = "Name_" + (df_new.groupby("ID").cumcount() + 1).astype(str)
df_wide = df_new.pivot(index="ID", columns="IDcount", values="Name").reset_index()

df_merge = pd.merge(df_orig, df_wide, on='ID', how='left')

#    ID  GPA Name_1 Name_2
# 0   1  3.2    Bob   None
# 1   3  3.9   Jane  Sanoj
# 2   4  1.2    NaN    NaN
# 3   5  2.2    NaN    NaN