Data Cleaning and Validation

Introduction

Data analysis is only as trustworthy as the data on which it is based. In practice, datasets commonly contain missing values, repeated records, inconsistent labels, malformed dates, impossible measurements, and values that are technically valid but highly unusual. Data cleaning is the systematic detection and treatment of such problems. Data validation is the process of checking whether data conform to defined structural, semantic, and business rules.

Cleaning and validation are closely related but not identical. Cleaning changes, removes, or annotates data; validation evaluates whether data meet specified requirements. A professional workflow does not silently alter a dataset. It records what was changed, why the change was justified, how many records were affected, and what information was lost.

A useful principle is: preserve the raw data, create a documented working copy, and make every transformation reproducible.

Learning Objectives

By the end of this chapter, you should be able to:

• distinguish common types and causes of poor-quality data;
• measure and treat missing values appropriately;
• detect duplicates and inconsistent categories;
• parse dates, numbers, and text safely;
• identify invalid records using rule-based validation;
• distinguish errors from legitimate outliers;
• document assumptions, transformations, and data loss;
• evaluate the ethical and operational consequences of cleaning decisions.

1. Dimensions and Sources of Data Quality

Data quality is multidimensional. A dataset may be accurate but incomplete, or complete but inconsistent. The principal dimensions are shown below.

Poor quality may originate during data entry, sensor operation, system integration, manual spreadsheet editing, changes in coding standards, or incorrect extraction. Cleaning should therefore investigate causes rather than merely suppress symptoms.

2. A Controlled Cleaning Workflow

A reliable workflow normally follows these stages:

1. Profile the raw data. Inspect row counts, data types, ranges, missingness, unique values, and relationships.
2. Define data-quality rules. Specify required fields, allowed categories, valid ranges, formats, and key relationships.
3. Create a cleaning plan. Decide in advance how each known problem will be handled.
4. Apply transformations reproducibly. Use scripts, queries, or documented procedures rather than undocumented manual edits.
5. Validate the cleaned output. Re-run quality checks and compare results with the original dataset.
6. Record an audit trail. Report changes, exclusions, assumptions, and unresolved limitations.
7. Obtain subject-matter review. Domain experts should assess ambiguous values and unusual observations.

A data-quality report should include the number of records before and after processing, counts of affected cells or rows, missing-value rates, duplicate counts, invalid-record counts, and the reason for every exclusion.

3. Missing Values

A missing value is an absent, unavailable, or unrecorded value. Missingness is not always equivalent to zero, false, or “not applicable.” For example, a blank income field may mean that the respondent refused to answer, while a blank date-of-completion field may mean that the event has not occurred.

3.1 Forms of missingness

Common representations include blank strings, NULL, NA, “unknown,” “not provided,” −999, and “N/A.” These representations should be standardised only after their meanings are understood. A code such as 0 may be a genuine measurement or a missing-value code, depending on the data dictionary.

Missingness is often classified as:

• MCAR (Missing Completely At Random): missingness is unrelated to observed or unobserved values;
• MAR (Missing At Random): missingness is explainable by observed variables;
• MNAR (Missing Not At Random): missingness depends on the unobserved value or another unmeasured factor.

These categories describe assumptions about the missingness mechanism, not visual patterns alone. For instance, income may be MNAR if people with very high incomes are less likely to report them.

3.2 Measuring missingness

For variable j, the missingness rate is:

where m_j is the number of missing observations for variable j, and n is the total number of records. The result is a proportion between 0 and 1, or a percentage after multiplication by 100. This calculation assumes that each row represents one comparable observational unit.

A missingness indicator can also be created:

Here, x_ij is the value for record i and variable j. Indicators help reveal whether missingness is associated with region, time period, customer type, or another observed variable.

3.3 Treatment strategies

Deletion. Listwise deletion removes a row if any required field is missing. It is simple but may waste information and introduce bias. Pairwise deletion uses available values for particular calculations but can produce analyses based on different subsets.

Single-value imputation. Replacing missing numerical values with the mean or median is easy, but it reduces variation and can distort relationships. Median imputation is often more robust for skewed data. Mode imputation may be appropriate for a categorical variable with a stable, meaningful modal category, but it can exaggerate that category.

Group-wise imputation. A value may be imputed using a relevant subgroup, such as median delivery time by region. This can preserve broad group differences, but small or poorly chosen groups may create unstable results.

Model-based or multiple imputation. A predictive model estimates missing values from other variables. Multiple imputation creates several plausible datasets and combines results, reflecting uncertainty. These methods require stronger statistical knowledge and should not be used merely to conceal poor data collection.

Explicit categories. For categorical variables, “unknown” may be retained as a separate category when absence itself is informative. It should not be confused with a substantive category such as “none.”

Leaving values missing. Sometimes the most honest treatment is to retain missingness and report it. This is preferable when no defensible replacement exists.

Worked Example: Missingness

A survey contains 800 records. The employment-status field is missing for 56 records. Its missingness rate is:

If missingness is concentrated among respondents under 25, replacing all missing values with the overall mode may create systematic error. The analyst should investigate the survey design and consider age-group reporting, a justified imputation method, or a separate “not reported” category.

4. Duplicates and Entity Resolution

A duplicate is a record that represents the same entity or event more than once. Exact duplicates have identical values across selected fields. Near-duplicates differ in spelling, punctuation, formatting, or minor details. Two rows with the same customer name are not necessarily duplicates: they may represent two legitimate purchases by that customer.

Duplicate detection requires a definition of the unit of observation and a key. A transaction table might use transaction ID; a customer table might use customer ID. If no reliable key exists, composite keys or similarity methods may be required.

A practical process is:

1. identify candidate keys;
2. count repeated keys;
3. compare timestamps, amounts, locations, and source systems;
4. distinguish repeated events from repeated imports;
5. retain the authoritative record or consolidate records using documented rules;
6. record the number and identity of removed or merged records.

Fuzzy matching can compare names or addresses, but it creates false-positive and false-negative risks. Thresholds should be tested against manually reviewed examples. Sensitive entity resolution should receive additional governance because an incorrect merge can combine information belonging to different people.

5. Inconsistent Categories and Text Standardisation

Categorical fields often contain variants such as “Male,” “male,” “M,” and “ m ”. Standardisation may include trimming whitespace, normalising case, correcting known spelling errors, and mapping approved aliases to controlled labels.

A mapping table is safer than unrestricted replacement:

Do not collapse categories merely because they appear similar. “Cancelled,” “Returned,” and “Refunded” may represent different business states. Category decisions should be based on a data dictionary, domain knowledge, and the purpose of the analysis.

For free text, cleaning can include Unicode normalisation, whitespace handling, removal of accidental control characters, and preservation of meaningful punctuation. Over-cleaning can destroy information, particularly in names, addresses, product codes, and clinical notes.

6. Parsing Errors and Data Types

Parsing converts a stored representation into a meaningful data type. Common errors include interpreting day-month dates as month-day dates, reading currency symbols as text, losing leading zeros in postal codes, and treating percentages as whole numbers rather than proportions.

Dates and times

Dates should be parsed with an explicitly stated format, such as YYYY-MM-DD, rather than relying on software defaults. Time zones matter when combining event data from different locations. A timestamp without a time zone may not be safely comparable with one recorded in Coordinated Universal Time.

Numbers and units

Currency should be separated from numeric magnitude, and thousands separators should be interpreted according to locale. Units must be standardised before comparison. For example, 2.5 kilograms and 2,500 grams represent the same quantity, but the conversion must be explicit:

This assumes that the input is measured in kilograms and the output is required in grams.

Parsing failures

A failed conversion should not automatically become zero. It should become a flagged error or missing value, with the original text preserved. Examples include 31/02/2024, unknown, and £1O0 where the letter O has been entered instead of zero.

7. Rule-Based Validation

Validation rules translate knowledge about a dataset into testable conditions. Rules should be specific, interpretable, and linked to an action.

7.1 Types of rules

Type rules: a field must be numeric, date-valued, Boolean, or text.

Range rules: age must be between 0 and 120; a percentage must lie between 0 and 100 when expressed in percent units.

Set-membership rules: status must belong to an approved set such as Pending, Completed, or Cancelled.

Format rules: an identifier must follow a documented pattern. Format validity does not prove that the identifier exists.

Cross-field rules: a delivery date should not precede an order date; a returned order should have a return date.

Aggregate rules: order total should equal the sum of valid line-item amounts, subject to an explicitly defined rounding tolerance.

Referential rules: every foreign key in a child table should refer to an existing key in the parent table.

Temporal rules: an event timestamp should fall within the period covered by the data source.

A validation result should ideally contain the record identifier, rule identifier, observed value, expected condition, severity, and recommended action.

7.2 Hard and soft rules

A hard rule identifies a condition that makes a record unusable for a specific purpose, such as a missing primary key. A soft rule identifies a warning requiring review, such as an unusually large purchase. Rules are context-dependent: an age outside a survey target range may be invalid for that survey but not impossible in the wider population.

Worked Example: Cross-field Validation

Suppose an order has quantity 3, unit price £20, discount £5, and recorded total £55. If the rule is:

then the expected total is:

The record passes this rule, assuming discount is defined as a fixed monetary amount and taxes or shipping charges are excluded. If a tax field exists, the rule must be revised rather than silently treating tax as absent.

8. Outliers and Unusual Records

An outlier is an observation that is unusually distant from other observations under a chosen definition. An outlier may be a data-entry error, a measurement failure, a rare but genuine event, or evidence of a different population.

8.1 Detection methods

The z-score for observation x_i is:

where μ is the mean and σ is the standard deviation of the reference population or sample, calculated consistently. The z-score is dimensionless. A common screening convention flags values with absolute z-score greater than 3, but this is not a universal error rule and is less reliable for skewed data or small samples.

The interquartile range (IQR) is:

where Q_1 and Q_3 are the first and third quartiles. A conventional box-plot screening rule flags values below:

or above:

This is a screening convention, not proof that an observation is erroneous. It is often more robust than the z-score rule for skewed distributions.

8.2 Treatment

Possible actions include correcting a verified entry error, retaining the value, winsorising extreme values, transforming a variable, analysing subgroups separately, or excluding the record from a particular analysis. The choice must be justified by context. Removing an inconvenient but genuine observation is unethical and may materially bias conclusions.

9. Documenting Assumptions and Data Loss

A professional cleaning log should record:

Data loss should be quantified. If 120 of 10,000 rows are removed, the row-loss rate is:

This percentage alone does not establish acceptability. If all removed rows belong to one region, the practical bias may be severe. Report loss by important subgroups and distinguish row loss from cell replacement, category mapping, and information compression.

A cleaned dataset should retain provenance: source location, extraction time, schema version, code version, validation results, and links to rejected records where privacy and governance rules permit.

10. Applied Case: Retail Orders Dataset

The following is an illustrative case.

Background

A retailer combines online orders from three platforms to analyse delivery performance and customer retention. The combined table contains 250,000 rows and includes order ID, customer ID, order date, dispatch date, delivery date, status, quantity, price, discount, and region.

Problem

Initial profiling finds inconsistent date formats, repeated order IDs, blank delivery dates, negative quantities, multiple spellings of regions, and unusually high order values.

Analysis

The team first preserves the raw extracts and defines the unit of observation as one order. It discovers that repeated order IDs are caused partly by legitimate line-item records and partly by duplicate imports. Therefore, order ID alone cannot identify a row until line-item structure is understood.

Date fields are parsed using the source-specific documented formats. Invalid dates are retained in an error table rather than converted to missing without trace. Region labels are mapped through an approved reference table. A cross-field rule checks that dispatch date is not earlier than order date and that delivery date is not earlier than dispatch date.

Negative quantities are reviewed with the operations team. They represent returns in one platform, but errors in another. The platforms therefore receive separate rules rather than a universal replacement of negative values. High order values are screened using the IQR rule and investigated against payment records.

Approach and solution

The team creates three outputs:

1. a conformed order table for valid orders;
2. a rejected-record table containing failed hard rules and reasons;
3. a quality report containing warnings, counts, and subgroup comparisons.

Missing delivery dates are not imputed for orders whose status is still Pending. For Completed orders, missing delivery dates are flagged for operational investigation. Duplicate imports are removed only when source ID, order ID, timestamp, and amount agree; otherwise records are retained for review.

Outcome

The final analysis dataset contains fewer rows but stronger interpretability. Analysts can distinguish genuine returns from invalid quantities, and managers can see that missing delivery dates are concentrated in one platform. The cleaning process exposes a system-integration problem rather than hiding it through imputation.

Learning points

The case demonstrates that cleaning requires domain knowledge, that one rule may not suit every source, and that validation results can reveal weaknesses in upstream systems. It also shows why rejected records and transformation logs are essential for auditability.

11. Ethical, Governance, and Practical Considerations

Cleaning decisions can affect people’s eligibility, credit assessment, healthcare classification, or employment opportunities. Deleting records disproportionately associated with a group can produce discriminatory results. Imputation can create a false appearance of certainty. Sensitive attributes should not be inferred casually, and access to raw data must follow privacy and security requirements.

Organisations should apply data-minimisation principles, protect identifiers, control access, and retain only information needed for legitimate purposes. Where automated cleaning or machine-learning systems are used, decisions should be explainable and reviewable. A model should not learn that a rare group is an error simply because it is underrepresented.

Artificial intelligence can assist with anomaly detection, entity matching, and text normalisation, but it should support—not replace—defined rules and human review. AI-generated corrections require confidence thresholds, sampled verification, version control, and an audit trail. Generative systems may invent plausible values, so they should never silently fill authoritative records.

12. Best Practices and Limitations

Best practices include preserving raw data, profiling before editing, using explicit data dictionaries, separating validation from correction, applying source-specific rules, testing pipelines on known examples, retaining rejected records, measuring subgroup effects, and rerunning validation after every major transformation.

No cleaning process can guarantee truth. A value may pass every syntactic and range check while still being inaccurate. Statistical outlier rules depend on the reference population and may fail for multimodal or highly skewed data. Duplicate detection can merge distinct people, and imputation can understate uncertainty. Consequently, data quality is a reasoned judgement supported by evidence, not a final state that can be certified solely by software.

Chapter Summary

Data cleaning prepares data for analysis by treating missing values, duplicates, inconsistent categories, parsing errors, invalid records, and unusual observations. Data validation checks conformance with type, range, format, cross-field, aggregate, temporal, and referential rules. Missing values should be interpreted before treatment, because their mechanism and meaning influence whether deletion, imputation, explicit categories, or retention is appropriate. Duplicates require a clear unit of observation and reliable keys. Category mapping should use controlled vocabularies, while parsing must preserve units, formats, and failed conversions.

Outlier methods such as z-scores and IQR fences are screening tools rather than automatic deletion rules. The most important professional practice is documentation: preserve the raw source, record assumptions, quantify changes and data loss, retain rejected records where appropriate, and involve domain experts. Ethical cleaning protects privacy, avoids discriminatory effects, and keeps automated methods reviewable.

Key Terms

Missing value: An absent, unavailable, or unrecorded value.

MCAR: Missing completely at random.

MAR: Missingness explainable by observed variables.

MNAR: Missingness related to unobserved values or factors.

Imputation: Replacing or estimating a missing value.

Duplicate: A repeated representation of an entity or event.

Entity resolution: Identifying records that refer to the same real-world entity.

Controlled vocabulary: An approved set of category labels.

Parsing: Converting stored text into a meaningful data type.

Data type: The structural kind of a value, such as number, date, or text.

Validation rule: A test of whether data satisfy a defined condition.

Hard rule: A rule whose failure makes a record unusable for a specified purpose.

Soft rule: A warning condition requiring investigation.

Referential integrity: Correct correspondence between related table keys.

Outlier: An unusually distant observation under a chosen criterion.

Z-score: A standardised distance from a mean measured in standard deviations.

IQR: The difference between the third and first quartiles.

Data provenance: Information about a dataset’s origin, transformations, and versions.

Audit trail: A record of actions, decisions, and responsible persons.

Winsorisation: Limiting extreme values to selected boundary values.

Review Questions

Short Answer Questions

1. Distinguish data cleaning from data validation.
2. Why should a blank value not automatically be treated as zero?
3. State two examples of cross-field validation rules.
4. What is the purpose of a controlled vocabulary?
5. Why is an outlier not automatically a data error?

Long Answer Questions

1. Explain the principal dimensions of data quality and give an example of failure for each.
2. Compare deletion, single-value imputation, group-wise imputation, and multiple imputation.
3. Describe a robust workflow for detecting and resolving duplicates.
4. Explain how parsing errors can affect dates, numbers, and units.
5. Discuss why documentation and provenance are essential in a professional cleaning process.

Analytical / Application Questions

1. A dataset has 12,000 records, of which 180 are removed after failing a hard validation rule. Calculate the row-loss rate and explain what additional subgroup analysis is needed before judging the loss acceptable.
2. An order table contains negative quantities. Design a validation and investigation strategy that distinguishes returns from data-entry errors.
3. A high-income field has 18% missingness, concentrated among the highest-earning respondents. Evaluate the risks of mean imputation and propose a defensible alternative.

Multiple Choice Questions with Answers

Question 1

Which action best preserves auditability during data cleaning?

A. Overwrite the raw file with corrected values

B. Delete all records that fail validation

C. Keep the raw data and record transformations in a cleaning log

D. Replace every missing value with zero

Correct Answer: C. Keep the raw data and record transformations in a cleaning log

Question 2

A field has 56 missing values in a dataset of 800 records. What is the missingness rate?

A. 0.7%

B. 7%

C. 14%

D. 70%

Correct Answer: B. 7%

Question 3

Which situation is an example of a cross-field validation rule?

A. Status must be one of three approved labels

B. Age must be numeric

C. Delivery date must not precede dispatch date

D. Customer name must be stored as text

Correct Answer: C. Delivery date must not precede dispatch date

Question 4

Why can mean imputation be problematic?

A. It always increases variance

B. It can reduce variation and distort relationships

C. It converts numbers into categories

D. It guarantees unbiased estimates

Correct Answer: B. It can reduce variation and distort relationships

Question 5

What is the main purpose of a controlled vocabulary?

A. To create random category labels

B. To standardise approved categorical values

C. To remove all unknown values

D. To convert dates into numbers

Correct Answer: B. To standardise approved categorical values

Question 6

Which statement about an outlier is correct?

A. Every outlier is an error

B. Every outlier must be deleted

C. An outlier may be a genuine rare observation

D. Outliers occur only in categorical data

Correct Answer: C. An outlier may be a genuine rare observation

Question 7

For an observation x, mean μ, and standard deviation σ, which expression is the z-score?

A. (x + μ) / σ

B. (x − μ) / σ

C. (μ − σ) / x

D. x × μ × σ

Correct Answer: B. (x − μ) / σ

Question 8

Which treatment is most appropriate when a pending order has no delivery date?

A. Impute today’s date

B. Replace it with zero

C. Flag it as structurally expected missingness

D. Delete the customer

Correct Answer: C. Flag it as structurally expected missingness

Question 9

What does referential integrity require?

A. All values must be normally distributed

B. Every foreign key must correspond to an existing parent key

C. All records must have identical dates

D. Every category must contain the same number of records

Correct Answer: B. Every foreign key must correspond to an existing parent key

Question 10

A cleaning process removes 120 rows from 10,000. What is the row-loss rate?

A. 0.12%

B. 1.2%

C. 8.8%

D. 12%

Correct Answer: B. 1.2%