使用FuzzyWuzzy进行模糊匹配后，如何结合多列实现客户记录去重并关联同一客户的CustomerID

Hey there! Let's walk through how to take your fuzzy matching results and combine them with field completeness to group duplicate customer records into unified lists of CustomerIDs. Here's a practical, step-by-step plan tailored to your dataset:

1. Structure Your Fuzzy Match Results #

First, make sure you have a clean dataset of match pairs with their token_sort_ratio scores. If you're using pandas, you might build this like so after computing pairwise matches:

import pandas as pd
from fuzzywuzzy import fuzz

# Assume your original data is stored in a dataframe called customer_records
match_pairs = []
for i in range(len(customer_records)):
    for j in range(i+1, len(customer_records)):
        # Combine name fields for fuzzy matching (adjust fields based on your priority)
        name_i = f"{customer_records.iloc[i]['Firstname']} {customer_records.iloc[i]['lastname']} {customer_records.iloc[i]['middlename']}".strip()
        name_j = f"{customer_records.iloc[j]['Firstname']} {customer_records.iloc[j]['lastname']} {customer_records.iloc[j]['middlename']}".strip()
        match_score = fuzz.token_sort_ratio(name_i, name_j)
        if match_score >= 80: # Use your chosen threshold here
            match_pairs.append({
                "id1": customer_records.iloc[i]['CustomerID'],
                "id2": customer_records.iloc[j]['CustomerID'],
                "score": match_score
            })

match_df = pd.DataFrame(match_pairs)

This gives you all record pairs that are likely duplicates based on name similarity.

2. Group Records Using Union-Find (Disjoint Set Union) #

To cluster all related records into a single group (even if they don't directly match each other but share a common match), use the Union-Find data structure. It’s perfect for efficiently merging groups as you process match pairs:

class UnionFind:
    def __init__(self, customer_ids):
        self.parent = {cid: cid for cid in customer_ids}
    
    def find(self, x):
        if self.parent[x] != x:
            self.parent[x] = self.find(self.parent[x])
        return self.parent[x]
    
    def union(self, x, y):
        x_root = self.find(x)
        y_root = self.find(y)
        if x_root != y_root:
            self.parent[y_root] = x_root

# Initialize with all unique CustomerIDs from your dataset
uf = UnionFind(customer_records['CustomerID'].unique())

# Merge all matching pairs into groups
for _, row in match_df.iterrows():
    uf.union(row['id1'], row['id2'])

# Now group all CustomerIDs by their root representative
customer_groups = {}
for cid in customer_records['CustomerID']:
    root = uf.find(cid)
    if root not in customer_groups:
        customer_groups[root] = []
    customer_groups[root].append(cid)

At this point, customer_groups is a dictionary where each key is a "representative" CustomerID, and the value is a list of all CustomerIDs belonging to the same customer.

3. Sort & Validate Groups with Field Completeness #

For each group, you can sort records to identify the most complete "master record" (and confirm they’re truly duplicates). Here’s how to score records by completeness, with weighted importance for critical fields:

def calculate_completeness_score(record):
    # Assign weights based on how important each field is to you (adjust as needed)
    weights = {
        'email addr': 3,
        'phone#': 2,
        'billing addr': 2,
        'middlename': 1
    }
    score = 0
    for field, weight in weights.items():
        if pd.notna(record[field]) and str(record[field]).strip() != '':
            score += weight
    return score

# Add the completeness score to your original dataframe
customer_records['completeness_score'] = customer_records.apply(calculate_completeness_score, axis=1)

# For each group, sort records by completeness (descending) to prioritize the most complete entry
for root, cids in customer_groups.items():
    group_records = customer_records[customer_records['CustomerID'].isin(cids)]
    # Sort by completeness first, then fuzzy match score (if you want to tie-break)
    sorted_group = group_records.sort_values(by=['completeness_score', 'score'], ascending=False)
    
    print(f"Customer Group (Root ID: {root}):")
    print(f"All Associated CustomerIDs: {cids}")
    print("Sorted Records (most complete first):")
    print(sorted_group[['CustomerID', 'Firstname', 'lastname', 'email addr', 'phone#', 'completeness_score']])
    print("---")

This step helps you:

• Quickly identify the most reliable record in each group (the top entry in the sorted list)
• Verify if all records in the group are indeed duplicates (e.g., cross-check billing addresses even if some are missing)
• Resolve minor conflicts (e.g., if two records have different phone numbers, prioritize the one with a complete email or higher completeness score)

4. Handle Edge Cases #

• Close match scores: If a record falls just below your threshold, manually review it to avoid missing duplicates.
• Conflicting fields: If two records in a group have conflicting non-null values (e.g., different emails), flag these for manual check—you might have two distinct customers with similar names.
• Single-record groups: Any group with only one CustomerID is automatically a unique record, so you can skip further review for those.

By combining fuzzy matching grouping with field completeness sorting, you’ll have a clear way to consolidate all duplicate CustomerIDs into lists while keeping track of the most reliable record for each customer.

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