6. Data Wrangling and Feature Engineering

Introduction and Context

Data rarely arrives in the form required for statistical modelling or machine learning. It may contain inconsistent names, duplicated records, missing values, implausible measurements, mixed date formats, unstructured text, and information distributed across several files or database tables. Data wrangling is the disciplined process of converting such raw data into a reliable analytical dataset. Feature engineering extends this process by constructing variables that represent useful patterns for statistical or predictive models.

For a data scientist, wrangling is not merely a programming task. It is an exercise in interpretation. A missing value may indicate non-response, system failure, or that a question was not applicable. A duplicate may be an error or a legitimate repeated transaction. An extreme value may be an outlier, or it may represent an important but rare event. Consequently, transformation decisions must be based on domain knowledge, documented assumptions, and evidence.

This chapter develops practical methods using Python tools—especially pandas and Polars—and R tools—especially data.table, dplyr, and tidyr. It also addresses reproducibility, leakage-resistant feature construction, governance, and professional quality assurance.

Learning Objectives

After studying this chapter, students should be able to:

1. Explain the purpose, scope, and terminology of data wrangling and feature engineering.

2. Inspect, clean, reshape, join, aggregate, and validate structured data.

3. Apply pandas, Polars, data.table, dplyr, and tidyr appropriately.

4. Develop defensible strategies for missing values, outliers, duplicates, dates, text, and categorical variables.

5. Construct features without introducing target leakage.

6. Design auditable and reproducible transformation pipelines.

7. Evaluate practical, ethical, technical, and organizational risks in feature development.

From Raw Records to Analytical Features

A useful distinction is between an observation, a variable, and a feature. An observation is a unit such as a customer, order, patient, or event. A variable is a recorded attribute. A feature is a variable supplied to an analytical model; it may be an original variable or a transformation of several variables. The target or response is the outcome to be predicted or explained.

A robust wrangling workflow usually follows this sequence:

1. Profile the data. Identify sources, dimensions, data types, ranges, missingness, uniqueness, and likely keys.

2. Clarify the analytical grain. Decide whether one row represents a customer, transaction, day, or another unit.

3. Standardize and validate. Harmonize names, types, units, codes, and formats, while checking constraints.

4. Reshape and combine. Convert wide and long representations as required and join related tables.

5. Handle data quality problems. Treat missingness, duplicates, invalid values, and outliers using documented rules.

6. Construct features. Encode categories, derive dates, aggregate events, transform text, and scale numeric values where appropriate.

7. Split and audit. Separate training and evaluation data before learning data-dependent transformations, then test the final dataset.

The analytical grain must be established before joining tables. If a customer table is joined directly to a transaction table, one customer may appear many times. A subsequent customer-level model may then unintentionally give greater weight to customers with more transactions. Grain errors are among the most consequential and least visible wrangling mistakes.

The Grammar of Modern Data Wrangling

Most data manipulation systems provide equivalent conceptual operations:

• Select columns of interest.
• Filter rows satisfying conditions.
• Mutate or transform columns.
• Arrange or sort observations.
• Group and summarise data.
• Join tables using keys.
• Reshape between long and wide forms.

In pandas, these operations commonly use `loc`, `query`, assignment, `groupby`, `agg`, `merge`, `pivot`, and `melt`. Polars provides an expression-based API with `select`, `filter`, `with_columns`, `group_by`, `join`, `pivot`, and `unpivot`; its lazy execution can optimize a query before collecting results. In R, dplyr uses verbs such as `select()`, `filter()`, `mutate()`, `summarise()`, `group_by()`, and joins. tidyr supplies `pivot_longer()` and `pivot_wider()`, while data.table uses concise expressions such as `DT[i, j, by]` for filtering, transformation, and grouped computation.

The exact syntax differs, but the reasoning is the same. A pipeline should read from source to result and should avoid silently changing row counts or meanings. For example, a grouped customer summary might be represented conceptually as:

```python

customer_features = (

transactions

.groupby("customer_id", as_index=False)

.agg(total_spend=("amount", "sum"),

order_count=("order_id", "nunique"),

last_order=("order_date", "max"))

)

```

An equivalent dplyr operation is:

```r

customer_features <- transactions |>

group_by(customer_id) |>

summarise(

total_spend = sum(amount, na.rm = TRUE),

order_count = n_distinct(order_id),

last_order = max(order_date, na.rm = TRUE),

.groups = "drop"

)

```

These calculations are meaningful only if transaction identifiers, amounts, and dates have first been validated.

Inspection, Types, and Constraints

Inspection should precede transformation. Useful checks include row and column counts, data types, distinct values, quantiles, frequency tables, missing-value counts, and minimum and maximum dates. A data dictionary should describe each field, its unit, permitted values, source, and intended use.

Type conversion is analytical, not cosmetic. A code such as `00127` should generally remain a string, because converting it to an integer destroys leading zeros. Monetary values should have a defined currency and precision. Dates should be parsed with an explicit convention rather than relying on ambiguous automatic inference. Boolean fields should not treat arbitrary strings such as “unknown” as false.

Constraints can be classified as:

• Domain constraints: age cannot be negative; a probability lies between zero and one.
• Relational constraints: an order’s customer identifier should exist in the customer table.
• Temporal constraints: a discharge date should not precede admission.
• Uniqueness constraints: a declared transaction key should identify one record.

A failed constraint should generate a visible warning or exception. Silently coercing invalid values to missing values can conceal source-system failures.

Missing Data: Diagnosis Before Treatment

Missingness has both statistical and operational meaning. Three conventional mechanisms are missing completely at random (MCAR), missing at random conditional on observed information (MAR), and missing not at random (MNAR), where missingness depends partly on the unobserved value itself. These mechanisms cannot always be established from the dataset alone, so the analyst should combine statistical examination with knowledge of how data was collected.

First distinguish structural missingness from accidental missingness. “Number of dependants” may be inapplicable to a child, whereas a missing postal code may reflect a failed form submission. Useful diagnostics include missingness by group, collection period, source system, and target class.

Common treatments include:

• deleting rows or variables when the loss is justified and limited;
• imputing numeric values with a median or a model-based estimate;
• imputing categorical values with a meaningful category such as “Unknown”;
• carrying values forward only when the time-based logic supports it;
• adding a missingness indicator when absence itself may be informative.

Imputation must be fitted on the training data and then applied to validation or production data. For a numeric variable, the training median—not the median of the complete dataset—must be used. Mean imputation can distort distributions and is sensitive to extreme values. Multiple imputation may be more suitable for statistical inference than for a straightforward production scoring pipeline, but its assumptions must still be examined.

Duplicates, Inconsistency, and Outliers

Duplicate detection requires a definition of identity. Exact duplicate rows are easy to find, but near-duplicates may differ in spelling, timestamps, or formatting. A customer may legitimately place two orders at the same amount, so deduplicating on amount and customer alone would be unsafe. Prefer a trusted event identifier, source-system rule, or survivorship policy that specifies which record is retained.

Inconsistent categories should be standardized using controlled mappings. For example, “United Kingdom,” “UK,” and “U.K.” may be mapped to one canonical value, while an unrecognized value should be reported rather than automatically changed.

Outliers are observations that are unusual relative to a chosen reference distribution. Detection methods include domain thresholds, the interquartile range rule, robust z-scores, and model-based approaches. Detection is not proof of error. A very large transaction may be fraud, a corporate purchase, or a data-entry mistake. Appropriate responses include correcting a verified error, capping values, transforming them, retaining them with an indicator, or excluding them under a documented rule.

Winsorisation and logarithmic transformations can reduce the influence of extreme values, but they alter interpretation. Tree-based models often require less scaling, whereas distance-based methods, regularized regression, and neural networks are commonly sensitive to feature scale.

Reshaping, Joining, and Aggregation

Tidy data generally represents each observational unit as a row, each variable as a column, and each value as a cell. Long form is often preferable for grouped analysis and visualization. Wide form is sometimes required for modelling or reporting.

`pivot_longer()` in tidyr, `melt()` in pandas, and corresponding operations in data.table convert repeated columns into key-value rows. Their inverse, `pivot_wider()` or `pivot()`, spreads values into columns. Reshaping requires attention to duplicate combinations of identifier and category; otherwise, aggregation rules may be applied unintentionally.

Joins should be classified by expected cardinality: one-to-one, one-to-many, many-to-one, or many-to-many. Inner joins retain matches only; left joins retain all records from the left table; full joins retain records from both sides. A left join is often appropriate when enriching a primary population, but unmatched records should be counted and investigated. Many-to-many joins can multiply rows dramatically and should normally trigger an explicit warning.

Aggregation should occur at the correct grain. To build customer-level features from transactions, calculate measures such as count, sum, average, recency, and variability, then verify that the resulting customer key is unique. Distinguish `count` from `nunique`: repeated transaction lines may not represent repeated orders.

Feature Engineering for Structured Data

Categorical Variables

Nominal categories with no order are commonly one-hot encoded. If a variable has levels red, blue, and green, encoding should create indicator columns, usually with a defined handling policy for unseen levels. Ordinal categories such as bronze, silver, and gold may be represented by ordered scores only when the ordering and spacing are substantively defensible.

High-cardinality variables require care. Frequency encoding, carefully regularized target encoding, hashing, or domain-based grouping may be useful. Target encoding must be calculated within training folds; otherwise, the outcome of an observation can influence its own feature and cause leakage. Unknown categories in production should map to a defined fallback rather than causing a pipeline failure.

Numeric Transformations and Scaling

Standardization transforms a value using the training mean and standard deviation. Min–max scaling maps values to a specified interval. Robust scaling uses statistics such as the median and interquartile range. The choice depends on the model, distribution, interpretability, and sensitivity to outliers. Scaling is usually unnecessary for decision trees but important for k-nearest neighbours, support vector machines, principal component analysis, and many optimization-based models.

Logarithmic or `log1p` transformations are useful for positive right-skewed quantities such as income or transaction value. Binning can capture nonlinear effects but may discard information and create boundary artifacts. Polynomial and interaction features can represent relationships that a linear model otherwise cannot capture, though they increase dimensionality and risk overfitting.

Dates and Time

Dates should be parsed into a consistent time zone and stored with explicit semantics. Useful features include year, month, weekday, hour, elapsed time, age at an event, time since last activity, and time to next event. Cyclical variables such as hour of day can be represented with sine and cosine terms so that 23:00 and 00:00 remain close in feature space.

Time-dependent problems require chronological splitting. A random split may allow future patterns to influence the past and produce unrealistic performance estimates. Rolling-window validation is often more credible for forecasting or operational prediction.

Text

Basic text features include character or word counts, token frequencies, vocabulary indicators, and domain-specific dictionaries. More advanced representations include term-frequency inverse-document-frequency (TF–IDF) vectors, word embeddings, and transformer-based representations. Text preprocessing should preserve meaningful distinctions and should be fitted within the training process when vocabulary or statistics are learned.

Removing stop words, punctuation, or personally identifying information is not universally beneficial. Negation, code fragments, medical terms, and product identifiers may be analytically important. Text also carries privacy and bias risks, particularly when it contains names, demographic references, or sensitive narratives.

Aggregated and Relational Features

Many useful features summarize event histories: number of purchases in the previous 30 days, average support response time, proportion of failed payments, or number of distinct products. The time boundary is essential. A feature used to predict an event at time t must be computed only from information available before t. A “lifetime spend” calculated using all future transactions is leakage if used to predict an earlier decision.

Leakage-Resistant Pipelines and Auditability

Target leakage occurs when a feature contains information unavailable at prediction time or is influenced by the target. It may arise from future records, post-outcome administrative fields, preprocessing performed before data splitting, or target encoding calculated globally.

A safe design separates three stages. First, split data according to the deployment setting. Second, fit all learned transformations—imputers, scalers, encoders, vocabularies, and feature selectors—on training data only. Third, apply the fitted transformations to validation, test, and production data. In Python, scikit-learn `Pipeline` and `ColumnTransformer` can enforce this structure; in R, recipes from tidymodels provide a similar approach. The wrangling libraries remain responsible for data preparation, while the modelling framework can control fitting and resampling.

An auditable pipeline records source versions, code version, schema, transformation parameters, row counts, key uniqueness, rejected records, missingness before and after processing, and feature definitions. Data lineage should permit an analyst to answer: where did this feature come from, when was it computed, what population and time window did it use, and who approved the rule?

An Illustrative Case: Predicting Subscription Churn

Background

A fictional streaming company wants to identify subscribers at risk of cancelling in the next 30 days. Available data includes customer attributes, subscription records, payment events, viewing sessions, support contacts, and cancellation outcomes.

Situation and Problem

The source tables use different identifiers and time zones. Some customers have multiple subscription records, payment amounts are missing for promotional plans, and viewing data contains duplicate event messages. The initial modelling team proposes using all available customer information and reports excellent validation performance.

Analysis

A data audit reveals that the proposed feature `cancellation_reason` is populated only after cancellation. It is therefore a direct leakage variable. The team also discovers that a customer-level join has multiplied rows because each customer is linked to many viewing events. Missing payment amounts are structural for free promotional periods, not random numeric omissions. Finally, the random train-test split places later activity from the same subscription into training when earlier activity appears in testing.

Approach and Solution

The team defines the prediction timestamp as the end of each weekly observation window. It constructs one row per active customer-window. Features are generated only from events before that timestamp: sessions in the previous 7, 30, and 90 days; number of failed payments; days since last session; support-contact count; and subscription tenure. Promotional subscriptions receive a separate plan indicator, while payment amount is imputed only for plans where payment is expected.

Duplicate viewing events are removed using the source event identifier and a documented duplicate rule. Tables are aggregated to the customer-window grain before joining. Categorical encoders, imputers, and scalers are fitted within each training fold. Validation uses chronological rolling windows, and all feature definitions are recorded in a data catalogue.

Outcome

The revised performance is lower than the original result but is more credible because it reflects the information available at deployment. Operational staff receive a ranked list of customers with explanations such as declining recent activity or repeated payment failures. The business monitors both predictive performance and whether retention interventions are fairly distributed across customer groups.

Learning Points

The case demonstrates that correct grain, temporal reasoning, leakage prevention, and domain-specific missing-data treatment are more important than producing a high apparent score. A sophisticated model cannot compensate for an invalid dataset.

Professional and Governance Considerations

Data wrangling can create harms even when the modelling objective appears legitimate. Imputation may conceal unequal data collection. Encoding postcode, language, or browsing behaviour may act as a proxy for protected characteristics. Automated outlier removal can erase minority but valid cases. Text features may expose confidential information. Analysts should apply data minimisation, purpose limitation, access controls, retention policies, and relevant privacy law.

Feature documentation should include business meaning, source, calculation, refresh frequency, owner, permitted use, and known limitations. Reproducible code should be tested like software, with unit tests for boundary dates, unseen categories, null keys, duplicate identifiers, and empty groups. Monitoring after deployment should detect schema drift, distribution shift, missingness changes, and declining model performance.

Current Directions

Modern systems increasingly use lazy and distributed execution, allowing Polars, data.table, SQL engines, and cloud platforms to process larger datasets efficiently. Data contracts specify expected schemas between producers and consumers. Feature stores can centralize reusable, versioned features and enforce online/offline consistency, although they introduce operational complexity. Automated feature discovery and generative AI assistants may accelerate transformation development, but generated code still requires testing, security review, lineage, and human approval. The central principle remains unchanged: automation should improve consistency and scale without weakening interpretability or accountability.

Best-Practice Checklist

A professional wrangling and feature-engineering process should:

• define the unit of analysis and prediction timestamp;
• preserve raw data and create versioned derived datasets;
• inspect before transforming and validate after transforming;
• use explicit schemas, units, keys, and category mappings;
• quantify unmatched joins, duplicates, rejected values, and missingness;
• fit learned transformations only on training data;
• use temporal validation when deployment is time-dependent;
• document every feature’s meaning, source, and availability;
• test pipelines with representative edge cases;
• monitor data quality and feature distributions after deployment.

Chapter Summary

Data wrangling converts heterogeneous raw records into a coherent analytical structure, while feature engineering creates variables that expose meaningful patterns to models. Effective practice begins with profiling, a clear analytical grain, explicit types, and validated constraints. Missing values, duplicates, outliers, categories, dates, and text require context-sensitive treatment rather than mechanical rules. Reshaping, joining, and aggregation must respect keys and cardinality. Numeric transformations and encoding should match the model and be fitted only on training data. Temporal features must use information available at prediction time, and all transformations should be reproducible, documented, and auditable. Professional practice also requires attention to privacy, fairness, drift, governance, and operational monitoring.

Key Terms

Analytical grain: The unit represented by one row in a dataset.

Data wrangling: The process of inspecting, cleaning, transforming, combining, and validating data.

Feature engineering: Creating or transforming variables for analytical or predictive use.

Target leakage: Use of information unavailable at prediction time or influenced by the outcome.

MCAR: Missing completely at random, with missingness unrelated to observed or unobserved values.

MAR: Missing at random conditional on observed information.

MNAR: Missingness related partly to the unobserved value itself.

Imputation: Replacing missing values using a defined statistical or logical procedure.

Cardinality: The number and relationship of unique values or records in a key.

Join: Combining tables using matching key fields.

Tidy data: A structure in which each observation is a row, each variable a column, and each value a cell.

One-hot encoding: Representing categorical levels as binary indicator variables.

Target encoding: Replacing a category with an outcome-based statistic, subject to leakage risk.

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

Winsorisation: Limiting extreme values to selected percentile boundaries.

TF–IDF: A text representation weighting terms by frequency within a document and rarity across documents.

Data lineage: A record of a dataset or feature’s origin and transformation history.

Schema drift: Change in data structure, types, categories, or fields over time.

Review Questions

Short Answer Questions

1. Why must analytical grain be established before joining tables?

2. Distinguish structural missingness from accidental missingness.

3. What is the difference between an inner join and a left join?

4. Why should a code such as `00127` normally be stored as a string?

5. Define target leakage and give one example.

Long Answer Questions

1. Explain a complete data-wrangling workflow from profiling to validation, including the role of documentation.

2. Compare common strategies for handling missing values, and discuss how their suitability depends on missingness mechanisms.

3. Explain how categorical encoding, scaling, and transformation choices depend on the intended model.

4. Discuss the use of date, text, and event-history features in a time-dependent prediction problem.

5. Describe how pandas, Polars, dplyr, tidyr, and data.table express the principal operations of data wrangling.

Analytical / Application Questions

1. A many-to-one customer table is joined to a transaction table and the resulting row count triples. Diagnose the likely issue and propose a validation and correction strategy.

2. A churn model has outstanding validation performance, but one feature is populated after customers cancel. Analyse the problem and redesign the feature pipeline.

3. Design a leakage-resistant feature-engineering plan for predicting next-month loan default using monthly customer records, including splitting, aggregation windows, missing-data treatment, and monitoring.

22. Multiple Choice Questions with Answers

Question 1

A dataset contains one row per transaction, but the intended model predicts customer churn. What is the most appropriate first step?

A. One-hot encode every transaction column

B. Define a customer-level analytical grain and aggregate transactions

C. Delete all customers with multiple transactions

D. Scale transaction amounts using the full dataset

Correct Answer: B. Define a customer-level analytical grain and aggregate transactions

Question 2

Which practice most directly prevents leakage when median-imputing a predictor?

A. Use the median calculated from training data only

B. Use the median calculated from the test data

C. Replace all missing values with zero

D. Calculate a separate median after model deployment

Correct Answer: A. Use the median calculated from training data only

Question 3

A left join is selected when enriching a primary customer table. What does it preserve?

A. Only customers with matching records

B. Only records from the right table

C. All records from the left table, including unmatched customers

D. Only duplicate keys from both tables

Correct Answer: C. All records from the left table, including unmatched customers

Question 4

Why can random train-test splitting be inappropriate for a forecasting problem?

A. It always produces too few observations

B. It may allow future information to influence evaluation of past predictions

C. It prevents categorical encoding

D. It removes all missing values

Correct Answer: B. It may allow future information to influence evaluation of past predictions

Question 5

Which example is most likely structural missingness?

A. A failed sensor reading caused by system downtime

B. A blank age field caused by form abandonment

C. Payment amount absent because a subscription plan is free

D. A missing value created by an invalid parser

Correct Answer: C. Payment amount absent because a subscription plan is free

Question 6

Which operation is most appropriate for representing nominal categories such as payment method?

A. One-hot encoding

B. Ranking categories from 1 to 5 without justification

C. Replacing every category with the target mean globally

D. Taking the logarithm of category labels

Correct Answer: A. One-hot encoding

Question 7

A feature is calculated as total purchases made during the 30 days after a prediction date. Why is it invalid for predicting behaviour at that date?

A. It has too few categories

B. It uses information from the future

C. It is necessarily an outlier

D. It cannot be aggregated

Correct Answer: B. It uses information from the future

Question 8

Which statement about an extreme transaction value is most defensible?

A. Every extreme value must be deleted

B. Every extreme value must be capped at the mean

C. It should be investigated in context before deciding how to treat it

D. It proves the data was collected incorrectly

Correct Answer: C. It should be investigated in context before deciding how to treat it

Question 9

What is a principal purpose of data lineage?

A. To increase the number of features automatically

B. To record a feature’s source and transformation history

C. To replace validation with documentation

D. To guarantee that a model is unbiased

Correct Answer: B. To record a feature’s source and transformation history

Question 10

Why can target encoding be dangerous when computed globally before cross-validation?

A. It removes all numeric variables

B. It uses outcome information that can influence validation observations

C. It prevents joins between tables

D. It changes dates into text

Correct Answer: B. It uses outcome information that can influence validation observations