Commodities¶

This example uses the 'Commodities' dataset to predict the price of different commodities. You can download the Jupyter Notebook of the study here.

date: Date of the record
Gold: Price per ounce of Gold
Oil: Price per Barrel - West Texas Intermediate (WTI)
Spread: Interest Rate Spreads.
Vix: The CBOE Volatility Index (VIX) is a measure of expected price fluctuations in the SP500 Index options over the next 30 days.
Dol_Eur: How much $1 US is in euros.
SP500: The S&P 500, or simply the S&P, is a stock market index that measures the stock performance of 500 large companies listed on stock exchanges in the United States.

We will follow the data science cycle (Data Exploration - Data Preparation - Data Modeling - Model Evaluation - Model Deployment) to solve this problem.

Initialization¶

This example uses the following version of VerticaPy:

In [1]:

import verticapy as vp
vp.__version__

Out[1]:

'0.9.0'

Connect to Vertica. This example uses an existing connection called "VerticaDSN." For details on how to create a connection, use see the connection tutorial.

In [2]:

vp.connect("VerticaDSN")

Let's create a Virtual DataFrame of the dataset.

In [3]:

from verticapy.datasets import load_commodities
commodities = load_commodities()
display(commodities)

	📅 date Date	123 Gold Float	123 Oil Float	123 Spread Float	123 Vix Float	123 Dol_Eur Float	123 SP500 Float
1	1986-01-01	345.561363636364	22.9254545454545	1.05142857142857	18.1213636363636	1.12159999999858	211.779999
2	1986-02-01	339.0525	15.4547368421053	0.736842105263158	20.6242105263158	1.07880000000296	226.919998
3	1986-03-01	346.094736842105	12.6125	0.564	23.564	1.04850000000442	238.899994
4	1986-04-01	340.715909090909	12.8436363636364	0.604090909090909	23.0154545454545	1.05259999999544	235.520004
5	1986-05-01	342.325	15.377619047619	0.642380952380952	18.8875	1.03720000000612	247.350006
6	1986-06-01	342.797619047619	13.4257142857143	0.614761904761905	18.5980952380952	1.0399999999936	250.839996
7	1986-07-01	348.554347826087	11.5845454545455	0.636818181818182	19.6390909090909	1.01029999999446	236.119995
8	1986-08-01	376.29	15.0966666666667	0.83952380952381	18.6380952380952	0.979300000000876	252.929993
9	1986-09-01	418.152272727273	14.8666666666667	1.10142857142857	22.7052380952381	0.973200000000361	231.320007
10	1986-10-01	423.863043478261	14.8968181818182	1.14727272727273	22.5239130434783	0.961600000000544	243.979996
11	1986-11-01	396.9825	15.2215789473684	0.973888888888889	18.6315789473684	0.967399999999543	249.220001
12	1986-12-01	391.595238095238	16.107619047619	0.840909090909091	19.7586363636364	0.957399999999325	242.169998
13	1987-01-01	408.52380952381	18.6514285714286	0.8585	20.7666666666667	0.900400000000445	274.079987
14	1987-02-01	401.045	17.7489473684211	0.852631578947368	23.4463157894737	0.882600000000821	284.200012
15	1987-03-01	408.847727272727	18.3028571428571	0.824090909090909	21.8372727272727	0.883599999999206	291.700012
16	1987-04-01	439.665	18.6771428571429	0.998571428571429	26.8814285714286	0.872100000000501	288.359985
17	1987-05-01	461.65	19.4375	0.8515	25.4115	0.861100000000079	290.100006
18	1987-06-01	449.277272727273	20.0731818181818	0.829090909090909	21.6245454545455	0.876500000000306	304.0
19	1987-07-01	450.330434782609	21.3421739130435	1.00454545454545	17.8009090909091	0.888600000000224	318.660004
20	1987-08-01	460.9875	20.3109523809524	1.00761904761905	20.8490476190476	0.89600000000064	329.799988
21	1987-09-01	460.120454545455	19.53	1.07809523809524	22.8938095238095	0.873499999999694	321.829987
22	1987-10-01	465.763636363636	19.8590909090909	1.12238095238095	58.2195454545455	0.868599999999788	251.789993
23	1987-11-01	468.140476190476	18.854	1.16789473684211	49.4365	0.814700000000812	230.300003
24	1987-12-01	487.078571428571	17.2745454545455	1.13	41.7640909090909	0.791400000000067	247.080002
25	1988-01-01	477.7575	17.1295	1.03578947368421	38.3365	0.800100000000384	257.070007
26	1988-02-01	442.12380952381	16.7957142857143	1.0315	33.674	0.822000000000116	267.820007
27	1988-03-01	443.491304347826	16.1973913043478	1.10304347826087	29.3569565217391	0.810900000000402	258.890015
28	1988-04-01	451.557894736842	17.8625	1.129	27.405	0.805700000000798	261.329987
29	1988-05-01	451.32	17.4236363636364	1.09333333333333	25.7166666666667	0.813099999999395	262.160004
30	1988-06-01	451.656818181818	16.5277272727273	0.895454545454546	25.2709090909091	0.842500000000655	273.5
31	1988-07-01	437.452380952381	15.4975	0.777	23.644	0.886300000000119	272.019989
32	1988-08-01	431.063636363636	15.5234782608696	0.631739130434783	23.7073913043478	0.905899999999747	261.519989
33	1988-09-01	413.438636363636	14.5354545454545	0.513809523809524	19.5242857142857	0.900299999999334	271.910004
34	1988-10-01	406.390476190476	13.7704761904762	0.4415	20.4819047619048	0.87960000000021	278.970001
35	1988-11-01	419.965909090909	14.1413636363636	0.2965	21.6590476190476	0.844499999999243	273.700012
36	1988-12-01	419.2475	16.382380952381	0.0171428571428571	17.7542857142857	0.843999999999141	277.720001
37	1989-01-01	404.445238095238	18.0242857142857	-0.084	17.7580952380952	0.880499999999302	297.470001
38	1989-02-01	387.9725	17.9365	-0.202631578947368	18.3126315789474	0.888899999999921	288.859985
39	1989-03-01	390.27380952381	19.4840909090909	-0.321818181818182	17.4754545454545	0.896699999999328	294.869995
40	1989-04-01	384.72	21.069	-0.276	16.8875	0.899199999999837	309.640015
41	1989-05-01	371.35	20.1234782608696	-0.158636363636364	17.435	0.9375	320.519989
42	1989-06-01	367.727272727273	20.0504545454545	-0.129090909090909	16.7322727272727	0.955400000000736	317.980011
43	1989-07-01	375.209523809524	19.7814285714286	0.199	18.0565	0.914000000000669	346.079987
44	1989-08-01	365.547727272727	18.5778260869565	-0.0321739130434783	19.3234782608696	0.927799999999479	351.450012
45	1989-09-01	361.797619047619	19.5914285714286	-0.094	16.7465	0.941100000000006	349.149994
46	1989-10-01	366.8	20.0977272727273	0.0285714285714286	21.9509090909091	0.907199999999648	340.359985
47	1989-11-01	394.361363636364	19.8559090909091	0.0785714285714286	20.932380952381	0.893899999999121	345.98999
48	1989-12-01	409.655263157895	21.1	0.059	18.235	0.856499999999869	353.399994
49	1990-01-01	410.118181818182	22.8631818181818	0.121428571428571	23.4990909090909	0.831899999999223	329.079987
50	1990-02-01	416.5425	22.113	0.102631578947368	23.3268421052632	0.820799999999508	331.890015
51	1990-03-01	393.661363636364	20.3877272727273	-0.0381818181818182	18.8313636363636	0.835100000000239	339.940002
52	1990-04-01	374.928947368421	18.4255	0.0615	20.4575	0.82510000000002	330.799988
53	1990-05-01	368.854761904762	18.1995454545455	0.115909090909091	18.1518181818182	0.811400000000504	361.230011
54	1990-06-01	352.657142857143	16.6952380952381	0.129047619047619	17.5828571428571	0.817699999999604	358.019989
55	1990-07-01	361.820454545455	18.4540909090909	0.314285714285714	18.9328571428571	0.792699999999968	356.149994
56	1990-08-01	394.861363636364	27.3073913043478	0.691304347826087	27.7752173913043	0.759899999999106	322.559998
57	1990-09-01	389.56	33.5075	0.813684210526316	28.8189473684211	0.761699999999109	306.049988
58	1990-10-01	381.332608695652	36.0395652173913	0.841818181818182	30.1304347826087	0.739799999999377	304.0
59	1990-11-01	381.865909090909	32.3322727272727	0.7935	25.1066666666667	0.72400000000016	322.220001
60	1990-12-01	378.160526315789	27.281	0.761	22.5845	0.731999999999971	330.220001
61	1991-01-01	384.590909090909	25.2340909090909	0.967619047619048	26.9318181818182	0.736699999999473	343.929993
62	1991-02-01	363.7475	20.4775	0.987894736842105	22.1126315789474	0.722599999999147	367.070007
63	1991-03-01	363.39	19.9015	1.007	19.0410526315789	0.781800000000658	375.220001
64	1991-04-01	358.054761904762	20.83	1.09090909090909	18.9272727272727	0.826699999999619	375.339996
65	1991-05-01	357.116666666667	21.2322727272727	1.28409090909091	17.1804545454545	0.83389999999963	389.829987
66	1991-06-01	366.36	20.189	1.328	17.575	0.868700000000899	371.160004
67	1991-07-01	368.013043478261	21.4030434782609	1.355	17.6695454545455	0.870699999999488	387.809998
68	1991-08-01	356.721428571429	21.6936363636364	1.46681818181818	15.9327272727273	0.850300000000061	395.429993
69	1991-09-01	348.459523809524	21.8865	1.4685	17.029	0.828100000000632	387.859985
70	1991-10-01	358.826086956522	23.2308695652174	1.615	16.8463636363636	0.82550000000083	392.450012
71	1991-11-01	359.959523809524	22.4609523809524	1.85842105263158	17.6815	0.795000000000073	375.220001
72	1991-12-01	361.875	19.4980952380952	2.06285714285714	18.0185714285714	0.768099999999322	417.089996
73	1992-01-01	354.436363636364	18.7854545454545	2.07428571428571	17.4995454545455	0.770599999999831	408.779999
74	1992-02-01	353.8525	19.0125	2.12578947368421	17.0505263157895	0.791999999999462	412.700012
75	1992-03-01	344.640909090909	18.9218181818182	1.85727272727273	16.2227272727273	0.812700000000404	403.690002
76	1992-04-01	338.7275	20.23	2.13619047619048	16.1885714285714	0.804899999999179	414.950012
77	1992-05-01	337.039473684211	20.9755	2.1665	14.728	0.78859999999986	415.350006
78	1992-06-01	340.784090909091	22.3845454545455	2.21363636363636	14.7531818181818	0.767900000000736	408.140015
79	1992-07-01	352.452173913043	21.7752173913043	2.48909090909091	13.3036363636364	0.729799999999159	424.209991
80	1992-08-01	343.6025	21.3390476190476	2.39285714285714	14.422380952381	0.71349999999984	414.029999
81	1992-09-01	345.3	21.8813636363636	2.52095238095238	14.2585714285714	0.721999999999753	417.799988
82	1992-10-01	344.277272727273	21.6859090909091	2.50619047619048	17.4786363636364	0.755199999999604	418.679993
83	1992-11-01	334.923809523809	20.3385	2.29368421052632	14.594	0.807199999999284	431.350006
84	1992-12-01	334.657142857143	19.4140909090909	2.09636363636364	12.7813636363636	0.807199999999284	435.709991
85	1993-01-01	328.9925	19.032	2.21	12.584	0.825000000000728	438.779999
86	1993-02-01	329.31	20.0861111111111	2.16052631578947	13.6247368421053	0.845300000000861	443.380005
87	1993-03-01	329.973913043478	20.3221739130435	2.02565217391304	13.6930434782609	0.848400000000766	451.670013
88	1993-04-01	341.9475	20.2525	2.13190476190476	13.4566666666667	0.820100000000821	440.190002
89	1993-05-01	367.044736842105	19.9495	2.0585	13.4855	0.821200000000317	450.190002
90	1993-06-01	371.913636363636	19.0940909090909	1.79909090909091	12.9731818181818	0.844300000000658	450.529999
91	1993-07-01	392.034090909091	17.89	1.73333333333333	11.8695	0.878399999999601	448.130005
92	1993-08-01	379.795238095238	18.0109090909091	1.67636363636364	11.9531818181818	0.882400000000416	463.559998
93	1993-09-01	355.561363636364	17.5042857142857	1.51285714285714	12.649	0.848200000000361	458.929993
94	1993-10-01	364.004761904762	18.1533333333333	1.4595	11.3752380952381	0.859300000000076	467.829987
95	1993-11-01	373.938636363636	16.6090476190476	1.5685	13.3709523809524	0.885800000000018	461.790009
96	1993-12-01	383.242857142857	14.5147619047619	1.56181818181818	10.8645454545455	0.885800000000018	466.450012
97	1994-01-01	387.11	15.0266666666667	1.6105	10.6052380952381	0.897499999999127	481.609985
98	1994-02-01	381.6575	14.7810526315789	1.49894736842105	12.8852631578947	0.894800000000032	467.140015
99	1994-03-01	384.0	14.6808695652174	1.48739130434783	14.2408695652174	0.876000000000204	445.769989
100	1994-04-01	377.907894736842	16.42	1.42263157894737	15.3347368421053	0.877300000000105	450.910004

Rows: 1-100 | Columns: 7

Data Exploration and Preparation¶

Let's explore the data by displaying descriptive statistics of all the columns.

In [6]:

commodities.describe(method = "all", unique = True)

Out[6]:

	123 "Gold" Float 100%	123 "Oil" Float 100%	123 "Spread" Float 100%	123 "Vix" Float 100%	123 "Dol_Eur" Float 100%	123 "SP500" Float 100%	📅 "date" Date 100%
dtype	float	float	float	float	float	float	date
percent	100	100	100	100	100	100	100
count	416	416	416	416	416	416	416
top	1715.69736842105	94.7566666666667	0.0733333333333333	17.156	0.807199999999284	375.220001	1986-01-01
top_percent	0.24	0.24	0.481	0.24	0.481	0.481	0.24
avg	720.569809753815	44.0579252458624	1.08827531978751	20.1401719548448	0.849616545697939	1190.84896648798	[null]
stddev	470.8105631842	28.9281842560106	0.85864363632335	8.61242315019305	0.107742852952828	752.215482101544	[null]
min	256.197727272727	11.3472727272727	-0.413157894736842	8.02	0.634608695652174	211.779999	1986-01-01
approx_25%	356.899837662338	19.771525974026	0.304	14.0076190476191	0.770517047499652	467.657494	[null]
approx_50%	421.556011904762	31.4188612836439	1.00318181818181	18.06625	0.836329950325119	1132.5	[null]
approx_75%	1206.60686090226	62.6951035196687	1.80636363636364	23.9204642857142	0.900483152174247	1454.767487	[null]
max	1971.17	133.88	2.83421052631579	65.4465217391304	1.171642344	3500.310059	2020-08-01
range	1714.97227272727	122.532727272727	3.24736842105263	57.4265217391304	0.537033648347826	3288.53006	12631
empty	[null]	[null]	[null]	[null]	[null]	[null]	[null]
unique	413.0	416.0	416.0	416.0	413.0	416.0	414.0

Rows: 1-15 | Columns: 8

We have data from January 1986 to the beginning of August 2020. We don't have any missing values, so our data is already clean.

Let's draw the different variables.

In [8]:

%matplotlib inline
commodities.plot(ts = "date")

Out[8]:

<AxesSubplot:xlabel='"date"'>

Some of the commodities have an upward monotonic trend and some others might be stationary. Let's use Augmented Dickey-Fuller tests to check our hypotheses.

In [15]:

from verticapy.stats import adfuller
from verticapy import *

fuller = {}
for commodity in ["Gold", "Oil", "Spread", "Vix", "Dol_Eur", "SP500"]:
    result = adfuller(commodities,
                      column = commodity,
                      ts = "date",
                      p = 3,
                      with_trend = True)
    fuller["index"] = result["index"]
    fuller[commodity] = result["value"]
fuller = tablesample(fuller)
display(fuller)

	Gold	Oil	Spread	Vix	Dol_Eur	SP500
ADF Test Statistic	-0.8615535609119586	-2.8096937546469283	-2.4384304559428434	-4.930594978481499	-2.7980161815599516	-0.09469625542100038
p_value	0.389441781898794	0.00519866095306732	0.0151790361779463	1.19713909737974e-06	0.00538662270061962	0.924602810088177
# Lags used	3	3	3	3	3	3
# Observations Used	416	416	416	416	416	416
Critical Value (1%)	3.98	3.98	3.98	3.98	3.98	3.98
Critical Value (2.5%)	-3.68	-3.68	-3.68	-3.68	-3.68	-3.68
Critical Value (5%)	-3.42	-3.42	-3.42	-3.42	-3.42	-3.42
Critical Value (10%)	-3.13	-3.13	-3.13	-3.13	-3.13	-3.13
Stationarity (alpha = 1%)	❌	✅	❌	✅	✅	❌

Rows: 1-9 | Columns: 7

As expected: The price of gold and the S&P 500 index are not stationary. Let's use the Mann-Kendall test to confirm the trends.

In [26]:

from verticapy.stats import mkt

kendall = {}
for commodity in ["Gold", "SP500"]:
    result = mkt(commodities,
                 column = commodity,
                 ts = "date")
    kendall["index"] = result["index"]
    kendall[commodity] = result["value"]
kendall = tablesample(kendall)
display(kendall)

	Gold	SP500
Mann Kendall Test Statistic	15.001075433725964	24.886617341458717
S	42504.0	70513.0
STDS	2833.33019607669	2833.33001960591
p_value	7.223928538831201e-51	1.0387121156452156e-136
Monotonic Trend	✅	✅
Trend	increasing	increasing

Rows: 1-6 | Columns: 3

Our hypothesis is correct. We can also look at the correlation between the elapsed time and our variables to see the different trends.

In [27]:

import verticapy.stats as st
commodities["elapsed_days"] = commodities["date"] - st.min(commodities["date"])._over()
commodities.corr(focus = "elapsed_days")

Out[27]:

	"elapsed_days"
"elapsed_days"	1.0
"SP500"	0.912
"Gold"	0.83
"Oil"	0.712
"Spread"	0.178
"Dol_Eur"	-0.177
"Vix"	-0.123

Rows: 1-7 | Columns: 2

In the last plot, it's a bit hard to tell if 'Spread' is stationary. Let's draw it alone.

In [28]:

commodities["Spread"].plot(ts = "date")

Out[28]:

<AxesSubplot:xlabel='"date"', ylabel='"Spread"'>

We can see some sudden changes, so let's smooth the curve.

In [29]:

commodities.rolling(func = "avg",
                    window = (-20, 0),
                    columns = "Spread",
                    order_by = ["date"],
                    name = "Spread_smooth",)
commodities["Spread_smooth"].plot(ts = "date")

Out[29]:

<AxesSubplot:xlabel='"date"', ylabel='"Spread_smooth"'>

After each local minimum, there is a local maximum. Let's look at the number of lags needed to keep most of the information. To visualize this, we can draw the autocorrelation function (ACF) and partial autocorrelation function (PACF) plots.

In [31]:

from verticapy.learn.model_selection import plot_acf_pacf
plot_acf_pacf(commodities,
              column = "Spread",
              ts = "date",
              p = 60)

Out[31]:

	acf	pacf	confidence
0	1.0	1.0	0.09609514041450774
1	0.987	0.986665869482618	0.16516404714137115
2	0.966	-0.301561968384232	0.1703898204716388
3	0.945	0.0844166594141543	0.17098407552667444
4	0.92	-0.201885034178834	0.1733971035027304
5	0.895	0.061854739837099	0.17381378139741463
6	0.868	-0.0896785060630146	0.1744580710199262
7	0.838	-0.103305688149318	0.17524412731694863
8	0.805	-0.0877012026295132	0.17587100727343766
9	0.767	-0.146682496028717	0.17723645098664653
10	0.726	-0.0398106849633363	0.17753907309122982
11	0.685	0.0130943721290234	0.17776727092127834
12	0.645	0.0127675165343769	0.17799585214443142
13	0.605	-0.00712170836980866	0.17821926658370002
14	0.566	0.0475016337767778	0.17856158886339543
15	0.526	-0.0709243928306935	0.17905342712099498
16	0.482	-0.142238307190517	0.18035763635727056
17	0.438	0.0432517496025344	0.18068321561332523
18	0.395	0.00692374347448146	0.1809126197556603
19	0.352	-0.0363310815864315	0.18121082181014225
20	0.31	-0.0106532663190662	0.1814455468371735
21	0.269	-0.0595700363786228	0.18186493917383315
22	0.227	-0.0223965397593972	0.18212244130940808
23	0.186	0.0205203094149791	0.18237657192122106
24	0.146	-0.0162819277129079	0.18262327292378183
25	0.105	-0.0240767693418756	0.18288780094153362
26	0.066	-0.0316182193576195	0.18317588764089568
27	0.028	-0.02142984475672	0.18343590572251958
28	-0.01	-0.0373333006642763	0.18374725482094137
29	-0.044	0.0801603031651037	0.1843308503381982
30	-0.077	-0.0353584186206295	0.18463686572755553
31	-0.108	0.0329779347884827	0.18493518398481934
32	-0.137	0.0192058684952797	0.18519575467060828
33	-0.164	-0.0334815925429122	0.18549799053069815
34	-0.191	-0.0707134920083493	0.18601115955984251
35	-0.216	0.0635613269811052	0.18647368054930963
36	-0.238	-0.0111112909041713	0.1867255635874214
37	-0.258	0.0331751272182887	0.18703139473757538
38	-0.275	0.0191188141026162	0.18729846157410004
39	-0.29	-0.021908146018327	0.18757277864287758
40	-0.304	-0.0492601436920311	0.1879539922316871
41	-0.317	-0.0168810513162093	0.18821994086248556
42	-0.327	0.0759012399970719	0.1887851045856074
43	-0.335	-0.0163894554542288	0.18905263191850366
44	-0.34	0.0121754967609487	0.18931465071332243
45	-0.341	0.0612488311913758	0.18977441282629565
46	-0.339	0.0272526994881629	0.19007126527783125
47	-0.335	0.0566731012383067	0.19050423806891068
48	-0.329	-0.0144591821648624	0.1907743398221931
49	-0.323	0.00378181093551356	0.19103485719949814
50	-0.316	-0.0545835993103231	0.19145905457055504
51	-0.307	-0.00887910812158204	0.19172547565390927
52	-0.297	-0.0274702495033302	0.19203013025700777
53	-0.286	-0.0330624460927561	0.19235460115993974
54	-0.273	0.0285902162539611	0.19266512778188527
55	-0.259	-0.0541355970895344	0.193093365609904
56	-0.245	0.00195774368468057	0.19336157637058388
57	-0.231	-0.020444309022865	0.19365379142665426
58	-0.217	-0.0431945915171019	0.1940272796833102
59	-0.204	0.0208032553951857	0.19432280243198308
60	-0.19	-0.0103630013647603	0.19460149126928603

Rows: 1-61 | Columns: 4

We can clearly see the influence of the last two values on 'Spread,' which makes sense. When the curve slightly changes its direction, it will increase/decrease until reaching a new local maximum/minimum. Only the recent values can help the prediction in case of autoregressive periodical model. The local minimums of interest rate spreads are indicators of an economic crisis.

We saw the correlation between the price-per-barrel of Oil and the time. Let's look at the time series plot of this variable.

In [32]:

commodities["Oil"].plot(ts = "date")

Out[32]:

<AxesSubplot:xlabel='"date"', ylabel='"Oil"'>

Our chart shows effects of the 2010s oil glut and its lowest price in two decades with COVID-19.

In [33]:

plot_acf_pacf(commodities,
              column = "Oil",
              ts = "date",
              p = 60)

Out[33]: