<jupyter_start><jupyter_text>Explore Lending Club data<jupyter_code>!ls *.csv
!ls *.csv | wc -l
import pandas as pd
pd.options.display.max_columns = 200
pd.options.display.max_rows = 200
browse = pd.read_csv('primaryMarketNotes_browseNotes_1-RETAIL.csv')
browse.shape
browse.head()
!ls *.xlsx
dictionary1 = pd.read_excel('LCDataDictionary.xlsx', sheet_name=0).set_index('LoanStatNew')
dictionary2 = pd.read_excel('LCDataDictionary.xlsx', sheet_name=1).set_index('BrowseNotesFile')
dictionary2.loc['expDefaultRate']
browse.sort_values(by='exp_default_rate').head()
# What if found 40 loans like id 152966674, and invest $25 in each ?
# We'd expect ~1 to default and 39 to be fully paid
1 * -25 + 39 * 0.0756 * 25
browse.sort_values(by='int_rate', ascending=False).head()
browse['worst'] = -25
browse['best'] = (25  # original investment returned, plus interest...
                  + 25 * browse['term']/12 # for a given number of years ...
                  * (browse['int_rate'] - browse['service_fee_rate']) / 100)
columns = ['term', 'int_rate', 'exp_default_rate', 'service_fee_rate', 
           'best', 'worst']
browse[columns].sort_values(by='best', ascending=False)
picks = browse.sample(40)
import numpy as np
# Simulate weighted coin flips, whether the loan will default,
# based on Lending Club's expected default rate
picks['default'] = picks['exp_default_rate'] > np.random.rand(len(picks)) * 100
picks['default'].value_counts()
picks['paid'] = picks['best']
picks.loc[picks['default'], 'paid'] = picks['worst']
columns += ['default', 'paid']
picks['paid'].sum()
from tqdm import tnrange, tqdm_notebook
from time import sleep

simulations = []
for _ in tnrange(10000):
    picks['default'] = picks['exp_default_rate'] > np.random.rand(len(picks)) * 100
    picks['paid'] = picks['best']
    picks.loc[picks['default'], 'paid'] = picks['worst']
    paid = picks['paid'].sum()
    simulations.append(paid)
pd.Series(simulations).describe()
%matplotlib inline
import seaborn as sns
sns.distplot(simulations);<jupyter_output><empty_output><jupyter_text>Historical Data!<jupyter_code>loans2019q1 = pd.read_csv('LoanStats_securev1_2019Q1.csv', 
                          skiprows=1, skipfooter=2, engine='python')
loans2019q1.shape
loans2019q1.head()
dictionary1
loans2019q1['loan_status'].value_counts()
loans2019q1['grade'].value_counts()
def a_to_d_done(df):
    a_to_d = df['grade'].isin(['A', 'B', 'C', 'D'])
    done = df['loan_status'].isin(['Fully Paid', 'Charged Off'])
    return a_to_d & done

df = loans2019q1.where(a_to_d_done).dropna(subset=['grade', 'loan_status'])
df['grade'].value_counts(dropna=False)
df['loan_status'].value_counts(dropna=False)
from glob import glob
type(glob)
files = glob('LoanStats*.csv')

dfs = (pd.read_csv(file, skiprows=1, skipfooter=2, engine='python')
       .where(a_to_d_done)
       .dropna(subset=['grade', 'loan_status'])
       for file in tqdm_notebook(files))

df = pd.concat(dfs)
df.shape
df.info()
df['loan_status'].value_counts(normalize=True)
from sklearn.model_selection import train_test_split

subset, _ = train_test_split(df.copy(), train_size=0.10, 
                             stratify=df['loan_status'])
subset['loan_status'].value_counts(normalize=True)
subset2 = df.sample(frac=0.10)
subset2['loan_status'].value_counts(normalize=True)
del subset2
subset.info()
subset.to_csv('lending-club-subset.csv', index=False)
common_columns = set(subset.columns) & set(browse.columns)
len(common_columns)
len(subset.columns)
len(browse.columns)
set(subset.columns) - set(browse.columns)
common_columns
subset['percent_paid'] = subset['total_pymnt'] / subset['funded_amnt']
subset.groupby('loan_status')['percent_paid'].mean()
subset['paid'] = 25 * subset['percent_paid']
subset['paid'].describe()
sns.distplot(subset['paid']);
simulations = []
for _ in tnrange(10000):
    picks = subset.sample(40).copy()
    paid = picks['paid'].sum()
    simulations.append(paid)
pd.Series(simulations).describe()
%matplotlib inline
import seaborn as sns
ax = sns.distplot(simulations)
ax.axvline(x=1000);<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1605875152998776, 'fraction_numerical': 0.031089351285189717, 'mean_word_length': 5.64390243902439, 'pattern_counts': {'":': 0, '<': 9, '<?xml version=': 0, '>': 11, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '2', 'dir': 'jupyter-structured-clean-dedup', 'id': '440626', 'license': 'MIT', 'n_tokens_mistral': 1569, 'n_tokens_neox': 1442, 'n_words': 268, 'path': 'explore_lending_club.ipynb', 'repo_name': 'jtkernan7/DS-Unit-2-Classification-2', 'size': '4085'}