Transform table to result view

pallav · June 2020

Hi i have table in this below form.

description	Category	count
Z	Available	27.0
Z	Not Available	14169.0
Z	Less Available	863.0
Y	Available	50.0
Y	Not Available	58971.0
Y	Less Available	888.0
OTHER	Available	380.0
OTHER	Not Available	272343.0
OTHER	Less Available	3231.0
X	Available	1564.0
X	Not Available	1313.0
X	Less Available	3087.0

How can i transform same into result view like below:

description	Avaialble	Less Available	Not Available
X	1,564	3,087	1,313
Y	50	888	58,971
Z	27	863	14,169
OTHER	380	3,231	272,343

hbajpai · June 2020

Hey @pallav,

This can be achieved easily with pivot operator, where in you can group by description and column grouping by category at last use aggregation sum over count, that should do it.

hbajpai · June 2020

Hey @pallav
I tired it on my end and it does work. Check out the XML.

Image: https://us.v-cdn.net/6030995/uploads/editor/8r/d6pq1xy0gnw7.png

<?xml version="1.0" encoding="UTF-8"?><process version="9.7.000">
  <context>
    <input/>
    <output/>
    <macros/>
  </context>
  <operator activated="true" class="process" compatibility="6.0.002" expanded="true" name="Process" origin="GENERATED_TUTORIAL">
    <parameter key="logverbosity" value="init"/>
    <parameter key="random_seed" value="2001"/>
    <parameter key="send_mail" value="never"/>
    <parameter key="notification_email" value=""/>
    <parameter key="process_duration_for_mail" value="30"/>
    <parameter key="encoding" value="SYSTEM"/>
    <process expanded="true">
      <operator activated="true" class="utility:create_exampleset" compatibility="9.7.000" expanded="true" height="68" name="Create ExampleSet" width="90" x="45" y="34">
        <parameter key="generator_type" value="comma separated text"/>
        <parameter key="number_of_examples" value="100"/>
        <parameter key="use_stepsize" value="false"/>
        <list key="function_descriptions"/>
        <parameter key="add_id_attribute" value="false"/>
        <list key="numeric_series_configuration"/>
        <list key="date_series_configuration"/>
        <list key="date_series_configuration (interval)"/>
        <parameter key="date_format" value="yyyy-MM-dd HH:mm:ss"/>
        <parameter key="time_zone" value="SYSTEM"/>
        <parameter key="input_csv_text" value="description, Category, count&#10;Z, Available,27.0&#10;Z,Not Available , &#9;14169.0&#10;Z,&#9;Less Available&#9;,863.0&#10;Y,&#9;Available&#9;,50.0&#10;Y,&#9;Not Available&#9;,58971.0&#10;Y,&#9;Less Available,&#9;888.0&#10;OTHER,&#9;Available,&#9;380.0&#10;OTHER,&#9;Not Available,&#9;272343.0&#10;OTHER,&#9;Less Available,&#9;3231.0&#10;X,&#9;Available,&#9;1564.0&#10;X,&#9;Not Available, 1313.0&#10;X,Less Available,&#9;3087.0&#10;"/>
        <parameter key="column_separator" value=","/>
        <parameter key="parse_all_as_nominal" value="false"/>
        <parameter key="decimal_point_character" value="."/>
        <parameter key="trim_attribute_names" value="true"/>
      </operator>
      <operator activated="true" class="replace" compatibility="9.7.000" expanded="true" height="82" name="Replace" width="90" x="179" y="34">
        <parameter key="attribute_filter_type" value="all"/>
        <parameter key="attribute" value=""/>
        <parameter key="attributes" value=""/>
        <parameter key="use_except_expression" value="false"/>
        <parameter key="value_type" value="nominal"/>
        <parameter key="use_value_type_exception" value="false"/>
        <parameter key="except_value_type" value="file_path"/>
        <parameter key="block_type" value="single_value"/>
        <parameter key="use_block_type_exception" value="false"/>
        <parameter key="except_block_type" value="single_value"/>
        <parameter key="invert_selection" value="false"/>
        <parameter key="include_special_attributes" value="false"/>
        <parameter key="replace_what" value="[  \t]+$"/>
      </operator>
      <operator activated="true" class="replace" compatibility="9.7.000" expanded="true" height="82" name="Replace (2)" width="90" x="313" y="34">
        <parameter key="attribute_filter_type" value="all"/>
        <parameter key="attribute" value=""/>
        <parameter key="attributes" value=""/>
        <parameter key="use_except_expression" value="false"/>
        <parameter key="value_type" value="nominal"/>
        <parameter key="use_value_type_exception" value="false"/>
        <parameter key="except_value_type" value="file_path"/>
        <parameter key="block_type" value="single_value"/>
        <parameter key="use_block_type_exception" value="false"/>
        <parameter key="except_block_type" value="single_value"/>
        <parameter key="invert_selection" value="false"/>
        <parameter key="include_special_attributes" value="false"/>
        <parameter key="replace_what" value="^\s"/>
      </operator>
      <operator activated="true" class="guess_types" compatibility="9.7.000" expanded="true" height="82" name="Guess Types" width="90" x="447" y="34">
        <parameter key="attribute_filter_type" value="all"/>
        <parameter key="attribute" value=""/>
        <parameter key="attributes" value=""/>
        <parameter key="use_except_expression" value="false"/>
        <parameter key="value_type" value="attribute_value"/>
        <parameter key="use_value_type_exception" value="false"/>
        <parameter key="except_value_type" value="time"/>
        <parameter key="block_type" value="attribute_block"/>
        <parameter key="use_block_type_exception" value="false"/>
        <parameter key="except_block_type" value="value_matrix_row_start"/>
        <parameter key="invert_selection" value="false"/>
        <parameter key="include_special_attributes" value="false"/>
        <parameter key="decimal_point_character" value="."/>
      </operator>
      <operator activated="true" class="blending:pivot" compatibility="9.7.000" expanded="true" height="82" name="Pivot" width="90" x="581" y="34">
        <parameter key="group_by_attributes" value="description"/>
        <parameter key="column_grouping_attribute" value="Category"/>
        <list key="aggregation_attributes">
          <parameter key="count" value="sum"/>
        </list>
        <parameter key="use_default_aggregation" value="false"/>
        <parameter key="default_aggregation_function" value="first"/>
      </operator>
      <connect from_op="Create ExampleSet" from_port="output" to_op="Replace" to_port="example set input"/>
      <connect from_op="Replace" from_port="example set output" to_op="Replace (2)" to_port="example set input"/>
      <connect from_op="Replace (2)" from_port="example set output" to_op="Guess Types" to_port="example set input"/>
      <connect from_op="Guess Types" from_port="example set output" to_op="Pivot" to_port="input"/>
      <connect from_op="Pivot" from_port="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="72"/>
    </process>
  </operator>
</process>

hbajpai · June 2020

@pallav
If you try to save the the datasets in your repo and use the dataset for further process. Here is how it looks on my end.

hbajpai · June 2020

Hey @pallav,

This can be achieved easily with pivot operator, where in you can group by description and column grouping by category at last use aggregation sum over count, that should do it.

pallav · June 2020

@hbajpai - I was trying pivot but it is asking one of aggregation . but i need the same value to be transposed and cant leave aggregation empty.

According to you i should use pivot operator
step1. Groupby attribute - description and cateogry
aggregation - sum over count..

I tried but it is not giving me desired result.

pallav · June 2020

@hbajpai-Thanks i got it.

hbajpai · June 2020

Hey @pallav
I tired it on my end and it does work. Check out the XML.

<?xml version="1.0" encoding="UTF-8"?><process version="9.7.000">
  <context>
    <input/>
    <output/>
    <macros/>
  </context>
  <operator activated="true" class="process" compatibility="6.0.002" expanded="true" name="Process" origin="GENERATED_TUTORIAL">
    <parameter key="logverbosity" value="init"/>
    <parameter key="random_seed" value="2001"/>
    <parameter key="send_mail" value="never"/>
    <parameter key="notification_email" value=""/>
    <parameter key="process_duration_for_mail" value="30"/>
    <parameter key="encoding" value="SYSTEM"/>
    <process expanded="true">
      <operator activated="true" class="utility:create_exampleset" compatibility="9.7.000" expanded="true" height="68" name="Create ExampleSet" width="90" x="45" y="34">
        <parameter key="generator_type" value="comma separated text"/>
        <parameter key="number_of_examples" value="100"/>
        <parameter key="use_stepsize" value="false"/>
        <list key="function_descriptions"/>
        <parameter key="add_id_attribute" value="false"/>
        <list key="numeric_series_configuration"/>
        <list key="date_series_configuration"/>
        <list key="date_series_configuration (interval)"/>
        <parameter key="date_format" value="yyyy-MM-dd HH:mm:ss"/>
        <parameter key="time_zone" value="SYSTEM"/>
        <parameter key="input_csv_text" value="description, Category, count&#10;Z, Available,27.0&#10;Z,Not Available , &#9;14169.0&#10;Z,&#9;Less Available&#9;,863.0&#10;Y,&#9;Available&#9;,50.0&#10;Y,&#9;Not Available&#9;,58971.0&#10;Y,&#9;Less Available,&#9;888.0&#10;OTHER,&#9;Available,&#9;380.0&#10;OTHER,&#9;Not Available,&#9;272343.0&#10;OTHER,&#9;Less Available,&#9;3231.0&#10;X,&#9;Available,&#9;1564.0&#10;X,&#9;Not Available, 1313.0&#10;X,Less Available,&#9;3087.0&#10;"/>
        <parameter key="column_separator" value=","/>
        <parameter key="parse_all_as_nominal" value="false"/>
        <parameter key="decimal_point_character" value="."/>
        <parameter key="trim_attribute_names" value="true"/>
      </operator>
      <operator activated="true" class="replace" compatibility="9.7.000" expanded="true" height="82" name="Replace" width="90" x="179" y="34">
        <parameter key="attribute_filter_type" value="all"/>
        <parameter key="attribute" value=""/>
        <parameter key="attributes" value=""/>
        <parameter key="use_except_expression" value="false"/>
        <parameter key="value_type" value="nominal"/>
        <parameter key="use_value_type_exception" value="false"/>
        <parameter key="except_value_type" value="file_path"/>
        <parameter key="block_type" value="single_value"/>
        <parameter key="use_block_type_exception" value="false"/>
        <parameter key="except_block_type" value="single_value"/>
        <parameter key="invert_selection" value="false"/>
        <parameter key="include_special_attributes" value="false"/>
        <parameter key="replace_what" value="[  \t]+$"/>
      </operator>
      <operator activated="true" class="replace" compatibility="9.7.000" expanded="true" height="82" name="Replace (2)" width="90" x="313" y="34">
        <parameter key="attribute_filter_type" value="all"/>
        <parameter key="attribute" value=""/>
        <parameter key="attributes" value=""/>
        <parameter key="use_except_expression" value="false"/>
        <parameter key="value_type" value="nominal"/>
        <parameter key="use_value_type_exception" value="false"/>
        <parameter key="except_value_type" value="file_path"/>
        <parameter key="block_type" value="single_value"/>
        <parameter key="use_block_type_exception" value="false"/>
        <parameter key="except_block_type" value="single_value"/>
        <parameter key="invert_selection" value="false"/>
        <parameter key="include_special_attributes" value="false"/>
        <parameter key="replace_what" value="^\s"/>
      </operator>
      <operator activated="true" class="guess_types" compatibility="9.7.000" expanded="true" height="82" name="Guess Types" width="90" x="447" y="34">
        <parameter key="attribute_filter_type" value="all"/>
        <parameter key="attribute" value=""/>
        <parameter key="attributes" value=""/>
        <parameter key="use_except_expression" value="false"/>
        <parameter key="value_type" value="attribute_value"/>
        <parameter key="use_value_type_exception" value="false"/>
        <parameter key="except_value_type" value="time"/>
        <parameter key="block_type" value="attribute_block"/>
        <parameter key="use_block_type_exception" value="false"/>
        <parameter key="except_block_type" value="value_matrix_row_start"/>
        <parameter key="invert_selection" value="false"/>
        <parameter key="include_special_attributes" value="false"/>
        <parameter key="decimal_point_character" value="."/>
      </operator>
      <operator activated="true" class="blending:pivot" compatibility="9.7.000" expanded="true" height="82" name="Pivot" width="90" x="581" y="34">
        <parameter key="group_by_attributes" value="description"/>
        <parameter key="column_grouping_attribute" value="Category"/>
        <list key="aggregation_attributes">
          <parameter key="count" value="sum"/>
        </list>
        <parameter key="use_default_aggregation" value="false"/>
        <parameter key="default_aggregation_function" value="first"/>
      </operator>
      <connect from_op="Create ExampleSet" from_port="output" to_op="Replace" to_port="example set input"/>
      <connect from_op="Replace" from_port="example set output" to_op="Replace (2)" to_port="example set input"/>
      <connect from_op="Replace (2)" from_port="example set output" to_op="Guess Types" to_port="example set input"/>
      <connect from_op="Guess Types" from_port="example set output" to_op="Pivot" to_port="input"/>
      <connect from_op="Pivot" from_port="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="72"/>
    </process>
  </operator>
</process>

pallav · June 2020

@hbajpai - Adding to this this is giving column name such as sum(count)........ when i am trying to rename the names it is not giving me all three attribute to rename how to rename all three column here.

pallav · June 2020

@hbajpai -

Image: https://us.v-cdn.net/6030995/uploads/editor/qt/o7hz8ggun3d0.png

In Replace i am getting only one option for all three atribite

hbajpai · June 2020

@pallav
I am unable see the image and understand the issue. Can you elaborate more?

pallav · June 2020

@hbajpai

In this image how to rename all the column i want only "Available" as column name Not "Sum(count)_Available" same for less available and not available as well

hbajpai · June 2020

There is a bug here.

Image: https://us.v-cdn.net/6030995/uploads/editor/1g/tfjcb09gzsiu.png

I think, one workaround would be to save the file and use the stored output to rename the column names.
Let me know if that works for you.

pallav · June 2020

@hbajpai in my case only ope option is coming

hbajpai · June 2020

@pallav
If you try to save the the datasets in your repo and use the dataset for further process. Here is how it looks on my end.

Transform table to result view

description

Category

count

description

Avaialble

Less Available

Not Available

Best Answers

Answers

Categories