# Load in csv
import pandas as pd
mortgage_sample = pd.read_csv("mortgage-testing-data.csv")
mortgage_sample.head()
# Add Columns
mortgage_sample['conforming'] = mortgage_sample['conforming_loan_limit']
mortgage_sample['nonconforming'] = mortgage_sample['conforming_loan_limit']
mortgage_sample['conforming']= mortgage_sample['conforming'].map({'C':True, 'NC':False}).astype(int)
mortgage_sample['nonconforming'] = mortgage_sample['nonconforming'].map({'C':False, 'NC':True}).astype(int)
# Overview of cleaned columns
mortgage_sample
# Add Columns
mortgage_sample['derived_sex_male'] = mortgage_sample['derived_sex']
mortgage_sample['derived_sex_female'] = mortgage_sample['derived_sex']
mortgage_sample['derived_sex_male']= mortgage_sample['derived_sex_male'].map({'Male':True, 'Female':False, 'Sex Not Available':False, 'Joint': False}).astype(int)
mortgage_sample['derived_sex_female'] = mortgage_sample['derived_sex_female'].map({ 'Male':False, 'Female':True, 'Sex Not Available':False, 'Joint': False}).astype(int)
# Overview of cleaned columns
mortgage_sample
# Change Values
mortgage_sample['action_taken'] = mortgage_sample['action_taken'].replace(3, 0)
# Overview of data
mortgage_sample
# Change values
mortgage_sample["debt_to_income_ratio"] = mortgage_sample["debt_to_income_ratio"].replace('20%-<30%', '25')
mortgage_sample["debt_to_income_ratio"] = mortgage_sample["debt_to_income_ratio"].replace('50%-60%', '55')
mortgage_sample["debt_to_income_ratio"] = mortgage_sample["debt_to_income_ratio"].replace('30%-<36%', '33')
mortgage_sample["debt_to_income_ratio"] = mortgage_sample["debt_to_income_ratio"].replace('<20%', '15')
mortgage_sample["debt_to_income_ratio"] = mortgage_sample["debt_to_income_ratio"].replace('>60%', '70')
# Change data type
mortgage_sample['debt_to_income_ratio'] = mortgage_sample['debt_to_income_ratio'].astype(float)
# Overview of data
mortgage_sample
mortgage_sample = mortgage_sample[['action_taken', 'loan_amount', 'loan_term', 'property_value', 'income', 'debt_to_income_ratio', 'conforming', 'nonconforming', 'derived_sex_male', 'derived_sex_female']]
mortgage_sample
mortgage_sample.to_csv("mortgage_sample.csv")