Data Cleaning, Integration and Preparation

Introduction

Data science rarely begins with a clean analytical table. Real-world data are collected by different people, systems and instruments, often for different purposes. A customer may appear under several spellings, dates may use incompatible conventions, measurements may use different units, and important values may be absent. If these problems are ignored, statistical summaries become misleading, machine-learning models learn artefacts, and operational decisions may be unfair or unsafe.

Data preparation is therefore not a minor preliminary task. It is a disciplined process of converting raw observations into data that are accurate enough, consistently represented, analytically appropriate and reproducible. The process includes profiling, cleaning, integrating, transforming, encoding and validating data. It also requires judgement: a missing value may represent non-response, inapplicability or a system failure; an outlier may be an error or the most important observation in the dataset.

This chapter develops practical and conceptual foundations for preparing structured data for analysis and modelling. It emphasizes traceability, prevention of data leakage and preservation of the meaning of the original information.

Learning objectives

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

• profile a dataset and identify quality problems;
• detect and handle missing values, duplicates, invalid entries and outliers;
• standardize inconsistent formats, units and categories;
• integrate datasets using appropriate keys and join strategies;
• transform variables and encode categorical features;
• explain when and why scaling is required;
• construct a reproducible preparation pipeline;
• identify data leakage, bias, privacy and governance risks; and
• evaluate whether prepared data remain fit for purpose.

1. Data quality as fitness for purpose

Data quality is multidimensional. A dataset is not simply “clean” or “dirty”; its suitability depends on the intended use. Common dimensions include:

A cleaning decision should follow the analytical purpose. For example, replacing missing income with a median may be acceptable for a descriptive dashboard but inappropriate for credit-risk modelling if missingness itself carries information. Preparation must therefore be documented as a set of justified decisions rather than treated as cosmetic editing.

A useful distinction is between an observation, a variable and a record identifier. An observation is a measured value; a variable is a property such as age or income; and an identifier distinguishes an entity or event. Confusing identifiers with predictive features can produce spurious results—for example, a customer ID may appear numerically related to an outcome although it has no substantive meaning.

2. Profiling before modification

Profiling is the systematic inspection of data before cleaning. It should include row and column counts, data types, distinct-value counts, frequency distributions, minimum and maximum values, missingness, duplicate patterns and relationships between tables.

For a variable x with n observations, the missing-value rate is:

where m is the number of missing observations and n is the total number of observations. The result is a proportion; multiplying by 100 gives a percentage. The denominator must be stated because missingness may be calculated across all records or only within a relevant subgroup.

Profiling should examine missingness by time, location, customer group and outcome. A 10% overall missing rate may conceal 60% missingness for one protected group. Frequency tables are especially useful for categorical variables because they reveal spelling variants, unexpected codes and rare categories.

A data dictionary should record the variable name, business meaning, type, unit, allowed range, source, sensitivity classification and transformation history. For example, “temperature” is incomplete without specifying whether it is measured in Celsius or Fahrenheit.

3. Missing values

3.1 Types and causes

Missing values have different meanings:

• MCAR (Missing Completely At Random): missingness is unrelated to observed and unobserved values.
• MAR (Missing At Random): missingness is explainable by observed variables, such as income being missing more often for younger respondents.
• MNAR (Missing Not At Random): missingness depends on the unobserved value itself, such as high-income individuals being less willing to report income.

These categories describe assumptions about the data-generating process, not merely the appearance of blank cells. They cannot usually be proven from the observed dataset alone.

Missingness may also be structural. “Number of dependants” may be inapplicable to a business account, while “test result” may be absent because a test was not ordered. Coding both situations as zero would create a false measurement.

3.2 Detection and treatment

First identify representations such as blank strings, “N/A”, “unknown”, “-999” and “not collected”. They should not automatically be treated as valid numerical values. Then determine whether the variable is required, whether the missingness is informative and whether the analysis can tolerate incomplete rows.

Common strategies are:

1. Deletion. Remove rows or variables when missingness is small, plausibly random and removal does not distort the target population. Complete-case analysis can reduce statistical power and introduce bias.
2. Simple imputation. Replace numerical values with a mean or median and categorical values with a mode or explicit “Unknown” category. The median is often more robust to skew and outliers than the mean.
3. Group-wise imputation. Use a median within a meaningful group, such as product category, provided the group has adequate observations.
4. Model-based or multiple imputation. Estimate missing values using relationships among variables. Multiple imputation represents uncertainty by creating several plausible datasets rather than pretending the estimate is known exactly.
5. Missingness indicators. Add a binary variable M, where M = 1 if the original value was missing and M = 0 otherwise. This can preserve predictive information, but it must be interpreted carefully.

For a numerical variable, the arithmetic mean is:

where xᵢ is an observed value and n is the number of observed values included in the calculation. It is appropriate when values are quantitative and the mean is not unduly distorted by extreme observations. A median is the middle ordered value, or the average of the two middle values when the number of observations is even.

Imputation must be fitted only on the training data in predictive modelling. If the full dataset is used to calculate the median before splitting, information from the test set enters the training process. This is a form of data leakage.

4. Duplicates and entity resolution

Exact duplicates are records identical across selected fields. They may result from repeated file uploads or transaction retries. Removing them is safe only when each row is expected to represent one occurrence.

Near-duplicates are harder: “Amina Khan”, “A. Khan” and “Amina Khan” may refer to one person or several people. Entity resolution uses identifiers, normalized text, addresses, dates and domain rules to estimate whether records refer to the same entity. Fuzzy matching can assist discovery but should not automatically merge records in high-risk contexts.

A robust procedure is to:

1. define the unit of observation;
2. identify a primary key or composite key;
3. normalize comparison fields, such as case and whitespace;
4. generate candidate matches using blocking rules;
5. review similarity scores and business constraints;
6. retain a merge or rejection audit trail.

When duplicate records contain conflicting values, use source reliability, timestamps and business rules rather than arbitrary selection. In financial or health data, unresolved conflicts should be escalated rather than silently overwritten.

5. Errors, invalid values and inconsistent formats

Validation rules should be explicit. Examples include:

• age must be within a plausible domain range;
• quantity must be non-negative;
• an end date cannot precede a start date;
• a country code must belong to an approved reference list;
• a percentage should normally lie between 0 and 100;
• a postal code must conform to the relevant country format.

A value can be syntactically valid but semantically wrong. “31/12/2024” is a valid date under day-month-year interpretation but may be misread under month-day-year interpretation. Dates should be parsed with an explicit convention and stored in an unambiguous standard representation such as ISO 8601.

Units must also be standardized. If one table records distance in kilometres and another in miles, the conversion must be documented. For distance d, the relationship is approximately:

Unit conversion is valid only when the quantity being measured is conceptually equivalent. A monetary amount also requires currency and date, because nominal values from different currencies or years are not directly comparable.

Text normalization may include trimming whitespace, consistent case, Unicode normalization and controlled replacement of known variants. It should not destroy meaningful distinctions, such as capitalization in product codes or accents in legal names.

6. Outliers

An outlier is an observation unusually distant from the general pattern. It may indicate measurement error, data entry error, a rare but genuine event or a different population. Outliers must not be removed merely because they are inconvenient.

The z-score is commonly defined as:

where xᵢ is an observation, x̄ is the sample mean and s is the sample standard deviation. It expresses distance in standard-deviation units. The rule that |zᵢ| > 3 indicates an outlier is a heuristic, not a universal law; it is most informative for roughly symmetric distributions without severe contamination.

The interquartile range is:

where Q₁ and Q₃ are the first and third quartiles. The conventional Tukey fences are:

These fences identify potential outliers in a box-plot sense. They do not prove that a value is erroneous and may flag many observations in a skewed distribution.

Responses include correcting a verified error, retaining a genuine extreme value, winsorizing extreme values, applying a transformation, using a robust model or analysing the subgroup separately. Every intervention should preserve the original value in an immutable raw layer.

7. Integrating datasets

Integration combines data from multiple sources. Before joining, assess the grain of each table. A customer table may contain one row per customer, whereas an orders table contains many rows per customer. Joining them requires a one-to-many relationship and can multiply rows if the relationship is misunderstood.

Important join types include:

• Inner join: retains records with matching keys in both tables.
• Left join: retains every record from the left table and matching records from the right.
• Right join: the converse of a left join.
• Full outer join: retains unmatched records from both tables.

Keys should be stable, unique where expected and consistently typed. A join on names alone is risky. Referential-integrity checks should count unmatched keys before and after integration. Analysts should also check for row multiplication: if a supposed one-to-one join increases the row count unexpectedly, the key may not be unique.

Integration can be vertical or horizontal. Vertical integration appends records with compatible columns; horizontal integration adds variables to existing records. Schema mapping is required when column names, coding systems or definitions differ. Reference data, such as an official country-code table, can convert local codes into standardized codes.

8. Transforming variables

Transformation changes representation while aiming to preserve relevant information.

8.1 Numerical transformations

Logarithmic transformation can reduce right skew and compress large values:

It requires x > 0. For non-negative data containing zero, a common alternative is log(1 + x), but the choice changes interpretation and should be documented. Transformations are not automatically beneficial; they should be evaluated using distributional diagnostics and model assumptions.

Ratios and rates can be useful, but their denominators must be meaningful and non-zero. A per-customer rate may be misleading for groups with very small exposure. Date variables can be decomposed into year, month, weekday, elapsed time or season. Cyclical variables such as hour of day may be represented using sine and cosine so that 23:00 and 00:00 remain close in feature space.

8.2 Categorical encoding

Categorical variables require representation suitable for the algorithm:

• One-hot encoding: creates one binary feature per category, often dropping one category for a linear model with an intercept to avoid perfect multicollinearity.
• Ordinal encoding: assigns ordered integers and is appropriate only when categories possess a genuine order.
• Frequency encoding: replaces a category with its occurrence frequency, but can obscure meaning and must be computed without leakage.
• Target encoding: uses outcome information and therefore requires careful cross-validation; otherwise it can severely overstate performance.

Unknown categories appearing at deployment should be handled explicitly, for example by an “unknown” bucket. High-cardinality identifiers should not automatically be one-hot encoded because this can create sparse, unstable features.

8.3 Scaling numerical features

Standardization converts a variable to mean zero and standard deviation one:

where μ and σ are the mean and standard deviation estimated from the training data. The transformed quantity is dimensionless. Standardization is important for distance-based methods, support vector machines, principal component analysis and gradient-based optimization. It is usually unnecessary for tree-based models, although consistent preprocessing may still simplify a pipeline.

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

where x_min and x_max are training-set minimum and maximum values, and x_max ≠ x_min. It is sensitive to extreme values. Robust scaling based on the median and IQR is often preferable when outliers are substantial.

9. Reproducible preparation pipelines

A reproducible pipeline expresses preparation as ordered, executable steps: schema validation, cleaning, splitting, fitting transformations, applying transformations and exporting the resulting dataset. The order matters. For example, a train–test split should occur before fitting an imputer, scaler, feature selector or target encoder.

A typical workflow is:

1. preserve the raw source files;
2. assign a version and record acquisition time;
3. profile and validate the schema;
4. split data into training, validation and test sets where modelling is intended;
5. fit preprocessing parameters on training data only;
6. apply identical transformations to validation, test and future data;
7. validate outputs and monitor drift;
8. record code, configuration, dependencies and data versions.

Pipeline objects in common data-science frameworks can combine imputers, encoders and scalers so that the same logic is used during training and deployment. Reproducibility also requires deterministic random seeds where appropriate, although a seed does not remove all sources of variation.

Data leakage occurs when information unavailable at prediction time influences a feature or preprocessing decision. Examples include using a post-outcome variable, calculating an imputation statistic from the test set or selecting features using the entire labelled dataset before cross-validation. Leakage produces optimistic evaluation and is a methodological error, not merely a software bug.

10. Illustrative case: preparing retail order data

Background

A fictional online retailer combines customer, order and web-session data to predict whether an order will be returned. The customer table contains one row per customer, the order table one row per order and the session table multiple rows per customer per day.

Problem

The raw data contain duplicate customer IDs, dates in two formats, missing product weights, inconsistent country names, negative quantities caused by returns and an order value recorded in both euros and pounds. A preliminary model reports unusually high accuracy.

Analysis

The team first defines the prediction time as the moment an order is placed. It discovers that “return processed date” was included as a feature, creating direct leakage. It also finds that joining sessions to orders on customer ID alone duplicates each order for every session.

Approach and solution

The team creates an immutable raw layer and a validated staging layer. Customer records are resolved using a verified customer key and a documented merge table. Dates are parsed using source-specific rules and stored in ISO format. Currency is converted using the transaction-date exchange-rate table, with the original amount and currency retained.

Negative quantities are not deleted; they are classified as return or adjustment transactions according to the order-status field. Missing product weights are imputed using the training-set median within product category, with a missingness indicator. Country labels are mapped to official codes through a reference table.

Session data are aggregated to features available before order placement, such as number of sessions and average session duration during the previous 30 days. The aggregation prevents row multiplication and respects the prediction boundary. One-hot encoding is applied to low-cardinality categories, and numerical features are standardized within a model pipeline. The test set is kept untouched until final evaluation.

Outcome

After removing leakage and correcting the join grain, accuracy decreases but performance on a time-separated holdout becomes more realistic and stable. The retailer gains an auditable dataset and can explain how each feature was produced.

Learning points

The case demonstrates that preparation is inseparable from problem definition. Correct keys, temporal reasoning, source documentation and leakage controls are as important as imputation or scaling. A model trained on incorrectly integrated data may appear sophisticated while solving the wrong problem.

11. Governance, ethics and contemporary practice

Preparation can affect people unequally. Deleting records with missing values may disproportionately exclude disadvantaged groups. Imputation can conceal structural inequality, while target encoding may expose sensitive outcome information. Protected attributes should not be removed automatically: retaining them may be necessary for fairness auditing, even if they are excluded from prediction.

Personal data should be minimized, access-controlled and retained only as long as justified. Identifiers should be separated from analytical features, and pseudonymization should not be confused with anonymization. Data provenance, consent, lawful purpose and retention requirements must be considered within the applicable legal and institutional framework.

Modern data platforms increasingly use schema contracts, automated data-quality tests, lineage systems and monitored feature stores. Artificial intelligence can assist anomaly detection, entity matching and documentation, but automated corrections require human review, especially in health, finance, employment and public-sector applications. A generated transformation is not trustworthy merely because it is technically plausible.

Data drift occurs when the distribution or meaning of incoming data changes. A pipeline should therefore monitor missingness rates, category frequencies, ranges, join failure rates and feature distributions. Preparation is not finished at model deployment; it becomes an operational control process.

Best-practice checklist

• Define the unit of observation and prediction time.
• Preserve raw data and maintain an audit trail.
• Profile before modifying.
• Distinguish missing, zero, not applicable and unknown.
• Validate keys and table grain before joining.
• Investigate outliers rather than deleting them automatically.
• Fit learned transformations only on training data.
• Make category and unit mappings explicit.
• Test for leakage and unexpected row multiplication.
• Version code, data, parameters and reference tables.
• Monitor quality and drift after deployment.
• Review fairness, privacy and regulatory implications.

Chapter Summary

Data cleaning, integration and preparation convert heterogeneous raw data into an analytically defensible representation. Quality must be judged against purpose and assessed through accuracy, completeness, consistency, validity, uniqueness and timeliness. Profiling identifies missing values, duplicates, invalid entries, inconsistent formats and unusual observations before interventions are made.

Missingness may be random, conditionally explainable or informative; treatment can involve deletion, imputation, indicators or explicit unknown categories. Duplicates require a clear unit of observation and, for near-duplicates, careful entity resolution. Outlier methods such as z-scores and Tukey fences are diagnostic heuristics rather than automatic deletion rules. Integration depends on correct table grain, keys, join types and referential-integrity checks.

Transformations include unit conversion, logarithms, date features, categorical encoding and numerical scaling. Learned transformations must be fitted on training data only to prevent leakage. Reproducible pipelines combine validation and transformations, preserve provenance and support consistent deployment. Finally, preparation has ethical and governance consequences: it can change representation, fairness, privacy and model validity. Professional data scientists treat preparation as an auditable, continuing part of the analytical lifecycle.

Key Terms

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

Data quality: Fitness of data for a specified analytical or operational purpose.

Missing completely at random (MCAR): Missingness unrelated to observed or unobserved values.

Missing at random (MAR): Missingness explainable by observed information.

Missing not at random (MNAR): Missingness related to unobserved values or the missing value itself.

Imputation: Estimation and replacement of missing values.

Duplicate: A repeated record or entity representation that is not intended by the data design.

Entity resolution: Determining which records refer to the same real-world entity.

Data validation: Checking values against structural, syntactic and semantic rules.

Outlier: An observation unusually distant from the general pattern.

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

Join: An operation that combines tables using matching keys.

Data grain: The real-world unit represented by one row in a table.

One-hot encoding: Representation of categories as binary indicator variables.

Ordinal encoding: Representation of ordered categories by numerical codes.

Standardization: Transformation using training mean and standard deviation.

Data leakage: Use of information unavailable at prediction time in model development.

Pipeline: Ordered, reproducible sequence of preparation and modelling operations.

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

Data provenance: Record of a datum’s source, transformations and history.

Review Questions

Short Answer Questions

1. Why should data be profiled before cleaning decisions are made?
2. Distinguish MCAR, MAR and MNAR missingness.
3. What is the difference between an inner join and a left join?
4. Why is an identifier not automatically a useful predictive feature?
5. Why must scaling parameters be fitted only on training data?

Long Answer Questions

1. Explain a systematic procedure for detecting and treating missing values, including the limitations of deletion and simple imputation.
2. Discuss how incorrect table grain and non-unique keys can produce erroneous results during data integration.
3. Compare one-hot, ordinal, frequency and target encoding, including situations in which each may be appropriate.
4. Explain the purpose, assumptions and limitations of z-score and IQR-based outlier detection.
5. Describe the components of a reproducible data-preparation pipeline and explain how it prevents leakage.

Analytical / Application Questions

1. A customer table has one row per customer and a session table has several rows per customer per day. Design a preparation strategy for predicting next-week churn without multiplying customer records or using future information.
2. A survey variable has 25% missing income values, with missingness much higher among younger respondents. Evaluate possible treatments and explain what additional analyses are needed before choosing one.
3. A model’s validation accuracy falls sharply after a new data source is integrated. Develop a diagnostic plan addressing join keys, schema changes, category mapping, leakage, missingness and distribution drift.

Multiple Choice Questions with Answers

Question 1

Which action best prevents leakage when imputing a numerical feature for a predictive model?

A. Calculate the median using the complete dataset before splitting

B. Calculate the median using the training data and apply it to later sets

C. Calculate a separate median for each test-set class

D. Replace all missing values with zero

Correct Answer: B. Calculate the median using the training data and apply it to later sets

Question 2

What does a left join retain?

A. Only records matching in both tables

B. Every record from the right table and matching left records

C. Every record from the left table and matching right records

D. Only records with unique keys in both tables

Correct Answer: C. Every record from the left table and matching right records

Question 3

For a numerical variable, what does the conventional upper Tukey fence equal?

A. Q₃ + 1.5 × IQR

B. Q₁ + 1.5 × IQR

C. Mean + 1.5 × standard deviation

D. Median + 3 × IQR

Correct Answer: A. Q₃ + 1.5 × IQR

Question 4

Which encoding is appropriate when categories have a genuine order, such as basic, intermediate and advanced?

A. Ordinal encoding

B. Random encoding

C. Date encoding

D. Unit conversion

Correct Answer: A. Ordinal encoding

Question 5

What is the principal purpose of standardization?

A. To convert all categories into text

B. To give a numerical feature mean zero and standard deviation one

C. To remove every outlier

D. To guarantee a normal distribution

Correct Answer: B. To give a numerical feature mean zero and standard deviation one

Question 6

A sudden increase in row count after a supposed one-to-one join most directly suggests that:

A. The numerical values were standardized

B. The join key is not unique as assumed

C. All missing values were removed

D. The data are MCAR

Correct Answer: B. The join key is not unique as assumed

Question 7

Which statement about a z-score is correct?

A. It measures an observation’s distance from the mean in standard-deviation units

B. It proves that an observation is erroneous

C. It can be calculated only for categorical variables

D. It is unaffected by extreme observations

Correct Answer: A. It measures an observation’s distance from the mean in standard-deviation units

Question 8

Why might a missingness indicator be added during imputation?

A. To preserve possible information in the fact that a value was missing

B. To ensure every variable becomes normally distributed

C. To eliminate the need for validation

D. To convert an identifier into a target variable

Correct Answer: A. To preserve possible information in the fact that a value was missing

Question 9

Which transformation requires a strictly positive input when using y = log(x)?

A. Logarithmic transformation

B. One-hot encoding

C. Min–max scaling

D. Date parsing

Correct Answer: A. Logarithmic transformation

Question 10

Which practice best supports reproducibility?

A. Editing raw files directly and deleting earlier versions

B. Recording code, parameters, data versions and transformation history

C. Applying different preprocessing rules in training and deployment

D. Removing all audit information after modelling

Correct Answer: B. Recording code, parameters, data versions and transformation history