Data Cleaning, Transformation and Feature Engineering

Introduction

Data science models learn from the data they receive, not from the intentions of the analyst who collected it. Real-world data commonly contains missing observations, duplicated records, inconsistent labels, incorrect data types, extreme values, and measurements recorded in incompatible units. If these problems are ignored, descriptive statistics may be misleading, visualisations may be distorted, and predictive models may produce unreliable or unfair results.

Data cleaning is the systematic detection and treatment of data-quality problems. Data transformation changes data into forms that are suitable for analysis or modelling. Feature engineering creates informative variables from raw fields. Together, these activities form a central part of the data-science workflow.

A professional workflow must be reproducible. The same cleaning and transformation decisions should be applied consistently to training data, validation data, test data, and future production observations. Python, particularly pandas and scikit-learn, provides tools for implementing such workflows.

Learning Objectives

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

1. Diagnose missing values, duplicates, outliers, inconsistent categories, and incorrect data types.
2. Select appropriate treatments based on data meaning, scale, and modelling purpose.
3. Transform numerical and categorical variables safely.
4. Construct useful and defensible engineered features.
5. Build reproducible preprocessing pipelines in Python.
6. Evaluate the statistical, ethical, and operational consequences of cleaning decisions.

1. Data Quality as an Analytical Problem

Data quality is multidimensional. A dataset may be complete but inaccurate, consistent but irrelevant, or correctly formatted but biased. Important dimensions include:

Cleaning is not merely deleting rows until a dataset looks tidy. Every intervention should have a rationale, be documented, and be tested against the intended analytical task.

A useful principle is to distinguish data errors from unusual but valid observations. A salary of 500,000 may be an error in a general employee dataset, but it may be valid for an executive. Context determines treatment.

2. A Diagnostic Workflow

A robust workflow normally follows these stages:

1. Understand the data-generating process. Identify sources, units, definitions, collection procedures, and the meaning of each field.
2. Profile the raw data. Inspect shape, types, ranges, uniqueness, missingness, and representative records.
3. Specify quality rules. For example, dates must be valid, quantities must be non-negative, and product codes must belong to an approved list.
4. Separate training and test data when modelling. Do not estimate imputation or scaling parameters from the test set.
5. Clean and transform using documented rules.
6. Validate the resulting data. Recheck constraints and compare distributions before and after treatment.
7. Persist the procedure. Store code, configuration, versions, and assumptions.

Typical pandas inspection commands include:

import pandas as pd

print(df.shape)
print(df.head())
print(df.info())
print(df.describe(include="all"))
print(df.isna().mean().sort_values(ascending=False))
print(df.nunique(dropna=False).sort_values())

isna().mean() calculates the proportion of missing values in each column because Boolean values are averaged as 1 for true and 0 for false. For a column with 20 missing values among 100 observations, the missingness rate is 0.20, or 20%.

3. Missing Values

3.1 Types and mechanisms of missingness

A missing value may be represented by NaN, None, NaT, an empty string, or a placeholder such as "Unknown", -999, or 0. Placeholders are not automatically genuine measurements and should be identified during profiling.

Statistical discussions commonly distinguish three missingness mechanisms:

• MCAR (Missing Completely At Random): missingness is unrelated to observed or unobserved values.
• MAR (Missing At Random): missingness depends on observed variables, after accounting for them.
• MNAR (Missing Not At Random): missingness depends on the missing value itself or an unobserved factor.

These mechanisms cannot always be established from the dataset alone. For example, income may be missing more often for people who prefer not to disclose high income, suggesting a non-random mechanism.

3.2 Diagnosing missingness

missing = (
    df.isna()
      .sum()
      .to_frame("count")
      .assign(rate=lambda x: x["count"] / len(df))
      .sort_values("rate", ascending=False)
)

Missingness should also be examined by group and time. A column may appear acceptable overall while being almost entirely absent for one region or one month.

3.3 Treatment strategies

The appropriate treatment depends on the variable, the missingness mechanism, and the purpose of the analysis.

For numerical data, the sample mean is:

where xᵢ is an observed value and n is the number of observed values. The mean is suitable only when its sensitivity to extreme values is acceptable. The median is the middle ordered observation, or the average of the two middle observations when the number of observations is even.

A missingness indicator can preserve information about absence:

df["income_missing"] = df["income"].isna().astype("int8")
df["income"] = df["income"].fillna(df["income"].median())

This is useful when the fact that a value was missing may itself relate to the outcome. It should not, however, be added automatically without considering privacy, fairness, and causal interpretation.

For machine learning, fit imputation values only on the training set. Otherwise, the test set influences the model-development process, creating data leakage.

4. Duplicate Records

Duplicates may be exact copies or partial duplicates. Exact duplicates can often be detected with:

exact_duplicates = df.duplicated(keep=False)
df[exact_duplicates]

To remove exact duplicates while retaining the first occurrence:

df = df.drop_duplicates()

Partial duplicates require domain rules. For example, two rows with the same transaction ID may indicate a repeated import, or they may represent legitimate line items. A safe procedure is to:

1. Identify the candidate key.
2. Count repeated keys.
3. Compare timestamps and non-key fields.
4. Determine whether repetition is expected.
5. Retain, aggregate, or remove records according to the data model.

key_counts = df["transaction_id"].value_counts()
repeated = key_counts[key_counts > 1]

Never remove duplicates solely because two records share one value. A customer can place multiple orders, and a patient can have multiple visits.

5. Inconsistent Categories and Text

Categorical inconsistency occurs when different strings represent the same concept. Common sources include capitalisation, spelling, whitespace, punctuation, abbreviations, and language differences.

df["city"] = (
    df["city"].astype("string")
      .str.strip()
      .str.casefold()
)

city_map = {
    "new york city": "new york",
    "nyc": "new york",
    "n.y.": "new york"
}
df["city"] = df["city"].replace(city_map)

Standardisation should use an approved mapping rather than uncontrolled fuzzy matching. Fuzzy matching can be useful for candidate generation, but questionable matches should be reviewed because incorrectly merging categories can be more damaging than leaving them separate.

For categorical validation:

allowed = {"basic", "standard", "premium"}
invalid = ~df["plan"].isin(allowed) & df["plan"].notna()
print(df.loc[invalid, "plan"].value_counts())

Do not silently convert unknown values to a valid category. Preserve an unknown or other category when the distinction matters, and record how it was created.

6. Data Types, Units and Dates

Incorrect data types cause both computational errors and misleading analysis. A column containing numbers stored as strings cannot be safely averaged. A date stored as text cannot reliably support time-based grouping.

df["amount"] = pd.to_numeric(df["amount"], errors="coerce")
df["order_date"] = pd.to_datetime(df["order_date"], errors="coerce", utc=True)
df["quantity"] = pd.to_numeric(df["quantity"], errors="coerce")

errors="coerce" converts invalid values to missing values, which must then be diagnosed rather than forgotten. Before conversion, preserve the raw column if auditability is important.

Unit consistency is essential. If one source records weight in kilograms and another in pounds, values must be converted before combination. The conversion is:

where m_lb is mass in pounds and m_kg is mass in kilograms. The factor is exact under the international definition of the pound. Similar care is required for currency, temperature, distance, and time zones.

Dates should be interpreted with an explicit convention where ambiguity exists. For example, 03/04/2025 may mean 3 April or March 4. Time-zone normalisation is particularly important when combining logs from different locations.

7. Outliers and Anomalies

An outlier is an observation unusually distant from the rest of a distribution. An anomaly is an observation that violates expected behaviour or a process rule. They are related but not identical: an unusually large legitimate purchase is an outlier, whereas a duplicate payment may be an anomaly without being statistically extreme.

7.1 Interquartile-range rule

Let Q₁ be the first quartile and Q₃ the third quartile. The interquartile range is:

A common exploratory rule identifies 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 approximately continuous data and can flag many legitimate observations in heavy-tailed distributions.

q1 = df["amount"].quantile(0.25)
q3 = df["amount"].quantile(0.75)
iqr = q3 - q1
flag = (df["amount"] < q1 - 1.5 * iqr) | (df["amount"] > q3 + 1.5 * iqr)

The z-score is another screening measure:

where μ is the population or reference mean and σ is the corresponding standard deviation. It assumes that a mean and standard deviation provide a meaningful reference; it can be unreliable for skewed data and is itself influenced by extreme values.

7.2 Treatment options

• Investigate: verify against the source system.
• Correct: repair a known data-entry or unit error.
• Retain: keep a valid extreme value and consider robust methods.
• Transform: use a logarithm for positive, right-skewed quantities when substantively justified.
• Winsorise or cap: replace values beyond a chosen threshold, documenting the rule.
• Remove: delete only when there is strong evidence of invalidity.

Outlier treatment should be performed using training data when it is part of a predictive pipeline. Blindly removing high-value customers, severe medical cases, or rare fraud events can create serious bias.

8. Transformation of Numerical Variables

Transformations alter representation while attempting to preserve useful information.

8.1 Scaling

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

where x̄ is the training-sample mean and s is the training-sample standard deviation. It is useful for distance-based algorithms, regularised linear models, principal component analysis, and neural networks. Tree-based models are generally less sensitive to scale.

Min–max scaling maps values to a chosen interval, commonly [0, 1]:

It is sensitive to future values outside the training range and should not be confused with standardisation.

Robust scaling uses the median and IQR and is less affected by extreme observations:

8.2 Skewness and transformations

A logarithmic transformation such as log(1 + x) can compress large positive values, particularly when zero is valid. It must not be applied to negative values without a carefully justified alternative. Transformation changes interpretation: a one-unit change on the transformed scale is not necessarily a one-unit change in the original measurement.

9. Encoding Categorical Variables

Many algorithms require numerical input. Common approaches include:

• One-hot encoding: creates one binary column per category. It is suitable for nominal categories but can produce many columns.
• Ordinal encoding: maps ordered categories to numbers, such as low = 1, medium = 2, high = 3. It should be used only when order is real and meaningful.
• Frequency encoding: replaces a category with its training frequency. It is compact but may lose semantic information.
• Target encoding: replaces a category using outcome information. It requires careful cross-validation and smoothing to prevent leakage.

For an unordered variable such as payment method, assigning cash = 1, card = 2, and transfer = 3 creates an artificial order. One-hot encoding is safer.

A binary variable can sometimes be represented as 0 and 1, provided the coding is documented and the two categories are genuinely distinct.

10. Feature Engineering

Feature engineering translates domain knowledge into variables that expose useful structure to an algorithm. Examples include:

Time features

From a timestamp, derive hour, day of week, month, quarter, holiday status, or elapsed time. Cyclic variables such as hour-of-day can be represented using sine and cosine so that adjacent values such as 23:00 and 00:00 remain close:

Here h is the hour from 0 to 23. These two features preserve periodicity better than treating hour as an ordinary linear number.

Ratios and rates

Business features may include revenue per customer, defects per unit, or clicks per impression. Ratios require meaningful denominators and safeguards for zero values. A rate should not be calculated when its exposure period is unknown.

Aggregations

For customer-level modelling, transaction data can be aggregated into purchase count, total spend, average order value, and days since last purchase. The aggregation window must end before the prediction date; otherwise, future information leaks into the predictors.

Text and interaction features

Text may be represented using word counts, character length, term-frequency measures, or embeddings. Interactions such as price × discount_rate may capture relationships not represented by either variable alone. Feature creation should be guided by domain understanding and validated empirically rather than generated indiscriminately.

11. Reproducible Pipelines in scikit-learn

A pipeline ensures that transformations are applied in the correct order and fitted only on the appropriate data.

from sklearn.compose import ColumnTransformer
from sklearn.impute import SimpleImputer
from sklearn.pipeline import Pipeline
from sklearn.preprocessing import OneHotEncoder, StandardScaler
from sklearn.linear_model import LogisticRegression

numeric_features = ["age", "income"]
categorical_features = ["region", "plan"]

numeric_pipeline = Pipeline([
    ("imputer", SimpleImputer(strategy="median", add_indicator=True)),
    ("scaler", StandardScaler())
])

categorical_pipeline = Pipeline([
    ("imputer", SimpleImputer(strategy="most_frequent")),
    ("encoder", OneHotEncoder(handle_unknown="ignore"))
])

preprocessor = ColumnTransformer([
    ("numeric", numeric_pipeline, numeric_features),
    ("categorical", categorical_pipeline, categorical_features)
])

model = Pipeline([
    ("preprocessor", preprocessor),
    ("classifier", LogisticRegression(max_iter=1000))
])

model.fit(X_train, y_train)
predictions = model.predict(X_test)

ColumnTransformer applies different procedures to different columns. handle_unknown="ignore" prevents an unseen category at prediction time from causing a failure; it does not mean that the category has been meaningfully understood. The fitted pipeline should be saved with compatible package versions and metadata.

A sound modelling sequence is:

1. Split data into training and test sets.
2. Fit the complete pipeline on training data only.
3. Select hyperparameters using cross-validation within the training set.
4. Evaluate once or sparingly on the untouched test set.
5. Monitor production data for drift and new quality failures.

12. Applied Case: Predicting Subscription Cancellation

Background

This illustrative case concerns a subscription company that wants to predict whether a customer will cancel within the next 30 days. The data contains customer age, plan, monthly charge, region, last-login date, support contacts, and cancellation status.

Problem

Initial profiling reveals that age contains impossible negative values, region labels vary in spelling, monthly charge is stored as text, some customers appear twice, and last-login dates are missing for inactive accounts. A small number of monthly charges are extremely large.

Analysis and approach

The team first defines a customer ID and checks whether repeated IDs represent multiple legitimate subscriptions. Exact duplicate rows are removed only after confirming they are repeated exports. Region values are trimmed and mapped to a controlled vocabulary. Monthly charge is converted to numeric, with conversion failures retained for review.

Negative ages are invalid and are set to missing after checking the source. Median age is estimated from the training partition, and an age-missing indicator is retained. Missing last-login values are not automatically replaced with the average date. Instead, the team creates days_since_login only where a login exists and adds never_logged_in, because absence has operational meaning.

Monthly charge is examined by plan and currency. A large value is found to be a legitimate annual enterprise charge incorrectly recorded in a monthly field. It is corrected using source documentation rather than capped. Other extreme values are retained because they represent high-value customers.

A pipeline imputes numerical variables, scales them, imputes categorical variables, and one-hot encodes them. Features such as tenure in days, support contacts per month, and recent login status are created using only information available before the prediction date.

Outcome

The final evaluation compares a baseline model with the engineered pipeline. The team examines not only predictive performance but also calibration, false-negative rates across regions, and operational usefulness. A model that identifies likely cancellations but disproportionately misses customers in one region requires further investigation before deployment.

Learning points

1. Data correction requires knowledge of the source process.
2. Missingness may carry business meaning.
3. Extreme values should be investigated, not automatically deleted.
4. Time-based feature construction must prevent future leakage.
5. Model performance and fairness must be evaluated together.

13. Governance, Ethics and Operational Practice

Cleaning decisions can change who is represented in a dataset. Removing records with missing values may disproportionately exclude people with limited access to services. Encoding “unknown” as an ordinary category may hide systematic collection problems. Target encoding can expose outcome information and create leakage. Sensitive attributes should not be used casually, but excluding them can also prevent fairness auditing.

Professional practice includes:

• retaining raw data separately from curated data;
• documenting definitions, mappings, units, exclusions, and imputation rules;
• applying access controls and minimising personal data;
• recording data lineage and transformation versions;
• testing for subgroup performance and disparate error rates;
• monitoring data drift after deployment;
• obtaining human review for consequential decisions.

Data protection obligations vary by jurisdiction, but principles such as purpose limitation, data minimisation, accuracy, security, and accountability are broadly relevant. Reproducibility is both a technical and governance requirement: another analyst should be able to determine what changed and why.

14. Common Failure Modes and Best Practices

Chapter Summary

Data cleaning, transformation, and feature engineering are analytical activities rather than mechanical preparation steps. Effective practice begins with understanding the data-generating process and profiling completeness, validity, consistency, accuracy, uniqueness, and timeliness. Missing values require consideration of their mechanism and meaning; duplicates require knowledge of the data model; inconsistent categories require controlled mappings; data types and units must be made explicit; and outliers should be investigated rather than automatically removed.

Numerical scaling, categorical encoding, date processing, aggregation, ratios, cyclic representations, and domain-specific features can improve model suitability and interpretability. The most important operational principle is to prevent leakage by fitting preprocessing rules only on training data and by ensuring that engineered features use information available at prediction time. Reproducible scikit-learn pipelines support this principle. Finally, cleaning decisions affect representation, fairness, privacy, and accountability, so technical quality must be evaluated alongside ethical and governance requirements.

Key Terms

Missing value: An observation for which no usable value is recorded.

MCAR: Missing completely at random; missingness is unrelated to observed and unobserved values.

MAR: Missing at random conditional on observed information.

MNAR: Missing not at random; missingness depends on unobserved or missing information.

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

Missingness indicator: A variable recording whether another value was missing.

Duplicate: A repeated record that may be accidental or legitimate.

Data type: The computational representation of a value, such as integer, string, category, or datetime.

Data validation: Testing values against structural, domain, or business rules.

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

Anomaly: An observation that violates expected behaviour or process rules.

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

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

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

Feature engineering: Creating informative variables from raw data.

Data leakage: Unintended use of information unavailable at prediction time.

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

Data drift: Change in the distribution or quality of data over time.

Target encoding: Encoding a category using information from the outcome variable.

Data lineage: A record of data sources and transformations through which data passes.

Review Questions

Short Answer Questions

1. Why should an analyst distinguish a valid extreme value from a data-entry error?
2. What is the difference between MCAR, MAR, and MNAR missingness?
3. Why is median imputation often preferred for skewed numerical variables?
4. When is ordinal encoding inappropriate?
5. What is the purpose of fitting preprocessing steps only on training data?

Long Answer Questions

1. Explain a complete workflow for diagnosing and treating missing values in a customer dataset. Include statistical and governance considerations.
2. Compare standardisation, min–max scaling, and robust scaling, including situations in which each may be appropriate.
3. Discuss how duplicate records, inconsistent categories, incorrect data types, and unit errors can affect a predictive model.
4. Explain how feature engineering from dates, transactions, and categorical variables can improve a model while creating leakage risks.
5. Describe how a scikit-learn ColumnTransformer and Pipeline support reproducible data preparation.

Analytical / Application Questions

1. A sales dataset contains 8% missing income values, 3% invalid negative quantities, and a highly skewed transaction-value field. Propose a treatment plan and justify each decision.
2. A churn model has strong overall accuracy but a much higher false-negative rate for one region. Explain how cleaning, feature construction, sampling, and governance could contribute to this result and how you would investigate it.
3. Design a leakage-resistant feature set for predicting whether a customer will cancel in the next 30 days using subscription, login, and support data. State the prediction cutoff and identify at least three features that should not be used.

Multiple Choice Questions with Answers

Question 1

Which action best prevents imputation leakage during model development?

A. Impute the complete dataset before splitting it

B. Fit the imputer on training data and apply it to validation and test data

C. Replace all missing values with zero

D. Delete every row containing a missing value

Correct Answer: B. Fit the imputer on training data and apply it to validation and test data

Question 2

What does the interquartile range equal?

A. The mean minus the median

B. The maximum minus the minimum

C. The third quartile minus the first quartile

D. The standard deviation divided by the mean

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

Question 3

Which encoding is generally most suitable for an unordered variable such as payment method?

A. One-hot encoding

B. Ordinal encoding based on alphabetical order

C. Ranking categories from 1 to 5

D. Replacing categories with target values without validation

Correct Answer: A. One-hot encoding

Question 4

Why can a missingness indicator be useful?

A. It guarantees that imputation is unbiased

B. It records whether absence itself may carry information

C. It converts all missing values into valid measurements

D. It removes the need to inspect missingness patterns

Correct Answer: B. It records whether absence itself may carry information

Question 5

A value is stored as text but represents a numeric amount. Which pandas function is designed for conversion?

A. pd.to_numeric

B. pd.get_dummies

C. pd.qcut

D. pd.concat

Correct Answer: A. pd.to_numeric

Question 6

Which statement about an outlier is correct?

A. Every outlier must be deleted

B. An outlier is always a data-entry error

C. An outlier should be investigated in its domain context

D. Outliers cannot occur in valid business data

Correct Answer: C. An outlier should be investigated in its domain context

Question 7

What is the main purpose of a scikit-learn ColumnTransformer?

A. To delete columns with missing values

B. To apply different transformations to selected column groups

C. To convert every column into a date

D. To replace a predictive model

Correct Answer: B. To apply different transformations to selected column groups

Question 8

Which feature is most likely to create leakage when predicting future churn?

A. Customer tenure measured at the prediction date

B. Number of support contacts before the prediction date

C. A cancellation event recorded after the prediction date

D. Whether the customer logged in during the previous week

Correct Answer: C. A cancellation event recorded after the prediction date

Question 9

For a positive, highly right-skewed variable containing zero values, which transformation is commonly considered?

A. log(1 + x)

B. 1 divided by x without checking zeros

C. Alphabetical sorting

D. One-hot encoding

Correct Answer: A. log(1 + x)

Question 10

Which practice improves auditability of a cleaning workflow?

A. Making undocumented manual edits

B. Overwriting raw data permanently

C. Version-controlling rules, mappings, and transformation code

D. Removing all unusual observations automatically

Correct Answer: C. Version-controlling rules, mappings, and transformation code