🎉Community Raffle - Win $25

An exclusive raffle opportunity for active members like you! Complete your profile, answer questions and get your first accepted badge to enter the raffle.
Join and Win

Transform table to result view

User: "pallav"
pallav
New Altair Community Member
Updated by Jocelyn
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
Sort by:
1 - 11 of 111
    User: "hbajpai"
    hbajpai
    New Altair Community Member
    Accepted Answer
    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. 
    User: "pallav"
    pallav
    New Altair Community Member
    OP
    @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.
    User: "hbajpai"
    hbajpai
    New Altair Community Member
    Accepted Answer
    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>


    User: "pallav"
    pallav
    New Altair Community Member
    OP
    @hbajpai-Thanks i got it.
    User: "pallav"
    pallav
    New Altair Community Member
    OP
    @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.
    User: "pallav"
    pallav
    New Altair Community Member
    OP
    @hbajpai -       In Replace i am getting only one option for all three atribite
    User: "hbajpai"
    hbajpai
    New Altair Community Member
    @pallav
     I am unable see the image and understand the issue. Can you elaborate more?
    User: "pallav"
    pallav
    New Altair Community Member
    OP
    @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
    User: "hbajpai"
    hbajpai
    New Altair Community Member
    There is a bug here.

    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.

    User: "pallav"
    pallav
    New Altair Community Member
    OP
    @hbajpai in my case only ope option is coming 
    User: "hbajpai"
    hbajpai
    New Altair Community Member
    Accepted Answer
    @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.

Quick Links