Change value order?

eldenosoeldenoso Member Posts: 65 Contributor I
edited November 2018 in Help

Hey altogether,

 

simple problem with hopefully a simple answer. Is there a way (in excel this is quite easy) to change orders withing an example e.g.

12.25.23 to 25.12.23

Thank you for your help.

Best Answer

  • Edin_KlapicEdin_Klapic Moderator, Employee, RMResearcher, Member Posts: 299 RM Data Scientist
    Solution Accepted

    Hi Philipp,

    Within Generate Attributes everything between double quotes is interpreted as a string. So the function wants to parse the string Day (and not the attribute with the name Day) to a date which creates the error.

    Assuming the value type of Day is nominal the solution would therefore be

    date_str_custom(date_parse_custom(Day,"M/d/yy"),"dd.MM.yy")

     

    Best,

    Edin

Answers

  • MartinLiebigMartinLiebig Administrator, Moderator, Employee, RapidMiner Certified Analyst, RapidMiner Certified Expert, University Professor Posts: 3,503 RM Data Scientist

    Hey,

     

    do you mean attribute order? If yes, reorder attributes is the operator. Or do you to sort a string?

     

    ~Martin

    - Sr. Director Data Solutions, Altair RapidMiner -
    Dortmund, Germany
  • eldenosoeldenoso Member Posts: 65 Contributor I
    No, I don't want to change the order. In Excel you would use the mid() function the reorder Parts of one "cell".
  • MartinLiebigMartinLiebig Administrator, Moderator, Employee, RapidMiner Certified Analyst, RapidMiner Certified Expert, University Professor Posts: 3,503 RM Data Scientist

    Hey,

     

    you can go for a Replace operator with a regex

     

    (\d+).(\d+).(\d+)

     

    and replace it with

     

    $2.$1.$3

     

    Edit: I think the Excel mid is similar than cut() in Generate Attributes, if you prefer this.

     

    Best,

    Martin

    - Sr. Director Data Solutions, Altair RapidMiner -
    Dortmund, Germany
  • eldenosoeldenoso Member Posts: 65 Contributor I

    Regex worked pretty well. Going a step further what I wanted to achieve was to convert the english date into a german one so:

    12/3/16 (mm/dd/yy) in 03.12.16

    I replaced the "/" with dots and changed the order of day and month with the regex-replace. Now to further work with this date in excel it should be in the named format (above). Unfortunately it is in the format 3.12.16, thus the zeros are missing. 

    My question is how can I get the zeros in the date and if this way of achieving this is even good to do so, because I think this is somehow very complicated to just transform english date into a german one.

    Thank you 

    Philipp

  • MartinLiebigMartinLiebig Administrator, Moderator, Employee, RapidMiner Certified Analyst, RapidMiner Certified Expert, University Professor Posts: 3,503 RM Data Scientist

    Hey,

     

    i think its just another regex with Replace:

     

    0(\d)

     

    replace with

     

    $1


    Attached is a process doing it.

     

    ~Martin

    <?xml version="1.0" encoding="UTF-8"?><process version="7.4.000">
    <context>
    <input/>
    <output/>
    <macros/>
    </context>
    <operator activated="true" class="process" compatibility="7.4.000" expanded="true" name="Process">
    <process expanded="true">
    <operator activated="true" class="generate_data_user_specification" compatibility="7.4.000" expanded="true" height="68" name="Generate Data by User Specification" width="90" x="112" y="34">
    <list key="attribute_values">
    <parameter key="date" value="&quot;03.12.16&quot;"/>
    </list>
    <list key="set_additional_roles"/>
    </operator>
    <operator activated="true" class="replace" compatibility="7.4.000" expanded="true" height="82" name="Replace" width="90" x="246" y="34">
    <parameter key="replace_what" value="(\d+).(\d+).(\d+)"/>
    <parameter key="replace_by" value="$2.$1.$3"/>
    </operator>
    <operator activated="true" class="replace" compatibility="7.4.000" expanded="true" height="82" name="Replace (2)" width="90" x="380" y="34">
    <parameter key="replace_what" value="0(\d)"/>
    <parameter key="replace_by" value="$1"/>
    </operator>
    <connect from_op="Generate Data by User Specification" from_port="output" to_op="Replace" to_port="example set input"/>
    <connect from_op="Replace" from_port="example set output" to_op="Replace (2)" to_port="example set input"/>
    <connect from_op="Replace (2)" 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>
    - Sr. Director Data Solutions, Altair RapidMiner -
    Dortmund, Germany
  • Edin_KlapicEdin_Klapic Moderator, Employee, RMResearcher, Member Posts: 299 RM Data Scientist

    Hi Philipp,

     

    I suggest the usage of the Generate Attributes Operator and the built-in date conversion functions.

    The following expression converts the nominal value "12/3/16" into the desired nominal output format "03.12.16".

     

    date_str_custom(date_parse_custom("12/3/16","M/d/yy"),"dd.MM.yy")

     

    Simply replace the string with the attribute name and you are set :)

     

    Best regards,

    Edin

  • eldenosoeldenoso Member Posts: 65 Contributor I

    But then he "deletes" the zeros? I tried it and nothing really changed. The process you posted unfortunately doesn't work. It says "Day not found".

  • IngoRMIngoRM Administrator, Moderator, Employee, RapidMiner Certified Analyst, RapidMiner Certified Expert, Community Manager, RMResearcher, Member, University Professor Posts: 1,751 RM Founder

    Although I am sure that you will get there with regex I think Edin's solution above is the simplest approach...

  • eldenosoeldenoso Member Posts: 65 Contributor I

    Thank you Edin, 

    that soundy very useful. When I replace the string (name of the attribute is "Day")

    date_str_custom(date_parse_custom("Day","M/d/yy"),"dd.MM.yy")

    I get the error: "invalid_argument.date." and the process stops.

    Do you know where there could be a mistake?

    Philipp 

Sign In or Register to comment.