如何用Pandas按自定义规则对比两个DataFrame差异？

To solve this problem without manual loops, we can leverage Pandas' merge and concat operations, along with grouping logic, to identify unmatched rows based on your specified rules. Here's a step-by-step breakdown:

Step 1: Prepare the DataFrames with Source Labels #

First, we'll add a from column to each DataFrame to track their origin:

import pandas as pd

# Create original DataFrames
data1 = {'one':['A', 'E', 'G'], 'two':['B', 'D', 'H'], 'three':['C', 'F', 'J']}
df1 = pd.DataFrame(data1)
df1['from'] = 'df1'

data2 = {'one':['C', 'F', 'P'], 'two':['B', 'D', 'R'], 'three':['A', 'E', 'C']}
df2 = pd.DataFrame(data2)
df2['from'] = 'df2'

Method 1: Using Merge to Identify Matches #

We can use pd.merge to find rows that meet your matching criteria (same two value, and one/three values swapped between the two DataFrames), then exclude these matched rows from the original data:

# Find matched rows by merging on swapped one/three values
matched = pd.merge(
    df1.reset_index(),
    df2.reset_index(),
    left_on=['two', 'one', 'three'],
    right_on=['two', 'three', 'one'],
    how='inner',
    suffixes=('_df1', '_df2')
)

# Extract indices of matched rows from both DataFrames
matched_df1_idx = matched['index_df1']
matched_df2_idx = matched['index_df2']

# Filter out matched rows
unmatched_df1 = df1[~df1.index.isin(matched_df1_idx)]
unmatched_df2 = df2[~df2.index.isin(matched_df2_idx)]

# Combine results
result = pd.concat([unmatched_df1, unmatched_df2]).reset_index(drop=True)
print(result)

Output:

one two three  from
0   G   H     J  df1
1   P   R     C  df2

Method 2: Using GroupBy to Find Unmatched Pairs #

Another approach is to combine both DataFrames first, then group by the two column to check if each row has a corresponding swapped one/three pair in the same group:

# Combine both DataFrames
combined = pd.concat([df1, df2]).reset_index(drop=True)

# Define a function to filter unmatched rows per group
def filter_unmatched(group):
    # Create a set of (one, three) pairs in the group
    pairs = set(zip(group['one'], group['three']))
    # Keep rows where the reversed pair (three, one) doesn't exist in the set
    mask = ~group.apply(lambda row: (row['three'], row['one']) in pairs, axis=1)
    return group[mask]

# Apply the function to each group and combine results
result = combined.groupby('two', group_keys=False).apply(filter_unmatched)
print(result)

This will produce the same output as Method 1. Both methods avoid manual loops and use Pandas' optimized built-in operations for better performance, especially with larger datasets.

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