ModelMetrics_Avant.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Monitoring Automation\n",
    "\n",
    "This notebook documents the code and progress as I work my way towards setting up an automated process to monitor the performance of the fraud model(s), across partners and products. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "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"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Constants with Column Names\n",
    "\n",
    "Lots of columns that are used in multiple functions throughout this document. I'll define those columns here so I won't have to change them in multiple places later if there's a change"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# TODO - Define these as global variables (store them in a config.py file)\n",
    "\n",
    "SCORE_COL = 'score_5'\n",
    "YPRED_COL = 'prediction'\n",
    "YTRUE_COL = 'suspected_fraud'\n",
    "TIME_COL = 'loan_processing_start_time'\n",
    "WEEKSTART_COL = 'entered_lp_week'\n",
    "#TIME_COL = 'created_at'\n",
    "AMOUNT_COL = 'loan_amount'\n",
    "THRESHOLD = 0.05\n",
    "LOAN_WINDOW = 'week'\n",
    "MODEL_START_DATE = '2018-09-15'\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Base Data Creation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "#trellis.start()\n",
    "# fraud = trellis.connect('us_fraud_follower')\n",
    "#parent_dir_path = os.path.dirname(os.path.abspath(__file__)) - REMOVE COMMENT IN PYTHON SCRIPT\n",
    "parent_dir_path = os.getcwd()\n",
    "subject = 'Avant Model Monitor Weekly Report (Data Only)'\n",
    "credentials = {'username': trellis.keys('automate_email')['email'], 'password': trellis.keys('automate_email')['pw']}\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# TD prod connection\n",
    "td_connector = trellis.connect('us_fraud_follower')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_raw = presto.execute_df('''\n",
    "SELECT\n",
    "  l.id as loan_id\n",
    ", l.loan_processing_start_time\n",
    ", date_trunc('{LOAN_WINDOW}', l.loan_processing_start_time) as entered_lp_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.loan_processing_start_time\n",
    "WHERE l.loan_processing_start_time > 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.loan_processing_start_time > date '{START_DATE}'\n",
    "'''.format(LOAN_WINDOW = LOAN_WINDOW, START_DATE = MODEL_START_DATE))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Remove records with no fraud score\n",
    "df = df_raw[df_raw.score_5.notnull()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'2020-10-07 21:46:17.937'"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "max(df[TIME_COL])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>loan_id</th>\n",
       "      <th>loan_processing_start_time</th>\n",
       "      <th>entered_lp_week</th>\n",
       "      <th>status</th>\n",
       "      <th>issued</th>\n",
       "      <th>high_confidence_fraud_indicator</th>\n",
       "      <th>suspected_fraud</th>\n",
       "      <th>score_5_old</th>\n",
       "      <th>score_5_new</th>\n",
       "      <th>score_5</th>\n",
       "      <th>state</th>\n",
       "      <th>payment_method</th>\n",
       "      <th>loan_amount</th>\n",
       "      <th>product_type</th>\n",
       "      <th>risk_summary_identity_high</th>\n",
       "      <th>risk_summary_identity_medium</th>\n",
       "      <th>risk_summary_identity_low</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3725947</td>\n",
       "      <td>2019-09-09 08:37:27.287</td>\n",
       "      <td>2019-09-09 00:00:00.000</td>\n",
       "      <td>rejected</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.007947</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.007947</td>\n",
       "      <td>OH</td>\n",
       "      <td>ach</td>\n",
       "      <td>3400.00</td>\n",
       "      <td>installment</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3569707</td>\n",
       "      <td>2019-06-01 14:53:33.840</td>\n",
       "      <td>2019-05-27 00:00:00.000</td>\n",
       "      <td>cancelled</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.923066</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.923066</td>\n",
       "      <td>NY</td>\n",
       "      <td>ach</td>\n",
       "      <td>2100.00</td>\n",
       "      <td>installment</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3672702</td>\n",
       "      <td>2019-08-03 10:35:47.987</td>\n",
       "      <td>2019-07-29 00:00:00.000</td>\n",
       "      <td>rejected</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.007495</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.007495</td>\n",
       "      <td>SC</td>\n",
       "      <td>ach</td>\n",
       "      <td>8553.69</td>\n",
       "      <td>refinance</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3858077</td>\n",
       "      <td>2019-12-31 13:18:48.127</td>\n",
       "      <td>2019-12-30 00:00:00.000</td>\n",
       "      <td>rejected</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.015310</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.015310</td>\n",
       "      <td>KS</td>\n",
       "      <td>ach</td>\n",
       "      <td>3200.00</td>\n",
       "      <td>installment</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3156066</td>\n",
       "      <td>2018-09-19 15:50:18.018</td>\n",
       "      <td>2018-09-17 00:00:00.000</td>\n",
       "      <td>cancelled</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.004678</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.004678</td>\n",
       "      <td>SC</td>\n",
       "      <td>ach</td>\n",
       "      <td>2000.00</td>\n",
       "      <td>installment</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>502656</th>\n",
       "      <td>3504087</td>\n",
       "      <td>2019-04-22 18:26:32.161</td>\n",
       "      <td>2019-04-22 00:00:00.000</td>\n",
       "      <td>current</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.006523</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.006523</td>\n",
       "      <td>NJ</td>\n",
       "      <td>ach</td>\n",
       "      <td>10547.26</td>\n",
       "      <td>refinance</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>502657</th>\n",
       "      <td>3644025</td>\n",
       "      <td>2019-07-16 21:03:02.335</td>\n",
       "      <td>2019-07-15 00:00:00.000</td>\n",
       "      <td>charged_off</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.006476</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.006476</td>\n",
       "      <td>FL</td>\n",
       "      <td>ach</td>\n",
       "      <td>4400.00</td>\n",
       "      <td>installment</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>502658</th>\n",
       "      <td>3899587</td>\n",
       "      <td>2020-02-18 17:08:42.044</td>\n",
       "      <td>2020-02-17 00:00:00.000</td>\n",
       "      <td>current</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.008027</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.008027</td>\n",
       "      <td>CT</td>\n",
       "      <td>ach</td>\n",
       "      <td>5605.18</td>\n",
       "      <td>refinance</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>502659</th>\n",
       "      <td>3163165</td>\n",
       "      <td>2018-09-24 08:25:08.489</td>\n",
       "      <td>2018-09-24 00:00:00.000</td>\n",
       "      <td>cancelled</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.004640</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.004640</td>\n",
       "      <td>MD</td>\n",
       "      <td>ach</td>\n",
       "      <td>4000.00</td>\n",
       "      <td>installment</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>502660</th>\n",
       "      <td>3272008</td>\n",
       "      <td>2018-11-27 15:49:06.310</td>\n",
       "      <td>2018-11-26 00:00:00.000</td>\n",
       "      <td>current</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.005282</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.005282</td>\n",
       "      <td>GA</td>\n",
       "      <td>ach</td>\n",
       "      <td>6600.00</td>\n",
       "      <td>refinance</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>486153 rows × 17 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        loan_id loan_processing_start_time          entered_lp_week  \\\n",
       "0       3725947    2019-09-09 08:37:27.287  2019-09-09 00:00:00.000   \n",
       "1       3569707    2019-06-01 14:53:33.840  2019-05-27 00:00:00.000   \n",
       "2       3672702    2019-08-03 10:35:47.987  2019-07-29 00:00:00.000   \n",
       "3       3858077    2019-12-31 13:18:48.127  2019-12-30 00:00:00.000   \n",
       "4       3156066    2018-09-19 15:50:18.018  2018-09-17 00:00:00.000   \n",
       "...         ...                        ...                      ...   \n",
       "502656  3504087    2019-04-22 18:26:32.161  2019-04-22 00:00:00.000   \n",
       "502657  3644025    2019-07-16 21:03:02.335  2019-07-15 00:00:00.000   \n",
       "502658  3899587    2020-02-18 17:08:42.044  2020-02-17 00:00:00.000   \n",
       "502659  3163165    2018-09-24 08:25:08.489  2018-09-24 00:00:00.000   \n",
       "502660  3272008    2018-11-27 15:49:06.310  2018-11-26 00:00:00.000   \n",
       "\n",
       "             status  issued  high_confidence_fraud_indicator  suspected_fraud  \\\n",
       "0          rejected       0                                0                0   \n",
       "1         cancelled       0                                0                0   \n",
       "2          rejected       0                                0                0   \n",
       "3          rejected       0                                0                0   \n",
       "4         cancelled       0                                0                0   \n",
       "...             ...     ...                              ...              ...   \n",
       "502656      current       1                                0                0   \n",
       "502657  charged_off       1                                0                0   \n",
       "502658      current       1                                0                0   \n",
       "502659    cancelled       0                                0                0   \n",
       "502660      current       1                                0                0   \n",
       "\n",
       "        score_5_old  score_5_new   score_5 state payment_method  loan_amount  \\\n",
       "0          0.007947          NaN  0.007947    OH            ach      3400.00   \n",
       "1          0.923066          NaN  0.923066    NY            ach      2100.00   \n",
       "2          0.007495          NaN  0.007495    SC            ach      8553.69   \n",
       "3          0.015310          NaN  0.015310    KS            ach      3200.00   \n",
       "4          0.004678          NaN  0.004678    SC            ach      2000.00   \n",
       "...             ...          ...       ...   ...            ...          ...   \n",
       "502656     0.006523          NaN  0.006523    NJ            ach     10547.26   \n",
       "502657     0.006476          NaN  0.006476    FL            ach      4400.00   \n",
       "502658     0.008027          NaN  0.008027    CT            ach      5605.18   \n",
       "502659     0.004640          NaN  0.004640    MD            ach      4000.00   \n",
       "502660     0.005282          NaN  0.005282    GA            ach      6600.00   \n",
       "\n",
       "       product_type risk_summary_identity_high risk_summary_identity_medium  \\\n",
       "0       installment                       True                        False   \n",
       "1       installment                       True                        False   \n",
       "2         refinance                      False                        False   \n",
       "3       installment                       True                        False   \n",
       "4       installment                       True                        False   \n",
       "...             ...                        ...                          ...   \n",
       "502656    refinance                      False                        False   \n",
       "502657  installment                      False                        False   \n",
       "502658    refinance                      False                        False   \n",
       "502659  installment                      False                        False   \n",
       "502660    refinance                       None                         None   \n",
       "\n",
       "       risk_summary_identity_low  \n",
       "0                          False  \n",
       "1                          False  \n",
       "2                           True  \n",
       "3                          False  \n",
       "4                          False  \n",
       "...                          ...  \n",
       "502656                      True  \n",
       "502657                      True  \n",
       "502658                      True  \n",
       "502659                      True  \n",
       "502660                      None  \n",
       "\n",
       "[486153 rows x 17 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/jovyan/.conda-envs/gkrishna_env/lib/python3.6/site-packages/ipykernel_launcher.py:1: 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",
      "  \"\"\"Entry point for launching an IPython kernel.\n"
     ]
    }
   ],
   "source": [
    "df[YPRED_COL] = np.where(df[SCORE_COL] > THRESHOLD, 1, 0)\n",
    "#df['prediction'] = [1 if x > 0.05 else 0 for x in df['score_5']]\n",
    "#df['prediction'] = list(np.where(df['score_5'] > 0.05, 1, 0)) \n",
    "# All three solutions raise the settingwithcopywarining"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Model Evaluation Pipeline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "def weekly_evaluator(dframe, ytrue = YTRUE_COL, ypred = YPRED_COL, scores = SCORE_COL, amount = AMOUNT_COL):\n",
    "    \n",
    "    #false positives, true negatives for false positive rate\n",
    "    true_positives = (dframe[ytrue] * dframe[ypred]).sum()\n",
    "    false_positives = ((1-dframe[ytrue]) * dframe[ypred]).sum()\n",
    "    false_negatives =  (dframe[ytrue] * (1-dframe[ypred])).sum()\n",
    "    true_negatives = ((1-dframe[ytrue]) * (1-dframe[ypred])).sum()\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",
    "    false_positive_rate = false_positives/(false_positives+true_negatives)\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",
    "    fraudmissed_dollar = (dframe[amount]*dframe[ytrue]*(1-dframe[ypred])).sum()\n",
    "    fraudmissed_dollar_rate = 100*(dframe[amount]*dframe[ytrue]*(1-dframe[ypred])).sum()/(dframe[amount]*dframe[ytrue]).sum()\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,'false_positive_rate':false_positive_rate,\n",
    "                      'fraudmissed_dollar': fraudmissed_dollar,'fraudmissed_dollar_rate':fraudmissed_dollar_rate})\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Figuring out the date from which we have both fraud and not fraud in the same week, so that we can group by that week. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "#unique fraud values per week\n",
    "\n",
    "test = pd.DataFrame(df.groupby('entered_lp_week', as_index = False)['suspected_fraud'].nunique())\n",
    "test.query('suspected_fraud == 1')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>entered_lp_week</th>\n",
       "      <th>precision</th>\n",
       "      <th>recall</th>\n",
       "      <th>f1score</th>\n",
       "      <th>auc_pr</th>\n",
       "      <th>auc_roc</th>\n",
       "      <th>fraud_rate</th>\n",
       "      <th>avg_score</th>\n",
       "      <th>false_positive_rate</th>\n",
       "      <th>fraudmissed_dollar</th>\n",
       "      <th>fraudmissed_dollar_rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-09-10 00:00:00.000</td>\n",
       "      <td>0.116279</td>\n",
       "      <td>0.384615</td>\n",
       "      <td>0.178571</td>\n",
       "      <td>0.237991</td>\n",
       "      <td>0.661805</td>\n",
       "      <td>0.023152</td>\n",
       "      <td>0.024801</td>\n",
       "      <td>0.069280</td>\n",
       "      <td>79300.00</td>\n",
       "      <td>54.840941</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2018-09-17 00:00:00.000</td>\n",
       "      <td>0.117517</td>\n",
       "      <td>0.456897</td>\n",
       "      <td>0.186949</td>\n",
       "      <td>0.228845</td>\n",
       "      <td>0.775441</td>\n",
       "      <td>0.018071</td>\n",
       "      <td>0.024988</td>\n",
       "      <td>0.063145</td>\n",
       "      <td>505100.00</td>\n",
       "      <td>56.797481</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2018-09-24 00:00:00.000</td>\n",
       "      <td>0.121839</td>\n",
       "      <td>0.456897</td>\n",
       "      <td>0.192377</td>\n",
       "      <td>0.160161</td>\n",
       "      <td>0.781717</td>\n",
       "      <td>0.018902</td>\n",
       "      <td>0.024543</td>\n",
       "      <td>0.063445</td>\n",
       "      <td>499600.00</td>\n",
       "      <td>51.462711</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2018-10-01 00:00:00.000</td>\n",
       "      <td>0.133858</td>\n",
       "      <td>0.482270</td>\n",
       "      <td>0.209553</td>\n",
       "      <td>0.218586</td>\n",
       "      <td>0.776581</td>\n",
       "      <td>0.021105</td>\n",
       "      <td>0.026667</td>\n",
       "      <td>0.067278</td>\n",
       "      <td>538100.00</td>\n",
       "      <td>51.905083</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2018-10-08 00:00:00.000</td>\n",
       "      <td>0.122530</td>\n",
       "      <td>0.466165</td>\n",
       "      <td>0.194053</td>\n",
       "      <td>0.159918</td>\n",
       "      <td>0.772095</td>\n",
       "      <td>0.022215</td>\n",
       "      <td>0.027208</td>\n",
       "      <td>0.075846</td>\n",
       "      <td>557700.00</td>\n",
       "      <td>55.999598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>105</th>\n",
       "      <td>2020-09-14 00:00:00.000</td>\n",
       "      <td>0.163366</td>\n",
       "      <td>0.687500</td>\n",
       "      <td>0.264000</td>\n",
       "      <td>0.451319</td>\n",
       "      <td>0.880661</td>\n",
       "      <td>0.018370</td>\n",
       "      <td>0.027449</td>\n",
       "      <td>0.065887</td>\n",
       "      <td>133325.56</td>\n",
       "      <td>31.829131</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106</th>\n",
       "      <td>2020-09-21 00:00:00.000</td>\n",
       "      <td>0.142259</td>\n",
       "      <td>0.723404</td>\n",
       "      <td>0.237762</td>\n",
       "      <td>0.332760</td>\n",
       "      <td>0.877794</td>\n",
       "      <td>0.018504</td>\n",
       "      <td>0.034365</td>\n",
       "      <td>0.082230</td>\n",
       "      <td>89799.16</td>\n",
       "      <td>24.515251</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>107</th>\n",
       "      <td>2020-09-28 00:00:00.000</td>\n",
       "      <td>0.166667</td>\n",
       "      <td>0.750000</td>\n",
       "      <td>0.272727</td>\n",
       "      <td>0.345619</td>\n",
       "      <td>0.932326</td>\n",
       "      <td>0.016660</td>\n",
       "      <td>0.037105</td>\n",
       "      <td>0.063535</td>\n",
       "      <td>102791.95</td>\n",
       "      <td>28.964295</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>108</th>\n",
       "      <td>2020-10-05 00:00:00.000</td>\n",
       "      <td>0.159817</td>\n",
       "      <td>0.813953</td>\n",
       "      <td>0.267176</td>\n",
       "      <td>0.409126</td>\n",
       "      <td>0.918128</td>\n",
       "      <td>0.016810</td>\n",
       "      <td>0.032969</td>\n",
       "      <td>0.073161</td>\n",
       "      <td>66080.49</td>\n",
       "      <td>18.495410</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>109</th>\n",
       "      <td>2020-10-12 00:00:00.000</td>\n",
       "      <td>0.170616</td>\n",
       "      <td>0.837209</td>\n",
       "      <td>0.283465</td>\n",
       "      <td>0.541343</td>\n",
       "      <td>0.934164</td>\n",
       "      <td>0.019581</td>\n",
       "      <td>0.037095</td>\n",
       "      <td>0.081282</td>\n",
       "      <td>53400.00</td>\n",
       "      <td>16.771357</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>110 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             entered_lp_week  precision    recall   f1score    auc_pr  \\\n",
       "0    2018-09-10 00:00:00.000   0.116279  0.384615  0.178571  0.237991   \n",
       "1    2018-09-17 00:00:00.000   0.117517  0.456897  0.186949  0.228845   \n",
       "2    2018-09-24 00:00:00.000   0.121839  0.456897  0.192377  0.160161   \n",
       "3    2018-10-01 00:00:00.000   0.133858  0.482270  0.209553  0.218586   \n",
       "4    2018-10-08 00:00:00.000   0.122530  0.466165  0.194053  0.159918   \n",
       "..                       ...        ...       ...       ...       ...   \n",
       "105  2020-09-14 00:00:00.000   0.163366  0.687500  0.264000  0.451319   \n",
       "106  2020-09-21 00:00:00.000   0.142259  0.723404  0.237762  0.332760   \n",
       "107  2020-09-28 00:00:00.000   0.166667  0.750000  0.272727  0.345619   \n",
       "108  2020-10-05 00:00:00.000   0.159817  0.813953  0.267176  0.409126   \n",
       "109  2020-10-12 00:00:00.000   0.170616  0.837209  0.283465  0.541343   \n",
       "\n",
       "      auc_roc  fraud_rate  avg_score  false_positive_rate  fraudmissed_dollar  \\\n",
       "0    0.661805    0.023152   0.024801             0.069280            79300.00   \n",
       "1    0.775441    0.018071   0.024988             0.063145           505100.00   \n",
       "2    0.781717    0.018902   0.024543             0.063445           499600.00   \n",
       "3    0.776581    0.021105   0.026667             0.067278           538100.00   \n",
       "4    0.772095    0.022215   0.027208             0.075846           557700.00   \n",
       "..        ...         ...        ...                  ...                 ...   \n",
       "105  0.880661    0.018370   0.027449             0.065887           133325.56   \n",
       "106  0.877794    0.018504   0.034365             0.082230            89799.16   \n",
       "107  0.932326    0.016660   0.037105             0.063535           102791.95   \n",
       "108  0.918128    0.016810   0.032969             0.073161            66080.49   \n",
       "109  0.934164    0.019581   0.037095             0.081282            53400.00   \n",
       "\n",
       "     fraudmissed_dollar_rate  \n",
       "0                  54.840941  \n",
       "1                  56.797481  \n",
       "2                  51.462711  \n",
       "3                  51.905083  \n",
       "4                  55.999598  \n",
       "..                       ...  \n",
       "105                31.829131  \n",
       "106                24.515251  \n",
       "107                28.964295  \n",
       "108                18.495410  \n",
       "109                16.771357  \n",
       "\n",
       "[110 rows x 11 columns]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "byWeek_stats\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "byWeek_stats = df.groupby('entered_lp_week', as_index = False).apply(weekly_evaluator)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Connecting to Google Sheets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Importing the module\n",
    "import gspread\n",
    "from df2gspread import df2gspread as d2g\n",
    "from oauth2client.service_account import ServiceAccountCredentials"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "#The scope is always look like this so we did not need to change anything\n",
    "scope = [\n",
    "   'https://spreadsheets.google.com/feeds',\n",
    "         'https://www.googleapis.com/auth/drive']\n",
    "#Name of our Service Account Key\n",
    "google_key_file = 'service_key.json'\n",
    "credentials = ServiceAccountCredentials.from_json_keyfile_name(google_key_file, scope)\n",
    "gc = gspread.authorize(credentials)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'spreadsheetId': '14ROlpuOP9IkixM5-nn1Pc0ux6kWgmj7c62NzdDl-5hU',\n",
       " 'updatedRange': \"'Raw Data'!A1:H110\",\n",
       " 'updatedRows': 110,\n",
       " 'updatedColumns': 8,\n",
       " 'updatedCells': 880}"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#This is the Worksheet ID\n",
    "workbook = gc.open_by_key('14ROlpuOP9IkixM5-nn1Pc0ux6kWgmj7c62NzdDl-5hU')\n",
    "#This is the sheet name for week by week data\n",
    "rawdata_worksheet_name = 'Raw Data'\n",
    "rawdata_worksheet = workbook.worksheet(rawdata_worksheet_name)\n",
    "\n",
    "#clear the existing data in worksheet\n",
    "rawdata_worksheet.clear()\n",
    "\n",
    "#update new data to worksheet (first list out columns, and then add values for each column)\n",
    "rawdata_worksheet.update([byWeek_stats.columns.values.tolist()] + byWeek_stats.values.tolist())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'spreadsheetId': '14ROlpuOP9IkixM5-nn1Pc0ux6kWgmj7c62NzdDl-5hU',\n",
       " 'updatedRange': \"'Tables Data'!A1:D10\",\n",
       " 'updatedRows': 10,\n",
       " 'updatedColumns': 4,\n",
       " 'updatedCells': 40}"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#This is the sheet name for tables data\n",
    "workbook = gc.open_by_key('14ROlpuOP9IkixM5-nn1Pc0ux6kWgmj7c62NzdDl-5hU')\n",
    "\n",
    "tablesdata_worksheet_name = 'Tables Data'\n",
    "tablesdata_worksheet = workbook.worksheet(tablesdata_worksheet_name)\n",
    "\n",
    "#clear the existing data in worksheet\n",
    "tablesdata_worksheet.clear()\n",
    "\n",
    "#update new data to worksheet (first list out columns, and then add values for each column)\n",
    "tablesdata_worksheet.update([tables_data.columns.values.tolist()] + tables_data.values.tolist())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'spreadsheetId': '14ROlpuOP9IkixM5-nn1Pc0ux6kWgmj7c62NzdDl-5hU',\n",
       " 'updatedRange': \"'Baselines Data'!A1:J110\",\n",
       " 'updatedRows': 110,\n",
       " 'updatedColumns': 10,\n",
       " 'updatedCells': 1100}"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#This is the sheet name for tables data\n",
    "workbook = gc.open_by_key('14ROlpuOP9IkixM5-nn1Pc0ux6kWgmj7c62NzdDl-5hU')\n",
    "\n",
    "baselinedata_worksheet_name = 'Baselines Data'\n",
    "baselinedata_worksheet = workbook.worksheet(baselinedata_worksheet_name)\n",
    "\n",
    "#clear the existing data in worksheet\n",
    "baselinedata_worksheet.clear()\n",
    "\n",
    "#update new data to worksheet (first list out columns, and then add values for each column)\n",
    "baselinedata_worksheet.update([baseline_data.columns.values.tolist()] + baseline_data.values.tolist())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'spreadsheetId': '14ROlpuOP9IkixM5-nn1Pc0ux6kWgmj7c62NzdDl-5hU',\n",
       " 'replies': [{}]}"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ws.format(\"A2:A1000\", { \"numberFormat\": { \"type\": ('DATE') }})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Filling in Table Values"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For each metric, I need the following \n",
    "\n",
    "- Value since the date model was trained\n",
    "- Compare last 30 days to previous 30 days (with scope for excluding the last 2 months)\n",
    "- Absolute difference between value when model was trained, and current value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "datetime.datetime(2018, 10, 15, 0, 0)"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "datetime.strptime(a, \"%Y-%m-%d\") + timedelta(days = 30)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "test = df.query('loan_processing_start_time > \"2019-09-15\" & loan_processing_start_time < \"2019-10-30\"')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'2020-10-05'"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "today_date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [],
   "source": [
    "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",
    "a = datetime.strptime(MODEL_START_DATE, \"%Y-%m-%d\")\n",
    "b = (a + timedelta(days = 30)).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",
    "\n",
    "\n",
    "data_first30 = df.query('{0} > @MODEL_START_DATE & {0} < @b'.format(TIME_COL))\n",
    "data_last30 = df.query('loan_processing_start_time > @prev30_date & loan_processing_start_time < @today_date')\n",
    "#data_prev30 = dframe.query('@timecol > @prev60_date & @timecol < @prev30_date')   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>loan_id</th>\n",
       "      <th>loan_processing_start_time</th>\n",
       "      <th>entered_lp_week</th>\n",
       "      <th>status</th>\n",
       "      <th>issued</th>\n",
       "      <th>high_confidence_fraud_indicator</th>\n",
       "      <th>suspected_fraud</th>\n",
       "      <th>score_5_old</th>\n",
       "      <th>score_5_new</th>\n",
       "      <th>score_5</th>\n",
       "      <th>state</th>\n",
       "      <th>payment_method</th>\n",
       "      <th>loan_amount</th>\n",
       "      <th>product_type</th>\n",
       "      <th>risk_summary_identity_high</th>\n",
       "      <th>risk_summary_identity_medium</th>\n",
       "      <th>risk_summary_identity_low</th>\n",
       "      <th>prediction</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3156066</td>\n",
       "      <td>2018-09-19 15:50:18.018</td>\n",
       "      <td>2018-09-17 00:00:00.000</td>\n",
       "      <td>cancelled</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.004678</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.004678</td>\n",
       "      <td>SC</td>\n",
       "      <td>ach</td>\n",
       "      <td>2000.0</td>\n",
       "      <td>installment</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61</th>\n",
       "      <td>3188808</td>\n",
       "      <td>2018-10-08 20:09:51.096</td>\n",
       "      <td>2018-10-08 00:00:00.000</td>\n",
       "      <td>paid_off</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.002656</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.002656</td>\n",
       "      <td>FL</td>\n",
       "      <td>ach</td>\n",
       "      <td>3400.0</td>\n",
       "      <td>installment</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>111</th>\n",
       "      <td>3187785</td>\n",
       "      <td>2018-10-08 11:38:45.465</td>\n",
       "      <td>2018-10-08 00:00:00.000</td>\n",
       "      <td>paid_off</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.019646</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.019646</td>\n",
       "      <td>MN</td>\n",
       "      <td>ach</td>\n",
       "      <td>7900.0</td>\n",
       "      <td>refinance</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>140</th>\n",
       "      <td>3169142</td>\n",
       "      <td>2018-09-27 08:42:47.307</td>\n",
       "      <td>2018-09-24 00:00:00.000</td>\n",
       "      <td>paid_off</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.003395</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.003395</td>\n",
       "      <td>OH</td>\n",
       "      <td>ach</td>\n",
       "      <td>12500.0</td>\n",
       "      <td>installment</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>161</th>\n",
       "      <td>3178944</td>\n",
       "      <td>2018-10-02 21:38:24.323</td>\n",
       "      <td>2018-10-01 00:00:00.000</td>\n",
       "      <td>cancelled</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.049705</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.049705</td>\n",
       "      <td>TX</td>\n",
       "      <td>ach</td>\n",
       "      <td>9300.0</td>\n",
       "      <td>installment</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>502569</th>\n",
       "      <td>3157148</td>\n",
       "      <td>2018-09-20 08:37:07.494</td>\n",
       "      <td>2018-09-17 00:00:00.000</td>\n",
       "      <td>rejected</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.003565</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.003565</td>\n",
       "      <td>CA</td>\n",
       "      <td>remotely_created_check</td>\n",
       "      <td>25000.0</td>\n",
       "      <td>installment</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>502634</th>\n",
       "      <td>3187499</td>\n",
       "      <td>2018-10-08 09:26:10.299</td>\n",
       "      <td>2018-10-08 00:00:00.000</td>\n",
       "      <td>charged_off</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.018444</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.018444</td>\n",
       "      <td>MI</td>\n",
       "      <td>ach</td>\n",
       "      <td>2400.0</td>\n",
       "      <td>installment</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>502635</th>\n",
       "      <td>3177090</td>\n",
       "      <td>2018-10-02 05:18:50.992</td>\n",
       "      <td>2018-10-01 00:00:00.000</td>\n",
       "      <td>current</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.005252</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.005252</td>\n",
       "      <td>NC</td>\n",
       "      <td>ach</td>\n",
       "      <td>3000.0</td>\n",
       "      <td>installment</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>502640</th>\n",
       "      <td>3157809</td>\n",
       "      <td>2018-09-21 15:26:42.783</td>\n",
       "      <td>2018-09-17 00:00:00.000</td>\n",
       "      <td>late</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.010588</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.010588</td>\n",
       "      <td>IL</td>\n",
       "      <td>ach</td>\n",
       "      <td>3200.0</td>\n",
       "      <td>installment</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>502659</th>\n",
       "      <td>3163165</td>\n",
       "      <td>2018-09-24 08:25:08.489</td>\n",
       "      <td>2018-09-24 00:00:00.000</td>\n",
       "      <td>cancelled</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.004640</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.004640</td>\n",
       "      <td>MD</td>\n",
       "      <td>ach</td>\n",
       "      <td>4000.0</td>\n",
       "      <td>installment</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>26347 rows × 18 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        loan_id loan_processing_start_time          entered_lp_week  \\\n",
       "4       3156066    2018-09-19 15:50:18.018  2018-09-17 00:00:00.000   \n",
       "61      3188808    2018-10-08 20:09:51.096  2018-10-08 00:00:00.000   \n",
       "111     3187785    2018-10-08 11:38:45.465  2018-10-08 00:00:00.000   \n",
       "140     3169142    2018-09-27 08:42:47.307  2018-09-24 00:00:00.000   \n",
       "161     3178944    2018-10-02 21:38:24.323  2018-10-01 00:00:00.000   \n",
       "...         ...                        ...                      ...   \n",
       "502569  3157148    2018-09-20 08:37:07.494  2018-09-17 00:00:00.000   \n",
       "502634  3187499    2018-10-08 09:26:10.299  2018-10-08 00:00:00.000   \n",
       "502635  3177090    2018-10-02 05:18:50.992  2018-10-01 00:00:00.000   \n",
       "502640  3157809    2018-09-21 15:26:42.783  2018-09-17 00:00:00.000   \n",
       "502659  3163165    2018-09-24 08:25:08.489  2018-09-24 00:00:00.000   \n",
       "\n",
       "             status  issued  high_confidence_fraud_indicator  suspected_fraud  \\\n",
       "4         cancelled       0                                0                0   \n",
       "61         paid_off       1                                0                0   \n",
       "111        paid_off       1                                0                0   \n",
       "140        paid_off       1                                0                0   \n",
       "161       cancelled       0                                0                0   \n",
       "...             ...     ...                              ...              ...   \n",
       "502569     rejected       0                                0                0   \n",
       "502634  charged_off       1                                0                0   \n",
       "502635      current       1                                0                0   \n",
       "502640         late       1                                0                0   \n",
       "502659    cancelled       0                                0                0   \n",
       "\n",
       "        score_5_old  score_5_new   score_5 state          payment_method  \\\n",
       "4          0.004678          NaN  0.004678    SC                     ach   \n",
       "61         0.002656          NaN  0.002656    FL                     ach   \n",
       "111        0.019646          NaN  0.019646    MN                     ach   \n",
       "140        0.003395          NaN  0.003395    OH                     ach   \n",
       "161        0.049705          NaN  0.049705    TX                     ach   \n",
       "...             ...          ...       ...   ...                     ...   \n",
       "502569     0.003565          NaN  0.003565    CA  remotely_created_check   \n",
       "502634     0.018444          NaN  0.018444    MI                     ach   \n",
       "502635     0.005252          NaN  0.005252    NC                     ach   \n",
       "502640     0.010588          NaN  0.010588    IL                     ach   \n",
       "502659     0.004640          NaN  0.004640    MD                     ach   \n",
       "\n",
       "        loan_amount product_type risk_summary_identity_high  \\\n",
       "4            2000.0  installment                       True   \n",
       "61           3400.0  installment                      False   \n",
       "111          7900.0    refinance                       None   \n",
       "140         12500.0  installment                       True   \n",
       "161          9300.0  installment                       True   \n",
       "...             ...          ...                        ...   \n",
       "502569      25000.0  installment                      False   \n",
       "502634       2400.0  installment                      False   \n",
       "502635       3000.0  installment                      False   \n",
       "502640       3200.0  installment                      False   \n",
       "502659       4000.0  installment                      False   \n",
       "\n",
       "       risk_summary_identity_medium risk_summary_identity_low  prediction  \n",
       "4                             False                     False           0  \n",
       "61                            False                      True           0  \n",
       "111                            None                      None           0  \n",
       "140                           False                     False           0  \n",
       "161                           False                     False           0  \n",
       "...                             ...                       ...         ...  \n",
       "502569                         True                     False           0  \n",
       "502634                        False                      True           0  \n",
       "502635                        False                      True           0  \n",
       "502640                        False                      True           0  \n",
       "502659                        False                      True           0  \n",
       "\n",
       "[26347 rows x 18 columns]"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_first30"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.332906862189837"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#false positives, true negatives for false positive rate\n",
    "    true_positives = (dframe[ytrue] * dframe[ypred]).sum()\n",
    "    false_positives = ((1-dframe[ytrue]) * dframe[ypred]).sum()\n",
    "    false_negatives =  (dframe[ytrue] * (1-dframe[ypred])).sum()\n",
    "    true_negatives = ((1-dframe[ytrue]) * (1-dframe[ypred])).sum()\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",
    "    false_positive_rate = false_positives/(false_positives+true_negatives)\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",
    "    fraudmissed_dollar = (dframe[amount]*dframe[ytrue]*(1-dframe[ypred])).sum()\n",
    "    fraudmissed_dollar_rate = 100*(dframe[amount]*dframe[ytrue]*(1-dframe[ypred])).sum()/(dframe[amount]*dframe[ytrue]).sum()\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"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "#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_overall = dframe.query('{0} > @modeltrain_date_start'.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_last30 = precision_score(y_true = data_last30[ytrue], y_pred = data_last30[ypred], pos_label = 1)\n",
    "    precision_overall = precision_score(y_true = data_overall[ytrue], y_pred = data_overall[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_last30 = recall_score(y_true = data_last30[ytrue], y_pred = data_last30[ypred], pos_label = 1)\n",
    "    recall_overall = recall_score(y_true = data_overall[ytrue], y_pred = data_overall[ypred], pos_label = 1)\n",
    "    recall_prev30 = recall_score(y_true = data_prev30[ytrue], y_pred = data_prev30[ypred], pos_label = 1) \n",
    "    \n",
    "    #False positive rates\n",
    "    fp_last30 = ((1-data_last30[ytrue]) * data_last30[ypred]).sum()/(((1-data_last30[ytrue]) * data_last30[ypred]).sum() + ((1-data_overall[ytrue]) * (1-data_overall[ypred])).sum())\n",
    "    fp_overall = ((1-data_overall[ytrue]) * data_overall[ypred]).sum()/(((1-data_overall[ytrue]) * data_overall[ypred]).sum() + ((1-data_overall[ytrue]) * (1-data_overall[ypred])).sum())\n",
    "    fp_prev30 = ((1-data_prev30[ytrue]) * data_prev30[ypred]).sum()/(((1-data_prev30[ytrue]) * data_prev30[ypred]).sum() + ((1-data_prev30[ytrue]) * (1-data_prev30[ypred])).sum())\n",
    "\n",
    "    #F1 score\n",
    "    f1_last30 = f1_score(y_true = data_last30[ytrue], y_pred = data_last30[ypred], pos_label = 1)\n",
    "    f1_overall = f1_score(y_true = data_overall[ytrue], y_pred = data_overall[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_last30 = average_precision_score(y_true = data_last30[ytrue], y_score = data_last30[scores], pos_label = 1)\n",
    "    aucpr_overall = average_precision_score(y_true = data_overall[ytrue], y_score = data_overall[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_last30 = roc_auc_score(y_true = data_last30[ytrue], y_score = data_last30[scores])\n",
    "    aucroc_overall = roc_auc_score(y_true = data_overall[ytrue], y_score = data_overall[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_last30 = data_last30[ytrue].sum()/len(data_last30.index)\n",
    "    fraudrate_overall = data_overall[ytrue].sum()/len(data_overall.index)\n",
    "    fraudrate_prev30 = data_prev30[ytrue].sum()/len(data_prev30.index)\n",
    "    \n",
    "    #avg score\n",
    "    avgscore_last30 = data_last30[scores].sum()/len(data_last30.index)\n",
    "    avgscore_overall = data_overall[scores].sum()/len(data_overall.index)\n",
    "    avgscore_prev30 = data_prev30[scores].sum()/len(data_prev30.index)\n",
    "\n",
    "    #$ value of fraud missed\n",
    "    fraudmissed_dollar_last30 = (data_last30[amount]*data_last30[ytrue]*(1-data_last30[ypred])).sum()\n",
    "    fraudmissed_dollar_overall = (data_overall[amount]*data_overall[ytrue]*(1-data_overall[ypred])).sum()\n",
    "    fraudmissed_dollar_prev30 = (data_prev30[amount]*data_prev30[ytrue]*(1-data_prev30[ypred])).sum()\n",
    "    \n",
    "    # $ value fraud rate\n",
    "    fraudmissed_dollar_rate_last30 = 100*(data_last30[amount]*data_last30[ytrue]*(1-data_last30[ypred])).sum()/(data_last30[amount]*data_last30[ytrue]).sum()\n",
    "    fraudmissed_dollar_rate_overall = 100*(data_overall[amount]*data_overall[ytrue]*(1-data_overall[ypred])).sum()/(data_overall[amount]*data_overall[ytrue]).sum()\n",
    "    fraudmissed_dollar_rate_prev30 = 100*(data_prev30[amount]*data_prev30[ytrue]*(1-data_prev30[ypred])).sum()/(data_prev30[amount]*data_prev30[ytrue]).sum()\n",
    "\n",
    "    \n",
    "\n",
    "    output = {\"metric\": ['precision', 'recall','f1score', 'auc_pr', 'auc_roc', 'fraudrate', 'avg_score', 'falsepositive_rate', 'fraudmissed_dollar', 'fraudmissed_dollar_rate'],\n",
    "             \"last30_values\":[precision_last30, recall_last30, f1_last30, aucpr_last30, aucroc_last30, fraudrate_last30, avgscore_last30, fp_last30, fraudmissed_dollar_last30, fraudmissed_dollar_rate_last30],\n",
    "             \"overall_values\":[precision_overall, recall_overall, f1_overall, aucpr_overall, aucroc_overall, fraudrate_overall, avgscore_overall, fp_overall, fraudmissed_dollar_overall, fraudmissed_dollar_rate_overall],\n",
    "             \"prev30_values\":[precision_prev30, recall_prev30, f1_prev30, aucpr_prev30, aucroc_prev30, fraudrate_prev30, avgscore_prev30, fp_prev30, fraudmissed_dollar_prev30, fraudmissed_dollar_rate_prev30]}    \n",
    "        \n",
    "    return output\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "tables_data = pd.DataFrame.from_dict(values_for_cells(df))\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>metric</th>\n",
       "      <th>current_values</th>\n",
       "      <th>initial_values</th>\n",
       "      <th>prev30_values</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>precision</td>\n",
       "      <td>0.160043</td>\n",
       "      <td>1.466901e-01</td>\n",
       "      <td>0.201691</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>recall</td>\n",
       "      <td>0.768041</td>\n",
       "      <td>6.460334e-01</td>\n",
       "      <td>0.738938</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>f1score</td>\n",
       "      <td>0.264889</td>\n",
       "      <td>2.390914e-01</td>\n",
       "      <td>0.316888</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>auc_pr</td>\n",
       "      <td>0.385375</td>\n",
       "      <td>2.993749e-01</td>\n",
       "      <td>0.426599</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>auc_roc</td>\n",
       "      <td>0.908301</td>\n",
       "      <td>8.514888e-01</td>\n",
       "      <td>0.906391</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>fraudrate</td>\n",
       "      <td>0.017998</td>\n",
       "      <td>2.067618e-02</td>\n",
       "      <td>0.021489</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>avg_score</td>\n",
       "      <td>0.034580</td>\n",
       "      <td>3.223616e-02</td>\n",
       "      <td>0.032977</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>fraudmissed_dollar</td>\n",
       "      <td>364160.080000</td>\n",
       "      <td>2.824432e+07</td>\n",
       "      <td>441717.420000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>fraudmissed_dollar_rate</td>\n",
       "      <td>23.274070</td>\n",
       "      <td>3.468574e+01</td>\n",
       "      <td>25.483145</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    metric  current_values  initial_values  prev30_values\n",
       "0                precision        0.160043    1.466901e-01       0.201691\n",
       "1                   recall        0.768041    6.460334e-01       0.738938\n",
       "2                  f1score        0.264889    2.390914e-01       0.316888\n",
       "3                   auc_pr        0.385375    2.993749e-01       0.426599\n",
       "4                  auc_roc        0.908301    8.514888e-01       0.906391\n",
       "5                fraudrate        0.017998    2.067618e-02       0.021489\n",
       "6                avg_score        0.034580    3.223616e-02       0.032977\n",
       "7       fraudmissed_dollar   364160.080000    2.824432e+07  441717.420000\n",
       "8  fraudmissed_dollar_rate       23.274070    3.468574e+01      25.483145"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tables_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "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",
    "    modeltrain_date_start = datetime.strptime(MODEL_START_DATE, \"%Y-%m-%d\")\n",
    "    modeltrain_date_end = (modeltrain_date_start + timedelta(days = 60)).strftime(\"%Y-%m-%d\")\n",
    "    \n",
    "    #creating different datasets for the different time periods\n",
    "    \n",
    "    #dataset 1 - 60 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",
    "    #False positive rate values\n",
    "    fp_initial = ((1-data_first30[ytrue]) * data_first30[ypred]).sum()/(((1-data_first30[ytrue]) * data_first30[ypred]).sum() + ((1-data_first30[ytrue]) * (1-data_first30[ypred])).sum())\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",
    "    fraudmissed_dollar_initial = (data_first30[amount]*data_first30[ytrue]*(1-data_first30[ypred])).sum()\n",
    "\n",
    "    #$ value of fraud missed\n",
    "    fraudmissed_dollar_rate_initial = 100*(data_first30[amount]*data_first30[ytrue]*(1-data_first30[ypred])).sum()/(data_first30[amount]*data_first30[ytrue]).sum()\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",
    "                              falsepositive_rate_baseline =  fp_initial,                     \n",
    "                              fraudmissed_dollar_baseline = fraudmissed_dollar_initial,\n",
    "                              fraudmissed_dollar_rate_baseline = fraudmissed_dollar_rate_initial\n",
    "                              )\n",
    "    \n",
    "    return baseline_dataframe\n",
    "    \n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "test = pd.DataFrame(df[WEEKSTART_COL].unique()).rename(columns={0: WEEKSTART_COL}).sort_values(by = WEEKSTART_COL)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "baseline_data = create_baseline_data(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>entered_lp_week</th>\n",
       "      <th>precision_baseline</th>\n",
       "      <th>recall_baseline</th>\n",
       "      <th>f1_baseline</th>\n",
       "      <th>aucpr_baseline</th>\n",
       "      <th>aucroc_baseline</th>\n",
       "      <th>fraudrate_baseline</th>\n",
       "      <th>avgscore_baseline</th>\n",
       "      <th>falsepositive_rate_baseline</th>\n",
       "      <th>fraudmissed_dollar_baseline</th>\n",
       "      <th>fraudmissed_dollar_rate_baseline</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>105</th>\n",
       "      <td>2018-09-10 00:00:00.000</td>\n",
       "      <td>0.123588</td>\n",
       "      <td>0.487273</td>\n",
       "      <td>0.197168</td>\n",
       "      <td>0.193705</td>\n",
       "      <td>0.78309</td>\n",
       "      <td>0.020467</td>\n",
       "      <td>0.027373</td>\n",
       "      <td>0.072199</td>\n",
       "      <td>4441000.0</td>\n",
       "      <td>51.307231</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55</th>\n",
       "      <td>2018-09-17 00:00:00.000</td>\n",
       "      <td>0.123588</td>\n",
       "      <td>0.487273</td>\n",
       "      <td>0.197168</td>\n",
       "      <td>0.193705</td>\n",
       "      <td>0.78309</td>\n",
       "      <td>0.020467</td>\n",
       "      <td>0.027373</td>\n",
       "      <td>0.072199</td>\n",
       "      <td>4441000.0</td>\n",
       "      <td>51.307231</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>2018-09-24 00:00:00.000</td>\n",
       "      <td>0.123588</td>\n",
       "      <td>0.487273</td>\n",
       "      <td>0.197168</td>\n",
       "      <td>0.193705</td>\n",
       "      <td>0.78309</td>\n",
       "      <td>0.020467</td>\n",
       "      <td>0.027373</td>\n",
       "      <td>0.072199</td>\n",
       "      <td>4441000.0</td>\n",
       "      <td>51.307231</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>2018-10-01 00:00:00.000</td>\n",
       "      <td>0.123588</td>\n",
       "      <td>0.487273</td>\n",
       "      <td>0.197168</td>\n",
       "      <td>0.193705</td>\n",
       "      <td>0.78309</td>\n",
       "      <td>0.020467</td>\n",
       "      <td>0.027373</td>\n",
       "      <td>0.072199</td>\n",
       "      <td>4441000.0</td>\n",
       "      <td>51.307231</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>2018-10-08 00:00:00.000</td>\n",
       "      <td>0.123588</td>\n",
       "      <td>0.487273</td>\n",
       "      <td>0.197168</td>\n",
       "      <td>0.193705</td>\n",
       "      <td>0.78309</td>\n",
       "      <td>0.020467</td>\n",
       "      <td>0.027373</td>\n",
       "      <td>0.072199</td>\n",
       "      <td>4441000.0</td>\n",
       "      <td>51.307231</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69</th>\n",
       "      <td>2020-09-14 00:00:00.000</td>\n",
       "      <td>0.123588</td>\n",
       "      <td>0.487273</td>\n",
       "      <td>0.197168</td>\n",
       "      <td>0.193705</td>\n",
       "      <td>0.78309</td>\n",
       "      <td>0.020467</td>\n",
       "      <td>0.027373</td>\n",
       "      <td>0.072199</td>\n",
       "      <td>4441000.0</td>\n",
       "      <td>51.307231</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>91</th>\n",
       "      <td>2020-09-21 00:00:00.000</td>\n",
       "      <td>0.123588</td>\n",
       "      <td>0.487273</td>\n",
       "      <td>0.197168</td>\n",
       "      <td>0.193705</td>\n",
       "      <td>0.78309</td>\n",
       "      <td>0.020467</td>\n",
       "      <td>0.027373</td>\n",
       "      <td>0.072199</td>\n",
       "      <td>4441000.0</td>\n",
       "      <td>51.307231</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89</th>\n",
       "      <td>2020-09-28 00:00:00.000</td>\n",
       "      <td>0.123588</td>\n",
       "      <td>0.487273</td>\n",
       "      <td>0.197168</td>\n",
       "      <td>0.193705</td>\n",
       "      <td>0.78309</td>\n",
       "      <td>0.020467</td>\n",
       "      <td>0.027373</td>\n",
       "      <td>0.072199</td>\n",
       "      <td>4441000.0</td>\n",
       "      <td>51.307231</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>2020-10-05 00:00:00.000</td>\n",
       "      <td>0.123588</td>\n",
       "      <td>0.487273</td>\n",
       "      <td>0.197168</td>\n",
       "      <td>0.193705</td>\n",
       "      <td>0.78309</td>\n",
       "      <td>0.020467</td>\n",
       "      <td>0.027373</td>\n",
       "      <td>0.072199</td>\n",
       "      <td>4441000.0</td>\n",
       "      <td>51.307231</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106</th>\n",
       "      <td>2020-10-12 00:00:00.000</td>\n",
       "      <td>0.123588</td>\n",
       "      <td>0.487273</td>\n",
       "      <td>0.197168</td>\n",
       "      <td>0.193705</td>\n",
       "      <td>0.78309</td>\n",
       "      <td>0.020467</td>\n",
       "      <td>0.027373</td>\n",
       "      <td>0.072199</td>\n",
       "      <td>4441000.0</td>\n",
       "      <td>51.307231</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>110 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             entered_lp_week  precision_baseline  recall_baseline  \\\n",
       "105  2018-09-10 00:00:00.000            0.123588         0.487273   \n",
       "55   2018-09-17 00:00:00.000            0.123588         0.487273   \n",
       "22   2018-09-24 00:00:00.000            0.123588         0.487273   \n",
       "80   2018-10-01 00:00:00.000            0.123588         0.487273   \n",
       "23   2018-10-08 00:00:00.000            0.123588         0.487273   \n",
       "..                       ...                 ...              ...   \n",
       "69   2020-09-14 00:00:00.000            0.123588         0.487273   \n",
       "91   2020-09-21 00:00:00.000            0.123588         0.487273   \n",
       "89   2020-09-28 00:00:00.000            0.123588         0.487273   \n",
       "25   2020-10-05 00:00:00.000            0.123588         0.487273   \n",
       "106  2020-10-12 00:00:00.000            0.123588         0.487273   \n",
       "\n",
       "     f1_baseline  aucpr_baseline  aucroc_baseline  fraudrate_baseline  \\\n",
       "105     0.197168        0.193705          0.78309            0.020467   \n",
       "55      0.197168        0.193705          0.78309            0.020467   \n",
       "22      0.197168        0.193705          0.78309            0.020467   \n",
       "80      0.197168        0.193705          0.78309            0.020467   \n",
       "23      0.197168        0.193705          0.78309            0.020467   \n",
       "..           ...             ...              ...                 ...   \n",
       "69      0.197168        0.193705          0.78309            0.020467   \n",
       "91      0.197168        0.193705          0.78309            0.020467   \n",
       "89      0.197168        0.193705          0.78309            0.020467   \n",
       "25      0.197168        0.193705          0.78309            0.020467   \n",
       "106     0.197168        0.193705          0.78309            0.020467   \n",
       "\n",
       "     avgscore_baseline  falsepositive_rate_baseline  \\\n",
       "105           0.027373                     0.072199   \n",
       "55            0.027373                     0.072199   \n",
       "22            0.027373                     0.072199   \n",
       "80            0.027373                     0.072199   \n",
       "23            0.027373                     0.072199   \n",
       "..                 ...                          ...   \n",
       "69            0.027373                     0.072199   \n",
       "91            0.027373                     0.072199   \n",
       "89            0.027373                     0.072199   \n",
       "25            0.027373                     0.072199   \n",
       "106           0.027373                     0.072199   \n",
       "\n",
       "     fraudmissed_dollar_baseline  fraudmissed_dollar_rate_baseline  \n",
       "105                    4441000.0                         51.307231  \n",
       "55                     4441000.0                         51.307231  \n",
       "22                     4441000.0                         51.307231  \n",
       "80                     4441000.0                         51.307231  \n",
       "23                     4441000.0                         51.307231  \n",
       "..                           ...                               ...  \n",
       "69                     4441000.0                         51.307231  \n",
       "91                     4441000.0                         51.307231  \n",
       "89                     4441000.0                         51.307231  \n",
       "25                     4441000.0                         51.307231  \n",
       "106                    4441000.0                         51.307231  \n",
       "\n",
       "[110 rows x 11 columns]"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "baseline_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "precision_initial = precision_score(y_true = data_first30[YTRUE_COL], 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"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>entered_lp_week</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>106</th>\n",
       "      <td>2018-09-10 00:00:00.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2018-09-17 00:00:00.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78</th>\n",
       "      <td>2018-09-24 00:00:00.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83</th>\n",
       "      <td>2018-10-01 00:00:00.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>2018-10-08 00:00:00.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75</th>\n",
       "      <td>2020-09-07 00:00:00.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2020-09-14 00:00:00.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2020-09-21 00:00:00.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66</th>\n",
       "      <td>2020-09-28 00:00:00.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94</th>\n",
       "      <td>2020-10-05 00:00:00.000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>109 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             entered_lp_week\n",
       "106  2018-09-10 00:00:00.000\n",
       "4    2018-09-17 00:00:00.000\n",
       "78   2018-09-24 00:00:00.000\n",
       "83   2018-10-01 00:00:00.000\n",
       "48   2018-10-08 00:00:00.000\n",
       "..                       ...\n",
       "75   2020-09-07 00:00:00.000\n",
       "14   2020-09-14 00:00:00.000\n",
       "8    2020-09-21 00:00:00.000\n",
       "66   2020-09-28 00:00:00.000\n",
       "94   2020-10-05 00:00:00.000\n",
       "\n",
       "[109 rows x 1 columns]"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test.assign(precision_baseline = precision_initial)\n",
    "\n",
    "df.assign(column_new_1=np.nan, column_new_2='dogs', column_new_3=3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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
}