Convert yyyyQn to yyyy-mm

Question

I have a data frame:

    ID    Date     Volume 
    1    2019Q1      9
    1    2020Q2     11
    2    2019Q3     39
    2    2020Q4     23

I want to convert this to yyyy-Qn to datetime.

I have used a dictionary to map the corresponding dates to the quarters.

But I need a more generalized code in instances where the yyyy changes.

Expected output:

     ID    Date     Volume 
     1    2019-03     9
     1    2020-06    11
     2    2019-09    39
     2    2020-12    23

What did you try? Please, spend some time reading ["How to create a Minimal, Complete, and Verifiable example"](https://stackoverflow.com/help/mcve). — accdias, Apr 22 '21 at 00:42
Dictionary and map.. but need a more generalized code. Thanks — tj judge, Apr 22 '21 at 00:46
This link might help you : https://stackoverflow.com/questions/53898482/clean-way-to-convert-quarterly-periods-to-datetime-in-pandas — Shubham Periwal, Apr 22 '21 at 00:46

Shubham Periwal · Answer 1 · 2021-04-22T02:55:15.227

Here's a simple solution but not as efficient (shouldn't be a problem if your dataset is not too large).

Convert the date column to datetime using to_datetime. Then add 2 months to each date because you want month to be end-of-quarter month

df = pd.DataFrame({'date': ["2019Q1" ,"2019Q3", "2019Q2", "2020Q4"], 'volume': [1,2,3, 4]})
df['datetime'] = pd.to_datetime(df['date'])
df['datetime'] = df['datetime'] + pd.DateOffset(months=2)

Output is the same

date    volume  datetime
0   2019Q1  1   2019-03-01
1   2019Q3  2   2019-09-01
2   2019Q2  3   2019-06-01
3   2020Q4  4   2020-12-01

score 2 · Accepted Answer · answered Apr 22 '21 at 03:07

Let's use pd.PeriodIndex:

df['Date_new'] = pd.PeriodIndex(df['Date'], freq='Q').strftime('%Y-%m')

Output:

   ID    Date  Volume Date_new
0   1  2019Q1       9  2019-03
1   1  2020Q2      11  2020-06
2   2  2019Q3      39  2019-09
3   2  2020Q4      23  2020-12

Convert yyyyQn to yyyy-mm

2 Answers2