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:

import verticapy as vp

vp.__version__
Out[2]: '1.1.0'

Connect to Vertica. This example uses an existing connection called VerticaDSN. For details on how to create a connection, see the Connection tutorial. You can skip the below cell if you already have an established connection.

vp.connect("VerticaDSN")

Let’s create a Virtual DataFrame of the dataset.

from verticapy.datasets import load_amazon

amazon = load_amazon()
amazon.head(5)

	📅 date Date 100%	...	Abc state Varchar(32) 100%	123 number Int 100%
1	1998-01-01	...	AMAPÁ	0
2	1998-01-01	...	AMAZONAS	0
3	1998-01-01	...	DISTRITO FEDERAL	0
4	1998-01-01	...	ESPÍRITO SANTO	0
5	1998-01-01	...	MARANHÃO	0

Data Exploration and Preparation¶

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

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

	...	top_percent	unique
"date"	...	0.434	239.0
"state"	...	3.719	27.0
"number"	...	9.56	1480.0

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

amazon["date"].describe()

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

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).

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

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

from verticapy.machine_learning.model_selection.statistical_tests import adfuller

adfuller(
    amazon,
    ts = "date",
    column = "number",
    by = ["state"],
    p = 48,
)

	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%)	❌

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.

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

We can clearly observe the seasonality within each state, which contributes to an overall global seasonality. Let’s plot the total number of forest fires to illustrate this more clearly.

import verticapy.sql.functions as fun

amazon = amazon.groupby(
    ["date"],
    [
        fun.sum(amazon["number"])._as("number"),
    ],
)
amazon["number"].plot(ts = "date")

Although it would be preferable to use seasonal decomposition and predict the residuals, let’s build an ARIMA model on the data.

Machine Learning¶

Since the seasonality occurs monthly, we set p = 12. There is no trend in the data, and we observe some moving average in the residuals, so q should be around 2. Let’s proceed with building the model.

from verticapy.machine_learning.vertica import ARIMA

model = ARIMA(
    order = (12, 0, 2),
    missing = "drop",
)
model.fit(
    amazon,
    y = "number",
    ts = "date",
)
model.regression_report(start = 50)

	value
explained_variance	0.736312610167603
max_error	59136.6749123761
median_absolute_error	2327.80445032527
mean_absolute_error	5986.61401134727
mean_squared_error	119008352.86804
root_mean_squared_error	10909.0949609965
r2	0.736119043052252
r2_adj	0.734707914940232
aic	3518.5066283893
bic	3524.8825955377

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

model.plot(
    vdf = amazon,
    ts = "date",
    y = "number",
    npredictions = 40,
    method = "auto",
)

The plot shows that our model has successfully captured the seasonality present in the data. However, to improve the model, we should remove the seasonality and focus on predicting the residuals directly. The current model is not entirely stable and requires further adjustments.

Conclusion¶

We’ve solved our problem in a Pandas-like way, all without ever loading data into memory!