Data Cleaning, Transformation and Preparation

Introduction

Data analysis is only as reliable as the data supplied to it. Real-world datasets are rarely ready for immediate analysis: values may be missing, duplicated, incorrectly typed, recorded in incompatible units, entered using inconsistent spelling, or affected by measurement errors. If these problems are ignored, visualizations become misleading, statistical summaries become unreliable, and machine-learning models may learn patterns that do not represent reality.

Data cleaning, transformation and preparation is therefore a central stage in the data-analysis workflow. It converts raw observations into a dataset that is sufficiently accurate, consistent, documented and suitable for a defined analytical purpose. Preparation does not mean changing data until it produces a desired result. Every decision must be justified by the data-generating process, domain knowledge and the intended use of the dataset.

A useful preparation workflow is:

1. Understand the analytical question and data-generating process.
2. Inspect structure, types, ranges and quality problems.
3. Clean errors, duplicates and inconsistencies.
4. Transform variables into useful analytical forms.
5. Validate the prepared dataset.
6. Document decisions and implement a repeatable pipeline.

Learning Objectives

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

• identify missing values, duplicates, inconsistent formats, invalid records and outliers;
• distinguish data errors from legitimate unusual observations;
• select suitable methods for imputing, removing or retaining missing values;
• convert data types and standardize units and formats;
• encode categorical variables, scale numerical variables, bin values and aggregate records;
• derive meaningful features without introducing leakage;
• validate, document and reproduce preparation decisions;
• explain the ethical and operational risks of poorly prepared data.

1. The Role of Data Preparation

A dataset consists of observations, variables and metadata. An observation is a recorded unit, such as a customer, transaction or sensor reading. A variable is an attribute measured for each observation. Metadata describes definitions, units, sources, timestamps and allowed values.

Preparation has three related goals:

• Validity: values represent permissible states of the phenomenon being measured.
• Consistency: equivalent values use the same representation, unit and meaning.
• Fitness for purpose: the dataset is appropriate for the intended analysis or model.

A value can be valid in one context and invalid in another. For example, a negative bank balance may be valid, whereas a negative age is normally invalid. A missing value may indicate non-response, sensor failure, deliberate non-disclosure or that the variable was not applicable. These meanings should not be treated as identical.

Preparation also differs from exploratory analysis. Exploration helps discover patterns and problems; preparation creates a controlled dataset and records the decisions made. Analysts should preserve the raw data and create a separate processed version.

2. Initial Data Profiling

Before modifying data, create a profile. At minimum, inspect:

• number of rows and columns;
• variable names and definitions;
• data types;
• minimum, maximum, mean, median and selected percentiles for numerical variables;
• frequency counts for categorical variables;
• missing-value counts and patterns;
• duplicate keys or rows;
• date ranges and time zones;
• units of measurement;
• relationships between identifiers and observations.

A data dictionary should record each variable’s name, meaning, type, unit, allowed range, missing-value code and source. Codes such as -99, 9999, N/A and a blank string may all represent missingness, but they must be identified before numerical summaries are calculated.

A useful principle is to inspect data in layers: schema first, individual fields second, and relationships among fields third. For example, a sales amount may look plausible alone but conflict with quantity multiplied by unit price.

3. Missing Values

3.1 Forms and causes of missingness

Missing data may appear as null values, empty strings, special codes or impossible placeholders. Three broad mechanisms are commonly discussed:

• MCAR (Missing Completely At Random): missingness is unrelated to observed and unobserved values.
• MAR (Missing At Random): missingness is related to observed variables but, after accounting for them, not to the missing value itself.
• MNAR (Missing Not At Random): missingness depends on the unobserved value or another unmeasured process.

These categories concern the mechanism generating missingness, not merely its percentage. For example, income may be missing more often for high-income respondents because they prefer not to disclose it; this may be MNAR.

3.2 Strategies

Deletion is appropriate when missingness is small, observations are not central to the analysis, and the deletion rule will not introduce material bias. Listwise deletion removes a complete observation if any required field is missing. Pairwise deletion uses available values for each calculation but can produce different sample sizes and inconsistent comparisons.

Simple imputation replaces missing numerical values with a statistic such as the median. The median is often safer than the mean for skewed data because it is less affected by extreme values. Categorical values may be replaced by the mode or an explicit category such as “Unknown.” Imputation must be calculated from the training data only when a predictive model is being developed.

Group-wise imputation uses a relevant subgroup, such as the median income within region or the median delivery time within product category. This can preserve important structure but may be unstable for small groups.

Model-based imputation predicts missing values using other variables. It can be useful, but it introduces model assumptions and may understate uncertainty. Imputed values should be flagged so that analysts can distinguish observed from estimated information.

A missingness indicator can be defined as:

Here, M_i indicates missingness for observation i. Such an indicator is useful when the fact that a value is missing may itself carry information. It should not be used automatically; it requires domain justification.

3.3 Worked example: median imputation

Suppose delivery times in days are 2, 3, missing, 4, 25. The observed median is 3. The missing value can be replaced by 3 if the objective is a robust summary and the missingness mechanism supports this assumption. However, the value 25 must not automatically be removed: it may represent a genuine delayed delivery. The imputed value and the reason for using the median should be recorded.

4. Duplicates and Record Identity

A duplicate is not always an identical row. Exact duplicates repeat every field, while partial duplicates may share a business key such as customer ID, order ID and transaction date. Multiple records for one customer may be legitimate if the table represents transactions rather than customers.

Duplicate handling requires understanding the table’s grain, meaning what one row represents. If one row represents an order, repeated customer IDs are expected. If one row represents a customer, repeated customer IDs may indicate duplication or a one-to-many relationship incorrectly flattened into a table.

A defensible duplicate procedure is:

1. define the expected grain and key;
2. identify exact and key-based duplicates;
3. compare timestamps, source systems and completeness;
4. retain the most authoritative record or consolidate records using documented rules;
5. report how many records were removed or merged.

Never remove duplicates solely because two rows have the same name. Names are not necessarily unique identifiers.

5. Inconsistent Formats and Invalid Records

5.1 Text and categorical consistency

The categories Male, male, M and m may describe the same value, while Unknown may have a different meaning from a missing response. Standardization should use a controlled mapping table rather than uncontrolled replacement. Spelling normalization, whitespace removal and case conversion are common operations, but they must respect meaningful distinctions.

5.2 Dates and times

Dates should be converted to a real date or timestamp type. Ambiguous strings such as 03/04/2025 may mean 3 April or March 4. The source convention must be established before conversion. Time zones are important in event data: a timestamp without a zone may cause events to appear in the wrong day or sequence.

Useful derived date fields include year, month, weekday, elapsed time and whether an event occurred before a defined cutoff. Calendar features should be created from the timestamp after its interpretation is validated.

5.3 Units and numeric representation

Values must use compatible units. If distance is recorded in kilometres and miles, convert one unit before aggregation. For a distance of d_m miles, the kilometre value is approximately:

The factor 1.60934 is applicable when d_m is measured in international miles. Unit conversion is meaningful only when the unit definitions are known.

Currency conversion additionally requires an exchange-rate date and currency convention. A number formatted as 1,250 may use a comma as a thousands separator, whereas 1,250 in some locales may be interpreted differently. Parsing rules must therefore be explicit.

5.4 Validation rules

Invalid records violate logical, domain or relational rules. Examples include:

• age less than zero or greater than a plausible maximum;
• an end date before a start date;
• a quantity that is not a permitted integer;
• a percentage outside 0–100;
• a product code absent from the product master table;
• a payment marked “refunded” but with no refund date.

Validation rules should distinguish hard failures, which cannot be accepted, from warnings, which require review. Records that fail a rule should be quarantined or flagged rather than silently deleted.

6. Outliers

An outlier is an observation that is unusually distant from the rest of the data. It may be a recording error, a data-entry problem, a rare but valid case or evidence of a different population.

6.1 IQR rule

For a numerical variable, let Q₁ be the first quartile and Q₃ the third quartile. The interquartile range is:

A common screening rule identifies values below Q₁ - 1.5IQR or above Q₃ + 1.5IQR as potential outliers. The rule is distribution-free in its basic use, but it is a screening convention, not proof that an observation is erroneous.

6.2 Standard scores

For a value x, mean μ and standard deviation σ, the standard score is:

The standard score is dimensionless. It is informative when the mean and standard deviation are suitable summaries and the distribution is not severely skewed. A threshold such as |z| > 3 is a practical flag, not a universal law.

6.3 Treatment decisions

Possible responses include correcting a verified entry error, retaining a legitimate extreme value, transforming a skewed variable, winsorizing extreme values, or using a robust statistical method. Winsorization replaces values beyond selected limits with the limits; it should be documented because it changes the data distribution. Outlier treatment must be performed without using information from a future test set in predictive modeling.

7. Type Conversion and Structural Transformation

Data types communicate permitted operations. Common types include integer, decimal, Boolean, categorical, string, date, timestamp and identifier. An identifier such as account number should usually remain categorical or textual, even if it contains only digits. Converting it to a numerical measure would falsely imply meaningful arithmetic.

Structural transformation may include splitting a full name, extracting a domain from an email address, reshaping wide data into long form, or joining a fact table to a validated reference table. Joins require attention to key uniqueness. An unintended many-to-many join can multiply rows and inflate totals.

8. Encoding Categorical Variables

Categorical variables may be nominal, ordinal or binary.

• Nominal: categories have no natural order, such as payment method.
• Ordinal: categories have an order, such as low, medium and high.
• Binary: two categories, such as yes and no.

One-hot encoding creates a binary column for each category, often omitting one reference category in regression models to avoid perfect linear dependence. If categories are A, B and C, a reference-coded representation may use columns for B and C, with A represented when both are zero.

Ordinal encoding is appropriate only when order and spacing are meaningful. Assigning 1, 2 and 3 to small, medium and large implies a numerical structure that may not be justified. High-cardinality categories may require grouping rare values, hashing or carefully designed target encoding. Target encoding must be fitted within training folds to avoid target leakage.

9. Scaling Numerical Variables

Scaling is important for distance-based, gradient-based and regularized models, and for visual comparisons involving variables with different units. It is less important for many tree-based models.

9.1 Min–max scaling

For a value x, minimum x_min and maximum x_max, min–max scaling to the interval [0, 1] is:

This assumes x_max ≠ x_min. If all values are identical, the denominator is zero and a separate constant-column rule is required. Future values outside the fitted training range may produce values below 0 or above 1 unless clipping is applied.

9.2 Standardization

Standardization uses:

where μ and σ are estimated from the training data and σ > 0. The transformed variable has mean approximately 0 and standard deviation approximately 1 on the fitting data. Standardization does not make a distribution normal.

9.3 Robust scaling

For skewed data or data containing outliers, a robust alternative is:

This uses the median and interquartile range and is less sensitive to extreme values. Scaling parameters must be saved and reused when transforming validation, test or production data.

10. Binning, Aggregation and Feature Derivation

Binning converts a continuous variable into intervals. Equal-width bins divide the numerical range into intervals of the same width; quantile bins aim for similar numbers of observations per bin. Binning can improve interpretability but loses information and may create artificial boundaries. Business thresholds are often preferable when they have a substantive meaning.

Aggregation summarizes observations at a different grain. Examples include total sales per customer, average response time per day and number of incidents per machine. Every aggregation must specify the grouping key, measure, function and time window. A mean is not always appropriate: totals, medians, counts, minimums and maximums answer different questions.

Feature derivation creates variables from existing information. Examples include:

• age from date of birth and a reference date;
• profit from revenue minus cost;
• order frequency from transaction counts;
• response delay from completion time minus request time;
• rolling averages from ordered time-series observations.

A derived feature must be available at the time the prediction or decision would have been made. If it uses future information, it creates data leakage, producing unrealistically strong evaluation results.

For example, a customer’s total purchases during the entire year must not be used to predict whether the customer will purchase in March. A valid alternative is purchases accumulated only up to the prediction date.

11. Repeatable Preparation Pipelines

A preparation pipeline is an ordered set of transformations that can be executed consistently on new data. A typical pipeline is:

Raw source → schema checks → missing-value handling → validation
→ type and unit conversion → encoding/scaling → feature derivation
→ quality checks → analytical dataset

The order matters. For example, a placeholder such as -999 should be converted to missing before calculating a median. Training and test data should be split before fitting imputation, scaling, encoding or feature-selection parameters. Otherwise, information from the test set leaks into the training process.

A professional pipeline should include:

• version-controlled code and configuration;
• explicit input and output schemas;
• deterministic transformations where possible;
• saved parameters such as medians, category mappings and scaling limits;
• validation checks and failure messages;
• logging of row counts before and after each major stage;
• a data-quality report;
• tests for representative edge cases;
• a method for handling unseen categories and new missing-value patterns.

Pipeline stages should be modular. A failure in validation should not be hidden by later transformations. Idempotence is desirable: running the same preparation process twice on the same raw input should produce the same result, rather than repeatedly changing values.

12. Documentation and Governance

A preparation log should record the problem, decision, rationale, implementation, affected variables, date, responsible person and validation result. For example:

Data governance also concerns privacy and fairness. Preparation may expose personal information when fields are joined or derived. Analysts should minimize data collection, control access, protect identifiers and follow applicable laws and organizational policies. Removing records from a minority group, or imputing their values using a majority-group pattern, can produce unfair results. Quality checks should therefore be stratified by relevant groups where legally and ethically appropriate.

Automated AI tools can assist with profiling, anomaly detection and schema inference, but they do not replace domain judgment. Automatically inferred types and corrections can be wrong, and language models may produce undocumented changes. Human review, reproducibility and auditability remain essential.

13. Illustrative Case: Preparing Retail Delivery Data

Background

A retailer wants to predict late deliveries and visualize delivery performance. The raw table contains order ID, customer region, order date, promised date, delivered date, shipping method and delivery distance.

Problem

Initial profiling reveals blank delivered dates for open orders, dates recorded in two formats, distances in miles and kilometres, repeated order IDs, and several delivery durations much larger than the usual range.

Analysis

The team first defines the grain as one row per shipment. Open orders are not treated as late deliveries because a delivered date is not yet available. Dates are parsed using source-specific rules and converted to a common time zone. Distances are converted to kilometres after identifying the unit field. Repeated order IDs are investigated: some represent split shipments and are legitimate, while exact repeated extracts are duplicates.

Delivery duration is calculated only for completed shipments:

The resulting unit is days when both timestamps are interpreted in the same time zone and the subtraction is expressed in days. Negative durations are flagged as invalid. Extreme positive durations are compared with tracking events rather than automatically removed.

Approach and solution

The pipeline standardizes missing codes, validates date order, removes exact extraction duplicates, preserves legitimate split shipments, converts distance units, and creates a late-delivery indicator by comparing delivered and promised dates. A model-development split is created before fitting the median imputer and numerical scaler. Categorical shipping methods are encoded using mappings learned from training data; unseen production categories are assigned to an explicit “Other” handling rule.

Outcome

The retailer obtains consistent daily metrics and a reproducible modeling dataset. A small group of apparently extreme observations is retained because event logs confirm genuine delays. These observations become useful cases for operational improvement rather than being erased as “bad data.”

Learning points

The case demonstrates that preparation depends on table grain, business meaning and timing. A technically convenient deletion would have removed valid split shipments and severe service failures. Correct preparation improved both analytical accuracy and managerial usefulness.

14. Challenges and Limitations

No cleaning method guarantees truth. Imputation can reduce variation and conceal uncertainty. Deduplication can remove legitimate repeated events. Outlier rules can classify rare but important cases as errors. Encoding and binning may simplify data while discarding information. Feature engineering can create leakage if timing is ignored. Automated pipelines can scale mistakes as efficiently as correct decisions.

Preparation also involves trade-offs between consistency and fidelity. Standardizing categories may make reporting easier but erase meaningful distinctions. A single global rule may be inappropriate across regions, instruments or time periods. Analysts should review quality over time because changes in source systems can create sudden shifts that resemble changes in the underlying population.

Best-Practice Checklist

• Preserve the raw source and record its version.
• Define the row grain and keys before removing duplicates.
• Profile missingness, types, ranges and category frequencies.
• Replace sentinel missing codes before calculating statistics.
• Validate domain and relational rules.
• Investigate outliers using context, not thresholds alone.
• Fit preparation parameters on training data only.
• Prevent temporal and target leakage.
• Save mappings, parameters, logs and validation results.
• Review consequences across relevant populations.
• Make the pipeline repeatable, testable and auditable.

Chapter Summary

Data cleaning and preparation transform raw records into a consistent and fit-for-purpose analytical dataset. Effective work begins with profiling, a data dictionary and an understanding of the data-generating process. Missing values require decisions based on their cause and analytical purpose. Duplicates must be evaluated according to the table’s grain, while inconsistent formats require controlled mappings, valid type conversion and unit standardization. Invalid records should be flagged or quarantined using explicit rules.

Outliers are signals for investigation rather than automatic errors. The IQR and standard-score methods provide useful screening tools, but domain context determines treatment. Encoding, scaling, binning, aggregation and feature derivation make data suitable for analysis and machine learning, provided that information loss and leakage are controlled. Finally, professional preparation requires versioned pipelines, validation, documentation, privacy protection and fairness awareness.

Key Terms

Data preparation: The process of making data suitable for analysis or modeling.

Data dictionary: A structured description of variables, meanings, types, units and rules.

Missing value: An observation for which the intended value is unavailable or unrecorded.

MCAR: Missing completely at random.

MAR: Missing at random conditional on observed information.

MNAR: Missing not at random.

Imputation: Replacement of missing values using a defined method.

Duplicate: A repeated record or repeated business entity that violates the intended grain.

Grain: The entity or event represented by one row.

Validity: The degree to which a value conforms to permitted rules.

Outlier: An unusually distant observation relative to a reference distribution.

IQR: The difference between the third and first quartiles.

Encoding: Representing categorical information numerically.

One-hot encoding: Creation of binary indicator columns for categories.

Scaling: Rescaling numerical variables to a comparable numerical range.

Binning: Converting a continuous variable into defined intervals.

Aggregation: Combining multiple records into grouped summaries.

Feature derivation: Creation of an analytical variable from existing data.

Data leakage: Use of information unavailable at the time of prediction or decision.

Pipeline: An ordered, repeatable sequence of data transformations and checks.

Review Questions

Short Answer Questions

1. Why should the grain of a table be identified before removing duplicates?
2. Distinguish MCAR, MAR and MNAR missingness.
3. What is the purpose of a data dictionary?
4. Why should scaling parameters be fitted using training data only?
5. State two reasons why an outlier should not automatically be deleted.

Long Answer Questions

1. Explain a complete workflow for profiling and preparing a newly received dataset.
2. Compare deletion, simple imputation, group-wise imputation and model-based imputation.
3. Explain how categorical variables can be encoded and discuss when ordinal encoding is inappropriate.
4. Describe the risks of data leakage and provide two examples involving derived features.
5. Discuss the documentation, privacy and fairness responsibilities associated with data preparation.

Analytical / Application Questions

1. A customer table contains repeated customer IDs, blank income values, ages of −4 and 240, and categories recorded as Gold, gold and G. Design a preparation plan and justify each decision.
2. A delivery dataset contains distances in miles and kilometres, timestamps in different time zones, and several very long delivery times. Explain how you would standardize, validate and investigate the data without deleting genuine delays.
3. A model predicts March purchases. The analyst proposes using each customer’s total purchases for the entire year as a feature. Identify the problem, explain why it affects evaluation, and propose a valid alternative.

Multiple Choice Questions with Answers

Question 1

Which statement best describes data leakage?

A. Using a median to replace a missing value

B. Using information unavailable at prediction time

C. Converting a string to a date type

D. Removing an exact duplicate row

Correct Answer: B. Using information unavailable at prediction time

Question 2

For a numerical variable, the interquartile range is calculated as:

A. Q₁ + Q₃

B. Q₃ − Q₁

C. Mean − median

D. Maximum − minimum

Correct Answer: B. Q₃ − Q₁

Question 3

Which variable should usually remain categorical rather than numerical?

A. Temperature in degrees Celsius

B. Distance in kilometres

C. Account identification number

D. Quantity of items

Correct Answer: C. Account identification number

Question 4

What is a primary advantage of median imputation over mean imputation for strongly skewed data?

A. It is less affected by extreme values

B. It always reproduces the original distribution

C. It requires no assumptions about missingness

D. It increases the number of observed values

Correct Answer: A. It is less affected by extreme values

Question 5

In min–max scaling to [0, 1], what happens if the fitted minimum equals the fitted maximum?

A. The result is always normally distributed

B. The denominator becomes zero

C. All values become negative

D. The variable becomes categorical

Correct Answer: B. The denominator becomes zero

Question 6

Why is the table grain important when evaluating duplicate records?

A. It determines the font used in a report

B. It identifies what one row represents

C. It automatically fills missing values

D. It guarantees that all keys are unique

Correct Answer: B. It identifies what one row represents

Question 7

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

A. One-hot encoding

B. Subtracting its mean

C. Calculating its standard deviation

D. Treating category labels as ordered scores

Correct Answer: A. One-hot encoding

Question 8

A value below Q₁ − 1.5IQR is best described as:

A. A guaranteed data-entry error

B. A potential outlier under a common screening rule

C. A missing value

D. A duplicate key

Correct Answer: B. A potential outlier under a common screening rule

Question 9

When developing a predictive model, when should an imputer be fitted?

A. After combining training and test sets

B. Using the test set only

C. Using the training data only

D. Using future production data

Correct Answer: C. Using the training data only

Question 10

Which action best supports reproducible data preparation?

A. Making undocumented manual edits

B. Changing rules for each report

C. Saving transformation parameters and versioning the pipeline

D. Deleting the raw dataset after processing

Correct Answer: C. Saving transformation parameters and versioning the pipeline