Data Cleaning, Transformation and Quality

Introduction

Business analytics depends on the quality of the data supplied to reports, dashboards, forecasts and decisions. A sophisticated model cannot compensate for systematically missing values, duplicated transactions, inconsistent date formats or incorrectly coded categories. Data cleaning is therefore not a minor programming task; it is an analytical and governance activity that determines whether conclusions are trustworthy.

In Python, data preparation commonly uses pandas and NumPy. The central principle is to transform raw data into an analysis-ready dataset while preserving evidence of what was changed, why it was changed and how the resulting quality was assessed. This chapter develops that principle through practical methods for profiling data, treating common defects, transforming variables and building auditable pipelines.

Learning Objectives

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

• profile a dataset using structural, statistical and quality checks;
• identify and treat missing values, duplicates, outliers and inconsistent formats;
• transform categorical and numerical variables appropriately;
• distinguish correction, imputation, exclusion and retention of unusual observations;
• construct reproducible and auditable pandas pipelines;
• validate data quality before analytical use; and
• explain the ethical and managerial consequences of poor data preparation.

1. Data Quality as an Analytical Concept

Data quality is fitness for a defined purpose. A dataset may be adequate for a monthly revenue summary but unsuitable for customer-level churn modelling if customer identifiers are incomplete. Quality is therefore contextual rather than absolute.

Important dimensions include:

A useful general measure of missingness is the missing-value rate:

Here, n_m is the number of missing observations for a field and n is the total number of observations. The measure assumes that each row represents a comparable observational unit. A missingness rate should be reported with the field name, time period and population to which it applies.

2. Profiling a Dataset Before Cleaning

Profiling is the systematic examination of structure, content and quality before transformations are applied. Cleaning without profiling can remove useful information or conceal important business problems.

A first inspection in pandas might be:

import pandas as pd
import numpy as np

sales = pd.read_csv("sales.csv")

print(sales.shape)
print(sales.head())
sales.info()
print(sales.describe(include="all").T)
print(sales.isna().mean().mul(100).sort_values(ascending=False))

shape shows rows and columns. info() reveals data types and non-null counts. describe() provides numerical summaries and, with include="all", summaries of categorical fields. The final expression calculates the percentage of missing values in each column.

A profile should also inspect:

print(sales.nunique(dropna=False))
print(sales["status"].value_counts(dropna=False))
print(sales.duplicated().sum())

These commands identify cardinality, category frequencies and exact duplicate rows. Profiling should be performed before and after major transformations, with results stored as part of the project record.

Data dictionaries and business rules

A data dictionary defines each field, its meaning, type, allowed values, unit, source and treatment rules. For example, discount_rate might be defined as a proportion between 0 and 1, whereas a source system may store it as a percentage between 0 and 100. Without a dictionary, a technically successful transformation can still be conceptually wrong.

Business rules can be expressed as assertions:

assert sales["quantity"].ge(0).all(), "Negative quantities require investigation"
assert sales["order_id"].notna().all(), "Order ID is mandatory"

Assertions should be used carefully. A rule should fail when a genuine data-quality breach occurs, not merely because a legitimate exception exists.

3. Missing Values

Missingness may arise because a question was not asked, a sensor failed, a customer declined to respond, a value was not applicable or a system failed during extraction. The reason matters. A blank income field and a blank discount field may require different treatments.

Three broad mechanisms are commonly discussed:

• Missing completely at random (MCAR): missingness is unrelated to observed and unobserved values.
• Missing at random (MAR): missingness is related to observed variables, after relevant information is considered.
• Missing not at random (MNAR): missingness is related to the unobserved value itself or an unmeasured process.

These mechanisms are assumptions about how missingness arose; they cannot usually be proven from the incomplete dataset alone.

Diagnosing missingness

missing = (
    sales.isna()
         .sum()
         .to_frame("missing_count")
         .assign(missing_rate=lambda x: x["missing_count"] / len(sales))
         .sort_values("missing_rate", ascending=False)
)
print(missing)

Examine missingness by branch, month, product or collection method. A low overall rate can conceal a serious problem concentrated in one region.

Treatment choices

1. Leave missing values: appropriate when absence is meaningful or the analytical method handles missingness explicitly.
2. Remove rows: defensible when only a small, non-systematic number of rows is missing a mandatory field and the loss will not bias results.
3. Impute values: replace missing observations using a justified rule.
4. Create an indicator: add a field recording whether imputation occurred.
5. Escalate to the source: preferred when missingness indicates a process failure.

Mean imputation is simple but can reduce variation and distort relationships. Median imputation is more robust to skewed numerical data. Mode imputation may be suitable for a stable categorical field but can overrepresent the most common category. Groupwise imputation can be more defensible when groups differ substantively:

sales["delivery_days"] = (
    sales.groupby("region")["delivery_days"]
         .transform(lambda s: s.fillna(s.median()))
)

The imputation rule must be learned from the training data only when building a predictive model. Otherwise, information from the validation or test set can leak into the model-development process.

4. Duplicates and Record Grain

A duplicate is not simply a repeated-looking row. The correct definition depends on the grain of the dataset. If the grain is one row per order, repeated order_id values may indicate duplication. If the grain is one row per product within an order, repeated order IDs are expected.

Inspect duplicates using the business key:

sales[sales.duplicated(subset=["order_id"], keep=False)]

Exact duplicates can be removed when they are confirmed to be accidental:

sales = sales.drop_duplicates()

For conflicting records with the same key, do not silently keep the first row. Compare timestamps, source-system priorities and field completeness. A documented survivorship rule might retain the most recent authoritative record:

sales = (
    sales.sort_values(["order_id", "updated_at"])
         .drop_duplicates("order_id", keep="last")
)

This is valid only if updated_at is reliable and the intended grain is one row per order. Duplicate removal can change revenue totals, customer counts and operational conclusions, so its effect should be quantified.

5. Inconsistent Formats and Data Types

Inconsistent formats commonly occur when data is combined from spreadsheets, applications and manual entry. Typical examples include UK, United Kingdom and United kingdom; dates such as 2025-03-04 and 04/03/2025; and currency values stored as £1,250.00.

Text standardisation

sales["country"] = (
    sales["country"]
    .astype("string")
    .str.strip()
    .str.casefold()
    .replace({"uk": "united kingdom", "gb": "united kingdom"})
)

Standardisation should not erase meaningful distinctions. For example, converting all product codes to lowercase may be harmful if codes are case-sensitive.

Dates

sales["order_date"] = pd.to_datetime(
    sales["order_date"], errors="coerce", dayfirst=False
)

errors="coerce" converts invalid values to missing values, which makes errors visible for later review. Date interpretation must be established from the source specification; 03/04/2025 can mean 3 April or 4 March.

Numeric fields

sales["revenue"] = (
    sales["revenue"].astype("string")
    .str.replace("£", "", regex=False)
    .str.replace(",", "", regex=False)
    .str.strip()
    .pipe(pd.to_numeric, errors="coerce")
)

A numeric conversion does not prove that the values are valid. It must be followed by range and reconciliation checks.

6. Outliers and Unusual Observations

An outlier is an observation unusually distant from the rest of a distribution. It may be a data-entry error, a rare but genuine event, a change in business conditions or evidence of fraud. Outliers must therefore be investigated, not automatically deleted.

The interquartile range is:

where Q₁ is the first quartile and Q₃ is the third quartile. A conventional lower and upper screening boundary are:

Values below L or above U are flagged by this rule. The rule is a screening convention, not proof that an observation is erroneous. It is most informative for continuous numerical variables and may be inappropriate for strongly seasonal or multimodal data.

q1 = sales["revenue"].quantile(0.25)
q3 = sales["revenue"].quantile(0.75)
iqr = q3 - q1
sales["revenue_outlier"] = sales["revenue"].lt(q1 - 1.5 * iqr) | sales["revenue"].gt(q3 + 1.5 * iqr)

Other approaches include domain limits, robust z-scores, percentile rules and model-based detection. A conventional z-score is:

where x is an observation, μ is the mean and σ is the standard deviation. This approach assumes that mean and standard deviation provide a meaningful reference; it can be distorted by the very outliers it is intended to identify.

Possible treatments are correction from the source, retaining with a flag, capping for a specific modelling purpose, transforming the variable or excluding the observation with documented justification. Analysts should compare results with and without flagged observations.

7. Transforming Categorical Variables

Categorical variables represent groups rather than measured quantities. Their treatment depends on whether categories have an order and whether the analytical method accepts text labels.

• Nominal categories: no inherent order, such as region or payment method.
• Ordinal categories: meaningful order, such as bronze, silver and gold.
• Binary categories: two states, such as active/inactive.

For reporting, a controlled mapping is often best:

status_map = {
    "complete": "Completed",
    "completed": "Completed",
    "cancelled": "Cancelled",
    "canceled": "Cancelled"
}
sales["status"] = sales["status"].str.strip().str.casefold().map(status_map)

Unmapped values become missing and should be reviewed rather than silently discarded.

One-hot encoding creates indicator variables for nominal categories. If a model includes an intercept, one category is commonly omitted to avoid perfect linear dependence among the indicators. The appropriate choice depends on the modelling method and implementation. Ordinal encoding should be used only when numeric order is substantively justified; assigning 1, 2 and 3 to unrelated categories creates a false distance relationship.

High-cardinality categories may require grouping rare values, frequency encoding or domain-specific aggregation. Any grouping rule should be stable, documented and evaluated for unintended bias.

8. Transformation Pipelines and Auditability

A pipeline makes preparation reproducible and reduces the risk of applying steps in the wrong order. A robust process separates:

1. Ingestion: read the source and record its location, timestamp and version.
2. Profiling: inspect shape, types, missingness and distributions.
3. Standardisation: harmonise names, whitespace, formats and types.
4. Validation: test keys, ranges, relationships and business rules.
5. Treatment: correct, impute, exclude or flag defects.
6. Output: save the prepared dataset and a quality report.

Example:

def prepare_sales(df):
    out = df.copy()

    out.columns = out.columns.str.strip().str.lower().str.replace(" ", "_")
    out["order_date"] = pd.to_datetime(out["order_date"], errors="coerce")
    out["quantity"] = pd.to_numeric(out["quantity"], errors="coerce")
    out["revenue"] = pd.to_numeric(out["revenue"], errors="coerce")

    out["region"] = (
        out["region"].astype("string").str.strip().str.casefold()
    )
    out["region"] = out["region"].replace({"uk": "united kingdom"})

    out["quantity_invalid"] = out["quantity"].lt(0)
    out["revenue_invalid"] = out["revenue"].lt(0)

    return out

A transformation should ideally be deterministic: the same input and configuration should produce the same output. Avoid overwriting raw files. Retain raw, cleaned and analysis-ready layers, together with code, configuration and a change log.

A quality report can be represented as a table containing rule, result, threshold, count of failures and action taken. For example:

quality = pd.DataFrame({
    "check": ["missing_order_id", "negative_quantity", "duplicate_order_id"],
    "failures": [
        sales["order_id"].isna().sum(),
        sales["quantity"].lt(0).sum(),
        sales["order_id"].duplicated().sum()
    ]
})

9. Validation After Cleaning

Cleaning is incomplete until the output has been validated. Useful validation categories include:

• Schema checks: required columns exist and have expected types.
• Completeness checks: mandatory fields meet acceptable thresholds.
• Domain checks: quantities, dates and rates fall within plausible ranges.
• Uniqueness checks: business keys behave according to the stated grain.
• Referential checks: foreign keys match valid records in related tables.
• Reconciliation checks: totals before and after treatment are explainable.
• Distribution checks: cleaning has not produced unexpected concentration.

For an order line, a reconciliation rule may be:

This assumes quantity and unit_price are non-negative, discount_rate is expressed as a proportion from 0 to 1, and taxes or shipping are excluded. In code:

expected = (
    sales["quantity"] * sales["unit_price"] * (1 - sales["discount_rate"])
)
sales["reconciliation_difference"] = sales["line_total"] - expected

The acceptable tolerance must reflect currency precision and source-system rounding. A difference of 0.01 may be immaterial in one context and significant in another.

10. Illustrative Case: Preparing Retail Sales Data

Background

A retailer receives a quarterly sales extract from stores and an online channel. Management wants a reliable regional revenue dashboard and a customer-retention analysis.

Problem

The extract contains blank customer IDs, duplicated online orders, inconsistent region names, dates in mixed formats and several unusually large transactions. The raw revenue total is higher than the finance system total.

Analysis

The analyst first records that the intended grain is one row per order line. Profiling shows that customer ID is optional for anonymous purchases, whereas order ID, date, quantity and revenue are mandatory. Region values include North, north and NORTH. Duplicate order lines occur because an export was appended twice. Large transactions are concentrated in corporate accounts and are not automatically errors.

Approach and solution

The analyst preserves the raw extract and creates a versioned preparation script. Text fields are standardised, dates are parsed using the source-system specification, and invalid dates are reported. Exact duplicated rows are removed after confirming that the duplicates have identical source identifiers. Conflicting records are reviewed against the latest transaction timestamp.

Missing customer IDs are retained and labelled as anonymous rather than imputed. Negative quantities are investigated because they may represent returns; they are not deleted. Revenue is reconciled to quantity, price and discount, with documented tolerance for rounding. The unusually large corporate transactions are retained but flagged for separate reporting.

Outcome

The prepared dashboard total reconciles to the finance system after duplicate export rows are removed and legitimate returns are represented correctly. The retention analysis uses only records with valid customer IDs and clearly states that anonymous purchases are excluded. A quality report shows the number of rows affected by each rule.

Learning points

The case demonstrates that cleaning is not equivalent to making data look uniform. Business meaning determines whether a blank is missing, a negative is erroneous or an extreme value is legitimate. A defensible process preserves unusual observations when evidence supports them and makes exclusions visible.

11. Governance, Ethics and Contemporary Practice

Data cleaning can create ethical risks. Imputing income, removing low-frequency groups or treating unknown categories as the majority class can change the representation of customers and communities. Analysts should assess whether treatment decisions produce disparate effects and should document assumptions that may affect fairness.

Personally identifiable information should be minimised, access-controlled and handled according to applicable privacy law and organisational policy. Customer identifiers should not be copied unnecessarily into analysis outputs. Data lineage should identify source, transformations, responsible owner and approval status.

Modern data platforms increasingly combine schema validation, automated data-quality monitoring, version control and orchestration tools. Artificial intelligence can assist with anomaly detection, semantic matching and suggested standardisation, but automated recommendations require human review. An AI system may infer that two categories are equivalent when the business distinction is important. Automation should therefore generate evidence and proposals, not replace accountability.

Best-Practice Checklist

• Define the dataset grain and key before removing duplicates.
• Profile before transforming.
• Preserve raw data and create versioned outputs.
• Record reasons for every exclusion, correction and imputation.
• Treat outlier detection as investigation, not automatic deletion.
• Use domain knowledge to interpret missingness and unusual values.
• Fit model-based transformations only on training data.
• Validate both individual fields and relationships between fields.
• Reconcile important totals with an authoritative source.
• Report limitations and populations excluded from analysis.

Chapter Summary

Data cleaning and transformation convert raw records into information that can support responsible analysis. Effective preparation begins with profiling and a clear data dictionary. Missing values require diagnosis of their meaning and mechanism; duplicates must be considered in relation to dataset grain; inconsistent formats require controlled standardisation; and outliers must be investigated rather than mechanically deleted. Categorical variables should be encoded according to their measurement level and analytical purpose.

A professional workflow separates raw, cleaned and analysis-ready data, uses reproducible pipelines and produces an audit trail. Quality validation should cover schema, completeness, validity, uniqueness, integrity, reconciliation and distributions. Finally, cleaning decisions have governance and ethical consequences because they can alter representation, totals and model outcomes. Reliable analytics depends not only on Python syntax but also on transparent reasoning and documented evidence.

Key Terms

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

Data profiling: Systematic examination of dataset structure, content and quality.

Data dictionary: Documentation describing fields, meanings, types, units and rules.

Completeness: The extent to which required values are present.

Validity: Conformity to permitted formats, types or ranges.

Accuracy: Agreement between a recorded value and the real-world value.

Data grain: The entity represented by one row in a dataset.

Business key: Field or combination of fields intended to identify a record.

Missing value: An absent, unavailable or inapplicable observation.

Imputation: Replacing a missing value using a stated rule or estimate.

Duplicate: A repeated record relative to the defined grain and identification rule.

Outlier: An observation unusually distant from a reference distribution.

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

Categorical variable: A variable whose values represent groups or labels.

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

Data pipeline: A reproducible sequence of ingestion, transformation and validation steps.

Data lineage: A record of data origin, movement and transformation.

Data leakage: Use of information during model preparation that would not be available at prediction time.

Reconciliation: Comparison of a calculated result with an authoritative total or rule.

Quality gate: A condition that data must satisfy before progressing to the next stage.

Review Questions

Short Answer Questions

1. Why should a dataset’s grain be established before duplicate removal?
2. Distinguish completeness from validity.
3. Give two legitimate reasons why a value may be missing.
4. What does the interquartile range measure?
5. Why should raw data normally be preserved?

Long Answer Questions

1. Explain a complete workflow for profiling, cleaning and validating a business dataset.
2. Compare row deletion, imputation and retention as approaches to missing values.
3. Explain why an outlier should not automatically be treated as an error.
4. Discuss how nominal and ordinal categorical variables should be transformed.
5. Describe the governance and ethical risks associated with automated data cleaning.

Analytical / Application Questions

1. A sales file contains UK, uk and United Kingdom, dates in two formats and negative quantities. Design a Python-based preparation and validation plan, including rules for investigating negative quantities.
2. A customer field has 35% missing values, concentrated in one branch. Explain how you would investigate the missingness and decide whether to impute, retain, exclude or escalate it.
3. After cleaning, reported revenue falls by 8%. Specify the reconciliation checks and audit evidence you would use to determine whether the reduction is justified.

Multiple Choice Questions with Answers

Question 1

Which quality dimension asks whether required values are present?

A. Completeness

B. Uniqueness

C. Timeliness

D. Accuracy

Correct Answer: A. Completeness

Question 2

What should an analyst establish before removing duplicate records?

A. The preferred chart type

B. The dataset grain

C. The model accuracy

D. The colour palette

Correct Answer: B. The dataset grain

Question 3

Which pandas expression calculates the missing-value rate for each column as a proportion?

A. df.notna().sum()

B. df.isna().mean()

C. df.duplicated().sum()

D. df.nunique()

Correct Answer: B. df.isna().mean()

Question 4

Under the conventional IQR screening rule, an observation is flagged above which boundary?

A. Q1 + 1.5 × IQR

B. Q3 + 1.5 × IQR

C. Mean + 0.5 × IQR

D. Median + 3 × Q1

Correct Answer: B. Q3 + 1.5 × IQR

Question 5

Why is assigning numbers to nominal categories potentially misleading?

A. It always creates missing values

B. It implies an order or distance that may not exist

C. It prevents any statistical analysis

D. It removes all duplicate rows

Correct Answer: B. It implies an order or distance that may not exist

Question 6

What does errors="coerce" commonly do in pandas conversion functions?

A. Deletes the entire column

B. Converts invalid values to missing values

C. Converts every value to zero

D. Stops all processing permanently

Correct Answer: B. Converts invalid values to missing values

Question 7

Which treatment is most appropriate when a large transaction is unusual but verified as genuine?

A. Automatically delete it

B. Replace it with the mean

C. Retain it and potentially flag it

D. Convert it to a category without review

Correct Answer: C. Retain it and potentially flag it

Question 8

Why must model-based imputation generally be fitted only on training data?

A. To prevent data leakage

B. To increase the number of duplicates

C. To remove business rules

D. To force all variables to be categorical

Correct Answer: A. To prevent data leakage

Question 9

In the reconciliation formula line_total = quantity × unit_price × (1 − discount_rate), what assumption is required about discount_rate?

A. It is expressed as a proportion from 0 to 1

B. It is always greater than 100

C. It is a date

D. It is a customer identifier

Correct Answer: A. It is expressed as a proportion from 0 to 1

Question 10

Which practice best supports auditability?

A. Overwriting the raw source file

B. Keeping only the final dashboard

C. Recording transformations, rules and affected row counts

D. Removing all unusual observations without explanation

Correct Answer: C. Recording transformations, rules and affected row counts