Data Cleaning, Integration and Feature Preparation

Introduction

Data science projects rarely fail because an algorithm cannot be executed. They more often fail because the data used by the algorithm is incomplete, inconsistent, duplicated, poorly integrated, or expressed in unsuitable forms. Data cleaning, integration and feature preparation therefore form the operational foundation of reliable analysis and visualization.

Data preparation is not a single mechanical step. It is an evidence-based process in which the analyst investigates how data was generated, determines whether observed values are plausible, documents decisions, and prepares variables without introducing avoidable bias. A cleaned dataset is not necessarily one in which every unusual value has been deleted. Rather, it is a dataset whose limitations, transformations and quality controls are understood and defensible.

A typical preparation workflow is:

Figure: An iterative workflow for data preparation.

Learning Objectives

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

1. Diagnose missing values, outliers, duplicates and inconsistent representations.
2. Select cleaning treatments that are appropriate to the data-generating process.
3. Integrate relational, transactional and externally sourced datasets safely.
4. Transform numerical and categorical variables for analysis and machine learning.
5. Explain data leakage, reproducibility, fairness and governance risks.
6. Establish quality checks and document a defensible preparation pipeline.

Data Quality as a Multidimensional Concept

Data quality is fitness for a defined use, not an absolute property. A value may be adequate for a monthly management report but inadequate for clinical decision-making. Common dimensions include:

A useful completeness measure for a field is:

The measure assumes that a missing value can be identified reliably and that every record has equal relevance. It does not measure accuracy or validity.

Profiling Before Modification

Data profiling precedes cleaning. Analysts inspect data types, row and column counts, distinct-value frequencies, minimum and maximum values, distributions, missingness patterns, duplicate keys and relationships between fields. Profiling should be performed separately for each source and again after integration.

A data dictionary records a field's name, business meaning, type, unit, permitted values, source system, sensitivity classification and missing-value policy. For example, income must distinguish annual income in rupees from monthly income in dollars; a numeric type alone does not provide that meaning.

Missing Values

Missingness can be represented by null values, blank strings, special codes such as −99, or impossible placeholders such as 1900-01-01. These representations must be standardized before analysis. A blank may mean “not collected,” “not applicable,” “refused,” or “unknown”; these meanings should not automatically be collapsed.

Three conceptual mechanisms are commonly used:

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

The mechanisms are assumptions about the data-generating process, not labels that can be proved merely by looking at a table. For example, income may be missing more frequently for high-income respondents because they are reluctant to disclose it; this suggests a potentially MNAR process.

Treatment options include deletion, constant-value replacement, statistical imputation and model-based imputation. Deleting rows is reasonable when the affected proportion is small, the missingness is plausibly random, and the remaining sample remains representative. Mean imputation is simple but reduces variation and can distort relationships. Median imputation is more resistant to skew and outliers. For a variable X, the median is the middle ordered value, or the average of the two middle values when the sample size is even.

Categorical missingness may be represented by an explicit category such as “Unknown” when absence itself carries information. Multiple imputation can better reflect uncertainty by creating several plausible completed datasets, analysing each, and combining results; it is preferable for inferential work when assumptions and implementation are appropriate.

All imputation statistics must be calculated using the training data only in predictive modelling. Otherwise, information from validation or test records leaks into the model-development process.

Outliers and Anomalies

An outlier is an observation unusually distant from the main distribution. It may be a data-entry error, a measurement failure, a legitimate rare event, or evidence of a separate population. Outlier treatment must therefore begin with investigation, not automatic deletion.

The interquartile range is:

where Q₁ is the first quartile and Q₃ is the third quartile. A conventional box-plot flag identifies observations below Q₁ − 1.5 × IQR or above Q₃ + 1.5 × IQR. This is a screening rule, not proof that a value is erroneous. It is most useful for distributions where quartiles provide a meaningful summary.

For approximately symmetric data, a standardized score may be used:

where xᵢ is an observation, μ is the population or reference mean, and σ is the corresponding standard deviation. In practice, sample estimates are often substituted. The rule is sensitive to skew and to the outliers it is intended to detect, so robust measures such as the median and median absolute deviation may be preferable.

Possible actions are correction against the source, retaining with a flag, transformation, winsorization, use of robust statistics, or removal with a documented reason. In fraud detection, extreme transactions may be the most important records and should not be discarded merely for being unusual.

Duplicates

Duplicates may be exact row copies or semantic duplicates: two records describing the same customer with different spellings or addresses. Exact duplicates can be detected by hashing complete rows. Semantic deduplication requires a stable identifier, composite keys, or record linkage based on similarities such as name, date of birth and address.

A deduplication rule must define which record is retained. “Keep the latest” is only defensible when timestamps are reliable and later records are more authoritative. A survivorship decision should preserve the source identifiers and record the discarded or merged rows.

Inconsistencies and Invalid Values

Common inconsistencies include Male, M, and male; kilograms mixed with pounds; dates in day-month and month-day formats; different time zones; and currency symbols embedded in numeric fields. Cleaning requires explicit standards rather than arbitrary replacement.

Useful controls include format validation, range checks, controlled vocabularies, unit conversion, timezone normalization, referential-integrity checks and reconciliation rules. For example, if an invoice total is expected to equal the sum of line items, tolerance for rounding should be defined rather than treating every tiny difference as an error.

Data Integration

Integration combines data from multiple sources into a coherent analytical structure. Sources may include relational databases, spreadsheets, APIs, logs, survey files and cloud systems. Integration introduces risks because schemas, identifiers, granularity, units, time periods and definitions may differ.

Keys, Joins and Grain

A primary key uniquely identifies a row in a table. A foreign key refers to a key in another table. A one-to-many join between customers and orders produces multiple rows per customer; it must not be mistaken for a customer-level dataset. The grain is the real-world unit represented by each row, such as one customer, one order, or one order line.

Common joins are:

• Inner join: retains matching records from both sources.
• Left join: retains every record from the left source and matching records from the right.
• Full outer join: retains records from either source, including unmatched records.

Before joining, analysts should test key uniqueness, missing keys, data types, whitespace, case, time zones and code systems. A many-to-many join can multiply rows unexpectedly and produce inflated totals. Aggregate to a compatible grain or resolve the relationship through a bridge table.

Schema and Entity Resolution

Schema matching maps different field names and structures to common concepts. Entity resolution determines whether records from different sources refer to the same real-world entity. Exact matching is appropriate when identifiers are stable; probabilistic or rule-based matching may be required when identifiers are absent. Potential matches should be reviewed at a threshold that balances false matches and missed matches.

A reproducible integration pipeline should preserve raw data, create standardized staging tables, apply documented mappings, and retain lineage showing source-to-output transformations. Raw data should be immutable so that errors can be corrected by rerunning the pipeline rather than editing history.

Feature Preparation

A feature is an input variable used for analysis or prediction. Feature preparation should be guided by the measurement scale, distribution, algorithm requirements and interpretation goals.

Numerical Transformations

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

This requires x_max ≠ x_min and is sensitive to future extreme values.

• Standardization:

It produces a variable measured in standard-deviation units when μ and σ are estimated from the training set. It does not guarantee a normal distribution.

• Log transformation: x′ = log(x) can reduce right skew, but x must be positive. For non-negative data containing zero, log(1 + x) is often used, with interpretation adjusted accordingly.

• Binning: converts continuous values into intervals. It may improve interpretability but loses information and can create arbitrary boundaries.

Scaling is especially relevant to distance-based, gradient-based and regularized algorithms. Tree-based models generally do not require scaling, although consistent preprocessing remains valuable for pipelines.

Categorical Encoding

Nominal categories have no inherent order; one-hot encoding creates indicator variables. For k categories, using all k indicators with an intercept can create perfect linear dependence in ordinary least squares. Dropping one reference category avoids this issue, although regularized models and some software can handle alternative parameterizations.

Ordinal encoding is appropriate only when category order is meaningful and the numerical spacing is defensible. Label-encoding nominal categories as 1, 2 and 3 can falsely imply order and distance. High-cardinality variables may require frequency encoding, carefully designed target encoding, hashing, or domain-based grouping. Target encoding must be fitted within training folds to prevent target leakage.

Dates, Text and Derived Features

Dates can yield year, month, weekday, elapsed time and season, but derived features must respect the prediction timestamp. Text can be represented using token counts, term frequency–inverse document frequency, embeddings or domain dictionaries. Features should have a documented relationship to the outcome and should not encode information unavailable at decision time.

Feature selection may remove identifiers, near-zero-variance fields, redundant variables, leakage-prone fields and variables prohibited by policy. Correlation does not by itself establish redundancy, causation or fairness.

Worked Example: A Small Customer Dataset

Suppose five records contain annual income in thousands of currency units:

The exact duplicate for D is removed after checking the source identifier. Status values are standardized to Active. Income completeness before treatment is 4 ÷ 5 = 80%; after deduplication it is 3 ÷ 4 = 75%, because the duplicate had falsely increased the denominator and non-missing count.

The observed incomes are 40, 42 and 43. Their median is 42, so median imputation would assign 42 to C if the missingness policy supports it. The result must be marked as imputed, because an imputed value is not equivalent to an observed measurement. A final quality report should state the duplicate rule, the imputation method, the original and final completeness, and the limitations of the small sample.

Applied Case: Integrating Retail Sales and Customer Data

Background. A fictional retailer has a sales table at order-line level and a customer table at customer level. Management wants a customer-level dashboard showing revenue, order frequency and region.

Problem. The sales source contains customer codes with trailing spaces; the customer source contains uppercase codes. Some customers appear twice after a system migration. Sales include timestamps in local time, while the reporting period is defined in UTC. A direct join produces duplicated sales for migrated customers.

Analysis. The team first documents grain: one row per order line in sales and one row per customer in the customer master. It profiles key uniqueness and finds duplicate customer codes in the master. It confirms the authoritative record using the latest verified timestamp, while retaining the old identifiers for lineage. Codes are trimmed and case-normalized. Timestamps are converted to UTC before filtering the reporting period.

Approach and solution. The team creates immutable raw tables and standardized staging tables. It validates that quantities are positive unless a return flag is present, checks that line totals reconcile within a defined rounding tolerance, and resolves customer identities through a migration crosswalk. Sales are aggregated to customer grain before joining to the customer table. Missing region is retained as Unknown and reported separately rather than silently assigned. Automated tests check row counts, unique customer keys, unmatched sales, duplicate joins and total revenue reconciliation.

Outcome. The dashboard no longer double-counts migrated customers, and every metric has a documented grain and time basis. Analysts can reproduce the result from raw data and inspect exceptions.

Learning points. Integration quality depends on keys, grain, time standards and lineage. A technically successful join can still produce analytically false results if its cardinality is not understood.

Data-Quality Controls and Reproducibility

Quality controls should be both preventive and detective. Preventive controls include input schemas, permitted-value lists, database constraints and clear ownership. Detective controls include null-rate monitoring, distribution shifts, reconciliation totals, duplicate detection, referential-integrity tests and exception reports.

A useful pipeline separates extraction, validation, transformation and publication. Each run should record source versions, code version, parameters, execution time, row counts, rejected records and warnings. Tests should distinguish failures that block publication from warnings that require review. Version control, peer review and automated testing improve reproducibility.

Data drift occurs when source distributions, category frequencies or relationships change over time. A rule that was valid last year may become invalid after a business-process change. Monitoring should therefore examine both technical quality and statistical stability.

Ethical, Privacy and Governance Considerations

Cleaning decisions can change who is represented. Removing records with missing values may disproportionately exclude low-income, rural or minority groups. Imputation can conceal uncertainty. Outlier removal may erase legitimate cases involving vulnerable populations. Analysts should compare missingness and treatment rates across relevant groups and document potential impacts.

Personally identifiable information should be minimized, access-controlled and protected through appropriate retention and security policies. Pseudonymization does not guarantee anonymity, particularly when datasets can be linked. Sensitive attributes may be needed for fairness auditing but require careful governance. Data preparation must comply with applicable law, institutional policy, consent conditions and purpose limitations.

AI-assisted cleaning can suggest mappings, detect anomalies or generate code, but it can also hallucinate business rules, expose confidential records and reproduce bias. Human review, restricted environments, test cases and provenance are essential. No automated recommendation should replace domain validation.

Challenges and Limitations

No cleaning method can recover information that was never collected. Different reasonable treatments may produce different analytical conclusions. Rules based only on statistical thresholds can remove valid rare events, while rules based only on domain judgment can be inconsistent. Entity resolution remains uncertain when identifiers are weak. Scaling and encoding can improve model performance while reducing interpretability. Finally, quality is contextual: a dataset may be fit for exploratory visualization but unfit for high-stakes automated decisions.

Best-Practice Checklist

1. Define the analytical purpose, unit of analysis and prediction time.
2. Preserve immutable raw data and maintain a data dictionary.
3. Profile each source before changing values.
4. Distinguish missing, not applicable, unknown and refused responses.
5. Investigate outliers and duplicates before removing them.
6. Validate keys, join cardinality, units, dates and grain.
7. Fit imputers, scalers and encoders on training data only.
8. Preserve flags for imputed, corrected and exceptional records.
9. Test totals, ranges, uniqueness, referential integrity and drift.
10. Document assumptions, rejected records, lineage and responsible owners.

Chapter Summary

Data cleaning, integration and feature preparation convert raw observations into defensible analytical inputs. Quality must be assessed across completeness, validity, accuracy, consistency, uniqueness, timeliness and integrity. Missing values require interpretation of their mechanism; outliers require investigation; duplicates require identity and survivorship rules; and inconsistencies require explicit standards. Integration depends on keys, schema mapping, entity resolution, time alignment and grain. Feature preparation includes scaling, transformations, categorical encoding, date and text derivation, and leakage prevention. Reproducible pipelines preserve raw data, record lineage and apply automated quality controls. Ethical practice requires attention to representation, privacy, fairness, uncertainty and human oversight.

Key Terms

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

Data dictionary: Documentation of field meanings, types, units and rules.

Missing value: An absent, unknown, unavailable or inapplicable observation.

MCAR: Missing completely at random.

MAR: Missing at random conditional on observed information.

MNAR: Missing not at random.

Outlier: An unusually distant observation requiring investigation.

IQR: Difference between the third and first quartiles.

Duplicate: A repeated or semantically repeated record.

Primary key: Field or field combination that uniquely identifies a row.

Foreign key: Field referencing an identifier in another table.

Grain: Real-world unit represented by one row.

Entity resolution: Linking records that refer to the same entity.

Data lineage: Record of a value's source and transformations.

Feature: Input variable used in analysis or prediction.

Standardization: Transformation using a reference mean and standard deviation.

One-hot encoding: Representation of categories with indicator variables.

Data leakage: Use of information unavailable at prediction time or improperly shared across evaluation stages.

Referential integrity: Validity of relationships between related tables.

Data drift: Change in data distributions or relationships over time.

Review Questions

Short Answer Questions

1. Why should data profiling precede modification?
2. Distinguish MCAR, MAR and MNAR missingness.
3. What is the purpose of the IQR outlier rule?
4. Define the grain of a dataset and explain why it matters in joins.
5. Why must preprocessing statistics be fitted on training data only?

Long Answer Questions

1. Explain major data-quality dimensions and propose a control for each.
2. Compare deletion, median imputation, explicit missing categories and multiple imputation.
3. Explain how primary keys, foreign keys and join cardinality affect data integration.
4. Compare min–max scaling, standardization, log transformation and binning.
5. Discuss ethical and privacy risks associated with cleaning and feature preparation.

Analytical / Application Questions

1. A sales table has one row per order line, while a customer table has one row per customer. Explain how a careless join could inflate revenue and design checks to prevent it.
2. A variable has many missing values in one demographic group but few in another. Propose an investigation and treatment plan that addresses statistical and fairness concerns.
3. Design a reproducible preparation pipeline for a predictive model, specifying where validation, imputation, encoding, scaling, lineage and monitoring occur.

Multiple Choice Questions with Answers

Question 1

Which quality dimension asks whether values obey permitted formats or ranges?

A. Validity

B. Timeliness

C. Uniqueness

D. Accuracy

Correct Answer: A. Validity

Question 2

The conventional upper IQR screening boundary is:

A. Q₃ + 1.5 × IQR

B. Q₁ + 1.5 × IQR

C. Q₃ − 1.5 × IQR

D. Q₁ − 1.5 × IQR

Correct Answer: A. Q₃ + 1.5 × IQR

Question 3

Which join retains every record from the left table and matching records from the right table?

A. Inner join

B. Left join

C. Full outer join

D. Many-to-many join

Correct Answer: B. Left join

Question 4

Why can label-encoding nominal categories as 1, 2 and 3 be problematic?

A. It always creates missing values

B. It implies an order and spacing that may not exist

C. It prevents database joins

D. It converts numbers into text

Correct Answer: B. It implies an order and spacing that may not exist

Question 5

For predictive modelling, an imputation median should normally be calculated from:

A. The complete dataset including the test set

B. The test set only

C. The training data only

D. An unrelated external dataset

Correct Answer: C. The training data only

Question 6

If incomes are 40, 42 and 43, what is their median?

A. 40

B. 41

C. 42

D. 43

Correct Answer: C. 42

Question 7

Which concept describes the real-world unit represented by each row?

A. Grain

B. Schema

C. Encoding

D. Drift

Correct Answer: A. Grain

Question 8

Which transformation is most directly suited to reducing right skew for strictly positive values?

A. One-hot encoding

B. Log transformation

C. Deduplication

D. Case normalization

Correct Answer: B. Log transformation

Question 9

What is a major risk of an uncontrolled many-to-many join?

A. All null values become zero

B. Rows and aggregate totals may be multiplied

C. Dates automatically become UTC

D. Categorical variables become ordinal

Correct Answer: B. Rows and aggregate totals may be multiplied

Question 10

Which statement about an outlier is correct?

A. It is always a data-entry error

B. It should always be deleted

C. It may be a valid rare event or an error and requires investigation

D. It cannot occur in a normal distribution

Correct Answer: C. It may be a valid rare event or an error and requires investigation