CODEWITHSUNDEEP
kdb+
duckman
duckman

Reputation: 747

KDB/Q: compute the percentage by group

Is there a quick way to get the proportion/percentage of each item in a group? right now, I first compute the sum of the values by group, and then merge this total table to the original table, and then compute the proportion. Does Q have a quick function to compute this?

day     week    item    proportion
mon     1       2       20% 
tue     1       7       70%
wed     1       1       10%
mon     2       1       25% 
tue     2       2       50%
wed     2       1       25%

currently, I would do:

total: select total: sum item by week from table;
out: update proportion: item%total from table lj `week xkey total;

Upvotes: 1

Views: 1157

Answers (1)

Jorge Sawyer
Jorge Sawyer

Reputation: 1341

You can use fby to do this in one query:

q)table:flip`day`week`item!(`mon`tue`wed`mon`tue`wed;1 1 1 2 2 2;2 7 1 1 2 1)
q)update proportion:item % (sum;item) fby week from table
day week item proportion
------------------------
mon 1    2    0.2
tue 1    7    0.7
wed 1    1    0.1
mon 2    1    0.25
tue 2    2    0.5
wed 2    1    0.25

Upvotes: 4

Related Questions