Quick summary
Summarize this blog with AI
Introduction
Convert text to numbers only after deciding which text formats are acceptable and what to do with failures. Keep the original text and a parsing status, so a malformed value cannot silently become an ordinary missing observation. This lesson uses base R rather than passing a character vector to a data-frame verb.
Tested with R 4.5.1. Run the following code blocks in order. All inputs are included.
Observe conversion and the factor trap
A factor stores category codes. Convert its labels to character before parsing their numeric meaning.
text <- c("10", " 2.5 ", "-3")
print(as.numeric(text)) # 10.0 2.5 -3.0
f <- factor(c("10", "20", "10"))
print(as.numeric(f)) # 1 2 1: category codes
print(as.numeric(as.character(f))) # 10 20 10: parsed labels
stopifnot(identical(as.numeric(as.character(f)), c(10, 20, 10)))
The numeric reference documents this factor behavior. Do not use the internal codes as measurements.
Define a decimal-text contract
The parser below accepts signed decimals and scientific notation, with surrounding whitespace. It rejects currency symbols, grouping commas, hexadecimal notation and nonfinite values. Blank text and source NA are recorded as missing.
parse_decimal <- function(raw) {
if (!is.character(raw)) stop("raw must be a character vector")
cleaned <- trimws(raw)
missing <- is.na(raw) | cleaned == ""
decimal <- "^[+-]?([0-9]+([.][0-9]*)?|[.][0-9]+)([eE][+-]?[0-9]+)?$"
acceptable <- !missing & grepl(decimal, cleaned)
value <- rep(NA_real_, length(raw))
value[acceptable] <- as.numeric(cleaned[acceptable])
finite <- acceptable & is.finite(value)
status <- rep("invalid", length(raw))
status[missing] <- "missing"
status[finite] <- "valid"
value[!finite] <- NA_real_
data.frame(raw, value, status, stringsAsFactors = FALSE)
}
parsed <- parse_decimal(c("10", "2.5", "bad", "", NA, "1e2", "Inf", "1,200"))
print(parsed)
stopifnot(identical(parsed$status, c("valid", "valid", "invalid", "missing",
"missing", "valid", "invalid", "invalid")))
stopifnot(identical(parsed$value[c(1, 2, 6)], c(10, 2.5, 100)))
Only text that matches the declared syntax is converted. Overflow such as 1e999 is rejected as nonfinite. Ordinary floating-point rounding and underflow still apply; this parser does not promise arbitrary precision or exact decimal money arithmetic.
Attach the result without losing rows
The position of every original row remains available. Inspect invalid values before deciding whether to fix, reject or omit them.
records <- data.frame(id = c("A", "B", "C"), amount_text = c("12.5", "oops", "20"))
result <- parse_decimal(records$amount_text)
records$amount <- result$value
records$amount_status <- result$status
print(records)
invalid_rows <- which(records$amount_status == "invalid")
stopifnot(identical(invalid_rows, 2L), nrow(records) == 3)
observed_subtotal <- sum(records$amount, na.rm = TRUE)
stopifnot(observed_subtotal == 32.5)
The observed subtotal is 32.5, but the complete total is unknown because B is invalid. Calling na.omit() before conversion cannot remove new failures created during conversion. Do not strip every non-digit character: turning “12kg” into 12 or “1,5” into 15 may change the meaning.
Test boundary inputs
Use a separate locale-aware parsing rule when commas mean decimal marks or grouping separators. Dates and identifiers are different domains; leading zeros may be meaningful.
stopifnot(nrow(parse_decimal(character(0))) == 0)
edge <- parse_decimal(c("+.5", "-2E-2", "1e999", "0x10", "12kg", "NA"))
stopifnot(identical(edge$status, c("valid", "valid", rep("invalid", 4))))
stopifnot(isTRUE(all.equal(edge$value[1:2], c(0.5, -0.02))))
wrong_type <- tryCatch(parse_decimal(1:3), error = function(e) e)
stopifnot(inherits(wrong_type, "error"))
Preserve the parsing contract alongside the cleaned data. For subsequent aggregation, see sum and missing-value policies in R.
