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:

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

In [3]:

covid19 = vp.read_csv("data/covid19_deaths.csv")
display(covid19)

	📅 date Date	Abc county Varchar(66)	Abc state Varchar(48)	123 fips Int	123 cases Int	123 deaths Int
1	2020-01-21	Snohomish	Washington	53061	1	0
2	2020-01-22	Snohomish	Washington	53061	1	0
3	2020-01-23	Snohomish	Washington	53061	1	0
4	2020-01-24	Cook	Illinois	17031	1	0
5	2020-01-24	Snohomish	Washington	53061	1	0
6	2020-01-25	Cook	Illinois	17031	1	0
7	2020-01-25	Orange	California	6059	1	0
8	2020-01-25	Snohomish	Washington	53061	1	0
9	2020-01-26	Cook	Illinois	17031	1	0
10	2020-01-26	Los Angeles	California	6037	1	0
11	2020-01-26	Maricopa	Arizona	4013	1	0
12	2020-01-26	Orange	California	6059	1	0
13	2020-01-26	Snohomish	Washington	53061	1	0
14	2020-01-27	Cook	Illinois	17031	1	0
15	2020-01-27	Los Angeles	California	6037	1	0
16	2020-01-27	Maricopa	Arizona	4013	1	0
17	2020-01-27	Orange	California	6059	1	0
18	2020-01-27	Snohomish	Washington	53061	1	0
19	2020-01-28	Cook	Illinois	17031	1	0
20	2020-01-28	Los Angeles	California	6037	1	0
21	2020-01-28	Maricopa	Arizona	4013	1	0
22	2020-01-28	Orange	California	6059	1	0
23	2020-01-28	Snohomish	Washington	53061	1	0
24	2020-01-29	Cook	Illinois	17031	1	0
25	2020-01-29	Los Angeles	California	6037	1	0
26	2020-01-29	Maricopa	Arizona	4013	1	0
27	2020-01-29	Orange	California	6059	1	0
28	2020-01-29	Snohomish	Washington	53061	1	0
29	2020-01-30	Cook	Illinois	17031	2	0
30	2020-01-30	Los Angeles	California	6037	1	0
31	2020-01-30	Maricopa	Arizona	4013	1	0
32	2020-01-30	Orange	California	6059	1	0
33	2020-01-30	Snohomish	Washington	53061	1	0
34	2020-01-31	Cook	Illinois	17031	2	0
35	2020-01-31	Los Angeles	California	6037	1	0
36	2020-01-31	Maricopa	Arizona	4013	1	0
37	2020-01-31	Orange	California	6059	1	0
38	2020-01-31	Santa Clara	California	6085	1	0
39	2020-01-31	Snohomish	Washington	53061	1	0
40	2020-02-01	Cook	Illinois	17031	2	0
41	2020-02-01	Los Angeles	California	6037	1	0
42	2020-02-01	Maricopa	Arizona	4013	1	0
43	2020-02-01	Orange	California	6059	1	0
44	2020-02-01	Santa Clara	California	6085	1	0
45	2020-02-01	Snohomish	Washington	53061	1	0
46	2020-02-01	Suffolk	Massachusetts	25025	1	0
47	2020-02-02	Cook	Illinois	17031	2	0
48	2020-02-02	Los Angeles	California	6037	1	0
49	2020-02-02	Maricopa	Arizona	4013	1	0
50	2020-02-02	Orange	California	6059	1	0
51	2020-02-02	San Francisco	California	6075	2	0
52	2020-02-02	Santa Clara	California	6085	2	0
53	2020-02-02	Snohomish	Washington	53061	1	0
54	2020-02-02	Suffolk	Massachusetts	25025	1	0
55	2020-02-03	Cook	Illinois	17031	2	0
56	2020-02-03	Los Angeles	California	6037	1	0
57	2020-02-03	Maricopa	Arizona	4013	1	0
58	2020-02-03	Orange	California	6059	1	0
59	2020-02-03	San Francisco	California	6075	2	0
60	2020-02-03	Santa Clara	California	6085	2	0
61	2020-02-03	Snohomish	Washington	53061	1	0
62	2020-02-03	Suffolk	Massachusetts	25025	1	0
63	2020-02-04	Cook	Illinois	17031	2	0
64	2020-02-04	Los Angeles	California	6037	1	0
65	2020-02-04	Maricopa	Arizona	4013	1	0
66	2020-02-04	Orange	California	6059	1	0
67	2020-02-04	San Francisco	California	6075	2	0
68	2020-02-04	Santa Clara	California	6085	2	0
69	2020-02-04	Snohomish	Washington	53061	1	0
70	2020-02-04	Suffolk	Massachusetts	25025	1	0
71	2020-02-05	Cook	Illinois	17031	2	0
72	2020-02-05	Dane	Wisconsin	55025	1	0
73	2020-02-05	Los Angeles	California	6037	1	0
74	2020-02-05	Maricopa	Arizona	4013	1	0
75	2020-02-05	Orange	California	6059	1	0
76	2020-02-05	San Francisco	California	6075	2	0
77	2020-02-05	Santa Clara	California	6085	2	0
78	2020-02-05	Snohomish	Washington	53061	1	0
79	2020-02-05	Suffolk	Massachusetts	25025	1	0
80	2020-02-06	Cook	Illinois	17031	2	0
81	2020-02-06	Dane	Wisconsin	55025	1	0
82	2020-02-06	Los Angeles	California	6037	1	0
83	2020-02-06	Maricopa	Arizona	4013	1	0
84	2020-02-06	Orange	California	6059	1	0
85	2020-02-06	San Francisco	California	6075	2	0
86	2020-02-06	Santa Clara	California	6085	2	0
87	2020-02-06	Snohomish	Washington	53061	1	0
88	2020-02-06	Suffolk	Massachusetts	25025	1	0
89	2020-02-07	Cook	Illinois	17031	2	0
90	2020-02-07	Dane	Wisconsin	55025	1	0
91	2020-02-07	Los Angeles	California	6037	1	0
92	2020-02-07	Maricopa	Arizona	4013	1	0
93	2020-02-07	Orange	California	6059	1	0
94	2020-02-07	San Francisco	California	6075	2	0
95	2020-02-07	Santa Clara	California	6085	2	0
96	2020-02-07	Snohomish	Washington	53061	1	0
97	2020-02-07	Suffolk	Massachusetts	25025	1	0
98	2020-02-08	Cook	Illinois	17031	2	0
99	2020-02-08	Dane	Wisconsin	55025	1	0
100	2020-02-08	Los Angeles	California	6037	1	0

Rows: 1-100 | Columns: 6

Data Exploration and Preparation¶

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

In [5]:

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

Out[5]:

	dtype	count	top	top_percent	unique
"date"	date	128256	2020-05-09	2.247	110.0
"county"	varchar(66)	128256	Washington	1.163	1710.0
"state"	varchar(48)	128256	Texas	6.71	51.0
"fips"	int	128256	53061	0.086	2882.0
"cases"	int	128256	1	13.376	3830.0
"deaths"	int	128256	0	61.125	806.0

Rows: 1-6 | Columns: 6

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

In [6]:

covid19["date"].describe()

Out[6]:

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

Rows: 1-5 | Columns: 2

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.

In [7]:

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

Out[7]:

	📅 date Date	Abc state Varchar(48)	123 cases Int	123 deaths Int
1	2020-01-21	Washington	1	0
2	2020-01-22	Washington	1	0
3	2020-01-23	Washington	1	0
4	2020-01-24	Illinois	1	0
5	2020-01-24	Washington	1	0
6	2020-01-25	Illinois	1	0
7	2020-01-25	California	1	0
8	2020-01-25	Washington	1	0
9	2020-01-26	Illinois	1	0
10	2020-01-26	California	1	0
11	2020-01-26	Arizona	1	0
12	2020-01-26	California	1	0
13	2020-01-26	Washington	1	0
14	2020-01-27	Illinois	1	0
15	2020-01-27	California	1	0
16	2020-01-27	Arizona	1	0
17	2020-01-27	California	1	0
18	2020-01-27	Washington	1	0
19	2020-01-28	Illinois	1	0
20	2020-01-28	California	1	0
21	2020-01-28	Arizona	1	0
22	2020-01-28	California	1	0
23	2020-01-28	Washington	1	0
24	2020-01-29	Illinois	1	0
25	2020-01-29	California	1	0
26	2020-01-29	Arizona	1	0
27	2020-01-29	California	1	0
28	2020-01-29	Washington	1	0
29	2020-01-30	Illinois	2	0
30	2020-01-30	California	1	0
31	2020-01-30	Arizona	1	0
32	2020-01-30	California	1	0
33	2020-01-30	Washington	1	0
34	2020-01-31	Illinois	2	0
35	2020-01-31	California	1	0
36	2020-01-31	Arizona	1	0
37	2020-01-31	California	1	0
38	2020-01-31	California	1	0
39	2020-01-31	Washington	1	0
40	2020-02-01	Illinois	2	0
41	2020-02-01	California	1	0
42	2020-02-01	Arizona	1	0
43	2020-02-01	California	1	0
44	2020-02-01	California	1	0
45	2020-02-01	Washington	1	0
46	2020-02-01	Massachusetts	1	0
47	2020-02-02	Illinois	2	0
48	2020-02-02	California	1	0
49	2020-02-02	Arizona	1	0
50	2020-02-02	California	1	0
51	2020-02-02	California	2	0
52	2020-02-02	California	2	0
53	2020-02-02	Washington	1	0
54	2020-02-02	Massachusetts	1	0
55	2020-02-03	Illinois	2	0
56	2020-02-03	California	1	0
57	2020-02-03	Arizona	1	0
58	2020-02-03	California	1	0
59	2020-02-03	California	2	0
60	2020-02-03	California	2	0
61	2020-02-03	Washington	1	0
62	2020-02-03	Massachusetts	1	0
63	2020-02-04	Illinois	2	0
64	2020-02-04	California	1	0
65	2020-02-04	Arizona	1	0
66	2020-02-04	California	1	0
67	2020-02-04	California	2	0
68	2020-02-04	California	2	0
69	2020-02-04	Washington	1	0
70	2020-02-04	Massachusetts	1	0
71	2020-02-05	Illinois	2	0
72	2020-02-05	Wisconsin	1	0
73	2020-02-05	California	1	0
74	2020-02-05	Arizona	1	0
75	2020-02-05	California	1	0
76	2020-02-05	California	2	0
77	2020-02-05	California	2	0
78	2020-02-05	Washington	1	0
79	2020-02-05	Massachusetts	1	0
80	2020-02-06	Illinois	2	0
81	2020-02-06	Wisconsin	1	0
82	2020-02-06	California	1	0
83	2020-02-06	Arizona	1	0
84	2020-02-06	California	1	0
85	2020-02-06	California	2	0
86	2020-02-06	California	2	0
87	2020-02-06	Washington	1	0
88	2020-02-06	Massachusetts	1	0
89	2020-02-07	Illinois	2	0
90	2020-02-07	Wisconsin	1	0
91	2020-02-07	California	1	0
92	2020-02-07	Arizona	1	0
93	2020-02-07	California	1	0
94	2020-02-07	California	2	0
95	2020-02-07	California	2	0
96	2020-02-07	Washington	1	0
97	2020-02-07	Massachusetts	1	0
98	2020-02-08	Illinois	2	0
99	2020-02-08	Wisconsin	1	0
100	2020-02-08	California	1	0

Rows: 1-100 of 128256 | Columns: 4

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

In [8]:

import verticapy.stats as st
covid19 = covid19.groupby(["state",
                           "date"],
                          [st.sum(covid19["deaths"])._as("deaths"),
                           st.sum(covid19["cases"])._as("cases")])
display(covid19)

	Abc state Varchar(48)	📅 date Date	123 deaths Integer	123 cases Integer
1	South Carolina	2020-03-20	3	126
2	South Carolina	2020-04-04	40	1917
3	South Carolina	2020-04-03	34	1700
4	South Carolina	2020-04-02	31	1554
5	South Carolina	2020-04-08	63	2552
6	South Carolina	2020-04-20	124	4439
7	South Carolina	2020-04-19	120	4377
8	South Carolina	2020-04-18	119	4246
9	South Carolina	2020-04-17	116	4086
10	South Carolina	2020-04-16	109	3931
11	South Carolina	2020-04-15	107	3656
12	South Carolina	2020-04-14	97	3553
13	South Carolina	2020-04-13	87	3439
14	District of Columbia	2020-03-07	0	1
15	District of Columbia	2020-03-08	0	1
16	District of Columbia	2020-03-09	0	4
17	District of Columbia	2020-03-10	0	4
18	District of Columbia	2020-03-11	0	10
19	District of Columbia	2020-03-12	0	10
20	District of Columbia	2020-03-13	0	10
21	District of Columbia	2020-03-14	0	16
22	District of Columbia	2020-03-15	0	17
23	District of Columbia	2020-03-16	0	22
24	District of Columbia	2020-03-17	0	31
25	District of Columbia	2020-03-18	0	36
26	District of Columbia	2020-03-19	0	39
27	District of Columbia	2020-03-20	0	71
28	District of Columbia	2020-03-21	1	77
29	District of Columbia	2020-03-22	1	98
30	Nevada	2020-05-08	298	5972
31	Nevada	2020-05-09	301	6076
32	North Dakota	2020-04-22	14	679
33	North Dakota	2020-04-23	15	709
34	North Dakota	2020-04-20	13	627
35	North Dakota	2020-04-21	13	644
36	North Dakota	2020-04-26	17	867
37	North Dakota	2020-04-27	19	942
38	North Dakota	2020-04-24	15	748
39	North Dakota	2020-04-25	16	803
40	North Dakota	2020-04-14	9	341
41	North Dakota	2020-04-15	9	365
42	North Dakota	2020-04-12	8	308
43	North Dakota	2020-04-13	9	331
44	North Dakota	2020-04-18	9	528
45	North Dakota	2020-04-19	10	585
46	North Dakota	2020-04-16	9	393
47	North Dakota	2020-04-17	9	439
48	North Dakota	2020-04-07	4	237
49	North Dakota	2020-04-10	6	278
50	South Carolina	2020-04-28	192	5735
51	South Carolina	2020-04-27	177	5613
52	South Carolina	2020-04-26	174	5490
53	South Carolina	2020-04-25	166	5253
54	South Carolina	2020-04-24	157	5070
55	South Carolina	2020-04-23	150	4917
56	South Carolina	2020-04-22	140	4761
57	South Carolina	2020-04-21	135	4608
58	South Carolina	2020-05-06	305	6936
59	South Carolina	2020-05-05	296	6841
60	South Carolina	2020-05-04	283	6757
61	South Carolina	2020-05-03	275	6626
62	South Carolina	2020-05-02	267	6489
63	South Carolina	2020-05-01	256	6258
64	South Carolina	2020-04-30	244	6095
65	South Carolina	2020-04-29	232	5881
66	South Carolina	2020-05-09	330	7531
67	South Carolina	2020-05-08	320	7367
68	South Carolina	2020-05-07	316	7142
69	Ohio	2020-05-04	1056	20474
70	Ohio	2020-05-07	1271	22131
71	Hawaii	2020-05-09	17	620
72	Hawaii	2020-05-08	17	619
73	Hawaii	2020-05-05	17	615
74	Hawaii	2020-05-04	17	612
75	Hawaii	2020-05-07	17	619
76	Hawaii	2020-05-06	17	616
77	Hawaii	2020-03-22	0	56
78	Hawaii	2020-03-21	0	48
79	Hawaii	2020-03-24	0	77
80	Hawaii	2020-03-23	0	72
81	Hawaii	2020-03-18	0	16
82	Hawaii	2020-03-30	0	191
83	Hawaii	2020-03-29	0	163
84	Hawaii	2020-04-01	1	238
85	Hawaii	2020-03-31	1	209
86	Hawaii	2020-03-28	0	145
87	Hawaii	2020-03-27	0	115
88	Hawaii	2020-03-06	0	1
89	Hawaii	2020-03-08	0	2
90	Hawaii	2020-03-07	0	1
91	Hawaii	2020-03-13	0	2
92	Hawaii	2020-04-23	12	590
93	Hawaii	2020-04-22	12	586
94	Hawaii	2020-04-25	14	597
95	Hawaii	2020-04-24	13	595
96	Hawaii	2020-04-19	10	574
97	Hawaii	2020-04-18	9	568
98	Hawaii	2020-04-21	12	580
99	Hawaii	2020-04-20	10	578
100	Hawaii	2020-05-01	16	610

Rows: 1-100 | Columns: 4

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

In [9]:

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

Out[9]:

	value	confidence
0	1.0	0.03306808277915286
1	0.999	0.05724557037333314
2	0.998	0.07387480270409284
3	0.997	0.08737730395335205
4	0.995	0.09901827142968875
5	0.992	0.10937378440149341
6	0.989	0.11878064860034902
7	0.985	0.12743226263766533
8	0.981	0.13547360751434442
9	0.975	0.14297793284551047
10	0.969	0.15002630207245246
11	0.963	0.15668069918376346
12	0.955	0.16296406141763914
13	0.947	0.1689185480557603
14	0.938	0.1745667355850512
15	0.929	0.17993837287877298
16	0.918	0.18503660039549066
17	0.907	0.18988474395122357
18	0.895	0.19449265660474047
19	0.883	0.1988785290267625
20	0.87	0.20304878272513283
21	0.856	0.20700903997259487
22	0.84	0.21075551505216278
23	0.824	0.21430193241170437
24	0.809	0.21766872646402852
25	0.793	0.22085833870057284
26	0.776	0.22387311276697835
27	0.758	0.2267153493691516
28	0.74	0.22939446302883856
29	0.721	0.2319122183474391
30	0.703	0.23428371983050017
31	0.683	0.2365035253502923
32	0.663	0.23857946037199607
33	0.645	0.2405306388244739
34	0.624	0.24234583571399215
35	0.599	0.24401021225441466
36	0.583	0.24557919332126724
37	0.555	0.2469965798526741
38	0.527	0.24827182839332118
39	0.497	0.24940512413516747
40	0.465	0.2503979952407609
41	0.433	0.2512610083135219
42	0.4	0.25200086557622814
43	0.362	0.2526121165114924
44	0.323	0.2531053885928537
45	0.28	0.25348470463884176
46	0.235	0.2537625031036636
47	0.199	0.25397198430011114
48	0.167	0.25413032503412625

Rows: 1-49 | Columns: 3

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

In [10]:

from verticapy.stats import adfuller
adfuller(covid19,
         ts = "date", 
         column = "deaths", 
         by = ["state"], 
         p = 12)

Out[10]:

	value
ADF Test Statistic	-0.9916599727457468
p_value	0.321448022269228
# Lags used	12
# Observations Used	3513
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

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

In [11]:

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

Out[11]:

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