Links

Special functions

fillfill
_
missing()missing()

Syntax
Example
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:
Date
A
B
C
D
Output
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:
Date
A
B
C
D
Output
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:
Date
A
B
C
D
Output
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)
Date
A
B
C
D
Output
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()

Syntax
Example
Output
cumsum(x)cumsum(x)
cumsum([0,1,2,3])cumsum([0, 1, 2, 3])
6

where()where()

Syntax
Example
Output
where(condition, operation if true, operation if false)
Step Function:
Assuming a variable
x=[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 variable
x=[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()

Syntax
Example
Output
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()

Sintax
Example
Output
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 modified 1mo ago