Nominal to Date for quarterly data

hmhsinghmhsing Member Posts: 29 Maven
I have an attribute includes following nominal data:
2011-Q1
2011-Q2
2011-Q3
...
I hope to change them to Date, however the date format shows only Year, Month and Day but no Quarter. Is there anyway I can solve this? Thanks for the help!   
Tagged:

Best Answer

  • BalazsBaranyBalazsBarany Administrator, Moderator, Employee, RapidMiner Certified Analyst, RapidMiner Certified Expert Posts: 955 Unicorn
    Solution Accepted
    Hi,

    these are not full dates. You need to define yourself which date you want to refer to. The first day of the quarter? The middle one? The end?

    If the first day is OK, here's a formula you can use in Generate Attributes to create an exact nominal date from the quarter:

    prefix(date, 5) + str(1 + (parse(suffix(date, 1)) - 1) * 3) + "-01"

    You can then convert this to an actual date attribute with Nominal to Date and the date format "yyyy-M-dd".

    Regards,
    Balázs

Answers

  • lionelderkrikorlionelderkrikor Moderator, RapidMiner Certified Analyst, Member Posts: 1,195 Unicorn
    Dear all,

    @hmhsing , unfortunately, I do not come with an answer but with a question .... :(

    I tried to convert your attribute to a date attribute using the following Python script but without success...
    data.rm_metadata["date"]=('date','id')
    data['date'] = pd.to_datetime(data['date'])
    data['date'] = pd.PeriodIndex(data['date'], freq='Q')

    But for an unknown reason, the line : 
    data['date'] = pd.to_datetime(data['date'])
    re-initialize the date to 1970 and does not apply the required format. In deed I obtain as final result : 



    Is it the normal behavior ?

    Regards,

    Lionel

    PS : in attached file, my (unsucessful) process.

     



  • hmhsinghmhsing Member Posts: 29 Maven
    Thanks a lot for all the help, now I know quarterly data needs be expressed in month and day first for "Date" attribute.   
Sign In or Register to comment.