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. Note that the system will convert the data type of Y to the specified column during the replacement. If Y cannot be converted, an error is raised.

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);
// output
[1,0,0,6,0,7]

y=1..6
nullFill(x,y);
// output
[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

Related functions: isNull, hasNull, bfill, ffill, lfill