Data Preparation and Feature Engineering

Introduction

Data preparation is the disciplined transformation of raw, heterogeneous observations into an analytical dataset that is valid, useful, reproducible, and suitable for modelling. In data warehousing, preparation begins before a dataset reaches a machine-learning algorithm: source systems must be profiled, integrated, reconciled, historised, and governed. In data mining, preparation determines what patterns can be discovered and whether those patterns represent the population or merely artifacts of collection and processing.

A useful principle is that feature engineering is not cosmetic preprocessing. It is the construction of measurable representations of a phenomenon. A poorly designed feature may encode future information, institutional bias, or a data-entry convention rather than the underlying business process. Conversely, a carefully engineered feature can make a simple model accurate, interpretable, and operationally valuable.

A standard preparation pipeline is:

Figure: A governed preparation pipeline with feedback from deployment monitoring.

The central challenge is to improve signal without introducing leakage, unfairness, instability, or unjustified assumptions.

Learning Objectives

By the end of this chapter, a postgraduate learner should be able to:

• design sampling and dataset-splitting strategies for supervised and unsupervised mining;
• integrate heterogeneous warehouse and operational sources while preserving meaning and lineage;
• diagnose and treat missing values, outliers, inconsistent categories, and measurement errors;
• encode, scale, transform, and discretise variables appropriately;
• select features and reduce dimensionality using statistically and operationally defensible criteria;
• address class imbalance and evaluate models with leakage-resistant procedures;
• identify bias, privacy, fairness, and governance risks in engineered data products.

1. Data Understanding Before Transformation

Preparation begins with a data contract: a documented description of entities, keys, grain, units, permissible values, timestamps, target definition, refresh frequency, and ownership. The grain states what one row represents—for example, one customer, one order, or one customer-month. Joining tables with incompatible grain can silently duplicate observations and distort frequencies.

Data profiling examines completeness, uniqueness, validity, consistency, distributions, and relationships. For variable X, the sample mean and variance are commonly computed as:

Here n is the number of observed values, xᵢ is observation i, x̄ is the sample mean, and s² is the unbiased sample variance under the usual independent-sampling interpretation. The denominator n−1 is appropriate for estimating a population variance from a sample; it is not required when describing the variance of an entire finite population. Robust summaries such as the median and interquartile range (IQR) are preferable when distributions are skewed.

A preparation log should record the source version, transformation, parameters learned from data, excluded records, software version, and reason for every decision. This is essential for reproducibility and auditability.

2. Sampling, Splitting, and Representativeness

Sampling reduces computational cost or creates a manageable training set, but it can also change the population being represented. Simple random sampling gives each unit equal selection probability. Stratified sampling samples within defined groups, preserving representation of important subpopulations. Cluster sampling selects groups such as branches or hospitals and then observes units within them; it is operationally convenient but may increase sampling variance because members of a cluster resemble one another.

If a population contains groups of sizes Nₕ and a sample of n units is required, proportional stratified allocation is:

where N = ∑ₕ Nₕ and nₕ is the sample allocated to stratum h. This allocation is appropriate when within-stratum costs and variability are broadly similar. Disproportionate sampling may be better for rare but important groups, provided analysis uses selection weights when estimating population quantities. If unit i has inclusion probability πᵢ, its basic inverse-probability weight is wᵢ = 1/πᵢ.

For predictive modelling, a hold-out test set should approximate the intended deployment population. Random splits are inappropriate for time-dependent data, repeated measurements from the same person, or grouped observations. Use:

• temporal splitting when future prediction is the task;
• group splitting when records from one entity must not appear in both training and test data;
• stratification when preserving class proportions is important and observations are independent;
• nested cross-validation when tuning and unbiased performance estimation are both required.

All preprocessing parameters—imputation values, category vocabularies, scaling constants, selected features, and principal components—must be learned on the training portion only. Applying a transformation before splitting permits information from validation or test data to influence training, which is data leakage.

3. Integration and Data Quality

Integration combines data from operational databases, warehouses, files, APIs, sensors, and external providers. It involves schema matching, entity resolution, key management, unit harmonisation, temporal alignment, and conflict resolution.

A natural key, such as an email address, may change or be shared. A warehouse surrogate key provides a stable internal identifier, while a cross-reference table maps source identifiers to the conformed entity. Slowly changing dimensions require an explicit decision about history: Type 1 overwrites a value, whereas Type 2 preserves versions with effective and expiry timestamps. The correct choice depends on whether historical analysis must reflect knowledge available at the time.

Integration controls include duplicate detection, referential-integrity checks, reconciliation to source totals, and unit checks. For example, kilograms must not be combined with pounds without conversion. Timestamp operations must state time zone and whether a feature is available before the prediction instant. A feature such as “account status at month end” is valid for a month-end prediction only if it was known by that time.

Entity resolution should be conservative. Probabilistic matching can compare name, address, telephone, and date of birth, but false merges may be more damaging than missed matches. Sensitive identifiers should be minimised, protected, and separated from modelling tables.

4. Missing Values

Missingness is not a single condition. A value may be missing completely at random (MCAR), missing at random conditional on observed variables (MAR), or missing not at random (MNAR), where missingness depends on the unobserved value or a related unobserved process. These are assumptions about a data-generating mechanism, not labels that can be proven from a table alone.

Let R = 1 indicate that X is observed and R = 0 that it is missing. MCAR means that the distribution of R is unrelated to observed and unobserved variables. Under MAR, missingness may depend on observed covariates Z, while MNAR involves dependence on unobserved X even after conditioning on Z. The mechanism affects whether deletion or imputation can support valid inference.

Common strategies are:

• deletion, appropriate only when loss is small and the resulting sample remains representative;
• constant or indicator coding, useful when “unknown” has operational meaning;
• mean or median imputation, simple but reducing variability and potentially weakening relationships;
• groupwise imputation, valid only when the grouping variable is available and substantively justified;
• model-based or multiple imputation, which represents uncertainty more adequately for statistical inference;
• algorithms that natively handle missing values, subject to their documented semantics.

A missingness indicator Mⱼ can be added for variable Xⱼ:

This may preserve predictive information about the process causing missingness, but it can also encode unequal access, administrative practice, or discrimination. Imputation must be fitted within each training fold. For a skewed numeric variable, training-median imputation is often more robust than training-mean imputation. Never replace an unknown value with zero unless zero genuinely means absence.

5. Outliers and Anomalies

An outlier is an observation unusual relative to a reference distribution; it is not automatically an error. It may be a valid extreme customer, a sensor failure, a fraud signal, or a population mixture. Treatment therefore follows diagnosis rather than an automatic deletion rule.

The IQR rule defines lower and upper fences as:

Q₁ and Q₃ are the first and third quartiles. Values outside [L, U] are flagged, not necessarily removed. The 1.5 multiplier is a descriptive convention, not a universal law, and becomes less informative in heavy-tailed or multimodal distributions.

Alternatives include domain limits, robust z-scores based on the median and median absolute deviation, transformations such as log(1 + x) for non-negative skewed data, winsorisation, robust models, and anomaly-detection methods. Capping must be learned from training data and documented because it changes the estimand. If the target is fraud, removing unusual transactions may remove the very cases of interest.

6. Encoding Categorical and Textual Variables

Categorical variables require representations compatible with the algorithm. Nominal categories have no intrinsic order; ordinal categories do. One-hot encoding creates a binary feature for each level, usually dropping one level in a linear model with an intercept to avoid perfect multicollinearity. Tree models do not require the same treatment, but implementation-specific handling must be verified.

High-cardinality variables such as product code or postal code may produce sparse, unstable matrices. Options include frequency encoding, hashing, learned embeddings, hierarchical grouping, or target encoding. Target encoding replaces a category with an estimate related to the target, but naïve encoding leaks target information. It should use out-of-fold estimates, smoothing, and training data only. For category c, a simple smoothed binary target encoding is:

where n_c is the category count, μ_c its training target mean, μ the global training target mean, and α > 0 a smoothing strength. The formula is applicable to a binary target when means represent event rates; it must be adapted for other target types. Larger α shrinks rare-category estimates toward the global mean.

Ordinal encoding is appropriate only when numerical order is meaningful. Assigning “red = 1, blue = 2, green = 3” falsely creates distances and ordering. Text may be represented through token counts, TF–IDF, topic representations, or embeddings. Any vocabulary or embedding adaptation must respect the train/test boundary.

7. Scaling and Transformation

Scaling is essential for distance-based methods, nearest neighbours, clustering, support-vector machines, regularised linear models, and gradient-based optimisation. It is usually less important for decision trees, although extreme numerical ranges can still affect implementation and interpretation.

Standardisation uses training mean μ and standard deviation σ:

The result is dimensionless, with training mean approximately 0 and standard deviation approximately 1 when σ > 0. Min–max scaling to [a, b] is:

where x_min and x_max are training-set extrema and a < b. It is sensitive to future extremes and should not be confused with normalisation of a probability distribution. Robust scaling can use the training median and IQR. Logarithmic transformations require suitable domains; log(x) requires x > 0, while log(1 + x) is defined for x ≥ 0.

Scaling does not remove outliers and does not make a distribution normal. A transformation should be selected because it improves model assumptions, numerical conditioning, or interpretability—not merely because it is conventional.

8. Discretisation and Feature Construction

Discretisation converts a continuous variable into intervals. Equal-width bins divide the numerical range into intervals of equal width; equal-frequency bins aim for similar observation counts. Domain thresholds are often more interpretable—for example, clinically defined risk bands—but thresholds can create discontinuities and information loss.

Supervised binning, including weight-of-evidence approaches, must be fitted within training data and monitored for stability. Bins should be sufficiently populated, meaningful, and capable of handling future values. Missingness and values outside historical limits need explicit bins or policies.

Feature construction uses domain knowledge and mathematically valid aggregation. Examples include tenure = prediction date − start date, rolling transaction count over the preceding 30 days, ratio of debt to income, and lagged sales. The temporal phrase “preceding” is crucial: a rolling feature must exclude records after the prediction time. Aggregations should respect grain; customer-level totals joined to transaction-level rows may be valid for some tasks but can cause target duplication for others.

Interactions such as X₁X₂ may represent complementary effects, while polynomial terms can model curvature. They increase dimensionality and risk overfitting, so regularisation, validation, and interpretability checks are required.

9. Feature Selection

Feature selection removes irrelevant, redundant, unstable, or prohibited variables while retaining predictive and substantive value. It can improve generalisation, computational efficiency, interpretability, and privacy.

Filter methods rank variables independently of a final model, using correlation, mutual information, variance thresholds, or statistical tests. Correlation detects linear association, not causality, and a low marginal association does not imply irrelevance in interactions. Wrapper methods evaluate subsets using a model and validation score, but are computationally expensive. Embedded methods perform selection during fitting; examples include L1-regularised regression and tree-based importance.

For binary logistic regression, the modelled probability is:

Here Y is the binary outcome, Xⱼ are predictors, βⱼ are coefficients, and β₀ is the intercept. L1 regularisation adds λ∑ⱼ|βⱼ| to the loss, encouraging some coefficients to become exactly zero. λ ≥ 0 controls the penalty and must be selected using training data only. Correlated predictors may lead L1 selection to choose one arbitrarily; elastic net can provide more stable grouped selection.

Selection must occur inside cross-validation. Selecting variables once using the full dataset before evaluating a model leaks validation information, even if the target is not used directly. Domain restrictions also matter: post-outcome variables, protected attributes used without justification, and proxies for protected attributes may be excluded or carefully governed.

10. Dimensionality Reduction

Feature selection retains original variables; dimensionality reduction constructs new variables. Principal component analysis (PCA) finds orthogonal directions of maximum variance. Given a centred and, when appropriate, scaled data matrix X, PCA decomposes its covariance or correlation structure. The first component direction v₁ solves:

where v is a unit-length direction and Xv is the projected data. Subsequent components maximise variance subject to orthogonality to earlier components. PCA is unsupervised: high variance is not necessarily predictive of the target. Components are linear combinations and may be difficult to explain.

The explained-variance ratio for component k is λ_k/∑ⱼλⱼ, where λ_k is its eigenvalue and the denominator is total retained variance. This ratio is meaningful for the covariance or correlation matrix used, but retaining 95% variance is a heuristic, not a guarantee of predictive adequacy. PCA must be fitted only on training data. Alternatives include truncated singular-value decomposition for sparse matrices, manifold methods for visualisation, and supervised representation learning; each has distinct stability and interpretability limitations.

11. Class Imbalance and Evaluation

When one class is rare, accuracy can be misleading. If 99% of cases are negative, an always-negative classifier has 99% accuracy but zero recall for the positive class. The confusion matrix contains true positives (TP), false positives (FP), true negatives (TN), and false negatives (FN). Important measures include:

These ratios require non-zero denominators. Precision is the positive predictive value; recall is sensitivity; F₁ is the harmonic mean of precision and recall. Receiver operating characteristic and precision–recall curves examine thresholds, while expected monetary cost may be more relevant than any generic metric.

Responses to imbalance include collecting more minority examples, stratified or group-aware sampling, class-weighted loss, threshold adjustment, and carefully controlled over- or under-sampling. Synthetic methods such as SMOTE generate interpolated minority examples, but synthetic records must be created inside each training fold. Sampling the full dataset before splitting allows near-duplicate or synthetic information to cross the boundary. Oversampling does not create new independent evidence and may amplify noise. Calibration, subgroup performance, and operational capacity must be evaluated alongside aggregate scores.

12. Leakage, Bias, and Governance

Data leakage occurs when information unavailable at prediction time influences a feature, split, selection decision, or evaluation. Common examples include using a post-treatment diagnosis, imputing with full-dataset statistics, selecting features using all labels, random splitting of future events, and target encoding before cross-validation.

A leakage audit asks: What is the prediction time? When was each source field created? Could the field have changed after the decision? Was it generated by a downstream process? Are duplicate entities or related episodes present across splits?

Bias can enter through sampling, measurement, labels, missingness, proxy variables, and deployment feedback. Removing protected attributes does not guarantee fairness because correlated proxies remain. Fairness assessment should examine relevant group metrics, such as recall, false-positive rate, calibration, and error costs, while recognising that some fairness criteria cannot generally be satisfied simultaneously when base rates differ. Governance requires a documented purpose, lawful and proportionate data use, access controls, retention limits, security, explainability appropriate to stakeholders, and mechanisms for contesting harmful decisions. Privacy-enhancing approaches include minimisation, aggregation, pseudonymisation, differential privacy, and federated learning, but each introduces utility or governance trade-offs.

13. A Reproducible Implementation Pattern

A production pipeline should encapsulate preprocessing and modelling so that training and inference execute identical, versioned transformations. A conceptual sequence is:

1. define target, prediction time, population, and grain;
2. split by time or group before learning transformation parameters;
3. fit imputers, encoders, scalers, selectors, and reducers on training data;
4. transform validation and test data with those fitted objects;
5. tune models using nested or appropriately separated validation;
6. evaluate discrimination, calibration, subgroup performance, stability, and cost;
7. version the pipeline, data snapshot, feature definitions, and model;
8. monitor missingness, category novelty, distributions, drift, performance, and fairness after deployment.

A feature store can centralise reusable definitions and lineage, but it does not automatically prevent leakage. Offline feature computation must reproduce online availability and timestamp semantics. Automated data-quality tests should detect schema changes, impossible values, unexpected null rates, duplicate keys, and distribution shifts. Human review remains necessary for high-impact transformations.

Illustrative Case: Predicting Missed Loan Payments

Background

A retail lender wants to predict whether an active customer will miss a payment during the next 30 days. The warehouse contains customer, account, repayment, contact, and collections tables. The modelling population is defined as accounts active at each weekly scoring date.

Problem

An initial model reports high accuracy but performs poorly after deployment. Investigation finds that the dataset includes collection status recorded after missed payments, customers with multiple weekly rows are randomly split, and missing income is replaced by zero. A rare-event rate of 4% further obscures poor positive-class detection.

Analysis

The correct grain is account-score-date. The target is defined using payment events after the score date and within the next 30 days. Features must be frozen at the score date. A temporal split assigns earlier weeks to training, a later period to validation, and the most recent period to test. Accounts belonging to the same customer are grouped to prevent inappropriate cross-period leakage where required by the deployment design.

Approach and Solution

The team creates a point-in-time feature table. Income is imputed with the training median and accompanied by a missingness indicator; the zero value is reserved for genuine zero income. Transaction amounts are log-transformed after checking non-negativity, and robust scaling is used for a regularised logistic model. Nominal occupation is one-hot encoded, rare categories are grouped using training data, and target encoding is avoided initially because of its leakage risk.

Features include repayment ratio, number of late payments in the preceding 90 days, account age, and contact attempts preceding the score date. Collection outcomes after the score date are excluded. Class-weighted training is compared with modest within-fold oversampling. Evaluation reports precision–recall curves, recall at a fixed contact capacity, calibration, expected intervention cost, and performance by age band and region. The final threshold is selected according to the lender’s cost matrix, not default probability 0.5.

Outcome

The revised evaluation produces lower headline accuracy but substantially better recall at the available intervention capacity, stable calibration, and a smaller gap in false-negative rates across monitored groups. A model card records intended use, exclusions, features, limitations, monitoring thresholds, and escalation procedures. The lender retains human review and an appeal process rather than treating the score as an automatic adverse decision.

Learning Points

The case demonstrates that grain, temporal availability, leakage control, missingness semantics, class-aware metrics, and governance are inseparable. A more complex algorithm would not repair a contaminated target or an invalid split.

Challenges and Limitations

Preparation choices depend on the data-generating process and deployment context. Imputation can conceal structural exclusion; outlier removal can erase rare events; dimensionality reduction can reduce explainability; balancing can distort prevalence; and automated feature generation can encode historical discrimination. Distribution shift may invalidate training-derived thresholds. In streaming environments, delayed labels complicate monitoring and retraining. Reproducibility is also challenged by changing source systems, nondeterministic algorithms, and undocumented analyst decisions. These limitations should be treated as model-risk issues, not merely technical inconveniences.

Best-Practice Checklist

• Define grain, target, prediction time, population, and permissible information.
• Profile before transforming; distinguish errors, extremes, and legitimate missingness.
• Split by time, group, or entity when random splitting is unrealistic.
• Fit every learned transformation inside the training partition or fold.
• Preserve units, timestamps, lineage, and transformation parameters.
• Prefer domain-justified features and document their availability.
• Evaluate imbalance with class-sensitive, calibrated, cost-aware, and subgroup metrics.
• Test for leakage, proxy discrimination, drift, and category novelty.
• Version data, code, pipeline, features, and model documentation.
• Monitor the deployed feature distribution and outcomes continuously.

Chapter Summary

Data preparation converts raw observations into a trustworthy analytical representation. Sound practice begins with data contracts, profiling, correct grain, representative sampling, and leakage-resistant splitting. Integration requires entity resolution, temporal alignment, unit consistency, and historical awareness. Missing values must be interpreted through their mechanism; outliers require diagnosis; categorical variables require semantically appropriate encoding; and scaling, transformation, and discretisation must be selected for the algorithm and domain.

Feature selection and dimensionality reduction address redundancy and high dimensionality but must be performed within training data. Class imbalance requires suitable sampling, weighting, thresholds, calibration, and precision–recall or cost-based evaluation. Across all stages, bias, privacy, fairness, lineage, and monitoring are central. The most reliable workflow is a reproducible, point-in-time pipeline whose transformations can be audited and whose performance is evaluated under deployment-like conditions.

Key Terms

Data grain: The real-world unit represented by one row.

Data contract: A documented agreement about schema, meaning, quality, and availability.

Sampling bias: Systematic distortion caused by unequal or inappropriate selection of observations.

Stratified sampling: Sampling separately within defined subgroups.

Data leakage: Use of information unavailable at prediction time in training or evaluation.

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

Missing completely at random: Missingness unrelated to observed and unobserved variables.

Missing at random: Missingness explainable by observed variables, conditional on them.

Imputation: Replacing or modelling missing values.

Outlier: An observation unusually distant from a reference distribution.

Winsorisation: Capping extreme values at selected limits.

One-hot encoding: Representing categories with binary indicator variables.

Target encoding: Replacing categories with target-related estimates.

Standardisation: Rescaling using a mean and standard deviation.

Discretisation: Converting a continuous variable into intervals.

Feature selection: Retaining a subset of available variables.

Dimensionality reduction: Constructing fewer variables from a larger feature space.

Class imbalance: Unequal prevalence of target classes.

Calibration: Agreement between predicted probabilities and observed frequencies.

Feature drift: Change in feature distributions over time or between environments.

Review Questions

Short Answer Questions

1. Why must preprocessing parameters be learned only from training data?
2. Distinguish MCAR, MAR, and MNAR missingness.
3. When is ordinal encoding inappropriate?
4. What does the IQR outlier rule identify, and why is it not proof of error?
5. Why can accuracy be misleading for an imbalanced classification problem?

Long Answer Questions

1. Design a leakage-resistant preparation pipeline for a time-dependent prediction problem.
2. Compare filter, wrapper, and embedded feature-selection methods, including their advantages and risks.
3. Explain how entity resolution, surrogate keys, and slowly changing dimensions affect analytical validity.
4. Discuss the benefits and limitations of PCA compared with selecting original features.
5. Explain how missing-data treatment can affect fairness, interpretability, and model performance.

Analytical / Application Questions

1. A fraud dataset contains 0.5% confirmed fraud, repeated transactions from the same customer, and labels delayed by 60 days. Propose a split, sampling, feature-availability policy, and evaluation plan.
2. A model uses a “number of collection calls” feature and achieves excellent validation performance. Develop an audit to determine whether the feature leaks post-outcome information.
3. A regional lending model has equal overall recall but much lower recall for one region. Analyse possible data-preparation causes and recommend technically and ethically defensible remedies.

Multiple Choice Questions with Answers

Question 1

Which action best prevents imputation leakage during cross-validation?

A. Impute the entire dataset before creating folds

B. Fit the imputer separately on each training fold

C. Use the test-set median for all folds

D. Replace every missing value with zero

Correct Answer: B. Fit the imputer separately on each training fold

Question 2

For a variable with meaningful categories such as low, medium, and high, which encoding is generally most appropriate?

A. Random integer encoding

B. Ordinal encoding preserving the order

C. One unique hash per row

D. Replacing every category with its frequency in the test set

Correct Answer: B. Ordinal encoding preserving the order

Question 3

What does the standard IQR rule primarily provide?

A. A proof that flagged records are erroneous

B. A method for identifying unusually distant observations

C. A guarantee of normally distributed data

D. A replacement for domain validation

Correct Answer: B. A method for identifying unusually distant observations

Question 4

Which split is most appropriate when future observations are predicted from historical observations?

A. A random split ignoring dates

B. A temporal split

C. A split based only on row order after shuffling

D. A split using target values from the future

Correct Answer: B. A temporal split

Question 5

In binary classification, recall is calculated as:

A. TP/(TP + FP)

B. TN/(TN + FN)

C. TP/(TP + FN)

D. (TP + TN)/(TP + TN + FP + FN)

Correct Answer: C. TP/(TP + FN)

Question 6

Why can naïve target encoding leak information?

A. It always removes rare categories

B. It uses target outcomes from validation records to encode those records

C. It cannot represent numerical variables

D. It requires standardisation before fitting

Correct Answer: B. It uses target outcomes from validation records to encode those records

Question 7

Which method constructs new orthogonal variables ordered by variance?

A. One-hot encoding

B. Principal component analysis

C. Median imputation

D. Stratified sampling

Correct Answer: B. Principal component analysis

Question 8

Why is an accuracy score of 99% potentially poor when the positive class prevalence is 1%?

A. Accuracy cannot be computed for binary outcomes

B. An all-negative classifier can achieve approximately that score while missing positives

C. Accuracy is always lower than recall

D. The test set must contain equal classes

Correct Answer: B. An all-negative classifier can achieve approximately that score while missing positives

Question 9

What is the main purpose of a point-in-time feature table?

A. To include every field eventually recorded for an entity

B. To ensure features reflect only information available at the scoring time

C. To remove all historical records

D. To force all variables to have equal variance

Correct Answer: B. To ensure features reflect only information available at the scoring time

Question 10

Which statement about class oversampling is correct?

A. It creates independent new evidence

B. It should be performed before the train-test split

C. It should be confined to training data, typically within each fold

D. It makes calibration automatically perfect

Correct Answer: C. It should be confined to training data, typically within each fold