ML_database_2_rev.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "68b5acf5",
   "metadata": {},
   "source": [
    "## This Notebook:\n",
    "\n",
    "1) links to the AWS database\n",
    "\n",
    "2) cleans the AWS database\n",
    "\n",
    "3) creates a local path for the output Cases_Cleaned/ML_cases.csv\n",
    "\n",
    "4) creates a local path for the output Deaths_Cleaned/ML_deaths.csv\n",
    "\n",
    "5) ML model for cases reads ML_cases.csv\n",
    "\n",
    "6) ML model for cases saves output to user-defined location\n",
    "\n",
    "5) ML model for deaths reads ML_deaths.csv\n",
    "\n",
    "6) ML model for deaths saves output to user-defined location\n",
    "\n",
    "7) creates PostgresSQL database for machine learning models\n",
    "\n",
    "8) option to write model results information directly to a csv file\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f9c4d477",
   "metadata": {},
   "source": [
    "<div style=\"background:lightblue\">\n",
    "\n",
    "# Navigator\n",
    "\n",
    "## [Start](#Notebook-and-Run-Count-Information)\n",
    "\n",
    "## [Database Cleaner](#AWS-Database-Cleaner)\n",
    "\n",
    "## [Machine Learning Model](#TITLE:-cases)\n",
    "\n",
    "## [Database Structure](#PostgresSQL-Database)\n",
    "\n",
    "## [Export to Database](#Dataframes-to-PostgreSQL-tables)\n",
    "\n",
    "</div>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6c07899c",
   "metadata": {},
   "source": [
    "## Notebook and Run Count Information "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "31d5bdca",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'notebook': 19}"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#results database information\n",
    "# ********************************\n",
    "\n",
    "name_nb = \"ML_pn_rev1\"\n",
    "\n",
    "run_nb = {}\n",
    "run_counter = 18\n",
    "run_counter = run_counter + 1\n",
    "run_nb['notebook'] = run_counter\n",
    "run_nb\n",
    "\n",
    "# ********************************"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "144c70bf",
   "metadata": {},
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "e2fa06c8",
   "metadata": {},
   "source": [
    "## AWS Database Cleaner\n",
    "\n",
    "\n",
    "**RELEVANT DATAFRAMES:  df, df_cases, df_deaths**\n",
    "\n",
    "**FILE: vax_cases_death.csv**\n",
    "\n",
    "**SOURCE:  AWS download from SQL database**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "efdee1e0",
   "metadata": {},
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "3425fa45",
   "metadata": {},
   "outputs": [],
   "source": [
    "# import dependencies\n",
    "\n",
    "import os\n",
    "import pandas as pd\n",
    "import re\n",
    "from sklearn.preprocessing import OneHotEncoder, LabelEncoder\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "567e7c18",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Clean file, save information for results database, make csv files for machine learning model\n",
    "\n",
    "# read file\n",
    "\n",
    "file_path = \"https://initial-datasets.s3.amazonaws.com/vax_cases_deaths.csv\"\n",
    "df = pd.read_csv(file_path)\n",
    "\n",
    "\n",
    "# results database information\n",
    "# *********************************\n",
    "\n",
    "if file_path == \"https://initial-datasets.s3.amazonaws.com/vax_cases_deaths.csv\":\n",
    "    source_db = \"AWS database csv file\"\n",
    "    file_id = file_path\n",
    "\n",
    "# the statistics dataset used for the label column (name_statsfile)\n",
    "\n",
    "name_statsfile = \"stats_Xb_cases_ds\"\n",
    "\n",
    "# the statistic used for the setting the label column (name_statistic)\n",
    "\n",
    "name_statistic = \"mean\"\n",
    "\n",
    "# **********************************\n",
    " \n",
    "\n",
    "# Location to string\n",
    "\n",
    "df[\"location\"] = df[\"location\"].astype(str)\n",
    "\n",
    "\n",
    "\n",
    "# Delete the column \"submission_date.\"\n",
    "\n",
    "df.drop(columns = [\"date\"], inplace = True)\n",
    "\n",
    "\n",
    "# Add the label columns to df. \n",
    "\n",
    "df[\"2020_mean_cases\"] = 0\n",
    "df[\"2020_mean_deaths\"] = 0\n",
    "\n",
    "\n",
    "# Make lists for mean values\n",
    "\n",
    "# mean cases for 2020 and for 2020 and 2021 combined\n",
    "\n",
    "mean_cases = [90135, 325018]\n",
    "\n",
    "mean_cases_value = mean_cases[1]\n",
    "\n",
    "# mean deaths for 2020 and for 2020 and 2021 combined\n",
    "\n",
    "mean_deaths = [2634, 6365]\n",
    "\n",
    "mean_deaths_value = mean_deaths[1]\n",
    "\n",
    "\n",
    "\n",
    "# Populate \"2020_mean_cases\" with 1 or 0 \n",
    "# Populate \"2020_mean_deaths\" with 1 or 0 \n",
    "\n",
    "# cases\n",
    "\n",
    "for index, row in df.iterrows():\n",
    "    x = row[\"total_cases\"]\n",
    "    if x >= int(mean_cases_value):\n",
    "        df.loc[index, \"2020_mean_cases\"]=1\n",
    "    else:\n",
    "        df.loc[index, \"2020_mean_cases\"]=0\n",
    "\n",
    "# deaths\n",
    "\n",
    "for index, row in df.iterrows():\n",
    "    x = row[\"total_deaths\"]\n",
    "    if x >= int(mean_deaths_value):\n",
    "        df.loc[index, \"2020_mean_deaths\"]=1\n",
    "    else:\n",
    "        df.loc[index, \"2020_mean_deaths\"]=0\n",
    "\n",
    "#delete columns \"total_cases\" and \"total_deaths\"\n",
    "\n",
    "df.drop(columns = [\"total_cases\", \"total_deaths\"], inplace = True)\n",
    "\n",
    "\n",
    "\n",
    "# Perform OneHotEncoding\n",
    "\n",
    "# import dependencies\n",
    "\n",
    "obj_list = df.dtypes[df.dtypes == \"object\"].index.to_list()\n",
    "\n",
    "# apply OneHotEncoder to objects\n",
    "\n",
    "enc = OneHotEncoder(sparse = False)\n",
    "encoded_df = pd.DataFrame(enc.fit_transform(df[obj_list]))\n",
    "encoded_df.columns = enc.get_feature_names(obj_list)\n",
    "\n",
    "df = df.merge(encoded_df, left_index = True, right_index = True)\n",
    "df = df.drop(obj_list, 1)\n",
    "\n",
    "\n",
    "\n",
    "# Make dataframes for cases and deaths\n",
    "\n",
    "# make a new dataframe for cases only.\n",
    "\n",
    "# first reorder columns\n",
    "\n",
    "columns_cases = ['mmwr_week', 'year', 'distributed', 'administered', '2020_mean_cases',\n",
    "       '2020_mean_deaths', 'location_AK', 'location_AL', 'location_AR',\n",
    "       'location_AZ', 'location_CA', 'location_CO', 'location_CT',\n",
    "       'location_DC', 'location_DE', 'location_FL', 'location_GA',\n",
    "       'location_HI', 'location_IA', 'location_ID', 'location_IL',\n",
    "       'location_IN', 'location_KS', 'location_KY', 'location_LA',\n",
    "       'location_MA', 'location_MD', 'location_ME', 'location_MI',\n",
    "       'location_MN', 'location_MO', 'location_MS', 'location_MT',\n",
    "       'location_NC', 'location_ND', 'location_NE', 'location_NH',\n",
    "       'location_NJ', 'location_NM', 'location_NV', 'location_NY',\n",
    "       'location_OH', 'location_OK', 'location_OR', 'location_PA',\n",
    "       'location_RI', 'location_SC', 'location_SD', 'location_TN',\n",
    "       'location_TX', 'location_UT', 'location_VA', 'location_VI',\n",
    "       'location_VT', 'location_WA', 'location_WI', 'location_WV',\n",
    "       'location_WY']\n",
    "\n",
    "columns_cases_new = [ 'year', 'mmwr_week', 'distributed', 'administered', \n",
    "        'location_AK', 'location_AL', 'location_AR',\n",
    "       'location_AZ', 'location_CA', 'location_CO', 'location_CT',\n",
    "       'location_DC', 'location_DE', 'location_FL', 'location_GA',\n",
    "       'location_HI', 'location_IA', 'location_ID', 'location_IL',\n",
    "       'location_IN', 'location_KS', 'location_KY', 'location_LA',\n",
    "       'location_MA', 'location_MD', 'location_ME', 'location_MI',\n",
    "       'location_MN', 'location_MO', 'location_MS', 'location_MT',\n",
    "       'location_NC', 'location_ND', 'location_NE', 'location_NH',\n",
    "       'location_NJ', 'location_NM', 'location_NV', 'location_NY',\n",
    "       'location_OH', 'location_OK', 'location_OR', 'location_PA',\n",
    "       'location_RI', 'location_SC', 'location_SD', 'location_TN',\n",
    "       'location_TX', 'location_UT', 'location_VA', 'location_VI',\n",
    "       'location_VT', 'location_WA', 'location_WI', 'location_WV',\n",
    "       'location_WY', '2020_mean_cases', '2020_mean_deaths']\n",
    "\n",
    "df = df.reindex(columns = columns_cases_new )\n",
    "\n",
    "# next drop out death-related column for df_cases\n",
    "\n",
    "df_cases = df.copy()\n",
    "df_cases.drop(columns = ['2020_mean_deaths'], inplace = True)\n",
    "\n",
    "# next drop out cases-related column for df_deaths\n",
    "\n",
    "df_deaths = df.copy()\n",
    "df_deaths.drop(columns = ['2020_mean_cases'], inplace = True)\n",
    "\n",
    "\n",
    "\n",
    "# Save dataframes as csv files for folders Cases_Cleaned and Deaths_Cleaned\n",
    "\n",
    "# Save df_cases as csv file.\n",
    "\n",
    "os.makedirs(\"Cases_Cleaned/\",exist_ok=True)\n",
    "df_cases.to_csv('Cases_Cleaned/ML_cases_vcd.csv', index = False)\n",
    "\n",
    "# Save df_deaths as csv file.\n",
    "\n",
    "os.makedirs(\"Deaths_Cleaned/\",exist_ok=True)\n",
    "df_deaths.to_csv('Deaths_Cleaned/ML_deaths_vcd.csv', index = False)\n",
    "\n",
    "\n",
    "\n",
    "#results database information\n",
    "# *********************************\n",
    "\n",
    "casesfile_id = f\"ML_cases_vcd.csv_{run_counter}\"\n",
    "deathsfile_id = f\"ML_deaths_vcd.csv_{run_counter}\"\n",
    "\n",
    "# *********************************\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f503cd1d",
   "metadata": {},
   "source": [
    "## Machine Learning Model\n",
    "\n",
    "### TITLE: cases\n",
    "\n",
    "**MODEL: RandomForest**\n",
    "\n",
    "**FILE:  Cases_Cleaned/ML_cases_vcd.csv**\n",
    "\n",
    "**RELEVANT DATAFRAMES:  CR_cases_df, df_importance_cases**\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "f7171e8c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Initial imports\n",
    "\n",
    "import pandas as pd\n",
    "from path import Path\n",
    "from sklearn.ensemble import RandomForestClassifier\n",
    "from sklearn.preprocessing import StandardScaler\n",
    "from sklearn.model_selection import train_test_split\n",
    "from sklearn.metrics import confusion_matrix, accuracy_score, classification_report\n",
    "from sklearn import metrics\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "5715aa96",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Define input parameters, save information for the results database\n",
    "# Same input parameters are used for both the cases and deaths machine learning parameters\n",
    "#\n",
    "# Functions defined here are input_deck, input_params, RandomForestClassifier\n",
    "\n",
    "\n",
    "\n",
    "#results database information\n",
    "# **********************************\n",
    "\n",
    "type_model_cases = \"Random Forest\"\n",
    "name_model_cases = \"cases\"\n",
    "\n",
    "# *********************************\n",
    "\n",
    "# Loading data\n",
    "file_path = Path(\"Cases_Cleaned/ML_cases_vcd.csv\")\n",
    "df_cases = pd.read_csv(file_path)\n",
    "\n",
    "# Define the features set.\n",
    "X = df_cases.copy()\n",
    "X = X.drop(\"2020_mean_cases\", axis=1)\n",
    "\n",
    "# Define the target set.\n",
    "y = df_cases[\"2020_mean_cases\"].ravel()\n",
    "\n",
    "# Splitting into Train and Test sets.\n",
    "X_train, X_test, y_train, y_test = train_test_split(X, y, random_state=78)\n",
    "\n",
    "# Creating a StandardScaler instance.\n",
    "scaler = StandardScaler()\n",
    "\n",
    "# Fitting the Standard Scaler with the training data.\n",
    "X_scaler = scaler.fit(X_train)\n",
    "\n",
    "# Scaling the data.\n",
    "X_train_scaled = X_scaler.transform(X_train)\n",
    "X_test_scaled = X_scaler.transform(X_test)\n",
    "\n",
    "# set input deck to be used for both cases and deaths\n",
    "\n",
    "def input_deck(n):\n",
    "    \n",
    "# format is [n_estimators, random_state, criterion, max_depth, max_features, min_impurity_decrease, oob_score]\n",
    "\n",
    "    rf_input = [\n",
    "        \n",
    "        [128, 78, 'gini', None, 'auto', 0.0, False],\n",
    "        [128, 78, 'gini', None, 'auto', 0.0, True],\n",
    "        [128, 78, 'entropy', None, 'auto', 0.0, False],\n",
    "        [128, 78, 'entropy', None, 'auto', 0.0, True],\n",
    "        [128, 78, 'gini', 10, 'sqrt', 0.0, False],\n",
    "        [128, 78, 'gini', 10, 'sqrt', 0.0, True],\n",
    "        [128, 78, 'entropy', 10, 'sqrt', 0.0, False],\n",
    "        [128, 78, 'entropy', 10, 'sqrt', 0.0, True],\n",
    "        [128, 78, 'gini', None, 'sqrt', 0.02, False],\n",
    "        [128, 78, 'gini', None, 'sqrt', 0.02, True],\n",
    "        [128, 78, 'entropy', None, 'sqrt', 0.02, False],\n",
    "        [128, 78, 'entropy', None, 'sqrt', 0.02, True],\n",
    "        [128, 78, 'entropy', 10, 'sqrt', 0.0, True],\n",
    "        [128, 78, 'gini', None, 'sqrt', 0.5, False],\n",
    "        [128, 78, 'gini', None, 'sqrt', 0.5, True]\n",
    "        \n",
    "    ]\n",
    "    \n",
    "    rf_input_params = rf_input[n]\n",
    "    \n",
    "    return rf_input_params\n",
    "\n",
    "def input_params(n):\n",
    "\n",
    "    n_estimators = input_deck(n)[0]\n",
    "    random_state = input_deck(n)[1]\n",
    "    criterion = input_deck(n)[2]\n",
    "    max_depth = input_deck(n)[3]\n",
    "    max_features = input_deck(n)[4]\n",
    "    min_impurity_decrease = input_deck(n)[5]\n",
    "    oob_score = input_deck(n)[6]\n",
    "    \n",
    "    return n_estimators, random_state, criterion, max_depth, max_features, min_impurity_decrease, oob_score \n",
    "\n",
    "# set the input parameters\n",
    "\n",
    "n_estimators, random_state, criterion, max_depth, max_features, min_impurity_decrease, oob_score = input_params(5)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "f0083fb5",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a random forest classifier.\n",
    "rf_model = RandomForestClassifier(n_estimators=n_estimators, random_state=random_state,\n",
    "                                  criterion=criterion,\n",
    "                                  max_depth=max_depth, max_features =max_features,\n",
    "                                  min_impurity_decrease = min_impurity_decrease,\n",
    "                                 oob_score = oob_score) \n",
    "\n",
    "\n",
    "#results database information\n",
    "# ************************************************************\n",
    "\n",
    "run_dt = pd.to_datetime('now').strftime('%Y-%m-%d %H:%M:%S')\n",
    "\n",
    "#parameter names used in the arguments\n",
    "# n_estimators=128\n",
    "# random_state=78\n",
    "# criterion = 'gini' or 'entropy'\n",
    "# max_depth = None or 10\n",
    "# max_features = 'auto' or 'sqrt'\n",
    "# min_impurity_decrease = 0.0 or a fraction\n",
    "# oob_score = False or True\n",
    "\n",
    "rf_pars = rf_model.get_params()\n",
    "rf_n_estimators = rf_pars['n_estimators']\n",
    "rf_random_state = rf_pars['random_state']\n",
    "rf_criterion = rf_pars['criterion']\n",
    "rf_max_depth = rf_pars['max_depth']\n",
    "rf_max_features = rf_pars['max_features']\n",
    "rf_min_impurity_decrease = rf_pars['min_impurity_decrease']\n",
    "rf_oob_score = rf_pars['oob_score']\n",
    "\n",
    "\n",
    "par_name_1 = f\"n_estimators={rf_n_estimators}\"\n",
    "par_name_2 = f\"random_state={rf_random_state}\"\n",
    "par_name_3 = f\"criterion={rf_criterion}\"\n",
    "par_name_4 = f\"max_depth={rf_max_depth}\"\n",
    "par_name_5 = f\"max_features={rf_max_features}\"\n",
    "par_name_6 = f\"max_depth={rf_min_impurity_decrease}\"\n",
    "par_name_7 = f\"max_features={rf_oob_score}\"\n",
    "\n",
    "# **********************************************************\n",
    "\n",
    "# Fitting the model\n",
    "rf_model = rf_model.fit(X_train_scaled, y_train)\n",
    "\n",
    "# Making predictions using the testing data.\n",
    "predictions = rf_model.predict(X_test_scaled)\n",
    "\n",
    "# Calculating the confusion matrix.\n",
    "cm = confusion_matrix(y_test, predictions)\n",
    "\n",
    "# Create a DataFrame from the confusion matrix.\n",
    "cm_df = pd.DataFrame(\n",
    "    cm, index=[\"Actual 0\", \"Actual 1\"], columns=[\"Predicted 0\", \"Predicted 1\"])\n",
    "\n",
    "#results database information\n",
    "# *************************************************\n",
    "\n",
    "CM_A0P0_cases= cm_df.loc[\"Actual 0\", \"Predicted 0\"]\n",
    "CM_A0P1_cases= cm_df.loc[\"Actual 0\", \"Predicted 1\"]\n",
    "CM_A1P0_cases= cm_df.loc[\"Actual 1\", \"Predicted 0\"]\n",
    "CM_A1P1_cases= cm_df.loc[\"Actual 1\", \"Predicted 1\"]\n",
    "\n",
    "# *************************************************\n",
    "\n",
    "\n",
    "# Calculating the accuracy score.\n",
    "acc_score = accuracy_score(y_test, predictions)\n",
    "\n",
    "#results database information\n",
    "# *********************************\n",
    "\n",
    "acc_score_cases = acc_score\n",
    "\n",
    "# make a dataframe from the classification report\n",
    "\n",
    "def get_classification_report(y_test, y_pred):\n",
    "    \n",
    "    # Source: https://\n",
    "    # stackoverflow.com/questions/39662398/scikit-learn-output-metrics-classification-report-into-csv-\n",
    "    # tab-delimited-format\n",
    "    \n",
    "    report = metrics.classification_report(y_test, y_pred, output_dict=True)\n",
    "    df_classification_report = pd.DataFrame(report).transpose()\n",
    "    df_classification_report = df_classification_report.sort_values(by=['f1-score'], ascending=False)\n",
    "    return df_classification_report\n",
    "\n",
    "CR_cases_df = get_classification_report(y_test, predictions)\n",
    "\n",
    "CR_P0_cases = CR_cases_df.loc['0', 'precision']\n",
    "CR_P1_cases = CR_cases_df.loc['1', 'precision']\n",
    "CR_R0_cases = CR_cases_df.loc['0', 'recall']\n",
    "CR_R1_cases = CR_cases_df.loc['1', 'recall']\n",
    "CR_f1_0_cases = CR_cases_df.loc['0', 'f1-score']\n",
    "CR_f1_1_cases = CR_cases_df.loc['1', 'f1-score']\n",
    "\n",
    "# *********************************\n",
    "\n",
    "# sort the features by their importance\n",
    "\n",
    "imp_list = sorted(zip(rf_model.feature_importances_, X.columns), reverse=True)\n",
    "df_importance_cases = pd.DataFrame(imp_list)\n",
    "df_importance_cases.rename(columns = {0 :'Importance_cases'}, inplace = True)\n",
    "df_importance_cases.rename(columns = {1 :'Feature_cases'}, inplace = True)\n",
    "df_importance_cases['notebook'] = run_nb['notebook']\n",
    "df_importance_cases['run_dt'] = run_dt\n",
    "cols_imp = ['notebook', 'run_dt', 'Feature_cases', 'Importance_cases']\n",
    "df_importance_cases = df_importance_cases.reindex(columns = cols_imp)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "d05dd488",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Confusion Matrix\n"
     ]
    },
    {
     "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>Predicted 0</th>\n",
       "      <th>Predicted 1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Actual 0</th>\n",
       "      <td>245</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Actual 1</th>\n",
       "      <td>21</td>\n",
       "      <td>328</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Predicted 0  Predicted 1\n",
       "Actual 0          245            4\n",
       "Actual 1           21          328"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Accuracy Score : 0.9581939799331104\n",
      "Classification Report\n",
      "              precision    recall  f1-score   support\n",
      "\n",
      "           0       0.92      0.98      0.95       249\n",
      "           1       0.99      0.94      0.96       349\n",
      "\n",
      "    accuracy                           0.96       598\n",
      "   macro avg       0.95      0.96      0.96       598\n",
      "weighted avg       0.96      0.96      0.96       598\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Displaying results\n",
    "\n",
    "print(\"Confusion Matrix\")\n",
    "display(cm_df)\n",
    "print(f\"Accuracy Score : {acc_score}\")\n",
    "print(\"Classification Report\")\n",
    "rep = classification_report(y_test, predictions)\n",
    "print(classification_report(y_test, predictions))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9f64778e",
   "metadata": {},
   "source": [
    "## Machine Learning Model\n",
    "\n",
    "**TITLE: deaths**\n",
    "\n",
    "**MODEL: RandomForest**\n",
    "\n",
    "**FILE:  Cases_Cleaned/ML_deaths_vcd.csv**\n",
    "\n",
    "**RELEVANT DATAFRAMES:  CR_death_df, df_importance_death**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "d99e3d1d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save information for the results database\n",
    "# \n",
    "# Functions defined here are RandomForestClassifier\n",
    "\n",
    "\n",
    "\n",
    "#results database information\n",
    "# *************************************\n",
    "\n",
    "model_id = 1\n",
    "\n",
    "type_model_deaths = \"Random Forest\"\n",
    "name_model_deaths = \"deaths\"\n",
    "\n",
    "# *************************************\n",
    "\n",
    "# Loading data\n",
    "file_path = Path(\"Deaths_Cleaned/ML_deaths_vcd.csv\")\n",
    "df_deaths = pd.read_csv(file_path)\n",
    "\n",
    "# Define the features set.\n",
    "X = df_deaths.copy()\n",
    "X = X.drop(\"2020_mean_deaths\", axis=1)\n",
    "\n",
    "# Define the target set.\n",
    "y = df_deaths[\"2020_mean_deaths\"].ravel()\n",
    "\n",
    "# Splitting into Train and Test sets.\n",
    "X_train, X_test, y_train, y_test = train_test_split(X, y, random_state=78)\n",
    "\n",
    "# Creating a StandardScaler instance.\n",
    "scaler = StandardScaler()\n",
    "# Fitting the Standard Scaler with the training data.\n",
    "X_scaler = scaler.fit(X_train)\n",
    "\n",
    "# Scaling the data.\n",
    "X_train_scaled = X_scaler.transform(X_train)\n",
    "X_test_scaled = X_scaler.transform(X_test)\n",
    "\n",
    "# Create a random forest classifier.\n",
    "\n",
    "rf_model = RandomForestClassifier(n_estimators=n_estimators, random_state=random_state,\n",
    "                                  criterion=criterion,\n",
    "                                  max_depth=max_depth, max_features =max_features,\n",
    "                                  min_impurity_decrease = min_impurity_decrease,\n",
    "                                 oob_score = oob_score) \n",
    "\n",
    "# Fitting the model\n",
    "rf_model = rf_model.fit(X_train_scaled, y_train)\n",
    "\n",
    "# Making predictions using the testing data.\n",
    "predictions = rf_model.predict(X_test_scaled)\n",
    "\n",
    "# Calculating the confusion matrix.\n",
    "cm = confusion_matrix(y_test, predictions)\n",
    "\n",
    "# Create a DataFrame from the confusion matrix.\n",
    "cm_df = pd.DataFrame(\n",
    "    cm, index=[\"Actual 0\", \"Actual 1\"], columns=[\"Predicted 0\", \"Predicted 1\"])\n",
    "\n",
    "#results database information\n",
    "# *************************************************\n",
    "\n",
    "CM_A0P0_death= cm_df.loc[\"Actual 0\", \"Predicted 0\"]\n",
    "CM_A0P1_death= cm_df.loc[\"Actual 0\", \"Predicted 1\"]\n",
    "CM_A1P0_death= cm_df.loc[\"Actual 1\", \"Predicted 0\"]\n",
    "CM_A1P1_death= cm_df.loc[\"Actual 1\", \"Predicted 1\"]\n",
    "\n",
    "# *************************************************\n",
    "\n",
    "# Calculating the accuracy score.\n",
    "acc_score = accuracy_score(y_test, predictions)\n",
    "\n",
    "#results database information\n",
    "# **************************************\n",
    "\n",
    "acc_score_death = acc_score\n",
    "\n",
    "# make a dataframe from the classification report\n",
    "\n",
    "def get_classification_report(y_test, y_pred):\n",
    "    # Source: https://\n",
    "    # stackoverflow.com/questions/39662398/scikit-learn-output-metrics-classification-report-into-csv-\n",
    "    # tab-delimited-format\n",
    "    report = metrics.classification_report(y_test, y_pred, output_dict=True)\n",
    "    df_classification_report = pd.DataFrame(report).transpose()\n",
    "    df_classification_report = df_classification_report.sort_values(by=['f1-score'], ascending=False)\n",
    "    return df_classification_report\n",
    "\n",
    "CR_death_df = get_classification_report(y_test, predictions)\n",
    "\n",
    "CR_P0_death = CR_death_df.loc['0', 'precision']\n",
    "CR_P1_death = CR_death_df.loc['1', 'precision']\n",
    "CR_R0_death = CR_death_df.loc['0', 'recall']\n",
    "CR_R1_death = CR_death_df.loc['1', 'recall']\n",
    "CR_f1_0_death = CR_death_df.loc['0', 'f1-score']\n",
    "CR_f1_1_death = CR_death_df.loc['1', 'f1-score']\n",
    "\n",
    "# ********************************************\n",
    "\n",
    "# sort the features by their importance\n",
    "\n",
    "imp_list = sorted(zip(rf_model.feature_importances_, X.columns), reverse=True)\n",
    "df_importance_death = pd.DataFrame(imp_list)\n",
    "df_importance_death.rename(columns = {0 :'Importance_death'}, inplace = True)\n",
    "df_importance_death.rename(columns = {1 :'Feature_death'}, inplace = True)\n",
    "df_importance_death['notebook'] = run_nb['notebook']\n",
    "df_importance_death['run_dt'] = run_dt\n",
    "cols_imp = ['notebook', 'run_dt', 'Feature_death', 'Importance_death']\n",
    "df_importance_death = df_importance_death.reindex(columns = cols_imp)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "c5bf9fcd",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Confusion Matrix\n"
     ]
    },
    {
     "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>Predicted 0</th>\n",
       "      <th>Predicted 1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Actual 0</th>\n",
       "      <td>271</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Actual 1</th>\n",
       "      <td>38</td>\n",
       "      <td>280</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Predicted 0  Predicted 1\n",
       "Actual 0          271            9\n",
       "Actual 1           38          280"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Accuracy Score : 0.9214046822742475\n",
      "Classification Report\n",
      "              precision    recall  f1-score   support\n",
      "\n",
      "           0       0.88      0.97      0.92       280\n",
      "           1       0.97      0.88      0.92       318\n",
      "\n",
      "    accuracy                           0.92       598\n",
      "   macro avg       0.92      0.92      0.92       598\n",
      "weighted avg       0.93      0.92      0.92       598\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Displaying results\n",
    "\n",
    "print(\"Confusion Matrix\")\n",
    "display(cm_df)\n",
    "print(f\"Accuracy Score : {acc_score}\")\n",
    "print(\"Classification Report\")\n",
    "print(classification_report(y_test, predictions))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "02ca0faa",
   "metadata": {},
   "source": [
    "## PostgresSQL Database\n",
    "\n",
    "### Database to hold machine learning results\n",
    "\n",
    "#### Create 4 Dataframes for Importing into PostgreSQL Database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "fe6ec440",
   "metadata": {},
   "outputs": [],
   "source": [
    "# option to manually set the run counter here to start at a later notebook number\n",
    "# all results database information are used to create input for the PostgresSQL database here\n",
    "\n",
    "if run_counter == 19:\n",
    "\n",
    "    \n",
    "# df_model\n",
    "   \n",
    "    name_nb_dict = {\"name_nb\":name_nb}\n",
    "    run_dt_dict = {\"run_dt\":run_dt}\n",
    "    run_nb_dict = run_nb\n",
    "    source_db_dict = {\"source_db\":source_db}\n",
    "    file_id_dict = {\"file_id\":file_id}\n",
    "    model_id_dict = {\"model_id\":model_id}\n",
    "    type_model_cases_dict = {\"type_model_cases\":type_model_cases}\n",
    "    type_model_deaths_dict = {\"type_model_deaths\":type_model_deaths}\n",
    "    name_model_cases_dict = {\"name_model_cases\":name_model_cases}\n",
    "    name_model_deaths_dict = {\"name_model_deaths\":name_model_deaths}\n",
    "    par_name_1_dict = {\"par_name_1\":par_name_1}\n",
    "    par_name_2_dict = {\"par_name_2\":par_name_2}\n",
    "    par_name_3_dict = {\"par_name_3\":par_name_3}\n",
    "    par_name_4_dict = {\"par_name_4\":par_name_4}\n",
    "    par_name_5_dict = {\"par_name_5\":par_name_5}\n",
    "    par_name_6_dict = {\"par_name_6\":par_name_6}\n",
    "    par_name_7_dict = {\"par_name_7\":par_name_7}\n",
    "    casesfile_id_dict = {\"casesfile_id\":casesfile_id}\n",
    "    deathsfile_id_dict = {\"deathsfile_id\":deathsfile_id}\n",
    "    name_statsfile_dict = {\"name_statsfile\":name_statsfile}\n",
    "    name_statistic_dict = {\"name_statistic\":name_statistic}\n",
    "  \n",
    "    \n",
    "    data = [run_nb_dict,name_nb_dict, run_dt_dict, source_db_dict, file_id_dict, model_id_dict,\n",
    "            type_model_cases_dict, type_model_deaths_dict,  name_model_cases_dict, name_model_deaths_dict,\n",
    "            par_name_1_dict, par_name_2_dict, par_name_3_dict, par_name_4_dict, par_name_5_dict,\n",
    "            par_name_6_dict, par_name_7_dict, casesfile_id_dict, deathsfile_id_dict,\n",
    "            name_statsfile_dict, name_statistic_dict]\n",
    "    \n",
    "    data_merged = {}\n",
    "    for x in data:\n",
    "        data_merged.update(x)\n",
    "    data_list = [data_merged]\n",
    "    \n",
    "    df_model = pd.DataFrame(data_list)\n",
    "    \n",
    "    \n",
    "# df_model_results\n",
    "\n",
    "    results_dict ={\n",
    "\n",
    "        'notebook': run_nb_dict['notebook'],\n",
    "        'run_dt':run_dt_dict['run_dt'],\n",
    "        'CM_A0P0_cases':CM_A0P0_cases,\n",
    "        'CM_A0P1_cases':CM_A0P1_cases,\n",
    "        'CM_A1P0_cases':CM_A1P0_cases,\n",
    "        'CM_A1P1_cases':CM_A1P1_cases,\n",
    "        'CM_A0P0_death':CM_A0P0_death,\n",
    "        'CM_A0P1_death':CM_A0P1_death,\n",
    "        'CM_A1P0_death':CM_A1P0_death,\n",
    "        'CM_A1P1_death':CM_A1P1_death,\n",
    "        'acc_score_cases':acc_score_cases,\n",
    "        'acc_score_death':acc_score_death,\n",
    "        'CR_P0_cases':CR_P0_cases,\n",
    "        'CR_P1_cases':CR_P1_cases,\n",
    "        'CR_R0_cases':CR_R0_cases,\n",
    "        'CR_R1_cases':CR_R1_cases,\n",
    "        'CR_f1_0_cases':CR_f1_0_cases,\n",
    "        'CR_f1_1_cases':CR_f1_1_cases,\n",
    "        'CR_P0_death':CR_P0_death,\n",
    "        'CR_P1_death':CR_P1_death,\n",
    "        'CR_R0_death':CR_R0_death,\n",
    "        'CR_R1_death':CR_R1_death,\n",
    "        'CR_f1_0_death':CR_f1_0_death,\n",
    "        'CR_f1_1_death':CR_f1_1_death\n",
    "\n",
    "    }\n",
    "\n",
    "    results_list = [results_dict]\n",
    "    df_model_results = pd.DataFrame(results_list)\n",
    "\n",
    "# df_model_importances\n",
    "\n",
    "    df_model_importances = pd.merge(df_importance_cases, df_importance_death, left_index =True, right_index=True)\n",
    "    df_model_importances.drop(columns=[\"notebook_y\", \"Feature_death\"], inplace = True)\n",
    "    df_model_importances.rename(columns = {'notebook_x':'notebook','Feature_cases':\"Feature\"}, inplace = True)\n",
    "    df_model_importances.drop(columns = [\"run_dt_y\"], inplace = True)\n",
    "    df_model_importances.rename(columns = {'run_dt_x':'run_dt'}, inplace = True)\n",
    "    \n",
    "\n",
    "# initialize the new dataframes\n",
    "\n",
    "    df_model_new = df_model.copy()\n",
    "    df_model_results_new = df_model_results.copy()\n",
    "    df_model_importances_new = df_model_importances.copy()\n",
    "\n",
    "# saved copies for resetting the dataframes\n",
    "\n",
    "    df_model_first_run = df_model.copy()\n",
    "    df_model_results_first_run = df_model_results.copy()\n",
    "    df_model_importances_first_run = df_model_importances.copy()\n",
    "        \n",
    "else:\n",
    "    \n",
    "# dataframes for run_counter > 1\n",
    "\n",
    "# df_model\n",
    "\n",
    "   \n",
    "    name_nb_dict = {\"name_nb\":name_nb}\n",
    "    run_dt_dict = {\"run_dt\":run_dt}\n",
    "    run_nb_dict = run_nb\n",
    "    source_db_dict = {\"source_db\":source_db}\n",
    "    file_id_dict = {\"file_id\":file_id}\n",
    "    model_id_dict = {\"model_id\":model_id}\n",
    "    type_model_cases_dict = {\"type_model_cases\":type_model_cases}\n",
    "    type_model_deaths_dict = {\"type_model_deaths\":type_model_deaths}\n",
    "    name_model_cases_dict = {\"name_model_cases\":name_model_cases}\n",
    "    name_model_deaths_dict = {\"name_model_deaths\":name_model_deaths}\n",
    "    par_name_1_dict = {\"par_name_1\":par_name_1}\n",
    "    par_name_2_dict = {\"par_name_2\":par_name_2}\n",
    "    par_name_3_dict = {\"par_name_3\":par_name_3}\n",
    "    par_name_4_dict = {\"par_name_4\":par_name_4}\n",
    "    par_name_5_dict = {\"par_name_5\":par_name_5}\n",
    "    par_name_6_dict = {\"par_name_6\":par_name_6}\n",
    "    par_name_7_dict = {\"par_name_7\":par_name_7}\n",
    "    casesfile_id_dict = {\"casesfile_id\":casesfile_id}\n",
    "    deathsfile_id_dict = {\"deathsfile_id\":deathsfile_id}\n",
    "    name_statsfile_dict = {\"name_statsfile\":name_statsfile}\n",
    "    name_statistic_dict = {\"name_statistic\":name_statistic}\n",
    "    \n",
    "    data = [run_nb_dict,name_nb_dict, run_dt_dict, source_db_dict, file_id_dict, model_id_dict,\n",
    "            type_model_cases_dict, type_model_deaths_dict,  name_model_cases_dict, name_model_deaths_dict,\n",
    "            par_name_1_dict, par_name_2_dict, par_name_3_dict, par_name_4_dict, par_name_5_dict,\n",
    "            par_name_6_dict, par_name_7_dict, casesfile_id_dict, deathsfile_id_dict,\n",
    "            name_statsfile_dict,  name_statistic_dict]\n",
    "    \n",
    "    data_merged = {}\n",
    "    for x in data:\n",
    "        data_merged.update(x)\n",
    "    data_list = [data_merged]\n",
    "    \n",
    "    df_model = pd.DataFrame(data_list)\n",
    "    \n",
    "\n",
    "# df_model_results\n",
    "\n",
    "    \n",
    "    results_dict ={\n",
    "\n",
    "        'notebook': run_nb_dict['notebook'],\n",
    "        'run_dt':run_dt_dict['run_dt'],\n",
    "        'CM_A0P0_cases':CM_A0P0_cases,\n",
    "        'CM_A0P1_cases':CM_A0P1_cases,\n",
    "        'CM_A1P0_cases':CM_A1P0_cases,\n",
    "        'CM_A1P1_cases':CM_A1P1_cases,\n",
    "        'CM_A0P0_death':CM_A0P0_death,\n",
    "        'CM_A0P1_death':CM_A0P1_death,\n",
    "        'CM_A1P0_death':CM_A1P0_death,\n",
    "        'CM_A1P1_death':CM_A1P1_death,\n",
    "        'acc_score_cases':acc_score_cases,\n",
    "        'acc_score_death':acc_score_death,\n",
    "        'CR_P0_cases':CR_P0_cases,\n",
    "        'CR_P1_cases':CR_P1_cases,\n",
    "        'CR_R0_cases':CR_R0_cases,\n",
    "        'CR_R1_cases':CR_R1_cases,\n",
    "        'CR_f1_0_cases':CR_f1_0_cases,\n",
    "        'CR_f1_1_cases':CR_f1_1_cases,\n",
    "        'CR_P0_death':CR_P0_death,\n",
    "        'CR_P1_death':CR_P1_death,\n",
    "        'CR_R0_death':CR_R0_death,\n",
    "        'CR_R1_death':CR_R1_death,\n",
    "        'CR_f1_0_death':CR_f1_0_death,\n",
    "        'CR_f1_1_death':CR_f1_1_death\n",
    "\n",
    "    }\n",
    "\n",
    "    results_list = [results_dict]\n",
    "    df_model_results = pd.DataFrame(results_list)\n",
    "\n",
    "\n",
    "# df_model_importances\n",
    "\n",
    "    df_model_importances = pd.merge(df_importance_cases, df_importance_death, left_index =True, right_index=True)\n",
    "    df_model_importances.drop(columns=[\"notebook_y\", \"Feature_death\"], inplace = True)\n",
    "    df_model_importances.rename(columns = {'notebook_x':'notebook','Feature_cases':\"Feature\"}, inplace = True)\n",
    "    df_model_importances.drop(columns = [\"run_dt_y\"], inplace = True)\n",
    "    df_model_importances.rename(columns = {'run_dt_x':'run_dt'}, inplace = True)\n",
    "\n",
    "\n",
    "# concat dataframes\n",
    "\n",
    "    df_model_new = pd.concat([df_model_new, df_model], ignore_index = True)\n",
    "    # df_set_stats_new = pd.concat([df_set_stats_new, df_set_stats], ignore_index = True)\n",
    "    df_model_results_new = pd.concat([df_model_results_new, df_model_results],ignore_index = True)\n",
    "    df_model_importances_new = pd.concat([df_model_importances_new, df_model_importances], ignore_index = True)\n",
    "\n",
    "\n",
    "# the 3 dataframes to be put into PostgresSql:\n",
    "#\n",
    "# df_model_new\n",
    "# df_model_results_new\n",
    "# df_model_importances_new\n",
    "#\n",
    "# Note: df_set_stats_new is NOT used in this notebook\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "107a433e",
   "metadata": {
    "scrolled": true
   },
   "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>notebook</th>\n",
       "      <th>name_nb</th>\n",
       "      <th>run_dt</th>\n",
       "      <th>source_db</th>\n",
       "      <th>file_id</th>\n",
       "      <th>model_id</th>\n",
       "      <th>type_model_cases</th>\n",
       "      <th>type_model_deaths</th>\n",
       "      <th>name_model_cases</th>\n",
       "      <th>name_model_deaths</th>\n",
       "      <th>...</th>\n",
       "      <th>par_name_2</th>\n",
       "      <th>par_name_3</th>\n",
       "      <th>par_name_4</th>\n",
       "      <th>par_name_5</th>\n",
       "      <th>par_name_6</th>\n",
       "      <th>par_name_7</th>\n",
       "      <th>casesfile_id</th>\n",
       "      <th>deathsfile_id</th>\n",
       "      <th>name_statsfile</th>\n",
       "      <th>name_statistic</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>19</td>\n",
       "      <td>ML_pn_rev1</td>\n",
       "      <td>2021-11-28 16:59:09</td>\n",
       "      <td>AWS database csv file</td>\n",
       "      <td>https://initial-datasets.s3.amazonaws.com/vax_...</td>\n",
       "      <td>1</td>\n",
       "      <td>Random Forest</td>\n",
       "      <td>Random Forest</td>\n",
       "      <td>cases</td>\n",
       "      <td>deaths</td>\n",
       "      <td>...</td>\n",
       "      <td>random_state=78</td>\n",
       "      <td>criterion=gini</td>\n",
       "      <td>max_depth=10</td>\n",
       "      <td>max_features=sqrt</td>\n",
       "      <td>max_depth=0.0</td>\n",
       "      <td>max_features=True</td>\n",
       "      <td>ML_cases_vcd.csv_19</td>\n",
       "      <td>ML_deaths_vcd.csv_19</td>\n",
       "      <td>stats_Xb_cases_ds</td>\n",
       "      <td>mean</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   notebook     name_nb               run_dt              source_db  \\\n",
       "0        19  ML_pn_rev1  2021-11-28 16:59:09  AWS database csv file   \n",
       "\n",
       "                                             file_id  model_id  \\\n",
       "0  https://initial-datasets.s3.amazonaws.com/vax_...         1   \n",
       "\n",
       "  type_model_cases type_model_deaths name_model_cases name_model_deaths  ...  \\\n",
       "0    Random Forest     Random Forest            cases            deaths  ...   \n",
       "\n",
       "        par_name_2      par_name_3    par_name_4         par_name_5  \\\n",
       "0  random_state=78  criterion=gini  max_depth=10  max_features=sqrt   \n",
       "\n",
       "      par_name_6         par_name_7         casesfile_id  \\\n",
       "0  max_depth=0.0  max_features=True  ML_cases_vcd.csv_19   \n",
       "\n",
       "          deathsfile_id     name_statsfile name_statistic  \n",
       "0  ML_deaths_vcd.csv_19  stats_Xb_cases_ds           mean  \n",
       "\n",
       "[1 rows x 21 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# View dataframes for input into the results database  \n",
    "    \n",
    "df_model_new"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "id": "f01df67a",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "# Option to append the dataframes to a csv file (not used for appending to a csv file created from PostgreSQL)\n",
    "\n",
    "#df_model_new.to_csv('rfinput_optimal.csv', mode = 'a', index = False, header = False)\n",
    "#df_model_results_new.to_csv('rfresult_optimal.csv', mode = 'a', index = False, header = False)\n",
    "#df_model_importances_new.to_csv('rfimportance_optimal.csv', mode = 'a', index = False, header = False)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eb763226",
   "metadata": {},
   "source": [
    "### Dataframes to PostgreSQL tables\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 405,
   "id": "38706d80",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 406,
   "id": "51b83f9f",
   "metadata": {},
   "outputs": [],
   "source": [
    "from config import db_password"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 407,
   "id": "2eadde7d",
   "metadata": {},
   "outputs": [],
   "source": [
    "db_string = f\"postgresql://postgres:{db_password}@127.0.0.1:5432/MLmodels\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 408,
   "id": "4052b1bf",
   "metadata": {},
   "outputs": [],
   "source": [
    "engine = create_engine(db_string)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 410,
   "id": "4d1ca9d7",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_model_new.to_sql(name='mlinputs_2', con=engine)\n",
    "#df_model_results_new.to_sql(name = 'rfresults', con=engine, if_exists='append')\n",
    "#df_model_importances_new.to_sql(name = 'rfimportances', con = engine, if_exists='append')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "267a7b12",
   "metadata": {},
   "source": [
    "<div style=\"background:lightblue\">\n",
    "    \n",
    "# Navigator\n",
    "\n",
    "## [Start](#Notebook-and-Run-Count-Information)\n",
    "\n",
    "## [Database Cleaner](#AWS-Database-Cleaner)\n",
    "\n",
    "## [Machine Learning Model](#TITLE:-cases)\n",
    "\n",
    "## [Database Structure](#PostgresSQL-Database)\n",
    "\n",
    "## [Export to Database](#Dataframes-to-PostgreSQL-tables)\n",
    "    \n",
    "</div>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c6170ab8",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-block alert-danger\">\n",
    "\n",
    "\n",
    "## STOP BEFORE RUNNING THE NEXT CELL:   RESET OPTION\n",
    "    \n",
    "ARE YOU SURE YOU WANT TO RESET?\n",
    "ALL RUNS AFTER THE FIRST WILL BE GONE!\n",
    "    \n",
    "</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "id": "ab787418",
   "metadata": {},
   "outputs": [],
   "source": [
    "# RESET OF DATAFRAMES TO THE FIRST RUN.  ONLY RESET IF NEEDED.\n",
    "#\n",
    "\n",
    "\n",
    "\n",
    "reset_dataframes = False\n",
    "if reset_dataframes == True:\n",
    "    df_model_new = df_model_first_run\n",
    "    df_model_results_new = df_model_results_first_run\n",
    "    df_model_importances_new = df_model_importances_first_run \n",
    "    \n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "mlenv",
   "language": "python",
   "name": "mlenv"
  },
  "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.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}