Replace all values using a data dictionary, where the column name matches the rows in the dictionary (R language)

I have an encoded survey file and data dictionary that describes each coded value for each column. Example of inputs and desired output here.

The "Field" column in the data dictionary lists every column in the survey data. The values have different descriptions depending on the column name. For example, "1" can mean "Very satisfied" if it's in the Satisfaction column, but "Metro" if in the Region column.

I've gotten this code to work in Python, but I need it to work in R.

I am open to alternative approaches to this, this code is just what I've come up with. It runs in ~5 seconds across 150 columns and 40,000 rows.

import pandas as pd

# Read the data dictionary and raw survey data
dictionary_df = pd.read_excel(filepath)
dictionary_df["Value"] = dictionary_df["Value"].astype(int)

survey_df = pd.read_excel(filepath)


# Replaces the original unalbelled survey_df with a labelled survey_df.

lookup_dict = {}
cols = list(dictionary_df.Field.unique())
for i in cols:
    subset = dictionary_df.loc[dictionary_df.Field==i]
    lookup_dict_vals = dict(zip(subset['Value'], subset['Description']))
    lookup_dict[i] = lookup_dict_vals

# This try/except is necessary where columns that are in the raw file are not in the data dictionary. These columns will remain untouched, but columns to the right will get labelled.

for i in cols:
    try:
        survey_df[i] = survey_df[i].map(lookup_dict[i])
    except:
        continue

I have tried to replicate this in R but get a dataframe full of NAs. Is there a way to fix the code below?

library(dplyr)

# Read the data dictionary and raw survey data
dictionary_df <- readxl::read_excel(filepath)
dictionary_df$Value <- as.integer(dictionary_df$Value)

survey_df <- readxl::read_excel(filepath)

# Replaces the original unlabelled survey_df with a labelled survey_df.

lookup_dict <- list()
cols <- unique(dictionary_df$Field)

for (i in cols) {
  subset <- filter(dictionary_df, Field == i)
  lookup_dict_vals <- setNames(subset$Description, subset$Value)
  lookup_dict[[i]] <- lookup_dict_vals
}

# This try/except is necessary where columns that are in the raw file are not in the data dictionary. These columns will remain untouched, but columns to the right will get labelled.

for (i in colnames(survey_df)) {
  tryCatch({
    survey_df <- survey_df %>% mutate({{i}} := lookup_dict[[i]][{{i}}])
  }, error = function(e) {
    next
  })
}

Since the dictionary could store entries as

0     1
Rural Metro

(not necessarily using the definitions 1,2,3...) we have to invoke names and which in a loop to obtain the indices, then subset the dictionary for those indices. Simply add a little overhead code involving the names and the unlist function, which turns a single column data frame into a vector.

for (i in colnames(survey_df)) {
  tryCatch({
    k <- c()
    for (j in unlist(survey_df[{{i}}])) { #for each coded value
      k<-c(k, which(names(lookup_dict[[i]]) == j)) #find the definition's slot
    }
    survey_df <- survey_df %>% mutate({{i}} := lookup_dict[[i]][k]) #take those names
  }, error = function(e) {
    next
  })
}

I'm curious how this compares performance-wise. The pivoting steps might be costly, but the join should be very fast.

library(tidyverse)
survey_df %>%
  mutate(row = row_number()) %>%
  pivot_longer(-row, names_to = "Field", values_to = "Value") %>%
  # left_join(dictionary_df) %>% # won't work for values not in dictionary
  # EDIT -- I think this will keep existing unmatched values
  rows_update(dictionary_df, unmatched = "ignore") %>%  
  select(-Value) %>%
  pivot_wider(names_from = Field, values_from = Description)