How to combine multiple columns into one column in kdb?

user_13899248 0
Viewed 348

I have written a below code that works, but I think there would be a better way to solve this problem.

I have something like this

table:([]time:9 11;Bid1px:4 5;Bid2px:7 3;Bid3px:6 8);
time Bid1px Bid2px Bid3px
-------------------------
9    4      7      6
11   5      3      8
table:update All_bid:flip(Bid1px;Bid2px;Bid3px) from table;
time Bid1px Bid2px Bid3px All_bid
---------------------------------
9    4      7      6      4 7 6
11   5      3      8      5 3 8

I would like to write a code using Bidcols like the below but can't seem to find a way, any help would be appreciated.

Bidcols:`Bid1px`Bid2px`Bid3px;
table:update All_bid:flip(Bidcols????) from table;
kdb
3 Answers

One way would be to use # (take) which on tables will return a subset of columns. As a table in kdb is simply a list of dicts, can then use value each on this table to get the values for each row:

q)table:([]time:9 11;Bid1px:4 5;Bid2px:7 3;Bid3px:6 8)
q)Bidcols:`Bid1px`Bid2px`Bid3px;
q)// using just #, all_bid column is a table so each row is a dict
q)update all_bid:Bidcols#table from table
time Bid1px Bid2px Bid3px all_bid
-----------------------------------------------------
9    4      7      6      `Bid1px`Bid2px`Bid3px!4 7 6
11   5      3      8      `Bid1px`Bid2px`Bid3px!5 3 8
q)// adding value each gives us the final desired result
q)update all_bid:value each Bidcols#table from table
time Bid1px Bid2px Bid3px all_bid
---------------------------------
9    4      7      6      4 7 6
11   5      3      8      5 3 8
user_86943030

One way you can do this is via indexing.

q)Bidcols:`Bid1px`Bid2px`Bid3px
q)show table:([]time:9 11;Bid1px:4 5;Bid2px:7 3;Bid3px:6 8)
time Bid1px Bid2px Bid3px
-------------------------
9    4      7      6
11   5      3      8

q)update all_bid:flip table Bidcols from table
time Bid1px Bid2px Bid3px all_bid
---------------------------------
9    4      7      6      4 7 6
11   5      3      8      5 3 8

To explain this, first we index into table with Bidcols:

q)table Bidcols
4 5
7 3
6 8

This returns a list of lists - one list per column name in Bidcols. We then flip this to return another lists of lists - this time equal to the length of table

q)flip table Bidcols
4 7 6
5 3 8
user_65387790

For the sake of completeness, while Seans and Jonathons answers are better solutions for this use case, the more general approach to problems like yours (trying to make a select statement more configurable) is to convert it to a functional select statement.

In this example it would be:

/use parse to determine functional form
q)0N!parse"update All_bid:flip(Bid1px;Bid2px;Bid3px) from table";
(!;`table;();0b;(,`All_bid)!,(+:;(enlist;`Bid1px;`Bid2px;`Bid3px)))

/replicate the functional form, swapping in your Bidcols list
q)![table;();0b;(1#`All_bid)!enlist(flip;enlist,Bidcols)]
time Bid1px Bid2px Bid3px All_bid
---------------------------------
9    4      7      6      4 7 6
11   5      3      8      5 3 8
user_38956970
Related