Every M5–M6 operation, tagged

The column that has five spellings of two provinces

A branch sends its January orders as a sheet typed by hand. The prov column reads Ont., ontario , ON, QC, Que. — five values for two provinces, one with a trailing space, one with a leading space. A COUNTIF by province on this column undercounts Ontario by two-thirds and a pivot shows five rows. The fix is not to retype the cells; it is a mapping table that every future import can reuse.

Worked example — find, then map

Find the variants with a frequency table. In Sheets =UNIQUE(A2:A6) lists the distinct values and a COUNTIF beside each counts them; in R, table() does both:

> table(x)
x
    Que.       ON     Ont. ontario       QC 
       1        1        1        1        1 

Five distinct values where two were expected — and the first one sorts before ON because its leading space sorts before any letter, which is how the space is discovered.

Map them through a two-column table in H2:I6: the raw spelling on the left, the code on the right (Ont. → ON, Ontario → ON, ON → ON, QC → QC, Que. → QC). Then, in a new column:

=VLOOKUP(TRIM(A2), $H$2:$I$6, 2, FALSE)

TRIM strips the spaces before the lookup, and because VLOOKUP's text match is case-insensitive, ontario finds the Ontario row without a LOWER. A spelling not in the table returns #N/A — which is what you want: a new variant is a row to add to the mapping, not a value to guess. Filled down, the column reads ON ON ON QC QC.

R's idiom is a named vector indexed by key. Give each value a name, then index the vector with the raw text; R returns the value whose name matches:

> map <- c("ONT." = "ON", "ONTARIO" = "ON", "ON" = "ON", "QC" = "QC", "QUE." = "QC")
> map[toupper(trimws(x))]
   ONT. ONTARIO      ON      QC    QUE. 
   "ON"    "ON"    "ON"    "QC"    "QC" 

toupper(trimws(x)) standardises the key the way TRIM did — R is case-sensitive, so the names are upper-cased and so is the input. The result carries the matched names above the values; unname() drops them for a clean column, and an unknown key gives NA, the twin of #N/A.

The preparation map

CRISP-DM's data-preparation phase has five generic tasks, and every operation of the last two modules belongs to exactly one:

• Select data — choose the rows and columns to work with: a filter, subset(), keeping only Q1, dropping an unused column.
• Clean data — fix what is wrong or missing: TRIM, recoding Ont. → ON, removing duplicates, deciding what to do with a blank.
• Construct data — make new attributes or records: a derived column (hst, unit_price), a band (IFS, cut()), a month bucket from a date.
• Integrate data — combine information from several tables: VLOOKUP, XLOOKUP, merge().
• Format data — change the shape or representation without changing the meaning: unpivoting months from columns into rows, converting a text date to a real date, reordering columns for a tool that expects a layout.

The adjacent pairs are the exam's distractors. Recoding a spelling is clean, not format: the value was wrong. Turning a text date into a date is format, not clean: the information was right, the representation was not. Adding a column is construct, never clean, even when the column exists to fix something. And a merge is integrate, not select, however many rows it drops.

Type both, then tag six operations cold

Type the mapping VLOOKUP and the named-vector lookup. The questions give an operation and ask for its task; the figure is the answer key you should no longer need.

CRISP-DM: recoding categories is data preparation → clean data; this lesson's map is the whole phase.