Test_Scripts.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Library Imports\n",
    "\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import trellis\n",
    "import os\n",
    "from avant_python_utils.email import send_email\n",
    "from datalaketools.connectors.presto_db import PrestoDB\n",
    "presto = PrestoDB()\n",
    "import matplotlib.pyplot as plt\n",
    "from sklearn import metrics\n",
    "from sklearn.metrics import roc_auc_score\n",
    "from sklearn.metrics import roc_curve, f1_score,recall_score,precision_score, average_precision_score\n",
    "from datetime import date, timedelta, datetime\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "#modules to write to google sheets\n",
    "import gspread\n",
    "from oauth2client.service_account import ServiceAccountCredentials"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection_name = 'us_fraud_follower'\n",
    "sheet_key = '10aJZFUxDhEoa1uBw47sB0SS7Re5QQ5a3twujraopm84'\n",
    "google_key_file = 'service_key.json'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "#global variables - these store column names that will be used in functions below\n",
    "SCORE_COL = 'score_5'\n",
    "YPRED_COL = 'prediction'\n",
    "YTRUE_COL = 'suspected_fraud'\n",
    "TIME_COL = 'created_date'\n",
    "WEEKSTART_COL = 'created_week'\n",
    "#TIME_COL = 'created_at'\n",
    "AMOUNT_COL = 'loan_amount'\n",
    "THRESHOLD = 0.05\n",
    "MODEL_START_DATE = '2018-09-15'\n",
    "\n",
    "#Google Sheet names\n",
    "BASELINE_WORKSHEET = 'Baselines Data'\n",
    "WEEKLY_WORKSHEET = 'Charts Data'\n",
    "TABLES_WORKSHEET = 'Tables Data'\n",
    "\n",
    "\n",
    "\n",
    "#SQL Query to pull base table data\n",
    "base_table_query = \"\"\"\n",
    "SELECT\n",
    "  l.id as loan_id\n",
    ", l.created_date\n",
    ", date_trunc('week', l.created_date) as created_week\n",
    ", l.status\n",
    ", case when l.status in ('current','late','paid_off','charged_off') then 1 else 0 end as issued\n",
    ", case when c.high_confidence_fraud_indicator=true or cfl.id is not null then 1 else 0 end as high_confidence_fraud_indicator\n",
    ", case when cfr.customer_id is not null then 1 else 0 end as suspected_fraud \n",
    "--, cfrt.name as fraud_reason\n",
    ", cast(fd.score_5_old as double) as score_5_old\n",
    ", cast(fd.score_5_new as double) as score_5_new\n",
    ", coalesce(cast(fd.score_5_old as double), cast(fd.score_5_new as double)) as score_5\n",
    ", l.state\n",
    ", l.payment_method\n",
    ", l.loan_amount\n",
    ", ca.product_type\n",
    ", vrdt.risk_summary_identity_high\n",
    ", vrdt.risk_summary_identity_medium\n",
    ", vrdt.risk_summary_identity_low\n",
    "FROM avant.dw.customer_applications ca\n",
    "LEFT JOIN avant.dw.loans l on l.customer_application_id = ca.id\n",
    "JOIN avant.dw.customers c\n",
    "  ON c.id = l.customer_id\n",
    "  \n",
    "  -- getting dependent variable\n",
    "  \n",
    "LEFT JOIN (\n",
    "select customer_id \n",
    "from avant.avant_basic.customer_fraud_reasons cfr \n",
    "group by 1\n",
    ") cfr on c.id = cfr.customer_id\n",
    "  \n",
    " -- LEFT JOIN avant.avant_basic.customer_fraud_reason_types cfrt on cfr.customer_fraud_reason_type_id = cfrt.id\n",
    "  \n",
    "  -- getting fraud scores\n",
    "LEFT JOIN (\n",
    "  SELECT\n",
    "    l.id as loan_id\n",
    "  , json_extract_scalar(fd.model_scores, '$[\"fraud/en-US/4.1.0\"][\"score\"]') as score_4\n",
    "  , json_extract_scalar(fd.model_scores, '$[\"fraud/en-US/5.0.0\"][\"score\"]') as score_5_old\n",
    "  , json_extract_scalar(fd.model_scores, '$[\"fraud/en-US/5.0.0/avant\"][\"score\"]') as score_5_new\n",
    "  \n",
    "  , fd.id as fraud_decision_id\n",
    "  , row_number() over (partition by l.id order by fd.created_at desc) as row_num\n",
    "  FROM avant.dw.loans l\n",
    "  JOIN avant.avant_basic.fraud_decisions fd\n",
    "    ON fd.customer_application_id = l.customer_application_id\n",
    "    AND fd.created_at AT TIME ZONE 'America/Chicago' >= l.created_date\n",
    "WHERE l.created_date > date '{START_DATE}'\n",
    ") fd \n",
    "  ON fd.loan_id = l.id \n",
    "  AND fd.row_num=1\n",
    "  -- getting fraud indicator\n",
    "LEFT JOIN avant.avant_basic.confirmed_fraud_logs cfl \n",
    "  ON cfl.customer_id = c.id\n",
    "  \n",
    "    -- filtering for valid loans to evaluate performance on\n",
    "  -- JOIN avant.dw.loan_performance_by_installment lp \n",
    "  -- ON lp.loan_id = l.id \n",
    "  -- AND lp.installment_number = 1\n",
    "  -- AND lp.installment_date <= date_add('day', -64, current_timestamp)\n",
    "\n",
    "  \n",
    "  -- adding identity tier a loan was assigned to and fraud_review flag\n",
    "  LEFT JOIN avant.dw_temp_newver.verifications_risks_decisions_test vrdt\n",
    "  on ca.id = vrdt.customer_application_id and vrdt.row_num_recent = 1\n",
    "  \n",
    "  \n",
    "WHERE l.created_date > date '{START_DATE}'\n",
    "\"\"\".format(START_DATE = MODEL_START_DATE)\n",
    "\n",
    "\n",
    "def base_table_creator(query = base_table_query):\n",
    "    df_raw = presto.execute_df(base_table_query)\n",
    "    #df_raw = pd.read_sql(query, connector)\n",
    "    df = df_raw[df_raw[SCORE_COL].notnull()]\n",
    "    df[YPRED_COL] = np.where(df[SCORE_COL] > THRESHOLD, 1, 0)\n",
    "    return df\n",
    "    \n",
    "\n",
    "#Get monitoring metrics for each week\n",
    "def weekly_evaluator(dframe, ytrue = YTRUE_COL, ypred = YPRED_COL, scores = SCORE_COL):\n",
    "    \n",
    "    #calculating multiple metrics\n",
    "    precision = precision_score(y_true = dframe[ytrue], y_pred = dframe[ypred], pos_label = 1, zero_division = 0)\n",
    "    recall = recall_score(y_true = dframe[ytrue], y_pred = dframe[ypred], pos_label = 1, zero_division = 0)\n",
    "    f1score = f1_score(y_true = dframe[ytrue], y_pred = dframe[ypred], pos_label = 1)\n",
    "    auc_pr = average_precision_score(y_true = dframe[ytrue], y_score = dframe[scores], pos_label=1)\n",
    "    fraud_rate = dframe[ytrue].sum()/len(dframe.index)\n",
    "    avg_score = dframe[scores].sum()/len(dframe.index)\n",
    "    try:\n",
    "        auc_roc = roc_auc_score(y_true = dframe[ytrue], y_score = dframe[scores])\n",
    "    except ValueError:\n",
    "        auc_roc = \"\"\n",
    "\n",
    "    \n",
    "    \n",
    "    return pd.Series({'precision': precision, 'recall': recall, 'f1score': f1score, 'auc_pr':auc_pr, 'auc_roc':auc_roc,\n",
    "                     'fraud_rate': fraud_rate, 'avg_score': avg_score})\n",
    "\n",
    "\n",
    "#function to create metric values for tables in Google Sheets\n",
    "def values_for_cells(dframe, ytrue = YTRUE_COL, ypred = YPRED_COL, scores = SCORE_COL, timecol = TIME_COL, amount = AMOUNT_COL):\n",
    "   \n",
    "    #Setting up variables with different date values\n",
    "    \n",
    "    model_start_date = min(dframe[TIME_COL])\n",
    "    model_start_date = datetime.strptime(model_start_date, '%Y-%m-%d %H:%M:%S.%f').date()\n",
    "    today_date = date.today().strftime(\"%Y-%m-%d\")\n",
    "    prev30_date = (date.today() - timedelta(days = 30)).strftime(\"%Y-%m-%d\")\n",
    "    prev60_date = (date.today() - timedelta(days = 60)).strftime(\"%Y-%m-%d\")\n",
    "    \n",
    "    modeltrain_date_start = (model_start_date + timedelta(days = 30)).strftime(\"%Y-%m-%d\")\n",
    "    modeltrain_date_end = (model_start_date + timedelta(days = 60)).strftime(\"%Y-%m-%d\")\n",
    "   \n",
    "    #creating different datasets for the different time periods\n",
    "\n",
    "    data_first30 = dframe.query('{0} > @modeltrain_date_start & {0} < @modeltrain_date_end'.format(TIME_COL))\n",
    "    data_last30 = dframe.query('{0} > @prev30_date & {0} < @today_date'.format(TIME_COL))\n",
    "    data_prev30 = dframe.query('{0} > @prev60_date & {0} < @prev30_date'.format(TIME_COL))   \n",
    "\n",
    "    #PRECISION\n",
    "    precision_current = precision_score(y_true = data_last30[ytrue], y_pred = data_last30[ypred], pos_label = 1)\n",
    "    precision_initial = precision_score(y_true = data_first30[ytrue], y_pred = data_first30[ypred], pos_label = 1)\n",
    "    precision_prev30 = precision_score(y_true = data_prev30[ytrue], y_pred = data_prev30[ypred], pos_label = 1)\n",
    "\n",
    "    #recall values\n",
    "    recall_current = recall_score(y_true = data_last30[ytrue], y_pred = data_last30[ypred], pos_label = 1)\n",
    "    recall_initial = recall_score(y_true = data_first30[ytrue], y_pred = data_first30[ypred], pos_label = 1)\n",
    "    recall_prev30 = recall_score(y_true = data_prev30[ytrue], y_pred = data_prev30[ypred], pos_label = 1) \n",
    "\n",
    "    #F1 score\n",
    "    f1_current = f1_score(y_true = data_last30[ytrue], y_pred = data_last30[ypred], pos_label = 1)\n",
    "    f1_initial = f1_score(y_true = data_first30[ytrue], y_pred = data_first30[ypred], pos_label = 1)\n",
    "    f1_prev30 = f1_score(y_true = data_prev30[ytrue], y_pred = data_prev30[ypred], pos_label = 1) \n",
    "\n",
    "    #auc pr\n",
    "    aucpr_current = average_precision_score(y_true = data_last30[ytrue], y_score = data_last30[scores], pos_label = 1)\n",
    "    aucpr_initial = average_precision_score(y_true = data_first30[ytrue], y_score = data_first30[scores], pos_label = 1)\n",
    "    aucpr_prev30 = average_precision_score(y_true = data_prev30[ytrue], y_score = data_prev30[scores], pos_label = 1) \n",
    "\n",
    "    #auc roc\n",
    "    aucroc_current = roc_auc_score(y_true = data_last30[ytrue], y_score = data_last30[scores])\n",
    "    aucroc_initial = roc_auc_score(y_true = data_first30[ytrue], y_score = data_first30[scores])\n",
    "    aucroc_prev30 = roc_auc_score(y_true = data_prev30[ytrue], y_score = data_prev30[scores]) \n",
    "\n",
    "    #TODO - Confirm fraud rate definition\n",
    "    #fraud rate\n",
    "    fraudrate_current = data_last30[ytrue].sum()/len(data_last30.index)\n",
    "    fraudrate_initial = data_first30[ytrue].sum()/len(data_first30.index)\n",
    "    fraudrate_prev30 = data_prev30[ytrue].sum()/len(data_prev30.index)\n",
    "    \n",
    "    #avg score\n",
    "    avgscore_current = data_last30[scores].sum()/len(data_last30.index)\n",
    "    avgscore_initial = data_first30[scores].sum()/len(data_first30.index)\n",
    "    avgscore_prev30 = data_prev30[scores].sum()/len(data_prev30.index)\n",
    "\n",
    "\n",
    "    #TODO - Confirm fraud missed definition\n",
    "    #fraud rate with dollar values\n",
    "    fraudrate_dollar_current = (data_last30[amount]*data_last30[YTRUE_COL]).sum()/data_last30[amount].sum()\n",
    "    fraudrate_dollar_initial = (data_first30[amount]*data_first30[YTRUE_COL]).sum()/data_first30[amount].sum()\n",
    "    fraudrate_dollar_prev30 = (data_prev30[amount]*data_prev30[YTRUE_COL]).sum()/data_prev30[amount].sum()\n",
    "\n",
    "    #$ value of fraud missed\n",
    "    fraudmissed_dollar_current = (data_last30[amount]*data_last30[YTRUE_COL]*(1-data_last30[YPRED_COL])).sum()\n",
    "    fraudmissed_dollar_initial = (data_first30[amount]*data_first30[YTRUE_COL]*(1-data_first30[YPRED_COL])).sum()\n",
    "    fraudmissed_dollar_prev30 = (data_prev30[amount]*data_prev30[YTRUE_COL]*(1-data_prev30[YPRED_COL])).sum()\n",
    "    \n",
    "    #fraud missed rate (false negative)\n",
    "    fraudmissed_rate_current = (data_last30[YTRUE_COL]*(1-data_last30[YPRED_COL])).sum()/len(data_last30.index)\n",
    "    fraudmissed_rate_initial = (data_first30[YTRUE_COL]*(1-data_first30[YPRED_COL])).sum()/len(data_first30.index)\n",
    "    fraudmissed_rate_prev30 = (data_prev30[YTRUE_COL]*(1-data_prev30[YPRED_COL])).sum()/len(data_prev30.index)\n",
    "    \n",
    "\n",
    "    output = {\"metric\": ['precision', 'recall','f1score', 'auc_pr', 'auc_roc', 'fraudrate', 'avg_score', 'fraudrate_dollar', 'fraudmissed_dollar'],\n",
    "             \"current_values\":[precision_current, recall_current, f1_current, aucpr_current, aucroc_current, fraudrate_current, avgscore_current, fraudrate_dollar_current, fraudmissed_dollar_current],\n",
    "             \"initial_values\":[precision_initial, recall_initial, f1_initial, aucpr_initial, aucroc_initial, fraudrate_initial, avgscore_initial, fraudrate_dollar_initial, fraudmissed_dollar_initial],\n",
    "             \"prev30_values\":[precision_prev30, recall_prev30, f1_prev30, aucpr_prev30, aucroc_prev30, fraudrate_prev30, avgscore_prev30, fraudrate_dollar_prev30, fraudmissed_dollar_prev30]}    \n",
    "        \n",
    "    return output\n",
    "\n",
    "\n",
    "#Function to create baseline data that will be used in charts\n",
    "def create_baseline_data(dframe, ytrue = YTRUE_COL, ypred = YPRED_COL, scores = SCORE_COL, timecol = TIME_COL, amount = AMOUNT_COL):\n",
    "    #Setting up variables with different date values\n",
    "    model_start_date = min(dframe[TIME_COL])\n",
    "    model_start_date = datetime.strptime(model_start_date, '%Y-%m-%d %H:%M:%S.%f').date()\n",
    "     \n",
    "    modeltrain_date_start = (model_start_date + timedelta(days = 30)).strftime(\"%Y-%m-%d\")\n",
    "    modeltrain_date_end = (model_start_date + timedelta(days = 60)).strftime(\"%Y-%m-%d\")\n",
    "    \n",
    "    #creating different datasets for the different time periods\n",
    "    \n",
    "    #dataset 1 - 30 days after model was trained\n",
    "    data_first30 = dframe.query('{0} > @MODEL_START_DATE & {0} < @modeltrain_date_end'.format(TIME_COL))\n",
    "    \n",
    "    #PRECISION\n",
    "    precision_initial = precision_score(y_true = data_first30[ytrue], y_pred = data_first30[ypred], pos_label = 1)\n",
    "    \n",
    "    #recall values\n",
    "    recall_initial = recall_score(y_true = data_first30[ytrue], y_pred = data_first30[ypred], pos_label = 1)\n",
    "    \n",
    "    #F1 score\n",
    "    f1_initial = f1_score(y_true = data_first30[ytrue], y_pred = data_first30[ypred], pos_label = 1)\n",
    "    \n",
    "    #auc pr\n",
    "    aucpr_initial = average_precision_score(y_true = data_first30[ytrue], y_score = data_first30[scores], pos_label = 1)\n",
    "    \n",
    "    #auc roc\n",
    "    aucroc_initial = roc_auc_score(y_true = data_first30[ytrue], y_score = data_first30[scores])\n",
    "    \n",
    "    #TODO - Confirm fraud rate definition\n",
    "    #fraud rate\n",
    "    fraudrate_initial = data_first30[ytrue].sum()/len(data_first30.index)\n",
    "    \n",
    "    #avg score\n",
    "    avgscore_initial = data_first30[scores].sum()/len(data_first30.index)\n",
    "\n",
    "    #TODO - Confirm fraud missed definition\n",
    "    #fraud rate with dollar values\n",
    "    fraudrate_dollar_initial = (data_first30[amount]*data_first30[YTRUE_COL]).sum()/data_first30[amount].sum()\n",
    "\n",
    "    #$ value of fraud missed\n",
    "    fraudmissed_dollar_initial = data_first30[scores].sum()/len(data_first30.index)\n",
    "    \n",
    "    #creating grouped by data frame with needed weeks\n",
    "    baseline_dataframe = pd.DataFrame(dframe[WEEKSTART_COL].unique()).rename(columns={0: WEEKSTART_COL}).sort_values(by = WEEKSTART_COL)\n",
    "    baseline_dataframe = baseline_dataframe.assign(precision_baseline = precision_initial,\n",
    "                              recall_baseline = recall_initial, \n",
    "                              f1_baseline = f1_initial, \n",
    "                              aucpr_baseline = aucpr_initial,\n",
    "                              aucroc_baseline = aucroc_initial,\n",
    "                              fraudrate_baseline = fraudrate_initial,\n",
    "                              avgscore_baseline = avgscore_initial,\n",
    "                              fraudrate_dollar_baseline = fraudrate_dollar_initial,\n",
    "                              fraudmissed_dollar_baseline = fraudmissed_dollar_initial\n",
    "                              )\n",
    "    baseline_dataframe[WEEKSTART_COL] = byWeek_stats[WEEKSTART_COL].astype(str)\n",
    "    \n",
    "    return baseline_dataframe\n",
    "\n",
    "\n",
    "def sheets_updater(workbook_key, google_key_file, byweek_dataset, tables_dataset, baselines_dataset):\n",
    "    \n",
    "    #authorization\n",
    "    scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']\n",
    "    credentials = ServiceAccountCredentials.from_json_keyfile_name(google_key_file, scope)\n",
    "    gc = gspread.authorize(credentials)\n",
    "    \n",
    "    \n",
    "    workbook = gc.open_by_key(workbook_key)\n",
    "    \n",
    "    #opening worksheets\n",
    "    weekly_worksheet = workbook.worksheet(WEEKLY_WORKSHEET)\n",
    "    tablesdata_worksheet = workbook.worksheet(TABLES_WORKSHEET)\n",
    "    baselinedata_worksheet = workbook.worksheet(BASELINE_WORKSHEET)\n",
    "    \n",
    "    #clearing worksheets\n",
    "    weekly_worksheet.clear()\n",
    "    tablesdata_worksheet.clear()\n",
    "    baselinedata_worksheet.clear()\n",
    "    \n",
    "    #updating worksheets  (first list out columns, and then add values for each column)\n",
    "    weekly_worksheet.update([byweek_dataset.columns.values.tolist()] + byweek_dataset.values.tolist())\n",
    "    tablesdata_worksheet.update([tables_dataset.columns.values.tolist()] + tables_dataset.values.tolist())\n",
    "    baselinedata_worksheet.update([baselines_dataset.columns.values.tolist()] + baselines_dataset.values.tolist())\n",
    "    \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/jovyan/.conda-envs/gkrishna_env/lib/python3.6/site-packages/ipykernel_launcher.py:97: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n"
     ]
    }
   ],
   "source": [
    "applications_data = base_table_creator()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'2018-09-15 00:08:16.781'"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "min(applications_data[TIME_COL])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "datetime.date(2018, 9, 15)"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "datetime.strptime('2018-09-15 00:08:16.781', '%Y-%m-%d %H:%M:%S.%f').date()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "#creating weekly data\n",
    "byWeek_stats = applications_data.groupby(WEEKSTART_COL, as_index = False).apply(weekly_evaluator)\n",
    "byWeek_stats[WEEKSTART_COL] = byWeek_stats[WEEKSTART_COL].astype(str)\n",
    "byWeek_stats = byWeek_stats.fillna(\"\")\n",
    "byWeek_stats.replace(0, \"\", inplace=True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "#creating baseline data\n",
    "baseline_data = create_baseline_data(applications_data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "\n",
    "#creating data for tables\n",
    "tables_data = pd.DataFrame.from_dict(values_for_cells(applications_data))\n",
    "\n",
    "#updating google sheets\n",
    "sheets_updater(workbook_key = sheet_key, google_key_file = google_key_file, \n",
    "               byweek_dataset = byWeek_stats, tables_dataset = tables_data, baselines_dataset = baseline_data)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "python -c \"import oauth2client; print(oauth2client.__version__)\""
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "gkrishna_env",
   "language": "python",
   "name": "gkrishna_env"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}