Recode Data

Use the recoding tool to change all of the values in a column at once. For example, suppose you are interested in comparing the sales of computer and pharmaceutical companies. Your current company labels are Computer and Pharmaceutical. You want to change them to Technical and Drug. Going through all 32 rows of data and changing all the values would be tedious, inefficient, and error-prone, especially if you had many more rows of data. Recode is a better option.

Note: If you need to recode similar values within multiple columns, use the Recode option in Cols > Standardize Attributes. See Standardize Attributes in The Column Info Window.

Select Help > Sample Data Library and open Companies.jmp.

Select the Type column by clicking once on the column heading.

Select Cols > Recode.

In the Recode window, enter the desired values in the New Value boxes. For this example, enter Technical in the Computer row, and Drug in the Pharmaceutical row.

Click Done and select the In Place option from the menu.

Recode Window

All cells are updated automatically to the new values.

Note: If you enter a non-numeric value in a column with a Numeric data type, you are prompted to convert the data type to Character. Click Yes to convert the column and display the new value. Click No to keep the column Numeric and display a missing value.

Recode Options

When you are finished recoding data, click Done to view the following options:

In Place

applies any change to the original data column.

New Column

creates a new column for the changed data and retains the original column.

Formula Column

creates a new column with the changes as a formula instead of values. Changing a value in the original column in the data table causes the formula column to update that value automatically.

Script

creates a new script called Recode in the data table. You can run this script to perform recoding in-place. If you recode additional values later, and want to update your current script, use the Script option again. You are prompted to choose if you want to merge your updates with the saved script, or create a new script. Run the Recode script immediately or after making changes to the data. You can also copy the Recode script to other data tables, or use it inside your custom scripts.

The remaining options are available on the Recode window:

Undo

reverses the last change made to the window.

Redo

recalls the last change made to the window.

Filter

searches for specific values. Add quotes around your search to find an exact phrase that includes whitespace.

Group

becomes active when multiple values are selected. Click Group to make highlighted values part of the same group. If you previously edited a value before grouping, the edited value becomes the group representative in the New Value column. Otherwise, the group representative is the value that occurs most often.

Note that when a string ends in whitespace, the string is grouped with only similar strings that end in whitespace.

Show only Grouped

shows recoded values that have been grouped.

Show only Ungrouped

shows values that have not been grouped.

Red Triangle Options for Recode

The red triangle menu contains options for the Recode window.

Convert to Titlecase

converts the first letter of each word to uppercase, and the remaining letters to lowercase.

Convert to Uppercase

converts values to uppercase.

Convert to Lowercase

converts values to lowercase.

Tab characters, space characters, and line separators are often imported into a data table. Remove these characters using the following commands:

Trim Whitespace

removes leading and trailing whitespace characters. For example, if an extra space was imported before and after the name John, this command would delete the spaces.

Collapse Whitespace

removes leading and trailing whitespace characters and removes duplicate interior whitespace characters. That is, if more than one whitespace character is present, the Collapse Whitespace command replaces the two spaces with one space.

Use the following commands to group data based on value:

First Word

groups values based on the first word of the value. For example, if “John Smith” and “John Adams” were values, this command would group them under “John.”

Last Word

groups values based on the last word of the value.

All But First Word

groups values based on the remaining value after the first word is excluded.

All But Last Word

groups values based on the remaining value after the last word is excluded.

Group Similar Values

enables you to customize how data is grouped. Choose from the grouping options list. See “Grouping Options” below.

Start Over

returns the window to the default condition.

Recall

recalls previous changes made in the Recode window.

Script

view options to import, merge, or save Recode scripts.

–

Import From File: import a JSL script to recode previously recoded data. Run the same script on different data to recode data the same way.

–

Import From Data Table: import a JSL script saved to a data table.

–

Save to File: saves Recode changes to a JSL script. After selecting Save, you are prompted to name and save the file.

–

Save to Data Table: saves Recode script to current data table.

–

Merge with Data Table Script: merges changes made in the Recode window to the current Recode script saved to the data table. If there are multiple scripts, you are prompted to choose which script to merge your recoded data with.

–

Save to Script Window: appends the Recode script to the script window.

Right-Click Options for Recode

Right-click values in the Recode window to view the following options:

Group To

right-click selected values to select a different grouping value, or group representative. The Group To command displays the Old Values that occur most often in the data table with their corresponding New Values (if they are different). The list displays the first 8 possible group representatives.

Swap New Values

when two values are highlighted, select Swap New Values to make the new value of the first value adopt the new value of the second value, and vice versa.

Remove From Group

after values are grouped, right-click a single value or multiple values to remove them from that group.

Make Representative

right-click a single value from a group and select Make Representative to make the selected value the New Value.

Group Similar Values

right-click a single value to find values that are similar. The Grouping Options window appears. See “Grouping Options” in the section below.

Grouping Options for Recode

Select the following Group Similar Values commands to increase the accuracy of grouping:

Ignore Case

item case is ignored.

Ignore Non-Printable Characters

non-printable characters are ignored. Some data can include non-printable characters (such as file separators) that only the computer can read.