vDataFrame.join¶

In [ ]:

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

Joins the vDataFrame with another vDataFrame or an input relation.

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

Parameters¶

Name	Type	Optional	Description
input_relation	str / vDataFrame	❌	Relation to use to do the merging.
on	list / dict	✓	If it is a list then: List of 3-tuples. Each tuple must include (key1, key2, operator) where key1 is the key of the vDataFrame, key2 is the key of the input relation, and operator can be one of the following: '=' : exact match '<' : key1 < key2 '>' : key1 > key2 '≤' : key1 ≤ key2 '≥' : key1 ≥ key2 'llike' : key1 LIKE '%' \|\| key2 \|\| '%' 'rlike' : key2 LIKE '%' \|\| key1 \|\| '%' 'linterpolate': key1 INTERPOLATE key2 'rinterpolate': key2 INTERPOLATE key1 Some operators need 5-tuples: (key1, key2, operator, operator2, x)—where operator2 is a simple operator (=, <, >, ≤, ≥), x is a float or an integer, and operator is one of the following: 'jaro' : JARO(key1, key2) operator2 x 'jarow' : JARO_WINCKLER(key1, key2) operator2 x 'lev' : LEVENSHTEIN(key1, key2) operator2 x If it is a dictionary then: This parameter must include all the different keys. It must be similar to the following: {"relationA_key1": "relationB_key1" ..., "relationA_keyk": "relationB_keyk"} where relationA is the current vDataFrame and relationB is the input relation or the input vDataFrame.
on_interpolate	dict	✓	A dictionary of all unique keys in the following format: {"relationA_key1": "relationB_key1" ..., "relationA_keyk": "relationB_keyk"} where relationA is the current vDataFrame and relationB is the input relation or the input vDataFrame. These keys are used in joins between two event series using some ordered attribute. Event series joins let you compare values from two series directly and does not require you to normalize the series to the same measurement interval
how	str	✓	Join Type. left : Left Join. right : Right Join. cross : Cross Join. full : Full Outer Join. natural : Natural Join. inner : Inner Join.
expr1	list	✓	List, in pure SQL, of the different columns to select from the current vDataFrame. Aliases are recommended to keep the track of the different features and to avoid ambigious names. Example: 'column' or 'column AS my_new_alias'.
expr2	list	✓	List, in pure SQL, of the different columns to select from the current vDataFrame. Aliases are recommended to keep the track of the different features and to avoid ambigious names. Example: 'column' or 'column AS my_new_alias'.

Returns¶

vDataFrame : object result of the join.

Example¶

In [1]:

from verticapy import *
flights = vDataFrame("public.flights")
airports = vDataFrame("public.airports")
airlines = vDataFrame("public.airlines")
display(flights)
display(airports)
display(airlines)

	123 YEAR Int	123 MONTH Int	123 DAY Int	123 DAY_OF_WEEK Int	Abc AIRLINE Varchar(20)	123 FLIGHT_NUMBER Int	Abc TAIL_NUMBER Varchar(20)	Abc ORIGIN_AIRPORT Varchar(20)	Abc DESTINATION_AIRPORT Varchar(20)	123 SCHEDULED_DEPARTURE Int	123 DEPARTURE_TIME Int	123 DEPARTURE_DELAY Int	123 TAXI_OUT Int	123 WHEELS_OFF Int	123 SCHEDULED_TIME Int	123 ELAPSED_TIME Int	123 AIR_TIME Int	123 DISTANCE Int	123 WHEELS_ON Int	123 TAXI_IN Int	123 SCHEDULED_ARRIVAL Int	123 ARRIVAL_TIME Int	123 ARRIVAL_DELAY Int	123 DIVERTED Int	123 CANCELLED Int	Abc CANCELLATION_REASON Varchar(20)	123 AIR_SYSTEM_DELAY Int	123 SECURITY_DELAY Int	123 AIRLINE_DELAY Int	123 LATE_AIRCRAFT_DELAY Int	123 WEATHER_DELAY Int
1	2015	3	11	3	AA	6	N388AA	OGG	DFW	1715	1710	-5	14	1724	424	400	382	3711	446	4	519	450	-29	0	0	[null]	[null]	[null]	[null]	[null]	[null]
2	2015	3	11	3	AA	8	N378AA	HNL	DFW	1645	1642	-3	24	1706	441	413	385	3784	431	4	506	435	-31	0	0	[null]	[null]	[null]	[null]	[null]	[null]
3	2015	3	11	3	AA	10	N793AA	LAX	JFK	2210	2202	-8	19	2221	321	317	290	2475	611	8	631	619	-12	0	0	[null]	[null]	[null]	[null]	[null]	[null]
4	2015	3	11	3	AA	14	N5ECAA	OGG	LAX	2159	2156	-3	11	2207	311	271	253	2486	520	7	610	527	-43	0	0	[null]	[null]	[null]	[null]	[null]	[null]
5	2015	3	11	3	AA	21	N794AA	JFK	LAX	1845	1839	-6	13	1852	391	350	334	2475	2126	3	2216	2129	-47	0	0	[null]	[null]	[null]	[null]	[null]	[null]
6	2015	3	11	3	AA	27	N5BRAA	DFW	LAS	1850	1852	2	20	1912	179	174	149	1055	1941	5	1949	1946	-3	0	0	[null]	[null]	[null]	[null]	[null]	[null]
7	2015	3	11	3	AA	30	N799AA	LAX	JFK	2325	2322	-3	21	2343	323	331	304	2475	747	6	748	753	5	0	0	[null]	[null]	[null]	[null]	[null]	[null]
8	2015	3	11	3	AA	37	N3JYAA	DFW	SAN	2100	2058	-2	24	2122	185	207	174	1171	2216	9	2205	2225	20	0	0	[null]	20	0	0	0	0
9	2015	3	11	3	AA	41	N3CNAA	ORD	SEA	2050	2116	26	19	2135	275	273	239	1721	2334	15	2325	2349	24	0	0	[null]	0	0	1	23	0
10	2015	3	11	3	AA	45	N3JDAA	JFK	LAS	1729	1727	-2	16	1743	356	330	308	2248	1951	6	2025	1957	-28	0	0	[null]	[null]	[null]	[null]	[null]	[null]
11	2015	3	11	3	AA	49	N630AA	DFW	SAN	1935	1940	5	18	1958	186	175	154	1171	2032	3	2041	2035	-6	0	0	[null]	[null]	[null]	[null]	[null]	[null]
12	2015	3	11	3	AA	51	N4YBAA	DFW	SLC	1725	1728	3	18	1746	171	172	145	989	1911	9	1916	1920	4	0	0	[null]	[null]	[null]	[null]	[null]	[null]
13	2015	3	11	3	AA	60	N380AA	DFW	MIA	1755	1809	14	18	1827	160	169	143	1121	2150	8	2135	2158	23	0	0	[null]	9	0	14	0	0
14	2015	3	11	3	AA	63	N3CKAA	MIA	PHX	1955	2012	17	20	2032	324	303	256	1972	2148	27	2219	2215	-4	0	0	[null]	[null]	[null]	[null]	[null]	[null]
15	2015	3	11	3	AA	65	N3DHAA	JFK	DFW	1615	1611	-4	19	1630	256	252	213	1391	1903	20	1931	1923	-8	0	0	[null]	[null]	[null]	[null]	[null]	[null]
16	2015	3	11	3	AA	69	N3GYAA	MIA	SFO	1840	1859	19	12	1911	393	357	341	2585	2152	4	2213	2156	-17	0	0	[null]	[null]	[null]	[null]	[null]	[null]
17	2015	3	11	3	AA	71	N3ACAA	DFW	SAN	1805	1807	2	14	1821	186	187	169	1171	1910	4	1911	1914	3	0	0	[null]	[null]	[null]	[null]	[null]	[null]
18	2015	3	11	3	AA	75	N3HGAA	IAD	LAX	1831	1844	13	13	1857	361	342	315	2288	2112	14	2132	2126	-6	0	0	[null]	[null]	[null]	[null]	[null]	[null]
19	2015	3	11	3	AA	81	N471AA	DFW	TUS	2245	2257	12	18	2315	142	133	110	813	2305	5	2307	2310	3	0	0	[null]	[null]	[null]	[null]	[null]	[null]
20	2015	3	11	3	AA	87	N491AA	ORD	RNO	1900	1857	-3	11	1908	267	238	223	1671	2051	4	2127	2055	-32	0	0	[null]	[null]	[null]	[null]	[null]	[null]
21	2015	3	11	3	AA	93	N3JKAA	ORD	SAN	1720	1749	29	20	1809	261	243	220	1723	1949	3	1941	1952	11	0	0	[null]	[null]	[null]	[null]	[null]	[null]
22	2015	3	11	3	AA	95	N3JHAA	JFK	SAN	1720	1718	-2	14	1732	380	346	329	2446	2001	3	2040	2004	-36	0	0	[null]	[null]	[null]	[null]	[null]	[null]
23	2015	3	11	3	AA	102	N353AA	HNL	DFW	1830	1836	6	27	1903	440	436	404	3784	647	5	650	652	2	0	0	[null]	[null]	[null]	[null]	[null]	[null]
24	2015	3	11	3	AA	103	N503AA	MSY	DFW	1615	1631	16	9	1640	103	74	63	447	1743	2	1758	1745	-13	0	0	[null]	[null]	[null]	[null]	[null]	[null]
25	2015	3	11	3	AA	111	N3LJAA	ORD	SNA	1720	1731	11	21	1752	263	243	218	1726	1930	4	1943	1934	-9	0	0	[null]	[null]	[null]	[null]	[null]	[null]
26	2015	3	11	3	AA	113	N3JWAA	MIA	DEN	2115	2105	-10	11	2116	275	241	224	1709	2300	6	2350	2306	-44	0	0	[null]	[null]	[null]	[null]	[null]	[null]
27	2015	3	11	3	AA	122	N864AA	DFW	MCO	1910	1908	-2	16	1924	140	159	137	985	2241	6	2230	2247	17	0	0	[null]	17	0	0	0	0
28	2015	3	11	3	AA	125	N528AA	ATL	DFW	1750	1744	-6	13	1757	153	134	105	731	1842	16	1923	1858	-25	0	0	[null]	[null]	[null]	[null]	[null]	[null]
29	2015	3	11	3	AA	128	N3EYAA	DFW	IAD	2020	2020	0	17	2037	153	158	134	1172	2351	7	2353	2358	5	0	0	[null]	[null]	[null]	[null]	[null]	[null]
30	2015	3	11	3	AA	132	N436AA	PHL	DFW	1835	1832	-3	42	1914	249	249	188	1303	2122	19	2144	2141	-3	0	0	[null]	[null]	[null]	[null]	[null]	[null]
31	2015	3	11	3	AA	133	N789AA	JFK	LAX	1629	1710	41	19	1729	402	367	339	2475	2008	9	2011	2017	6	0	0	[null]	[null]	[null]	[null]	[null]	[null]
32	2015	3	11	3	AA	134	N3BLAA	DCA	DFW	1745	1920	95	12	1932	216	200	179	1192	2131	9	2021	2140	79	0	0	[null]	0	0	49	30	0
33	2015	3	11	3	AA	138	N3FAAA	DFW	DCA	1655	1653	-2	13	1706	159	155	138	1192	2024	4	2034	2028	-6	0	0	[null]	[null]	[null]	[null]	[null]	[null]
34	2015	3	11	3	AA	142	N4XVAA	MKE	DFW	1810	1844	34	11	1855	164	145	119	853	2054	15	2054	2109	15	0	0	[null]	0	0	8	7	0
35	2015	3	11	3	AA	143	N3JVAA	MIA	LAX	1849	1847	-2	22	1909	369	326	294	2342	2103	10	2158	2113	-45	0	0	[null]	[null]	[null]	[null]	[null]	[null]
36	2015	3	11	3	AA	145	N3FWAA	MIA	SAN	2005	2000	-5	15	2015	345	295	277	2267	2152	3	2250	2155	-55	0	0	[null]	[null]	[null]	[null]	[null]	[null]
37	2015	3	11	3	AA	146	N3GWAA	MIA	BOS	2100	2100	0	12	2112	182	169	152	1258	2344	5	2	2349	-13	0	0	[null]	[null]	[null]	[null]	[null]	[null]
38	2015	3	11	3	AA	147	N528AA	DFW	SLC	2105	2105	0	19	2124	169	156	132	989	2236	5	2254	2241	-13	0	0	[null]	[null]	[null]	[null]	[null]	[null]
39	2015	3	11	3	AA	151	N3BWAA	DEN	ORD	1817	1810	-7	25	1835	138	149	116	888	2131	8	2135	2139	4	0	0	[null]	[null]	[null]	[null]	[null]	[null]
40	2015	3	11	3	AA	154	N3GSAA	ORD	MCO	1955	2006	11	20	2026	152	157	128	1005	2334	9	2327	2343	16	0	0	[null]	5	0	5	6	0
41	2015	3	11	3	AA	155	N3LAAA	LAX	ORD	1745	1754	9	28	1822	241	237	202	1744	2344	7	2346	2351	5	0	0	[null]	[null]	[null]	[null]	[null]	[null]
42	2015	3	11	3	AA	156	N3FSAA	ORD	SAN	2125	2149	24	8	2157	259	226	215	1723	2332	3	2344	2335	-9	0	0	[null]	[null]	[null]	[null]	[null]	[null]
43	2015	3	11	3	AA	161	N5ECAA	LAX	OGG	1730	1724	-6	17	1741	359	379	360	2486	2041	2	2029	2043	14	0	0	[null]	[null]	[null]	[null]	[null]	[null]
44	2015	3	11	3	AA	163	N797AA	JFK	SFO	1955	1951	-4	12	2003	400	372	356	2586	2259	4	2335	2303	-32	0	0	[null]	[null]	[null]	[null]	[null]	[null]
45	2015	3	11	3	AA	165	N3LKAA	BOS	LAX	1620	1628	8	23	1651	399	379	344	2611	1935	12	1959	1947	-12	0	0	[null]	[null]	[null]	[null]	[null]	[null]
46	2015	3	11	3	AA	180	N787AA	LAX	JFK	1655	1725	30	27	1752	322	351	314	2475	206	10	117	216	59	0	0	[null]	29	0	30	0	0
47	2015	3	11	3	AA	181	N799AA	JFK	LAX	1735	1725	-10	27	1752	400	409	346	2475	2038	36	2115	2114	-1	0	0	[null]	[null]	[null]	[null]	[null]	[null]
48	2015	3	11	3	AA	182	N3FRAA	LAX	LAS	1805	1805	0	20	1825	74	72	46	236	1911	6	1919	1917	-2	0	0	[null]	[null]	[null]	[null]	[null]	[null]
49	2015	3	11	3	AA	185	N788AA	JFK	LAX	2105	2059	-6	29	2128	400	385	328	2475	2356	28	45	24	-21	0	0	[null]	[null]	[null]	[null]	[null]	[null]
50	2015	3	11	3	AA	188	N3HEAA	RSW	DFW	1730	1730	0	14	1744	185	162	130	1017	1854	18	1935	1912	-23	0	0	[null]	[null]	[null]	[null]	[null]	[null]
51	2015	3	11	3	AA	192	N3HXAA	LAX	BOS	2145	2141	-4	16	2157	327	316	294	2611	551	6	612	557	-15	0	0	[null]	[null]	[null]	[null]	[null]	[null]
52	2015	3	11	3	AA	197	N3LWAA	SFO	ORD	1731	1738	7	17	1755	243	231	209	1846	2324	5	2334	2329	-5	0	0	[null]	[null]	[null]	[null]	[null]	[null]
53	2015	3	11	3	AA	199	N3BDAA	JFK	ORD	1710	1706	-4	12	1718	176	166	130	740	1828	24	1906	1852	-14	0	0	[null]	[null]	[null]	[null]	[null]	[null]
54	2015	3	11	3	AA	207	N3FFAA	MIA	LAS	2000	2014	14	23	2037	339	307	277	2174	2214	7	2239	2221	-18	0	0	[null]	[null]	[null]	[null]	[null]	[null]
55	2015	3	11	3	AA	209	N3GVAA	LAX	SFO	1625	1622	-3	19	1641	84	76	54	337	1735	3	1749	1738	-11	0	0	[null]	[null]	[null]	[null]	[null]	[null]
56	2015	3	11	3	AA	209	N3GVAA	SFO	LAX	1840	1833	-7	10	1843	91	75	54	337	1937	11	2011	1948	-23	0	0	[null]	[null]	[null]	[null]	[null]	[null]
57	2015	3	11	3	AA	219	N3ABAA	LAX	SFO	1845	1838	-7	23	1901	86	84	56	337	1957	5	2011	2002	-9	0	0	[null]	[null]	[null]	[null]	[null]	[null]
58	2015	3	11	3	AA	219	N3ABAA	SFO	LAX	2101	2053	-8	19	2112	92	101	54	337	2206	28	2233	2234	1	0	0	[null]	[null]	[null]	[null]	[null]	[null]
59	2015	3	11	3	AA	220	N862AA	MIA	TPA	1705	1812	67	17	1829	72	66	45	204	1914	4	1817	1918	61	0	0	[null]	0	0	29	32	0
60	2015	3	11	3	AA	220	N862AA	TPA	MIA	1911	1958	47	12	2010	66	55	35	204	2045	8	2017	2053	36	0	0	[null]	0	0	0	36	0
61	2015	3	11	3	AA	223	N3JRAA	BOS	LAX	2001	1953	-8	14	2007	401	376	349	2611	2256	13	2342	2309	-33	0	0	[null]	[null]	[null]	[null]	[null]	[null]
62	2015	3	11	3	AA	227	N3FGAA	DFW	MSP	2045	2047	2	14	2101	135	129	109	852	2250	6	2300	2256	-4	0	0	[null]	[null]	[null]	[null]	[null]	[null]
63	2015	3	11	3	AA	231	N3KMAA	LAS	ORD	1617	1614	-3	13	1627	203	206	186	1514	2133	7	2140	2140	0	0	0	[null]	[null]	[null]	[null]	[null]	[null]
64	2015	3	11	3	AA	232	N434AA	ATL	DFW	1909	1906	-3	14	1920	152	123	99	731	1959	10	2041	2009	-32	0	0	[null]	[null]	[null]	[null]	[null]	[null]
65	2015	3	11	3	AA	234	N3CXAA	MIA	BWI	2135	2130	-5	13	2143	154	137	121	946	2344	3	9	2347	-22	0	0	[null]	[null]	[null]	[null]	[null]	[null]
66	2015	3	11	3	AA	235	N3HWAA	JFK	SEA	1835	1829	-6	14	1843	384	379	353	2422	2136	12	2159	2148	-11	0	0	[null]	[null]	[null]	[null]	[null]	[null]
67	2015	3	11	3	AA	238	N3CEAA	DCA	MIA	1805	1804	-1	15	1819	167	164	139	919	2038	10	2052	2048	-4	0	0	[null]	[null]	[null]	[null]	[null]	[null]
68	2015	3	11	3	AA	245	N3LFAA	DCA	LAX	1715	1725	10	14	1739	362	345	311	2311	1950	20	2017	2010	-7	0	0	[null]	[null]	[null]	[null]	[null]	[null]
69	2015	3	11	3	AA	246	N5FCAA	KOA	LAX	2130	2130	0	15	2145	310	[null]	[null]	2504	[null]	[null]	540	[null]	[null]	1	0	[null]	[null]	[null]	[null]	[null]	[null]
70	2015	3	11	3	AA	247	N5FCAA	LAX	KOA	1700	1658	-2	16	1714	360	375	355	2504	2009	4	2000	2013	13	0	0	[null]	[null]	[null]	[null]	[null]	[null]
71	2015	3	11	3	AA	263	N5DJAA	MCO	LAX	1630	1626	-4	13	1639	344	312	290	2218	1829	9	1914	1838	-36	0	0	[null]	[null]	[null]	[null]	[null]	[null]
72	2015	3	11	3	AA	267	N5ESAA	LAX	HNL	1830	1821	-9	18	1839	372	409	387	2556	2206	4	2142	2210	28	0	0	[null]	28	0	0	0	0
73	2015	3	11	3	AA	268	N597AA	DFW	SJC	2110	2127	17	22	2149	229	220	195	1438	2304	3	2259	2307	8	0	0	[null]	[null]	[null]	[null]	[null]	[null]
74	2015	3	11	3	AA	272	N3ADAA	SFO	MIA	2350	2346	-4	16	2	310	330	308	2585	810	6	800	816	16	0	0	[null]	16	0	0	0	0
75	2015	3	11	3	AA	280	N383AA	DFW	MIA	1855	1859	4	18	1917	157	169	143	1121	2240	8	2232	2248	16	0	0	[null]	16	0	0	0	0
76	2015	3	11	3	AA	288	N3BDAA	ORD	BOS	2005	2004	-1	16	2020	129	127	102	867	2302	9	2314	2311	-3	0	0	[null]	[null]	[null]	[null]	[null]	[null]
77	2015	3	11	3	AA	291	N3FDAA	JFK	AUS	1700	1658	-2	16	1714	265	244	223	1521	1957	5	2025	2002	-23	0	0	[null]	[null]	[null]	[null]	[null]	[null]
78	2015	3	11	3	AA	293	N791AA	JFK	LAX	1945	1944	-1	17	2001	398	372	324	2475	2225	31	2323	2256	-27	0	0	[null]	[null]	[null]	[null]	[null]	[null]
79	2015	3	11	3	AA	294	N466AA	ORF	DFW	1708	1702	-6	18	1720	222	225	184	1212	1924	23	1950	1947	-3	0	0	[null]	[null]	[null]	[null]	[null]	[null]
80	2015	3	11	3	AA	296	N564AA	DFW	SAT	1720	1920	120	12	1932	66	64	45	247	2017	7	1826	2024	118	0	0	[null]	0	0	19	99	0
81	2015	3	11	3	AA	296	N564AA	SAT	DFW	1906	2059	113	11	2110	69	60	41	247	2151	8	2015	2159	104	0	0	[null]	0	0	0	104	0
82	2015	3	11	3	AA	297	N5DEAA	LAX	HNL	1626	1621	-5	25	1646	371	423	391	2556	2017	7	1937	2024	47	0	0	[null]	47	0	0	0	0
83	2015	3	11	3	AA	298	N5DEAA	HNL	LAX	2107	2145	38	15	2200	326	286	265	2556	525	6	533	531	-2	0	0	[null]	[null]	[null]	[null]	[null]	[null]
84	2015	3	11	3	AA	301	N4XEAA	DFW	TUS	2005	2028	23	17	2045	145	138	116	813	2041	5	2030	2046	16	0	0	[null]	0	0	14	2	0
85	2015	3	11	3	AA	312	N5FLAA	MIA	JFK	1950	1958	8	18	2016	173	156	131	1089	2227	7	2243	2234	-9	0	0	[null]	[null]	[null]	[null]	[null]	[null]
86	2015	3	11	3	AA	335	N560AA	ORD	TUS	1959	2031	32	11	2042	234	200	177	1437	2139	12	2153	2151	-2	0	0	[null]	[null]	[null]	[null]	[null]	[null]
87	2015	3	11	3	AA	340	N489AA	DFW	HOU	2150	2209	19	9	2218	63	62	50	247	2308	3	2253	2311	18	0	0	[null]	0	0	18	0	0
88	2015	3	11	3	AA	341	N4XEAA	AUS	DFW	1744	1818	34	14	1832	66	65	37	190	1909	14	1850	1923	33	0	0	[null]	0	0	0	33	0
89	2015	3	11	3	AA	342	N3FXAA	MIA	LGA	1700	1656	-4	21	1717	182	162	137	1096	1934	4	2002	1938	-24	0	0	[null]	[null]	[null]	[null]	[null]	[null]
90	2015	3	11	3	AA	344	N3FCAA	DEN	MIA	1715	1710	-5	13	1723	226	225	205	1709	2248	7	2301	2255	-6	0	0	[null]	[null]	[null]	[null]	[null]	[null]
91	2015	3	11	3	AA	345	N3LTAA	LGA	ORD	1709	1716	7	23	1739	171	163	127	733	1846	13	1900	1859	-1	0	0	[null]	[null]	[null]	[null]	[null]	[null]
92	2015	3	11	3	AA	346	N3LTAA	ORD	LGA	1955	1951	-4	11	2002	120	111	90	733	2232	10	2255	2242	-13	0	0	[null]	[null]	[null]	[null]	[null]	[null]
93	2015	3	11	3	AA	348	N3HYAA	MIA	MSY	2005	2002	-3	23	2025	132	129	103	675	2108	3	2117	2111	-6	0	0	[null]	[null]	[null]	[null]	[null]	[null]
94	2015	3	11	3	AA	349	N028AA	MIA	ATL	1815	1807	-8	15	1822	124	113	85	594	1947	13	2019	2000	-19	0	0	[null]	[null]	[null]	[null]	[null]	[null]
95	2015	3	11	3	AA	350	N3CJAA	ORD	LGA	1750	1856	66	13	1909	124	106	88	733	2137	5	2054	2142	48	0	0	[null]	0	0	36	12	0
96	2015	3	11	3	AA	351	N3JYAA	IAH	DFW	1710	1749	39	17	1806	73	70	40	224	1846	13	1823	1859	36	0	0	[null]	0	0	2	34	0
97	2015	3	11	3	AA	352	N3MBAA	ORD	LGA	1845	1838	-7	21	1859	122	118	89	733	2128	8	2147	2136	-11	0	0	[null]	[null]	[null]	[null]	[null]	[null]
98	2015	3	11	3	AA	354	N3KFAA	ORD	LGA	1635	1631	-4	15	1646	123	112	90	733	1916	7	1938	1923	-15	0	0	[null]	[null]	[null]	[null]	[null]	[null]
99	2015	3	11	3	AA	355	N3MHAA	LGA	ORD	1805	1758	-7	25	1823	171	150	117	733	1920	8	1956	1928	-28	0	0	[null]	[null]	[null]	[null]	[null]	[null]
100	2015	3	11	3	AA	356	N006AA	ATL	DFW	1644	1645	1	15	1700	151	130	109	731	1749	6	1815	1755	-20	0	0	[null]	[null]	[null]	[null]	[null]	[null]

Rows: 1-100 | Columns: 31

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

Rows: 1-100 | Columns: 7

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

Rows: 1-14 | Columns: 2

In [18]:

# Cross Join
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"])

	Abc airport1 Varchar(20)	123 airport1_latitude Numeric(10,6)	123 airport1_longitude Numeric(11,6)	Abc airport2 Varchar(20)	123 airport2_latitude Numeric(10,6)	123 airport2_longitude Numeric(11,6)
1	ABE	40.652360	-75.440400	ABE	40.652360	-75.440400
2	ABE	40.652360	-75.440400	ABI	32.411320	-99.681900
3	ABE	40.652360	-75.440400	ABQ	35.040220	-106.609190
4	ABE	40.652360	-75.440400	ABR	45.449060	-98.421830
5	ABE	40.652360	-75.440400	ABY	31.535520	-84.194470
6	ABE	40.652360	-75.440400	ACK	41.253050	-70.060180
7	ABE	40.652360	-75.440400	ACT	31.611290	-97.230520
8	ABE	40.652360	-75.440400	ACV	40.978120	-124.108620
9	ABE	40.652360	-75.440400	ACY	39.457580	-74.577170
10	ABE	40.652360	-75.440400	ADK	51.877960	-176.646030
11	ABE	40.652360	-75.440400	ADQ	57.749970	-152.493860
12	ABE	40.652360	-75.440400	AEX	31.327370	-92.548560
13	ABE	40.652360	-75.440400	AGS	33.369960	-81.964500
14	ABE	40.652360	-75.440400	AKN	58.676800	-156.649220
15	ABE	40.652360	-75.440400	ALB	42.748120	-73.802980
16	ABE	40.652360	-75.440400	ALO	42.557080	-92.400340
17	ABE	40.652360	-75.440400	AMA	35.219370	-101.705930
18	ABE	40.652360	-75.440400	ANC	61.174320	-149.996190
19	ABE	40.652360	-75.440400	APN	45.078070	-83.560290
20	ABE	40.652360	-75.440400	ASE	39.223160	-106.868850
21	ABE	40.652360	-75.440400	ATL	33.640440	-84.426940
22	ABE	40.652360	-75.440400	ATW	44.257410	-88.519480
23	ABE	40.652360	-75.440400	AUS	30.194530	-97.669870
24	ABE	40.652360	-75.440400	AVL	35.436190	-82.541810
25	ABE	40.652360	-75.440400	AVP	41.338150	-75.724270
26	ABE	40.652360	-75.440400	AZO	42.234880	-85.552060
27	ABE	40.652360	-75.440400	BDL	41.938870	-72.683230
28	ABE	40.652360	-75.440400	BET	60.779780	-161.838000
29	ABE	40.652360	-75.440400	BFL	35.433600	-119.056770
30	ABE	40.652360	-75.440400	BGM	42.208480	-75.979610
31	ABE	40.652360	-75.440400	BGR	44.807440	-68.828140
32	ABE	40.652360	-75.440400	BHM	33.562940	-86.753550
33	ABE	40.652360	-75.440400	BIL	45.807660	-108.542860
34	ABE	40.652360	-75.440400	BIS	46.774110	-100.746720
35	ABE	40.652360	-75.440400	BJI	47.509420	-94.933720
36	ABE	40.652360	-75.440400	BLI	48.792750	-122.537530
37	ABE	40.652360	-75.440400	BMI	40.477990	-88.915950
38	ABE	40.652360	-75.440400	BNA	36.124480	-86.678180
39	ABE	40.652360	-75.440400	BOI	43.564440	-116.222780
40	ABE	40.652360	-75.440400	BOS	42.364350	-71.005180
41	ABE	40.652360	-75.440400	BPT	29.950830	-94.020690
42	ABE	40.652360	-75.440400	BQK	31.259030	-81.466310
43	ABE	40.652360	-75.440400	BQN	18.494860	-67.129440
44	ABE	40.652360	-75.440400	BRD	46.397860	-94.137230
45	ABE	40.652360	-75.440400	BRO	25.906830	-97.425860
46	ABE	40.652360	-75.440400	BRW	71.285450	-156.766000
47	ABE	40.652360	-75.440400	BTM	45.954800	-112.497460
48	ABE	40.652360	-75.440400	BTR	30.533160	-91.149630
49	ABE	40.652360	-75.440400	BTV	44.473000	-73.150310
50	ABE	40.652360	-75.440400	BUF	42.940520	-78.732170
51	ABE	40.652360	-75.440400	BUR	34.200620	-118.358500
52	ABE	40.652360	-75.440400	BWI	39.175400	-76.668200
53	ABE	40.652360	-75.440400	BZN	45.776900	-111.153010
54	ABE	40.652360	-75.440400	CAE	33.938840	-81.119540
55	ABE	40.652360	-75.440400	CAK	40.916310	-81.442470
56	ABE	40.652360	-75.440400	CDC	37.700970	-113.098580
57	ABE	40.652360	-75.440400	CDV	60.491830	-145.477650
58	ABE	40.652360	-75.440400	CEC	41.780160	-124.236530
59	ABE	40.652360	-75.440400	CHA	35.035270	-85.203790
60	ABE	40.652360	-75.440400	CHO	38.138640	-78.452860
61	ABE	40.652360	-75.440400	CHS	32.898650	-80.040510
62	ABE	40.652360	-75.440400	CID	41.884590	-91.710870
63	ABE	40.652360	-75.440400	CIU	46.250750	-84.472390
64	ABE	40.652360	-75.440400	CLD	33.127230	-117.278730
65	ABE	40.652360	-75.440400	CLE	41.410890	-81.849400
66	ABE	40.652360	-75.440400	CLL	30.588590	-96.363820
67	ABE	40.652360	-75.440400	CLT	35.214010	-80.943130
68	ABE	40.652360	-75.440400	CMH	39.997990	-82.891880
69	ABE	40.652360	-75.440400	CMI	40.039250	-88.278060
70	ABE	40.652360	-75.440400	CMX	47.168420	-88.489060
71	ABE	40.652360	-75.440400	CNY	38.754960	-109.754840
72	ABE	40.652360	-75.440400	COD	44.520190	-109.023800
73	ABE	40.652360	-75.440400	COS	38.805810	-104.700250
74	ABE	40.652360	-75.440400	COU	38.818090	-92.219630
75	ABE	40.652360	-75.440400	CPR	42.908360	-106.464470
76	ABE	40.652360	-75.440400	CRP	27.770360	-97.501220
77	ABE	40.652360	-75.440400	CRW	38.373150	-81.593190
78	ABE	40.652360	-75.440400	CSG	32.516330	-84.938860
79	ABE	40.652360	-75.440400	CVG	39.046140	-84.662170
80	ABE	40.652360	-75.440400	CWA	44.777620	-89.666780
81	ABE	40.652360	-75.440400	DAB	29.179920	-81.058060
82	ABE	40.652360	-75.440400	DAL	32.847110	-96.851770
83	ABE	40.652360	-75.440400	DAY	39.902380	-84.219380
84	ABE	40.652360	-75.440400	DBQ	42.402960	-90.709170
85	ABE	40.652360	-75.440400	DCA	38.852080	-77.037720
86	ABE	40.652360	-75.440400	DEN	39.858410	-104.667000
87	ABE	40.652360	-75.440400	DFW	32.895950	-97.037200
88	ABE	40.652360	-75.440400	DHN	31.321340	-85.449630
89	ABE	40.652360	-75.440400	DIK	46.797390	-102.801950
90	ABE	40.652360	-75.440400	DLG	59.045410	-158.503340
91	ABE	40.652360	-75.440400	DLH	46.842090	-92.193650
92	ABE	40.652360	-75.440400	DRO	37.151520	-107.753770
93	ABE	40.652360	-75.440400	DSM	41.534930	-93.660680
94	ABE	40.652360	-75.440400	DTW	42.212060	-83.348840
95	ABE	40.652360	-75.440400	DVL	48.114250	-98.908780
96	ABE	40.652360	-75.440400	EAU	44.865260	-91.485070
97	ABE	40.652360	-75.440400	ECP	[null]	[null]
98	ABE	40.652360	-75.440400	EGE	39.642570	-106.917700
99	ABE	40.652360	-75.440400	EKO	40.824930	-115.791700
100	ABE	40.652360	-75.440400	ELM	42.159910	-76.891440

Out[18]:

Rows: 1-100 of 103684 | Columns: 6

In [19]:

# Left Join
flights.join(airlines,
             how = "left",
             on = [("airline": "IATA_CODE", "=")],
             expr1 = ["*"],
             expr2 = ["AIRLINE AS airline_long"])

	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	Abc airline_long Varchar(56)
1	2015	5	15	5	DL	2242	N979DL	DTW	IND	1350	1356	6	43	1439	64	88	39	231	1518	6	1454	1524	30	0	0	[null]	24	0	6	0	0	Delta Air Lines Inc.
2	2015	5	15	5	DL	2242	N979DL	IND	DTW	1535	1600	25	13	1613	70	63	45	231	1658	5	1645	1703	18	0	0	[null]	0	0	0	18	0	Delta Air Lines Inc.
3	2015	5	15	5	DL	2243	N924DN	MSP	TPA	912	908	-4	11	919	191	178	163	1306	1302	4	1323	1306	-17	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
4	2015	5	15	5	DL	2243	N924DN	TPA	MSP	1415	1412	-3	11	1423	201	186	167	1306	1610	8	1636	1618	-18	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
5	2015	5	15	5	DL	2244	N347NB	MSP	SNA	945	942	-3	17	959	236	226	204	1522	1123	5	1141	1128	-13	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
6	2015	5	15	5	DL	2245	N673DL	RSW	MSP	1330	1329	-1	11	1340	212	190	171	1416	1531	8	1602	1539	-23	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
7	2015	5	15	5	DL	2246	N587NW	HNL	SEA	2020	2026	6	19	2045	338	337	314	2677	459	4	458	503	5	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
8	2015	5	15	5	DL	2247	N554NW	ATL	PBI	2259	2302	3	16	2318	101	95	72	545	30	7	40	37	-3	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
9	2015	5	15	5	DL	2248	N351NB	DTW	LGA	1527	1525	-2	17	1542	108	103	80	502	1702	6	1715	1708	-7	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
10	2015	5	15	5	DL	2249	N942DL	ATL	IAH	2210	2208	-2	10	2218	136	113	94	689	2252	9	2326	2301	-25	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
11	2015	5	15	5	DL	2250	N342NB	ATL	GSP	859	928	29	15	943	54	46	27	153	1010	4	953	1014	21	0	0	[null]	0	0	4	17	0	Delta Air Lines Inc.
12	2015	5	15	5	DL	2250	N342NB	GSP	ATL	1028	1056	28	15	1111	62	58	33	153	1144	10	1130	1154	24	0	0	[null]	0	0	6	18	0	Delta Air Lines Inc.
13	2015	5	15	5	DL	2251	N945DL	ATL	CLT	1000	958	-2	16	1014	79	63	41	226	1055	6	1119	1101	-18	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
14	2015	5	15	5	DL	2251	N945DL	CLT	ATL	1200	1152	-8	28	1220	84	83	47	226	1307	8	1324	1315	-9	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
15	2015	5	15	5	DL	2252	N917DE	CHS	ATL	845	842	-3	11	853	77	77	54	259	947	12	1002	959	-3	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
16	2015	5	15	5	DL	2253	N594NW	MSP	SEA	1735	1751	16	14	1805	212	195	176	1399	1901	5	1907	1906	-1	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
17	2015	5	15	5	DL	2254	N814DN	LAX	ATL	55	117	22	14	131	259	238	219	1947	810	5	814	815	1	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
18	2015	5	15	5	DL	2255	N654DL	ATL	LAX	2040	2059	19	16	2115	295	278	256	1947	2231	6	2235	2237	2	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
19	2015	5	15	5	DL	2256	N550NW	ATL	MCI	1455	1534	39	17	1551	133	122	101	692	1632	4	1608	1636	28	0	0	[null]	0	0	0	28	0	Delta Air Lines Inc.
20	2015	5	15	5	DL	2256	N550NW	MCI	ATL	1658	1735	37	15	1750	122	116	94	692	2024	7	2000	2031	31	0	0	[null]	0	0	8	23	0	Delta Air Lines Inc.
21	2015	5	15	5	DL	2257	N314NB	SLC	SNA	1130	1126	-4	11	1137	114	104	88	588	1205	5	1224	1210	-14	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
22	2015	5	15	5	DL	2258	N816DN	ATL	SMF	1930	1927	-3	11	1938	306	281	267	2092	2105	3	2136	2108	-28	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
23	2015	5	15	5	DL	2258	N816DN	SMF	ATL	2244	2241	-3	11	2252	261	275	257	2092	609	7	605	616	11	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
24	2015	5	15	5	DL	2259	N804DN	MSP	SLC	1140	1147	7	15	1202	167	158	140	991	1322	3	1327	1325	-2	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
25	2015	5	15	5	DL	2260	N543US	PBI	ATL	540	536	-4	13	549	100	99	77	545	706	9	720	715	-5	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
26	2015	5	15	5	DL	2261	N954AT	ATL	DAL	821	821	0	18	839	133	133	111	721	930	4	934	934	0	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
27	2015	5	15	5	DL	2261	N954AT	DAL	ATL	1010	1010	0	14	1024	128	119	95	721	1259	10	1318	1309	-9	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
28	2015	5	15	5	DL	2262	N707TW	LAX	JFK	1340	1343	3	14	1357	334	309	284	2475	2141	11	2214	2152	-22	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
29	2015	5	15	5	DL	2263	N909DE	JFK	MIA	1535	1533	-2	24	1557	210	183	151	1089	1828	8	1905	1836	-29	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
30	2015	5	15	5	DL	2264	N906DA	DEN	MSP	600	554	-6	12	606	118	114	99	680	845	3	858	848	-10	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
31	2015	5	15	5	DL	2265	N393DA	CMH	LAX	643	637	-6	13	650	292	315	272	1995	822	30	835	852	17	0	0	[null]	17	0	0	0	0	Delta Air Lines Inc.
32	2015	5	15	5	DL	2266	N375NC	CLT	MSP	615	608	-7	16	624	169	154	134	930	738	4	804	742	-22	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
33	2015	5	15	5	DL	2267	N681DA	ATL	SAN	1110	1107	-3	11	1118	281	272	257	1892	1235	4	1251	1239	-12	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
34	2015	5	15	5	DL	2267	N681DA	SAN	ATL	1345	1339	-6	17	1356	254	256	216	1892	2032	23	2059	2055	-4	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
35	2015	5	15	5	DL	2268	N324US	CLT	DTW	1710	1704	-6	10	1714	112	110	93	500	1847	7	1902	1854	-8	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
36	2015	5	15	5	DL	2269	N185DN	ATL	MCO	1155	1148	-7	14	1202	83	85	63	404	1305	8	1318	1313	-5	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
37	2015	5	15	5	DL	2269	N185DN	MCO	ATL	1420	1417	-3	28	1445	99	97	60	404	1545	9	1559	1554	-5	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
38	2015	5	15	5	DL	2270	N910DL	LGA	RSW	815	815	0	45	900	196	209	159	1080	1139	5	1131	1144	13	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
39	2015	5	15	5	DL	2271	N932DN	PBI	ATL	930	927	-3	12	939	107	110	81	545	1100	17	1117	1117	0	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
40	2015	5	15	5	DL	2272	N674DL	ATL	SRQ	1509	1506	-3	15	1521	92	82	64	444	1625	3	1641	1628	-13	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
41	2015	5	15	5	DL	2272	N674DL	SRQ	ATL	1731	1725	-6	15	1740	100	87	66	444	1846	6	1911	1852	-19	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
42	2015	5	15	5	DL	2273	N935AT	GSO	ATL	700	656	-4	16	712	81	82	59	306	811	7	821	818	-3	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
43	2015	5	15	5	DL	2274	N957DL	DTW	RDU	1350	1345	-5	15	1400	97	94	75	501	1515	4	1527	1519	-8	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
44	2015	5	15	5	DL	2274	N957DL	RDU	DTW	1705	1705	0	24	1729	111	115	87	501	1856	4	1856	1900	4	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
45	2015	5	15	5	DL	2275	N917DE	ATL	ORF	2045	2058	13	18	2116	100	91	69	516	2225	4	2225	2229	4	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
46	2015	5	15	5	DL	2276	N967DL	MCO	JFK	1955	1949	-6	18	2007	167	155	130	944	2217	7	2242	2224	-18	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
47	2015	5	15	5	DL	2277	N938AT	JFK	TPA	1840	1850	10	25	1915	193	167	139	1005	2134	3	2153	2137	-16	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
48	2015	5	15	5	DL	2279	N924DN	MSP	SJC	1743	1740	-3	18	1758	242	237	216	1576	1934	3	1945	1937	-8	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
49	2015	5	15	5	DL	2280	N910AT	LGA	TPA	835	836	1	18	854	174	160	139	1010	1113	3	1129	1116	-13	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
50	2015	5	15	5	DL	2281	N977DL	ATL	IND	1708	1710	2	37	1747	97	106	64	432	1851	5	1845	1856	11	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
51	2015	5	15	5	DL	2281	N977DL	IND	ATL	1925	1935	10	22	1957	98	106	64	432	2101	20	2103	2121	18	0	0	[null]	8	0	0	10	0	Delta Air Lines Inc.
52	2015	5	15	5	DL	2282	N913DE	LGA	MCO	2004	1957	-7	18	2015	176	144	124	950	2219	2	2300	2221	-39	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
53	2015	5	15	5	DL	2283	N585NW	DTW	ATL	605	607	2	14	621	118	113	93	594	754	6	803	800	-3	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
54	2015	5	15	5	DL	2284	N317US	SLC	SMF	1142	1139	-3	11	1150	97	87	73	532	1203	3	1219	1206	-13	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
55	2015	5	15	5	DL	2285	N911DE	LGA	MCO	705	656	-9	34	730	163	187	143	950	953	10	948	1003	15	0	0	[null]	15	0	0	0	0	Delta Air Lines Inc.
56	2015	5	15	5	DL	2286	N326US	ABQ	SLC	605	603	-2	10	613	100	87	69	493	722	8	745	730	-15	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
57	2015	5	15	5	DL	2287	N331NW	DEN	DTW	1605	1631	26	31	1702	173	178	138	1123	2120	9	2058	2129	31	0	0	[null]	5	0	3	23	0	Delta Air Lines Inc.
58	2015	5	15	5	DL	2288	N996DL	ATL	BHM	1345	1339	-6	12	1351	56	44	26	134	1317	6	1341	1323	-18	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
59	2015	5	15	5	DL	2289	N921DL	ATL	AVL	2050	2049	-1	15	2104	60	52	33	164	2137	4	2150	2141	-9	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
60	2015	5	15	5	DL	2290	N913DE	PIT	ATL	700	655	-5	12	707	105	107	85	526	832	10	845	842	-3	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
61	2015	5	15	5	DL	2291	N343NB	ATL	CLE	1930	1929	-1	14	1943	110	105	83	554	2106	8	2120	2114	-6	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
62	2015	5	15	5	DL	2292	N939DL	JFK	PBI	805	757	-8	39	836	187	187	143	1028	1059	5	1112	1104	-8	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
63	2015	5	15	5	DL	2293	N310NW	ATL	ELP	1931	1927	-4	14	1941	206	194	176	1282	2037	4	2057	2041	-16	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
64	2015	5	15	5	DL	2294	N359NW	GSO	ATL	525	641	76	17	658	71	83	59	306	757	7	636	804	88	0	0	[null]	12	0	76	0	0	Delta Air Lines Inc.
65	2015	5	15	5	DL	2295	N356NW	SAN	SLC	1335	1405	30	17	1422	113	106	84	626	1646	5	1628	1651	23	0	0	[null]	0	0	0	23	0	Delta Air Lines Inc.
66	2015	5	15	5	DL	2296	N326NB	MSP	LGA	1731	1730	-1	10	1740	169	149	131	1020	2051	8	2120	2059	-21	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
67	2015	5	15	5	DL	2297	N908DA	ATL	JAX	1350	1402	12	14	1416	75	61	43	270	1459	4	1505	1503	-2	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
68	2015	5	15	5	DL	2297	N908DA	JAX	ATL	1545	1552	7	9	1601	80	64	47	270	1648	8	1705	1656	-9	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
69	2015	5	15	5	DL	2298	N667DN	ATL	SRQ	905	901	-4	11	912	87	75	62	444	1014	2	1032	1016	-16	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
70	2015	5	15	5	DL	2298	N667DN	SRQ	ATL	1122	1119	-3	13	1132	100	90	66	444	1238	11	1302	1249	-13	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
71	2015	5	15	5	DL	2300	N325US	ATL	BOS	2140	2157	17	14	2211	158	149	125	946	16	10	18	26	8	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
72	2015	5	15	5	DL	2301	N339NB	JFK	SAT	1745	1737	-8	23	1800	269	247	220	1587	2040	4	2114	2044	-30	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
73	2015	5	15	5	DL	2302	N928DL	DCA	DTW	1933	1927	-6	15	1942	92	93	72	405	2054	6	2105	2100	-5	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
74	2015	5	15	5	DL	2303	N366NB	DTW	GRR	2007	2011	4	18	2029	58	46	24	120	2053	4	2105	2057	-8	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
75	2015	5	15	5	DL	2304	N542US	ATL	TPA	2150	2149	-1	11	2200	86	76	60	406	2300	5	2316	2305	-11	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
76	2015	5	15	5	DL	2305	N944DN	MSP	SFO	916	912	-4	13	925	249	224	207	1589	1052	4	1125	1056	-29	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
77	2015	5	15	5	DL	2305	N944DN	SFO	MSP	1230	1227	-3	15	1242	218	230	211	1589	1813	4	1808	1817	9	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
78	2015	5	15	5	DL	2306	N968AT	JFK	TPA	805	759	-6	47	846	186	184	134	1005	1100	3	1111	1103	-8	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
79	2015	5	15	5	DL	2307	N989DL	ATL	SAV	1915	1910	-5	17	1927	66	56	34	214	2001	5	2021	2006	-15	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
80	2015	5	15	5	DL	2308	N585NW	SEA	MSP	1300	1257	-3	21	1318	195	208	182	1399	1820	5	1815	1825	10	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
81	2015	5	15	5	DL	2309	N901DA	ATL	STL	818	819	1	14	833	96	92	73	484	846	5	854	851	-3	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
82	2015	5	15	5	DL	2309	N901DA	STL	ATL	935	940	5	10	950	90	81	65	484	1155	6	1205	1201	-4	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
83	2015	5	15	5	DL	2310	N974DL	ATL	DFW	850	854	4	11	905	139	142	127	731	1012	4	1009	1016	7	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
84	2015	5	15	5	DL	2310	N974DL	DFW	ATL	1100	1107	7	22	1129	133	131	96	731	1405	13	1413	1418	5	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
85	2015	5	15	5	DL	2311	N915DE	JFK	MIA	1845	1844	-1	22	1906	206	167	141	1089	2127	4	2211	2131	-40	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
86	2015	5	15	5	DL	2312	N976DL	JFK	DTW	1630	1625	-5	15	1640	140	108	86	509	1806	7	1850	1813	-37	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
87	2015	5	15	5	DL	2313	N333NB	IND	MSP	1305	1306	1	10	1316	104	89	76	503	1332	3	1349	1335	-14	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
88	2015	5	15	5	DL	2313	N333NB	MSP	IND	957	1000	3	15	1015	93	87	69	503	1224	3	1230	1227	-3	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
89	2015	5	15	5	DL	2314	N582NW	SEA	MSP	715	712	-3	18	730	195	194	173	1399	1223	3	1230	1226	-4	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
90	2015	5	15	5	DL	2315	N3767	CVG	ATL	718	716	-2	10	726	91	85	65	373	831	10	849	841	-8	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
91	2015	5	15	5	DL	2316	N397DA	DTW	LAS	845	845	0	18	903	262	268	240	1749	1003	10	1007	1013	6	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
92	2015	5	15	5	DL	2317	N906DL	PBI	LGA	1421	1418	-3	12	1430	173	160	140	1035	1650	8	1714	1658	-16	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
93	2015	5	15	5	DL	2318	N337NB	BZN	MSP	1328	1321	-7	9	1330	133	136	124	874	1634	3	1641	1637	-4	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
94	2015	5	15	5	DL	2318	N337NB	MSP	BZN	1125	1122	-3	15	1137	147	137	118	874	1235	4	1252	1239	-13	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
95	2015	5	15	5	DL	2319	N337NW	LGA	MSP	1015	1010	-5	17	1027	195	162	141	1020	1148	4	1230	1152	-38	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
96	2015	5	15	5	DL	2321	N804DN	MSP	LAX	1932	1937	5	21	1958	241	237	209	1535	2127	7	2133	2134	1	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
97	2015	5	15	5	DL	2322	N943DN	MIA	MSP	1525	1541	16	21	1602	234	218	190	1501	1812	7	1819	1819	0	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
98	2015	5	15	5	DL	2322	N943DN	MSP	MIA	1005	1003	-2	17	1020	217	222	197	1501	1437	8	1442	1445	3	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
99	2015	5	15	5	DL	2323	N532US	TPA	ATL	520	517	-3	11	528	85	80	62	406	630	7	645	637	-8	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.
100	2015	5	15	5	DL	2324	N627DL	SLC	ATL	115	115	0	13	128	215	216	195	1590	643	8	650	651	1	0	0	[null]	[null]	[null]	[null]	[null]	[null]	Delta Air Lines Inc.

Out[19]:

Rows: 1-100 of 5819079 | Columns: 32

In [2]:

sm_consumption = vDataFrame("public.sm_consumption")
sm_weather = vDataFrame("public.sm_weather")
display(sm_consumption)
display(sm_weather)

	123 meterID Int	📅 dateUTC Timestamp	123 value Numeric(10,6)
1	0	2014-01-01 11:00:00	0.029
2	0	2014-01-01 13:45:00	0.277
3	0	2014-01-02 10:45:00	0.321
4	0	2014-01-02 11:15:00	0.305
5	0	2014-01-02 13:45:00	0.358
6	0	2014-01-02 15:30:00	0.115
7	0	2014-01-03 08:30:00	0.071
8	0	2014-01-04 23:45:00	0.323
9	0	2014-01-06 01:15:00	0.085
10	0	2014-01-06 21:45:00	0.713
11	0	2014-01-07 00:15:00	0.242
12	0	2014-01-07 23:00:00	0.09
13	0	2014-01-09 05:30:00	0.115
14	0	2014-01-09 15:00:00	0.928
15	0	2014-01-09 18:15:00	1.09
16	0	2014-01-10 10:00:00	0.07
17	0	2014-01-10 14:30:00	10.832
18	0	2014-01-10 15:30:00	3.288
19	0	2014-01-10 18:00:00	0.42
20	0	2014-01-11 10:45:00	1.045
21	0	2014-01-12 07:45:00	0.091
22	0	2014-01-13 19:45:00	0.249
23	0	2014-01-13 20:15:00	0.34
24	0	2014-01-14 13:00:00	0.099
25	0	2014-01-14 14:00:00	0.2
26	0	2014-01-14 14:45:00	0.865
27	0	2014-01-14 22:00:00	0.716
28	0	2014-01-15 03:45:00	0.05
29	0	2014-01-15 11:15:00	0.052
30	0	2014-01-15 11:30:00	0.038
31	0	2014-01-15 23:15:00	0.239
32	0	2014-01-16 19:30:00	1.422
33	0	2014-01-17 15:00:00	0.291
34	0	2014-01-18 00:30:00	0.828
35	0	2014-01-19 03:00:00	0.017
36	0	2014-01-19 05:30:00	0.024
37	0	2014-01-19 06:15:00	0.048
38	0	2014-01-19 15:30:00	13.3
39	0	2014-01-20 19:30:00	0.59
40	0	2014-01-21 12:30:00	0.327
41	0	2014-01-21 13:30:00	0.108
42	0	2014-01-22 01:30:00	0.083
43	0	2014-01-22 04:30:00	0.044
44	0	2014-01-23 21:00:00	0.226
45	0	2014-01-24 02:00:00	0.125
46	0	2014-01-24 12:15:00	0.168
47	0	2014-01-27 22:45:00	0.495
48	0	2014-01-28 06:15:00	0.056
49	0	2014-01-28 11:30:00	0.113
50	0	2014-01-28 19:00:00	1.566
51	0	2014-01-28 19:45:00	1.108
52	0	2014-01-29 05:00:00	0.064
53	0	2014-01-29 07:45:00	0.145
54	0	2014-01-29 13:00:00	1.719
55	0	2014-01-30 06:15:00	0.059
56	0	2014-01-30 17:45:00	0.575
57	0	2014-01-30 18:45:00	1.129
58	0	2014-02-01 03:30:00	0.057
59	0	2014-02-01 03:45:00	0.059
60	0	2014-02-02 08:45:00	0.186
61	0	2014-02-04 03:45:00	0.045
62	0	2014-02-04 12:00:00	0.187
63	0	2014-02-04 16:00:00	0.313
64	0	2014-02-04 18:45:00	0.912
65	0	2014-02-05 06:45:00	0.018
66	0	2014-02-05 17:15:00	0.112
67	0	2014-02-06 15:15:00	10.146
68	0	2014-02-07 11:00:00	0.868
69	0	2014-02-07 17:30:00	3.812
70	0	2014-02-07 22:15:00	1.262
71	0	2014-02-08 03:45:00	0.043
72	0	2014-02-08 06:45:00	0.106
73	0	2014-02-09 04:15:00	0.449
74	0	2014-02-09 08:30:00	0.007
75	0	2014-02-11 02:30:00	0.087
76	0	2014-02-11 05:30:00	0.039
77	0	2014-02-11 19:00:00	0.094
78	0	2014-02-12 02:30:00	0.102
79	0	2014-02-13 02:45:00	0.097
80	0	2014-02-13 09:30:00	0.161
81	0	2014-02-13 19:00:00	1.147
82	0	2014-02-14 00:15:00	0.084
83	0	2014-02-14 02:00:00	0.174
84	0	2014-02-14 11:15:00	0.05
85	0	2014-02-14 13:45:00	0.033
86	0	2014-02-15 02:00:00	0.181
87	0	2014-02-15 13:45:00	0.031
88	0	2014-02-15 15:00:00	0.483
89	0	2014-02-15 20:15:00	0.368
90	0	2014-02-16 00:00:00	0.195
91	0	2014-02-17 02:45:00	0.094
92	0	2014-02-17 12:45:00	0.049
93	0	2014-02-17 15:15:00	0.008
94	0	2014-02-18 05:00:00	0.041
95	0	2014-02-18 20:15:00	1.202
96	0	2014-02-19 00:15:00	0.148
97	0	2014-02-19 07:00:00	0.095
98	0	2014-02-20 00:15:00	0.039
99	0	2014-02-20 07:30:00	0.213
100	0	2014-02-20 19:00:00	1.208

Rows: 1-100 | Columns: 3

	📅 dateUTC Timestamp	123 temperature Numeric(10,6)	123 humidity Numeric(11,6)
1	2014-01-01 00:00:00	38.0	95.0
2	2014-01-01 00:30:00	37.4	93.0
3	2014-01-01 01:00:00	37.4	100.0
4	2014-01-01 01:30:00	37.4	100.0
5	2014-01-01 02:00:00	39.2	93.0
6	2014-01-01 02:30:00	39.2	93.0
7	2014-01-01 03:00:00	39.2	93.0
8	2014-01-01 03:30:00	39.2	93.0
9	2014-01-01 04:00:00	39.2	93.0
10	2014-01-01 04:30:00	39.2	93.0
11	2014-01-01 05:00:00	39.2	93.0
12	2014-01-01 05:30:00	39.2	87.0
13	2014-01-01 06:00:00	38.0	89.0
14	2014-01-01 06:30:00	37.4	93.0
15	2014-01-01 07:00:00	37.4	93.0
16	2014-01-01 07:30:00	37.4	93.0
17	2014-01-01 08:00:00	37.4	93.0
18	2014-01-01 08:30:00	37.4	87.0
19	2014-01-01 09:00:00	37.4	87.0
20	2014-01-01 09:30:00	37.4	87.0
21	2014-01-01 10:00:00	37.4	93.0
22	2014-01-01 10:30:00	37.4	93.0
23	2014-01-01 11:00:00	37.4	87.0
24	2014-01-01 11:30:00	37.4	93.0
25	2014-01-01 12:00:00	38.0	85.0
26	2014-01-01 12:30:00	39.2	87.0
27	2014-01-01 13:00:00	39.2	87.0
28	2014-01-01 13:30:00	39.2	87.0
29	2014-01-01 14:00:00	39.2	87.0
30	2014-01-01 14:30:00	39.2	87.0
31	2014-01-01 15:00:00	39.2	87.0
32	2014-01-01 15:30:00	39.2	87.0
33	2014-01-01 16:00:00	37.4	93.0
34	2014-01-01 16:30:00	39.2	87.0
35	2014-01-01 17:00:00	39.2	87.0
36	2014-01-01 17:30:00	37.4	87.0
37	2014-01-01 18:00:00	38.0	81.0
38	2014-01-01 18:30:00	37.4	87.0
39	2014-01-01 19:00:00	35.6	87.0
40	2014-01-01 19:30:00	37.4	93.0
41	2014-01-01 20:00:00	39.2	87.0
42	2014-01-01 20:30:00	39.2	87.0
43	2014-01-01 21:00:00	39.2	87.0
44	2014-01-01 21:30:00	41.0	81.0
45	2014-01-01 22:00:00	39.2	87.0
46	2014-01-01 22:30:00	39.2	87.0
47	2014-01-01 23:00:00	39.2	87.0
48	2014-01-01 23:30:00	39.2	81.0
49	2014-01-02 00:00:00	38.0	76.0
50	2014-01-02 00:30:00	37.4	81.0
51	2014-01-02 01:00:00	37.4	81.0
52	2014-01-02 01:30:00	37.4	81.0
53	2014-01-02 02:00:00	37.4	81.0
54	2014-01-02 02:30:00	37.4	81.0
55	2014-01-02 03:00:00	37.4	81.0
56	2014-01-02 03:30:00	37.4	81.0
57	2014-01-02 04:00:00	37.4	81.0
58	2014-01-02 04:30:00	35.6	87.0
59	2014-01-02 05:00:00	35.6	93.0
60	2014-01-02 05:30:00	37.4	81.0
61	2014-01-02 06:00:00	37.0	75.0
62	2014-01-02 06:30:00	37.4	81.0
63	2014-01-02 07:00:00	37.4	87.0
64	2014-01-02 07:30:00	37.4	81.0
65	2014-01-02 08:00:00	35.6	81.0
66	2014-01-02 08:30:00	35.6	81.0
67	2014-01-02 09:00:00	37.4	75.0
68	2014-01-02 09:30:00	37.4	81.0
69	2014-01-02 10:00:00	39.2	75.0
70	2014-01-02 10:30:00	41.0	70.0
71	2014-01-02 11:00:00	39.2	75.0
72	2014-01-02 11:30:00	41.0	70.0
73	2014-01-02 12:00:00	41.0	61.0
74	2014-01-02 12:30:00	41.0	70.0
75	2014-01-02 13:00:00	41.0	76.0
76	2014-01-02 13:30:00	41.0	76.0
77	2014-01-02 14:00:00	41.0	76.0
78	2014-01-02 14:30:00	41.0	76.0
79	2014-01-02 15:00:00	41.0	76.0
80	2014-01-02 15:30:00	41.0	76.0
81	2014-01-02 16:00:00	41.0	76.0
82	2014-01-02 16:30:00	41.0	76.0
83	2014-01-02 17:00:00	41.0	76.0
84	2014-01-02 17:30:00	39.2	81.0
85	2014-01-02 18:00:00	39.0	70.0
86	2014-01-02 18:30:00	37.4	81.0
87	2014-01-02 19:00:00	39.2	75.0
88	2014-01-02 19:30:00	37.4	81.0
89	2014-01-02 20:00:00	37.4	81.0
90	2014-01-02 20:30:00	39.2	75.0
91	2014-01-02 21:00:00	39.2	65.0
92	2014-01-02 21:30:00	39.2	65.0
93	2014-01-02 22:00:00	39.2	65.0
94	2014-01-02 22:30:00	39.2	61.0
95	2014-01-02 23:00:00	39.2	61.0
96	2014-01-02 23:30:00	39.2	65.0
97	2014-01-03 00:00:00	39.0	48.0
98	2014-01-03 00:30:00	39.2	61.0
99	2014-01-03 01:00:00	39.2	65.0
100	2014-01-03 01:30:00	37.4	70.0

Rows: 1-100 | Columns: 3

In [5]:

# Time Series Join with interpolation

sm_consumption.join(sm_weather,
                    on = [("dateUTC": "dateUTC", "linterpolate")],
                    how = "left",
                    expr1 = ["dateUTC AS dateUTC_sm", "meterID", "value"],
                    expr2 = ["*"])

Out[5]:

	📅 dateUTC_sm Timestamp	123 meterID Int	123 value Numeric(10,6)	📅 dateUTC Timestamp	123 temperature Numeric(10,6)	123 humidity Numeric(11,6)
1	2014-01-01 00:00:00	913	0.097	2014-01-01 00:00:00	38.0	95.0
2	2014-01-01 00:00:00	895	0.184	2014-01-01 00:00:00	38.0	95.0
3	2014-01-01 00:00:00	795	0.093	2014-01-01 00:00:00	38.0	95.0
4	2014-01-01 00:00:00	780	0.116	2014-01-01 00:00:00	38.0	95.0
5	2014-01-01 00:00:00	747	0.559	2014-01-01 00:00:00	38.0	95.0
6	2014-01-01 00:00:00	660	0.836	2014-01-01 00:00:00	38.0	95.0
7	2014-01-01 00:00:00	640	1.796	2014-01-01 00:00:00	38.0	95.0
8	2014-01-01 00:00:00	605	0.038	2014-01-01 00:00:00	38.0	95.0
9	2014-01-01 00:00:00	484	0.211	2014-01-01 00:00:00	38.0	95.0
10	2014-01-01 00:00:00	474	0.313	2014-01-01 00:00:00	38.0	95.0
11	2014-01-01 00:00:00	457	0.022	2014-01-01 00:00:00	38.0	95.0
12	2014-01-01 00:00:00	371	0.815	2014-01-01 00:00:00	38.0	95.0
13	2014-01-01 00:00:00	355	0.448	2014-01-01 00:00:00	38.0	95.0
14	2014-01-01 00:00:00	348	0.071	2014-01-01 00:00:00	38.0	95.0
15	2014-01-01 00:00:00	307	1.423	2014-01-01 00:00:00	38.0	95.0
16	2014-01-01 00:00:00	188	0.152	2014-01-01 00:00:00	38.0	95.0
17	2014-01-01 00:00:00	181	0.138	2014-01-01 00:00:00	38.0	95.0
18	2014-01-01 00:00:00	179	0.033	2014-01-01 00:00:00	38.0	95.0
19	2014-01-01 00:00:00	129	0.054	2014-01-01 00:00:00	38.0	95.0
20	2014-01-01 00:00:00	116	0.232	2014-01-01 00:00:00	38.0	95.0
21	2014-01-01 00:15:00	986	0.301	2014-01-01 00:00:00	38.0	95.0
22	2014-01-01 00:15:00	848	0.186	2014-01-01 00:00:00	38.0	95.0
23	2014-01-01 00:15:00	829	0.145	2014-01-01 00:00:00	38.0	95.0
24	2014-01-01 00:15:00	718	0.091	2014-01-01 00:00:00	38.0	95.0
25	2014-01-01 00:15:00	637	0.319	2014-01-01 00:00:00	38.0	95.0
26	2014-01-01 00:15:00	625	0.141	2014-01-01 00:00:00	38.0	95.0
27	2014-01-01 00:15:00	619	0.103	2014-01-01 00:00:00	38.0	95.0
28	2014-01-01 00:15:00	583	4.081	2014-01-01 00:00:00	38.0	95.0
29	2014-01-01 00:15:00	444	0.131	2014-01-01 00:00:00	38.0	95.0
30	2014-01-01 00:15:00	221	0.399	2014-01-01 00:00:00	38.0	95.0
31	2014-01-01 00:15:00	67	0.157	2014-01-01 00:00:00	38.0	95.0
32	2014-01-01 00:15:00	52	0.157	2014-01-01 00:00:00	38.0	95.0
33	2014-01-01 00:30:00	933	0.061	2014-01-01 00:30:00	37.4	93.0
34	2014-01-01 00:30:00	930	0.202	2014-01-01 00:30:00	37.4	93.0
35	2014-01-01 00:30:00	904	0.159	2014-01-01 00:30:00	37.4	93.0
36	2014-01-01 00:30:00	829	0.082	2014-01-01 00:30:00	37.4	93.0
37	2014-01-01 00:30:00	796	0.237	2014-01-01 00:30:00	37.4	93.0
38	2014-01-01 00:30:00	663	0.08	2014-01-01 00:30:00	37.4	93.0
39	2014-01-01 00:30:00	628	1.246	2014-01-01 00:30:00	37.4	93.0
40	2014-01-01 00:30:00	626	0.106	2014-01-01 00:30:00	37.4	93.0
41	2014-01-01 00:30:00	618	0.067	2014-01-01 00:30:00	37.4	93.0
42	2014-01-01 00:30:00	506	0.108	2014-01-01 00:30:00	37.4	93.0
43	2014-01-01 00:30:00	497	0.144	2014-01-01 00:30:00	37.4	93.0
44	2014-01-01 00:30:00	460	0.094	2014-01-01 00:30:00	37.4	93.0
45	2014-01-01 00:30:00	376	0.129	2014-01-01 00:30:00	37.4	93.0
46	2014-01-01 00:30:00	332	0.044	2014-01-01 00:30:00	37.4	93.0
47	2014-01-01 00:30:00	308	0.045	2014-01-01 00:30:00	37.4	93.0
48	2014-01-01 00:30:00	286	0.209	2014-01-01 00:30:00	37.4	93.0
49	2014-01-01 00:30:00	277	0.287	2014-01-01 00:30:00	37.4	93.0
50	2014-01-01 00:30:00	264	0.162	2014-01-01 00:30:00	37.4	93.0
51	2014-01-01 00:30:00	138	2.276	2014-01-01 00:30:00	37.4	93.0
52	2014-01-01 00:30:00	90	0.183	2014-01-01 00:30:00	37.4	93.0
53	2014-01-01 00:45:00	977	0.176	2014-01-01 00:30:00	37.4	93.0
54	2014-01-01 00:45:00	937	0.201	2014-01-01 00:30:00	37.4	93.0
55	2014-01-01 00:45:00	927	0.551	2014-01-01 00:30:00	37.4	93.0
56	2014-01-01 00:45:00	867	0.844	2014-01-01 00:30:00	37.4	93.0
57	2014-01-01 00:45:00	762	0.13	2014-01-01 00:30:00	37.4	93.0
58	2014-01-01 00:45:00	528	0.067	2014-01-01 00:30:00	37.4	93.0
59	2014-01-01 00:45:00	447	0.599	2014-01-01 00:30:00	37.4	93.0
60	2014-01-01 00:45:00	375	0.519	2014-01-01 00:30:00	37.4	93.0
61	2014-01-01 00:45:00	297	0.609	2014-01-01 00:30:00	37.4	93.0
62	2014-01-01 00:45:00	250	0.428	2014-01-01 00:30:00	37.4	93.0
63	2014-01-01 00:45:00	235	0.06	2014-01-01 00:30:00	37.4	93.0
64	2014-01-01 01:00:00	980	0.16	2014-01-01 01:00:00	37.4	100.0
65	2014-01-01 01:00:00	797	0.114	2014-01-01 01:00:00	37.4	100.0
66	2014-01-01 01:00:00	783	0.025	2014-01-01 01:00:00	37.4	100.0
67	2014-01-01 01:00:00	709	0.058	2014-01-01 01:00:00	37.4	100.0
68	2014-01-01 01:00:00	697	0.184	2014-01-01 01:00:00	37.4	100.0
69	2014-01-01 01:00:00	636	0.139	2014-01-01 01:00:00	37.4	100.0
70	2014-01-01 01:00:00	548	0.309	2014-01-01 01:00:00	37.4	100.0
71	2014-01-01 01:00:00	374	0.104	2014-01-01 01:00:00	37.4	100.0
72	2014-01-01 01:00:00	358	0.471	2014-01-01 01:00:00	37.4	100.0
73	2014-01-01 01:00:00	304	0.109	2014-01-01 01:00:00	37.4	100.0
74	2014-01-01 01:00:00	291	0.412	2014-01-01 01:00:00	37.4	100.0
75	2014-01-01 01:00:00	263	0.172	2014-01-01 01:00:00	37.4	100.0
76	2014-01-01 01:00:00	256	0.124	2014-01-01 01:00:00	37.4	100.0
77	2014-01-01 01:00:00	123	0.309	2014-01-01 01:00:00	37.4	100.0
78	2014-01-01 01:00:00	79	0.243	2014-01-01 01:00:00	37.4	100.0
79	2014-01-01 01:00:00	75	0.274	2014-01-01 01:00:00	37.4	100.0
80	2014-01-01 01:00:00	61	0.1	2014-01-01 01:00:00	37.4	100.0
81	2014-01-01 01:00:00	35	0.134	2014-01-01 01:00:00	37.4	100.0
82	2014-01-01 01:15:00	950	0.127	2014-01-01 01:00:00	37.4	100.0
83	2014-01-01 01:15:00	844	0.539	2014-01-01 01:00:00	37.4	100.0
84	2014-01-01 01:15:00	832	0.358	2014-01-01 01:00:00	37.4	100.0
85	2014-01-01 01:15:00	807	0.095	2014-01-01 01:00:00	37.4	100.0
86	2014-01-01 01:15:00	656	0.104	2014-01-01 01:00:00	37.4	100.0
87	2014-01-01 01:15:00	598	0.18	2014-01-01 01:00:00	37.4	100.0
88	2014-01-01 01:15:00	588	0.064	2014-01-01 01:00:00	37.4	100.0
89	2014-01-01 01:15:00	570	0.018	2014-01-01 01:00:00	37.4	100.0
90	2014-01-01 01:15:00	476	0.299	2014-01-01 01:00:00	37.4	100.0
91	2014-01-01 01:15:00	467	0.292	2014-01-01 01:00:00	37.4	100.0
92	2014-01-01 01:15:00	386	0.156	2014-01-01 01:00:00	37.4	100.0
93	2014-01-01 01:15:00	306	0.16	2014-01-01 01:00:00	37.4	100.0
94	2014-01-01 01:15:00	290	0.092	2014-01-01 01:00:00	37.4	100.0
95	2014-01-01 01:15:00	193	0.101	2014-01-01 01:00:00	37.4	100.0
96	2014-01-01 01:15:00	134	0.429	2014-01-01 01:00:00	37.4	100.0
97	2014-01-01 01:15:00	123	0.446	2014-01-01 01:00:00	37.4	100.0
98	2014-01-01 01:15:00	119	0.268	2014-01-01 01:00:00	37.4	100.0
99	2014-01-01 01:15:00	89	0.107	2014-01-01 01:00:00	37.4	100.0
100	2014-01-01 01:15:00	2	0.037	2014-01-01 01:00:00	37.4	100.0

Rows: 1-100 | Columns: 6

vDataFrame.append	Merges the vDataFrame with another relation.
vDataFrame.groupby	Aggregates the vDataFrame.
vDataFrame.sort	Sorts the vDataFrame.

vDataFrame.join¶

Parameters¶

Returns¶

Example¶

See Also¶