KX Community

Find answers, ask questions, and connect with our KX Community around the world.
KX Community Guidelines

Home Forums kdb+ How can I apply a func to a grouped table?

Tagged: , ,

  • How can I apply a func to a grouped table?

    Posted by Cecilia on July 21, 2024 at 12:05 pm

    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.) …

    Cecilia replied 3 months, 2 weeks ago 2 Members · 8 Replies
  • 8 Replies
  • rocuinneagain

    Member
    July 22, 2024 at 9:48 am

    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 99

    Transforming 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
    • Cecilia

      Member
      July 24, 2024 at 2:42 am

      Thank you so very much!!

    • Cecilia

      Member
      July 27, 2024 at 3:50 am

      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 stock

      My t is like this:

      q)t
      stock AskOrder BidOrder
      -----------------------
      p 1 39
      e 76 97
      k 65 74

      I’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;
      • rocuinneagain

        Member
        July 31, 2024 at 6:41 pm
        select from t where 16<=(count;AskOrder) fby stock

        You’d want an fby for that filtering

        https://code.kx.com/q/ref/fby/

        • rocuinneagain

          Member
          July 31, 2024 at 8:41 pm

          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
          • Cecilia

            Member
            August 1, 2024 at 1:44 pm

            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…

            • rocuinneagain

              Member
              August 1, 2024 at 4:07 pm

              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

            • Cecilia

              Member
              August 2, 2024 at 2:06 pm

              Thank you so much! I reeeally appreciate your help!! 🤩🤩

Log in to reply.