python pandas: get fiscal quarter from fiscal year and month (for UK)

Question

I have a dataframe with two useful columns 1) fiscal year, 2) date. I want to add a new column which shows the fiscal quarter.

FYI - UK Financial year runs from 1 April to 31 March

my data looks like:

    fiscal year  date
    FY15/16      2015-11-01
    FY14/15      2014-10-01
    FY15/16      2016-02-01

I want it to look like this:

    fiscal year  date        Quarter
    FY15/16      2015-11-01  q3
    FY14/15      2014-10-01  q3
    FY15/16      2016-02-01  q4

Really hope I got the quarters right!

Code below works but I believe it returns American financial quarters but I want UK.

df['Quater'] = df['Date'].dt.quarter 

Solution

import pandas as pd
df = pd.DataFrame({'date': ['2015-11-01', '2014-10-01', '2016-02-01'],
                   'fiscal year': ['FY15/16', 'FY14/15', 'FY15/16']})
df['Quarter'] = pd.PeriodIndex(df['date'], freq='Q-MAR').strftime('Q%q')
print(df)

yields

         date fiscal year Quarter
0  2015-11-01     FY15/16      Q3
1  2014-10-01     FY14/15      Q3
2  2016-02-01     FY15/16      Q4

---

The default quarterly frequency Q is equivalent to Q-DEC.

In [60]: pd.PeriodIndex(df['date'], freq='Q')
Out[60]: PeriodIndex(['2015Q4', '2014Q4', '2016Q1'], dtype='int64', freq='Q-DEC')

Q-DEC specifies quarterly periods whose last quarter ends on the last day in December. Q-MAR specifies quarterly periods whose last quarter ends on the last day in March.

In [86]: pd.PeriodIndex(df['date'], freq='Q-MAR')
Out[86]: PeriodIndex(['2016Q3', '2015Q3', '2016Q4'], dtype='int64', freq='Q-MAR')

Comments

[psychemedia]

Note that for personal tax purposes, the UK fiscal year starts on April 6th...

[Boosted_d16]

This is exactly what I want however I get this error: AttributeError: 'PeriodIndex' object has no attribute 'strftime'

[Boosted_d16]

I'm using 0.16.2 and your code works on 0.17.1 pandas

[unutbu]

For older versions of pandas you could use df['Quarter'] = 'Q' + pd.PeriodIndex(df['date'], freq='Q-MAR').to_series().dt.quarter.astype(str).