Nominal to Date for quarterly data

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!

Find more posts tagged with

AI Studio

ETL + Data Prep

Accepted answers

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

All comments

BalazsBaranyRM

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 :

Image: https://us.v-cdn.net/6030995/uploads/editor/1d/7kok4pv8epso.png

Is it the normal behavior ?

Regards,

Lionel

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

timestamp_convert_to_quarter.rmp

hmhsing

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