Cleaning a messy contacts CSV in 5 steps (with a short pandas script)
Most "messy CSV" problems in small businesses come down to the same five fixes. Here they are on a tiny example, with a short pandas script you can run yourself. The example (synthetic data, made up for this post) Before ( contacts_raw.csv ): Name,Phone,Email,Signup Date,City " jane DOE ",(519) 555-0142,Jane.Doe@Example.com ,2026/03/04,waterloo JANE…
Messed-up CSV files are a common headache for small businesses. Here's a five-step solution using a simple pandas script to clean up a synthetic dataset.
1. Start by trimming and normalizing case. Stray spaces and capitalization issues can break matching. Convert names to title case and email addresses to lowercase.
2. Standardize phone numbers. Remove all non-digit characters, keep the last 10 digits, and format as +1-AAA-EEE-NNNN for North American numbers.
3. Choose one consistent date format and parse each value explicitly. ISO format YYYY-MM-DD works well. For values like 04-03-2026, decide on a rule (day-month-year in this example) and apply it uniformly.
4. Remove any empty rows. Deduplicate on a normalized key, such as the cleaned email address, not the raw name.
5. Finally, save the cleaned dataset to a new file. Never overwrite the original data. Review any values you couldn't parse automatically before finalizing.
The pandas script demonstrated takes in the messy raw CSV contacts_raw.csv, applies these transformations, and outputs a cleaned contacts_clean.csv file with standardized formats and no duplicates or empty rows. The script can be run yourself and costs only USD 40 per file for larger datasets up to 5,000 rows. Peter at The Trillium Company offers this service for businesses needing a quick, clean CSV fix.
Written by urgent.news from Dev.to's reporting — not their text. Machine-written — may contain errors; check the original before relying on it.
