如何在含重复值的列上合并两个DataFrame并输出无重复行结果

Got it, I see exactly what's happening here—when you use pd.merge(df1, df2, how="inner") directly on the occupation column, you're getting a Cartesian product because multiple rows in df2 share the same occupation value. What you actually want is a one-to-one match where each row in df1 pairs with the corresponding row in df2 for the same occupation, in the order they appear.

The Root Cause #

Looking at your data:

• df1 has 2 Administrators, 3 Engineers, and 1 Account
• df2 has exactly the same counts for each occupation (2 Administrators, 3 Engineers, 1 Account)

So we just need a way to "label" each row within its occupation group, then use that label plus the occupation as our merge key.

The Solution: Add Group-Level Indexes #

We can use groupby().cumcount() to generate an index number for each row within its occupation group. This index will let us match rows one-to-one instead of creating all possible combinations.

Here's the step-by-step code:

import pandas as pd

# Your original DataFrames
df1 = pd.DataFrame({
    "company": ["A","B","C","D","E","F"],
    "occupation": ["Administrator","Engineer","Engineer","Account","Administrator","Engineer"]
})
df2 = pd.DataFrame({
    "occupation": ["Account","Engineer","Administrator","Administrator","Engineer","Engineer"],
    "description": ["balance","database","chores","calling","frontend","backendend"]
})

# Add a group-specific index to both DataFrames
df1['group_index'] = df1.groupby('occupation').cumcount()
df2['group_index'] = df2.groupby('occupation').cumcount()

# Merge using both occupation and group_index to avoid duplicates
merged_df = pd.merge(df1, df2, on=['occupation', 'group_index'], how='inner')

# Drop the temporary group_index column since we don't need it anymore
final_df = merged_df.drop('group_index', axis=1)

# Check the result
display(final_df)

What This Does #

• cumcount() assigns a sequential number starting at 0 to each row within its occupation group. For example, the two Administrators in df1 get 0 and 1, same as in df2.
• Merging on both occupation and group_index ensures each row only matches its exact counterpart in the other DataFrame, eliminating the Cartesian product duplicates.

Running this code will give you exactly the result you're expecting, matching your df3 perfectly!

内容的提问来源于stack exchange，提问作者theabc50111