-
How can I apply a func to a grouped table?
I have a table with 3 columns: stock, AskOrder, BidOrder.
I want to find 1/16 to 15/16th percentile of AskOrder and BidOrder for each stock.I’m thinking of something like this:
(I attached a screenshot of my code because if I put it down, its format will come out very odd.)
Obviously this code won’t work: I can’t name the columns differently (I want them to be like Ask_1 (means the 1/16th percentile), Ask_2,…Ask_15). And this can’t apply ‘f’ to each stock.
I would like the result be like this:
stock Ask_1 Ask_2 … Ask_15 Bid_1 … Bid_15
——————————————————
stock1 150(e.g.) …
stock2 300(e.g.) …
…
-
8 Replies
-
Sample table:
q)t:([] stock:10000?`1;AskOrder:10000?100;BidOrder:10000?100)
q)t
stock AskOrder BidOrder
-----------------------
n 85 43
p 40 15
n 9 47
j 10 64
i 87 88
i 79 87
m 46 41
h 92 63
g 86 39
......Function which returns a dictionary of percentiles:
q)f:{(`$x,/:string 1+til y)!az -1+(where deltas y xrank az:asc z),count z}Running the function returns a dictionary:
q)r:exec f["Ask_";4;AskOrder],f["Bid_";4;BidOrder] by stock from t
q)r
| AskOrder BidOrder
-| -------------------------------------------------------------------------
a| `Ask_1`Ask_2`Ask_3`Ask_4!25 50 74 99 `Bid_1`Bid_2`Bid_3`Bid_4!23 51 74 99
b| `Ask_1`Ask_2`Ask_3`Ask_4!26 49 77 99 `Bid_1`Bid_2`Bid_3`Bid_4!23 47 74 99
c| `Ask_1`Ask_2`Ask_3`Ask_4!23 49 74 99 `Bid_1`Bid_2`Bid_3`Bid_4!26 49 75 99
d| `Ask_1`Ask_2`Ask_3`Ask_4!29 53 78 99 `Bid_1`Bid_2`Bid_3`Bid_4!27 50 74 99
e| `Ask_1`Ask_2`Ask_3`Ask_4!22 46 73 99 `Bid_1`Bid_2`Bid_3`Bid_4!26 54 79 99
f| `Ask_1`Ask_2`Ask_3`Ask_4!25 49 75 99 `Bid_1`Bid_2`Bid_3`Bid_4!23 48 74 99
g| `Ask_1`Ask_2`Ask_3`Ask_4!26 49 73 99 `Bid_1`Bid_2`Bid_3`Bid_4!27 50 76 99
h| `Ask_1`Ask_2`Ask_3`Ask_4!24 51 77 99 `Bid_1`Bid_2`Bid_3`Bid_4!27 52 76 99
i| `Ask_1`Ask_2`Ask_3`Ask_4!24 48 74 99 `Bid_1`Bid_2`Bid_3`Bid_4!23 49 72 99
j| `Ask_1`Ask_2`Ask_3`Ask_4!23 46 72 99 `Bid_1`Bid_2`Bid_3`Bid_4!31 53 75 99
k| `Ask_1`Ask_2`Ask_3`Ask_4!21 44 72 99 `Bid_1`Bid_2`Bid_3`Bid_4!22 50 75 99
l| `Ask_1`Ask_2`Ask_3`Ask_4!24 49 73 99 `Bid_1`Bid_2`Bid_3`Bid_4!27 50 73 99
m| `Ask_1`Ask_2`Ask_3`Ask_4!23 45 70 99 `Bid_1`Bid_2`Bid_3`Bid_4!25 52 78 99
n| `Ask_1`Ask_2`Ask_3`Ask_4!26 52 77 99 `Bid_1`Bid_2`Bid_3`Bid_4!25 53 76 99
o| `Ask_1`Ask_2`Ask_3`Ask_4!22 50 75 99 `Bid_1`Bid_2`Bid_3`Bid_4!24 47 74 99
p| `Ask_1`Ask_2`Ask_3`Ask_4!24 51 76 99 `Bid_1`Bid_2`Bid_3`Bid_4!23 48 74 99Transforming the dictionary in to a table:
q){`stock xcols update stock:key x from (value x)[`AskOrder],'(value x)[`BidOrder]} r
stock Ask_1 Ask_2 Ask_3 Ask_4 Bid_1 Bid_2 Bid_3 Bid_4
-----------------------------------------------------
a 25 50 74 99 23 51 74 99
b 26 49 77 99 23 47 74 99
c 23 49 74 99 26 49 75 99
d 29 53 78 99 27 50 74 99
e 22 46 73 99 26 54 79 99
f 25 49 75 99 23 48 74 99
g 26 49 73 99 27 50 76 99
h 24 51 77 99 27 52 76 99
i 24 48 74 99 23 49 72 99
j 23 46 72 99 31 53 75 99
k 21 44 72 99 22 50 75 99
l 24 49 73 99 27 50 73 99
m 23 45 70 99 25 52 78 99
n 26 52 77 99 25 53 76 99
o 22 50 75 99 24 47 74 99
p 24 51 76 99 23 48 74 99-
Thank you so very much!!
-
I ran into another problem. It seems some AskOrder of the stock has less than 16 data, so it says
‘length
[1] f:{(`$x,/:string 1+til y)!az -1+(where deltas y xrank az:asc z),count z}
What should I do?
I’ve tried to filter out those stock using:
select from t where count each AskOrder > 16 by stock
However, it says
'stock
[3] select from t where count each AskOrder > 16 by stockMy t is like this:
q)t
stock AskOrder BidOrder
-----------------------
p 1 39
e 76 97
k 65 74I’ve also tried to use length:
l: select length: count each AskOrder by stock from t;
l: () xkey lengths;
select stock from l where length > 16;But it says:
'type
[3] select stock from l where length > 16;
-
select from t where 16<=(count;AskOrder) fby stock
You’d want an fby for that filtering
-
Best would be to update the function to pad out nulls when needed:
q)f:{i:az -1+(where deltas y xrank az:asc z),count z;(`$x,/:string 1+til y)!i,(y-count i)#0N}
q)r:exec f["Ask_";6;AskOrder],f["Bid_";6;BidOrder] by stock from st
q){`stock xcols update stock:key x from (value x)[`AskOrder],'(value x)[`BidOrder]} r
stock Ask_1 Ask_2 Ask_3 Ask_4 Ask_5 Ask_6 Bid_1 Bid_2 Bid_3 Bid_4 Bid_5 Bid_6
-----------------------------------------------------------------------------
a 17 28 59 61 64 34 49 52 90 92
b 41 46 74 92 94 97 26 30 70 82 88 93
c 6 35 35 37 96 2 30 30 38 58
d 4 4 7 10 66 96 12 23 38 39 45 61
e 4 21 49 68 91 92 28 59 66 84 97 97
f 10 18 45 65 83 91 8 39 43 64 77 78
g 5 16 16 30 59 62 84 84 90 97
h 48 51 56 59 71 12 45 47 82 95
i 10 15 37 48 59 91 24 37 52 68 69 84
j 0 57 57 66 74 36 63 63 73 83
k 14 19 43 46 58 60 30 37 50 53 90 99
l 25 29 33 49 59 98 6 26 59 60 68 93
m 9 65 65 74 88 12 64 64 66 77
n 14 14 40 40 98 44 44 73 73 93
o 32 46 50 63 70 80 20 32 54 89 93 94
p 4 48 48 51 92 23 40 41 74 88-
Thanks soo much! Only a small and last question. Is there anything special about the table?
I used
rev:{stock xcols update stock:key x from (value x)[AskOrder],'(value x)[BidOrder]} r; <br>ddd: string dt[i][date2];
save hsym$":/Users/void_dump/Desktop/Reverse/", ddd,"/rev.csv"</pre><p>ddd is a string of date, e.g. '2014.01.01'</p><p>I've done this several times before and they all work well. But this time it says</p><pre>'type<br> [0] save hsym$":/Users/void_dump/Desktop/Reverse/", ddd,"/rev.csv"
^I searched but didn’t see a solution. This rev table isn’t a keyed one, nor is it very special, I don’t see why there would be a bug here…
-
In my answer I use 0N which is the long null. If your table column type for AskOrder is float then the resulting column will be of mixed type. Check by calling meta on the result. save cannot write mixed type columns like this.
A good solution is to update f to ensure it uses a null of the correct datatype:
f:{i:az -1+(where deltas y xrank az:asc z),count z;(`$x,/:string 1+til y)!i,(y-count i)#z count z}The added bit is z count z in place of 0N
Indexing out of bounds on a q vector returns nulls of the dataype of the vector:
q)(0 1)2 / Long null returned
0N
q)(0 1f)2 / Float null returned
0n -
Thank you so much! I reeeally appreciate your help!! 🤩🤩
-
-
-
-
-
Log in to reply.