Allstate - Create train and test datasets with dummy vars.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "\n",
    "## Project:  Allstate Claims Severity\n",
    "#### Author:   Joshep Downs, James Peng, Megan Pera, Diana Rodenberger \n",
    "#### Purpose:  Create dummy variables for train and test data and save datasets to .csv files\n",
    "#### Created:  12/06/2016\n",
    "\n",
    "### Team name in Kaggle: UCB_207_1\n",
    "\n",
    "## Link to Leaderboard\n",
    "https://www.kaggle.com/c/allstate-claims-severity/leaderboard\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%matplotlib inline\n",
    "import unittest\n",
    "\n",
    "# General libraries.\n",
    "import re, os, sys\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import logging\n",
    "from sklearn.pipeline import Pipeline\n",
    "#from sklearn.feature_extraction import \n",
    "from sklearn import preprocessing\n",
    "\n",
    "from sklearn.utils import shuffle\n",
    "from sklearn.model_selection import train_test_split\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Important note:\n",
    "\n",
    "When transforming categorical variables into dummy variables, we get a different set of variables for the training data and the test data. The train data and the test data have different categories for some categorical variables; thus, when we tranform  categorical variables to dummy variables then a different set of dummy variables for the train and test data. \n",
    "\n",
    "The following code addresses this problem by adding dummy variables to the train data that only exist in the set data and viceversa. The newly added dummy variables in each set are set to 0.\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Load train data and test data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(188318, 132)"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_train = pd.read_csv('./data_in/train.csv')\n",
    "df_train.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(125546, 131)"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_test = pd.read_csv('./data_in/test.csv')\n",
    "df_test.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Extract categorical variables from train and test data\n",
    "\n",
    " 1) Extract data and load into dataframe\n",
    " \n",
    " 2) Create dummy variables for categorical variables\n",
    " \n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#Extract categorical variables from AllState dataset\n",
    "def GetColNamesByType(df, col_type):\n",
    "    cat_cols=[]\n",
    "    \n",
    "    for c in df.columns:\n",
    "        if c.find(col_type) >=0: # -1: substring not found, >=0, starting index\n",
    "            cat_cols.append(c)\n",
    "       \n",
    "    return cat_cols"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "cat_cols_train=GetColNamesByType(df=df_train,col_type='cat')\n",
    "#convert categorical variables into dummy variables for train data\n",
    "df_dummy_vars_train=pd.get_dummies(df_train[cat_cols_train])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "cat_cols_test=GetColNamesByType(df=df_train,col_type='cat')\n",
    "#convert categorical variables into dummy variables for test data\n",
    "df_dummy_vars_test=pd.get_dummies(df_test[cat_cols_test])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Identify dummy variables in train data that do not exist in test data and viceversa. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Num of dummy vars in train set but not in test set: 74\n",
      "Example of 10 vars in train set but not in test set:  ['cat101_N' 'cat101_U' 'cat102_H' 'cat102_J' 'cat105_R' 'cat105_S'\n",
      " 'cat109_AG' 'cat109_AK' 'cat109_B' 'cat109_BF']\n",
      "Num of dummy vars in train set but not in test set:  37\n",
      "Example of 10 vars in train set but not in test set:  ['cat103_M' 'cat106_Q' 'cat109_AD' 'cat110_BH' 'cat110_CA' 'cat110_EN'\n",
      " 'cat111_L' 'cat113_AA' 'cat113_R' 'cat116_A']\n"
     ]
    }
   ],
   "source": [
    "#get the list of dummy variables for each set.\n",
    "dummy_cols_train=list(df_dummy_vars_train.columns)\n",
    "dummy_cols_test=list(df_dummy_vars_test.columns)\n",
    "\n",
    "#get list of dummy vars that exist only in training set\n",
    "cols_train_only= np.setdiff1d(dummy_cols_train,dummy_cols_test)\n",
    "print('Num of dummy vars in train set but not in test set:',len(cols_train_only) )\n",
    "print('Example of 10 vars in train set but not in test set: ', cols_train_only[0:10])\n",
    "\n",
    "#get list of dummy vars that exist only in test set\n",
    "cols_test_only= np.setdiff1d(dummy_cols_test,dummy_cols_train)\n",
    "print('Num of dummy vars in train set but not in test set: ', len(cols_test_only))\n",
    "print('Example of 10 vars in train set but not in test set: ',cols_test_only[0:10])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Add missing dummy variables to train set and test set to create a complete set of variables for both train and test data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#add dummy variables to train set that only exist in test set\n",
    "for column in cols_test_only:\n",
    "    dummy_cols_train.append(column)\n",
    "    #add the column at the beginning of the dataset\n",
    "    df_dummy_vars_train.insert(0, column, 0, allow_duplicates=False)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#add dummy variables to test set that only exist in train set\n",
    "for column in cols_train_only:\n",
    "    dummy_cols_test.append(column)\n",
    "    df_dummy_vars_test.insert(0, column, 0, allow_duplicates=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Sort dummy variables according to the order defined by AllState\n",
    "\n",
    "AllState ordered categorical variables using the number first, and then the characters. AllState used a convention similar to Excel to sort the character part of the column name where 'Z' comes before 'AA'.\n",
    "\n",
    "Steps to sort dummy variables: \n",
    "\n",
    "    1) Standardize names to meet the format 'catXXX_YY' where XXX is the original number of the categorical variable padded with zeros and YY are the original characters in the categorical variable padded with zeros.\n",
    "    \n",
    "    2) Create array of sorted indeces.\n",
    "    \n",
    "    3) Sort dummy variables using the index array. \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "\n",
    "The following function transforms names of categorical variables to meet the format 'catXXX_YY' by adding zeros to numbers (XXX) with less than 3 digits and to names (YY) with less than one letter.\n",
    "For example: \n",
    "    'cat2_A' --> 'cat002_0A'\n",
    "    \n",
    "Prior to this transformation, the string comparison 'cat20_AA' > 'cat101_C returns 'True'. After transformation, the string comparison 'cat020_AA' > cat101_0C' returns False."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "### The following function transforms the number in the column to be three digits (with zero padding) and\n",
    "### character part of the column (e.g. 'A', 'MB') to be two characters (by adding '0' for columns with one character) \n",
    "### with the purpose of sorting the columns in the same order provided by AllState.\n",
    "\n",
    "### Example:  before transformation column 'cat20_AA' > 'cat101_C'\n",
    "###           After transformation column 'cat020_AA' < cat101_0C' \n",
    "\n",
    "def RenameCatVars(columns):\n",
    "\n",
    "    trans_columns=[]\n",
    "    new_col=''\n",
    "    for column in columns:\n",
    "        new_col=column\n",
    "        if column[:3]=='cat':\n",
    "            delim_pos=int(np.core.defchararray.find(column,'_'))\n",
    "            col_num=column[3:delim_pos]\n",
    "            col_char=column[delim_pos+1:]\n",
    "            \n",
    "            #pad column number with zeros\n",
    "            if len(col_num)==1:\n",
    "                col_num='00'+ col_num\n",
    "            elif len(col_num)==2:\n",
    "                col_num='0'+ col_num\n",
    "\n",
    "            #pad column char with '0'\n",
    "\n",
    "            if len(col_char)==1:\n",
    "                col_char='0'+ col_char\n",
    "            \n",
    "            new_col='cat'+ col_num + '_' + col_char\n",
    "        trans_columns.append(new_col)\n",
    "        \n",
    "    return np.array(trans_columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "\n",
    "def SortCatVariables(columns):\n",
    "    \n",
    "    #purpose: sort dummy variables in the order provided by AllState\n",
    "    \n",
    "    sorted_cols=[]\n",
    "    \n",
    "    #rename cat variables prior to sort them\n",
    "    renamed_cols=RenameCatVars(columns)\n",
    "    #create array with sorted indexes\n",
    "    sorted_indx=np.argsort(renamed_cols)\n",
    "\n",
    "    #create output array with original names sorted as defined by AllState\n",
    "    for ii in range(len(sorted_indx)):\n",
    "        sorted_cols.append(columns[sorted_indx[ii]])\n",
    "    return sorted_cols"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Sort complete set of dummy vars in train data and set data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "dummy_cols_train=SortCatVariables(dummy_cols_train)\n",
    "dummy_cols_test=SortCatVariables(dummy_cols_test)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Add missing dummy variables to train data and test data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df_dummy_vars_train=df_dummy_vars_train[dummy_cols_train]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df_dummy_vars_test=df_dummy_vars_test[dummy_cols_test]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create final X, y and Id sets for train and test data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "\n",
    "cont_cols_test=GetColNamesByType(df=df_test,col_type='cont')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#get the list of continous variables\n",
    "cont_cols_train=GetColNamesByType(df=df_train,col_type='cont')\n",
    "\n",
    "#create dataset with predictors\n",
    "X_train = pd.concat([df_dummy_vars_train, df_train[cont_cols_train]], axis=1)\n",
    "#create dataset with dependent variable\n",
    "y_train = np.log10(df_train.loss)\n",
    "#create additioanl dataset with claim ids\n",
    "id_train = df_train.id\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#get the list of continous variables\n",
    "cont_cols_test=GetColNamesByType(df=df_test,col_type='cont')\n",
    "\n",
    "#create dataset with predictors\n",
    "X_test = pd.concat([df_dummy_vars_test, df_test[cont_cols_test]], axis=1)\n",
    "\n",
    "#create additioanl dataset with claim ids\n",
    "id_test = df_test.id\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(125546, 1190)\n",
      "(125546,)\n",
      "(188318, 1190)\n",
      "(188318,)\n",
      "(188318,)\n"
     ]
    }
   ],
   "source": [
    "#Training dataset should have one more variable (loss)\n",
    "print(X_test.shape)\n",
    "print(id_test.shape)\n",
    "print(X_train.shape)\n",
    "print(y_train.shape)\n",
    "print(id_train.shape)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Save final datasets to files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 162,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "X_test.to_csv('./data_temp/X_dummies_test.csv', encoding='utf-8', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 163,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "id_test.to_csv('./data_temp/id_test.csv', encoding='utf-8', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 164,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "X_train.to_csv('./data_temp/X_dummies_train.csv', encoding='utf-8', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 165,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "y_train.to_csv('./data_temp/y_train.csv', encoding='utf-8', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 166,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "id_train.to_csv('./data_temp/id_train.csv', encoding='utf-8', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python [Root]",
   "language": "python",
   "name": "Python [Root]"
  },
  "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
}