Due to recent updates, all users are required to create an Altair One account to login to the RapidMiner community. Click the Register button to create your account using the same email that you have previously used to login to the RapidMiner community. This will ensure that any previously created content will be synced to your Altair One account. Once you login, you will be asked to provide a username that identifies you to other Community users. Email us at Community with questions.
Pivot date-time columns into a certain way (EventLog) using Turbo Prep
data1maths
Member Posts: 27 Contributor II
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
0
Best Answer
-
earmijo Member Posts: 271 Unicorn
@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>2
Answers
De-Pivot is the operator you want---check out the tutorial process.
Lindon Ventures
Data Science Consulting from Certified RapidMiner Experts
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
Thank you so much for your help.
Have a nice day.