TRIM, UPPER, LEFT, & — and their R twins

The problem a frequency table exposes

A prov column entered by hand over three months arrives with "ON", "on " and "Ontario" in it. Nothing is missing, but a pivot by province now shows three Ontarios. The first move is not to clean — it is to count. The frequency table from Lesson 4.1 is the diagnostic:

> table(c("ON", "on ", "Ontario", "ON", "QC"))

     ON     on  Ontario      QC 
      2       1       1       1 

Every stray spelling shows up as its own row. That table is data understanding → verify data quality; what follows is data preparation → clean data.

Worked example — fold case and trim space

Two functions handle most of the mess. TRIM removes leading, trailing and doubled spaces; UPPER folds every letter to capitals. Nested, in a helper column:

=UPPER(TRIM(A2))

"on " becomes "ON", " Qc" becomes "QC". LOWER and PROPER (first letter capitalised) are the alternatives when the standard is lowercase or title case. What the pair cannot do is turn "Ontario" into "ON" — that is a recoding, and Lesson 6.8 does it with a mapping table. In R the same two steps nest the same way, inside out:

> x <- c("ON", "on ", " Qc", "BC")
> toupper(trimws(x))
[1] "ON" "ON" "QC" "BC"

trimws (trim whitespace) and toupper; tolower for the other direction.

Taking text apart: LEFT, MID, LEN

LEFT(text, n) returns the first n characters; RIGHT(text, n) the last n; MID(text, start, n) takes n characters starting at position start; LEN(text) counts characters. R's substr(text, start, stop) covers LEFT, RIGHT and MID at once — but note the third argument is the stop position, not a length:

> nchar("Ontario")
[1] 7
> substr("Ontario", 1, 3)
[1] "Ont"
> substr("Ontario", 3, 5)
[1] "tar"

MID("Ontario", 3, 3) also gives "tar" — start 3, three characters — while substr("Ontario", 3, 5) gives it as start 3, stop at 5. Same output, different third argument; the swap is the quiz's favourite distractor.

Putting text together: & and paste0

A report wants a label like ON-1001: province, hyphen, order id. In Sheets the join operator is &:

=LEFT(B2, 2)&"-"&A2

R joins with paste0 — the 0 means no separator:

> paste0(substr(orders$prov, 1, 2), "-", orders$order_id)
 [1] "ON-1001" "QC-1002" "ON-1003" "BC-1004" "AB-1005" "ON-1006" "QC-1007"
 [8] "BC-1008" "ON-1009" "AB-1010" "QC-1011" "BC-1012"

Plain paste inserts a space between its pieces by default (paste("ON", 1001) is "ON 1001"); paste("ON", 1001, sep = "-") sets the separator explicitly. Note that paste0 happily glued a number (order_id) onto text — R converts it for you, as & does in the spreadsheet.

Type three, then clean a list

Type the UPPER(TRIM()), the label formula and the R toupper(trimws()). The code block cleans the four-element list in Python, where .strip().upper() chains the same way.

CRISP-DM: this is data preparation → clean data.