Find end date of quarter given date, pandas

Question

Assume we have a table like

table = [[datetime.datetime(2015, 1, 1), 1, 0.5],
         [datetime.datetime(2015, 1, 27), 1, 0.5],
         [datetime.datetime(2015, 1, 31), 1, 0.5],
         [datetime.datetime(2015, 2, 1), 1, 2],
         [datetime.datetime(2015, 2, 3), 1, 2],
         [datetime.datetime(2015, 2, 15), 1, 2],
         [datetime.datetime(2015, 2, 28), 1, 2],
         [datetime.datetime(2015, 3, 1), 1, 3],
         [datetime.datetime(2015, 3, 17), 1, 3],
         [datetime.datetime(2015, 3, 31), 1, 3]]

df = pd.DataFrame(table, columns=['Date', 'Id', 'Value'])

Is there a way to get the specific end date of the actual quarter given the dates in the column Date? E.g., I would like to add the column Q_date to df such that

        Date  Id  Value  Qdate
0 2015-01-01   1    0.5  2015-03-31
1 2015-01-27   1    0.5  2015-03-31
2 2015-01-31   1    0.5  2015-03-31
3 2015-02-01   1    2.0  2015-03-31
4 2015-02-03   1    2.0  2015-03-31
5 2015-02-15   1    2.0  2015-03-31
6 2015-02-28   1    2.0  2015-03-31
7 2015-03-01   1    3.0  2015-03-31
8 2015-03-17   1    3.0  2015-03-31
9 2015-03-31   1    3.0  2015-03-31

I've only considered the first quarter for simplicity - as I know what date it is.

Answer 1

You can use pd.tseries.offsets.QuarterEnd() to achieve your goal here.

import pandas as pd
import datetime

# your data
# ================================
table = [[datetime.datetime(2015, 1, 1), 1, 0.5],
         [datetime.datetime(2015, 1, 27), 1, 0.5],
         [datetime.datetime(2015, 1, 31), 1, 0.5],
         [datetime.datetime(2015, 2, 1), 1, 2],
         [datetime.datetime(2015, 2, 3), 1, 2],
         [datetime.datetime(2015, 2, 15), 1, 2],
         [datetime.datetime(2015, 2, 28), 1, 2],
         [datetime.datetime(2015, 3, 1), 1, 3],
         [datetime.datetime(2015, 3, 17), 1, 3],
         [datetime.datetime(2015, 3, 31), 1, 3]]

df = pd.DataFrame(table, columns=['Date', 'Id', 'Value'])

# processing
# ================================
# in case of 2015.03.31, simple QuarterEnd will roll forward to next quarter, so use DateOffset here to make it robust to this
df['Qdate'] = [date - pd.tseries.offsets.DateOffset(days=1) + pd.tseries.offsets.QuarterEnd() for date in  df.Date]

print(df)


        Date  Id  Value      Qdate
0 2015-01-01   1    0.5 2015-03-31
1 2015-01-27   1    0.5 2015-03-31
2 2015-01-31   1    0.5 2015-03-31
3 2015-02-01   1    2.0 2015-03-31
4 2015-02-03   1    2.0 2015-03-31
5 2015-02-15   1    2.0 2015-03-31
6 2015-02-28   1    2.0 2015-03-31
7 2015-03-01   1    3.0 2015-03-31
8 2015-03-17   1    3.0 2015-03-31
9 2015-03-31   1    3.0 2015-03-31

Answer 2

An easier way to do it would be to convert the date to a (quarter) period, and then back to a date, e.g.:

df['Qdate'] = df['Date'].dt.to_period("Q").dt.end_time

Note there is also .start_time for the start of the quarter.

Answer 3

Using searchsorted is another option:

import datetime
import pandas as pd

table = [[datetime.datetime(2015, 1, 1), 1, 0.5],
         [datetime.datetime(2015, 1, 27), 1, 0.5],
         [datetime.datetime(2015, 1, 31), 1, 0.5],
         [datetime.datetime(2015, 2, 1), 1, 2],
         [datetime.datetime(2015, 2, 3), 1, 2],
         [datetime.datetime(2015, 2, 15), 1, 2],
         [datetime.datetime(2015, 2, 28), 1, 2],
         [datetime.datetime(2015, 3, 1), 1, 3],
         [datetime.datetime(2015, 3, 17), 1, 3],
         [datetime.datetime(2015, 3, 31), 1, 3],
         [datetime.datetime(2015, 4, 1), 1, 3],
]

df = pd.DataFrame(table, columns=['Date', 'Id', 'Value'])
quarters = pd.date_range(
    df['Date'].min(), 
    df['Date'].max()+pd.tseries.offsets.QuarterEnd(), freq='Q')
df['Qdate'] = quarters[quarters.searchsorted(df['Date'].values)]
print(df)

yields

         Date  Id  Value      Qdate
0  2015-01-01   1    0.5 2015-03-31
1  2015-01-27   1    0.5 2015-03-31
2  2015-01-31   1    0.5 2015-03-31
3  2015-02-01   1    2.0 2015-03-31
4  2015-02-03   1    2.0 2015-03-31
5  2015-02-15   1    2.0 2015-03-31
6  2015-02-28   1    2.0 2015-03-31
7  2015-03-01   1    3.0 2015-03-31
8  2015-03-17   1    3.0 2015-03-31
9  2015-03-31   1    3.0 2015-03-31
10 2015-04-01   1    3.0 2015-06-30

By avoiding computation row-by-row, using searchsorted as above can be orders of magnitude faster for moderately large DataFrames.