Data wrangling vs data cleaning: a practical workflow

Data cleaning fixes problems in the data itself: duplicates, missing values, invalid dates, inconsistent categories, and impossible numbers. Data wrangling changes the structure so the data can be analysed: reshaping, joining, splitting columns, deriving fields, and aggregating to a useful grain.

The two overlap in practice, and some tasks sit on the boundary. Splitting a combined address column is wrangling; correcting a misspelt city is cleaning. The distinction is useful because it clarifies what a transformation is for and who should own it.

Cleaning starts with profiling. Count nulls, distinct values, duplicates, and out-of-range entries. Check date formats, currency symbols, negative quantities, and categories that appear only once. The goal is to make each field trustworthy before it is used in a join or a total.

Not every anomaly should be changed. An outlier may be a real large order, and a missing value may mean 'not yet known'. Decide the rule, record the exception, and keep the raw data intact. Cleaning should make uncertainty visible, not hide it. A short list of accepted exceptions is more useful than a perfectly clean file that nobody can explain.

Wrangling turns source data into the table shape the analysis needs. Monthly columns may need unpivoting into rows; first and last names may need combining; several systems may need joining on a shared key; transactions may need aggregating to a customer-month grain.

Good wrangling is driven by the target dataset, not by whatever transformation is convenient. Sketch the columns and grain first, then work backwards. That prevents a wide, messy output that still needs reshaping before it can be charted.

Profile the sources, define the target grain, and identify the fields that must be clean before joining. Standardise keys and dates first, because those cause the most failed matches. Then reshape, join, derive, and aggregate. Validate after each stage rather than at the end.

The order is not fixed. A join can expose duplicate customers that were invisible in either source, sending the work back to cleaning. Treat the workflow as a loop: profile, transform, validate, fix, document. Keep the raw extract unchanged so the process can be rerun. A short validation query after each join is usually faster than debugging a wrong total at the end.

Record the rules in plain language: how duplicates were resolved, which rows were excluded, how missing values were treated, and why. Note the source extract date and the expected row count. The next person should be able to rerun the process without reverse-engineering a notebook.

Assign an owner for the dataset and for the rules. Cleaning and wrangling decisions are business decisions, not just technical ones. A short data dictionary and a version-controlled script are usually more valuable than a perfectly clever transformation.

An online retailer exports orders with one row per line, a separate customer file, and monthly sales columns. The order file contains duplicate rows from a failed export, dates in two formats, and product names that differ by case.

Cleaning removes the duplicate export, standardises dates and product labels, and resolves missing customer IDs. Wrangling unpivots the monthly columns, joins the customer file on a validated key, and aggregates to customer-month. Validation checks row counts, unmatched customers, and revenue against the platform total. The order is not ceremonial; each step makes the next one safer.