Data Cleaning Best Practices: Preparing Data for Analysis and AI

Analysts spend most of their time cleaning data rather than analysing it, and the reason is not incompetence — it is that dirty data does not announce itself. A duplicate customer does not throw an error. It quietly inflates your revenue, splits one person's order history in two, and makes a segmentation model produce clusters that mean nothing.

Clean data is not a nice-to-have. It is the precondition for every number you are going to trust.

Step 1: Profile before you change anything

You cannot clean what you have not looked at. Before a single transformation, produce a profile:

• Type and distribution of every column
• Null rate per column, and whether nulls cluster in particular rows or periods
• Duplicate counts under several candidate keys, not just one
• Cardinality of categorical fields — a "country" column with 340 distinct values is telling you something
• Min, max, mean and standard deviation for anything numeric

Pandas, ydata-profiling, OpenRefine or Great Expectations will all do this. The output is a written record of what was wrong at the start, which is what lets you prove later that the cleaning helped.

Step 2: Deduplicate, with rules you can defend

Duplicates come from multiple form submissions, merged sources, replayed API events, and the same person signing up twice with two spellings of their own name.

The work is in two decisions. First, the matching key: which combination of fields means "this is the same entity". Email alone merges a shared family address. Name and postcode merges a father and son. Second, survivorship: when matched records disagree, which value wins. Usually field by field — the newest phone number, the earliest signup date, the most complete address.

Where no rule settles a conflict, flag the pair for review rather than picking. An unmerged duplicate is visible and fixable later. A bad merge destroys information and is nearly impossible to find again.

Step 3: Treat missing values as information

Dropping every row with a null is the most common mistake in this whole process, because it silently biases the dataset toward whoever fills forms in completely. Why the value is missing determines what to do:

• Missing completely at random. No pattern. Dropping or imputing with a central value is defensible.
• Missing at random. Explained by other columns — a field only shown to some users. Predictive imputation from those columns works.
• Missing not at random. The absence itself carries meaning. Income left blank correlates with income. Here, imputing destroys the signal; add an explicit is_missing flag and keep it.

Whatever you choose, record the choice. An imputed value that later gets treated as observed is a hard bug to trace.

Step 4: One format per field

Inconsistent formatting is the most tedious category and the easiest to get fully right:

• Dates. 01/05/2026 is ambiguous between two continents. Normalise to ISO 8601 and store the timezone.
• Currency. Separate the amount from the currency code. A numeric column mixing dollars and taka is not a number.
• Phone numbers. Normalise to one canonical form, and be careful with leading zeros — a spreadsheet will strip the 0 from a mobile number and hand you something undialable that still looks fine.
• Categories. Map free text to a controlled list. "N/A", "n/a", "NA", "-" and "" are one concept wearing five costumes.

Step 5: Enforce types with a schema

A revenue column stored as text breaks every calculation downstream, usually without erroring. Define a schema — column, type, allowed range, nullability, allowed values — and validate incoming data against it before it lands. Watch for numeric columns polluted by text sentinels, booleans expressed as "Yes"/"1"/"TRUE" in the same column, and dates stored as strings that sort lexicographically into nonsense.

Step 6: Investigate outliers, do not delete them

An outlier is either an error or your most interesting record, and the arithmetic cannot tell you which. A revenue figure with an extra zero and your largest-ever customer look identical to an IQR filter.

So detect with Z-score, IQR or isolation forests, then quarantine rather than remove: move suspect rows to a separate table with the reason attached, and have somebody with business context look. Automatically deleting outliers is how companies remove their best customers from a customer-value analysis.

Step 7: Make it reproducible

Cleaning done by hand in a spreadsheet is not cleaning; it is a one-off rescue that has to happen again next quarter, differently. Write it as code, keep it in version control, and log what changed and why. Every rule should be re-runnable against the original input to produce an identical result.

Then decide honestly whether the mess was historic or ongoing. If new records arrive dirty every week, cleaning the backlog buys a few months. The durable fix is validation at the point of entry plus a scheduled pipeline, with alerts when incoming quality drops below a threshold.

What good looks like

You have finished when someone can ask where a number came from and get an answer: this rule, applied on this date, to these rows, for this reason. Not "the spreadsheet says so". That traceability is the actual deliverable — the clean file is just what falls out of having it.