Amazon¶

This example uses the 'Amazon' dataset to predict the number of forest fires in Brazil. You can download a copy of the Jupyter Notebook of the study here.

date: Date of the record
number: Number of forest fires
state: State in Brazil

We'll follow the data science cycle (Data Exploration - Data Preparation - Data Modeling - Model Evaluation - Model Deployment) to solve this problem, and we'll do it without ever loading our data into memory.

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_amazon
amazon = load_amazon()
amazon.head(5)

Out[3]:

	📅 date Date	Abc state Varchar(32)	123 number Int
1	1998-01-01	ACRE	0
2	1998-01-01	ALAGOAS	0
3	1998-01-01	AMAPÁ	0
4	1998-01-01	AMAZONAS	0
5	1998-01-01	BAHIA	0

Rows: 1-5 | Columns: 3

Data Exploration and Preparation¶

We can explore our data by displaying descriptive statistics of all the columns.

In [4]:

amazon.describe(method = "categorical", unique = True)

Out[4]:

	dtype	count	top	top_percent	unique
"date"	date	6454	2017-01-01	0.434	239.0
"state"	varchar(32)	6454	ALAGOAS	3.719	27.0
"number"	int	6454	0	9.56	1480.0

Rows: 1-3 | Columns: 6

Using the describe() method, we can see that our data ranges from the beginning of 1998 to the end of 2017.

In [5]:

amazon["date"].describe()

Out[5]:

	value
name	"date"
dtype	date
count	6454
min	1998-01-01
max	2017-11-01

Rows: 1-5 | Columns: 2

Brazil has dry and rainy seasons. Knowing this, we would expect that the frequency of forest fires vary between seasons. Let's confirm our hypothesis using an autocorrelation plot with 48 lags (4 years).

In [6]:

%matplotlib inline
amazon.acf(column = "number", 
           ts = "date",
           by = ["state"],
           p = 48)

Out[6]:

	value	confidence
0	1.0	0.024396841824873755
1	0.673	0.036275983687006595
2	0.349	0.045111637590579914
3	0.165	0.052306855578449836
4	0.03	0.057263854711292146
5	-0.042	0.06180006129512837
6	-0.06	0.06516081663993491
7	-0.045	0.06829524194541631
8	0.026	0.07124360828375358
9	0.148	0.07407557653279681
10	0.316	0.07669571841412227
11	0.563	0.07912720149833499
12	0.767	0.08102489031074944
13	0.592	0.08192275688166938
14	0.323	0.08272773065888367
15	0.144	0.08348307665310473
16	0.023	0.0842182150459408
17	-0.046	0.08492934300188434
18	-0.061	0.08560860089613088
19	-0.043	0.08624466587899496
20	0.026	0.08672523909375807
21	0.152	0.08691922124869496
22	0.332	0.08709516883010104
23	0.587	0.0872602406741454
24	0.778	0.08741685133673856
25	0.587	0.08757323051611797
26	0.31	0.08772142268465483
27	0.148	0.0878541726492689
28	0.016	0.08794631519454699
29	-0.051	0.08798551761309605
30	-0.065	0.08802107344145058
31	-0.05	0.08805320275459808
32	0.016	0.08808450865585683
33	0.136	0.08811343002364964
34	0.301	0.08813936389787239
35	0.575	0.08816388633991983
36	0.777	0.08818772410073934
37	0.598	0.08820895683521275
38	0.32	0.08822957354705878
39	0.157	0.0882489976063002
40	0.018	0.08826784670309079
41	-0.053	0.08828083535495473
42	-0.069	0.0882923062148065
43	-0.057	0.08830377916236357
44	0.001	0.08831425667709758
45	0.112	0.08832334504593417
46	0.262	0.08833197382451202
47	0.504	0.08834060445341575
48	0.675	0.08834750611745151

Rows: 1-49 | Columns: 3

The process is not stationary. Let's use a Dickey-Fuller test to confirm our hypothesis.

In [7]:

from verticapy.stats import adfuller
adfuller(amazon,
         ts = "date", 
         column = "number", 
         by = ["state"], 
         p = 48)

Out[7]:

	value
ADF Test Statistic	-1.4627032775079862
p_value	0.143610439349909
# Lags used	48
# Observations Used	6454
Critical Value (1%)	-3.43
Critical Value (2.5%)	-3.12
Critical Value (5%)	-2.86
Critical Value (10%)	-2.57
Stationarity (alpha = 1%)	❌

Rows: 1-9 | Columns: 2

The effects of each season seem pretty clear. We can see this graphically using the cumulative sum of the number of forest fires partitioned by states. If our hypothesis is correct, we should see staircase functions.

In [8]:

amazon.cumsum("number", 
              by = ["state"], 
              order_by = ["date"], 
              name = "cum_sum")
amazon["cum_sum"].plot(ts = "date", 
                       by = "state")

Out[8]:

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

We can clearly see the seasonality per state which contributes to a global seasonality. Let's draw the cumulative sum to see this more clearly.

In [9]:

import verticapy.stats as st
amazon = amazon.groupby(["date"], 
                        [st.sum(amazon["number"])._as("number")])
amazon.cumsum("number", 
              order_by = ["date"], 
              name = "cum_sum")
amazon["cum_sum"].plot(ts = "date")

Out[9]:

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

Machine Learning¶

Let's create an spatial autoregressive (SAR) model to predict the number of forest fires in Brazil. We know that this seasonality happens each year (s=12) and let's consider 4 lags (P=4).

In [10]:

from verticapy.learn.tsa import SARIMAX

model = SARIMAX("amazon_ar", 
                s = 12, 
                P = 4)
model.fit(amazon,
          y = "number",
          ts = "date")
model.regression_report()

Out[10]:

	value
explained_variance	0.735475202180538
max_error	53472.4790951474829625
median_absolute_error	2345.29902142932
mean_absolute_error	6098.51854215664
mean_squared_error	118668651.839548
root_mean_squared_error	10893.514209819896
r2	0.735475202180538
r2_adj	0.7297864968510872
aic	3561.3668585620294
bic	3577.3039013779385

Rows: 1-10 | Columns: 2

Our model is quite good. Let's look at our predictions.

In [11]:

x = model.plot(amazon, 
               nlead=100, 
               dynamic=True)

The plot shows that our model has successfully captured the seasonality implied by our data. Let's add the prediction in the vDataFrame.

In [12]:

amazon = model.predict(amazon, name = "prediction")
display(amazon)

	📅 date Date	123 cum_sum Integer	123 prediction Numeric(34,16)	123 number Integer
1	1998-01-01	0	[null]	0
2	1998-02-01	0	[null]	0
3	1998-03-01	0	[null]	0
4	1998-04-01	0	[null]	0
5	1998-05-01	0	[null]	0
6	1998-06-01	3551	[null]	3551
7	1998-07-01	11617	[null]	8066
8	1998-08-01	47166	[null]	35549
9	1998-09-01	89134	[null]	41968
10	1998-10-01	112629	[null]	23495
11	1998-11-01	119433	[null]	6804
12	1998-12-01	123881	[null]	4448
13	1999-01-01	124962	[null]	1081
14	1999-02-01	126246	[null]	1284
15	1999-03-01	126913	[null]	667
16	1999-04-01	127630	[null]	717
17	1999-05-01	129442	[null]	1812
18	1999-06-01	133074	[null]	3632
19	1999-07-01	141830	[null]	8756
20	1999-08-01	181316	[null]	39486
21	1999-09-01	218229	[null]	36913
22	1999-10-01	245241	[null]	27012
23	1999-11-01	254101	[null]	8860
24	1999-12-01	258477	[null]	4376
25	2000-01-01	259255	[null]	778
26	2000-02-01	259816	[null]	561
27	2000-03-01	260664	[null]	848
28	2000-04-01	261201	[null]	537
29	2000-05-01	263298	[null]	2097
30	2000-06-01	269573	[null]	6275
31	2000-07-01	274312	[null]	4739
32	2000-08-01	296514	[null]	22202
33	2000-09-01	319805	[null]	23291
34	2000-10-01	347141	[null]	27336
35	2000-11-01	355540	[null]	8399
36	2000-12-01	360005	[null]	4465
37	2001-01-01	360552	[null]	547
38	2001-02-01	361611	[null]	1059
39	2001-03-01	362879	[null]	1268
40	2001-04-01	363960	[null]	1081
41	2001-05-01	366050	[null]	2090
42	2001-06-01	374483	[null]	8433
43	2001-07-01	380973	[null]	6490
44	2001-08-01	412860	[null]	31887
45	2001-09-01	452694	[null]	39834
46	2001-10-01	483732	[null]	31038
47	2001-11-01	499371	[null]	15639
48	2001-12-01	505572	[null]	6201
49	2002-01-01	507226	2316.441403385607	1654
50	2002-02-01	508796	2432.2522086194726	1570
51	2002-03-01	510475	2372.9891920446694	1679
52	2002-04-01	512157	2210.1984530467385	1682
53	2002-05-01	515975	3560.923815872453	3818
54	2002-06-01	526814	7369.910334009876	10839
55	2002-07-01	540565	7771.160774847538	13751
56	2002-08-01	597716	31142.45470770594	57151
57	2002-09-01	653519	32092.92299971871	55803
58	2002-10-01	701241	29428.73986256616	47722
59	2002-11-01	729420	11848.871124101008	28179
60	2002-12-01	741364	6222.514063438655	11944
61	2003-01-01	746455	2288.5907656560316	5091
62	2003-02-01	748853	2386.2497467077687	2398
63	2003-03-01	751602	2669.304291959208	2749
64	2003-04-01	754279	2465.8488608210528	2677
65	2003-05-01	756026	3949.205715373259	1747
66	2003-06-01	762532	9923.07893534352	6506
67	2003-07-01	774336	8788.013997465541	11804
68	2003-08-01	818072	34822.96150714659	43736
69	2003-09-01	894397	38546.623177400485	76325
70	2003-10-01	937692	35117.18915152546	43295
71	2003-11-01	961264	17826.577704133844	23572
72	2003-12-01	976606	8485.212699766464	15342
73	2004-01-01	979311	3586.2618738419033	2705
74	2004-02-01	980566	3074.315430791295	1255
75	2004-03-01	982606	3265.9031950005697	2040
76	2004-04-01	983941	3204.759017434466	1335
77	2004-05-01	987476	4133.647584518473	3535
78	2004-06-01	1001738	10438.460402649542	14262
79	2004-07-01	1025547	12444.998071670168	23809
80	2004-08-01	1074872	47514.40748404308	49325
81	2004-09-01	1158372	58250.16797498169	83500
82	2004-10-01	1198703	42556.73396601229	40331
83	2004-11-01	1229466	24861.420765111307	30763
84	2004-12-01	1246990	12488.713081512751	17524
85	2005-01-01	1251980	4898.984236199336	4990
86	2005-02-01	1254133	3288.9640891464164	2153
87	2005-03-01	1255839	3664.8276375724126	1706
88	2005-04-01	1256850	3470.643367507149	1011
89	2005-05-01	1260060	4363.786056925852	3210
90	2005-06-01	1265871	11293.403930382246	5811
91	2005-07-01	1281534	17315.99878246324	15663
92	2005-08-01	1333515	51992.889918420115	51981
93	2005-09-01	1409772	73577.59183873676	76257
94	2005-10-01	1459648	45954.84587055446	49876
95	2005-11-01	1481400	28885.01358775507	21752
96	2005-12-01	1487754	16559.253134356753	6354
97	2006-01-01	1491009	5676.858923746963	3255
98	2006-02-01	1492675	3299.071810075103	1666
99	2006-03-01	1494449	3647.2478998427196	1774
100	2006-04-01	1495241	3137.2990214293236	792

Rows: 1-100 | Columns: 4

From here, we can use a time series plot to compare our prediction with the real values.

In [13]:

amazon.plot(ts = "date", 
            columns = ["number", "prediction"])

Out[13]:

<AxesSubplot:xlabel='"date"'>