RapidMiner

RapidMiner

[SOLVED] Question to "sum" aggregation when Pivoting

Regular Contributor

[SOLVED] Question to "sum" aggregation when Pivoting

Hi,
I'm trying to create a rather simple Pivot Table using the "Pivoting" operator.

Let's say this is the raw data:






Customer Article Amount Quantity
C1A1 10010
C1A1 15015
C1A2 20020


(Note: I initially posted the wrong raw numbers in the third row, but it's corrected now.)


The Pivot operator is configured as follows:

Group Attribute:        Customer
Index Attribute:         Article
Weight Aggrecation: sum

When I run the process, I get this result:




CustomerAmount_A1AmountA2Quantity_A1Quantity_A2
C11502001520


However, I would have expected this:




CustomerAmount_A1AmountA2Quantity_A1Quantity_A2
C12502002520


Could somebody help me to understand what I'm doing wrong?
2 REPLIES
Regular Contributor

Re: Question to "sum" aggregation when Pivoting

Hi,

the pivot operator does not aggregate data values. Just weights are aggregated thus the name attribute name weighte aggregation.
What you want to use is the Aggregate operator:


<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<process version="5.3.009">
  <context>
    <input/>
    <output/>
    <macros/>
  </context>
  <operator activated="true" class="process" compatibility="5.3.009" expanded="true" name="Process">
    <process expanded="true">
      <operator activated="true" class="read_csv" compatibility="5.3.009" expanded="true" height="60" name="Read CSV" width="90" x="112" y="255">
        <parameter key="csv_file" value="C:\Users\nwoehler\Desktop\test.csv"/>
        <parameter key="column_separators" value=","/>
        <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="customer.true.binominal.attribute"/>
          <parameter key="1" value="article.true.binominal.attribute"/>
          <parameter key="2" value="amount.true.integer.attribute"/>
          <parameter key="3" value="quantity.true.integer.attribute"/>
        </list>
      </operator>
      <operator activated="true" class="aggregate" compatibility="5.3.009" expanded="true" height="76" name="Aggregate" width="90" x="246" y="255">
        <list key="aggregation_attributes">
          <parameter key="quantity" value="sum"/>
          <parameter key="amount" value="sum"/>
        </list>
        <parameter key="group_by_attributes" value="article|customer|"/>
      </operator>
      <connect from_op="Read CSV" from_port="output" to_op="Aggregate" to_port="example set input"/>
      <connect from_op="Aggregate" 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>


Best,
Nils
Regular Contributor

Re: [Solved] Question to "sum" aggregation when Pivoting

Hi Nils,

thank you very much for clarifying. You are right, the operator clearly states "weight aggregation".

All works well now, I simply added the aggregation operator before pivoting, and I'm getting the correct results.

Thanks for helping out!