Imputing Missing Values: Mean vs Median vs Mode

1.Drop or fill: why imputation exists

Real datasets have gaps, and most estimators can't work with them. You have two broad options. You can remove the rows (or columns) that have gaps, or you can fill each gap with a value inferred from the data you do have. The Databricks EDA tutorial lists both options in its data-cleaning step: handling missing values 'might involve replacing them with a specific value or removing the affected rows.' Before comparing mean, median and mode, it helps to see what dropping costs.

from pyspark.sql import Row

df = spark.createDataFrame([
    Row(age=10, height=80.0, name="Alice"),
    Row(age=5, height=None, name="Bob"),
    Row(age=None, height=None, name="Tom"),
])

df.na.drop().show()

+---+------+-----+
|age|height| name|
+---+------+-----+
| 10|  80.0|Alice|
+---+------+-----+

The scikit-learn guide makes the same point. Dropping incomplete rows 'comes at the price of losing data which may be valuable (even though incomplete)'. Its recommended alternative is to impute: work out the missing values from the known part of the data. The simplest way is univariate. For each column, compute one statistic from that column's present values and write it into every gap. scikit-learn's SimpleImputer offers three such statistics (mean, median and most frequent) plus a fixed constant. The rest of this lesson is about choosing among them.

Sources123

2.Mean vs median: what one extreme value does

Mean and median imputation both fill a numeric column with one central value, but they measure 'central' differently. The mean adds up all the present values and divides by how many there are. The median is the middle value once the values are sorted. In Databricks you can compute the median directly with the PySpark function sf.median, which 'Returns the median of the values in a group.' Here it is applied to earnings per course:

from pyspark.sql import functions as sf
df = spark.createDataFrame([
    ("Java", 2012, 20000), ("dotNET", 2012, 5000),
    ("Java", 2012, 22000), ("dotNET", 2012, 10000),
    ("dotNET", 2013, 48000), ("Java", 2013, 30000)],
    schema=("course", "year", "earnings"))
df.groupby("course").agg(sf.median("earnings")).show()

+------+----------------+
|course|median(earnings)|
+------+----------------+
|  Java|         22000.0|
|dotNET|         10000.0|
+------+----------------+

That reveal is the core of the mean-versus-median comparison. The mean uses the size of every value, so one large value moves it. The median depends only on which value is in the middle, so an extreme value at one end barely moves it. When a numeric column's values are bunched together, the two statistics come out close and either one is a reasonable fill. When a few values sit far out on one side, they can be well apart, and the median gives a fill value much nearer to most of the real data. One caveat: the documentation provided for this lesson has no written rule such as 'use the median for skewed data.' That guideline comes from the exam guide's framing. The arithmetic above shows why it holds.

The statistic is learned once and then reused. In scikit-learn, SimpleImputer computes each column's statistic when you call fit, then writes it into the gaps of whatever data you pass to transform. Fit on a first column of 1, NaN and 7 with strategy='mean', and the learned mean is 4. Every later NaN in that column becomes 4, even in rows that weren't present during fit.

Sources43

3.Mode: the strategy that works on categories

Mean and median both need numbers you can add or sort. A column of country codes, product types or colours has neither, so the only statistic left is the most frequent value, the mode. This is the main contrast in the objective. Mean and median are choices for numeric columns. Mode is the statistic that also works on categorical ones. scikit-learn's SimpleImputer 'supports categorical data represented as string values or pandas categoricals when using the 'most_frequent' or 'constant' strategy'. Mean and median aren't on that list. Note the name: scikit-learn calls the mode strategy most_frequent, not 'mode'.

>>> imp = SimpleImputer(strategy=" ? ")
>>> print(imp.fit_transform(df))

On the Databricks side, the mode is available as the PySpark function sf.mode and as the SQL mode aggregate. The SQL reference says it 'Returns the most frequent, not NULL, value of expr in a group'. The argument can be 'An expression of any type that can be compared', including strings. The 'not NULL' part matters for imputation. In the query below, NULL is the most common entry (three of seven), but mode ignores it and returns the most common real value. If a group contains only nulls, mode returns NULL, so there is nothing to fill with.

> SELECT mode(col) FROM VALUES (NULL), (1), (NULL), (2), (NULL), (3), (3) AS tab(col);
 3

Ties are a risk mean and median don't have. If two categories are equally common, the imputed value can differ from one run to the next, and so can a model trained on the result. Both the PySpark and SQL forms take a deterministic flag. In PySpark, deterministic: 'If there are multiple equally-frequent results then return the lowest (defaults to false).' Here it breaks a three-way tie by returning -10. The SQL reference adds one caveat: even with deterministic set to true, results can vary for some string collations, such as UTF8_LCASE.

from pyspark.sql import functions as sf
df = spark.createDataFrame([(-10,), (0,), (10,)], ["col"])
df.select(sf.mode("col", True)).show()

Sources563

4.Applying a strategy: na.fill, constants and AutoML imputers

In PySpark, DataFrame.fillna and DataFrameNaFunctions.fill (df.na.fill) are aliases. Both take a literal replacement value: 'The replacement value must be an int, float, boolean, or string.' So a median or mode fill in Spark takes two steps. First compute the statistic with sf.median or sf.mode, then pass the result to na.fill. To give different columns different strategies (say a median for a numeric column and a mode for a string column), pass a dict: 'If the value is a dict, then subset is ignored and value must be a mapping from column name (string) to replacement value.'

df = spark.createDataFrame([
    (10, 80.5, "Alice"),
    (5, None, "Bob"),
    (None, None, "Tom")],
    schema=["age", "height", "name"])

df.na.fill({'age': 50, 'name': 'unknown'}).show()

+---+------+-------+
|age|height|   name|
+---+------+-------+
| 10|  80.5|  Alice|
|  5|  NULL|    Bob|
| 50|  NULL|unknown|
+---+------+-------+

A single scalar is easier to write but less careful. In the fillna reference, df.na.fill(50) fills age and height but leaves the string name and boolean bool columns NULL, because 'Columns specified in subset that do not have matching data types are ignored.' A constant fill like this, or the EDA tutorial's df.fillna(0), is a different kind of choice from mean, median or mode. The tutorial describes it as 'A common way to treat NaN or Null values is to replace them with 0 for easier mathematical processing', but the 0 doesn't come from the column's own values.

Databricks AutoML exposes the same three choices as a training option. The imputers parameter of databricks.automl.classify and regress maps column names to a strategy, and as a string 'the value must be one of “mean”, “median”, or “most_frequent”.' For a fixed value, use a dictionary with strategy constant and a fill_value. If you leave a column out, 'AutoML selects a default strategy based on column type and content', the same type-driven choice this lesson has been making by hand. Specifying a non-default method has a side effect: AutoML then skips semantic type detection.

Sources781

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.Dropping rows with missing values is the safe, lossless default; na.drop() only removes rows that are mostly empty.Why is that wrong?

   By default, na.drop() removes every row that has any null or NaN. That also throws away the real values in those rows, which is the cost imputation avoids.

   Covered in Drop or fill: why imputation exists

2. 2.Mean or median imputation can be applied to a string or categorical column, and scikit-learn's mode strategy is called 'mode'.Why is that wrong?

   SimpleImputer supports string and categorical data only with most_frequent (the mode) or constant. The strategy name is most_frequent.

   Covered in Mode: the strategy that works on categories

3. 3.Mode imputation always gives the same fill value for the same data.Why is that wrong?

   When two values are equally frequent, Databricks mode returns either one unless deterministic is set to true. Even then, some string collations can still vary.

   Covered in Mode: the strategy that works on categories

4. 4.df.na.fill(value) with one scalar fills every column of the DataFrame.Why is that wrong?

   Columns whose type doesn't match the replacement value are silently skipped. To fill numeric and string columns with different statistics, pass a dict of column name to value.

   Covered in Applying a strategy: na.fill, constants and AutoML imputers