select only those columns from table have not null values in q kdb

Question

I have a table:

q)t:([] a:1 2 3; b:```; c:`a`b`c)
a b c
-----
1   a
2   b
3   c

From this table I want to select only the columns who have not null values, in this case column b should be omitted from output.(something similar to dropna method in pandas).

expected output
a c
---
1 a
2 b
3 c

I tried many things like

select from t where not null cols

but of no use.

Answer 1

Here is a simple solution that does just what you want:

q)where[all null t]_t
a c
---
1 a
2 b
3 c

[all null t] gives a dictionary that checks if the column values are all null or not.

q)all null t
a| 0
b| 1
c| 0

Where returns the keys of the dictionary where it is true

q)where[all null t]
,`b

Finally you use _ to drop the columns from table t

Hopefully this helps

Answer 2

A modification of Sander's solution which handles string columns (or any nested columns):

q)t:([] a:1 2 3; b:```; c:`a`b`c;d:"   ";e:("";"";"");f:(();();());g:(1 1;2 2;3 3))

q)t
a b c d e  f g
----------------
1   a   ""   1 1
2   b   ""   2 2
3   c   ""   3 3

q)where[{$[type x;all null x;all 0=count each x]}each flip t]_t
a c g
-------
1 a 1 1
2 b 2 2
3 c 3 3