align

New in version 1.30.20.

Syntax

align(left, right, [how=’outer’], [byRow], [view=true])

Arguments

left and right are both matrices with column and/or row labels.

how indicates the join method with which the two matrices are aligned. The matrices are aligned on the column labels and/or row labels. It can be ‘outer’ (or ‘fj’), ‘inner’ (or ‘ej’), ‘left’ (or ‘lj’) or ‘asof (‘aj’)’. The default value is ‘outer’, indicating outer join.

byRow is a Boolean or NULL value.

true: align the matrices on row labels.
false: align on the column labels.
NULL (default): align on the row labels and the column labels. Specify how in the format of “<row_alignment>,<column alignment>”, e.g., how="outer,inner". Do not add a space or special character before or after the comma. If the same alignment method is used on rows and columns, it only needs to be specified once, e.g., how="inner".

Note: The left and right matrices must both have the required labels (based on the value of byRow). The columns with the same label in both tables must have compatible data type. The supported data types and compatibility rules are as follows:

integral (INT, SHORT, LONG and CHAR are compatible data types)
floating (FLOAT and DOUBLE are compatible )
temporal
STRING and SYMBOL (compatible data types)

view is a Boolean value. The default value is true, indicating the result will be a view of the original matrix (shallow copy) and changes made to the original matrix will be reflected in the view. If set to false, the result will be a new matrix (deep copy).

Details

Align the left and right matrices based on row labels and/or column labels (specified by byRow) using the join method specified by how. Return a tuple with 2 aligned matrices.

Note:

The aligned matrices do not keep the attributes of the original matrices. For instance, an indexed matrix will no longer have index after the alignment.
To add column/row labels to a matrix, call rename!. Use the SQL keywords exec and pivot by to create a matrix with the columns specified in the pivot by clause as the matrix labels (see pivot by).

Context

Prior to version 1.30.20/2.10.0, matrices must be converted to indexed matrices/series for binary operations. As the matrix/series will be aligned on index (with “outer” join), they must be monotonically increasing.

With the align function, alignment between matrices are more flexible in the following aspects:

Alignment between non-indexed matrices are supported. The matrices can be aligned on column/row labels which do not have to be monotonically increasing.
More options for the alignment methods.

Examples

// align matrices with overlapping labels
$ x1 = [09:00:00, 09:00:01, 09:00:03]
$ x2 = [09:00:00, 09:00:03, 09:00:03, 09:00:04]
$ m1 = matrix(1 2 3, 2 3 4, 3 4 5).rename!(x1)
$ m2 = matrix(11 12 13, 12 13 14, 13 14 15, 14 15 16).rename!(x2)
$ a, b = align(m1, m2, 'fj', false);
$ a;

09:00:00	09:00:01	09:00:03	09:00:03
1	2	3	3
2	3	4	4
3	4	5	5

$ b;

09:00:00	09:00:03	09:00:03	09:00:04
11	12	13	14
12	13	14	15
13	14	15	16

$ a+b;

09:00:00	09:00:03	09:00:03
16	19	20
12	15	16
14	17	18

$ m = align(m1, m2, 'aj', false);
$ m[0];

09:00:00	09:00:01	09:00:03
1	2	3
2	3	4
3	4	5

$ m[1];

09:00:00	09:00:01	09:00:03
11	11	13
12	12	14
13	13	15

//create table pt for prices and vt for trading volumes
$ timestamp = [09:00:00, 09:00:02, 09:00:03, 09:00:06, 09:00:08]
$ id= ['st1', 'st2', 'st1', 'st1', 'st2']
$ price = [197.8, 197.5, 198.4, 198.6, 198.6]
$ pt = table(timestamp, id, price)

$ timestamp = [09:00:00, 09:00:01, 09:00:02, 09:00:05, 09:00:08]
$ id = ['st1', 'st2', 'st2', 'st3', 'st2']
$ vol = [200, 300, 150, 200, 180]
$ vt = table(timestamp, id, vol)

// convert vt and pt to matrices. Use the columns specified by “pivot by” as labels for the matrices
$ m1 = exec vol from vt pivot by timestamp, id
$ m2 = exec price from pt pivot by timestamp, id

// align the matrices using the full join method
$ m = align(m1, m2, how='aj,fj')

// get the matrix of the total trading value
$ re = m[0] * m[1]
$ re;

label	st1	st2
09:00:00	39560
09:00:01
09:00:02		29625
09:00:03
09:00:05
09:00:08		35748