cancel
Showing results for
Did you mean:

## Filling zeros for missing combinations of keys

New Contributor
Hello,I often need to fill in zeros so that I have a value for every combinationof a set of keys.For example:q)t:([date:1 1 1 2 2; sym:`A`B`D`B`C] pl:5?1f)q)tdate sym| pl--------| ---------1 A | 0.30177231 B | 0.7850331 D | 0.53470962 B | 0.71117162 C | 0.411597q)2!`date`sym xasc update 0^pl from (0!t)uj (select distinct date from t)cross select distinct sym from tdate sym| pl--------| ---------1 A | 0.30177231 A | 01 B | 0.7850331 B | 01 C | 01 D | 0.53470961 D | 02 A | 02 B | 0.71117162 B | 02 C | 0.4115972 C | 02 D | 0Is there a more terse, idiomatic way to get the same result?Thanks,Josh
4 REPLIES 4
New Contributor
Oops.. I screwed up the example. The output I want isq)update 0^pl from uj[;t] 2!update pl:0f from (select distinct date from t)cross select distinct sym from tdate sym| pl--------| ---------1 A | 0.30177231 B | 0.7850331 D | 0.53470961 C | 02 A | 02 B | 0.71117162 D | 02 C | 0.411597On 30 November 2015 11:51 UTC, joshmyzie2@yandex.com wrote:> Hello,>> I often need to fill in zeros so that I have a value for every combination> of a set of keys.>> For example:>> q)t:([date:1 1 1 2 2; sym:`A`B`D`B`C] pl:5?1f)> q)t> date sym| pl> --------| ---------> 1 A | 0.3017723> 1 B | 0.785033> 1 D | 0.5347096> 2 B | 0.7111716> 2 C | 0.411597> q)2!`date`sym xasc update 0^pl from (0!t)uj (select distinct date from t)cross select distinct sym from t> date sym| pl> --------| ---------> 1 A | 0.3017723> 1 A | 0> 1 B | 0.785033> 1 B | 0> 1 C | 0> 1 D | 0.5347096> 1 D | 0> 2 A | 0> 2 B | 0.7111716> 2 B | 0> 2 C | 0.411597> 2 C | 0> 2 D | 0>>> Is there a more terse, idiomatic way to get the same result?>> Thanks,> Josh
New Contributor
q)update 0^pl from uj[;t] 2!update pl:0f from (select distinct date from t)cross select distinct sym from t

you can shorten the cross.  and then another cross will give you null pl:

q)(cross[;].('[asc;distinct])'[(0!t)`date`sym]) cross 0n
1 `A 0n
1 `B 0n
1 `C 0n
1 `D 0n
2 `A 0n
2 `B 0n
2 `C 0n
2 `D 0n

so a shorter expression is:

q)(2!flip `date`sym`pl!flip(cross[;].('[asc;distinct])'[(0!t)`date`sym]) cross 0n) uj t
date sym| pl
--------| ---------
1    A  | 0.3927524
1    B  | 0.5170911
1    C  |
1    D  | 0.5159796
2    A  |
2    B  | 0.4066642
2    C  | 0.1780839
2    D  |

but select distinct may be faster and use less memory than ('[asc;distinct])'[(0!t)`date`sym]) above

ta, jack
New Contributor II
one way
q)((select distinct date from t)cross select distinct sym from t)pj t

New Contributor
Hi Josh,

Here is a generic method, for a table with any number of keys:

t:([date:1 1 1 2 2; sym:`A`B`D`B`C] pl:5?1f)
t
date sym| pl
--------| ---------
1    A  | 0.1330435
1    B  | 0.8999625
1    D  | 0.2571
2    B  | 0.0651849
2    C  | 0.425422

pad:{0^k!x k:flip keys[x]!flip (cross/)distinct each value flip key x}
date sym| pl
--------| ---------
1    A  | 0.1330435
1    B  | 0.8999625
1    D  | 0.2571
1    C  | 0
2    A  | 0
2    B  | 0.0651849
2    D  | 0
2    C  | 0.425422

The pad function extracts the distinct keys and creates a new table with the original information, inserted by key. The nulls are replaced with zeros.

Here is an example using a table with four key columns:

)t:([date:1 1 1 2 2; sym:`A`C`A`B`A; key3:"UUMUM"; key4:7 8 7 9 7] pl:5?1f)
date sym key3 key4| pl
------------------| ---------
1    A   U    7   | 0.9921317
1    A   U    8   | 0
1    A   U    9   | 0
1    A   M    7   | 0.3933534
1    A   M    8   | 0
1    A   M    9   | 0
1    C   U    7   | 0
1    C   U    8   | 0.4865121
1    C   U    9   | 0
1    C   M    7   | 0
1    C   M    8   | 0
1    C   M    9   | 0
1    B   U    7   | 0
1    B   U    8   | 0
1    B   U    9   | 0
1    B   M    7   | 0
1    B   M    8   | 0
1    B   M    9   | 0
2    A   U    7   | 0
2    A   U    8   | 0
..

Regards,

Liam,
AquaQ Analyics