CODEWITHSUNDEEP
formattingdisplaytabularkdb+
Riley Hun
Riley Hun

Reputation: 2785

KDB+: Formatting Values in Tables

What is the most robust way to specify rules for formatting table values? I want to apply each rule to its corresponding column in the most efficient way. I suppose using the functional form would be helpful in this case.

Here is the sample table: 

tbl:flip `GARP`longWgt`shortWgt`longWgtBeta`shortWgtBeta`longWgtRisk`shortWgtRisk`netWgt`netExposure`relativeBeta`relativeRisk`adjBeta`adjRisk!(`GARP_AUTOS_CA`GARP_BANKS_CA`GARP_CHEMICALS_CA`GARP_COMMUNICATIONS_CA`GARP_CONS_DISCR_CA;0.0091686 0.0176234 0.0076484 0.0131509 0.0460397;-0.010305 -0.0470135 0n -0.0078549 -0.0563819;1.3522162 0.6234817 1.3140238 0.7327634 1.1802914;0.1440806 0.7642193 0n 0.7216727 0.6112765;0.3254744 0.1573925 0.2541326 0.2554008 0.350877;0.3079491 0.2218098 0n 0.2594863 0.2758658;-0.0011365 -0.0293902 0.0076484 0.005296 -0.0103422;0.8897173 0.374857 0n 1.67422 0.8165681;9.3851363 0.8158414 0n 1.0153681 1.9308631;1.0569097 0.7095833 0n 0.9842553 1.2719117;8.3501184 -3.269856 0n 1.6999496 1.5766812;-1.0634328 -3.7595078 0n 1.64786 1.0386025)

I want all the columns to have 2 significant figures. The longWgt, shortWgt and netWgt columns should be in percent.

I have something like this, but I'm sure there is a better way of doing this: 

tbl:update longWgt:100f*longWgt, shortWgt:100f*shortWgt, netWgt:100f*netWgt from tbl;
tbl:update .Q.f[2] each longWgt, .Q.f[2] each shortWgt, .Q.f[2] each longWgtBeta, .Q.f[2] each shortWgtBeta, .Q.f[2] each longWgtRisk, .Q.f[2] each shortWgtRisk, .Q.f[2] each netWgt, .Q.f[2] each netExposure, .Q.f[2] each relativeBeta, .Q.f[2] each relativeRisk, .Q.f[2] each adjBeta, .Q.f[2] each adjRisk from tbl;
tbl:update {x,"%"} each longWgt, {x,"%"} each shortWgt, {x,"%"} each netWgt from tbl;

Upvotes: 1

Views: 1833

Answers (2)

Mark Kelly
Mark Kelly

Reputation: 1780

You could also use the 3 argument form of the @ apply operator which can be found here https://code.kx.com/wiki/Reference/AtSymbol since you are only applying functions to columns and not aggregating, filtering or renaming any columns.

It indexes into the item specified in the first argument with the 2nd argument and then applies the function in 3rd argument to the resulting elements, leaving the other elements untouched.

@[`tbl;wgtCols;100*];
@[`tbl;allCols;.Q.f[2]'];
@[`tbl;wgtCols;{x,'"%"}];

These can be nicely merged into one function using each both ' which will iterate through the list of arguments, applying them in turn. Information on each-both can be found https://code.kx.com/q/ref/adverbs/#each-both

@[`tbl;;]'[(wgtCols;allCols;wgtCols);(100*;.Q.f[2]';{x,'"%"})]]

These will all amend in place, e.g overwrite the tbl variable. If you do not want this to happen you can use this function:

@[;;]/[tbl;(wgtCols;allCols;wgtCols);(100*;.Q.f[2]';{x,'"%"})]

This utilises the / (scan) operator to apply each function in turn, with the first function operating on the initial table and the results feeding into the next function.

Upvotes: 2

JejeBelfort
JejeBelfort

Reputation: 1663

How about using a functional query:

First multiply the wgtCols below by 100:

wgtCols: `longWgt`shortWgt`netWgt;
![`tbl;();0b;wgtCols!{(*;100f;x)} each wgtCols];

Then format all columns allCols except the `GARP with 2 decimals:

allCols:1_cols tbl;
![`tbl;();0b;allCols!{(each;.Q.f[2];x)} each allCols];

Finally format the wgtCols to percentage format:

![`tbl;();0b;wgtCols!{(each;{x,"%"};x)} each wgtCols]

NB: To find out how to construct your functional query, you can use the parse operator on the q-query of your choice:

parse "update longWgt:100f*longWgt, shortWgt:100f*shortWgt, netWgt:100f*netWgt from tbl"

Output:

!
`tbl
()
0b
`longWgt`shortWgt`netWgt!((*;100f;`longWgt);(*;100f;`shortWgt);(*;100f;`netWgt))

Upvotes: 5

Related Questions