Python中是否有更优字符串比对方案？CSV企业匹配脚本求优化

Hey there! Nice work putting together that manual matching script—it gets the job done, but automating this with dedicated libraries will save you a ton of time on those repetitive manual confirmations. Let’s break down some solid, ready-to-use tools to handle fuzzy company name matching like "Apple" vs "Apple, Inc.":

1. RapidFuzz（FuzzyWuzzy的高效替代） #

RapidFuzz is a fast, optimized library for string similarity matching. It’s a drop-in replacement for the popular FuzzyWuzzy but runs way faster, making it perfect for large CSV datasets.

安装 #

pip install rapidfuzz

适配你的CSV场景的示例代码 #

We can refactor your script to use RapidFuzz's partial_ratio (ideal for cases where one name is a subset of the other) and set a similarity threshold to automate matches:

import csv
from rapidfuzz import fuzz, process

def load_and_clean_csv(file_path):
    """Helper function to load CSV and clean whitespace/newlines"""
    with open(file_path, 'r') as f:
        return [line.strip() for line in f if line.strip()]

# Load your two CSV datasets
test_companies = load_and_clean_csv("test.csv")
database_companies = load_and_clean_csv("CMPList.csv")

# Define a similarity threshold (adjust based on your needs)
MATCH_THRESHOLD = 80

confirmed_matches = []
not_in_database = []

for company in test_companies:
    # Find the best possible match in the database
    best_match, similarity_score, _ = process.extractOne(
        company, 
        database_companies, 
        scorer=fuzz.partial_ratio
    )
    if similarity_score >= MATCH_THRESHOLD:
        confirmed_matches.append((company, best_match, similarity_score))
    else:
        not_in_database.append(company)

# Print match results
print("Confirmed company matches:")
for comp, match, score in confirmed_matches:
    print(f"[{comp}] matches [{match}] with similarity score: {score}")

# Generate the "not in database" CSV
print("\nAll comparisons complete, creating new CSV of companies not in our database.")
with open('NotInDatabase.csv', 'w', newline='') as csv_file:
    writer = csv.writer(csv_file)
    for item in not_in_database:
        writer.writerow([item])
print("CSV creation complete. Exiting...")

2. Difflib（Python标准库，无需额外安装） #

If you prefer to stick with Python’s built-in tools, difflib has a SequenceMatcher that calculates string similarity. It’s a bit slower than RapidFuzz but works great for smaller datasets.

示例代码片段 #

import csv
import difflib

def is_similar_match(str1, str2, threshold=0.7):
    # Normalize to lowercase to avoid case sensitivity
    return difflib.SequenceMatcher(None, str1.lower(), str2.lower()).ratio() >= threshold

# Load datasets (use the same load_and_clean_csv function from above)
test_companies = load_and_clean_csv("test.csv")
database_companies = load_and_clean_csv("CMPList.csv")

confirmed_matches = []
not_in_database = []

for company in test_companies:
    matched = False
    for db_company in database_companies:
        if is_similar_match(company, db_company):
            confirmed_matches.append((company, db_company))
            matched = True
            break
    if not matched:
        not_in_database.append(company)

3. SpaCy（针对复杂数据的进阶实体匹配） #

If you’re dealing with really messy data (typos, inconsistent naming conventions like "Apple Corp" vs "Apple Inc."), SpaCy’s NLP models can identify and normalize company entities. It’s more heavyweight but incredibly powerful for complex cases.

基本步骤 #

1. Install SpaCy and a pre-trained model:

pip install spacy
python -m spacy download en_core_web_sm

2. Use entity recognition to extract standardized company names, then run matches on the normalized entities.

为什么这些库比手动匹配更好？ #

• Automation: No more manual y/n inputs—set a threshold and let the library handle matching.
• Accuracy: They account for more than just first characters or substrings, handling typos, suffixes (Inc., Ltd.), and minor name variations.
• Scalability: Libraries like RapidFuzz are optimized for large datasets, so your script won’t slow down with thousands of company names.

调整阈值的小技巧 #

• Start with a threshold around 70-80, then tweak based on test results. If you get too many false matches, raise the threshold; if you miss valid matches, lower it.
• Use partial_ratio for cases where one name is a shorter version of the other (like "Apple" vs "Apple, Inc."), and ratio for full-string similarity checks.

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