The ML.ONE_HOT_ENCODER function

This document describes theML.ONE_HOT_ENCODER function, which lets youencode a string expression using aone-hotordummyencoding scheme.

The encoding vocabulary is sorted alphabetically.NULL values and categoriesthat aren't in the vocabulary are encoded with anindex value of0. If youuse dummy encoding, the dropped category is encoded with avalue of0.

When used in theTRANSFORM clause,the vocabulary and dropped category values calculated during training, alongwith the topk and frequency threshold values that you specified, areautomatically 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.ONE_HOT_ENCODER(string_expression [, drop] [, top_k] [, frequency_threshold]) OVER()

Arguments

ML.ONE_HOT_ENCODER takes the following arguments:

• string_expression: theSTRING expression to encode.
• drop: aSTRING value that specifies whether the function dropsa category. Valid values are as follows:
  • none: Retain all categories. This is the default value.
  • most_frequent: Drop the most frequent category found inthe string expression. Selecting this value causes the function to usedummy encoding.
• top_k: anINT64 value that specifies the number of categoriesincluded in the encoding vocabulary. The function selects thetop_kmost frequent categories in the data and uses those; categories below thisthreshold are encoded to0. This value must be less than1,000,000to avoid problems due to high dimensionality. The default value is32,000.
• frequency_threshold: anINT64 value that limits the categoriesincluded in the encoding vocabulary based on category frequency. Thefunction uses categories whose frequency is greater than or equal tofrequency_threshold; categories below this threshold are encoded to0.The default value is5.

Output

ML.ONE_HOT_ENCODER returns an array of struct values, in the formARRAY<STRUCT<INT64, FLOAT64>>. The first element in the struct provides theindex of the encoded string expression, and the second element provides thevalue of the encoded string expression.

Example

The following example performs dummy encoding on a set of string expressions.It limits the encoding vocabulary to the ten categories that occur the mostfrequently in the data and that also occur zero or more times.

SELECTf,ML.ONE_HOT_ENCODER(f,'most_frequent',10,0)OVER()ASoutputFROMUNNEST([NULL,'a','b','b','c','c','c','d','d'])ASfORDERBYf;

The output looks similar to the following:

+------+-----------------------------+|  f   | output.index | output.value |+------+--------------+--------------+| NULL |  0           |  1.0         || a    |  1           |  1.0         || b    |  2           |  1.0         || b    |  2           |  1.0         || c    |  3           |  0.0         || c    |  3           |  0.0         || c    |  3           |  0.0         || d    |  4           |  1.0         || d    |  4           |  1.0         |+------+-----------------------------+

What's next

• For information about feature preprocessing, seeFeature preprocessing overview.