Missing data imputation is a method for handling missing data in datasets, aiming to enhance data integrity and model performance by inferring and replacing missing data. Common imputation techniques include filling with minimum, maximum, mean, or custom values. These techniques help mitigate the adverse effects of incomplete data on model training and prediction.
Configure the component
Method 1: Configure the component on the pipeline page
On the pipeline details page in Machine Learning Designer, add the Missing Data Imputation component to the pipeline and configure the parameters described in the following table.
Tab | Parameter | Description |
Fields Setting | Columns to Impute | Select the columns for which you want to impute data. |
Original Value | The original value of the column for which you want to impute data. Valid values:
| |
Replace With | The new value of the column for which you want to impute data. Valid values:
| |
Configs | Configure a custom replacement policy. The value is in the Note This parameter is displayed only if you select Advanced Options. | |
Tuning | Cores | The number of cores. |
Memory Size per Core | The memory size of each core. Unit: MB. |
Method 2: Configure the component by using PAI commands
Configure the component parameters by using PAI commands. You can use the SQL Script component to call PAI commands. For more information, see Scenario 4: Execute PAI commands within the SQL script component.
PAI -name FillMissingValues
-project algo_public
-Dconfigs="poutcome,null-empty,testing"
-DoutputParaTableName="test_input_model_output"
-DoutputTableName="test_3"
-DinputTablePartitions="pt=20150501"
-DinputTableName="bank_data_partition";Parameter | Required | Default value | Description |
inputTableName | Yes | No default value | The name of the input table. |
inputTablePartitions | No | All partitions | The partitions that are selected from the input table for training. The following formats are supported:
Note If you specify multiple partitions, separate them with commas (,). |
outputTableName | Yes | No default value | The name of the output table. |
configs | Yes | No default value | The values that you want to use to impute missing values. For example, in
|
outputParaTableName | Yes | Output Parameter Table 1 that is a non-partitioned table | The name of the output parameter table. |
inputParaTableName | No | No default value | The name of the input parameter table. |
lifecycle | No | No default value | The lifecycle of the output table. Valid values: [1,3650]. |
coreNum | No | Determined by the system | The number of cores used in computing. The value must be a positive integer. |
memSizePerCore | No | Determined by the system | The memory size of each core. Unit: MB. Valid values: (1,65536). |
Example
Execute the following SQL statements to generate test data:
drop table if exists fill_missing_values_test_input; create table fill_missing_values_test_input( col_string string, col_bigint bigint, col_double double, col_boolean boolean, col_datetime datetime); insert overwrite table fill_missing_values_test_input select * from ( select '01' as col_string, 10 as col_bigint, 10.1 as col_double, True as col_boolean, cast('2016-07-01 10:00:00' as datetime) as col_datetime union all select cast(null as string) as col_string, 11 as col_bigint, 10.2 as col_double, False as col_boolean, cast('2016-07-02 10:00:00' as datetime) as col_datetime union all select '02' as col_string, cast(null as bigint) as col_bigint, 10.3 as col_double, True as col_boolean, cast('2016-07-03 10:00:00' as datetime) as col_datetime union all select '03' as col_string, 12 as col_bigint, cast(null as double) as col_double, False as col_boolean, cast('2016-07-04 10:00:00' as datetime) as col_datetime union all select '04' as col_string, 13 as col_bigint, 10.4 as col_double, cast(null as boolean) as col_boolean, cast('2016-07-05 10:00:00' as datetime) as col_datetime union all select '05' as col_string, 14 as col_bigint, 10.5 as col_double, True as col_boolean, cast(null as datetime) as col_datetime ) tmp;Input data:
+------------+------------+------------+-------------+--------------+ | col_string | col_bigint | col_double | col_boolean | col_datetime | +------------+------------+------------+-------------+--------------+ | 04 | 13 | 10.4 | NULL | 2016-07-05 10:00:00 | | 02 | NULL | 10.3 | true | 2016-07-03 10:00:00 | | 03 | 12 | NULL | false | 2016-07-04 10:00:00 | | NULL | 11 | 10.2 | false | 2016-07-02 10:00:00 | | 01 | 10 | 10.1 | true | 2016-07-01 10:00:00 | | 05 | 14 | 10.5 | true | NULL | +------------+------------+------------+-------------+--------------+Run the following commands:
drop table if exists fill_missing_values_test_input_output; drop table if exists fill_missing_values_test_input_model_output; PAI -name FillMissingValues -project algo_public -Dconfigs="col_double,null,mean;col_string,null-empty,str_type_empty;col_bigint,null,max;col_boolean,null,true;col_datetime,null,2016-07-06 10:00:00" -DoutputParaTableName="fill_missing_values_test_input_model_output" -Dlifecycle="28" -DoutputTableName="fill_missing_values_test_input_output" -DinputTableName="fill_missing_values_test_input"; drop table if exists fill_missing_values_test_input_output_using_model; drop table if exists fill_missing_values_test_input_output_using_model_model_output; PAI -name FillMissingValues -project algo_public -DoutputParaTableName="fill_missing_values_test_input_output_using_model_model_output" -DinputParaTableName="fill_missing_values_test_input_model_output" -Dlifecycle="28" -DoutputTableName="fill_missing_values_test_input_output_using_model" -DinputTableName="fill_missing_values_test_input";View the return results.
fill_missing_values_test_input_output
+------------+------------+------------+-------------+--------------+ | col_string | col_bigint | col_double | col_boolean | col_datetime | +------------+------------+------------+-------------+--------------+ | 04 | 13 | 10.4 | true | 2016-07-05 10:00:00 | | 02 | 14 | 10.3 | true | 2016-07-03 10:00:00 | | 03 | 12 | 10.3 | false | 2016-07-04 10:00:00 | | str_type_empty | 11 | 10.2 | false | 2016-07-02 10:00:00 | | 01 | 10 | 10.1 | true | 2016-07-01 10:00:00 | | 05 | 14 | 10.5 | true | 2016-07-06 10:00:00 | +------------+------------+------------+-------------+--------------+fill_missing_values_test_input_model_output
+------------+------------+ | feature | json | +------------+------------+ | col_string | {"name": "fillMissingValues", "type": "string", "paras":{"missing_value_type": "null-empty", "replaced_value": "str_type_empty"}} | | col_bigint | {"name": "fillMissingValues", "type": "bigint", "paras":{"missing_value_type": "null", "replaced_value": 14}} | | col_double | {"name": "fillMissingValues", "type": "double", "paras":{"missing_value_type": "null", "replaced_value": 10.3}} | | col_boolean | {"name": "fillMissingValues", "type": "boolean", "paras":{"missing_value_type": "null", "replaced_value": 1}} | | col_datetime | {"name": "fillMissingValues", "type": "datetime", "paras":{"missing_value_type": "null", "replaced_value": 1467770400000}} | +------------+------------+fill_missing_values_test_input_output_using_model
+------------+------------+------------+-------------+--------------+ | col_string | col_bigint | col_double | col_boolean | col_datetime | +------------+------------+------------+-------------+--------------+ | 04 | 13 | 10.4 | true | 2016-07-05 10:00:00 | | 02 | 14 | 10.3 | true | 2016-07-03 10:00:00 | | 03 | 12 | 10.3 | false | 2016-07-04 10:00:00 | | str_type_empty | 11 | 10.2 | false | 2016-07-02 10:00:00 | | 01 | 10 | 10.1 | true | 2016-07-01 10:00:00 | | 05 | 14 | 10.5 | true | 2016-07-06 10:00:00 | +------------+------------+------------+-------------+--------------+fill_missing_values_test_input_output_using_model_model_output
+------------+------------+ | feature | json | +------------+------------+ | col_string | {"name": "fillMissingValues", "type": "string", "paras":{"missing_value_type": "null-empty", "replaced_value": "str_type_empty"}} | | col_bigint | {"name": "fillMissingValues", "type": "bigint", "paras":{"missing_value_type": "null", "replaced_value": 14}} | | col_double | {"name": "fillMissingValues", "type": "double", "paras":{"missing_value_type": "null", "replaced_value": 10.3}} | | col_boolean | {"name": "fillMissingValues", "type": "boolean", "paras":{"missing_value_type": "null", "replaced_value": 1}} | | col_datetime | {"name": "fillMissingValues", "type": "datetime", "paras":{"missing_value_type": "null", "replaced_value": 1467770400000}} | +------------+------------+