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

user_8794630 0
Viewed 2322

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.

pandas datetime python
2 Answers

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'))
user_29010020

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
user_92095460
Related