convert yyyyqq to pandas date in clean way-CodePudding

I am trying to convert year and quarter to date in pandas. In example below I am trying to get 1980-03-31 for 1980Q1 and so on.

df=pd.DataFrame({'year':['1980','1980','1980','1980'],'qtr':
                 ['Q1','Q2','Q3','Q4']})

scenario['date']=pd.to_datetime(scenario['year'] scenario['qtr'], infer_datetime_format=True)
scenario[['date','year','qtr']]
      date      year    qtr
0   1980-01-01  1980    Q1
1   1980-04-01  1980    Q2
2   1980-07-01  1980    Q3
3   1980-10-01  1980    Q4

CodePudding user response：

are you looking for QuarterEnd offset?

import pandas as pd

df=pd.DataFrame({'year':['1980','1980','1980','1980'],'qtr':
                 ['Q1','Q2','Q3','Q4']})

df['date'] = pd.to_datetime(df['year'] df['qtr'])   pd.tseries.offsets.QuarterEnd()

df[['date','year','qtr']]
        date  year qtr
0 1980-03-31  1980  Q1
1 1980-06-30  1980  Q2
2 1980-09-30  1980  Q3
3 1980-12-31  1980  Q4

CodePudding user response：

df.apply(lambda x: pd.Period(x['year']   x['qtr']), axis=1).dt.to_timestamp(freq='Q')

if you don want use apply

pd.to_datetime(df['year']   df['qtr']).dt.to_period(freq='Q').dt.to_timestamp(freq='Q')

output:

0   1980-03-31
1   1980-06-30
2   1980-09-30
3   1980-12-31
dtype: datetime64[ns]

CodePudding user response：

import datetime as dt
import pandas as pd


def func(x):
    # converts columns to datetime.date object
    if x.qtr == 'Q1':
        month = 1
    elif x.qtr == 'Q2':
        month = 3
    elif x.qtr == 'Q3':
        month = 7
    elif x.qtr == 'Q4':
        month = 10
    dt_obj = dt.date(year=int(x.year), month=month, day=1)
    return dt_obj


df=pd.DataFrame({'year':['1980','1980','1980','1980'],'qtr':
                 ['Q1','Q2','Q3','Q4']})
print(df)
df['date'] = df.apply(lambda x: func(x), axis=1)
print(df)