Not all tables are created equal: tidy data, keys, duplicates and tools

Before the table: what kind of value, from which source

Wrangling starts before any table exists, with two questions. What kind of value is this? And should I trust the place it comes from? The slides answer the first with a bunch of balloons (p. 1130).

Read the bunch as five facts about one object. Its colour is categorical: a label taken from a set of possible values. "Nice, Good, For birthday" are qualitative, and the slide adds "Subjective": an opinion that depends on who gives it. "6 balloons", "Pressure 15 PSI" and "139 m. over sea level" are quantitative. The first is discrete, a count with gaps between its values; the other two are continuous, measurements on a scale limited only by the instrument.

Why sort values at all? Because the type decides which cleaning makes sense. You can average a pressure, not a colour. "6.5 balloons" is a glitch, "15.3 PSI" is not. "Nice" needs text handling, not statistics. And only a quantitative value can sit outside the whiskers of a boxplot, as an outlier (lesson 36).

The second question gets twelve words on p. 1131, the data source selection criteria. The slide prints them in two columns without comment. The table groups them by the question each one asks; the grouping and the questions are ours, the words are the slide's.

Suppose you need hit counts per film category and quarter, the data of the next section. A chart scraped from a fan site is accessible, free and current, but its credibility and verifiability are low and its legal status is unclear (scraping is lesson 34). An industry feed is credible and verifiable, but it costs money. No source wins on all twelve. The list makes the trade-off explicit before you spend a week cleaning the wrong data.

Observations and variables: the shape of a table

Page 1132 shows the same six numbers in two tables: the student counts of two schools in three years.

The slide labels the first table with the words of a spreadsheet, Column and Row, and the second with the words of the data, Variable and Observation. That difference is the whole lesson.

In the wide table the variable year has no column. Its values 2012, 2013 and 2014 are column names, and each row carries three observations. In the long table each variable is one column and each row one observation. Same information, different shape — and the shape decides which questions are easy.

Page 1133 makes it worse. Its header Comedy-Q1 glues two values into one name, a category and a quarter, and the "…" promises Comedy-Q2, Thriller-Q2 and more. Here is the hook's question against the wide table and against the long table of the same slide, Category | Quarter | Year | #Hits:

-- wide table: returns 2
-- (the one comedy column of Q1)
SELECT "Comedy-Q1"
FROM hits
WHERE Year = 2014;

-- long table: returns 2
-- (one filter, one sum)
SELECT SUM("#Hits")
FROM hits
WHERE Category = 'Comedy'
  AND Year = 2014;

Both return 2 today. Only the second is still right when Q2 arrives. The first must be rewritten for every new quarter, because what it filters on lives in the column names. The slide's second note, "Very easy to add a new column", says the same from the other side: a new measure — say the number of movies released, which its "% of hit comedy movies" question would need — is one more column in the long table, but a whole block of new columns in the wide one.

The repair has a name. Melt (also called pivot longer) turns the headers that are values into a column, one row per old cell. Its inverse, pivot (pivot wider), spreads the values of a column into headers. In pandas, which p. 1143 lists among the wrangler's tools, the melt and the split of the glued header are two lines; R's tidyr (p. 1149) calls the moves pivot_longer and pivot_wider.

# wide -> long: 6 rows,
# one per (Year, old header)
long = wide.melt(
    id_vars="Year",
    var_name="Header",
    value_name="#Hits")

# split the glued header in two
long[["Category", "Quarter"]] = (
    long["Header"]
    .str.split("-", expand=True))

Page 1134 holds the messiest case, "Variables in both rows and columns". The quarters run across the headers (Q1, Q2, Q3, …) and the rating runs down the rows: an Excellent row and a Good row for each category. One observation, a category in a quarter, is smeared over two rows and several columns. The slide's repair melts the quarters into a Quarter column and lifts the two ratings into two count columns, so that "Each row is complete observation".

A value never belongs in a header: when it sits there, new data means new columns, and every query that names them must be rewritten.

One kind of thing per table

The two remaining messy shapes are about observational units — the kind of thing one row describes. Page 1135 bills two customers. Its first table mixes two kinds of thing, invoices and invoice lines, so every fact about an invoice is repeated on each of its lines.

The slide's verdict is "Normalize to avoid duplication": one table per kind of observation, linked by the key Invoice. It is the normalization of lesson 9, met from the data side.

Why bother, if no number is lost? Because a fact stored twice can disagree with itself. An update that changes one copy — Jim Jones moves, the rate changes — leaves the other behind; lesson 9 calls it an update anomaly. Before reading on, check invoice 1 on the slide: two lines, 1 × 3.55 and 2 × 2.05, a Sales % of 8 and a Total($) of 8.03. Does the total agree with its lines?

The last shape is the opposite case: one kind of observation scattered over many tables, typically one file per period. Page 1136 labels a stack of tables "Multiple Tables Divided by Time" and asks to "Combine all tables accommodating varying formats". Two things make that union work. The formats must be aligned on the way — column order, column names, separators. And the period must become a column: in the union the file names disappear, so a value that lived only in a name ("Q2 2014") must first become a cell.

Same thing, different records: keys and duplicates

Combining sources raises a harder question than shape: which rows describe the same thing? Page 1137 puts it in four lines — "Keys are crucial in DB", "Many DBs --> Many keys", "How to align?", "Identification to certain degree of accuracy likely-identities" — with the example "same user match" over eight social-network logos.

Inside one database a key identifies a record exactly. Across databases there is no shared key: the same person is a numeric id on one network, a handle on another, an account name on a third. This is the key (matching) problem. With no key in common, records are compared on what they say — name, address, age, behaviour — and a pair similar enough becomes a likely identity. It is a decision that can be wrong, not a lookup. You met its rule-based form as the Matching step of cleansing, with its table of match patterns (p. 1094, lesson 35).

The duplicates problem of p. 1138 is the same question inside one data set: "Related to Key problem", again "Identification to certain degree of accuracy", now of "likely-duplicates", with the example "duplicate posts". A duplicate is a record that should share a key with another one and does not: the same post on four networks, the same customer registered twice with a typo. SELECT DISTINCT removes only identical copies; one letter of difference and it sees two different rows.

The widget imitates a real menu: "Cluster and edit…", visible under "Edit cells" in the OpenRefine screenshot of p. 1147. OpenRefine groups values that collide on a fingerprint or lie within an edit distance, and a person accepts or rejects each proposed merge (background from the OpenRefine documentation, not the slides). The widget's own merging rule is the single linkage of hierarchical clustering: two records closer than the threshold share a cluster, and closeness chains — A near B and B near C put all three together.

Why does the slide call matching and duplicates "crucial in data science"? Because they decide what you count. A missed match counts one customer twice; a false merge sends one person's purchases to another. In machine-learning terms: the same image left in both the training and the test split inflates the test accuracy.

Without a shared key, identity is a likelihood: every matching rule trades false merges against missed duplicates.

Pages 1140–1141, "Lessons Learnt on Tables", sum up everything so far in six lines. Learn them as printed:

Why the reader has to wrangle: schema on read

Why does a course about databases spend a lecture on cleaning tables? Page 1142 gives the reason in three lines: "Traditional DBMSs enforced writing only data consistent with a pre-designed schema"; "Today data modeling at design time is a luxury"; "In schema on read, data is applied to a plan or schema as it is pulled out of a stored location, rather than as it goes in".

With schema on write the database is a gatekeeper: a row that does not fit the declared table is refused, so whatever is stored has already been shaped and typed — by whoever loaded it. The cost is design time: someone modelled the data before it arrived. That is the luxury. With many sources that change faster than anyone can agree on a schema, nobody gets that time.

With schema on read everything is stored as it comes, and a schema is applied only when the data is read. No designed schema stood in the way of the wide comedy table, the invoice with its repeated totals or the buyer registered twice: they were stored as they came. So every reader inherits the work of this lesson — tidy the shape, align the keys, merge the duplicates — and of lesson 36: fill the holes, question the outliers. Wrangling itself is not new: lesson 35 met it as "(good old) ETL", done once, before the load. Schema on read moves it to the moment of reading, and to every reader. Lesson 4 shows the same shift as two pipelines (pp. 169–170).

The wrangler's toolbox

Pages 1143–1151 close the lecture with tools. Know them by name; more importantly, notice the one idea they share.

The open-source stacks of p. 1143 are R with RStudio; IPython with Python and pandas; OpenRefine; and the Data Science Toolkit. Page 1144 adds helpers for getting data out of awkward containers — BeautifulSoup for HTML, Tesseract and OCRFeeder for text in images, textract for text in any document — and two training communities, School of Data and Open Knowledge Labs. The commercial vendors of p. 1145 are Paxata, Platfora, Tamr, Datameer, Alteryx, Trifacta, Dataiku and Datawatch.

The Trifacta Wrangler screenshot of p. 1146 shows what such a tool adds to a spreadsheet. The data set raw-top-50-pop (50 rows, 5 columns, 3 data types) began as one block of text. Above every column the tool shows its type — # for rank, ABC for the strings, a clock for year — a histogram of its values and a summary: "1 - 50", "48 Categories", "50 Categories", "35 Categories", "2000 - 2009". The rough checks of exploration come for free. A dark mark at the end of the company column's bar flags a missing value (the tool's convention), and indeed row 14, Iron & Wine, has no company. The right pane is the real point: every operation that split the block into columns is a step of a script — splitrows, replace, delete row, split, extract, merge, drop, set, rename.

OpenRefine shows the same idea as an Undo / Redo history (p. 1147). Read it as a complete small job: scrape a web page, parse it, clean it, keep the result.

A recorded step can be undone, read by a colleague and replayed on next month's file; the "Extract…" and "Apply…" buttons above the history export it and apply it to another project. Recording is what turns munging — the "vague", "potentially lossy" transformations of p. 1102 (lesson 36) — into a documented, repeatable job.

Page 1148 turns the whole lecture into a checklist of five phases, and p. 1149 names the R libraries for it. The table puts them together; the slides' words are verbatim, the mapping of libraries to phases and the Python counterparts are ours.

The slide lists shaping before cleansing, and the order makes sense. Cleaning rules are written per variable — a range for a pressure, a format for a date — and a variable must be a column before a rule can be attached to it. And matching sits under augmenting: you match records in order to join sources.

The references of pp. 1150–1151 are a data-cleaning guide hosted by GBIF, a Webopedia entry, Witten and Frank's Data Mining, and the book of Dasu and Johnson. That book is titled Exploratory Data Mining and Data Cleaning (Wiley, 2003), not "Exploratory Data Mining and Data Quality … (Wiley, 2004)" as the slide prints. The tool links point to Stanford Wrangler — whose project page now lives at idl.uw.edu, since the slide's vis.stanford.edu address fails — to openrefine.org, okfnlabs.org and schoolofdata.org.

The hook, settled. The comedy query could not be finished because the quarter lived in the column names: the wide table breaks "Never values as columns!". One melt and one split turn it into Category | Quarter | Year | #Hits, where the query is one filter and one sum and survives every new quarter. And the one who melts is you. Under schema on read nothing stopped the wide table from being stored, so its reader must tidy it — best as a recorded step that runs again on next quarter's file.