The ML.IMPUTER function

This document describes theML.IMPUTER function, which lets you replaceNULL values in a string or numerical expression. You can replaceNULL valueswith the most frequently used value for string expressions, or themean ormedian value for numerical expressions.

When used in theTRANSFORM clause,the values calculated during training for mean, median, and most frequentlyused value are automatically used in prediction.

You can use this function with models that supportmanual feature preprocessing. For moreinformation, see the following documents:

• End-to-end user journeys for ML models
• Contribution analysis user journey

Syntax

ML.IMPUTER(expression, strategy) OVER()

Arguments

ML.IMPUTER takes the following arguments:

• expression: thenumericalorSTRING expression to impute.
• strategy: aSTRING value that specifies how to replaceNULL values.Valid values are as follows:
  • mean: the mean ofexpression. You can only use this value withnumerical expressions.
  • median: the median ofexpression. You can only use this value withnumerical expressions.
  • most_frequent: the most frequent value inexpression.

Output

ML.IMPUTER returns aFLOAT64 (for numerical expressions) orSTRING(for string expressions) value that contains the replacement for theNULL value.

Examples

Example 1

The following example imputes numerical expressions:

SELECTf,ML.IMPUTER(f,'mean')OVER()ASoutputFROMUNNEST([NULL,-3,-3,-3,1,2,3,4,5])ASfORDERBYf;

The output looks similar to the following:

+------+--------+|  f   | output |+------+--------+| NULL |   0.75 ||   -3 |   -3.0 ||   -3 |   -3.0 ||   -3 |   -3.0 ||    1 |    1.0 ||    2 |    2.0 ||    3 |    3.0 ||    4 |    4.0 ||    5 |    5.0 |+------+--------+

Example 2

The following example imputes string expressions:

SELECTf,ML.IMPUTER(f,'most_frequent')OVER()ASoutputFROMUNNEST([NULL,NULL,NULL,NULL,'a','a','b','b','c','c','c'])ASfORDERBYf;

The output looks similar to the following:

+------+--------+|  f   | output |+------+--------+| NULL | c      || NULL | c      || NULL | c      || NULL | c      || a    | a      || a    | a      || b    | b      || b    | b      || c    | c      || c    | c      || c    | c      |+------+--------+

What's next

• For information about feature preprocessing, seeFeature preprocessing overview.