7. Data Preparation and Feature Engineering

Introduction and Context

Data science rarely begins with a clean table ready for modelling. Real-world data is collected from transactions, sensors, surveys, databases, application logs, documents, and external providers. These sources differ in structure, quality, measurement scales, naming conventions, update frequency, and reliability. Values may be missing, duplicated, incorrectly recorded, expressed in incompatible units, or distorted by unusual observations. Even when the data is technically valid, it may not represent the variables required for analysis.

Data preparation is the disciplined process of converting raw data into a reliable, consistent, and analysis-ready form. Feature engineering extends this process by creating informative variables from existing data. Together, they connect data acquisition with statistical analysis and machine learning. A sophisticated algorithm cannot compensate for systematically biased, poorly defined, or incorrectly transformed inputs. In many applied projects, the quality of preparation influences results more strongly than the choice between two advanced algorithms.

Preparation is not merely a technical cleaning exercise. It involves decisions about meaning, measurement, business rules, risk, fairness, and the intended use of the model. A missing value may indicate non-response, system failure, or a meaningful absence. An extreme value may be an error or a genuine event. A categorical variable may require one-hot encoding, ordinal encoding, grouping, or a domain-specific representation. These decisions must therefore be documented, tested, and evaluated in relation to the analytical objective.

Learning Objectives

After studying this chapter, students should be able to:

1. Explain the purpose and scope of data preparation and feature engineering.

2. Diagnose and address missing values, duplicates, inconsistent formats, and outliers.

3. Integrate data from multiple sources while preserving keys, meaning, and quality.

4. Select suitable transformations for numerical, categorical, temporal, and textual variables.

5. Construct useful features while avoiding data leakage.

6. Design a reproducible preparation pipeline for analysis and machine learning.

7. Evaluate the ethical, operational, and governance implications of preparation decisions.

From Raw Records to Analysis-Ready Data

An analysis-ready dataset has a clearly defined unit of observation, consistent variable definitions, appropriate data types, documented provenance, and a quality level suitable for its purpose. “Clean” does not mean that every value is complete or that all variation has been removed. It means that imperfections have been understood and managed in a defensible manner.

A useful preparation process begins with a data audit. The analyst identifies the source, owner, collection method, time period, schema, primary key, expected ranges, and relationship between tables. Descriptive summaries then reveal data types, cardinality, missingness, distributions, and unusual patterns. Data dictionaries are particularly important in organizational settings because the same field name can have different meanings across systems. For example, “customer” may refer to an account holder in one database and an individual user in another.

The unit of analysis must be established before transformations are performed. A dataset may contain one row per customer, order, product, or customer-month. Joining an order table to a customer table is different from joining two transaction tables; the latter may multiply rows and create unintended duplication. A preparation workflow should therefore ask: What does one row represent? Which columns identify it? What relationships are one-to-one, one-to-many, or many-to-many?

A typical workflow includes profiling, validation, integration, cleaning, transformation, feature construction, splitting, and documentation. These stages are iterative rather than strictly linear. Profiling may reveal an integration problem, while modelling may reveal that a feature has no variation or contains leakage.

Diagnosing Imperfect Data

Missing values

Missingness can arise because a question was not answered, a sensor failed, a value was not applicable, a record was lost, or the value was deliberately withheld. The mechanism matters. Missing completely at random means that missingness is unrelated to observed and unobserved values. Missing at random means that it can be explained by observed variables. Missing not at random means that the probability of missingness depends on the unobserved value itself or on an unmeasured process.

Missingness should first be measured rather than automatically replaced. Analysts should examine the proportion of missing values by column and row, compare missingness across groups and time, and create missingness indicators where the fact of non-observation may contain information. For numerical variables, mean imputation is simple but reduces variation and can distort relationships. Median imputation is more robust to skew and outliers. Model-based, regression, or multiple imputation may be appropriate when preserving relationships is important, but these methods require assumptions and careful validation.

For categorical variables, a separate “Unknown” or “Not reported” category may be preferable to the mode, particularly when non-response has operational meaning. Rows may be removed when missingness is small, plausibly random, and deletion does not alter the target population. In contrast, deletion can create serious bias when missingness is concentrated in a demographic or business segment.

Imputation parameters must be learned from the training data only. Computing a median using the entire dataset before the train-test split allows information from the test set to influence the training process, producing a form of leakage.

Duplicates and inconsistent records

Duplicates may be exact copies, repeated events, or records that refer to the same entity with different spellings. Removing exact duplicates is often appropriate, but repeated transactions should not be removed merely because their values look similar. Entity resolution may require standardizing names, addresses, telephone numbers, or identifiers and then applying deterministic or probabilistic matching.

A duplicate policy should distinguish between technical duplicates and legitimate repeated observations. For example, two identical payment attempts may represent two real events, whereas two copies of the same customer record created during a system migration may not. Rules should be based on keys, timestamps, source priority, and business meaning.

Inconsistent formats include dates recorded as text in multiple conventions, currencies expressed without a common unit, weights using different measurement systems, and categorical labels such as “M”, “Male”, and “male”. Standardization should preserve the original field when traceability is important and create a documented canonical field for analysis.

Outliers

An outlier is an observation that is unusually distant from the general pattern of the data. It may be a data-entry error, a measurement failure, a rare but valid event, or evidence of a different population. Outliers can be identified through domain limits, box plots, quantiles, z-scores, robust statistics, or model-based methods. No method alone establishes that an observation is wrong.

Deletion is justified only when the value is demonstrably erroneous or outside the population of interest. Alternatives include correcting it from a trusted source, capping or winsorizing extreme values, applying a logarithmic transformation, using robust models, or analysing the subgroup separately. For example, a very large purchase may be precisely the event a fraud-detection system must identify; removing it would undermine the purpose of the analysis.

Data validation

Validation checks should be explicit. Examples include ensuring that ages are non-negative, dates follow a logical order, percentages lie within their defined range, identifiers are unique where required, and foreign keys match reference tables. Expectations can be implemented as automated tests that fail or raise warnings when new data violates established rules. Data quality should be monitored over time because a pipeline that worked last month may fail after a source-system change.

Integrating Multiple Sources

Integration combines data from different systems into a coherent analytical view. The central issues are schema alignment, entity matching, keys, granularity, time alignment, and conflicting values. A join should be selected according to the analytical question: an inner join retains only matched records, a left join preserves the reference population, and other joins serve different relational purposes. Analysts must inspect row counts before and after joining. Unexpected growth often signals a many-to-many relationship or duplicate key.

Data integration also requires semantic alignment. A revenue field may be gross in one source and net in another. A timestamp may reflect event time in one system and processing time in another. Units, currencies, time zones, and fiscal calendars must be reconciled. When source values disagree, the resolution rule should be recorded rather than hidden in code.

Temporal data requires special care. A feature used to predict an event must be calculated using information available before the prediction time. Joining a future customer status to an earlier transaction creates leakage even when the join is technically correct. Time-aware joins and effective-date records help preserve historical reality.

Transforming Variables for Analysis

Numerical transformations and scaling

Numerical variables often have different units and distributions. Standardization transforms a value using its mean and standard deviation, producing a variable with approximately zero mean and unit variance. It is useful for distance-based methods, regularized regression, principal component analysis, and neural networks. Min-max scaling maps values to a specified interval, commonly zero to one, but is sensitive to extreme values. Robust scaling uses the median and interquartile range and is less affected by outliers.

Scaling is not generally necessary for tree-based models, whose split rules depend on ordering rather than distance. Nevertheless, consistent transformations may simplify a shared pipeline. Skewed variables such as income, counts, and transaction amounts may benefit from logarithmic or other power transformations. Transformation choice should be guided by distribution, model assumptions, interpretability, and the possibility of zero or negative values.

Categorical variables

Categorical variables represent labels or groups. Nominal categories have no natural order, whereas ordinal categories do. One-hot encoding creates a binary column for each category, usually omitting one reference category in linear models to avoid perfect multicollinearity. It is transparent but can produce very wide datasets when cardinality is high.

Ordinal encoding assigns numbers to ordered categories, such as low, medium, and high. It should not be used for nominal categories because a numerical order would be introduced artificially. Frequency encoding replaces a category with its occurrence frequency. Target encoding uses the outcome associated with each category, but it is highly vulnerable to leakage and must be calculated within training folds, preferably with smoothing.

Rare categories may be combined into an “Other” group when they provide little information or create unstable estimates. High-cardinality identifiers such as account numbers should not automatically be treated as meaningful predictors. They may memorize individuals rather than generalize to new cases.

Dates, text, and structured fields

Dates can be decomposed into year, month, day of week, hour, quarter, elapsed time, or time since a relevant event. Cyclical variables such as hour of day or month can be represented with sine and cosine transformations so that December and January are treated as adjacent. Date-derived features must respect the prediction time.

Text can be represented through token counts, n-grams, term-frequency inverse-document-frequency values, embeddings, or domain dictionaries. The appropriate representation depends on vocabulary, sample size, computational resources, and the need for interpretability. Structured strings such as product codes or postal codes may be split into meaningful components, but arbitrary decomposition can create spurious patterns.

Feature Engineering as Representation Design

Feature engineering creates variables that express relationships more directly than raw fields. Examples include total spending over a defined period, average time between purchases, debt-to-income ratio, number of support contacts, rolling sensor statistics, or an indicator that a contractual deadline was missed. Good features are relevant to the prediction or analytical question, available at the appropriate time, sufficiently reliable, and interpretable enough for their use context.

Features may be constructed through transformations, aggregations, interactions, ratios, differences, temporal windows, or domain rules. Interaction terms can represent situations in which the effect of one variable depends on another. However, ratios can be unstable when denominators are near zero, and complex aggregates can conceal important subgroups. Every engineered feature should have a definition, data source, calculation window, unit, and missing-value policy.

Feature selection is related but distinct. Selection chooses a subset of available features, whereas engineering creates or reformulates them. Filters use statistical characteristics such as variance or association; wrappers evaluate subsets through model performance; embedded methods perform selection during model fitting. Selection should occur within the training process when it uses the target or validation performance.

Preventing Data Leakage

Data leakage occurs when information unavailable at the moment of prediction enters the training data. It can be direct, such as including a variable recorded after the outcome, or indirect, such as calculating a global average using future observations. Leakage often produces impressive validation performance and disappointing deployment results.

Common sources include imputing or scaling before splitting, target encoding categories using all observations, selecting features on the complete dataset, random splitting of time-dependent records, duplicate entities appearing in both training and test sets, and aggregating transactions across the prediction boundary. Leakage prevention requires a clear prediction point and an information availability rule.

A robust approach is to divide data into training, validation, and test sets before target-dependent preparation. Fit imputers, scalers, encoders, and feature-selection procedures on training data only. Apply the learned transformations to validation and test data without refitting. Cross-validation pipelines should perform preparation separately within each fold. For repeated entities, group-based splitting may be needed; for temporal processes, use time-based splits.

An Illustrative Case: Predicting Customer Churn

Background

A subscription company wants to identify customers likely to cancel within the next 30 days. It has customer profiles, invoices, service-usage logs, support contacts, and cancellation records.

Situation and analysis

Initial inspection finds missing demographic fields, duplicate customer profiles, inconsistent plan names, highly skewed usage measures, and several customers with unusually high support activity. The data contains one row per monthly customer snapshot, but some tables contain multiple transactions per customer. A careless analyst might join all tables directly, causing repeated rows and inflated usage totals. The cancellation date is also present in the operational system.

The team first defines the prediction point as the end of each monthly snapshot. Features must use information available up to that date; the target indicates cancellation during the following 30 days. Customer identifiers are resolved using the master customer table, and transactions are aggregated by customer and snapshot month. Duplicate snapshots are investigated rather than automatically removed.

Approach

Missing numerical values are assessed by source and customer segment. Median imputation is fitted on the training period, while missingness indicators are retained for fields where non-reporting may be informative. Plan names are standardized and encoded using training-derived categories. Usage amounts are log-transformed, and robust scaling is used for a regularized model. Features include recent usage change, number of support contacts in the previous 60 days, payment failures in the previous 90 days, and tenure in months.

The cancellation date is excluded because it is recorded after or at the outcome event. A random split is rejected because future snapshots could enter training while earlier snapshots enter testing. Instead, earlier months are used for training, a later month for validation, and the most recent period for testing. The entire preparation process is implemented as a reproducible pipeline.

Outcome and learning points

The resulting model has lower validation performance than the initial leakage-prone model, but its performance is more stable on the future test period. Managers can act on the features because each has a clear operational interpretation. The case demonstrates that trustworthy preparation may reduce apparent performance while improving real-world usefulness.

Key lessons are that granularity must be controlled before joining, feature windows must respect time, missingness requires interpretation, and a feature is not acceptable merely because it improves a historical score.

Professional Practice, Governance, and Ethical Considerations

Preparation decisions can affect individuals and organizations. Deleting records from underrepresented groups may change the population being modelled. Imputation can conceal unequal data-collection quality. Encoding postal areas or names may act as a proxy for protected characteristics. Analysts should evaluate subgroup performance, document assumptions, and involve domain experts when variables have social or legal implications.

Data minimization requires collecting and retaining only what is necessary for the stated purpose. Sensitive data should be protected through access controls, pseudonymization, encryption, retention limits, and appropriate consent or legal basis. Reproducibility requires versioning source data where permitted, code, schemas, transformation parameters, and feature definitions. Governance documentation should make it possible to explain how a raw field became a model input.

Automation and AI can assist with schema matching, anomaly detection, imputation suggestions, feature generation, and data-quality monitoring. They do not remove the need for human review. Automated transformations may encode hidden assumptions, amplify historical bias, or create features that are impossible to explain. Generated features should be tested for stability, leakage, fairness, and operational availability.

Common Mistakes and Best Practices

Frequent mistakes include treating all missing values identically, deleting every outlier, using arbitrary numerical codes for nominal categories, joining tables without checking granularity, calculating features across future periods, and preparing the entire dataset before splitting. Another error is creating many features without considering interpretability, computational cost, drift, or maintenance.

Strong practice includes:

• define the unit of analysis and prediction time;
• profile data before changing it;
• retain raw fields when traceability matters;
• validate ranges, keys, dates, and joins automatically;
• fit transformations only on training data;
• use domain knowledge to interpret missingness and outliers;
• document every engineered feature and its availability;
• assess subgroup effects and fairness;
• monitor data quality and feature drift after deployment;
• prefer the simplest representation that adequately serves the purpose.

Current Developments

Modern preparation increasingly uses declarative data-quality systems, feature stores, metadata catalogs, and automated lineage. Feature stores can provide consistent definitions for training and production, reducing training-serving skew. Streaming systems require incremental validation and time-aware feature computation. Representation learning and foundation models reduce manual engineering in areas such as text, images, and speech, but they do not eliminate preparation concerns involving labels, provenance, privacy, bias, and leakage.

The most important development is not full automation but greater traceability. Organizations increasingly expect data products to be versioned, monitored, explainable, and governed throughout their life cycle.

Chapter Summary

Data preparation converts heterogeneous, imperfect raw data into a reliable analytical representation. It requires understanding the unit of analysis, profiling quality, resolving duplicates, managing missingness, investigating outliers, standardizing formats, and integrating sources without altering meaning. Transformation methods such as scaling, encoding, date decomposition, and text representation must be selected according to variable type and model requirements. Feature engineering adds domain-relevant information through aggregates, windows, interactions, and derived measures. All target-dependent operations must be performed within the training process to prevent leakage. Professional preparation also requires documentation, validation, ethical review, privacy protection, reproducibility, and monitoring after deployment.

Key Terms

Missingness: The absence of an expected data value.

Imputation: Replacing or estimating missing values using a defined method.

Outlier: An observation unusually distant from the dominant data pattern.

Deduplication: Identification and treatment of repeated or redundant records.

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

Data validation: Testing data against structural, logical, and domain rules.

Granularity: The level represented by one observation or row.

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

Ordinal encoding: Mapping ordered categories to numerical values.

Target encoding: Replacing categories with outcome-related statistics.

Standardization: Rescaling a variable using its mean and standard deviation.

Robust scaling: Scaling based on statistics less sensitive to extreme values.

Feature engineering: Creating informative analytical variables from available data.

Feature selection: Choosing a subset of variables for analysis or modelling.

Data leakage: Use of information unavailable at prediction time.

Prediction point: The time at which available information is defined for prediction.

Concept drift: Change in the relationship between inputs and an outcome over time.

Feature store: A managed system for storing and serving consistent features.

Data lineage: A record of a field’s sources, transformations, and destinations.

Review Questions

Short Answer Questions

1. Why must the unit of analysis be established before joining datasets?

2. Distinguish between missing completely at random and missing not at random.

3. What is the purpose of a missingness indicator?

4. Why is ordinal encoding inappropriate for nominal categories?

5. Define data leakage and give one example.

Long Answer Questions

1. Explain a systematic workflow for preparing raw data for machine learning.

2. Compare deletion, statistical imputation, and model-based imputation for missing values.

3. Discuss methods for identifying and treating outliers, including the danger of automatic deletion.

4. Explain how numerical, categorical, temporal, and textual variables may be transformed.

5. Describe how a reproducible, leakage-resistant feature-engineering pipeline should be designed.

Analytical / Application Questions

1. A transaction table is joined to a customer table and the number of rows triples. Diagnose possible causes and propose validation steps.

2. A churn model performs extremely well under a random split but poorly on future customers. Analyse likely preparation and leakage problems.

3. Design a feature set for predicting late invoice payment. Specify the prediction point, permissible data, feature windows, and safeguards against leakage.

22. Multiple Choice Questions with Answers

Question 1

A dataset contains one row per customer-month, while transactions contain many rows per customer-month. What should be done before joining them?

A. Delete all repeated transaction rows

B. Aggregate transactions to the customer-month level

C. Use an inner join without inspection

D. Encode customer identifiers as ordinal values

Correct Answer: B. Aggregate transactions to the customer-month level

Question 2

Why should a median imputer be fitted only on training data?

A. To increase the number of observations

B. To remove all outliers from the test set

C. To prevent information from the test set entering training

D. To convert categorical variables into numbers

Correct Answer: C. To prevent information from the test set entering training

Question 3

Which treatment is most appropriate when an extreme purchase is genuine and important for fraud detection?

A. Automatically delete it

B. Replace it with the mean

C. Investigate it and consider robust methods or a separate indicator

D. Convert it to a missing value without documentation

Correct Answer: C. Investigate it and consider robust methods or a separate indicator

Question 4

Which encoding is generally suitable for a nominal variable with categories such as red, blue, and green?

A. One-hot encoding

B. Ordinal encoding assigning 1, 2, and 3

C. Date decomposition

D. Logarithmic scaling

Correct Answer: A. One-hot encoding

Question 5

Which situation is a clear example of temporal leakage?

A. Using customer tenure measured at the prediction date

B. Using previous-month transaction totals

C. Using a cancellation date recorded after the prediction point

D. Using a training-derived median for missing income

Correct Answer: C. Using a cancellation date recorded after the prediction point

Question 6

Why may a random train-test split be unsuitable for forecasting customer churn over time?

A. It always removes categorical variables

B. It can allow future information patterns into training

C. It prevents numerical scaling

D. It guarantees that all rows are duplicated

Correct Answer: B. It can allow future information patterns into training

Question 7

Which statement about target encoding is correct?

A. It is risk-free because categories are converted to numbers

B. It should be calculated using the entire dataset before splitting

C. It can leak outcome information unless calculated within training folds

D. It is only applicable to dates

Correct Answer: C. It can leak outcome information unless calculated within training folds

Question 8

A many-to-many join unexpectedly increases row counts and inflates total revenue. What is the most likely issue?

A. Insufficient standardization of numerical scales

B. Uncontrolled duplication caused by incompatible granularity

C. Use of robust scaling

D. A missingness indicator

Correct Answer: B. Uncontrolled duplication caused by incompatible granularity

Question 9

Which feature is most defensible for predicting payment lateness at the start of a billing period?

A. The customer’s eventual late-payment status

B. A payment failure recorded after the due date

C. Historical payment delays available before the billing period

D. A manually entered outcome label

Correct Answer: C. Historical payment delays available before the billing period

Question 10

What is the primary purpose of a feature store in professional analytics practice?

A. To guarantee that no data is missing

B. To provide consistent, managed features across training and production

C. To replace all domain experts

D. To remove the need for model validation

Correct Answer: B. To provide consistent, managed features across training and production