as.numeric, as.Date(format =), gsub

Text that should be a number

Module 10 met the file where one amount was "760 " and the whole column loaded as text. Exported reports do worse: thousands separators. as.numeric() reads only digits, a sign, and a decimal point:

> as.numeric("1,320")
[1] NA
> as.numeric(gsub(",", "", "1,320"))
[1] 1320

NA with a warning — not an error, and not 1.32 — so a column of formatted amounts silently becomes a column of NAs. gsub(pattern, replacement, x) — global substitute — removes every comma first, and then the conversion is clean. Argument order: what to find, what to put in its place, where to look.

Worked example — a date in the wrong order

The Quebec branch sends dates as 06/01/2025, day first. as.Date() with no format tries %Y-%m-%d and then %Y/%m/%d, and the second one matches:

> as.Date("06/01/2025")
[1] "6-01-20"
> as.Date("06/01/2025", format = "%d/%m/%Y")
[1] "2025-01-06"

The first line is the sixth of January in the year 6, day 20 — R read 06 as the year, 01 as the month, 20 as the day and ignored the trailing 25. No error, no warning, a plausible-looking Date object. Always pass format unless the text is already ISO 2025-01-06: %d day, %m month, %Y four-digit year, %y two-digit, %b abbreviated month name. The same codes run the other way in format(): format(as.Date("2025-01-06"), "%b %Y") is "Jan 2025" — Module 7's month grouping key.

A factor of numbers

Module 10 showed read.csv no longer makes factors, but a column can still arrive as one — from cut(), or an older file. Converting it directly returns the level positions, not the values:

> f <- factor(c("10", "20", "10"))
> as.numeric(f)
[1] 1 2 1
> as.numeric(as.character(f))
[1] 10 20 10

A factor stores each value as an integer code with a label; as.numeric() hands back the codes. Go through as.character() first — the idiom is as.numeric(as.character(f)) and the final asks for it by name.

Five string tools

Province arrives as " Ont. " from a form. Each tool fixes one thing, and they nest inside-out:

> x <- " Ont. "
> trimws(x)
[1] "Ont."
> gsub("\\.", "", x)
[1] " Ont "
> toupper(trimws(gsub("\\.", "", x)))
[1] "ONT"

trimws() strips the spaces at both ends (Sheets TRIM); toupper() is UPPER. gsub() is a regular-expression find-and-replace, so . means any character — gsub(".", "", x) returns "", everything deleted. A literal dot is "\\." inside an R string: one backslash for the regex, doubled because the string needs it. substr("2025-01-06", 1, 7) is "2025-01" (LEFT(A2, 7)); paste("ON", "Store", sep = "-") is "ON-Store" (& or TEXTJOIN), and its default separator is a single space.

A cleaned code still needs a dictionary (Module 5): "ONT" is not "ON", and the recode in L12.6 finishes the job.

Type the three conversions, then name the trap

CRISP-DM: conversions are data preparation → format data; string cleaning is clean data.