<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 Smart Underwriter!
Step1:  Underwrting
Step2:  Let's take a peek at the data.
Step3:  How many morgages have been prepaid in these three years?
Step4:  Remember prepay includes a common case that if you want to switch to another house. You will sell the current house that you live in. That would count as prepay too.
Default Rate?
Step5:  Default rate is not very high in these three years. The general trend is going down, which make senses because the economy is getting better.
Step6:  It is very interesting to find out that "default" loans tends to have slightly higher loan-to-value (LTV) ratio on average, meaning they tends to have lower downpayment.
Step7:  Again, it is very interesting to find that default loans tend to have slightly lower credit score, on average.
<ASSISTANT_TASK:>
Python Code:
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import matplotlib
import seaborn as sns
pd.options.display.max_columns = 999
%matplotlib inline
matplotlib.rcParams['savefig.dpi'] = 1.5 * matplotlib.rcParams['savefig.dpi']
Explanation: Smart Underwriter!
End of explanation
# Read the data inside:
loan2011 = pd.read_csv('merged_summary_2011.csv')
loan2010 = pd.read_csv('merged_summary_2010.csv')
loan2012 = pd.read_csv('merged_summary_2012.csv')
Explanation: Underwrting:
In insurance, underwriting is to sign and accept liability and guaranteeing payment in case loss or damage occurs.
Underwriting is provided by a large financial service provider such as a bank, insurer or investment house. 
From Wikipedia
Drawbacks
It was mainly done by human before. 
Subjective, concerns of equality.
Need for computer aid underwriting
Objective
Non bias (regulatory requirement!)
Fast
There are products outside already:
Desktop Underwriter From Fannie
Loan Prospector From Freddie
Goal:
Develop a prediction model to predict the risk of the morgage
Data:
Fannie Mae Single Family House Morgage data (2000 - 2014)
End of explanation
loan2011.head()
loan2011['Monthly.Rpt.Prd'] = pd.to_datetime(loan2011['Monthly.Rpt.Prd'], format = '%m/%d/%Y')
loan2011['ORIG_DTE'] = pd.to_datetime(loan2011['ORIG_DTE'], format = '%m/%Y')
loan = {'2011':loan2011, '2012': loan2012, '2010': loan2010}
years = []
prepaid = {}
default = {}
loan_num = {}
for key in loan.keys():
    temp_size =len(loan[key])
    temp_count = loan[key].groupby('Zero.Bal.Code')['LOAN_ID'].count()
    loan_num[key] = temp_size
    prepaid[key] = temp_count[1]/temp_size
    default[key] = 1 - sum(temp_count[0:2])/temp_size
Explanation: Let's take a peek at the data.
End of explanation
def plot_dict_bar(val_dict):
    x_axis = sorted(list(val_dict.keys()))
    y_axis = [val_dict[key] for key in x_axis]
    x = np.arange(3) - 0.175
    y = np.array(y_axis)*100
    fig, ax  = plt.subplots()
    ax.bar(x,y,0.35)
    ax.set_xticks(np.arange(3))
    ax.set_xticklabels(x_axis)
    ax.set_xlabel("Year")
    ax.set_ylabel("Percentage (%)")
    ax.set_title("Prepaid Ratio")
    plt.show()
    
plot_dict_bar(prepaid)
Explanation: How many morgages have been prepaid in these three years?
End of explanation
def plot_dict_bar(val_dict):
    x_axis = sorted(list(val_dict.keys()))
    y_axis = [val_dict[key] for key in x_axis]
    x = np.arange(3) - 0.175
    y = np.array(y_axis)*100
    fig, ax  = plt.subplots()
    ax.bar(x,y,0.35)
    ax.set_xticks(np.arange(3))
    ax.set_xticklabels(x_axis)
    ax.set_xlabel("Year")
    ax.set_ylabel("Percentage (%)")
    ax.set_title("Default Ratio")
    plt.show()
    
plot_dict_bar(default)
Explanation: Remember prepay includes a common case that if you want to switch to another house. You will sell the current house that you live in. That would count as prepay too.
Default Rate?
End of explanation
def plot_dict_bar(val_dict):
    x_axis = sorted(list(val_dict.keys()))
    y_axis = [val_dict[key] for key in x_axis]
    x = np.arange(3) - 0.175
    y = np.array(y_axis)
    fig, ax  = plt.subplots()
    ax.bar(x,y,0.35)
    ax.set_xticks(np.arange(3))
    ax.set_xticklabels(x_axis)
    ax.set_xlabel("Year")
    ax.set_ylabel("Percentage (%)")
    ax.set_title("Loan Initiation Amount")
    plt.show()
    
plot_dict_bar(loan_num)
years = []
loan_to_value = {}
for key in loan.keys():
    temp_ltv = {}
    temp_mean = loan[key].groupby('Zero.Bal.Code')['OLTV'].mean()
    temp_ltv['Current'] = temp_mean[0]
    temp_ltv['Prepaid'] = temp_mean[1]
    temp_ltv['Default'] = np.mean(temp_mean[2:])
    loan_to_value[key] = temp_ltv
result = pd.DataFrame.from_dict(loan_to_value)
result.head()
Explanation: Default rate is not very high in these three years. The general trend is going down, which make senses because the economy is getting better.
End of explanation
years = []
credit_score = {}
for key in loan.keys():
    temp_cscore = {}
    temp_mean = loan[key].groupby('Zero.Bal.Code')['CSCORE_C'].mean()
    temp_cscore['Current'] = temp_mean[0]
    temp_cscore['Prepaid'] = temp_mean[1]
    temp_cscore['Default'] = np.mean(temp_mean[2:])
    credit_score[key] = temp_cscore
result2 = pd.DataFrame.from_dict(credit_score)
result2.head()
Explanation: It is very interesting to find out that "default" loans tends to have slightly higher loan-to-value (LTV) ratio on average, meaning they tends to have lower downpayment.
End of explanation
# Last one, default by states...
years = []
state_res = {}
for key in loan.keys():
    temp_state_count = loan[key][(loan[key]['Zero.Bal.Code'] == 3) | (loan[key]['Zero.Bal.Code'] == 6) | (loan[key]['Zero.Bal.Code'] == 9)].groupby('STATE')['LOAN_ID'].count()
    states = list(temp_state_count.index)
    for state in states:
        if state_res.get(state) == None:
            state_res[state] = temp_state_count[state]
        else:
            state_res[state] = state_res[state] + temp_state_count[state]
    
total_state_res = pd.DataFrame.from_dict(state_res, orient='index')
total_state_res.columns=['Default']
total_state_res.sort_values(by='Default', ascending=False)
Explanation: Again, it is very interesting to find that default loans tend to have slightly lower credit score, on average.
End of explanation
<END_TASK>