Recode values using Excel map (lookup table) — recode_using_excel

This function recodes values using a map (lookup table) located in an Excel sheet.

Usage

recode_using_excel_map(
  values,
  path,
  sheet = 1,
  col_find = 1,
  col_replace = 2,
  first_row_is_header = TRUE,
  keep_original_when_no_match = FALSE,
  ...
)

Arguments

values: values to be recoded
path: (character) path to the excel file
sheet: (numeric or character) sheet number or name where the map is
col_find: (numeric or character) column number or name where original values are
col_replace: (numeric or character) column number or name where replacement values are
first_row_is_header: (logical) whether the first row is column names (default TRUE)
keep_original_when_no_match: (logical) whether to retain original value when no match is found (otherwise will be NA; default FALSE)
...: additional named arguments passed to readxl::read_excel() (like range or na)

Value

values recoded

Details

The map should have at least two columns, one with original (find) values, one with replacement values. By default the function uses the first sheet in the Excel file specified in path; it uses the first column as the find column, and the second column as the replacement column; and it assumes the first row is a header (column names). Additional named arguments can be passed to readxl::read_excel() via ... for more complicated maps (e.g. custom cell range).

If there is at least one value that does not have a match and keep_original_when_no_match has been requested, a warning will be thrown with the list of values that could not be matched.

Examples

if (FALSE) {
df$Nationality <- df$Nationality |>
   recode_using_excel_map("map.xlsx", sheet="Nationality")
}