Search code examples
pandasgroup-by

using groupby on pandas dataframe to group by financial year

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.*

Solution

  • 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.