python - 使用熊猫重新排列表格

Question

我有一张包含客户 ID 和电子邮件的表格。一些用户有多个电子邮件。该表如下所示：

| Customer  | Email          |
| ----------| -------------- |
| 1         | [e-mail]  |
| 2         | [e-mail] |
| 3         | [e-mail]  |
| 1         | [e-mail]|

我想做的是重新排列表格，使每个客户 ID 只有一行，并将辅助电子邮件添加为附加列。像这样的东西：

| Customer  | Email1         |Email2         |
| ----------| -------------- |---------------|
| 1         | [e-mail]  |[e-mail]
| 2         | [e-mail] |               |
| 3         | [e-mail]  |               |

使用熊猫来做到这一点的最佳方法是什么？我曾尝试使用 df.pivot 但这似乎对我不起作用。

标签： pythonpandas

Answer 1

你可以使用Series.duplicated()++pd.merge()DataFrame.drop_duplicates()

# We get the Customers with more than one email.
df_seconds_email = df[df['Customer'].duplicated()]

# We merge your original dataframe (I called it 'df') and the above one, suffixes param help us to get
# 'Email2' column, finally we drop duplicates taking into account 'Customer' column.
df = pd.merge(df, df_seconds_email, how='left', on=['Customer'], suffixes=('', '2')).drop_duplicates(subset='Customer')
print(df)

输出：

    Customer    Email          Email2
0      1    [e-mail]   [e-mail]
1      2    [e-mail]      NaN
2      3    [e-mail]       NaN