Missing Values¶

Missing values occur when no data value is stored for the variable in an observation and are most often represented with a NULL or None. Not handling them can lead to unexpected results (for example, some ML algorithms can't handle missing values at all) and worse, it can lead to incorrect conclusions.

There are 3 main types of missing values:

MCAR (Missing Completely at Random) : The events that lead to any particular data-item being missing occur entirely at random. For example, in IOT, we can lose sensory data in transmission.
MAR (Missing {Conditionally} at Random) : Missing data doesn't happen at random and is instead related to some of the observed data. For example, some students may have not answered to some specific questions of a test because they were absent during the relevant lesson.
MNAR (Missing not at Random) : The value of the variable that’s missing is related to the reason it’s missing. For example, if someone didn’t subscribe to a loyalty program, we can leave the cell empty.

Different types of missing values tend to suggest different methods for imputing them. For example, when dealing with MCAR values, you can use mathematical aggregations to impute the missing values. For MNAR values, we can simply create another category. MAR values, however, we'll need to do some more investigation before deciding how to impute the data.

To see how to handle missing values in VerticaPy, we'll use the well-known 'Titanic' dataset.

In [64]:

from verticapy.datasets import load_titanic
vdf = load_titanic()
display(vdf)

	123 pclass Int	123 survived Int	Abc Varchar(164)	Abc sex Varchar(20)	123 age Numeric(6,3)	123 sibsp Int	123 parch Int	Abc ticket Varchar(36)	123 fare Numeric(10,5)	Abc cabin Varchar(30)	Abc embarked Varchar(20)	Abc boat Varchar(100)	123 body Int	Abc home.dest Varchar(100)
1	1	0		female	2.0	1	2	113781	151.55	C22 C26	S	[null]	[null]	Montreal, PQ / Chesterville, ON
2	1	0		male	30.0	1	2	113781	151.55	C22 C26	S	[null]	135	Montreal, PQ / Chesterville, ON
3	1	0		female	25.0	1	2	113781	151.55	C22 C26	S	[null]	[null]	Montreal, PQ / Chesterville, ON
4	1	0		male	39.0	0	0	112050	0.0	A36	S	[null]	[null]	Belfast, NI
5	1	0		male	71.0	0	0	PC 17609	49.5042	[null]	C	[null]	22	Montevideo, Uruguay
6	1	0		male	47.0	1	0	PC 17757	227.525	C62 C64	C	[null]	124	New York, NY
7	1	0		male	[null]	0	0	PC 17318	25.925	[null]	S	[null]	[null]	New York, NY
8	1	0		male	24.0	0	1	PC 17558	247.5208	B58 B60	C	[null]	[null]	Montreal, PQ
9	1	0		male	36.0	0	0	13050	75.2417	C6	C	A	[null]	Winnipeg, MN
10	1	0		male	25.0	0	0	13905	26.0	[null]	C	[null]	148	San Francisco, CA
11	1	0		male	45.0	0	0	113784	35.5	T	S	[null]	[null]	Trenton, NJ
12	1	0		male	42.0	0	0	110489	26.55	D22	S	[null]	[null]	London / Winnipeg, MB
13	1	0		male	41.0	0	0	113054	30.5	A21	S	[null]	[null]	Pomeroy, WA
14	1	0		male	48.0	0	0	PC 17591	50.4958	B10	C	[null]	208	Omaha, NE
15	1	0		male	[null]	0	0	112379	39.6	[null]	C	[null]	[null]	Philadelphia, PA
16	1	0		male	45.0	0	0	113050	26.55	B38	S	[null]	[null]	Washington, DC
17	1	0		male	[null]	0	0	113798	31.0	[null]	S	[null]	[null]	[null]
18	1	0		male	33.0	0	0	695	5.0	B51 B53 B55	S	[null]	[null]	New York, NY
19	1	0		male	28.0	0	0	113059	47.1	[null]	S	[null]	[null]	Montevideo, Uruguay
20	1	0		male	17.0	0	0	113059	47.1	[null]	S	[null]	[null]	Montevideo, Uruguay
21	1	0		male	49.0	0	0	19924	26.0	[null]	S	[null]	[null]	Ascot, Berkshire / Rochester, NY
22	1	0		male	36.0	1	0	19877	78.85	C46	S	[null]	172	Little Onn Hall, Staffs
23	1	0		male	46.0	1	0	W.E.P. 5734	61.175	E31	S	[null]	[null]	Amenia, ND
24	1	0		male	[null]	0	0	112051	0.0	[null]	S	[null]	[null]	Liverpool, England / Belfast
25	1	0		male	27.0	1	0	13508	136.7792	C89	C	[null]	[null]	Los Angeles, CA
26	1	0		male	[null]	0	0	110465	52.0	A14	S	[null]	[null]	Stoughton, MA
27	1	0		male	47.0	0	0	5727	25.5875	E58	S	[null]	[null]	Victoria, BC
28	1	0		male	37.0	1	1	PC 17756	83.1583	E52	C	[null]	[null]	Lakewood, NJ
29	1	0		male	[null]	0	0	113791	26.55	[null]	S	[null]	[null]	Roachdale, IN
30	1	0		male	70.0	1	1	WE/P 5735	71.0	B22	S	[null]	269	Milwaukee, WI
31	1	0		male	39.0	1	0	PC 17599	71.2833	C85	C	[null]	[null]	New York, NY
32	1	0		male	31.0	1	0	F.C. 12750	52.0	B71	S	[null]	[null]	Montreal, PQ
33	1	0		male	50.0	1	0	PC 17761	106.425	C86	C	[null]	62	Deephaven, MN / Cedar Rapids, IA
34	1	0		male	39.0	0	0	PC 17580	29.7	A18	C	[null]	133	Philadelphia, PA
35	1	0		female	36.0	0	0	PC 17531	31.6792	A29	C	[null]	[null]	New York, NY
36	1	0		male	[null]	0	0	PC 17483	221.7792	C95	S	[null]	[null]	[null]
37	1	0		male	30.0	0	0	113051	27.75	C111	C	[null]	[null]	New York, NY
38	1	0		male	19.0	3	2	19950	263.0	C23 C25 C27	S	[null]	[null]	Winnipeg, MB
39	1	0		male	64.0	1	4	19950	263.0	C23 C25 C27	S	[null]	[null]	Winnipeg, MB
40	1	0		male	[null]	0	0	113778	26.55	D34	S	[null]	[null]	Westcliff-on-Sea, Essex
41	1	0		male	[null]	0	0	112058	0.0	B102	S	[null]	[null]	[null]
42	1	0		male	37.0	1	0	113803	53.1	C123	S	[null]	[null]	Scituate, MA
43	1	0		male	47.0	0	0	111320	38.5	E63	S	[null]	275	St Anne's-on-Sea, Lancashire
44	1	0		male	24.0	0	0	PC 17593	79.2	B86	C	[null]	[null]	[null]
45	1	0		male	71.0	0	0	PC 17754	34.6542	A5	C	[null]	[null]	New York, NY
46	1	0		male	38.0	0	1	PC 17582	153.4625	C91	S	[null]	147	Winnipeg, MB
47	1	0		male	46.0	0	0	PC 17593	79.2	B82 B84	C	[null]	[null]	New York, NY
48	1	0		male	[null]	0	0	113796	42.4	[null]	S	[null]	[null]	[null]
49	1	0		male	45.0	1	0	36973	83.475	C83	S	[null]	[null]	New York, NY
50	1	0		male	40.0	0	0	112059	0.0	B94	S	[null]	110	[null]
51	1	0		male	55.0	1	1	12749	93.5	B69	S	[null]	307	Montreal, PQ
52	1	0		male	42.0	0	0	113038	42.5	B11	S	[null]	[null]	London / Middlesex
53	1	0		male	[null]	0	0	17463	51.8625	E46	S	[null]	[null]	Brighton, MA
54	1	0		male	55.0	0	0	680	50.0	C39	S	[null]	[null]	London / Birmingham
55	1	0		male	42.0	1	0	113789	52.0	[null]	S	[null]	38	New York, NY
56	1	0		male	[null]	0	0	PC 17600	30.6958	[null]	C	14	[null]	New York, NY
57	1	0		female	50.0	0	0	PC 17595	28.7125	C49	C	[null]	[null]	Paris, France New York, NY
58	1	0		male	46.0	0	0	694	26.0	[null]	S	[null]	80	Bennington, VT
59	1	0		male	50.0	0	0	113044	26.0	E60	S	[null]	[null]	London
60	1	0		male	32.5	0	0	113503	211.5	C132	C	[null]	45	[null]
61	1	0		male	58.0	0	0	11771	29.7	B37	C	[null]	258	Buffalo, NY
62	1	0		male	41.0	1	0	17464	51.8625	D21	S	[null]	[null]	Southington / Noank, CT
63	1	0		male	[null]	0	0	113028	26.55	C124	S	[null]	[null]	Portland, OR
64	1	0		male	[null]	0	0	PC 17612	27.7208	[null]	C	[null]	[null]	Chicago, IL
65	1	0		male	29.0	0	0	113501	30.0	D6	S	[null]	126	Springfield, MA
66	1	0		male	30.0	0	0	113801	45.5	[null]	S	[null]	[null]	London / New York, NY
67	1	0		male	30.0	0	0	110469	26.0	C106	S	[null]	[null]	Brockton, MA
68	1	0		male	19.0	1	0	113773	53.1	D30	S	[null]	[null]	New York, NY
69	1	0		male	46.0	0	0	13050	75.2417	C6	C	[null]	292	Vancouver, BC
70	1	0		male	54.0	0	0	17463	51.8625	E46	S	[null]	175	Dorchester, MA
71	1	0		male	28.0	1	0	PC 17604	82.1708	[null]	C	[null]	[null]	New York, NY
72	1	0		male	65.0	0	0	13509	26.55	E38	S	[null]	249	East Bridgewater, MA
73	1	0		male	44.0	2	0	19928	90.0	C78	Q	[null]	230	Fond du Lac, WI
74	1	0		male	55.0	0	0	113787	30.5	C30	S	[null]	[null]	Montreal, PQ
75	1	0		male	47.0	0	0	113796	42.4	[null]	S	[null]	[null]	Washington, DC
76	1	0		male	37.0	0	1	PC 17596	29.7	C118	C	[null]	[null]	Brooklyn, NY
77	1	0		male	58.0	0	2	35273	113.275	D48	C	[null]	122	Lexington, MA
78	1	0		male	64.0	0	0	693	26.0	[null]	S	[null]	263	Isle of Wight, England
79	1	0		male	65.0	0	1	113509	61.9792	B30	C	[null]	234	Providence, RI
80	1	0		male	28.5	0	0	PC 17562	27.7208	D43	C	[null]	189	?Havana, Cuba
81	1	0		male	[null]	0	0	112052	0.0	[null]	S	[null]	[null]	Belfast
82	1	0		male	45.5	0	0	113043	28.5	C124	S	[null]	166	Surbiton Hill, Surrey
83	1	0		male	23.0	0	0	12749	93.5	B24	S	[null]	[null]	Montreal, PQ
84	1	0		male	29.0	1	0	113776	66.6	C2	S	[null]	[null]	Isleworth, England
85	1	0		male	18.0	1	0	PC 17758	108.9	C65	C	[null]	[null]	Madrid, Spain
86	1	0		male	47.0	0	0	110465	52.0	C110	S	[null]	207	Worcester, MA
87	1	0		male	38.0	0	0	19972	0.0	[null]	S	[null]	[null]	Rotterdam, Netherlands
88	1	0		male	22.0	0	0	PC 17760	135.6333	[null]	C	[null]	232	[null]
89	1	0		male	[null]	0	0	PC 17757	227.525	[null]	C	[null]	[null]	[null]
90	1	0		male	31.0	0	0	PC 17590	50.4958	A24	S	[null]	[null]	Trenton, NJ
91	1	0		male	[null]	0	0	113767	50.0	A32	S	[null]	[null]	Seattle, WA
92	1	0		male	36.0	0	0	13049	40.125	A10	C	[null]	[null]	Winnipeg, MB
93	1	0		male	55.0	1	0	PC 17603	59.4	[null]	C	[null]	[null]	New York, NY
94	1	0		male	33.0	0	0	113790	26.55	[null]	S	[null]	109	London
95	1	0		male	61.0	1	3	PC 17608	262.375	B57 B59 B63 B66	C	[null]	[null]	Haverford, PA / Cooperstown, NY
96	1	0		male	50.0	1	0	13507	55.9	E44	S	[null]	[null]	Duluth, MN
97	1	0		male	56.0	0	0	113792	26.55	[null]	S	[null]	[null]	New York, NY
98	1	0		male	56.0	0	0	17764	30.6958	A7	C	[null]	[null]	St James, Long Island, NY
99	1	0		male	24.0	1	0	13695	60.0	C31	S	[null]	[null]	Huntington, WV
100	1	0		male	[null]	0	0	113056	26.0	A19	S	[null]	[null]	Streatham, Surrey

Rows: 1-100 | Columns: 14

We can examine the missing values with the 'count' method.

In [66]:

vdf.count_percent()

Out[66]:

	count	percent
"pclass"	1234.0	100.0
"survived"	1234.0	100.0
"name"	1234.0	100.0
"sex"	1234.0	100.0
"sibsp"	1234.0	100.0
"parch"	1234.0	100.0
"ticket"	1234.0	100.0
"fare"	1233.0	99.919
"embarked"	1232.0	99.838
"age"	997.0	80.794
"home.dest"	706.0	57.212
"boat"	439.0	35.575
"cabin"	286.0	23.177
"body"	118.0	9.562

Rows: 1-14 | Columns: 3

The missing values for 'boat' are MNAR; missing values simply indicate that the passengers didn't pay for a lifeboat. We can replace all the missing values with a new category 'No Lifeboat' using the 'fillna' method.

In [67]:

vdf["boat"].fillna("No Lifeboat")
vdf["boat"]

795 elements were filled.

Out[67]:

	Abc boat Varchar(100)
1	No Lifeboat
2	No Lifeboat
3	No Lifeboat
4	No Lifeboat
5	No Lifeboat
6	No Lifeboat
7	No Lifeboat
8	No Lifeboat
9	A
10	No Lifeboat
11	No Lifeboat
12	No Lifeboat
13	No Lifeboat
14	No Lifeboat
15	No Lifeboat
16	No Lifeboat
17	No Lifeboat
18	No Lifeboat
19	No Lifeboat
20	No Lifeboat
21	No Lifeboat
22	No Lifeboat
23	No Lifeboat
24	No Lifeboat
25	No Lifeboat
26	No Lifeboat
27	No Lifeboat
28	No Lifeboat
29	No Lifeboat
30	No Lifeboat
31	No Lifeboat
32	No Lifeboat
33	No Lifeboat
34	No Lifeboat
35	No Lifeboat
36	No Lifeboat
37	No Lifeboat
38	No Lifeboat
39	No Lifeboat
40	No Lifeboat
41	No Lifeboat
42	No Lifeboat
43	No Lifeboat
44	No Lifeboat
45	No Lifeboat
46	No Lifeboat
47	No Lifeboat
48	No Lifeboat
49	No Lifeboat
50	No Lifeboat
51	No Lifeboat
52	No Lifeboat
53	No Lifeboat
54	No Lifeboat
55	No Lifeboat
56	14
57	No Lifeboat
58	No Lifeboat
59	No Lifeboat
60	No Lifeboat
61	No Lifeboat
62	No Lifeboat
63	No Lifeboat
64	No Lifeboat
65	No Lifeboat
66	No Lifeboat
67	No Lifeboat
68	No Lifeboat
69	No Lifeboat
70	No Lifeboat
71	No Lifeboat
72	No Lifeboat
73	No Lifeboat
74	No Lifeboat
75	No Lifeboat
76	No Lifeboat
77	No Lifeboat
78	No Lifeboat
79	No Lifeboat
80	No Lifeboat
81	No Lifeboat
82	No Lifeboat
83	No Lifeboat
84	No Lifeboat
85	No Lifeboat
86	No Lifeboat
87	No Lifeboat
88	No Lifeboat
89	No Lifeboat
90	No Lifeboat
91	No Lifeboat
92	No Lifeboat
93	No Lifeboat
94	No Lifeboat
95	No Lifeboat
96	No Lifeboat
97	No Lifeboat
98	No Lifeboat
99	No Lifeboat
100	No Lifeboat

Rows: 1-100 of 1234 | Column: boat | Type: Varchar(100)

Missing values for 'age' seem to be MCAR, so the best way to impute them is with mathematical aggregations. Let's impute the age using the average age of passengers of the same sex and class.

In [68]:

vdf["age"].fillna(method = "avg",
                  by = ["pclass", "sex"])
vdf["age"]

237 elements were filled.

Out[68]:

	123 age Float
1	36.0
2	31.0
3	21.0
4	50.0
5	45.0
6	31.0
7	55.0
8	60.0
9	35.0
10	37.2635658914729
11	39.0
12	37.2635658914729
13	39.0
14	18.0
15	48.0
16	62.0
17	52.0
18	43.0
19	37.2635658914729
20	40.0
21	23.0
22	18.0
23	39.0
24	40.0
25	30.0
26	35.0
27	24.0
28	48.0
29	18.0
30	54.0
31	33.0
32	33.0
33	43.0
34	56.0
35	30.0
36	17.0
37	22.0
38	22.0
39	39.0
40	19.0
41	23.0
42	31.0
43	37.0
44	33.0
45	37.2635658914729
46	18.0
47	16.0
48	15.0
49	58.0
50	21.0
51	51.0
52	16.0
53	55.0
54	49.0
55	30.0
56	39.0
57	45.0
58	37.2635658914729
59	38.0
60	35.0
61	35.0
62	51.0
63	44.0
64	16.0
65	52.0
66	24.0
67	35.0
68	49.0
69	25.0
70	45.0
71	58.0
72	19.0
73	37.2635658914729
74	45.0
75	22.0
76	35.0
77	35.0
78	48.0
79	22.0
80	37.2635658914729
81	30.0
82	60.0
83	23.0
84	28.0
85	24.0
86	37.2635658914729
87	37.2635658914729
88	54.0
89	38.0
90	23.0
91	48.0
92	27.0
93	54.0
94	17.0
95	27.0
96	33.0
97	38.0
98	64.0
99	36.0
100	55.0

Rows: 1-100 of 1234 | Column: age | Type: Float

The features 'embarked' and 'fare' have a couple missing values. Instead of using a technique to impute them, we can just drop them with the 'dropna' method.

In [69]:

vdf["fare"].dropna()
vdf["embarked"].dropna()

1 element was filtered.
2 elements were filtered.

Out[69]:

	123 pclass Int	123 survived Int	Abc Varchar(164)	Abc sex Varchar(20)	123 age Numeric(6,3)	123 sibsp Int	123 parch Int	Abc ticket Varchar(36)	123 fare Numeric(10,5)	Abc cabin Varchar(30)	Abc embarked Varchar(20)	Abc boat Varchar(100)	123 body Int	Abc Varchar(100)
1	1	1		female	36.0	0	0	PC 17760	135.6333	C32	C	8	[null]
2	1	1		female	31.0	0	0	16966	134.5	E39 E41	C	3	[null]
3	1	1		female	21.0	0	0	113795	26.55	[null]	S	8 10	[null]
4	1	1		female	50.0	1	1	113503	211.5	C80	C	4	[null]
5	1	1		female	45.0	1	1	36928	164.8667	[null]	S	8	[null]
6	1	1		female	31.0	0	2	36928	164.8667	C7	S	8	[null]
7	1	1		female	55.0	0	0	PC 17760	135.6333	C32	C	8	[null]
8	1	1		female	60.0	1	0	110813	75.25	D37	C	5	[null]
9	1	1		female	35.0	0	0	PC 17755	512.3292	[null]	C	3	[null]
10	1	1		female	37.2635658914729	0	0	PC 17585	79.2	[null]	C	D	[null]
11	1	1		female	39.0	1	1	17421	110.8833	C68	C	4	[null]
12	1	1		female	37.2635658914729	1	0	19996	52.0	C126	S	5 7	[null]
13	1	1		female	39.0	1	1	110413	79.65	E67	S	8	[null]
14	1	1		female	18.0	0	2	110413	79.65	E68	S	8	[null]
15	1	1		female	48.0	0	0	17466	25.9292	D17	S	8	[null]
16	1	1		female	52.0	1	0	36947	78.2667	D20	C	4	[null]
17	1	1		female	43.0	1	0	11778	55.4417	C116	C	5	[null]
18	1	1		female	37.2635658914729	1	0	PC 17569	146.5208	B78	C	6	[null]
19	1	1		female	40.0	1	1	16966	134.5	E34	C	3	[null]
20	1	1		female	23.0	1	0	21228	82.2667	B45	S	7	[null]
21	1	1		female	18.0	1	0	13695	60.0	C31	S	6	[null]
22	1	1		female	39.0	1	0	13507	55.9	E44	S	11	[null]
23	1	1		female	40.0	0	0	PC 17582	153.4625	C125	S	3	[null]
24	1	1		female	30.0	0	0	113798	31.0	[null]	C	4	[null]
25	1	1		female	35.0	1	0	13236	57.75	C28	C	11	[null]
26	1	1		female	24.0	0	0	PC 17477	69.3	B35	C	9	[null]
27	1	1		female	48.0	1	3	PC 17608	262.375	B57 B59 B63 B66	C	4	[null]
28	1	1		female	18.0	2	2	PC 17608	262.375	B57 B59 B63 B66	C	4	[null]
29	1	1		female	54.0	1	0	PC 17603	59.4	[null]	C	6	[null]
30	1	1		female	33.0	0	0	110152	86.5	B77	S	8	[null]
31	1	1		female	33.0	0	0	PC 17613	27.7208	A11	C	11	[null]
32	1	1		female	43.0	0	1	24160	211.3375	B3	S	2	[null]
33	1	1		female	56.0	0	1	11767	83.1583	C50	C	7	[null]
34	1	1		female	30.0	0	0	12749	93.5	B73	S	3	[null]
35	1	1		female	17.0	1	0	PC 17758	108.9	C65	C	8	[null]
36	1	1		female	22.0	1	0	113776	66.6	C2	S	8	[null]
37	1	1		female	22.0	0	1	113509	61.9792	B36	C	5	[null]
38	1	1		female	39.0	0	0	PC 17758	108.9	C105	C	8	[null]
39	1	1		female	19.0	0	2	11752	26.2833	D47	S	5	[null]
40	1	1		female	23.0	1	0	35273	113.275	D36	C	6	[null]
41	1	1		female	31.0	1	0	35273	113.275	D36	C	6	[null]
42	1	1		female	37.0	1	0	19928	90.0	C78	Q	14	[null]
43	1	1		female	33.0	1	0	19928	90.0	C78	Q	14	[null]
44	1	1		female	37.2635658914729	1	0	PC 17604	82.1708	[null]	C	6	[null]
45	1	1		female	18.0	1	0	113773	53.1	D30	S	10	[null]
46	1	1		female	16.0	0	0	110152	86.5	B79	S	8	[null]
47	1	1		female	15.0	0	1	24160	211.3375	B5	S	2	[null]
48	1	1		female	58.0	0	0	PC 17569	146.5208	B80	C	No Lifeboat	[null]
49	1	1		female	21.0	0	0	13502	77.9583	D9	S	10	[null]
50	1	1		female	51.0	0	1	PC 17592	39.4	D28	S	9	[null]
51	1	1		female	16.0	0	1	PC 17592	39.4	D28	S	9	[null]
52	1	1		female	55.0	0	0	112377	27.7208	[null]	C	6	[null]
53	1	1