Fill in Missing Values by average of whole row

msacs09

I'm trying to fill in the missing values by averaging the rows or possibly use some row level computation like regression. I'm wondering if i need to do un-pivot it? I would greatly appreciate a sample process that can do this . Below is my sample row level data 

SGolbert

Hi @msacs09

You can do it with the Generate Aggregation operator and a Loop. Something like this:

<?xml version="1.0" encoding="UTF-8"?><process version="9.1.000"><br>  <context><br>    <input/><br>    <output/><br>    <macros/><br>  </context><br>  <operator activated="true" class="process" compatibility="9.1.000" expanded="true" name="Process"><br>    <parameter key="logverbosity" value="init"/><br>    <parameter key="random_seed" value="2001"/><br>    <parameter key="send_mail" value="never"/><br>    <parameter key="notification_email" value=""/><br>    <parameter key="process_duration_for_mail" value="30"/><br>    <parameter key="encoding" value="SYSTEM"/><br>    <process expanded="true"><br>      <operator activated="true" class="read_excel" compatibility="9.1.000" expanded="true" height="68" name="Read Excel" width="90" x="112" y="34"><br>        <parameter key="excel_file" value="C:\Users\sgolbert\Downloads\Sample_Data_1.xlsx"/><br>        <parameter key="sheet_selection" value="sheet number"/><br>        <parameter key="sheet_number" value="1"/><br>        <parameter key="imported_cell_range" value="A1"/><br>        <parameter key="encoding" value="SYSTEM"/><br>        <parameter key="first_row_as_names" value="true"/><br>        <list key="annotations"/><br>        <parameter key="date_format" value=""/><br>        <parameter key="time_zone" value="SYSTEM"/><br>        <parameter key="locale" value="English (United States)"/><br>        <parameter key="read_all_values_as_polynominal" value="false"/><br>        <list key="data_set_meta_data_information"><br>          <parameter key="0" value="ID.true.integer.id"/><br>          <parameter key="1" value="2017-01.true.integer.attribute"/><br>          <parameter key="2" value="2017-02.true.integer.attribute"/><br>          <parameter key="3" value="2017-03.true.integer.attribute"/><br>          <parameter key="4" value="2017-04.true.integer.attribute"/><br>          <parameter key="5" value="2017-05.true.integer.attribute"/><br>          <parameter key="6" value="2017-06.true.integer.attribute"/><br>          <parameter key="7" value="2017-07.true.integer.attribute"/><br>          <parameter key="8" value="2017-08.true.integer.attribute"/><br>          <parameter key="9" value="2017-09.true.integer.attribute"/><br>          <parameter key="10" value="2017-10.true.integer.attribute"/><br>          <parameter key="11" value="2017-11.true.integer.attribute"/><br>          <parameter key="12" value="2017-12.true.integer.attribute"/><br>          <parameter key="13" value="2018-01.true.integer.attribute"/><br>          <parameter key="14" value="2018-02.true.integer.attribute"/><br>          <parameter key="15" value="2018-03.true.integer.attribute"/><br>          <parameter key="16" value="2018-04.true.integer.attribute"/><br>          <parameter key="17" value="2018-05.true.integer.attribute"/><br>          <parameter key="18" value="2018-06.true.integer.attribute"/><br>          <parameter key="19" value="2018-07.true.integer.attribute"/><br>          <parameter key="20" value="2018-08.true.integer.attribute"/><br>          <parameter key="21" value="2018-09.true.integer.attribute"/><br>          <parameter key="22" value="2018-10.true.integer.attribute"/><br>          <parameter key="23" value="2018-11.true.integer.attribute"/><br>          <parameter key="24" value="2018-12.true.integer.attribute"/><br>        </list><br>        <parameter key="read_not_matching_values_as_missings" value="false"/><br>        <parameter key="datamanagement" value="double_array"/><br>        <parameter key="data_management" value="auto"/><br>      </operator><br>      <operator activated="true" class="generate_aggregation" compatibility="9.1.000" expanded="true" height="82" name="Generate Aggregation" width="90" x="246" y="34"><br>        <parameter key="attribute_name" value="row_mean"/><br>        <parameter key="attribute_filter_type" value="all"/><br>        <parameter key="attribute" value=""/><br>        <parameter key="attributes" value=""/><br>        <parameter key="use_except_expression" value="false"/><br>        <parameter key="value_type" value="attribute_value"/><br>        <parameter key="use_value_type_exception" value="false"/><br>        <parameter key="except_value_type" value="time"/><br>        <parameter key="block_type" value="attribute_block"/><br>        <parameter key="use_block_type_exception" value="false"/><br>        <parameter key="except_block_type" value="value_matrix_row_start"/><br>        <parameter key="invert_selection" value="false"/><br>        <parameter key="include_special_attributes" value="false"/><br>        <parameter key="aggregation_function" value="average"/><br>        <parameter key="concatenation_separator" value="|"/><br>        <parameter key="keep_all" value="true"/><br>        <parameter key="ignore_missings" value="true"/><br>        <parameter key="ignore_missing_attributes" value="false"/><br>      </operator><br>      <operator activated="true" class="numerical_to_real" compatibility="9.1.000" expanded="true" height="82" name="Numerical to Real" width="90" x="380" y="34"><br>        <parameter key="attribute_filter_type" value="all"/><br>        <parameter key="attribute" value=""/><br>        <parameter key="attributes" value=""/><br>        <parameter key="use_except_expression" value="false"/><br>        <parameter key="value_type" value="numeric"/><br>        <parameter key="use_value_type_exception" value="false"/><br>        <parameter key="except_value_type" value="real"/><br>        <parameter key="block_type" value="value_series"/><br>        <parameter key="use_block_type_exception" value="false"/><br>        <parameter key="except_block_type" value="value_series_end"/><br>        <parameter key="invert_selection" value="false"/><br>        <parameter key="include_special_attributes" value="false"/><br>      </operator><br>      <operator activated="true" class="extract_macro" compatibility="9.1.000" expanded="true" height="68" name="Extract Macro (2)" width="90" x="514" y="34"><br>        <parameter key="macro" value="sample_size"/><br>        <parameter key="macro_type" value="number_of_examples"/><br>        <parameter key="statistics" value="average"/><br>        <parameter key="attribute_name" value=""/><br>        <list key="additional_macros"/><br>      </operator><br>      <operator activated="true" class="concurrency:loop" compatibility="9.1.000" expanded="true" height="82" name="Loop" width="90" x="648" y="34"><br>        <parameter key="number_of_iterations" value="%{sample_size}"/><br>        <parameter key="iteration_macro" value="iteration"/><br>        <parameter key="reuse_results" value="false"/><br>        <parameter key="enable_parallel_execution" value="true"/><br>        <process expanded="true"><br>          <operator activated="true" class="filter_example_range" compatibility="9.1.000" expanded="true" height="82" name="Filter Example Range" width="90" x="112" y="34"><br>            <parameter key="first_example" value="%{iteration}"/><br>            <parameter key="last_example" value="%{iteration}"/><br>            <parameter key="invert_filter" value="false"/><br>          </operator><br>          <operator activated="true" class="extract_macro" compatibility="9.1.000" expanded="true" height="68" name="Extract Macro" width="90" x="380" y="34"><br>            <parameter key="macro" value="replacement"/><br>            <parameter key="macro_type" value="data_value"/><br>            <parameter key="statistics" value="average"/><br>            <parameter key="attribute_name" value="row_mean"/><br>            <parameter key="example_index" value="1"/><br>            <list key="additional_macros"/><br>          </operator><br>          <operator activated="true" class="replace_missing_values" compatibility="9.1.000" expanded="true" height="103" name="Replace Missing Values" width="90" x="648" y="34"><br>            <parameter key="return_preprocessing_model" value="false"/><br>            <parameter key="create_view" value="false"/><br>            <parameter key="attribute_filter_type" value="all"/><br>            <parameter key="attribute" value=""/><br>            <parameter key="attributes" value=""/><br>            <parameter key="use_except_expression" value="false"/><br>            <parameter key="value_type" value="attribute_value"/><br>            <parameter key="use_value_type_exception" value="false"/><br>            <parameter key="except_value_type" value="time"/><br>            <parameter key="block_type" value="attribute_block"/><br>            <parameter key="use_block_type_exception" value="false"/><br>            <parameter key="except_block_type" value="value_matrix_row_start"/><br>            <parameter key="invert_selection" value="false"/><br>            <parameter key="include_special_attributes" value="false"/><br>            <parameter key="default" value="value"/><br>            <list key="columns"/><br>            <parameter key="replenishment_value" value="%{replacement}"/><br>          </operator><br>          <connect from_port="input 1" to_op="Filter Example Range" to_port="example set input"/><br>          <connect from_op="Filter Example Range" from_port="example set output" to_op="Extract Macro" to_port="example set"/><br>          <connect from_op="Extract Macro" from_port="example set" to_op="Replace Missing Values" to_port="example set input"/><br>          <connect from_op="Replace Missing Values" from_port="example set output" to_port="output 1"/><br>          <portSpacing port="source_input 1" spacing="0"/><br>          <portSpacing port="source_input 2" spacing="0"/><br>          <portSpacing port="sink_output 1" spacing="0"/><br>          <portSpacing port="sink_output 2" spacing="0"/><br>        </process><br>      </operator><br>      <operator activated="true" class="append" compatibility="9.1.000" expanded="true" height="82" name="Append" width="90" x="782" y="34"><br>        <parameter key="datamanagement" value="double_array"/><br>        <parameter key="data_management" value="auto"/><br>        <parameter key="merge_type" value="all"/><br>      </operator><br>      <connect from_op="Read Excel" from_port="output" to_op="Generate Aggregation" to_port="example set input"/><br>      <connect from_op="Generate Aggregation" from_port="example set output" to_op="Numerical to Real" to_port="example set input"/><br>      <connect from_op="Numerical to Real" from_port="example set output" to_op="Extract Macro (2)" to_port="example set"/><br>      <connect from_op="Extract Macro (2)" from_port="example set" to_op="Loop" to_port="input 1"/><br>      <connect from_op="Loop" from_port="output 1" to_op="Append" to_port="example set 1"/><br>      <connect from_op="Append" from_port="merged set" to_port="result 1"/><br>      <portSpacing port="source_input 1" spacing="0"/><br>      <portSpacing port="sink_result 1" spacing="0"/><br>      <portSpacing port="sink_result 2" spacing="0"/><br>    </process><br>  </operator><br></process><br><br>

It is a bit clumsy, but I didn't find an easier way.

Regards,

Sebastian