Loading...

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.

from verticapy.datasets import load_titanic

titanic = load_titanic()
titanic.head(100)
123
pclass
Int
100%
...
123
body
Int
9%
Abc
Varchar(100)
57%
11...[null]
21...[null]
31...[null]
41...[null]
51...[null]
61...208
71...[null]
81...[null]
91...[null]
101...[null]
111...[null]
121...[null]
131...275
141...147
151...307
161...[null]
171...[null]
181...258
191...[null]
201...[null]
211...249
221...[null]
231...[null]
241...[null]
251...[null]
261...96
271...[null]
281...245
291...[null]
301...[null]
311...[null]
321...[null]
331...[null]
341...[null]
351...[null]
361...[null]
371...[null]
381...[null]
391...[null]
401...[null]
411...[null]
421...[null]
431...[null]
441...[null]
451...[null]
461...[null]
471...[null]
481...[null]
491...[null]
501...[null]
511...[null]
521...[null]
531...[null]
541...[null]
551...[null]
561...[null]
571...[null]
581...[null]
591...[null]
601...[null]
611...[null]
621...[null]
631...[null]
641...[null]
651...[null]
661...[null]
671...[null]
681...[null]
691...[null]
701...[null]
711...[null]
721...[null]
731...[null]
741...[null]
751...[null]
761...[null]
771...[null]
781...[null]
791...[null]
801...[null]
811...[null]
821...[null]
831...[null]
841...[null]
851...[null]
862...[null]
872...[null]
882...[null]
892...[null]
902...[null]
912...[null]
922...[null]
932...[null]
942...[null]
952...322
962...[null]
972...[null]
982...[null]
992...[null]
1002...256

We can examine the missing values with the count() method.

titanic.count_percent()
...
count
percent
"pclass"...1234.0100.0
"survived"...1234.0100.0
"name"...1234.0100.0
"sex"...1234.0100.0
"sibsp"...1234.0100.0
"parch"...1234.0100.0
"ticket"...1234.0100.0
"fare"...1233.099.919
"embarked"...1232.099.838
"age"...997.080.794
"home.dest"...706.057.212
"boat"...439.035.575
"cabin"...286.023.177
"body"...118.09.562

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.

titanic["boat"].fillna("No Lifeboat")
titanic["boat"]
Abc
boat
Varchar(100)
1No Lifeboat
2No Lifeboat
3No Lifeboat
4No Lifeboat
5No Lifeboat
6No Lifeboat
7No Lifeboat
8No Lifeboat
9No Lifeboat
10No Lifeboat
11No Lifeboat
12No Lifeboat
13No Lifeboat
14No Lifeboat
15No Lifeboat
16No Lifeboat
1714
18No Lifeboat
19No Lifeboat
20No Lifeboat

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.

titanic["age"].fillna(
    method = "avg",
    by = ["pclass", "sex"],
)
titanic["age"]
123
age
Float(22)
136.5
20.42
325.0
414.0
516.0
631.0
725.0
812.0
920.0
1024.0
1132.0
1220.0
1326.0
1426.2142058823529
1532.0
1626.2142058823529
1727.0
1826.2142058823529
1921.0
2020.0

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.

titanic["fare"].dropna()
titanic["embarked"].dropna()
123
pclass
Int
100%
...
123
survived
Int
100%
Abc
home.dest
Varchar(100)
57%
11...0Montevideo, Uruguay
21...0Trenton, NJ
31...0[null]
41...0Montevideo, Uruguay
51...0Los Angeles, CA
61...0Lakewood, NJ
71...0Montreal, PQ
81...0Deephaven, MN / Cedar Rapids, IA
91...0New York, NY
101...0Scituate, MA
111...0[null]
121...0New York, NY
131...0[null]
141...0London / Middlesex
151...0Brighton, MA
161...0New York, NY
171...0New York, NY
181...0Springfield, MA
191...0Vancouver, BC
201...0Dorchester, MA

The fillna() method offers many options. Let’s use the help() function to view its parameters.

help(titanic["embarked"].fillna)
Help on function fillna in module verticapy.core.vdataframe._fill:

fillna(val: Union[int, float, str, datetime.datetime, datetime.date] = None, method: Literal['auto', 'mode', '0ifnull', 'mean', 'avg', 'median', 'ffill', 'pad', 'bfill', 'backfill'] = 'auto', expr: Union[str, verticapy.core.string_sql.base.StringSQL] = '', by: Optional[Annotated[Union[str, list[str]], 'STRING representing one column or a list of columns']] = None, order_by: Optional[Annotated[Union[str, list[str]], 'STRING representing one column or a list of columns']] = None) -> 'vDataFrame'

Fills missing elements in the vDataColumn with a user-specified
rule.

Parameters
----------
val: PythonScalar / date, optional
    Value used to impute the vDataColumn.
method: dict, optional
    Method used to impute the missing values.

    - auto:
        Mean  for  the  numerical  and  Mode  for  the
        categorical vDataColumns.
    - bfill:
        Back Propagation of the next element (Constant
        Interpolation).
    - ffill:
        Propagation  of  the  first element  (Constant
        Interpolation).
    - mean:
        Average.
    - median:
        Median.
    - mode:
        Mode (most occurent element).
    - 0ifnull:
        0 when the vDataColumn is null, 1 otherwise.
expr: str, optional
    SQL string.
by: SQLColumns, optional
    vDataColumns used in the partition.
order_by: SQLColumns, optional
    List of the vDataColumns used to sort the data when using
    TS methods.

Returns
-------
vDataFrame
    self._parent

print(titanic.current_relation())
(
   SELECT
     * 
   FROM
 (
   SELECT
     "pclass",
     "survived",
     "name",
     "sex",
     
                    COALESCE("age", AVG("age")  OVER (PARTITION BY "pclass", "sex")) AS "age",
     "sibsp",
     "parch",
     "ticket",
     "fare",
     "cabin",
     "embarked",
     COALESCE("boat", 'No Lifeboat') AS "boat",
     "body",
     "home.dest" 
   FROM
 "public"."titanic") 
VERTICAPY_SUBTABLE 
   WHERE ("fare" IS NOT NULL) AND ("embarked" IS NOT NULL)) 
VERTICAPY_SUBTABLE

Depending on the circumstances, we’ll need to investigate to find the most suitable solution.

In conclusion, before imputing missing data, you have to understand why it might be missing and how it relates to the rest of your dataset.