Transform table to result view

pallav · 2020-06-05T15:44:26+00:00

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…

pallav

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

Find more posts tagged with

AI Studio

Accepted answers

hbajpai

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

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

@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.

All comments

hbajpai

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

@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

@hbajpai-Thanks i got it.

hbajpai

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

@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

@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

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

pallav

@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

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

@hbajpai in my case only ope option is coming

hbajpai

@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.