Feedback

×

• Feedback
• Was this page helpful?*
  • Yes
  • No
• Do you have any additional feedback?
  • Yes
  • No
• Additional Feedback

Joins

When working with datasets, we often need to merge data from different sources. To do this, we need keys on which to join our data.

Let’s use the US Flights 2015 datasets. We have three datasets.

First, we have information on each flight.

import verticapy as vp

flights  = vp.read_csv("flights.csv")
flights.head(100)

	123 YEAR Int 100%	...	123 LATE_AIRCRAFT_DELAY Int 32%	123 WEATHER_DELAY Int 32%
1	2015	...	92	0
2	2015	...	0	0
3	2015	...	[null]	[null]
4	2015	...	[null]	[null]
5	2015	...	[null]	[null]
6	2015	...	0	0
7	2015	...	0	0
8	2015	...	[null]	[null]
9	2015	...	[null]	[null]
10	2015	...	[null]	[null]
11	2015	...	0	0
12	2015	...	[null]	[null]
13	2015	...	[null]	[null]
14	2015	...	[null]	[null]
15	2015	...	[null]	[null]
16	2015	...	[null]	[null]
17	2015	...	23	0
18	2015	...	[null]	[null]
19	2015	...	0	0
20	2015	...	[null]	[null]
21	2015	...	0	0
22	2015	...	[null]	[null]
23	2015	...	[null]	[null]
24	2015	...	[null]	[null]
25	2015	...	[null]	[null]
26	2015	...	47	0
27	2015	...	0	0
28	2015	...	50	0
29	2015	...	[null]	[null]
30	2015	...	[null]	[null]
31	2015	...	[null]	[null]
32	2015	...	[null]	[null]
33	2015	...	[null]	[null]
34	2015	...	[null]	[null]
35	2015	...	[null]	[null]
36	2015	...	[null]	[null]
37	2015	...	[null]	[null]
38	2015	...	0	0
39	2015	...	184	0
40	2015	...	[null]	[null]
41	2015	...	[null]	[null]
42	2015	...	46	0
43	2015	...	[null]	[null]
44	2015	...	[null]	[null]
45	2015	...	[null]	[null]
46	2015	...	[null]	[null]
47	2015	...	35	0
48	2015	...	[null]	[null]
49	2015	...	[null]	[null]
50	2015	...	0	0
51	2015	...	74	0
52	2015	...	[null]	[null]
53	2015	...	[null]	[null]
54	2015	...	[null]	[null]
55	2015	...	[null]	[null]
56	2015	...	0	0
57	2015	...	0	0
58	2015	...	[null]	[null]
59	2015	...	[null]	[null]
60	2015	...	[null]	[null]
61	2015	...	[null]	[null]
62	2015	...	[null]	[null]
63	2015	...	[null]	[null]
64	2015	...	[null]	[null]
65	2015	...	[null]	[null]
66	2015	...	[null]	[null]
67	2015	...	[null]	[null]
68	2015	...	[null]	[null]
69	2015	...	[null]	[null]
70	2015	...	[null]	[null]
71	2015	...	96	0
72	2015	...	[null]	[null]
73	2015	...	[null]	[null]
74	2015	...	[null]	[null]
75	2015	...	[null]	[null]
76	2015	...	[null]	[null]
77	2015	...	[null]	[null]
78	2015	...	[null]	[null]
79	2015	...	[null]	[null]
80	2015	...	[null]	[null]
81	2015	...	[null]	[null]
82	2015	...	[null]	[null]
83	2015	...	[null]	[null]
84	2015	...	[null]	[null]
85	2015	...	[null]	[null]
86	2015	...	[null]	[null]
87	2015	...	[null]	[null]
88	2015	...	0	0
89	2015	...	[null]	[null]
90	2015	...	[null]	[null]
91	2015	...	0	0
92	2015	...	[null]	[null]
93	2015	...	70	0
94	2015	...	[null]	[null]
95	2015	...	[null]	[null]
96	2015	...	[null]	[null]
97	2015	...	[null]	[null]
98	2015	...	[null]	[null]
99	2015	...	114	0
100	2015	...	110	0

Second, we have information on each airport.

airports = vp.read_csv("airports.csv")
airports.head(100)

	Abc IATA_CODE Varchar(20) 100%	...	🌎 LATITUDE Float 100%	🌎 LONGITUDE Float 100%
1	ABI	...	32.41132	-99.6819
2	ABR	...	45.44906	-98.42183
3	ACY	...	39.45758	-74.57717
4	AEX	...	31.32737	-92.54856
5	ALB	...	42.74812	-73.80298
6	ALO	...	42.55708	-92.40034
7	ATL	...	33.64044	-84.42694
8	AVL	...	35.43619	-82.54181
9	BGM	...	42.20848	-75.97961
10	BHM	...	33.56294	-86.75355
11	BMI	...	40.47799	-88.91595
12	BOI	...	43.56444	-116.22278
13	BOS	...	42.36435	-71.00518
14	BQN	...	18.49486	-67.12944
15	BTM	...	45.9548	-112.49746
16	BTR	...	30.53316	-91.14963
17	BUF	...	42.94052	-78.73217
18	CAK	...	40.91631	-81.44247
19	CEC	...	41.78016	-124.23653
20	CHA	...	35.03527	-85.20379
21	CHS	...	32.89865	-80.04051
22	CLD	...	33.12723	-117.27873
23	CRW	...	38.37315	-81.59319
24	DAB	...	29.17992	-81.05806
25	DAY	...	39.90238	-84.21938
26	DCA	...	38.85208	-77.03772
27	DHN	...	31.32134	-85.44963
28	DIK	...	46.79739	-102.80195
29	DLH	...	46.84209	-92.19365
30	DSM	...	41.53493	-93.66068
31	EVV	...	38.03799	-87.53063
32	GNV	...	29.69006	-82.27178
33	GRB	...	44.48507	-88.12959
34	GRK	...	31.0649	-97.8278
35	GRR	...	42.88082	-85.52277
36	GSO	...	36.09775	-79.9373
37	GUC	...	38.53396	-106.93318
38	HLN	...	46.60682	-111.98275
39	HOB	...	32.68753	-103.21703
40	HPN	...	41.06696	-73.70757
41	IAH	...	29.98047	-95.33972
42	ILG	...	39.67872	-75.60653
43	ISP	...	40.79524	-73.10021
44	JAX	...	30.49406	-81.68786
45	JFK	...	40.63975	-73.77893
46	LAR	...	41.31205	-105.67499
47	LAW	...	34.56771	-98.41664
48	LAX	...	33.94254	-118.40807
49	MCO	...	28.42889	-81.31603
50	MFR	...	42.37423	-122.8735
51	MKG	...	43.16949	-86.23822
52	MLI	...	41.44853	-90.50754
53	MLU	...	32.51087	-92.03769
54	MOB	...	30.69142	-88.24283
55	MQT	...	46.35364	-87.39536
56	MRY	...	36.58698	-121.84295
57	MSN	...	43.13986	-89.33751
58	OMA	...	41.30252	-95.89417
59	OME	...	64.5122	-165.44525
60	ORD	...	41.9796	-87.90446
61	ORH	...	42.26734	-71.87571
62	OTH	...	43.41714	-124.24603
63	PBI	...	26.68316	-80.09559
64	PHL	...	39.87195	-75.24114
65	PIB	...	31.46715	-89.33706
66	PSE	...	18.0083	-66.56301
67	PSG	...	56.80165	-132.94528
68	RDM	...	44.25407	-121.14996
69	RDU	...	35.87764	-78.78747
70	ROA	...	37.32547	-79.97543
71	RSW	...	26.53617	-81.75517
72	SBA	...	34.42621	-119.84037
73	SBN	...	41.70895	-86.31847
74	SCE	...	40.85121	-77.8463
75	SEA	...	47.44898	-122.30931
76	SMF	...	38.69542	-121.59077
77	SMX	...	34.89925	-120.45758
78	SNA	...	33.67566	-117.86822
79	SPI	...	39.84395	-89.67762
80	STL	...	38.74769	-90.35999
81	STX	...	17.70189	-64.79856
82	SWF	...	41.50409	-74.10484
83	TPA	...	27.97547	-82.53325
84	TRI	...	36.47521	-82.40742
85	TTN	...	40.27669	-74.81347
86	TUL	...	36.19837	-95.88824
87	YAK	...	59.50336	-139.66023
88	ABY	...	31.53552	-84.19447
89	ACK	...	41.25305	-70.06018
90	ACT	...	31.61129	-97.23052
91	ADQ	...	57.74997	-152.49386
92	AUS	...	30.19453	-97.66987
93	BDL	...	41.93887	-72.68323
94	BET	...	60.77978	-161.838
95	BFL	...	35.4336	-119.05677
96	BLI	...	48.79275	-122.53753
97	BRD	...	46.39786	-94.13723
98	BTV	...	44.473	-73.15031
99	CAE	...	33.93884	-81.11954
100	CID	...	41.88459	-91.71087

Third, we have the names of each airline.

airlines = vp.read_csv("airlines.csv")
airlines.head(100)

	Abc IATA_CODE Varchar(20) 100%	Abc AIRLINE Varchar(56) 100%
1	AA	American Airlines Inc.
2	VX	Virgin America
3	AS	Alaska Airlines Inc.
4	B6	JetBlue Airways
5	DL	Delta Air Lines Inc.
6	HA	Hawaiian Airlines Inc.
7	MQ	American Eagle Airlines Inc.
8	NK	Spirit Air Lines
9	EV	Atlantic Southeast Airlines
10	F9	Frontier Airlines Inc.
11	WN	Southwest Airlines Co.
12	OO	Skywest Airlines Inc.
13	UA	United Air Lines Inc.
14	US	US Airways Inc.

Notice that each dataset has a primary or secondary key on which to join the data. For example, we can join the flights dataset to the airlines and airport datasets using the corresponding IATA code.

To join datasets in VerticaPy, use the join() method.

help(vp.vDataFrame.join)
Help on function join in module verticapy.core.vdataframe._join_union_sort:

join(self, input_relation: Annotated[Union[str, ForwardRef('vDataFrame')], ''], on: Union[NoneType, tuple, dict, list] = None, on_interpolate: Optional[dict] = None, how: Literal['left', 'right', 'cross', 'full', 'natural', 'self', 'inner', None] = 'natural', expr1: Optional[Annotated[Union[str, list[str], ForwardRef('StringSQL'), list['StringSQL']], '']] = None, expr2: Optional[Annotated[Union[str, list[str], ForwardRef('StringSQL'), list['StringSQL']], '']] = None) -> 'vDataFrame'

Joins the :py:class:`~vDataFrame` with another
one or an ``input_relation``.

.. warning::

    Joins  can  make  the  vDataFrame  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 :py:class:`~vDataFrame`,
    ``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 || '%'
    - 'linterpolate':
        key1 INTERPOLATE key2
    - 'rinterpolate':
        key2 INTERPOLATE 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:

    - 'jaro':
        ``JARO(key1, key2) operator2 x``
    - 'jarow':
        ``JARO_WINCKLER(key1, key2) operator2 x``
    - 'lev':
        ``LEVENSHTEIN(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 :py:class:`~vDataFrame`
    and ``relationB`` is the ``input_relation`` or
    the input :py:class:`~vDataFrame`.

on_interpolate: dict, optional
    Dictionary of all unique keys. This is used
    to join two event series together using some
    ordered attribute. Event series joins let you
    compare values from two series directly, rather
    than having to normalize the series to the same
    measurement interval. The dict must be similar
    to the following:
    ``{"relationA_key1": "relationB_key1" ...,"relationA_keyk": "relationB_keyk"}``
    where ``relationA`` is the current :py:class:`~vDataFrame`
    and ``relationB`` is the ``input_relation`` or the
    input :py:class:`~vDataFrame`.

how: str, optional
    Join Type.

    - left:
        Left Join.
    - right:
        Right Join.
    - cross:
        Cross Join.
    - full:
        Full Outer Join.
    - natural:
        Natural Join.
    - inner:
        Inner Join.

expr1: SQLExpression, optional
    List of the different columns in pure SQL
    to select from the current :py:class:`~vDataFrame`,
    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 :py:class:`~vDataFrame`,
    optionally as aliases. Aliases are recommended
    to avoid ambiguous names. For example: ``column``
    or ``column AS my_new_alias``.

Returns
-------
vDataFrame
    object result of the join.

Let’s use a left join to merge the airlines dataset and the flights dataset.

flights = flights.join(
    airlines,
    how = "left",
    on = {"airline": "IATA_CODE"},
    expr2 = ["AIRLINE AS airline_long"],
)
flights.head(100)

	123 YEAR Integer 100%	...	123 WEATHER_DELAY Integer 32%	Abc airline_long Varchar(56) 100%
1	2015	...	0	American Airlines Inc.
2	2015	...	0	American Airlines Inc.
3	2015	...	[null]	American Airlines Inc.
4	2015	...	[null]	American Airlines Inc.
5	2015	...	[null]	American Airlines Inc.
6	2015	...	0	American Airlines Inc.
7	2015	...	0	American Airlines Inc.
8	2015	...	[null]	American Airlines Inc.
9	2015	...	[null]	American Airlines Inc.
10	2015	...	[null]	American Airlines Inc.
11	2015	...	0	American Airlines Inc.
12	2015	...	[null]	American Airlines Inc.
13	2015	...	[null]	American Airlines Inc.
14	2015	...	[null]	American Airlines Inc.
15	2015	...	[null]	American Airlines Inc.
16	2015	...	[null]	American Airlines Inc.
17	2015	...	0	American Airlines Inc.
18	2015	...	[null]	American Airlines Inc.
19	2015	...	0	American Airlines Inc.
20	2015	...	[null]	American Airlines Inc.
21	2015	...	0	American Airlines Inc.
22	2015	...	[null]	American Airlines Inc.
23	2015	...	[null]	American Airlines Inc.
24	2015	...	[null]	American Airlines Inc.
25	2015	...	[null]	American Airlines Inc.
26	2015	...	0	American Airlines Inc.
27	2015	...	0	American Airlines Inc.
28	2015	...	0	American Airlines Inc.
29	2015	...	[null]	American Airlines Inc.
30	2015	...	[null]	American Airlines Inc.
31	2015	...	[null]	American Airlines Inc.
32	2015	...	[null]	American Airlines Inc.
33	2015	...	[null]	American Airlines Inc.
34	2015	...	[null]	American Airlines Inc.
35	2015	...	[null]	American Airlines Inc.
36	2015	...	[null]	American Airlines Inc.
37	2015	...	[null]	American Airlines Inc.
38	2015	...	0	American Airlines Inc.
39	2015	...	0	American Airlines Inc.
40	2015	...	[null]	American Airlines Inc.
41	2015	...	[null]	American Airlines Inc.
42	2015	...	0	American Airlines Inc.
43	2015	...	[null]	American Airlines Inc.
44	2015	...	[null]	American Airlines Inc.
45	2015	...	[null]	American Airlines Inc.
46	2015	...	[null]	American Airlines Inc.
47	2015	...	0	American Airlines Inc.
48	2015	...	[null]	American Airlines Inc.
49	2015	...	[null]	American Airlines Inc.
50	2015	...	0	American Airlines Inc.
51	2015	...	0	American Airlines Inc.
52	2015	...	[null]	American Airlines Inc.
53	2015	...	[null]	American Airlines Inc.
54	2015	...	[null]	American Airlines Inc.
55	2015	...	[null]	American Airlines Inc.
56	2015	...	0	American Airlines Inc.
57	2015	...	0	American Airlines Inc.
58	2015	...	[null]	American Airlines Inc.
59	2015	...	[null]	American Airlines Inc.
60	2015	...	[null]	American Airlines Inc.
61	2015	...	[null]	American Airlines Inc.
62	2015	...	[null]	American Airlines Inc.
63	2015	...	[null]	American Airlines Inc.
64	2015	...	[null]	American Airlines Inc.
65	2015	...	[null]	American Airlines Inc.
66	2015	...	[null]	American Airlines Inc.
67	2015	...	[null]	American Airlines Inc.
68	2015	...	[null]	American Airlines Inc.
69	2015	...	[null]	American Airlines Inc.
70	2015	...	[null]	American Airlines Inc.
71	2015	...	0	American Airlines Inc.
72	2015	...	[null]	American Airlines Inc.
73	2015	...	[null]	American Airlines Inc.
74	2015	...	[null]	American Airlines Inc.
75	2015	...	[null]	American Airlines Inc.
76	2015	...	[null]	American Airlines Inc.
77	2015	...	[null]	American Airlines Inc.
78	2015	...	[null]	American Airlines Inc.
79	2015	...	[null]	American Airlines Inc.
80	2015	...	[null]	American Airlines Inc.
81	2015	...	[null]	American Airlines Inc.
82	2015	...	[null]	American Airlines Inc.
83	2015	...	[null]	American Airlines Inc.
84	2015	...	[null]	American Airlines Inc.
85	2015	...	[null]	American Airlines Inc.
86	2015	...	[null]	American Airlines Inc.
87	2015	...	[null]	American Airlines Inc.
88	2015	...	0	American Airlines Inc.
89	2015	...	[null]	American Airlines Inc.
90	2015	...	[null]	American Airlines Inc.
91	2015	...	0	American Airlines Inc.
92	2015	...	[null]	American Airlines Inc.
93	2015	...	0	American Airlines Inc.
94	2015	...	[null]	American Airlines Inc.
95	2015	...	[null]	American Airlines Inc.
96	2015	...	[null]	American Airlines Inc.
97	2015	...	[null]	American Airlines Inc.
98	2015	...	[null]	American Airlines Inc.
99	2015	...	0	American Airlines Inc.
100	2015	...	0	American Airlines Inc.

Let’s use two left joins to get the information on the origin and destination airports.

flights = flights.join(
    airports,
    how = "left",
    on = {"origin_airport": "IATA_CODE"},
    expr2 = [
        "LATITUDE AS origin_lat",
        "LONGITUDE AS origin_lon",
    ],
)
flights = flights.join(
    airports,
    how = "left",
    on = {"destination_airport": "IATA_CODE"},
    expr2 = [
        "LATITUDE AS destination_lat",
        "LONGITUDE AS destination_lon",
    ],
)
flights.head(100)

	123 YEAR Integer 100%	...	123 destination_lat Float(22) 99%	123 destination_lon Float(22) 99%
1	2015	...	28.42889	-81.31603
2	2015	...	26.07258	-80.15275
3	2015	...	26.07258	-80.15275
4	2015	...	40.63975	-73.77893
5	2015	...	30.19453	-97.66987
6	2015	...	40.63975	-73.77893
7	2015	...	35.21401	-80.94313
8	2015	...	33.94254	-118.40807
9	2015	...	40.63975	-73.77893
10	2015	...	42.36435	-71.00518
11	2015	...	38.69542	-121.59077
12	2015	...	26.07258	-80.15275
13	2015	...	18.43942	-66.00183
14	2015	...	36.08036	-115.15233
15	2015	...	43.11887	-77.67238
16	2015	...	40.77724	-73.87261
17	2015	...	40.63975	-73.77893
18	2015	...	42.36435	-71.00518
19	2015	...	27.97547	-82.53325
20	2015	...	18.43942	-66.00183
21	2015	...	33.81772	-118.15161
22	2015	...	40.63975	-73.77893
23	2015	...	40.63975	-73.77893
24	2015	...	40.77724	-73.87261
25	2015	...	28.42889	-81.31603
26	2015	...	38.85208	-77.03772
27	2015	...	41.06696	-73.70757
28	2015	...	42.36435	-71.00518
29	2015	...	40.63975	-73.77893
30	2015	...	18.43942	-66.00183
31	2015	...	42.36435	-71.00518
32	2015	...	40.6925	-74.16866
33	2015	...	42.36435	-71.00518
34	2015	...	42.36435	-71.00518
35	2015	...	40.63975	-73.77893
36	2015	...	26.53617	-81.75517
37	2015	...	26.07258	-80.15275
38	2015	...	42.36435	-71.00518
39	2015	...	40.63975	-73.77893
40	2015	...	26.53617	-81.75517
41	2015	...	40.63975	-73.77893
42	2015	...	43.11887	-77.67238
43	2015	...	33.94254	-118.40807
44	2015	...	42.94052	-78.73217
45	2015	...	27.97547	-82.53325
46	2015	...	40.78839	-111.97777
47	2015	...	40.6925	-74.16866
48	2015	...	27.97547	-82.53325
49	2015	...	42.36435	-71.00518
50	2015	...	40.63975	-73.77893
51	2015	...	37.619	-122.37484
52	2015	...	40.63975	-73.77893
53	2015	...	28.42889	-81.31603
54	2015	...	38.85208	-77.03772
55	2015	...	42.36435	-71.00518
56	2015	...	33.43417	-112.00806
57	2015	...	26.07258	-80.15275
58	2015	...	36.08036	-115.15233
59	2015	...	33.94254	-118.40807
60	2015	...	26.07258	-80.15275
61	2015	...	18.0083	-66.56301
62	2015	...	37.36186	-121.92901
63	2015	...	40.63975	-73.77893
64	2015	...	29.99339	-90.25803
65	2015	...	30.49406	-81.68786
66	2015	...	26.53617	-81.75517
67	2015	...	28.42889	-81.31603
68	2015	...	40.63975	-73.77893
69	2015	...	33.94254	-118.40807
70	2015	...	38.85208	-77.03772
71	2015	...	33.94254	-118.40807
72	2015	...	40.6925	-74.16866
73	2015	...	42.36435	-71.00518
74	2015	...	18.43942	-66.00183
75	2015	...	28.42889	-81.31603
76	2015	...	27.39533	-82.55411
77	2015	...	40.63975	-73.77893
78	2015	...	40.63975	-73.77893
79	2015	...	26.68316	-80.09559
80	2015	...	42.36435	-71.00518
81	2015	...	40.63975	-73.77893
82	2015	...	30.19453	-97.66987
83	2015	...	40.78839	-111.97777
84	2015	...	28.42889	-81.31603
85	2015	...	42.36435	-71.00518
86	2015	...	41.06696	-73.70757
87	2015	...	42.36435	-71.00518
88	2015	...	38.85208	-77.03772
89	2015	...	41.50409	-74.10484
90	2015	...	26.07258	-80.15275
91	2015	...	18.43942	-66.00183
92	2015	...	36.08036	-115.15233
93	2015	...	35.87764	-78.78747
94	2015	...	40.63975	-73.77893
95	2015	...	41.93887	-72.68323
96	2015	...	42.36435	-71.00518
97	2015	...	40.63975	-73.77893
98	2015	...	18.49486	-67.12944
99	2015	...	42.36435	-71.00518
100	2015	...	42.36435	-71.00518

To avoid duplicate information, splitting the data into different tables is very important. Just imagine: what if we wrote the longitude and the latitude of the destination and origin airports for each flight? It would add way too many duplicates and drastically impact the volume of the data.

Cross joins are special: they don’t need a key. Cross joins are used to perform mathematical operations.

Let’s use a cross join of the airports dataset on itself to compute the distance between every airport.

distances = airports.join(
    airports,
    how = "cross",
    expr1 = [
        "IATA_CODE AS airport1",
        "LATITUDE AS airport1_latitude",
        "LONGITUDE AS airport1_longitude"
    ],
    expr2 = [
        "IATA_CODE AS airport2",
        "LATITUDE AS airport2_latitude",
        "LONGITUDE AS airport2_longitude",
    ],
)
distances.filter("airport1 != airport2")

import verticapy.sql.functions as fun

distances["distance"] = fun.distance(
    distances["airport1_latitude"],
    distances["airport1_longitude"],
    distances["airport2_latitude"],
    distances["airport2_longitude"],
)

	123 distance Float(22)
1	7450.70997424659
2	6421.84779178945
3	5391.61496246993
4	6523.04980811255
5	4844.61059668544
6	5276.14927676841
7	4972.32495308659
8	7287.89561709663
9	2126.1820031486
10	7269.62662402063
11	6281.2924573778
12	5231.27187027905
13	5165.70779431547
14	7334.43604776347
15	6825.29092241442
16	5492.87923122046
17	5480.20089285439
18	6655.0263408654
19	5358.90426145315
20	6849.70391597978

VerticaPy offers many powerful options for joining datasets.

In the next lesson, we’ll learn how to deal with duplicates.