Teradata Package for Python Function Reference on VantageCloud Lake - join - Teradata Package for Python - Look here for syntax, methods and examples for the functions included in the Teradata Package for Python.
Teradata® Package for Python Function Reference on VantageCloud Lake
- Deployment
- VantageCloud
- Edition
- Lake
- Product
- Teradata Package for Python
- Release Number
- 20.00.00.03
- Published
- December 2024
- ft:locale
- en-US
- ft:lastEdition
- 2024-12-19
- dita:id
- TeradataPython_FxRef_Lake_2000
- Product Category
- Teradata Vantage
- teradataml.geospatial.geodataframe.GeoDataFrame.join = join(self, other, on=None, how='left', lsuffix=None, rsuffix=None)
- DESCRIPTION:
Joins two different teradataml GeoDataFrames together based on column comparisons
specified in argument 'on' and type of join is specified in the argument 'how'.
Supported join operations are:
• Inner join: Returns only matching rows, non matching rows are eliminated.
• Left outer join: Returns all matching rows plus non matching rows from the left table.
• Right outer join: Returns all matching rows plus non matching rows from the right table.
• Full outer join: Returns all rows from both tables, including non matching rows.
• Cross join: Returns all rows from both tables where each row from the first table
is joined with each row from the second table. The result of the join
is a cartesian cross product.
Note: For a cross join, the 'on' argument is ignored.
Supported join operators are =, ==, <, <=, >, >=, <> and != (= and <> operators are
not supported when using GeoDataFrame columns as operands).
Notes:
1. When multiple join conditions are given as a list string/ColumnExpression,
they are joined using AND operator.
2. Two or more on conditions can be combined using & and | operators
and can be passed as single ColumnExpression.
You can use (df1.a == df1.b) & (df1.c == df1.d) in place of
[df1.a == df1.b, df1.c == df1.d].
3. Two or more on conditions can not be combined using pythonic 'and'
and 'or'.
You can use (df1.a == df1.b) & (df1.c == df1.d) in place of
[df1.a == df1.b and df1.c == df1.d].
4. Performing self join using same GeoDataFrame object in 'other'
argument is not supported. In order to perform self join,
first create aliased GeoDataFrame using alias() API and pass it
for 'other' argument. Refer to Example 4 in EXAMPLES section.
PARAMETERS:
other:
Required Argument.
Specifies the right teradataml DataFrame/GeoDataFrame on which join is to
be performed.
Types: teradataml GeoDataFrame or teradataml DataFrame
on:
Optional argument when "how" is "cross", otherwise required.
If specified when "how" is "cross", it is ignored.
Specifies list of conditions that indicate the columns to be join keys.
It can take the following forms:
• String comparisons, in the form of "col1 <= col2", where col1 is
the column of left GeoDataFrame df1 and col2 is the column of right
GeoDataFrame df2.
Examples:
1. ["a","b"] indicates df1.a = df2.a and df1.b = df2.b.
2. ["a = b", "c == d"] indicates df1.a = df2.b and df1.c = df2.d.
3. ["a <= b", "c > d"] indicates df1.a <= df2.b and df1.c > df2.d.
4. ["a < b", "c >= d"] indicates df1.a < df2.b and df1.c >= df2.d.
5. ["a <> b"] indicates df1.a != df2.b. Same is the case for ["a != b"].
• Column comparisons, in the form of df1.col1 <= df2.col2, where col1
is the column of left GeoDataFrame df1 and col2 is the column of right
GeoDataFrame df2.
Examples:
1. [df1.a == df2.a, df1.b == df2.b] indicates df1.a = df2.a AND df1.b = df2.b.
2. [df1.a == df2.b, df1.c == df2.d] indicates df1.a = df2.b AND df1.c = df2.d.
3. [df1.a <= df2.b & df1.c > df2.d] indicates df1.a <= df2.b AND df1.c > df2.d.
4. [df1.a < df2.b | df1.c >= df2.d] indicates df1.a < df2.b OR df1.c >= df2.d.
5. df1.a != df2.b indicates df1.a != df2.b.
• The combination of both string comparisons and comparisons as column expressions.
Examples:
1. ["a", df1.b == df2.b] indicates df1.a = df2.a AND df1.b = df2.b.
2. [df1.a <= df2.b, "c > d"] indicates df1.a <= df2.b AND df1.c > df2.d.
• ColumnExpressions containing FunctionExpressions which represent SQL functions
invoked on GeoDataFrame Columns.
Examples:
1. gdf1.points_col.within(gdf2.lines_col) == 1
2. (gdf2d.lines_col.is_closed == 1) & (gdf2d_alias.poly_col.perimeter > 0)
Types: str (or) ColumnExpression (or) List of strings(str) or ColumnExpressions
how:
Optional Argument.
Specifies the type of join to perform.
Default value is "left".
Permitted Values : "inner", "left", "right", "full" and "cross"
Types: str
lsuffix:
Optional Argument.
Specifies the suffix to be added to the left table columns.
Default Value: None.
Types: str
rsuffix:
Optional Argument.
Specifies the suffix to be added to the right table columns.
Default Value: None.
Types: str
lprefix:
Optional Argument.
Specifies the prefix to be added to the left table columns.
Default Value: None.
Types: str
rprefix:
Optional Argument.
Specifies the prefix to be added to the right table columns.
Default Value: None.
Types: str
RETURNS:
teradataml GeoDataFrame
RAISES:
TeradataMlException
EXAMPLES:
# Load the data to run the example.
>>> from teradataml import load_example_data, GeoDataFrame
>>> load_example_data("geodataframe", ["sample_cities", "sample_streets"])
>>> df1 = GeoDataFrame("sample_cities")
>>> df2 = GeoDataFrame("sample_streets")
>>>
# Print GeoDataFrame.
>>> df1
city_name city_shape
skey
0 Oceanville POLYGON ((1 1,1 3,6 3,6 0,1 1))
1 Seaside POLYGON ((10 10,10 20,20 20,20 15,10 10))
# Print GeoDataFrame.
>>> df2
street_name street_shape
skey
1 Coast Blvd LINESTRING (12 12,18 17)
1 Main Street LINESTRING (2 2,3 2,4 1)
# Example 1: Specifying on condition as string for common column.
>>> df1.join(other = df2, on = ["skey"], how = "inner", lsuffix = "t1", rsuffix = "t2")
t1_skey t2_skey city_name city_shape street_name street_shape
0 1 1 Seaside POLYGON ((10 10,10 20,20 20,20 15,10 10)) Coast Blvd LINESTRING (12 12,18 17)
1 1 1 Seaside POLYGON ((10 10,10 20,20 20,20 15,10 10)) Main Street LINESTRING (2 2,3 2,4 1)
>>>
# Example 2: Specifying on condition as ColumnExpression with left outer join.
>>> df1.join(df2, on = [df1.city_name == df2.street_name], how = "left", lsuffix = "t1", rsuffix = "t2")
t1_skey t2_skey city_name city_shape street_name street_shape
0 0 None Oceanville POLYGON ((1 1,1 3,6 3,6 0,1 1)) None None
1 1 None Seaside POLYGON ((10 10,10 20,20 20,20 15,10 10)) None None
>>>
# Example 3: Cross join "sample_cities" with "admissions_train", i.e.,
# joining teradataml GeoDataFrame with teradataml DataFrame.
>>> load_example_data("dataframe", "admissions_train")
>>> tdf = DataFrame("admissions_train")
>>> df3 = df1.join(other=tdf, how="cross", lsuffix="l", rsuffix="r")
>>> df3
city_name city_shape id masters gpa stats programming admitted
skey
1 Seaside POLYGON ((10 10,10 20,20 20,20 15,10 10)) 5 no 3.44 Novice Novice 0
1 Seaside POLYGON ((10 10,10 20,20 20,20 15,10 10)) 3 no 3.70 Novice Beginner 1
1 Seaside POLYGON ((10 10,10 20,20 20,20 15,10 10)) 1 yes 3.95 Beginner Beginner 0
0 Oceanville POLYGON ((1 1,1 3,6 3,6 0,1 1)) 38 yes 2.65 Advanced Beginner 1
0 Oceanville POLYGON ((1 1,1 3,6 3,6 0,1 1)) 5 no 3.44 Novice Novice 0
0 Oceanville POLYGON ((1 1,1 3,6 3,6 0,1 1)) 24 no 1.87 Advanced Novice 1
0 Oceanville POLYGON ((1 1,1 3,6 3,6 0,1 1)) 3 no 3.70 Novice Beginner 1
0 Oceanville POLYGON ((1 1,1 3,6 3,6 0,1 1)) 1 yes 3.95 Beginner Beginner 0
0 Oceanville POLYGON ((1 1,1 3,6 3,6 0,1 1)) 26 yes 3.57 Advanced Advanced 1
1 Seaside POLYGON ((10 10,10 20,20 20,20 15,10 10)) 24 no 1.87 Advanced Novice 1
>>>
# Example 4: Perform join on GeoDataFrames with 'on' condition
# having FunctionExpression.
>>> load_example_data("geodataframe", "sample_shapes")
>>> gdf = GeoDataFrame("sample_shapes")
>>> geo_df = gdf.select(["skey", "points", "linestrings"])
>>> geo_df2 = geo_df.alias("rhs_gdf")
>>> joined_geo = geo_df.join(geo_df2, geo_df.points.within(geo_df2.linestrings) == 1,
>>> how="inner", lprefix="l")
l_skey skey l_points points l_linestrings linestrings
0 1002 1007 POINT (1 3) MULTIPOINT (1 1,1 3,6 3,10 5,20 1) LINESTRING (1 3,3 0,0 1) MULTILINESTRING ((1 1,1 3,6 3),(10 5,20 1))
1 1009 1004 MULTIPOINT (10 20 30,40 50 60,70 80 80) POINT (10 20 30) MULTILINESTRING ((10 20 30,40 50 60),(70 80 80,90 100 110)) LINESTRING (10 20 30,40 50 60,70 80 80)
2 1007 1007 MULTIPOINT (1 1,1 3,6 3,10 5,20 1) MULTIPOINT (1 1,1 3,6 3,10 5,20 1) MULTILINESTRING ((1 1,1 3,6 3),(10 5,20 1)) MULTILINESTRING ((1 1,1 3,6 3),(10 5,20 1))
3 1005 1007 POINT (1 3 5) MULTIPOINT (1 1,1 3,6 3,10 5,20 1) LINESTRING (1 3 6,3 0 6,6 0 1) MULTILINESTRING ((1 1,1 3,6 3),(10 5,20 1))