Aggregated Pivot Table

Question: A pivot table is used to draw attention to useful information. It groups data based on key column(s) and rearranges the data based on the pivot column. Define a function 'pvt' that takes in a simple table, key column(s), pivot column, values column, and monadic aggregation function, and returns a pivot table with the function applied on each grouping.

More Information:

https://en.wikipedia.org/wiki/Pivot_table

Example

                                
                                q)q:select from quote where date in 2018.01.03 2018.01.04, sym in `A`AA`AAAP`AABA`AAC`AADR
// single key column
q)pvt[q;`date;`sym;`bid_price;avg]
date       A        AA       AAAP     AABA     AAC      AADR
----------------------------------------------------------------
2018.01.03 68.83324 53.91935 79.77654 72.88424 9.085549 60.43724
2018.01.04 68.93665 54.40963 77.70118 74.27877 9.047451 60.98878
// verify pivot table values
q)0!select avg bid_price by date,sym from q
date       sym  bid_price
-------------------------
2018.01.03 A    68.83324
2018.01.03 AA   53.91935
2018.01.03 AAAP 79.77654
2018.01.03 AABA 72.88424
2018.01.03 AAC  9.085549
..

// multiple key columns
q)pvt[q;`date`sym;`exchange;`bid_price;max]
date       sym  A     B     J     K     M     N     P     Q     T     V     X     Y     Z
---------------------------------------------------------------------------------------------
2018.01.03 A    69.42 69.49 69.45 69.48 69.37 69.49 69.49       69.49 69.46 69.48 69.47 69.49
2018.01.03 AA   55.13 55.13 55.12 55.13 54.82 55.13 55.13       55.13 55.08 55.07 55.13 55.13
2018.01.03 AAAP 81.7  81.76 81.76 81.75 0           81.75 81.76       81.74 81.74 81.76 81.75
2018.01.03 AABA 73.85 73.85 73.85 73.85 73.75       73.85 73.85       73.85 73.85 73.85 73.85
2018.01.03 AAC  9.25  9.27  9.23  9.25  9.23  9.27  9.25        9.27  9.23  9.25  9.25  9.25
..
// verify pivot table values
q)0!select max bid_price by date,sym,exchange from q
date       sym exchange bid_price
---------------------------------
2018.01.03 A   A        69.42
2018.01.03 A   B        69.49
2018.01.03 A   J        69.45
2018.01.03 A   K        69.48
2018.01.03 A   M        69.37
..
                                
                            

Solution

Tags:
functions tables
Searchable Tags
algorithms api architecture csv data structures dictionaries disk feedhandler finance functions ingestion ipc iterators math multithreading optimizations realtime sql streaming strings tables temporal websockets

Email sent!

Email not sent