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
C1 A1 100 10
C1 A1 150 15
C1 A2 200 20


(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:




Customer Amount_A1 AmountA2 Quantity_A1 Quantity_A2
C1 150 200 15 20


However, I would have expected this:




Customer Amount_A1 AmountA2 Quantity_A1 Quantity_A2
C1 250 200 25 20


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

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!