How to find the last day of each quarter in a given date period in Python?

Question

Now I have a dataframe like this:
(Ticker and date are indexes)
Ticker  Date        Open    Low High    Close   Adj. Close  Shares Outstanding                              
A       2007-01-25  34.99   34.05      35.48    34.30       NaN
        2007-01-26  34.30   33.46      34.60    34.41       NaN
        ....
        2007-06-27  34.30   34.00      34.40    34.09       NaN
        2007-06-28  33.98   33.68      34.08    33.97       NaN
        2007-07-01  34.08   33.63      34.32    34.01       NaN
B       2007-01-12  34.99   34.05      35.48    34.30       NaN
        ...
        2007-08-27  34.30   33.46      34.60    34.41       NaN
        2007-08-28  34.30   34.00      34.40    34.09       NaN
        2007-09-01  33.98   33.68      34.08    33.97       NaN
        2007-09-02  34.08   33.63      34.32    34.01       NaN
C       2007-03-28  34.99   34.05      35.48    34.30       NaN
        2007-03-29  34.30   33.46      34.60    34.41       NaN
        2007-04-01  34.30   34.00      34.40    34.09       NaN
        2007-04-02  33.98   33.68      34.08    33.97       NaN
        2007-04-03  34.08   33.63      34.32    34.01       NaN

For each ticker, there are stock prices for each day for many years. However, I only want the quarter end prices for each company, and get a dataframe of all companies' quarterly ending price. I also want to separate each quarter for all companies into different dataframe.
For example:
Ticker  Date        Open    Low High    Close   Adj. Close  Shares Outstanding                              
A       2007-06-30  34.99   34.05      35.48    34.30       NaN
B       2007-06-30  34.30   33.46      34.60    34.41       NaN
        ....
c       2007-06-30  34.30   34.00      34.40    34.09       NaN

Once problem is, due to the fact that the stock market won't open on weekend. So the quarter end for each ticker might not be calendar quarter end. For example, Q2 ends on 6/30, but stock quarter end might be on 6/29. Is there a way I can find the last day of the given date range for each quarter?

Serge Ballesta · Answer

I would first compute the last row for each ticker in each quarter through groupby and last into a temporary dataframe, then extract the individual dataframes per quarter from it:
tmp = df.groupby(['Ticker', pd.PeriodIndex(df['Date'], freq='Q',   name='Quarter')]

).last().reset_index(level=0)
individual_df = {str(i): df.reset_index(drop=True) for i, df in tmp.groupby(['Quarter'])}

With your example data, it gives:
>>> pprint.pprint(individual_df)

{'2007Q1':   Ticker       Date   Open  Low High  Close  Adj. Close  Shares Outstanding
0      A 2007-01-26  34.30     33.46  34.60       34.41                 NaN
1      B 2007-01-12  34.99     34.05  35.48       34.30                 NaN
2      C 2007-03-29  34.30     33.46  34.60       34.41                 NaN,
 '2007Q2':   Ticker       Date   Open  Low High  Close  Adj. Close  Shares Outstanding
0      A 2007-06-28  33.98     33.68  34.08       33.97                 NaN
1      C 2007-04-03  34.08     33.63  34.32       34.01                 NaN,
 '2007Q3':   Ticker       Date   Open  Low High  Close  Adj. Close  Shares Outstanding
0      A 2007-07-01  34.08     33.63  34.32       34.01                 NaN
1      B 2007-09-02  34.08     33.63  34.32       34.01                 NaN}

Code Different · Answer

A simple GroupBy is all you need:
quarter = pd.PeriodIndex(df['Date'], freq='Q', name='Quarter')
result = df.groupby(['Ticker', quarter]).last()

To get data for a specific quarter:
result.loc[('A', '2019Q1')]

How to find the last day of each quarter in a given date period in Python?

2 Answers

Add your own answers!

Ask a Question