Using product and average formula in python to create a loss rate table

Question

I am trying to create Loss rate table (see below) from the flow rate data frame I already have in python. Appreciate if someone can help me with the code. I have reproduced excel formulas below, but let me know if you need more information.

excel formula in cell B8 of loss rate table is =PRODUCT(B2:$H$2). This formula has been applied to the whole table, for example in cell C3 I have used =PRODUCT(C3:$H$3).

Finally, average in formula in row 13 is simple average of columns after calculating the loss rate. i.e. Formula in B13 is =AVERAGE(B8:B12)

thanks in advance for your help!

	Flow Rate
	A	B	C	D	E	F	G	H
1	Month	NYD	DPD30	DPD60	DPD90	DPD120	DPD150	Loss
2	Feb	0.40	0.70	0.63	0.50	0.90	0.30	1.00
3	Mar	0.74	0.63	0.57	0.72	0.80	0.56	1.00
4	Apr	0.67	0.73	0.76	0.63	0.83	0.67	1.00
5	May	0.57	0.67	0.69	0.66	0.64	0.60	1.00
6	June	0.50	0.67	0.64	0.72	0.72	0.94	1.00

	Loss rate

	A	B	C	D	E	F	G	H
7	Month	NYD	DPD30	DPD60	DPD90	DPD120	DPD150	Loss
8	Feb	0.02	0.06	0.08	0.14	0.27	0.30	1.00
9	Mar	0.08	0.11	0.18	0.32	0.44	0.56	1.00
10	Apr	0.13	0.19	0.26	0.35	0.56	0.67	1.00
11	May	0.07	0.12	0.17	0.25	0.38	0.60	1.00
12	June	0.10	0.21	0.31	0.49	0.68	0.94	1.00
13	Average	0.08	0.14	0.20	0.31	0.47	0.61	1.00

How is your data arranged in the dataframes? could you give the dataframe output you expect in and what you expect out? — Paul Brennan
i tried copying df here but i was unable to copy paste in a readable form. — Mohsin Jamil

n1colas.m n1colas.m · Accepted Answer · 2021-02-01T02:47:49

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

Using product and average formula in python to create a loss rate table

1 Answers