Recode a Column
See Also
This displays a spreadsheet containing a list of the unique values from a column. Each value can then be changed to a new value if required. When a new value is given, all occurrences of the old value are changed within the column to the new value.

This menu can be used for changing multiple occurences of a single value to a new one (for example changing all zeros to missing values or vice-versa), or for recoding some text entries on to a numerical scale (e.g. 'Bad', 'Poor', 'Average', 'Good' to 1,2,3,4 or 'No', 'Yes' to 0,1).

Spreadsheet Columns

Old Values - a list of the unique values or text found in a column.
New Values - a list to provide new values or text for each corresponding item in the Old value list.
Counts - the frequency of occurrence of each unique value found in the column. This column is provided for information only and cannot be changed.

The cursor key behaviour for this menu is slightly different to a standard spreadsheet where the right/left cursor keys moves the cursor within a cell. The tab and back-tab (shift+tab) keys can be used to move between column. The shift key + up/down arrow or page up/down key can be used to select individual cells. A group of cells can be selected using the mouse and a whole column can be selected by clicking on the column name.

The new values can be pasted from the clipboard using the Ctrl+V shortcut keys. Selected cells can be copied to the clipboard using the Ctrl+C shortcut keys.

Recoded Column Name

The name of the column to hold the results of recoded data. If a column with this name already exists in the spreadsheet, it will be overwritten.

Create as a Factor

When selected, the new column of results will be converted to a factor.

Recode to Text

When selected, the values in the New Values column will be displayed as text, and the resulting column will be a text. Alternatively, if the Create as a Factor option is selected the column will be converted to factor using the text as factor labels.

Ordinals/Levels/Labels

Allows you to toggle between the different display formats for a factor.

OK

Use the specified exchanges to recode the column and close the dialog.

Cancel

Close the dialog without making any changes.

See Also

The MVINSERT and MVREPLACE functions of CALCULATE can be used to provide some of the functionality of this menu within the command language. For example, the following calculation can be used to recode 0's to 1's (provided the data does not contain any missing values):

CALC X = MVREPLACE(MVINSERT(X;X.in.0);1)

Note that if MVREPLACE was removed from the above calculation all the 0's in X would be recoded as missing values. An alternative method of recoding the values could be to use the WHERE function. This finds the positions of items in the data which can then be used to change the values. For example,

CALC Pos = WHERE(X.in.0)
CALC X$[Pos] = 1

Note that in the above code, if X does not contain any 0's the WHERE function will produce a fault as there are no values to save. However, this code can be extended to avoid this problem by using an IF statement as follows:

IF SUM(X.in.0) > 0
  CALC Pos = WHERE(X.in.0)
  CALC X$[Pos] = 1
ENDIF