%load_ext sql

%sql postgresql://dsi_student:gastudents@dsi.c20gkj5cvu3l.us-east-1.rds.amazonaws.com/titanic

import pandas as pd
import numpy as np
%matplotlib inline
import matplotlib.pyplot as plt

%%sql

select * from train limit 5;

titanic_train = %%sql select * from train;
titanic_df = titanic_train.DataFrame()

titanic_df.head()

# titanic_df[0:50]
titanic_df[titanic_df['Cabin'] == 'G6']

titanic_df[titanic_df['Ticket'] == 'PP 9549']

#titanic_df[titanic_df['Cabin'] == re.compile]
titanic_df[titanic_df['Name'].str.contains("Sandstrom")]

titanic_df['Name'][394]

test = pd.read_csv('test.csv')

test.shape

test[test['Name'].str.contains("Sandstrom")]

dropped = ['index', 'PassengerId']
df = titanic_df.drop(dropped, 1)

df.head()

df.Age.isnull().value_counts()

# will need to find a way to impute age, else will use median

df.Embarked.isnull().value_counts()

# since only two embarked values are missing will fill with majority class

df.Embarked.value_counts()

# Check two missing embarked indexes
df[df.Embarked.isnull() == True]

# Let's see if the ticket numbers can give us a clue, 
# Search for similar ticket numbers with fuzzy wuzzy
from fuzzywuzzy import fuzz
from fuzzywuzzy import process

query = '113572'
choices = df.Ticket

process.extract(query, choices)

df[df['Ticket'] == '113792']

# df[df['Ticket'].str.contains('113')]

df['Embarked'] = df['Embarked'].fillna('S')

df['Embarked'].isnull().value_counts()

# Check for missing values in Cabin
df.Cabin.isnull().value_counts()

# We will not really be able to impute Missing cabins and some passengeres were not assigned a cabin 
# so will replace with value for missing

# Create a family size feature
df['Fsize'] = df['SibSp'] + df['Parch'] + 1

# Pull Titles from Name
import re
def titles(string):
    titles = re.search(' ([A-Za-z]+)\.', string)
    # If the title exists, extract and return it.
    if titles:
        return titles.group(1)
    return ""
# Create a new feature Title, containing the titles of passenger names
df['title'] = df['Name'].apply(titles)

df.title.value_counts()

df['title'][df['title'] == 'Mlle'] = 'Miss'

df['title'][df['title'] == 'Countess'] = 'Lady'

df['title'][df['title'] == 'Mme'] = 'Ms'

male_hon = ['Major', 'Col','Jonkheer','Don','Capt']
for i in male_hon:
    df['title'][df['title'] == i] = 'MHon'

df[df['title'] == 'Dr']

df.title.value_counts()

# Fill missing cabin values with 'X'
df['Cabin'] = df['Cabin'].fillna('X')

# Create a feature for Deck
# Pull deck from Cabin
def deck(string):
    decks = re.search('[A-Za-z]', string)
    # If the deck exists, extract and return it.
    if decks:
        return decks.group(0)
    return ""
# Create a new feature deck
df['deck'] = df['Cabin'].apply(deck)

df.head()

df['deck'][df['Pclass'] == 2].value_counts()

# Surname feature, will try using this feature in decision tree models
names = df['Name'].str.split(',')
surnames = []
for i in names:
    surnames.append(i[0])
df['surname'] = surnames

df.head()

df[["Sex", "Survived"]].groupby(['Sex'], as_index=False).mean()

df[["Fsize", "Survived"]].groupby(['Fsize'], as_index=False).mean()

df[df['Fsize'] == 11]

test[test['Name'].str.contains('Sage')]

# It appears the fare column may be fare per ticket and not per passenger
# Will try and address that later if time permits

df[["Parch", "Survived"]].groupby(['Parch'], as_index=False).mean()

df[["Pclass", "Survived"]].groupby(['Pclass'], as_index=False).mean()

df[["Fsize", "Survived"]].groupby(['Fsize'], as_index=False).mean()

df[df['Ticket'] == 'F.C.C. 13529']

# Impute missing ages

pd.pivot_table(df[df['Age'].isnull() == True], index=['Pclass', 'title'], values=['Age'])

# Fill missings ages with median for title and pclass
df['Age'].fillna(df.groupby(["title", "Pclass"])["Age"].transform("median"), inplace=True)

df['Age'].isnull().value_counts()

df['Age'][df['title'] == 'Dr'][df['Pclass'] == 1]

df['Age'][df['title'] == 'Dr'][df['Pclass'] == 1].median()

import seaborn as sns

# Create a list of quantitative data columns
quant = [f for f in df.columns if df.dtypes[f] != 'object']
quant

hists = ['Age', 'Fare']

# Plot Histograms of numerical data columns
sns.set(rc={"figure.figsize": (8, 4)})
for i in hists:
    sns.distplot(df[i])
    plt.xlabel(i)
    plt.ylabel('Count')
    plt.show()

df[df['Fare'] > 500]

plt.figure(figsize=(8,8))
plt.title('Correlation of Features', y=1.05, size=15)
sns.heatmap(df.corr(),linewidths=0.1,vmax=1.0, square=True, linecolor='white', annot=True)

g = sns.pairplot(df[['Survived', 'Pclass', 'Sex', 'Age', 'SibSp','Parch', 'Fare', 'Embarked', 'Fsize', 'title']], \
                 hue='Survived',size=1,diag_kind = 'kde',diag_kws=dict(shade=True),plot_kws=dict(s=10))
g.set(xticklabels=[])

# Standardize Age

from sklearn.preprocessing import StandardScaler

std_scale = StandardScaler()
age_df_std = std_scale.fit_transform(df[['Age']])

fig, ax = plt.subplots(1,2, figsize=(15,6))
sns.distplot(df['Age'], ax=ax[0], kde=False, color="steelblue", bins=30)
sns.distplot(age_df_std, ax=ax[1], kde=False, color="seagreen", bins=30)
ax[1].set_xlabel('Sklearn');

# Standardize Fare
std_scale = StandardScaler()
fare_df_std = std_scale.fit_transform(df[['Fare']])

fig, ax = plt.subplots(1,2, figsize=(15,6))
sns.distplot(df['Fare'], ax=ax[0], kde=False, color="steelblue", bins=30)
sns.distplot(fare_df_std, ax=ax[1], kde=False, color="seagreen", bins=30)
ax[1].set_xlabel('Sklearn');

df_lr = df.copy()

df.head()

lr_drop = ['Name', 'Ticket', 'Cabin', 'surname']

df_lr.drop(lr_drop, axis=1, inplace=True)
df_lr.head()

df_lr['Sex'] = df_lr['Sex'].apply(lambda x: 1 if x == 'male' else 0)

df_lr['Survived'][df_lr['Survived'] == 0].count()*1.0/(df_lr['Survived'][df_lr['Survived'] == 0].count()+\
                                                   df_lr['Survived'][df_lr['Survived'] == 1].count())*1.0

df_lr = pd.get_dummies(df_lr)
df_lr.columns

df_lr.drop(['Embarked_C','title_Mr','deck_X'], axis=1, inplace=True)
df_lr.head()

df_lr = pd.concat([df_lr.drop('Pclass',axis=1),pd.get_dummies(df_lr['Pclass'], prefix='Class',drop_first=True)], axis = 1)

df_lr = pd.concat([df_lr.drop('SibSp',axis=1),pd.get_dummies(df_lr['SibSp'], prefix='SibSp',drop_first=True)], axis = 1)

df_lr = pd.concat([df_lr.drop('Parch',axis=1),pd.get_dummies(df_lr['Parch'], prefix='Parch',drop_first=True)], axis = 1)

df_lr = pd.concat([df_lr.drop('Fsize',axis=1),pd.get_dummies(df_lr['Fsize'], prefix='Fsize',drop_first=True)], axis = 1)

df_lr.shape

df_lr['age_std'] = age_df_std

df_lr['fare_std'] = fare_df_std

df_lr.columns

df_lr.drop(['Age','Fare'], axis=1, inplace=True)

corr = df_lr.corr(method='pearson', min_periods=1).iloc[:,0]

corr = corr[np.argsort(corr, axis=0)[::-1]]
corr = pd.DataFrame(corr)

plt.figure(figsize=(6, 0.25*len(corr)))
sns.barplot(data=corr, y=corr.index, x=corr['Survived'], orient='h')

X_lr = df_lr.iloc[:,1:]
y_lr = df_lr['Survived']

X_lr.head()

# Split training and test
from sklearn.model_selection import train_test_split, cross_val_score

Xlr_train, Xlr_test, ylr_train, ylr_test = train_test_split(X_lr, y_lr, test_size=.30, random_state=42)

from sklearn.linear_model import LogisticRegression

# fit model
lr = LogisticRegression()
lr_model = lr.fit(Xlr_train, ylr_train)

# predictions
ylr_pred = lr_model.predict(Xlr_test)

from sklearn.metrics import accuracy_score, confusion_matrix, classification_report

acc = accuracy_score(ylr_test, ylr_pred)

acc

# conf matrix
lr_cm = confusion_matrix(ylr_test, ylr_pred)
lr_cm = pd.DataFrame(lr_cm, columns=["Survived-N", "Survived-Y"], index=["Survived-N", "Survived-Y"])
lr_cm

from sklearn.metrics import classification_report

print(classification_report(ylr_test, ylr_pred))

cv_lr = cross_val_score(lr, X_lr, y_lr, cv=3)
cv_lr

cv_lr.mean()

lr_model.get_params()

from sklearn.model_selection import GridSearchCV

lrcv = LogisticRegression(verbose=False)
Cs = [0.0001, 0.001, 0.01, 0.1, .15, .25, .275, .33, 0.5, .66, 0.75, 1.0, 2.5, 5.0, 10.0, 100.0, 1000.0]
penalties = ['l1','l2']

gs = GridSearchCV(lrcv, {'penalty': penalties, 'C': Cs},verbose=False, cv=15)
gs.fit(Xlr_train, ylr_train)

print gs.best_params_
print gs.best_score_

lr2 = LogisticRegression(penalty='l2', C=1.0)
lr2_model = lr2.fit(Xlr_train, ylr_train)

# predictions
ylr_pred = lr2_model.predict(Xlr_test)

acc = accuracy_score(ylr_test, ylr_pred)
acc

# conf matrix
lr_cm = confusion_matrix(ylr_test, ylr_pred)
lr_cm = pd.DataFrame(lr_cm, columns=["Survived_N", "Survived_Y"], index=["Survived_N", "Survived_Y"])
lr_cm

# lrcv_model.predict_proba(Xlr_test)

from sklearn.metrics import roc_curve, auc
import matplotlib.pyplot as plt
plt.style.use('seaborn-white')
%matplotlib inline

Y_score = lr2_model.decision_function(Xlr_test)

FPR = dict()
TPR = dict()
ROC_AUC = dict()

# For class 1, find the area under the curve
FPR[1], TPR[1], _ = roc_curve(ylr_test, Y_score)
ROC_AUC[1] = auc(FPR[1], TPR[1])

# Plot of a ROC curve for class 1 (has_cancer)
plt.figure(figsize=[6,5])
plt.plot(FPR[1], TPR[1], label='ROC curve (area = %0.2f)' % ROC_AUC[1], linewidth=4)
plt.plot([0, 1], [0, 1], 'k--', linewidth=4)
plt.xlim([0.0, 1.0])
plt.ylim([0.0, 1.05])
plt.xlabel('False Positive Rate', fontsize=18)
plt.ylabel('True Positive Rate', fontsize=18)
plt.title('ROC for predicting Survival', fontsize=18)
plt.legend(loc="lower right")
plt.show()

# Lets try to improve our recall on the positive class using the above roc to aim for about 86% recall.
lrcv3 = LogisticRegression(penalty='l2', C=1.0, class_weight={1: 0.95, 0:.30})
lrcv3_model = lrcv3.fit(Xlr_train, ylr_train)

# predictions
ylr_pred3 = lrcv3_model.predict(Xlr_test)

# conf matrix
lr_cm3 = confusion_matrix(ylr_test, ylr_pred3)
lr_cm3 = pd.DataFrame(lr_cm3, columns=["Survived_N", "Survived_Y"], index=["Survived_N", "Survived_Y"])
lr_cm3

print(classification_report(ylr_test, ylr_pred3))

# We will have to rescale our our age feature, we are going to leave out fare

df_knn = df_lr.copy()

# df_knn.head()

# I wonder if it matters if we min max scale from standardized data.
from sklearn.preprocessing import MinMaxScaler
scaler = MinMaxScaler()
age_scaled = scaler.fit_transform(df_knn['age_std'])
df_knn['age_scaled'] = age_scaled

df_knn.drop(['age_std', 'fare_std'], axis=1, inplace=True)

df_knn.drop(['Embarked_Q', 'Embarked_S'], axis=1, inplace=True)

df_knn.columns

df_knn.drop(['deck_A','deck_B','deck_C','deck_D','deck_E','deck_F','deck_G','deck_T'], axis=1, inplace=True)

df_knn.drop(['SibSp_1','SibSp_2','SibSp_3','SibSp_4','SibSp_5','SibSp_8'], axis=1, inplace=True)

df_knn.drop(['Parch_1','Parch_2','Parch_3','Parch_4','Parch_5','Parch_6'], axis=1, inplace=True)

df_knn.drop(['title_Dr','title_Lady','title_MHon', 'title_Master','title_Miss',\
             'title_Mrs','title_Ms','title_Rev','title_Sir'], axis=1, inplace=True)

X_knn = df_knn.iloc[:,1:]
y_knn = df_knn['Survived']

X_knn.head()

Xknn_train, Xknn_test, yknn_train, yknn_test = train_test_split(X_knn, y_knn, test_size=.30, random_state=78)

from sklearn.neighbors import KNeighborsClassifier
knn = KNeighborsClassifier(n_neighbors=3, weights='uniform')

knn.fit(Xknn_train,yknn_train)

# Check accuracy
knn_pred = knn.predict(Xknn_test)
accuracy_score(yknn_test, knn_pred)

# Let's gridsearch some parameters for knn

K = range(1,11)
wghts = ['uniform','distance']

knn = KNeighborsClassifier()

gs = GridSearchCV(knn, {'n_neighbors': K, 'weights':wghts}, cv=3)
gs.fit(X_knn, y_knn)

print gs.best_params_
print gs.best_score_

knn = KNeighborsClassifier(n_neighbors=4, weights='uniform')

knn.fit(Xknn_train,yknn_train)

# Check accuracy
knn_pred = knn.predict(Xknn_test)
accuracy_score(yknn_test, knn_pred)

# conf matrix
knn_cm = confusion_matrix(yknn_test, knn_pred)
knn_cm = pd.DataFrame(knn_cm, columns=["Survived_N", "Survived_Y"], index=["Survived_N", "Survived_Y"])
knn_cm

print(classification_report(yknn_test, knn_pred))

Y_score = knn.predict_proba(Xknn_test)[:,1]


FPR = dict()
TPR = dict()
ROC_AUC = dict()

# For class 1, find the area under the curve
FPR[1], TPR[1], _ = roc_curve(yknn_test, Y_score)
ROC_AUC[1] = auc(FPR[1], TPR[1])

# Plot of a ROC curve for class 1 (has_cancer)
plt.figure(figsize=[6,5])
plt.plot(FPR[1], TPR[1], label='ROC curve (area = %0.2f)' % ROC_AUC[1], linewidth=4)
plt.plot([0, 1], [0, 1], 'k--', linewidth=4)
plt.xlim([0.0, 1.0])
plt.ylim([0.0, 1.05])
plt.xlabel('False Positive Rate', fontsize=18)
plt.ylabel('True Positive Rate', fontsize=18)
plt.title('Knn - ROC for predicting Survival', fontsize=18)
plt.legend(loc="lower right")
plt.show()

# knn does better capturing non-survivors with a 92% recall, but is worse on survivors with a recall of 64, 
# while maintaining a similar accuaracy as log reg

# Lets try a decision tree
from sklearn.tree import DecisionTreeClassifier

df_tree = df.copy()

to_drop = ['Name', 'Ticket', 'Cabin', 'surname']
df_tree.drop(to_drop, axis=1, inplace=True)
df_tree.head()

df_tree['Sex'] = df_tree['Sex'].apply(lambda x: 1 if x == 'male' else 0)

df_tree = pd.concat([df_tree.drop('Pclass',axis=1),pd.get_dummies(df['Pclass'], prefix='Class')], axis = 1)
df_tree = pd.concat([df_tree.drop('Fsize',axis=1),pd.get_dummies(df['Fsize'], prefix='Fsize')], axis = 1)

ytr = df_tree['Survived']
Xtr = pd.get_dummies(df_tree.drop('Survived', axis=1))
Xtr.head()

Xtr_train, Xtr_test, ytr_train, ytr_test = train_test_split(Xtr, ytr, test_size=.30, random_state=42)

dt = DecisionTreeClassifier(max_depth = 20) 

dt.fit(Xtr_train, ytr_train)

feature_importances = pd.DataFrame(dt.feature_importances_,
                                   index = Xtr_train.columns,
                                    columns=['importance'])
feature_importances.sort_values(by='importance', ascending=False)

# Check accuracy
dt_pred = dt.predict(Xtr_test)
accuracy_score(ytr_test, dt_pred)

K = range(5,31)
wghts = ['balanced', None]

dt = DecisionTreeClassifier()

gs = GridSearchCV(dt, {'max_depth': K, 'class_weight':wghts}, cv=15)
gs.fit(Xtr, ytr)

print gs.best_params_
print gs.best_score_

# re fit model with max depth 8
dt = DecisionTreeClassifier(max_depth = 8) 

dt.fit(Xtr_train, ytr_train)

# Check accuracy
dt_pred = dt.predict(Xtr_test)
accuracy_score(ytr_test, dt_pred)

# get a slightly better acuuracy than before gridsearch

# conf matrix
dt_cm = confusion_matrix(ytr_test, dt_pred)
dt_cm = pd.DataFrame(dt_cm, columns=["Survived_N", "Survived_Y"], index=["Survived_N", "Survived_Y"])
dt_cm

# looks pretty much the same as the first logistic regression
# predicted 82 survivors correctly, but missed 29 survivors and also classified 19 non-survivors as survivors.

print(classification_report(ytr_test, dt_pred))

# Our precision is fairly good, but our recall on class 1 - survival - is a bit lower, 
# missing 26% of of survivors (29)  

Y_score = dt.predict_proba(Xtr_test)[:,1]


FPR = dict()
TPR = dict()
ROC_AUC = dict()

# For class 1, find the area under the curve
FPR[1], TPR[1], _ = roc_curve(ytr_test, Y_score)
ROC_AUC[1] = auc(FPR[1], TPR[1])

# Plot of a ROC curve for class 1 (has_cancer)
plt.figure(figsize=[6,5])
plt.plot(FPR[1], TPR[1], label='ROC curve (area = %0.2f)' % ROC_AUC[1], linewidth=4)
plt.plot([0, 1], [0, 1], 'k--', linewidth=4)
plt.xlim([0.0, 1.0])
plt.ylim([0.0, 1.05])
plt.xlabel('False Positive Rate', fontsize=18)
plt.ylabel('True Positive Rate', fontsize=18)
plt.title('Decision Tree - ROC for predicting Survival', fontsize=18)
plt.legend(loc="lower right")
plt.show()

# Lets adjust the decision threshold  
dt2 = DecisionTreeClassifier(max_depth = 8, class_weight={1: 0.1, 0:.9}) 

dt2.fit(Xtr_train, ytr_train)

dt2_pred = dt2.predict(Xtr_test)
accuracy_score(ytr_test, dt2_pred)

# conf matrix
dt2_cm = confusion_matrix(ytr_test, dt2_pred)
dt2_cm = pd.DataFrame(dt2_cm, columns=["P_Survived_N", "P_Survived_Y"], index=["Survived_N", "Survived_Y"])
dt2_cm

dt_cm

print(classification_report(ytr_test, dt2_pred))

Y_score = dt2.predict_proba(Xtr_test)[:,1]


FPR = dict()
TPR = dict()
ROC_AUC = dict()

# For class 1, find the area under the curve
FPR[1], TPR[1], _ = roc_curve(ytr_test, Y_score)
ROC_AUC[1] = auc(FPR[1], TPR[1])

# Plot of a ROC curve for class 1 (has_cancer)
plt.figure(figsize=[6,5])
plt.plot(FPR[1], TPR[1], label='ROC curve (area = %0.2f)' % ROC_AUC[1], linewidth=4)
plt.plot([0, 1], [0, 1], 'k--', linewidth=4)
plt.xlim([0.0, 1.0])
plt.ylim([0.0, 1.05])
plt.xlabel('False Positive Rate', fontsize=18)
plt.ylabel('True Positive Rate', fontsize=18)
plt.title('Decision Tree (weighted) - ROC', fontsize=18)
plt.legend(loc="lower right")
plt.show()

Y_score = dt2.predict_proba(Xtr_test)[:,1]

FPR = dict()
TPR = dict()
ROC_AUC = dict()

# For class 1, find the area under the curve
FPR[1], TPR[1], _ = roc_curve(ytr_test, Y_score)
ROC_AUC[1] = auc(FPR[1], TPR[1])

# Plot of a ROC curve for class 1 - survival
plt.subplots(figsize=(6,6));
plt.plot(FPR[1], TPR[1], label='CART ROC curve (area = %0.2f)' % ROC_AUC[1], linewidth=4)
plt.plot([0, 1], [0, 1], 'k--', linewidth=4)
plt.xlim([0.0, 1.0])
plt.ylim([0.0, 1.05])
plt.xlabel('False Positive Rate', fontsize=18)
plt.ylabel('True Positive Rate', fontsize=18)
plt.title('Predicting Survival - ROC', fontsize=18)
plt.legend(loc="lower right")

Y_score = knn.predict_proba(Xknn_test)[:,1]

FPR = dict()
TPR = dict()
ROC_AUC = dict()

FPR[1], TPR[1], _ = roc_curve(yknn_test, Y_score)
ROC_AUC[1] = auc(FPR[1], TPR[1])

plt.plot(FPR[1], TPR[1], label='Knn ROC curve (area = %0.2f)' % ROC_AUC[1], linewidth=4)
plt.plot([0, 1], [0, 1], 'k--', linewidth=4)
plt.xlim([0.0, 1.0])
plt.ylim([0.0, 1.05])
plt.xlabel('False Positive Rate', fontsize=18)
plt.ylabel('True Positive Rate', fontsize=18)
plt.legend(loc="lower right")

Y_score = lr2_model.decision_function(Xlr_test)

FPR = dict()
TPR = dict()
ROC_AUC = dict()

FPR[1], TPR[1], _ = roc_curve(ylr_test, Y_score)
ROC_AUC[1] = auc(FPR[1], TPR[1])

plt.plot(FPR[1], TPR[1], label='LR ROC curve (area = %0.2f)' % ROC_AUC[1], linewidth=4)
plt.plot([0, 1], [0, 1], 'k--', linewidth=4)
plt.xlim([0.0, 1.0])
plt.ylim([0.0, 1.05])
plt.xlabel('False Positive Rate', fontsize=18)
plt.ylabel('True Positive Rate', fontsize=18)
plt.legend(loc="lower right")
plt.show()

# Lets try bagging

from sklearn.ensemble import BaggingClassifier
bagger = BaggingClassifier(dt2, max_samples=1.0)

print "DT Score:\t", cross_val_score(dt2, Xtr, ytr, cv=10, n_jobs=1).mean()
print "Bagging Score:\t", cross_val_score(bagger, Xtr, ytr, cv=10, n_jobs=1).mean()

baggerk = BaggingClassifier(knn)

print "Knn Score:\t", cross_val_score(knn, X_knn, y_knn, cv=10, n_jobs=1).mean()
print "Bagging Score:\t", cross_val_score(baggerk, X_knn, y_knn, cv=10, n_jobs=1).mean()

bagger_lr = BaggingClassifier(lr)

print "LR Score:\t", cross_val_score(lr, X_lr, y_lr, cv=10, n_jobs=1).mean()
print "Bagging Score:\t", cross_val_score(bagger_lr, X_lr, y_lr, cv=10, n_jobs=1).mean()

# Our decision tree is the only model that benefits from bagging here and only slightly. 
# It perhaps was mildly overfit, while the other two were tuned fairly well already