I am currently merging two dataframes with an inner join. However, after merging, I see all the rows are duplicated even when the columns that I merged upon contain the same values.

我目前正在使用内部联接合并两个数据帧。然而，在合并之后，我发现所有行都是重复的，即使我合并的列包含相同的值。

Specifically, I have the following code.

具体地说，我有以下代码。

merged_df = pd.merge(df1, df2, on=['email_address'], how='inner')

Here are the two dataframes and the results.

以下是两个数据帧和结果。

df1

DF1

          email_address    name   surname
0  [email protected]    john     smith
1  [email protected]    john     smith
2       [email protected]   elvis   presley

df2

DF2

          email_address    street  city
0  [email protected]   street1    NY
1  [email protected]   street1    NY
2       [email protected]   street2    LA

merged_df

合并的_df

          email_address    name   surname    street  city
0  [email protected]    john     smith   street1    NY
1  [email protected]    john     smith   street1    NY
2  [email protected]    john     smith   street1    NY
3  [email protected]    john     smith   street1    NY
4       [email protected]   elvis   presley   street2    LA
5       [email protected]   elvis   presley   street2    LA

My question is, shouldn't it be like this?

我的问题是，难道不应该是这样的吗？

This is how I would like my merged_df to be like.

这就是我希望我的合并后的df是什么样子。

          email_address    name   surname    street  city
0  [email protected]    john     smith   street1    NY
1  [email protected]    john     smith   street1    NY
2       [email protected]   elvis   presley   street2    LA

Are there any ways I can achieve this?

我有什么方法可以做到这一点吗？

list_2_nodups = list_2.drop_duplicates()
pd.merge(list_1 , list_2_nodups , on=['email_address'])

The duplicate rows are expected. Each john smith in list_1 matches with each john smith in list_2. I had to drop the duplicates in one of the lists. I chose list_2.

预计会出现重复行。List_1中的每个John Smith都与List_2中的每个John Smith匹配。我必须删除其中一个列表中的副本。我选择了List_2。

DO NOT drop duplicates BEFORE the merge, but after!

不要在合并之前删除重复项，而要在合并之后删除！

Best solution is do the merge and then drop the duplicates.

最好的解决方案是进行合并，然后删除副本。

In your case:

在您的案例中：

merged_df = pd.merge(df1, df2, on=['email_address'], how='inner')
merged_df.drop_duplicates(subset=['email_address'], keep='first', inplace=True, ignore_index=True)

To make sure you don't have duplicates in your keys, you can use the validate parameter:

为了确保您的密钥中没有重复项，您可以使用验证参数：

validate : str, optional

If specified, checks if merge is of specified
type.

• “one_to_one” or “1:1”: check if merge keys are unique in both
  left and right datasets.


• “one_to_many” or “1:m”: check if merge keys
  are unique in left dataset.


• “many_to_one” or “m:1”: check if merge
  keys are unique in right dataset.


• “many_to_many” or “m:m”: allowed,
  but does not result in checks.

In your case, you don't want any duplicate keys in the "right" dataframe df2, so you need to set validate to many_to_one.

在您的例子中，您不希望在“正确的”dataframe df2中有任何重复的键，因此需要将验证设置为MULTO_TO_ONE。

df1.merge(df2, on=['email_address'], validate='many_to_one')

If you have duplicate keys in df2, the function will return this error:

如果df2中有重复的密钥，该函数将返回以下错误：

MergeError: Merge keys are not unique in right record; not a many-to-one merge

To drop duplicate keys in df2 and do a merge you can use:

要删除df2中的重复键并执行合并，您可以使用：

keys = ['email_address']
df1.merge(df2.drop_duplicates(subset=keys), on=keys)

Make sure you set the subset parameter in drop_duplicates to the key columns you are using to merge. If you don't specify a subset drop_duplicates will compare all columns and if some of them have different values it will not drop those rows.

确保将DROP_DUPLICATES中的子集参数设置为要用于合并的键列。如果不指定子集，DROP_DUPLICATES将比较所有列，如果其中一些列具有不同的值，则不会删除这些行。