How to convert date format QQ-YYYY to a datetime object

Question

I have a pandas dataframe with a column that should indicate the end of a financial quarter. The format is of the type "Q1-2009". Is there a quick way to convert these strings into a timestamp as "2009-03-31"?

I have found only the conversion from the format "YYYY-QQ", but not the opposite.

`pd.to_datetime` returns a ValueError: ('Unknown string format:', 'Q1-2009'). — AleB, Jan 07 '19 at 14:28

jezrael · Accepted Answer · 2019-01-07T14:32:19.837

3

Create quarters periods with swap quarter and year part by replace and convert to datetimes with PeriodIndex.to_timestamp:

df = pd.DataFrame({'per':['Q1-2009','Q3-2007']})

df['date'] = (pd.PeriodIndex(df['per'].str.replace(r'(Q\d)-(\d+)', r'\2-\1'), freq='Q')
                .to_timestamp(how='e'))

print (df)
       per       date
0  Q1-2009 2009-03-31
1  Q3-2007 2007-09-30

Another solution is use string indexing:

df['date'] = (pd.PeriodIndex(df['per'].str[-4:] + df['per'].str[:2], freq='Q')
                .to_timestamp(how='e'))

edited Jan 07 '19 at 14:32

answered Jan 07 '19 at 14:27

jezrael

822,522
95
1,334
1,252

It works perfectly! Very useful. There was no answer for a format like that – AleB Jan 07 '19 at 14:31

score 1 · Answer 2 · answered Jan 07 '19 at 15:33

One solution using a list comprehension followed by pd.offsets.MonthEnd:

# data from @jezrael
df = pd.DataFrame({'per':['Q1-2009','Q3-2007']})

def get_values(x):
    ''' Returns string with quarter number multiplied by 3 '''
    return f'{int(x[0][1:])*3}-{x[1]}'

values = [get_values(x.split('-')) for x in df['per']]
df['LastDay'] = pd.to_datetime(values, format='%m-%Y') + pd.offsets.MonthEnd(1)

print(df)

       per    LastDay
0  Q1-2009 2009-03-31
1  Q3-2007 2007-09-30

How to convert date format QQ-YYYY to a datetime object

2 Answers2