Data Cleaning, Preparation, and Feature Engineering

Introduction

Machine-learning performance depends not only on the choice of algorithm but also on the quality, structure, and provenance of the data supplied to it. Real-world datasets commonly contain missing observations, duplicate records, inconsistent units, abnormal values, typing errors, and variables that accidentally reveal the target. Data preparation converts such data into a reliable analytical representation while preserving legitimate information.

Data cleaning is not merely a technical pre-processing step. It involves judgement about meaning, measurement, context, fairness, and acceptable evidence. A value may be statistically unusual but operationally correct; a missing value may indicate non-response, system failure, or a meaningful business event. Consequently, every transformation should be justified, reproducible, tested, and documented.

Learning Objectives

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

• diagnose missing values, duplicates, outliers, inconsistent formats, leakage, and bias;
• select suitable cleaning and imputation strategies;
• encode categorical variables and scale or transform numerical variables;
• construct features while avoiding target leakage;
• select variables using statistical, model-based, and domain methods;
• build reusable preprocessing pipelines in Python;
• document assumptions, data lineage, and transformation decisions;
• evaluate the ethical and operational consequences of data preparation.

1. The Data-Preparation Workflow

A robust workflow is iterative rather than a single linear sequence:

1. Define the analytical purpose. Identify the prediction target, unit of analysis, prediction time, acceptable error, and intended users.
2. Inspect the data. Examine shape, data types, ranges, unique values, distributions, and relationships.
3. Profile data quality. Measure missingness, duplication, validity, consistency, and possible bias.
4. Split the data. Create training, validation, and test sets before learning transformations from the data.
5. Fit transformations on training data only. This prevents information from the validation or test sets entering the model.
6. Validate and monitor. Test assumptions, compare distributions, inspect fairness, and record the data lineage.

A useful distinction is between structural validity and substantive validity. Structural validity asks whether a field has the correct type and format, such as a date stored as a date. Substantive validity asks whether its value makes sense in context, such as an account closure date preceding its opening date.

2. Data Quality Dimensions

Important dimensions include:

For a column with n records and m missing values, the missingness rate is:

Here, r_missing is a proportion between 0 and 1. It is meaningful only when the denominator and definition of “missing” are clear. Blank strings, special codes such as −999, and “unknown” categories may need to be identified before calculating it.

3. Missing Values

3.1 Mechanisms of missingness

Missingness is often classified as:

• MCAR (Missing Completely At Random): missingness is unrelated to observed and unobserved values. This strong assumption is uncommon.
• MAR (Missing At Random): missingness depends on observed variables, after conditioning on them. For example, income may be missing more often for younger respondents.
• MNAR (Missing Not At Random): missingness depends on the unobserved value itself or an unmeasured cause. High-income individuals may be less willing to report income.

These categories are modelling assumptions, not labels that can always be proven from the dataset. Missingness itself may be predictive, so an indicator variable can sometimes be appropriate. It must not, however, be used to conceal poor data collection.

3.2 Treatment strategies

1. Deletion: Remove rows or columns when missingness is small, plausibly random, and the remaining sample is adequate. Complete-case deletion can introduce bias when missingness is systematic.
2. Simple imputation: Use the training-set mean or median for numerical features, and the mode or an explicit “Unknown” category for categorical features. The median is usually more robust to skew and outliers.
3. Group-wise imputation: Impute using relevant groups, such as median income within region, only when the grouping variable is available at prediction time.
4. Model-based imputation: Use methods such as iterative regression or nearest-neighbour imputation. These can preserve relationships but may amplify model assumptions and computational cost.
5. Missingness indicators: Add a binary variable indicating whether the original value was missing. This is useful when the fact of non-response carries information.

All imputation statistics must be computed on the training set only. For a training-set mean, where observed values are x₁, x₂, …, x_k,

The imputed value is then x̄. This assumes that the mean is an appropriate representation and that using it does not erase important variation.

3.3 Python example

import pandas as pd
from sklearn.impute import SimpleImputer

num_imputer = SimpleImputer(strategy="median", add_indicator=True)
X_num = num_imputer.fit_transform(X_train_num)
X_test_num = num_imputer.transform(X_test_num)

cat_imputer = SimpleImputer(strategy="most_frequent")
X_cat = cat_imputer.fit_transform(X_train_cat)
X_test_cat = cat_imputer.transform(X_test_cat)

The use of fit_transform on training data and transform on test data is essential. Fitting separately on the test set produces optimistic evaluation.

4. Duplicates and Record Identity

Duplicates may be exact copies, near-duplicates, or multiple legitimate events involving the same entity. Removing every repeated identifier is unsafe: one customer may make many purchases, and one patient may have multiple visits.

A defensible process is:

• define the unit of analysis;
• identify a natural or generated key;
• distinguish repeated entities from repeated events;
• compare timestamps and source systems;
• retain the most authoritative or latest record only when policy supports it;
• record how many records were merged or removed.

For near-duplicates, standardise text and formats before comparing records. Fuzzy matching should use a threshold validated against labelled examples because false merges can be more damaging than missed matches.

5. Inconsistent Formats and Invalid Values

Common inconsistencies include dates written as 03/04/2024, which may mean 3 April or 4 March; weights recorded in kilograms and pounds; and categorical values such as Male, M, and male.

Recommended controls include:

• parse dates with an explicitly documented convention;
• store time zones and distinguish event time from processing time;
• convert units using verified conversion factors;
• standardise case, whitespace, spelling, and controlled vocabularies;
• validate ranges and cross-field rules;
• preserve the raw field while creating a cleaned field;
• quarantine records that fail validation rather than silently deleting them.

For example, if a value is in pounds and must be converted to kilograms, the conversion is approximately:

The factor is applicable to mass measured in avoirdupois pounds; it should not be applied to a field whose unit is unknown.

6. Outliers and Anomalies

An outlier is an observation that is unusually distant from other observations. It may represent a data error, a rare but valid case, a change in population, fraud, or a heavy-tailed distribution.

6.1 Detection methods

The z-score for observation xᵢ is:

where μ is the mean and σ is the standard deviation. It is most informative for approximately symmetric, light-tailed data and is sensitive to extreme values.

The interquartile range is:

where Q₁ and Q₃ are the first and third quartiles. A common exploratory rule flags values below Q₁ − 1.5 × IQR or above Q₃ + 1.5 × IQR. This is a screening rule, not proof that a value is erroneous.

Other approaches include domain limits, robust statistics, isolation forests, clustering, and time-series change detection. Detection should be performed on the training data when thresholds are learned, and any later monitoring must account for population drift.

6.2 Treatment

Possible actions are correction from a trusted source, retention, winsorisation, transformation, robust modelling, or removal. Removing an observation merely because it harms model accuracy is unethical and can create biased samples. The decision should be based on evidence and documented separately for errors and legitimate extremes.

7. Encoding Categorical Variables

Categorical variables may be nominal, ordinal, binary, or high-cardinality.

• One-hot encoding creates one binary feature per category. It is suitable for nominal variables but can produce many columns.
• Ordinal encoding assigns ordered integers, such as low = 1, medium = 2, high = 3. It is appropriate only when order is real and the model can interpret the representation appropriately.
• Frequency encoding replaces a category with its training-set frequency. It is compact but may lose semantic distinctions.
• Target encoding replaces a category with an estimate based on the target. It can be powerful but is highly vulnerable to leakage and requires out-of-fold or smoothed training procedures.
• Hashing or learned embeddings can manage very high cardinality, but collisions, interpretability, and governance must be considered.

Unknown categories at deployment should be handled explicitly, for example with handle_unknown="ignore" in one-hot encoding. Encoding must be fitted on training data only.

8. Scaling and Transforming Numerical Variables

Scaling is important for distance-based, gradient-based, and regularised algorithms, but often less important for tree-based models.

Standardisation is:

where the mean and standard deviation are estimated from training data. The result has training-set mean approximately 0 and standard deviation approximately 1, assuming σ_train > 0.

Min–max scaling to an interval [a,b] is:

It assumes x_max ≠ x_min and is sensitive to future values outside the training range.

For positively skewed measurements, a logarithmic transformation can reduce skew:

This form is defined for x ≥ 0 and is useful for counts and monetary values with many small observations. Transformation changes interpretation; it does not automatically make data normal.

9. Feature Engineering

Feature engineering constructs variables that express domain knowledge or useful structure.

Examples include:

• date parts such as month, weekday, and elapsed time;
• ratios such as expenditure per customer;
• counts over a defined time window;
• interaction terms representing combined effects;
• rolling averages for time series;
• text features such as token counts or embeddings;
• geographical distance and aggregate behavioural measures.

A feature must be available at the exact time the prediction would be made. For example, “number of complaints in the next 30 days” is not a valid predictor for today’s decision, even if it is highly predictive in a historical dataset.

Leakage

Target leakage occurs when a feature contains information unavailable at prediction time or information generated after the target event. Leakage may arise from post-outcome fields, random splitting of temporal records, preprocessing before splitting, target encoding, or duplicated individuals across train and test sets.

A practical leakage audit asks:

• When was the feature created?
• Who created it and from which system?
• Was it available to the decision-maker at prediction time?
• Does the feature incorporate the label directly or indirectly?
• Could the same entity appear in multiple partitions?

Time-dependent problems generally require chronological splitting. Grouped problems may require group-aware splitting so that records from one customer, patient, or machine do not cross partitions.

10. Feature Selection

Feature engineering creates candidates; feature selection retains useful and defensible variables.

• Filter methods use data-independent or target-association measures, such as variance thresholds, correlation, mutual information, or statistical tests. They are fast but may ignore interactions.
• Wrapper methods evaluate subsets using a predictive model, such as recursive feature elimination. They can be accurate but computationally expensive.
• Embedded methods select during model fitting, such as L1 regularisation or tree-based importance. Importance can be unstable or biased toward high-cardinality variables.

Feature selection must occur inside cross-validation when used for performance estimation. If the entire dataset is used to select features before cross-validation, information leaks from validation folds into the training process.

Correlation is not a complete selection criterion. Two strongly correlated variables may both be useful, while an apparently weak variable may improve performance through interaction. Domain relevance, stability, fairness, interpretability, and deployment cost should also influence selection.

11. Reusable Scikit-learn Pipelines

A pipeline expresses the sequence of operations and ensures that fitting is performed consistently.

from sklearn.compose import ColumnTransformer
from sklearn.pipeline import Pipeline
from sklearn.preprocessing import OneHotEncoder, StandardScaler
from sklearn.impute import SimpleImputer
from sklearn.linear_model import LogisticRegression

numeric_pipe = Pipeline([
    ("impute", SimpleImputer(strategy="median", add_indicator=True)),
    ("scale", StandardScaler())
])

categorical_pipe = Pipeline([
    ("impute", SimpleImputer(strategy="most_frequent")),
    ("encode", OneHotEncoder(handle_unknown="ignore"))
])

preprocess = ColumnTransformer([
    ("numeric", numeric_pipe, numeric_columns),
    ("categorical", categorical_pipe, categorical_columns)
])

model = Pipeline([
    ("preprocess", preprocess),
    ("classifier", LogisticRegression(max_iter=1000))
])

model.fit(X_train, y_train)
predictions = model.predict(X_test)

This design prevents inconsistent manual transformations and supports reproducible deployment. The fitted pipeline, package versions, feature schema, and configuration should be versioned together.

12. Worked Example: A Customer-Churn Dataset

Assume a telecommunications company predicts whether a customer will leave within the next 30 days. The dataset contains customer ID, contract type, monthly charge, tenure, region, support calls, and churn status.

Analysis

Initial profiling finds blank monthly charges, duplicated customer IDs, region labels with inconsistent spelling, a few negative tenure values, and a field called cancellation_date. The cancellation date is recorded after a customer leaves, so it is unavailable at prediction time and must be excluded. Randomly splitting multiple monthly records from the same customer would also make evaluation unrealistically easy.

Approach

The team defines the prediction unit as one customer-month and splits data chronologically. Duplicate customer-month keys are investigated rather than blindly removed. Negative tenure values are checked against the source system; confirmed entry errors are corrected, while unresolved records are quarantined. Monthly charge is median-imputed using training data, with a missingness indicator. Region is standardised and one-hot encoded. Numerical variables are scaled for logistic regression. A pipeline performs all fitted transformations.

The team evaluates discrimination and calibration, but also compares false-negative rates across relevant groups. Feature documentation records each source, extraction time, transformation, availability time, and business meaning.

Outcome

The resulting model has a lower apparent score than the leaked baseline but performs more reliably on future months. Operations staff can explain the major predictors, and monitoring detects when a new region code appears or the missing-charge rate changes sharply.

Learning points

The most predictive feature is not necessarily the most valid feature. Temporal availability, entity-level splitting, and documented assumptions are central to trustworthy modelling. Cleaning decisions affect not only accuracy but also customer treatment and organisational risk.

13. Governance, Ethics, and Responsible Practice

Data preparation can reproduce or intensify social disadvantage. Missingness may be concentrated among people with limited internet access; historical labels may reflect discriminatory decisions; and removing records from a small group can make that group invisible.

Responsible practice includes:

• documenting lawful purpose, consent, retention, and access controls;
• minimising collection and protecting sensitive attributes;
• assessing representation and performance across relevant groups;
• retaining sensitive attributes when necessary for fairness auditing, with appropriate safeguards;
• avoiding proxy variables that encode protected characteristics without justification;
• recording human review, overrides, and appeals;
• monitoring drift and fairness after deployment.

Fairness is not achieved by a single cleaning rule. Different definitions—such as equal error rates, calibration, or demographic parity—may conflict, and the appropriate choice depends on the application, legal context, and harms involved.

14. Data Lineage and Documentation

A data-preparation record should identify:

A data dictionary explains fields; a lineage record explains where values came from and how they changed. Together they support reproducibility, auditability, debugging, and handover between data scientists and operational teams.

15. Contemporary Developments and Limitations

Modern platforms increasingly automate profiling, feature generation, and drift detection. Automated machine learning can compare preprocessing strategies, while feature stores provide centrally governed, reusable features. However, automation does not solve ambiguous definitions, hidden leakage, poor source quality, or fairness concerns. Generated features may also increase complexity and attack surface.

Data cleaning remains partly domain-dependent. No universal threshold identifies every outlier, no imputation method restores information that was never collected, and no pipeline can guarantee fairness without understanding the social process that generated the data. The appropriate objective is not perfectly clean data but fit-for-purpose, transparent, and monitored data.

Chapter Summary

Data cleaning and preparation establish the reliability of machine-learning systems. Effective practice begins by defining the analytical unit, prediction time, and data-quality requirements. Missing values should be investigated through their mechanisms and treated using methods justified by context. Duplicates, inconsistent formats, invalid values, and outliers require domain-aware validation rather than automatic deletion.

Categorical variables may be encoded through one-hot, ordinal, frequency, target, or high-cardinality methods. Numerical variables may be standardised, scaled, or transformed when appropriate. Feature engineering can express temporal, behavioural, textual, and domain relationships, but every feature must be available at prediction time. Leakage prevention requires correct splitting, training-only fitting, and careful auditing.

Feature selection should combine statistical evidence, model performance, domain knowledge, stability, fairness, and deployment constraints. Reusable pipelines reduce errors and improve reproducibility. Finally, data lineage, assumptions, governance, and post-deployment monitoring are essential because preparation decisions influence model validity, fairness, and organisational accountability.

Key Terms

Missing value: An absent, unknown, or unrecorded observation.

MCAR: Missingness unrelated to observed and unobserved data values.

MAR: Missingness explainable by observed variables after conditioning.

MNAR: Missingness related to unobserved values or causes.

Imputation: Replacing missing values using a defined procedure.

Duplicate: A repeated or incorrectly repeated record.

Outlier: An unusually distant observation that may be erroneous or legitimate.

IQR: The difference between the third and first quartiles.

Validity: Conformance to permitted formats, ranges, or rules.

One-hot encoding: Representation of categories using binary indicator columns.

Ordinal encoding: Mapping ordered categories to numerical codes.

Standardisation: Transformation using a training mean and standard deviation.

Feature engineering: Construction of informative variables from available data.

Target leakage: Use of information unavailable at prediction time or derived from the outcome.

Feature selection: Choosing a subset of variables for modelling.

Pipeline: A reproducible sequence of fitted data transformations and modelling steps.

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

Data drift: Change in data distributions or relationships over time.

Review Questions

Short Answer Questions

1. Distinguish MCAR, MAR, and MNAR missingness.
2. Why should imputation statistics be fitted on training data only?
3. State one appropriate use and one limitation of one-hot encoding.
4. What is target leakage, and why does it inflate evaluation results?
5. What information should a data-lineage record contain?

Long Answer Questions

1. Explain a complete workflow for diagnosing and treating missing values, including statistical and governance considerations.
2. Compare z-score and IQR-based outlier detection, including their assumptions and limitations.
3. Discuss numerical scaling and transformation methods and identify algorithms for which scaling is especially important.
4. Explain how a reusable ColumnTransformer and pipeline improve reproducibility and prevent leakage.
5. Evaluate the ethical risks of deleting records with missing values from an under-represented group.

Analytical / Application Questions

1. A dataset contains monthly records for the same customers. Design a training, validation, and test strategy that prevents entity and temporal leakage.
2. A categorical variable has 50,000 levels and several levels occur only once. Recommend an encoding strategy and explain the risks of target encoding.
3. A model’s test accuracy is unusually high after a new feature is added. Describe an investigation to determine whether the feature is leaked, duplicated, or genuinely informative.

Multiple Choice Questions with Answers

Question 1

Which action best prevents imputation leakage?

A. Fit the imputer separately on every dataset

B. Fit the imputer on the training set and apply it to validation and test sets

C. Replace all missing values with zero before splitting

D. Use the complete dataset to calculate the median

Correct Answer: B. Fit the imputer on the training set and apply it to validation and test sets

Question 2

Which missingness mechanism depends on the unobserved value itself or an unmeasured cause?

A. MCAR

B. MAR

C. MNAR

D. Random sampling

Correct Answer: C. MNAR

Question 3

For a numerical feature with Q₁ = 10 and Q₃ = 18, what is the upper 1.5-IQR screening boundary?

A. 20

B. 24

C. 30

D. 36

Correct Answer: B. 24

Question 4

Which encoding is generally suitable for a nominal variable such as country?

A. One-hot encoding

B. Ordinal encoding with arbitrary ranks

C. Replacing each country with the target mean without validation

D. Deleting the variable

Correct Answer: A. One-hot encoding

Question 5

Why is scaling especially important for k-nearest neighbours?

A. It converts categorical values into labels

B. It prevents all missing values

C. Distance calculations can otherwise be dominated by large-unit features

D. It guarantees normally distributed data

Correct Answer: C. Distance calculations can otherwise be dominated by large-unit features

Question 6

Which feature is most clearly an example of target leakage in a churn model?

A. Tenure at the prediction date

B. Number of support calls before the prediction date

C. Contract type at the prediction date

D. Cancellation date recorded after the customer leaves

Correct Answer: D. Cancellation date recorded after the customer leaves

Question 7

What is the primary purpose of a preprocessing pipeline?

A. To remove the need for a test set

B. To apply transformations consistently and reproducibly

C. To guarantee that a model is unbiased

D. To replace domain knowledge

Correct Answer: B. To apply transformations consistently and reproducibly

Question 8

Which statement about outliers is most appropriate?

A. Every outlier must be deleted

B. Every outlier is a measurement error

C. An outlier may be a valid rare event and requires contextual investigation

D. Outliers occur only in numerical variables

Correct Answer: C. An outlier may be a valid rare event and requires contextual investigation

Question 9

A min–max transformation maps a value using the training minimum and maximum. What is a major limitation?

A. It cannot be applied to numerical data

B. It is sensitive to extreme training values and future values outside the range

C. It always creates missing values

D. It automatically removes target leakage

Correct Answer: B. It is sensitive to extreme training values and future values outside the range

Question 10

Which practice best supports auditability?

A. Overwriting the raw data without recording changes

B. Keeping only the final model score

C. Documenting sources, transformations, assumptions, versions, and exclusions

D. Using undocumented manual spreadsheet edits

Correct Answer: C. Documenting sources, transformations, assumptions, versions, and exclusions