您可以通過給定一個缺失值的配置列表,來實現將輸入表的缺失值用指定的值來填充。
背景資訊
將數值型的空值替換為最大值、最小值、均值或者一個自訂的值。
將字元型的空值、Null 字元串、空值和Null 字元串,指定值替換為一個自訂的值。
待填充的缺失值可以選擇空值或Null 字元,也可以自訂。
缺失值如果選擇Null 字元串,則填充的目標列應是STRING型。
數值型替換可以自訂,也可以直接選擇替換成數值最大值、最小值或者均值。
組件配置
您可以使用以下任意一種方式,配置缺失值填充組件參數。
方式一:可視化方式
在Designer工作流程頁面配置組件參數。
頁簽 | 參數 | 描述 |
參數設定 | 填充的欄位 | 預設全選,多餘列不影響預測結果。 |
原值 |
| |
替換為 |
| |
configs | ID列。 說明 勾選進階選項時展示。 | |
執行調優 | 計算核心數 | |
每個核記憶體數 |
方式二:PAI命令方式
使用PAI命令方式,配置該組件參數。您可以使用SQL指令碼組件進行PAI命令調用,詳情請參見SQL指令碼。
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";
參數名稱 | 是否必選 | 參數描述 | 預設值 |
inputTableName | 是 | 輸入表的表名。 | 無 |
inputTablePartitions | 否 | 輸入表中,參與訓練的分區。支援以下格式:
說明 如果指定多個分區,則使用英文逗號(,)分隔。 | 所有分區 |
outputTableName | 是 | 輸出結果表。 | 無 |
configs | 是 | 缺失值填充的配置。 例如格式
| 無 |
outputParaTableName | 是 | 配置輸出表。 | 輸出表1為非分區表 |
inputParaTableName | 否 | 配置輸入表。 | 無 |
lifecycle | 否 | 輸出表的生命週期,取值範圍為[1,3650]。 | 無 |
coreNum | 否 | 計算的核心數目,取值為正整數。 | 系統自動分配 |
memSizePerCore | 否 | 每個核心的記憶體(單位是兆),取值範圍為(1, 65536)。 | 系統自動分配 |
樣本
使用SQL語句,產生測試資料。
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;
輸入資料說明。
+------------+------------+------------+-------------+--------------+ | 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 | +------------+------------+------------+-------------+--------------+
運行命令。
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";
運行結果。
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}} | +------------+------------+