difference() function

The difference() function computes the difference between subsequent records.
The user-specified columns of numeric type are subtracted while others are kept intact.

Output data type: Float

difference(
    nonNegative: false,
    columns: ["_value"],
    keepFirst: false,
    initialZero: false,
)

Parameters

nonNegative

Indicates if the difference is allowed to be negative. When set to true, if a value is less than the previous value, the function assumes the previous value should have been a zero. Default is false.

columns

The columns to use to compute the difference. Default is ["_value"].

keepFirst

Indicates the first row should be kept. If true, the difference will be null. Default is false.

initialZero

Use zero (0) as the initial value in the difference calculation when the subsequent value is less than the previous value and nonNegative is true. Default is false.

tables

Input data. Default is piped-forward data (<-).

Subtraction rules for numeric types

• The difference between two non-null values is their algebraic difference; or null, if the result is negative and nonNegative: true;
• null minus some value is always null;
• Some value v minus null is v minus the last non-null value seen before v; or null if v is the first non-null value seen.
• If nonNegative and initialZero are set to true, difference() returns the difference between 0 and the subsequent value. If the subsequent value is less than zero, difference() returns null.

Output tables

For each input table with n rows, difference() outputs a table with n - 1 rows.

Examples

The following examples use data provided by the sampledata package to show how difference() transforms data.

• Calculate the difference between subsequent values
• Calculate the non-negative difference between subsequent values
• Calculate the difference between subsequent values with null values
• Keep the first value when calculating the difference between values

Calculate the difference between subsequent values

import "sampledata"

data = sampledata.int()

data
    |> difference()

Calculate the non-negative difference between subsequent values

import "sampledata"

data = sampledata.int()

data
    |> difference(nonNegative: true):

Calculate the difference between subsequent values with null values

import "sampledata"

data = sampledata.int(includeNull: true)

data
    |> difference()

Keep the first value when calculating the difference between values

import "sampledata"

sampledata.int()
    |> difference(keepFirst: true)