(kdb+/q) Pivot table: date in rows, symbols in columns, last price as values

Question

General pivot function provided at http://code.kx.com/q/cookbook/pivoting-tables/ goes:

piv:{[t;k;p;v;f;g]
 v:(),v;
 G:group flip k!(t:.Q.v t)k;
 F:group flip p!t p;
 count[k]!g[k;P;C]xcols 0!key[G]!flip(C:f[v]P:flip value flip key F)!raze
  {[i;j;k;x;y]
   a:count[x]#x 0N;
   a[y]:x y;
   b:count[x]#0b;
   b[y]:1b;
   c:a i;
   c[k]:first'[a[j]@'where'[b j]];
   c}[I[;0];I J;J:where 1<>count'[I:value G]]/:\:[t v;value F]}

given that f and g are defined as

 f:{[v;P]`$raze each string raze P[;0],'/:v,/:\:P[;1]}
 g:{[k;P;c]k,(raze/)flip flip each 5 cut'10 cut raze reverse 10 cut asc c}

Now this example works (see the above link how table q is defined):

piv[`q;`date`sym`time;`side`level;`price`size;f;g]

date       sym  time        | Bprice0 Bsize0 Aprice0  Asize0 Bprice1  Bsize1 ..
----------------------------| -----------------------------------------------..
2009.01.05 milg 09:30:00.187|                                93.56285 29     ..
2009.01.05 milg 09:30:15.798|                                                ..
2009.01.05 milg 09:30:30.627|                                                ..

In my case, I would like to pivot by sym (each column different symbol) and key by date (each row different date), values being last price (each cell last price for a given date and symbol).

This naive call doesn't work:

piv[`q;`date;`sym;`price;f;g]

How to redefine f and g functions to be able to get a table of last price for each distinct date and each symbol, yielding something like this?

date       |  syma    symb     symc 
-----------| ----------------------
2009.01.05 | 93.56     8.5    566.1
2009.01.06 | 93.6      8.0    564.2
2009.01.07 |           9.0         
2009.01.08 | 94.05            560.3

score 3 · Accepted Answer · edited Nov 30 '17 at 12:16

The function piv requires the key and pivot arguments to be a list. So modifying your naive call as follows gives us a result:

q) piv[`q;(),`date;(),`sym;`price;f;g]
    date      | obhmprice oijbprice mkjkprice nihdprice ldegprice mbgnprice jmmip..
    ----------| -----------------------------------------------------------------..
    2009.01.08| 72.35531  28.9323   23.88535  12.21371  2.417089  49.45298  98.14..
    2009.01.07| 83.59946  6.036849  21.47751  78.8127   10.8295   98.52086  92.95..
    2009.01.09| 46.80912  46.26248  81.22377  93.51525  22.72674  95.42947  0.780..
    2009.01.05| 15.15728  26.88241  52.27362  90.73569  46.13635  50.67675  36.28..
    2009.01.06| 87.55843  38.49544  44.47115  59.41535  28.59271  94.38644  72.12..

...values being last price...

You will need to pre-process the quote data as follows to get the last price:

q) piv[0!select last price by date,sym from `date`time xasc q;(),`date;(),`sym;`price;f;g]
    date      | obhmprice oijbprice mkjkprice nihdprice ldegprice mbgnprice jmmip..
    ----------| -----------------------------------------------------------------..
    2009.01.05| 68.45829  95.92887  55.64212  82.06286  86.33655  6.020653  49.99..
    2009.01.06| 80.80825  39.97803  44.47115  20.84011  95.24609  26.23889  21.87..
    2009.01.07| 30.9605   15.85936  2.112642  74.48924  63.82276  49.55288  9.791..
    2009.01.08| 40.17655  34.08045  49.43202  59.99883  68.43116  74.76724  96.43..
    2009.01.09| 5.628707  96.54884  93.37123  65.68942  12.78308  91.10505  59.37..

Finally, to get the column name to be the symbol name, you need to modify the f and g functions - can review the documentation here or simply add some debug prints to see what is passed to these functions to understand what they should be.

In your case, they can simply be set to the following:

q) piv[0!select last price by date,sym from `date`time xasc q;(),`date;(),`sym;`price;{y[;0]};{x,z}]
    date      | abaa     aeec     agom     ahnj     amlj     aogk     bbfc     be..
    ----------| -----------------------------------------------------------------..
    2009.01.05| 1.382202 16.05533 33.8328  31.08264 82.60021 87.60909 37.37758 82..
    2009.01.06| 14.96261 35.70751 11.48701 5.394078 47.38052 79.5398  31.46943 64..
    2009.01.07| 12.95503 17.96305 92.59666 89.16665 83.80779 15.04835 34.70387 9...
    2009.01.08| 39.33969 15.18487 2.20252  8.859751 43.23156 22.88771 15.73866 76..
    2009.01.09| 48.32295 18.01393 91.38    46.87793 45.88258 67.70167 57.27067 63..

Thank you for insights, it worked for my case, except that I had to define ``f:{[v;P] `$raze each string raze P[;0]}`` instead of your proposal `f:{y[;0]}` (or `f:{P[;0]}`) — Daniel Krizian, May 06 '15 at 11:00
Auxiliary question: The http://code.kx.com/wiki/Pivot says that `f` and `g` can be defaulted with (::) - how would the syntax look like in that case? — Daniel Krizian, May 06 '15 at 13:07

(kdb+/q) Pivot table: date in rows, symbols in columns, last price as values

1 Answers1