Python中定位DataFrame互逆对行位置的优化实现及重复结果处理方案

Hey there! Let's tackle your problem step by step—refactoring that reciprocal pair locator to be cleaner, more efficient, and free of duplicate entries.

First: A Far More Efficient Approach Using Pandas Native Tools #

Your current implementations rely on nested loops or itertools.product (both O(n²) operations), and direct iloc calls in loops are slow for larger DataFrames. Instead, we can leverage pandas' vectorized operations and merging to make this way cleaner and faster.

Here's a streamlined function that does exactly what you need:

def reciprocals_locator(df):
    # Create a temporary copy to avoid modifying the original DataFrame
    temp_df = df.copy()
    
    # Add columns for the original pair and its reverse (as tuples for easy matching)
    temp_df['pair'] = list(zip(temp_df['y'], temp_df['x']))
    temp_df['reversed_pair'] = list(zip(temp_df['x'], temp_df['y']))
    
    # Merge the DataFrame with itself to find reciprocal pairs
    # We use suffixes to distinguish between the two matched rows
    merged = temp_df.merge(
        temp_df,
        left_on='pair',
        right_on='reversed_pair',
        suffixes=('_a', '_b')
    )
    
    # Filter out duplicate pairs (e.g., (1,5) and (5,1)) by keeping only pairs where index_a < index_b
    valid_pairs = merged[merged.index_a < merged.index_b]
    
    # Extract and return the list of index pairs
    return valid_pairs[['index_a', 'index_b']].values.tolist()

Second: Fixing the Duplicate Entries Problem #

The root issue with your original code is that it checks every possible combination of rows, including both (row_y, row_x) and (row_x, row_y). The merge approach above solves this automatically with the index_a < index_b filter—this ensures each reciprocal pair is only returned once, no duplicates.

Let's Test This With Your Scenario #

Plug the function into your existing test code to see it in action:

import itertools as it
import pandas as pd
import random as rd

# Original dataset
sourcelst = ['a','b','c','d','e']
pairs = [list(perm) for perm in it.permutations(sourcelst,2)]
df = pd.DataFrame(pairs, columns=['y','x'])

# Simulate processing: drop random rows
drop_rows = [rd.randint(0, len(pairs)-1) for _ in range(2)]
df.drop(index=drop_rows, inplace=True)
df.reset_index(inplace=True, drop=True)

# Run our refactored function
print(reciprocals_locator(df))

This will output a list of unique index pairs (e.g., [[0, 3], [2, 5]]) where each pair corresponds to a reciprocal (a,b) and (b,a) in your DataFrame.

Why This Is Better Than Your Original Implementations #

• Efficiency: Vectorized pandas operations run in O(n log n) time (vs O(n²) for loops), making it drastically faster for larger datasets.
• Readability: The code clearly expresses intent—creating pair columns, merging to find matches, filtering duplicates—no confusing nested logic.
• No Duplicates: The index filter ensures you only get each reciprocal pair once, no need for messy post-processing.
• Non-destructive: We use a temporary copy of the DataFrame, so your original data stays untouched.

Bonus: Alternative for Smaller DataFrames #

If you're working with a tiny dataset and prefer a more manual approach, you can use a set to track seen pairs:

def reciprocals_locator_small(df):
    seen_pairs = set()
    reciprocal_indices = []
    
    for idx, row in df.iterrows():
        current_pair = (row['y'], row['x'])
        reversed_pair = (row['x'], row['y'])
        
        if reversed_pair in seen_pairs:
            # Find the index of the reversed pair
            reversed_idx = df[(df['y'] == reversed_pair[0]) & (df['x'] == reversed_pair[1])].index[0]
            # Add the pair in sorted order to avoid duplicates
            reciprocal_indices.append([min(idx, reversed_idx), max(idx, reversed_idx)])
        
        seen_pairs.add(current_pair)
    
    # Remove any accidental duplicates (shouldn't happen with proper logic)
    return list(set(tuple(p) for p in reciprocal_indices))

Note: This is O(n²) in the worst case, so stick to the merge approach for larger datasets.

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