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

data1maths

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

earmijo

@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>

Telcontar120

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

BalazsBarany

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

data1maths

Thank you so much for your help.

Have a nice day.