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
)

Parameters

nonNegative

Indicates if the difference is allowed to be negative. When set to true, if a value is less than the previous value, it is assumed 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.

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.

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 "sample"

data = sample.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)