Hello,
I have two matrices of two column vectors each. In each matrix, the first column contains a series of countries and the second column of the percentages referring to that country. I would like to create a new matrix containing:
- The countries column.
- The percentages of the first matrix in the second column.
- The percentages of the second matrix in the third column.
The countries, however, are not in the same order in the two starting matrices and sometimes they differ. I try to explain it with an example.
Let X1 and X2 be two matrices defined as follows:
X1 = { Germany 0.30,
France 0.25,
Italy 0.25,
Netherland 0.20 };
X2 = { Germany 0.50,
Italy 0.25,
France 0.20,
Spain 0.05 };I wish X3 was:
X3 = { Germany 0.30 0.50,
France 0.25 0.20,
Italy 0.25 0.25,
Netherland 0.20 0,
Spain 0 0.05 };thanks for your attention.
1 Answer
0
I think what you want is a full outer join. GAUSS has built-in functions for an inner join and for a left outer join.
All joins combine data based on a key column, country name in this case. Inner joins remove any observations where the key is not present in both matrices or datasets.
In your example above, Italy and the Netherlands would be removed and the output would be:
// Inner join
Germany 0.30 0.50
France 0.25 0.20
Italy 0.25 0.25A left outer join will keep all observations from the first matrix. The observations from the second matrix will be kept if their key is also in the first matrix. For example:
X1 = { Germany 0.30,
France 0.25,
Italy 0.25,
Netherland 0.20 };
X2 = { Germany 0.50,
Italy 0.25,
France 0.20,
Spain 0.05 };
// Perform left outer join using the first
// column of both input matrices as the keys
X3 = outerJoin(X1, 1, X2, 1);This code will assign X3 to be:
// left outer join
Germany 0.30 0.50
France 0.25 0.20
Italy 0.25 0.25
Netherland 0.20 .You can compute a full outer join by making sure the first matrix contains all keys. Any that are missing can be added with a row of missing values like this:
X1 = { Germany 0.30,
France 0.25,
Italy 0.25,
Netherland 0.20 };
X2 = { Germany 0.50,
Italy 0.25,
France 0.20,
Spain 0.05 };
// Find keys in X2, but not in X1
missing_keys = setdif(X2[.,1], X1[.,1], 2);
// Create a missing value
mv = { . };
// Add missing keys
X1_full = X1 | (missing_keys ~ reshape(mv, rows(missing_keys), cols(X1) - 1));
X3 = outerJoin(X1_full, 1, X2, 1);which will set X3 equal to:
Germany 0.3 0.5
France 0.25 0.2
Italy 0.25 0.25
Netherla 0.2 .
Spain . 0.05 
Your Answer
1 Answer
I think what you want is a full outer join. GAUSS has built-in functions for an inner join and for a left outer join.
All joins combine data based on a key column, country name in this case. Inner joins remove any observations where the key is not present in both matrices or datasets.
In your example above, Italy and the Netherlands would be removed and the output would be:
// Inner join
Germany 0.30 0.50
France 0.25 0.20
Italy 0.25 0.25A left outer join will keep all observations from the first matrix. The observations from the second matrix will be kept if their key is also in the first matrix. For example:
X1 = { Germany 0.30,
France 0.25,
Italy 0.25,
Netherland 0.20 };
X2 = { Germany 0.50,
Italy 0.25,
France 0.20,
Spain 0.05 };
// Perform left outer join using the first
// column of both input matrices as the keys
X3 = outerJoin(X1, 1, X2, 1);This code will assign X3 to be:
// left outer join
Germany 0.30 0.50
France 0.25 0.20
Italy 0.25 0.25
Netherland 0.20 .You can compute a full outer join by making sure the first matrix contains all keys. Any that are missing can be added with a row of missing values like this:
X1 = { Germany 0.30,
France 0.25,
Italy 0.25,
Netherland 0.20 };
X2 = { Germany 0.50,
Italy 0.25,
France 0.20,
Spain 0.05 };
// Find keys in X2, but not in X1
missing_keys = setdif(X2[.,1], X1[.,1], 2);
// Create a missing value
mv = { . };
// Add missing keys
X1_full = X1 | (missing_keys ~ reshape(mv, rows(missing_keys), cols(X1) - 1));
X3 = outerJoin(X1_full, 1, X2, 1);which will set X3 equal to:
Germany 0.3 0.5
France 0.25 0.2
Italy 0.25 0.25
Netherla 0.2 .
Spain . 0.05