Mean, Median, or Mode Imputation: Choosing the Fill Statistic

1.Drop the row, or fill the gap?

Real datasets have holes in them. In a Spark DataFrame a missing value appears as NULL. In pandas or NumPy it is usually NaN. Many estimators can't use these rows as they are. The scikit-learn guide says its estimators assume that every value in an array is numerical and meaningful. So before training you have two basic choices: remove the incomplete data, or fill it in.

In Spark, both choices are methods on df.na, which returns a DataFrameNaFunctions object. Its drop method returns a new DataFrame without the rows that contain null or NaN values. Its fill method returns a new DataFrame with the null values replaced by a value you choose. Dropping is simple, but scikit-learn warns that it comes at the price of losing data which may be valuable, even when that data is incomplete. Its suggested alternative is to impute the missing values, which means inferring them from the known part of the data.

The simplest kind of imputation is univariate. scikit-learn defines it as filling values in one feature using only the non-missing values in that same feature. You summarise the column as one number (or one category), then write that value into every gap. This lesson covers the three summaries used for this: the mean, the median, and the mode (most frequent value).

Sources123

2.Computing the mean, median, and mode in Spark

PySpark has an aggregate function for each of the three statistics. sf.mean(col) returns the average of the values in a group and is an alias of sf.avg. sf.median(col) returns the median. sf.mode(col) returns the most frequent value. Because they are aggregates, you can call them with select to get one value for the whole column, or with groupby(...).agg(...) to get one value per group.

import pyspark.sql.functions as sf
df = spark.createDataFrame([(1982, None), (1990, 2), (2000, 4)], ["birth", "age"])
df.select(sf.mean("age")).show()

This is the behaviour imputation needs. The statistic is computed from the values the column actually has, and those are the values used to estimate the missing ones. sf.mode has one extra parameter, deterministic. When several values are tied for the highest frequency, Spark returns any one of them if deterministic is false or not set. If it is true, Spark returns the lowest. That matters when the fill value must be the same on every run.

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

Sources456

3.Which statistic fits which column

The first thing to look at is the column's type. You can't take the mean or median of a column of strings such as country codes or product tiers. Of the three statistics, only the most frequent value works for them. scikit-learn says this directly: SimpleImputer accepts categorical data, as strings or pandas categoricals, only with the 'most_frequent' or 'constant' strategy. Spark's sf.mode is just as general, because it returns the most frequent value of whatever the column holds.

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()

That arithmetic is the reason behind the usual rule for numeric columns. If the values are fairly symmetric, the mean and median are close and either one works. If a few extreme values stretch one tail, the mean moves toward them. The median stays with the bulk of the data, so it gives a more typical fill value. The Databricks and scikit-learn pages document how each statistic is computed, but they don't state a selection rule. The guidance here comes from what the example's numbers show, not from vendor documentation.

Sources53

Exam traps

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

1. 1.When computing a column's mean to use as a fill value, the nulls count as zeros, so the mean comes out too low.Why is that wrong?

   Spark's mean (avg) skips nulls. In the documented example, the ages None, 2 and 4 give avg(age) = 3.0, not 2.0.

   Covered in Computing the mean, median, and mode in Spark

2. 2.sf.mode always returns the same value, so a mode-based fill can be reproduced without any extra setting.Why is that wrong?

   When there is a tie, sf.mode returns any of the tied values unless deterministic is true. Only then does it consistently return the lowest.

   Covered in Computing the mean, median, and mode in Spark

3. 3.A mean or median strategy can be applied to a string categorical column, as long as the imputer is configured correctly.Why is that wrong?

   scikit-learn's SimpleImputer supports string or categorical data only with the 'most_frequent' or 'constant' strategies. For categories, use the mode.

   Covered in Which statistic fits which column