COVID-19¶

This example uses the covid19 dataset to predict the number of deaths and cases one day in advance.

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 vastorbit:

import vastorbit as vo

vo.__version__

Connect to VAST. This example uses an existing connection called VASTDSN. 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.

vo.connect("VASTDSN")

Let’s create a VastFrame of the dataset. The dataset is available here.

from vastorbit.datasets import load_commodities

covid19 = vo.read_csv("deaths.csv")
covid19

	📅 date Date 100%	Abc county Varchar(50) 100%	Abc state Varchar(50) 100%	123 fips Integer 98%	123 cases Integer 100%	123 deaths Integer 100%
1	2020-03-12	Volusia	Florida	12127	3	0
2	2020-03-12	Wake	North Carolina	37183	8	0
3	2020-03-12	Walla Walla	Washington	53071	1	0
4	2020-03-12	Ward	North Dakota	38101	1	0
5	2020-03-12	Washington	Oregon	41067	10	0
6	2020-03-12	Washington	Utah	49053	1	0
7	2020-03-12	Washoe	Nevada	32031	2	0
8	2020-03-12	Washtenaw	Michigan	26161	2	0
9	2020-03-12	Waukesha	Wisconsin	55133	1	0
10	2020-03-12	Wayne	Michigan	26163	1	0
11	2020-03-12	Wayne	Pennsylvania	42127	1	0
12	2020-03-12	Weber	Utah	49057	1	0
13	2020-03-12	Westchester	New York	36119	147	0
14	2020-03-12	Whatcom	Washington	53073	1	0
15	2020-03-12	Williamson	Tennessee	47187	8	0
16	2020-03-12	Worcester	Massachusetts	25027	2	0
17	2020-03-12	Wyandotte	Kansas	20209	1	1
18	2020-03-12	Yakima	Washington	53077	2	0
19	2020-03-12	Yolo	California	6113	1	0
20	2020-03-13	Ada	Idaho	16001	1	0

Data Exploration and Preparation¶

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

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

	dtype	count	top	top_percent	unique
"date"	date	129747	2020-05-09	2.244	110.0
"county"	varchar(50)	129747	Washington	1.15	1713.0
"state"	varchar(50)	129747	Texas	6.633	55.0
"fips"	integer	128256	[null]	1.149	2882.0
"cases"	integer	129747	1	13.303	3903.0
"deaths"	integer	129747	0	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(50) 100%	123 cases Integer 100%	123 deaths Integer 100%
1	2020-03-12	Florida	3	0
2	2020-03-12	North Carolina	8	0
3	2020-03-12	Washington	1	0
4	2020-03-12	North Dakota	1	0
5	2020-03-12	Oregon	10	0
6	2020-03-12	Utah	1	0
7	2020-03-12	Nevada	2	0
8	2020-03-12	Michigan	2	0
9	2020-03-12	Wisconsin	1	0
10	2020-03-12	Michigan	1	0
11	2020-03-12	Pennsylvania	1	0
12	2020-03-12	Utah	1	0
13	2020-03-12	New York	147	0
14	2020-03-12	Washington	1	0
15	2020-03-12	Tennessee	8	0
16	2020-03-12	Massachusetts	2	0
17	2020-03-12	Kansas	1	1
18	2020-03-12	Washington	2	0
19	2020-03-12	California	1	0
20	2020-03-13	Idaho	1	0

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

import vastorbit.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(50) 100%	📅 date Date 100%	123 deaths Bigint 100%	123 cases Bigint 100%
1	District of Columbia	2020-04-23	139	3361
2	Massachusetts	2020-04-23	2360	46023
3	Pennsylvania	2020-05-09	3793	58661
4	Virginia	2020-05-09	827	23196
5	New York	2020-04-16	15669	225761
6	Indiana	2020-04-16	477	9542
7	Kansas	2020-04-16	80	1595
8	Kentucky	2020-04-16	132	2494
9	Massachusetts	2020-04-16	1245	32181
10	Florida	2020-04-17	725	24745

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 vastorbit.machine_learning.model_selection.statistical_tests import adfuller

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

	value
ADF Test Statistic	0.02032251874539726
p_value	0.9837874324401557
# Lags used	12
# Observations Used	3039
Critical Value (1%)	-3.43
Critical Value (2.5%)	-3.12
Critical Value (5%)	-2.86
Critical Value (10%)	-2.57
Stationarity (alpha = 1%)	False

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"])

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(50) 100%	📅 date Date 100%	123 deaths Bigint 100%	123 cases Bigint 100%	123 state_Alaska Bool 100%	123 state_Connecticut Bool 100%	123 state_Delaware Bool 100%	123 state_Illinois Bool 100%	123 state_Louisiana Bool 100%	...	123 state_Nevada Bool 100%	123 state_New_Hampshire Bool 100%	123 state_New_York Bool 100%	123 state_North_Carolina Bool 100%	123 state_North_Dakota Bool 100%	123 state_Oklahoma Bool 100%	123 state_Oregon Bool 100%	123 state_Tennessee Bool 100%	123 state_Virgin_Islands Bool 100%	123 state_West_Virginia Bool 100%
1	Tennessee	2020-03-28	6	1363	0	0	0	0	0	...	0	0	0	0	0	0	0	1	0	0
2	New York	2020-03-28	935	53517	0	0	0	0	0	...	0	0	1	0	0	0	0	0	0	0
3	West Virginia	2020-03-28	0	113	0	0	0	0	0	...	0	0	0	0	0	0	0	0	0	1
4	Oklahoma	2020-03-29	16	429	0	0	0	0	0	...	0	0	0	0	0	1	0	0	0	0
5	Massachusetts	2020-03-29	48	4955	0	0	0	0	0	...	0	0	0	0	0	0	0	0	0	0
6	Louisiana	2020-04-01	279	6424	0	0	0	0	1	...	0	0	0	0	0	0	0	0	0	0
7	Nebraska	2020-04-01	5	226	0	0	0	0	0	...	0	0	0	0	0	0	0	0	0	0
8	New Hampshire	2020-03-26	1	158	0	0	0	0	0	...	0	1	0	0	0	0	0	0	0	0
9	Delaware	2020-03-26	1	143	0	0	1	0	0	...	0	0	0	0	0	0	0	0	0	0
10	Virgin Islands	2020-03-26	0	17	0	0	0	0	0	...	0	0	0	0	0	0	0	0	1	0
11	Montana	2020-03-27	1	121	0	0	0	0	0	...	0	0	0	0	0	0	0	0	0	0
12	North Carolina	2020-04-12	86	4520	0	0	0	0	0	...	0	0	0	1	0	0	0	0	0	0
13	North Dakota	2020-04-12	8	308	0	0	0	0	0	...	0	0	0	0	1	0	0	0	0	0
14	Connecticut	2020-04-12	554	12035	0	1	0	0	0	...	0	0	0	0	0	0	0	0	0	0
15	Alaska	2020-04-12	6	270	1	0	0	0	0	...	0	0	0	0	0	0	0	0	0	0
16	North Carolina	2020-04-13	91	4788	0	0	0	0	0	...	0	0	0	1	0	0	0	0	0	0
17	Nevada	2020-04-13	114	3036	0	0	0	0	0	...	1	0	0	0	0	0	0	0	0	0
18	Illinois	2020-03-30	84	5125	0	0	0	1	0	...	0	0	0	0	0	0	0	0	0	0
19	Oregon	2020-03-30	16	607	0	0	0	0	0	...	0	0	0	0	0	0	1	0	0	0
20	Michigan	2020-04-29	3670	40361	0	0	0	0	0	...	0	0	0	0	0	0	0	0	0	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"]])
covid19["elapsed_days"] = "EXTRACT(DAY FROM {})"

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

print(covid19.current_relation())

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

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 = vo.read_csv("deaths.csv").groupby(
    ["date"],
    [
        fun.sum(covid19["deaths"])._as("deaths"),
        fun.sum(covid19["cases"])._as("cases"),
    ],
).search("date > CAST('2020-01-04' AS DATE)")

Machine Learning¶

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

from vastorbit.machine_learning.vast.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.8207904673903647	0.8604025086316581

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

Cases:¶

model.plot(
    covid19,
    npredictions = 100,
    idx = 0,
    method="forecast",
)

Deaths:¶

model.plot(
    covid19,
    npredictions = 100,
    idx = 1,
    method="forecast",
)

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!