vastorbit.VastFrame.join¶

Joins the VastFrame with another one or an input_relation.

Warning

Joins can make the VastFrame structure heavier. It is recommended that you check the current structure using the current_relation method and save it with the to_db method, using the parameters inplace = True and relation_type = table.

Parameters:

input_relation (SQLRelation) – Relation to join with.
on (tuple | dict | list, optional) –
If using a list: List of 3-tuples. Each tuple must include (key1, key2, operator) — where key1 is the key of the VastFrame, key2 is the key of the input_relation, and operator is one of the following:
- ’=’:
  exact match
- ’<’:
  key1 < key2
- ’>’:
  key1 > key2
- ’<=’:
  key1 <= key2
- ’>=’:
  key1 >= key2
- ’llike’:
  key1 LIKE ‘%’ || key2 || ‘%’
- ’rlike’:
  key2 LIKE ‘%’ || key1 || ‘%’
Some operators need 5-tuples: (key1, key2, operator, operator2, x) where operator2 is a simple operator (=, >, <, <=, >=), x is a float or an integer, and operator is one of the following:
- ’lev’:
  LEVENSHTEIN_DISTANCE(key1, key2) operator2 x
If using a dictionary: This parameter must include all the different keys. It must be similar to the following: {"relationA_key1": "relationB_key1" ...,"relationA_keyk": "relationB_keyk"} where relationA is the current VastFrame and relationB is the input_relation or the input VastFrame.
how (str, optional) –
Join Type.
- left:
  Left Join.
- right:
  Right Join.
- cross:
  Cross Join.
- full:
  Full Outer Join.
- inner:
  Inner Join.
expr1 (SQLExpression, optional) – List of the different columns in pure SQL to select from the current VastFrame, optionally as aliases. Aliases are recommended to avoid ambiguous names. For example: column or column AS my_new_alias.
expr2 (SQLExpression, optional) – List of the different columns in pure SQL to select from the current VastFrame, optionally as aliases. Aliases are recommended to avoid ambiguous names. For example: column or column AS my_new_alias.
on_interpolate (dict, optional) –
Performs an interpolated (time-series “as-of”) join. It must be a dictionary of the form {"left_time_col": "right_time_col"} mapping the time column of the current VastFrame to the time column of the input_relation. For each left-hand row, the most recent right-hand row whose time is less than or equal to the left-hand time is matched (last-observation-carried-forward)

Note

Trino exposes no native interpolated / ASOF join, so vastorbit emulates it: both relations are interleaved on the time axis and the latest right-hand reading is propagated forward with LAST_VALUE(...) IGNORE NULLS. When on_interpolate is set, expr1 and expr2 must list explicit columns, any equality keys passed through on are used to partition the interpolation (e.g. independently per sensor or per region), and only how="left" (default) and how="inner" are supported.

For example, to enrich smart-meter consumption with the most recent weather reading at or before each measurement:
```
consumption.join(
    weather,
    how = "left",
    on_interpolate = {"dateUTC": "dateUTC"},
    expr1 = ["dateUTC", "meterID", "value"],
    expr2 = ["humidity", "temperature"],
)
```

Returns:

object result of the join.

Return type:

VastFrame

Examples

Let’s begin by importing vastorbit.

import vastorbit as vo

Hint

By assigning an alias to vastorbit, we mitigate the risk of code collisions with other libraries. This precaution is necessary because vastorbit uses commonly known function names like “average” and “median”, which can potentially lead to naming conflicts. The use of an alias ensures that the functions from vastorbit are used as intended without interfering with functions from other libraries.

Let us create two VastFrame which we can JOIN for this example:

employees_data = vo.VastFrame(
    {
        "employee_id": [1, 2, 3, 4],
        "employee_name": ['Alice', 'Bob', 'Charlie', 'David'],
        "department_id": [101, 102, 101, 103],
    },
)

departments_data = vo.VastFrame(
    {
        "department_id": [101, 102, 104],
        "department_name": ['HR', 'Finance', 'Marketing'],
    }
)

	123 employee_id Integer	Abc employee_name Varchar(7)	123 department_id Integer
1	1	Alice	101
2	4	David	103
3	3	Charlie	101
4	2	Bob	102

Rows: 1-4 | Columns: 3

	123 department_id Integer	Abc department_name Varchar(9)
1	104	Marketing
2	101	HR
3	102	Finance

Rows: 1-3 | Columns: 2

Let us look at the different type of JOINs available below:

INNER JOIN
LEFT JOIN
RIGHT JOIN
FULL JOIN

After that we will also have a look at:

Other operators.
Special operators like Levenshtein distance.

INNER JOIN¶

We can conveniently JOIN the two VastFrame using the key column. Let us perform an INNER JOIN. INNER JOIN is executed to combine rows from both the main table and the input_relation based on a specified condition. Only the rows with matching values in the specified column are included in the result. If there is no match, those rows are excluded from the output.

result = employees_data.join(
    input_relation = departments_data,
    on = [("department_id", "department_id", "=")],
    how = "inner",
    expr1 = [
        "employee_id AS ID",
        "employee_name AS Name",
    ],
    expr2 = ["department_name AS Dep"],
)

	123 ID Integer	Abc Name Varchar(7)	Abc Dep Varchar(9)
1	3	Charlie	HR
2	1	Alice	HR
3	2	Bob	Finance

Rows: 1-3 | Columns: 3

LEFT JOIN¶

Similarly we can perform a LEFT JOIN which ensures that all rows from the main table are included in the result, and matching rows from the input_relation are included if they exist. If there is no match, the columns from the input relation will contain NULL values for the corresponding rows in the result.

left_join_result = employees_data.join(
    input_relation = departments_data,
    on = [("department_id", "department_id", "=")],
    how = "left",
    expr1 = ["employee_id AS ID", "employee_name AS Name"],
    expr2 = ["department_name AS Dep"],
)

	123 ID Integer	Abc Name Varchar(7)	Abc Dep Varchar(9)
1	1	Alice	HR
2	3	Charlie	HR
3	4	David	[null]
4	2	Bob	Finance

Rows: 1-4 | Columns: 3

RIGHT JOIN¶

A RIGHT JOIN is employed to include all rows from the input_relation in the result, regardless of whether there are matching values in the main table. Rows from the main table are included if there are matching values, and for non-matching rows, the columns from the main table will contain NULL values in the result.

right_join_result = employees_data.join(
    input_relation = departments_data,
    on = [("department_id", "department_id", "=")],
    how = "right",
    expr1 = ["employee_id AS ID", "employee_name AS Name"],
    expr2 = ["department_name AS Dep"],
)

	123 ID Integer	Abc Name Varchar(7)	Abc Dep Varchar(9)
1	[null]	[null]	Marketing
2	2	Bob	Finance
3	3	Charlie	HR
4	1	Alice	HR

Rows: 1-4 | Columns: 3

FULL JOIN¶

A FULL JOIN is utilized to include all rows from both the main table and the input_relation in the result. Matching rows are included based on the specified condition, and for non-matching rows in either table, the columns from the non-matching side will contain NULL values in the result. This ensures that all rows from both tables are represented in the output.

full_join_result = employees_data.join(
    input_relation = departments_data,
    on = [("department_id", "department_id", "=")],
    how = "full",
    expr1 = ["employee_id AS ID", "employee_name AS Name"],
    expr2 = ["department_name AS Dep"],
)

	123 ID Integer	Abc Name Varchar(7)	Abc Dep Varchar(9)
1	4	David	[null]
2	1	Alice	HR
3	3	Charlie	HR
4	2	Bob	Finance
5	[null]	[null]	Marketing

Rows: 1-5 | Columns: 3

OTHER OPERATORS¶

Let us explore some additional features of joins. For that let us create another table:

additional_departments_data = vo.VastFrame(
    {
        "department_size": [12, 8, 8, 10],
        "department": ['HR', 'Fin', 'Mar', 'IT'],
    }
)

	123 department_size Integer	Abc department Varchar(3)
1	10	IT
2	8	Fin
3	8	Mar
4	12	HR

Rows: 1-4 | Columns: 2

Notice the names are a bit different than the “department_name” column in the previous department_data table. In such cases we can utilize the llike operator:

department_join = departments_data.join(
    input_relation = additional_departments_data,
    on = [("department_name", "department", "llike")],
    how = "inner",
    expr1 = ["department_id AS ID", "department_name AS Dep"],
    expr2 = ["department_size AS Size"],
)

	123 ID Integer	Abc Name Varchar(7)	Abc Dep Varchar(9)
1	1	Alice	HR
2	4	David	[null]
3	2	Bob	Finance
4	3	Charlie	HR
5	[null]	[null]	Marketing

Rows: 1-5 | Columns: 3

Note

vastorbit provides an array of join options and diverse operators, delivering an exceptional user experience.

LEVENSHTEIN DISTANCE¶

vastorbit also allows you to JOIN tables using the Levenshtein distance. It is a string similarity metric used to compare the similarity between two strings. This method can be particularly useful in scenarios where slight spelling mistakes are expected between keys of different tables.