Excel String to Numerical in RM?

mortiz

I have imported a bunch of numerical data from excel into RM, but some cells have text, so RM converts all the attributes to a polynominal. However, I want everything to be converted to the value indicated in the string. The only way I know to do this is go into the Data Editor and click on each attribute to make it into a real, however I have 200 attributes and this would be tedious to do everytime.

Lastly, how do I filter out all cells in each attribute that has text in it? I can't seem to get the filter expression syntax correct.

Any help would be great! thank you.

Marco_Boeck

Hi,

I suggest using Parse Numbers for it. You can specify on which columns it will run (e.g. all nominal columns), and you can also set what should happen if it finds values that are not a number (i.e. they can be replaced with missing values, or skipped).

<?xml version="1.0" encoding="UTF-8"?><process version="9.4.001-SNAPSHOT">
  <context>
    <input/>
    <output/>
    <macros/>
  </context>
  <operator activated="true" class="process" compatibility="9.4.001-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="parse_numbers" compatibility="9.4.001-SNAPSHOT" expanded="true" height="82" name="Parse Numbers" 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="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="replace with missing values"/>
      </operator>
      <connect from_op="Parse Numbers" 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>

Regards,
Marco

varunm1

Hello @mortiz

If you want to convert all the attributes to numerical and if every attribute is a nominal you can use (nominal to numerical operator). You can also change it using Turbo prep.

Hope this helps. If you need more information please inform.

Marco_Boeck

Hi,

I suggest using Parse Numbers for it. You can specify on which columns it will run (e.g. all nominal columns), and you can also set what should happen if it finds values that are not a number (i.e. they can be replaced with missing values, or skipped).

<?xml version="1.0" encoding="UTF-8"?><process version="9.4.001-SNAPSHOT">
  <context>
    <input/>
    <output/>
    <macros/>
  </context>
  <operator activated="true" class="process" compatibility="9.4.001-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="parse_numbers" compatibility="9.4.001-SNAPSHOT" expanded="true" height="82" name="Parse Numbers" 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="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="replace with missing values"/>
      </operator>
      <connect from_op="Parse Numbers" 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>

Regards,
Marco

mortiz

This is helpful. thank you!

mortiz

Selecting the Operator Parse Numbers and setting the option Parameter "Unparsable value handling":  "replace with missing values" absolutely did the trick! thank you.