Data Preparation and Feature Engineering

Introduction

Data science rarely begins with a perfectly organised table. Real data may contain missing values, duplicate records, inconsistent labels, impossible measurements, incompatible formats, and variables recorded at different levels of detail. If these problems are not addressed, statistical analysis and machine-learning models may produce unreliable or misleading results.

Data preparation is the process of profiling, cleaning, integrating, restructuring and validating data before analysis. Feature engineering extends this process by creating useful explanatory variables from existing information. Together, they convert raw data into an analysis-ready dataset.

A central principle is that preparation must be performed in a way that preserves the validity of later evaluation. In particular, information from a validation or test set must not influence transformations learned from the training set. This problem is known as data leakage.

Learning Objectives

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

• profile a dataset using structural, statistical and quality checks;
• identify and treat missing values, duplicates, inconsistent categories and outliers;
• integrate data from multiple sources while preserving meaning and traceability;
• transform variables through encoding, scaling, aggregation and date-time operations;
• engineer useful features for analysis and prediction;
• distinguish legitimate predictive information from data leakage;
• evaluate preparation decisions ethically and professionally.

1. The Data-Preparation Workflow

A practical workflow is iterative rather than strictly linear:

1. define the analytical question and the unit of observation;
2. acquire and document the data;
3. profile structure and quality;
4. clean values and resolve inconsistencies;
5. integrate sources;
6. split data for model evaluation when prediction is involved;
7. learn transformations from training data only;
8. engineer and select features;
9. validate the resulting dataset;
10. document all decisions and produce a reproducible pipeline.

The unit of observation is especially important. A row may represent a customer, transaction, patient visit or monthly account. Joining two tables without understanding their grain can duplicate observations and distort totals.

Data quality dimensions

A value can be complete but inaccurate, or valid in format but inconsistent with another field. Consequently, quality assessment requires both automated rules and subject-matter understanding.

2. Profiling Data Before Cleaning

Profiling is the systematic inspection of a dataset before modifying it. A profile should include:

• number of rows and columns;
• data types and formats;
• unique-value counts;
• missing-value counts and percentages;
• minimum, maximum, mean, median and selected percentiles for numerical variables;
• frequency tables for categorical variables;
• likely identifiers and keys;
• duplicate records;
• suspicious values and range violations.

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

Here, n > 0, and both m and n count records. This percentage describes the amount of missingness, not its cause. Analysts should also examine whether missing values are concentrated in particular groups or time periods.

For numerical variables, the median is often more robust than the mean when extreme values are present. The interquartile range is:

where Q₁ is the 25th percentile and Q₃ is the 75th percentile. Under the conventional box-plot rule, observations below Q₁ − 1.5IQR or above Q₃ + 1.5IQR are flagged as potential outliers. This is a screening rule, not proof that a value is erroneous.

3. Missing Data

Missingness may arise because a question was not asked, a sensor failed, a customer refused to answer, a value was not applicable, or data were lost during extraction. These causes have different implications.

Three useful conceptual mechanisms are:

• Missing completely at random (MCAR): missingness is unrelated to observed and unobserved values.
• Missing at random (MAR): missingness is related to variables that have been observed.
• Missing not at random (MNAR): missingness depends on the unobserved value itself or another unobserved factor.

These are assumptions about a data-generating process, not labels that can always be proven from the dataset.

Treatment strategies

Deletion. Rows or columns may be removed when missingness is small and plausibly harmless. Deletion can reduce sample size and introduce bias, particularly when missingness is systematic.

Simple imputation. Numerical values may be replaced by a training-set mean or median; categorical values may be replaced by the mode or an explicit “Unknown” category. The median is less affected by extreme values. A missingness indicator can also be added:

The indicator is useful when the fact that a value was missing may itself carry information. Imputation values must be calculated from the training data only when building a predictive model.

Group-wise imputation. A value may be imputed using a relevant group, such as median income within region. This can be more realistic but may be unstable for small groups.

Model-based or multiple imputation. More advanced methods estimate plausible values using relationships among variables. They require careful assumptions and validation and should not be used merely because they are sophisticated.

Missing categories should not automatically be interpreted as zero. For example, a missing number of complaints does not necessarily mean that no complaints occurred.

4. Duplicates and Inconsistent Records

A duplicate is not always an identical row. An exact duplicate may be safely removed, but repeated transactions, follow-up visits or multiple legitimate events can look similar. Duplicate detection should therefore use business keys, timestamps and domain rules.

For example, an order identifier should normally be unique in an order table. If the same identifier occurs twice, the analyst must determine whether it represents a duplicated import, an order with multiple line items, or a correction history.

Inconsistent categories commonly include:

• “Male”, “M” and “male”;
• “United Kingdom”, “UK” and “U.K.”;
• spelling errors such as “Managment”;
• mixed units such as kilograms and pounds;
• invisible spaces and differing punctuation.

A reliable standardisation process should:

1. preserve the original value;
2. trim whitespace and standardise case where appropriate;
3. map known variants through a controlled dictionary;
4. separate genuinely different categories;
5. record values that require manual review.

Do not merge categories solely because they appear similar. Category definitions should be agreed with domain experts and documented.

5. Outliers and Anomalies

An outlier is an observation unusually distant from most others. It may be:

• a data-entry error;
• a measurement failure;
• a rare but genuine event;
• a member of a different population;
• evidence of an important business process.

Outliers should not be removed automatically. First investigate the source, units, timestamp, population and associated variables.

Common approaches include:

• correcting confirmed errors using authoritative records;
• retaining valid extreme observations;
• capping values at selected percentiles, known as winsorisation;
• applying a transformation such as a logarithm to a positively skewed variable;
• using robust statistics or models;
• flagging unusual records for separate analysis.

A logarithmic transformation is defined for positive values. If x > 0, a transformed feature may be x' = log(x). The base may be natural logarithm or base 10, but it must be documented. A logarithm changes scale and interpretation; it does not prove that an outlier is invalid.

6. Integrating Data from Multiple Sources

Integration combines data from databases, files, APIs, surveys or operational systems. Important issues include keys, grain, time alignment, units, definitions and provenance.

A one-to-one relationship links one record in one table to one record in another. A one-to-many relationship links one entity, such as a customer, to several transactions. Joining customer attributes directly to transactions may therefore repeat customer values across many rows; this is appropriate for transaction-level analysis but not for calculating the number of unique customers by simply counting rows.

Common join types are:

• inner join: retains matching records in both sources;
• left join: retains every record from the left source and matching records from the right;
• right join: the reverse of a left join;
• full outer join: retains records from either source.

Before joining, analysts should check key uniqueness, unmatched-key counts and row counts before and after the operation. A sudden multiplication of rows often indicates a many-to-many join or an incorrect key.

Integration also requires semantic alignment. “Revenue” may mean gross sales in one system and sales after discounts in another. Dates may represent transaction time, settlement time or reporting time. Data dictionaries, source identifiers and transformation logs support lineage and auditability.

7. Transforming Variables

Numerical variables

Many algorithms perform better when numerical variables are on comparable scales. Two common transformations are:

Min–max scaling

This maps observed minimum and maximum values to 0 and 1 when x_max ≠ x_min. It is sensitive to future values outside the observed range and to extreme observations.

Standardisation

where μ is the training-set mean and σ is the training-set standard deviation, with σ > 0. The resulting training values have mean approximately 0 and standard deviation 1. Standardisation does not make a variable normally distributed.

Tree-based models often do not require scaling, whereas distance-based methods, nearest-neighbour methods, support vector machines and many optimisation-based models can be sensitive to scale.

Categorical variables

One-hot encoding creates one binary variable for each category. For a nominal variable with categories A, B and C, a record in category B can be represented as (0, 1, 0). If an intercept is included in a linear regression, one category is commonly omitted to avoid perfect linear dependence; this is called reference or dummy coding.

Ordinal encoding assigns ordered numbers only when categories have a meaningful order, such as low, medium and high. Assigning 1, 2 and 3 to unrelated categories creates a false numerical relationship.

High-cardinality variables may require grouping rare categories, frequency encoding or domain-specific representations. Such choices should be evaluated for both predictive performance and interpretability.

Dates, text and ratios

Dates can produce year, month, day-of-week, elapsed time and seasonality features. Cyclic variables such as month may be represented using sine and cosine so that December and January are close in the representation:

where t is the position in the cycle and P is the cycle length, such as 12 for months. Both features are needed to represent the cycle consistently.

Text can be transformed into token counts, term-frequency representations or embeddings. Ratios and rates can be informative, but their denominators must be meaningful and non-zero. For example, revenue per customer is not defined when the customer count is zero.

8. Feature Engineering

A feature is an input variable used for description, prediction or decision-making. Feature engineering uses domain knowledge and data structure to create variables that express relevant patterns.

Examples include:

• total spending over the previous 30 days;
• number of support contacts in the previous quarter;
• average delivery delay;
• age calculated from date of birth and a reference date;
• interaction between product type and promotion;
• proportion of income spent on a category;
• rolling mean or change from a previous period.

The reference date is essential. For a model predicting whether a customer will cancel next month, a feature must be calculated using information available before the prediction time. A feature calculated from the customer’s later cancellation record is invalid, even if it improves model accuracy.

Feature selection may remove irrelevant, redundant or highly unstable variables. However, correlation alone does not establish usefulness or causation. A feature may be predictive because it is a proxy for a sensitive attribute, a temporary data-collection practice or an accidental leakage channel.

9. Data Leakage and Reproducible Pipelines

Data leakage occurs when information unavailable at prediction time, or information from the evaluation data, influences model training or feature construction. It produces performance estimates that are unrealistically high.

Two major forms are:

• target leakage: a feature is created using the target or a later consequence of the target;
• train–test contamination: preprocessing is fitted using validation or test observations.

Suppose a dataset is divided into training and test sets. The mean used for imputation, the categories learned by an encoder, scaling parameters and feature-selection decisions should be learned from the training set. The same fitted transformations are then applied unchanged to validation and test data.

A safe conceptual pipeline is:

Raw data → time/stratified split → fit preparation on training data
        → transform training data
        → transform validation/test data using fitted preparation
        → train model → evaluate once on held-out test data

For time-dependent prediction, a random split may be inappropriate because it can allow future patterns to influence the past. A chronological split or rolling evaluation is usually more realistic.

Worked Example: Preparing a Customer-Churn Dataset

Consider an illustrative dataset containing one row per customer per month. The target is whether the customer cancels during the following month.

Initial profiling finds the following:

• 10,000 rows and 12 columns;
• 8% missing values in monthly income;
• region recorded as “North”, “north ” and “N”;
• 42 repeated customer-month keys;
• two negative tenure values;
• a variable called cancellation_date recorded after cancellation;
• customers appearing in several months.

A defensible preparation plan is as follows.

First, confirm the unit of observation: one customer-month. The repeated customer identifier is therefore expected, but the customer-month key should be unique. The 42 repeated keys are investigated and corrected or deduplicated using source-system records.

Second, the two negative tenure values are checked. If they are confirmed entry errors, they are set to missing and treated under the missing-value policy; they are not silently changed to positive values.

Third, region labels are mapped through a documented dictionary to standard categories while retaining the raw field.

Fourth, income is imputed using the median calculated on the training period only. A missing-income indicator is added because missingness may reflect a customer group or collection process.

Fifth, cancellation_date is excluded because it is known only after the outcome and therefore causes target leakage. A legitimate alternative is the number of support contacts during the 30 days before the prediction date.

Finally, the data are split chronologically. Features such as rolling usage and recent complaints are calculated using records available before each prediction date. The preparation steps are stored in a reproducible pipeline and applied identically during evaluation.

The objective is not simply to obtain the highest apparent accuracy. It is to estimate how the system would perform when deployed on future customer-months.

Ethical, Governance and Professional Considerations

Data preparation can affect people even when it appears technically routine. Deleting records may disproportionately remove a disadvantaged group. Imputation may conceal unequal data quality. A category mapping may erase culturally meaningful distinctions. Features may act as proxies for protected characteristics.

Professional practice should therefore include:

• documenting sources, definitions and transformations;
• restricting access to personal and sensitive data;
• minimising data collection and retaining it only as long as justified;
• checking whether missingness and errors differ across groups;
• assessing fairness and disparate impact where decisions affect people;
• preserving audit trails and versioned datasets;
• obtaining appropriate consent and complying with applicable privacy law;
• explaining important transformations to stakeholders.

Synthetic data and automated feature-generation tools can accelerate experimentation, but they do not remove the need for validation, privacy assessment or human oversight. Generative AI may suggest transformations, yet analysts remain responsible for correctness, leakage control and documentation.

Challenges and Limitations

No preparation method is universally best. Median imputation is simple but can reduce variation. Removing outliers can discard valuable rare events. One-hot encoding can create many columns. Scaling can complicate interpretation. Feature engineering may encode historical bias or make a model difficult to explain.

Preparation also depends on context. A value that is an outlier in a national dataset may be normal for a specialist hospital. A missing field may be harmless in one process but a strong operational signal in another. Consequently, automated rules should support—not replace—domain review.

Best-Practice Checklist

• Define the unit of observation and target time before modifying data.
• Profile before cleaning and retain an untouched raw copy.
• Preserve original values and document every transformation.
• Investigate the cause of missingness, duplication and outliers.
• Validate keys, joins, units and row counts after integration.
• Fit preprocessing operations on training data only.
• Use time-aware splitting when future information could otherwise enter training.
• Test engineered features for validity, stability, usefulness and fairness.
• Build transformations into reproducible, version-controlled pipelines.
• Review the final dataset with both technical and domain experts.

Chapter Summary

Data preparation converts imperfect raw data into a trustworthy analytical resource. Profiling identifies structure and quality problems; cleaning addresses missing values, duplicates, inconsistent categories and outliers; integration combines sources while respecting keys, grain, units and provenance. Transformation includes scaling, encoding, date-time extraction, aggregation and other domain-informed operations. Feature engineering creates variables that represent relevant behaviour, but every feature must be available at the time a prediction would be made. Data leakage, whether through target information or contaminated preprocessing, invalidates evaluation. Ethical preparation additionally requires attention to privacy, fairness, representativeness, documentation and reproducibility.

Key Terms

Data profiling: Systematic inspection of dataset structure, distributions and quality.

Data preparation: The process of cleaning, integrating and transforming data for analysis.

Unit of observation: The entity or event represented by one row.

Missing data: A value that is absent, unavailable or not recorded.

MCAR: Missing completely at random.

MAR: Missing at random conditional on observed information.

MNAR: Missing not at random.

Imputation: Replacing or estimating missing values.

Duplicate: A repeated record or repeated business key.

Outlier: An unusually distant observation that requires investigation.

Interquartile range: The difference between the third and first quartiles.

Data integration: Combining information from multiple sources.

Data lineage: A record of data origins and transformations.

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

Standardisation: Subtracting a mean and dividing by a standard deviation.

Feature engineering: Creating useful analytical variables from available data.

Target leakage: Use of target or future information in a predictor.

Data contamination: Allowing evaluation data to influence model preparation.

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

Reference date: The time at which information available for prediction is defined.

Review Questions

Short Answer Questions

1. What is the purpose of data profiling?
2. Distinguish between a duplicate row and a repeated legitimate transaction.
3. Why should median imputation parameters be learned from training data only?
4. When is ordinal encoding appropriate?
5. What is target leakage?

Long Answer Questions

1. Explain the principal data-quality dimensions and describe how each can affect analysis.
2. Compare deletion, simple imputation and model-based imputation, including their limitations.
3. Explain how keys, grain and units affect the integration of two data sources.
4. Discuss the benefits and risks of feature engineering in predictive modelling.
5. Describe a reproducible preparation pipeline from raw data through model evaluation.

Analytical / Application Questions

1. A sales table contains repeated order identifiers after a join. Explain the checks you would perform before deciding whether the records are duplicates or valid line items.
2. A churn model uses “number of complaints resolved” measured during the month after the prediction date. Identify the problem and propose a valid replacement feature.
3. A dataset has missing income concentrated in one region and extreme income values in another. Propose a preparation and validation strategy, including ethical considerations.

Multiple Choice Questions with Answers

Question 1

Which activity should normally occur before cleaning or transforming a dataset?

A. Model deployment

B. Data profiling

C. Hyperparameter tuning

D. Test-set scoring

Correct Answer: B. Data profiling

Question 2

Which statistic is generally more resistant to extreme values than the mean?

A. Median

B. Range

C. Variance

D. Maximum

Correct Answer: A. Median

Question 3

Under the conventional box-plot rule, a potential high outlier is above:

A. Q₃ − 1.5IQR

B. Q₁ + 1.5IQR

C. Q₃ + 1.5IQR

D. Q₁ − 1.5IQR

Correct Answer: C. Q₃ + 1.5IQR

Question 4

Which encoding is most appropriate for a nominal variable such as blood group?

A. Ordinal encoding with arbitrary ranks

B. One-hot encoding

C. Standardisation

D. Logarithmic transformation

Correct Answer: B. One-hot encoding

Question 5

What is the main purpose of a missingness indicator?

A. To guarantee that imputed values are correct

B. To record that the original value was missing

C. To remove all missing records

D. To convert categories into numbers

Correct Answer: B. To record that the original value was missing

Question 6

Which action is an example of target leakage?

A. Calculating age from date of birth at prediction time

B. Scaling a feature using training-set parameters

C. Using a post-cancellation resolution record to predict cancellation

D. Encoding a known region category

Correct Answer: C. Using a post-cancellation resolution record to predict cancellation

Question 7

For standardisation, the mean and standard deviation should generally be calculated from:

A. The complete dataset including the test set

B. The test set only

C. The training set only

D. An unrelated external dataset

Correct Answer: C. The training set only

Question 8

A left join retains:

A. Only unmatched records from both tables

B. Every record from the left table and matching records from the right

C. Only records matching in both tables

D. Every record from the right table and none from the left

Correct Answer: B. Every record from the left table and matching records from the right

Question 9

Why can random splitting be unsuitable for time-dependent prediction?

A. It always reduces the number of rows

B. It may allow future information or patterns to influence training

C. It prevents categorical encoding

D. It removes all outliers

Correct Answer: B. It may allow future information or patterns to influence training

Question 10

If a positively skewed variable x is transformed as x' = log(x), which condition is required?

A. x must be zero

B. x must be positive

C. x must be categorical

D. x must equal its mean

Correct Answer: B. x must be positive