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.

[57]:

import verticapy as vp
flights  = vp.read_csv("data/flights.csv")
display(flights)
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
12015114AA1N787AAJFKLAX900855-51791239040237824751230712301237700[null][null][null][null][null][null]
22015114AA2N795AALAXJFK900856-41691233529527124751643817351651-4400[null][null][null][null][null][null]
32015114AA3N798AAJFKLAX12301226-419124538038235824751543515501548-200[null][null][null][null][null][null]
42015114AA4N799AALAXJFK12201214-6231237330319284247520211220502033-1700[null][null][null][null][null][null]
52015114AA5N376AADFWHNL130517542892118155155264993784223461740224030000[null]110197920
62015114AA6N398AAOGGDFW1805[null][null][null][null]425[null][null]3711[null][null]510[null][null]01A[null][null][null][null][null]
72015114AA7N398AADFWOGG121515131782415375005174903711194731635195019500[null]17017800
82015114AA8N368AAHNLDFW1745193310815194844544642037846481151065910900[null]1001080
92015114AA9N792AAJFKLAX700649-112271138039736824751019710201026600[null][null][null][null][null][null]
102015114AA10N796AALAXJFK21502150014220430929427524755395559544-1500[null][null][null][null][null][null]
112015114AA12N792AALAXJFK11151114-1191133335321287247519201519501935-1500[null][null][null][null][null][null]
122015114AA14N5ERAAOGGLAX23002254-6122306305289267248653310605543-2200[null][null][null][null][null][null]
132015114AA15N783AAJFKSFO800755-557852400453388258612208114012284800[null]480000
142015114AA16N783AASFOJFK12451316311813343303333052586213910211521493400[null]300310
152015114AA17N019AAATLMIA700656-415711112106825948339852842-1000[null][null][null][null][null][null]
162015114AA19N786AAJFKLAX11001055-517111238037835424751406714201413-700[null][null][null][null][null][null]
172015114AA20N789AASFOJFK15001503319152232532830125862323823252331600[null][null][null][null][null][null]
182015114AA21N795AAJFKLAX1845185162919203854073682475222810221022382800[null]220600
192015114AA22N786AALAXJFK15151512-3231535320317277247523121723352329-600[null][null][null][null][null][null]
202015114AA23N371AADFWSFO755841463591623023319014641026894510344900[null]304600
212015114AA23N371AASFODFW10501123332811512102171781464164911162017004000[null]700330
222015114AA24N784AASFOJFK720714-62073434033230725861541516001546-1400[null][null][null][null][null][null]
232015114AA25N3AVAABOSLAX900857-31591239540538126111233912351242700[null][null][null][null][null][null]
242015114AA27N5DUAADFWLAS1850192939161945175165141105520068194520142900[null]002900
252015114AA28N358AALAXMIA15001456-4191515290305284234222592225023011100[null][null][null][null][null][null]
262015114AA29N455AAONTDFW122513569125142117521714911881850431720193313300[null]4209100
272015114AA29N568AADFWONT1035[null][null][null][null]190[null][null]1188[null][null]1145[null][null]01B[null][null][null][null][null]
282015114AA30N795AALAXJFK23302326-423234932029927024757196750725-2500[null][null][null][null][null][null]
292015114AA31N5ESAALAXHNL8058282312840365362346255612264121012302000[null]002000
302015114AA32N794AALAXJFK141514249141438325315288247522261322402239-100[null][null][null][null][null][null]
312015114AA35N3ALAADFWHDN11101109-1111120145178111769121156123513073200[null]320000
322015114AA35N3ALAAHDNDFW1320140040111411135130109769170010163517103500[null]007280
332015114AA36N3EKAASANDFW935928-713941170172140117114011914251420-500[null][null][null][null][null][null]
342015114AA37N483AADFWSAN21002103318212118518716611712207322052210500[null][null][null][null][null][null]
352015114AA40N3BYAADCAORD9559550910041301261116121055611051101-400[null][null][null][null][null][null]
362015114AA41N3KEAAORDSEA20552054-11621102702932361721230641232523472200[null]220000
372015114AA42N3GPAASEAORD850849-11690523522420117211426714451433-1200[null][null][null][null][null][null]
382015114AA43N3BNAADFWDTW1030[null][null][null][null]150[null][null]986[null][null]1400[null][null]01B[null][null][null][null][null]
392015114AA43N3KCAADTWDFW144514450191504180199163986164717164517041900[null]190000
402015114AA44N3AJAALASJFK810803-71782029527224822481528716051535-3000[null][null][null][null][null][null]
412015114AA45N3ELAAJFKLAS17291727-221174835534731822482006820242014-1000[null][null][null][null][null][null]
422015114AA46N3CSAASJCORD725725013738255253226182913241413401338-200[null][null][null][null][null][null]
432015114AA47N3KSAAORDSJC1915203580182053280261238182922515215522566100[null]000610
442015114AA48N3JXAASANDFW835830-513843175164141117113041013301314-1600[null][null][null][null][null][null]
452015114AA49N5ELAADFWSAN1935200631152021185178160117121013204021042400[null]002400
462015114AA50N570AASLCDFW81582493690016517913498912149120012232300[null]140900
472015114AA51N471AADFWSLC17252035190242059170161130989220971915221618100[null]00221590
482015114AA52N3LMAALAXDCA12251222-318124029027925323111953820152001-1400[null][null][null][null][null][null]
492015114AA53N3LHAADCALAX840838-21285036035033123111121711401128-1200[null][null][null][null][null][null]
502015114AA54N3CWAALASORD910906-41692221519317015141412714451419-2600[null][null][null][null][null][null]
512015114AA55N3JJAAORDLAS1820192464131937230225205151421027201021095900[null]005900
522015114AA58N5DFAAKOALAX1400140002214223103202762504205822211021201000[null][null][null][null][null][null]
532015114AA59N5DFAALAXKOA840837-31785435035233225041226312301229-100[null][null][null][null][null][null]
542015114AA60N372AADFWMIA175419551211520101601511331121232332134232611200[null]0011200
552015114AA61N850AAMIADFW715719413732199192167112191912934931-300[null][null][null][null][null][null]
562015114AA63N3ECAAMIAPHX19501955523201831730227119722249823072257-1000[null][null][null][null][null][null]
572015114AA64N3HXAADFWJFK102510411613105420018716913911443514451448300[null][null][null][null][null][null]
582015114AA65N3HLAAJFKDFW16151625101916442552942271391193148193020194900[null]3901000
592015114AA66N631AASJUJFK11511142-922120424524722115981445414561449-700[null][null][null][null][null][null]
602015114AA67N631AAJFKSJU160516292423165223521118215982054621002100000[null][null][null][null][null][null]
612015114AA68N5EGAASFOMIA70070331471732431729525851512815241520-400[null][null][null][null][null][null]
622015114AA69N3HTAAMIASFO18351836112184838635934225852130522012135-2600[null][null][null][null][null][null]
632015114AA70N5BTAASANDFW705659-622721180171137117111381212051150-1500[null][null][null][null][null][null]
642015114AA71N3GHAADFWSAN18051803-214181718017616011711857219051859-600[null][null][null][null][null][null]
652015114AA72N5EKAADFWMCO6006066186241451421209859244925928300[null][null][null][null][null][null]
662015114AA72N5EKAAMCODFW10201015-516103117517114398511541212151206-900[null][null][null][null][null][null]
672015114AA73N3FBAAPHLCLT1005957-8261023120108764491139612051145-2000[null][null][null][null][null][null]
682015114AA74N488AACLEDFW15551552-311160319018116110211744918051753-1200[null][null][null][null][null][null]
692015114AA74N505AADFWCLE1145[null][null][null][null]150[null][null]1021[null][null]1515[null][null]01B[null][null][null][null][null]
702015114AA75N3HPAAIADLAX18301822-817183936035833522882114621302120-1000[null][null][null][null][null][null]
712015114AA76N3HPAALAXIAD950946-418100428526524322881707417351711-2400[null][null][null][null][null][null]
722015114AA76N3HPAASFOLAX730723-71573885974933782733855900500[null][null][null][null][null][null]
732015114AA79N520AADFWSMF2140221939302249225224188143123576232533800[null]000380
742015114AA80N4XTAATUSDFW1655174146101751130130105813203615200520514600[null]000460
752015114AA81N4XCAADFWTUS22452308231323211401401238132445282300[null]000230
762015114AA83N4XBAAMCODFW735733-21674918017414698591512935927-800[null][null][null][null][null][null]
772015114AA84N3AXAABOSJFK940930-10159457569461871031810551039-1600[null][null][null][null][null][null]
782015114AA84N3AXAAJFKBOS7307344177517065441878354840839-100[null][null][null][null][null][null]
792015114AA85N785AAJFKSFO15301524-620154440038636125861845519101850-2000[null][null][null][null][null][null]
802015114AA86N3GTAAPDXORD705701-41571623523020817391244713001251-900[null][null][null][null][null][null]
812015114AA87N490AAORDRNO18451840-511185126522821316712024421102028-4200[null][null][null][null][null][null]
822015114AA89N3KVAAIAHMIA520618581963714113711196492878419355400[null]005400
832015114AA92N3KHAASANORD90090001591524021018917231424615001430-3000[null][null][null][null][null][null]
842015114AA93N3HHAAORDSAN1720180545101815255222210172319452193519471200[null][null][null][null][null][null]
852015114AA94N3ANAASANJFK755810151382332028126224461545616151551-2400[null][null][null][null][null][null]
862015114AA95N3ANAAJFKSAN1720174929181807380386363244621105204021153500[null]602900
872015114AA96N553AADFWSTL82083111218529594705501002395510051000[null][null][null][null][null][null]
882015114AA96N553AASTLDFW103510511615110611513198550124418123013023200[null]1606100
892015114AA97N3ENAADCADFW845842-314856215210185119211011111201112-800[null][null][null][null][null][null]
902015114AA99N3KMAAORDPDX15501553311160427525323917391803318251806-1900[null][null][null][null][null][null]
912015114AA102N376AAHNLDFW1920235627617134454454173784111011645112127600[null]0002760
922015114AA103N4XSAADFWMSY141014332313144680806244715485153015532300[null]002300
932015114AA103N4XSAAMSYDFW16101624148163210091744471746917501755500[null][null][null][null][null][null]
942015114AA110N3LRAASNAORD805801-47808230211194172613221013551332-2300[null][null][null][null][null][null]
952015114AA111N3LYAAORDSNA17201719-111173026023722217261912419401916-2400[null][null][null][null][null][null]
962015114AA112N857AADENMIA101510172191036224229199170915551115591606700[null][null][null][null][null][null]
972015114AA113N3GRAAMIADEN211521150212136269269235170923311323442344000[null][null][null][null][null][null]
982015114AA115N3CTAALAXMIA105103-21411728627625523428327851839-1200[null][null][null][null][null][null]
992015114AA116N383AAOGGDFW1955201621102026430433418371172457057292400[null]302100
1002015114AA117N793AAJFKLAX14451438-728150639038935524751801618151807-800[null][null][null][null][null][null]
Rows: 1-100 | Columns: 31

Second, we have information on each airport.

[58]:

airports = vp.read_csv("data/airports.csv")
display(airports)
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
61CHSCharlestonSCUSA32.89865-80.04051
62CIDCedar RapidsIAUSA41.88459-91.71087
63CIUSault Ste. MarieMIUSA46.25075-84.47239
64CLDSan DiegoCAUSA33.12723-117.27873
65CLEClevelandOHUSA41.41089-81.8494
66CLLCollege StationTXUSA30.58859-96.36382
67CLTCharlotteNCUSA35.21401-80.94313
68CMHColumbusOHUSA39.99799-82.89188
69CMIChampaign/UrbanaILUSA40.03925-88.27806
70CMXHancockMIUSA47.16842-88.48906
71CNYMoabUTUSA38.75496-109.75484
72CODCodyWYUSA44.52019-109.0238
73COSColorado SpringsCOUSA38.80581-104.70025
74COUColumbiaMOUSA38.81809-92.21963
75CPRCasperWYUSA42.90836-106.46447
76CRPCorpus ChristiTXUSA27.77036-97.50122
77CRWCharlestonWVUSA38.37315-81.59319
78CSGColumbusGAUSA32.51633-84.93886
79CVGCovingtonKYUSA39.04614-84.66217
80CWAMosineeWIUSA44.77762-89.66678
81DABDaytona BeachFLUSA29.17992-81.05806
82DALDallasTXUSA32.84711-96.85177
83DAYDaytonOHUSA39.90238-84.21938
84DBQDubuqueIAUSA42.40296-90.70917
85DCAArlingtonVAUSA38.85208-77.03772
86DENDenverCOUSA39.85841-104.667
87DFWDallas-Fort WorthTXUSA32.89595-97.0372
88DHNDothanALUSA31.32134-85.44963
89DIKDickinsonNDUSA46.79739-102.80195
90DLGDillinghamAKUSA59.04541-158.50334
91DLHDuluthMNUSA46.84209-92.19365
92DRODurangoCOUSA37.15152-107.75377
93DSMDes MoinesIAUSA41.53493-93.66068
94DTWDetroitMIUSA42.21206-83.34884
95DVLDevils LakeNDUSA48.11425-98.90878
96EAUEau ClaireWIUSA44.86526-91.48507
97ECPPanama CityFLUSA[null][null]
98EGEEagleCOUSA39.64257-106.9177
99EKOElkoNVUSA40.82493-115.7917
100ELMElmiraNYUSA42.15991-76.89144
Rows: 1-100 | Columns: 7

Third, we have the names of each airline.

[59]:

airlines = vp.read_csv("data/airlines.csv")
display(airlines)
Abc
IATA_CODE
Varchar(20)
Abc
AIRLINE
Varchar(56)
1AAAmerican Airlines Inc.
2ASAlaska Airlines Inc.
3B6JetBlue Airways
4DLDelta Air Lines Inc.
5EVAtlantic Southeast Airlines
6F9Frontier Airlines Inc.
7HAHawaiian Airlines Inc.
8MQAmerican Eagle Airlines Inc.
9NKSpirit Air Lines
10OOSkywest Airlines Inc.
11UAUnited Air Lines Inc.
12USUS Airways Inc.
13VXVirgin America
14WNSouthwest Airlines Co.
Rows: 1-14 | Columns: 2

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 vDataFrame’s ‘join’ method.

[60]:

help(vp.vDataFrame.join)

Help on function join in module verticapy.vdataframe:

join(self, input_relation, on:dict={}, on_interpolate:dict={}, how:str='natural', expr1:list=['*'], expr2:list=['*'])
    ---------------------------------------------------------------------------
    Joins the vDataFrame with another one or an input relation.

    ⚠ Warning : Joins can make the vDataFrame structure heavier. It is
                     recommended to always check the current structure
                     using the 'current_relation' method and to save it using the
                     'to_db' method with the parameters 'inplace = True' and
                     'relation_type = table'

    Parameters
    ----------
    input_relation: str/vDataFrame
        Relation to use to do the merging.
    on: dict, optional
        Dictionary of all different keys. The dict must be similar to the following:
        {"relationA_key1": "relationB_key1" ..., "relationA_keyk": "relationB_keyk"}
        where relationA is the current vDataFrame and relationB is the input relation
        or the input vDataFrame.
    on_interpolate: dict, optional
        Dictionary of all different keys. 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 vDataFrame and relationB is the input relation
        or the input 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: list, optional
        List of the different columns in pure SQL to select from the current
        vDataFrame, optionally as aliases. Aliases are recommended to avoid
        ambiguous names. For example: 'column' or 'column AS my_new_alias'.
    expr2: list, optional
        List of the different columns in pure SQL to select from the input
        relation 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.

    See Also
    --------
    vDataFrame.append  : Merges the vDataFrame with another relation.
    vDataFrame.groupby : Aggregates the vDataFrame.
    vDataFrame.sort    : Sorts the vDataFrame.

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

[61]:

flights = flights.join(airlines,
                       how = "left",
                       on = {"airline": "IATA_CODE"},
                       expr2 = ["AIRLINE AS airline_long"])
display(flights)
123
YEAR
Integer
123
MONTH
Integer
123
DAY
Integer
123
DAY_OF_WEEK
Integer
Abc
AIRLINE
Varchar(20)
123
FLIGHT_NUMBER
Integer
Abc
TAIL_NUMBER
Varchar(20)
Abc
ORIGIN_AIRPORT
Varchar(20)
Abc
DESTINATION_AIRPORT
Varchar(20)
123
SCHEDULED_DEPARTURE
Integer
123
DEPARTURE_TIME
Integer
123
DEPARTURE_DELAY
Integer
123
TAXI_OUT
Integer
123
WHEELS_OFF
Integer
123
SCHEDULED_TIME
Integer
123
ELAPSED_TIME
Integer
123
AIR_TIME
Integer
123
DISTANCE
Integer
123
WHEELS_ON
Integer
123
TAXI_IN
Integer
123
SCHEDULED_ARRIVAL
Integer
123
ARRIVAL_TIME
Integer
123
ARRIVAL_DELAY
Integer
123
DIVERTED
Integer
123
CANCELLED
Integer
Abc
CANCELLATION_REASON
Varchar(20)
123
AIR_SYSTEM_DELAY
Integer
123
SECURITY_DELAY
Integer
123
AIRLINE_DELAY
Integer
123
LATE_AIRCRAFT_DELAY
Integer
123
WEATHER_DELAY
Integer
Abc
airline_long
Varchar(56)
12015114AA1N787AAJFKLAX900855-51791239040237824751230712301237700[null][null][null][null][null][null]American Airlines Inc.
22015114AA2N795AALAXJFK900856-41691233529527124751643817351651-4400[null][null][null][null][null][null]American Airlines Inc.
32015114AA3N798AAJFKLAX12301226-419124538038235824751543515501548-200[null][null][null][null][null][null]American Airlines Inc.
42015114AA4N799AALAXJFK12201214-6231237330319284247520211220502033-1700[null][null][null][null][null][null]American Airlines Inc.
52015114AA5N376AADFWHNL130517542892118155155264993784223461740224030000[null]110197920American Airlines Inc.
62015114AA6N398AAOGGDFW1805[null][null][null][null]425[null][null]3711[null][null]510[null][null]01A[null][null][null][null][null]American Airlines Inc.
72015114AA7N398AADFWOGG121515131782415375005174903711194731635195019500[null]17017800American Airlines Inc.
82015114AA8N368AAHNLDFW1745193310815194844544642037846481151065910900[null]1001080American Airlines Inc.
92015114AA9N792AAJFKLAX700649-112271138039736824751019710201026600[null][null][null][null][null][null]American Airlines Inc.
102015114AA10N796AALAXJFK21502150014220430929427524755395559544-1500[null][null][null][null][null][null]American Airlines Inc.
112015114AA12N792AALAXJFK11151114-1191133335321287247519201519501935-1500[null][null][null][null][null][null]American Airlines Inc.
122015114AA14N5ERAAOGGLAX23002254-6122306305289267248653310605543-2200[null][null][null][null][null][null]American Airlines Inc.
132015114AA15N783AAJFKSFO800755-557852400453388258612208114012284800[null]480000American Airlines Inc.
142015114AA16N783AASFOJFK12451316311813343303333052586213910211521493400[null]300310American Airlines Inc.
152015114AA17N019AAATLMIA700656-415711112106825948339852842-1000[null][null][null][null][null][null]American Airlines Inc.
162015114AA19N786AAJFKLAX11001055-517111238037835424751406714201413-700[null][null][null][null][null][null]American Airlines Inc.
172015114AA20N789AASFOJFK15001503319152232532830125862323823252331600[null][null][null][null][null][null]American Airlines Inc.
182015114AA21N795AAJFKLAX1845185162919203854073682475222810221022382800[null]220600American Airlines Inc.
192015114AA22N786AALAXJFK15151512-3231535320317277247523121723352329-600[null][null][null][null][null][null]American Airlines Inc.
202015114AA23N371AADFWSFO755841463591623023319014641026894510344900[null]304600American Airlines Inc.
212015114AA23N371AASFODFW10501123332811512102171781464164911162017004000[null]700330American Airlines Inc.
222015114AA24N784AASFOJFK720714-62073434033230725861541516001546-1400[null][null][null][null][null][null]American Airlines Inc.
232015114AA25N3AVAABOSLAX900857-31591239540538126111233912351242700[null][null][null][null][null][null]American Airlines Inc.
242015114AA27N5DUAADFWLAS1850192939161945175165141105520068194520142900[null]002900American Airlines Inc.
252015114AA28N358AALAXMIA15001456-4191515290305284234222592225023011100[null][null][null][null][null][null]American Airlines Inc.
262015114AA29N455AAONTDFW122513569125142117521714911881850431720193313300[null]4209100American Airlines Inc.
272015114AA29N568AADFWONT1035[null][null][null][null]190[null][null]1188[null][null]1145[null][null]01B[null][null][null][null][null]American Airlines Inc.
282015114AA30N795AALAXJFK23302326-423234932029927024757196750725-2500[null][null][null][null][null][null]American Airlines Inc.
292015114AA31N5ESAALAXHNL8058282312840365362346255612264121012302000[null]002000American Airlines Inc.
302015114AA32N794AALAXJFK141514249141438325315288247522261322402239-100[null][null][null][null][null][null]American Airlines Inc.
312015114AA35N3ALAADFWHDN11101109-1111120145178111769121156123513073200[null]320000American Airlines Inc.
322015114AA35N3ALAAHDNDFW1320140040111411135130109769170010163517103500[null]007280American Airlines Inc.
332015114AA36N3EKAASANDFW935928-713941170172140117114011914251420-500[null][null][null][null][null][null]American Airlines Inc.
342015114AA37N483AADFWSAN21002103318212118518716611712207322052210500[null][null][null][null][null][null]American Airlines Inc.
352015114AA40N3BYAADCAORD9559550910041301261116121055611051101-400[null][null][null][null][null][null]American Airlines Inc.
362015114AA41N3KEAAORDSEA20552054-11621102702932361721230641232523472200[null]220000American Airlines Inc.
372015114AA42N3GPAASEAORD850849-11690523522420117211426714451433-1200[null][null][null][null][null][null]American Airlines Inc.
382015114AA43N3BNAADFWDTW1030[null][null][null][null]150[null][null]986[null][null]1400[null][null]01B[null][null][null][null][null]American Airlines Inc.
392015114AA43N3KCAADTWDFW144514450191504180199163986164717164517041900[null]190000American Airlines Inc.
402015114AA44N3AJAALASJFK810803-71782029527224822481528716051535-3000[null][null][null][null][null][null]American Airlines Inc.
412015114AA45N3ELAAJFKLAS17291727-221174835534731822482006820242014-1000[null][null][null][null][null][null]American Airlines Inc.
422015114AA46N3CSAASJCORD725725013738255253226182913241413401338-200[null][null][null][null][null][null]American Airlines Inc.
432015114AA47N3KSAAORDSJC1915203580182053280261238182922515215522566100[null]000610American Airlines Inc.
442015114AA48N3JXAASANDFW835830-513843175164141117113041013301314-1600[null][null][null][null][null][null]American Airlines Inc.
452015114AA49N5ELAADFWSAN1935200631152021185178160117121013204021042400[null]002400American Airlines Inc.
462015114AA50N570AASLCDFW81582493690016517913498912149120012232300[null]140900American Airlines Inc.
472015114AA51N471AADFWSLC17252035190242059170161130989220971915221618100[null]00221590American Airlines Inc.
482015114AA52N3LMAALAXDCA12251222-318124029027925323111953820152001-1400[null][null][null][null][null][null]American Airlines Inc.
492015114AA53N3LHAADCALAX840838-21285036035033123111121711401128-1200[null][null][null][null][null][null]American Airlines Inc.
502015114AA54N3CWAALASORD910906-41692221519317015141412714451419-2600[null][null][null][null][null][null]American Airlines Inc.
512015114AA55N3JJAAORDLAS1820192464131937230225205151421027201021095900[null]005900American Airlines Inc.
522015114AA58N5DFAAKOALAX1400140002214223103202762504205822211021201000[null][null][null][null][null][null]American Airlines Inc.
532015114AA59N5DFAALAXKOA840837-31785435035233225041226312301229-100[null][null][null][null][null][null]American Airlines Inc.
542015114AA60N372AADFWMIA175419551211520101601511331121232332134232611200[null]0011200American Airlines Inc.
552015114AA61N850AAMIADFW715719413732199192167112191912934931-300[null][null][null][null][null][null]American Airlines Inc.
562015114AA63N3ECAAMIAPHX19501955523201831730227119722249823072257-1000[null][null][null][null][null][null]American Airlines Inc.
572015114AA64N3HXAADFWJFK102510411613105420018716913911443514451448300[null][null][null][null][null][null]American Airlines Inc.
582015114AA65N3HLAAJFKDFW16151625101916442552942271391193148193020194900[null]3901000American Airlines Inc.
592015114AA66N631AASJUJFK11511142-922120424524722115981445414561449-700[null][null][null][null][null][null]American Airlines Inc.
602015114AA67N631AAJFKSJU160516292423165223521118215982054621002100000[null][null][null][null][null][null]American Airlines Inc.
612015114AA68N5EGAASFOMIA70070331471732431729525851512815241520-400[null][null][null][null][null][null]American Airlines Inc.
622015114AA69N3HTAAMIASFO18351836112184838635934225852130522012135-2600[null][null][null][null][null][null]American Airlines Inc.
632015114AA70N5BTAASANDFW705659-622721180171137117111381212051150-1500[null][null][null][null][null][null]American Airlines Inc.
642015114AA71N3GHAADFWSAN18051803-214181718017616011711857219051859-600[null][null][null][null][null][null]American Airlines Inc.
652015114AA72N5EKAADFWMCO6006066186241451421209859244925928300[null][null][null][null][null][null]American Airlines Inc.
662015114AA72N5EKAAMCODFW10201015-516103117517114398511541212151206-900[null][null][null][null][null][null]American Airlines Inc.
672015114AA73N3FBAAPHLCLT1005957-8261023120108764491139612051145-2000[null][null][null][null][null][null]American Airlines Inc.
682015114AA74N488AACLEDFW15551552-311160319018116110211744918051753-1200[null][null][null][null][null][null]American Airlines Inc.
692015114AA74N505AADFWCLE1145[null][null][null][null]150[null][null]1021[null][null]1515[null][null]01B[null][null][null][null][null]American Airlines Inc.
702015114AA75N3HPAAIADLAX18301822-817183936035833522882114621302120-1000[null][null][null][null][null][null]American Airlines Inc.
712015114AA76N3HPAALAXIAD950946-418100428526524322881707417351711-2400[null][null][null][null][null][null]American Airlines Inc.
722015114AA76N3HPAASFOLAX730723-71573885974933782733855900500[null][null][null][null][null][null]American Airlines Inc.
732015114AA79N520AADFWSMF2140221939302249225224188143123576232533800[null]000380American Airlines Inc.
742015114AA80N4XTAATUSDFW1655174146101751130130105813203615200520514600[null]000460American Airlines Inc.
752015114AA81N4XCAADFWTUS22452308231323211401401238132445282300[null]000230American Airlines Inc.
762015114AA83N4XBAAMCODFW735733-21674918017414698591512935927-800[null][null][null][null][null][null]American Airlines Inc.
772015114AA84N3AXAABOSJFK940930-10159457569461871031810551039-1600[null][null][null][null][null][null]American Airlines Inc.
782015114AA84N3AXAAJFKBOS7307344177517065441878354840839-100[null][null][null][null][null][null]American Airlines Inc.
792015114AA85N785AAJFKSFO15301524-620154440038636125861845519101850-2000[null][null][null][null][null][null]American Airlines Inc.
802015114AA86N3GTAAPDXORD705701-41571623523020817391244713001251-900[null][null][null][null][null][null]American Airlines Inc.
812015114AA87N490AAORDRNO18451840-511185126522821316712024421102028-4200[null][null][null][null][null][null]American Airlines Inc.
822015114AA89N3KVAAIAHMIA520618581963714113711196492878419355400[null]005400American Airlines Inc.
832015114AA92N3KHAASANORD90090001591524021018917231424615001430-3000[null][null][null][null][null][null]American Airlines Inc.
842015114AA93N3HHAAORDSAN1720180545101815255222210172319452193519471200[null][null][null][null][null][null]American Airlines Inc.
852015114AA94N3ANAASANJFK755810151382332028126224461545616151551-2400[null][null][null][null][null][null]American Airlines Inc.
862015114AA95N3ANAAJFKSAN1720174929181807380386363244621105204021153500[null]602900American Airlines Inc.
872015114AA96N553AADFWSTL82083111218529594705501002395510051000[null][null][null][null][null][null]American Airlines Inc.
882015114AA96N553AASTLDFW103510511615110611513198550124418123013023200[null]1606100American Airlines Inc.
892015114AA97N3ENAADCADFW845842-314856215210185119211011111201112-800[null][null][null][null][null][null]American Airlines Inc.
902015114AA99N3KMAAORDPDX15501553311160427525323917391803318251806-1900[null][null][null][null][null][null]American Airlines Inc.
912015114AA102N376AAHNLDFW1920235627617134454454173784111011645112127600[null]0002760American Airlines Inc.
922015114AA103N4XSAADFWMSY141014332313144680806244715485153015532300[null]002300American Airlines Inc.
932015114AA103N4XSAAMSYDFW16101624148163210091744471746917501755500[null][null][null][null][null][null]American Airlines Inc.
942015114AA110N3LRAASNAORD805801-47808230211194172613221013551332-2300[null][null][null][null][null][null]American Airlines Inc.
952015114AA111N3LYAAORDSNA17201719-111173026023722217261912419401916-2400[null][null][null][null][null][null]American Airlines Inc.
962015114AA112N857AADENMIA101510172191036224229199170915551115591606700[null][null][null][null][null][null]American Airlines Inc.
972015114AA113N3GRAAMIADEN211521150212136269269235170923311323442344000[null][null][null][null][null][null]American Airlines Inc.
982015114AA115N3CTAALAXMIA105103-21411728627625523428327851839-1200[null][null][null][null][null][null]American Airlines Inc.
992015114AA116N383AAOGGDFW1955201621102026430433418371172457057292400[null]302100American Airlines Inc.
1002015114AA117N793AAJFKLAX14451438-728150639038935524751801618151807-800[null][null][null][null][null][null]American Airlines Inc.
Rows: 1-100 | Columns: 32

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

[62]:

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"])
display(flights)
123
YEAR
Integer
123
MONTH
Integer
123
DAY
Integer
123
DAY_OF_WEEK
Integer
Abc
AIRLINE
Varchar(20)
123
FLIGHT_NUMBER
Integer
Abc
TAIL_NUMBER
Varchar(20)
Abc
ORIGIN_AIRPORT
Varchar(20)
Abc
DESTINATION_AIRPORT
Varchar(20)
123
SCHEDULED_DEPARTURE
Integer
123
DEPARTURE_TIME
Integer
123
DEPARTURE_DELAY
Integer
123
TAXI_OUT
Integer
123
WHEELS_OFF
Integer
123
SCHEDULED_TIME
Integer
123
ELAPSED_TIME
Integer
123
AIR_TIME
Integer
123
DISTANCE
Integer
123
WHEELS_ON
Integer
123
TAXI_IN
Integer
123
SCHEDULED_ARRIVAL
Integer
123
ARRIVAL_TIME
Integer
123
ARRIVAL_DELAY
Integer
123
DIVERTED
Integer
123
CANCELLED
Integer
Abc
CANCELLATION_REASON
Varchar(20)
123
AIR_SYSTEM_DELAY
Integer
123
SECURITY_DELAY
Integer
123
AIRLINE_DELAY
Integer
123
LATE_AIRCRAFT_DELAY
Integer
123
WEATHER_DELAY
Integer
Abc
airline_long
Varchar(56)
123
origin_lat
Numeric(10,6)
123
origin_lon
Numeric(11,6)
123
destination_lat
Numeric(10,6)
123
destination_lon
Numeric(11,6)
12015114AA1N787AAJFKLAX900855-51791239040237824751230712301237700[null][null][null][null][null][null]American Airlines Inc.40.63975-73.7789333.94254-118.40807
22015114AA2N795AALAXJFK900856-41691233529527124751643817351651-4400[null][null][null][null][null][null]American Airlines Inc.33.94254-118.4080740.63975-73.77893
32015114AA3N798AAJFKLAX12301226-419124538038235824751543515501548-200[null][null][null][null][null][null]American Airlines Inc.40.63975-73.7789333.94254-118.40807
42015114AA4N799AALAXJFK12201214-6231237330319284247520211220502033-1700[null][null][null][null][null][null]American Airlines Inc.33.94254-118.4080740.63975-73.77893
52015114AA5N376AADFWHNL130517542892118155155264993784223461740224030000[null]110197920American Airlines Inc.32.89595-97.037221.31869-157.92241
62015114AA6N398AAOGGDFW1805[null][null][null][null]425[null][null]3711[null][null]510[null][null]01A[null][null][null][null][null]American Airlines Inc.20.89865-156.4304632.89595-97.0372
72015114AA7N398AADFWOGG121515131782415375005174903711194731635195019500[null]17017800American Airlines Inc.32.89595-97.037220.89865-156.43046
82015114AA8N368AAHNLDFW1745193310815194844544642037846481151065910900[null]1001080American Airlines Inc.21.31869-157.9224132.89595-97.0372
92015114AA9N792AAJFKLAX700649-112271138039736824751019710201026600[null][null][null][null][null][null]American Airlines Inc.40.63975-73.7789333.94254-118.40807
102015114AA10N796AALAXJFK21502150014220430929427524755395559544-1500[null][null][null][null][null][null]American Airlines Inc.33.94254-118.4080740.63975-73.77893
112015114AA12N792AALAXJFK11151114-1191133335321287247519201519501935-1500[null][null][null][null][null][null]American Airlines Inc.33.94254-118.4080740.63975-73.77893
122015114AA14N5ERAAOGGLAX23002254-6122306305289267248653310605543-2200[null][null][null][null][null][null]American Airlines Inc.20.89865-156.4304633.94254-118.40807
132015114AA15N783AAJFKSFO800755-557852400453388258612208114012284800[null]480000American Airlines Inc.40.63975-73.7789337.619-122.37484
142015114AA16N783AASFOJFK12451316311813343303333052586213910211521493400[null]300310American Airlines Inc.37.619-122.3748440.63975-73.77893
152015114AA17N019AAATLMIA700656-415711112106825948339852842-1000[null][null][null][null][null][null]American Airlines Inc.33.64044-84.4269425.79325-80.29056
162015114AA19N786AAJFKLAX11001055-517111238037835424751406714201413-700[null][null][null][null][null][null]American Airlines Inc.40.63975-73.7789333.94254-118.40807
172015114AA20N789AASFOJFK15001503319152232532830125862323823252331600[null][null][null][null][null][null]American Airlines Inc.37.619-122.3748440.63975-73.77893
182015114AA21N795AAJFKLAX1845185162919203854073682475222810221022382800[null]220600American Airlines Inc.40.63975-73.7789333.94254-118.40807
192015114AA22N786AALAXJFK15151512-3231535320317277247523121723352329-600[null][null][null][null][null][null]American Airlines Inc.33.94254-118.4080740.63975-73.77893
202015114AA23N371AADFWSFO755841463591623023319014641026894510344900[null]304600American Airlines Inc.32.89595-97.037237.619-122.37484
212015114AA23N371AASFODFW10501123332811512102171781464164911162017004000[null]700330American Airlines Inc.37.619-122.3748432.89595-97.0372
222015114AA24N784AASFOJFK720714-62073434033230725861541516001546-1400[null][null][null][null][null][null]American Airlines Inc.37.619-122.3748440.63975-73.77893
232015114AA25N3AVAABOSLAX900857-31591239540538126111233912351242700[null][null][null][null][null][null]American Airlines Inc.42.36435-71.0051833.94254-118.40807
242015114AA27N5DUAADFWLAS1850192939161945175165141105520068194520142900[null]002900American Airlines Inc.32.89595-97.037236.08036-115.15233
252015114AA28N358AALAXMIA15001456-4191515290305284234222592225023011100[null][null][null][null][null][null]American Airlines Inc.33.94254-118.4080725.79325-80.29056
262015114AA29N455AAONTDFW122513569125142117521714911881850431720193313300[null]4209100American Airlines Inc.34.056-117.6011932.89595-97.0372
272015114AA29N568AADFWONT1035[null][null][null][null]190[null][null]1188[null][null]1145[null][null]01B[null][null][null][null][null]American Airlines Inc.32.89595-97.037234.056-117.60119
282015114AA30N795AALAXJFK23302326-423234932029927024757196750725-2500[null][null][null][null][null][null]American Airlines Inc.33.94254-118.4080740.63975-73.77893
292015114AA31N5ESAALAXHNL8058282312840365362346255612264121012302000[null]002000American Airlines Inc.33.94254-118.4080721.31869-157.92241
302015114AA32N794AALAXJFK141514249141438325315288247522261322402239-100[null][null][null][null][null][null]American Airlines Inc.33.94254-118.4080740.63975-73.77893
312015114AA35N3ALAADFWHDN11101109-1111120145178111769121156123513073200[null]320000American Airlines Inc.32.89595-97.037240.48118-107.21766
322015114AA35N3ALAAHDNDFW1320140040111411135130109769170010163517103500[null]007280American Airlines Inc.40.48118-107.2176632.89595-97.0372
332015114AA36N3EKAASANDFW935928-713941170172140117114011914251420-500[null][null][null][null][null][null]American Airlines Inc.32.73356-117.1896632.89595-97.0372
342015114AA37N483AADFWSAN21002103318212118518716611712207322052210500[null][null][null][null][null][null]American Airlines Inc.32.89595-97.037232.73356-117.18966
352015114AA40N3BYAADCAORD9559550910041301261116121055611051101-400[null][null][null][null][null][null]American Airlines Inc.38.85208-77.0377241.9796-87.90446
362015114AA41N3KEAAORDSEA20552054-11621102702932361721230641232523472200[null]220000American Airlines Inc.41.9796-87.9044647.44898-122.30931
372015114AA42N3GPAASEAORD850849-11690523522420117211426714451433-1200[null][null][null][null][null][null]American Airlines Inc.47.44898-122.3093141.9796-87.90446
382015114AA43N3BNAADFWDTW1030[null][null][null][null]150[null][null]986[null][null]1400[null][null]01B[null][null][null][null][null]American Airlines Inc.32.89595-97.037242.21206-83.34884
392015114AA43N3KCAADTWDFW144514450191504180199163986164717164517041900[null]190000American Airlines Inc.42.21206-83.3488432.89595-97.0372
402015114AA44N3AJAALASJFK810803-71782029527224822481528716051535-3000[null][null][null][null][null][null]American Airlines Inc.36.08036-115.1523340.63975-73.77893
412015114AA45N3ELAAJFKLAS17291727-221174835534731822482006820242014-1000[null][null][null][null][null][null]American Airlines Inc.40.63975-73.7789336.08036-115.15233
422015114AA46N3CSAASJCORD725725013738255253226182913241413401338-200[null][null][null][null][null][null]American Airlines Inc.37.36186-121.9290141.9796-87.90446
432015114AA47N3KSAAORDSJC1915203580182053280261238182922515215522566100[null]000610American Airlines Inc.41.9796-87.9044637.36186-121.92901
442015114AA48N3JXAASANDFW835830-513843175164141117113041013301314-1600[null][null][null][null][null][null]American Airlines Inc.32.73356-117.1896632.89595-97.0372
452015114AA49N5ELAADFWSAN1935200631152021185178160117121013204021042400[null]002400American Airlines Inc.32.89595-97.037232.73356-117.18966
462015114AA50N570AASLCDFW81582493690016517913498912149120012232300[null]140900American Airlines Inc.40.78839-111.9777732.89595-97.0372
472015114AA51N471AADFWSLC17252035190242059170161130989220971915221618100[null]00221590American Airlines Inc.32.89595-97.037240.78839-111.97777
482015114AA52N3LMAALAXDCA12251222-318124029027925323111953820152001-1400[null][null][null][null][null][null]American Airlines Inc.33.94254-118.4080738.85208-77.03772
492015114AA53N3LHAADCALAX840838-21285036035033123111121711401128-1200[null][null][null][null][null][null]American Airlines Inc.38.85208-77.0377233.94254-118.40807
502015114AA54N3CWAALASORD910906-41692221519317015141412714451419-2600[null][null][null][null][null][null]American Airlines Inc.36.08036-115.1523341.9796-87.90446
512015114AA55N3JJAAORDLAS1820192464131937230225205151421027201021095900[null]005900American Airlines Inc.41.9796-87.9044636.08036-115.15233
522015114AA58N5DFAAKOALAX1400140002214223103202762504205822211021201000[null][null][null][null][null][null]American Airlines Inc.19.73877-156.0456333.94254-118.40807
532015114AA59N5DFAALAXKOA840837-31785435035233225041226312301229-100[null][null][null][null][null][null]American Airlines Inc.33.94254-118.4080719.73877-156.04563
542015114AA60N372AADFWMIA175419551211520101601511331121232332134232611200[null]0011200American Airlines Inc.32.89595-97.037225.79325-80.29056
552015114AA61N850AAMIADFW715719413732199192167112191912934931-300[null][null][null][null][null][null]American Airlines Inc.25.79325-80.2905632.89595-97.0372
562015114AA63N3ECAAMIAPHX19501955523201831730227119722249823072257-1000[null][null][null][null][null][null]American Airlines Inc.25.79325-80.2905633.43417-112.00806
572015114AA64N3HXAADFWJFK102510411613105420018716913911443514451448300[null][null][null][null][null][null]American Airlines Inc.32.89595-97.037240.63975-73.77893
582015114AA65N3HLAAJFKDFW16151625101916442552942271391193148193020194900[null]3901000American Airlines Inc.40.63975-73.7789332.89595-97.0372
592015114AA66N631AASJUJFK11511142-922120424524722115981445414561449-700[null][null][null][null][null][null]American Airlines Inc.18.43942-66.0018340.63975-73.77893
602015114AA67N631AAJFKSJU160516292423165223521118215982054621002100000[null][null][null][null][null][null]American Airlines Inc.40.63975-73.7789318.43942-66.00183
612015114AA68N5EGAASFOMIA70070331471732431729525851512815241520-400[null][null][null][null][null][null]American Airlines Inc.37.619-122.3748425.79325-80.29056
622015114AA69N3HTAAMIASFO18351836112184838635934225852130522012135-2600[null][null][null][null][null][null]American Airlines Inc.25.79325-80.2905637.619-122.37484
632015114AA70N5BTAASANDFW705659-622721180171137117111381212051150-1500[null][null][null][null][null][null]American Airlines Inc.32.73356-117.1896632.89595-97.0372
642015114AA71N3GHAADFWSAN18051803-214181718017616011711857219051859-600[null][null][null][null][null][null]American Airlines Inc.32.89595-97.037232.73356-117.18966
652015114AA72N5EKAADFWMCO6006066186241451421209859244925928300[null][null][null][null][null][null]American Airlines Inc.32.89595-97.037228.42889-81.31603
662015114AA72N5EKAAMCODFW10201015-516103117517114398511541212151206-900[null][null][null][null][null][null]American Airlines Inc.28.42889-81.3160332.89595-97.0372
672015114AA73N3FBAAPHLCLT1005957-8261023120108764491139612051145-2000[null][null][null][null][null][null]American Airlines Inc.39.87195-75.2411435.21401-80.94313
682015114AA74N488AACLEDFW15551552-311160319018116110211744918051753-1200[null][null][null][null][null][null]American Airlines Inc.41.41089-81.849432.89595-97.0372
692015114AA74N505AADFWCLE1145[null][null][null][null]150[null][null]1021[null][null]1515[null][null]01B[null][null][null][null][null]American Airlines Inc.32.89595-97.037241.41089-81.8494
702015114AA75N3HPAAIADLAX18301822-817183936035833522882114621302120-1000[null][null][null][null][null][null]American Airlines Inc.38.94453-77.4558133.94254-118.40807
712015114AA76N3HPAALAXIAD950946-418100428526524322881707417351711-2400[null][null][null][null][null][null]American Airlines Inc.33.94254-118.4080738.94453-77.45581
722015114AA76N3HPAASFOLAX730723-71573885974933782733855900500[null][null][null][null][null][null]American Airlines Inc.37.619-122.3748433.94254-118.40807
732015114AA79N520AADFWSMF2140221939302249225224188143123576232533800[null]000380American Airlines Inc.32.89595-97.037238.69542-121.59077
742015114AA80N4XTAATUSDFW1655174146101751130130105813203615200520514600[null]000460American Airlines Inc.32.11608-110.9410332.89595-97.0372
752015114AA81N4XCAADFWTUS22452308231323211401401238132445282300[null]000230American Airlines Inc.32.89595-97.037232.11608-110.94103
762015114AA83N4XBAAMCODFW735733-21674918017414698591512935927-800[null][null][null][null][null][null]American Airlines Inc.28.42889-81.3160332.89595-97.0372
772015114AA84N3AXAABOSJFK940930-10159457569461871031810551039-1600[null][null][null][null][null][null]American Airlines Inc.42.36435-71.0051840.63975-73.77893
782015114AA84N3AXAAJFKBOS7307344177517065441878354840839-100[null][null][null][null][null][null]American Airlines Inc.40.63975-73.7789342.36435-71.00518
792015114AA85N785AAJFKSFO15301524-620154440038636125861845519101850-2000[null][null][null][null][null][null]American Airlines Inc.40.63975-73.7789337.619-122.37484
802015114AA86N3GTAAPDXORD705701-41571623523020817391244713001251-900[null][null][null][null][null][null]American Airlines Inc.45.58872-122.597541.9796-87.90446
812015114AA87N490AAORDRNO18451840-511185126522821316712024421102028-4200[null][null][null][null][null][null]American Airlines Inc.41.9796-87.9044639.49858-119.76806
822015114AA89N3KVAAIAHMIA520618581963714113711196492878419355400[null]005400American Airlines Inc.29.98047-95.3397225.79325-80.29056
832015114AA92N3KHAASANORD90090001591524021018917231424615001430-3000[null][null][null][null][null][null]American Airlines Inc.32.73356-117.1896641.9796-87.90446
842015114AA93N3HHAAORDSAN1720180545101815255222210172319452193519471200[null][null][null][null][null][null]American Airlines Inc.41.9796-87.9044632.73356-117.18966
852015114AA94N3ANAASANJFK755810151382332028126224461545616151551-2400[null][null][null][null][null][null]American Airlines Inc.32.73356-117.1896640.63975-73.77893
862015114AA95N3ANAAJFKSAN1720174929181807380386363244621105204021153500[null]602900American Airlines Inc.40.63975-73.7789332.73356-117.18966
872015114AA96N553AADFWSTL82083111218529594705501002395510051000[null][null][null][null][null][null]American Airlines Inc.32.89595-97.037238.74769-90.35999
882015114AA96N553AASTLDFW103510511615110611513198550124418123013023200[null]1606100American Airlines Inc.38.74769-90.3599932.89595-97.0372
892015114AA97N3ENAADCADFW845842-314856215210185119211011111201112-800[null][null][null][null][null][null]American Airlines Inc.38.85208-77.0377232.89595-97.0372
902015114AA99N3KMAAORDPDX15501553311160427525323917391803318251806-1900[null][null][null][null][null][null]American Airlines Inc.41.9796-87.9044645.58872-122.5975
912015114AA102N376AAHNLDFW1920235627617134454454173784111011645112127600[null]0002760American Airlines Inc.21.31869-157.9224132.89595-97.0372
922015114AA103N4XSAADFWMSY141014332313144680806244715485153015532300[null]002300American Airlines Inc.32.89595-97.037229.99339-90.25803
932015114AA103N4XSAAMSYDFW16101624148163210091744471746917501755500[null][null][null][null][null][null]American Airlines Inc.29.99339-90.2580332.89595-97.0372
942015114AA110N3LRAASNAORD805801-47808230211194172613221013551332-2300[null][null][null][null][null][null]American Airlines Inc.33.67566-117.8682241.9796-87.90446
952015114AA111N3LYAAORDSNA17201719-111173026023722217261912419401916-2400[null][null][null][null][null][null]American Airlines Inc.41.9796-87.9044633.67566-117.86822
962015114AA112N857AADENMIA101510172191036224229199170915551115591606700[null][null][null][null][null][null]American Airlines Inc.39.85841-104.66725.79325-80.29056
972015114AA113N3GRAAMIADEN211521150212136269269235170923311323442344000[null][null][null][null][null][null]American Airlines Inc.25.79325-80.2905639.85841-104.667
982015114AA115N3CTAALAXMIA105103-21411728627625523428327851839-1200[null][null][null][null][null][null]American Airlines Inc.33.94254-118.4080725.79325-80.29056
992015114AA116N383AAOGGDFW1955201621102026430433418371172457057292400[null]302100American Airlines Inc.20.89865-156.4304632.89595-97.0372
1002015114AA117N793AAJFKLAX14451438-728150639038935524751801618151807-800[null][null][null][null][null][null]American Airlines Inc.40.63975-73.7789333.94254-118.40807
Rows: 1-100 | Columns: 36

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.

[63]:

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.stats as st
distances["distance"] = st.distance(distances["airport1_latitude"], distances["airport1_longitude"],
                                    distances["airport2_latitude"], distances["airport2_longitude"])

322 elements were filtered.

VerticaPy offers many powerful options for joining datasets.

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