CODEWITHSUNDEEP
kdb+
James
James

Reputation: 1

Replace infinity with nulls throughout entire table KDB

Example table:

table:([]col1:20 40 30 0w;col2:4?4;col3: 100 200 0w 300)

My solution:

{.[table;(where 0w=table[x];x);:;0n]}'[exec c from meta table where t="f"]

There is a way I am not seeing I'm sure. This just returns a list of for each change which I don't want. I just want the original table returned with nulls replaced.

Thanks in advance!

Upvotes: 0

Views: 1913

Answers (1)

SeanHehir
SeanHehir

Reputation: 1593

It would be good to flesh out your question a bit more. Are you always expecting it to be float columns? Will the table have many columns? Will there be string/sym columns mixed in that might complicate things? If your table has a small number of columns you could just do an update

q)show t
col1 col2 col3
--------------
20   1    100
40   2    200
30   2    0w
0w   1    300

q)inftonull:{(x where x=0w):0n;x} 
q)update inftonull col1, inftonull col3 from t
col1 col2 col3
--------------
20   2    100
40   1    200
30   0
     3    300

If you think the column names might change or have a very large number of columns you could try a functional update (where you can pass the column names as parameters)

q){![t;();0b;x!inftonull,/:x,:()]}`col1`col3
col1 col2 col3
--------------
20   1    100
40   2    200
30   2
     1    300

If your table is comprised of only numeric data something like

q)flip{(x where x=.Q.t[type x]$0w):x 0N;x}each flip t
col1 col2 col3
--------------
20   2    100
40   1    200
30   0
     3    300

Might work, which tries to account for the fact the numeric data has different types. If your data is going to contain string/sym columns the last example won't work

Upvotes: 2

Related Questions