[SOLVED] Create calendar week out of date (German definition of calendar week)

SylviaSylvia Member Posts: 4 Contributor I
edited November 2018 in Help
Hello,

I'd like to analyze the number of contracts by calendar week based on a data extract which shows all contracts including the date of contract creation. The output table should include the number of created contracts grouped by product group, calendar week and year.

I transformed the contract creation date to calendar week using the operator "Date to Numerical" (time unit = week, week relative to year). Unfortunately the calendar week is generated based on the definition used in the US meaning that Sunday is the first day of a week. Is it possible to change the default settings so that the calendar week is generated according to the German definition (Monday is the first day of a week, each week has seven days, for the calendar week end of December / beginning of January the Thursday indicates to which year the calendar week belongs).

Thanks
Sylvia

Answers

  • Nils_WoehlerNils_Woehler Member Posts: 463  Guru
    Hi Sylvia,

    unfortunately you cant select the locale for the Date to Numerical operator. But i think you can use Date to Nominal and Parse Numbers afterwards as a workaround.
    Here is a example process:


    <?xml version="1.0" encoding="UTF-8" standalone="no"?>
    <process version="5.2.001">
      <context>
        <input/>
        <output/>
        <macros/>
      </context>
      <operator activated="true" class="process" compatibility="5.2.001" expanded="true" name="Process">
        <process expanded="true" height="611" width="748">
          <operator activated="true" class="generate_sales_data" compatibility="5.2.001" expanded="true" height="60" name="Generate Sales Data" width="90" x="112" y="210"/>
          <operator activated="true" breakpoints="after" class="date_to_nominal" compatibility="5.2.001" expanded="true" height="76" name="Date to Nominal" width="90" x="447" y="210">
            <parameter key="attribute_name" value="date"/>
            <parameter key="date_format" value="w"/>
            <parameter key="locale" value="German"/>
            <parameter key="keep_old_attribute" value="true"/>
          </operator>
          <operator activated="true" class="parse_numbers" compatibility="5.2.001" expanded="true" height="76" name="Parse Numbers" width="90" x="581" y="210">
            <parameter key="attribute_filter_type" value="single"/>
            <parameter key="attribute" value="date_nominal"/>
          </operator>
          <connect from_op="Generate Sales Data" from_port="output" to_op="Date to Nominal" to_port="example set input"/>
          <connect from_op="Date to Nominal" 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>
    Best,
    Nils
  • SylviaSylvia Member Posts: 4 Contributor I
    Hi Nils,

    thank you very much for your great solution. Do you have also an idea how to add the year of the calendar week? E. g. the 31st of December 2001 belongs to calendar week 1 of the year 2002 and not 2001.

    If there is no process available I would use a matching table.

    Best regards
    Sylvia
  • Nils_WoehlerNils_Woehler Member Posts: 463  Guru
    Hi Sylvia,

    this is also possible by extending the process a little bit:


    <?xml version="1.0" encoding="UTF-8" standalone="no"?>
    <process version="5.2.001">
      <context>
        <input/>
        <output/>
        <macros/>
      </context>
      <operator activated="true" class="process" compatibility="5.2.001" expanded="true" name="Process">
        <process expanded="true" height="611" width="1283">
          <operator activated="true" class="generate_sales_data" compatibility="5.2.001" expanded="true" height="60" name="Generate Sales Data" width="90" x="112" y="210"/>
          <operator activated="true" class="date_to_nominal" compatibility="5.2.001" expanded="true" height="76" name="Date to Nominal" width="90" x="246" y="210">
            <parameter key="attribute_name" value="date"/>
            <parameter key="date_format" value="w"/>
            <parameter key="locale" value="German"/>
            <parameter key="keep_old_attribute" value="true"/>
          </operator>
          <operator activated="true" class="parse_numbers" compatibility="5.2.001" expanded="true" height="76" name="Parse Numbers" width="90" x="380" y="210">
            <parameter key="attribute_filter_type" value="single"/>
            <parameter key="attribute" value="date_nominal"/>
          </operator>
          <operator activated="true" class="rename" compatibility="5.2.001" expanded="true" height="76" name="Rename" width="90" x="447" y="390">
            <parameter key="old_name" value="date_nominal"/>
            <parameter key="new_name" value="week"/>
            <list key="rename_additional_attributes"/>
          </operator>
          <operator activated="true" class="date_to_nominal" compatibility="5.2.001" expanded="true" height="76" name="Date to Nominal (2)" width="90" x="648" y="345">
            <parameter key="attribute_name" value="date"/>
            <parameter key="date_format" value="yyyy"/>
            <parameter key="locale" value="German"/>
            <parameter key="keep_old_attribute" value="true"/>
          </operator>
          <operator activated="true" class="parse_numbers" compatibility="5.2.001" expanded="true" height="76" name="Parse Numbers (2)" width="90" x="782" y="300">
            <parameter key="attribute_filter_type" value="single"/>
            <parameter key="attribute" value="date_nominal"/>
          </operator>
          <operator activated="true" class="rename" compatibility="5.2.001" expanded="true" height="76" name="Rename (2)" width="90" x="983" y="210">
            <parameter key="old_name" value="date_nominal"/>
            <parameter key="new_name" value="year"/>
            <list key="rename_additional_attributes"/>
          </operator>
          <connect from_op="Generate Sales Data" from_port="output" to_op="Date to Nominal" to_port="example set input"/>
          <connect from_op="Date to Nominal" 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_op="Rename" to_port="example set input"/>
          <connect from_op="Rename" from_port="example set output" to_op="Date to Nominal (2)" to_port="example set input"/>
          <connect from_op="Date to Nominal (2)" from_port="example set output" to_op="Parse Numbers (2)" to_port="example set input"/>
          <connect from_op="Parse Numbers (2)" from_port="example set output" to_op="Rename (2)" to_port="example set input"/>
          <connect from_op="Rename (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>
    Best,
    Nils
  • SylviaSylvia Member Posts: 4 Contributor I
    Hi Nils,

    thank you for your reply. The extended process is using the calendar year for each date but the year of the date is sometimes different to the year of the assigned calendar week. In Germany calendar weeks include sometimes dates out of two years (e. g. the 31st of December 2001 belongs to calendar week 1 of the year 2002). Is it possible to calculate the year of the calendar week?

    Best regards
    Sylvia
  • Nils_WoehlerNils_Woehler Member Posts: 463  Guru
    Hi,

    the process should work exactly as you have described it. Here is an example for the 01.01.2012:

    <?xml version="1.0" encoding="UTF-8" standalone="no"?>
    <process version="5.2.001">
      <context>
        <input/>
        <output/>
        <macros/>
      </context>
      <operator activated="true" class="process" compatibility="5.2.001" expanded="true" name="Process">
        <process expanded="true" height="611" width="1283">
          <operator activated="true" breakpoints="after" class="generate_data_user_specification" compatibility="5.2.001" expanded="true" height="60" name="Generate Data by User Specification" width="90" x="112" y="210">
            <list key="attribute_values">
              <parameter key="date" value="date_parse(1325379661000)"/>
            </list>
            <list key="set_additional_roles"/>
          </operator>
          <operator activated="true" class="date_to_nominal" compatibility="5.2.001" expanded="true" height="76" name="Date to Nominal" width="90" x="246" y="210">
            <parameter key="attribute_name" value="date"/>
            <parameter key="date_format" value="w"/>
            <parameter key="locale" value="German"/>
            <parameter key="keep_old_attribute" value="true"/>
          </operator>
          <operator activated="true" class="parse_numbers" compatibility="5.2.001" expanded="true" height="76" name="Parse Numbers" width="90" x="380" y="210">
            <parameter key="attribute_filter_type" value="single"/>
            <parameter key="attribute" value="date_nominal"/>
          </operator>
          <operator activated="true" class="rename" compatibility="5.2.001" expanded="true" height="76" name="Rename" width="90" x="447" y="390">
            <parameter key="old_name" value="date_nominal"/>
            <parameter key="new_name" value="week"/>
            <list key="rename_additional_attributes"/>
          </operator>
          <operator activated="true" class="date_to_nominal" compatibility="5.2.001" expanded="true" height="76" name="Date to Nominal (2)" width="90" x="648" y="345">
            <parameter key="attribute_name" value="date"/>
            <parameter key="date_format" value="yyyy"/>
            <parameter key="locale" value="German"/>
            <parameter key="keep_old_attribute" value="true"/>
          </operator>
          <operator activated="true" class="parse_numbers" compatibility="5.2.001" expanded="true" height="76" name="Parse Numbers (2)" width="90" x="782" y="300">
            <parameter key="attribute_filter_type" value="single"/>
            <parameter key="attribute" value="date_nominal"/>
          </operator>
          <operator activated="true" class="rename" compatibility="5.2.001" expanded="true" height="76" name="Rename (2)" width="90" x="983" y="210">
            <parameter key="old_name" value="date_nominal"/>
            <parameter key="new_name" value="year"/>
            <list key="rename_additional_attributes"/>
          </operator>
          <connect from_op="Generate Data by User Specification" from_port="output" to_op="Date to Nominal" to_port="example set input"/>
          <connect from_op="Date to Nominal" 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_op="Rename" to_port="example set input"/>
          <connect from_op="Rename" from_port="example set output" to_op="Date to Nominal (2)" to_port="example set input"/>
          <connect from_op="Date to Nominal (2)" from_port="example set output" to_op="Parse Numbers (2)" to_port="example set input"/>
          <connect from_op="Parse Numbers (2)" from_port="example set output" to_op="Rename (2)" to_port="example set input"/>
          <connect from_op="Rename (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>
    Best,
    Nils
  • SylviaSylvia Member Posts: 4 Contributor I
    Hi Nils,

    thanks, the result of your process is year 2012 and week 52 for the date 01.01.2012. The calendar week is correct but it belongs to 2011 and not 2012. Maybe my local settings are leading to another result as you can see.

    In the meantime I've created a table which includes calendar week and year for each date in the last past years. Using the operator "join" it is possible to assign the calendar week and the year of the calendar week to each date.

    Best regards
    Sylvia
  • Nils_WoehlerNils_Woehler Member Posts: 463  Guru
    Oh, well, then i got you wrong. I thought you wanted to get the actual year of the date and not the year the week belongs to.
    But if it works now the way you have described it everything then :-)

    Best,
    Nils
  • GaiyaGaiya Member Posts: 2 Contributor I

    Hi Nils,

     

    I'm new to rapidminer studio 8, I'm trying to get the same thing done with the ISO weeknumbering instead of the US format. Can you please give me guidance how to do it in rapidminder studio 8.

     

    Thank you,

     

    Dong

  • GaiyaGaiya Member Posts: 2 Contributor I

    I figured out how to edit the xml file, got it going already :)

    sgenzer
Sign In or Register to comment.