Filling zeros for missing combinations of keys

[joshmyzie2]

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

[joshmyzie2]

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

[effbiae]

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

[charlie]

one way
q)((select distinct date from t)cross select distinct sym from t)pj t

[liam_baron]

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}

pad t

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)

q)pad t

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