cancel
Showing results for 
Search instead for 
Did you mean: 

kdb equivalent of pandas groupby

powerpeanuts
New Contributor

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

 

powerpeanuts_0-1637559983438.png

 

 

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

2 ACCEPTED SOLUTIONS

davidcrossey
Moderator Moderator
Moderator

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  

View solution in original post

aandrews
New Contributor

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

 

aandrews_1-1637571832226.png

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

 

View solution in original post

2 REPLIES 2

davidcrossey
Moderator Moderator
Moderator

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  

View solution in original post

aandrews
New Contributor

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

 

aandrews_1-1637571832226.png

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

 

View solution in original post