Clean messy CSV data with Python: names, dates, phones and duplicates

Every data project starts with the same unglamorous job: cleaning. Names have odd spacing, dates come in five formats, phone numbers are typed ten ways, and the same person appears twice. This project builds a script that cleans a contacts CSV with only Python's standard library, and tells you exactly which rows it rejected and why. The code and outputs below were run with Python 3 before publishing.

The messy input

messy.csv:

name,email,phone,joined
  rahim   UDDIN ,Rahim@Example.com ,017-1234 5678,15/03/2025
KARIM hossain,karim@example.com,+8801812345678,2025-04-02
Nusrat jahan,nusrat@example,01912345678,"March 5, 2025"
Rahim Uddin,rahim@example.com,01712345678,16-03-2025
Sadia Islam,sadia@example.com,12345,10 Jan 2025
Tanvir Ahmed,tanvir@example.com,8801612345678,31/02/2025

Problems hidden in these 6 rows: extra spaces and wrong capitals, an email with uppercase letters, three phone styles, dates as 15/03/2025, 16-03-2025, March 5, 2025 and 10 Jan 2025, an invalid email, a duplicate person, a too-short phone number and a date that does not exist (31 February).

The script

Save as clean.py:

#!/usr/bin/env python3
"""Clean a messy CSV of contacts: tidy names, dates and phone numbers, drop duplicates, report rejects."""
import argparse
import csv
import re
from datetime import datetime

DATE_FORMATS = ["%Y-%m-%d", "%d/%m/%Y", "%d-%m-%Y", "%d %b %Y", "%B %d, %Y"]


def clean_name(value):
    return " ".join(value.split()).title()


def clean_email(value):
    email = value.strip().lower()
    return email if re.fullmatch(r"[^@\s]+@[^@\s]+\.[^@\s]+", email) else None


def clean_phone(value):
    """Return a Bangladesh mobile number as +8801XXXXXXXXX, or None if it does not look valid."""
    digits = re.sub(r"\D", "", value)
    if digits.startswith("880"):
        digits = digits[3:]
    digits = digits.lstrip("0")
    return "+880" + digits if re.fullmatch(r"1[3-9]\d{8}", digits) else None


def clean_date(value):
    for fmt in DATE_FORMATS:
        try:
            return datetime.strptime(value.strip(), fmt).date().isoformat()
        except ValueError:
            continue
    return None


def clean_rows(rows):
    good, rejected, seen = [], [], set()
    for number, row in enumerate(rows, start=2):  # row 1 is the header
        cleaned = {
            "name": clean_name(row.get("name", "")),
            "email": clean_email(row.get("email", "")),
            "phone": clean_phone(row.get("phone", "")),
            "joined": clean_date(row.get("joined", "")),
        }
        problems = [key for key, value in cleaned.items() if not value]
        if problems:
            rejected.append((number, ", ".join(problems), row))
        elif cleaned["email"] in seen:
            rejected.append((number, "duplicate email", row))
        else:
            seen.add(cleaned["email"])
            good.append(cleaned)
    return good, rejected


def main(argv=None):
    parser = argparse.ArgumentParser(description=__doc__)
    parser.add_argument("source")
    parser.add_argument("output")
    args = parser.parse_args(argv)
    with open(args.source, newline="", encoding="utf-8-sig") as f:
        good, rejected = clean_rows(csv.DictReader(f))
    with open(args.output, "w", newline="", encoding="utf-8") as f:
        writer = csv.DictWriter(f, fieldnames=["name", "email", "phone", "joined"])
        writer.writeheader()
        writer.writerows(good)
    print(f"{len(good)} rows kept, {len(rejected)} rejected")
    for number, reason, row in rejected:
        print(f"  line {number}: {reason}  {dict(row)}")


if __name__ == "__main__":
    main()

Run it

$ python3 clean.py messy.csv clean.csv
2 rows kept, 4 rejected
  line 4: email  {'name': 'Nusrat jahan', 'email': 'nusrat@example', ...}
  line 5: duplicate email  {'name': 'Rahim Uddin', 'email': 'rahim@example.com', ...}
  line 6: phone  {'name': 'Sadia Islam', 'email': 'sadia@example.com', 'phone': '12345', ...}
  line 7: joined  {'name': 'Tanvir Ahmed', ..., 'joined': '31/02/2025'}

clean.csv now contains the two good rows:

name,email,phone,joined
Rahim Uddin,rahim@example.com,+8801712345678,2025-03-15
Karim Hossain,karim@example.com,+8801812345678,2025-04-02

Rahim's first row was cleaned (name, email, phone and date all standardised), and his second row was rejected as a duplicate because it has the same email once lowercased.

How each cleaner works

Names. " ".join(value.split()) collapses any run of spaces into one, and .title() fixes the capitals. A warning: .title() is crude. It turns McDonald into Mcdonald and o'neil into O'Neil and mishandles many real names. For genuine person data, be careful before changing capitals automatically.

Email. Strip, lowercase, then a simple pattern: something, @, something, a dot, something. This catches obvious mistakes such as nusrat@example. It does not prove the mailbox exists. The only real proof is sending a confirmation email.

Phone. Remove everything that is not a digit, drop a leading 880 and leading zeros, and accept the rest only if it looks like a Bangladeshi mobile number: 1, then 3 to 9, then 8 more digits. Then we output a single standard form, +8801712345678. This is a simplified format check for this exercise, not a full phone validator, and operator ranges can change. For international data, use a library based on Google's libphonenumber.

Dates. We try several known formats in order with datetime.strptime. 31/02/2025 fails every format because that day does not exist, so it is rejected instead of silently turned into something wrong. A real danger: 03/04/2025 could mean 3 April or 4 March. Here we only list day-first formats on purpose. Decide the convention with the data owner; never guess.

Duplicates. We remember each email in a set (fast lookups) and reject a second row with the same email.

The most important design choice: report rejects, do not hide them

The script never invents values to fix bad rows and never drops them silently. It keeps a list of (line number, reason, original row) and prints it. That gives you a list to check by hand or send back to whoever produced the file. Silent cleaning is dangerous because errors disappear without anyone knowing.

Tests

Cleaning functions are small and pure, so they are easy to test with examples from real mess. Save as test_clean.py:

import unittest

import clean


class CleanTest(unittest.TestCase):
    def test_phone(self):
        self.assertEqual(clean.clean_phone("017-1234 5678"), "+8801712345678")
        self.assertEqual(clean.clean_phone("+8801812345678"), "+8801812345678")
        self.assertEqual(clean.clean_phone("8801612345678"), "+8801612345678")
        self.assertIsNone(clean.clean_phone("12345"))

    def test_date_formats(self):
        self.assertEqual(clean.clean_date("15/03/2025"), "2025-03-15")
        self.assertEqual(clean.clean_date("March 5, 2025"), "2025-03-05")
        self.assertIsNone(clean.clean_date("31/02/2025"))  # not a real date

    def test_name_and_email(self):
        self.assertEqual(clean.clean_name("  rahim   UDDIN "), "Rahim Uddin")
        self.assertEqual(clean.clean_email(" A@B.com "), "a@b.com")
        self.assertIsNone(clean.clean_email("nope@x"))

    def test_duplicates_and_rejects(self):
        rows = [
            {"name": "A", "email": "a@x.com", "phone": "01712345678", "joined": "2025-01-01"},
            {"name": "A again", "email": "A@x.com", "phone": "01712345678", "joined": "2025-01-02"},
            {"name": "B", "email": "b@x.com", "phone": "bad", "joined": "2025-01-03"},
        ]
        good, rejected = clean.clean_rows(rows)
        self.assertEqual(len(good), 1)
        self.assertEqual([r[1] for r in rejected], ["duplicate email", "phone"])


if __name__ == "__main__":
    unittest.main()

$ python3 -m unittest -v
Ran 4 tests in 0.013s

OK

Whenever you meet a new kind of mess in real data, add it as a test first, then fix the code.

Good habits for data cleaning

• Never modify the original file. Write a new file, as we did.
• Keep a log of what changed and what was rejected.
• Count before and after. 6 rows in, 2 kept, 4 rejected: it adds up.
• Handle encodings. utf-8-sig handles the invisible marker Excel adds to the start of CSV files.
• Do not guess. Reject ambiguous data and ask.
• Be careful with personal data. Contact lists are personal information. Store them securely, keep only what you need and follow the law that applies to you.

Limits

• It reads the whole file into memory, which is fine for files that fit comfortably in RAM, but for very large files process row by row and write as you go.
• Only one phone region and a short list of date formats.
• No fuzzy matching: Rahim Uddin and Rahim Udin with different emails are treated as different people.

Challenges

1. Add a --report rejects.csv option that writes rejected rows to a file with the reason.
2. Add a country column and a rule per country.
3. Strip invisible characters such as non-breaking spaces.
4. Detect duplicates by phone number as well as email.
5. Stream the file row by row instead of loading it all.
6. Try the same cleaning with the pandas library and compare the code.

Takeaway: cleaning is a pipeline of small, tested functions. Standardise what you can, reject what you cannot, never hide a rejected row, and keep the original file untouched.

---

Build-along projects series, Part 2 (tools you can reuse): 2 of 5. Previous: Build a URL shortener in Python and SQLite (with tests).