4 HanzeFitbit normal dataframe all treament ids, all models, results database, Predict.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This script predicts per hour if the threshold per participant will be met, using different predictive models for an individual, for the whole period.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#connect oracle using windows client..\n",
    "#comment:download correct version:3.5 python+ Oracle 12c  windows 64\n",
    "#download and pip install cx_Oracle-5.2+oci12c-cp35-none-win_amd64.whl from lfd.uci.edu/~gohlke/pythonlibs/\n",
    "#cx_oracle \n",
    "#commect:Download the right oracle client 12c+ windows 64 and install in C:\\instantclient_12_1\n",
    "#http://www.oracle.com/technetwork/topics/winx64soft-089540.html\n",
    "#Set path in windows add C:\\instantclient_12_1 (use path manager.exe) restart\n",
    "\n",
    "import cx_Oracle\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import time\n",
    "\n",
    "# classifiers & testing\n",
    "\n",
    "from sklearn.svm import LinearSVC\n",
    "from sklearn.neural_network import MLPClassifier\n",
    "from sklearn.tree import DecisionTreeClassifier\n",
    "from sklearn.model_selection import train_test_split, GridSearchCV\n",
    "from sklearn.metrics import f1_score, confusion_matrix, accuracy_score\n",
    "from sklearn import metrics\n",
    "from sklearn.ensemble import RandomForestClassifier\n",
    "from sklearn.ensemble import ExtraTreesClassifier\n",
    "from sklearn.preprocessing import Normalizer\n",
    "from sklearn.neighbors import KNeighborsClassifier\n",
    "from sklearn.linear_model import LogisticRegression\n",
    "from sklearn.model_selection import KFold\n",
    "        \n",
    "\n",
    "import re  \n",
    "import os \n",
    "import pickle\n",
    "from mpl_toolkits.mplot3d import Axes3D\n",
    "from matplotlib import colors\n",
    "import matplotlib.pyplot as plt\n",
    "from matplotlib.colors import ListedColormap\n",
    "%matplotlib inline\n",
    "import seaborn as sns\n",
    "import json\n",
    "import collections\n",
    "from math import sqrt\n",
    "from random import randrange\n",
    "from random import seed\n",
    "\n",
    "\n",
    "con = cx_Oracle.connect('hanzefitbit/hanzefitbit@127.0.0.1/xe')\n",
    "\n",
    "\n",
    "#con.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#function to determine if people have reached their goal of the day\n",
    "\n",
    "def daily_steps_cat_f (steps_value,threshold):\n",
    "    if (steps_value<threshold):\n",
    "        #print('smaller then threshold')\n",
    "        return 0\n",
    "    if (steps_value>=threshold):\n",
    "        #print('more then threshold')\n",
    "        return 1\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#function to determine the available models\n",
    "def get_pickled_models_f (treatment_id, algorithm_name):\n",
    "    pickle_model=str((treatment_id)+'_'+algorithm_name+'_'+'model.pkl')\n",
    "    cur.execute(\"select destination from hft_model_t where name=:pickle_model\",pickle_model=pickle_model)\n",
    "    dest = cur.fetchone()[0]\n",
    "    globals()['model%s' % treatment_id+algorithm_name] = pickle.load(open(os.path.join(dest,pickle_model), 'rb'))\n",
    "    cur.execute(\"select id from hft_model_t where name=:pickle_model\",pickle_model=pickle_model)\n",
    "    globals()['model_id%s' % treatment_id+algorithm_name]=cur.fetchone()[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#function to predict and save the results into the database\n",
    "def predict_f(treatment_id,algorithm_name):\n",
    "    cur.execute('select id,treatment_id,year,week,weekday,hour,sum_steps, sum_steps_hour,daily_steps from hft_sum_steps_v where \\\n",
    "             treatment_id=:treatment_id and hour in (7,8,9,10,11,12,13,14,15,16,17,18) \\\n",
    "             and year=2015 and weekday not in (5,6) and (case when research_group=2 and week>15 then 1 when research_group=1 and week>4 then 1 else 0 end = 1)\\\n",
    "             order by year,week,weekday,hour',treatment_id=treatment_id)     \n",
    "    df= pd.DataFrame(cur.fetchall(),columns=['id','treatment_id','year','week','weekday','hour','sum_steps','sum_steps_hour','daily_steps'])\n",
    "    \n",
    "    y_pred = globals()['model%s' % treatment_id+algorithm_name].predict( df.iloc[:, 5:8].values)\n",
    "    probs_all = globals()['model%s' % treatment_id+algorithm_name].predict_proba(df.iloc[:, 5:8].values)\n",
    "    #make a dataframe of  globals()['probs_all%s' % treatment_id] to use iloc\n",
    "    probs_all=pd.DataFrame(probs_all)\n",
    "    proba_all=probs_all.iloc[:,1]\n",
    "    \n",
    "    \n",
    "    cur.execute( 'select treatment_id,avg(sum_steps_hour) avg_daily_steps from hft_sum_steps_v where treatment_id=:treatment_id \\\n",
    "                  and (case when research_group=2 and week>15 then 1 when research_group=1 and week>4 then 1 else 0 end = 1)\\\n",
    "                  and year=2015 and hour=18 and weekday not in (5,6) group by treatment_id',treatment_id=treatment_id)\n",
    "    df_threshold= pd.DataFrame(cur.fetchall(),columns=['treatment_id','avg_daily_steps'])\n",
    "\n",
    "  \n",
    "    #use numpy vectorize to use function with more arguments and a dataframe determine if the threshold is met\n",
    "\n",
    "    daily_steps=df['daily_steps']\n",
    "    threshold=df_threshold['avg_daily_steps']\n",
    "    df['dailysteps_cat']=np.vectorize(daily_steps_cat_f)(daily_steps, threshold)\n",
    "    y= df.iloc[:,9].values\n",
    "    \n",
    "    #make a dataframe for all predictions\n",
    "    df_prediction =pd.DataFrame({'id':df.id,'model_id':globals()['model_id%s' % treatment_id+algorithm_name],'probs':proba_all,'y':y,'y_pred': y_pred})\n",
    "    #insert results into database, dataframe is based on alphabetical order.... so \n",
    "    rows = [tuple(x) for x in df_prediction.values]\n",
    "    cur.prepare(\"BEGIN INSERT INTO HFT_PREDICTION_T(HFT_DATA_T_ID,HFT_MODEL_T_ID,PROBABILITY,STEPS_CAT,PREDICTION_CAT)VALUES (:1,:2,:3,:4,:5);END;\")\n",
    "    cur.executemany(None,rows)\n",
    "    con.commit()\n",
    "    #cur.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# select treatment id's for looping through all models etc...\n",
    "cur = con.cursor()\n",
    "cur.execute('select distinct hft_treatment_id from hft_metrics_t where weekday=-1 ')\n",
    "#dataframe\n",
    "df_treatment_id=pd.DataFrame(cur.fetchall(),columns=['treatment_id'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#Load saved model to test if the model can be retrieved.\n",
    "for i in df_treatment_id['treatment_id']:\n",
    "    treatment_id=str(i)\n",
    "    get_pickled_models_f (treatment_id, 'LR')\n",
    "    get_pickled_models_f (treatment_id, 'ADA')\n",
    "    get_pickled_models_f (treatment_id, 'RF')\n",
    "    get_pickled_models_f (treatment_id, 'KNN')\n",
    "    get_pickled_models_f (treatment_id, 'NN')\n",
    "    get_pickled_models_f (treatment_id, 'SGD')\n",
    "    get_pickled_models_f (treatment_id, 'SVC')\n",
    "    get_pickled_models_f (treatment_id, 'DT')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#predict using different algorithms\n",
    "cur = con.cursor()\n",
    "sql='DELETE FROM HFT_PREDICTION_T'\n",
    "con.commit()\n",
    "cur.execute(sql)\n",
    "for i in df_treatment_id['treatment_id']:\n",
    "    treatment_id=int(i)\n",
    "    predict_f(treatment_id,'LR')\n",
    "    predict_f(treatment_id,'ADA') \n",
    "    predict_f(treatment_id,'RF')\n",
    "    predict_f(treatment_id,'KNN')\n",
    "    predict_f(treatment_id,'NN')\n",
    "    predict_f(treatment_id,'SGD')\n",
    "    predict_f(treatment_id,'SVC')\n",
    "    predict_f(treatment_id,'DT')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "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.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}