Predict Employee Computer Access_in_R.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Science Project in Python- Given his or her job role, predict employee access needs using amazon employee database."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## When an employee at any company starts work, they first need to obtain the computer access necessary to fulfill their role. This access may allow an employee to read/manipulate resources through various applications or web portals. It is assumed that employees fulfilling the functions of a given role will access the same or similar resources. It is often the case that employees figure out the access they need as they encounter roadblocks during their daily work (e.g. not able to log into a reporting portal). A knowledgeable supervisor then takes time to manually grant the needed access in order to overcome access obstacles. As employees move throughout a company, this access discovery/recovery cycle wastes a nontrivial amount of time and money.\n",
    "\n",
    "\n",
    "## There is a considerable amount of data regarding an employee’s role within an organization and the resources to which they have access. Given the data related to current employees and their provisioned access, models can be built that automatically determine access privileges as employees enter and leave roles within a company. In this data science project, we will build an auto-access model that minimizes the human involvement required to grant or revoke employee access."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "/root/hackerday/12_employee_access_need/train.csv\n",
    "/root/hackerday/12_employee_access_need/test.csv\n",
    "#/root/hackerday/12_employee_access_need/sampleSubmission.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [
    {
     "ename": "ERROR",
     "evalue": "Error in library(sqldf): there is no package called ‘sqldf’\n",
     "output_type": "error",
     "traceback": [
      "Error in library(sqldf): there is no package called ‘sqldf’\n"
     ]
    }
   ],
   "source": [
    "library(irlba)\n",
    "library(gbm)\n",
    "library(randomForest)\n",
    "library(glmnet)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [
    {
     "ename": "ERROR",
     "evalue": "Error in library(sqldf): there is no package called ‘sqldf’\n",
     "output_type": "error",
     "traceback": [
      "Error in library(sqldf): there is no package called ‘sqldf’\n"
     ]
    }
   ],
   "source": [
    "library(sqldf)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "train <- read.csv(\"/root/hackerday/12_employee_access_need/train.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<thead><tr><th></th><th scope=col>ACTION</th><th scope=col>RESOURCE</th><th scope=col>MGR_ID</th><th scope=col>ROLE_ROLLUP_1</th><th scope=col>ROLE_ROLLUP_2</th><th scope=col>ROLE_DEPTNAME</th><th scope=col>ROLE_TITLE</th><th scope=col>ROLE_FAMILY_DESC</th><th scope=col>ROLE_FAMILY</th><th scope=col>ROLE_CODE</th></tr></thead>\n",
       "<tbody>\n",
       "\t<tr><th scope=row>1</th><td>1</td><td>39353</td><td>85475</td><td>117961</td><td>118300</td><td>123472</td><td>117905</td><td>117906</td><td>290919</td><td>117908</td></tr>\n",
       "\t<tr><th scope=row>2</th><td>1</td><td>17183</td><td>1540</td><td>117961</td><td>118343</td><td>123125</td><td>118536</td><td>118536</td><td>308574</td><td>118539</td></tr>\n",
       "\t<tr><th scope=row>3</th><td>1</td><td>36724</td><td>14457</td><td>118219</td><td>118220</td><td>117884</td><td>117879</td><td>267952</td><td>19721</td><td>117880</td></tr>\n",
       "\t<tr><th scope=row>4</th><td>1</td><td>36135</td><td>5396</td><td>117961</td><td>118343</td><td>119993</td><td>118321</td><td>240983</td><td>290919</td><td>118322</td></tr>\n",
       "\t<tr><th scope=row>5</th><td>1</td><td>42680</td><td>5905</td><td>117929</td><td>117930</td><td>119569</td><td>119323</td><td>123932</td><td>19793</td><td>119325</td></tr>\n",
       "\t<tr><th scope=row>6</th><td>0</td><td>45333</td><td>14561</td><td>117951</td><td>117952</td><td>118008</td><td>118568</td><td>118568</td><td>19721</td><td>118570</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "\\begin{tabular}{r|llllllllll}\n",
       "  & ACTION & RESOURCE & MGR_ID & ROLE_ROLLUP_1 & ROLE_ROLLUP_2 & ROLE_DEPTNAME & ROLE_TITLE & ROLE_FAMILY_DESC & ROLE_FAMILY & ROLE_CODE\\\\\n",
       "\\hline\n",
       "\t1 & 1 & 39353 & 85475 & 117961 & 118300 & 123472 & 117905 & 117906 & 290919 & 117908\\\\\n",
       "\t2 & 1 & 17183 & 1540 & 117961 & 118343 & 123125 & 118536 & 118536 & 308574 & 118539\\\\\n",
       "\t3 & 1 & 36724 & 14457 & 118219 & 118220 & 117884 & 117879 & 267952 & 19721 & 117880\\\\\n",
       "\t4 & 1 & 36135 & 5396 & 117961 & 118343 & 119993 & 118321 & 240983 & 290919 & 118322\\\\\n",
       "\t5 & 1 & 42680 & 5905 & 117929 & 117930 & 119569 & 119323 & 123932 & 19793 & 119325\\\\\n",
       "\t6 & 0 & 45333 & 14561 & 117951 & 117952 & 118008 & 118568 & 118568 & 19721 & 118570\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "  ACTION RESOURCE MGR_ID ROLE_ROLLUP_1 ROLE_ROLLUP_2 ROLE_DEPTNAME ROLE_TITLE\n",
       "1      1    39353  85475        117961        118300        123472     117905\n",
       "2      1    17183   1540        117961        118343        123125     118536\n",
       "3      1    36724  14457        118219        118220        117884     117879\n",
       "4      1    36135   5396        117961        118343        119993     118321\n",
       "5      1    42680   5905        117929        117930        119569     119323\n",
       "6      0    45333  14561        117951        117952        118008     118568\n",
       "  ROLE_FAMILY_DESC ROLE_FAMILY ROLE_CODE\n",
       "1           117906      290919    117908\n",
       "2           118536      308574    118539\n",
       "3           267952       19721    117880\n",
       "4           240983      290919    118322\n",
       "5           123932       19793    119325\n",
       "6           118568       19721    118570"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "head(train)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "test <- read.csv(\"/root/hackerday/12_employee_access_need/test.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<thead><tr><th></th><th scope=col>id</th><th scope=col>RESOURCE</th><th scope=col>MGR_ID</th><th scope=col>ROLE_ROLLUP_1</th><th scope=col>ROLE_ROLLUP_2</th><th scope=col>ROLE_DEPTNAME</th><th scope=col>ROLE_TITLE</th><th scope=col>ROLE_FAMILY_DESC</th><th scope=col>ROLE_FAMILY</th><th scope=col>ROLE_CODE</th></tr></thead>\n",
       "<tbody>\n",
       "\t<tr><th scope=row>1</th><td>1</td><td>78766</td><td>72734</td><td>118079</td><td>118080</td><td>117878</td><td>117879</td><td>118177</td><td>19721</td><td>117880</td></tr>\n",
       "\t<tr><th scope=row>2</th><td>2</td><td>40644</td><td>4378</td><td>117961</td><td>118327</td><td>118507</td><td>118863</td><td>122008</td><td>118398</td><td>118865</td></tr>\n",
       "\t<tr><th scope=row>3</th><td>3</td><td>75443</td><td>2395</td><td>117961</td><td>118300</td><td>119488</td><td>118172</td><td>301534</td><td>249618</td><td>118175</td></tr>\n",
       "\t<tr><th scope=row>4</th><td>4</td><td>43219</td><td>19986</td><td>117961</td><td>118225</td><td>118403</td><td>120773</td><td>136187</td><td>118960</td><td>120774</td></tr>\n",
       "\t<tr><th scope=row>5</th><td>5</td><td>42093</td><td>50015</td><td>117961</td><td>118343</td><td>119598</td><td>118422</td><td>300136</td><td>118424</td><td>118425</td></tr>\n",
       "\t<tr><th scope=row>6</th><td>6</td><td>44722</td><td>1755</td><td>117961</td><td>117962</td><td>119223</td><td>125793</td><td>146749</td><td>118643</td><td>125795</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "\\begin{tabular}{r|llllllllll}\n",
       "  & id & RESOURCE & MGR_ID & ROLE_ROLLUP_1 & ROLE_ROLLUP_2 & ROLE_DEPTNAME & ROLE_TITLE & ROLE_FAMILY_DESC & ROLE_FAMILY & ROLE_CODE\\\\\n",
       "\\hline\n",
       "\t1 & 1 & 78766 & 72734 & 118079 & 118080 & 117878 & 117879 & 118177 & 19721 & 117880\\\\\n",
       "\t2 & 2 & 40644 & 4378 & 117961 & 118327 & 118507 & 118863 & 122008 & 118398 & 118865\\\\\n",
       "\t3 & 3 & 75443 & 2395 & 117961 & 118300 & 119488 & 118172 & 301534 & 249618 & 118175\\\\\n",
       "\t4 & 4 & 43219 & 19986 & 117961 & 118225 & 118403 & 120773 & 136187 & 118960 & 120774\\\\\n",
       "\t5 & 5 & 42093 & 50015 & 117961 & 118343 & 119598 & 118422 & 300136 & 118424 & 118425\\\\\n",
       "\t6 & 6 & 44722 & 1755 & 117961 & 117962 & 119223 & 125793 & 146749 & 118643 & 125795\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "  id RESOURCE MGR_ID ROLE_ROLLUP_1 ROLE_ROLLUP_2 ROLE_DEPTNAME ROLE_TITLE\n",
       "1  1    78766  72734        118079        118080        117878     117879\n",
       "2  2    40644   4378        117961        118327        118507     118863\n",
       "3  3    75443   2395        117961        118300        119488     118172\n",
       "4  4    43219  19986        117961        118225        118403     120773\n",
       "5  5    42093  50015        117961        118343        119598     118422\n",
       "6  6    44722   1755        117961        117962        119223     125793\n",
       "  ROLE_FAMILY_DESC ROLE_FAMILY ROLE_CODE\n",
       "1           118177       19721    117880\n",
       "2           122008      118398    118865\n",
       "3           301534      249618    118175\n",
       "4           136187      118960    120774\n",
       "5           300136      118424    118425\n",
       "6           146749      118643    125795"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "head(test)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<ol class=list-inline>\n",
       "\t<li>'ACTION'</li>\n",
       "\t<li>'RESOURCE'</li>\n",
       "\t<li>'MGR_ID'</li>\n",
       "\t<li>'ROLE_ROLLUP_1'</li>\n",
       "\t<li>'ROLE_ROLLUP_2'</li>\n",
       "\t<li>'ROLE_DEPTNAME'</li>\n",
       "\t<li>'ROLE_TITLE'</li>\n",
       "\t<li>'ROLE_FAMILY_DESC'</li>\n",
       "\t<li>'ROLE_FAMILY'</li>\n",
       "\t<li>'ROLE_CODE'</li>\n",
       "</ol>\n"
      ],
      "text/latex": [
       "\\begin{enumerate*}\n",
       "\\item 'ACTION'\n",
       "\\item 'RESOURCE'\n",
       "\\item 'MGR_ID'\n",
       "\\item 'ROLE_ROLLUP_1'\n",
       "\\item 'ROLE_ROLLUP_2'\n",
       "\\item 'ROLE_DEPTNAME'\n",
       "\\item 'ROLE_TITLE'\n",
       "\\item 'ROLE_FAMILY_DESC'\n",
       "\\item 'ROLE_FAMILY'\n",
       "\\item 'ROLE_CODE'\n",
       "\\end{enumerate*}\n"
      ],
      "text/markdown": [
       "1. 'ACTION'\n",
       "2. 'RESOURCE'\n",
       "3. 'MGR_ID'\n",
       "4. 'ROLE_ROLLUP_1'\n",
       "5. 'ROLE_ROLLUP_2'\n",
       "6. 'ROLE_DEPTNAME'\n",
       "7. 'ROLE_TITLE'\n",
       "8. 'ROLE_FAMILY_DESC'\n",
       "9. 'ROLE_FAMILY'\n",
       "10. 'ROLE_CODE'\n",
       "\n",
       "\n"
      ],
      "text/plain": [
       " [1] \"ACTION\"           \"RESOURCE\"         \"MGR_ID\"           \"ROLE_ROLLUP_1\"   \n",
       " [5] \"ROLE_ROLLUP_2\"    \"ROLE_DEPTNAME\"    \"ROLE_TITLE\"       \"ROLE_FAMILY_DESC\"\n",
       " [9] \"ROLE_FAMILY\"      \"ROLE_CODE\"       "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "names(trd)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<ol class=list-inline>\n",
       "\t<li>32769</li>\n",
       "\t<li>11</li>\n",
       "</ol>\n"
      ],
      "text/latex": [
       "\\begin{enumerate*}\n",
       "\\item 32769\n",
       "\\item 11\n",
       "\\end{enumerate*}\n"
      ],
      "text/markdown": [
       "1. 32769\n",
       "2. 11\n",
       "\n",
       "\n"
      ],
      "text/plain": [
       "[1] 32769    11"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/html": [
       "<ol class=list-inline>\n",
       "\t<li>58921</li>\n",
       "\t<li>11</li>\n",
       "</ol>\n"
      ],
      "text/latex": [
       "\\begin{enumerate*}\n",
       "\\item 58921\n",
       "\\item 11\n",
       "\\end{enumerate*}\n"
      ],
      "text/markdown": [
       "1. 58921\n",
       "2. 11\n",
       "\n",
       "\n"
      ],
      "text/plain": [
       "[1] 58921    11"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dim(train)\n",
    "dim(test)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<thead><tr><th></th><th scope=col>ACTION</th><th scope=col>RESOURCE</th><th scope=col>MGR_ID</th><th scope=col>ROLE_ROLLUP_1</th><th scope=col>ROLE_ROLLUP_2</th><th scope=col>ROLE_DEPTNAME</th><th scope=col>ROLE_TITLE</th><th scope=col>ROLE_FAMILY_DESC</th><th scope=col>ROLE_FAMILY</th><th scope=col>ROLE_CODE</th><th scope=col>id</th></tr></thead>\n",
       "<tbody>\n",
       "\t<tr><th scope=row>1</th><td>1</td><td>39353</td><td>85475</td><td>117961</td><td>118300</td><td>123472</td><td>117905</td><td>117906</td><td>290919</td><td>117908</td><td>100001</td></tr>\n",
       "\t<tr><th scope=row>2</th><td>1</td><td>17183</td><td>1540</td><td>117961</td><td>118343</td><td>123125</td><td>118536</td><td>118536</td><td>308574</td><td>118539</td><td>100002</td></tr>\n",
       "\t<tr><th scope=row>3</th><td>1</td><td>36724</td><td>14457</td><td>118219</td><td>118220</td><td>117884</td><td>117879</td><td>267952</td><td>19721</td><td>117880</td><td>100003</td></tr>\n",
       "\t<tr><th scope=row>4</th><td>1</td><td>36135</td><td>5396</td><td>117961</td><td>118343</td><td>119993</td><td>118321</td><td>240983</td><td>290919</td><td>118322</td><td>100004</td></tr>\n",
       "\t<tr><th scope=row>5</th><td>1</td><td>42680</td><td>5905</td><td>117929</td><td>117930</td><td>119569</td><td>119323</td><td>123932</td><td>19793</td><td>119325</td><td>100005</td></tr>\n",
       "\t<tr><th scope=row>6</th><td>0</td><td>45333</td><td>14561</td><td>117951</td><td>117952</td><td>118008</td><td>118568</td><td>118568</td><td>19721</td><td>118570</td><td>100006</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "\\begin{tabular}{r|lllllllllll}\n",
       "  & ACTION & RESOURCE & MGR_ID & ROLE_ROLLUP_1 & ROLE_ROLLUP_2 & ROLE_DEPTNAME & ROLE_TITLE & ROLE_FAMILY_DESC & ROLE_FAMILY & ROLE_CODE & id\\\\\n",
       "\\hline\n",
       "\t1 & 1 & 39353 & 85475 & 117961 & 118300 & 123472 & 117905 & 117906 & 290919 & 117908 & 100001\\\\\n",
       "\t2 & 1 & 17183 & 1540 & 117961 & 118343 & 123125 & 118536 & 118536 & 308574 & 118539 & 100002\\\\\n",
       "\t3 & 1 & 36724 & 14457 & 118219 & 118220 & 117884 & 117879 & 267952 & 19721 & 117880 & 100003\\\\\n",
       "\t4 & 1 & 36135 & 5396 & 117961 & 118343 & 119993 & 118321 & 240983 & 290919 & 118322 & 100004\\\\\n",
       "\t5 & 1 & 42680 & 5905 & 117929 & 117930 & 119569 & 119323 & 123932 & 19793 & 119325 & 100005\\\\\n",
       "\t6 & 0 & 45333 & 14561 & 117951 & 117952 & 118008 & 118568 & 118568 & 19721 & 118570 & 100006\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "  ACTION RESOURCE MGR_ID ROLE_ROLLUP_1 ROLE_ROLLUP_2 ROLE_DEPTNAME ROLE_TITLE\n",
       "1      1    39353  85475        117961        118300        123472     117905\n",
       "2      1    17183   1540        117961        118343        123125     118536\n",
       "3      1    36724  14457        118219        118220        117884     117879\n",
       "4      1    36135   5396        117961        118343        119993     118321\n",
       "5      1    42680   5905        117929        117930        119569     119323\n",
       "6      0    45333  14561        117951        117952        118008     118568\n",
       "  ROLE_FAMILY_DESC ROLE_FAMILY ROLE_CODE     id\n",
       "1           117906      290919    117908 100001\n",
       "2           118536      308574    118539 100002\n",
       "3           267952       19721    117880 100003\n",
       "4           240983      290919    118322 100004\n",
       "5           123932       19793    119325 100005\n",
       "6           118568       19721    118570 100006"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train$id<-100000+c(1:dim(train)[1])\n",
    "test$ACTION<-(-1)\n",
    "total<-rbind(train,test)\n",
    "head(total)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<ol class=list-inline>\n",
       "\t<li>91690</li>\n",
       "\t<li>16</li>\n",
       "</ol>\n"
      ],
      "text/latex": [
       "\\begin{enumerate*}\n",
       "\\item 91690\n",
       "\\item 16\n",
       "\\end{enumerate*}\n"
      ],
      "text/markdown": [
       "1. 91690\n",
       "2. 16\n",
       "\n",
       "\n"
      ],
      "text/plain": [
       "[1] 91690    16"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total$ACTION0<-total$ACTION\n",
    "total$ACTION<-(1-total$ACTION0)\n",
    "dim(total)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "total$y<-total$ACTION\n",
    "total$y[total$y>1]<-0\n",
    "total$ws <- 1\n",
    "total$ws[total$ACTION0<0]<-0\n",
    "total$dummy<-'A'\n",
    "total$split1<-0\n",
    "total$split1[total$ACTION0<0]<-2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [
    {
     "ename": "ERROR",
     "evalue": "Error in my.f2cnt(total, \"MGR_ID\", \"RESOURCE\"): could not find function \"sqldf\"\n",
     "output_type": "error",
     "traceback": [
      "Error in my.f2cnt(total, \"MGR_ID\", \"RESOURCE\"): could not find function \"sqldf\"\n"
     ]
    }
   ],
   "source": [
    "#data transformation\n",
    "#convert the categorical variables into factor variables so that we can use those factors as features in our model\n",
    "total$res_f<-factor(total$RESOURCE)\n",
    "total$res_f_id<-as.integer(total$res_f)\n",
    "total$mgr_f<-factor(total$MGR_ID)\n",
    "total$mgr_f_id<-as.integer(total$mgr_f)\n",
    "total$rocd_f<-factor(total$ROLE_CODE)\n",
    "total$rocd_f_id<-as.integer(total$rocd_f)\n",
    "total$rr1_f<-factor(total$ROLE_ROLLUP_1)\n",
    "total$rr1_f_id<-as.integer(total$rr1_f)\n",
    "total$rr2_f<-factor(total$ROLE_ROLLUP_2)\n",
    "total$rr2_f_id<-as.integer(total$rr2_f)\n",
    "total$rt_f<-factor(total$ROLE_TITLE)\n",
    "total$rt_f_id<-as.integer(total$rt_f)\n",
    "total$rf_f<-factor(total$ROLE_FAMILY)\n",
    "total$rf_f_id<-as.integer(total$rf_f)\n",
    "total$rd_f<-factor(total$ROLE_DEPTNAME)\n",
    "total$rd_f_id<-as.integer(total$rd_f)\n",
    "total$rfd_f<-factor(total$ROLE_FAMILY_DESC)\n",
    "total$rfd_f_id<-as.integer(total$rfd_f)\n",
    "\n",
    "total$mgr_res_cnt<-my.f2cnt(total,\"MGR_ID\",\"RESOURCE\")\n",
    "\n",
    "total$mgr_res_f<-as.factor(total$MGR_ID*10000+total$RESOURCE)\n",
    "total$mgr_res_f_id<-as.integer(total$mgr_res_f)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[1] \"RESOURCE\"\n"
     ]
    },
    {
     "ename": "ERROR",
     "evalue": "Error: could not find function \"sqldf\"\n",
     "output_type": "error",
     "traceback": [
      "Error: could not find function \"sqldf\"\n"
     ]
    }
   ],
   "source": [
    "#1 way count\n",
    "mean_t<-with(total[total$split1==0,],sum(ACTION)*1.0/length(ACTION))\n",
    "for (ii in 2:10){\n",
    "    print(names(total)[ii])\n",
    "    total$x<-total[,ii]\n",
    "    sum1<-sqldf(\"select x, sum(1) as cnt\n",
    "               from total group by 1\")\n",
    "    tmp<-sqldf(\"select cnt from total a left join sum1 b on a.x=b.x\")\n",
    "    total[,paste(names(total)[ii],\"_cnt\",sep=\"\")]<-tmp$cnt\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#2 way count\n",
    "my.f2cnt<-function(total2,vn1,vn2,filter=T){\n",
    "    df<-data.frame(f1=total2[,vn1],f2=total2[,vn2],filter=filter)\n",
    "    sum1<-sqldf(\"select f1, f2, count(*) as cnt from df where filter=1 group by 1,2\")\n",
    "    tmp<-sqldf(\"select b.cnt from df a left join sum1 b on a.f1=b.f1 and a.f2=b.f2\")\n",
    "    tmp$cnt[is.na(tmp$cnt)]<-0\n",
    "    return(tmp$cnt)\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#3way count\n",
    "my.f3cnt<-function(total2,vn1,vn2,vn3, filter=T){\n",
    "    df<-data.frame(f1=total2[,vn1],f2=total2[,vn2],f3=total2[,vn3], filter=filter)\n",
    "    sum1<-sqldf(\"select f1, f2, f3, count(*) as cnt from df where filter=1 group by 1,2, 3\")\n",
    "    tmp<-sqldf(\"select b.cnt from df a left join sum1 b on a.f1=b.f1 and a.f2=b.f2 and a.f3=b.f3\")\n",
    "    tmp$cnt[is.na(tmp$cnt)]<-0\n",
    "    return(tmp$cnt)\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#selected 2 way count for feature creation\n",
    "total$mgr_rocd_cnt<-my.f2cnt(total,\"MGR_ID\",\"ROLE_CODE\")\n",
    "total$mgr_rd_cnt<-my.f2cnt(total,\"MGR_ID\",\"ROLE_DEPTNAME\")\n",
    "total$mgr_rf_cnt<-my.f2cnt(total,\"MGR_ID\",\"ROLE_FAMILY\")\n",
    "total$mgr_rfd_cnt<-my.f2cnt(total,\"MGR_ID\",\"ROLE_FAMILY_DESC\")\n",
    "total$mgr_rt_cnt<-my.f2cnt(total,\"MGR_ID\",\"ROLE_TITLE\")\n",
    "total$mgr_rr1_cnt<-myf2cnt(total,\"MGR_ID\",\"ROLE_ROLLUP_1\")\n",
    "total$mgr_rr2_cnt<-myf2cnt(total,\"MGR_ID\",\"ROLE_ROLLUP_2\")\n",
    "\n",
    "total$res_rf_cnt<-my.f2cnt(total,\"RESOURCE\",\"ROLE_FAMILY\")\n",
    "total$res_rfd_cnt<-my.f2cnt(total,\"RESOURCE\",\"ROLE_FAMILY_DESC\")\n",
    "total$res_rt_cnt<-my.f2cnt(total,\"RESOURCE\",\"ROLE_TITLE\")\n",
    "total$res_rd_cnt<-my.f2cnt(total,\"RESOURCE\",\"ROLE_DEPTNAME\")\n",
    "total$res_rocd_cnt<-my.f2cnt(total,\"RESOURCE\",\"ROLE_CODE\")\n",
    "total$res_rr1_cnt<-myf2cnt(total,\"RESOURCE\",\"ROLE_ROLLUP_1\")\n",
    "total$res_rr2_cnt<-myf2cnt(total,\"RESOURCE\",\"ROLE_ROLLUP_2\")\n",
    "\n",
    "total$rr1_rf_cnt<-my.f2cnt(total,\"ROLE_ROLLUP_1\",\"ROLE_FAMILY\")\n",
    "total$rr1_rfd_cnt<-my.f2cnt(total,\"ROLE_ROLLUP_1\",\"ROLE_FAMILY_DESC\")\n",
    "total$rr1_rt_cnt<-my.f2cnt(total,\"ROLE_ROLLUP_1\",\"ROLE_TITLE\")\n",
    "total$rr1_rd_cnt<-my.f2cnt(total,\"ROLE_ROLLUP_1\",\"ROLE_DEPTNAME\")\n",
    "total$rr1_rocd_cnt<-my.f2cnt(total,\"ROLE_ROLLUP_1\",\"ROLE_CODE\")\n",
    "total$rr1_rr2_cnt<-myf2cnt(total,\"ROLE_ROLLUP_1\",\"ROLE_ROLLUP_2\")\n",
    "\n",
    "total$rr2_rf_cnt<-my.f2cnt(total,\"ROLE_ROLLUP_2\",\"ROLE_FAMILY\")\n",
    "total$rr2_rfd_cnt<-my.f2cnt(total,\"ROLE_ROLLUP_2\",\"ROLE_FAMILY_DESC\")\n",
    "total$rr2_rt_cnt<-my.f2cnt(total,\"ROLE_ROLLUP_2\",\"ROLE_TITLE\")\n",
    "total$rr2_rd_cnt<-my.f2cnt(total,\"ROLE_ROLLUP_2\",\"ROLE_DEPTNAME\")\n",
    "total$rr2_rocd_cnt<-my.f2cnt(total,\"ROLE_ROLLUP_2\",\"ROLE_CODE\")\n",
    "\n",
    "total$rf_rfd_cnt<-my.f2cnt(total,\"ROLE_FAMILY\",\"ROLE_FAMILY_DESC\")\n",
    "total$rf_rt_cnt<-my.f2cnt(total,\"ROLE_FAMILY\",\"ROLE_TITLE\")\n",
    "total$rf_rd_cnt<-my.f2cnt(total,\"ROLE_FAMILY\",\"ROLE_DEPTNAME\")\n",
    "total$rf_rocd_cnt<-my.f2cnt(total,\"ROLE_FAMILY\",\"ROLE_CODE\")\n",
    "\n",
    "total$rfd_rt_cnt<-my.f2cnt(total,\"ROLE_FAMILY_DESC\",\"ROLE_TITLE\")\n",
    "total$rfd_rd_cnt<-my.f2cnt(total,\"ROLE_FAMILY_DESC\",\"ROLE_DEPTNAME\")\n",
    "total$rfd_rocd_cnt<-my.f2cnt(total,\"ROLE_FAMILY_DESC\",\"ROLE_CODE\")\n",
    "\n",
    "total$rt_rd_cnt<-my.f2cnt(total,\"ROLE_TITLE\",\"ROLE_DEPTNAME\")\n",
    "total$rt_rocd_cnt<-my.f2cnt(total,\"ROLE_TITLE\",\"ROLE_CODE\")\n",
    "\n",
    "total$rd_rocd_cnt<-my.f2cnt(total,\"ROLE_DEPTNAME\",\"ROLE_CODE\")\n",
    "total$rd_rf_cnt<-my.f2cnt(total,\"ROLE_DEPTNAME\",\"ROLE_FAMILY\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#3 way count based feature creation for classification based models\n",
    "total$res_mgr_rr2_cnt<-myf3cnt(total,\"RESOURCE\",\"MGR_ID\",\"ROLE_ROLLUP_2\")\n",
    "total$res_mgr_rd_cnt<-myf3cnt(total,\"RESOURCE\",\"MGR_ID\",\"ROLE_DEPTNAME\")\n",
    "total$res_mgr_rcod_cnt<-myf3cnt(total,\"RESOURCE\",\"MGR_ID\",\"ROLE_CODE\")\n",
    "total$res_mgr_rdf_cnt<-myf3cnt(total,\"RESOURCE\",\"MGR_ID\",\"ROLE_FAMILY_DESC\")\n",
    "total$res_mgr_rf_cnt<-myf3cnt(total,\"RESOURCE\",\"MGR_ID\",\"ROLE_FAMILY\")\n",
    "total$rf_mgr_rd_cnt<-myf3cnt(total,\"ROLE_FAMILY\",\"MGR_ID\",\"ROLE_DEPTNAME\")\n",
    "total$rf_mgr_rr2_cnt<-myf3cnt(total,\"ROLE_FAMILY\",\"MGR_ID\",\"ROLE_ROLLUP_2\")\n",
    "total$res_rd_rf_cnt<-myf3cnt(total,\"RESOURCE\",\"ROLE_DEPTNAME\",\"ROLE_FAMILY\")\n",
    "total$res_rd_rr2_cnt<-myf3cnt(total,\"RESOURCE\",\"ROLE_DEPTNAME\",\"ROLE_ROLLUP_2\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#define one id which should show the combnation of role related variables\n",
    "sum1<-sqldf(\"select role_rollup_1, role_rollup_2, role_title, role_deptname, role_family, role_family_desc, role_code, count(*) as cnt from total group by 1,2,3,4,5,6,7\")\n",
    "\n",
    "sum1$role_id <- c(1:dim(sum1)[1])\n",
    "\n",
    "tmp<-sqldf(\"select role_id, b.cnt from total a join sum1 b on a.role_rollup_1=b.role_rollup_1 and \n",
    "a.role_rollup_2=b.role_rollup_2 and a.role_title=b.role_title and a.role_deptname=b.role_deptname and a.role_family=b.role_family\n",
    "and a.role_family_desc=b.role_family_desc and a.role_code=b.role_code\")\n",
    "\n",
    "total$rid<-tmp$role_id\n",
    "\n",
    "total$rid_res_cnt<-my.f2cnt(total,\"rid\",\"RESOURCE\")\n",
    "total$rid_cnt<-my.f2cnt(total,\"rid\",\"dummy\")\n",
    "\n",
    "total$all_but_res_cnt<-my.f2cnt(total,\"rid\",\"MGR_ID\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#role type id, this is a combination of role related variables\n",
    "sum1<-sqldf(\"select role_rollup_1, role_rollup_2, role_title, role_deptname, role_family, count(*) as cnt from total group by 1,2,3,4,5\")\n",
    "\n",
    "sum1$roletype_id <- c(1:dim(sum1)[1])\n",
    "\n",
    "tmp<-sqldf(\"select roletype_id, b.cnt from total a join sum1 b on a.role_rollup_1=b.role_rollup_1 and \n",
    "a.role_rollup_2=b.role_rollup_2 and a.role_title=b.role_title and a.role_deptname=b.role_deptname and a.role_family=b.role_family\")\n",
    "\n",
    "total$rtype_id<- tmp$roletype_id\n",
    "\n",
    "\n",
    "total$rtype_cnt<-my.f2cnt(total,\"rtype_id\",\"dummy\")\n",
    "total$all_but_rfd_cnt<-my.f2cnt(total,\"mgr_res_f_id\",\"rtype_id\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#feature extraction using eigen values using singular value decomposition method\n",
    "sum1<-sqldf(\"select rid, res_f_id, sum(1) as cnt from total group by 1, 2\")\n",
    "\n",
    "sm1<-sparseMatrix(i=sum1$res_f_id, j=sum1$rid, x=1)\n",
    "\n",
    "svd1<-irlba(sm1, nu=5, nv=5, adjust=3)\n",
    "\n",
    "total$rid_ev1<-svd1$v[total$rid,1]\n",
    "total$rid_ev2<-svd1$v[total$rid,2]\n",
    "total$rid_ev3<-svd1$v[total$rid,3]\n",
    "total$rid_ev4<-svd1$v[total$rid,4]\n",
    "total$rid_ev5<-svd1$v[total$rid,5]\n",
    "\n",
    "total$res_ev1<-svd1$u[total$res_f_id,1]\n",
    "total$res_ev2<-svd1$u[total$res_f_id,2]\n",
    "total$res_ev3<-svd1$u[total$res_f_id,3]\n",
    "total$res_ev4<-svd1$u[total$res_f_id,4]\n",
    "total$res_ev5<-svd1$u[total$res_f_id,5]\n",
    "\n",
    "\n",
    "sum1<-sqldf(\"select rid, mgr_f_id, sum(1) as cnt from total group by 1, 2\")\n",
    "\n",
    "sm1<-sparseMatrix(i=sum1$mgr_f_id, j=sum1$rid, x=1)\n",
    "\n",
    "svd1<-irlba(sm1, nu=5, nv=5, adjust=3)\n",
    "\n",
    "total$rid_mgr_ev1<-svd1$v[total$rid,1]\n",
    "total$rid_mgr_ev2<-svd1$v[total$rid,2]\n",
    "total$rid_mgr_ev3<-svd1$v[total$rid,3]\n",
    "total$rid_mgr_ev4<-svd1$v[total$rid,4]\n",
    "total$rid_mgr_ev5<-svd1$v[total$rid,5]\n",
    "\n",
    "total$mgr_rid_ev1<-svd1$u[total$mgr_f_id,1]\n",
    "total$mgr_rid_ev2<-svd1$u[total$mgr_f_id,2]\n",
    "total$mgr_rid_ev3<-svd1$u[total$mgr_f_id,3]\n",
    "total$mgr_rid_ev4<-svd1$u[total$mgr_f_id,4]\n",
    "total$mgr_rid_ev5<-svd1$u[total$mgr_f_id,5]\n",
    "\n",
    "sum1<-sqldf(\"select res_f_id, mgr_f_id, sum(1) as cnt from total group by 1, 2\")\n",
    "\n",
    "sm1<-sparseMatrix(i=sum1$mgr_f_id, j=sum1$res_f_id, x=1)\n",
    "\n",
    "svd1<-irlba(sm1, nu=5, nv=5, adjust=3)\n",
    "\n",
    "total$res_mgr_ev1<-svd1$v[total$res_f_id,1]\n",
    "total$res_mgr_ev2<-svd1$v[total$res_f_id,2]\n",
    "total$res_mgr_ev3<-svd1$v[total$res_f_id,3]\n",
    "total$res_mgr_ev4<-svd1$v[total$res_f_id,4]\n",
    "total$res_mgr_ev5<-svd1$v[total$res_f_id,5]\n",
    "\n",
    "total$mgr_res_ev1<-svd1$u[total$mgr_f_id,1]\n",
    "total$mgr_res_ev2<-svd1$u[total$mgr_f_id,2]\n",
    "total$mgr_res_ev3<-svd1$u[total$mgr_f_id,3]\n",
    "total$mgr_res_ev4<-svd1$u[total$mgr_f_id,4]\n",
    "total$mgr_res_ev5<-svd1$u[total$mgr_f_id,5]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#randomized leave-one-out average actual for categorical variables\n",
    "my_exp1<-function(d1, vn1, vn2, y, vnp, filter, cred_k, r_k=.3){\n",
    "  d2<-d1[, c(vn1, vn2, y, vnp)]\n",
    "  names(d2)<-c(\"f1\", \"f2\", \"a\", \"p\")\n",
    "  d2$filter<-filter\n",
    "  sum1<-sqldf(\"select f1, f2, sum(1) as cnt, sum(p) as sump, sum(a) as suma from d2 where filter=1 group by 1,2\")\n",
    "  tmp1<-sqldf(\"select a.p, b.cnt, b.sump, b.suma from d2 a left join sum1 b on a.f1=b.f1 and a.f2=b.f2\")\n",
    "  tmp1$cnt[is.na(tmp1$cnt)]<-0\n",
    "  tmp1$avgp<-with(tmp1, sump/cnt)\n",
    "  tmp1$avgp[is.na(tmp1$avgp)]<-0\n",
    "  tmp1$suma[is.na(tmp1$suma)]<-0\n",
    "  tmp1$cnt[filter]<-tmp1$cnt[filter]-1\n",
    "  tmp1$suma[filter]<-tmp1$suma[filter]-d1$y[filter]\n",
    "  tmp1$exp_a<-with(tmp1, suma/cnt)\n",
    "  tmp1$adj_a<-with(tmp1, (suma+p*cred_k)/(cnt+cred_k))\n",
    "  tmp1$exp_a[is.na(tmp1$exp_a)]<-tmp1$p[is.na(tmp1$exp_a)]\n",
    "  tmp1$adj_a[is.na(tmp1$adj_a)]<-tmp1$p[is.na(tmp1$adj_a)]\n",
    "  tmp1$adj_a[filter]<-tmp1$adj_a[filter]*(1+(runif(sum(filter))-0.5)*r_k)\n",
    "  return(tmp1)\n",
    "}\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#calc exps for selected one way and 2 ways\n",
    "tv_spec_trans<-function(total, calc_pred=T) {\n",
    "  \n",
    "  \n",
    "  if(calc_pred) {\n",
    "    total$pred0<-with(total[total$split1==0,], mean(ACTION*1.0))\n",
    "    total$pred0_offset<-0\n",
    "  } else {\n",
    "    total$pred0_offset<-total$pred0\n",
    "    \n",
    "  }\n",
    "  total$dummy<-1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "t1<-my_exp1(total, \"MGR_ID\", \"rid\", \"ACTION\", \"pred0\", total$split1==0, 40, r_k=r_k)\n",
    "  total$exp_all_but_res<-t1$adj_a-total$pred0_offset\n",
    "  \n",
    "  t1<-my_exp1(total, \"dummy\", \"rid\", \"ACTION\", \"pred0\", total$split1==0, 40, r_k=r_k)\n",
    "  total$exp_rid<-t1$adj_a-total$pred0_offset\n",
    "  \n",
    "  \n",
    "  t1<-my_exp1(total, \"MGR_ID\", \"dummy\", \"ACTION\", \"pred0\", total$split1==0, 40, r_k=r_k)\n",
    "  total$exp_mgr<-t1$adj_a-total$pred0_offset\n",
    "  \n",
    "  t1<-my_exp1(total, \"RESOURCE\", \"dummy\", \"ACTION\", \"pred0\", total$split1==0, 40, r_k=r_k)\n",
    "  total$exp_res<-t1$adj_a-total$pred0_offset\n",
    "  \n",
    "  t1<-my_exp1(total, \"ROLE_FAMILY_DESC\", \"dummy\", \"ACTION\", \"pred0\", total$split1==0, 40, r_k=r_k)\n",
    "  total$exp_rfd<-t1$adj_a-total$pred0_offset\n",
    "  \n",
    "  t1<-my_exp1(total, \"RESOURCE\", \"ROLE_DEPTNAME\", \"ACTION\", \"pred0\", total$split1==0, 40, r_k=r_k)\n",
    "  total$exp_res_rd<-t1$adj_a-total$pred0_offset\n",
    "  \n",
    "  \n",
    "  t1<-my_exp1(total, \"ROLE_TITLE\", \"dummy\", \"ACTION\", \"pred0\", total$split1==0, 20, r_k=r_k)\n",
    "  total$exp_rt<-t1$adj_a-total$pred0_offset\n",
    "  \n",
    "  t1<-my_exp1(total, \"ROLE_ROLLUP_1\", \"dummy\", \"ACTION\", \"pred0\", total$split1==0, 20, r_k=r_k)\n",
    "  total$exp_rr1<-t1$adj_a-total$pred0_offset\n",
    "  \n",
    "  t1<-my_exp1(total, \"ROLE_ROLLUP_2\", \"dummy\", \"ACTION\", \"pred0\", total$split1==0, 20, r_k=r_k)\n",
    "  total$exp_rr2<-t1$adj_a-total$pred0_offset\n",
    "  \n",
    "  t1<-my_exp1(total, \"ROLE_FAMILY\", \"dummy\", \"ACTION\", \"pred0\", total$split1==0, 20, r_k=r_k)\n",
    "  total$exp_rf<-t1$adj_a-total$pred0_offset\n",
    "  \n",
    "  t1<-my_exp1(total, \"ROLE_CODE\", \"dummy\", \"ACTION\", \"pred0\", total$split1==0, 20, r_k=r_k)\n",
    "  total$exp_rocd<-t1$adj_a-total$pred0_offset\n",
    "  \n",
    "  \n",
    "  t1<-my_exp1(total, \"ROLE_DEPTNAME\", \"dummy\", \"ACTION\", \"pred0\", total$split1==0, 20, r_k=r_k)\n",
    "  total$exp_rd<-t1$adj_a-total$pred0_offset\n",
    "  \n",
    "  t1<-my_exp1(total, \"MGR_ID\", \"RESOURCE\", \"ACTION\", \"pred0\", total$split1==0, 40, r_k=r_k)\n",
    "  total$exp_mgr_res<-t1$adj_a-total$pred0_offset\n",
    "  \n",
    "  t1<-my_exp1(total, \"MGR_ID\", \"ROLE_TITLE\", \"ACTION\", \"pred0\", total$split1==0, 40, r_k=r_k)\n",
    "  total$exp_mgr_rt<-t1$adj_a-total$pred0_offset\n",
    "  \n",
    "  t1<-my_exp1(total, \"MGR_ID\", \"ROLE_FAMILY_DESC\", \"ACTION\", \"pred0\", total$split1==0, 40, r_k=r_k)\n",
    "  total$exp_mgr_rfd<-t1$adj_a-total$pred0_offset\n",
    "  \n",
    "  t1<-my_exp1(total, \"RESOURCE\", \"ROLE_ROLLUP_2\", \"ACTION\", \"pred0\", total$split1==0, 40, r_k=r_k)\n",
    "  total$exp_res_rr2<-t1$adj_a-total$pred0_offset\n",
    "  \n",
    "  t1<-my_exp1(total, \"RESOURCE\", \"ROLE_TITLE\", \"ACTION\", \"pred0\", total$split1==0, 40, r_k=r_k)\n",
    "  total$exp_res_rt<-t1$adj_a-total$pred0_offset\n",
    "  \n",
    "  t1<-my_exp1(total, \"RESOURCE\", \"rid\", \"ACTION\", \"pred0\", total$split1==0, 40, r_k=r_k)\n",
    "  total$exp_res_rid<-t1$adj_a-total$pred0_offset\n",
    "  \n",
    "  t1<-my_exp1(total, \"ROLE_FAMILY\", \"ROLE_ROLLUP_2\", \"ACTION\", \"pred0\", total$split1==0, 40, r_k=r_k)\n",
    "  total$exp_rf_rr2<-t1$adj_a-total$pred0_offset\n",
    "  \n",
    "  t1<-my_exp1(total, \"ROLE_DEPTNAME\", \"ROLE_ROLLUP_2\", \"ACTION\", \"pred0\", total$split1==0, 40, r_k=r_k)\n",
    "  total$exp_rd_rr2<-t1$adj_a-total$pred0_offset\n",
    "  \n",
    "  t1<-my_exp1(total, \"ROLE_DEPTNAME\", \"rfd_f\", \"ACTION\", \"pred0\", total$split1==0, 40, r_k=r_k)\n",
    "  total$exp_rd_rfd<-t1$adj_a-total$pred0_offset\n",
    "  \n",
    "  t1<-my_exp1(total, \"rfd_f\", \"rocd_f\", \"ACTION\", \"pred0\", total$split1==0, 40, r_k=r_k)\n",
    "  total$exp_rfd_rocd<-t1$adj_a-total$pred0_offset\n",
    "  \n",
    "  \n",
    "  return(total)\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#model specification for tree based models \n",
    "mdl_def_tree<-paste(\"RESOURCE+ROLE_ROLLUP_1+ROLE_ROLLUP_2+ROLE_DEPTNAME+ROLE_TITLE\",\n",
    "                    \"+ROLE_FAMILY_DESC+ROLE_FAMILY+MGR_ID+ROLE_CODE\",\n",
    "                    \n",
    "                    \"+RESOURCE_cnt+ROLE_ROLLUP_1_cnt+ROLE_ROLLUP_2_cnt+ROLE_DEPTNAME_cnt+ROLE_TITLE_cnt\",\n",
    "                    \"+ROLE_FAMILY_DESC_cnt+ROLE_FAMILY_cnt+MGR_ID_cnt+mgr_res_cnt+ROLE_CODE_cnt\",\n",
    "                    \n",
    "                    \"+res_rf_cnt+res_rfd_cnt+res_rt_cnt+res_rr1_cnt+res_rr2_cnt+res_rd_cnt\",\n",
    "                    \"+mgr_rf_cnt+mgr_rfd_cnt+mgr_rt_cnt+mgr_rr1_cnt+mgr_rr2_cnt+mgr_rd_cnt\",\n",
    "                    \"+res_mgr_rr2_cnt+res_mgr_rd_cnt+res_rd_rr2_cnt\",\n",
    "                    \"+rr1_rr2_cnt+rr1_rf_cnt+rr1_rfd_cnt+rr1_rt_cnt+rr1_rd_cnt\",\n",
    "                    \"+rr2_rf_cnt+rr2_rfd_cnt+rr2_rt_cnt+rr2_rd_cnt\",\n",
    "                    \"+rf_rfd_cnt+rf_rt_cnt+rf_rd_cnt+rfd_rt_cnt+rfd_rd_cnt\",\n",
    "                    \"+rt_rd_cnt+rid_res_cnt\",\n",
    "                    \n",
    "                    \"+exp_res+exp_mgr+exp_rfd+exp_res_rd + exp_res_rt + exp_res_rr2\",\n",
    "                    \"+exp_mgr_res + exp_mgr_rt + exp_mgr_rfd +all_but_res_cnt +exp_rid\",\n",
    "                    \"+exp_res_rid+ exp_all_but_res\"\n",
    "                    ,\n",
    "                    \n",
    "                    \"+rid_ev1+rid_ev2+rid_ev3+res_ev1+res_ev2+res_ev3+rid_ev4+rid_ev5+res_ev4+res_ev5\",\n",
    "                    \"+rid_mgr_ev1+rid_mgr_ev2+rid_mgr_ev3+mgr_rid_ev1+mgr_rid_ev2+mgr_rid_ev3+rid_mgr_ev4+rid_mgr_ev5+mgr_rid_ev4+mgr_rid_ev5\",\n",
    "                    \"+mgr_res_ev1+mgr_res_ev2+mgr_res_ev3+res_mgr_ev1+res_mgr_ev2+res_mgr_ev3+mgr_res_ev4+mgr_res_ev5+res_mgr_ev4+res_mgr_ev5\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#model specification for glmnet\n",
    "mdl_def_gnet<-paste(\"y~log(exp_all_but_res)+log(exp_res)+log(exp_rfd)+log(exp_res_rd)+log(exp_rd+exp_mgr)+log(exp_rid)\",\n",
    "                    \"+log(exp_mgr_res)+log(exp_rr2)+log(exp_all_but_res)*log(exp_rid)\",\n",
    "                    \"+log(exp_rid)+log(exp_rid)*log(exp_rid)+log(exp_res_rr2)+log(exp_res_rr2)*log(exp_res_rr2)\",\n",
    "                    \"+log(exp_mgr_rt)+log(exp_mgr_rt)*log(exp_mgr_rt)+log(exp_res_rd)+log(exp_res_rd)*log(exp_res_rd)\",\n",
    "                    \"+log(exp_mgr)+log(exp_mgr)*log(exp_mgr)+log(exp_mgr_rfd)+log(exp_mgr_rfd)*log(exp_mgr_rfd)\",\n",
    "                    \"+log(exp_rf)+log(exp_rocd)+log(exp_res_rt)+log(exp_res_rid)+log(exp_res_rr2)+log(exp_res_rt)\",\n",
    "                    \"+log(res_rd_cnt-0.5)+log(rid_res_cnt)+log(mgr_rt_cnt)\",\n",
    "                    \"+log(exp_all_but_res/(1-exp_all_but_res))+log(exp_all_but_res)*log(exp_all_but_res)\",\n",
    "                    \"+log(exp_res)*log(exp_res) + log(exp_res)*log(exp_all_but_res) +log(res_rr2_cnt)\",\n",
    "                    \"+log(res_rd_cnt-0.5)*log(rid_res_cnt)\",\n",
    "                    \"+log(RESOURCE_cnt)++log(RESOURCE_cnt)*+log(RESOURCE_cnt) +log(all_but_res_cnt)\",\n",
    "                    \"+log(mgr_res_cnt)+log(mgr_res_cnt)*log(mgr_res_cnt)\",\n",
    "                    \"+log(rt_rd_cnt)+log(rt_rd_cnt)*log(rt_rd_cnt)\",\n",
    "                    \"+log(mgr_rd_cnt)+log(mgr_rd_cnt)*log(mgr_rd_cnt)\",\n",
    "                    \"+log(ROLE_TITLE_cnt)+log(ROLE_TITLE_cnt)*log(ROLE_TITLE_cnt)\",\n",
    "                    \"+log(MGR_ID_cnt)+log(MGR_ID_cnt)*log(MGR_ID_cnt)\",\n",
    "                    \"+log(ROLE_DEPTNAME_cnt)+log(ROLE_DEPTNAME_cnt)*log(ROLE_DEPTNAME_cnt)\",\n",
    "                    \"+log(ROLE_ROLLUP_2_cnt)+log(ROLE_FAMILY_cnt)+log(ROLE_FAMILY_DESC_cnt)+log(ROLE_ROLLUP_1_cnt)\",\n",
    "                    \"+log(ROLE_CODE_cnt)+log(rr2_rd_cnt)\",\n",
    "                    \"+log(res_mgr_rr2_cnt)+log(rf_rt_cnt)+log(res_rfd_cnt)+log(rf_rt_cnt)\",\n",
    "                    \"+log(exp_rd_rr2)\",\n",
    "                    \"+rid_ev1+rid_ev2+rid_ev3+res_ev1+res_ev2+res_ev3+rid_ev4+rid_ev5+res_ev4+res_ev5\",\n",
    "                    \"+rid_mgr_ev1+rid_mgr_ev2+rid_mgr_ev3+mgr_rid_ev1+mgr_rid_ev2+mgr_rid_ev3+rid_mgr_ev4+rid_mgr_ev5+mgr_rid_ev4+mgr_rid_ev5\",\n",
    "                    \"+mgr_res_ev1+mgr_res_ev2+mgr_res_ev3+res_mgr_ev1+res_mgr_ev2+res_mgr_ev3+mgr_res_ev4+mgr_res_ev5+res_mgr_ev4+res_mgr_ev5\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#model specification for tree based models without exp variables\n",
    "mdl_def_tree_noexp<-paste(\"RESOURCE+ROLE_ROLLUP_1+ROLE_ROLLUP_2+ROLE_DEPTNAME+ROLE_TITLE\",\n",
    "                          \"+ROLE_FAMILY_DESC+ROLE_FAMILY+MGR_ID+ROLE_CODE\",\n",
    "                          \n",
    "                          \"+RESOURCE_cnt+ROLE_ROLLUP_1_cnt+ROLE_ROLLUP_2_cnt+ROLE_DEPTNAME_cnt+ROLE_TITLE_cnt\",\n",
    "                          \"+ROLE_FAMILY_DESC_cnt+ROLE_FAMILY_cnt+MGR_ID_cnt+mgr_res_cnt+ROLE_CODE_cnt\",\n",
    "                          \n",
    "                          \"+res_rf_cnt+res_rfd_cnt+res_rt_cnt+res_rr1_cnt+res_rr2_cnt+res_rd_cnt\",\n",
    "                          \"+mgr_rf_cnt+mgr_rfd_cnt+mgr_rt_cnt+mgr_rr1_cnt+mgr_rr2_cnt+mgr_rd_cnt\",\n",
    "                          \"+res_mgr_rr2_cnt+res_mgr_rd_cnt+res_rd_rr2_cnt\",\n",
    "                          \"+rr1_rr2_cnt+rr1_rf_cnt+rr1_rfd_cnt+rr1_rt_cnt+rr1_rd_cnt\",\n",
    "                          \"+rr2_rf_cnt+rr2_rfd_cnt+rr2_rt_cnt+rr2_rd_cnt\",\n",
    "                          \"+rf_rfd_cnt+rf_rt_cnt+rf_rd_cnt+rfd_rt_cnt+rfd_rd_cnt\",\n",
    "                          \"+rt_rd_cnt+rid_res_cnt\",\n",
    "                          \"+rid_ev1+rid_ev2+rid_ev3+res_ev1+res_ev2+res_ev3+rid_ev4+rid_ev5+res_ev4+res_ev5\",\n",
    "                          \"+rid_mgr_ev1+rid_mgr_ev2+rid_mgr_ev3+mgr_rid_ev1+mgr_rid_ev2+mgr_rid_ev3+rid_mgr_ev4+rid_mgr_ev5+mgr_rid_ev4+mgr_rid_ev5\",\n",
    "                          \"+mgr_res_ev1+mgr_res_ev2+mgr_res_ev3+res_mgr_ev1+res_mgr_ev2+res_mgr_ev3+mgr_res_ev4+mgr_res_ev5+res_mgr_ev4+res_mgr_ev5\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#sort the dataset so that training obs appear first\n",
    "total<-total[order(total$split1),]\n",
    "\n",
    "#create exp variables (leave one out average actual by categorical variable)\n",
    "r_k<-0.2\n",
    "total<-tv_spec_trans(total)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#Extreme Randomized Trees \n",
    "mt<-model.matrix(as.formula(paste(\"y~\", mdl_def_tree)), data=total)\n",
    "ert1<-extraTrees(mt[total$split1==0,], total$y[total$split1==0], mtry=4, nodesize=10, numTreads=4, ntree=2000, numRandomCuts=6)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#GLMNET \n",
    "m1<-model.matrix(as.formula(mdl_def_gnet), data=total)\n",
    "filter_t<-total$split1==0\n",
    "gnet1<-cv.glmnet(m1[filter_t,],total$ACTION[filter_t],family=\"binomial\", alpha=0.8, lambda.min.ratio=0.0001)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#GBM\n",
    "t_frac<-with(total, sum(split1==0)/length(split1))\n",
    "g2<-gbm(as.formula(paste(\"y~\", mdl_def_tree)),weights=total$ws, #var.monotone=mono,\n",
    "        data=total, train.fraction=t_frac, n.trees=1000, interaction.depth=8, n.minobsinnode=20, distribution=\"bernoulli\", \n",
    "        shrinkage=0.03, bag.fraction=0.5, keep.data=F, verbose=T)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#Random Forest\n",
    "total$y_factor<-factor(total$y)\n",
    "rf2<-randomForest(as.formula(paste(\"y_factor~\", mdl_def_tree))\n",
    "                  , data=total, subset=(total$split1==0), ntree=2000, mtry=3, nodesize=10, replace=F, \n",
    "                  , importance=TRUE, do.trace=10, maxnodes=500, sampsize=0.5*sum(total$split1==0), \n",
    "                  xtest=NULL,ytest=NULL,classwt=NULL)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#noexp GBM@3000\n",
    "g2_no_exp1<-gbm(as.formula(paste(\"y~\", mdl_def_tree_noexp)), weights=total$ws, \n",
    "           data=total, train.fraction=t_frac, n.trees=3000, interaction.depth=8, n.minobsinnode=20, distribution=\"bernoulli\", \n",
    "           shrinkage=0.03, bag.fraction=0.5, keep.data=F, verbose=T)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#noexp ert@1000\n",
    "mt_noexp<-model.matrix(as.formula(paste(\"y~\", mdl_def_tree_noexp)), data=total)\n",
    "ert_no_exp1<-extraTrees(mt_noexp[total$split1==0,], total$y[total$split1==0], mtry=3, nodesize=10\n",
    "                 , numTreads=4, ntree=1000, numRandomCuts=6, evenCuts=F)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#score all models\n",
    "total$pred_ert<-predict(ert1, newdata=mt)\n",
    "total$pred_gnet<-predict(gnet1, newx=m1, s=\"lambda.min\", type=\"response\")[,1]\n",
    "total$pred_gbm<-predict(g2, newdata=total, type=\"response\", n.trees=1000)\n",
    "total$pred_rf<-predict(rf2, newdata=total, type=\"prob\")[,2]\n",
    "total$pred_gbm_noexp<-1-1/(1+exp(g2_no_exp1$fit))\n",
    "total$pred_ert_noexp<-predict(ert_no_exp1, newdata=mt_noexp)\n",
    "\n",
    "save(ert1, gnet1, g2, rf2, g2_no_exp1, ert_no_exp1, file=\"__final_models.RData\")\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [conda env:Anaconda3]",
   "language": "python",
   "name": "conda-env-Anaconda3-py"
  },
  "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.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}