← All samples

CSV Contact Cleanup

Standardizes names, emails, phones and dates; drops blank and duplicate rows; flags anything a person should check, including ambiguous dates.

Run it

cd samples/csv-cleanup
python clean_csv.py   # needs pandas

Files: messy_contacts.csv · clean_contacts.csv · issues.csv · clean_csv.py

Before

After

Flagged for review

Date handling: output is YYYY-MM-DD. Numeric dates like 05/02/2026 are read as DD/MM/YYYY (Canadian convention, one setting to switch to US). When both numbers could be the month, the row is converted and flagged for confirmation. Impossible dates are left blank and flagged.

Code

"""Clean a messy contacts CSV: trim, normalize case/emails/phones/dates, drop blanks & duplicates, flag issues.

Date policy: output is ISO YYYY-MM-DD. ISO (2026-01-05, 2026/03/10) and written-month
dates (March 3 2026) are unambiguous. Slash/dot dates like 05/02/2026 are read as
DD/MM/YYYY (Canadian convention; change DAYFIRST below for US data). If both parts are
<= 12 the date is ambiguous: it is still converted with that assumption AND flagged in
issues.csv so a person can confirm. Impossible dates (e.g. 2026-13-01) are left blank and flagged."""
import re, sys
import pandas as pd

DAYFIRST = True  # DD/MM/YYYY for numeric dates that aren't year-first

def parse_date(v):
    """Return (iso_or_None, note_or_None)."""
    v = (v or "").strip()
    if not v or v.lower() == "nan": return None, "missing date"
    m = re.fullmatch(r"(\d{4})[-/.](\d{1,2})[-/.](\d{1,2})", v)
    if m:
        d = pd.to_datetime(f"{m[1]}-{m[2]}-{m[3]}", errors="coerce", format="%Y-%m-%d")
        return (None, "invalid date") if pd.isna(d) else (d.strftime("%Y-%m-%d"), None)
    m = re.fullmatch(r"(\d{1,2})[/.-](\d{1,2})[/.-](\d{4})", v)
    if m:
        a, b = int(m[1]), int(m[2])
        day, mon = (a, b) if DAYFIRST else (b, a)
        note = None
        if a <= 12 and b <= 12 and a != b:
            note = f"ambiguous date, assumed {'DD/MM' if DAYFIRST else 'MM/DD'}/YYYY"
        elif a > 12 or b > 12:
            if (DAYFIRST and b > 12) or (not DAYFIRST and a > 12):
                day, mon = mon, day  # only one reading is possible
                note = "date order differs from assumed format, auto-corrected"
        d = pd.to_datetime(f"{m[3]}-{mon}-{day}", errors="coerce", format="%Y-%m-%d")
        return (None, "invalid date") if pd.isna(d) else (d.strftime("%Y-%m-%d"), note)
    d = pd.to_datetime(v, errors="coerce")  # written months, e.g. "March 3 2026"
    return (None, "unparseable date") if pd.isna(d) else (d.strftime("%Y-%m-%d"), None)

def phone(p):
    d = re.sub(r"\D", "", str(p))
    if len(d) == 11 and d.startswith("1"): d = d[1:]
    return f"({d[:3]}) {d[3:6]}-{d[6:]}" if len(d) == 10 else ""

def main(src="messy_contacts.csv", dst="clean_contacts.csv", rep="issues.csv"):
    df = pd.read_csv(src, dtype=str, skipinitialspace=True)
    df.columns = [c.strip().lower().replace(" ", "_") for c in df.columns]
    df = df.apply(lambda s: s.str.strip())
    df = df.dropna(how="all")
    df["name"] = df["name"].str.title()
    df["email"] = df["email"].str.lower()
    df["city"] = df["city"].str.title()
    df["phone"] = df["phone"].map(phone)
    raw_dates = df["signup_date"].fillna("")
    parsed = raw_dates.map(parse_date)
    df["signup_date"] = parsed.map(lambda t: t[0])
    issues = []
    for i, r in df.iterrows():
        if not re.fullmatch(r"[^@\s]+@[^@\s]+\.[a-z]{2,}", r["email"] or ""): issues.append((r["name"], "invalid email", r["email"]))
        if not r["phone"]: issues.append((r["name"], "incomplete phone", ""))
        note = parsed[i][1]
        if note: issues.append((r["name"], note, raw_dates[i]))
    before = len(df)
    df = df.drop_duplicates(subset=["email"], keep="first")
    df.to_csv(dst, index=False)
    pd.DataFrame(issues, columns=["name", "issue", "value"]).to_csv(rep, index=False)
    print(f"rows in: {before}, duplicates removed: {before-len(df)}, rows out: {len(df)}, issues flagged: {len(issues)}")

if __name__ == "__main__":
    main(*sys.argv[1:])