Generating Aggregate Table in specific format - Pivot

jeganathanvelu

Hi All,

I googled on this but still could not figure out how to do this using pivot function/alternative functions. Requesting your help.

I have a table as below:

Attribute 1    Attribute 2

A                    Z1
B                    Z1
C                    Z2
D                    Z2
E                    Z2
F                    Z3

I want to  generate a report like this with count between both the attributes in a matrix format.


        Z1            Z2
A    1                  0
B      1                0
C      0                1
D      0                1


Thanks in Advance,
Jegan

fras

You need a quantifier column otherwise pivoting does not know which values to insert:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<process version="6.0.003">
  <context>
    <input/>
    <output/>
    <macros/>
  </context>
  <operator activated="true" class="process" compatibility="6.0.003" expanded="true" name="Process">
    <process expanded="true">
      <operator activated="true" class="read_csv" compatibility="6.0.003" expanded="true" height="60" name="Read CSV" width="90" x="45" y="210">
        <parameter key="csv_file" value="http://pastebin.com/raw.php?i=ASgMba0T"/>
        <parameter key="column_separators" value="\s+"/>
        <parameter key="first_row_as_names" value="false"/>
        <list key="annotations">
          <parameter key="0" value="Name"/>
        </list>
        <parameter key="encoding" value="windows-1252"/>
        <list key="data_set_meta_data_information">
          <parameter key="0" value="Attribute01.true.polynominal.attribute"/>
          <parameter key="1" value="Attribute02.true.polynominal.attribute"/>
        </list>
      </operator>
      <operator activated="true" breakpoints="after" class="generate_attributes" compatibility="6.0.003" expanded="true" height="76" name="Generate Attributes (2)" width="90" x="179" y="210">
        <list key="function_descriptions">
          <parameter key="quantity" value="1"/>
        </list>
      </operator>
      <operator activated="true" class="pivot" compatibility="6.0.003" expanded="true" height="76" name="Pivot" width="90" x="313" y="210">
        <parameter key="group_attribute" value="Attribute01"/>
        <parameter key="index_attribute" value="Attribute02"/>
        <parameter key="consider_weights" value="false"/>
        <parameter key="weight_aggregation" value="count"/>
        <parameter key="skip_constant_attributes" value="false"/>
      </operator>
      <operator activated="true" class="rename_by_replacing" compatibility="6.0.003" expanded="true" height="76" name="Rename by Replacing" width="90" x="447" y="210">
        <parameter key="replace_what" value="quantity_(.*)"/>
        <parameter key="replace_by" value="$1"/>
      </operator>
      <operator activated="true" class="replace_missing_values" compatibility="6.0.003" expanded="true" height="94" name="Replace Missing Values" width="90" x="581" y="210">
        <parameter key="default" value="value"/>
        <list key="columns"/>
        <parameter key="replenishment_value" value="0"/>
      </operator>
      <connect from_op="Read CSV" from_port="output" to_op="Generate Attributes (2)" to_port="example set input"/>
      <connect from_op="Generate Attributes (2)" from_port="example set output" to_op="Pivot" to_port="example set input"/>
      <connect from_op="Pivot" from_port="example set output" to_op="Rename by Replacing" to_port="example set input"/>
      <connect from_op="Rename by Replacing" from_port="example set output" to_op="Replace Missing Values" to_port="example set input"/>
      <connect from_op="Replace Missing Values" 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>

jeganathanvelu

Hi fras,

Thanks a Lot Now I learnt how to generate such tables.

. I think I phrased the question wrong. Instead of just having "1" in the pivot, I need the count of Z1,Z2 against attribute 1 in the matrices. Could you please kindly help me ??

Thanks in Advance,
Jeganathan Velu.