Finding Rows That Do Not Match the Expected Pattern in a CSV

Most of the bad rows in a CSV are not subtle. An id that contains a letter, an email with no domain, a status spelled actve, a date that is not a date. They are easy to find with a pattern and hard to find by looking, because the file has 200,000 rows and the bad ones are 0.1% of them. This article builds the checking step: one pattern per column, a report with the line number of every failure in the original file, and a clear view of what pattern checks can and cannot catch. It is the last step of the cleaning series and the first step of validating a CSV before you trust it.

One rule per column

The rules live in a dictionary from column name to a regular expression that the whole value must match:

import sys, re, time, csv
sys.path.insert(0, "/batches/csv-0001-0200/lab/clean")
import pandas as pd
from clean import *

RULES = {
    "id":          r"\d+",
    "email":       r"[^@\s]+@[^@\s]+\.[^@\s]+",
    "signup_date": r"\d{4}-\d{2}-\d{2}",
    "status":      r"(?i)active|inactive|pending",
    "country":     r"(?i)usa|u\.s\.|united states( of america)?|italy|germany|brazil|switzerland|ukraine",
    "phone":       r"|[0-9()+. \-]{7,}",
}

def check(df):
    """Return one row per failed rule: line number (file line), column, value, rule."""
    out = []
    for col, pat in RULES.items():
        ok = df[col].str.fullmatch(pat)
        for idx in df.index[~ok]:
            out.append((int(df.loc[idx, "_line"]), col, df.loc[idx, col]))
    return pd.DataFrame(out, columns=["line", "column", "value"]).sort_values(["line", "column"])

def load(path):
    raw = pd.read_csv(path, dtype=str, keep_default_na=False, encoding="utf-8-sig")
    raw, _, _ = drop_blank_and_footer(raw)
    raw = raw.apply(lambda c: c.str.strip())
    raw["_line"] = raw.index + 2           # line 1 is the header, pandas counts from 0
    return raw

small = load("/batches/csv-0001-0200/lab/data/clean/customers-messy.csv")
bad = check(small)
print("small file, rows that break a rule:")
print(bad.to_string(index=False))
print("\nstr.match anchors only at the start, fullmatch at both ends:")
s = pd.Series(["2025-03-14", "2025-03-14 and more", "x2025-03-14"])
print(pd.DataFrame({"value": s, "match": s.str.match(RULES['signup_date']), "fullmatch": s.str.fullmatch(RULES['signup_date']), "contains": s.str.contains(RULES['signup_date'])}).to_string(index=False))

viol = load("/batches/csv-0001-0200/lab/data/clean/customers-violations.csv")
v = check(viol)
print("\nhand-made file with deliberate violations:")
print(v.merge(viol[["_line", "name"]].rename(columns={"_line": "line"}), on="line").to_string(index=False))
# a date that matches the pattern can still not be a date
d = pd.to_datetime(viol["signup_date"], format="%Y-%m-%d", errors="coerce")
print("matches the date pattern but is not a calendar date:", viol.loc[viol["signup_date"].str.fullmatch(RULES["signup_date"]) & d.isna(), "signup_date"].tolist())
print("email rule accepts the zero-width-space address from the small file:", bool(re.fullmatch(RULES["email"], "[email protected]\u200b")))

big = load("/batches/csv-0001-0200/lab/data/clean/customers-messy-200k.csv")
t = time.perf_counter(); bad = check(big); dt = time.perf_counter() - t
print(f"\n200k rows x {len(RULES)} rules: {len(bad)} failures in {dt:.2f}s")
print(bad["column"].value_counts().to_dict())
print("distinct failing values per column:", {c: bad.loc[bad['column'] == c, 'value'].nunique() for c in bad['column'].unique()})
print("most common failing signup_date values:", bad[bad['column'] == 'signup_date']['value'].value_counts().head(4).to_dict())
print("rows with at least one failure:", bad['line'].nunique(), "of", len(big))

python /batches/csv-0001-0200/lab/clean/t0121.py

small file, rows that break a rule:
 line      column            value
    3 signup_date       03/15/2025
    4 signup_date      15 Mar 2025
    5 signup_date        2025-3-16
    8       phone 555-0100 ext. 12
    9 signup_date       not a date

str.match anchors only at the start, fullmatch at both ends:
              value  match  fullmatch  contains
         2025-03-14   True       True      True
2025-03-14 and more   True      False      True
        x2025-03-14  False      False      True

hand-made file with deliberate violations:
 line  column            value      name
    3   email       bo@example   Bo Chen
    4   phone          call me Cy Dubois
    4  status            actve Cy Dubois
    5 country         Atlantis  Di Rossi
    5      id               A4  Di Rossi
    6   email ed@@example.test Ed Zurich
matches the date pattern but is not a calendar date: ['2025-13-45']
email rule accepts the zero-width-space address from the small file: True

200k rows x 6 rules: 112942 failures in 2.88s
{'signup_date': 112942}
distinct failing values per column: {'signup_date': 954}
most common failing signup_date values: {'not a date': 258, '31/02/2025': 240, 'TBD': 236, '2025-7-2': 173}
rows with at least one failure: 112942 of 199778

Regular expressions are Python's re flavor (the documentation covers fullmatch). The patterns are deliberately plain: \d+ for an id, [^@\s]+@[^@\s]+\.[^@\s]+ for an email, \d{4}-\d{2}-\d{2} for an ISO date, an alternation for the allowed statuses and countries, and for phone either empty or at least seven characters from digits and phone punctuation. Plain is the point. A short pattern says what you mean, anyone can read it, and the way to tighten it is to look at what it let through, not to write a clever one on the first attempt.

The function returns one row per failure with the file line number. Pandas counts rows from 0 and the header is line 1, so the line of a row is index + 2, and that number is what you send to the person who owns the source. A report without line numbers is a report nobody can act on.

match, fullmatch and contains

Anchoring is where most pattern checks go wrong (the pandas string reference lists match, fullmatch and contains), so look at the little table in the output:

str.match anchors at the start only: it accepted 2025-03-14 and more. str.contains finds the pattern anywhere: it accepted x2025-03-14. Only str.fullmatch requires the whole value to match. A validation that uses match or contains passes values with trailing junk. Use fullmatch in pandas and re.fullmatch in Python, and regexp_full_match in DuckDB.

What it finds

On the 6 row file with the deliberate violations, the check reported, with the file line:

• line 3, email: bo@example (no dot in the domain)

• line 4, phone: call me

• line 4, status: actve (a typo; row 4 has two problems)

• line 5, country: Atlantis

• line 5, id: A4

• line 6, email: ed@@example.test (two @)

That is four rows and six failures, each with the value, the column and the line. The report is sorted by line then column, so the problems of one row are adjacent. Group it by column and count, and the report becomes a summary: how many failures, of what kind.

What it does not find

Two rows in the same file pass their patterns and are still wrong.

2025-13-45. Row 5's date matches \d{4}-\d{2}-\d{2}, and there is no month 13. The output line says it: matches the date pattern but is not a calendar date: ['2025-13-45']. A regex can describe the shape of a date, not its validity; for dates, the check is a real parse (pd.to_datetime(..., format="%Y-%m-%d", errors="coerce")) and the failures are the NaT results. The same holds for any value whose rules are not regular: a credit card number needs a checksum, a postal code needs a lookup, a percentage needs a range.

Invisible characters. The email rule accepted [email protected] followed by U+200B, the zero-width space from the trimming article: email rule accepts the zero-width-space address from the small file: True. [^@\s] matches the zero-width space because it is not whitespace. The fix is ordering, not a better regex: trim and strip invisible characters first, then validate. A rule that runs on dirty text tests the dirt.

The email pattern is also a demonstration of how much to ask of a regex. A pattern that fully implements the email address grammar is enormous and permits addresses no mail system delivers to. The pragmatic check is the shape, and the real validation is sending a message.

At scale

Six rules on 199,778 rows took 2.91 s (3.66 s in a rerun). The result:

200k rows x 6 rules: 112942 failures in 2.91s
{'signup_date': 112942}
distinct failing values per column: {'signup_date': 954}
most common failing signup_date values: {'not a date': 258, '31/02/2025': 240, 'TBD': 236, '2025-7-2': 173}
rows with at least one failure: 112942 of 199778

All 112,942 failures are in one column, signup_date, and the cause is not 112,942 bad dates. The file has dates in four formats, and the rule asks for ISO, so every 03/15/2025 and 15 Mar 2025 fails. Distinct failing values (954) is the better number to read: most are valid dates in a different format, and a few, like not a date, 31/02/2025 and TBD, are real defects. This is how the report tells you what to do next: the date column needs a format-aware parse, and every other column is clean. Order the checks accordingly: run the cheap pattern checks to find which columns are in trouble, then fix those columns with the right tool.

A cross-check in SQL gave the same count:

bash /batches/csv-0001-0200/lab/clean/t0121b.sh

┌─────────────┬──────────┐
│ column_name │ failures │
├─────────────┼──────────┤
│ signup_date │ 112942   │
└─────────────┴──────────┘

DuckDB's regexp_full_match found 112,942 failing signup_date values and none in email, agreeing with pandas. Two implementations agreeing is worth more than either alone, and the SQL version scales to files that do not fit in pandas.

Turning a report into a decision

Counts alone do not tell you what to do. For each rule, decide in advance what a failure means:

• reject the file: a required column is missing, or more than a fixed share of ids fail (say 1%);

• reject the row: write it to a rejects file with the reason, import the rest;

• warn: log the count, keep the row (an unusual but legal status, a new country).

Write those thresholds next to the rules, so the pipeline can fail by itself. The validation guide puts these checks, the row counts and the schema together.

How to tell it worked

Take three failures from the report and find them in the original file by line number. Take three passing rows and confirm they are fine. Count failures per rule and compare with an independent method (the DuckDB query above). Add a row you know is bad and confirm it appears. And run the checks on the cleaned output of the pipeline: it should report only what you chose to leave.

What a pattern cannot prove

It cannot prove a value is true, only that it has the right shape. A well-formed email may belong to nobody, a valid-looking country code may be the wrong country for the person, a date in the right format may be the wrong date. Patterns find the rows that are clearly broken, which is most of what you need and none of what you can guarantee.