Spotfire Tips & Tricks: Normalize/Standardize your data with Spotfire

Introduction

A common requirement is to interpret and compare scores or sets of scores obtained on different scales. For example, how do we compare a score of 90 in a Singing contest with a score of 75 on a math test?

Two main popular types of normalization are used to solve this use case. The first type of normalization originates from linear algebra and treats the data as a vector in a multidimensional space. In order to normalize the data, it transforms the data vector into a new vector whose norm (i.e., length) is equal to one.

The second type of normalization has its origin from statistics and eliminates the unit of measurement by transforming the data into new scores. Spotfire provides various out of box methods and simple easy Custom Transformations to normalize your data.

Normalization with Spotfire

You can transform a data table by normalizing the data in one or more of its columns at the time of loading a Data Table or even after data is already inside Spotfire. It is also possible to replace a data table with a transformed version of itself.

To add a Normalization transformation when adding a new data table:

1. Select File > Add Data Tables... and add the data of interest.
2. Click Show transformations.
3. Select Normalization from the drop-down list and click Add....

To add a Normalization transformation to data that is already loaded into Spotfire:

1. Select Insert > Transformations....
2. In the Data table drop-down list, select the data table you want to add the transformation to.
3. Select Normalization from the drop-down list and click Add....

Transformed Columns can be added or they can also replace existing Columns. The method specifies the normalization method to use and Baseline column specifies the baseline column to use. You can also enter percentage value (P) when normalizing by percentile or by the trimmed mean. The description provides a brief description of the currently selected normalization method.

User Defined Normalization Methods

It is also possible to create user-defined Normalization methods using Expression Functions dialog to register a function in the Spotfire expression language that is based on TERR (now called Spotfire Enterprise Runtime for R. Once an expression function has been created, it is automatically available in the Spotfire expression language.

To add your custom Normalization method

1. Select Edit > Data Function Properties, and then click the Expression Functions tab.
2. Click New.

As an example, we are performing a log transformation to our input values and then subtracting by mean

output<-log10(input1)-mean(log10(input1))

Function type

Specifies whether the new expression function should be a column function or an aggregation function. Column functions return the same number of rows as the input, whereas an aggregation function aggregates the input values into a single output value.

Return type

The Spotfire allows the user to select the data type that the script should return.

Category

The category in the function list in which to place the function, in the Custom Expressions and Insert Calculated Column dialogs. A proper categorization of the functions may make them easier to find when working with them in expressions.

Linear Algebra Normalization

You normalize any vector by dividing every component by the square root of the sum of squares of its components. A unit vector will have a magnitude equal to one.

Expression functions can be used to perform Linear Algebra Normalization.

output<-input1 /sqrt(sum((input1)*(input1)))