CODEWITHSUNDEEP
kdb+
DrZhivago
DrZhivago

Reputation: 31

Pivot table with multiple value columns in KDB+

I would like to transform the following two row table generated by:

tb: ([] time: 2010.01.01 2010.01.01; side:`Buy`Sell; price:100 101; size:30 50)

time        side    price   size
--------------------------------
2010.01.01  Buy     100     30
2010.01.01  Sell    101     50

To the table below with single row:

tb1: ([] enlist time: 2010.01.01; enlist price_buy:100; enlist price_sell:101; enlist size_buy:30; enlist size_sell:50)

time        price_buy price_sell size_buy size_sell
-----------------------------------------------------
2010.01.01  100       101        30       50

What is the most efficient way to achieve this?

Upvotes: 3

Views: 905

Answers (3)

Matt Moore
Matt Moore

Reputation: 2800

(select price_buy:price, size_buy:size by time from tb where side = `Buy) lj select price_sell:price, size_sell:size by time from tb where side = `Sell

time      | price_buy size_buy price_sell size_sell
----------| ---------------------------------------
2010.01.01| 100       30       101        50

If you wanted to avoid 2 select statements:

raze each select `price_buy`price_sell!(side!price)@/:`Buy`Sell, `size_buy`size_sell!(side!size)@/:`Buy`Sell by time from tb

As an additional note, having a date column labeled time can be misleading. Typical financial tables in kdb have the format date time sym etc

Edit: Functional form for dynamic column generation:

{x[0] lj x[1]}[{?[`tb;enlist (=;`side;enlist `$x);(enlist `time)!enlist `time;(`$("price",x;"size",x))!(`price;`size)]} each ("Sell";"Buy")]
time      | priceSell sizeSell priceBuy sizeBuy
----------| -----------------------------------
2010.01.01| 101       50       100      30

Upvotes: 4

terrylynch
terrylynch

Reputation: 13657

The general pivot function on the Kx website can do this, see https://code.kx.com/q/kb/pivoting-tables/

q)piv[tb;(),`time;(),`side;`price`size;{[v;P]`$raze each string raze P[;0],'/:v,/:\:P[;1]};{x,z}]
time      | Buyprice Sellprice Buysize Sellsize
----------| -----------------------------------
2010.01.01| 100      101       30      50

Upvotes: 2

Anton Dovzhenko
Anton Dovzhenko

Reputation: 2569

I have a pivot function in github . But it doesn't support multiple columns

.math.st.pivot: {[t;rc;cf;ff]
  P: asc distinct t cf;
  Pcol: `$string[P] cross "_",/:string key ff;
  t: ?[t;();rc!rc;key[ff]!{({[x;y;z] z each y@group x}[;;z];x;y)}[cf]'[key ff;value ff]];
  t: ![t;();0b; Pcol! raze {((';@);x;$[-11h=type y;enlist;::] y)}'[key ff]'[P] ];
  ![t;();0b;key ff]
};

But you can left join to achieve expected result:

.math.st.pivot[tb;enlist`time;`side;enlist[`price]!enlist first]
  lj .math.st.pivot[tb;enlist`time;`side;enlist[`size]!enlist first]

Looks like adding support for multiple columns is a good idea.

Upvotes: 1

Related Questions