text_plain_output_1.png
import matplotlib.pyplot as plt

import sqlite3

import matplotlib.pyplot as plt
import numpy as np
import sqlite3
from sklearn import tree
def sql_query(s):
    """Return results for a SQL query.
    Arguments:
    s (str) -- SQL query string
    Returns:
    (list) -- SQL query results
    """
    conn = sqlite3.connect('../input/database.sqlite')
    c = conn.cursor()
    c.execute(s)
    result = c.fetchall()
    conn.close()
    return result
def print_details():
    """Print database details including table names and the number of rows.
    """
    table_names = sql_query('SELECT name FROM sqlite_master ' + "WHERE type='table' " + 'ORDER BY name;')[0][0]
    num_rows = sql_query('SELECT COUNT(*) FROM loan;')[0][0]
def print_column_names():
    """Print the column names in the 'loan' table.
    Note that the "index" column name is specific to Python and is not part of
    the original SQLite database.
    """
    conn = sqlite3.connect('../input/database.sqlite')
    conn.row_factory = sqlite3.Row
    c = conn.cursor()
    c.execute('SELECT * FROM loan LIMIT 2;')
    r = c.fetchone()
    i = 1
    for k in r.keys():
        i += 1
    conn.close()

emp_length_dict = {'n/a': 0, '< 1 year': 0, '1 year': 1, '2 years': 2, '3 years': 3, '4 years': 4, '5 years': 5, '6 years': 6, '7 years': 7, '8 years': 8, '9 years': 9, '10+ years': 10}
home_ownership_dict = {'MORTGAGE': 0, 'OWN': 1, 'RENT': 2, 'OTHER': 3, 'NONE': 4, 'ANY': 5}
features_dict = {'loan_amnt': 0, 'int_rate': 1, 'annual_inc': 2, 'delinq_2yrs': 3, 'open_acc': 4, 'dti': 5, 'emp_length': 6, 'funded_amnt': 7, 'tot_cur_bal': 8, 'home_ownership': 9}
def get_data(s):
    """Return features and targets for a specific search term.
    Arguments:
    s (str) -- string to search for in loan "title" field
    Returns:
    (list of lists) -- [list of feature tuples, list of targets]
         (features) -- [(sample1 features), (sample2 features),...]
           (target) -- [sample1 target, sample2 target,...]
    """
    data = sql_query('SELECT ' + 'loan_amnt,int_rate,annual_inc,' + 'loan_status,title,delinq_2yrs,' + 'open_acc,dti,emp_length,' + 'funded_amnt,tot_cur_bal,home_ownership ' + 'FROM loan ' + "WHERE application_type='INDIVIDUAL';")
    features_list = []
    target_list = []
    n = 0
    n0 = 0
    n1 = 0
    for d in data:
        test0 = isinstance(d[0], float)
        test1 = isinstance(d[1], str)
        test2 = isinstance(d[2], float)
        test3 = isinstance(d[3], str)
        test4 = isinstance(d[4], str)
        test5 = isinstance(d[5], float)
        test6 = isinstance(d[6], float)
        test7 = isinstance(d[7], float)
        test8 = isinstance(d[8], str)
        test9 = isinstance(d[9], float)
        test10 = isinstance(d[10], float)
        if test0 and test1 and test2 and test3 and test4 and test5 and test6 and test7 and test8 and test9 and test10:
            try:
                d1_float = float(d[1].replace('%', ''))
            except:
                continue
            try:
                e = emp_length_dict[d[8]]
            except:
                continue
            try:
                h = home_ownership_dict[d[11]]
            except:
                continue
            if s.lower() in d[4].lower():
                if d[3] == 'Fully Paid' or d[3] == 'Current':
                    target = 0
                    n += 1
                    n0 += 1
                elif 'Late' in d[3] or d[3] == 'Charged Off':
                    target = 1
                    n += 1
                    n1 += 1
                else:
                    continue
                features = (d[0], float(d[1].replace('%', '')), d[2], d[5], d[6], d[7], emp_length_dict[d[8]], d[9], d[10], home_ownership_dict[d[11]])
                features_list.append(features)
                target_list.append(target)
        else:
            pass
    result = [features_list, target_list]
    return result
def create_scatter_plot(x0_data, y0_data, x1_data, y1_data, pt, pa, x_label, y_label, axis_type):
    ax = plt.gca()
    ax.set_axis_bgcolor('#BBBBBB')
    ax.set_axisbelow(True)
    plt.axis(pa)
    plt.xticks(fontsize=16)
    plt.yticks(fontsize=16)
    if axis_type == 'semilogx':
        plt.semilogx(x0_data, y0_data, label='0: "Fully Paid" or "Current"', linestyle='None', marker='.', markersize=8, alpha=0.5, color='b')
        plt.semilogx(x1_data, y1_data, label='1: "Late" or "Charged Off"', linestyle='None', marker='.', markersize=8, alpha=0.5, color='r')
    elif axis_type == 'semilogy':
        plt.semilogy(x0_data, y0_data, label='0: "Fully Paid" or "Current"', linestyle='None', marker='.', markersize=8, alpha=0.5, color='b')
        plt.semilogy(x1_data, y1_data, label='1: "Late" or "Charged Off"', linestyle='None', marker='.', markersize=8, alpha=0.5, color='r')
    elif axis_type == 'loglog':
        plt.loglog(x0_data, y0_data, label='0: "Fully Paid" or "Current"', linestyle='None', marker='.', markersize=8, alpha=0.5, color='b')
        plt.loglog(x1_data, y1_data, label='1: "Late" or "Charged Off"', linestyle='None', marker='.', markersize=8, alpha=0.5, color='r')
    plt.clf()
def plot_two_fields(data, s, f1, f2, pa, x_label, y_label, axis_type):
    x0_list = []
    y0_list = []
    x1_list = []
    y1_list = []
    features_list = data[0]
    target_list = data[1]
    for i in range(len(features_list)):
        x = features_list[i][features_dict[f1]]
        y = features_list[i][features_dict[f2]]
        if target_list[i] == 0:
            x0_list.append(x)
            y0_list.append(y)
        elif target_list[i] == 1:
            x1_list.append(x)
            y1_list.append(y)
        else:
            pass

cc_data = get_data('credit card')

plot_two_fields(cc_data, 'credit card', 'loan_amnt', 'funded_amnt', [0.0, 35000.0, 0.0, 35000.0], 'loan amount', 'funded amount', 'standard')