Excel String to Numerical in RM?

mortizmortiz Member Posts: 2 Newbie
edited August 12 in Help
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.


Answers

  • varunm1varunm1 Member Posts: 665   Unicorn
    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.
    lionelderkrikorTghadiallymortiz
  • Marco_BoeckMarco_Boeck Team Lead Software Engineering Moderator, Employee, Member, University Professor Posts: 1,800   RM Engineering
    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
    varunm1Tghadiallymortiz
  • mortizmortiz Member Posts: 2 Newbie
    This is helpful. thank you!
    Tghadially
Sign In or Register to comment.