Data Wrangling and Quality Management

Introduction and Context

Data science rarely begins with a clean analytical table. Data are usually collected from databases, spreadsheets, application programming interfaces, sensors, surveys, transaction systems, and operational logs. These sources differ in structure, naming conventions, units, granularity, coding systems, and reliability. Before statistical modelling or visualisation, analysts must therefore convert heterogeneous raw data into a defensible analytical dataset.

Data wrangling is the systematic process of inspecting, restructuring, combining, transforming, and documenting data. Data quality management is the broader process of defining quality requirements, detecting defects, correcting or appropriately retaining problematic records, and monitoring quality over time. In R, the tidyverse provides an integrated environment for these activities, especially through `dplyr`, `tidyr`, `readr`, `stringr`, `forcats`, `lubridate`, and `purrr`.

A crucial principle is that quality is not synonymous with cleanliness. A value that looks unusual may be valid, while a value that appears plausible may be wrong. Consequently, quality management requires domain knowledge, explicit rules, reproducible code, and an audit trail of decisions.

Learning Objectives

After completing this chapter, the reader should be able to:

1. Explain the principles of tidy data and relational data integration.

2. Reshape data between long and wide forms using `tidyr`.

3. Select, filter, mutate, summarise, and group data using `dplyr`.

4. Join datasets safely while identifying unmatched and duplicated keys.

5. Diagnose and treat missing values, duplicates, outliers, inconsistent definitions, and measurement errors.

6. Design validation rules and quality reports in R.

7. Evaluate the ethical, operational, and governance implications of data-quality decisions.

1. Foundations of Data Wrangling

1.1 Tidy data

The tidy-data framework represents each observational unit as a row, each measured variable as a column, and each observational value as a cell. For example, a sales table may contain one row per invoice line, with columns for invoice identifier, product identifier, date, quantity, and price.

Tidy data is not the same as a universal database design. A highly normalised transactional database may be excellent for operational integrity but inconvenient for analysis. Conversely, an analytical table may deliberately denormalise information to support efficient modelling. Tidy structure is therefore an analytical convention rather than an absolute replacement for relational design.

Common violations include:

• several variables encoded in one column, such as `"North_2024"`;
• multiple observations stored in one cell, such as `"A;B;C"`;
• variables stored as column names, such as monthly columns `jan`, `feb`, and `mar`;
• one observational unit represented by several rows without a documented reason.

1.2 Data grain and keys

The grain of a dataset states what one row represents. Examples include one customer, one order, one order line, one patient visit, or one sensor reading. Many data-quality failures result from confusing these grains.

A key is an attribute or combination of attributes that identifies a row. A primary key should be unique and non-missing. A foreign key refers to a key in another table. A composite key uses multiple columns, such as `(student_id, course_id, semester)`.

Before joining or aggregating data, an analyst should ask:

• What does one row represent?
• Which columns identify that unit?
• Are keys unique where they should be?
• What is the time period and population covered?
• Are units and definitions consistent across sources?

These questions are more important than the syntax of any individual R function.

2. Core Tidyverse Operations

2.1 Selecting and filtering

`select()` chooses columns, while `filter()` chooses rows. For example:

```r

library(tidyverse)

sales_small <- sales |>

select(order_id, product_id, order_date, quantity, unit_price) |>

filter(quantity > 0, unit_price >= 0)

```

Conditions in `filter()` are interpreted as logical expressions. Multiple comma-separated conditions are combined with logical AND. The operators `|` and `!` represent OR and NOT. Missing logical values generally do not satisfy a filtering condition, so missingness should be handled explicitly when required.

2.2 Creating and transforming variables

`mutate()` creates or modifies columns. Derived variables should have clear names and documented units.

```r

sales <- sales |>

mutate(

revenue = quantity * unit_price,

order_date = lubridate::ymd(order_date),

product_id = stringr::str_to_upper(stringr::str_trim(product_id))

)

```

The arithmetic is meaningful only if quantity and price refer to compatible units and if discounts, taxes, returns, and currency conversion have been considered. A computed column can be syntactically correct but conceptually invalid.

`case_when()` is useful for rule-based classification:

```r

sales <- sales |>

mutate(

order_type = case_when(

revenue >= 1000 ~ "large",

revenue >= 100 ~ "medium",

revenue >= 0 ~ "small",

TRUE ~ "invalid"

)

)

```

The order of conditions matters because the first matching condition is used. An explicit final condition is advisable so that unexpected values are not silently converted to missing values.

2.3 Grouping and aggregation

`group_by()` defines groups and `summarise()` reduces each group to one row.

```r

monthly_sales <- sales |>

mutate(month = lubridate::floor_date(order_date, "month")) |>

group_by(month, product_id) |>

summarise(

units = sum(quantity, na.rm = TRUE),

revenue = sum(revenue, na.rm = TRUE),

orders = n_distinct(order_id),

.groups = "drop"

)

```

The use of `na.rm = TRUE` must be justified. If all observations in a group are missing, `sum(..., na.rm = TRUE)` returns zero, which may incorrectly imply that no units were sold. A safer approach may retain a missing result when no valid observations exist:

```r

summarise(

units = if (all(is.na(quantity))) NA_real_ else sum(quantity, na.rm = TRUE)

)

```

Aggregation also changes the grain. The resulting table no longer represents individual order lines; it represents product-month groups. This change must be documented.

3. Reshaping Data with `tidyr`

3.1 Long and wide forms

Long form stores repeated measurements in rows. Wide form stores repeated categories as columns. Long form is generally preferable for grouped analysis and visualisation; wide form is sometimes required for reporting or particular modelling procedures.

Suppose a table contains columns `student_id`, `math`, `science`, and `history`. Convert it to long form using:

```r

scores_long <- scores |>

pivot_longer(

cols = c(math, science, history),

names_to = "subject",

values_to = "score"

)

```

The reverse operation is:

```r

scores_wide <- scores_long |>

pivot_wider(

names_from = subject,

values_from = score

)

```

`pivot_wider()` requires a unique combination of identifying columns and `names_from` values if each output cell is expected to contain one value. If multiple values exist, the analyst must specify an aggregation function, such as `values_fn = mean`, but should first investigate why duplicates exist. Aggregating silently can hide a grain error.

3.2 Separating and uniting columns

A compound field can be separated when its format is reliable:

```r

data |> separate(period, into = c("year", "quarter"), sep = "-")

```

Columns can be combined with `unite()`:

```r

data |> unite("student_term", student_id, term, sep = "-")

```

String operations should account for whitespace, case, punctuation, encoding, and unexpected formats. Regular expressions are powerful but can produce false matches if rules are not tested against representative data.

3.3 Nested and list-column workflows

For advanced analyses, `nest_by()` or `group_nest()` can create one row per group with a list-column containing subgroup data. This supports group-specific models and quality checks. However, list-columns increase complexity and should be used when their analytical benefit outweighs reduced transparency for general users.

4. Joining Data Safely

4.1 Join types

A join combines tables according to matching key values. Common joins are:

• `left_join(x, y)`: retains every row of `x` and adds matching columns from `y`;
• `inner_join(x, y)`: retains only rows with matches in both tables;
• `right_join(x, y)`: retains every row of `y`;
• `full_join(x, y)`: retains all rows from both tables;
• `semi_join(x, y)`: retains rows of `x` having a match in `y`, without adding columns;
• `anti_join(x, y)`: retains rows of `x` having no match in `y`.

A join is not automatically one-to-one. If a key appears twice in the right table, one row in the left table can become multiple rows. Modern versions of `dplyr` support relationship declarations such as `relationship = "many-to-one"`, which can make assumptions explicit.

```r

orders |>

left_join(customers, by = "customer_id", relationship = "many-to-one")

```

The declared relationship should reflect the data model, not merely suppress a warning.

4.2 Join diagnostics

Before joining, examine key uniqueness:

```r

customers |>

count(customer_id) |>

filter(n > 1)

```

After joining, compare row counts and inspect unmatched keys:

```r

orders |> anti_join(customers, by = "customer_id")

```

A left join is appropriate when the left table defines the desired population. An inner join can unintentionally remove records and bias analysis, especially when missing matches are associated with geography, income, language, or another substantive characteristic.

5. A Data-Quality Framework

Data quality is multidimensional. Common dimensions include:

• Accuracy: closeness to the true or accepted value.
• Completeness: presence of required values or records.
• Consistency: agreement across fields, sources, and time.
• Validity: conformity to permitted formats, ranges, and categories.
• Uniqueness: absence of unintended duplicate entities or events.
• Timeliness: availability and currency for the intended use.
• Integrity: preservation of relationships among records and systems.

Quality must be evaluated relative to purpose. A dataset can be complete but inaccurate, accurate but untimely, or internally consistent but systematically biased.

5.1 Validation rules

Validation rules should be explicit, testable, and classified by severity. Examples include:

```r

quality_report <- tibble(

rule = c("order_id_not_missing", "quantity_nonnegative", "valid_status"),

failures = c(

sum(is.na(sales$order_id)),

sum(sales$quantity < 0, na.rm = TRUE),

sum(!sales$status %in% c("new", "paid", "cancelled"), na.rm = TRUE)

)

)

```

A rule should specify its scope, expected result, treatment of exceptions, and owner. Tools such as `pointblank`, `validate`, `testthat`, and database constraints can help automate checks. Data-quality tests should be executed in pipelines rather than only at the end of a project.

6. Missing Values

Missingness may represent non-response, unavailable measurement, not-applicable status, system failure, suppression for privacy, or an unrecorded zero. These meanings must not be collapsed without consideration.

In R, `NA` represents missingness. Empty strings, values such as `"unknown"`, and special codes such as `-99` are not automatically treated as `NA`. During import, define them deliberately:

```r

readr::read_csv("file.csv", na = c("", "NA", "Unknown", "-99"))

```

Three broad missingness mechanisms are commonly discussed:

• MCAR: missingness is unrelated to observed and unobserved values;
• MAR: missingness depends on observed variables after conditioning;
• MNAR: missingness depends on unobserved or missing values themselves.

These are assumptions about a data-generating process, not properties that can usually be proven from the observed table alone. Complete-case analysis is simple but can reduce sample size and introduce bias. Imputation may preserve power, but it introduces assumptions and uncertainty. Mean imputation often distorts variance and relationships and should not be treated as a general solution.

For a missing-value audit:

```r

sales |>

summarise(across(everything(), ~ sum(is.na(.)))) |>

pivot_longer(everything(), names_to = "variable", values_to = "missing_n")

```

Missingness indicators may be analytically useful when the fact that a value is missing carries information. Imputed values should be distinguishable from observed values, and sensitive analyses should compare plausible treatments.

7. Duplicates and Record Linkage

Duplicates may be exact copies, repeated transactions, multiple representations of the same entity, or legitimate repeated events. Removing duplicates mechanically can destroy valid observations.

Exact duplicates can be inspected with:

```r

duplicated_rows <- data |> filter(duplicated(.))

```

Key-based duplicates are assessed with `count()`:

```r

data |> count(customer_id) |> filter(n > 1)

```

When deduplicating entities, define a survivorship rule, such as retaining the most recent verified record. The rule should be deterministic and auditable. Fuzzy matching can support record linkage when names or addresses differ, but similarity thresholds create false matches and missed matches. High-impact linkage should include manual review or probabilistic methods with documented error assessment.

8. Outliers and Measurement Errors

An outlier is an observation unusually distant from others under a specified criterion. It is not automatically an error. A high-value transaction may be a genuine event, while a misplaced decimal point may create an erroneous value that is not extreme in a large dataset.

A common exploratory rule uses the interquartile range (IQR). Let Q1 be the first quartile and Q3 the third quartile. The IQR is:

IQR = Q3 − Q1

The conventional Tukey fences are:

Lower fence = Q1 − 1.5 × IQR

Upper fence = Q3 + 1.5 × IQR

These are screening thresholds, not universal definitions of error. They are most useful for roughly continuous distributions and can flag legitimate skewed observations. In grouped data, thresholds may need to be calculated within relevant populations, such as product or region.

```r

sales |>

summarise(

q1 = quantile(revenue, .25, na.rm = TRUE),

q3 = quantile(revenue, .75, na.rm = TRUE)

) |>

mutate(

iqr = q3 - q1,

lower = q1 - 1.5 * iqr,

upper = q3 + 1.5 * iqr

)

```

Other approaches include robust z-scores, domain limits, time-series residuals, and multivariate distance measures. Winsorisation, deletion, transformation, and capping should be selected according to the measurement process and analytical objective. Every intervention should be recorded; otherwise, reproducibility and auditability are lost.

9. Inconsistent Definitions and Units

Many severe quality problems are semantic rather than syntactic. Two systems may both contain a field called `revenue` while one includes tax and the other excludes it. A customer may be defined as a registered account in one system and as a purchaser in another.

A data dictionary should specify:

• name and business definition;
• data type and unit;
• valid range and permitted categories;
• grain and key;
• source and responsible owner;
• time zone and temporal meaning;
• treatment of missing and suppressed values;
• transformation history.

Units should be standardised before arithmetic. Dates require attention to locale, time zone, calendar system, and whether a timestamp represents event time or processing time. Category values should be mapped through controlled reference tables rather than ad hoc string replacement.

10. A Reproducible Quality Workflow

A defensible workflow is iterative:

1. Profile: inspect structure, types, ranges, distributions, keys, and missingness.

2. Specify: define the intended grain, data dictionary, quality rules, and acceptable exceptions.

3. Standardise: parse dates, harmonise names, convert units, and normalise categories.

4. Reshape: place data in an analytical structure appropriate to the task.

5. Integrate: join sources after key and relationship checks.

6. Validate: execute field-level, row-level, cross-field, and cross-table rules.

7. Investigate: distinguish errors from legitimate variation and document decisions.

8. Transform or correct: modify only with justified, reproducible procedures.

9. Assess impact: compare distributions, counts, model results, and subgroup effects before and after treatment.

10. Monitor: retain quality metrics and rerun tests as new data arrive.

Version control, code review, stable package environments, and immutable raw data improve reproducibility. A cleaned dataset should never overwrite the original source. Instead, preserve raw, intermediate, validated, and analytical layers.

11. Illustrative Case: University Student Success Data

Background

A university combines admissions, learning-management-system, examination, and fee-payment data to study first-year retention. The admissions table has one row per application, the learning table has one row per course activity, and the examination table has one row per student-course assessment.

Problem

An initial join produces more students than expected, average marks appear unusually low, and some students have no recorded programme. The project team initially suspects missing data but has not defined the grain or the meaning of key fields.

Analysis

The team first identifies `student_id` as the entity key, but discovers that the admissions table contains multiple applications per student and that the examination table contains several assessments per course. A direct join therefore multiplies rows. They also find that one source uses percentage marks, another uses a 0–4 grade-point scale, and a status value of `"withdrawn"` is represented as a missing mark in the examination extract.

Missing programme values are concentrated among late transfers, so they are not plausibly MCAR. Examination records with missing marks represent non-participation rather than zero performance. Finally, several student identifiers differ only by leading zeros.

Approach and solution

The team documents the grain of every table, pads identifiers consistently, and creates a student-level admissions table using a documented rule: the active application at enrolment date is retained. Examination data are first aggregated to the student-course level, with assessment rules specified by the academic registry. Marks are converted only after confirming the official scale and are not converted for withdrawn students. Reference tables harmonise programme codes.

They use `anti_join()` to identify unmatched students and `count()` to inspect duplicate keys. Validation tests check that marks are within the permitted scale, that enrolment dates precede examination dates, and that each active student has at most one programme assignment. A quality report is delivered alongside the analytical dataset.

Outcome

The apparent excess of students was caused by many-to-many joins, not by additional enrolments. The low average resulted partly from treating withdrawals as zeros. After correction, the retention analysis is based on the correct student-level population, while unresolved cases remain explicitly flagged.

Learning points

The case demonstrates that data quality is inseparable from data modelling. Correct syntax cannot compensate for an incorrect grain, undefined semantics, or inappropriate treatment of missingness. It also shows why quality checks should be conducted before, during, and after integration.

12. Governance, Ethics, and Contemporary Practice

Cleaning decisions can affect admission, credit, employment, healthcare, and public-service outcomes. Deleting records may disproportionately remove people with unstable connectivity, nonstandard names, rural addresses, or limited access to services. Imputation can conceal uncertainty, while outlier removal can suppress legitimate minority behaviour.

Organisations should apply data minimisation, purpose limitation, access control, retention rules, and privacy-preserving practices. Personally identifiable information should be separated from analytical identifiers where feasible. Data-quality logs must avoid exposing sensitive values unnecessarily.

Automated and AI-assisted data cleaning can suggest mappings, detect anomalies, or generate transformation code. Such systems may reproduce historical bias, infer sensitive attributes, or make plausible but unsupported corrections. Human review, provenance, validation, and reproducibility remain necessary. Contemporary data contracts increasingly specify schemas, ownership, freshness expectations, and quality tests between data producers and consumers. Metadata catalogues and observability systems extend quality management from a one-time project activity to an operational capability.

Challenges and Limitations

No cleaning procedure can recover information that was never collected. Quality dimensions can conflict: increasing completeness may reduce accuracy if uncertain values are filled automatically. Rules that work for one population may be inappropriate for another. Outlier thresholds are distribution-dependent, and missing-data assumptions are difficult to verify. Joins may create subtle bias when unmatched records are systematically different. Finally, highly automated pipelines can make decisions opaque unless transformations, exceptions, and approvals are recorded.

Best Practices

• Establish grain, keys, definitions, and units before transformation.
• Preserve immutable raw data and maintain an audit trail.
• Profile data before choosing cleaning actions.
• Treat missingness as information about the collection process.
• Validate joins and declare expected relationships.
• Separate detection, investigation, correction, and approval.
• Prefer domain rules to arbitrary statistical thresholds when reliable domain rules exist.
• Quantify the effect of cleaning on distributions and decisions.
• Test quality rules automatically and monitor them over time.
• Assess subgroup impacts and protect privacy throughout the workflow.

Chapter Summary

Data wrangling converts heterogeneous data into a structured, analysable form, while data quality management ensures that the resulting data are fit for purpose. Tidy data principles clarify rows, columns, values, and grain. Tidyverse functions support selection, filtering, transformation, aggregation, reshaping, and integration, but safe use depends on understanding keys and relationships. Missing values, duplicates, outliers, inconsistent definitions, and measurement errors require investigation rather than automatic deletion. Quality frameworks should address accuracy, completeness, consistency, validity, uniqueness, timeliness, and integrity. Reproducible pipelines preserve raw data, document decisions, automate validation, and monitor quality. Ethical practice requires attention to bias, privacy, uncertainty, and the consequences of cleaning decisions.

Key Terms

Tidy data: Data arranged so each variable is a column, each observation a row, and each value a cell.

Grain: The real-world unit represented by one row.

Primary key: A field or field combination that uniquely identifies a record.

Foreign key: A field referring to a key in another table.

Data wrangling: The process of restructuring, transforming, combining, and preparing data.

Data quality: The fitness of data for a defined purpose.

Completeness: The extent to which required data are present.

Validity: Conformity to permitted formats, ranges, or categories.

Consistency: Agreement across fields, sources, and time.

Missingness: The condition in which an expected value is unavailable or unrecorded.

MCAR: Missing completely at random.

MAR: Missing at random conditional on observed information.

MNAR: Missingness related to unobserved or missing information.

Outlier: An observation unusually distant under a specified criterion.

IQR: The difference between the third and first quartiles.

Join: An operation that combines tables using matching keys.

Data dictionary: Documentation of definitions, types, units, and rules.

Provenance: A record of data sources and transformations.

Data contract: An agreed specification of schema, meaning, ownership, and quality expectations.

Record linkage: The process of determining whether records refer to the same entity.

Review Questions

Short Answer Questions

1. What is meant by the grain of a dataset, and why is it important before joining tables?

2. Distinguish `pivot_longer()` from `pivot_wider()`.

3. Name four dimensions of data quality.

4. Why should missing values not automatically be replaced with zero?

5. What is the purpose of an `anti_join()` in a quality workflow?

Long Answer Questions

1. Explain the tidy-data principle and discuss three common violations with suitable R strategies.

2. Describe a reproducible workflow for identifying and managing data-quality problems.

3. Explain how many-to-many joins can distort analysis and how an analyst can diagnose them.

4. Compare deletion, imputation, and explicit missingness indicators as responses to missing data.

5. Discuss why an outlier should not automatically be considered an error.

Analytical / Application Questions

1. A customer table contains duplicate customer identifiers and an order table contains unmatched identifiers. Design an R-based diagnostic and remediation plan, including the checks you would perform before and after joining.

2. A hospital records temperature in Celsius for one year and Fahrenheit for another, with some values entered as text. Propose a transformation and validation strategy that addresses units, parsing, plausible ranges, and auditability.

3. After cleaning a loan dataset, approval rates rise substantially for one demographic group but fall for another. Explain how you would investigate whether the change reflects improved data quality, selection effects, or discriminatory cleaning decisions.

Multiple Choice Questions with Answers

Question 1

Which condition best describes tidy data?

A. Each variable is stored in a separate table

B. Each observation is a row and each variable is a column

C. Each subject is represented by one spreadsheet

D. Repeated measurements are always stored in separate columns

Correct Answer: B. Each observation is a row and each variable is a column

Question 2

Which function retains all rows from its left-hand table and matching columns from the right-hand table?

A. inner_join()

B. full_join()

C. left_join()

D. semi_join()

Correct Answer: C. left_join()

Question 3

What is the principal danger of an unexamined many-to-many join?

A. It converts all numeric values to character values

B. It can multiply rows and distort aggregates

C. It automatically removes unmatched rows

D. It prevents any missing values from occurring

Correct Answer: B. It can multiply rows and distort aggregates

Question 4

Which operation converts subject columns such as math and science into a subject column and a score column?

A. pivot_longer()

B. pivot_wider()

C. separate()

D. unite()

Correct Answer: A. pivot_longer()

Question 5

If Q1 = 10 and Q3 = 18, what is the upper Tukey fence using 1.5 times the IQR?

A. 20

B. 22

C. 30

D. 36

Correct Answer: C. 30

Question 6

Which statement about MCAR is correct?

A. Missingness depends on the unobserved value itself

B. Missingness is unrelated to observed and unobserved values

C. Missingness always occurs in the response variable

D. Missingness can be safely replaced by zero

Correct Answer: B. Missingness is unrelated to observed and unobserved values

Question 7

What does an anti-join generally help identify?

A. Rows with no matching key in another table

B. Duplicate columns within one table

C. The mean of each numeric group

D. The first quartile of a variable

Correct Answer: A. Rows with no matching key in another table

Question 8

Why is sum(x, na.rm = TRUE) potentially problematic when all values of x are missing?

A. It returns a character string

B. It returns the maximum value

C. It returns zero, which may be mistaken for a true zero

D. It always produces an error

Correct Answer: C. It returns zero, which may be mistaken for a true zero

Question 9

Which item belongs in a data dictionary?

A. Only the file size

B. Variable definitions, units, and valid categories

C. Only the analyst's preferred colour palette

D. The number of installed R packages

Correct Answer: B. Variable definitions, units, and valid categories

Question 10

Which is the most defensible response to a suspected outlier?

A. Delete it immediately

B. Replace it with the mean without documentation

C. Investigate whether it is valid or erroneous using context and documented rules

D. Always cap it at the median

Correct Answer: C. Investigate whether it is valid or erroneous using context and documented rules