如何按RecordID分组Pandas DataFrame并执行组合匹配代码

Approach #

1. Validate Group Integrity: Confirm each RecordID group has exactly one unique Quantity Difference value to avoid ambiguous matching.
2. Combination Matching Function: For each group, extract the target value and security amounts, then use itertools.combinations to find all subsets whose sum (rounded to 3 decimals) matches the rounded target.
3. Map Results to Rows: Apply the matching function to each group, then assign the result to every row in the original group to create the Combinations column.

Code Implementation #

import pandas as pd
import itertools

def find_matching_combinations(group):
    # Extract unique target value for the group
    target = group['Quantity Difference'].iloc[0]
    target_rounded = round(target, 3)
    
    # Get list of security amounts from the group
    security_amounts = group['securityAmount'].tolist()
    
    valid_combinations = []
    
    # Check all possible combination lengths (1 to number of amounts)
    for combo_length in range(1, len(security_amounts) + 1):
        for combo in itertools.combinations(security_amounts, combo_length):
            combo_sum = round(sum(combo), 3)
            if combo_sum == target_rounded:
                valid_combinations.append(str(combo))
    
    # Assign results to all rows in the group
    group['Combinations'] = ', '.join(valid_combinations) if valid_combinations else 'No match'
    return group

# Example usage
# Assume your input DataFrame is named df
# First validate groups have unique Quantity Difference values
group_validation = df.groupby('RecordID')['Quantity Difference'].nunique()
invalid_groups = group_validation[group_validation > 1].index
if invalid_groups.any():
    raise ValueError(f"Groups {invalid_groups.tolist()} contain multiple Quantity Difference values")

# Apply the function to each group and reset index
result_df = df.groupby('RecordID').apply(find_matching_combinations).reset_index(drop=True)

Explanation #

• Validation Step: Ensures each group has a single target value, preventing inconsistent matching results.
• Combination Check: Iterates over all subset sizes to find valid matches, using rounding to 3 decimals to handle floating-point precision errors.
• Result Mapping: The same combination result is applied to every row in the group, so all entries with the same RecordID share the same Combinations value.

Example Output #

For a sample input:

The resulting result_df will look like:

Notes #

• To store combinations as lists instead of strings, replace str(combo) with list(combo) (note that list values in DataFrame cells may be less readable in some tools).
• For large groups, this brute-force combination check can be slow. For performance improvements, consider using dynamic programming for subset sum problems.

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