A Data Scientist's Checklist for Cleaning Messy CSV Files Before Training

Every model you train inherits the state of the CSV it was fed. A ZIP code column read as integers, a date parsed month-first when the export was day-first, three spellings of the same city — none of these raise an error. They just sit in the training set, quietly deciding how your model splits the data. Cleaning is the part of a tabular project that most reliably determines whether the rest of the work means anything, and it is also the part most often done by reflex instead of by plan.

Below is a checklist of fifteen items, followed by the reasoning behind the ones that cause the most damage.

The Fifteen-Point Checklist

1. Load everything as text first. Read the file with dtype=str so nothing is guessed, then convert columns one at a time on purpose.
2. Reconcile the shape. Compare row and column counts against the source system or the export log.
3. Look at both ends. Truncated exports lose their tail, not their head, so always inspect the last rows too.
4. Profile every column. Null count, unique count, minimum, maximum, and the three most frequent values.
5. Normalize column names. Lowercase, underscores, no trailing whitespace, no duplicated headers.
6. Strip invisible characters. Leading and trailing spaces, non-breaking spaces, and stray tabs break every comparison that follows.
7. Unify the placeholders that mean missing. Empty strings, "NA", "N/A", "null", "-", "?", and "unknown" should become one representation.
8. Parse dates explicitly. State the format, then check the resulting range for impossible years.
9. Fix number formatting. Thousands separators, decimal commas, currency symbols, and percent signs.
10. Check identifiers for damage. Leading zeros dropped, long integers converted to floats, precision lost.
11. Canonicalize categories. Map every label variant to one value and keep that mapping in version control.
12. Choose a missing-value rule per column and write it down next to the column name.
13. Remove exact duplicates, then hunt near-duplicates using a normalized key.
14. Flag outliers with a documented rule and decide separately whether any of them should be removed.
15. Save the cleaned data as a new file and version the script that produced it.

Missing Values Need a Decision, Not a Default

Filling every null with zero or with the column mean is the single most common cleaning mistake, because it treats all missingness as the same thing. It is not. A blank income field might mean the household declined to answer, that the field did not exist in that period, or that someone forgot to type it. Filling with zero puts a high earner at the bottom of the distribution. Filling with the mean shrinks the variance and flattens whatever relationship that column had with the target.

Median is safer than mean for skewed columns, and forward fill is defensible for slowly changing status fields in data that you have sorted by time — but only for those. For anything else, consider leaving the value missing and adding a boolean indicator column, because missingness itself is often predictive. If you have categorical columns, a literal "missing" level lets tree-based models route those rows separately. Many gradient boosting implementations also handle nulls natively, which can beat any imputation you invent.

Three questions for any null column

• Is it missing because nobody recorded it, because it does not apply to this row, or because the respondent refused?
• Does the missingness correlate with the target? If yes, keep it as a feature rather than erasing it.
• If you impute, can the imputed value be computed from training rows only? If it touches the test set, you have a leak.

Where Inconsistent Types Hide

Date parsing is the classic trap. A column of "03/04/2024" is ambiguous until a value like "13/04/2024" appears, and a parser set to month-first will silently drop or misread everything it cannot fit. Be explicit about the format, and validate the output range: a birth date in 2077 is a parsing bug, not a data point.

Numbers carry their own damage. A weight column holding both kilograms and pounds looks perfectly numeric. A field with ranges like "10-12" is a string pretending to be a measurement. Identifiers are the worst case, because converting them to numbers strips leading zeros from postal codes and rounds long account numbers past the point of precision. Read IDs as strings and keep them that way — you will never do arithmetic on them.

Text fields need the same suspicion. "Yes", "yes ", "Y", and "1" may all be the same answer. Mixed capitalization and punctuation hide category counts, so a quick value-by-value review of any low-cardinality column is worth the two minutes. Where categories have a natural order, such as low, medium, and high, encode that order explicitly instead of leaving it alphabetical.

Duplicates Are Rarely Exact

Dropping exact duplicate rows is one line of code and rarely the whole job. Real duplicates differ by a capital letter in an email address, a middle initial, a nickname, or a single typo. Normalize a comparison key first — lowercase and trim emails, strip punctuation from phone numbers, collapse whitespace in names — then look for matches inside a sensible time window.

Before you remove anything, decide what a row represents: a person, a session, a transaction, or a shipment. In order data, the same customer buying twice in one afternoon is not a duplicate; it is two purchases. A duplicate flag that is wrong in either direction distorts every aggregate you compute afterwards.

Outliers: Flag Them, Then Judge

A height of 1750, an age of 999, a balance of zero where every other value is positive. Some of these are typos and some are the most interesting rows in the file. Interquartile range and z-score rules will find candidates, but they cannot tell you what to do, so split the job in two: flag first, decide later.

Start with domain limits, which catch obvious errors cheaply — ages between 0 and 120, prices above zero. Then flag statistical outliers without deleting them, and check whether they cluster in one source file, one date range, or one category. A cluster usually means a pipeline bug rather than a genuine anomaly. If you do remove rows, record how many and share that number with anyone reading your results. If you keep them, consider clipping the extremes or transforming a skewed target instead of dropping data.

The Last Pass Before Training

Cleaning ends with validation, not with a clean-looking dataframe. Fit imputers, scalers, and encoders on the training split only, then apply them to validation and test data — this is the leak that survives most reviews. Compare feature distributions across splits afterwards; a column whose median shifts sharply between train and test is telling you something about how the split was made.

Write a few assertions that run every time the pipeline executes: no nulls in required columns, values inside known ranges, categories within the allowed set, dates in the expected order. Keep a short data dictionary listing each column's type, units, allowed values, and missing-value rule. Save the raw file untouched and write the cleaned output to a new path, so anyone can reproduce your result from the beginning.

When next month's export arrives, run the same script rather than making the same judgment calls by hand. A cleaning routine you can rerun is worth more than one perfect afternoon of manual fixes.

Photo: Ylanite / Pixabay