Data Wrangling and Quality Management

Introduction

Business analytics depends less on the sophistication of a model than on the fitness of the data supplied to it. Data collected from operational systems is rarely analysis-ready. Customer names may differ across applications, dates may use several formats, transactions may be duplicated, values may be missing, and measurements may contain errors or legitimate extreme observations. Data wrangling is the disciplined process of discovering, restructuring, cleaning, integrating, and preparing data for analysis. Data quality management extends this work by defining quality expectations, monitoring them, assigning responsibility, documenting lineage, and controlling the consequences of poor data.

For postgraduate analysts, data preparation is not merely a programming task. It is a form of analytical reasoning. Every transformation embodies an assumption about what a value means, which records refer to the same entity, what constitutes an error, and which observations should be retained. Poorly documented cleaning can produce a polished but invalid dataset and may introduce bias that is difficult to detect later.

This chapter presents a practical and governance-oriented framework for profiling, cleaning, transforming, merging, reshaping, validating, and documenting data. Examples use Python-like pseudocode and relational concepts, but the principles apply equally to SQL, R, spreadsheets, cloud data platforms, and commercial data-quality tools.

Learning Objectives

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

1. Explain the dimensions and lifecycle of data quality.
2. Profile datasets to identify structural, semantic, and statistical problems.
3. Clean missing values, duplicates, outliers, inconsistent formats, and invalid records.
4. Transform, merge, aggregate, and reshape data without violating business meaning.
5. Design validation rules and distinguish errors from legitimate variation.
6. Document assumptions, transformations, lineage, and data-quality results.
7. Evaluate ethical, governance, and managerial implications of data preparation.

1. Data Wrangling as an Analytical Discipline

Data wrangling converts data from its captured form into a form suitable for a defined analytical purpose. It commonly includes:

• Discovery: identifying sources, owners, structures, and meanings.
• Profiling: measuring completeness, uniqueness, distributions, and relationships.
• Cleaning: correcting, removing, imputing, or flagging problematic values.
• Transformation: changing types, units, scales, labels, and derived variables.
• Integration: combining datasets while preserving keys and grain.
• Reshaping: converting between wide and long representations.
• Validation: testing whether data satisfies technical and business rules.
• Documentation: recording provenance, decisions, exceptions, and outputs.

The intended use must be specified before cleaning begins. A missing income value may be unacceptable for credit underwriting, tolerable for a descriptive customer count, and inappropriate to replace with a mean in a fairness-sensitive model. Thus, there is no universally clean dataset; quality is fitness for a stated purpose.

Data grain and unit of analysis

The grain of a table states what one row represents. Examples include one row per customer, invoice, invoice line, store-day, or sensor reading. Many integration errors arise when tables with different grains are merged without accounting for multiplicity. If a customer table is joined to an order table, the resulting table has order-level grain, not customer-level grain. If two order-line tables are joined on only customer ID, a many-to-many multiplication can occur.

Before manipulating data, write a grain statement such as: “Each row represents one completed order line identified by order ID and product ID.” This statement supports key design, validation, aggregation, and interpretation.

2. Dimensions of Data Quality

Quality is multidimensional. Common dimensions are shown below.

A simple completeness rate for field j is:

Here, n_non-null,j is the number of non-missing values in field j, and n_total is the number of records assessed. The measure assumes that missingness is identifiable and that every record should be included in the denominator. If only a subset of records requires the field, the denominator must be restricted to that population.

A validity rate can similarly be written as:

The applicable population may exclude records for which the field is legitimately not required. Reporting only one overall quality score can conceal serious failures in an important field; quality should therefore be reported by field, population, source, and time period.

3. Profiling Before Cleaning

Profiling is an evidence-gathering stage, not an automatic cleaning operation. A profile should examine:

• row and column counts;
• data types and storage formats;
• null, blank, and sentinel values;
• distinct-value counts and duplicate keys;
• minimum, maximum, mean, median, and quantiles for numerical fields;
• frequency distributions for categorical fields;
• date ranges and temporal gaps;
• invalid codes, impossible values, and suspicious patterns;
• relationships such as totals, dependencies, and referential integrity.

A practical workflow is:

load source → preserve raw copy → inspect schema → profile fields
→ inspect keys and relationships → define rules → clean and transform
→ validate → publish curated data and quality report

A profile must distinguish different representations of missingness. NULL, an empty string, whitespace, N/A, unknown, -1, and 9999 may all appear, but they do not necessarily have the same meaning. A sentinel such as -1 should not be treated as a genuine numerical value.

Distributional inspection

Summary statistics can be misleading. For a highly skewed revenue field, the mean may be much larger than the median. Frequency tables can reveal spelling variants such as United Kingdom, UK, and U.K.. Histograms, box plots, time plots, and stratified summaries are useful, but visual inspection should be supplemented with explicit rules and counts.

Profiling should also be stratified. A 98% valid rate overall may hide a 40% validity rate for one branch, supplier, month, or demographic group. Such differences often identify process failures or fairness risks.

4. Missing Data: Diagnosis and Treatment

Missingness can arise because a value was not collected, was not applicable, was lost during integration, or was deliberately withheld. Its mechanism matters:

• MCAR (missing completely at random): missingness is unrelated to observed or unobserved values.
• MAR (missing at random): missingness depends on observed variables.
• MNAR (missing not at random): missingness depends on the unobserved value or another unobserved factor.

These are assumptions about a missingness process, not labels that can be proven simply by inspecting null counts. They influence whether deletion or imputation is defensible.

Common treatments include:

1. Deletion: appropriate when missing records are few, non-critical, and plausibly unrelated to the analysis, or when a required transaction field makes the record unusable.
2. Constant or category replacement: useful for explicitly representing “unknown” or “not applicable” in categorical data.
3. Statistical imputation: median imputation is often more robust than mean imputation for skewed variables; model-based or multiple imputation may better represent uncertainty.
4. Forward or backward filling: suitable for ordered time-series states only when the business process justifies persistence.
5. Missingness indicators: an additional binary variable can preserve information that absence itself may be informative.

Mean imputation reduces observed variation and can distort relationships. Imputation must be fitted using training data only when preparing predictive models; otherwise, information from the validation or test set leaks into the model-development process. Every imputed value should be traceable through an indicator, method, and timestamp.

5. Duplicates and Entity Resolution

Duplicates have different meanings. An exact duplicate may result from repeated file delivery. A duplicate business event may be a legitimate repeated purchase. A customer may appear multiple times because the table is at account or address level. Therefore, deduplication begins with grain and key definitions.

Useful distinctions include:

• Exact duplicates: all relevant fields are identical.
• Key duplicates: supposedly unique identifiers repeat.
• Near duplicates: records differ in spelling, formatting, or minor attributes.
• Legitimate repeats: multiple events for the same entity are expected.

A safe duplicate procedure is to preserve the raw records, identify the candidate key, count multiplicities, define a survivorship rule, and retain an audit column explaining the action. Survivorship may use the most recent verified record, the record from an authoritative source, or a rule-based combination. It should never be an unexplained “keep first” operation.

Entity resolution links records that refer to the same real-world entity. Deterministic matching uses exact identifiers; probabilistic matching compares attributes such as name, address, telephone, and email. Matching thresholds should be validated because false merges can be more harmful than missed matches. Personal-data matching also requires access controls and a lawful purpose.

6. Inconsistent Formats and Semantic Standardisation

Format standardisation changes representation without changing meaning. Typical operations include:

• parsing dates with an explicit locale and timezone;
• converting numeric strings after removing permitted separators and symbols;
• standardising case and whitespace;
• mapping code values through controlled reference tables;
• converting units and currencies using documented rules;
• normalising phone numbers and postal codes;
• separating composite fields when the components are analytically needed.

Ambiguous dates such as 03/04/2025 should not be guessed. The system must use source metadata, a documented locale, or an exception queue. Time-zone conversion must preserve the event’s intended instant; a date-only business concept should not be converted as though it were a timestamp.

Categorical mapping should use a reference table rather than scattered conditional statements. For example, M, Male, and 1 may map to a standard code only if their source definitions are confirmed. Unknown and not-applicable should remain distinct when they imply different business conditions.

Unit conversions must be explicit. If a temperature in Fahrenheit is converted to Celsius, the correct transformation is:

The formula applies to temperature scales, where the offset matters; it is not a general multiplicative conversion. Currency conversion additionally requires a rate, rate date, currency pair, and treatment of fees or rounding.

7. Outliers: Error, Signal, or Population Difference?

An outlier is an observation unusually distant from other observations. It is not automatically an error. A large transaction may indicate fraud, a premium customer, a data-entry mistake, or a genuinely rare event.

For a numerical variable, the interquartile range is:

The conventional Tukey fences are:

Here, Q₁ and Q₃ are the first and third quartiles. The rule is a screening heuristic, not proof of invalidity. It is sensitive to the chosen population and may flag legitimate heavy-tailed observations.

Other approaches include robust z-scores based on the median and median absolute deviation, domain limits, peer-group comparisons, and time-series anomaly detection. Options are to retain, cap, transform, remove, or flag observations. Removing an outlier without investigation can eliminate important business information and bias estimates. A preferable default is to retain the raw value, add a quality flag, investigate its provenance, and create an analysis-specific treatment if justified.

8. Transformation, Aggregation, and Reshaping

Transformations should preserve semantic meaning and be reproducible. Common transformations include type conversion, derived variables, logarithms for strongly skewed positive quantities, binning, scaling, and aggregation.

A derived margin percentage, for example, is meaningful only when revenue is non-zero and the numerator and denominator use compatible definitions:

The result is dimensionless and expressed as a percentage. Zero-revenue records require a defined policy rather than silent division by zero.

Wide and long data

In wide data, repeated measures occupy separate columns, such as sales_jan, sales_feb, and sales_mar. In long data, each period is represented by a row with fields such as month and sales. Long form is generally convenient for grouping, visualisation, and time-based modelling; wide form may be convenient for reports or certain algorithms.

Reshaping must preserve keys and values. A pivot operation may fail or aggregate unexpectedly when multiple records exist for the intended index-column combination. Before pivoting, test uniqueness at the target grain and specify an aggregation function only when aggregation is substantively justified.

Aggregation also requires care. Summing revenue is appropriate for additive measures across a stated dimension, whereas averaging customer-level percentages may be inappropriate when weighted rates are required. Define the numerator and denominator before calculating a rate.

9. Merging and Integrating Data

Joins should be planned using key definitions, relationship cardinality, and grain. Common join types are:

• Inner join: retains only matching records from both sources.
• Left join: retains every record from the left source and matching records from the right.
• Right join: the converse of a left join.
• Full outer join: retains unmatched records from both sources.

Before a join, assess key uniqueness and data types. After a join, compare row counts, measure unmatched keys, and test whether expected cardinality was preserved. A one-to-one join should not unexpectedly multiply rows. A one-to-many join should increase rows only according to documented business structure.

A common failure is joining two transaction tables on customer_id alone. If customer A has three rows in one table and four in another, the join can create twelve combinations for that customer. This many-to-many multiplication inflates totals. Aggregate each source to a common grain or use a linking key before joining.

Reference data should generally be versioned. If product categories change over time, historical transactions may require slowly changing dimension logic so that analysis reflects the category valid at the event date rather than only the current category.

10. Validation and Business Data-Quality Rules

Validation combines technical checks with domain rules. A rule should specify the field or relationship, condition, severity, owner, frequency, and response. Examples include:

A validation result should contain counts and examples, not merely a pass/fail label. Severity levels may include warning, error, and critical failure. Critical failures should block publication; warnings may permit publication with a documented exception.

A useful quality rule record contains: rule ID, business rationale, expression, source fields, threshold, severity, owner, execution time, result, and remediation status. Automated tests can run in a pipeline, while sampled records and reconciliation reports support human review.

11. Lineage, Reproducibility, and Documentation

Data lineage describes where data came from, how it moved, and how it changed. It may be represented at table, column, record, or transformation level. A lineage record should identify source systems, extraction time, versions, transformations, reference data, code revision, and destination dataset.

Good documentation includes:

• data dictionary and field definitions;
• grain and key specification;
• source ownership and refresh frequency;
• permitted values and units;
• missing-data and outlier policies;
• transformation logic;
• quality-rule results;
• known limitations and unresolved exceptions;
• release version and approval history.

A reproducible pipeline separates raw, staged, curated, and analytical layers. Raw data should be immutable where legally and operationally feasible. Code should be version-controlled, parameters should be explicit, and random procedures should use recorded seeds when reproducibility is required. “Cleaned dataset” is not sufficient documentation: an analyst should be able to explain what changed, why it changed, and how to recreate the output.

12. Applied Case: Retail Customer and Order Integration

The following is an illustrative case.

Background

A retailer wants to predict customer churn using customer profiles, orders, and support interactions. The customer master contains one row per customer, the order table contains one row per order line, and the support table contains one row per case.

Problem

Initial profiling finds duplicate customer IDs, inconsistent country codes, missing birth dates, dates in mixed formats, negative quantities, and a sudden increase in revenue after joining orders to support cases. Managers initially request that all unusual records be deleted.

Analysis

The team first writes grain statements and discovers that the revenue inflation results from joining order lines and support cases directly on customer ID. A customer with five order lines and three support cases contributes fifteen joined rows. The team also finds that negative quantities represent authorised product returns, not necessarily errors. Missing birth dates are concentrated in older legacy accounts, indicating a process and historical migration issue rather than random missingness.

Approach and solution

1. Preserve immutable extracts of all three sources.
2. Standardise identifiers, dates, country codes, and text whitespace.
3. Validate customer IDs and create a survivorship rule using verified status and latest update time.
4. Aggregate order lines to customer-month before joining to customer-level features.
5. Represent returns separately and calculate net quantity according to an agreed business definition.
6. Retain birth-date missingness as a documented field and indicator rather than guessing ages.
7. Apply domain rules: non-negative price, valid status, order date not later than extraction date, and referential integrity.
8. Produce a quality report showing rule results by source and month.
9. Version the curated dataset and record all code and reference-table versions.

Outcome

Revenue totals reconcile to the finance ledger after the grain-correct aggregation. Duplicate customer records fall into a review queue rather than being silently removed. The churn model receives stable features, while analysts can distinguish missing demographic information from observed demographic values. Managers also obtain an actionable finding: the legacy migration is the primary source of missing birth dates.

Learning points

The case demonstrates that data-quality work is inseparable from business process knowledge. A technically valid join can still be analytically invalid. Outliers and negative values require semantic investigation, and missingness may itself be a useful operational signal. Quality management creates accountability rather than merely producing a cleaner file.

13. Ethical, Governance, and Regulatory Considerations

Cleaning decisions can affect people. Removing records from underrepresented groups, imputing sensitive attributes, or resolving identities incorrectly may create discriminatory outcomes. Analysts should assess quality by relevant subgroup, document exclusions, minimise personal data, and restrict access according to role and purpose.

Privacy principles commonly relevant to analytics include purpose limitation, data minimisation, accuracy, retention control, security, and accountability. Applicable obligations depend on jurisdiction and sector; examples include requirements concerning personal data protection, financial records, health information, and automated decision-making. Data lineage supports auditability, while model-development datasets should be separated from unnecessary direct identifiers.

Quality rules should be governed jointly by technical specialists and business owners. A rule that is technically convenient but inconsistent with policy can institutionalise an error. When automated matching or imputation is used, human review should be available for high-impact cases and uncertainty should be recorded.

14. Contemporary Practice and Limitations

Modern data platforms increasingly provide data catalogs, observability, schema-drift detection, automated profiling, and declarative quality tests. Machine-learning methods can detect anomalies, classify likely duplicates, and suggest mappings. However, automation does not establish business meaning. A model may identify a rare value without knowing whether it is fraud, a promotion, or a new product launch.

Generative AI can assist with code drafts, documentation, and rule suggestions, but generated transformations require testing, security review, and human approval. Sensitive data should not be sent to an unapproved service. Automated cleaning can also create silent changes that are difficult to audit.

Important limitations remain. Quality metrics may be incomplete, reference data may itself be wrong, and rules may become obsolete as products and policies change. Data preparation can reduce noise but cannot recover information that was never collected. Every quality report should therefore state its coverage, assumptions, and blind spots.

Best-Practice Checklist

Before releasing an analytical dataset, confirm that:

• the purpose, population, and grain are documented;
• raw inputs are preserved and source versions recorded;
• keys, cardinality, and referential integrity have been tested;
• missingness, duplicates, formats, and outliers have been profiled;
• transformations use explicit, reproducible rules;
• aggregation and joins do not inflate or omit measures;
• business validation rules have owners and severity levels;
• exceptions and imputation decisions are traceable;
• quality is examined across time, source, and relevant subgroups;
• privacy, access, retention, and regulatory requirements are addressed;
• the final dataset has a version, quality report, and known-limitations statement.

Chapter Summary

Data wrangling prepares captured data for a defined analytical purpose, while data quality management governs whether the resulting data is fit for use. Effective practice begins with profiling and explicit grain statements. Analysts must investigate missingness, distinguish duplicates from legitimate repeated events, standardise formats without changing meaning, and treat outliers as candidates for investigation rather than automatic errors. Joins and aggregations require careful attention to keys and cardinality because technically valid operations can produce analytically invalid results. Validation should combine structural, domain, cross-field, reconciliation, referential, and temporal rules. Lineage, version control, documentation, and quality reports make transformations reproducible and auditable. Finally, cleaning decisions have ethical and governance consequences, particularly when personal data or high-impact decisions are involved.

Key Terms

Data wrangling: The process of discovering, cleaning, transforming, integrating, and preparing data for analysis.

Data quality: The degree to which data is fit for a stated analytical or operational purpose.

Data grain: The business meaning represented by one row of a dataset.

Completeness: The extent to which required values are present.

Validity: Conformance to defined types, formats, ranges, or code lists.

Accuracy: Agreement between data and the real-world object or event.

Consistency: Absence of contradictory values across fields or sources.

Uniqueness: The property that records or identifiers are not improperly repeated.

Referential integrity: The requirement that related records point to valid keys.

Data profiling: Systematic examination of structure, distributions, missingness, and relationships.

MCAR: Missing completely at random.

MAR: Missing at random conditional on observed information.

MNAR: Missing not at random.

Entity resolution: Linking records that represent the same real-world entity.

Outlier: An unusually distant observation relative to a defined reference population.

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

Data lineage: A record of data origins, movements, transformations, and destinations.

Schema drift: An unexpected change in structure, fields, types, or permitted values.

Business rule: A domain-specific condition used to assess or constrain data.

Data quality dimension: A defined aspect of quality, such as completeness, validity, or timeliness.

Review Questions

Short Answer Questions

1. Why should a dataset’s grain be documented before joining it to another dataset?
2. Distinguish completeness from validity.
3. Why is an outlier not automatically a data error?
4. What is the purpose of a missingness indicator?
5. Name four components that should appear in a data-quality rule record.

Long Answer Questions

1. Explain the major dimensions of data quality and discuss why a single overall quality score can be misleading.
2. Compare deletion, constant replacement, median imputation, and model-based imputation for missing values. Include assumptions and risks.
3. Explain how an incorrect many-to-many join can inflate business measures. Provide a prevention strategy.
4. Discuss the role of lineage, version control, and documentation in reproducible analytics.
5. Evaluate the ethical and governance risks of automated deduplication and outlier removal.

Analytical / Application Questions

1. A customer table has one row per customer and an order table has one row per order line. Design a workflow to calculate monthly customer revenue without double-counting, and specify the validation tests you would perform.
2. A sales field contains NULL, unknown, -1, and zero. Propose a classification and treatment plan, explaining which values may represent legitimate business states.
3. A data-quality dashboard reports 99% validity overall but 62% validity for one branch. Analyse possible causes and design an investigation and remediation plan.

Multiple Choice Questions with Answers

Question 1

Which statement best defines data grain?

A. The number of columns in a table

B. What one row represents in business terms

C. The percentage of missing values

D. The storage format of a file

Correct Answer: B. What one row represents in business terms

Question 2

Which join most directly preserves every record from the left-hand dataset?

A. Inner join

B. Right join

C. Left join

D. Cross join

Correct Answer: C. Left join

Question 3

Which is a validity rule?

A. A required value is present

B. An order status belongs to an approved code list

C. A report is delivered before its deadline

D. A customer appears only once in a master table

Correct Answer: B. An order status belongs to an approved code list

Question 4

Why can joining two transaction tables on customer ID alone inflate totals?

A. It converts all values to text

B. It removes all unmatched customers

C. It can create many-to-many row multiplication

D. It automatically rounds monetary values

Correct Answer: C. It can create many-to-many row multiplication

Question 5

What is the conventional upper Tukey fence?

A. Q₁ + 1.5 × IQR

B. Q₃ + 1.5 × IQR

C. Q₃ − 1.5 × IQR

D. Mean + 1.5 × standard deviation

Correct Answer: B. Q₃ + 1.5 × IQR

Question 6

Which missingness mechanism means that missingness depends on observed variables?

A. MCAR

B. MAR

C. MNAR

D. Random deletion

Correct Answer: B. MAR

Question 7

What is the primary purpose of data lineage?

A. To increase the number of observations

B. To record data origins and transformations

C. To replace business validation rules

D. To remove all outliers

Correct Answer: B. To record data origins and transformations

Question 8

Which treatment is most appropriate for an ambiguous date such as 03/04/2025?

A. Always interpret it as March 4

B. Always interpret it as April 3

C. Guess based on the nearest record

D. Use documented locale or source metadata and flag unresolved cases

Correct Answer: D. Use documented locale or source metadata and flag unresolved cases

Question 9

A temperature is 68 °F. Using °C = (°F − 32) × 5/9, what is its Celsius value?

A. 10 °C

B. 20 °C

C. 36 °C

D. 68 °C

Correct Answer: B. 20 °C

Question 10

Which practice best protects predictive-model evaluation from imputation leakage?

A. Fit imputation parameters using the full dataset before splitting

B. Fit imputation parameters using training data only

C. Replace every missing value with zero

D. Delete the validation set

Correct Answer: B. Fit imputation parameters using training data only