Pandas Pivot table - How compute the following default ratio?

Question

I am able to compute the default rate in number (e.g, the percentage of customers falled into default), with the code below, getting the following output:

import numpy as np
import pandas as pd
df = {
    'ID': [1, 2, 3, 4, 5, 6, 7],
    'Default': [1,1,0,1,0,1,0],
    'Default_Amount': [1200,2000,0,350,0,760,0],
    'Tot_Amount': [1200,2000,3400,350,10000,760,7500],
    'Time' : ['November','November','November','November','November','December','December'],
    'Class': ['A','B','A','A','B','B','A']
}
df = pd.DataFrame(df)
display(df)
df.to_excel("output.xlsx")  


print(f'Default rate in number: {df.Default.mean()}')
default_number = df.Default.mean()

pivot = np.round(pd.pivot_table(df, values=['Default'], 
                                index=['Class'], 
                                columns=['Time'], 
                                aggfunc=[np.mean],
                                fill_value=0, 
                                margins=True, 
                                margins_name='Total')
,4)
display(pivot)

Now, I am facing same problems to compute the default rate in amount (€) in a pivot table. To do it overall, I use the following in Python:

print(f'Default rate in amount: {df.Default_Amount.sum()/df.Tot_Amount.sum()}')

How can I get this table (computed with excel), considering the default rate in amount?

Answer 1

default_amount = pd.pivot_table(df, values='Default_Amount', 
                                index=['Class'], 
                                columns=['Time'], 
                                aggfunc=np.sum,
                                fill_value=0, 
                                margins=True, 
                                margins_name='Total')

total_amount = pd.pivot_table(df, values='Tot_Amount', 
                              index=['Class'], 
                              columns=['Time'], 
                              aggfunc=np.sum,
                              fill_value=0, 
                              margins=True, 
                              margins_name='Total')

default_amount_rate = default_amount/total_amount

calculate both the total default in amount and total amount dfs and make ratios between them

Output:

Time    December    November    Total
Class           
A   0.00000 0.313131    0.124498
B   1.00000 0.166667    0.216301
Total   0.09201 0.209440    0.170964