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

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