%matplotlib inline
import pandas as pd
import mpld3
mpld3.enable_notebook()
%matplotlib inline
import matplotlib
import seaborn as sns
matplotlib.rcParams['savefig.dpi'] = 2 * matplotlib.rcParams['savefig.dpi']

import pandas as pd

Maps = pd.read_csv('./zip_code_database.csv')

import dill
with open('../LendingClubPrediction/CleanedUpData.pkl','rb') as in_strm:
    df = dill.load(in_strm)


df['int_rate'] = [float(df.int_rate.iloc[i][:-1]) for i in xrange(len(df.int_rate))]

county = pd.read_csv('../zcta_county_rel_10.txt')

valide_zip= pd.unique(df.zip_code)

zip_number =[int(i.replace('xx','')) for i in valide_zip]

a=(Maps.zip/100).floordiv(1)

zip_indata = pd.DataFrame({'zip_3':zip_number})

Maps['zip_3'] = a

after_merge=Maps.merge(zip_indata,how='inner',on='zip_3')

after_merge.to_csv('trial_1.csv')

pd.unique(after_merge.zip).shape

county_zip = after_merge.merge(county,how='inner',left_on='zip',right_on='ZCTA5')

county_zip.iloc[:500].to_csv('county_1.csv')

county_zip.columns

from bokeh.sampledata import us_states, us_counties, unemployment
from bokeh.plotting import figure, show, output_file, output_notebook


us_states = us_states.data.copy()
us_counties = us_counties.data.copy()
unemployment = unemployment.data

del us_states["HI"]
del us_states["AK"]

state_xs = [us_states[code]["lons"] for code in us_states]
state_ys = [us_states[code]["lats"] for code in us_states]

county_xs=[us_counties[code]["lons"] for code in us_counties if us_counties[code]["state"] not in ["ak", "hi", "pr", "gu", "vi", "mp", "as"]]
county_ys=[us_counties[code]["lats"] for code in us_counties if us_counties[code]["state"] not in ["ak", "hi", "pr", "gu", "vi", "mp", "as"]]

colors = ["#F1EEF6", "#D4B9DA", "#C994C7", "#DF65B0", "#DD1C77", "#980043"]

county_colors = []
for county_id in us_counties:
    if us_counties[county_id]["state"] in ["ak", "hi", "pr", "gu", "vi", "mp", "as"]:
        continue
    try:
        rate = unemployment[county_id]
        idx = min(int(rate/2), 5)
        county_colors.append(colors[idx])
    except KeyError:
        county_colors.append("black")



len(county_xs[40])

"""
Explanation: use the zipcode
End of explanation
"""


import simplejson
from requests_oauthlib import OAuth1
import requests
with open("./lendingclub_secrets.json.nogit") as fh:
    secrets = simplejson.loads(fh.read())

# create an auth object
auth = OAuth1(
    secrets["api_key"],
#     secrets["api_secret"],
#     secrets["access_token"],
#     secrets["access_token_secret"]
)




# Query Parameters:
# showAll — A non-required Boolean parameter that defines the contents of the result.
# showAll= False

params={'showAll' : True}
r = requests.get("https://api.lendingclub.com/api/investor/v1/loans/listing", headers={'Authorization':'5Rtb7dWC4Wps2C3VRAn2hBQVERg='},params=params)
# r = requests.get("https://api.lendingclub.com/api/64233077/v1/loans/listing", params=params)

# 
print r

loanlist=r.json()
print len(loanlist['loans'])

import dill
with open('loanlist_3.pkl','wb') as out_strm:
    dill.dump(loanlist,out_strm)

loanlist['loans'][0]

len(loanlist['loans'])

ids = []
for i in range(348):
    ids.append(loanlist['loans'][i]['memberId'])

# output_file("choropleth.html", title="choropleth.py example")
# output_notebook()
# TOOLS = ("hover,save")
# p = figure(title="US Unemployment 2009", toolbar_location="left",tools =TOOLS,
#     plot_width=1100, plot_height=700)

# p.patches(county_xs, county_ys, fill_color=county_colors, fill_alpha=0.7,
#     line_color="white", line_width=0.5)
# p.patches(state_xs, state_ys, fill_alpha=0.0,
#     line_color="#884444", line_width=2)

# show(p)

import dill
with open('loanlist_2.pkl','rb') as in_strm:
    loanlist_2 = dill.load(in_strm)
with open('loanlist.pkl','rb') as in_strm:
    loanlist_1 = dill.load(in_strm)

ids_1 = [loanlist_1['loans'][i]['id'] for i in range(348)]
ids_2 = [loanlist_2['loans'][i]['id'] for i in range(len(loanlist_2['loans']))]


missing =[]
for i in ids_1:
    if i not in ids_2:
        missing.append(i)

len(missing)

loanlist_1['loans'][1]

df.columns

"""
Explanation: LET US JUST DO STATES
PLANS:
1. include a page of exploring different features
    1.1 Need to get the map one working. 
        1.1.1 give options about further filtering based on FICO credit score, and LC  (MAYBE, depends on whether I have time or not.)
    1.2 Need to bar graphs about FICO score, home owner, other features too. So the KEY is make it PRETTY

2. the prediction app
    2.1 Need to explain why use the ensemble model, Simple answer is that it works better. LOL. Boosting decision tree seems to work better, though logistic regression seems to be enough. 
    2.2 Another issue is that if I am going to deploy to Heroku, probably I can only use logistic regression. 
    2.3 Engineer some other features?? Like the difference between low and high score. Maybe some nlp stuff, not sure this will work. Or the employment rate of the states? Libral vs Conservative? This sorts of thing. I should just factor it in use a mean model.

3. maybe a profolio, and a trainning program
    3.1 This is an afterthought. 
    3.2 involves with getting the API working

some API stuff
End of explanation
"""


df['paidoff'] = (df.stat== 1)

bystate = pd.DataFrame()
bystate['mean_rate'] = df.groupby([u'addr_state']).mean().paidoff
bystate['default_rate'] = 1- df.groupby([u'addr_state']).mean().paidoff

bystate['count'] = df.groupby([u'addr_state']).count().paidoff
bystate['fico_range_high'] = df.groupby([u'addr_state']).mean().fico_range_high
bystate['fico_range_high'] = df.groupby([u'addr_state']).mean().fico_range_high


bystate.to_csv('bystate.csv')

df[df.addr_state=='IA']

from bokeh.plotting import figure, show, output_file


"""
Explanation: Build the data used for the figure
End of explanation
"""


from bokeh.models import HoverTool, ColumnDataSource
from collections import OrderedDict


# bygrade = pd.DataFrame()
# bygrade['mean_rate'] = df.groupby([u'sub_grade']).mean().paidoff
# bygrade['default_rate'] = 1-df.groupby([u'sub_grade']).mean().paidoff
# bygrade['int_rate'] = df.groupby([u'sub_grade']).mean().int_rate

# bygrade['counts'] = df.groupby([u'sub_grade']).count().paidoff

bygrade = pd.DataFrame()
bygrade['mean_rate'] = df.groupby([u'grade']).mean().paidoff
bygrade['default_rate'] = 1-df.groupby([u'grade']).mean().paidoff
bygrade['int_rate'] = df.groupby([u'grade']).mean().int_rate

bygrade['counts'] = df.groupby([u'grade']).count().paidoff

xgrades = [i for i in bygrade.index]
pay_grades = bygrade.mean_rate.values*100
dft_grades = bygrade.default_rate.values*100

# xx = df.groupby([u'sub_grade']).mean()

# TOOLS = "hover,save"

# p = figure(background_fill="#EFE8E2", 
#            x_range=xgrades,
#            x_axis_label='LC grade', y_axis_label=('Pay off rate (%)'),
#            y_range = [0, 100],
#            title="Loan Outcome by LendingClub Grade",
#            tools = TOOLS,
#            plot_width=800, 
#            plot_height=400)

# source1 = ColumnDataSource(
#     data=dict(pay_grades=pay_grades, dft_grades=dft_grades,int_rate=bygrade['int_rate'].values)
# )
# source2 = ColumnDataSource(
#     data=dict(pay_grades=pay_grades, dft_grades=dft_grades,int_rate=bygrade['int_rate'].values)
# )

# p.rect(xgrades, pay_grades/2,  0.6, pay_grades,
#     fill_color="#08c994", source = source1)
# p.rect(xgrades, dft_grades/2 + pay_grades,  0.6,dft_grades,
#     fill_color="#ff5a00", source = source2)


# hover = p.select(dict(type=HoverTool))
# hover.tooltips = OrderedDict([
#     ('Grade', "$x"),
#     ('Payoff rate (%)', '@pay_grades'),
#     ('Default rate (%)', '@dft_grades'),
#     ('Interest (%)','@int_rate'),
# ])

# show(p)

"""
Explanation: Loan by grade
End of explanation
"""


byfico = pd.DataFrame()
byfico['mean_rate'] = df.groupby([u'fico_range_high']).mean().paidoff.iloc[2:]
byfico['default_rate'] = 1- df.groupby([u'fico_range_high']).mean().paidoff[2:]
byfico['count'] = df.groupby([u'fico_range_high']).count().paidoff[2:]

# TOOLS = "hover,save"
# xfico = [(str(int(byfico.index[i]-4)) + ' - ' +str(int(byfico.index[i]))) for i in xrange(len(byfico.index))]

# p = figure(background_fill="#EFE8E2", 
#            x_axis_label='Pay off rate (%)', y_axis_label='FICO score',
#            x_range = [0, 100] , 
#            y_range = xfico[::-1],
#            title="Loan Outcome by FICO score",
#            tools = TOOLS,
#            plot_width=800, 
#            plot_height=600)

# source1 = ColumnDataSource(
#     data=dict(fico_score=xfico, dft_rate=byfico['default_rate'].iloc[::-1]*100,payoff_rate=byfico['mean_rate'].iloc[::-1]*100)
# )
# source2 = ColumnDataSource(
#     data=dict(fico_score=xfico, dft_rate=byfico['default_rate'].iloc[::-1]*100,payoff_rate=byfico['mean_rate'].iloc[::-1]*100)
# )

# p.rect(byfico['mean_rate'].iloc[::-1]*100/2,  
#        xfico[::-1],byfico['mean_rate'].iloc[::-1]*100 ,0.8, 
#        fill_color="#08c994", source = source1)

# p.rect(byfico['default_rate'].iloc[::-1]*100/2 + byfico['mean_rate'].iloc[::-1]*100, 
#        xfico[::-1], byfico['default_rate'].iloc[::-1]*100, 0.8,
#        fill_color="#ff5a00", source = source2)

# hover = p.select(dict(type=HoverTool))
# hover.tooltips = OrderedDict([
#     ('FICO score', "$y"),
#     ('Payoff rate (%)', '@payoff_rate'),
#     ('Default rate (%)', '@dft_rate'),

# ])

# show(p)

df.columns

df['dti_bin'] = np.floor(df.dti/5)*5

(pd.unique(df.emp_length))

# N = 100
# x = np.random.random(size=N) * 100
# y = np.random.random(size=N) * 100
# radii = np.random.random(size=N) * 1.5
# colors = ["#%02x%02x%02x" % (r, g, 150) for r, g in zip(np.floor(50+2*x), np.floor(30+2*y))]
# colors

"""
Explanation: Loan by credit score
End of explanation
"""


# data =  df.groupby(['home_ownership','emp_length']).mean().paidoff

# from collections import OrderedDict

# import numpy as np

# from bokeh.plotting import ColumnDataSource, figure, show, output_file
# from bokeh.models import HoverTool

# # Read in the data with pandas. Convert the year column to string
# home_ownership = ['RENT', 'OWN', 'MORTGAGE']
# emp_lenght = [ 'n/a', '< 1 year', '1 year', '3 years', '2 years', '4 years', '5 years',
#         '6 years', '7 years', '8 years', '9 years','10+ years']

# # colors = [
# #     "#08C994", "#26BB81", "#45AD6F", "#649F5C", "#83914A",
# #     "#A28337", "#C17525", "#E06712", "#FF5A00"
# # ]
# colors = [
#     "#75968f", "#a5bab7", "#c9d9d3", "#e2e2e2", "#dfccce",
#     "#ddb7b1", "#cc7878", "#933b41", "#550b1d"
# ]
# a=sorted(data.values)[::7]

# home = []
# emp = []
# color = []
# rate = []
# for y in emp_lenght:
#     for m in home_ownership:
#         home.append(m)
#         emp.append(y)
#         rate_by_home_emp = data[m][y]
#         rate.append(rate_by_home_emp*100)
#         for i in xrange(1,9):
#             if rate_by_home_emp > a[i-1] and rate_by_home_emp<= a[i]:
#                 ci = 9-i
# #         ci = int((rate_by_home_emp - min(data))/(max(data)-min(data))*8.9)
        
#         color.append(colors[ci])

# output_notebook()

# TOOLS = "hover,save"

# p = figure(
#     x_axis_label='Employment length (%)', y_axis_label='House Ownership',
#     y_range=home_ownership, x_range=emp_lenght ,
#     x_axis_location="above", plot_width=800, plot_height=400,
#     toolbar_location="left", tools=TOOLS)

# source = ColumnDataSource(
#     data=dict(home=home, emp=emp,color=color, rate=rate)
# )
# p.rect("emp", "home", 1, 1, source=source,  color="color",line_color=None)

# p.grid.grid_line_color = None
# p.axis.axis_line_color = None
# p.axis.major_tick_line_color = None
# p.axis.major_label_text_font_size = "12pt"
# p.axis.major_label_standoff = 0
# p.xaxis.major_label_orientation = np.pi/3

# hover = p.select(dict(type=HoverTool))
# hover.tooltips = OrderedDict([
#     ('Pay off rate (%)', '@rate'),
# ])

# show(p)      # show the plot

max(np.random.random(size=1000) * 100)


354/4/8

"""
Explanation: Payoff rate by home ownership and emp_length
End of explanation
"""


