Why data is rarely clean
Data usually comes from people typing into forms, devices recording readings, files exported from older systems and spreadsheets edited by many hands. Each source introduces its own quirks. Analysts often say they spend a large share of a project preparing data rather than analyzing it, and while the exact proportion varies, the point is well accepted: results are only as reliable as the inputs.
The saying “garbage in, garbage out” fits here. A calculation can be perfectly correct and still give a wrong answer if the table it reads is wrong.
Common problems
The following hypothetical customer table shows several typical issues at once:
| name | country | signup_date | age |
|---|---|---|---|
| Dana Lee | USA | 03/04/2025 | 34 |
| dana lee | United States | 2025-04-03 | 34 |
| Omar K. | U.S. | 15/06/2025 | |
| Priya N. | India | 2025-07-01 | 340 |
Looking at it row by row:
- Duplicates. The first two rows are probably the same person entered twice, with different capitalization and formats. Counting rows would overstate the number of customers.
- Inconsistent labels. “USA”, “United States” and “U.S.” mean the same thing but count as three different values when you group by country.
- Mixed date formats. Is 03/04/2025 the third of April or the fourth of March? Without a rule, nobody can tell. Day-first and month-first formats are common in different regions.
- Missing values. Omar’s age is blank. Ignoring blanks, filling them in or removing the row each affect the result differently.
- Impossible values. An age of 340 is almost certainly a typo for 34. It would badly distort an average.
Other frequent problems include extra spaces, numbers stored as text, unit mismatches (some entries in kilograms, others in pounds), and dummy values like 0 or 9999 that stand in for “unknown.”
Why it causes wrong answers
Consider a hypothetical average age. Suppose four customers have ages 30, 32, 34 and 340. The true total is 436, so the average is 109. Without the typo, with 34 in place of 340, the total is 130 and the average is 32.5. One bad entry changes the answer by a factor of more than three.
Similarly, if one real customer appears twice, a customer count is inflated. If a missing value is silently treated as zero, an average gets pulled down. These errors rarely announce themselves, and the output still looks like a normal number.
The usual cleaning steps
Practices vary by team and tool, but the work often follows a pattern.
1. Look before changing anything. Check how many rows and columns there are, what types each column has, the minimum and maximum of numeric fields, and how many values are blank. Simple summaries and counts of distinct values reveal many problems quickly.
2. Keep the original. Work on a copy, and leave the raw data untouched. That way any mistake can be undone, and the cleaning can be repeated on new data. This is also why many organizations keep raw files in storage such as data lakes before shaping them for use.
3. Standardize formats. Pick one convention for dates (an unambiguous year-month-day order is a common choice), one for capitalization, one for units and one spelling for each category, then convert everything to match.
4. Remove or merge duplicates. Exact duplicates are easy to find. Near-duplicates, like “Dana Lee” and “dana lee,” need rules about what counts as the same record. Choose the rule carefully, because merging two real people is also an error.
5. Handle missing values deliberately. Options include leaving them blank and excluding them from a calculation, filling them using a simple rule such as the median, or collecting the missing information. No option is always right. What matters is that the choice is conscious and noted, especially if the data feeds machine learning basics, where gaps and fills can change what a model learns from its training data.
6. Investigate outliers. An odd value may be an error, or it may be real and important. A 340 for age is surely wrong, but an unusually large purchase might be a genuine bulk order. Check the source if possible instead of deleting extremes automatically.
7. Validate. Add rules that flag problems: ages between 0 and 120, dates not in the future, email addresses containing an @ sign, and totals that match across tables. Many database systems can enforce such checks at the point of entry, which prevents some mess from arriving at all.
8. Document the changes. Record what was changed, why, and how many rows were affected. Someone else, or you months later, will need to understand the result.
Cleaning is partly a judgment call
Each decision has trade-offs. Deleting rows with missing values is simple, but it can bias results if the missing rows are not typical. Filling gaps keeps the row count but invents information. This is why careful analysts report what they did and, where it matters, test whether their conclusions change under different choices.
Messy inputs also interact with fairness. If some groups are recorded less completely than others, the data can reflect that imbalance, which feeds into questions about algorithmic bias.
Prevention is cheaper than repair
The easiest cleaning is the cleaning you avoid. Drop-down menus instead of free text, required fields, format checks on forms, and clear definitions of each column all reduce errors upstream. When the data is cleaned and trustworthy, even a simple chart or summary becomes far more useful.