Clever Geek Handbook

Using groupby on pandas data to group by fiscal year

I have a dataframe with a datetime64 column called DT. Can groupby be used to group by fiscal year from April 1st to March 31st?

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

+3


source to share


3 answers


With pandas.DatetimeIndex, it's very simple:

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

Or, if you are using Date as your DT index, this is even easier:



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

But be careful, which shift(-3,freq='m')

shifts the date to the end of the month; for example, from April 8 to January 31 and so on. Either way, this suits your problem well.

+2


source


The first thing you want to do is define a function that outputs the fiscal year as a value. You can 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. Usually the groupby method is called by saying something like this df.groupby("colname")

, however this statement is semantically equivalent df.groupby(df["colname"]

- which means you can do something like this ...



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

and then apply the method to groups or whatever you want to do. If you just want these groups to split the callgrouped.groups

+3


source


I had a similar problem and used the following to offset the end of the fiscal year to March (month = 3) using Grouper and specifying the frequency:

grouped_df = df.groupby([pd.Grouper(key='DateColumn', freq=pd.tseries.offsets.BYearEnd(month=3))])

Pandas Business Year End and Grouper

0


source






More articles:


All Articles