How to Recode Data Based on a Lookup Using R

Introduction

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 or to and from variables from the same data set. In this example, we will use a pasted table called US.States:

See How to Import Table Data into Displayr for details on how to paste table data.

Method 1 - mapvalues() function

1. In the Data Sets tree, hover over any variable and click the Plus (+), then select Custom Code > R - Numeric.

2. Update the Name and Label on the object inspector under Properties > GENERAL.

3. Paste the below under Properties > R CODE:

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 Sets tree so 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 your are looking up.

4. Change Structure in object inspector to Nominal: Mutually Exclusive Categories.

Method 2 - match() function

1. In the Data Sets tree, hover over any variable and click the Plus (+), then select Custom Code > R - Text.

2. Update the Name and Label on the object inspector under Properties > GENERAL.

3. Paste the below under Properties > R CODE:

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.

4. Change Structure in object inspector to Nominal: Mutually Exclusive Categories.

See Also

How to Import Table Data into Displayr

How to Work with R in Displayr

How to Add a Custom Calculation

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