4. Data Quality, Cleaning and Transformation

Introduction: Preparation Before Analysis

Data analysis is often presented as a sequence of collecting data, applying statistical methods, visualising results, and making decisions. In practice, however, the reliability of every later stage depends on an earlier activity: preparing data so that it is sufficiently accurate, complete, consistent, valid, and timely for its intended use. A sophisticated model trained on poorly prepared data may produce precise-looking but misleading results. Data quality is therefore not a clerical concern; it is a methodological, technical, managerial, and ethical concern.

Data preparation includes profiling a dataset, identifying defects, correcting or documenting them, combining data from different sources, restructuring tables, aggregating observations, scaling variables, and constructing useful features. These operations should not be performed mechanically. Each transformation represents an assumption about the meaning of the data. Replacing a missing value with zero, for example, assumes that absence means no quantity. Converting a currency column without identifying the currency creates false precision rather than useful information.

This chapter develops a systematic approach to data quality, cleaning, and transformation. It treats preparation as an auditable analytical workflow rather than a collection of ad hoc fixes. The discussion applies to spreadsheets, relational databases, statistical software, and programming environments such as Python and R.

Learning Objectives

After studying this chapter, the reader should be able to:

1. Explain the dimensions of data quality and relate them to analytical purpose.

2. Profile datasets using structural, statistical, and semantic checks.

3. Treat missing values, duplicates, inconsistencies, outliers, and encoding errors appropriately.

4. Perform joins, reshaping, aggregation, normalization, and feature construction.

5. Distinguish correction, imputation, exclusion, and documentation as different responses to data problems.

6. Design a reproducible and auditable data-preparation workflow.

7. Evaluate the risks, ethical implications, and professional consequences of poor data quality.

Data Quality Is Fitness for Use

Data quality is best understood as fitness for a defined purpose. A dataset can be adequate for exploratory analysis but unsuitable for regulatory reporting. A postal code may be irrelevant to a sales forecast but essential for geographic planning. Consequently, quality cannot be judged solely by the number of missing or invalid records; it must be assessed against the decision, population, time period, and level of precision required.

The principal dimensions are as follows:

• Accuracy is the degree to which recorded values represent the true or accepted values. A customer’s recorded age of 42 is inaccurate if the person is actually 24.
• Completeness concerns whether required values and records are present. A table may be complete for sales amount but incomplete for customer income.
• Validity means that values conform to permitted formats, types, ranges, and rules. A percentage of 145 or a date such as 31 February is invalid under ordinary business rules.
• Consistency means that values agree across fields, records, datasets, and time. A transaction marked both “cancelled” and “delivered” is internally inconsistent.
• Timeliness indicates whether data is available and current enough for its purpose. A weekly risk report based on data that is two months old may be accurate historically but untimely operationally.
• Uniqueness concerns the absence of unintended duplicate entities or events. Duplicate rows can inflate totals and distort rates.
• Integrity refers to relationships among data elements, particularly keys and referential constraints. An order referring to a nonexistent customer violates referential integrity.

These dimensions may conflict. A rapidly updated data stream may be timely but less complete. An aggressively deduplicated customer table may be unique but accidentally remove legitimate household members. Quality assessment therefore requires explicit decisions about acceptable thresholds and trade-offs.

From Data Profile to Preparation Plan

A data profile is a structured description of a dataset before substantial transformation. Profiling should begin with metadata: source, owner, extraction date, intended population, units of measurement, schema, data types, and known limitations. Without this context, a value such as “100” could represent dollars, kilograms, or a coded category.

Structural profiling examines row and column counts, field names, data types, primary-key candidates, null representations, and file encoding. Statistical profiling examines minimum and maximum values, quantiles, mean, median, standard deviation, frequency distributions, and cardinality. Semantic profiling checks whether values make sense in context: dates follow the expected sequence, quantities are plausible, and category labels conform to a controlled vocabulary.

A useful profile might answer the following questions:

• What does one row represent?
• Which field, or combination of fields, identifies a record uniquely?
• What proportion of each field is missing?
• Which values occur most often, and are they plausible?
• Are units, currencies, time zones, and measurement definitions consistent?
• Which records fail business rules or relationships with other tables?

Profiling should produce a preparation plan that distinguishes errors from legitimate variation. For example, a high transaction value may be an error, an unusual but real purchase, or a value expressed in a different currency. The plan should record the proposed treatment, its rationale, the responsible person, and the expected effect on the dataset.

Missing Values: Absence Has Meaning

Missingness occurs for many reasons: a question was not asked, a sensor failed, a respondent declined to answer, a field was not applicable, or a data integration process lost a value. These causes matter because missing data may not be random.

A useful conceptual classification distinguishes:

• Missing completely at random (MCAR): missingness is unrelated to observed and unobserved values.
• Missing at random (MAR): missingness is related to observed variables, such as income being more often missing for respondents in a particular age group.
• Missing not at random (MNAR): missingness is related to the unobserved value itself, such as high-income respondents being less willing to report income.

The classification cannot always be established conclusively, but it guides treatment. First, distinguish genuine nulls from placeholders such as `-`, `unknown`, `N/A`, `999`, or blank strings. A placeholder must not be interpreted as a numerical value merely because a file stores it in a numeric-looking column.

Possible treatments include:

1. Deletion. Listwise deletion removes records with missing values; pairwise deletion uses available values for particular calculations. Deletion is defensible when missingness is limited, the removed cases do not bias the analysis, and sufficient observations remain.

2. Simple imputation. Mean, median, mode, or group-specific values can be used when a transparent baseline is appropriate. Median imputation is often less influenced by extreme values than mean imputation, but it reduces natural variation and can distort relationships.

3. Model-based imputation. Regression, multiple imputation, nearest-neighbour methods, or algorithms can incorporate relationships among variables. These methods require careful validation and should preserve uncertainty rather than implying that an imputed value was observed.

4. Missingness indicators. A separate flag can record that a value was missing. This is useful when the fact of missingness may itself carry information.

5. Domain-specific categories. “Not applicable,” “not disclosed,” and “not yet measured” should remain distinct where their meanings differ.

Imputation must be performed within the correct analytical partition. In predictive modelling, imputing the entire dataset before splitting into training and test sets can leak information from the test set into the training process. Fit the imputation rule on training data and apply it to validation or production data.

Duplicates, Inconsistencies, and Encoding Errors

A duplicate is not simply a repeated row. Two identical purchases may be legitimate transactions, while two slightly different rows may describe the same customer. Duplicate detection therefore depends on the unit of analysis and the key. Exact duplicates can be identified by comparing complete records. Fuzzy duplicates may require standardising names, addresses, telephone numbers, or email addresses and then applying similarity rules.

A sound deduplication process normally involves identifying candidate matches, estimating the confidence of each match, reviewing ambiguous cases, selecting a survivor record, and retaining a lineage of the merge. Automatic merging is risky when a false match can create legal, financial, or clinical harm.

Inconsistency occurs at several levels. Text may vary between “United Kingdom,” “UK,” and “U.K.” Dates may appear as day-month-year and month-day-year. A product may be classified as “consumer electronics” in one system and “electronics” in another. Numeric values may mix decimal commas and decimal points. These problems require standardisation rules, not arbitrary replacement.

Encoding errors often arise during file exchange. Characters may be misread because UTF-8, UTF-16, or another character encoding has been interpreted incorrectly. Names can consequently contain corrupted symbols. Delimiters, quotation marks, line breaks, and locale-specific decimal conventions can also damage imports. The original file should be preserved, the detected encoding documented, and a sample manually inspected after conversion.

Outliers: Error, Signal, or Population Change?

An outlier is an observation that is unusually distant from the general pattern. It may result from data-entry error, instrument failure, fraud, a rare event, a legitimate subgroup, or a change in the data-generating process. Outliers should not be removed merely because they weaken a statistical result.

Common detection methods include the interquartile range rule, in which values below Q1 − 1.5(IQR) or above Q3 + 1.5(IQR) are flagged; z-scores for approximately symmetric distributions; robust statistics such as the median absolute deviation; scatterplots; and domain-specific limits. Detection is not treatment. A flagged transaction should be investigated, not automatically deleted.

Possible responses are correction from a verified source, retention with a flag, transformation such as a logarithm, winsorisation or capping, robust modelling, or exclusion with a documented reason. The selected response should be tested through sensitivity analysis: do substantive conclusions change when flagged records are included or treated differently?

Combining Data: Joins and Referential Reasoning

Relational operations allow analysts to combine information from multiple tables. A primary key uniquely identifies a row in its table; a foreign key refers to a key in another table. Before joining, analysts should check key uniqueness, data types, whitespace, case, missing keys, and the expected relationship.

An inner join retains only matching records. A left join retains every record from the left table and adds matching information from the right. A right join does the reverse, while a full outer join retains unmatched records from both sides. A many-to-one join, such as orders to customers, generally preserves the number of order rows. A many-to-many join can multiply rows unexpectedly and produce inflated totals.

For example, joining a customer table to an order table on `customer_id` is safe only if `customer_id` is unique in the customer table. If it appears three times because of historical customer versions, each order may be replicated three times. Analysts should compare row counts before and after a join, inspect unmatched keys, and reconcile totals.

Reshaping, Aggregation, and Granularity

Data is commonly stored in wide form, where repeated measurements occupy separate columns, or long form, where each measurement is represented by a row. Long form is often convenient for grouping and visualisation; wide form may be convenient for reporting or certain modelling tasks. Reshaping is not merely cosmetic because it changes how observations are represented.

Aggregation combines records according to dimensions such as month, region, product, or customer. Measures must be aggregated according to their meaning. Sales revenue may be summed, unit price may require a weighted average, and a customer count may require distinct counting. Averaging daily averages without weighting can produce a misleading monthly average when days contain different numbers of observations.

Every aggregation should state its grain: for example, “one row per region per month.” This prevents the common error of mixing customer-level and transaction-level variables in the same table. Grain should also be checked after joins and pivots.

Transformation and Feature Construction

Transformation changes the representation of data while preserving or deliberately modifying its analytical meaning. Numerical transformations include unit conversion, logarithmic transformation for right-skewed variables, ratios, differences, and time-based measures. Categorical transformations include standardising labels, grouping rare categories, one-hot encoding, ordinal encoding where order is meaningful, and target encoding with strong safeguards against leakage.

Encoding must reflect the nature of the variable. One-hot encoding is appropriate for nominal categories such as payment method. Assigning codes 1, 2, and 3 may falsely imply order and distance. Ordinal encoding is justified for categories such as low, medium, and high only when the ordering is substantively meaningful. High-cardinality fields such as product identifiers may require grouping, hashing, embeddings, or carefully designed frequency features.

Feature construction creates variables that make patterns more accessible. Examples include customer tenure from registration and transaction dates, average order value from revenue and order count, rolling seven-day demand, proportion of late deliveries, and interaction terms between relevant predictors. Features must be calculated using information available at the prediction time. A future outcome accidentally included in a feature creates target leakage and produces unrealistic performance.

Normalization and standardization are distinct. Min–max normalization maps values to a specified interval, commonly 0 to 1. Standardization transforms a variable using its mean and standard deviation, producing a variable with mean approximately zero and standard deviation approximately one in the fitting data. Robust scaling uses the median and interquartile range and may be preferable when extreme values are present. Scaling is especially important for distance-based, gradient-based, and regularized algorithms, but it is not generally necessary for every tree-based method.

A Reproducible Preparation Workflow

A professional workflow is iterative rather than strictly linear:

1. Define the analytical question, population, grain, and acceptable quality thresholds.

2. Preserve the raw source and record provenance, access date, and extraction method.

3. Profile structure, missingness, distributions, keys, relationships, and semantic rules.

4. Standardise types, formats, labels, units, and encodings.

5. Resolve duplicates, missing values, invalid records, and outliers using documented rules.

6. Join, reshape, aggregate, and construct features while checking grain and row counts.

7. Separate training, validation, and test data before fitting transformations for predictive work.

8. Validate the prepared data through automated assertions and independent review.

9. Compare important statistics before and after preparation.

10. Publish a data dictionary, transformation log, quality report, and versioned output.

Automated checks may assert that dates are valid, identifiers are unique, percentages lie between 0 and 100, required fields are non-null, and foreign keys match reference tables. Tools such as SQL, Python data-frame libraries, R, spreadsheet validation, and data-quality platforms can implement these checks. Automation improves repeatability, but it does not replace domain judgment.

Illustrative Case: Preparing an Online Retail Dataset

Background

An online retailer wants to predict delayed deliveries and understand regional sales performance. Its data arrives from an order system, a customer relationship system, a warehouse application, and courier files. The proposed analytical table should contain one row per order.

Situation and Problem

Initial profiling identifies 2.4 million order rows, multiple date formats, missing delivery dates for cancelled orders, customer identifiers with inconsistent capitalization, repeated customer records, and a small number of unusually large order values. A preliminary join to the customer table increases the order count substantially, suggesting a duplication problem.

Analysis

The team first defines the grain as one row per order and identifies `order_id` as the expected primary key. Exact duplicate orders are found, but some repeated rows differ only in ingestion timestamp. The customer table contains multiple historical records per customer because addresses were stored as separate versions. A direct join therefore creates a one-to-many multiplication.

The team also discovers that missing delivery dates are not ordinary missing values: they are structurally missing for cancelled orders, while some completed orders lack dates because of a courier integration failure. Treating both groups identically would conceal an operational problem. Currency fields contain both euros and pounds, and a few order values are high because they represent business customers rather than errors.

Approach and Solution

The analysts retain the raw files and create a versioned preparation pipeline. They remove only exact duplicate ingestion records after verifying that the duplicated rows represent the same order event. They select the latest valid customer record before joining, while preserving customer-history information separately. Currency is converted using the documented reporting convention and the original currency is retained.

Missing delivery dates are represented with separate status categories: not applicable because cancelled, pending, and missing from the courier feed. Delivery delay is calculated only for completed orders with valid dispatch and delivery timestamps. Order values are inspected against customer and product records; legitimate high-value orders are retained and flagged rather than capped. Text labels and encodings are standardised, and date-time values are converted to a common time zone.

Before modelling, the dataset is divided chronologically so that future orders do not inform the prediction of earlier orders. Imputation and scaling rules are fitted on the training period only. Quality tests confirm unique order identifiers, valid status transitions, plausible durations, and reconciliation of total revenue with the source system.

Outcome

The prepared dataset preserves meaningful business distinctions and avoids inflated sales totals from many-to-many joins. The retailer can now analyse regional performance and build a delivery-risk model with a clearer understanding of which values are observed, inferred, unavailable, or not applicable.

Learning Points

The central lesson is that cleaning is interpretation. Missingness, duplicates, and outliers cannot be treated correctly without understanding the business process that generated them. A quality workflow also preserves raw data, documents decisions, validates relationships, and prevents information leakage.

Risks, Governance, and Ethical Responsibilities

Poor data preparation can produce discriminatory decisions, incorrect financial reports, unsafe recommendations, and loss of public trust. Imputation may disproportionately affect groups with higher missingness. Deduplication may mistakenly merge individuals with similar names. Removing outliers may erase evidence of fraud or the experience of a minority population. Analysts should therefore examine quality by relevant subgroups rather than only in aggregate.

Governance requires clear ownership, access controls, retention rules, data lineage, and versioning. Sensitive personal information should be minimised, protected, and processed for a legitimate purpose. Quality corrections must not silently alter official records without authority. Where data is used in consequential decisions, organisations should retain an explanation of source limitations, transformation rules, validation evidence, and human review procedures.

Emerging Practices

Contemporary data environments increasingly use continuous quality monitoring rather than one-time cleaning. Data contracts specify schemas, ownership, freshness, and acceptable ranges between producers and consumers. Observability systems monitor volume, distribution shifts, missingness, and pipeline failures. Machine-learning methods can detect anomalous records and suggest entity matches, but these systems require labelled examples, bias assessment, explainability, and human oversight.

Synthetic data and privacy-preserving transformations may support development when direct access to sensitive records is restricted. However, synthetic data does not automatically reproduce rare groups or important correlations, and it must be evaluated against the intended analytical use. Modern teams are also adopting “quality by design”: validation rules are placed close to data collection and ingestion so that defects are prevented or detected early rather than corrected after they have propagated.

Best Practices for Analysts

A reliable data-preparation practice rests on several principles:

• Understand the data-generating process before changing values.
• Keep raw data immutable and make every transformation reproducible.
• Define the unit of analysis and verify it after every join or aggregation.
• Distinguish unknown, not applicable, not yet observed, and erroneous values.
• Investigate outliers rather than deleting them automatically.
• Fit predictive transformations only on training data.
• Use domain rules together with statistical diagnostics.
• Measure quality before and after preparation, including by important subgroups.
• Record assumptions, thresholds, exceptions, and unresolved limitations.
• Prefer transparent, reviewable rules when the consequences of error are high.

Chapter Summary

Data preparation is a foundational component of reliable analysis. Data quality is multidimensional: accuracy, completeness, validity, consistency, timeliness, uniqueness, and integrity must be assessed in relation to intended use. Profiling establishes the structure, distributions, missingness, relationships, and semantic plausibility of a dataset before cleaning decisions are made.

Missing values require attention to their causes and should be deleted, imputed, flagged, or retained as meaningful categories only when justified. Duplicates, inconsistent labels, encoding errors, and outliers must be investigated in the context of the data-generating process. Joins require careful reasoning about keys and cardinality, while reshaping and aggregation require explicit control of data grain. Encoding, normalization, and feature construction should reflect variable meaning and must avoid leakage in predictive applications.

A professional workflow preserves raw data, documents transformations, automates quality checks, validates results, and considers ethical and governance implications. Data cleaning is therefore not the removal of inconvenient values; it is the disciplined construction of an analytical dataset whose limitations and assumptions are understood.

Key Terms

Accuracy: The extent to which a recorded value represents the true or accepted value.

Completeness: The extent to which required records and fields are present.

Validity: Conformity of data to permitted formats, types, ranges, and rules.

Consistency: Agreement among values, records, systems, and time periods.

Timeliness: Availability and currency of data for its intended use.

Uniqueness: The absence of unintended duplicate entities or events.

Referential integrity: Correct correspondence between foreign keys and referenced records.

Data profiling: Systematic examination of structure, distributions, quality, and relationships in a dataset.

MCAR: Missingness unrelated to observed and unobserved data values.

MAR: Missingness related to observed variables but not directly to the missing value after conditioning.

MNAR: Missingness related to the unobserved value itself.

Imputation: Replacement or estimation of missing values using a defined method.

Outlier: An observation unusually distant from the prevailing pattern.

Primary key: A field or field combination that uniquely identifies a row.

Foreign key: A field that refers to a key in another table.

Many-to-many join: A join in which multiple records on both sides match, potentially multiplying rows.

Data grain: The level of detail represented by one row.

Normalization: Rescaling numerical variables to a specified range or comparable scale.

Target leakage: Use of information unavailable at prediction time in a predictive feature or transformation.

Data lineage: A record of data origins, movements, transformations, and outputs.

Review Questions

Short Answer Questions

1. Why is data quality described as fitness for use rather than as a universal property?

2. Distinguish accuracy from validity using one example of each.

3. What is the difference between a primary key and a foreign key?

4. Why should an outlier not be removed automatically?

5. State one reason for preserving the original currency when converting monetary values.

Long Answer Questions

1. Explain the major dimensions of data quality and show how they can conflict in an operational dataset.

2. Compare deletion, simple imputation, model-based imputation, and missingness indicators.

3. Describe a systematic workflow for profiling and preparing a dataset for predictive modelling.

4. Explain how join cardinality, data grain, reshaping, and aggregation affect analytical results.

5. Discuss the ethical and governance responsibilities involved in cleaning personal or high-stakes data.

Analytical / Application Questions

1. A customer table contains three records for the same customer because of address history. Explain how a direct order-to-customer join could distort revenue and propose a safer approach.

2. A dataset contains missing income values mainly among respondents who declined to answer, along with a few values coded as 999. Design a treatment and justify each step.

3. A delivery dataset contains extreme delivery times, cancelled orders without delivery dates, and future delivery dates caused by a time-zone error. Develop a validation and transformation plan that preserves legitimate information while preventing misleading analysis.

22. Multiple Choice Questions with Answers

Question 1

Which situation most directly represents a validity problem?

A. A customer’s actual age is 35 but the database records 53

B. A required income field is blank

C. A percentage field contains the value 145 when the permitted range is 0 to 100

D. A report is produced two months after the decision date

Correct Answer: C. A percentage field contains the value 145 when the permitted range is 0 to 100

Question 2

Why can mean imputation be problematic when a numerical variable is strongly skewed?

A. It always creates invalid data types

B. It can be pulled toward extreme values and reduce natural variation

C. It guarantees target leakage

D. It converts categorical variables into numerical variables

Correct Answer: B. It can be pulled toward extreme values and reduce natural variation

Question 3

An order table has 1,000 rows, but joining it to a customer table produces 1,400 rows. What should be investigated first?

A. Whether the order values should be normalized

B. Whether the customer table contains multiple matching records per customer key

C. Whether all missing values should be replaced by zero

D. Whether outliers should be deleted

Correct Answer: B. Whether the customer table contains multiple matching records per customer key

Question 4

Which treatment is most appropriate for a delivery date that is missing because an order was cancelled?

A. Replace it with the median delivery date

B. Replace it with zero

C. Classify it as structurally not applicable and retain the cancellation status

D. Delete every cancelled order

Correct Answer: C. Classify it as structurally not applicable and retain the cancellation status

Question 5

Which encoding is generally most appropriate for a nominal variable such as payment method?

A. One-hot encoding

B. Ordinal codes implying distance

C. A z-score transformation

D. A logarithmic transformation

Correct Answer: A. One-hot encoding

Question 6

What is the principal risk of fitting an imputation rule on the complete dataset before splitting it into training and test sets?

A. Loss of primary keys

B. Information leakage from the test set into model development

C. Automatic duplication of every row

D. Conversion of dates into text

Correct Answer: B. Information leakage from the test set into model development

Question 7

Which statement best describes a many-to-many join?

A. Every left record matches exactly one right record

B. Only unmatched records are retained

C. Multiple records on both sides can match and multiply rows

D. The join always reduces the number of records

Correct Answer: C. Multiple records on both sides can match and multiply rows

Question 8

A legitimate high-value transaction is flagged by an interquartile-range rule. What is the most defensible immediate response?

A. Delete it because it is statistically unusual

B. Replace it with the upper quartile

C. Investigate it and retain or flag it according to evidence

D. Convert it to a missing value

Correct Answer: C. Investigate it and retain or flag it according to evidence

Question 9

Which operation maps a variable to a commonly used interval such as 0 to 1?

A. Min–max normalization

B. Deduplication

C. One-hot encoding

D. Referential validation

Correct Answer: A. Min–max normalization

Question 10

Why is defining data grain important before aggregation?

A. It determines the file encoding

B. It states what one row represents and helps prevent invalid mixing of levels

C. It guarantees that all values are accurate

D. It eliminates the need for quality checks

Correct Answer: B. It states what one row represents and helps prevent invalid mixing of levels