Data wrangling: quality, cleansing and understanding data

Five words for good data, four reasons they are not enough

Before you can fix data you need a word for "good". The slide "Conventional Definition of Data Quality" (p. 1069) gives five, each a property of how the data was recorded. Read each next to a defect it rules out, in a contact list like the one of the hook.

"Granted", in the timeliness line, means guaranteed: up-to-date records must also refer to the same moment, or comparing them makes no sense — the Monday and Friday columns above.

The next slide, "Problems …" (p. 1070), takes the definition apart in four words:

• Unmeasurable — "Accuracy and completeness are extremely difficult, perhaps impossible to measure." To check a value you need the true one, and if you had it you would have stored it; a record that was never entered leaves no trace to count. Accuracy can only be measured against something outside the data — remember this when the correcting step reaches for "secondary data sources".
• Context independent — "No accounting for what is important. E.g., if you are computing aggregates, you can tolerate a lot of inaccuracy." An average over a million rows can be fine on data that would be useless for billing one customer. In statistical terms: independent errors of standard deviation \(\sigma\) shrink to \(\sigma/\sqrt{n}\) in the mean of \(n\) records, but an error that every record shares — a bias — does not shrink at all.
• Incomplete — "What about interpretability, accessibility, metadata, analysis, etc." Five words say nothing about whether a person can read the data, reach it, or know what a field means.
• Vague — "The conventional definitions provide no guidance towards practical improvements of the data." Knowing that data should be accurate does not tell you how to make it so. The rest of the deck is the answer: a procedure.

Data quality, conventionally, is accuracy, completeness, uniqueness, timeliness and consistency — a definition that is unmeasurable, context independent, incomplete and vague.

When data is wrong: three bad reactions and a vicious cycle

The first part of the deck, "When Data Is Wrong", opens with three Dilbert strips (pp. 1073–1075), one per wrong reaction to bad data. In each, a manager holds data that nobody trusts.

The third strip is the subtle one. Averaging cancels only random errors that point in different directions; two sources that are wrong in the same way stay wrong on average — the bias of the previous section. Combining sources needs a model of their errors, which source is right about what and by which rule, and the cleansing steps below are that model.

Then the deck states the goal: "Goal: Better Faster Cheaper!" (p. 1076). A bar chart in millions of US dollars per year (no legend, illustrative values) leads, through an arrow labelled "Insights", to a "Better Marketing Campaign" and from there to "$$$". Good data feeds a chain that ends in money. The next slide runs the same chain on bad data and closes it into a ring, "The Vicious Cycle of Bad Data" (p. 1077):

Bad Data → Incorrect Analysis → Invalid Insights → Wrong Decisions → Poor Outcomes → Bad Data

Each arrow is a consequence. An analysis of bad data is incorrect however good the method; insights drawn from it are invalid; decisions taken on them are wrong; the outcomes are poor. Beside the ring a chart of "Revenue(million)" falls from 8 to 4 to 2 to 1 between 2013 and 2016 — values read off the chart, an illustration rather than a real company — under the line "Data Quality is an issue…".

Why a ring and not a chain? The slide draws the closing arrow but does not explain it. A reading of ours, in two parts: outcomes are recorded and become the data of the next round — a campaign aimed at the wrong customers produces response data about the wrong customers. And poor outcomes cost trust, budget and attention — the care that would fix the data. So a better analysis cannot break the ring: the only lever is the data. The widget lets you pull it.

The section ends on two authorities (p. 1078). A "Gartner Report" forecast: "By 2017, 33% of the largest global companies will experience an information crisis due to their inability to adequately value, govern and trust their enterprise information." The crisis is not a lack of data but a lack of trust — and 2017 is past, so read the sentence as a prediction, not a measurement. Then a quotation: "If you torture the data long enough, it will confess to anything", signed "Darrell Huff".

The warning stands whoever said it. Wrangling changes data, and a change made to reach a wanted answer is torture. Every step that follows is justified by something other than the answer: a business rule, a reference, a second source.

Bad data feeds itself — Bad Data, Incorrect Analysis, Invalid Insights, Wrong Decisions, Poor Outcomes, more Bad Data — and only work on the data breaks the ring.

Making a wrong right: what data wrangling is

The second part of the deck, "Making a Wrong Right", opens with a definition (p. 1080):

Three parts. The input is "raw" — in quotes on the slide: data as it comes from the source, whatever its shape. The output can be analysed. And the purpose is insights that are valid — not the "Invalid Insights" of the ring — and actionable: they can become decisions.

Above the definition the slide draws the data-science pipeline as five chevrons (Fig. 1, left): Data Acquisition, Data Preparation, Data Analysis, Data Visualization, Results Interpretation. It marks none of them, but wrangling is the second: the next slide lists "Data Preparation" among its names (p. 1081), and the course notes say "The step after Data Acquisition and before Analysis in the data management flow is Data Wrangling" (§5.2.1). So it comes after acquiring the data — from web APIs or by scraping, for instance (lesson 34) — and before analysing it. Under the chevrons: "Data scientists spend more time on preparing data than on analyzing it". The slide gives no number; the same section of the notes does: "in average 80% of the time of a data scientist is spent cleaning data".

That next slide gives eight names for the same activity (p. 1081): Data Preprocessing, Data Preparation, Data Cleansing, Data Scrubbing, Data Munging, Data Transformation, "Data Fold, Spindle, Mutilate…" — a joke on the warning once printed on punched cards, "do not fold, spindle or mutilate" — and "(good old) ETL". The last name carries the message: wrangling is not new.

ETL, Extract-Transform-Load, gets a slide of its own (p. 1082): an "Information architecture pattern for datawarehouse solutions" and "Batch oriented data integration and delivery". Data is extracted from the operational sources, transformed into the warehouse's schema — and cleaned on the way — then loaded, in periodic batches rather than record by record as in a streaming pipeline. Lesson 4 has the course's longer definition, "Extract Transform Load (in the Big Data era)" (p. 175), and lesson 23 the warehouse it feeds. The a.k.a. slide's message is that wrangling is "(good old) ETL" under a newer name — in our reading, above all its T, where data is reshaped and cleaned.

Six steps, in a loop

"Data Wrangling Steps" (p. 1083) draws six boxes — Obtain, Understand, Explore, Transform, Augment, Shape — and writes beside them "Iterative process". The slide prints only the names; in one line each, our glosses:

• Obtain — get the raw material: CSV files, PDFs, API responses, scraped pages.
• Understand — learn what each field means: the last section of this lesson.
• Explore — profile the data: distributions, missing values, outliers, duplicates (lesson 36).
• Transform — parse, correct and standardize: the cleansing steps below.
• Augment — add data from other sources: the ZIP code that the correcting step adds, the phone number that a second file brings.
• Shape — lay the data out for the analysis, one observation per row (lesson 37).

Iterative means that loop: no data set is clean after one pass. The architecture deck has a shorter version of the same picture: wrangling "includes understanding, cleansing, augmenting and shaping data", four boxes instead of six (p. 160, lesson 3).

Data wrangling, a.k.a. "(good old) ETL", is the Data Preparation stage: an iterative process of obtaining, understanding, exploring, transforming, augmenting and shaping raw data until it can generate valid actionable insights.

Data cleansing, and why data is dirty

Inside wrangling the deck singles out one activity (p. 1084):

The first sentence works on whole records: detect, then correct — or, as the bracket allows, remove. The second works on parts of records: five kinds of defect (incomplete, incorrect, inaccurate, partial, irrelevant) and four remedies (replacing, modifying, filling in, deleting). Deleting is the last resort; the course notes add "(when it's just too noisy to be fixed)", and lesson 36 counts what dropping records costs.

Where does the dirt come from? "Why is Data 'Dirty'?" (p. 1085) lists eleven causes. The slide prints only the names; the examples are ours.

Read the list by kind. Some causes are bad values: dummy, absent, cryptic, contradicting. Some misuse the structure: multipurpose, shared and inappropriately used fields. Some break the organisation's own rules and keys: business rules, reused keys, non-unique identifiers. The last appears only when sources are merged. Dirty data is not just typos: it is a by-product of how data is entered, designed and combined — which is why it keeps coming back.

Data cleansing detects and corrects — or removes — corrupt or inaccurate records; data gets dirty through eleven causes, from dummy values to data integration problems.

Cleansing in practice: one record, five steps

"Data Cleansing in Practice" (p. 1086) is a list of five activities in a fixed order: Parsing, Correcting, Standardizing, Matching, Consolidating. Each has a definition slide and an example slide, and the examples follow one record — Beth Parker of the hook — from the label to the end. Step through them; the end of each step says why it stands where it stands.

1. Parsing — "Parsing locates and identifies individual data elements in the source files and then isolates these data elements in the target files." (p. 1087). The five lines of the label become ten named fields (p. 1088): First Name Beth, Middle Name Christine, Last Name Parker, Title SLS MGR, Firm Regional Port Authority, Location Federal Building, Number 12800, Street Lake Calumet, City Hedgewisch, State IL. No value changes: "Hedgewisch" and "SLS MGR" stay. Why first: every later step works on fields, and a field must exist before it can be fixed.

2. Correcting — "Corrects parsed individual data components using sophisticated data algorithms and secondary data sources." (p. 1089). Looked up in a postal reference, the address changes (p. 1090): Street becomes South Butler Drive, City becomes Chicago, and two fields are added, Zip 60633 and Zip+Four 2398. Why second: the reference is searched by number, street, city and state, so these must already be separate; and a secondary source is needed because a record cannot certify its own accuracy — the "unmeasurable" of p. 1070.

3. Standardizing — "Standardizing applies conversion routines to transform data into its preferred (and consistent) format using both standard and custom business rules, as well as coherent measurement units, …" (p. 1091). Four changes (p. 1092): a Pre-name Ms. is added; the "1st Name Match Standards" Elizabeth, Bethany, Bethel list the names Beth may stand for; SLS MGR becomes Sales Mgr.; South Butler Drive becomes S. Butler Dr. "Preferred" means the agreed convention — here the short postal form, not the longest one — and coherent units are the same idea for numbers: every weight in kilograms, every price in one currency. Why third: values in different formats never compare equal.

4. Matching — "Searching and matching records within and across the parsed, corrected and standardized data based on predefined business rules to eliminate duplications." (p. 1093). A second source holds Ms. Elizabeth Christine Parker-Lewis at 12800 S. Butler Dr., Suite 2, with a phone and a fax (p. 1095). Field by field: Elizabeth is among Beth's match standards, Parker is part of Parker-Lewis, the streets differ only by the suite, and firm, city and ZIP are identical — under a matching rule (the match patterns below), a duplicate. "Within and across" means inside one file and between files. Why fourth: the definition names its own input, "parsed, corrected and standardized data".

5. Consolidating — "Analyzing and identifying relationships between matched records and consolidating/merging them into ONE representation." (p. 1096). The result (p. 1097): Ms. Beth (Elizabeth) Christine Parker-Lewis, Sales Mgr., Regional Port Authority, Federal Building, 12800 S. Butler Dr., Suite 2, Chicago, IL 60633-2398, phone 708-555-1234, fax 708-555-5678. Why last: only records that were matched can be merged.

The slides print in red the values a step changed or added (pp. 1090, 1092) and, on the matching slide, the values on which the two sources differ (p. 1095). Fig. 2 sets the two sources next to the final record and shows where every value of the result came from.

Match patterns: a duplicate is a rule, not a feeling

How does a matcher decide? "Match Patterns" (p. 1094) is a table of rules for business records: six compared fields, a grade for each, a pattern code and an identifier.

Each row is one rule. The slide does not define the grades; read them as: Exact — identical after standardizing; VClose — very close, apart by an abbreviation, a nickname or a suite number; Close — similar but less so, like Parker and Parker-Lewis; Blanks — empty in at least one record, so the field is no evidence either way, which is not the same as a mismatch. The pattern spells the row with one character per field, in column order: A for Exact, B for VClose, C for Close, "-" for Blanks. The slide does not print this key; its rows force it.

The identifiers come in two families, P110–P120 and S300–S310, which the slide leaves unexplained. A reasonable reading, ours: P rules pass a pair automatically, S rules flag it as suspect for a person to decide — the S rows are the ones where an identifying field, Branch Type or Customer #/Tax ID, drops to Close. Why grade field by field instead of one overall similarity? Fields differ in weight — an exact tax ID is nearly proof, an exact city almost nothing — and a named rule can be explained, audited and changed by the business.

Back to the hook. The two rows were one person, but no comparison of the raw rows could show it: first name, surname, street and city all disagreed. Correcting gave the label the address the contact list already had, standardizing made Beth comparable to Elizabeth, and only then could a rule declare the match. The merged record keeps a title only the label knew and a phone only the contact list knew. Nothing was guessed, and nothing was thrown away.

Parse, correct, standardize, match, consolidate: each step makes the next one possible, and the number of records changes only at the last.

Understanding data: a structure nobody wrote down

The four slides that close this part of the deck are titled "Understanding Data" (pp. 1098–1101) — the Understand step of Fig. 1. Each shows data whose structure is not written in it and has to be recovered.

A PDF invoice

To a person a PDF invoice looks structured; to a program it is a drawing of text. The slide shows a "Tax Invoice" of "Musculinity @Last, Inc" and, below it, the flat table extracted from it (p. 1098):

The numbers pass a check: 4 × 29.95 = 119.80 and 2 × 9.99 = 19.98, together 139.78; 5 % of that is 6.99, and 139.78 − 6.99 = 132.79. The structure does not. The table has one row per line item, so every invoice field is repeated: sum the Total column and you get 265.58, twice the bill. The extraction left parsing work behind — the invoice number 000001 became 1, $119.80 became 119.8, and each address is still packed into one cell with | as separator. And 5/6/2016 is 6 May or 5 June depending on the convention: a job for standardizing. Extraction is where wrangling starts, not where it ends.

Free text in an image

"Python + Textract +Tesseract" (p. 1099): an image holds three typed lines and, under them, "data Wrangling" written by hand. The slide runs this — the call is one line on the slide, split here:

# OCR of the image: returns the text found in it
>>> text = textract.process(
...     '/Users/akuntamu/Downloads/Drawing3.png',
...     method='tesseract-ocr')
>>> text
'     \n   \n\nhow is it going?\nThis is pretty\ninteresting\n\n'

textract is a Python library that extracts text from many kinds of file; for an image it calls Tesseract, an engine for optical character recognition (OCR), which the slide names in the method argument. The typed lines come back with their line breaks. The handwriting is simply not there — and nothing in the output says that a line was lost. That is completeness, the dimension p. 1070 calls unmeasurable, in miniature.

A sentence, and a secret message

The third slide sets a sentence labelled UNSTRUCTURED — "Looks like my V8 Chevy is running low on fuel. Didn't I fill up just the day before?" — above a row labelled STRUCTURED (p. 1100). Ask of each cell where it came from:

Two cells are quoted, one is measured, one is resolved against a date the sentence does not contain, one is inferred and one comes from knowing who speaks. Understanding mixes all of these, and each cell is only as reliable as its evidence.

The last slide is a puzzle (p. 1101): "Decode the following secret message:" DALDFWSFOEWRBOSDCALAXORDJFKMCO.

All four slides make one point. The structure is not in the bytes: it is knowledge you bring — a layout, a dictionary, a date, a sensor. That is why "metadata" is on the list of what the conventional definition forgot (p. 1070), and why Understand comes right after Obtain.

Understanding data means recovering a structure that is not written in the data — and what extraction produces is dirty data in its turn, to be parsed, standardized and shaped.