Data Transformation and Feature Preparation

Introduction

Data rarely arrives in a form that can be analysed directly or supplied safely to a machine-learning algorithm. A dataset may contain missing values, inconsistent text, dates stored as character strings, categorical labels, extreme measurements, duplicated records, and variables measured on incompatible scales. Data transformation is the systematic conversion of raw data into reliable, interpretable, and analysis-ready data. Feature preparation is the selection, construction, encoding, and scaling of variables used in descriptive analysis or predictive modelling.

Transformation is not merely a technical cleaning stage. It changes how information is represented and can therefore affect statistical conclusions, visualizations, model accuracy, fairness, and reproducibility. A transformation should have a clear purpose, be documented, and be applied consistently to training, validation, test, and operational data.

Learning Objectives

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

• filter and sort records using explicit conditions;
• aggregate data at an appropriate level of analysis;
• transform numerical variables through scaling and normalization;
• encode categorical variables without introducing misleading order;
• bin continuous variables and derive useful features;
• transform strings, dates, times, and categorical values;
• identify leakage, bias, and other risks in feature preparation;
• design a reproducible transformation workflow.

1. The Transformation Workflow

A professional workflow normally follows these stages:

1. Clarify the analytical question. Define the unit of analysis, outcome variable, time period, and decision to be supported.
2. Inspect the data. Examine types, ranges, missingness, duplicates, unique values, and relationships between tables.
3. Standardize representation. Correct names, data types, units, spelling, date formats, and category labels.
4. Transform variables. Filter, sort, aggregate, encode, scale, bin, or derive features as justified.
5. Validate. Check row counts, distributions, constraints, and representative records.
6. Document and operationalize. Record rules, parameters, software versions, and the order of operations.

The order matters. For example, category labels should generally be standardized before counting categories, and duplicate records should be investigated before aggregation. A transformation pipeline should distinguish between operations that are fixed by domain rules and parameters learned from data.

Unit of analysis and granularity

The unit of analysis is what one row represents: a customer, transaction, patient visit, sensor reading, or monthly branch. A common error is to combine variables at different granularities. For example, joining a customer table to transaction data creates repeated customer attributes for every transaction. This may be correct for transaction analysis but incorrect if a model is intended to predict one outcome per customer.

2. Filtering, Sorting, and Selecting

Filtering

Filtering retains records satisfying a condition. Conditions may use equality, inequalities, membership, ranges, and logical operators. For example, a sales dataset might retain transactions where region is “East”, amount is greater than 500, and transaction date falls within a specified quarter.

A range condition should state whether endpoints are included. The notation a ≤ x < b means that values from a inclusive to b exclusive are retained. Explicit boundaries prevent accidental overlap when consecutive intervals are used.

Filtering can be used for:

• selecting a study population;
• removing invalid measurements according to documented rules;
• creating training and evaluation subsets;
• restricting analysis to a time period;
• implementing business or privacy requirements.

Filtering is not the same as deleting inconvenient observations. Removing outliers merely because they weaken a result is poor practice. An extreme value should be investigated: it may be an error, a legitimate rare event, or evidence that the population contains subgroups.

Sorting

Sorting arranges rows according to one or more variables. It is useful for identifying the largest values, inspecting chronological sequences, checking duplicates, and preparing data for cumulative calculations. Sorting does not change the values or establish causality. A stable sort preserves the original order of records that have equal values, which can help reproducibility.

Selecting columns and records

Column selection should be guided by the analytical purpose. Unnecessary identifiers, free-text fields, or post-outcome variables may increase privacy risk or cause data leakage. A unique identifier can be retained for auditing but should not automatically be used as a predictive feature.

3. Aggregation and Grouped Summaries

Aggregation replaces several records with a summary at a higher level, such as calculating total monthly revenue from transaction-level data. Common functions include count, sum, mean, median, minimum, maximum, standard deviation, and distinct count.

For observations x₁, x₂, …, x_n, the arithmetic mean is:

Here, n is the number of observations and xᵢ is observation i. The mean is appropriate when values are quantitative and a mean is meaningful; it can be strongly affected by extreme values. The median may better represent a skewed distribution.

The sample standard deviation is:

The denominator n − 1 is used when estimating population variability from a sample under standard statistical assumptions. The result has the same units as x.

Worked example: grouped revenue

Suppose three transactions for Branch A have amounts 120, 180, and 300, while two transactions for Branch B have amounts 200 and 250. Branch A’s total is 600 and its mean is 200. Branch B’s total is 450 and its mean is 225. If branches are compared by total revenue, Branch A ranks higher; if compared by average transaction value, Branch B ranks higher. The aggregation measure must therefore match the decision.

Weighted summaries are necessary when observations represent different quantities. For example, an overall rate should generally be calculated as total successes divided by total trials, not as an unweighted average of subgroup rates.

Aggregation risks include the ecological fallacy, loss of individual variation, unequal group sizes, and accidental double-counting after joins. Always validate the number of records before and after grouping.

4. Numerical Transformation and Scaling

Numerical variables may differ greatly in units and range. A distance measured in kilometres and income measured in currency units can cause scale-sensitive algorithms to give disproportionate influence to one variable.

Min–max normalization

Min–max scaling maps a value to a specified interval, commonly [0, 1]:

Here, x_min and x_max are the minimum and maximum values used for scaling. The formula assumes x_max > x_min. It is sensitive to outliers and is especially useful when a bounded range is desired.

To map to [a, b]:

Standardization

Standardization produces a variable with mean approximately 0 and standard deviation approximately 1:

Here, μ is the reference mean and σ is the reference standard deviation, with σ > 0. In machine learning, μ and σ must be calculated using the training set only, then applied unchanged to validation, test, and future records. Otherwise, information from the evaluation data leaks into the model-development process.

Standardization is useful for distance-based methods, principal component analysis, regularized regression, and neural networks. It does not make a distribution normal and does not remove outliers.

Robust scaling and transformations

When extreme values are influential, robust scaling can use the median and interquartile range:

The interquartile range is IQR = Q₃ − Q₁, where Q₁ and Q₃ are the first and third quartiles. Logarithmic transformation can reduce right skew for strictly positive values. When zeros occur, a domain-justified transformation such as log(1 + x) may be used, but its interpretation must be documented. Negative values require a different treatment or a justified shift; arbitrary shifting can complicate interpretation.

5. Categorical Variables and Encoding

A categorical variable contains labels or categories rather than measured numerical quantities. Categories may be:

• nominal, with no natural order, such as department;
• ordinal, with a meaningful order, such as low, medium, and high;
• binary, with two categories, such as yes and no.

One-hot encoding

One-hot encoding creates a binary indicator for each category. If payment method has Cash, Card, and Transfer, three indicator columns may be created. For an observation using Card, the values are Cash = 0, Card = 1, Transfer = 0. In a linear model with an intercept, one category is often omitted as a reference category to avoid perfect multicollinearity. This is called the dummy-variable trap.

One-hot encoding is generally appropriate for nominal variables. It may create many columns when cardinality is high, so rare categories may be grouped into “Other” when this is substantively defensible.

Ordinal encoding

Ordinal encoding assigns increasing numbers to ordered categories, such as Poor = 1, Fair = 2, Good = 3, Excellent = 4. It should be used only when the order is meaningful. The numerical gaps may incorrectly imply that the difference between Poor and Fair equals the difference between Good and Excellent. If that assumption is unsuitable, one-hot encoding or another method is safer.

Frequency and target encoding

Frequency encoding replaces a category with its count or proportion. It is compact but may merge categories with different meanings. Target encoding replaces a category with an outcome statistic, such as its mean target value. It can be powerful but is highly vulnerable to leakage and overfitting. It must be calculated within training folds, with smoothing and appropriate validation.

Unknown and missing categories require explicit policies. A production system should not fail simply because a new category appears after deployment.

6. Binning and Derived Variables

Binning

Binning converts a continuous variable into intervals. Equal-width bins have intervals of the same numerical width; equal-frequency bins contain approximately equal numbers of observations. Domain-based bins, such as clinically defined age ranges, are often most interpretable.

Binning can reveal patterns and simplify communication, but it discards information and can create artificial discontinuities. A value just below a boundary may be treated very differently from a value just above it. Continuous variables should not be binned automatically merely because a model can accept categories.

Feature derivation

A derived feature is calculated from existing variables. Examples include:

• total order value = quantity × unit price;
• profit margin = profit ÷ revenue, where revenue is non-zero;
• customer tenure = reference date − start date;
• number of prior purchases;
• change from the previous measurement;
• ratio of actual production to planned production.

Each derived variable needs a precise definition, valid denominator rules, unit specification, and a time reference. Ratios can be unstable when denominators approach zero. A feature such as “number of complaints in the next 30 days” is invalid for predicting today’s outcome because it uses future information.

Interaction features represent combined effects, for example product category × customer segment. They may improve a model but increase complexity and risk of sparsity. Feature construction should be driven by domain reasoning, exploratory evidence, or a justified modelling strategy.

7. String Transformation

Text fields often contain inconsistent capitalization, whitespace, punctuation, abbreviations, and spelling. Common operations include trimming leading and trailing spaces, converting case, replacing known abbreviations, extracting substrings, splitting fields, and testing patterns with regular expressions.

For example, “ north ”, “NORTH”, and “N.” might be standardized to one approved category only if the business meaning is confirmed. Blind replacement can merge genuinely different values. Email addresses, telephone numbers, postcodes, and product codes should be transformed according to their formal rules rather than general lowercasing or punctuation removal.

Free text can be represented using token counts, term frequency–inverse document frequency, embeddings, or domain-specific dictionaries. Such methods are beyond simple categorical cleaning and require attention to privacy, language, vocabulary drift, and interpretability.

8. Date and Time Transformation

Dates should be parsed using an explicit format and time zone. Ambiguous values such as 03/04/2025 may mean 3 April or 4 March. A reliable data record should distinguish date, local time, time zone, and, when relevant, daylight-saving rules.

Useful date-time features include year, quarter, month, week, day of week, hour, weekend indicator, elapsed time, and time since the previous event. Cyclical variables such as hour of day can be represented using sine and cosine so that midnight and 23:00 are close rather than far apart:

Here, h is the hour represented on a 24-hour cycle. The pair preserves cyclic structure, although interpretation differs from a single linear hour variable.

For time-based prediction, records must be ordered chronologically. Randomly splitting future records into training data can create leakage. Lagged features must be calculated using only information available at the prediction time.

9. Missing Values, Outliers, and Quality Controls

Missingness may arise because a value was not collected, was not applicable, was refused, or was lost during integration. These meanings should not automatically be represented by the same value. Common strategies include deletion, constant-category assignment, mean or median imputation, model-based imputation, and adding a missingness indicator. The choice depends on the variable, missingness mechanism, and purpose of analysis.

Imputation parameters must be learned from the training data in predictive modelling. Mean imputation, for example, should use the training mean, not the full dataset. Imputed values should be flagged where their status matters.

Outliers should be investigated using domain limits, visualization, robust statistics, and source-system checks. Winsorization or clipping may be appropriate in some operational settings, but it changes the data-generating representation and must be documented.

Useful quality controls include:

• uniqueness and referential-integrity checks;
• allowed-value and range checks;
• row-count reconciliation;
• null-rate monitoring;
• duplicate detection;
• distribution comparison across time and groups;
• unit and currency validation;
• checks that derived values satisfy business constraints.

10. Leakage, Fairness, and Governance

Data leakage occurs when information unavailable at the intended prediction time enters the features or transformation process. Leakage can arise from future outcomes, post-treatment variables, random splitting of time-dependent data, or scaling and imputation performed before the train–test split.

Feature preparation also affects fairness. Removing a protected attribute does not necessarily remove discrimination because proxy variables, such as postcode or purchasing behaviour, may encode it. Compare performance and error rates across relevant groups where legally and ethically appropriate. Do not use sensitive data for analysis without a lawful purpose, suitable safeguards, and transparency.

Personal identifiers should be minimized, access-controlled, and protected. Transformation pipelines should record data sources, definitions, parameter values, timestamps, and approvals. Reproducibility requires that another analyst can reconstruct how a feature was created.

11. Applied Case: Predicting Customer Churn

Background

An illustrative subscription company wants to identify customers at risk of cancelling within the next 30 days. Raw data contains customer records, monthly invoices, support contacts, plan types, and cancellation events.

Problem

The data includes inconsistent plan labels, duplicate invoice rows, missing tenure, timestamps in multiple time zones, and variables recorded after cancellation. The company needs a customer-level modelling table with one row per customer at a defined monthly reference date.

Analysis and solution

First, duplicate invoices are investigated using invoice identifiers and retained only when duplication is confirmed. Plan labels such as “Pro”, “PRO ”, and “professional” are mapped to an approved taxonomy after business confirmation. Dates are converted to a common time zone and parsed explicitly.

Next, transactions are aggregated by customer and reference month. Features include monthly invoice total, number of support contacts during the previous 90 days, days since the last login, tenure in months, and an indicator for payment failure. A missing tenure value is investigated against the source system rather than automatically set to zero.

The outcome is defined as cancellation within the following 30 days. Therefore, all predictor windows end at the reference date. Records from the future are excluded from features. Numerical variables are standardized using training-period parameters, and plan type is one-hot encoded with a defined reference category. The data is split chronologically: earlier months for training, a later month for validation, and the most recent month for testing.

Outcome

The resulting table has a clear unit of analysis, consistent definitions, and reduced leakage risk. Model performance can be interpreted more confidently because the features correspond to information that would actually have been available at the time of intervention.

Learning points

The case demonstrates that feature preparation is a reasoning process, not a sequence of mechanical commands. Time, aggregation level, source-system definitions, and operational availability are as important as encoding and scaling techniques.

12. Best Practices and Contemporary Tools

Use pipeline-based workflows so that transformations are fitted and applied consistently. Store transformation metadata, use version control, test edge cases, and review changes as code. Separate exploratory transformations from production transformations. Parameterize dates and reference periods rather than hard-coding them.

Modern data platforms increasingly support declarative transformation, automated data-quality tests, feature stores, schema monitoring, and lineage tracking. Automated machine-learning systems can select transformations, but their outputs still require human review for leakage, fairness, interpretability, and domain validity. Artificial intelligence does not remove the need to understand the meaning and timing of variables.

Chapter Summary

Data transformation converts raw records into a structured representation suitable for analysis and modelling. Filtering and sorting support selection and inspection, while aggregation changes the unit of analysis and must be matched to the decision. Numerical scaling includes min–max normalization, standardization, and robust alternatives; parameters for predictive workflows must be learned from training data only. Categorical variables require encoding that respects nominal or ordinal meaning. Binning improves interpretability but loses information, while derived variables can express domain relationships when their units, denominators, and time windows are explicit. String and date-time transformations require controlled standards, and temporal features must respect information availability. Missing values, outliers, leakage, privacy, fairness, and reproducibility are central professional concerns. A reliable feature pipeline is validated, documented, and applied consistently from development through deployment.

Key Terms

Data transformation: Conversion of raw data into a form suitable for analysis or modelling.

Feature: A variable used to describe an observation or support prediction.

Unit of analysis: The entity represented by one row in a dataset.

Filtering: Retaining records that satisfy specified conditions.

Aggregation: Combining multiple records into a summary at a higher level.

Normalization: Rescaling a variable to a defined numerical range.

Standardization: Transforming a variable using a reference mean and standard deviation.

Categorical variable: A variable whose values are labels or categories.

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

Ordinal encoding: Assigning numerical codes to ordered categories.

Binning: Dividing a continuous variable into intervals.

Derived feature: A variable calculated from one or more existing variables.

Imputation: Replacing or estimating missing values.

Outlier: An observation unusually distant from the rest or from an expected range.

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

Target encoding: Replacing a category with an outcome-related statistic.

Reference category: The category against which other encoded categories are compared.

Feature pipeline: An ordered, repeatable set of feature-preparation operations.

Review Questions

Short Answer Questions

1. What is meant by the unit of analysis, and why is it important during aggregation?
2. Distinguish min–max normalization from standardization.
3. Why is one-hot encoding generally suitable for nominal variables?
4. State two risks associated with binning a continuous variable.
5. What is data leakage? Give one example involving time.

Long Answer Questions

1. Explain a complete workflow for transforming a raw transaction dataset into an analysis-ready table.
2. Compare one-hot, ordinal, frequency, and target encoding, including appropriate use cases and risks.
3. Discuss how missing values and outliers should be investigated and treated in a professional data project.
4. Explain why scaling and imputation parameters must be learned from training data only.
5. Describe the role of documentation, validation, privacy, and fairness in feature preparation.

Analytical / Application Questions

1. A dataset contains customer transactions and one row per customer is required for churn prediction. Design an aggregation and feature-construction plan that avoids future information.
2. A salary variable is highly right-skewed and contains several extreme values. Recommend a transformation strategy and explain what should be checked before applying it.
3. A model performs well overall but poorly for a minority region after postcode encoding. Analyse possible causes and propose technical and governance responses.

Multiple Choice Questions with Answers

Question 1

Which transformation is generally most appropriate for a nominal variable such as payment method?

A. One-hot encoding

B. Ordinal encoding based on alphabetical order

C. Standardization

D. Differencing

Correct Answer: A. One-hot encoding

Question 2

In min–max scaling to the interval [0, 1], what happens when x equals the reference minimum?

A. It becomes −1

B. It becomes 0

C. It becomes 1

D. It becomes the mean

Correct Answer: B. It becomes 0

Question 3

Why should the mean and standard deviation for standardization be calculated from training data only?

A. To increase the number of features

B. To guarantee a normal distribution

C. To prevent information leakage from evaluation data

D. To remove all outliers

Correct Answer: C. To prevent information leakage from evaluation data

Question 4

Which statistic is usually more resistant to extreme values?

A. Arithmetic mean

B. Maximum

C. Range

D. Median

Correct Answer: D. Median

Question 5

A customer-level churn model uses the number of complaints made during the 30 days after the prediction date. What problem does this create?

A. Underfitting

B. Data leakage

C. One-hot encoding

D. Robust scaling

Correct Answer: B. Data leakage

Question 6

For an ordinal variable with categories Poor, Fair, Good, and Excellent, which encoding is potentially appropriate when the order is meaningful?

A. Ordinal encoding

B. Random encoding

C. Date parsing

D. Min–max filtering

Correct Answer: A. Ordinal encoding

Question 7

What is the main purpose of using sine and cosine together for hour-of-day features?

A. To remove all missing values

B. To represent the cyclic relationship between times

C. To convert categories into text

D. To aggregate transactions

Correct Answer: B. To represent the cyclic relationship between times

Question 8

A grouped average is calculated for two branches. Why might the branch with the higher average not have the higher total revenue?

A. Averages and totals measure different quantities and group sizes may differ

B. Standardization always reverses rankings

C. One-hot encoding changes revenue

D. Sorting removes large transactions

Correct Answer: A. Averages and totals measure different quantities and group sizes may differ

Question 9

Which practice is most appropriate for a new category appearing in production data?

A. Silently assign it to the highest-risk category

B. Define and document an unknown-category policy

C. Delete the entire production dataset

D. Calculate its target encoding using future outcomes

Correct Answer: B. Define and document an unknown-category policy

Question 10

What is a major disadvantage of binning a continuous variable?

A. It always creates data leakage

B. It can discard information and create artificial boundaries

C. It prevents any form of visualization

D. It makes all categories nominally identical

Correct Answer: B. It can discard information and create artificial boundaries