CODEWITHSUNDEEP
pythonpandaspython-datetime
Mike
Mike

Reputation: 4259

reading quarterly data in pandas

I have a dataset with quarterly observations indicated as 200101 (quarter 1 of 2001) to 201504 (quarter 4 of 2015). I would like to transform these into proper pandas dates indices.

200101  ->  2001-03-31
       ...
201504  ->  2015-12-31

for year/months I often use

import datetime as dt
dates = [dt.datetime.strptime(str(d), '%Y%m') for d in series['date']]
series['date'] = pd.date_range(dates[0], dates[-1], freq='M')

unfortunately, the '%Y%q' notation is not allowed. Any suggestion on how to read quarterly date as above elegantly?

Upvotes: 3

Views: 1847

Answers (1)

MaxU - stand with Ukraine
MaxU - stand with Ukraine

Reputation: 210852

you can convert those strings into PeriodIndex(freq='Q') and then (if needed) into timestamp(freq='M')

Demo:

In [272]: df
Out[272]:
       qt
0  200101
1  201504

In [273]: pd.PeriodIndex(df.qt.astype(str).str.replace(r'(\d{4})[0]?(\d{1})', r'\1q\2'),
                         freq='Q') \
            .to_timestamp(freq='M')
Out[273]: DatetimeIndex(['2001-01-31', '2015-10-31'], dtype='datetime64[ns]', name='qt', freq=None)

Upvotes: 1

Related Questions