vDataFrame.join

In [ ]:
vDataFrame.join(input_relation, 
                on: dict = {},
                on_interpolate: dict = {},
                how: str = 'natural',
                expr1: list = ['*'],
                expr2: list = ['*'])

Joins the vDataFrame with another vDataFrame or an input relation.

⚠ Warning: Joins can significantly increase the memory consumption of your vDataFrame. You should always check the vDataFrame with the 'current_relation' method and save it to your Vertica database with the 'to_db' method with the parameters 'inplace = True' and 'relation_type = table'

Parameters

Name Type Optional Description
input_relation
str / vDataFrame
Relation to use to do the merging.
on
dict
A dictionary of all the different keys in the following format:
{"relationA_key1": "relationB_key1" ..., "relationA_keyk": "relationB_keyk"}
where relationA is the current vDataFrame and relationB is the input relation/vDataframe.
on_interpolate
dict
A dictionary of all unique keys in the following format:
{"relationA_key1": "relationB_key1" ..., "relationA_keyk": "relationB_keyk"}
where relationA is the current vDataFrame and relationB is the input relation or the input vDataFrame.
These keys are used in joins between two event series using some ordered attribute. Event series joins let you compare values from two series directly and does not require you to normalize the series to the same measurement interval
how
str
Join Type.
  • left : Left Join.
  • right : Right Join.
  • cross : Cross Join.
  • full : Full Outer Join.
  • natural : Natural Join.
  • inner : Inner Join.
expr1
list
List, in pure SQL, of the different columns to select from the current vDataFrame. Aliases are recommended to keep the track of the different features and to avoid ambigious names.
Example: 'column' or 'column AS my_new_alias'.
expr2
list
List, in pure SQL, of the different columns to select from the current vDataFrame. Aliases are recommended to keep the track of the different features and to avoid ambigious names.
Example: 'column' or 'column AS my_new_alias'.

Returns

vDataFrame : object result of the join.

Example

In [1]:
from verticapy import *
flights = vDataFrame("public.flights")
airports = vDataFrame("public.airports")
airlines = vDataFrame("public.airlines")
display(flights)
display(airports)
display(airlines)
123
YEAR
Int
123
MONTH
Int
123
DAY
Int
123
DAY_OF_WEEK
Int
Abc
AIRLINE
Varchar(20)
123
FLIGHT_NUMBER
Int
Abc
TAIL_NUMBER
Varchar(20)
Abc
ORIGIN_AIRPORT
Varchar(20)
Abc
DESTINATION_AIRPORT
Varchar(20)
123
SCHEDULED_DEPARTURE
Int
123
DEPARTURE_TIME
Int
123
DEPARTURE_DELAY
Int
123
TAXI_OUT
Int
123
WHEELS_OFF
Int
123
SCHEDULED_TIME
Int
123
ELAPSED_TIME
Int
123
AIR_TIME
Int
123
DISTANCE
Int
123
WHEELS_ON
Int
123
TAXI_IN
Int
123
SCHEDULED_ARRIVAL
Int
123
ARRIVAL_TIME
Int
123
ARRIVAL_DELAY
Int
123
DIVERTED
Int
123
CANCELLED
Int
Abc
CANCELLATION_REASON
Varchar(20)
123
AIR_SYSTEM_DELAY
Int
123
SECURITY_DELAY
Int
123
AIRLINE_DELAY
Int
123
LATE_AIRCRAFT_DELAY
Int
123
WEATHER_DELAY
Int
120153113AA6N388AAOGGDFW17151710-514172442440038237114464519450-2900[null][null][null][null][null][null]
220153113AA8N378AAHNLDFW16451642-324170644141338537844314506435-3100[null][null][null][null][null][null]
320153113AA10N793AALAXJFK22102202-819222132131729024756118631619-1200[null][null][null][null][null][null]
420153113AA14N5ECAAOGGLAX21592156-311220731127125324865207610527-4300[null][null][null][null][null][null]
520153113AA21N794AAJFKLAX18451839-613185239135033424752126322162129-4700[null][null][null][null][null][null]
620153113AA27N5BRAADFWLAS18501852220191217917414910551941519491946-300[null][null][null][null][null][null]
720153113AA30N799AALAXJFK23252322-321234332333130424757476748753500[null][null][null][null][null][null]
820153113AA37N3JYAADFWSAN21002058-2242122185207174117122169220522252000[null]200000
920153113AA41N3CNAAORDSEA20502116261921352752732391721233415232523492400[null]001230
1020153113AA45N3JDAAJFKLAS17291727-216174335633030822481951620251957-2800[null][null][null][null][null][null]
1120153113AA49N630AADFWSAN19351940518195818617515411712032320412035-600[null][null][null][null][null][null]
1220153113AA51N4YBAADFWSLC1725172831817461711721459891911919161920400[null][null][null][null][null][null]
1320153113AA60N380AADFWMIA1755180914181827160169143112121508213521582300[null]901400
1420153113AA63N3CKAAMIAPHX1955201217202032324303256197221482722192215-400[null][null][null][null][null][null]
1520153113AA65N3DHAAJFKDFW16151611-4191630256252213139119032019311923-800[null][null][null][null][null][null]
1620153113AA69N3GYAAMIASFO184018591912191139335734125852152422132156-1700[null][null][null][null][null][null]
1720153113AA71N3ACAADFWSAN18051807214182118618716911711910419111914300[null][null][null][null][null][null]
1820153113AA75N3HGAAIADLAX1831184413131857361342315228821121421322126-600[null][null][null][null][null][null]
1920153113AA81N471AADFWTUS22452257121823151421331108132305523072310300[null][null][null][null][null][null]
2020153113AA87N491AAORDRNO19001857-311190826723822316712051421272055-3200[null][null][null][null][null][null]
2120153113AA93N3JKAAORDSAN1720174929201809261243220172319493194119521100[null][null][null][null][null][null]
2220153113AA95N3JHAAJFKSAN17201718-214173238034632924462001320402004-3600[null][null][null][null][null][null]
2320153113AA102N353AAHNLDFW18301836627190344043640437846475650652200[null][null][null][null][null][null]
2420153113AA103N503AAMSYDFW16151631169164010374634471743217581745-1300[null][null][null][null][null][null]
2520153113AA111N3LJAAORDSNA172017311121175226324321817261930419431934-900[null][null][null][null][null][null]
2620153113AA113N3JWAAMIADEN21152105-1011211627524122417092300623502306-4400[null][null][null][null][null][null]
2720153113AA122N864AADFWMCO19101908-216192414015913798522416223022471700[null]170000
2820153113AA125N528AAATLDFW17501744-613175715313410573118421619231858-2500[null][null][null][null][null][null]
2920153113AA128N3EYAADFWIAD20202020017203715315813411722351723532358500[null][null][null][null][null][null]
3020153113AA132N436AAPHLDFW18351832-3421914249249188130321221921442141-300[null][null][null][null][null][null]
3120153113AA133N789AAJFKLAX162917104119172940236733924752008920112017600[null][null][null][null][null][null]
3220153113AA134N3BLAADCADFW1745192095121932216200179119221319202121407900[null]0049300
3320153113AA138N3FAAADFWDCA16551653-213170615915513811922024420342028-600[null][null][null][null][null][null]
3420153113AA142N4XVAAMKEDFW1810184434111855164145119853205415205421091500[null]00870
3520153113AA143N3JVAAMIALAX18491847-2221909369326294234221031021582113-4500[null][null][null][null][null][null]
3620153113AA145N3FWAAMIASAN20052000-515201534529527722672152322502155-5500[null][null][null][null][null][null]
3720153113AA146N3GWAAMIABOS21002100012211218216915212582344522349-1300[null][null][null][null][null][null]
3820153113AA147N528AADFWSLC2105210501921241691561329892236522542241-1300[null][null][null][null][null][null]
3920153113AA151N3BWAADENORD18171810-72518351381491168882131821352139400[null][null][null][null][null][null]
4020153113AA154N3GSAAORDMCO1955200611202026152157128100523349232723431600[null]50560
4120153113AA155N3LAAALAXORD17451754928182224123720217442344723462351500[null][null][null][null][null][null]
4220153113AA156N3FSAAORDSAN21252149248215725922621517232332323442335-900[null][null][null][null][null][null]
4320153113AA161N5ECAALAXOGG17301724-6171741359379360248620412202920431400[null][null][null][null][null][null]
4420153113AA163N797AAJFKSFO19551951-412200340037235625862259423352303-3200[null][null][null][null][null][null]
4520153113AA165N3LKAABOSLAX162016288231651399379344261119351219591947-1200[null][null][null][null][null][null]
4620153113AA180N787AALAXJFK16551725302717523223513142475206101172165900[null]2903000
4720153113AA181N799AAJFKLAX17351725-10271752400409346247520383621152114-100[null][null][null][null][null][null]
4820153113AA182N3FRAALAXLAS1805180502018257472462361911619191917-200[null][null][null][null][null][null]
4920153113AA185N788AAJFKLAX21052059-629212840038532824752356284524-2100[null][null][null][null][null][null]
5020153113AA188N3HEAARSWDFW173017300141744185162130101718541819351912-2300[null][null][null][null][null][null]
5120153113AA192N3HXAALAXBOS21452141-416215732731629426115516612557-1500[null][null][null][null][null][null]
5220153113AA197N3LWAASFOORD17311738717175524323120918462324523342329-500[null][null][null][null][null][null]
5320153113AA199N3BDAAJFKORD17101706-412171817616613074018282419061852-1400[null][null][null][null][null][null]
5420153113AA207N3FFAAMIALAS200020141423203733930727721742214722392221-1800[null][null][null][null][null][null]
5520153113AA209N3GVAALAXSFO16251622-31916418476543371735317491738-1100[null][null][null][null][null][null]
5620153113AA209N3GVAASFOLAX18401833-710184391755433719371120111948-2300[null][null][null][null][null][null]
5720153113AA219N3ABAALAXSFO18451838-72319018684563371957520112002-900[null][null][null][null][null][null]
5820153113AA219N3ABAASFOLAX21012053-8192112921015433722062822332234100[null][null][null][null][null][null]
5920153113AA220N862AAMIATPA170518126717182972664520419144181719186100[null]0029320
6020153113AA220N862AATPAMIA191119584712201066553520420458201720533600[null]000360
6120153113AA223N3JRAABOSLAX20011953-8142007401376349261122561323422309-3300[null][null][null][null][null][null]
6220153113AA227N3FGAADFWMSP2045204721421011351291098522250623002256-400[null][null][null][null][null][null]
6320153113AA231N3KMAALASORD16171614-313162720320618615142133721402140000[null][null][null][null][null][null]
6420153113AA232N434AAATLDFW19091906-31419201521239973119591020412009-3200[null][null][null][null][null][null]
6520153113AA234N3CXAAMIABWI21352130-51321431541371219462344392347-2200[null][null][null][null][null][null]
6620153113AA235N3HWAAJFKSEA18351829-6141843384379353242221361221592148-1100[null][null][null][null][null][null]
6720153113AA238N3CEAADCAMIA18051804-115181916716413991920381020522048-400[null][null][null][null][null][null]
6820153113AA245N3LFAADCALAX1715172510141739362345311231119502020172010-700[null][null][null][null][null][null]
6920153113AA246N5FCAAKOALAX213021300152145310[null][null]2504[null][null]540[null][null]10[null][null][null][null][null][null]
7020153113AA247N5FCAALAXKOA17001658-2161714360375355250420094200020131300[null][null][null][null][null][null]
7120153113AA263N5DJAAMCOLAX16301626-413163934431229022181829919141838-3600[null][null][null][null][null][null]
7220153113AA267N5ESAALAXHNL18301821-9181839372409387255622064214222102800[null]280000
7320153113AA268N597AADFWSJC211021271722214922922019514382304322592307800[null][null][null][null][null][null]
7420153113AA272N3ADAASFOMIA23502346-4162310330308258581068008161600[null]160000
7520153113AA280N383AADFWMIA185518594181917157169143112122408223222481600[null]160000
7620153113AA288N3BDAAORDBOS20052004-11620201291271028672302923142311-300[null][null][null][null][null][null]
7720153113AA291N3FDAAJFKAUS17001658-216171426524422315211957520252002-2300[null][null][null][null][null][null]
7820153113AA293N791AAJFKLAX19451944-1172001398372324247522253123232256-2700[null][null][null][null][null][null]
7920153113AA294N466AAORFDFW17081702-6181720222225184121219242319501947-300[null][null][null][null][null][null]
8020153113AA296N564AADFWSAT17201920120121932666445247201771826202411800[null]0019990
8120153113AA296N564AASATDFW19062059113112110696041247215182015215910400[null]0001040
8220153113AA297N5DEAALAXHNL16261621-5251646371423391255620177193720244700[null]470000
8320153113AA298N5DEAAHNLLAX210721453815220032628626525565256533531-200[null][null][null][null][null][null]
8420153113AA301N4XEAADFWTUS200520282317204514513811681320415203020461600[null]001420
8520153113AA312N5FLAAMIAJFK19501958818201617315613110892227722432234-900[null][null][null][null][null][null]
8620153113AA335N560AAORDTUS1959203132112042234200177143721391221532151-200[null][null][null][null][null][null]
8720153113AA340N489AADFWHOU21502209199221863625024723083225323111800[null]001800
8820153113AA341N4XEAAAUSDFW1744181834141832666537190190914185019233300[null]000330
8920153113AA342N3FXAAMIALGA17001656-421171718216213710961934420021938-2400[null][null][null][null][null][null]
9020153113AA344N3FCAADENMIA17151710-513172322622520517092248723012255-600[null][null][null][null][null][null]
9120153113AA345N3LTAALGAORD17091716723173917116312773318461319001859-100[null][null][null][null][null][null]
9220153113AA346N3LTAAORDLGA19551951-41120021201119073322321022552242-1300[null][null][null][null][null][null]
9320153113AA348N3HYAAMIAMSY20052002-32320251321291036752108321172111-600[null][null][null][null][null][null]
9420153113AA349N028AAMIAATL18151807-81518221241138559419471320192000-1900[null][null][null][null][null][null]
9520153113AA350N3CJAAORDLGA17501856661319091241068873321375205421424800[null]0036120
9620153113AA351N3JYAAIAHDFW1710174939171806737040224184613182318593600[null]002340
9720153113AA352N3MBAAORDLGA18451838-7211859122118897332128821472136-1100[null][null][null][null][null][null]
9820153113AA354N3KFAAORDLGA16351631-4151646123112907331916719381923-1500[null][null][null][null][null][null]
9920153113AA355N3MHAALGAORD18051758-72518231711501177331920819561928-2800[null][null][null][null][null][null]
10020153113AA356N006AAATLDFW1644164511517001511301097311749618151755-2000[null][null][null][null][null][null]
Rows: 1-100 | Columns: 31
Abc
IATA_CODE
Varchar(20)
Abc
Varchar(156)
Abc
CITY
Varchar(60)
Abc
STATE
Varchar(20)
Abc
COUNTRY
Varchar(20)
🌎
LATITUDE
Numeric(10,6)
🌎
LONGITUDE
Numeric(11,6)
1ABEAllentownPAUSA40.65236-75.4404
2ABIAbileneTXUSA32.41132-99.6819
3ABQAlbuquerqueNMUSA35.04022-106.60919
4ABRAberdeenSDUSA45.44906-98.42183
5ABYAlbanyGAUSA31.53552-84.19447
6ACKNantucketMAUSA41.25305-70.06018
7ACTWacoTXUSA31.61129-97.23052
8ACVArcata/EurekaCAUSA40.97812-124.10862
9ACYAtlantic CityNJUSA39.45758-74.57717
10ADKAdakAKUSA51.87796-176.64603
11ADQKodiakAKUSA57.74997-152.49386
12AEXAlexandriaLAUSA31.32737-92.54856
13AGSAugustaGAUSA33.36996-81.9645
14AKNKing SalmonAKUSA58.6768-156.64922
15ALBAlbanyNYUSA42.74812-73.80298
16ALOWaterlooIAUSA42.55708-92.40034
17AMAAmarilloTXUSA35.21937-101.70593
18ANCAnchorageAKUSA61.17432-149.99619
19APNAlpenaMIUSA45.07807-83.56029
20ASEAspenCOUSA39.22316-106.86885
21ATLAtlantaGAUSA33.64044-84.42694
22ATWAppletonWIUSA44.25741-88.51948
23AUSAustinTXUSA30.19453-97.66987
24AVLAshevilleNCUSA35.43619-82.54181
25AVPWilkes-Barre/ScrantonPAUSA41.33815-75.72427
26AZOKalamazooMIUSA42.23488-85.55206
27BDLWindsor LocksCTUSA41.93887-72.68323
28BETBethelAKUSA60.77978-161.838
29BFLBakersfieldCAUSA35.4336-119.05677
30BGMBinghamtonNYUSA42.20848-75.97961
31BGRBangorMEUSA44.80744-68.82814
32BHMBirminghamALUSA33.56294-86.75355
33BILBillingsMTUSA45.80766-108.54286
34BISBismarckNDUSA46.77411-100.74672
35BJIBemidjiMNUSA47.50942-94.93372
36BLIBellinghamWAUSA48.79275-122.53753
37BMIBloomingtonILUSA40.47799-88.91595
38BNANashvilleTNUSA36.12448-86.67818
39BOIBoiseIDUSA43.56444-116.22278
40BOSBostonMAUSA42.36435-71.00518
41BPTBeaumont/Port ArthurTXUSA29.95083-94.02069
42BQKBrunswickGAUSA31.25903-81.46631
43BQNAguadillaPRUSA18.49486-67.12944
44BRDBrainerdMNUSA46.39786-94.13723
45BROBrownsvilleTXUSA25.90683-97.42586
46BRWBarrowAKUSA71.28545-156.766
47BTMButteMTUSA45.9548-112.49746
48BTRBaton RougeLAUSA30.53316-91.14963
49BTVBurlingtonVTUSA44.473-73.15031
50BUFBuffaloNYUSA42.94052-78.73217
51BURBurbankCAUSA34.20062-118.3585
52BWIBaltimoreMDUSA39.1754-76.6682
53BZNBozemanMTUSA45.7769-111.15301
54CAEColumbiaSCUSA33.93884-81.11954
55CAKAkronOHUSA40.91631-81.44247
56CDCCedar CityUTUSA37.70097-113.09858
57CDVCordovaAKUSA60.49183-145.47765
58CECCrescent CityCAUSA41.78016-124.23653
59CHAChattanoogaTNUSA35.03527-85.20379
60CHOCharlottesvilleVAUSA38.13864-78.45286
61CHS