Filling Missing Values with fillna, SimpleImputer, and AutoML imputers

1.Writing the statistic back with df.na.fill

Imputing in Spark takes two steps. First you compute the statistic, for example with sf.mean, sf.median or sf.mode. Then you replace the nulls with it. DataFrame.fillna and DataFrameNaFunctions.fill, which you reach through df.na.fill, are aliases of each other, so they behave the same way. Both return a new DataFrame. Look at what value accepts: an int, float, string, bool, or a dict. It does not take a column expression. So you can't pass sf.median(...) straight into fill. You compute the median as a number first, then pass that number.

A table usually needs a different statistic for each column, such as a median for a skewed numeric column and the mode for a category. The dict form handles this in one call. You map each column name to its own replacement value, and columns that aren't in the dict keep their nulls.

df.na.fill({'age': 50, 'name': 'unknown'}).show()
# +---+------+-------+----+
# |age|height|   name|bool|
# +---+------+-------+----+
# | 10|  80.5|  Alice|NULL|
# |  5|  NULL|    Bob|NULL|
# | 50|  NULL|    Tom|NULL|
# | 50|  NULL|unknown|true|
# +---+------+-------+----+

The subset parameter limits a scalar fill to the columns you name. There are two rules to remember. First, any column in subset whose data type doesn't match the value is skipped without an error. Second, if value is a dict, subset is ignored entirely, because the dict already names the columns.

df.na.fill(value='Spark',  ? ='name').show()

Sources12

2.SimpleImputer: learn the statistic, then apply it

On pandas or NumPy data, scikit-learn's SimpleImputer does both steps in one object. The strategy argument chooses the statistic: 'mean', 'median' or 'most_frequent'. missing_values tells the imputer which placeholder means missing, such as np.nan. fit learns one statistic per column, and transform writes those learned values into whatever data you pass it.

>>> import numpy as np
>>> from sklearn.impute import SimpleImputer
>>> imp = SimpleImputer(missing_values=np.nan, strategy='mean')
>>> imp.fit([[1, 2], [np.nan, 3], [7, 6]])
SimpleImputer()
>>> X = [[np.nan, 2], [6, np.nan], [7, 6]]
>>> print(imp.transform(X))

For categorical columns you change the strategy to the most frequent value, which works on string and pandas categorical data. Because the imputer is fitted, it can sit inside a scikit-learn Pipeline with the model. One behaviour to know: by default the scikit-learn imputers drop any feature that is entirely missing, and that changes the number of columns in the output.

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

Sources34

3.Setting the strategy in Databricks AutoML

Databricks AutoML lets you make the same choice without writing fill code. The imputers parameter of databricks.automl.classify and databricks.automl.regress is a dictionary. Each key is a column name, and each value is a string or dictionary describing the imputation strategy. The allowed strategy strings are the scikit-learn names mean, median and most_frequent. To fill with a known value, you pass {"strategy": "constant", "fill_value": ...}. In the UI, the same option is the Impute with column in the table schema.

If you don't specify a strategy for a column, AutoML picks a default based on the column's type and content. Setting one yourself has a side effect: AutoML does not run semantic type detection on any column that has a non-default imputation method.

Sources35

Exam traps

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

1. 1.Passing subset together with a dict value restricts the fill to the subset columns.Why is that wrong?

   When value is a dict, Spark ignores subset and uses only the dict's column-to-value mapping.

   Covered in Writing the statistic back with df.na.fill

2. 2.Listing a string column in subset while filling with a numeric median raises an error, or turns the median into a string and fills it in.Why is that wrong?

   Columns in subset whose data type doesn't match the value are skipped, so their nulls remain and no error is raised.

   Covered in Writing the statistic back with df.na.fill

3. 3.SimpleImputer always returns the same number of columns it was given.Why is that wrong?

   By default, scikit-learn imputers drop features that are entirely missing, which changes the shape of the output.

   Covered in SimpleImputer: learn the statistic, then apply it