Options

if-function, comparing dates

felix_wfelix_w Member Posts: 61 Contributor II
edited November 2018 in Help

Dear Rapidminer Community, 

 

I have a problem I am pretty sure you can help me with. ;) 

 

I have a range of dates which are listed below each other like this: 

01.04.2015

02.04.2015

....

31.12.2017

 

What I would like to do is to implement a seperate Attribute which sets a mark if the date is before a certain date. This means, set "1" if date is before 01.08.2016 and after 01.08.2016 set 2. 

 

The date format I use in Rapidminer is: e.g. "Apr 1, 2015 12:00AM"

 

I tried to do this via "Generate Attributes" and implement an if-function. Like: if(date < 01.08.2017, 1,2) but it didnt work. Is there any handy possibility to do this? 

 

Best regards

Felix

Best Answer

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

    Hi @felix_w,

     

    In your special case you might also consider the function expression date_before().

    The expression would then be:

     

    if(date_before(Datum,date_parse_custom("01.08.2016","dd.MM.yyyy")),1,2)

     

    Process is attached.

     

    Best regards,

    Edin

     

    <?xml version="1.0" encoding="UTF-8"?><process version="8.0.001">
    <context>
    <input/>
    <output/>
    <macros/>
    </context>
    <operator activated="true" class="process" compatibility="8.0.001" expanded="true" name="Process">
    <process expanded="true">
    <operator activated="true" class="retrieve" compatibility="8.0.001" expanded="true" height="68" name="Retrieve Datum" width="90" x="45" y="34">
    <parameter key="repository_entry" value="//Local Repository/Community/date problem/Datum"/>
    </operator>
    <operator activated="true" class="generate_attributes" compatibility="8.0.001" expanded="true" height="82" name="Generate Attributes" width="90" x="179" y="34">
    <list key="function_descriptions">
    <parameter key="before my date" value="if(date_before(Datum,date_parse_custom(&quot;01.08.2016&quot;,&quot;dd.MM.yyyy&quot;)),1,2)"/>
    </list>
    </operator>
    <connect from_op="Retrieve Datum" from_port="output" to_op="Generate Attributes" to_port="example set input"/>
    <connect from_op="Generate Attributes" 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>

Answers

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

    Hi,

     

    i am a fan of using date_diff even though i often mess up a minus sign. So my way would be:

     

    if(
    date_diff(date_parse_custom("01-01-2017","dd-MM-YYYY"), date) > 1,
    "date is bigger",
    "date is smaller"
    )

    Best,

    Martin

    - Sr. Director Data Solutions, Altair RapidMiner -
    Dortmund, Germany
  • Options
    felix_wfelix_w Member Posts: 61 Contributor II

    Hi Martin, 

     

    first of all thank you for help!

     

    I have one further question. The if-function you sent me works for the day 01.01.2017 but when I am adapting the date to e.g 01.04.2017 it still sets the markers only before and after the 01.01.2017? Furthermore, I don't really understand what the "< 1" does in the function? 

     

    Best regards

    Felix

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

    Hi,

     

    date_diff returns the number of milliseconds between the two dates which are delivered. If this is > 1 then the first date was bigger than the second (or the other way around?).

    Thus you can easily do 01-04-2017 or what ever you prefer. You can also change the format, since i used the custom date format option.

    Note: date is the attribute i am comparing this to

     

    Best,

    Martin

    - Sr. Director Data Solutions, Altair RapidMiner -
    Dortmund, Germany
  • Options
    felix_wfelix_w Member Posts: 61 Contributor II

    Hi Martin, 

     

    I am sorry I have adapted the function but it still does not work on my data? It still sets marks until 01.01.2017 and then changes although I have changed the date in the function to 01.04.2017? 

     

    Yes, I have set date to my "Datum"

     

     

     

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

    Hey,

     

    can you maybe post an example process? Hard to debug without

     

    Best,

    Martin

    - Sr. Director Data Solutions, Altair RapidMiner -
    Dortmund, Germany
  • Options
    felix_wfelix_w Member Posts: 61 Contributor II

    Hi Martin, 

     

    I can't send you the original process because it contains sensitive material but I simply copied the "Datum" column in a seperate excel and the only thing to do would be to load it and then use generate attribute. 

    Thank you a lot for helping me out!! 

     

    Regards

    Felix

     

  • Options
    felix_wfelix_w Member Posts: 61 Contributor II

    YESS it worked :D

     

    Although I have no idea why my own solution didn't work because I think I tried this before but nevermind I am just happy that it works now ! 

     

    Also thank you again Martin for your help! 

     

    Best regards

    Felix

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

    Date functions are always tricky :)

    Luckily, the explanations in the function expressions are quite helpful in those cases.

     

    Best,

    Edin

    image.png

     

     

     

     

Sign In or Register to comment.