using groupby on pandas dataframe to group by financial year

Question

I have a dataframe with a datetime64 column called DT. Is it possible to use groupby to group by financial year from April 1 to March 31?

For example,

    Date | PE_LOW 
    2010-04-01 | 15.44
    ...
    2011-03-31 | 16.8
    2011-04-02 | 17.
    ...
    2012-03-31 | 17.4

For the above data, I want to group by Fiscal Year 2010-2011 and Fiscal Year 2011-2012 without creating an extra column.*

Answer 1

The first thing you want to do is define a function that outputs the financial year as a value. You could use the following.

def getFiscalYear(dt):
    year = dt.year
    if dt.month<4: year -= 1
    return year

You say you don't want to use an extra column to group the frame. Typically the groupby method is called by saying something like this df.groupby("colname") however that statement is semantically equivalent to df.groupby(df["colname"] - meaning you can do something like this...

grouped = DT.groupby(DT['Date'].apply(getFiscalYear))

and then apply a method to the groups or whatever you want to do. If you just want these groups separated call grouped.groups

Answer 2

With pandas.DatetimeIndex, that is very simple:

DT.groupby(pd.DatetimeIndex(DT.Date).shift(-3,freq='m').year)

Or if you use Date as an index of DT, it is even simpler:

DT.groupby(DT.index.shift(-3,freq='m').year)

But beware that shift(-3,freq='m') shifts date to ends of months; for example, 8 Apr to 31 Jan and so on. Anyway, it fits your problem well.