Select menu: Spread | Calculate | Recode
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 occurrences 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).
- From the menu select Spread | Calculate | Recode.
- Fill in the fields as required then click OK.
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 values 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
Click to toggle between the different display formats for a factor.
Use date format
This is only displayed if the column being recoded is in date format. If this is ticked, then the column being recoded date format is used to display the values in the New values input column, and this date format will be applied to the resulting spreadsheet column.
Behaviour with Unicode text in a column
When a column in the spreadsheet contains Unicode, you will not be able to edit the labels directly. An attempt to edit a cell will open the Edit Unicode Text dialog which accepts Unicode characters. You can still use the clipboard shortcut keys for copying (Ctrl+C), cutting (Ctrl+X) and pasting (Ctrl+V) Unicode text in this dialog. See Working with Unicode in a Spreadsheet for more information on working with Unicode text.
See also
- Combine/Concatenate Text Columns
- Split a Text Column
- Factor Product/Combine
- Understanding Factors within a Spreadsheet
- Edit Factor Levels and Labels
- Spreadsheet Calculate Menu
- Edit Unicode Text
- Working with Unicode in a Spreadsheet
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