Nominal to Date for quarterly data

hmhsing

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!   

BalazsBaranyRM

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

BalazsBaranyRM

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

lionelderkrikor

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.

 

hmhsing

Thanks a lot for all the help, now I know quarterly data needs be expressed in month and day first for "Date" attribute.