Data Preparation and Feature Engineering

Introduction

Data analysis is only as reliable as the data on which it is based. In business settings, raw data commonly arrives from transaction systems, spreadsheets, surveys, web applications, sensors, customer relationship management platforms, and external providers. These sources differ in definitions, formats, granularity, quality, and update schedules. Before analysis or visualization, an analyst must therefore convert raw records into data that are accurate enough, consistently structured, traceable, and suitable for the intended decision.

Data preparation is the systematic process of inspecting, cleaning, integrating, reshaping, transforming, and documenting data. Feature engineering extends this process by creating useful variables from existing fields. For example, a transaction date can be converted into month and weekday, revenue and cost can be combined into margin, and individual purchases can be aggregated into customer-level measures.

Preparation is not merely a technical preprocessing step. It involves interpretation. Deciding whether a missing value means “not applicable,” “not recorded,” or “zero” can change the meaning of an analysis. Similarly, removing an extreme observation may eliminate a genuine but important business event. Good preparation therefore combines computational skill with domain knowledge, statistical reasoning, governance, and documentation.

Learning Objectives

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

1. Explain the purpose and stages of a reproducible data-preparation workflow.
2. Identify and treat missing values, duplicates, outliers, and inconsistent formats.
3. Integrate datasets while preserving correct keys, grain, and relationships.
4. Reshape and transform data for analysis and visualization.
5. Engineer meaningful, reproducible features using spreadsheets, SQL, Python, or R.
6. Evaluate the risks of leakage, bias, inappropriate imputation, and undocumented changes.
7. Document data preparation so that another analyst can reproduce and audit it.

1. The Data-Preparation Workflow

A robust workflow is iterative rather than strictly linear. Analysts usually move through the following stages:

1. Understand the purpose. Define the business question, outcome variable, population, time period, and unit of analysis.
2. Inventory the sources. Record file names, tables, owners, refresh dates, formats, and access conditions.
3. Profile the data. Examine row counts, data types, ranges, missingness, distinct values, duplicate keys, and unusual patterns.
4. Clean and standardize. Correct formats, resolve inconsistencies, treat duplicates and missing values, and investigate outliers.
5. Integrate sources. Join or append data using documented keys and compatible levels of detail.
6. Reshape and transform. Convert data between wide and long forms, aggregate records, and create analytical variables.
7. Validate. Test totals, ranges, uniqueness, referential integrity, and business rules.
8. Document and preserve. Store the source, transformation logic, assumptions, output, and version information.

A useful principle is to preserve the raw data unchanged and create a separate cleaned or analytical layer. This supports auditability and makes it possible to revisit decisions.

Grain and the Unit of Analysis

The grain of a dataset describes what one row represents. A sales table may have one row per order, one row per order line, or one row per customer per month. Confusing these levels is a major source of incorrect analysis.

Suppose an order contains three product lines. Joining an order-level discount table to the line-level sales table can duplicate the discount three times. If the analyst then sums discount, the result is overstated. Before joining, determine the grain of every source and whether the join key is unique at that grain.

A data dictionary should identify each field, its meaning, type, unit, permitted values, and source. For example, revenue should specify whether it is gross or net, whether tax is included, and which currency is used.

2. Data Quality Dimensions

Data quality is multidimensional. Important dimensions include:

A dataset may be complete but inaccurate, or valid in format but conceptually wrong. For example, a date stored as 2025-03-01 is syntactically valid, but it may represent March 1 in one system and January 3 in a system using day-month-year notation. Quality checks must therefore combine technical rules with business understanding.

3. Missing Values

Missingness can arise because a question was not asked, a sensor failed, a customer refused to answer, a field was not applicable, or a system failed to capture a value. These meanings must not be treated as identical.

Common representations include blank cells, NULL, NA, N/A, unknown, -, and zero. They should be standardized carefully. A zero is a measured value, whereas a blank may mean that no measurement exists. Replacing all blanks with zero can create false sales, false quantities, or misleading averages.

Diagnosing Missingness

For variable X, the missing-value rate can be calculated as:

where m_X is the number of missing observations for X and n is the total number of observations. The result is a proportion; multiply by 100 for a percentage. This calculation assumes that every row represents one observation and that missingness has been consistently encoded.

Missingness may be described as:

• MCAR (Missing Completely at Random): missingness is unrelated to observed or unobserved values.
• MAR (Missing at Random): missingness is related to observed variables but not, after accounting for them, to the missing value itself.
• MNAR (Missing Not at Random): missingness depends on the unobserved value or another unobserved process.

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

Treatment Strategies

1. Retain missing values. Appropriate when missingness itself is meaningful or when the analytical method handles missing values.
2. Delete records or variables. Listwise deletion may be reasonable when missingness is limited and plausibly random, but it reduces sample size and can introduce bias.
3. Impute a constant. For example, “Not applicable” may be represented by a separate category. Zero should be used only when zero has substantive meaning.
4. Use simple statistical imputation. Median imputation is often more robust than mean imputation for skewed numerical data. Mode imputation can be used for categorical data, but it reduces variation.
5. Use conditional or model-based imputation. Values can be estimated using related variables, multiple imputation, or predictive models. Such methods require careful validation.
6. Create a missingness indicator. A binary variable can record whether the original value was missing. This is useful when missingness may itself carry information.

Imputation must be performed using information available at the relevant time. In predictive modeling, parameters such as a median should be calculated on the training data and then applied to validation or test data. Otherwise, information leaks from the evaluation set into model development.

4. Duplicates and Record Identity

Duplicates may be exact copies, repeated business events, or legitimate records that share some attributes. Removing duplicates solely because two rows look similar can destroy valid transactions.

Begin by identifying the intended key. If order_id should be unique, test its uniqueness. In SQL, a diagnostic query might be:

SELECT order_id, COUNT(*) AS row_count
FROM orders
GROUP BY order_id
HAVING COUNT(*) > 1;

The result requires investigation. Multiple rows may be correct if the table is at order-line grain. A duplicate is a data-quality problem only when it violates the intended representation.

Useful duplicate strategies include:

• exact-row comparison;
• key-based checks;
• retaining the latest record using a timestamp;
• consolidating repeated events when a documented rule supports it;
• preserving all rows when each represents a genuine event.

Always record how many rows were removed, retained, or consolidated and why.

5. Inconsistent Formats and Standardization

Inconsistent representation is common in dates, text, units, currency, categories, and identifiers.

Dates and Times

Convert dates to a recognized date type rather than leaving them as text. Record the time zone and determine whether a timestamp represents event time, upload time, or system-processing time. For cross-country data, daylight-saving changes may affect elapsed-time calculations.

A duration between two timestamps can be expressed as:

where both timestamps must use a compatible time zone and unit. If one is in hours and the other in minutes, conversion is required before subtraction.

Text and Categories

Standardization may include trimming spaces, normalizing case, correcting known spelling variants, and mapping synonyms to controlled categories. For example, United Kingdom, UK, and U.K. may be mapped to one canonical value. This mapping should be stored in a reference table rather than hidden in an undocumented formula.

Units and Currency

Values must not be combined until units are compatible. If a distance is reported in miles and kilometers, convert one unit before aggregation. Currency conversion requires an exchange-rate date and a stated convention, such as transaction-date rate or month-end rate. Historical conversion should not silently use today’s rate.

6. Outliers and Anomalous Observations

An outlier is an observation unusually distant from the rest of a distribution. An outlier may indicate an error, a rare but valid event, a change in process, fraud, or a different population. It is not automatically a value to delete.

For a numerical variable, the interquartile range is:

where Q₁ is the first quartile and Q₃ is the third quartile. A common exploratory rule labels observations below Q₁ − 1.5 × IQR or above Q₃ + 1.5 × IQR as potential outliers. This is a screening rule, not proof of error. It is most useful for distributions where quartiles provide a meaningful summary and should be interpreted cautiously for small or highly discrete samples.

The z-score is:

where x_i is an observation, μ is the population or reference mean, and σ is the corresponding standard deviation. A z-score has no units. Thresholds such as ±3 are rough screening conventions and are sensitive to skewness and to the influence of outliers on the mean and standard deviation.

Possible responses are:

• verify the observation against the source;
• correct a transcription or unit error;
• retain it and use robust summaries;
• cap or winsorize values under a justified policy;
• transform a skewed variable, such as using log(1+x) when X is non-negative;
• analyze the subgroup separately;
• remove the record only with documented evidence that it is erroneous.

7. Integrating Data Sources

Integration includes joining related tables and appending records with compatible columns. Joins depend on keys and relationships:

• An inner join keeps matching records in both sources.
• A left join keeps every record from the left source and matching records from the right.
• A full outer join keeps records from both sources, including unmatched records.

A join is safe only when the key relationship is understood. If a customer table has one row per customer and a transaction table has many rows per customer, the relationship is one-to-many. If both sides contain repeated keys, a many-to-many join may multiply rows unexpectedly.

After a join, validate:

1. row count before and after;
2. number of matched and unmatched keys;
3. uniqueness of expected identifiers;
4. totals before and after, allowing for legitimate changes;
5. whether measures were duplicated.

A practical alternative to a wide join is to aggregate each source to the intended analytical grain first. For customer analysis, transactions might be summarized into total orders, revenue, and most recent purchase date before being joined to customer attributes.

8. Reshaping and Transformation

Data are often stored in either wide or long form.

• Wide form: repeated measures appear in separate columns, such as sales_Jan, sales_Feb, and sales_Mar.
• Long form: each row contains an identifier, a measure name, and a value, such as customer_id, month, and sales.

Long form is generally convenient for grouping, filtering, visualization, and statistical operations. Wide form can be convenient for reports and some spreadsheet layouts. Reshaping should be driven by analytical purpose, not by a universal preference.

Common transformations include:

• aggregation by customer, product, region, or time;
• pivoting and unpivoting;
• converting text to numeric values;
• scaling or standardizing numerical fields;
• categorizing continuous measures into bands;
• extracting components from dates or strings;
• creating ratios and rates.

A ratio requires a meaningful denominator. For example, conversion rate is often:

The denominator must be defined consistently, and a zero denominator must not be silently converted into a valid rate. Analysts should retain numerator and denominator fields so that the rate can be recalculated and audited.

9. Feature Engineering

A feature is an input variable used for description, prediction, segmentation, or decision support. Feature engineering creates features that represent relevant business mechanisms more effectively than raw fields.

Common Feature Types

Reproducible Feature Definitions

A feature definition should specify:

1. name and business meaning;
2. source fields;
3. formula or transformation rule;
4. unit and time window;
5. treatment of missing and impossible values;
6. population and grain;
7. version and creation date;
8. validation tests.

For example, average order value may be defined as:

This definition assumes that revenue and order count refer to the same customer, period, currency, and inclusion rules. Canceled orders must be handled explicitly.

Scaling and Encoding

For some algorithms, numerical variables are transformed to comparable scales. Min–max scaling is:

where x_min and x_max are reference minimum and maximum values. It maps values to [0, 1] when the denominator is positive. Standardization is:

where μ and σ are calculated from the training data in predictive modeling and σ ≠ 0. Scaling does not remove skewness and can be sensitive to extreme values.

Categorical variables may be represented using one-hot encoding. For a variable with categories A, B, and C, three binary indicators can be created for descriptive purposes; in a linear regression with an intercept, one category is commonly omitted as a reference to avoid perfect multicollinearity. The correct encoding depends on the model and software.

Leakage and Temporal Validity

Data leakage occurs when a feature uses information that would not have been available at the time of prediction or decision. Examples include using a post-sale refund status to predict whether a sale will be refunded, calculating customer lifetime spend using transactions after the prediction date, or imputing using the full dataset before splitting into training and test sets.

A safe temporal process defines an observation date, a prediction date, and an information cutoff. Every feature must be computable using data available by that cutoff. Time-based validation is often more realistic than random splitting for forecasting and operational prediction.

10. Implementation Patterns

Spreadsheet Practice

Spreadsheets are effective for small, transparent datasets when formulas and controls are carefully managed. Use separate sheets for raw data, reference mappings, transformations, quality checks, and output. Prefer structured tables, data validation, explicit formulas, and protected cells. Avoid manually overwriting source values and document assumptions in a data dictionary.

SQL Practice

SQL is well suited to repeatable filtering, joining, aggregation, and validation. A common pattern is to use common table expressions:

WITH cleaned AS (
  SELECT
    customer_id,
    CAST(order_date AS DATE) AS order_date,
    revenue
  FROM orders
  WHERE revenue >= 0
), customer_features AS (
  SELECT
    customer_id,
    SUM(revenue) AS total_revenue,
    COUNT(*) AS order_count,
    MAX(order_date) AS last_order_date
  FROM cleaned
  GROUP BY customer_id
)
SELECT * FROM customer_features;

The exact syntax varies by database system. The logic should be accompanied by tests for null identifiers, negative revenue, duplicate keys, and date boundaries.

Python and R Practice

In Python, tabular preparation is commonly implemented with pandas; in R, packages such as dplyr and tidyr are widely used. A conceptual Python pattern is:

orders["order_date"] = pd.to_datetime(orders["order_date"], errors="coerce")
orders["revenue"] = pd.to_numeric(orders["revenue"], errors="coerce")
orders["month"] = orders["order_date"].dt.to_period("M")

errors="coerce" converts invalid values to missing values; it does not prove that the result is correct. The analyst must profile the newly created missing values and investigate their source. Reusable functions, notebooks under version control, parameterized pipelines, and automated tests improve reproducibility.

11. Worked Example: Customer Purchase Features

Consider the following illustrative transaction data. The intended grain is one row per order.

The objective is to create customer-level features as of March 31, using completed orders only.

Step 1: Define the population

Cancelled orders are excluded because the objective concerns completed purchases. The cutoff date is March 31, so no later records may be used.

Step 2: Aggregate

For C01, completed revenue is 120 + 80 = 200 and completed order count is 2. For C02, the cancelled order is excluded, so revenue is 150 and order count is 1. For C03, revenue is 90 and order count is 1.

Step 3: Calculate features

The resulting table is:

For C01, AOV = 200 ÷ 2 = 100. Each value is in the transaction currency. The calculation is valid because the numerator and denominator use identical inclusion criteria and the denominator is nonzero.

A misleading alternative would include the canceled 200 for C02, producing revenue of 350. That feature would not represent completed customer purchases. This example illustrates that feature engineering is partly a business-definition task, not merely a coding task.

12. Illustrative Case: Retail Data-Preparation Pipeline

Background

A fictional omnichannel retailer wants to identify customers at risk of becoming inactive. Its data team receives online orders, store transactions, customer profiles, and campaign responses from separate systems.

Problem

The initial dashboard reports inconsistent revenue by channel. Customer identifiers are missing in some store records, dates use different formats, and a join between order lines and order-level promotions inflates discount totals.

Analysis

The team profiles each source and establishes the following grains: order lines, orders, customers, and campaign responses. It discovers that online order IDs are unique, while store receipt numbers can repeat across branches. It also finds that some revenue values include tax and others exclude it. Missing customer IDs are concentrated in cash purchases, so treating them as randomly missing would be inappropriate.

Approach and Solution

The team creates a source-preservation layer and a data dictionary. It standardizes currency and defines net revenue consistently. It aggregates order lines to order grain before joining order-level promotions. Store receipt keys are combined with branch identifiers. Customer-level features are then generated using a fixed 90-day observation window:

• completed-order count;
• net revenue;
• days since most recent purchase;
• proportion of purchases made online;
• campaign response count.

The pipeline records unmatched keys, duplicate candidates, missingness by channel, and row counts at every stage. Features are calculated only from information available before the inactivity assessment date.

Outcome

The retailer obtains consistent channel totals and a reproducible customer table. The team does not impute missing customer IDs for cash transactions because no defensible identity link exists. Instead, it reports identifiable and unidentifiable sales separately and communicates the coverage limitation to decision-makers.

Learning Points

1. Grain must be established before integration.
2. Business definitions such as net revenue must be agreed before calculation.
3. Missing identifiers can create systematic bias rather than random inconvenience.
4. Aggregating to the correct level prevents duplicated measures.
5. A documented limitation is preferable to a fabricated value.

13. Validation, Governance, and Ethics

Preparation should include automated and human checks. Examples include:

• required identifiers are not null;
• dates fall within a plausible range;
• quantities are non-negative unless returns are explicitly represented;
• category values belong to approved lists;
• totals reconcile to trusted reports;
• joins do not unexpectedly multiply rows;
• feature distributions are monitored over time.

Data provenance records where data originated, how it changed, who changed it, and when. Version control should preserve code and configuration. A change log should distinguish corrections to source data from changes in analytical definitions.

Ethically, feature engineering can reproduce or intensify discrimination. A location variable may act as a proxy for protected characteristics; missingness may reflect unequal access to services; and a risk score may be used for high-impact decisions without adequate review. Analysts should assess purpose limitation, data minimization, fairness, privacy, access controls, retention, and explainability. Personal data should be processed according to applicable law and institutional policy.

Artificial intelligence can assist with schema matching, anomaly detection, coding, and natural-language classification. However, automated suggestions require human verification. An AI system may infer an incorrect category, expose sensitive data through an external service, or create undocumented transformations. AI-assisted preparation should therefore use approved tools, test outputs, preserve provenance, and include review by a knowledgeable analyst.

14. Best Practices and Common Failure Modes

Best practices include:

• begin with the analytical question and unit of analysis;
• keep raw data immutable;
• use explicit, reusable transformation logic;
• standardize missing-value representations;
• validate before and after every major join;
• calculate training-dependent transformations only on training data;
• retain numerator and denominator fields for rates;
• distinguish correction from deletion;
• document assumptions and unresolved limitations;
• monitor data quality after deployment.

Common failure modes include:

• replacing every blank with zero;
• deleting all outliers without investigation;
• joining tables without checking key uniqueness;
• using future information in predictive features;
• treating a valid format as evidence of conceptual accuracy;
• manually changing records without an audit trail;
• using a model-generated feature without checking bias or meaning.

Chapter Summary

Data preparation converts heterogeneous raw data into a trustworthy analytical asset. The process begins with a clear question, grain, and data dictionary; continues through profiling, cleaning, integration, reshaping, transformation, and feature engineering; and ends with validation and documentation. Missing values must be interpreted rather than mechanically replaced. Duplicates require knowledge of record identity, and outliers require investigation because they may be errors or genuine events. Joins must respect key relationships and grain. Features such as aggregates, ratios, temporal variables, lags, and encodings are valuable only when their definitions are transparent and temporally valid. Leakage, inconsistent units, undocumented assumptions, privacy risks, and proxy discrimination can undermine otherwise sophisticated analyses. Reproducible code, controlled mappings, automated tests, provenance, and clear communication are central professional practices.

Key Terms

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

Feature engineering: Creating analytical variables from raw or existing data.

Grain: The level of detail represented by one row.

Data dictionary: A structured description of fields, meanings, types, units, and rules.

Missing value: An observation for which a value is unavailable, inapplicable, or unrecorded.

MCAR: Missing completely at random.

MAR: Missing at random conditional on observed information.

MNAR: Missing not at random.

Duplicate: A repeated or conflicting record that violates the intended data representation.

Outlier: An observation unusually distant from the distribution of other observations.

IQR: The third quartile minus the first quartile.

Join: A relational operation that combines tables using matching fields.

Append: Combining records from datasets with compatible columns.

Data leakage: Use of information unavailable at the time of prediction or decision.

Imputation: Estimating or assigning a replacement for a missing value.

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

Scaling: Transforming numerical variables to a selected range or reference distribution.

Provenance: A record of data origin, transformations, ownership, and history.

Data validation: Testing whether data satisfy technical and business rules.

Review Questions

Short Answer Questions

1. What is meant by the grain of a dataset, and why is it important before joining tables?
2. Distinguish a missing value from a zero value.
3. What is the purpose of an interquartile-range outlier rule?
4. Define data leakage and give one example.
5. Why should raw data generally be preserved unchanged?

Long Answer Questions

1. Explain a complete, reproducible data-preparation workflow from source inventory through documentation.
2. Compare deletion, constant imputation, statistical imputation, and missingness indicators. Discuss when each may be appropriate.
3. Explain how incorrect key relationships can distort a join and produce misleading business measures.
4. Discuss the principles of feature engineering, including aggregation windows, scaling, categorical encoding, and temporal validity.
5. Evaluate the ethical and governance risks associated with automated or AI-assisted data preparation.

Analytical / Application Questions

1. A transaction table contains multiple rows per order, while a promotion table contains one row per order. Design a preparation sequence that prevents promotion values from being multiplied.
2. A customer-income field is missing for 35% of rural customers and 8% of urban customers. What investigations and treatment decisions should an analyst make before modeling?
3. A churn model is built using a feature called “number of support complaints in the 60 days after subscription cancellation.” Identify the problem and propose a temporally valid alternative.

Multiple Choice Questions with Answers

Question 1

Which principle should be established before joining two datasets?

A. The preferred chart type

B. The grain and key relationship of each dataset

C. The color palette for the dashboard

D. The number of decimal places in the output

Correct Answer: B. The grain and key relationship of each dataset

Question 2

A blank field in a sales table should be replaced with zero only when:

A. All blanks are inconvenient for analysis

B. The field is numeric

C. Zero has the intended business meaning

D. The dataset contains fewer than 10,000 rows

Correct Answer: C. Zero has the intended business meaning

Question 3

What does the IQR equal?

A. The mean minus the median

B. The third quartile minus the first quartile

C. The maximum minus the minimum in every case

D. The standard deviation divided by the mean

Correct Answer: B. The third quartile minus the first quartile

Question 4

Which situation is most clearly data leakage?

A. Using a customer’s past purchases to predict future activity

B. Using a product category recorded before prediction

C. Using post-cancellation complaints to predict cancellation

D. Using a training-set median to impute training-set values

Correct Answer: C. Using post-cancellation complaints to predict cancellation

Question 5

What is a likely consequence of joining two tables with repeated keys on both sides?

A. Automatic removal of all missing values

B. Unexpected multiplication of rows and measures

C. Conversion of all dates into timestamps

D. Guaranteed preservation of one row per customer

Correct Answer: B. Unexpected multiplication of rows and measures

Question 6

For the values 100, 150, and 50, what is the average order value?

A. 50

B. 75

C. 100

D. 300

Correct Answer: C. 100

Question 7

Which transformation is generally appropriate for text categories such as region?

A. One-hot encoding or a documented category mapping

B. Subtracting the category from the mean

C. Replacing every category with zero

D. Applying a time-zone conversion

Correct Answer: A. One-hot encoding or a documented category mapping

Question 8

Why should scaling parameters for a predictive model be calculated on training data only?

A. To make the test set larger

B. To prevent information from the evaluation data entering training

C. To eliminate all outliers automatically

D. To guarantee a normal distribution

Correct Answer: B. To prevent information from the evaluation data entering training

Question 9

Which item is most important in a feature definition?

A. The analyst’s preferred programming language only

B. The source fields, rule, grain, time window, and assumptions

C. The dashboard background color

D. The number of rows in unrelated tables

Correct Answer: B. The source fields, rule, grain, time window, and assumptions

Question 10

A customer table has one row per customer and a transaction table has many rows per customer. What relationship is this?

A. One-to-one

B. One-to-many

C. Many-to-one only from the transaction perspective

D. Many-to-many necessarily

Correct Answer: B. One-to-many