Articles in this section

See more

How to Recode Data Based on a Lookup Using R

  • Updated
Follow

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

mceclip2.png

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

mceclip3.png

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 pasted table (Tables > Paste or Enter Data) called US.States:mceclip0.png
  • A variable in your data set to match to 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 Structure > Numeric.

See How to Import Table Data 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 > Numeric.

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 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 you are looking up.

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

Method 2 - match() function

1. In the Data Sources 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 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.

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

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

Related articles