How to convert polynomial data into numerical

MonaLee

Hi all,

I try to read a csv file which has a Polynomial data type for the currency amount. 

Is there any operator, which convert the polynomial data type to real or numerical? For example: The polynomial value "$12,052.00" to numeric value "12,052.00". So that I can do some calculation.  I tried the operator "Nomial to Numerical", but it does not do the job what I want.

ruhaila

Perhaps share your .rpm and data files?

rmjones

Hi @MonaLee, I would suggest you try using 'Parse Numbers' for your task. It may struggle if currency symbols are present in the cell as well, it may cause some challenges, in which case I would suggest you trim those prior to parsing the numerical values. Hope this helps!

CKönig

"Parse Numbers" does indeed not like currency symbols. If you encounter numbers encoded with other symbols, you can try to remove all "non-digit" characters before using "Parse Numbers". In this example, I'm using "Replace" to match everything "not 0-9 or ." and remove it from the value. Afterwards, Parse Numbers works fine.

<?xml version="1.0" encoding="UTF-8"?><process version="10.1.003">

  <context>

    <input/>

    <output/>

    <macros/>

  </context>

  <operator activated="true" class="process" compatibility="10.1.003" 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="utility:create_exampleset" compatibility="10.1.003" expanded="true" height="68" name="Create ExampleSet" width="90" x="45" y="34">

        <parameter key="generator_type" value="attribute functions"/>

        <parameter key="number_of_examples" value="1"/>

        <parameter key="use_stepsize" value="false"/>

        <list key="function_descriptions">

          <parameter key="price" value="&quot;$12,052.42&quot;"/>

        </list>

        <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="time_zone" value="SYSTEM"/>

        <parameter key="input_csv_text" value="price&#10;$12,052.00"/>

        <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="replace" compatibility="10.1.003" expanded="true" height="82" name="Replace" width="90" x="179" y="34">

        <parameter key="attribute_filter_type" value="single"/>

        <parameter key="attribute" value="price"/>

        <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="replace_what" value="[^0-9.]"/>

        <parameter key="replace_by" value=""/>

      </operator>

      <operator activated="true" class="parse_numbers" compatibility="10.1.003" expanded="true" height="82" name="Parse Numbers" width="90" x="313" y="34">

        <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>

      <connect from_op="Create ExampleSet" from_port="output" to_op="Replace" to_port="example set input"/>

      <connect from_op="Replace" from_port="example set output" to_op="Parse Numbers" to_port="example set input"/>

      <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>