PoppyOS

Home › Guides

How to remove duplicate rows from a CSV by one column (keep the first or the last)

Whole-row duplicate removal is easy and often useless: two sign-ups from the same person usually differ in a date or a spelling, so the rows are not identical even though they describe one person. What you usually want is "one row per email address" or "one row per order number". Then the question is which copy to keep. For a contact list that is normally the first one (the original sign-up); for a price list that is updated over time it is normally the last one (the newest).

Python, with a record of what was removed

This keeps the first (or last) row for each email, ignoring capital letters and stray spaces. It sets aside, instead of silently losing, rows that are too short or have no email, and it writes everything it removed to a second file with the reason. It uses only the standard library.

import csv

KEY = "Email"
KEEP = "first"          # or "last"

with open("contacts.csv", newline="", encoding="utf-8-sig") as f:
    reader = csv.reader(f)
    header = next(reader)
    rows = list(enumerate(reader, start=2))      # row 1 is the header
k = header.index(KEY)

kept, removed, seen = [], [], {}
for n, r in (rows if KEEP == "first" else reversed(rows)):
    if len(r) != len(header):
        removed.append((n, r, "wrong number of columns"))
    elif not r[k].strip():
        removed.append((n, r, f"empty {KEY}"))
    elif r[k].strip().lower() in seen:
        removed.append((n, r, f"duplicate of row {seen[r[k].strip().lower()]}"))
    else:
        seen[r[k].strip().lower()] = n
        kept.append((n, r))

kept.sort(key=lambda x: x[0])
removed.sort(key=lambda x: x[0])
with open("contacts-deduped.csv", "w", newline="", encoding="utf-8-sig") as f:
    w = csv.writer(f)
    w.writerow(header)
    w.writerows(r for _, r in kept)
with open("contacts-removed.csv", "w", newline="", encoding="utf-8-sig") as f:
    w = csv.writer(f)
    w.writerow(["Original row"] + header + ["Reason"])
    w.writerows([n] + r + [why] for n, r, why in removed)

print(f"kept {len(kept)}, removed {len(removed)}")
for n, r, why in removed:
    print(f"row {n}: {why}")
kept 3, removed 4
row 4: duplicate of row 2
row 6: duplicate of row 3
row 7: empty Email
row 8: wrong number of columns

Row 4 (Ann@Example.com ) went as a duplicate of row 2, row 6 was an exact repeat of Bob, row 7 had no email, and row 8 was a column short. Change KEEP to "last" and the later copies win instead; the "duplicate of row N" numbers then point to the copy that was kept.

PowerShell (keeps the first)

Import-Csv contacts.csv |
  Where-Object { $_.Email -and $_.Email.Trim() } |
  Group-Object { $_.Email.Trim().ToLower() } |
  ForEach-Object { $_.Group[0] } |
  Select-Object Name,Email |
  ConvertTo-Csv -NoTypeInformation
"Name","Email"
"Ann Lee","ann@example.com"
"Bob Ray","bob@example.com"
"Cy Moss","cy@example.com"
"Eli Ng","eli@example.com"

This is shorter, but it gives you no record of what was dropped, and Import-Csv does not warn you about the short row (it fills the missing value with nothing). If you need the record, use the Python version, or the spreadsheet method that flags rows before deleting them.

Choosing the key

Last updated 2026-10-27. Code on this page was run on that date and the output shown is the real output. Spreadsheet menu steps are written from Microsoft's and Google's documented behaviour; where we have not run something ourselves, the page says so.

More guides

All guides