nullFill
Syntax
nullFill(X, Y)
Arguments
X is a vector/matrix/table.
Y is either a scalar, or a vector/matrix with the same dimension as X.
Details
When X is a vector/matrix:
If Y is a scalar: replace the NULLs in X with Y.
If Y is a vector/matrix : replace the NULLs in X with the values of corresponding elements in Y.
When X is a table, Y must be a scalar, and the function replaces all NULLs in X with Y. It is especially useful when we would like to replace all NULLs in a table with a certain value, such as -999999.
The function always returns a new object. Input X is not altered.
Examples
Ex 1. For vectors:
$ x=1 NULL NULL 6 NULL 7;
$ nullFill(x,0);
[1,0,0,6,0,7]
$ y=1..6
$ nullFill(x,y);
[1,2,3,6,5,7]
Use function nullFill
on a vector in a table in a SQL statement:
$ ID=take(1,6) join take(2,6)
$ date=take(2018.01.01..2018.01.06, 12)
$ x=3.2 5.2 NULL 7.4 NULL NULL NULL NULL 8 NULL NULL 11
$ t=table(ID, date, x)
$ t;
ID |
date |
x |
---|---|---|
1 |
2018.01.01 |
3.2 |
1 |
2018.01.02 |
5.2 |
1 |
2018.01.03 |
|
1 |
2018.01.04 |
7.4 |
1 |
2018.01.05 |
|
1 |
2018.01.06 |
|
2 |
2018.01.01 |
|
2 |
2018.01.02 |
|
2 |
2018.01.03 |
8 |
2 |
2018.01.04 |
|
2 |
2018.01.05 |
|
2 |
2018.01.06 |
11 |
$ update t set x=x.nullFill(avg(x)) context by id;
$ t;
ID |
date |
x |
---|---|---|
1 |
2018.01.01 |
3.2 |
1 |
2018.01.02 |
5.2 |
1 |
2018.01.03 |
5.266667 |
1 |
2018.01.04 |
7.4 |
1 |
2018.01.05 |
5.266667 |
1 |
2018.01.06 |
5.266667 |
2 |
2018.01.01 |
9.5 |
2 |
2018.01.02 |
9.5 |
2 |
2018.01.03 |
8 |
2 |
2018.01.04 |
9.5 |
2 |
2018.01.05 |
9.5 |
2 |
2018.01.06 |
11 |
Ex 2. For matrices:
$ x=1 NULL 2 NULL 3 4 $ 3:2;
$ x;
#0 |
#1 |
---|---|
1 |
|
3 |
|
2 |
4 |
$ x.nullFill(0);
#0 |
#1 |
---|---|
1 |
0 |
0 |
3 |
2 |
4 |
Ex 3. For tables:
$ t=table(1..6 as id, 2.1 2.2 NULL NULL 2.4 2.6 as x, 4.3 NULL 3.6 6.7 8.8 NULL as y);
$ nullFill(t, -999999);
id |
x |
y |
---|---|---|
1 |
2.1 |
4.3 |
2 |
2.2 |
-999999 |
3 |
-999999 |
3.6 |
4 |
-999999 |
6.7 |
5 |
2.4 |
8.8 |
6 |
2.6 |
-999999 |