You can use iloc
to get only the numeric portion of the data frame, then use the apply
function with cumprod
over the horizontal axis (axis=1
) in reverse order (x[::-1]
), evaluating the cumulative product over the data from right to left. This will produce the expected output described in the Loss rate table. To add the last row (average
), calculate the mean
and transform the resulting series into a transposed data frame. This data frame will then be concatenated to df_loss
to achieve the expected result. If you want, you can then create the new index
using the original month
names and add the Average
to it using pd.Index
.
import pandas as pd
d = {
'Month': {0: 'Feb ', 1: 'Mar ', 2: 'Apr ', 3: 'May ', 4: 'June '},
'NYD': {0: 0.4, 1: 0.74, 2: 0.67, 3: 0.57, 4: 0.5},
'DPD30': {0: 0.7, 1: 0.63, 2: 0.73, 3: 0.67, 4: 0.67},
'DPD60': {0: 0.63, 1: 0.57, 2: 0.76, 3: 0.69, 4: 0.64},
'DPD90': {0: 0.5, 1: 0.72, 2: 0.63, 3: 0.66, 4: 0.72},
'DPD120': {0: 0.9, 1: 0.8, 2: 0.83, 3: 0.64, 4: 0.72},
'DPD150': {0: 0.3, 1: 0.56, 2: 0.67, 3: 0.6, 4: 0.94},
'Loss': {0: 1.0, 1: 1.0, 2: 1.0, 3: 1.0, 4: 1.0}}
df = pd.DataFrame(d)
print(df)
df_loss = df.iloc[:, 1:].apply(lambda x: x[::-1].cumprod(), axis=1)
df_loss = df_loss[df.columns[1:]] # fix presentation order
dmean = pd.DataFrame(df_loss.mean()).T
df_loss = pd.concat([df_loss, dmean])
df_loss.index = pd.Index([*df.Month, "Average"], name="Month")
print(df_loss.round(3))
Output from df_loss
NYD DPD30 DPD60 DPD90 DPD120 DPD150 Loss
Month
Feb 0.024 0.060 0.085 0.135 0.270 0.300 1.0
Mar 0.086 0.116 0.184 0.323 0.448 0.560 1.0
Apr 0.130 0.194 0.266 0.350 0.556 0.670 1.0
May 0.067 0.117 0.175 0.253 0.384 0.600 1.0
June 0.104 0.209 0.312 0.487 0.677 0.940 1.0
Average 0.082 0.139 0.204 0.310 0.467 0.614 1.0