pivot
Syntax
pivot(func, funcArgs, rowAlignCol, colAlignCol)
Arguments
func is an aggregate function.
funcArgs are the parameters of func. It is a tuple if there are more than 1 parameter of func.
rowAlignCol is the grouping variable for the rows of the result.
colAlignCol is the grouping variable for the columns of the result.
rowAlignCol, colAlignCol, and each of the function argument in funcArgs are vectors of the same size.
Details
Rearrange the results of an aggregate function as a matrix.
Assume rowAlignCol has n unique elements and colAlignCol has m unique elements. The template will return an n (row) by m (column) matrix, with unique values of rowAlignCol as row labels and unique values of colAlignCol as column labels. For each element of the matrix, the given function is applied conditional on rowAlignCol and colAlignCol equal to corresponding values indicated by the cell’s row and column labels.
Examples
A trader needs to calculate the volume-weighted average prices (vwap) for each stock in every minute, and the pair-wise correlations of stock returns based on the vwap price series. The data are in a table with 4 columns: sym, price, qty, and trade_time.
We first use the pivot template to pivot the data to a vwap price matrix with the time as row label and the stock symbol as column label. Then we use the cross template to calculate the pairwise correlation.
$ syms=`600300`600400`600500$SYMBOL
$ sym=syms[0 0 0 0 0 0 0 1 1 1 1 1 1 1 2 2 2 2 2 2 2]
$ time=09:40:00+1 30 65 90 130 185 195 10 40 90 140 160 190 200 5 45 80 140 170 190 210
$ price=172.12 170.32 172.25 172.55 175.1 174.85 174.5 36.45 36.15 36.3 35.9 36.5 37.15 36.9 40.1 40.2 40.25 40.15 40.1 40.05 39.95
$ volume=100 * 10 3 7 8 25 6 10 4 5 1 2 8 6 10 2 2 5 5 4 4 3
$ t1=table(sym, time, price, volume);
$ t1;
sym |
time |
price |
volume |
|---|---|---|---|
600300 |
09:40:01 |
172.12 |
1000 |
600300 |
09:40:30 |
170.32 |
300 |
600300 |
09:41:05 |
172.25 |
700 |
600300 |
09:41:30 |
172.55 |
800 |
600300 |
09:42:10 |
175.1 |
2500 |
600300 |
09:43:05 |
174.85 |
600 |
600300 |
09:43:15 |
174.5 |
1000 |
600400 |
09:40:10 |
36.45 |
400 |
600400 |
09:40:40 |
36.15 |
500 |
600400 |
09:41:30 |
36.3 |
100 |
600400 |
09:42:20 |
35.9 |
200 |
600400 |
09:42:40 |
36.5 |
800 |
600400 |
09:43:10 |
37.15 |
600 |
600400 |
09:43:20 |
36.9 |
1000 |
600500 |
09:40:05 |
40.1 |
200 |
600500 |
09:40:45 |
40.2 |
200 |
600500 |
09:41:20 |
40.25 |
500 |
600500 |
09:42:20 |
40.15 |
500 |
600500 |
09:42:50 |
40.1 |
400 |
600500 |
09:43:10 |
40.05 |
400 |
600500 |
09:43:30 |
39.95 |
300 |
Align the data on the dimensions of time and sym, and calculate the vwap price for every minute in each minute:
$ stockprice=pivot(wavg, [t1.price, t1.volume], minute(t1.time), t1.sym)
$ stockprice.round(2);
label |
600300 |
600400 |
600500 |
|---|---|---|---|
09:40m |
171.7 |
36.28 |
40.15 |
09:41m |
172.41 |
36.3 |
40.25 |
09:42m |
175.1 |
36.38 |
40.13 |
09:43m |
174.63 |
36.99 |
40.01 |
The step above can also use the following SQL statement to get the same result:
$ stockreturn = each(ratios, stockprice)-1
$ stockreturn;
label |
600300 |
600400 |
600500 |
|---|---|---|---|
09:40m |
|||
09:41m |
0.004108 |
0.000459 |
0.002491 |
09:42m |
0.015602 |
0.002204 |
-0.003037 |
09:43m |
-0.002677 |
0.016871 |
-0.003006 |
Calculate stock returns in each minute:
$ cross(corr, stockreturn, stockreturn);
label |
600300 |
600400 |
600500 |
|---|---|---|---|
600300 |
1 |
-0.719182 |
-0.151824 |
600400 |
-0.719182 |
1 |
-0.577578 |
600500 |
-0.151824 |
-0.577578 |
1 |
Calculate pair-wise correlation:
$ pivot(count, price, minute(time), sym);
label |
600300 |
600400 |
600500 |
|---|---|---|---|
09:40m |
2 |
2 |
2 |
09:41m |
2 |
1 |
1 |
09:42m |
1 |
2 |
2 |
09:43m |
2 |
2 |
2 |
Count the number of observations within each minute for each stock:
$ pivot(last, price, minute(time), sym);
label |
600300 |
600400 |
600500 |
|---|---|---|---|
09:40m |
170.32 |
36.15 |
40.2 |
09:41m |
172.55 |
36.3 |
40.25 |
09:42m |
175.1 |
36.5 |
40.1 |
09:43m |
174.5 |
36.9 |
39.95 |