[SOLVED] Data ordering

rosana

Good afternoon,

I’m a new user of RapidMiner. I would like to build the following process with RapidMiner and the data ordering is being quite complicated for me.
I explain bellow what I would like to do:

1. The final goal of my RapidMiner process is to plot curves and correlation matrix between different attributes over a period of time.

2. I will explain later the source of the data. I would like to obtain an “example set” with the following structure:

[move][/move]Pressure Flow Temperature
dd/mm/yy hh:mm P1 F1 T1
dd/mm/yy hh:mm P2 F2 T2
dd/mm/yy hh:mm P3 F3 T3
…. …. … ….
(table 1)

From this table I would be able to plot curves, correlation matrix, etc.

3. The problem is that I don’t have the data stored in such a simple format.
4. I have the data stored in .txt files. (>1000 rows per file). I can read it from the operator “Read CSV”.
5. But the data are stored as follows:

dd/mm/yy hh:01 Pressure P1
dd/mm/yy hh:02 Flow F1
dd/mm/yy hh:02 Pressure P1
dd/mm/yy hh:03 Temperature T1
dd/mm/yy hh:03 Temperature T1
…. …. ….
(table 2)

So, a very different order to what I would like to have.

6. I need to build an “example set” reordering the data (from table 2 to table 1). How can I do that?

7. Besides that I would like to reduce the final number of examples considering only an example per every 5 minutes instead of per minute. Therearefore I would need to have an example set whose examples are averages of several examples included in another “example set”. Again, I dind’t find any operator to do that.

I hope I explained my problem clearly.

Thank you very much for your help.
Best regards! Rosana

MariusHelf

Hi Rosana,

your data is in a so called relational format, and you have to use the Pivot operator to convert it to a row-based format.
The pivot operator groups the data by a single attribute (in your case it would be the timestamp), and creates one column for each value of the "index" attribute (in your data the second row).
The Create Transactional Data operator creates data with a similar structure to your data. In the attached process, I pivoted it, so you can adapt the process to your data (the Id attribute corresponds to your timestamp).

Once you managed to pivot your data, please come back here and we will deal with your second problem (5 minutes averages).

Best, Marius

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<process version="5.3.000">
  <context>
    <input/>
    <output/>
    <macros/>
  </context>
  <operator activated="true" class="process" compatibility="5.3.000" expanded="true" name="Process">
    <process expanded="true" height="434" width="767">
      <operator activated="true" class="generate_transaction_data" compatibility="5.3.000" expanded="true" height="60" name="Generate Transaction Data" width="90" x="45" y="30">
        <parameter key="number_transactions" value="1000"/>
        <parameter key="number_customers" value="100"/>
        <parameter key="number_items" value="3"/>
      </operator>
      <operator activated="true" class="rename" compatibility="5.3.000" expanded="true" height="76" name="Rename" width="90" x="179" y="30">
        <parameter key="old_name" value="Item"/>
        <parameter key="new_name" value="Sensor"/>
        <list key="rename_additional_attributes"/>
      </operator>
      <operator activated="true" class="rename" compatibility="5.3.000" expanded="true" height="76" name="Rename (2)" width="90" x="313" y="30">
        <parameter key="old_name" value="Amount"/>
        <parameter key="new_name" value="Value"/>
        <list key="rename_additional_attributes"/>
      </operator>
      <operator activated="true" class="set_role" compatibility="5.3.000" expanded="true" height="76" name="Set Role" width="90" x="447" y="30">
        <parameter key="name" value="Id"/>
        <list key="set_additional_roles"/>
      </operator>
      <operator activated="true" class="pivot" compatibility="5.3.000" expanded="true" height="76" name="Pivot" width="90" x="581" y="30">
        <parameter key="group_attribute" value="Id"/>
        <parameter key="index_attribute" value="Sensor"/>
      </operator>
      <connect from_op="Generate Transaction Data" from_port="output" to_op="Rename" to_port="example set input"/>
      <connect from_op="Rename" from_port="example set output" to_op="Rename (2)" to_port="example set input"/>
      <connect from_op="Rename (2)" from_port="example set output" to_op="Set Role" to_port="example set input"/>
      <connect from_op="Set Role" from_port="example set output" to_op="Pivot" to_port="example set input"/>
      <connect from_op="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>