Data Wrangling

Data rarely arrives in the shape that analysis or a downstream system needs. Names vary. Dates use several formats. Missing values carry different meanings. Two files may describe the same customer with different identifiers.

Data wrangling is the work of turning that source data into a defined, usable dataset. You inspect the source, reshape its structure, standardize representations, resolve quality problems, combine sources, and validate the result. You also preserve enough evidence for another person to understand what changed.

The goal is not to make data look clean. The goal is to make it fit for a stated purpose without hiding uncertainty.

Why the work starts with purpose

Quality depends on use. A dataset can be complete enough for a weekly trend report but unsafe for individual billing decisions. The UK Government Data Quality Framework defines quality as fitness for purpose and separates six dimensions: completeness, uniqueness, consistency, timeliness, validity, and accuracy.

Those dimensions answer different questions:

• Completeness: Are the required records and values present?
• Uniqueness: Does each real entity appear only as intended?
• Consistency: Do representations and related values agree?
• Timeliness: Is the data current enough for this use?
• Validity: Do values follow the expected type, format, and range?
• Accuracy: Do values match reality?

A valid value is not necessarily accurate. A date can match the required format while naming the wrong day. A complete column can contain incorrect values. State the intended use before choosing which problems to fix and which trade-offs to accept.

A dependable workflow

Treat wrangling as a sequence with explicit checks.

1. Preserve the source. Keep the original data unchanged and record where it came from.
2. Define the target. State the row meaning, required fields, data types, keys, and quality rules.
3. Profile before editing. Inspect row counts, types, missingness, value frequencies, ranges, and candidate-key uniqueness.
4. Normalize structure. Give each variable a stable field and each observation a consistent row shape.
5. Standardize values. Parse dates and numbers, normalize categories, trim accidental whitespace, and preserve meaningful distinctions.
6. Resolve records. Identify duplicates or alternate representations. Use evidence before merging entities.
7. Combine sources. Declare join keys and expected relationships. Check unmatched rows and unexpected row multiplication.
8. Validate the output. Re-run rules and reconcile counts against the source.
9. Document decisions. Record assumptions, rejected rows, mappings, and unresolved issues.

This sequence is iterative. Profiling after a transformation can expose a new source problem. Return to the relevant step, update the rule, and rerun the process.

Structure before values

A tidy table uses one variable per column and one observation per row. That shape makes transformations and checks easier to express. It does not mean every system must store data in one table. It gives you a predictable working representation.

Reshaping changes structure without changing the intended facts. For example, twelve month columns can become two columns named month and sales. Splitting a combined field such as city, state creates separate variables. Joining adds related fields from another table.

Always define the grain before reshaping or joining. Grain is what one row represents. A row may represent one customer, one order, or one order line. If you confuse those grains, a join can duplicate measures and produce plausible but false totals.

Missing values are information

Missing data is not one condition. A value may be unknown, not collected, not applicable, withheld, or lost during processing. Converting every case to zero destroys those distinctions.

First identify how the source represents missingness. Empty strings, sentinel numbers, and text such as N/A may all occur. Convert them to an explicit representation only after confirming their meaning. Then decide whether to keep, exclude, or fill a value according to the intended use.

Any fill rule is an assumption. Record it. Compare results before and after applying it. Never imply that a filled value was observed.

Duplicates require a definition

Exact duplicate rows are easy to detect. Duplicate entities are harder. Two people may share a name, while one person may have several valid addresses. Define the entity and candidate key before removing records.

OpenRefine clustering can group strings that may be alternate representations of the same value. Its documentation warns that clustering works at the syntactic level. Similar spelling is evidence for review, not proof of identity.

Choose a surviving record with an explicit rule. A timestamp, source priority, or completeness score can support the choice. Keeping the first row merely because it appears first is rarely a business rule.

Joins are assertions

A join asserts that keys connect records across datasets. State whether you expect one-to-one, one-to-many, many-to-one, or many-to-many relationships. Then test that expectation.

Inspect unmatched keys from both sides. Compare row counts before and after. A larger output may be correct for a one-to-many join, but it is a warning when you expected one-to-one. pandas provides merge validation for checking expected key relationships and an indicator for showing each output row's source.

Validation is not accuracy

Validation checks whether data follows declared rules. Examples include a required field, an allowed category, a unique key, or a date within a permitted range. These checks establish validity, completeness, uniqueness, and some forms of consistency.

They do not automatically establish accuracy. Confirming accuracy usually requires a trusted reference, source-system check, or real-world observation. Keep that boundary visible in reports.

Limits and judgment

Wrangling cannot repair a source that never captured the required fact. It cannot infer identity safely from weak similarities. It cannot remove bias merely by standardizing formats. It also cannot make undocumented transformations trustworthy.

Automate repeatable rules, but keep review points for ambiguous cases. Improve problems at their source when possible. A permanent collection or validation fix is better than applying the same correction downstream forever.