How to calculate YTD (Year to Date) value using Pandas Dataframe?

Question

I want to calculate YTD using pandas dataframe in each month. Here I have used two measurements named sales and sales Rate. For measurement sales, YTD is calculated by taking the cumulative sum.Code is given below:

report_table['ytd_value'] = report_table.groupby(['financial_year', 'measurement', 'place', 'market', 'product'], sort=False)['value'].cumsum()

But, In the case of measurement sales rate YTD is calculated in different way.

YTD Calculation Explanation (sales rate) given below:

First month (April) YTD value of financial year = First month (April) value of financial year

From second month of financial year onwards YTD valueis calculated using formula.

Month May YTD value = ((APRIL YTD value(sales)* APRIL YTD value(sales rate)) + (APRIL value(sales)* APRIL value(sales rate)) / (APRIL value(sales) + APRIL value(sales rate)

Similarly for other months.Dataframe is given below as an image.

import pandas as pd

data = {'Month': ['April', 'May', 'April', 'June', 'April', 'May'],
    'Year': [2022, 2022, 2022, 2022, 2022, 2022],
    'Financial_Year': [2023, 2023, 2023, 2023, 2023, 2023],
    'Measurement': ['sales', 'sales', 'sales', 'sales', 'sales rate', 'sales rate'],
    'Place': ['Delhi', 'Delhi', 'Delhi', 'Delhi', 'Delhi', 'Delhi'],
    'Market': ['Domestic', 'Domestic', 'Export', 'Domestic', 'Domestic', 'Domestic'],
    'Product': ['Biscuit', 'Biscuit', 'Chocolate', 'Biscuit', 'Biscuit', 'Biscuit'],
    'Value': ['10', '10', '20', '25', '10', '20']}

# Create DataFrame
df = pd.DataFrame(data)
df['Value'] = df['Value'].astype(float)
df['ytd_value'] = df.groupby(['Financial_Year', 'Measurement', 'Place', 'Market', 'Product'], sort=False)['Value'].cumsum()

It will calculate ytd_value for both sales and sales rate measurement.But I want to calculate ytd_value for sales rate in the above mentioned format.

I have tried below code, but it shows an error:

rslt_df = df[(df['Measurement'] == 'sales')]
df.loc[df['Measurement'] == "sales rate", 'ytd_value'] = (df.groupby(['Financial_Year', 'Measurement', 'Place', 'Market', 'Product'], sort=False)['ytd_value']*rslt_df.groupby(['Financial_Year', 'Measurement', 'Place', 'Market', 'Product'], sort=False)['ytd_value'] + df.groupby(['Financial_Year', 'Measurement', 'Place', 'Market', 'Product'], sort=False)['Value'] * rslt_df.groupby(['Financial_Year', 'Measurement', 'Place', 'Market', 'Product'], sort=False)['Value']) / (rslt_df.groupby(['Financial_Year', 'Measurement', 'Place', 'Market', 'Product'], sort=False)['ytd_value'] + rslt_df.groupby(['Financial_Year', 'Measurement', 'Place', 'Market', 'Product'], sort=False)['Value'])

Expected output:

Month  Year  Financial_Year  ...    Product Value ytd_value
0  April  2022            2023  ...    Biscuit  10.0      10.0
1    May  2022            2023  ...    Biscuit  10.0      20.0
2  April  2022            2023  ...  Chocolate  20.0      20.0
3   June  2022            2023  ...    Biscuit  25.0      45.0
4  April  2022            2023  ...    Biscuit  10.0      10.0
5    May  2022            2023  ...    Biscuit  20.0      10.0

Can anyone help me to solve this caclculation?

DraftyHat · Answer

I recommend you change your dataframe around a bit:

   Month  Year  Financial_Year  Place    Market  Product  Sales  Sales Rate
0  April  2022            2023  Delhi  Domestic  Biscuit   10.0        10.0
1    May  2022            2023  Delhi  Domestic  Biscuit   10.0        20.0
2   June  2022            2023  Delhi  Domestic  Biscuit   25.0         0.0

You may be able to get here by aggregating the sales values across each month, but the point is that you have a single Sales value and Sales Rate value for each month.

Once you have this, you can set the YTD value for April, and then iterate through the following months to calculate their values.

I think there's an error in the formula you posted for YTD calculations, but using that as is, here's some sample code:

import pandas as pd

data = {'Month': ['April', 'May', 'June'],
    'Year': [2022, 2022, 2022],
    'Financial_Year': [2023, 2023, 2023],
    'Place': ['Delhi', 'Delhi', 'Delhi'],
    'Market': ['Domestic', 'Domestic', 'Domestic'],
    'Product': ['Biscuit', 'Biscuit', 'Biscuit'],
    'Sales': [10, 10, 25],
    'Sales Rate': [10, 20, 0]}

# Create DataFrame
df = pd.DataFrame(data)
df['Sales'] = df['Sales'].astype(float)
df['Sales Rate'] = df['Sales Rate'].astype(float)

df['YTD'] = 0.0
df.at[0,'YTD'] = df.iloc[0]['Sales']
for rowidx in range(1, len(df)):
    prevrow = df.iloc[rowidx - 1]
    tmp = prevrow['Sales'] * prevrow['Sales Rate']
    df.at[rowidx,'YTD'] = tmp + tmp/tmp

print(df)

This outputs, for example:

   Month  Year  Financial_Year  Place    Market  Product  Sales  Sales Rate    YTD
0  April  2022            2023  Delhi  Domestic  Biscuit   10.0        10.0   10.0
1    May  2022            2023  Delhi  Domestic  Biscuit   10.0        20.0  101.0
2   June  2022            2023  Delhi  Domestic  Biscuit   25.0         0.0  201.0

You should be able to use this as an example to implement the correct function to calculate the YTD values.

How to calculate YTD (Year to Date) value using Pandas Dataframe?

1 Answers