How to batch (re)label variables using a CSV lookup table

[Stephan Scheffelberg]

I want to (re)label my variables automatically, based on a variable name ￫ new label lookup table.

I currently do it as follows:

sysuse auto

foreach renaming_pair in "make model" "mpg mileage" "rep78 repair" {
    label variable `=word("`renaming_pair'", 1)' `"`=word("`renaming_pair'", 2)'"'
}

Edit: a space friendly alternative could be something along the lines of:

sysuse auto

foreach renaming_pair in "make|model of the car" "mpg|mileage" "weight|weight in pounds" {
    local var_name = regexr("`renaming_pair'", "\|.*", "")
    local label_name = regexr("`renaming_pair'", ".*\|", "")
    label variable `var_name' "`label_name'"
}

However, this suppose to hardcode the relabelling mapping. I would prefer to define the lookup table externally.

1. How to achieve the same result using the following label_lookup_table.csv?

var_name, new_label
make, model
mpg, mileage
rep78, repair

2. What if I want to use the following generic_label_lookup_table.csv (which also contains renaming_pairs not relevant for the given dataset + has labels with spaces)?

var_name, new_label
make, model of the car
mpg, mileage
rep78, repair
foo, bar
baz, buzz

[Stephan Scheffelberg]

Assuming the following generic_label_lookup_table.csv file saved in the working directory

var_name,new_label
make,model of the car
mpg,mileage
foo,bar
baz,buzz
rep78,repair

I can implement the batch relabelling as follows:

* Generate the list of renaming pairs
import delimited "generic_label_lookup_table.csv", varnames(1)

gen pair = var_name + "|" + new_label

quietly count
forvalues i = 1/`r(N)' {
    local renaming_pairs = `" `renaming_pairs' "`=pair[`i']'" "'
}

* Relabel dataset
sysuse auto, clear

foreach pair in `pairs' {
    local var_name = regexr("`pair'", "\|.*", "")
    local label_name = regexr("`pair'", ".*\|", "")
    capture label variable `var_name' "`label_name'"
}

However, this solution doesn't seems quite an elegant solution to me, and I imagine my lack of experience makes me miss something more straightforward — thus any hint or improvement would be appreciated.

[Andrew Musau]

merge the lookup table with your dataset and then relabel. If the number of variables is large, holding the pairs in a macro may not be feasible.

[Stephan Scheffelberg]

merge seems way more natural indeed!

I am, however, still a bit confused on how to merge on variable names themselves instead of on instances of a given (set of) variable(s). I.e. I know how to merge on the observations dataset itself, but not on the "variables name and label" table.

Could you please detail how one would do it?

[Andrew Musau]

After importing

* Example generated by -dataex-. To install: ssc install dataex
clear
input str5 var_name str16 new_label
"make"  "model of the car"
"mpg"   "mileage"         
"foo"   "bar"             
"baz"   "buzz"            
"rep78" "repair"          
end

tempfile labels
save `labels'
sysuse auto, clear
merge 1:1 _n using `labels'
qui tab var_name
forval i= 1/`r(N)'{
   foreach var of varlist *{
      if "`var'"== "`=var_name[`i']'"{
         label variable `var' "`=new_label[`i']'"
     }
  }
}

More efficiently, you can create an observation containing the variable names and merge on this observation. In this way, you do not need to loop through variable names when relabeling.

[Andrew Musau]

Read "an observation" and "this observation" as "a variable" and "this variable" in #5.

[Stephan Scheffelberg]

I have not been able to implement your suggestion; your solution, however, already works like a charm. Thank you very much!