shift function

The shift function shifts values by a specified value.


Parameters

shift(column, shift, group, cyclic)

Parameter

Description

column

Returns the value of the corresponding row of the specified column. Column is a mandatory parameter.

shift

The number of rows downward by which the value must be shifted. Shift is a mandatory parameter.

group

The results can be grouped according to a specified attribute.

cyclic

If True, any missing initial rows will be filled with values taken from the end of the dataset in a cyclical manner. By default, this value is set to False.


Example - shift(column, shift, group)

The following examples use the Oranges dataset.

Description

Screenshot

Our dataset contains the prices of oranges on a daily basis in 3 different farmers markets.

If we create a new attribute called Yesterday’s cost, and enter the formula shift($"Cost of oranges today", 1) the cost of oranges from the previous row will be displayed.

However, the results contain the values from the previous row. but not from the previous day, as there is an entry for every day for each farmer's market, so if we need to take the results from the previous row for the same market, using the group parameter.

So we’ll instead enter the formula shift($"Cost of oranges today", 1, $"Market").

Example - shift(column, shift, group, cyclic

Description

Screenshot

You’ll have noticed that there are missing values for the first rows in the dataset, as there are no previous rows to retrieve values from.

If it makes sense logically, you can use retrieve values from the final rows of the dataset in a cyclical manner, so there are no missing values.

To do this, you must set the Cyclic parameter to True - by default it is False, so if not specified, the function will not automatically fill empty initial rows.

The corresponding formula will be: shift($"Cost of oranges today", 1, $"Market), True)