Replacing missing values with subgroup averages not total column averages

parisave

Hi all,

I would like to replace the missing values of attributes with their respective group average instead of  

their column average, eg. :                     averages

state          region       widgets    north     south    east    west

florida         south           5            8.5         3           9        8

california      west         10

new york      north        15

delaware      east           8

georgia        south          1

texas           south          ?   

nevada        west            6

arizona        west            ?

maryland     east          10

mass.          east            ?

maine          north          ?

vermont      north           2

 

 

Andrew

Hello parisave,

 

You can aggregate by region and use the result to join back to the original where there are missing values.

 

Relatively complex - here's an example

 

<?xml version="1.0" encoding="UTF-8"?><process version="7.6.001">
  <context>
    <input/>
    <output/>
    <macros/>
  </context>
  <operator activated="true" class="process" compatibility="7.6.001" expanded="true" name="Process">
    <process expanded="true">
      <operator activated="true" class="subprocess" compatibility="7.6.001" expanded="true" height="82" name="Subprocess" width="90" x="112" y="85">
        <process expanded="true">
          <operator activated="true" class="generate_data_user_specification" compatibility="7.6.001" expanded="true" height="68" name="Generate Data by User Specification" width="90" x="45" y="34">
            <list key="attribute_values">
              <parameter key="state" value="&quot;florida&quot;"/>
              <parameter key="region" value="&quot;south&quot;"/>
              <parameter key="widget" value="5"/>
            </list>
            <list key="set_additional_roles"/>
          </operator>
          <operator activated="true" class="generate_data_user_specification" compatibility="7.6.001" expanded="true" height="68" name="Generate Data by User Specification (2)" width="90" x="45" y="136">
            <list key="attribute_values">
              <parameter key="state" value="&quot;california&quot;"/>
              <parameter key="region" value="&quot;west&quot;"/>
              <parameter key="widget" value="10"/>
            </list>
            <list key="set_additional_roles"/>
          </operator>
          <operator activated="true" class="generate_data_user_specification" compatibility="7.6.001" expanded="true" height="68" name="Generate Data by User Specification (3)" width="90" x="45" y="238">
            <list key="attribute_values">
              <parameter key="state" value="&quot;new york&quot;"/>
              <parameter key="region" value="&quot;north&quot;"/>
              <parameter key="widget" value="15"/>
            </list>
            <list key="set_additional_roles"/>
          </operator>
          <operator activated="true" class="generate_data_user_specification" compatibility="7.6.001" expanded="true" height="68" name="Generate Data by User Specification (4)" width="90" x="45" y="340">
            <list key="attribute_values">
              <parameter key="state" value="&quot;delaware&quot;"/>
              <parameter key="region" value="&quot;east&quot;"/>
              <parameter key="widget" value="8"/>
            </list>
            <list key="set_additional_roles"/>
          </operator>
          <operator activated="true" class="generate_data_user_specification" compatibility="7.6.001" expanded="true" height="68" name="Generate Data by User Specification (5)" width="90" x="45" y="442">
            <list key="attribute_values">
              <parameter key="state" value="&quot;georgia&quot;"/>
              <parameter key="region" value="&quot;south&quot;"/>
              <parameter key="widget" value="1"/>
            </list>
            <list key="set_additional_roles"/>
          </operator>
          <operator activated="true" class="generate_data_user_specification" compatibility="7.6.001" expanded="true" height="68" name="Generate Data by User Specification (6)" width="90" x="45" y="544">
            <list key="attribute_values">
              <parameter key="state" value="&quot;texas&quot;"/>
              <parameter key="region" value="&quot;south&quot;"/>
              <parameter key="widget" value="log(-1)"/>
            </list>
            <list key="set_additional_roles"/>
          </operator>
          <operator activated="true" class="generate_data_user_specification" compatibility="7.6.001" expanded="true" height="68" name="Generate Data by User Specification (7)" width="90" x="45" y="646">
            <list key="attribute_values">
              <parameter key="state" value="&quot;nevada&quot;"/>
              <parameter key="region" value="&quot;west&quot;"/>
              <parameter key="widget" value="6"/>
            </list>
            <list key="set_additional_roles"/>
          </operator>
          <operator activated="true" class="generate_data_user_specification" compatibility="7.6.001" expanded="true" height="68" name="Generate Data by User Specification (8)" width="90" x="45" y="748">
            <list key="attribute_values">
              <parameter key="state" value="&quot;arizona&quot;"/>
              <parameter key="region" value="&quot;west&quot;"/>
              <parameter key="widget" value="log(-1)"/>
            </list>
            <list key="set_additional_roles"/>
          </operator>
          <operator activated="true" class="generate_data_user_specification" compatibility="7.6.001" expanded="true" height="68" name="Generate Data by User Specification (9)" width="90" x="313" y="748">
            <list key="attribute_values">
              <parameter key="state" value="&quot;maryland&quot;"/>
              <parameter key="region" value="&quot;east&quot;"/>
              <parameter key="widget" value="10"/>
            </list>
            <list key="set_additional_roles"/>
          </operator>
          <operator activated="true" class="generate_data_user_specification" compatibility="7.6.001" expanded="true" height="68" name="Generate Data by User Specification (10)" width="90" x="514" y="748">
            <list key="attribute_values">
              <parameter key="state" value="&quot;mass.&quot;"/>
              <parameter key="region" value="&quot;east&quot;"/>
              <parameter key="widget" value="log(-1)"/>
            </list>
            <list key="set_additional_roles"/>
          </operator>
          <operator activated="true" class="generate_data_user_specification" compatibility="7.6.001" expanded="true" height="68" name="Generate Data by User Specification (11)" width="90" x="715" y="748">
            <list key="attribute_values">
              <parameter key="state" value="&quot;main&quot;"/>
              <parameter key="region" value="&quot;north&quot;"/>
              <parameter key="widget" value="log(-1)"/>
            </list>
            <list key="set_additional_roles"/>
          </operator>
          <operator activated="true" class="generate_data_user_specification" compatibility="7.6.001" expanded="true" height="68" name="Generate Data by User Specification (12)" width="90" x="849" y="748">
            <list key="attribute_values">
              <parameter key="state" value="&quot;vermont&quot;"/>
              <parameter key="region" value="&quot;north&quot;"/>
              <parameter key="widget" value="2"/>
            </list>
            <list key="set_additional_roles"/>
          </operator>
          <operator activated="true" class="append" compatibility="7.6.001" expanded="true" height="313" name="Append" width="90" x="514" y="34"/>
          <operator activated="true" class="generate_id" compatibility="7.6.001" expanded="true" height="82" name="Generate ID" width="90" x="648" y="34"/>
          <connect from_op="Generate Data by User Specification" from_port="output" to_op="Append" to_port="example set 1"/>
          <connect from_op="Generate Data by User Specification (2)" from_port="output" to_op="Append" to_port="example set 2"/>
          <connect from_op="Generate Data by User Specification (3)" from_port="output" to_op="Append" to_port="example set 3"/>
          <connect from_op="Generate Data by User Specification (4)" from_port="output" to_op="Append" to_port="example set 4"/>
          <connect from_op="Generate Data by User Specification (5)" from_port="output" to_op="Append" to_port="example set 5"/>
          <connect from_op="Generate Data by User Specification (6)" from_port="output" to_op="Append" to_port="example set 6"/>
          <connect from_op="Generate Data by User Specification (7)" from_port="output" to_op="Append" to_port="example set 7"/>
          <connect from_op="Generate Data by User Specification (8)" from_port="output" to_op="Append" to_port="example set 8"/>
          <connect from_op="Generate Data by User Specification (9)" from_port="output" to_op="Append" to_port="example set 9"/>
          <connect from_op="Generate Data by User Specification (10)" from_port="output" to_op="Append" to_port="example set 10"/>
          <connect from_op="Generate Data by User Specification (11)" from_port="output" to_op="Append" to_port="example set 11"/>
          <connect from_op="Generate Data by User Specification (12)" from_port="output" to_op="Append" to_port="example set 12"/>
          <connect from_op="Append" from_port="merged set" to_op="Generate ID" to_port="example set input"/>
          <connect from_op="Generate ID" from_port="example set output" to_port="out 1"/>
          <portSpacing port="source_in 1" spacing="0"/>
          <portSpacing port="sink_out 1" spacing="0"/>
          <portSpacing port="sink_out 2" spacing="0"/>
        </process>
      </operator>
      <operator activated="true" class="aggregate" compatibility="7.6.001" expanded="true" height="82" name="Aggregate" width="90" x="313" y="85">
        <list key="aggregation_attributes">
          <parameter key="widget" value="average"/>
        </list>
        <parameter key="group_by_attributes" value="region"/>
      </operator>
      <operator activated="true" class="rename" compatibility="7.6.001" expanded="true" height="82" name="Rename" width="90" x="447" y="34">
        <parameter key="old_name" value="average(widget)"/>
        <parameter key="new_name" value="widget"/>
        <list key="rename_additional_attributes"/>
      </operator>
      <operator activated="true" class="generate_id" compatibility="7.6.001" expanded="true" height="82" name="Generate ID (2)" width="90" x="581" y="34"/>
      <operator activated="true" class="filter_examples" compatibility="7.6.001" expanded="true" height="103" name="Filter Examples" width="90" x="447" y="238">
        <parameter key="condition_class" value="no_missing_attributes"/>
        <list key="filters_list"/>
      </operator>
      <operator activated="true" class="select_attributes" compatibility="7.6.001" expanded="true" height="82" name="Select Attributes" width="90" x="581" y="136">
        <parameter key="attribute_filter_type" value="subset"/>
        <parameter key="attributes" value="region|state"/>
      </operator>
      <operator activated="true" class="join" compatibility="7.6.001" expanded="true" height="82" name="Join" width="90" x="782" y="34">
        <parameter key="join_type" value="left"/>
        <parameter key="use_id_attribute_as_key" value="false"/>
        <list key="key_attributes">
          <parameter key="region" value="region"/>
        </list>
      </operator>
      <operator activated="true" class="append" compatibility="7.6.001" expanded="true" height="103" name="Append (2)" width="90" x="916" y="85"/>
      <operator activated="true" class="sort" compatibility="7.6.001" expanded="true" height="82" name="Sort" width="90" x="1050" y="85">
        <parameter key="attribute_name" value="id"/>
      </operator>
      <connect from_op="Subprocess" from_port="out 1" to_op="Aggregate" to_port="example set input"/>
      <connect from_op="Aggregate" from_port="example set output" to_op="Rename" to_port="example set input"/>
      <connect from_op="Aggregate" from_port="original" to_op="Filter Examples" to_port="example set input"/>
      <connect from_op="Rename" from_port="example set output" to_op="Generate ID (2)" to_port="example set input"/>
      <connect from_op="Generate ID (2)" from_port="example set output" to_op="Join" to_port="right"/>
      <connect from_op="Filter Examples" from_port="example set output" to_op="Append (2)" to_port="example set 2"/>
      <connect from_op="Filter Examples" from_port="unmatched example set" to_op="Select Attributes" to_port="example set input"/>
      <connect from_op="Select Attributes" from_port="example set output" to_op="Join" to_port="left"/>
      <connect from_op="Join" from_port="join" to_op="Append (2)" to_port="example set 1"/>
      <connect from_op="Append (2)" from_port="merged set" to_op="Sort" to_port="example set input"/>
      <connect from_op="Sort" from_port="example set output" to_port="result 1"/>
      <portSpacing port="source_input 1" spacing="0"/>
      <portSpacing port="sink_result 1" spacing="0"/>
      <portSpacing port="sink_result 2" spacing="0"/>
    </process>
  </operator>
</process>

 

regards,

 

Andrew