Pivot date-time columns into a certain way (EventLog) using Turbo Prep

data1mathsdata1maths Member Posts: 27 Contributor I
edited June 2019 in Help

Hello everyone,

 

I have a table that is like this: (e.g)

A (id)	B	C	D	E	F	X1	X2	X3	X4	X5	X6
1
2
3
4
5

                 

             

 

Where each Xi is a date time attribut.

And i am looking to pivot those Xi columns into a table like this one

 

A (id)	B	C	D	E	F	Event	date
1 X1 associated date-time 1
1 X2 associated date-time 1
1 X3 associated date-time 1
1 X4 associated date-time 1
1 X5 associated date-time 1
1 X6 associated date-time 1
2 X1 associated date-time 2
2 X2 associated date-time 2
2 X3 associated date-time 2
2 X4 associated date-time 2
2 X5 associated date-time 2
2 X6 associated date-time 2
ETC

It's doable in excel but i wanted to create the appropriate process using Turbo Prep.

I've tried pivot command in turbo prep but couldn't get what i wanted.

I really need your help.

Thank you in advance.

Best regards

Best Answer

  • earmijoearmijo Member Posts: 270 Unicorn
    Solution Accepted

    @Telcontar120 is right. I asked that question a while back. Check the useful answers I got here:

     

    https://community.rapidminer.com/t5/RapidMiner-Studio-Forum/De-pivot/m-p/36590

     

    Here's the code for a mini-version of your problem (the Excel file is attached):

     

    <?xml version="1.0" encoding="UTF-8"?><process version="9.0.002">
    <context>
    <input/>
    <output/>
    <macros/>
    </context>
    <operator activated="true" class="process" compatibility="9.0.002" expanded="true" name="Process">
    <process expanded="true">
    <operator activated="true" class="retrieve" compatibility="9.0.002" expanded="true" height="68" name="Retrieve Book2" width="90" x="112" y="238">
    <parameter key="repository_entry" value="//Clases/Book2"/>
    </operator>
    <operator activated="true" class="de_pivot" compatibility="9.0.002" expanded="true" height="82" name="De-Pivot" width="90" x="313" y="238">
    <list key="attribute_name">
    <parameter key="newdate" value="x.*"/>
    </list>
    <parameter key="index_attribute" value="type"/>
    <parameter key="create_nominal_index" value="true"/>
    </operator>
    <connect from_op="Retrieve Book2" from_port="output" to_op="De-Pivot" to_port="example set input"/>
    <connect from_op="De-Pivot" 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>

Answers

  • Telcontar120Telcontar120 Moderator, RapidMiner Certified Analyst, RapidMiner Certified Expert, Member Posts: 1,635 Unicorn

    De-Pivot is the operator you want---check out the tutorial process.  

    Brian T.
    Lindon Ventures 
    Data Science Consulting from Certified RapidMiner Experts
  • BalazsBaranyBalazsBarany Administrator, Moderator, Employee, RapidMiner Certified Analyst, RapidMiner Certified Expert Posts: 955 Unicorn

    Hi data1maths!

     

    What you need is actually not pivoting but unpivoting. 

    It's not available in Turbo Prep (yet?) but there's an operator "De-Pivot" for it in RapidMiner. So you would load your data and connect to this operator. 

     

    Look at the tutorial process first. It explains how to work with the operator.

    You might want to split up your data set. One copy would contain A-F, the other A and the X* attributes. You can then easily de-pivot the A+X data and re-join the result with the rest. The other attributes won't disturb the de-pivoting.

     

    Regards,

     

    Balázs

  • data1mathsdata1maths Member Posts: 27 Contributor I

    Thank you so much for your help.

    Have a nice day.

Sign In or Register to comment.