Remove Duplicate Records with Embeddings in Python

By the end of this guide you will have a script that reads a messy CSV of records, deletes the obvious duplicates for free, uses an embedding model to find the near-duplicates that a text comparison cannot see, and writes out a clean file that keeps the most complete version of each record. Budget about forty minutes for the first run on a few thousand rows.

The problem this solves is familiar to anyone who has merged two exports. A contact list contains Acme Ltd, ACME Limited and Acme Ltd. on three separate rows. Every one of them is the same company, but no exact comparison will ever match them, because as far as your computer is concerned those are three unrelated strings. This guide is part of Data Cleaning for AI, and it assumes you have already worked through Cleaning CSV Data with Pandas for AI so your file loads without errors.

An embedding is a long list of numbers that a model produces to capture what a piece of text means, arranged so that two texts with similar meaning produce number lists pointing in nearly the same direction. That is the whole trick: once each record is a list of numbers, "how alike are these two records?" becomes arithmetic you can do on your own laptop.

Prerequisites

You need Python 3.10 or newer inside a virtual environment. If you have not made one, follow Create a Python Virtual Environment for AI first, then install the four packages this guide uses:

pip install "pandas>=2.2" "numpy>=1.26" "openai>=1.40" "python-dotenv>=1.0"
pip freeze > requirements.txt

You also need an OpenAI API key, stored in a .env file in the same folder as your script:

OPENAI_API_KEY=sk-your-key-here

Add that file to .gitignore before you write another line, because a key committed to a public repository will be found and spent by strangers:

echo ".env" >> .gitignore

Finally, have a CSV to work on. The examples assume a file called companies.csv with a company_name column plus whatever other columns you happen to have — email, city, phone, anything.

Step 1 — Normalise the text and drop exact duplicates first

Never send a row to an API before you have tried to kill it for free. Normalising means rewriting each value into a plain, predictable form: all lowercase, no punctuation, single spaces. Acme Ltd. and ACME Ltd both collapse to acme ltd, and pandas can then throw one of them away without any model being involved.

This matters for two reasons. It cuts your bill, because you never pay to embed the same string twice. It also cuts the work in the expensive step later, where the cost grows with the square of the row count, so removing a tenth of your rows here removes about a fifth of the comparisons later.

import re
import pandas as pd

def normalise(value: object) -> str:
    """Lowercase, strip punctuation, and collapse runs of whitespace."""
    text = str(value).lower().strip()
    text = re.sub(r"[^\w\s]", " ", text)   # punctuation becomes a space
    text = re.sub(r"\s+", " ", text)       # many spaces become one
    return text.strip()

df = pd.read_csv("companies.csv")
df["name_key"] = df["company_name"].map(normalise)

before = len(df)
df = df.drop_duplicates(subset="name_key", keep="first").reset_index(drop=True)
print(f"removed {before - len(df)} exact duplicates, {len(df)} rows remain")

df.to_csv("companies_stage1.csv", index=False)

The reset_index(drop=True) call is not decoration. Later steps refer to rows by position, so the index has to be a clean 0, 1, 2, … run with no gaps left by the deleted rows.

The diagram below shows what this cheap pass catches and what it cannot, next to what the embedding pass adds.

Step 2 — Embed the surviving rows in batches

Now turn each remaining name_key into numbers. Send them in batches rather than one row at a time: a batch is a single HTTP request carrying many strings, so a hundred rows in one call is roughly a hundred times faster than a hundred calls, and it puts far less strain on your rate limit.

Two details are easy to get wrong. The API returns results in a data list where each item carries its own index, so sort by that before you collect the vectors and your rows can never drift out of alignment with your spreadsheet. And store the result as float32 instead of the default float64, which halves the memory for no meaningful loss of precision here.

import os

import numpy as np
import pandas as pd
from dotenv import load_dotenv
from openai import OpenAI

load_dotenv()                                   # reads .env, which is in .gitignore
client = OpenAI(api_key=os.getenv("OPENAI_API_KEY"))

EMBED_MODEL = "text-embedding-3-small"
BATCH_SIZE = 100

df = pd.read_csv("companies_stage1.csv")
texts = df["name_key"].fillna("").astype(str).tolist()

vectors: list[list[float]] = []
for start in range(0, len(texts), BATCH_SIZE):
    batch = texts[start:start + BATCH_SIZE]
    response = client.embeddings.create(model=EMBED_MODEL, input=batch)
    ordered = sorted(response.data, key=lambda item: item.index)
    vectors.extend(item.embedding for item in ordered)
    print(f"embedded {start + len(batch)} of {len(texts)}")

matrix = np.array(vectors, dtype="float32")
np.save("vectors.npy", matrix)
print("matrix shape:", matrix.shape)

The printed shape is one row per record and 1,536 columns, because that is how many numbers text-embedding-3-small returns per input. Saving to vectors.npy means you only pay for this once — every later experiment with thresholds reloads the file instead of calling the API again.

Step 3 — Score every pair with cosine similarity

Cosine similarity measures the angle between two number lists and reports it as a value between roughly -1 and 1, where 1 means "pointing the same way" and 0 means "unrelated". If you first scale every vector to length one, the whole calculation becomes a single matrix multiplication, which numpy does in a fraction of a second for a few thousand rows.

Use a deliberately generous cutoff on this pass. You are not deciding anything yet, only producing a shortlist to read in the next step.

import numpy as np
import pandas as pd

SHORTLIST_CUTOFF = 0.85          # deliberately loose; you will tighten it in Step 4

df = pd.read_csv("companies_stage1.csv")
matrix = np.load("vectors.npy")

unit = matrix / np.linalg.norm(matrix, axis=1, keepdims=True)
similarity = unit @ unit.T                 # every row against every other row
upper = np.triu(similarity, k=1)           # keep each pair once, ignore self-matches

left, right = np.where(upper >= SHORTLIST_CUTOFF)
pairs = pd.DataFrame({
    "left": left,
    "right": right,
    "similarity": upper[left, right],
    "left_name": df.loc[left, "company_name"].to_numpy(),
    "right_name": df.loc[right, "company_name"].to_numpy(),
}).sort_values("similarity", ascending=False)

pairs.to_csv("flagged_pairs.csv", index=False)
print(f"{len(pairs)} candidate pairs flagged")
print(pairs.head(15).to_string(index=False))

np.triu(similarity, k=1) keeps only the upper triangle of the square, above the diagonal. Without it you would get each pair twice — once as A-B and once as B-A — plus every row matched against itself at a perfect 1.0.

Step 4 — Pick your threshold by reading borderline pairs

There is no correct threshold you can look up, because the right value depends on how much wording varies in your particular data. Product descriptions need a different line than two-word company names. The reliable method is to look at real pairs from your own file and find where the model stops being right.

Print a sample from the grey zone and read it with your own eyes:

import pandas as pd

pairs = pd.read_csv("flagged_pairs.csv")
band = pairs[(pairs["similarity"] >= 0.85) & (pairs["similarity"] < 0.97)]
sample = band.sample(min(40, len(band)), random_state=0).sort_values(
    "similarity", ascending=False
)

for row in sample.itertuples():
    print(f"{row.similarity:.3f}  {row.left_name}  <->  {row.right_name}")

Read the list from the top down. The first rows will be obvious duplicates. Somewhere in the middle you will hit a pair that is clearly two different businesses that happen to sound alike — Northside Dental and Southside Dental, say. That crossover point is your threshold. Set it just above the highest score you disagreed with, then re-run the shortlist with the new number.

Two habits keep this honest. Pick the threshold from a sample you have not tuned on before, and write the chosen value down in a comment with the date, because the right cutoff drifts as your data changes.

Step 5 — Merge each group, keeping the most complete record

A duplicate rarely arrives alone. If row 4 matches row 90 and row 90 matches row 300, all three are the same entity even though row 4 and row 300 might score below your threshold against each other. So group first, then pick a survivor per group.

The grouping uses a small union-find helper: every row starts in its own group, and each accepted pair welds two groups together. Once the groups exist, keep the row with the fewest empty cells, since that is the version carrying the most information.

import pandas as pd

MERGE_THRESHOLD = 0.93        # the value you settled on in Step 4

df = pd.read_csv("companies_stage1.csv")
pairs = pd.read_csv("flagged_pairs.csv")

parent = list(range(len(df)))

def find(i: int) -> int:
    while parent[i] != i:
        parent[i] = parent[parent[i]]   # flatten the chain as we walk it
        i = parent[i]
    return i

def union(a: int, b: int) -> None:
    root_a, root_b = find(a), find(b)
    if root_a != root_b:
        parent[root_b] = root_a

for row in pairs.itertuples():
    if row.similarity >= MERGE_THRESHOLD:
        union(int(row.left), int(row.right))

df["filled_fields"] = df.notna().sum(axis=1)
df["group"] = [find(i) for i in range(len(df))]

survivors = (
    df.sort_values("filled_fields", ascending=False)
      .drop_duplicates(subset="group", keep="first")
      .sort_index()
      .drop(columns=["filled_fields", "group", "name_key"])
)

survivors.to_csv("companies_deduped.csv", index=False)
print(f"merged {len(df) - len(survivors)} near-duplicates, {len(survivors)} rows kept")

Keep companies_stage1.csv and flagged_pairs.csv after the run. If someone later asks why two rows became one, those two files plus the threshold in your script are the complete answer.

The cost of comparing everything, and how to shrink it

Comparing every row against every other row means the number of comparisons grows with the square of the row count. Ten thousand rows is about fifty million pairs, which numpy handles comfortably. Twenty thousand rows produces a similarity matrix of 400 million values, and at four bytes each that is roughly 1.6 GB of memory for a single array — enough to stop a laptop.

The fix is blocking: only compare rows that already share something cheap and reliable. If two companies are duplicates, their normalised names almost certainly start with the same letter, so use that letter as a block key and compare inside each block only. A country, postcode or first-word key works just as well, and picking a key with more distinct values makes each block smaller.

import numpy as np
import pandas as pd

df = pd.read_csv("companies_stage1.csv")
matrix = np.load("vectors.npy")
unit = matrix / np.linalg.norm(matrix, axis=1, keepdims=True)

df["block"] = df["name_key"].str[0].fillna("?")     # first letter of the clean name

found = []
for _, group in df.groupby("block"):
    idx = group.index.to_numpy()
    if len(idx) < 2:
        continue
    sub = np.triu(unit[idx] @ unit[idx].T, k=1)
    a, b = np.where(sub >= 0.85)
    found.append(pd.DataFrame({
        "left": idx[a], "right": idx[b], "similarity": sub[a, b],
    }))

pairs = pd.concat(found, ignore_index=True)
print(f"{len(pairs)} candidate pairs across {df['block'].nunique()} blocks")

Blocking trades a little recall for a lot of memory: a pair whose names start with different letters will never be compared, so Acme Ltd and The Acme Company slip through. Strip leading articles like "the" during normalisation and that particular gap mostly closes. If your records are long documents rather than short names, split them before embedding — Split Long Text into Chunks for AI APIs covers how.

Similarity bands and what to do with each

Treat these as a starting frame, then shift the boundaries using what you saw in Step 4.

Troubleshooting

• openai.BadRequestError: '$.input' is invalid — one string in your batch is empty, which the endpoint rejects. Cause: blank cells survived normalisation. Fix: drop them with df = df[df["name_key"].str.len() > 0] before you build the batches.
• openai.RateLimitError: Rate limit reached for requests — you sent batches faster than your account tier allows. Cause: a tight loop with no pause. Fix: add time.sleep(1) between batches, or follow Fix the 429 Rate-Limit Error in Python for proper backoff.
• numpy._core._exceptions._ArrayMemoryError: Unable to allocate 1.49 GiB — the all-pairs matrix does not fit in RAM. Cause: too many rows for one square matrix. Fix: use the blocking loop above so you only ever build small matrices.
• openai.AuthenticationError: Incorrect API key provided — the key never reached the client. Cause: load_dotenv() missing, or the .env file sitting in a different folder than the script. Fix: check the diagnosis in Fix the 401 Unauthorized Error in OpenAI Python.
• ValueError: cannot reindex on an axis with duplicate labels — you skipped reset_index(drop=True) in Step 1, so positions no longer line up with labels. Fix: rerun Step 1 as written. If the traceback is unfamiliar, Read a Python Traceback in Five Minutes shows how to find the failing line.

When to use this vs. alternatives

• Versus fuzzy string matching (rapidfuzz and friends): character-based matching is free, instant and better at typos, but it cannot know that "Ltd" and "Limited" are the same word. Run it alongside embeddings rather than instead of them — the two catch different mistakes.
• Versus asking a model to judge each pair: sending both records to gpt-4o-mini and asking "are these the same company?" gives the most accurate verdict, but it is one paid call per pair. Use it only on the middle band, where the number of pairs is small enough to justify the spend. Estimate OpenAI API Costs with Python helps you size that bill before you commit.
• Versus a dedicated record-linkage library: if you are matching on several structured columns at once — name plus address plus phone — a purpose-built matching library will beat a single embedding of one field. Embeddings shine when the deciding signal is one blob of free text.

Once your file is clean, everything downstream gets cheaper and more accurate, because you stop paying to process the same record three times and stop reporting one customer as three. If you are pushing the result into a system of record, Sync HubSpot Contacts with Python picks up where this script leaves off, and Count Tokens in Python Before You Send lets you price a run before you start it. Back to Data Cleaning for AI.

Related guides

• Cleaning CSV Data with Pandas for AI — get the file loading and typed correctly before you deduplicate it.
• Split Long Text into Chunks for AI APIs — what to do when each record is a document rather than a name.
• Group Keywords with Python and Embeddings — the same vectors used to group items instead of merging them.
• Estimate OpenAI API Costs with Python — price an embedding run before you launch it on a big file.
• Connect a Chatbot to Your Docs with RAG — the other everyday use for cosine similarity over embeddings.