Data Preparation and Feature Engineering

Introduction

Statistical and predictive models learn from the data supplied to them. Consequently, model quality is constrained not only by the choice of algorithm but also by the quality, structure, and information content of the input data. In business analytics, raw data commonly originate from transactional systems, customer relationship platforms, sensors, spreadsheets, surveys, web logs, and external data providers. These sources differ in format, granularity, definitions, quality, and update frequency.

Data preparation converts raw observations into an analytical dataset that is accurate, consistent, appropriately structured, and suitable for modelling. Feature engineering then creates informative predictor variables from the prepared data. Together, these activities often require more professional effort than model fitting itself.

A central principle is that preparation must be designed around the modelling task. A transformation that is appropriate for a linear regression model may be unnecessary or harmful for a tree-based model. Similarly, an imputation method must reflect the meaning and mechanism of missingness, not merely produce a complete table.

Learning Objectives

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

• identify common data-quality problems and their business consequences;
• distinguish data cleaning, transformation, integration, feature engineering, and feature selection;
• manage missing values and outliers using defensible procedures;
• encode categorical, temporal, textual, and other variables;
• scale variables and explain when scaling is necessary;
• construct reproducible preprocessing pipelines;
• detect and prevent data leakage;
• apply feature selection and dimensionality-reduction methods;
• evaluate preprocessing decisions using both statistical and business criteria.

1. The Analytical Dataset and the Preparation Workflow

An analytical dataset is usually represented by a matrix X, with rows corresponding to observational units and columns corresponding to variables. An observational unit might be a customer, invoice, account, product, store, employee, or day. The target variable, often written as y, is the outcome to be predicted or explained.

The first design question is the unit of analysis. If the target is customer churn, a row may represent a customer during a defined observation period. If the target is next-month sales, a row may represent a product-store-month combination. Mixing units—for example, combining customer-level and transaction-level records without aggregation—can produce duplicated information and misleading model results.

A defensible workflow is:

1. define the business question, target, prediction time, and unit of analysis;
2. acquire and profile source data;
3. validate identifiers, types, ranges, and relationships;
4. integrate sources at the correct grain;
5. split data into training, validation, and test sets;
6. fit preprocessing operations using training data only;
7. transform validation and test data using the fitted operations;
8. engineer and select features;
9. document, version, test, and monitor the pipeline.

The order matters. The data split must normally precede operations that learn from the distribution of the data, such as imputation, scaling, encoding, feature selection, or principal component analysis. Otherwise, information from the validation or test set can influence training.

Data profiling

Profiling examines structure and quality before modelling. Typical checks include:

• row and column counts;
• data types and units;
• unique-value counts;
• missing-value frequencies;
• minimum, maximum, mean, median, and quantiles;
• duplicate records and duplicate identifiers;
• invalid categories and inconsistent spelling;
• impossible dates or values;
• class balance in the target;
• relationships between tables and referential integrity.

A data dictionary should define each variable, source, business meaning, unit, permitted values, refresh frequency, and whether it is available at prediction time.

2. Data Quality and Integration

Common quality problems

Data may be incomplete, inaccurate, inconsistent, untimely, duplicated, or incorrectly represented. For example, a revenue field may contain both dollars and thousands of dollars; a date may use several formats; or a customer may appear under several identifiers. Such problems can create errors that are not visible in model-performance metrics.

Validation rules should reflect domain knowledge. Examples include:

• quantity must be a non-negative integer;
• transaction date cannot precede customer registration date;
• a percentage should normally lie between 0 and 100;
• a currency field must have a defined currency and unit;
• an account identifier should be unique in an account table;
• a product code in a transaction should exist in the product table.

Integration and joins

Integration combines data from multiple sources. Before joining tables, identify the key, cardinality, and grain. A one-to-many join can multiply rows. If a customer table is joined to a transaction table and the result is later treated as customer-level data, customers with more transactions may receive disproportionate weight.

A reliable integration process:

• standardizes identifiers and formats;
• checks whether keys are unique where expected;
• measures row counts before and after each join;
• investigates unmatched records;
• prevents accidental many-to-many joins;
• records source timestamps and extraction logic.

For time-dependent prediction, integration must also respect temporal availability. A customer’s current status, recorded after the prediction date, must not be joined into a historical training observation merely because it is present in today’s database.

3. Missing Data

Missingness may mean that a value was not collected, was not applicable, was unavailable at the time, or was deliberately withheld. These meanings are analytically different.

Three commonly used mechanisms are:

• MCAR (Missing Completely At Random): missingness is unrelated to observed and unobserved values.
• MAR (Missing At Random): missingness depends on observed variables, after those variables are considered.
• MNAR (Missing Not At Random): missingness depends on the unobserved value itself or another unobserved process.

These are modelling assumptions, not properties that can always be proven from the observed dataset.

Strategies

Deletion. Listwise deletion removes rows with missing values. It is simple but can reduce sample size and introduce bias, especially when missingness is systematic. Pairwise deletion may use different observations for different calculations, making results difficult to compare.

Simple imputation. Numeric variables may be imputed with a mean or median; categorical variables may use the mode or an explicit “Unknown” category. The median is more robust than the mean when the variable is skewed. However, imputation reduces natural variability and may distort relationships.

For a numeric variable x, training-set median imputation replaces a missing value with:

Here, xᵢ* is the replacement value. The median must be calculated from the training set only. A missingness indicator can be added:

The indicator is useful when the fact that a value is missing carries information. It should not be added automatically when missingness is purely accidental or when it creates an undesirable proxy.

Model-based and multiple imputation. Regression, nearest-neighbour, and iterative imputation estimate missing values using other variables. Multiple imputation creates several plausible datasets and combines estimates, reflecting uncertainty more honestly than a single replacement. Such methods require careful validation and can be computationally demanding.

Structural missingness. “No purchase,” “not applicable,” and “not measured” should not automatically be treated as ordinary missing values. A zero, a separate category, or a missing indicator may be more appropriate depending on the business meaning.

4. Outliers and Anomalies

An outlier is an observation that is unusually distant from the rest of the data. An anomaly is a record that violates a process, domain, or temporal expectation. An extreme value is not necessarily an error: a very large corporate order may be legitimate and commercially important.

Detection methods

For a numeric variable, the interquartile range is:

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

The z-score is:

where xᵢ is an observation, μ is the mean, and σ is the standard deviation. A large absolute z-score may indicate an unusual value, but the method is sensitive to skewness and extreme observations. Robust alternatives use the median and median absolute deviation.

Treatment options

• verify the source and correct demonstrable data-entry errors;
• retain valid extremes when they represent the population of interest;
• cap or winsorize values when justified and documented;
• transform a highly skewed variable, for example with log(1 + x) when x ≥ 0;
• use robust estimators or models;
• create an anomaly flag;
• remove observations only under a documented rule.

Outlier treatment must be learned from training data where the operation uses sample statistics. It should also be monitored after deployment because changes in outlier frequency may signal fraud, system failure, or genuine business change.

5. Transforming Variables

Transformation improves representation, comparability, or model fit.

Numeric transformations

Skewed positive variables such as income, claim amounts, and transaction values may benefit from a logarithmic transformation:

The addition of 1 permits x = 0. The transformation is appropriate only when x ≥ 0 and when its interpretation is acceptable. Other options include square-root, reciprocal, power, or quantile transformations.

Polynomial and interaction features can represent non-linear relationships. For variables x₁ and x₂, an interaction may be written as:

An interaction means that the association of one variable with the target depends on the level of another. Interactions should be guided by domain reasoning or validated empirically; indiscriminate generation causes dimensionality and overfitting problems.

Dates and time

Dates should rarely be used as unprocessed strings. Useful features include year, month, day of week, elapsed days, tenure, holiday status, and time since last event. Cyclical variables require special treatment because December and January are close in time. For a periodic variable with period P:

Here t is the time position and P is the period, such as 12 for month-of-year or 7 for day-of-week. The paired sine and cosine variables preserve cyclical proximity.

Text and location

Text can be represented using token counts, term frequency–inverse document frequency, embeddings, or domain dictionaries. A term’s TF–IDF value is commonly represented as:

where tf(t,d) is the frequency of term t in document d, N is the number of documents, and df(t) is the number of documents containing t. Exact conventions vary, including logarithm smoothing and normalization.

Location may be represented by region, distance, geohash, travel time, or spatial clusters. Location features can create privacy risks and may act as proxies for protected characteristics.

6. Encoding Categorical Variables

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

One-hot encoding creates one indicator for each category. For a category c:

For a linear model with an intercept, one category is commonly omitted as a reference category to avoid perfect multicollinearity. Some software handles this automatically; tree-based models may accept integer or native categorical representations, but arbitrary integer labels can imply a false ordering.

Ordinal encoding is appropriate only when categories have a meaningful order, such as low, medium, and high. Numeric codes do not guarantee that distances between categories are meaningful.

Frequency encoding replaces a category with its training-set frequency. It is compact but may lose semantic information.

Target encoding replaces a category with an estimate of its target mean. It can be powerful for high-cardinality variables but has substantial leakage risk. It must be computed inside each training fold, often with smoothing and out-of-fold construction.

Rare categories may be grouped into “Other,” but the threshold should be chosen using training data and domain knowledge. Unknown categories at deployment must have a defined handling rule.

7. Scaling and Normalization

Scaling is essential for methods based on distances, dot products, or gradient optimization, including k-nearest neighbours, k-means, support vector machines, neural networks, and regularized linear models. It is usually less important for decision trees and many tree ensembles because splitting depends on order rather than magnitude.

Standardization produces a variable with training-set mean 0 and standard deviation 1:

where μ_train and σ_train are calculated only from the training data, with σ_train > 0. The transformed value is dimensionless.

Min–max scaling maps a variable to [0, 1]:

This is sensitive to future values outside the training range and requires a non-zero denominator.

Robust scaling uses the median and IQR:

It is often preferable when extreme values are present. Scaling does not remove outliers; it changes their numerical scale.

8. Feature Engineering

Feature engineering creates variables that express business mechanisms more directly than raw fields. Examples include:

• recency, frequency, and monetary-value measures for customer behaviour;
• utilization = used capacity ÷ available capacity, where the denominator is positive;
• conversion rate = conversions ÷ eligible opportunities, with an explicit zero-denominator rule;
• rolling averages and counts over a defined historical window;
• customer tenure measured at the prediction date;
• price change relative to a documented baseline;
• ratios, margins, and growth rates;
• interactions such as promotion × season;
• lagged demand and inventory measures in forecasting.

Every engineered variable needs a precise definition, time reference, unit, denominator rule, and missing-value policy. Ratios can be unstable when denominators are small, and aggregate features can hide important variation. Feature definitions should be tested against known examples and business rules.

Feature engineering should also consider model interpretability. A feature such as “average support contacts in the previous 30 days” is easier to explain and govern than an undocumented composite score.

9. Feature Selection and Dimensionality Reduction

Feature selection retains a subset of original variables. Dimensionality reduction creates a smaller representation, often combining many variables.

Selection methods

• Filter methods: correlation screening, variance thresholds, mutual information, and statistical tests. They are fast but may ignore interactions and model-specific behaviour.
• Wrapper methods: recursive feature elimination or sequential selection. They evaluate subsets using a model and validation procedure, but can be computationally expensive.
• Embedded methods: regularization and tree-based importance. They select features during model fitting, although importance can be biased when predictors are correlated.

Correlation screening must be interpreted carefully. A low marginal correlation does not imply that a variable is useless, especially in non-linear or interaction-based relationships. Conversely, a high correlation may reflect leakage or duplication.

Principal component analysis

Principal component analysis (PCA) transforms correlated numeric variables into orthogonal components. After suitable centring, and often scaling, the first component is the linear combination with maximum variance; later components maximize remaining variance subject to orthogonality.

If X is a centred and scaled matrix, PCA uses the covariance or correlation structure. The explained-variance ratio for component k is:

where λₖ is the eigenvalue associated with component k and p is the number of variables. The ratios sum to 1 when all p components are retained. PCA is unsupervised: high variance does not necessarily mean high predictive value. Components may also be difficult to interpret, and categorical variables require suitable adaptations rather than naive numeric coding.

The number of components can be chosen using cumulative explained variance, validation performance, computational constraints, and interpretability. PCA must be fitted on the training set only.

10. Leakage and Reproducible Pipelines

Data leakage occurs when information unavailable at prediction time influences model development. It produces overly optimistic evaluation and poor deployment performance.

Common forms include:

• using the outcome or a post-outcome field as a predictor;
• calculating an aggregate using future transactions;
• imputing or scaling before the data split;
• selecting features using the full dataset;
• target encoding categories before cross-validation;
• randomly splitting time-series data when future observations influence the past;
• allowing duplicate entities across training and test sets when entity-level separation is required.

A pipeline encapsulates preprocessing and modelling so that each operation is fitted within the appropriate training partition. In k-fold cross-validation, each fold’s imputer, encoder, scaler, feature selector, and model must be fitted on the fold’s training portion and applied to its validation portion. A conceptual pipeline is:

Raw sources → validation → time-aware split → fit preprocessing on training data
          → transform training data → engineer/select features → fit model
          → transform validation/test with stored parameters → evaluate → deploy

Reproducibility requires versioned source data or snapshots, documented code, fixed random seeds where relevant, environment specifications, feature definitions, transformation parameters, and audit logs. Automated tests should check schema, row counts, permitted ranges, missingness, leakage-sensitive timestamps, and expected feature names.

11. Applied Case: Predicting Customer Churn

The following is an illustrative case based on a fictitious subscription business.

Background

A subscription provider wants to predict whether a customer will cancel in the next 30 days. The available sources are customer profiles, invoices, service contacts, product usage logs, and cancellation records.

Problem

Initial analysis produces excellent validation accuracy, but performance falls sharply after deployment. Investigation shows that the dataset includes “cancellation reason” and a customer-status field updated after cancellation. In addition, the preprocessing script calculated average usage using the entire customer history, including days after the prediction date.

Analysis and approach

The analytical unit is one customer at each monthly prediction date. The target is cancellation during the following 30 days. A temporal cutoff is applied: only information recorded on or before the prediction date is eligible.

The team performs the following steps:

1. standardizes customer identifiers and checks one-to-many joins;
2. removes post-cutoff fields and documents their exclusion;
3. creates recency, number of support contacts in the previous 30 days, usage trend, payment failure count, and tenure;
4. imputes numeric variables with training-set medians and adds selected missingness indicators;
5. one-hot encodes plan type and region, grouping rare plans;
6. scales variables for a regularized logistic regression benchmark;
7. uses a time-based validation split rather than a random split;
8. compares logistic regression with a tree-based model;
9. calibrates predicted probabilities and selects an intervention threshold based on retention capacity and cost.

Outcome

The revised evaluation is less optimistic but more realistic. The business can now rank customers by predicted risk before cancellation occurs. Retention staff receive only the highest-priority cases, and performance is monitored by month, customer segment, missingness pattern, and data freshness.

Learning points

The most important improvement did not come from a more complex algorithm. It came from defining the prediction time correctly, preventing leakage, engineering behaviourally meaningful features, and aligning the evaluation design with deployment.

12. Professional, Ethical, and Governance Considerations

Data preparation can embed organizational values and produce discriminatory outcomes. Removing records with missing income may disproportionately exclude low-income applicants. Location, language, device type, and purchasing behaviour may act as proxies for protected attributes. Feature selection based solely on predictive power can therefore create unacceptable decisions.

Governance practices should include:

• lawful and purpose-limited data collection;
• data minimization and retention controls;
• documentation of sources, transformations, and legitimate uses;
• access control and encryption for sensitive data;
• fairness assessment across relevant groups;
• human review for high-impact decisions;
• explanation of important features and adverse outcomes;
• monitoring for drift, changing missingness, and performance degradation.

Artificial intelligence systems increasingly use automated feature generation, embeddings, and foundation-model representations. These tools can improve predictive performance but introduce risks involving opacity, privacy, copyright, bias, and reproducibility. Automated features should be treated as model inputs requiring validation, versioning, and governance—not as inherently objective facts.

13. Best-Practice Checklist

Before releasing a predictive pipeline, confirm that:

• the unit of analysis and target window are explicit;
• every feature was available at prediction time;
• training, validation, and test partitions reflect deployment;
• preprocessing parameters were fitted only on training data;
• missing-value and unknown-category rules are defined;
• outlier decisions are documented and monitored;
• feature units, denominators, and time windows are recorded;
• high-cardinality and target encoding are leakage-safe;
• selection and PCA occur inside the resampling process;
• data-quality tests and drift monitoring are automated;
• privacy, fairness, security, and explainability risks are assessed.

Chapter Summary

Data preparation turns heterogeneous raw data into a reliable analytical representation. It begins with the unit of analysis, business definition, data profiling, and validation. Integration must respect keys, cardinality, grain, and time. Missing values require attention to their mechanism and business meaning; outliers require investigation rather than automatic deletion.

Transformation includes numeric functions, date decomposition, cyclical representations, text processing, encoding, and scaling. Feature engineering expresses business processes through recency, frequency, ratios, trends, lags, and interactions. Feature selection can use filter, wrapper, or embedded methods, while PCA reduces dimensionality at the cost of interpretability. All learned transformations must be fitted within training data and, during cross-validation, within each training fold.

Leakage prevention is fundamental. Reproducible pipelines, temporal reasoning, version control, validation tests, documentation, and monitoring protect the credibility of predictive analytics. Finally, preparation decisions have ethical and governance consequences because variables and missingness patterns can affect groups differently.

Key Terms

Analytical dataset: A structured dataset prepared for analysis or modelling.

Data dictionary: Documentation describing variables, meanings, units, sources, and valid values.

Data leakage: Use of information during development that would not be available at prediction time.

Feature: An input variable used by an analytical model.

Feature engineering: Creation or transformation of variables to represent useful patterns.

Feature selection: Choosing a subset of available variables for modelling.

Imputation: Replacing or estimating missing values.

MCAR: Missing completely at random.

MAR: Missing at random conditional on observed information.

MNAR: Missing not at random.

Outlier: An observation unusually distant from the rest of the data.

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

Ordinal encoding: Numeric representation of categories with meaningful order.

Pipeline: An ordered, reproducible sequence of data transformations and modelling steps.

Principal component analysis: A method that creates orthogonal components capturing variance in numeric data.

Scaling: Rescaling variables to comparable numerical ranges.

Target encoding: Replacing categories with target-related statistics, using leakage-safe procedures.

Unit of analysis: The entity represented by one row of an analytical dataset.

Winsorization: Capping extreme observations at specified limits.

Review Questions

Short Answer Questions

1. Why must the unit of analysis be defined before joining data sources?
2. Distinguish MCAR, MAR, and MNAR missingness.
3. When is scaling particularly important for a predictive model?
4. What is the purpose of a missingness indicator?
5. Define data leakage and give one example.

Long Answer Questions

1. Compare deletion, simple imputation, model-based imputation, and multiple imputation. Discuss their assumptions and limitations.
2. Explain how categorical, temporal, textual, and numeric variables can be transformed for modelling.
3. Describe filter, wrapper, and embedded feature-selection methods and compare their advantages.
4. Explain PCA, including the meaning of explained-variance ratio and its main limitations.
5. Design a reproducible preprocessing pipeline for a customer-churn problem.

Analytical / Application Questions

1. A retailer joins customer-level data to transaction-level data and obtains three times as many rows as expected. Diagnose possible causes and propose validation checks.
2. A fraud model uses a payment-reversal field recorded seven days after a transaction. Explain why this is leakage and propose an admissible alternative feature design.
3. A numeric feature has median 40, Q₁ = 20, and Q₃ = 60. Calculate the IQR and the upper exploratory outlier fence, then explain why an observation above that fence should not automatically be deleted.

Multiple Choice Questions with Answers

Question 1

Which operation should normally be fitted using training data only?

A. Calculating a median imputation value

B. Reading a fixed business definition

C. Checking whether a value is a valid date format

D. Renaming a column according to a schema

Correct Answer: A. Calculating a median imputation value

Question 2

Which missingness mechanism depends on observed variables after those variables are considered?

A. MCAR

B. MAR

C. MNAR

D. Structural missingness

Correct Answer: B. MAR

Question 3

For Q₁ = 20 and Q₃ = 60, what is the upper 1.5 × IQR exploratory fence?

A. 80

B. 100

C. 120

D. 140

Correct Answer: B. 100

Question 4

Which model family is generally most sensitive to differences in feature scale?

A. Decision trees

B. Random forests

C. k-nearest neighbours

D. Rule-based classification

Correct Answer: C. k-nearest neighbours

Question 5

Why are sine and cosine features useful for month of year?

A. They remove all missing values

B. They represent the cyclical relationship between December and January

C. They guarantee a normal distribution

D. They eliminate the need for validation

Correct Answer: B. They represent the cyclical relationship between December and January

Question 6

What is a major risk of target encoding?

A. It always removes predictive information

B. It can leak target information into predictors

C. It cannot represent categorical variables

D. It requires all variables to be numeric originally

Correct Answer: B. It can leak target information into predictors

Question 7

Which statement about PCA is correct?

A. It is supervised and always maximizes predictive accuracy

B. It creates orthogonal components based on variance structure

C. It can be fitted safely on the full dataset before cross-validation

D. It preserves the original interpretation of every feature

Correct Answer: B. It creates orthogonal components based on variance structure

Question 8

A many-to-many join is especially concerning because it can:

A. Automatically standardize all variables

B. Multiply records and distort observations’ weights

C. Guarantee referential integrity

D. Prevent duplicate identifiers

Correct Answer: B. Multiply records and distort observations’ weights

Question 9

Which is an example of temporal leakage?

A. Using customer tenure measured at the prediction date

B. Using the previous 30 days of support contacts

C. Using a cancellation reason recorded after cancellation

D. Using plan type known at prediction time

Correct Answer: C. Using a cancellation reason recorded after cancellation

Question 10

For a linear model with an intercept, why is one one-hot category often omitted?

A. To prevent perfect multicollinearity

B. To increase the number of categories

C. To make all variables ordinal

D. To remove the target variable

Correct Answer: A. To prevent perfect multicollinearity