Options

Merge Example Sets in a Collection

earmijoearmijo Member Posts: 270 Unicorn
in Help
I have a collection of examples sets. They all have the same number of observations and the same ids, but differ in the variables other than the iD. How do I merge them into a single example set? I have tried (unsuccessfully) to use joins. I'm sure there must be a trivial solution (for the experts on this forum), but it completely eludes me. Here's an initial process with the Iris dataset. 

<?xml version="1.0" encoding="UTF-8"?><process version="10.1.001">
  <context>
    <input/>
    <output/>
    <macros/>
  </context>
  <operator activated="true" class="process" compatibility="10.1.001" 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="retrieve" compatibility="10.1.001" expanded="true" height="68" name="Retrieve Iris" width="90" x="179" y="136">
        <parameter key="repository_entry" value="//Samples/data/Iris"/>
      </operator>
      <operator activated="true" class="generate_id" compatibility="10.1.001" expanded="true" height="82" name="Generate ID" width="90" x="313" y="136">
        <parameter key="create_nominal_ids" value="false"/>
        <parameter key="offset" value="0"/>
      </operator>
      <operator activated="true" class="blending:select_attributes" compatibility="10.1.001" expanded="true" height="82" name="Select Attributes" width="90" x="447" y="136">
        <parameter key="type" value="include attributes"/>
        <parameter key="attribute_filter_type" value="a subset"/>
        <parameter key="select_attribute" value=""/>
        <parameter key="select_subset" value="id␞a1"/>
        <parameter key="also_apply_to_special_attributes_(id,_label..)" value="true"/>
      </operator>
      <operator activated="true" class="retrieve" compatibility="10.1.001" expanded="true" height="68" name="Retrieve Iris (2)" width="90" x="179" y="289">
        <parameter key="repository_entry" value="//Samples/data/Iris"/>
      </operator>
      <operator activated="true" class="generate_id" compatibility="10.1.001" expanded="true" height="82" name="Generate ID (2)" width="90" x="313" y="289">
        <parameter key="create_nominal_ids" value="false"/>
        <parameter key="offset" value="0"/>
      </operator>
      <operator activated="true" class="blending:select_attributes" compatibility="10.1.001" expanded="true" height="82" name="Select Attributes (2)" width="90" x="447" y="289">
        <parameter key="type" value="include attributes"/>
        <parameter key="attribute_filter_type" value="a subset"/>
        <parameter key="select_attribute" value=""/>
        <parameter key="select_subset" value="id␞a2"/>
        <parameter key="also_apply_to_special_attributes_(id,_label..)" value="true"/>
      </operator>
      <operator activated="true" class="retrieve" compatibility="10.1.001" expanded="true" height="68" name="Retrieve Iris (3)" width="90" x="179" y="442">
        <parameter key="repository_entry" value="//Samples/data/Iris"/>
      </operator>
      <operator activated="true" class="generate_id" compatibility="10.1.001" expanded="true" height="82" name="Generate ID (3)" width="90" x="313" y="442">
        <parameter key="create_nominal_ids" value="false"/>
        <parameter key="offset" value="0"/>
      </operator>
      <operator activated="true" class="blending:select_attributes" compatibility="10.1.001" expanded="true" height="82" name="Select Attributes (3)" width="90" x="447" y="442">
        <parameter key="type" value="include attributes"/>
        <parameter key="attribute_filter_type" value="a subset"/>
        <parameter key="select_attribute" value=""/>
        <parameter key="select_subset" value="id␞a3"/>
        <parameter key="also_apply_to_special_attributes_(id,_label..)" value="true"/>
      </operator>
      <operator activated="true" class="retrieve" compatibility="10.1.001" expanded="true" height="68" name="Retrieve Iris (4)" width="90" x="179" y="544">
        <parameter key="repository_entry" value="//Samples/data/Iris"/>
      </operator>
      <operator activated="true" class="generate_id" compatibility="10.1.001" expanded="true" height="82" name="Generate ID (4)" width="90" x="313" y="544">
        <parameter key="create_nominal_ids" value="false"/>
        <parameter key="offset" value="0"/>
      </operator>
      <operator activated="true" class="blending:select_attributes" compatibility="10.1.001" expanded="true" height="82" name="Select Attributes (4)" width="90" x="447" y="544">
        <parameter key="type" value="include attributes"/>
        <parameter key="attribute_filter_type" value="a subset"/>
        <parameter key="select_attribute" value=""/>
        <parameter key="select_subset" value="id␞a4"/>
        <parameter key="also_apply_to_special_attributes_(id,_label..)" value="true"/>
      </operator>
      <operator activated="true" class="collect" compatibility="10.1.001" expanded="true" height="145" name="Collect" width="90" x="715" y="289">
        <parameter key="unfold" value="false"/>
      </operator>
      <connect from_op="Retrieve Iris" from_port="output" to_op="Generate ID" to_port="example set input"/>
      <connect from_op="Generate ID" from_port="example set output" to_op="Select Attributes" to_port="example set input"/>
      <connect from_op="Select Attributes" from_port="example set output" to_op="Collect" to_port="input 1"/>
      <connect from_op="Retrieve Iris (2)" from_port="output" to_op="Generate ID (2)" to_port="example set input"/>
      <connect from_op="Generate ID (2)" from_port="example set output" to_op="Select Attributes (2)" to_port="example set input"/>
      <connect from_op="Select Attributes (2)" from_port="example set output" to_op="Collect" to_port="input 2"/>
      <connect from_op="Retrieve Iris (3)" from_port="output" to_op="Generate ID (3)" to_port="example set input"/>
      <connect from_op="Generate ID (3)" from_port="example set output" to_op="Select Attributes (3)" to_port="example set input"/>
      <connect from_op="Select Attributes (3)" from_port="example set output" to_op="Collect" to_port="input 3"/>
      <connect from_op="Retrieve Iris (4)" from_port="output" to_op="Generate ID (4)" to_port="example set input"/>
      <connect from_op="Generate ID (4)" from_port="example set output" to_op="Select Attributes (4)" to_port="example set input"/>
      <connect from_op="Select Attributes (4)" from_port="example set output" to_op="Collect" to_port="input 4"/>
      <connect from_op="Collect" from_port="collection" 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

  • Options
    earmijoearmijo Member Posts: 270 Unicorn
    edited July 2023
    Ok. I'm answer my own question. This may prove useful to other users. Unfortunately I had to get out of Rapidminer and use R. I had the program write CSV files to the disk and then write a 4-line R code. 

    The solutions I found in this forum (use JOIN) were not feasible for me because of the large number of files I was creating (about 200). 

    Here's the code to write the files to my desktop (the real files had the same structure - id + prediction) :

    <?xml version="1.0" encoding="UTF-8"?><process version="10.1.001">
  <context>
    <input/>
    <output/>
    <macros/>
  </context>
  <operator activated="true" class="process" compatibility="10.1.001" 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="retrieve" compatibility="10.1.001" expanded="true" height="68" name="Retrieve Iris" width="90" x="112" y="187">
        <parameter key="repository_entry" value="//Samples/data/Iris"/>
      </operator>
      <operator activated="true" class="blending:select_attributes" compatibility="10.1.001" expanded="true" height="82" name="Select Attributes" width="90" x="246" y="187">
        <parameter key="type" value="exclude attributes"/>
        <parameter key="attribute_filter_type" value="one attribute"/>
        <parameter key="select_attribute" value="label"/>
        <parameter key="select_subset" value=""/>
        <parameter key="also_apply_to_special_attributes_(id,_label..)" value="true"/>
      </operator>
      <operator activated="true" class="concurrency:loop_attributes" compatibility="10.1.001" expanded="true" height="82" name="Loop Attributes" width="90" x="514" y="187">
        <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="attribute_value"/>
        <parameter key="use_value_type_exception" value="false"/>
        <parameter key="except_value_type" value="time"/>
        <parameter key="block_type" value="attribute_block"/>
        <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"/>
        <parameter key="attribute_name_macro" value="loop_attribute"/>
        <parameter key="reuse_results" value="false"/>
        <parameter key="enable_parallel_execution" value="true"/>
        <process expanded="true">
          <operator activated="true" class="blending:select_attributes" compatibility="10.1.001" expanded="true" height="82" name="Select Attributes (2)" width="90" x="246" y="34">
            <parameter key="type" value="include attributes"/>
            <parameter key="attribute_filter_type" value="one attribute"/>
            <parameter key="select_attribute" value="%{loop_attribute}"/>
            <parameter key="select_subset" value=""/>
            <parameter key="also_apply_to_special_attributes_(id,_label..)" value="false"/>
          </operator>
          <operator activated="true" class="write_csv" compatibility="10.1.001" expanded="true" height="82" name="Write CSV" width="90" x="715" y="136">
            <parameter key="csv_file" value="/Users/earmijo/Desktop/attribute_%{loop_attribute}.csv"/>
            <parameter key="column_separator" value=","/>
            <parameter key="write_attribute_names" value="true"/>
            <parameter key="quote_nominal_values" value="true"/>
            <parameter key="format_date_attributes" value="true"/>
            <parameter key="date_format" value="yyyy-MM-dd HH:mm:ss"/>
            <parameter key="append_to_file" value="false"/>
            <parameter key="encoding" value="SYSTEM"/>
          </operator>
          <connect from_port="input 1" to_op="Select Attributes (2)" to_port="example set input"/>
          <connect from_op="Select Attributes (2)" from_port="example set output" to_op="Write CSV" to_port="input"/>
          <connect from_op="Write CSV" from_port="through" to_port="output 1"/>
          <portSpacing port="source_input 1" spacing="0"/>
          <portSpacing port="source_input 2" spacing="0"/>
          <portSpacing port="sink_output 1" spacing="0"/>
          <portSpacing port="sink_output 2" spacing="0"/>
        </process>
      </operator>
      <connect from_op="Retrieve Iris" from_port="output" to_op="Select Attributes" to_port="example set input"/>
      <connect from_op="Select Attributes" from_port="example set output" to_op="Loop Attributes" to_port="input 1"/>
      <connect from_op="Loop Attributes" from_port="output 1" 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>


    And here is the R script:

    library(tidyverse)
    setwd("/Users/earmijo/Desktop/")

    filename.list <- list.files(pattern = '*.csv')
    files.list <- map(filename.list, ~read_csv(.))  
    combined <- reduce(files.list, full_join, by = 'id')
Sign In or Register to comment.