Linking of data

1705410G · January 2019

I have 2 sets of .csv files.

One has attributes of 2_distance, 2_speed, 2_time, 3_distance, 3_speed, 3_time... 800_distance, 800_speed, 800_time and so on. With the numbers representing the location ID. Example, 2_time represents the time taken at location ID 2.

The second set of data has attributes of the ID, latitude and longitude of the location. Example, location ID 3 is located at latitude and longitude of 50.05775 and 19.96138 respectively.

So my question is how can I link/merge the files so that when I plot the graph for let's say 2_distance, 2_speed or 2_time (either one), I am able to find out the latitude and longitude of location ID 2 automatically?

Really appreciate any form of help!! Thanks!

lionelderkrikor · January 2019

Hi @1705410G,

You can find here a process which performs what you want to do from your 2 example sets (it is the result of a Turbo Prep / Pivot preparation) :

Image: https://us.v-cdn.net/6030995/uploads/editor/sw/vt17sxnbxh6x.png

Hope it helps,

Regards,

Lionel

Nb: the process :

<?xml version="1.0" encoding="UTF-8"?><process version="9.2.000-SNAPSHOT">
  <context>
    <input/>
    <output/>
    <macros/>
  </context>
  <operator activated="true" class="process" compatibility="9.2.000-SNAPSHOT" expanded="true" name="Process">
    <parameter key="logverbosity" value="init"/>
    <parameter key="random_seed" value="2001"/>
    <parameter key="send_mail" value="never"/>
    <parameter key="notification_email" value=""/>
    <parameter key="process_duration_for_mail" value="30"/>
    <parameter key="encoding" value="SYSTEM"/>
    <process expanded="true">
      <operator activated="true" class="operator_toolbox:create_exampleset" compatibility="1.7.000" expanded="true" height="68" name="Create ExampleSet" width="90" x="112" y="85">
        <parameter key="generator_type" value="comma_separated_text"/>
        <parameter key="number_of_examples" value="100"/>
        <parameter key="use_stepsize" value="false"/>
        <list key="function_descriptions"/>
        <parameter key="add_id_attribute" value="false"/>
        <list key="numeric_series_configuration"/>
        <list key="date_series_configuration"/>
        <list key="date_series_configuration (interval)"/>
        <parameter key="date_format" value="yyyy-MM-dd HH:mm:ss"/>
        <parameter key="input_csv_text" value="1_distance,1_speed,1_time,2_distance,2_speed,2_time,3_distance,3_speed,3_time,4_distance,4_speed,4_time&#10;1,2,3,4,5,6,7,8,9,10,11,12"/>
        <parameter key="column_separator" value=","/>
        <parameter key="parse_all_as_nominal" value="false"/>
        <parameter key="decimal_point_character" value="."/>
        <parameter key="trim_attribute_names" value="true"/>
      </operator>
      <operator activated="true" class="transpose" compatibility="9.2.000-SNAPSHOT" expanded="true" height="82" name="Transpose" width="90" x="246" y="85"/>
      <operator activated="true" class="split" compatibility="9.2.000-SNAPSHOT" expanded="true" height="82" name="Split" width="90" x="380" y="85">
        <parameter key="attribute_filter_type" value="single"/>
        <parameter key="attribute" value="id"/>
        <parameter key="attributes" value=""/>
        <parameter key="use_except_expression" value="false"/>
        <parameter key="value_type" value="nominal"/>
        <parameter key="use_value_type_exception" value="false"/>
        <parameter key="except_value_type" value="file_path"/>
        <parameter key="block_type" value="single_value"/>
        <parameter key="use_block_type_exception" value="false"/>
        <parameter key="except_block_type" value="single_value"/>
        <parameter key="invert_selection" value="false"/>
        <parameter key="include_special_attributes" value="true"/>
        <parameter key="split_pattern" value="_"/>
        <parameter key="split_mode" value="ordered_split"/>
      </operator>
      <operator activated="true" class="subprocess" compatibility="9.2.000-SNAPSHOT" expanded="true" height="82" name="Turbo Prep Process" width="90" x="514" y="85">
        <process expanded="true">
          <operator activated="true" class="subprocess" compatibility="9.2.000-SNAPSHOT" expanded="true" height="82" name="Subprocess" width="90" x="45" y="34">
            <process expanded="true">
              <operator activated="true" class="nominal_to_text" compatibility="9.2.000-SNAPSHOT" expanded="true" height="82" name="Nominal to Text" width="90" x="45" y="34">
                <parameter key="attribute_filter_type" value="value_type"/>
                <parameter key="attribute" value=""/>
                <parameter key="attributes" value=""/>
                <parameter key="use_except_expression" value="false"/>
                <parameter key="value_type" value="nominal"/>
                <parameter key="use_value_type_exception" value="false"/>
                <parameter key="except_value_type" value="file_path"/>
                <parameter key="block_type" value="single_value"/>
                <parameter key="use_block_type_exception" value="false"/>
                <parameter key="except_block_type" value="single_value"/>
                <parameter key="invert_selection" value="false"/>
                <parameter key="include_special_attributes" value="false"/>
                <description align="center" color="transparent" colored="false" width="126">Change all categorical columns to text</description>
              </operator>
              <operator activated="true" class="text_to_nominal" compatibility="9.2.000-SNAPSHOT" expanded="true" height="82" name="Text to Nominal" width="90" x="179" y="34">
                <parameter key="attribute_filter_type" value="value_type"/>
                <parameter key="attribute" value=""/>
                <parameter key="attributes" value=""/>
                <parameter key="use_except_expression" value="false"/>
                <parameter key="value_type" value="text"/>
                <parameter key="use_value_type_exception" value="false"/>
                <parameter key="except_value_type" value="text"/>
                <parameter key="block_type" value="value_matrix"/>
                <parameter key="use_block_type_exception" value="false"/>
                <parameter key="except_block_type" value="value_matrix_row_start"/>
                <parameter key="invert_selection" value="false"/>
                <parameter key="include_special_attributes" value="false"/>
                <description align="center" color="transparent" colored="false" width="126">Change all text columns to nominal</description>
              </operator>
              <operator activated="true" class="numerical_to_real" compatibility="9.2.000-SNAPSHOT" expanded="true" height="82" name="Numerical to Real" width="90" x="313" y="34">
                <parameter key="attribute_filter_type" value="value_type"/>
                <parameter key="attribute" value=""/>
                <parameter key="attributes" value=""/>
                <parameter key="use_except_expression" value="false"/>
                <parameter key="value_type" value="numeric"/>
                <parameter key="use_value_type_exception" value="false"/>
                <parameter key="except_value_type" value="real"/>
                <parameter key="block_type" value="value_series"/>
                <parameter key="use_block_type_exception" value="false"/>
                <parameter key="except_block_type" value="value_series_end"/>
                <parameter key="invert_selection" value="false"/>
                <parameter key="include_special_attributes" value="false"/>
                <description align="center" color="transparent" colored="false" width="126">Change all numerical columns to real</description>
              </operator>
              <connect from_port="in 1" to_op="Nominal to Text" to_port="example set input"/>
              <connect from_op="Nominal to Text" from_port="example set output" to_op="Text to Nominal" to_port="example set input"/>
              <connect from_op="Text to Nominal" from_port="example set output" to_op="Numerical to Real" to_port="example set input"/>
              <connect from_op="Numerical to Real" from_port="example set output" to_port="out 1"/>
              <portSpacing port="source_in 1" spacing="0"/>
              <portSpacing port="source_in 2" spacing="0"/>
              <portSpacing port="sink_out 1" spacing="0"/>
              <portSpacing port="sink_out 2" spacing="0"/>
            </process>
            <description align="center" color="transparent" colored="false" width="126">Unify column types</description>
          </operator>
          <operator activated="true" class="subprocess" compatibility="9.2.000-SNAPSHOT" expanded="true" height="82" name="Pivot Subprocess" origin="GENERATED_TURBOPREP" width="90" x="179" y="34">
            <process expanded="true">
              <operator activated="true" class="blending:pivot" compatibility="9.2.000-SNAPSHOT" expanded="true" height="82" name="Pivot" origin="GENERATED_TURBOPREP" width="90" x="45" y="34">
                <parameter key="group_by_attributes" value="id_1"/>
                <parameter key="column_grouping_attribute" value="id_2"/>
                <list key="aggregation_attributes">
                  <parameter key="att_1" value="average"/>
                </list>
                <parameter key="use_default_aggregation" value="false"/>
                <parameter key="default_aggregation_function" value="first"/>
              </operator>
              <operator activated="true" class="order_attributes" compatibility="9.2.000-SNAPSHOT" expanded="true" height="82" name="Reorder Attributes" origin="GENERATED_TURBOPREP" width="90" x="179" y="34">
                <parameter key="sort_mode" value="user specified"/>
                <parameter key="attribute_ordering" value="id_1|average(att_1)_distance|average(att_1)_speed|average(att_1)_time"/>
                <parameter key="use_regular_expressions" value="false"/>
                <parameter key="handle_unmatched" value="append"/>
                <parameter key="sort_direction" value="ascending"/>
              </operator>
              <connect from_port="in 1" to_op="Pivot" to_port="input"/>
              <connect from_op="Pivot" from_port="output" to_op="Reorder Attributes" to_port="example set input"/>
              <connect from_op="Reorder Attributes" from_port="example set output" to_port="out 1"/>
              <portSpacing port="source_in 1" spacing="0"/>
              <portSpacing port="source_in 2" spacing="0"/>
              <portSpacing port="sink_out 1" spacing="0"/>
              <portSpacing port="sink_out 2" spacing="0"/>
            </process>
            <description align="center" color="transparent" colored="false" width="126">Pivot to [group by = id_1] with [columns = id_2] and [aggregates = att_1]</description>
          </operator>
          <operator activated="true" class="order_attributes" compatibility="9.2.000-SNAPSHOT" expanded="true" height="82" name="Reorder Attributes (2)" width="90" x="313" y="34">
            <parameter key="sort_mode" value="user specified"/>
            <parameter key="attribute_ordering" value="id_1|average(att_1)_distance|average(att_1)_speed|average(att_1)_time"/>
            <parameter key="use_regular_expressions" value="false"/>
            <parameter key="handle_unmatched" value="append"/>
            <parameter key="sort_direction" value="ascending"/>
            <description align="center" color="transparent" colored="false" width="126">Ensure order of columns matches the table view.</description>
          </operator>
          <connect from_port="in 1" to_op="Subprocess" to_port="in 1"/>
          <connect from_op="Subprocess" from_port="out 1" to_op="Pivot Subprocess" to_port="in 1"/>
          <connect from_op="Pivot Subprocess" from_port="out 1" to_op="Reorder Attributes (2)" to_port="example set input"/>
          <connect from_op="Reorder Attributes (2)" from_port="example set output" to_port="out 1"/>
          <portSpacing port="source_in 1" spacing="0"/>
          <portSpacing port="source_in 2" spacing="0"/>
          <portSpacing port="sink_out 1" spacing="0"/>
          <portSpacing port="sink_out 2" spacing="0"/>
        </process>
        <description align="center" color="transparent" colored="false" width="126">TP Operations: Pivot</description>
      </operator>
      <operator activated="true" class="parse_numbers" compatibility="9.2.000-SNAPSHOT" expanded="true" height="82" name="Parse Numbers" width="90" x="648" y="85">
        <parameter key="attribute_filter_type" value="all"/>
        <parameter key="attribute" value=""/>
        <parameter key="attributes" value=""/>
        <parameter key="use_except_expression" value="false"/>
        <parameter key="value_type" value="nominal"/>
        <parameter key="use_value_type_exception" value="false"/>
        <parameter key="except_value_type" value="file_path"/>
        <parameter key="block_type" value="single_value"/>
        <parameter key="use_block_type_exception" value="false"/>
        <parameter key="except_block_type" value="single_value"/>
        <parameter key="invert_selection" value="false"/>
        <parameter key="include_special_attributes" value="false"/>
        <parameter key="decimal_character" value="."/>
        <parameter key="grouped_digits" value="false"/>
        <parameter key="grouping_character" value=","/>
        <parameter key="infinity_representation" value=""/>
        <parameter key="unparsable_value_handling" value="fail"/>
      </operator>
      <operator activated="true" class="operator_toolbox:create_exampleset" compatibility="1.7.000" expanded="true" height="68" name="Create ExampleSet (2)" width="90" x="112" y="289">
        <parameter key="generator_type" value="comma_separated_text"/>
        <parameter key="number_of_examples" value="100"/>
        <parameter key="use_stepsize" value="false"/>
        <list key="function_descriptions"/>
        <parameter key="add_id_attribute" value="false"/>
        <list key="numeric_series_configuration"/>
        <list key="date_series_configuration"/>
        <list key="date_series_configuration (interval)"/>
        <parameter key="date_format" value="yyyy-MM-dd HH:mm:ss"/>
        <parameter key="input_csv_text" value="id,longitude,latitude&#10;1,10,20&#10;2,30,40&#10;3,50,60&#10;4,70,80"/>
        <parameter key="column_separator" value=","/>
        <parameter key="parse_all_as_nominal" value="false"/>
        <parameter key="decimal_point_character" value="."/>
        <parameter key="trim_attribute_names" value="true"/>
      </operator>
      <operator activated="true" class="set_role" compatibility="9.2.000-SNAPSHOT" expanded="true" height="82" name="Set Role" width="90" x="380" y="238">
        <parameter key="attribute_name" value="id"/>
        <parameter key="target_role" value="id"/>
        <list key="set_additional_roles"/>
      </operator>
      <operator activated="true" class="set_role" compatibility="9.2.000-SNAPSHOT" expanded="true" height="82" name="Set Role (2)" width="90" x="782" y="85">
        <parameter key="attribute_name" value="id_1"/>
        <parameter key="target_role" value="id"/>
        <list key="set_additional_roles"/>
      </operator>
      <operator activated="true" class="concurrency:join" compatibility="9.2.000-SNAPSHOT" expanded="true" height="82" name="Join" width="90" x="916" y="187">
        <parameter key="remove_double_attributes" value="true"/>
        <parameter key="join_type" value="inner"/>
        <parameter key="use_id_attribute_as_key" value="true"/>
        <list key="key_attributes">
          <parameter key="id_1" value="id"/>
        </list>
        <parameter key="keep_both_join_attributes" value="false"/>
      </operator>
      <connect from_op="Create ExampleSet" from_port="output" to_op="Transpose" to_port="example set input"/>
      <connect from_op="Transpose" from_port="example set output" to_op="Split" to_port="example set input"/>
      <connect from_op="Split" from_port="example set output" to_op="Turbo Prep Process" to_port="in 1"/>
      <connect from_op="Turbo Prep Process" from_port="out 1" to_op="Parse Numbers" to_port="example set input"/>
      <connect from_op="Parse Numbers" from_port="example set output" to_op="Set Role (2)" to_port="example set input"/>
      <connect from_op="Create ExampleSet (2)" from_port="output" to_op="Set Role" to_port="example set input"/>
      <connect from_op="Set Role" from_port="example set output" to_op="Join" to_port="right"/>
      <connect from_op="Set Role (2)" from_port="example set output" to_op="Join" to_port="left"/>
      <connect from_op="Join" from_port="join" 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>

varunm1 · January 2019

Hi @1705410G,

See the below thread about joins and supersets. See if this is useful.

https://rapidminer.com/blog/tips-tricks-different-ways-to-join-data/

Thanks,
varun

1705410G · January 2019

@varunm1

Thanks for replying!

But I've read through the thread that you've sent a couple of time previously.
I don't think it's what I'm looking for.

Howdy, Stranger!

Quick Links

Categories

Altair RapidMiner Community

GET HELP. LEARN BEST PRACTICES. NETWORK WITH YOUR PEERS.

Linking of data

Best Answer

Answers

Be Safe. Follow precautions and Maintain Social Distancing