Quick summary
Summarize this blog with AI
Introduction
An Excel-like exact lookup maps a key to a value while preserving the order of the requested keys. In an expss workflow, library(expss) attaches its maditr dependency, which provides vlookup(). This lesson uses maditr::vlookup() explicitly; expss::vlookup() is not an exported function in the tested version.
Tested with R 4.5.1, expss 0.11.7 and maditr 0.8.7. Run the following code blocks in order. All inputs are included.
Create a real lookup dictionary
Install expss once if necessary, then run these blocks. Character IDs retain leading zeros. The dictionary has one row per key.
# Install once if needed: install.packages("expss")
dictionary <- data.frame(code = c("001", "002", "003"),
product = c("Notebook", "Pen", "Folder"),
price = c(12, 2, NA_real_))
requested <- c("002", "999", "001", "002", NA_character_, "003")
stopifnot(!anyNA(dictionary$code), !anyDuplicated(dictionary$code))
prices <- maditr::vlookup(requested, dictionary, result_column = "price",
lookup_column = "code")
print(prices) # 2 NA 12 2 NA NA
stopifnot(identical(prices, c(2, NA_real_, 12, 2, NA_real_, NA_real_)))
This is key matching, not row-number indexing. Repeated request keys produce repeated results, and an unmatched key produces NA by default. The final NA belongs to a matched product with a missing price, which is a different situation.
Keep unmatched keys visible
Track whether a key exists separately from whether its returned value is missing. Do not replace every missing price with zero.
matched <- !is.na(requested) & requested %in% dictionary$code
result <- data.frame(code = requested, matched, price = prices)
print(result)
stopifnot(identical(matched, c(TRUE, FALSE, TRUE, TRUE, FALSE, TRUE)))
products <- maditr::vlookup(requested, dictionary, result_column = "product",
lookup_column = "code", no_match = "Not found")
print(products)
stopifnot(identical(products, c("Pen", "Not found", "Notebook", "Pen",
"Not found", "Folder")))
A text fallback is useful for a display column; use a compatible missing value in a numeric analytical column. Check types on both sides instead of converting IDs to numbers and accidentally collapsing 001 into 1.
Reject ambiguous dictionary keys
The underlying lookup returns the first matching dictionary row. Make duplicate precedence explicit; this helper rejects duplicates and missing keys rather than depending on their current order.
checked_lookup <- function(keys, dict) {
if (!is.character(keys) || !is.character(dict$code)) stop("keys must be character")
if (anyNA(dict$code) || anyDuplicated(dict$code)) stop("dictionary keys must be unique and nonmissing")
maditr::vlookup(keys, dict, result_column = "price", lookup_column = "code")
}
duplicated_dict <- rbind(dictionary, data.frame(code = "002", product = "Other", price = 99))
rejected <- tryCatch(checked_lookup("002", duplicated_dict), error = function(e) e)
stopifnot(inherits(rejected, "error"))
stopifnot(identical(checked_lookup(character(0), dictionary), numeric(0)))
stopifnot(identical(checked_lookup("999", dictionary), NA_real_))
If multiple prices per key are legitimate, a single-key lookup is the wrong contract. Add the missing key dimension or explicitly aggregate first.
Use multiple keys when the result depends on them
For store-specific prices, expss::add_columns joins by both code and store while keeping the requested rows. Its default rejects duplicate lookup-key combinations.
orders <- data.frame(code = c("001", "001", "002"), store = c("East", "West", "East"))
store_prices <- data.frame(code = c("001", "001"), store = c("East", "West"), price = c(12, 15))
joined <- expss::add_columns(orders, store_prices, by = c("code", "store"))
print(joined)
stopifnot(nrow(joined) == 3, identical(joined$price, c(12, 15, NA_real_)))
stopifnot(identical(joined$code, orders$code), identical(joined$store, orders$store))
ambiguous <- rbind(store_prices, store_prices[1, ])
err <- tryCatch(expss::add_columns(orders, ambiguous, by = c("code", "store")),
error = function(e) e)
stopifnot(inherits(err, "error"))
This is an exact-match workflow. It does not implement Excel approximate-match lookup or “closest lower” thresholds. The maditr lookup reference and expss add_columns reference define these separate APIs.
