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 vastorbit as vo

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

	123 year Integer 100%	123 month Integer 100%	123 day Integer 100%	123 day_of_week Integer 100%	Abc airline Varchar(50) 100%	123 flight_number Integer 100%	Abc tail_number Varchar(50) 99%	Abc origin_airport Varchar(50) 100%	Abc destination_airport Varchar(50) 100%	123 scheduled_departure Integer 100%	123 departure_time Integer 97%	123 departure_delay Integer 97%	123 taxi_out Integer 97%	123 wheels_off Integer 97%	123 scheduled_time Integer 100%	123 elapsed_time Integer 96%	123 air_time Integer 96%	123 distance Integer 100%	123 wheels_on Integer 97%	123 taxi_in Integer 97%	123 scheduled_arrival Integer 100%	123 arrival_time Integer 97%	123 arrival_delay Integer 96%	123 cancelled Integer 100%	Abc cancellation_reason Varchar(50) 2%	123 air_system_delay Integer 32%	123 security_delay Integer 32%	123 airline_delay Integer 32%	123 late_aircraft_delay Integer 32%	123 weather_delay Integer 32%
1	2015	1	1	4	AA	5	N376AA	DFW	HNL	1305	1754	289	21	1815	515	526	499	3784	2234	6	1740	2240	300	0	[null]	11	0	197	92	0
2	2015	1	1	4	AA	7	N398AA	DFW	OGG	1215	1513	178	24	1537	500	517	490	3711	1947	3	1635	1950	195	0	[null]	17	0	178	0	0
3	2015	1	1	4	AA	20	N789AA	SFO	JFK	1500	1503	3	19	1522	325	328	301	2586	2323	8	2325	2331	6	0	[null]	[null]	[null]	[null]	[null]	[null]
4	2015	1	1	4	AA	27	N5DUAA	DFW	LAS	1850	1929	39	16	1945	175	165	141	1055	2006	8	1945	2014	29	0	[null]	0	0	29	0	0
5	2015	1	1	4	AA	43	N3KCAA	DTW	DFW	1445	1445	0	19	1504	180	199	163	986	1647	17	1645	1704	19	0	[null]	19	0	0	0	0
6	2015	1	1	4	AA	61	N850AA	MIA	DFW	715	719	4	13	732	199	192	167	1121	919	12	934	931	-3	0	[null]	[null]	[null]	[null]	[null]	[null]
7	2015	1	1	4	AA	63	N3ECAA	MIA	PHX	1950	1955	5	23	2018	317	302	271	1972	2249	8	2307	2257	-10	0	[null]	[null]	[null]	[null]	[null]	[null]
8	2015	1	1	4	AA	65	N3HLAA	JFK	DFW	1615	1625	10	19	1644	255	294	227	1391	1931	48	1930	2019	49	0	[null]	39	0	10	0	0
9	2015	1	1	4	AA	72	N5EKAA	MCO	DFW	1020	1015	-5	16	1031	175	171	143	985	1154	12	1215	1206	-9	0	[null]	[null]	[null]	[null]	[null]	[null]
10	2015	1	1	4	AA	74	N505AA	DFW	CLE	1145	[null]	[null]	[null]	[null]	150	[null]	[null]	1021	[null]	[null]	1515	[null]	[null]	1	B	[null]	[null]	[null]	[null]	[null]
11	2015	1	1	4	AA	76	N3HPAA	LAX	IAD	950	946	-4	18	1004	285	265	243	2288	1707	4	1735	1711	-24	0	[null]	[null]	[null]	[null]	[null]	[null]
12	2015	1	1	4	AA	81	N4XCAA	DFW	TUS	2245	2308	23	13	2321	140	140	123	813	24	4	5	28	23	0	[null]	0	0	0	23	0
13	2015	1	1	4	AA	94	N3ANAA	SAN	JFK	755	810	15	13	823	320	281	262	2446	1545	6	1615	1551	-24	0	[null]	[null]	[null]	[null]	[null]	[null]
14	2015	1	1	4	AA	95	N3ANAA	JFK	SAN	1720	1749	29	18	1807	380	386	363	2446	2110	5	2040	2115	35	0	[null]	6	0	29	0	0
15	2015	1	1	4	AA	103	N4XSAA	DFW	MSY	1410	1433	23	13	1446	80	80	62	447	1548	5	1530	1553	23	0	[null]	0	0	23	0	0
16	2015	1	1	4	AA	110	N3LRAA	SNA	ORD	805	801	-4	7	808	230	211	194	1726	1322	10	1355	1332	-23	0	[null]	[null]	[null]	[null]	[null]	[null]
17	2015	1	1	4	AA	111	N3LYAA	ORD	SNA	1720	1719	-1	11	1730	260	237	222	1726	1912	4	1940	1916	-24	0	[null]	[null]	[null]	[null]	[null]	[null]
18	2015	1	1	4	AA	118	N788AA	LAX	JFK	700	650	-10	13	703	325	286	268	2475	1431	5	1525	1436	-49	0	[null]	[null]	[null]	[null]	[null]	[null]
19	2015	1	1	4	AA	126	N4XGAA	PHX	DFW	1410	1508	58	21	1529	140	137	107	868	1816	9	1730	1825	55	0	[null]	0	0	8	47	0
20	2015	1	1	4	AA	127	N3GHAA	IAD	DFW	625	622	-3	14	636	210	230	200	1172	856	16	855	912	17	0	[null]	17	0	0	0	0
21	2015	1	1	4	AA	132	N3DUAA	DFW	PHL	1335	1337	2	19	1356	180	184	160	1303	1736	5	1735	1741	6	0	[null]	[null]	[null]	[null]	[null]	[null]
22	2015	1	1	4	AA	134	N3HBAA	DCA	DFW	1745	1733	-12	12	1745	215	217	196	1192	2001	9	2020	2010	-10	0	[null]	[null]	[null]	[null]	[null]	[null]
23	2015	1	1	4	AA	138	N3CLAA	DFW	DCA	1655	1712	17	12	1724	160	141	125	1192	2029	4	2035	2033	-2	0	[null]	[null]	[null]	[null]	[null]	[null]
24	2015	1	1	4	AA	142	N5EDAA	HNL	LAX	2315	2314	-1	18	2332	325	303	278	2556	610	7	640	617	-23	0	[null]	[null]	[null]	[null]	[null]	[null]
25	2015	1	1	4	AA	148	N3EJAA	MIA	CLT	630	624	-6	15	639	125	120	96	650	815	9	835	824	-11	0	[null]	[null]	[null]	[null]	[null]	[null]
26	2015	1	1	4	AA	156	N590AA	LAX	STL	1150	1146	-4	23	1209	215	206	181	1592	1710	2	1725	1712	-13	0	[null]	[null]	[null]	[null]	[null]	[null]
27	2015	1	1	4	AA	160	N3KNAA	BOS	ORD	1005	957	-8	13	1010	180	174	153	867	1143	8	1205	1151	-14	0	[null]	[null]	[null]	[null]	[null]	[null]
28	2015	1	1	4	AA	162	N5ESAA	HNL	LAX	1340	1350	10	20	1410	330	318	283	2556	2053	15	2110	2108	-2	0	[null]	[null]	[null]	[null]	[null]	[null]
29	2015	1	1	4	AA	165	N3JSAA	BOS	LAX	1610	1617	7	12	1629	395	407	384	2611	1953	11	1945	2004	19	0	[null]	12	0	7	0	0
30	2015	1	1	4	AA	172	N791AA	LAX	JFK	1015	1007	-8	12	1019	330	298	275	2475	1754	11	1845	1805	-40	0	[null]	[null]	[null]	[null]	[null]	[null]
31	2015	1	1	4	AA	176	N3EPAA	DFW	DCA	1020	1022	2	29	1051	160	163	127	1192	1358	7	1400	1405	5	0	[null]	[null]	[null]	[null]	[null]	[null]
32	2015	1	1	4	AA	178	N3CCAA	DCA	DFW	1600	1646	46	12	1658	215	227	204	1192	1922	11	1835	1933	58	0	[null]	12	0	0	46	0
33	2015	1	1	4	AA	180	N798AA	LAX	JFK	1645	1645	0	14	1659	315	293	270	2475	29	9	100	38	-22	0	[null]	[null]	[null]	[null]	[null]	[null]
34	2015	1	1	4	AA	181	N796AA	JFK	LAX	1730	1724	-6	23	1747	385	395	366	2475	2053	6	2055	2059	4	0	[null]	[null]	[null]	[null]	[null]	[null]
35	2015	1	1	4	AA	184	N3LCAA	DFW	SFO	1025	[null]	[null]	[null]	[null]	225	[null]	[null]	1464	[null]	[null]	1210	[null]	[null]	1	B	[null]	[null]	[null]	[null]	[null]
36	2015	1	1	4	AA	189	N570AA	SJC	DFW	1515	1553	38	11	1604	205	212	188	1438	2112	13	2040	2125	45	0	[null]	7	0	3	35	0
37	2015	1	1	4	AA	194	N025AA	ATL	DFW	1525	1534	9	14	1548	145	149	123	731	1651	12	1650	1703	13	0	[null]	[null]	[null]	[null]	[null]	[null]
38	2015	1	1	4	AA	198	N3ELAA	ORD	JFK	1255	1252	-3	12	1304	130	112	95	740	1539	5	1605	1544	-21	0	[null]	[null]	[null]	[null]	[null]	[null]
39	2015	1	1	4	AA	209	N3LRAA	SFO	LAX	1805	1919	74	15	1934	80	80	52	337	2026	13	1925	2039	74	0	[null]	0	0	0	74	0
40	2015	1	1	4	AA	219	N3KYAA	SFO	LAX	2010	2005	-5	13	2018	90	77	52	337	2110	12	2140	2122	-18	0	[null]	[null]	[null]	[null]	[null]	[null]
41	2015	1	1	4	AA	231	N3FNAA	LAS	ORD	1635	1632	-3	16	1648	210	206	181	1514	2149	9	2205	2158	-7	0	[null]	[null]	[null]	[null]	[null]	[null]
42	2015	1	1	4	AA	234	N3EXAA	MIA	BWI	2135	2127	-8	21	2148	155	148	123	946	2351	4	10	2355	-15	0	[null]	[null]	[null]	[null]	[null]	[null]
43	2015	1	1	4	AA	241	N3LRAA	ORD	SFO	1610	1606	-4	19	1625	280	264	242	1846	1827	3	1850	1830	-20	0	[null]	[null]	[null]	[null]	[null]	[null]
44	2015	1	1	4	AA	253	N378AA	LAX	OGG	900	856	-4	19	915	355	353	332	2486	1247	2	1255	1249	-6	0	[null]	[null]	[null]	[null]	[null]	[null]
45	2015	1	1	4	AA	262	N3MDAA	RSW	DFW	1335	1329	-6	10	1339	180	173	152	1017	1511	11	1535	1522	-13	0	[null]	[null]	[null]	[null]	[null]	[null]
46	2015	1	1	4	AA	263	N633AA	MCO	LAX	1635	1628	-7	15	1643	335	310	287	2218	1830	8	1910	1838	-32	0	[null]	[null]	[null]	[null]	[null]	[null]
47	2015	1	1	4	AA	275	N634AA	MCO	MIA	830	828	-2	11	839	67	57	39	192	918	7	937	925	-12	0	[null]	[null]	[null]	[null]	[null]	[null]
48	2015	1	1	4	AA	294	N4YDAA	DFW	ORF	1240	1255	15	14	1309	165	153	136	1212	1625	3	1625	1628	3	0	[null]	[null]	[null]	[null]	[null]	[null]
49	2015	1	1	4	AA	294	N4YDAA	ORF	DFW	1710	1705	-5	17	1722	215	232	204	1212	1946	11	1945	1957	12	0	[null]	[null]	[null]	[null]	[null]	[null]
50	2015	1	1	4	AA	298	N5DGAA	HNL	LAX	2155	2159	4	20	2219	320	313	283	2556	502	10	515	512	-3	0	[null]	[null]	[null]	[null]	[null]	[null]
51	2015	1	1	4	AA	309	N4YDAA	DFW	BNA	700	652	-8	31	723	100	112	74	631	837	7	840	844	4	0	[null]	[null]	[null]	[null]	[null]	[null]
52	2015	1	1	4	AA	310	N201AA	MFE	DFW	605	[null]	[null]	[null]	[null]	95	[null]	[null]	469	[null]	[null]	740	[null]	[null]	1	B	[null]	[null]	[null]	[null]	[null]
53	2015	1	1	4	AA	315	N3MAAA	LGA	ORD	700	658	-2	16	714	165	165	136	733	830	13	845	843	-2	0	[null]	[null]	[null]	[null]	[null]	[null]
54	2015	1	1	4	AA	316	N557AA	DFW	DEN	1140	1146	6	29	1215	120	120	87	641	1242	4	1240	1246	6	0	[null]	[null]	[null]	[null]	[null]	[null]
55	2015	1	1	4	AA	320	N4XPAA	EWR	DFW	1445	1450	5	16	1506	245	251	222	1372	1748	13	1750	1801	11	0	[null]	[null]	[null]	[null]	[null]	[null]
56	2015	1	1	4	AA	323	N520AA	DFW	STL	645	[null]	[null]	[null]	[null]	95	[null]	[null]	550	[null]	[null]	820	[null]	[null]	1	B	[null]	[null]	[null]	[null]	[null]
57	2015	1	1	4	AA	325	N3LWAA	LGA	ORD	945	933	-12	23	956	160	168	136	733	1112	9	1125	1121	-4	0	[null]	[null]	[null]	[null]	[null]	[null]
58	2015	1	1	4	AA	326	N486AA	STL	DFW	1205	[null]	[null]	[null]	[null]	115	[null]	[null]	550	[null]	[null]	1400	[null]	[null]	1	A	[null]	[null]	[null]	[null]	[null]
59	2015	1	1	4	AA	329	N3LGAA	LGA	ORD	1130	1126	-4	21	1147	165	156	128	733	1255	7	1315	1302	-13	0	[null]	[null]	[null]	[null]	[null]	[null]
60	2015	1	1	4	AA	334	N494AA	DFW	MFE	915	[null]	[null]	[null]	[null]	95	[null]	[null]	469	[null]	[null]	1050	[null]	[null]	1	B	[null]	[null]	[null]	[null]	[null]
61	2015	1	1	4	AA	338	N3DJAA	LAX	SFO	650	644	-6	21	705	80	79	55	337	800	3	810	803	-7	0	[null]	[null]	[null]	[null]	[null]	[null]
62	2015	1	1	4	AA	348	N3ENAA	FLL	DFW	1650	1651	1	17	1708	200	217	163	1119	1851	37	1910	1928	18	0	[null]	18	0	0	0	0
63	2015	1	1	4	AA	350	N3MBAA	ORD	LGA	1735	1728	-7	13	1741	120	111	93	733	2014	5	2035	2019	-16	0	[null]	[null]	[null]	[null]	[null]	[null]
64	2015	1	1	4	AA	362	N582AA	DFW	MKE	2000	2027	27	10	2037	135	110	96	853	2213	4	2215	2217	2	0	[null]	[null]	[null]	[null]	[null]	[null]
65	2015	1	1	4	AA	365	N3JPAA	DCA	DFW	1005	1029	24	11	1040	210	223	193	1192	1253	19	1235	1312	37	0	[null]	13	0	24	0	0
66	2015	1	1	4	AA	374	N3ARAA	ORD	LGA	1225	1221	-4	13	1234	120	106	91	733	1505	2	1525	1507	-18	0	[null]	[null]	[null]	[null]	[null]	[null]
67	2015	1	1	4	AA	379	N5CEAA	DFW	SFO	1315	1338	23	22	1400	225	211	185	1464	1505	4	1500	1509	9	0	[null]	[null]	[null]	[null]	[null]	[null]
68	2015	1	1	4	AA	380	N3HVAA	ORD	LGA	1040	1053	13	11	1104	120	98	84	733	1328	3	1340	1331	-9	0	[null]	[null]	[null]	[null]	[null]	[null]
69	2015	1	1	4	AA	384	N3EEAA	ORD	LGA	930	934	4	10	944	120	100	87	733	1211	3	1230	1214	-16	0	[null]	[null]	[null]	[null]	[null]	[null]
70	2015	1	1	4	AA	385	N403AA	BNA	DFW	825	817	-8	9	826	135	118	105	631	1011	4	1040	1015	-25	0	[null]	[null]	[null]	[null]	[null]	[null]
71	2015	1	1	4	AA	386	N434AA	JAX	DFW	1825	2026	121	13	2039	170	159	136	918	2155	10	2015	2205	110	0	[null]	0	0	0	110	0
72	2015	1	1	4	AA	395	N3MCAA	ORD	SJU	830	1409	339	14	1423	270	241	223	2072	2006	4	1500	2010	310	0	[null]	0	0	75	235	0
73	2015	1	1	4	AA	397	N3CGAA	MIA	SJU	925	921	-4	16	937	155	148	122	1045	1239	10	1300	1249	-11	0	[null]	[null]	[null]	[null]	[null]	[null]
74	2015	1	1	4	AA	397	N3CGAA	SJU	MIA	1401	1356	-5	11	1407	174	181	157	1045	1544	13	1555	1557	2	0	[null]	[null]	[null]	[null]	[null]	[null]
75	2015	1	1	4	AA	942	N491AA	DFW	TPA	1705	2013	188	11	2024	135	129	113	929	2317	5	2020	2322	182	0	[null]	0	0	23	159	0
76	2015	1	1	4	AA	959	N635AA	MIA	DFW	1245	1244	-1	11	1255	198	217	168	1121	1443	38	1503	1521	18	0	[null]	18	0	0	0	0
77	2015	1	1	4	AA	976	N4XLAA	IAH	DFW	1335	1339	4	12	1351	70	62	41	224	1432	9	1445	1441	-4	0	[null]	[null]	[null]	[null]	[null]	[null]
78	2015	1	1	4	AA	978	N549AA	DFW	COS	1555	1618	23	16	1634	115	111	86	592	1700	9	1650	1709	19	0	[null]	0	0	0	17	2
79	2015	1	1	4	AA	986	N5ENAA	MIA	STT	1115	1110	-5	14	1124	163	147	129	1107	1433	4	1458	1437	-21	0	[null]	[null]	[null]	[null]	[null]	[null]
80	2015	1	1	4	AA	989	N854AA	LAS	MIA	1500	1638	98	36	1714	270	286	248	2174	22	2	2230	24	114	0	[null]	16	0	0	98	0
81	2015	1	1	4	AA	1015	N4XRAA	DFW	SAN	1535	1614	39	23	1637	195	189	162	1171	1719	4	1650	1723	33	0	[null]	0	0	0	33	0
82	2015	1	1	4	AA	1015	N4XRAA	SAN	DFW	1730	1750	20	15	1805	170	167	144	1171	2229	8	2220	2237	17	0	[null]	0	0	0	17	0
83	2015	1	1	4	AA	1020	N483AA	MEM	DFW	1825	1835	10	10	1845	105	99	78	432	2003	11	2010	2014	4	0	[null]	[null]	[null]	[null]	[null]	[null]
84	2015	1	1	4	AA	1042	N3ANAA	SAN	MIA	2230	2226	-4	23	2249	279	287	255	2267	604	9	609	613	4	0	[null]	[null]	[null]	[null]	[null]	[null]
85	2015	1	1	4	AA	1046	N505AA	DFW	MCI	900	911	11	15	926	85	82	61	460	1027	6	1025	1033	8	0	[null]	[null]	[null]	[null]	[null]	[null]
86	2015	1	1	4	AA	1057	N3ASAA	DFW	MIA	515	703	108	15	718	161	155	133	1121	1031	7	856	1038	102	0	[null]	0	0	0	0	102
87	2015	1	1	4	AA	1059	N3BEAA	MIA	MSY	2000	1958	-2	28	2026	131	146	115	675	2121	3	2111	2124	13	0	[null]	[null]	[null]	[null]	[null]	[null]
88	2015	1	1	4	AA	1071	N4YTAA	DFW	HOU	1250	1249	-1	13	1302	65	65	49	247	1351	3	1355	1354	-1	0	[null]	[null]	[null]	[null]	[null]	[null]
89	2015	1	1	4	AA	1084	N3GFAA	LGA	MIA	945	939	-6	14	953	195	193	150	1096	1223	29	1300	1252	-8	0	[null]	[null]	[null]	[null]	[null]	[null]
90	2015	1	1	4	AA	1085	N635AA	JFK	MIA	830	841	11	23	904	204	181	152	1089	1136	6	1154	1142	-12	0	[null]	[null]	[null]	[null]	[null]	[null]
91	2015	1	1	4	AA	1086	N486AA	DFW	SLC	940	936	-4	22	958	170	165	135	989	1113	8	1130	1121	-9	0	[null]	[null]	[null]	[null]	[null]	[null]
92	2015	1	1	4	AA	1104	N3LAAA	LGA	DFW	1150	1148	-2	10	1158	250	242	221	1389	1439	11	1500	1450	-10	0	[null]	[null]	[null]	[null]	[null]	[null]
93	2015	1	1	4	AA	1110	N3CKAA	LGA	DFW	1329	1323	-6	15	1338	250	259	229	1389	1627	15	1639	1642	3	0	[null]	[null]	[null]	[null]	[null]	[null]
94	2015	1	1	4	AA	1112	N3LAAA	SFO	DFW	30	19	-11	17	36	195	193	173	1464	529	3	545	532	-13	0	[null]	[null]	[null]	[null]	[null]	[null]
95	2015	1	1	4	AA	1115	N486AA	DFW	MFE	1640	1656	16	12	1708	90	97	82	469	1830	3	1810	1833	23	0	[null]	7	0	0	16	0
96	2015	1	1	4	AA	1116	N4XNAA	MSP	ORD	1955	2233	158	10	2243	90	70	53	334	2336	7	2125	2343	138	0	[null]	0	0	0	138	0
97	2015	1	1	4	AA	1121	N3GUAA	LGA	DFW	815	841	26	14	855	255	254	228	1389	1143	12	1130	1155	25	0	[null]	0	0	25	0	0
98	2015	1	1	4	AA	1133	N3FJAA	LGA	MIA	1055	1054	-1	12	1106	195	165	149	1096	1335	4	1410	1339	-31	0	[null]	[null]	[null]	[null]	[null]	[null]
99	2015	1	1	4	AA	1133	N3FJAA	MIA	LGA	715	713	-2	16	729	175	176	150	1096	959	10	1010	1009	-1	0	[null]	[null]	[null]	[null]	[null]	[null]
100	2015	1	1	4	AA	1140	N3GUAA	LGA	DFW	1730	1731	1	13	1744	250	260	217	1389	2021	30	2040	2051	11	0	[null]	[null]	[null]	[null]	[null]	[null]

Second, we have information on each airport.

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

	Abc iata_code Varchar(20) 100%	Abc airport Varchar(156) 100%	Abc city Varchar(60) 100%	Abc state Varchar(20) 100%	Abc country Varchar(20) 100%	🌎 latitude Real 99%	🌎 longitude Real 99%
1	ABE	Lehigh Valley International Airport	Allentown	PA	USA	40.65236	-75.4404
2	ABI	Abilene Regional Airport	Abilene	TX	USA	32.41132	-99.6819
3	ABQ	Albuquerque International Sunport	Albuquerque	NM	USA	35.04022	-106.60919
4	ABR	Aberdeen Regional Airport	Aberdeen	SD	USA	45.44906	-98.42183
5	ABY	Southwest Georgia Regional Airport	Albany	GA	USA	31.53552	-84.19447
6	ACK	Nantucket Memorial Airport	Nantucket	MA	USA	41.25305	-70.06018
7	ACT	Waco Regional Airport	Waco	TX	USA	31.61129	-97.23052
8	ACV	Arcata Airport	Arcata/Eureka	CA	USA	40.97812	-124.10862
9	ACY	Atlantic City International Airport	Atlantic City	NJ	USA	39.45758	-74.57717
10	ADK	Adak Airport	Adak	AK	USA	51.87796	-176.64604
11	ADQ	Kodiak Airport	Kodiak	AK	USA	57.74997	-152.49387
12	AEX	Alexandria International Airport	Alexandria	LA	USA	31.32737	-92.54856
13	AGS	Augusta Regional Airport (Bush Field)	Augusta	GA	USA	33.36996	-81.9645
14	AKN	King Salmon Airport	King Salmon	AK	USA	58.6768	-156.64922
15	ALB	Albany International Airport	Albany	NY	USA	42.74812	-73.80298
16	ALO	Waterloo Regional Airport	Waterloo	IA	USA	42.55708	-92.40034
17	AMA	Rick Husband Amarillo International Airport	Amarillo	TX	USA	35.21937	-101.70593
18	ANC	Ted Stevens Anchorage International Airport	Anchorage	AK	USA	61.17432	-149.99619
19	APN	Alpena County Regional Airport	Alpena	MI	USA	45.07807	-83.56029
20	ASE	Aspen-Pitkin County Airport	Aspen	CO	USA	39.22316	-106.86885
21	ATL	Hartsfield-Jackson Atlanta International Airport	Atlanta	GA	USA	33.64044	-84.42694
22	ATW	Appleton International Airport	Appleton	WI	USA	44.25741	-88.51948
23	AUS	Austin-Bergstrom International Airport	Austin	TX	USA	30.19453	-97.66987
24	AVL	Asheville Regional Airport	Asheville	NC	USA	35.43619	-82.54181
25	AVP	Wilkes-Barre/Scranton International Airport	Wilkes-Barre/Scranton	PA	USA	41.33815	-75.72427
26	AZO	Kalamazoo/Battle Creek International Airport	Kalamazoo	MI	USA	42.23488	-85.55206
27	BDL	Bradley International Airport	Windsor Locks	CT	USA	41.93887	-72.68323
28	BET	Bethel Airport	Bethel	AK	USA	60.77978	-161.838
29	BFL	Meadows Field	Bakersfield	CA	USA	35.4336	-119.05677
30	BGM	Greater Binghamton Airport	Binghamton	NY	USA	42.20848	-75.97961
31	BGR	Bangor International Airport	Bangor	ME	USA	44.80744	-68.82814
32	BHM	Birmingham-Shuttlesworth International Airport	Birmingham	AL	USA	33.56294	-86.75355
33	BIL	Billings Logan International Airport	Billings	MT	USA	45.80766	-108.54286
34	BIS	Bismarck Municipal Airport	Bismarck	ND	USA	46.77411	-100.74672
35	BJI	Bemidji Regional Airport	Bemidji	MN	USA	47.50942	-94.93372
36	BLI	Bellingham International Airport	Bellingham	WA	USA	48.79275	-122.53753
37	BMI	Central Illinois Regional Airport at Bloomington-Normal	Bloomington	IL	USA	40.47799	-88.91595
38	BNA	Nashville International Airport	Nashville	TN	USA	36.12448	-86.67818
39	BOI	Boise Airport (Boise Air Terminal)	Boise	ID	USA	43.56444	-116.22278
40	BOS	Gen. Edward Lawrence Logan International Airport	Boston	MA	USA	42.36435	-71.00518
41	BPT	Jack Brooks Regional Airport (Southeast Texas Regional)	Beaumont/Port Arthur	TX	USA	29.95083	-94.02069
42	BQK	Brunswick Golden Isles Airport	Brunswick	GA	USA	31.25903	-81.46631
43	BQN	Rafael Hernández Airport	Aguadilla	PR	USA	18.49486	-67.12944
44	BRD	Brainerd Lakes Regional Airport	Brainerd	MN	USA	46.39786	-94.13723
45	BRO	Brownsville/South Padre Island International Airport	Brownsville	TX	USA	25.90683	-97.42586
46	BRW	Wiley Post-Will Rogers Memorial Airport	Barrow	AK	USA	71.28545	-156.766
47	BTM	Bert Mooney Airport	Butte	MT	USA	45.9548	-112.49746
48	BTR	Baton Rouge Metropolitan Airport	Baton Rouge	LA	USA	30.53316	-91.14963
49	BTV	Burlington International Airport	Burlington	VT	USA	44.473	-73.15031
50	BUF	Buffalo Niagara International Airport	Buffalo	NY	USA	42.94052	-78.73217
51	BUR	Bob Hope Airport (Hollywood Burbank Airport)	Burbank	CA	USA	34.20062	-118.3585
52	BWI	Baltimore-Washington International Airport	Baltimore	MD	USA	39.1754	-76.6682
53	BZN	Bozeman Yellowstone International Airport (Gallatin Field Airport)	Bozeman	MT	USA	45.7769	-111.15301
54	CAE	Columbia Metropolitan Airport	Columbia	SC	USA	33.93884	-81.11954
55	CAK	Akron-Canton Regional Airport	Akron	OH	USA	40.91631	-81.44247
56	CDC	Cedar City Regional Airport	Cedar City	UT	USA	37.70097	-113.09858
57	CDV	Merle K. (Mudhole) Smith Airport	Cordova	AK	USA	60.49183	-145.47765
58	CEC	Del Norte County Airport (Jack McNamara Field)	Crescent City	CA	USA	41.78016	-124.23653
59	CHA	Chattanooga Metropolitan Airport (Lovell Field)	Chattanooga	TN	USA	35.03527	-85.20379
60	CHO	Charlottesville-Albemarle Airport	Charlottesville	VA	USA	38.13864	-78.45286
61	CHS	Charleston International Airport/Charleston AFB	Charleston	SC	USA	32.89865	-80.04051
62	CID	The Eastern Iowa Airport	Cedar Rapids	IA	USA	41.88459	-91.71087
63	CIU	Chippewa County International Airport	Sault Ste. Marie	MI	USA	46.25075	-84.47239
64	CLD	McClellan-Palomar Airport	San Diego	CA	USA	33.12723	-117.27873
65	CLE	Cleveland Hopkins International Airport	Cleveland	OH	USA	41.41089	-81.8494
66	CLL	Easterwood Airport	College Station	TX	USA	30.58859	-96.36382
67	CLT	Charlotte Douglas International Airport	Charlotte	NC	USA	35.21401	-80.94313
68	CMH	Port Columbus International Airport	Columbus	OH	USA	39.99799	-82.89188
69	CMI	University of Illinois - Willard Airport	Champaign/Urbana	IL	USA	40.03925	-88.27806
70	CMX	Houghton County Memorial Airport	Hancock	MI	USA	47.16842	-88.48906
71	CNY	Canyonlands Field	Moab	UT	USA	38.75496	-109.75484
72	COD	Yellowstone Regional Airport	Cody	WY	USA	44.52019	-109.0238
73	COS	City of Colorado Springs Municipal Airport	Colorado Springs	CO	USA	38.80581	-104.70025
74	COU	Columbia Regional Airport	Columbia	MO	USA	38.81809	-92.21963
75	CPR	Natrona County International Airport	Casper	WY	USA	42.90836	-106.46447
76	CRP	Corpus Christi International Airport	Corpus Christi	TX	USA	27.77036	-97.50122
77	CRW	Yeager Airport	Charleston	WV	USA	38.37315	-81.59319
78	CSG	Columbus Metropolitan Airport	Columbus	GA	USA	32.51633	-84.93886
79	CVG	Cincinnati/Northern Kentucky International Airport	Covington	KY	USA	39.04614	-84.66217
80	CWA	Central Wisconsin Airport	Mosinee	WI	USA	44.77762	-89.66678
81	DAB	Daytona Beach International Airport	Daytona Beach	FL	USA	29.17992	-81.05806
82	DAL	Dallas Love Field	Dallas	TX	USA	32.84711	-96.85177
83	DAY	James M. Cox Dayton International Airport	Dayton	OH	USA	39.90238	-84.21938
84	DBQ	Dubuque Regional Airport	Dubuque	IA	USA	42.40296	-90.70917
85	DCA	Ronald Reagan Washington National Airport	Arlington	VA	USA	38.85208	-77.03772
86	DEN	Denver International Airport	Denver	CO	USA	39.85841	-104.667
87	DFW	Dallas/Fort Worth International Airport	Dallas-Fort Worth	TX	USA	32.89595	-97.0372
88	DHN	Dothan Regional Airport	Dothan	AL	USA	31.32134	-85.44963
89	DIK	Dickinson Theodore Roosevelt Regional Airport	Dickinson	ND	USA	46.79739	-102.80195
90	DLG	Dillingham Airport	Dillingham	AK	USA	59.04541	-158.50334
91	DLH	Duluth International Airport	Duluth	MN	USA	46.84209	-92.19365
92	DRO	Durango-La Plata County Airport	Durango	CO	USA	37.15152	-107.75377
93	DSM	Des Moines International Airport	Des Moines	IA	USA	41.53493	-93.66068
94	DTW	Detroit Metropolitan Airport	Detroit	MI	USA	42.21206	-83.34884
95	DVL	Devils Lake Regional Airport	Devils Lake	ND	USA	48.11425	-98.90878
96	EAU	Chippewa Valley Regional Airport	Eau Claire	WI	USA	44.86526	-91.48507
97	ECP	Northwest Florida Beaches International Airport	Panama City	FL	USA	[null]	[null]
98	EGE	Eagle County Regional Airport	Eagle	CO	USA	39.64257	-106.9177
99	EKO	Elko Regional Airport	Elko	NV	USA	40.82493	-115.7917
100	ELM	Elmira/Corning Regional Airport	Elmira	NY	USA	42.15991	-76.89144

Third, we have the names of each airline.

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

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

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 vastorbit, use the join() method.

help(vo.VastFrame.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 month Integer 100%	123 day Integer 100%	123 day_of_week Integer 100%	Abc airline Varchar(50) 100%	123 flight_number Integer 100%	Abc tail_number Varchar(50) 99%	Abc origin_airport Varchar(50) 100%	Abc destination_airport Varchar(50) 100%	123 scheduled_departure Integer 100%	123 departure_time Integer 97%	123 departure_delay Integer 97%	123 taxi_out Integer 97%	123 wheels_off Integer 97%	123 scheduled_time Integer 100%	123 elapsed_time Integer 96%	123 air_time Integer 96%	123 distance Integer 100%	123 wheels_on Integer 97%	123 taxi_in Integer 97%	123 scheduled_arrival Integer 100%	123 arrival_time Integer 97%	123 arrival_delay Integer 96%	123 cancelled Integer 100%	Abc cancellation_reason Varchar(50) 2%	123 air_system_delay Integer 32%	123 security_delay Integer 32%	123 airline_delay Integer 32%	123 late_aircraft_delay Integer 32%	123 weather_delay Integer 32%	Abc airline_long Varchar(50) 100%
1	2015	1	1	4	AA	5	N376AA	DFW	HNL	1305	1754	289	21	1815	515	526	499	3784	2234	6	1740	2240	300	0	[null]	11	0	197	92	0	American Airlines Inc.
2	2015	1	1	4	AA	7	N398AA	DFW	OGG	1215	1513	178	24	1537	500	517	490	3711	1947	3	1635	1950	195	0	[null]	17	0	178	0	0	American Airlines Inc.
3	2015	1	1	4	AA	20	N789AA	SFO	JFK	1500	1503	3	19	1522	325	328	301	2586	2323	8	2325	2331	6	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.
4	2015	1	1	4	AA	27	N5DUAA	DFW	LAS	1850	1929	39	16	1945	175	165	141	1055	2006	8	1945	2014	29	0	[null]	0	0	29	0	0	American Airlines Inc.
5	2015	1	1	4	AA	43	N3KCAA	DTW	DFW	1445	1445	0	19	1504	180	199	163	986	1647	17	1645	1704	19	0	[null]	19	0	0	0	0	American Airlines Inc.
6	2015	1	1	4	AA	61	N850AA	MIA	DFW	715	719	4	13	732	199	192	167	1121	919	12	934	931	-3	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.
7	2015	1	1	4	AA	63	N3ECAA	MIA	PHX	1950	1955	5	23	2018	317	302	271	1972	2249	8	2307	2257	-10	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.
8	2015	1	1	4	AA	65	N3HLAA	JFK	DFW	1615	1625	10	19	1644	255	294	227	1391	1931	48	1930	2019	49	0	[null]	39	0	10	0	0	American Airlines Inc.
9	2015	1	1	4	AA	72	N5EKAA	MCO	DFW	1020	1015	-5	16	1031	175	171	143	985	1154	12	1215	1206	-9	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.
10	2015	1	1	4	AA	74	N505AA	DFW	CLE	1145	[null]	[null]	[null]	[null]	150	[null]	[null]	1021	[null]	[null]	1515	[null]	[null]	1	B	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.
11	2015	1	1	4	AA	76	N3HPAA	LAX	IAD	950	946	-4	18	1004	285	265	243	2288	1707	4	1735	1711	-24	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.
12	2015	1	1	4	AA	81	N4XCAA	DFW	TUS	2245	2308	23	13	2321	140	140	123	813	24	4	5	28	23	0	[null]	0	0	0	23	0	American Airlines Inc.
13	2015	1	1	4	AA	94	N3ANAA	SAN	JFK	755	810	15	13	823	320	281	262	2446	1545	6	1615	1551	-24	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.
14	2015	1	1	4	AA	95	N3ANAA	JFK	SAN	1720	1749	29	18	1807	380	386	363	2446	2110	5	2040	2115	35	0	[null]	6	0	29	0	0	American Airlines Inc.
15	2015	1	1	4	AA	103	N4XSAA	DFW	MSY	1410	1433	23	13	1446	80	80	62	447	1548	5	1530	1553	23	0	[null]	0	0	23	0	0	American Airlines Inc.
16	2015	1	1	4	AA	110	N3LRAA	SNA	ORD	805	801	-4	7	808	230	211	194	1726	1322	10	1355	1332	-23	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.
17	2015	1	1	4	AA	111	N3LYAA	ORD	SNA	1720	1719	-1	11	1730	260	237	222	1726	1912	4	1940	1916	-24	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.
18	2015	1	1	4	AA	118	N788AA	LAX	JFK	700	650	-10	13	703	325	286	268	2475	1431	5	1525	1436	-49	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.
19	2015	1	1	4	AA	126	N4XGAA	PHX	DFW	1410	1508	58	21	1529	140	137	107	868	1816	9	1730	1825	55	0	[null]	0	0	8	47	0	American Airlines Inc.
20	2015	1	1	4	AA	127	N3GHAA	IAD	DFW	625	622	-3	14	636	210	230	200	1172	856	16	855	912	17	0	[null]	17	0	0	0	0	American Airlines Inc.
21	2015	1	1	4	AA	132	N3DUAA	DFW	PHL	1335	1337	2	19	1356	180	184	160	1303	1736	5	1735	1741	6	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.
22	2015	1	1	4	AA	134	N3HBAA	DCA	DFW	1745	1733	-12	12	1745	215	217	196	1192	2001	9	2020	2010	-10	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.
23	2015	1	1	4	AA	138	N3CLAA	DFW	DCA	1655	1712	17	12	1724	160	141	125	1192	2029	4	2035	2033	-2	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.
24	2015	1	1	4	AA	142	N5EDAA	HNL	LAX	2315	2314	-1	18	2332	325	303	278	2556	610	7	640	617	-23	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.
25	2015	1	1	4	AA	148	N3EJAA	MIA	CLT	630	624	-6	15	639	125	120	96	650	815	9	835	824	-11	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.
26	2015	1	1	4	AA	156	N590AA	LAX	STL	1150	1146	-4	23	1209	215	206	181	1592	1710	2	1725	1712	-13	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.
27	2015	1	1	4	AA	160	N3KNAA	BOS	ORD	1005	957	-8	13	1010	180	174	153	867	1143	8	1205	1151	-14	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.
28	2015	1	1	4	AA	162	N5ESAA	HNL	LAX	1340	1350	10	20	1410	330	318	283	2556	2053	15	2110	2108	-2	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.
29	2015	1	1	4	AA	165	N3JSAA	BOS	LAX	1610	1617	7	12	1629	395	407	384	2611	1953	11	1945	2004	19	0	[null]	12	0	7	0	0	American Airlines Inc.
30	2015	1	1	4	AA	172	N791AA	LAX	JFK	1015	1007	-8	12	1019	330	298	275	2475	1754	11	1845	1805	-40	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.
31	2015	1	1	4	AA	176	N3EPAA	DFW	DCA	1020	1022	2	29	1051	160	163	127	1192	1358	7	1400	1405	5	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.
32	2015	1	1	4	AA	178	N3CCAA	DCA	DFW	1600	1646	46	12	1658	215	227	204	1192	1922	11	1835	1933	58	0	[null]	12	0	0	46	0	American Airlines Inc.
33	2015	1	1	4	AA	180	N798AA	LAX	JFK	1645	1645	0	14	1659	315	293	270	2475	29	9	100	38	-22	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.
34	2015	1	1	4	AA	181	N796AA	JFK	LAX	1730	1724	-6	23	1747	385	395	366	2475	2053	6	2055	2059	4	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.
35	2015	1	1	4	AA	184	N3LCAA	DFW	SFO	1025	[null]	[null]	[null]	[null]	225	[null]	[null]	1464	[null]	[null]	1210	[null]	[null]	1	B	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.
36	2015	1	1	4	AA	189	N570AA	SJC	DFW	1515	1553	38	11	1604	205	212	188	1438	2112	13	2040	2125	45	0	[null]	7	0	3	35	0	American Airlines Inc.
37	2015	1	1	4	AA	194	N025AA	ATL	DFW	1525	1534	9	14	1548	145	149	123	731	1651	12	1650	1703	13	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.
38	2015	1	1	4	AA	198	N3ELAA	ORD	JFK	1255	1252	-3	12	1304	130	112	95	740	1539	5	1605	1544	-21	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.
39	2015	1	1	4	AA	209	N3LRAA	SFO	LAX	1805	1919	74	15	1934	80	80	52	337	2026	13	1925	2039	74	0	[null]	0	0	0	74	0	American Airlines Inc.
40	2015	1	1	4	AA	219	N3KYAA	SFO	LAX	2010	2005	-5	13	2018	90	77	52	337	2110	12	2140	2122	-18	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.
41	2015	1	1	4	AA	231	N3FNAA	LAS	ORD	1635	1632	-3	16	1648	210	206	181	1514	2149	9	2205	2158	-7	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.
42	2015	1	1	4	AA	234	N3EXAA	MIA	BWI	2135	2127	-8	21	2148	155	148	123	946	2351	4	10	2355	-15	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.
43	2015	1	1	4	AA	241	N3LRAA	ORD	SFO	1610	1606	-4	19	1625	280	264	242	1846	1827	3	1850	1830	-20	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.
44	2015	1	1	4	AA	253	N378AA	LAX	OGG	900	856	-4	19	915	355	353	332	2486	1247	2	1255	1249	-6	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.
45	2015	1	1	4	AA	262	N3MDAA	RSW	DFW	1335	1329	-6	10	1339	180	173	152	1017	1511	11	1535	1522	-13	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.
46	2015	1	1	4	AA	263	N633AA	MCO	LAX	1635	1628	-7	15	1643	335	310	287	2218	1830	8	1910	1838	-32	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.
47	2015	1	1	4	AA	275	N634AA	MCO	MIA	830	828	-2	11	839	67	57	39	192	918	7	937	925	-12	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.
48	2015	1	1	4	AA	294	N4YDAA	DFW	ORF	1240	1255	15	14	1309	165	153	136	1212	1625	3	1625	1628	3	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.
49	2015	1	1	4	AA	294	N4YDAA	ORF	DFW	1710	1705	-5	17	1722	215	232	204	1212	1946	11	1945	1957	12	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.
50	2015	1	1	4	AA	298	N5DGAA	HNL	LAX	2155	2159	4	20	2219	320	313	283	2556	502	10	515	512	-3	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.
51	2015	1	1	4	AA	309	N4YDAA	DFW	BNA	700	652	-8	31	723	100	112	74	631	837	7	840	844	4	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.
52	2015	1	1	4	AA	310	N201AA	MFE	DFW	605	[null]	[null]	[null]	[null]	95	[null]	[null]	469	[null]	[null]	740	[null]	[null]	1	B	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.
53	2015	1	1	4	AA	315	N3MAAA	LGA	ORD	700	658	-2	16	714	165	165	136	733	830	13	845	843	-2	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.
54	2015	1	1	4	AA	316	N557AA	DFW	DEN	1140	1146	6	29	1215	120	120	87	641	1242	4	1240	1246	6	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.
55	2015	1	1	4	AA	320	N4XPAA	EWR	DFW	1445	1450	5	16	1506	245	251	222	1372	1748	13	1750	1801	11	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.
56	2015	1	1	4	AA	323	N520AA	DFW	STL	645	[null]	[null]	[null]	[null]	95	[null]	[null]	550	[null]	[null]	820	[null]	[null]	1	B	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.
57	2015	1	1	4	AA	325	N3LWAA	LGA	ORD	945	933	-12	23	956	160	168	136	733	1112	9	1125	1121	-4	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.
58	2015	1	1	4	AA	326	N486AA	STL	DFW	1205	[null]	[null]	[null]	[null]	115	[null]	[null]	550	[null]	[null]	1400	[null]	[null]	1	A	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.
59	2015	1	1	4	AA	329	N3LGAA	LGA	ORD	1130	1126	-4	21	1147	165	156	128	733	1255	7	1315	1302	-13	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.
60	2015	1	1	4	AA	334	N494AA	DFW	MFE	915	[null]	[null]	[null]	[null]	95	[null]	[null]	469	[null]	[null]	1050	[null]	[null]	1	B	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.
61	2015	1	1	4	AA	338	N3DJAA	LAX	SFO	650	644	-6	21	705	80	79	55	337	800	3	810	803	-7	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.
62	2015	1	1	4	AA	348	N3ENAA	FLL	DFW	1650	1651	1	17	1708	200	217	163	1119	1851	37	1910	1928	18	0	[null]	18	0	0	0	0	American Airlines Inc.
63	2015	1	1	4	AA	350	N3MBAA	ORD	LGA	1735	1728	-7	13	1741	120	111	93	733	2014	5	2035	2019	-16	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.
64	2015	1	1	4	AA	362	N582AA	DFW	MKE	2000	2027	27	10	2037	135	110	96	853	2213	4	2215	2217	2	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.
65	2015	1	1	4	AA	365	N3JPAA	DCA	DFW	1005	1029	24	11	1040	210	223	193	1192	1253	19	1235	1312	37	0	[null]	13	0	24	0	0	American Airlines Inc.
66	2015	1	1	4	AA	374	N3ARAA	ORD	LGA	1225	1221	-4	13	1234	120	106	91	733	1505	2	1525	1507	-18	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.
67	2015	1	1	4	AA	379	N5CEAA	DFW	SFO	1315	1338	23	22	1400	225	211	185	1464	1505	4	1500	1509	9	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.
68	2015	1	1	4	AA	380	N3HVAA	ORD	LGA	1040	1053	13	11	1104	120	98	84	733	1328	3	1340	1331	-9	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.
69	2015	1	1	4	AA	384	N3EEAA	ORD	LGA	930	934	4	10	944	120	100	87	733	1211	3	1230	1214	-16	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.
70	2015	1	1	4	AA	385	N403AA	BNA	DFW	825	817	-8	9	826	135	118	105	631	1011	4	1040	1015	-25	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.
71	2015	1	1	4	AA	386	N434AA	JAX	DFW	1825	2026	121	13	2039	170	159	136	918	2155	10	2015	2205	110	0	[null]	0	0	0	110	0	American Airlines Inc.
72	2015	1	1	4	AA	395	N3MCAA	ORD	SJU	830	1409	339	14	1423	270	241	223	2072	2006	4	1500	2010	310	0	[null]	0	0	75	235	0	American Airlines Inc.
73	2015	1	1	4	AA	397	N3CGAA	MIA	SJU	925	921	-4	16	937	155	148	122	1045	1239	10	1300	1249	-11	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.
74	2015	1	1	4	AA	397	N3CGAA	SJU	MIA	1401	1356	-5	11	1407	174	181	157	1045	1544	13	1555	1557	2	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.
75	2015	1	1	4	AA	942	N491AA	DFW	TPA	1705	2013	188	11	2024	135	129	113	929	2317	5	2020	2322	182	0	[null]	0	0	23	159	0	American Airlines Inc.
76	2015	1	1	4	AA	959	N635AA	MIA	DFW	1245	1244	-1	11	1255	198	217	168	1121	1443	38	1503	1521	18	0	[null]	18	0	0	0	0	American Airlines Inc.
77	2015	1	1	4	AA	976	N4XLAA	IAH	DFW	1335	1339	4	12	1351	70	62	41	224	1432	9	1445	1441	-4	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.
78	2015	1	1	4	AA	978	N549AA	DFW	COS	1555	1618	23	16	1634	115	111	86	592	1700	9	1650	1709	19	0	[null]	0	0	0	17	2	American Airlines Inc.
79	2015	1	1	4	AA	986	N5ENAA	MIA	STT	1115	1110	-5	14	1124	163	147	129	1107	1433	4	1458	1437	-21	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.
80	2015	1	1	4	AA	989	N854AA	LAS	MIA	1500	1638	98	36	1714	270	286	248	2174	22	2	2230	24	114	0	[null]	16	0	0	98	0	American Airlines Inc.
81	2015	1	1	4	AA	1015	N4XRAA	DFW	SAN	1535	1614	39	23	1637	195	189	162	1171	1719	4	1650	1723	33	0	[null]	0	0	0	33	0	American Airlines Inc.
82	2015	1	1	4	AA	1015	N4XRAA	SAN	DFW	1730	1750	20	15	1805	170	167	144	1171	2229	8	2220	2237	17	0	[null]	0	0	0	17	0	American Airlines Inc.
83	2015	1	1	4	AA	1020	N483AA	MEM	DFW	1825	1835	10	10	1845	105	99	78	432	2003	11	2010	2014	4	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.
84	2015	1	1	4	AA	1042	N3ANAA	SAN	MIA	2230	2226	-4	23	2249	279	287	255	2267	604	9	609	613	4	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.
85	2015	1	1	4	AA	1046	N505AA	DFW	MCI	900	911	11	15	926	85	82	61	460	1027	6	1025	1033	8	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.
86	2015	1	1	4	AA	1057	N3ASAA	DFW	MIA	515	703	108	15	718	161	155	133	1121	1031	7	856	1038	102	0	[null]	0	0	0	0	102	American Airlines Inc.
87	2015	1	1	4	AA	1059	N3BEAA	MIA	MSY	2000	1958	-2	28	2026	131	146	115	675	2121	3	2111	2124	13	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.
88	2015	1	1	4	AA	1071	N4YTAA	DFW	HOU	1250	1249	-1	13	1302	65	65	49	247	1351	3	1355	1354	-1	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.
89	2015	1	1	4	AA	1084	N3GFAA	LGA	MIA	945	939	-6	14	953	195	193	150	1096	1223	29	1300	1252	-8	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.
90	2015	1	1	4	AA	1085	N635AA	JFK	MIA	830	841	11	23	904	204	181	152	1089	1136	6	1154	1142	-12	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.
91	2015	1	1	4	AA	1086	N486AA	DFW	SLC	940	936	-4	22	958	170	165	135	989	1113	8	1130	1121	-9	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.
92	2015	1	1	4	AA	1104	N3LAAA	LGA	DFW	1150	1148	-2	10	1158	250	242	221	1389	1439	11	1500	1450	-10	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.
93	2015	1	1	4	AA	1110	N3CKAA	LGA	DFW	1329	1323	-6	15	1338	250	259	229	1389	1627	15	1639	1642	3	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.
94	2015	1	1	4	AA	1112	N3LAAA	SFO	DFW	30	19	-11	17	36	195	193	173	1464	529	3	545	532	-13	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.
95	2015	1	1	4	AA	1115	N486AA	DFW	MFE	1640	1656	16	12	1708	90	97	82	469	1830	3	1810	1833	23	0	[null]	7	0	0	16	0	American Airlines Inc.
96	2015	1	1	4	AA	1116	N4XNAA	MSP	ORD	1955	2233	158	10	2243	90	70	53	334	2336	7	2125	2343	138	0	[null]	0	0	0	138	0	American Airlines Inc.
97	2015	1	1	4	AA	1121	N3GUAA	LGA	DFW	815	841	26	14	855	255	254	228	1389	1143	12	1130	1155	25	0	[null]	0	0	25	0	0	American Airlines Inc.
98	2015	1	1	4	AA	1133	N3FJAA	LGA	MIA	1055	1054	-1	12	1106	195	165	149	1096	1335	4	1410	1339	-31	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.
99	2015	1	1	4	AA	1133	N3FJAA	MIA	LGA	715	713	-2	16	729	175	176	150	1096	959	10	1010	1009	-1	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.
100	2015	1	1	4	AA	1140	N3GUAA	LGA	DFW	1730	1731	1	13	1744	250	260	217	1389	2021	30	2040	2051	11	0	[null]	[null]	[null]	[null]	[null]	[null]	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 month Integer 100%	123 day Integer 100%	123 day_of_week Integer 100%	Abc airline Varchar(50) 100%	123 flight_number Integer 100%	Abc tail_number Varchar(50) 99%	Abc origin_airport Varchar(50) 100%	Abc destination_airport Varchar(50) 100%	123 scheduled_departure Integer 100%	123 departure_time Integer 97%	123 departure_delay Integer 97%	123 taxi_out Integer 97%	123 wheels_off Integer 97%	123 scheduled_time Integer 100%	123 elapsed_time Integer 96%	123 air_time Integer 96%	123 distance Integer 100%	123 wheels_on Integer 97%	123 taxi_in Integer 97%	123 scheduled_arrival Integer 100%	123 arrival_time Integer 97%	123 arrival_delay Integer 96%	123 cancelled Integer 100%	Abc cancellation_reason Varchar(50) 2%	123 air_system_delay Integer 32%	123 security_delay Integer 32%	123 airline_delay Integer 32%	123 late_aircraft_delay Integer 32%	123 weather_delay Integer 32%	Abc airline_long Varchar(50) 100%	123 origin_lat Real 99%	123 origin_lon Real 99%	123 destination_lat Real 99%	123 destination_lon Real 99%
1	2015	1	1	4	AA	5	N376AA	DFW	HNL	1305	1754	289	21	1815	515	526	499	3784	2234	6	1740	2240	300	0	[null]	11	0	197	92	0	American Airlines Inc.	32.89595	-97.0372	21.31869	-157.92241
2	2015	1	1	4	AA	7	N398AA	DFW	OGG	1215	1513	178	24	1537	500	517	490	3711	1947	3	1635	1950	195	0	[null]	17	0	178	0	0	American Airlines Inc.	32.89595	-97.0372	20.89865	-156.43047
3	2015	1	1	4	AA	20	N789AA	SFO	JFK	1500	1503	3	19	1522	325	328	301	2586	2323	8	2325	2331	6	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.	37.619	-122.37484	40.63975	-73.77893
4	2015	1	1	4	AA	27	N5DUAA	DFW	LAS	1850	1929	39	16	1945	175	165	141	1055	2006	8	1945	2014	29	0	[null]	0	0	29	0	0	American Airlines Inc.	32.89595	-97.0372	36.08036	-115.15233
5	2015	1	1	4	AA	43	N3KCAA	DTW	DFW	1445	1445	0	19	1504	180	199	163	986	1647	17	1645	1704	19	0	[null]	19	0	0	0	0	American Airlines Inc.	42.21206	-83.34884	32.89595	-97.0372
6	2015	1	1	4	AA	61	N850AA	MIA	DFW	715	719	4	13	732	199	192	167	1121	919	12	934	931	-3	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.	25.79325	-80.29056	32.89595	-97.0372
7	2015	1	1	4	AA	63	N3ECAA	MIA	PHX	1950	1955	5	23	2018	317	302	271	1972	2249	8	2307	2257	-10	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.	25.79325	-80.29056	33.43417	-112.00806
8	2015	1	1	4	AA	65	N3HLAA	JFK	DFW	1615	1625	10	19	1644	255	294	227	1391	1931	48	1930	2019	49	0	[null]	39	0	10	0	0	American Airlines Inc.	40.63975	-73.77893	32.89595	-97.0372
9	2015	1	1	4	AA	72	N5EKAA	MCO	DFW	1020	1015	-5	16	1031	175	171	143	985	1154	12	1215	1206	-9	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.	28.42889	-81.31603	32.89595	-97.0372
10	2015	1	1	4	AA	74	N505AA	DFW	CLE	1145	[null]	[null]	[null]	[null]	150	[null]	[null]	1021	[null]	[null]	1515	[null]	[null]	1	B	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.	32.89595	-97.0372	41.41089	-81.8494
11	2015	1	1	4	AA	76	N3HPAA	LAX	IAD	950	946	-4	18	1004	285	265	243	2288	1707	4	1735	1711	-24	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.	33.94254	-118.40807	38.94453	-77.45581
12	2015	1	1	4	AA	81	N4XCAA	DFW	TUS	2245	2308	23	13	2321	140	140	123	813	24	4	5	28	23	0	[null]	0	0	0	23	0	American Airlines Inc.	32.89595	-97.0372	32.11608	-110.94103
13	2015	1	1	4	AA	94	N3ANAA	SAN	JFK	755	810	15	13	823	320	281	262	2446	1545	6	1615	1551	-24	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.	32.73356	-117.18966	40.63975	-73.77893
14	2015	1	1	4	AA	95	N3ANAA	JFK	SAN	1720	1749	29	18	1807	380	386	363	2446	2110	5	2040	2115	35	0	[null]	6	0	29	0	0	American Airlines Inc.	40.63975	-73.77893	32.73356	-117.18966
15	2015	1	1	4	AA	103	N4XSAA	DFW	MSY	1410	1433	23	13	1446	80	80	62	447	1548	5	1530	1553	23	0	[null]	0	0	23	0	0	American Airlines Inc.	32.89595	-97.0372	29.99339	-90.25803
16	2015	1	1	4	AA	110	N3LRAA	SNA	ORD	805	801	-4	7	808	230	211	194	1726	1322	10	1355	1332	-23	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.	33.67566	-117.86822	41.9796	-87.90446
17	2015	1	1	4	AA	111	N3LYAA	ORD	SNA	1720	1719	-1	11	1730	260	237	222	1726	1912	4	1940	1916	-24	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.	41.9796	-87.90446	33.67566	-117.86822
18	2015	1	1	4	AA	118	N788AA	LAX	JFK	700	650	-10	13	703	325	286	268	2475	1431	5	1525	1436	-49	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.	33.94254	-118.40807	40.63975	-73.77893
19	2015	1	1	4	AA	126	N4XGAA	PHX	DFW	1410	1508	58	21	1529	140	137	107	868	1816	9	1730	1825	55	0	[null]	0	0	8	47	0	American Airlines Inc.	33.43417	-112.00806	32.89595	-97.0372
20	2015	1	1	4	AA	127	N3GHAA	IAD	DFW	625	622	-3	14	636	210	230	200	1172	856	16	855	912	17	0	[null]	17	0	0	0	0	American Airlines Inc.	38.94453	-77.45581	32.89595	-97.0372
21	2015	1	1	4	AA	132	N3DUAA	DFW	PHL	1335	1337	2	19	1356	180	184	160	1303	1736	5	1735	1741	6	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.	32.89595	-97.0372	39.87195	-75.24114
22	2015	1	1	4	AA	134	N3HBAA	DCA	DFW	1745	1733	-12	12	1745	215	217	196	1192	2001	9	2020	2010	-10	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.	38.85208	-77.03772	32.89595	-97.0372
23	2015	1	1	4	AA	138	N3CLAA	DFW	DCA	1655	1712	17	12	1724	160	141	125	1192	2029	4	2035	2033	-2	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.	32.89595	-97.0372	38.85208	-77.03772
24	2015	1	1	4	AA	142	N5EDAA	HNL	LAX	2315	2314	-1	18	2332	325	303	278	2556	610	7	640	617	-23	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.	21.31869	-157.92241	33.94254	-118.40807
25	2015	1	1	4	AA	148	N3EJAA	MIA	CLT	630	624	-6	15	639	125	120	96	650	815	9	835	824	-11	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.	25.79325	-80.29056	35.21401	-80.94313
26	2015	1	1	4	AA	156	N590AA	LAX	STL	1150	1146	-4	23	1209	215	206	181	1592	1710	2	1725	1712	-13	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.	33.94254	-118.40807	38.74769	-90.35999
27	2015	1	1	4	AA	160	N3KNAA	BOS	ORD	1005	957	-8	13	1010	180	174	153	867	1143	8	1205	1151	-14	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.	42.36435	-71.00518	41.9796	-87.90446
28	2015	1	1	4	AA	162	N5ESAA	HNL	LAX	1340	1350	10	20	1410	330	318	283	2556	2053	15	2110	2108	-2	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.	21.31869	-157.92241	33.94254	-118.40807
29	2015	1	1	4	AA	165	N3JSAA	BOS	LAX	1610	1617	7	12	1629	395	407	384	2611	1953	11	1945	2004	19	0	[null]	12	0	7	0	0	American Airlines Inc.	42.36435	-71.00518	33.94254	-118.40807
30	2015	1	1	4	AA	172	N791AA	LAX	JFK	1015	1007	-8	12	1019	330	298	275	2475	1754	11	1845	1805	-40	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.	33.94254	-118.40807	40.63975	-73.77893
31	2015	1	1	4	AA	176	N3EPAA	DFW	DCA	1020	1022	2	29	1051	160	163	127	1192	1358	7	1400	1405	5	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.	32.89595	-97.0372	38.85208	-77.03772
32	2015	1	1	4	AA	178	N3CCAA	DCA	DFW	1600	1646	46	12	1658	215	227	204	1192	1922	11	1835	1933	58	0	[null]	12	0	0	46	0	American Airlines Inc.	38.85208	-77.03772	32.89595	-97.0372
33	2015	1	1	4	AA	180	N798AA	LAX	JFK	1645	1645	0	14	1659	315	293	270	2475	29	9	100	38	-22	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.	33.94254	-118.40807	40.63975	-73.77893
34	2015	1	1	4	AA	181	N796AA	JFK	LAX	1730	1724	-6	23	1747	385	395	366	2475	2053	6	2055	2059	4	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.	40.63975	-73.77893	33.94254	-118.40807
35	2015	1	1	4	AA	184	N3LCAA	DFW	SFO	1025	[null]	[null]	[null]	[null]	225	[null]	[null]	1464	[null]	[null]	1210	[null]	[null]	1	B	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.	32.89595	-97.0372	37.619	-122.37484
36	2015	1	1	4	AA	189	N570AA	SJC	DFW	1515	1553	38	11	1604	205	212	188	1438	2112	13	2040	2125	45	0	[null]	7	0	3	35	0	American Airlines Inc.	37.36186	-121.92901	32.89595	-97.0372
37	2015	1	1	4	AA	194	N025AA	ATL	DFW	1525	1534	9	14	1548	145	149	123	731	1651	12	1650	1703	13	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.	33.64044	-84.42694	32.89595	-97.0372
38	2015	1	1	4	AA	198	N3ELAA	ORD	JFK	1255	1252	-3	12	1304	130	112	95	740	1539	5	1605	1544	-21	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.	41.9796	-87.90446	40.63975	-73.77893
39	2015	1	1	4	AA	209	N3LRAA	SFO	LAX	1805	1919	74	15	1934	80	80	52	337	2026	13	1925	2039	74	0	[null]	0	0	0	74	0	American Airlines Inc.	37.619	-122.37484	33.94254	-118.40807
40	2015	1	1	4	AA	219	N3KYAA	SFO	LAX	2010	2005	-5	13	2018	90	77	52	337	2110	12	2140	2122	-18	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.	37.619	-122.37484	33.94254	-118.40807
41	2015	1	1	4	AA	231	N3FNAA	LAS	ORD	1635	1632	-3	16	1648	210	206	181	1514	2149	9	2205	2158	-7	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.	36.08036	-115.15233	41.9796	-87.90446
42	2015	1	1	4	AA	234	N3EXAA	MIA	BWI	2135	2127	-8	21	2148	155	148	123	946	2351	4	10	2355	-15	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.	25.79325	-80.29056	39.1754	-76.6682
43	2015	1	1	4	AA	241	N3LRAA	ORD	SFO	1610	1606	-4	19	1625	280	264	242	1846	1827	3	1850	1830	-20	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.	41.9796	-87.90446	37.619	-122.37484
44	2015	1	1	4	AA	253	N378AA	LAX	OGG	900	856	-4	19	915	355	353	332	2486	1247	2	1255	1249	-6	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.	33.94254	-118.40807	20.89865	-156.43047
45	2015	1	1	4	AA	262	N3MDAA	RSW	DFW	1335	1329	-6	10	1339	180	173	152	1017	1511	11	1535	1522	-13	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.	26.53617	-81.75517	32.89595	-97.0372
46	2015	1	1	4	AA	263	N633AA	MCO	LAX	1635	1628	-7	15	1643	335	310	287	2218	1830	8	1910	1838	-32	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.	28.42889	-81.31603	33.94254	-118.40807
47	2015	1	1	4	AA	275	N634AA	MCO	MIA	830	828	-2	11	839	67	57	39	192	918	7	937	925	-12	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.	28.42889	-81.31603	25.79325	-80.29056
48	2015	1	1	4	AA	294	N4YDAA	DFW	ORF	1240	1255	15	14	1309	165	153	136	1212	1625	3	1625	1628	3	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.	32.89595	-97.0372	36.89461	-76.20122
49	2015	1	1	4	AA	294	N4YDAA	ORF	DFW	1710	1705	-5	17	1722	215	232	204	1212	1946	11	1945	1957	12	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.	36.89461	-76.20122	32.89595	-97.0372
50	2015	1	1	4	AA	298	N5DGAA	HNL	LAX	2155	2159	4	20	2219	320	313	283	2556	502	10	515	512	-3	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.	21.31869	-157.92241	33.94254	-118.40807
51	2015	1	1	4	AA	309	N4YDAA	DFW	BNA	700	652	-8	31	723	100	112	74	631	837	7	840	844	4	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.	32.89595	-97.0372	36.12448	-86.67818
52	2015	1	1	4	AA	310	N201AA	MFE	DFW	605	[null]	[null]	[null]	[null]	95	[null]	[null]	469	[null]	[null]	740	[null]	[null]	1	B	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.	26.17583	-98.23861	32.89595	-97.0372
53	2015	1	1	4	AA	315	N3MAAA	LGA	ORD	700	658	-2	16	714	165	165	136	733	830	13	845	843	-2	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.	40.77724	-73.87261	41.9796	-87.90446
54	2015	1	1	4	AA	316	N557AA	DFW	DEN	1140	1146	6	29	1215	120	120	87	641	1242	4	1240	1246	6	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.	32.89595	-97.0372	39.85841	-104.667
55	2015	1	1	4	AA	320	N4XPAA	EWR	DFW	1445	1450	5	16	1506	245	251	222	1372	1748	13	1750	1801	11	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.	40.6925	-74.16866	32.89595	-97.0372
56	2015	1	1	4	AA	323	N520AA	DFW	STL	645	[null]	[null]	[null]	[null]	95	[null]	[null]	550	[null]	[null]	820	[null]	[null]	1	B	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.	32.89595	-97.0372	38.74769	-90.35999
57	2015	1	1	4	AA	325	N3LWAA	LGA	ORD	945	933	-12	23	956	160	168	136	733	1112	9	1125	1121	-4	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.	40.77724	-73.87261	41.9796	-87.90446
58	2015	1	1	4	AA	326	N486AA	STL	DFW	1205	[null]	[null]	[null]	[null]	115	[null]	[null]	550	[null]	[null]	1400	[null]	[null]	1	A	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.	38.74769	-90.35999	32.89595	-97.0372
59	2015	1	1	4	AA	329	N3LGAA	LGA	ORD	1130	1126	-4	21	1147	165	156	128	733	1255	7	1315	1302	-13	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.	40.77724	-73.87261	41.9796	-87.90446
60	2015	1	1	4	AA	334	N494AA	DFW	MFE	915	[null]	[null]	[null]	[null]	95	[null]	[null]	469	[null]	[null]	1050	[null]	[null]	1	B	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.	32.89595	-97.0372	26.17583	-98.23861
61	2015	1	1	4	AA	338	N3DJAA	LAX	SFO	650	644	-6	21	705	80	79	55	337	800	3	810	803	-7	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.	33.94254	-118.40807	37.619	-122.37484
62	2015	1	1	4	AA	348	N3ENAA	FLL	DFW	1650	1651	1	17	1708	200	217	163	1119	1851	37	1910	1928	18	0	[null]	18	0	0	0	0	American Airlines Inc.	26.07258	-80.15275	32.89595	-97.0372
63	2015	1	1	4	AA	350	N3MBAA	ORD	LGA	1735	1728	-7	13	1741	120	111	93	733	2014	5	2035	2019	-16	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.	41.9796	-87.90446	40.77724	-73.87261
64	2015	1	1	4	AA	362	N582AA	DFW	MKE	2000	2027	27	10	2037	135	110	96	853	2213	4	2215	2217	2	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.	32.89595	-97.0372	42.94722	-87.89658
65	2015	1	1	4	AA	365	N3JPAA	DCA	DFW	1005	1029	24	11	1040	210	223	193	1192	1253	19	1235	1312	37	0	[null]	13	0	24	0	0	American Airlines Inc.	38.85208	-77.03772	32.89595	-97.0372
66	2015	1	1	4	AA	374	N3ARAA	ORD	LGA	1225	1221	-4	13	1234	120	106	91	733	1505	2	1525	1507	-18	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.	41.9796	-87.90446	40.77724	-73.87261
67	2015	1	1	4	AA	379	N5CEAA	DFW	SFO	1315	1338	23	22	1400	225	211	185	1464	1505	4	1500	1509	9	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.	32.89595	-97.0372	37.619	-122.37484
68	2015	1	1	4	AA	380	N3HVAA	ORD	LGA	1040	1053	13	11	1104	120	98	84	733	1328	3	1340	1331	-9	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.	41.9796	-87.90446	40.77724	-73.87261
69	2015	1	1	4	AA	384	N3EEAA	ORD	LGA	930	934	4	10	944	120	100	87	733	1211	3	1230	1214	-16	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.	41.9796	-87.90446	40.77724	-73.87261
70	2015	1	1	4	AA	385	N403AA	BNA	DFW	825	817	-8	9	826	135	118	105	631	1011	4	1040	1015	-25	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.	36.12448	-86.67818	32.89595	-97.0372
71	2015	1	1	4	AA	386	N434AA	JAX	DFW	1825	2026	121	13	2039	170	159	136	918	2155	10	2015	2205	110	0	[null]	0	0	0	110	0	American Airlines Inc.	30.49406	-81.68786	32.89595	-97.0372
72	2015	1	1	4	AA	395	N3MCAA	ORD	SJU	830	1409	339	14	1423	270	241	223	2072	2006	4	1500	2010	310	0	[null]	0	0	75	235	0	American Airlines Inc.	41.9796	-87.90446	18.43942	-66.00183
73	2015	1	1	4	AA	397	N3CGAA	MIA	SJU	925	921	-4	16	937	155	148	122	1045	1239	10	1300	1249	-11	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.	25.79325	-80.29056	18.43942	-66.00183
74	2015	1	1	4	AA	397	N3CGAA	SJU	MIA	1401	1356	-5	11	1407	174	181	157	1045	1544	13	1555	1557	2	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.	18.43942	-66.00183	25.79325	-80.29056
75	2015	1	1	4	AA	942	N491AA	DFW	TPA	1705	2013	188	11	2024	135	129	113	929	2317	5	2020	2322	182	0	[null]	0	0	23	159	0	American Airlines Inc.	32.89595	-97.0372	27.97547	-82.53325
76	2015	1	1	4	AA	959	N635AA	MIA	DFW	1245	1244	-1	11	1255	198	217	168	1121	1443	38	1503	1521	18	0	[null]	18	0	0	0	0	American Airlines Inc.	25.79325	-80.29056	32.89595	-97.0372
77	2015	1	1	4	AA	976	N4XLAA	IAH	DFW	1335	1339	4	12	1351	70	62	41	224	1432	9	1445	1441	-4	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.	29.98047	-95.33972	32.89595	-97.0372
78	2015	1	1	4	AA	978	N549AA	DFW	COS	1555	1618	23	16	1634	115	111	86	592	1700	9	1650	1709	19	0	[null]	0	0	0	17	2	American Airlines Inc.	32.89595	-97.0372	38.80581	-104.70025
79	2015	1	1	4	AA	986	N5ENAA	MIA	STT	1115	1110	-5	14	1124	163	147	129	1107	1433	4	1458	1437	-21	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.	25.79325	-80.29056	18.33731	-64.97336
80	2015	1	1	4	AA	989	N854AA	LAS	MIA	1500	1638	98	36	1714	270	286	248	2174	22	2	2230	24	114	0	[null]	16	0	0	98	0	American Airlines Inc.	36.08036	-115.15233	25.79325	-80.29056
81	2015	1	1	4	AA	1015	N4XRAA	DFW	SAN	1535	1614	39	23	1637	195	189	162	1171	1719	4	1650	1723	33	0	[null]	0	0	0	33	0	American Airlines Inc.	32.89595	-97.0372	32.73356	-117.18966
82	2015	1	1	4	AA	1015	N4XRAA	SAN	DFW	1730	1750	20	15	1805	170	167	144	1171	2229	8	2220	2237	17	0	[null]	0	0	0	17	0	American Airlines Inc.	32.73356	-117.18966	32.89595	-97.0372
83	2015	1	1	4	AA	1020	N483AA	MEM	DFW	1825	1835	10	10	1845	105	99	78	432	2003	11	2010	2014	4	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.	35.04242	-89.97667	32.89595	-97.0372
84	2015	1	1	4	AA	1042	N3ANAA	SAN	MIA	2230	2226	-4	23	2249	279	287	255	2267	604	9	609	613	4	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.	32.73356	-117.18966	25.79325	-80.29056
85	2015	1	1	4	AA	1046	N505AA	DFW	MCI	900	911	11	15	926	85	82	61	460	1027	6	1025	1033	8	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.	32.89595	-97.0372	39.29761	-94.71391
86	2015	1	1	4	AA	1057	N3ASAA	DFW	MIA	515	703	108	15	718	161	155	133	1121	1031	7	856	1038	102	0	[null]	0	0	0	0	102	American Airlines Inc.	32.89595	-97.0372	25.79325	-80.29056
87	2015	1	1	4	AA	1059	N3BEAA	MIA	MSY	2000	1958	-2	28	2026	131	146	115	675	2121	3	2111	2124	13	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.	25.79325	-80.29056	29.99339	-90.25803
88	2015	1	1	4	AA	1071	N4YTAA	DFW	HOU	1250	1249	-1	13	1302	65	65	49	247	1351	3	1355	1354	-1	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.	32.89595	-97.0372	29.64542	-95.27889
89	2015	1	1	4	AA	1084	N3GFAA	LGA	MIA	945	939	-6	14	953	195	193	150	1096	1223	29	1300	1252	-8	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.	40.77724	-73.87261	25.79325	-80.29056
90	2015	1	1	4	AA	1085	N635AA	JFK	MIA	830	841	11	23	904	204	181	152	1089	1136	6	1154	1142	-12	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.	40.63975	-73.77893	25.79325	-80.29056
91	2015	1	1	4	AA	1086	N486AA	DFW	SLC	940	936	-4	22	958	170	165	135	989	1113	8	1130	1121	-9	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.	32.89595	-97.0372	40.78839	-111.97777
92	2015	1	1	4	AA	1104	N3LAAA	LGA	DFW	1150	1148	-2	10	1158	250	242	221	1389	1439	11	1500	1450	-10	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.	40.77724	-73.87261	32.89595	-97.0372
93	2015	1	1	4	AA	1110	N3CKAA	LGA	DFW	1329	1323	-6	15	1338	250	259	229	1389	1627	15	1639	1642	3	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.	40.77724	-73.87261	32.89595	-97.0372
94	2015	1	1	4	AA	1112	N3LAAA	SFO	DFW	30	19	-11	17	36	195	193	173	1464	529	3	545	532	-13	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.	37.619	-122.37484	32.89595	-97.0372
95	2015	1	1	4	AA	1115	N486AA	DFW	MFE	1640	1656	16	12	1708	90	97	82	469	1830	3	1810	1833	23	0	[null]	7	0	0	16	0	American Airlines Inc.	32.89595	-97.0372	26.17583	-98.23861
96	2015	1	1	4	AA	1116	N4XNAA	MSP	ORD	1955	2233	158	10	2243	90	70	53	334	2336	7	2125	2343	138	0	[null]	0	0	0	138	0	American Airlines Inc.	44.88055	-93.21692	41.9796	-87.90446
97	2015	1	1	4	AA	1121	N3GUAA	LGA	DFW	815	841	26	14	855	255	254	228	1389	1143	12	1130	1155	25	0	[null]	0	0	25	0	0	American Airlines Inc.	40.77724	-73.87261	32.89595	-97.0372
98	2015	1	1	4	AA	1133	N3FJAA	LGA	MIA	1055	1054	-1	12	1106	195	165	149	1096	1335	4	1410	1339	-31	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.	40.77724	-73.87261	25.79325	-80.29056
99	2015	1	1	4	AA	1133	N3FJAA	MIA	LGA	715	713	-2	16	729	175	176	150	1096	959	10	1010	1009	-1	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.	25.79325	-80.29056	40.77724	-73.87261
100	2015	1	1	4	AA	1140	N3GUAA	LGA	DFW	1730	1731	1	13	1744	250	260	217	1389	2021	30	2040	2051	11	0	[null]	[null]	[null]	[null]	[null]	[null]	American Airlines Inc.	40.77724	-73.87261	32.89595	-97.0372

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 vastorbit.sql.functions as fun

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

	123 distance Double
1	2341.900732035145
2	2791.4425092738484
3	1934.498599931156
4	1281.6240796204604
5	456.6649404787453
6	2191.658678030639
7	4041.187798092764
8	151.8135995780375
9	7235.601486240135
10	5569.081435580986
11	1850.6000474638338
12	994.9559515340186
13	5772.980671007461
14	269.7769747760563
15	1423.4865492636043
16	2371.545522727664
17	5345.719161217486
18	824.0219966535159
19	2669.973632953226
20	1113.4206575147346

vastorbit offers many powerful options for joining datasets.

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