excel-fuzzy-duplicates-match

import pandas as pd

file_path = "~/Documents/companies-with-duplicates.xlsx"
df = pd.read_excel(file_path)

print(f"Shape: {df.shape}")
columns = df.columns.tolist()
print(f"Columns ({len(columns)}):", columns if len(columns) <= 100 else columns[:100] + ["..."])
df.head()

check_columns = ["Customer_ID", "Company_Name", "Email", "Phone"]

exact_duplicate_rows = int(df.duplicated().sum())
rows_in_exact_duplicate_groups = int(df.duplicated(keep=False).sum())

print(f"Exact duplicate rows (excluding the first occurrence): {exact_duplicate_rows}")
print(f"Rows that belong to exact-duplicate groups: {rows_in_exact_duplicate_groups}")

repeated_frames = []

for column in check_columns:
    counts = (
        df[column]
        .value_counts(dropna=False)
        .rename_axis("Value")
        .reset_index(name="Occurrences")
    )
    counts = counts[counts["Occurrences"] > 1].assign(Field=column)
    repeated_frames.append(counts[["Field", "Value", "Occurrences"]])

repeated_values = pd.concat(repeated_frames, ignore_index=True)

display(repeated_values.sort_values(["Field", "Occurrences"], ascending=[True, False]))

%pip install -q rapidfuzz

from itertools import combinations
from rapidfuzz.fuzz import token_sort_ratio

company_names = df["Company_Name"].dropna().astype(str).drop_duplicates().tolist()

similar_pairs = [
    {
        "Company_Name_1": name_1,
        "Company_Name_2": name_2,
        "Similarity_Score": round(token_sort_ratio(name_1, name_2), 1),
    }
    for name_1, name_2 in combinations(company_names, 2)
    if name_1 != name_2 and token_sort_ratio(name_1, name_2) >= 70
]

similar_company_pairs = (
    pd.DataFrame(similar_pairs)
    .sort_values("Similarity_Score", ascending=False)
    .reset_index(drop=True)
)

similar_company_pairs

details = [
    "Contact_Name", "Email", "Phone", "City",
    "Annual_Spend_USD", "Signup_Date"
]

left_details = df[["Company_Name"] + details].rename(
    columns={
        "Company_Name": "Company_Name_1",
        **{column: f"{column}_1" for column in details},
    }
)

right_details = df[["Company_Name"] + details].rename(
    columns={
        "Company_Name": "Company_Name_2",
        **{column: f"{column}_2" for column in details},
    }
)

matches_with_details = (
    similar_company_pairs
    .merge(left_details, on="Company_Name_1", how="left")
    .merge(right_details, on="Company_Name_2", how="left")
)

display(matches_with_details)

df["Signup_Date"] = pd.to_datetime(df["Signup_Date"], errors="coerce")

pair_dates = (
    similar_company_pairs
    .merge(
        df[["Company_Name", "Signup_Date"]].rename(
            columns={"Company_Name": "Company_Name_1", "Signup_Date": "Signup_Date_1"}
        ),
        on="Company_Name_1",
        how="left",
    )
    .merge(
        df[["Company_Name", "Signup_Date"]].rename(
            columns={"Company_Name": "Company_Name_2", "Signup_Date": "Signup_Date_2"}
        ),
        on="Company_Name_2",
        how="left",
    )
)

remove_names = set(
    pair_dates.apply(
        lambda row: row["Company_Name_2"]
        if row["Signup_Date_1"] <= row["Signup_Date_2"]
        else row["Company_Name_1"],
        axis=1,
    )
)

current_total = df["Annual_Spend_USD"].sum()
deduplicated_total = df.loc[
    ~df["Company_Name"].isin(remove_names), "Annual_Spend_USD"
].sum()

print(f"Current total Annual_Spend_USD: {current_total:,.2f} USD")
print(f"Total after counting each matched pair once: {deduplicated_total:,.2f} USD")
print(f"Rows excluded: {len(remove_names)}")
print(f"Pairs with tied or missing Signup_Date: {int((pair_dates['Signup_Date_1'] == pair_dates['Signup_Date_2']).sum())}")

deduplicated_df = (
    df.loc[~df["Company_Name"].isin(remove_names)]
    .sort_values("Signup_Date")
    .reset_index(drop=True)
)

output_path = "companies-deduplicated.xlsx"
deduplicated_df.to_excel(output_path, index=False)

print(f"Saved {len(deduplicated_df)} rows to: {output_path}")
print(f"Annual_Spend_USD total: {deduplicated_df['Annual_Spend_USD'].sum():,.2f} USD")