Calculating Cum Product / Sum but add in variable factor at each step

Question

I have a dataframe that look as such

df = pd.DataFrame({'Group':["A","A","A","A","B","B","B","B","C","C","C","C"],
                   'Month' :[1,2,3,4,1,2,3,4,1,2,3,4],
                   'Interest_Rate':[.01,.02,.03,.03,.01,.02,.03,.04,.01,.02,.02,.03],
                   'Value': [1,1,2,3,2,3,4,5,2,3,4,5]})

I want to calculate a cumulative product / sum described as follows. I want to group by the Group and stop the cumulative sum when the group ends. AKA sum from months 1 to 4 in this case. I want to convert the interest rate to a discount factor which is contingent on the time remaining (aka Row number minus index of subgroup)

For instance the first value of the resulting column should be:

1 * (1-.01)**(1) + 1 * (1-.02)**2 +   2 * (1-.03)**3 + 4 * (1-.03)**4

The second value should be

1 * (1-.02)**1 +   2 * (1-.03)**2 + 4 * (1-.03)**-3

the third

2 * (1-.03)^-1 + 4 * (1-.03)^-2

the fourth

4 * (1-.03)^-1

This would then reset for group B.

I know how to do this without the discounting factor I want to add. It would be:

df.groupby('Group').cumsum()

I am a bit lost with what to do next. I know I can brute force this by creating 4 discount rate vectors (but the problem is my actual data has 840 months so this is not ideal).

Answer 1

I know how to do this for one case and I could not figure out how to do this for the multi case using any of the pandas summation packages. So I just created a loop.

Keys = ["Val_Month","Group"]
Totals = ["Value_Discounted"]
     for month in range(len(data["Month"].unique())):
         df_temp= df[df["Month"]> month].copy(deep=True)
         df_temp["Val_Month"] = month+1
         df_temp["Discount_Factor"] = (1+df_temp["Interest_Rate"])**(-(df_temp["RowNo"]-(1+month))/12)
         df_temp[ "Value_Discounted"]  =df_temp["Value"] * df_temp["Discount_Factor"] 
         if month ==0:
             df_sum_temp = df_temp[Totals+Keys].groupby(Keys).sum()
         else: 
             df_sum_temp = pd.concat([df_sum_temp,df_temp[Totals+Keys].groupby(Keys).sum()])