KDB/Q: how to transpose a table with null values

Question

I have a table with 2 keyed columns. It looks like this

date        sym   type  val
2020.01.02  ABC         100
2020.01.02  ABC   a2    200
2020.01.02  ANX         300
2020.01.02  XYZ   a3    400
2020.01.02  XYZ   a2    100

Note that type column has some null values. I want to transpose that table to

date        sym     type_null   type_a2     type_a3
2020.01.02  ABC     100         200         0
2020.01.02  ANX     300         0           0
2020.01.02  XYZ     0           100         400

Note how if type is null, we give it a value of 0. I can do this by update type_a2:?[type_a2=0n;0;type_a2] once the table has been transposed. Is there a more elegant way to do this?

Regarding the transpose, I have no idea how to do that. I tried the following but it did not work.

P:asc exec distinct type from myTable;
pvt:exec P#(type!val) by date,sym from myTable;

Cathal O'Neill · Accepted Answer · 2020-12-08T02:01:28.647

I think the issue you may be having is with naming a column type. Key words or error types can't be used in variable assignment so you might want to change that.

In terms of the actual code, this seems to work fine for me with the column name changed.

q)myTable:([]date:2020.01.02;sym:`ABC`ABC`ANX`XYZ`XYZ;typ:``a2``a3`a2;val:100 200 300 400 100)
q)P:`$"type_",/:asc exec string distinct typ from myTable
q)0!0^exec P#((`$"type_",/:string[typ])!val) by date,sym from myTable
date       sym type_ type_a2 type_a3
------------------------------------
2020.01.02 ABC 100   200     0
2020.01.02 ANX 300   0       0
2020.01.02 XYZ 0     100     400

I've used 0^ here to fill all the null entries of the table with 0, and 0! to unkey the table.

Thanks, I tried your code and it works with your table, but when I switched to my table, I got a "length" error. I tried `exec P#(trdType!val) by date,sym from `date`sym xasc myTable;` and got a table in the wanted format but everything is empty except the date and sym columns. I tried adding 0!0^ and everything is 0 — duckman, Dec 09 '20 at 06:08

score 1 · Answer 2 · answered Dec 08 '20 at 02:22

Note that it is not good practice to name columns as keywords (you lose the ability to use qSQL) but you can still work around it with the functional form:

q)table:flip `date`sym`type`val!(2020.01.02;`ABC`ABC`ANX`XYZ`XYZ;``a2``a3`a2;100 200 300 400 100)
q)parse"update `null^type from table"
!
`table
()
0b
(,`x)!,(^;,`null;@:)
q)table:![table;();0b;enlist[`type]!enlist(^;enlist`null;`type)]
q)P:`$?[table;();();(string;(distinct;`type))]
q)0!0^?[table;();{x!x}`date`sym;(#;`P;(!;`type;`val))]
date       sym null a2  a3
---------------------------
2020.01.02 ABC 100  200 0
2020.01.02 ANX 300  0   0
2020.01.02 XYZ 0    100 400
q)`date`sym`type_null`type_a2`type_a3 xcol 0!0^?[table;();{x!x}`date`sym;(#;`P;(!;`type;`val))]
date       sym type_null type_a2 type_a3
----------------------------------------
2020.01.02 ABC 100       200     0
2020.01.02 ANX 300       0       0
2020.01.02 XYZ 0         100     400

Applying parse to a qSQL statement written as a string will return the internal representation of the functional form.

score 1 · Answer 3 · answered Dec 08 '20 at 11:07

The more general pivot function (here) works out of the box and even works with your column called type .... though as others have said, you should definitely avoid a column called type.

q)myTable:flip`date`sym`type`val!(5#2020.01.02;`ABC`ABC`ANX`XYZ`XYZ;``a2``a3`a2;100 200 300 400 100)
q)0^piv[myTable;`date`sym;1#`type;`val;{`$raze each"type_",/:string`null^y};{x,z}]
date       sym| type_null type_a2 type_a3
--------------| -------------------------
2020.01.02 ABC| 100       200     0
2020.01.02 ANX| 300       0       0
2020.01.02 XYZ| 0         100     400

KDB/Q: how to transpose a table with null values

3 Answers3