Pivot tables with 2 attributes for columns

ivangeraskinivangeraskin Member Posts: 6 Contributor I

I have table of purchases. Buyers with related Card_IDs buy diffrent products with related Segment (Cheap, Expensive) and Type (Meat, Bread, Milk)
Card_ID - Segment - Type - Amount
1012      - Cheap     - Meat - 1

1012      - Cheap     - Bread - 2

1013     - Expensive - Milk - 1

1013     - Cheap    - Bread - 3

1014     - Expensive - Bread - 1

1014     - Cheap    - Meat - 2

1014     - Expensive - Milk - 1

I want to make a pivot table, where "Card_ID" will be in string, "Segment"+"Type" in columns, aggregation sum by "Amount":
Card_ID - Cheap+Meat - Cheap+Bread - Cheap+Milk - Expensive+Meat - Expensive+Bread - Expensive+Milk

1012      -           1           -           2          -          0          -             0             -             0                -              0           

1013      -           0           -           3          -          0          -             0             -             0                -              1           

1014      -           2           -           0          -          0          -             0             -             1                -              1

Thank you for your help,
Ivan         

Best Answer

  • mschmitzmschmitz Posts: 2,132  RM Data Scientist
    Solution Accepted

    Hi,

     

    i think the easiest thing is to create a new attribute with concat(Segment,Type) and use this as the index variable.

     

    ~Martin

    - Head of Data Science Services at RapidMiner -
    Dortmund, Germany

Answers

  • ivangeraskinivangeraskin Member Posts: 6 Contributor I

    Thank you very much!

  • ivangeraskinivangeraskin Member Posts: 6 Contributor I

    Can you tell, which operator could make such pivot table (after concatination), aggregating by sum (Card_id in strings, Segment_Type in columns)? I can't make "pivot table" operator do such thing.

  • mschmitzmschmitz Administrator, Moderator, Employee, RapidMiner Certified Analyst, RapidMiner Certified Expert, University Professor Posts: 2,132  RM Data Scientist

    Hi,

     

    you first use Generate Attribute to generate the Concat, then Select Attributes to remove the two attributes you just concatted and then Pivot.

     

    ~Martin

    - Head of Data Science Services at RapidMiner -
    Dortmund, Germany
  • ivangeraskinivangeraskin Member Posts: 6 Contributor I

    Thank you) I found solution to aggregate by sum we also need Aggregate operator before Pivot Table operator.

  • ivangeraskinivangeraskin Member Posts: 6 Contributor I

    Thank you) I found solution, to aggregate by sum we also need Aggregate operator before Pivot Table operator.

Sign In or Register to comment.