exact_join
exact_join
joins data from a pair of tables - a left and right table - based upon a set of match columns. The match columns establish key identifiers in the left table that will be used to find data in the right table. Any data types can be chosen as keys, and keys can be constructed from multiple values.
The output table contains all of the rows and columns of the left table plus additional columns containing data from the right table. For columns appended to the left table, row values equal the row values from the right table where the key values in the left and right tables are equal. If there are zero or multiple matches, the operation will fail.
Syntax
left.exact_join(
right: Table,
on: Union[str, Sequence[str]],
joins: Union[str, Sequence[str]] = None
) -> Table
Parameters
Parameter | Type | Description |
---|---|---|
table | Table | The table data is added from (the right table). |
on | Union[str, Sequence[str]] | Columns from the left and right tables used to join on.
|
joins optional | Union[str, Sequence[str]] | Columns from the right table to be added to the left table based on key may be specified in this list:
|
Returns
A new table containing all of the rows and columns of the left table plus additional columns containing data from the right table. For columns appended to the left table, row values equal the row values from the right table where the key values in the left and right tables are equal.
Examples
In the following example, the left and right tables are joined by the matching column, DeptID
.
from deephaven import new_table
from deephaven.column import string_col, int_col
from deephaven.constants import NULL_INT
left = new_table(
[
string_col("LastName", ["Rafferty", "Jones", "Steiner", "Robins", "Smith"]),
int_col("DeptID", [31, 33, 33, 34, 34]),
string_col(
"Telephone",
[
"(303) 555-0162",
"(303) 555-0149",
"(303) 555-0184",
"(303) 555-0125",
"",
],
),
]
)
right = new_table(
[
int_col("DeptID", [31, 33, 34, 35]),
string_col("DeptName", ["Sales", "Engineering", "Clerical", "Marketing"]),
string_col(
"DeptTelephone",
["(303) 555-0136", "(303) 555-0162", "(303) 555-0175", "(303) 555-0171"],
),
]
)
result = left.exact_join(table=right, on=["DeptID"])
- left
- right
- result
If the right table has columns that need renaming due to an initial name match, a new column name can be supplied in the third argument of the join. In the following example, Telephone
is renamed to DeptTelephone
.
from deephaven import new_table
from deephaven.column import string_col, int_col
from deephaven.constants import NULL_INT
left = new_table(
[
string_col("LastName", ["Rafferty", "Jones", "Steiner", "Robins", "Smith"]),
int_col("DeptID", [31, 33, 33, 34, 34]),
string_col(
"Telephone",
[
"(303) 555-0162",
"(303) 555-0149",
"(303) 555-0184",
"(303) 555-0125",
"",
],
),
]
)
right = new_table(
[
int_col("DeptID", [31, 33, 34, 35]),
string_col("DeptName", ["Sales", "Engineering", "Clerical", "Marketing"]),
string_col(
"Telephone",
["(303) 555-0136", "(303) 555-0162", "(303) 555-0175", "(303) 555-0171"],
),
]
)
result = left.exact_join(
table=right, on=["DeptID"], joins=["DeptName, DeptTelephone = Telephone"]
)
- left
- right
- result
In some cases, the matching columns have different names in the left and right table. Below, the left table has a column named DeptNumber
that needs to be matched to the column DeptID
in the right table. To perform this match, the second argument needs the name of each column in the left and right tables.
from deephaven import new_table
from deephaven.column import string_col, int_col
from deephaven.constants import NULL_INT
left = new_table(
[
string_col("LastName", ["Rafferty", "Jones", "Steiner", "Robins", "Smith"]),
int_col("DeptNumber", [31, 33, 33, 34, 34]),
string_col(
"Telephone",
[
"(303) 555-0162",
"(303) 555-0149",
"(303) 555-0184",
"(303) 555-0125",
"",
],
),
]
)
right = new_table(
[
int_col("DeptID", [31, 33, 34, 35]),
string_col("DeptName", ["Sales", "Engineering", "Clerical", "Marketing"]),
string_col(
"Telephone",
["(303) 555-0136", "(303) 555-0162", "(303) 555-0175", "(303) 555-0171"],
),
]
)
result = left.exact_join(
table=right,
on=["DeptNumber = DeptID"],
joins=["DeptName, DeptTelephone = Telephone"],
)
- left
- right
- result