normalise

Overview

The normalise statement is used to update the values in a numerical column such that they are all positive, negative or inverted.

In this documentation the spelling normalise is used but normalize may also be used. The functionality is identical in either case.

Syntax

normalise columncolNameas positive

normalise columncolNameas negative

normalise columncolNameas invert

normalise columncolNameas standard

Details

The normalise statement processes each value in the column called colName and applies the following logic based on the last argument shown above as follows:

Argument	Result
positive	All negative numbers are replaced with their positive equivalent. Non-negative numbers are left unmodified.
negative	All positive numbers are replaced with their negative equivalent. Negative numbers are left unmodified.
invert	All positive numbers are replaced with their negative equivalent, and all negative numbers are replaced with their positive equivalent
standard	All non-blank values are assumed to be a decimal number and are replaced with that value in conventional notation. This functionality is intended to provide a means to convert numbers in scientific notation such as 2.1E-5 to conventional notation such as 0.000021.

In order to be considered a number, a value in the colName column must start with any of the characters +, -, . or 0 to 9 and may contain a single . character which is interpreted as a decimal point.

If a value in colName is non-numeric or blank it is left intact

When using standardall non-blank values are assumed to be numeric, and as such any non-numeric values will be changed to a numeric zero.

Additionally:

• Any numerical value in colName which starts with a +, . or decimal character is considered positive

• Any numerical value in colName which starts with a - character is considered negative

• When using standard the resulting conventional number will be accurate up to 14 decimal places

The normalise statement ignores the option overwrite setting, as its sole purpose is to modify existing values.

Example

import "system/extracted/csp_usage.csv" source test alias data

# Invert all numerical values in column 'quantity'
normalise column quantity as invert