Special functions

fillfill_missing()missing()

SyntaxExample

fill_missing (expression, <first_fill>, <last_fill>, <fill_value>)

fillfill_missing(A+B)missing(A+B)

To better understand this function, let's suppose you have a set of variables that you want to sum up together.

fillfill_missing(A+B+C+D)missing(A + B + C + D)

The variables A, B, C, and D don't have data in all of the timestamps, as shown in the table below, so the fill missing() function is used to fill in the gaps and do the calculation. The following examples are based on the table:

DateABCDOutput

07/12/2020 00:00:04

5

2

4

5

16

07/12/2020 00:00:03

3

2

3

4

12

07/12/2020 00:00:02

4

4

5

2

15

07/12/2020 00:00:01

4

3

5

5

-

By default, the fill_missing() expression makes the first_fill parameter as "ffill", meaning that the function will fill the gaps forward, starting at a point where it has enough data to fill the gaps for all the involved time series. The result would be as follows:

DateABCDOutput

07/12/2020 00:00:04

5

2

4

5

-

07/12/2020 00:00:03

3

2

3

4

12

07/12/2020 00:00:02

3

4

3

2

12

07/12/2020 00:00:01

4

3

5

2

14

fillfill_missing(A+B+C+D,firstmissing(A + B + C + D, first_fill="bfill")fill="bfill")

On the other hand, It's also possible to make the first_fill a "bfill" The gaps would be filled in the following manner:

DateABCDOutput

07/12/2020 00:00:04

5

2

4

5

16

07/12/2020 00:00:03

3

2

3

4

12

07/12/2020 00:00:02

4

4

5

2

15

07/12/2020 00:00:01

4

3

5

2

14

fillfill_missing(A+B+C+D,fillmissing(A + B + C + D, fill_value=0)value=0)

DateABCDOutput

07/12/2020 00:00:04

5

0

4

5

14

07/12/2020 00:00:04

3

2

3

4

12

07/12/2020 00:00:04

0

4

0

2

6

07/12/2020 00:00:04

4

3

5

2

12

cumsum()cumsum()

SyntaxExampleOutput

cumsum(x)cumsum(x)

cumsum([0,1,2,3])cumsum([0, 1, 2, 3])

6

where()where()

SyntaxExampleOutput

where(condition, operation if true, operation if false)

Step Function:

Assuming a variablex=[āˆ’1,2,1] x = [-1, 2, 1]where(x>=0,1,0)where(x >=0, 1, 0)

[0,1,1][0, 1, 1]

Interval Function:

Assuming a variablex=[āˆ’1,2,1] x = [-1, 2, 1]where(x<0,0,where(x<1),1,2)where(x<0, 0, where(x<1), 1, 2)

[0,2,2][0, 2, 2]

diff()diff()

SyntaxExampleOutput

diff(<variable>,<steps>)

Assuming a variable x=[14,15,17,16]x = [14, 15, 17, 16] diff(x),2)diff(x), 2)

[3, 1][3,1][3, 1]

shift()shift()

SintaxExampleOutput

shift(<variable>,<step> )

Assuming a variable x=[āˆ’2,āˆ’1,0,1,2]x = [-2, -1, 0, 1, 2]shift(x,āˆ’1)shift(x, -1)

Allows you to retrieve the previous value of variable, by shifting the series to the left.

[āˆ’1,0,1,2][-1, 0, 1, 2]

Assuming a variable x=[āˆ’2,āˆ’1,0,1,2]x = [-2, -1, 0, 1, 2]shift(x,2)shift(x, 2)

Shifts the series to the right

[x,x,āˆ’2,āˆ’1,0][x, x, -2, -1, 0]

Last updated