Description:
In this tutorial we’ll explain what transformations are and how to create and apply them.
Transformations generate new data from existing columns. They can be used to perform mathematical operations such as addition, subtraction, multiplication, and division. For example, you might want to create a new column that is the logarithm of the data in another column or the total of two other columns.
Transformations are made up of two components: the result column and the transformation formula or function. The result column is the place where the newly created data will be stored in the dataset. Transformations modify columns of data, not individual cells. This is one of the main ways in which the NCSS Data Table is different from traditional spreadsheets. Result columns can be used as variables in reports and plots just like any other columns in the dataset.
The transformation formula is stored in the result column and specifies how a new column is constructed from other existing columns. The formula is made up of mathematical operators, column names, constants, and common mathematical functions.
To create a transformation, click the Transformation button in the toolbar. You will first be asked to select the result column for the transformation. You should select an empty column to hold the newly transformed data or the data in the non-empty column will be overwritten. Empty columns are indicated by a star in the column selection tool window. We’ll select column “C3” as the result column for this example. Click OK to load the Transformation Editor. This tool displays columns names and functions that can be used to create transformations. We’ll take the log of column 1. Select the log function from the list and then enter C1 as the argument. Click OK to save the transformation.
By default, transformations are applied automatically. If you edit data values in columns that are involved in transformations, the transformation results are automatically recalculated. To change this setting, select View then Transformation Toolbar from the menu to display the transformation toolbar. Deselect Auto Recalculate Transformation to turn off auto-calculation. You’ll have to recalculate the transformations manually each time you make a change to the data by clicking on the Recalculate All Transformations button. This button is highlighted whenever a transformation needs to be recalculated because of a data edit. Let’s turn auto-calculation back on to make sure that our data and transformations never get out of sync. This is the recommended setting.
As a side note, remember that the calculation of transformations does not depend on any data filter that may be active on a dataset. All rows are transformed, even if they are excluded from analysis and graphs by a filter.
To edit or create a transformation without using the Transformation editor, click on the Column Info button. Notice that the log transformation we entered is stored in column 3. Let’s change the transformation to be the natural log and also make column 4 be the sum of columns 2 and 3. When you have functions that depend on other columns that contain transformations themselves, NCSS will automatically determine the correct order in which to apply the transformations so that all results are accurate. In this case, the transformation in column 3 needs to be calculated before column 4 because column 4 depends on column 3.
Columns that contain active transformations are locked to editing. To edit the data in a column containing a transformation, you’ll first need to deactivate the transformation. Let’s do this for column 4. Click on the transformation formula and then click the button that appears on the right side of the cell. This will load the transformation editor. Put a check next to Deactivate Transformation and click OK. Notice that a single quote has been placed in front of the transformation formula in column 4 and that it has been grayed out. You can now edit the data in the column. Be careful, though, because when you reactivate the transformation any changes will be lost when the transformation is recalculated.
