Python实现右排他连接：DataFrame按非共有cluster键合并

Got it, let's work through this problem. The core issue here is that you don't want matches where clusters are identical—instead, you need every row from df1 paired with every row from df2 where their cluster values differ. Your original merge approach only looks for shared (or missing) clusters, which doesn't meet this requirement. Here's how to fix it:

Step 1: Perform a Cross Join of Both DataFrames #

First, we'll create a temporary dummy column in both DataFrames to enable a cross join (this pairs every row in df1 with every row in df2):

import pandas as pd

# Add a temporary key column to both DataFrames
df1['temp_key'] = 1
df2['temp_key'] = 1

# Execute cross join to combine all rows from df1 and df2
cross_combined = pd.merge(df1, df2, on='temp_key')

Step 2: Filter for Non-Matching Clusters #

Next, we'll keep only the rows where the cluster from df1 does not match the cluster from df2:

# Filter out rows with identical cluster values
filtered_result = cross_combined[cross_combined['cluster_x'] != cross_combined['cluster_y']]

Step 3: Clean Up Columns to Match Your Expected Output #

We'll rename columns to match your desired format and remove the temporary key:

# Rename columns for clarity (adjust if you strictly need duplicate 'cluster' names)
filtered_result = filtered_result.rename(columns={
    'cluster_x': 'cluster',
    'cluster_y': 'cluster_matched',
    'SMILES': 'SMILES_z'
})

# Reorder columns to match your expected output structure
final_result = filtered_result[['cluster', 'SMILES_x', 'SMILES_y', 'cluster_matched', 'SMILES_z']]

# Optional: If you really want duplicate 'cluster' columns (not recommended for future code clarity)
# final_result = filtered_result.rename(columns={'cluster_y': 'cluster'})
# final_result = final_result[['cluster', 'SMILES_x', 'SMILES_y', 'cluster', 'SMILES_z']]

How This Works With Your Sample Data #

Using your provided df1 and df2:

• The row with cluster=381.0 in df1 will pair only with df2 rows where cluster is 721.0 and 840.0 (skipping the two 381.0 rows)
• The row with cluster=548.0 in df1 will pair with all rows in df2 (since 548.0 doesn't match any cluster in df2)

This produces exactly the output structure you described.

Why Your Original Code Didn't Work #

Your original merge used on='cluster', which only matches rows where cluster values are equal. Filtering for right_only then gives you rows from df2 that have clusters not present in df1—but this isn't the same as pairing every df1 row with every df2 row that has a different cluster.

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