How to Recode Data Based on a Lookup Using R

This article describes how to recode a variable, such as a state question, into...

... a variable of regions based on a lookup table or variables:

Requirements

• A lookup table with at least 2 columns, one to match on and one to use as the new recoded value. In this example, we will use a named US.States:

  

• A variable in your data set to match the new recoded values in the lookup table. This is the What state do you live in? variable in this example. Note that if your column to match on in the lookup table is all numbers, it will be converted to numeric data, and the variable in your data set must be Object Inspector > Structure > Numeric.

See How to Import Excel Tables into Displayr for details on how to paste table data.

Method 1 - mapvalues() function

1. In the Data Sources tree, hover over any variable and click Plus (+) > Custom Code > R > Nominal.
2. Update the Name and Label on the object inspector under General > General.
3. Paste the below into the R Code editor:

plyr::mapvalues(`What state do you live in?`, from=US.States$State, to=US.States$Region, warn_missing=F)

• The above code uses the R library plyr's mapvalues function, so we need to first load that.
• The question, What state do you live in?, was dragged over from the Data Sources tree and is using the label as the reference. This is why it is enclosed in backticks.
• The state variable is then compared to the State field from the US.States table and returns the Region field.
• Note: if your data does not match a lookup category, it will return the original data that you are looking up.

Method 2 - match() function

1. In the Data Sources tree, hover over any variable and click the Plus (+), then select Custom Code > R > Nominal.
2. Update the Name and Label on the object inspector under General > General.
3. Paste the below into the R Code editor:

x = as.character(`What state do you live in?`)
lookup = US.States
m = match(x,lookup$State)
x[!is.na(m)] = lookup[m[!is.na(m)], "Region"]
x[is.na(m)] = "Outside of USA"
x

• The above code converts our state question into text values and defines it as x.
• We use the match function to compare the state data with the lookup table's State field. This returns an index number.
• Where x matches the lookup table, we recode it with the corresponding Region data.
• Anything that does not match, i.e., is NA, we recode as "Outside of USA".
• We then return the recoded x as the final result.

Next

How to Import Table Data into Displayr

How to Use R in Displayr

How to Add a Custom Calculation

How to Create a New Variable Based on Other Variables Using R