An independent study reference written by Dr Phuc V. Nguyen. It is not official subject material — for assessment requirements always follow your subject outline and vUWS.

Data Foundationsintermediate

Data cleansing

Q: An analyst fixes 300 malformed dates by hand in a spreadsheet export, then builds a report from the corrected file. What is the main problem?

The corrections vanish at the next refresh and nobody can reproduce or review what was changed. A manual edit is invisible and unrepeatable. Next month the same file arrives with the same defect, the fix has to be redone from memory, and no reviewer can see which values were altered or on what basis. Encoding the rule in the pipeline solves all three at once.

Q: A deduplication rule is loosened to catch more matches, and the merged customer count falls sharply. Which risk has increased?

Over-merging, combining records that belong to genuinely different people. Loosening the rule raises recall and lowers precision. More pairs are declared matches, so distinct people with similar names in the same area get combined, which corrupts contact history and consent records and is much harder to reverse than leaving a duplicate in place.

Cleansing is the set of operations that makes data usable: profiling to find out what is actually there, standardising formats and units, validating against rules and reference data, resolving duplicates, and deciding explicitly what to do about missing values and outliers. Two disciplines separate a professional job from a spreadsheet fix. Every change is made in the pipeline so it repeats on the next refresh, and every change is recorded, because a cleansing step is an assumption about the world that all later analysis inherits. Cleansing treats the symptom; it does not repair the process that produced the defects.

Why it matters

Cleaning data is closer to editing than to washing. You are not removing dirt from an object that stays the same underneath. You are deciding that this blank means zero, that this record and that record are the same person, that this reading of two hundred degrees is an instrument fault rather than a fire. Each decision could have gone the other way, and each one shapes the answer. Which is exactly why the decisions get written down.

Before you read on — recall

An analyst fixes 300 malformed dates by hand in a spreadsheet export, then builds a report from the corrected file. What is the main problem?

Formulas

Why deduplication needs blocking

P = \dfrac{n(n-1)}{2}

Comparing every record with every other record across 50,000 rows means about 1.25 billion pairs. Blocking first, for instance comparing only records that share a postcode and the first letter of the surname, cuts the candidate set by orders of magnitude, at the cost of missing genuine pairs that disagree on the blocking key.

Mean imputation shrinks apparent variability

s^2_{\text{after}} = s^2_{\text{before}} \times \dfrac{n_{\text{obs}} - 1}{n_{\text{total}} - 1}

Filling gaps with the column mean leaves the mean unchanged and adds nothing to the sum of squared deviations, because each filled value sits exactly at the mean. With 200 observed values in a column of 250, the variance comes out about a fifth too low, which makes any interval built on it look tighter than the evidence supports.

Worked examples

Scenario

A team merges customer records from a retail system and a loyalty app. Matching on email finds 12,000 duplicates. Matching on name and postcode as well finds 19,000.

Solution

Neither figure is right on its own. Email matching is precise and misses people who used a different address for the app, so it under-merges. Adding name and postcode raises recall and starts merging distinct people who share a common name in a dense postcode, so it over-merges. The workable method is to block on postcode, score candidate pairs across several fields, and set the threshold by which error costs more. Merging two real customers is harder to undo than leaving a duplicate, so the threshold leans conservative and borderline pairs go to review.

Scenario

Fifteen per cent of income values are missing in a customer dataset, and an analyst fills them with the column mean so the model will run.

Solution

That choice is not neutral. It asserts that people who did not disclose their income earn the average, and it shrinks the spread of the column so the model looks more certain than it should. Before imputing, check whether missingness relates to anything. If high earners decline to answer more often, mean imputation drags the top of the distribution down and biases every coefficient involving income. Recording missingness as its own flag often carries more information than any guess at the value.

Common mistakes

✗Cleansing is a preliminary chore before the real analysis. The decisions made while cleansing, such as which records are the same person and what a blank means, can move the result more than the choice of model does.
✗Cleansing should always be done in the source system so the data is fixed at the root. Correcting the source is worth doing where it is possible, and analytical corrections still belong in the pipeline where they are versioned and repeatable; a manual edit is lost at the next refresh.
✗Removing outliers improves the data. An outlier can be an error or the most important observation in the file, and deleting it without a reason removes exactly the fraud, fault or breakthrough you might be looking for.
✗Deleting rows with missing values is the neutral option. Dropping incomplete records is safe in general only when values are missing completely at random, meaning missingness is unrelated to both the recorded and the unrecorded values. Under weaker conditions whether it biases the answer depends on the estimand and the model being fitted, and where missingness relates to the outcome, dropping introduces bias while looking tidy.

Revision bullets

•Profile first: counts, distinct values, ranges, patterns, null rates
•Standardise, validate against reference data, then deduplicate
•Blocking makes deduplication tractable and trades some recall for feasibility
•Every cleansing rule is an assumption, so version it in the pipeline and log it
•Missing value handling and outlier removal are analytical choices, not housekeeping

Quick check

An analyst fixes 300 malformed dates by hand in a spreadsheet export, then builds a report from the corrected file. What is the main problem?

A deduplication rule is loosened to catch more matches, and the merged customer count falls sharply. Which risk has increased?

Connected topics

Analytics Workflow ETL and ELT Fitness for Use Quality Dimensions Why Data Goes Bad Sampling Bias Text Mining Pipeline

More in Data Foundations

What Big Data Means Forms of Data Data Warehouse Metadata Star Schema Measures & Dimensions Cloud for Analytics

Sources

Rahm & Do (2000)
Rahm, E., & Do, H. H. "Data Cleaning: Problems and Current Approaches." IEEE Data Engineering Bulletin, 23(4), 3-13, 2000.
A structured account of cleaning steps from profiling through transformation to duplicate elimination.
Fellegi & Sunter (1969)
Fellegi, I. P., & Sunter, A. B. "A Theory for Record Linkage." Journal of the American Statistical Association, 64(328), 1183-1210, 1969.
The probabilistic foundation for scoring candidate record pairs and setting match and non-match thresholds.
Dasu & Johnson (2003)
Dasu, T., & Johnson, T. Exploratory Data Mining and Data Cleaning. Wiley, 2003.
Argues for profiling the data before cleaning it, and for treating cleaning decisions as part of the analysis.

How to cite this page

Dr. Phil's Quant Lab. (2026). Data cleansing. Derivatives Atlas. https://phucnguyenvan.com/concept/ba-data-cleansing