outerJoin#

Purpose#

Performs a left outer join on two matrices based upon user-specified key columns.

Format#

C = outerJoin(A, ca, B, cb[, type])#

Parameters:

A (matrix or dataframe) – data to join
ca (scalar, vector or string) – key column indices or names from A
B (matrix or dataframe) – matrix to join with A
cb (scalar, vector or string) – key column indices or names from B

type (string) –

Optional argument, the type of join to perform. Default = "left". Options are:

”left”	Left outer join. Result is A merged with matched data from B. Unmatched rows contains missing values for columns of B.
”full”	Full outer join. Result is A merged with B. Unmatched rows from either side contain missing values for columns of the opposing symbol.

Returns:

C (matrix or dataframe) – result of join of A and B

Examples#

Basic example#

A = { 1 1.1,
      2 2.2,
      3 3.3 };

B = { 1  9,
      3 27,
      8 15 };

// Perform left outer-join, using the first
// columns of 'A' and 'B' as key columns
C = outerJoin(A, 1, B, 1);

After the code above, C equals:

0000000        1.1000000        9.0000000
0000000        2.2000000                .
0000000        3.3000000        27.000000

Continuing from the above example, if we perform a full outer join:

D = outerJoin(A, 1, B, 1, "full");

then unmatched data from B is also included, and D equals:

0000000        1.1000000        9.0000000
0000000        2.2000000                .
0000000        3.3000000        27.000000
0000000                .        15.000000

Join on two columns#

A = { 1 3.1 12 0.5,
      3 1.2 15 0.6,
      5 4.4 19 1.1,
      2 6.9 11 0.9 };

B = { 7 20 0.3 5,
      2 12 1.1 1,
      9 15 0.1 3 };

a_keys = { 1, 3 };
b_keys = { 4, 2 };

// Perform a left-outer join on A and B, based on matches
// from the 1st column of A with the 4th column of B
// and the 3rd column of A with the 2nd column of B
C = outerJoin(A, a_keys, B, b_keys);

After the code above, C equals:

3.1 12 0.5 2 1.1
1.2 15 0.6 9 0.1
4.4 19 1.1 .   .
6.9 11 0.9 .   .

Join dataframes based on a date variable#

// Load Tbill and US recession data
tbill_3mo = loadd(getGAUSSHome() $+ "examples/tbill_3mo.xlsx", "date($obs_date) + tbill_3m");
USREC = loadd(getGAUSSHome() $+ "examples/USREC.csv",  "date($DATE) + USREC");

// Perform left outer-join
tbill_rec = outerJoin(tbill_3mo, "obs_date", USREC, "DATE");

print tbill_rec[1:12,.];

will print out:

  obs_date         tbill_3m            USREC
1982-01-01        12.920000        1.0000000
1982-02-01        14.280000        1.0000000
1982-03-01        13.310000        1.0000000
1982-04-01        13.340000        1.0000000
1982-05-01        12.710000        1.0000000
1982-06-01        13.080000        1.0000000
1982-07-01        11.860000        1.0000000
1982-08-01        9.0000000        1.0000000
1982-09-01        8.1900000        1.0000000
1982-10-01        7.9700000        1.0000000
1982-11-01        8.3500000        1.0000000
1982-12-01        8.2000000        0.0000000

Remarks#

By default, outerJoin() performs a left outer-join, retaining only the key columns from the first input matrix.

The first columns of the output matrix C will be the columns of A in the same order as in A. The remaining columns of C will be the columns of B with the key columns removed.