Build a Lookup Table in R Without Losing Rows

To add readable labels to coded data in R, create a lookup table with one row per code, then use left_join() to attach its columns. Check duplicate keys and unmatched codes before exporting: a readable label is useful only if it belongs to the right record.

This worked example turns fictional regional counts into a table containing both codes and names. It deliberately includes an unknown code so you can see how to flag it without dropping the row.

1. Create the data and lookup table

Use a current R installation and dplyr 1.1.1 or newer: that release added the relationship argument used below. Dplyr changelog

Install or update dplyr and readr with install.packages(c("dplyr", "readr")), then run:

library(dplyr)

observations <- tibble(
  record_id = 1:4,
  code = c("01", "02", "01", "99"),
  count = c(12L, 8L, 5L, 3L)
)

lookup <- tibble(
  code = c("01", "02"),
  region = c("North", "South")
)

The key is code: the column used to identify a matching lookup row. Repeated codes in observations are expected; repeated codes in this lookup are not.

Keep identifiers such as "01" as character strings, not numbers. For real CSV inputs with the same column structure, specify the key’s type in both files rather than relying on type guessing:

observations <- readr::read_csv(
  "observations.csv",
  col_types = readr::cols(code = readr::col_character())
)

lookup <- readr::read_csv(
  "region_lookup.csv",
  col_types = readr::cols(code = readr::col_character())
)

These imports replace the sample objects; skip them when following the fictional example. Readr supports explicit column types and warns that inferred types are not robust. Choose missing-value rules that suit your identifiers too: its default treats empty fields and the string "NA" as missing. Readr import documentation

If spaces or capitalization need standardizing, apply a documented rule to both keys before checking duplicates. Do not strip punctuation or leading zeros just to make records match.

2. Check lookup keys and labels

duplicate_keys <- lookup |>
  count(code, name = "key_rows") |>
  filter(key_rows > 1)

print(duplicate_keys)

stopifnot(
  nrow(duplicate_keys) == 0L,
  !anyNA(lookup$code),
  all(nzchar(trimws(lookup$code))),
  !anyNA(lookup$region),
  all(nzchar(trimws(lookup$region)))
)

This stops the workflow if the lookup contains duplicate or missing keys, or missing labels. Resolve conflicting entries using the codebook or source definition; taking the first row is not a reliable correction.

If a code is unique only within a year or geography, use that complete key instead—for example, by = c("code", "year")—and check uniqueness across those columns. Dplyr supports explicit multi-column join keys. Join documentation

3. Find unmatched codes, then join

anti_join() returns input rows that have no matching lookup row. Here, it identifies record 4 with code "99". Filtering-join documentation

unmatched <- anti_join(
  observations,
  lookup,
  by = "code",
  na_matches = "never"
)

print(unmatched)

result <- observations |>
  left_join(
    lookup,
    by = "code",
    relationship = "many-to-one",
    na_matches = "never"
  ) |>
  mutate(
    lookup_status = case_when(
      is.na(code) ~ "Missing code",
      is.na(region) ~ "Unmapped code",
      .default = "Matched"
    )
  )

stopifnot(nrow(result) == nrow(observations))

Three choices protect the output:

• left_join() preserves every input row. An inner_join() would omit the unknown code.
• relationship = "many-to-one" requires each input row to match at most one lookup row. It raises an error if that expectation fails.
• na_matches = "never" prevents missing keys from matching each other.

These behaviors are documented in dplyr’s join reference. The relationship check does not detect zero matches, which is why the separate unmatched report matters.

Expected sample result:

record_id	code	count	region	lookup_status
1	01	12	North	Matched
2	02	8	South	Matched
3	01	5	North	Matched
4	99	3	NA	Unmapped code

Here, NA means the region label is missing—not that the count is zero. Correct 99 from an authoritative source, or retain it with an explicit explanation. Do not invent a label.

Base R alternative for one label column

For the lookup step alone, use match() with the same validated inputs:

index <- match(
  observations$code,
  lookup$code,
  incomparables = NA
)

result_base <- observations
result_base$region <- lookup$region[index]

match() returns the position of the first matching key and returns NA when no match exists. That makes the duplicate-key check essential; match() does not reject ambiguous lookup entries. incomparables = NA prevents missing keys from matching. R documentation

Export the reviewed public table

After reviewing the unmatched report, export the dplyr result:

readr::write_csv(result, "regional_counts_public.csv", na = "")

write_csv() omits row names and writes UTF-8 text; na = "" exports missing values as empty fields. Keep lookup_status so readers can interpret blank region labels. Readr export documentation

Before sharing, document the observation source, lookup source and version, key definition, count units, and treatment of unresolved codes. A dataset reference sheet can keep those definitions alongside the table.

TablePage accepts CSV uploads and generates public dataset pages with filterable tables. Review privacy before uploading: use only the reviewed, non-sensitive export—not private identifiers or an unrestricted working dataset. Check the published table for leading zeros, blank labels and row counts before sharing the link.