Easiest way to remove empty rows and attributes?

Hallo Kayman,

Could Select Attributes be a solution?

Maerkli

Hi Maerkly.

I don't thinks so, the behaviour of the 'no_missing_values' seems to be that as soon as one value is missing you (rightfully) get a hit, so in this case also Col2 and Col3 would be impacted since they have at least one missing value. 

I'd need kind of the opposite, as soon as there is a value it's ok, otherwise skip. Or something like 'if all values are missing' skip, otherwise keep.

I am back, Kayman. It is just a suggestion: have a look at RM Studio/Repository/Training Resources/ETL-Advanced/Aggregations/Hotel App Aggregations & Pivoting Solution. With Pivot, you get another perspective on your data and you can apply another treatment like Replace Missing Values. Keep us posted.

Maerkli

I think you may be making this more complicated than necessary.  There are two simple ETL operators that can solve these issues. Remove Useless Attributes will do the trick for you to get rid of attributes (columns) that are all missing (either nominal or numerical), and Filter Examples has a pre-defined condition in the dropdown for "no missing attributes" that should get rid of all your blank examples (rows).

Not really, as the missing attributes operator just looks if there is at least one missing value, where I need to have the option to see if ALL values are missing. I don't care for the occasional missing value, just if my whole example or column is missing. 

So if I use the missing attributes operator and loop it through my collection, only the sets without any single missing attribute are returned, which is what the operator promised but not what I wanted to see...

@kayman Got it, but I think it can still be done with the two operators I describe.  In Filter Examples you just have to set the conditions to each attribute to be "is missing" with "match all" logic and then invert the operator.  No looping is needed with either.  I think that the the attached process does exactly what you want to do (using an exampleset similar to your original sample) with just the two operators I referenced.

<?xml version="1.0" encoding="UTF-8"?><process version="9.0.003">
 <context>
 <input/>
 <output/>
 <macros/>
 </context>
 <operator activated="true" class="process" compatibility="9.0.003" expanded="true" name="Process">
 <process expanded="true">
 <operator activated="true" breakpoints="after" class="operator_toolbox:create_exampleset" compatibility="1.5.000" expanded="true" height="68" name="Create ExampleSet" width="90" x="112" y="34">
 <parameter key="generator_type" value="comma_separated_text"/>
 <list key="function_descriptions"/>
 <list key="numeric_series_configuration"/>
 <list key="date_series_configuration"/>
 <list key="date_series_configuration (interval)"/>
 <parameter key="input_csv_text" value="Row,Att1,Att2,Att3&#10;1,4,7,&#10;2,6,,&#10;3,5,8,&#10;,,,&#10;5,2,,&#10;"/>
 </operator>
 <operator activated="true" breakpoints="after" class="remove_useless_attributes" compatibility="9.0.003" expanded="true" height="82" name="Remove Useless Attributes" width="90" x="246" y="34"/>
 <operator activated="true" breakpoints="after" class="filter_examples" compatibility="9.0.003" expanded="true" height="103" name="Filter Examples" width="90" x="380" y="34">
 <parameter key="invert_filter" value="true"/>
 <list key="filters_list">
 <parameter key="filters_entry_key" value="Row.is_missing."/>
 <parameter key="filters_entry_key" value="Att1.is_missing."/>
 <parameter key="filters_entry_key" value="Att2.is_missing."/>
 </list>
 </operator>
 <connect from_op="Create ExampleSet" from_port="output" to_op="Remove Useless Attributes" to_port="example set input"/>
 <connect from_op="Remove Useless Attributes" from_port="example set output" to_op="Filter Examples" to_port="example set input"/>
 <connect from_op="Filter Examples" 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> 

And if you don't want to declare a separate condition for each attribute (maybe you have a lot of attributes in your real dataset) then you can get around that pretty easily by using Generate Aggregation to count the nonmissings and then filter purely based on that, as in the attached process.

<?xml version="1.0" encoding="UTF-8"?><process version="9.0.003">
  <context>
    <input/>
    <output/>
    <macros/>
  </context>
  <operator activated="true" class="process" compatibility="9.0.003" expanded="true" name="Process">
    <process expanded="true">
      <operator activated="true" breakpoints="after" class="operator_toolbox:create_exampleset" compatibility="1.5.000" expanded="true" height="68" name="Create ExampleSet" width="90" x="112" y="34">
        <parameter key="generator_type" value="comma_separated_text"/>
        <list key="function_descriptions"/>
        <list key="numeric_series_configuration"/>
        <list key="date_series_configuration"/>
        <list key="date_series_configuration (interval)"/>
        <parameter key="input_csv_text" value="Row,Att1,Att2,Att3,Att4&#10;1,4,7,,&quot;yes&quot;&#10;2,6,,,&quot;no&quot;&#10;3,5,8,,&quot;yes&quot;&#10;,,,,&#10;5,,2,,&quot;green&quot;&#10;"/>
      </operator>
      <operator activated="true" breakpoints="after" class="trim" compatibility="9.0.003" expanded="true" height="82" name="Trim" width="90" x="246" y="34"/>
      <operator activated="true" breakpoints="after" class="remove_useless_attributes" compatibility="9.0.003" expanded="true" height="82" name="Remove Useless Attributes" width="90" x="380" y="34"/>
      <operator activated="true" breakpoints="after" class="generate_aggregation" compatibility="9.0.003" expanded="true" height="82" name="Generate Aggregation" width="90" x="514" y="34">
        <parameter key="attribute_name" value="count"/>
        <parameter key="aggregation_function" value="count"/>
      </operator>
      <operator activated="true" class="filter_examples" compatibility="9.0.003" expanded="true" height="103" name="Filter Examples" width="90" x="648" y="34">
        <list key="filters_list">
          <parameter key="filters_entry_key" value="count.gt.0"/>
        </list>
      </operator>
      <connect from_op="Create ExampleSet" from_port="output" to_op="Trim" to_port="example set input"/>
      <connect from_op="Trim" from_port="example set output" to_op="Remove Useless Attributes" to_port="example set input"/>
      <connect from_op="Remove Useless Attributes" from_port="example set output" to_op="Generate Aggregation" to_port="example set input"/>
      <connect from_op="Generate Aggregation" from_port="example set output" to_op="Filter Examples" to_port="example set input"/>
      <connect from_op="Filter Examples" 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>

Yeah, but my data is unfortunatly not that simple :-)

I do not know up front how many attributes I have, let alone the name, so I'm back to looping through my content again.

Also, the remove useless atributes has the unwanted side effect that when my column actually contains too many times the same content it will also be removed.

So if I have something like

COL1,COL2,COL3

?,1,2

?,2,2

both COL1 and COL3 will be removed. Which is correct behaviour for the operator but again a bit too greedy for my purpose.

Seems afterall that for a simple problem it requires a complex solution... 

Hi,

i usually go for something like aggregate without missings - #examples and check if this is 0. If yes, i do data to weights and got something.

Maybe this is not too straight foward and we should put something into toolbox for it? 

BR,

Martin

CC: @tftemme

Hi Martin, that's my current strategy also (and even having some mongo in the middle :-))

I was just wondering if there were easier ways, as this looks to me like quite a common scenario.

I can help myself, but if you can add it in the toolbox it would be handy indeed.

Hi @kayman, Hi @mschmitz,

From my point of view this would be a handy addition to the Toolbox.

I will have a look, if I find time for this.

Best regards,
Fabian