Regular Expressions for Data Cleaning

Why it exists

Raw data from users or external systems is rarely clean. It contains typos, inconsistent formatting, and irrelevant characters. Manually cleaning this data is impossible at scale, and writing specific procedural code for every possible variation is brittle and unmaintainable.

The mental model

Think of a regular expression as search-and-replace on steroids. Instead of searching for a literal string like "555-1234", you describe a pattern like "three digits, a hyphen, then four digits". This lets you find, validate, and transform all strings that match the pattern, not just one specific instance. It's a tiny, powerful language for describing text.

How it works

You construct a pattern using a special syntax. For example, \d matches any digit, . matches any character, + means "one or more of the preceding element," and () create "capturing groups" to extract specific parts of a match. You provide this pattern to a regex engine, available in nearly every modern language, which scans your input string and returns any substrings that match the pattern you described.

When to use it

Use regex when you need to validate, extract, or transform text based on a consistent but non-literal structure. It's the go-to tool for standardizing user-entered data like phone numbers, emails, and dates. It's also perfect for pulling structured information out of unstructured text, like extracting all URLs from a document.

When not to use it

Do not use regex to parse structured, nested formats like HTML or JSON. These formats are not "regular" and require a proper parser; using regex will lead to brittle, incorrect code. For simple tasks like checking if a string contains a fixed substring, use your language's built-in functions, as they are faster and more readable.

One canonical example

A dataset has phone numbers in multiple formats: (555) 123-4567, 555.123.4567, and 555-123-4567. A regex pattern like \(?(\d{3})\)?[\s.-]?(\d{3})[\s.-]?(\d{4}) can identify all of them. You can then use the captured groups (the parts in parentheses) to reformat them all into a single, standard format like 555-123-4567, creating clean, usable data.