名寄せの基礎：完全一致からファジーマッチングへ

-- 同一メアドで複数レコードを統合SELECT  email,  COUNT(*) AS dup_count,  ARRAY_AGG(customer_id ORDER BY created_at) AS customer_idsFROM customersWHERE email IS NOT NULLGROUP BY emailHAVING COUNT(*) > 1;

import reimport jaconv
def normalize_company_name(s: str) -> str:    if not s:        return ""    # 1. 全角→半角、カタカナ揃え    s = jaconv.z2h(s, kana=False, ascii=True, digit=True)    s = jaconv.h2z(s, kana=True, ascii=False, digit=False)    # 2. 空白除去・大文字化    s = re.sub(r"\s+", "", s).upper()    # 3. 法人格を統一 → 削除    legal_forms = [        "株式会社", "(株)", "(株)", "KABUSHIKIKAISHA",        "合同会社", "(同)", "(同)",        "有限会社", "(有)", "(有)",        "CO.,LTD.", "CO.LTD", "CO LTD", "COLTD",        "LLC", "LTD", "INC", "K.K.", "KK",    ]    for lf in legal_forms:        s = s.replace(lf, "")    # 4. 残った記号を除去    s = re.sub(r"[・\-\.,()（）「」『』]", "", s)    return s.strip()
assert normalize_company_name("合同会社ふくふく") == "フクフク"assert normalize_company_name("(同)ふくふく")    == "フクフク"assert normalize_company_name("ふくふく合同会社") == "フクフク"assert normalize_company_name("Fukufuku LLC")    == "FUKUFUKU"  # ※カタカナ化は別問題

!pip install -q rapidfuzzfrom rapidfuzz import process, fuzz
candidates = ["ふくふく", "ふくふく商会", "ふくふくテック", "ふくふくホールディングス"]query = "ふくふぐ"  # タイポ
# 上位 3 件と類似度（0〜100）for match, score, _ in process.extract(query, candidates, scorer=fuzz.WRatio, limit=3):    print(f"{match}\t{score:.1f}")# 出力例:#   ふくふく           93.3#   ふくふく商会       72.0#   ふくふくテック     68.5

from itertools import combinationsimport pandas as pdfrom rapidfuzz import fuzz
df = pd.read_csv("customers.csv")df["norm_name"] = df["name"].apply(normalize_company_name)
pairs = []for zipcode, group in df.groupby("zipcode"):    rows = group.to_dict("records")    for a, b in combinations(rows, 2):        score = fuzz.WRatio(a["norm_name"], b["norm_name"])        if score >= 85:            pairs.append({**{f"a_{k}": v for k, v in a.items()},                          **{f"b_{k}": v for k, v in b.items()},                          "score": score})
print(f"自動マージ候補: {len(pairs)}件")