How to calculate year to date (YTD) of a financial year starting from month April using Pandas dataf-CodePudding

I want to calculate YTD of a financial year using Pandas dataframe. I used below code to find it.But I got YTD from month January.

report_table['ytd_sales'] = report_table.groupby(['year','month', 'planttype', 'market', 'product'])['sales'].cumsum()

Can anyone help me to calculate YTD fom month April to March(financial year).

CodePudding user response：

USE -

report_table['Fiscal Year'] = report_table['Date'].dt.to_period('Q-MAR').dt.qyear
report_table['ytd_sales'] = report_table.groupby(['Fiscal Year',month, 'planttype', 'market', 'product'])['sales'].cumsum()

Example-

import pandas as pd
dates = {'Date': ['1/1/2021', '1/31/2021', '2/1/2021', '2/28/2021', '3/1/2021', '3/31/2021', '4/1/2021', '4/30/2021', '5/1/2021', '5/31/2021', '6/1/2021']}
df = pd.DataFrame(dates)
df['Date'] = pd.to_datetime(df['Date'])
print(df)

df['Fiscal Year'] = df['Date'].dt.to_period('Q-MAR').dt.qyear

Ref link- https://datagy.io/pandas-fiscal-year/

CodePudding user response：

Create column of financial_year by numpy.where :

report_table['financial_year'] = np.where(report_table['month'] > 3,  
                                          report_table['year'] 1, 
                                          report_table['year'])

Or crete datetime by both columns month and year and then convert to financial_year:

report_table['financial_year']=(pd.to_datetime(report_table[['month','year']].assign(day=1))
                                  .dt.to_period('Q-MAR')
                                  .dt.qyear)

report_table['ytd_sales'] = report_table.groupby(['financial_year', 'planttype', 'market', 'product'])['sales'].cumsum()