KX Community

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

Home Forums kdb+ kdb equivalent of pandas groupby

  • kdb equivalent of pandas groupby

    Posted by powerpeanuts on November 22, 2021 at 12:00 am

    Example:

    trade:([]time:`time$();sym:`symbol$();price:`float$();size:`int$());

    `trade insert(09:30:00.000;`a;10.75;100);
    `trade insert(09:31:00.000;`a;11.75;100);
    `trade insert(09:32:00.000;`a;13.2;100);
    `trade insert(09:30:00.000;`b;100.75;100);
    `trade insert(09:31:00.000;`b;106.95;100);
    `trade insert(09:32:00.000;`b;123.95;100);

     

    I would like to calculate the change in price over time for each sym:
    select sym, time, change:price-prev price by sym from trade

     

     

    It seems the above query is not working well. How should I modify it?

    powerpeanuts replied 3 months ago 3 Members · 2 Replies
  • 2 Replies
  • davidcrossey

    Member
    November 22, 2021 at 12:00 am

    Hi powerpeanuts,

    I would keep it simple as you have it, and change select to update in your example as follows:

    q)update change:0^price-prev[price] by sym from trade 
    time       sym price size change 
    ----------------------------------- 
    09:30:00.000 a 10.75  100 0 
    09:31:00.000 a 11.75  100 1 
    09:32:00.000 a 13.2   100 1.45 
    09:30:00.000 b 100.75 100 0 
    09:31:00.000 b 106.95 100 6.2 
    09:32:00.000 b 123.95 100 17

    Note, I’ve also used fill (^) here to set the first change value for each sym as 0; this is optional and you can leave it as null if you prefer.

    Hope this helps and answers your question.

    Kind regards,

    David

  • Laura

    Administrator
    November 22, 2021 at 12:00 am

    For this query you are better using the ‘update’ query rather than ‘select’.

     

    This will show you the updated trade table but it will not save it in place as the new trade table. If you want to save the new column to the trade table permanently then use `trades in the update statement, i.e. update change:price-prev price by sym from `trade

    Here’s a link if you want to read more about the update statement.

    https://code.kx.com/q4m3/9_Queries_q-sql/#95-the-update-template

     

Log in to reply.