Read Blog Post

Read Blog Post

Contributed on February 6, 2017

2.0K

2.4K

How to Normalize or Standardize Your Data with TIBCO Spotfire

Blog: The Tibco Blog

A common analytic 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. TIBCO Spotfire provides various out-of-the-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. 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 trimmed mean. Description provides brief description of the currently selected normalization method.

A column name setting specifies how the naming of the normalized columns should be handled. You can either add the word “Normalized” to the column name of the original columns or use the expression (normalization equation) as a column name. If you have selected to replace columns, you will also get a third option where you can keep the current column names.

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 TIBCO 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
Spotfire allows user to select 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 magnitude equal to one.
Expression functions can be used to perform Linear Algebra Normalization.

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

Read up on more Tips and Tricks articles to become a Spotfire master. Want to try it out for yourself? Here are more Spotfire information and free trial details.