COVID-19¶

This example uses the covid19 dataset to predict the number of deaths and cases one day in advance. You can download the Jupyter Notebook of the study here.

date: Date of the record.
cases: Number of people infected.
deaths: Number of deaths.
state: State.
fips: The Federal Information Processing Standards (FIPS) code for the county.
county: County.

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:

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. The dataset is available here.

from verticapy.datasets import load_commodities

covid19 = vp.read_csv("deaths.csv")
covid19.head(10)

Data Exploration and Preparation¶

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

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

	...	top_percent	unique
"date"	...	2.244	110.0
"county"	...	1.15	1713.0
"state"	...	6.633	55.0
"fips"	...	1.149	2883.0
"cases"	...	13.303	3903.0
"deaths"	...	60.989	851.0

We have data from January 2020 to the beginning of May.

covid19["date"].describe()

	value
name	"date"
dtype	date
count	129747
min	2020-01-21
max	2020-05-09

We’ll try to predict the number of future deaths by using the statistics from previous days. We can drop the columns county and fips, since the scope of our analysis is focused on the United States and the FIPS code isn’t relevant to our predictions.

covid19.drop(["fips", "county"])

	📅 date Date 100%	...	Abc state Varchar(48) 100%	123 deaths Int 100%
1	2020-01-25	...	California	0
2	2020-01-29	...	California	0
3	2020-01-29	...	Washington	0
4	2020-01-30	...	California	0
5	2020-01-31	...	California	0
6	2020-01-31	...	Arizona	0
7	2020-02-01	...	Illinois	0
8	2020-02-01	...	California	0
9	2020-02-02	...	California	0
10	2020-02-02	...	Washington	0
11	2020-02-03	...	California	0
12	2020-02-05	...	California	0
13	2020-02-05	...	California	0
14	2020-02-05	...	Washington	0
15	2020-02-06	...	California	0
16	2020-02-08	...	Illinois	0
17	2020-02-08	...	California	0
18	2020-02-08	...	Washington	0
19	2020-02-09	...	Illinois	0
20	2020-02-09	...	California	0

Let’s sum the number of deaths and cases by state and date.

import verticapy.sql.functions as fun

covid19 = covid19.groupby(
    [
        "state",
        "date",
    ],
    [
        fun.sum(covid19["deaths"])._as("deaths"),
        fun.sum(covid19["cases"])._as("cases"),
    ],
)
covid19.head(10)

	Abc state Varchar(48) 100%	...	123 deaths Integer 100%	123 cases Integer 100%
1	Iowa	...	6	424
2	Indiana	...	6	201
3	South Carolina	...	150	4917
4	Hawaii	...	9	497
5	Idaho	...	44	1577
6	Florida	...	23	1971
7	Florida	...	1267	33683
8	Utah	...	0	40
9	Utah	...	32	3301
10	Kansas	...	133	4305

Let’s look at the autocorrelation graphic of the number of deaths.

covid19.acf(
    column = "deaths",
    ts = "date",
    by = ["state"],
    p = 24,
)

The process doesn’t seem to be stationary. Let’s use a Dickey-Fuller test to confirm our hypothesis.

from verticapy.machine_learning.model_selection.statistical_tests import adfuller

adfuller(
    covid19,
    ts = "date",
    column = "deaths",
    by = ["state"],
    p = 12,
)

	value
ADF Test Statistic	0.020308057561120422
p_value	0.98379896746093
# Lags used	12
# Observations Used	3754
Critical Value (1%)	-3.43
Critical Value (2.5%)	-3.12
Critical Value (5%)	-2.86
Critical Value (10%)	-2.57
Stationarity (alpha = 1%)	❌

We can look at the cumulative number of deaths and its exponentiality.

covid19["deaths"].plot(
    ts = "date",
    by = "state",
)

Let’s plot this for the entire country.

covid = covid19.groupby(
    ["date"],
    [fun.sum(covid19["deaths"])._as("deaths")],
)
covid["deaths"].plot(ts = "date")

As you would expect, there’s a clear correlation between the number of people infected and the number of deaths.

covid19.corr(["deaths", "cases"])
Out[3]: 0.980134457432841

A vector autoregression (VAR) model can be very good to do the predictions. But first, let’s encode the states to look at their influence.

covid19["state"].one_hot_encode()

	Abc state Varchar(48) 100%	...	📅 date Date 100%	123 state_Wisconsin Bool 100%
1	North Dakota	...	2020-03-22	0
2	Kentucky	...	2020-04-09	0
3	Wisconsin	...	2020-05-05	1
4	Nebraska	...	2020-03-25	0
5	Colorado	...	2020-04-29	0
6	Washington	...	2020-03-03	0
7	Washington	...	2020-01-27	0
8	District of Columbia	...	2020-03-10	0
9	Puerto Rico	...	2020-04-29	0
10	Iowa	...	2020-03-27	0
11	New Hampshire	...	2020-04-07	0
12	Tennessee	...	2020-04-11	0
13	New Mexico	...	2020-04-20	0
14	Georgia	...	2020-03-21	0
15	Louisiana	...	2020-03-10	0
16	Pennsylvania	...	2020-03-14	0
17	Mississippi	...	2020-03-17	0
18	Kentucky	...	2020-03-13	0
19	Hawaii	...	2020-03-13	0
20	Wyoming	...	2020-03-18	0

Because of the upward monotonic trend, we can also look at the correlation between the days elapsed and the number of cases.

covid19["elapsed_days"] = covid19["date"] - fun.min(covid19["date"])._over(by = [covid19["state"]])

We can generate the SQL code of the vDataFrame to see what happens behind the scenes when we modify our data from within the vDataFrame.

print(covid19.current_relation())
(
   SELECT
     "state",
     "date",
     "deaths",
     "cases",
     DECODE("state_Alabama", 'Alabama', 1, 0) AS "state_Alabama",
     DECODE("state_Alaska", 'Alaska', 1, 0) AS "state_Alaska",
     DECODE("state_Arizona", 'Arizona', 1, 0) AS "state_Arizona",
     DECODE("state_Arkansas", 'Arkansas', 1, 0) AS "state_Arkansas",
     DECODE("state_California", 'California', 1, 0) AS "state_California",
     DECODE("state_Colorado", 'Colorado', 1, 0) AS "state_Colorado",
     DECODE("state_Connecticut", 'Connecticut', 1, 0) AS "state_Connecticut",
     DECODE("state_Delaware", 'Delaware', 1, 0) AS "state_Delaware",
     DECODE("state_District_of_Columbia", 'District of Columbia', 1, 0) AS "state_District_of_Columbia",
     DECODE("state_Florida", 'Florida', 1, 0) AS "state_Florida",
     DECODE("state_Georgia", 'Georgia', 1, 0) AS "state_Georgia",
     DECODE("state_Guam", 'Guam', 1, 0) AS "state_Guam",
     DECODE("state_Hawaii", 'Hawaii', 1, 0) AS "state_Hawaii",
     DECODE("state_Idaho", 'Idaho', 1, 0) AS "state_Idaho",
     DECODE("state_Illinois", 'Illinois', 1, 0) AS "state_Illinois",
     DECODE("state_Indiana", 'Indiana', 1, 0) AS "state_Indiana",
     DECODE("state_Iowa", 'Iowa', 1, 0) AS "state_Iowa",
     DECODE("state_Kansas", 'Kansas', 1, 0) AS "state_Kansas",
     DECODE("state_Kentucky", 'Kentucky', 1, 0) AS "state_Kentucky",
     DECODE("state_Louisiana", 'Louisiana', 1, 0) AS "state_Louisiana",
     DECODE("state_Maine", 'Maine', 1, 0) AS "state_Maine",
     DECODE("state_Maryland", 'Maryland', 1, 0) AS "state_Maryland",
     DECODE("state_Massachusetts", 'Massachusetts', 1, 0) AS "state_Massachusetts",
     DECODE("state_Michigan", 'Michigan', 1, 0) AS "state_Michigan",
     DECODE("state_Minnesota", 'Minnesota', 1, 0) AS "state_Minnesota",
     DECODE("state_Mississippi", 'Mississippi', 1, 0) AS "state_Mississippi",
     DECODE("state_Missouri", 'Missouri', 1, 0) AS "state_Missouri",
     DECODE("state_Montana", 'Montana', 1, 0) AS "state_Montana",
     DECODE("state_Nebraska", 'Nebraska', 1, 0) AS "state_Nebraska",
     DECODE("state_Nevada", 'Nevada', 1, 0) AS "state_Nevada",
     DECODE("state_New_Hampshire", 'New Hampshire', 1, 0) AS "state_New_Hampshire",
     DECODE("state_New_Jersey", 'New Jersey', 1, 0) AS "state_New_Jersey",
     DECODE("state_New_Mexico", 'New Mexico', 1, 0) AS "state_New_Mexico",
     DECODE("state_New_York", 'New York', 1, 0) AS "state_New_York",
     DECODE("state_North_Carolina", 'North Carolina', 1, 0) AS "state_North_Carolina",
     DECODE("state_North_Dakota", 'North Dakota', 1, 0) AS "state_North_Dakota",
     DECODE("state_Northern_Mariana_Islands", 'Northern Mariana Islands', 1, 0) AS "state_Northern_Mariana_Islands",
     DECODE("state_Ohio", 'Ohio', 1, 0) AS "state_Ohio",
     DECODE("state_Oklahoma", 'Oklahoma', 1, 0) AS "state_Oklahoma",
     DECODE("state_Oregon", 'Oregon', 1, 0) AS "state_Oregon",
     DECODE("state_Pennsylvania", 'Pennsylvania', 1, 0) AS "state_Pennsylvania",
     DECODE("state_Puerto_Rico", 'Puerto Rico', 1, 0) AS "state_Puerto_Rico",
     DECODE("state_Rhode_Island", 'Rhode Island', 1, 0) AS "state_Rhode_Island",
     DECODE("state_South_Carolina", 'South Carolina', 1, 0) AS "state_South_Carolina",
     DECODE("state_South_Dakota", 'South Dakota', 1, 0) AS "state_South_Dakota",
     DECODE("state_Tennessee", 'Tennessee', 1, 0) AS "state_Tennessee",
     DECODE("state_Texas", 'Texas', 1, 0) AS "state_Texas",
     DECODE("state_Utah", 'Utah', 1, 0) AS "state_Utah",
     DECODE("state_Vermont", 'Vermont', 1, 0) AS "state_Vermont",
     DECODE("state_Virgin_Islands", 'Virgin Islands', 1, 0) AS "state_Virgin_Islands",
     DECODE("state_Virginia", 'Virginia', 1, 0) AS "state_Virginia",
     DECODE("state_Washington", 'Washington', 1, 0) AS "state_Washington",
     DECODE("state_West_Virginia", 'West Virginia', 1, 0) AS "state_West_Virginia",
     DECODE("state_Wisconsin", 'Wisconsin', 1, 0) AS "state_Wisconsin",
     ("date") - (MIN("date") OVER (PARTITION BY "state" )) AS "elapsed_days" 
   FROM
 (
                
   SELECT
     
                    "state",
     "date",
     "deaths",
     "cases",
     "state" AS "state_Alabama",
     "state" AS "state_Alaska",
     "state" AS "state_Arizona",
     "state" AS "state_Arkansas",
     "state" AS "state_California",
     "state" AS "state_Colorado",
     "state" AS "state_Connecticut",
     "state" AS "state_Delaware",
     "state" AS "state_District_of_Columbia",
     "state" AS "state_Florida",
     "state" AS "state_Georgia",
     "state" AS "state_Guam",
     "state" AS "state_Hawaii",
     "state" AS "state_Idaho",
     "state" AS "state_Illinois",
     "state" AS "state_Indiana",
     "state" AS "state_Iowa",
     "state" AS "state_Kansas",
     "state" AS "state_Kentucky",
     "state" AS "state_Louisiana",
     "state" AS "state_Maine",
     "state" AS "state_Maryland",
     "state" AS "state_Massachusetts",
     "state" AS "state_Michigan",
     "state" AS "state_Minnesota",
     "state" AS "state_Mississippi",
     "state" AS "state_Missouri",
     "state" AS "state_Montana",
     "state" AS "state_Nebraska",
     "state" AS "state_Nevada",
     "state" AS "state_New_Hampshire",
     "state" AS "state_New_Jersey",
     "state" AS "state_New_Mexico",
     "state" AS "state_New_York",
     "state" AS "state_North_Carolina",
     "state" AS "state_North_Dakota",
     "state" AS "state_Northern_Mariana_Islands",
     "state" AS "state_Ohio",
     "state" AS "state_Oklahoma",
     "state" AS "state_Oregon",
     "state" AS "state_Pennsylvania",
     "state" AS "state_Puerto_Rico",
     "state" AS "state_Rhode_Island",
     "state" AS "state_South_Carolina",
     "state" AS "state_South_Dakota",
     "state" AS "state_Tennessee",
     "state" AS "state_Texas",
     "state" AS "state_Utah",
     "state" AS "state_Vermont",
     "state" AS "state_Virgin_Islands",
     "state" AS "state_Virginia",
     "state" AS "state_Washington",
     "state" AS "state_West_Virginia",
     "state" AS "state_Wisconsin" 
                
   FROM
 (
   SELECT
     "state",
     "date",
     (SUM("deaths")) AS deaths,
     (SUM("cases")) AS cases 
   FROM
 ( 
   SELECT
     "date",
     "state",
     "cases",
     "deaths" 
   FROM
 "v_temp_schema"."_verticapy_tmp_deaths_v_mldb_29a6b70297bb11efa8720242ac120002_") 
VERTICAPY_SUBTABLE 
   GROUP BY 1,
     2) 
VERTICAPY_SUBTABLE) 
VERTICAPY_SUBTABLE) 
VERTICAPY_SUBTABLE

The vDataFrame memorizes all of our operations on the data to dynamically generate the correct SQL statement and passes computation and aggregation to Vertica.

Let’s see the correlation between the number of deaths and the other variables.

covid19.corr(focus = "deaths")

We can see clearly a high correlation for some variables. We can use them to compute a SARIMAX model, but we’ll stick to a VAR model for this study.

Let’s compute the total number of deaths and cases to create our VAR model.

covid19 = vp.read_csv("deaths.csv").groupby(
    ["date"],
    [
        fun.sum(covid19["deaths"])._as("deaths"),
        fun.sum(covid19["cases"])._as("cases"),
    ],
).search("date > '04-01-2020'")

Machine Learning¶

Let’s create a VAR model to predict the number of COVID-19 deaths and cases in the USA.

from verticapy.machine_learning.vertica.tsa import VAR

model = VAR(p = 3)
model.fit(
    covid19,
    ts = "date",
    y = ["cases", "deaths"],
    return_report = True,
)
model.score(start = 20)

	...	"cases"	"deaths"
r2	...	0.686208217269015	0.50498353928935

Our model is not bad. Let’s predict the number of deaths in a near future.

Cases:¶

model.plot(
    covid19,
    start = 37,
    npredictions = 10,
    idx = 0,
)

Deaths:¶

model.plot(
    covid19,
    start = 37,
    npredictions = 10,
    idx = 1,
)

The model performs well but may be somewhat unstable. To improve it, we could apply data preparation techniques, such as seasonal decomposition, before building the VAR model.

Conclusion¶

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