Ytukyg

I have a dataframe (df). The Date variable is a string. I want to convert it to date and reformat it to the date at the end of the quarter. A sample is below:

df:

         Date

0     201601

1     201602

2     201603

3     201604

201601 is the first quarter of 2016 and 201604 is the fourth quarter of 2016. The desired outcome is:

df:

          Date

0     2016-03-31

1     2016-06-30

2     2016-09-30

3     2016-12-31

This is what I tried, but it doesn't work.

df['date'] = pd.to_datetime(df.Date, format = '%Y%q')

Thanks!

You could define a function to compute the date and then apply that function.

Using the monthrange function from the calendar module (https://docs.python.org/3.7/library/calendar.html#calendar.monthrange), your function might look something like:

from calendar import monthrange

from datetime import datetime   



def end_quarter(quarter):

    year = int(quarter[:4])

    month = int(quarter[-2:]) * 3

    day = monthrange(year, month)[1]

    return datetime(year, month, day).date()

and you could then apply it using:

df["Date"] = df["Date"].apply(end_quarter)

One way is to keep treating it as a string, and simply do an if / else statement, i.e.

res = 

for i in df['Date']:

    v1 = i[-2:]

    if v1 == '01':

        res.append(i[:4] + '-03-31')

    elif v1 == '02':

        res.append(i[:4] + '-06-30')

    elif v1 == '03':

        res.append(i[4:] + '-09-30')

    else:

        res.append(i[4:] + '-12-31')



#>>> res

#['2016-03-31', '2016-06-30', '03-09-30', '04-12-31']