Recode is available from the top Columns menu, then found under Utilities.
We can also access Recode using the red triangle from our Data Table near the Columns panel.
Once our Recode window launches most of the Recode features can be found under its red triangle. The first five items carry over from previous versions of JMP.
Let's review some of the existing features before we explore the enhancements to Recode by inspecting this sample data set using the REGISTERED NAME column.
Note: Double-clicking on the REGISTERED NAME column in the columns panel will immediately bring it into view.
After selecting the recode menu option, JMP returns a summary of the column's values, including counts and number of groups. The group option is new, and in our example, we have 287 The New Value section, as with the previous Recode tool, allows you to edit any value.
Convert to Titlecase, Uppercase and Lowercase operate on all of the rows in the column.
Let's select Convert to Titlecase to see the effect this has on the contents of the New Value section. Notice how the values have been modified to reflect the Titlecase change.
At any time during the recode process we can select either the "Undo" or "Redo" keys to move back and forth between editing steps, or select "Start Over" from the red triangle menu to return to the original state.
Trim Whitespace removes leading and trailing spaces from the text. Notice how "Todd Tullio" is grouped with "Todd Tullio" when this function is selected. Trim Whitespace takes us from 287 to 280 groups.
Collapse Whitespace removes leading and trailing whitespaces, while also replacing internal white space strings (of any length) with a single whitespace.
Collapse Whitespace takes us from 287 to 270 groups. So which rows changed? Any changes are shaded to allow you to scan the table to see the changes quickly.
First Word replaces the given text with only its first word, using spaces as the separator character. Using this feature takes us from 287 to 144 groups.
This is a quick way to separate names into firstname and lastnames
Last Word, All But First Word, and All But Last Word perform similar to First Word.
Selecting Group Similar Values allows us to ignore case, nonprintable characters, Whitespace and punctuation.
At the bottom of the Group Similar Values window, we can also allow for character edits using the difference ratio or by maximum character difference.
Max Character difference is the number of character edits like insertion, deletion, or change needed to alter one word to another. It groups values according to a maximum number of character differences — for example, type "5" to group values that differ by five characters or less.
Difference ratio is the number of character edits divided by the length of the shorter word. It groups values according to proportional difference. For example, type ".25" to group values that are at least 25% different. Difference Ratio adapts to the length of the strings being compared, so it's better if the data set contains both long and short strings for Difference Ratio.
Experimenting with different types of data yourself, or the Companies data set in JMP, is the best way to become familiar with Difference Ratio and Max Character Difference for grouping results.
We can also group manually, altogether, or in conjunction with other grouping methods.
There are two ways of doing this:
By selecting desired values or groups of values, and right-clicking (or, press the Group button), or by y editing rows to the same value. As new rows are edited to this value, they will automatically be added to the group. This option is very handy, as rows with whitespace differences won't be grouped together—you'll be able to tell that the rows are somehow different, even if they appear the same.
To illustrate the second grouping option, let's start by manually removing the periods from the names A. M. Taylor. Notice with the A M Taylor group, one of the entries is not grouped, so we know it is still different. It has a trailing space.
Here's an example of performing an automated grouping, and modifying it manually.
From the red triangle menu, select Group Similar Values, check Allow Character Edits, enter 0.25 in the dialog and click OK.
Our defaults did a pretty good job, but we see that Gary H. Hartmann and Gary Hartmann (who happen to be the same person) do not belong in Gary W. Hartmann's group.
Remove them from the group by selecting both rows, right-clicking, and selecting Remove From Group.
Next, while these 2 lines are still highlighted group them with a click of the Group button.
Moving to Joel Kohler, we see that not all of his entries were grouped. However, he prefers to use his professional title, which is not in the present grouping name.
Select the four entries, right-click and select Group to JOEL E KOHLER PE. Add punctuation by editing the new group name to JOEL E. KOHLER, PE. When you've finished recoding, press the Done button (upper right).
You are presented with four options. The options determine how you apply the recoded values in your data table.
In place: Save the recoded values in place, overwriting the current column.
New Column: Save the recoded values in a new column.
Formula Column: Save the recoded values in a new formula column
Script: Write a recode script to the data table—which is one of the best features of the new Recode; cleaning up data often can take more time than is available in a single session, so it is important to be able to save your work. You can run the script at the beginning of your next session to pick up right where you left off.
