Insurance_main_features.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/usr/local/lib/python2.7/site-packages/sklearn/cross_validation.py:44: DeprecationWarning: This module was deprecated in version 0.18 in favor of the model_selection module into which all the refactored classes and functions are moved. Also note that the interface of the new CV iterators are different from that of this module. This module will be removed in 0.20.\n",
      "  \"This module will be removed in 0.20.\", DeprecationWarning)\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os\n",
    "import glob\n",
    "from datetime import datetime\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "from sklearn.model_selection import train_test_split, GridSearchCV,StratifiedKFold\n",
    "from sklearn.metrics import mean_absolute_error\n",
    "from sklearn.cross_validation import KFold\n",
    "from sklearn import preprocessing\n",
    "from lightgbm import LGBMRegressor\n",
    "import lightgbm as lgb\n",
    "import xgboost as xgb\n",
    "from sklearn.cluster import KMeans\n",
    "from sklearn import metrics\n",
    "from sklearn.feature_extraction import FeatureHasher\n",
    "from sklearn import preprocessing"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Preprocessing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['/Users/charlie/Desktop/insurance/claim_0702.csv',\n",
       " '/Users/charlie/Desktop/insurance/feature_importance.csv',\n",
       " '/Users/charlie/Desktop/insurance/model_log.txt',\n",
       " '/Users/charlie/Desktop/insurance/policy2.csv',\n",
       " '/Users/charlie/Desktop/insurance/policy_0702.csv',\n",
       " '/Users/charlie/Desktop/insurance/submit.csv',\n",
       " '/Users/charlie/Desktop/insurance/te.csv',\n",
       " '/Users/charlie/Desktop/insurance/te2.csv',\n",
       " '/Users/charlie/Desktop/insurance/test_mean_y_age_class.csv',\n",
       " '/Users/charlie/Desktop/insurance/test_mean_y_age_class3.csv',\n",
       " '/Users/charlie/Desktop/insurance/test_mean_y_Channel.csv',\n",
       " '/Users/charlie/Desktop/insurance/test_mean_y_Channel3.csv',\n",
       " '/Users/charlie/Desktop/insurance/test_mean_y_Coverage_Group0_YN.csv',\n",
       " '/Users/charlie/Desktop/insurance/test_mean_y_Coverage_Group1_YN.csv',\n",
       " '/Users/charlie/Desktop/insurance/test_mean_y_Coverage_Group2_YN.csv',\n",
       " '/Users/charlie/Desktop/insurance/test_mean_y_fmarriage.csv',\n",
       " '/Users/charlie/Desktop/insurance/test_mean_y_fsex.csv',\n",
       " '/Users/charlie/Desktop/insurance/test_mean_y_Imported_or_Domestic_Car.csv',\n",
       " '/Users/charlie/Desktop/insurance/test_mean_y_Insurance_Coverage.csv',\n",
       " '/Users/charlie/Desktop/insurance/test_mean_y_legal_man.csv',\n",
       " '/Users/charlie/Desktop/insurance/test_mean_y_no_future.csv',\n",
       " '/Users/charlie/Desktop/insurance/testing-set.csv',\n",
       " '/Users/charlie/Desktop/insurance/tr.csv',\n",
       " '/Users/charlie/Desktop/insurance/tr2.csv',\n",
       " '/Users/charlie/Desktop/insurance/train_mean_y_age_class.csv',\n",
       " '/Users/charlie/Desktop/insurance/train_mean_y_age_class3.csv',\n",
       " '/Users/charlie/Desktop/insurance/train_mean_y_Channel.csv',\n",
       " '/Users/charlie/Desktop/insurance/train_mean_y_Channel3.csv',\n",
       " '/Users/charlie/Desktop/insurance/train_mean_y_Coverage_Group0_YN.csv',\n",
       " '/Users/charlie/Desktop/insurance/train_mean_y_Coverage_Group1_YN.csv',\n",
       " '/Users/charlie/Desktop/insurance/train_mean_y_Coverage_Group2_YN.csv',\n",
       " '/Users/charlie/Desktop/insurance/train_mean_y_fmarriage.csv',\n",
       " '/Users/charlie/Desktop/insurance/train_mean_y_fsex.csv',\n",
       " '/Users/charlie/Desktop/insurance/train_mean_y_Imported_or_Domestic_Car.csv',\n",
       " '/Users/charlie/Desktop/insurance/train_mean_y_Insurance_Coverage.csv',\n",
       " '/Users/charlie/Desktop/insurance/train_mean_y_legal_man.csv',\n",
       " '/Users/charlie/Desktop/insurance/train_mean_y_no_future.csv',\n",
       " '/Users/charlie/Desktop/insurance/training-set.csv']"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "path = '/Users/charlie/Desktop/insurance/'\n",
    "glob.glob(os.path.join(path,'*'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#tr = pd.read_csv(path+ 'training-set.csv')\n",
    "#te = pd.read_csv(path+ 'testing-set.csv')\n",
    "claim = pd.read_csv(path+ 'claim_0702.csv')\n",
    "#policy = pd.read_csv(path+ 'policy_0702.csv')\n",
    "#print len(tr)\n",
    "#print len(te)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# fsex\n",
    "## 空格 為 法人\n",
    "## 遺失值為 法人\n",
    "## 法人皆為 0\n",
    "policy.loc[policy.fsex ==' ', 'fsex'] = 0\n",
    "policy.loc[policy.fsex.isnull(), 'fsex'] = 0\n",
    "\n",
    "\n",
    "# Cancellation\n",
    "#print policy.Cancellation.value_counts()\n",
    "policy.loc[policy.Cancellation == 'Y', 'Cancellation'] = 0\n",
    "policy.loc[policy.Cancellation == ' ', 'Cancellation'] = 1\n",
    "\n",
    "policy.ibirthv = pd.to_datetime(policy.ibirth, format= '%m/%Y')\n",
    "policy['age'] = 2016 - policy.ibirthv.dt.year\n",
    "\n",
    "# 法人婚姻補值\n",
    "## 婚姻遺失值都是國內法人\n",
    "## 婚姻空格為國內法人或法人ID有誤\n",
    "### 法人皆為 0\n",
    "policy.loc[policy.fmarriage.isnull(),'fmarriage'] = '0' \n",
    "policy.loc[policy.fmarriage == ' ', 'fmarriage'] = '0'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Main_Insurance_Coverage_Group\n",
    "tmp = pd.get_dummies(policy.Main_Insurance_Coverage_Group)\n",
    "tmp.columns =['Coverage_Group0','Coverage_Group1','Coverage_Group2']\n",
    "policy = pd.concat([policy, tmp],axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# fassured\n",
    "tmp = pd.get_dummies(policy.fassured)\n",
    "tmp.columns = ['fassured1','fassured2','fassured3','fassured6']\n",
    "policy = pd.concat([policy, tmp],axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "tmp = pd.get_dummies(policy.fmarriage)\n",
    "tmp.columns = ['fmarriage0','fmarriage1','fmarriage2']\n",
    "policy = pd.concat([policy, tmp],axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# fsex\n",
    "tmp = pd.get_dummies(policy.fsex)\n",
    "tmp.columns = ['fsex0','fsex1','fsex2']\n",
    "policy = pd.concat([policy, tmp],axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# lia & acc 係數\n",
    "policy['lia'] = policy.lia_class + policy.plia_acc\n",
    "policy['acc'] = policy.pdmg_acc + policy.plia_acc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# total insurance\n",
    "policy['Insured_Amount'] = policy.Insured_Amount1 + policy.Insured_Amount2 + policy.Insured_Amount3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#Distribution_Channel\n",
    "tmp_count = policy.groupby(['Distribution_Channel'],as_index=False)[\"Insured's_ID\"].count()\n",
    "tmp_count.columns = ['Distribution_Channel','Distribution_Channel_count']\n",
    "\n",
    "tmp_premium = policy.groupby(['Distribution_Channel'],as_index=False).agg({'Premium':['mean','std','median','sum','min','max']})\n",
    "tmp_premium.columns = ['Distribution_Channel','mean_Premium_Distribution_Channel', 'std_Premium_Distribution_Channel', \n",
    "               'median_Premium_Distribution_Channel', 'sum_Premium_Distribution_Channel', 'min_Premium_Distribution_Channel', 'max_Premium_Distribution_Channel']\n",
    "\n",
    "tmp_policy = policy.groupby(['Distribution_Channel'],as_index=False).agg({'Policy_Number':'nunique'})\n",
    "tmp_policy.columns = ['Distribution_Channel','Policy_Number_Distribution_Channel_distinct_count']\n",
    "\n",
    "tmp_insuredID = policy.groupby(['Distribution_Channel'],as_index=False).agg({\"Insured's_ID\":'nunique'})\n",
    "tmp_insuredID.columns = ['Distribution_Channel','insuredID_Distribution_Channel_distinct_count']\n",
    "\n",
    "\n",
    "tmp_repl = policy.groupby(['Distribution_Channel'],as_index=False).agg({'Replacement_cost_of_insured_vehicle':['mean','std','median','sum','min','max']})\n",
    "tmp_premium.columns = ['Distribution_Channel','mean_Replacement_cost_of_insured_vehicle_Distribution_Channel', 'std_Replacement_cost_of_insured_vehicle_Distribution_Channel', \n",
    "               'median_Replacement_cost_of_insured_vehicle_Distribution_Channel', 'sum_Replacement_cost_of_insured_vehicle_Distribution_Channel', 'min_Replacement_cost_of_insured_vehicle_Distribution_Channel', 'max_Replacement_cost_of_insured_vehicle_Distribution_Channel']\n",
    "\n",
    "tmp_eng = policy.groupby(['Distribution_Channel'],as_index=False).agg({'Engine_Displacement_(Cubic_Centimeter)':['mean','std','median','sum','min','max']})\n",
    "tmp_eng.columns = ['Distribution_Channel','mean_eng_Distribution_Channel', 'std_eng_Distribution_Channel', \n",
    "               'median_eng_Distribution_Channel', 'sum_eng_Distribution_Channel', 'min_eng_Distribution_Channel', 'max_eng_Distribution_Channel']\n",
    "\n",
    "tmp_amt1 = policy.groupby(['Distribution_Channel'],as_index=False).agg({'Insured_Amount1':['mean','std','median','sum','min','max']})\n",
    "tmp_amt1.columns = ['Distribution_Channel','mean_amt1_Distribution_Channel', 'std_amt1_Distribution_Channel', \n",
    "               'median_amt1_Distribution_Channel', 'sum_amt1_Distribution_Channel', 'min_amt1_Distribution_Channel', 'max_amt1_Distribution_Channel']\n",
    "\n",
    "tmp_amt2 = policy.groupby(['Distribution_Channel'],as_index=False).agg({'Insured_Amount2':['mean','std','median','sum','min','max']})\n",
    "tmp_amt2.columns = ['Distribution_Channel','mean_amt2_Distribution_Channel', 'std_amt2_Distribution_Channel', \n",
    "               'median_amt2_Distribution_Channel', 'sum_amt2_Distribution_Channel', 'min_amt2_Distribution_Channel', 'max_amt2_Distribution_Channel']\n",
    "\n",
    "tmp_amt3 = policy.groupby(['Distribution_Channel'],as_index=False).agg({'Insured_Amount3':['mean','std','median','sum','min','max']})\n",
    "tmp_amt3.columns = ['Distribution_Channel','mean_amt3_Distribution_Channel', 'std_amt3_Distribution_Channel', \n",
    "               'medianamt3_Distribution_Channel', 'sum_amt3_Distribution_Channel', 'min_amt3_Distribution_Channel', 'max_amt3_Distribution_Channel']\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "m = pd.concat([tmp_count.iloc[:,1:], \n",
    "               tmp_premium.iloc[:,1:],\n",
    "               tmp_policy.iloc[:,1:],\n",
    "               tmp_insuredID.iloc[:,1:],\n",
    "               tmp_repl.iloc[:,1:],\n",
    "               tmp_eng.iloc[:,1:],\n",
    "               tmp_amt1.iloc[:,1:],\n",
    "               tmp_amt2.iloc[:,1:],\n",
    "               tmp_amt3.iloc[:,1:]\n",
    "              ],axis=1)\n",
    "m = m.fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAXcAAAD8CAYAAACMwORRAAAABHNCSVQICAgIfAhkiAAAAAlwSFlz\nAAALEgAACxIB0t1+/AAADWZJREFUeJzt3X+s3Xddx/Hni5aJDBxqrwTbjjaxTBv8seVmmy7RxY2k\nG6YlEc2aQNBM+g/FKYumRDPNTMwQgz+SijYwh4ircxC9kWoxY2aJYUvvGM61tXItc71l2LsxppFg\naXz7xz0jZ6e3Pefenu579rnPR9L0fL/ns3Pe+yZ99nu/50dTVUiS2vKKrgeQJI2fcZekBhl3SWqQ\ncZekBhl3SWqQcZekBhl3SWqQcZekBhl3SWrQ2q6eeN26dbVp06aunl6SXpYeffTRZ6pqati6zuK+\nadMmZmdnu3p6SXpZSvIfo6zzsowkNci4S1KDjLskNci4S1KDhsY9yd1JTiV54hz3J8kfJplL8niS\nq8Y/piRpOUY5c78H2Hae+28CtvR+7QI+fOFjSZIuxNC4V9VDwFfPs2QH8Ge16GHgdUneMK4BJUnL\nN45r7uuBE33b8719kqSOvKQvqCbZlWQ2yezCwsJL+dSStKqM4xOqJ4GNfdsbevvOUlX7gH0A09PT\nK/6XuTft+fRK/9MVefKut57zvkmaRZJeMI64zwC7k+wHrgGer6qnx/C4Wgb/kpHUb2jck9wLXA+s\nSzIP/AbwSoCq+mPgAHAzMAd8Hfj5izWsJGk0Q+NeVTuH3F/Ae8Y2kSTpgvkJVUlqUGdf+at2ef1f\n6p5n7pLUIOMuSQ0y7pLUIOMuSQ0y7pLUIOMuSQ0y7pLUIOMuSQ0y7pLUIOMuSQ0y7pLUIOMuSQ0y\n7pLUIOMuSQ3yK3/VtJfy64f9t3Y1STxzl6QGGXdJapBxl6QGec1dWmUm5XUIXVyeuUtSg4y7JDXI\nuEtSg4y7JDXIuEtSg4y7JDXIuEtSg4y7JDXIDzFJ6sQkfZnaJM0yLp65S1KDjLskNci4S1KDRop7\nkm1JjiWZS7JnifsvT/JgkseSPJ7k5vGPKkka1dC4J1kD7AVuArYCO5NsHVj268B9VXUlcAvwR+Me\nVJI0ulHO3K8G5qrqeFWdBvYDOwbWFPAdvduXAV8e34iSpOUa5a2Q64ETfdvzwDUDa34T+EyS9wKX\nAjeOZTpJ0oqM6wXVncA9VbUBuBn4eJKzHjvJriSzSWYXFhbG9NSSpEGjxP0ksLFve0NvX79bgfsA\nqupzwKuAdYMPVFX7qmq6qqanpqZWNrEkaahR4n4I2JJkc5JLWHzBdGZgzVPADQBJfoDFuHtqLkkd\nGRr3qjoD7AYOAkdZfFfM4SR3JtneW3Y78O4k/wzcC/xcVdXFGlqSdH4jfbdMVR0ADgzsu6Pv9hHg\nuvGOJklaKT+hKkkNMu6S1CDjLkkNMu6S1CDjLkkNMu6S1CDjLkkNMu6S1CDjLkkNMu6S1CDjLkkN\nMu6S1CDjLkkNMu6S1CDjLkkNMu6S1CDjLkkNMu6S1CDjLkkNMu6S1CDjLkkNMu6S1CDjLkkNMu6S\n1CDjLkkNMu6S1CDjLkkNMu6S1CDjLkkNMu6S1CDjLkkNMu6S1KCR4p5kW5JjSeaS7DnHmp9NciTJ\n4SR/Md4xJUnLsXbYgiRrgL3AW4B54FCSmao60rdmC/B+4Lqqei7J91ysgSVJw41y5n41MFdVx6vq\nNLAf2DGw5t3A3qp6DqCqTo13TEnScowS9/XAib7t+d6+fm8C3pTkn5I8nGTbuAaUJC3f0Msyy3ic\nLcD1wAbgoSQ/WFVf61+UZBewC+Dyyy8f01NLkgaNcuZ+EtjYt72ht6/fPDBTVd+sqi8B/8Zi7F+k\nqvZV1XRVTU9NTa10ZknSEKPE/RCwJcnmJJcAtwAzA2v+msWzdpKsY/EyzfExzilJWoahca+qM8Bu\n4CBwFLivqg4nuTPJ9t6yg8CzSY4ADwK/UlXPXqyhJUnnN9I196o6ABwY2HdH3+0C3tf7JUnqmJ9Q\nlaQGGXdJapBxl6QGGXdJapBxl6QGGXdJapBxl6QGGXdJapBxl6QGGXdJapBxl6QGGXdJapBxl6QG\nGXdJapBxl6QGGXdJapBxl6QGGXdJapBxl6QGGXdJapBxl6QGGXdJapBxl6QGGXdJapBxl6QGGXdJ\napBxl6QGGXdJapBxl6QGGXdJapBxl6QGGXdJapBxl6QGjRT3JNuSHEsyl2TPedb9dJJKMj2+ESVJ\nyzU07knWAHuBm4CtwM4kW5dY91rgNuCRcQ8pSVqeUc7crwbmqup4VZ0G9gM7llj3W8AHgG+McT5J\n0gqMEvf1wIm+7fnevm9JchWwsao+PcbZJEkrdMEvqCZ5BfAh4PYR1u5KMptkdmFh4UKfWpJ0DqPE\n/SSwsW97Q2/fC14LvBn4xyRPAtcCM0u9qFpV+6pquqqmp6amVj61JOm8Ron7IWBLks1JLgFuAWZe\nuLOqnq+qdVW1qao2AQ8D26tq9qJMLEkaamjcq+oMsBs4CBwF7quqw0nuTLL9Yg8oSVq+taMsqqoD\nwIGBfXecY+31Fz6WJOlC+AlVSWqQcZekBhl3SWqQcZekBhl3SWqQcZekBhl3SWqQcZekBhl3SWqQ\ncZekBhl3SWqQcZekBhl3SWqQcZekBhl3SWqQcZekBhl3SWqQcZekBhl3SWqQcZekBhl3SWqQcZek\nBhl3SWqQcZekBhl3SWqQcZekBhl3SWqQcZekBhl3SWqQcZekBhl3SWqQcZekBhl3SWrQSHFPsi3J\nsSRzSfYscf/7khxJ8niSB5K8cfyjSpJGNTTuSdYAe4GbgK3AziRbB5Y9BkxX1Q8B9wO/M+5BJUmj\nG+XM/WpgrqqOV9VpYD+wo39BVT1YVV/vbT4MbBjvmJKk5Rgl7uuBE33b871953Ir8HdL3ZFkV5LZ\nJLMLCwujTylJWpaxvqCa5B3ANPDBpe6vqn1VNV1V01NTU+N8aklSn7UjrDkJbOzb3tDb9yJJbgR+\nDfiJqvrf8YwnSVqJUc7cDwFbkmxOcglwCzDTvyDJlcCfANur6tT4x5QkLcfQuFfVGWA3cBA4CtxX\nVYeT3Jlke2/ZB4HXAH+V5AtJZs7xcJKkl8Aol2WoqgPAgYF9d/TdvnHMc0mSLoCfUJWkBhl3SWqQ\ncZekBhl3SWqQcZekBhl3SWqQcZekBhl3SWqQcZekBhl3SWqQcZekBhl3SWqQcZekBhl3SWqQcZek\nBhl3SWqQcZekBhl3SWqQcZekBhl3SWqQcZekBhl3SWqQcZekBhl3SWqQcZekBhl3SWqQcZekBhl3\nSWqQcZekBhl3SWqQcZekBhl3SWrQSHFPsi3JsSRzSfYscf+3JfnL3v2PJNk07kElSaMbGvcka4C9\nwE3AVmBnkq0Dy24Fnquq7wN+D/jAuAeVJI1ulDP3q4G5qjpeVaeB/cCOgTU7gI/1bt8P3JAk4xtT\nkrQco8R9PXCib3u+t2/JNVV1Bnge+O5xDChJWr5U1fkXJG8HtlXVL/S23wlcU1W7+9Y80Vsz39v+\n996aZwYeaxewq7d5BXBsXP8jI1oHPDN01eriMTmbx2RpHpezdXFM3lhVU8MWrR3hgU4CG/u2N/T2\nLbVmPsla4DLg2cEHqqp9wL4RnvOiSDJbVdNdPf8k8piczWOyNI/L2Sb5mIxyWeYQsCXJ5iSXALcA\nMwNrZoB39W6/HfhsDfuRQJJ00Qw9c6+qM0l2AweBNcDdVXU4yZ3AbFXNAB8FPp5kDvgqi38BSJI6\nMsplGarqAHBgYN8dfbe/AfzMeEe7KDq7JDTBPCZn85gszeNytok9JkNfUJUkvfz49QOS1KBVEfck\nG5M8mORIksNJbut6pkmRZE2Sx5L8bdezTIIkr0tyf5J/TXI0yY92PVPXkvxy78/NE0nuTfKqrmfq\nQpK7k5zqvfX7hX3fleQfknyx9/t3djljv1URd+AMcHtVbQWuBd6zxFcorFa3AUe7HmKC/AHw91X1\n/cAPs8qPTZL1wC8C01X1ZhbfVLFa3zBxD7BtYN8e4IGq2gI80NueCKsi7lX1dFV9vnf7v1n8Azv4\nKdtVJ8kG4K3AR7qeZRIkuQz4cRbf/UVVna6qr3U71URYC3x77zMsrwa+3PE8naiqh1h8N2C//q9e\n+Rjwtpd0qPNYFXHv1/vGyiuBR7qdZCL8PvCrwP91PciE2AwsAH/au1T1kSSXdj1Ul6rqJPC7wFPA\n08DzVfWZbqeaKK+vqqd7t78CvL7LYfqtqrgneQ3wSeCXquq/up6nS0l+CjhVVY92PcsEWQtcBXy4\nqq4E/ocJ+jG7C71ryDtY/Ivve4FLk7yj26kmU++DmxPz9sNVE/ckr2Qx7J+oqk91Pc8EuA7YnuRJ\nFr/p8yeT/Hm3I3VuHpivqhd+qrufxdivZjcCX6qqhar6JvAp4Mc6nmmS/GeSNwD0fj/V8Tzfsiri\n3vv64Y8CR6vqQ13PMwmq6v1VtaGqNrH4Atlnq2pVn5FV1VeAE0mu6O26ATjS4UiT4Cng2iSv7v05\nuoFV/iLzgP6vXnkX8DcdzvIiqyLuLJ6lvpPFs9Mv9H7d3PVQmkjvBT6R5HHgR4Df7nieTvV+irkf\n+DzwLyw2Y2I/lXkxJbkX+BxwRZL5JLcCdwFvSfJFFn/KuavLGfv5CVVJatBqOXOXpFXFuEtSg4y7\nJDXIuEtSg4y7JDXIuEtSg4y7JDXIuEtSg/4fTvQf2gUmFmgAAAAASUVORK5CYII=\n",
      "text/plain": [
       "<matplotlib.figure.Figure at 0x10a5a6f50>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[0.99660254081132671, 0.9933131052804689, 0.99081212179113776, 0.97150624561693222, 0.89841068035492211, 0.89924689392691726, 0.87828269415560734, 0.83540174227650854, 0.83577673982500267]\n"
     ]
    }
   ],
   "source": [
    "silhouette_avgs = []\n",
    "ks = range(2, 11)\n",
    "for k in ks:\n",
    "    kmeans_fit = KMeans(n_clusters = k).fit(m)\n",
    "    cluster_labels = kmeans_fit.labels_\n",
    "    silhouette_avg = metrics.silhouette_score(m, cluster_labels)\n",
    "    silhouette_avgs.append(silhouette_avg)\n",
    "\n",
    "plt.bar(ks, silhouette_avgs)\n",
    "plt.show()\n",
    "print(silhouette_avgs)\n",
    "plt.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0    802\n",
      "7     54\n",
      "5     12\n",
      "4      3\n",
      "6      1\n",
      "3      1\n",
      "2      1\n",
      "1      1\n",
      "Name: 0, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "kmeans = KMeans(n_clusters=8, random_state=0).fit(m)\n",
    "print pd.DataFrame(kmeans.labels_)[0].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "tmp = pd.DataFrame({'Distribution_Channel':tmp_premium.Distribution_Channel, 'channel_cluster_8':KMeans(n_clusters=8, random_state=0).fit(m).labels_})\n",
    "policy = pd.merge(policy, tmp, how='left', on='Distribution_Channel')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "tmp = policy.groupby('Policy_Number', as_index=False)['channel_cluster_8'].first()\n",
    "tr = pd.merge(tr, tmp , on = 'Policy_Number', how = 'left')\n",
    "te = pd.merge(te, tmp , on = 'Policy_Number', how = 'left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "tmp_count = policy.groupby(['iply_area'],as_index=False)[\"Insured's_ID\"].count()\n",
    "tmp_count.columns = ['iply_area','zip_count']\n",
    "\n",
    "tmp_premium = policy.groupby(['iply_area'],as_index=False).agg({'Premium':['mean','std','median','sum','min','max']})\n",
    "tmp_premium.columns = ['iply_area','mean_Premium_zip', 'std_Premium_zip', \n",
    "               'median_Premium_zip', 'sum_Premium_zip', 'min_Premium_zip', 'max_Premium_zip']\n",
    "\n",
    "tmp_policy = policy.groupby(['iply_area'],as_index=False).agg({'Policy_Number':'nunique'})\n",
    "tmp_policy.columns = ['iply_area','Policy_Number_distinct_count']\n",
    "\n",
    "tmp_insuredID = policy.groupby(['iply_area'],as_index=False).agg({\"Insured's_ID\":'nunique'})\n",
    "tmp_insuredID.columns = ['iply_area','insuredID_distinct_count']\n",
    "\n",
    "tmp_repl = policy.groupby(['iply_area'],as_index=False).agg({'Replacement_cost_of_insured_vehicle':['mean','std','median','sum','min','max']})\n",
    "tmp_premium.columns = ['iply_area','mean_Replacement_cost_of_insured_vehicle_Distribution_Channel', 'std_Replacement_cost_of_insured_vehicle_Distribution_Channel', \n",
    "               'median_Replacement_cost_of_insured_vehicle_Distribution_Channel', 'sum_Replacement_cost_of_insured_vehicle_Distribution_Channel', 'min_Replacement_cost_of_insured_vehicle_Distribution_Channel', 'max_Replacement_cost_of_insured_vehicle_Distribution_Channel']\n",
    "\n",
    "tmp_eng = policy.groupby(['iply_area'],as_index=False).agg({'Engine_Displacement_(Cubic_Centimeter)':['mean','std','median','sum','min','max']})\n",
    "tmp_eng.columns = ['iply_area','mean_eng_Distribution_Channel', 'std_eng_Distribution_Channel', \n",
    "               'median_eng_Distribution_Channel', 'sum_eng_Distribution_Channel', 'min_eng_Distribution_Channel', 'max_eng_Distribution_Channel']\n",
    "\n",
    "tmp_amt1 = policy.groupby(['iply_area'],as_index=False).agg({'Insured_Amount1':['mean','std','median','sum','min','max']})\n",
    "tmp_amt1.columns = ['iply_area','mean_amt1_Distribution_Channel', 'std_amt1_Distribution_Channel', \n",
    "               'median_amt1_Distribution_Channel', 'sum_amt1_Distribution_Channel', 'min_amt1_Distribution_Channel', 'max_amt1_Distribution_Channel']\n",
    "\n",
    "tmp_amt2 = policy.groupby(['iply_area'],as_index=False).agg({'Insured_Amount2':['mean','std','median','sum','min','max']})\n",
    "tmp_amt2.columns = ['iply_area','mean_amt2_Distribution_Channel', 'std_amt2_Distribution_Channel', \n",
    "               'median_amt2_Distribution_Channel', 'sum_amt2_Distribution_Channel', 'min_amt2_Distribution_Channel', 'max_amt2_Distribution_Channel']\n",
    "\n",
    "tmp_amt3 = policy.groupby(['iply_area'],as_index=False).agg({'Insured_Amount3':['mean','std','median','sum','min','max']})\n",
    "tmp_amt3.columns = ['iply_area','mean_amt3_Distribution_Channel', 'std_amt3_Distribution_Channel', \n",
    "               'medianamt3_Distribution_Channel', 'sum_amt3_Distribution_Channel', 'min_amt3_Distribution_Channel', 'max_amt3_Distribution_Channel']\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "m = pd.concat([tmp_count.iloc[:,1:], \n",
    "               tmp_premium.iloc[:,1:],\n",
    "               tmp_policy.iloc[:,1:],\n",
    "               tmp_insuredID.iloc[:,1:],\n",
    "               tmp_repl.iloc[:,1:],\n",
    "               tmp_eng.iloc[:,1:],\n",
    "               tmp_amt1.iloc[:,1:],\n",
    "               tmp_amt2.iloc[:,1:],\n",
    "               tmp_amt3.iloc[:,1:]\n",
    "              ],axis=1)\n",
    "m = m.fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAXcAAAD8CAYAAACMwORRAAAABHNCSVQICAgIfAhkiAAAAAlwSFlz\nAAALEgAACxIB0t1+/AAADy1JREFUeJzt3X+M3Hldx/Hni9aq/AiitxLsD9pogTSAHK4FJUECd0lP\nTEsCmNZAIAEbEwonELUXTWNqYgAN6B+NoYFDohzlrERXWa0EMEQDpHtwAdpaWMtJt4K3HAcYDZTK\n2z92jgxz2853u7Oduc8+H8nm5vudT2beN0mf+53v/NhUFZKktjxq3ANIkkbPuEtSg4y7JDXIuEtS\ng4y7JDXIuEtSg4y7JDXIuEtSg4y7JDVo47ju+Kabbqrt27eP6+4l6RHpnnvu+VpVTQ1bN7a4b9++\nnbm5uXHdvSQ9IiX5jy7rPC0jSQ0y7pLUIOMuSQ0y7pLUIOMuSQ0y7pLUIOMuSQ0y7pLUIOMuSQ0a\n2ydUV2P74Q/d0Pu77y0vvqH3J0mr5ZG7JDXIuEtSg4y7JDWoU9yT7ElyPsl8ksPLXP+OJPf2fr6Q\n5BujH1WS1NXQF1STbACOAbcCC8DpJDNVdfahNVX1xr71rwduXoNZpRW7kS+++8K7JkmXI/fdwHxV\nXaiqy8AJYN811h8A3j+K4SRJ16dL3DcDF/u2F3r7HibJk4EdwEevcv3BJHNJ5hYXF1c6qySpo1G/\noLofOFlV/7fclVV1vKqmq2p6amroX4mSJF2nLnG/BGzt297S27ec/XhKRpLGrkvcTwM7k+xIsoml\ngM8MLkryNOAJwCdGO6IkaaWGxr2qrgCHgFPAOeDuqjqT5GiSvX1L9wMnqqrWZlRJUledvlumqmaB\n2YF9Rwa2f390Y0mSVsNPqEpSg4y7JDXIuEtSg4y7JDXIuEtSgx6Rf4lpkvhXoSRNIo/cJalBHrlL\nN4DP8HSjeeQuSQ0y7pLUIE/LNMKn/ZL6eeQuSQ0y7pLUIE/LaOQ8RSSNn3GX1pkb+cvXX7zj42kZ\nSWqQcZekBhl3SWqQcZekBnWKe5I9Sc4nmU9y+CprfjXJ2SRnktw12jElSSsx9N0ySTYAx4BbgQXg\ndJKZqjrbt2YncAfwvKp6MMlPrtXAkqThuhy57wbmq+pCVV0GTgD7Btb8OnCsqh4EqKr7RzumJGkl\nusR9M3Cxb3uht6/fU4CnJPnXJJ9MsmdUA0qSVm5UH2LaCOwEXgBsAT6e5BlV9Y3+RUkOAgcBtm3b\nNqK7liQN6nLkfgnY2re9pbev3wIwU1XfraovAV9gKfY/oKqOV9V0VU1PTU1d78ySpCG6HLmfBnYm\n2cFS1PcDvzaw5m+AA8B7ktzE0mmaC6McVFJb/A6itTX0yL2qrgCHgFPAOeDuqjqT5GiSvb1lp4AH\nkpwFPgb8VlU9sFZDS5KurdM596qaBWYH9h3pu1zAm3o/kqQx81shJa17LZ4i8usHJKlBxl2SGmTc\nJalBxl2SGmTcJalBxl2SGmTcJalBxl2SGmTcJalBxl2SGmTcJalBxl2SGmTcJalBxl2SGmTcJalB\nxl2SGmTcJalBxl2SGmTcJalBneKeZE+S80nmkxxe5vpXJ1lMcm/v57WjH1WS1NXQP5CdZANwDLgV\nWABOJ5mpqrMDSz9QVYfWYEZJ0gp1OXLfDcxX1YWqugycAPat7ViSpNXoEvfNwMW+7YXevkEvTfLZ\nJCeTbF3uhpIcTDKXZG5xcfE6xpUkdTGqF1T/DtheVc8EPgy8d7lFVXW8qqaranpqampEdy1JGtQl\n7peA/iPxLb1931dVD1TVd3qb7wJ+bjTjSZKuR5e4nwZ2JtmRZBOwH5jpX5DkSX2be4FzoxtRkrRS\nQ98tU1VXkhwCTgEbgDur6kySo8BcVc0Ab0iyF7gCfB149RrOLEkaYmjcAapqFpgd2Hek7/IdwB2j\nHU2SdL38hKokNci4S1KDjLskNci4S1KDjLskNci4S1KDjLskNci4S1KDjLskNci4S1KDjLskNci4\nS1KDjLskNci4S1KDjLskNci4S1KDjLskNci4S1KDjLskNahT3JPsSXI+yXySw9dY99IklWR6dCNK\nklZqaNyTbACOAbcBu4ADSXYts+5xwO3Ap0Y9pCRpZbocue8G5qvqQlVdBk4A+5ZZ9wfAW4Fvj3A+\nSdJ16BL3zcDFvu2F3r7vS/JsYGtVfWiEs0mSrtOqX1BN8ijg7cCbO6w9mGQuydzi4uJq71qSdBVd\n4n4J2Nq3vaW37yGPA54O/HOS+4DnAjPLvahaVcerarqqpqempq5/aknSNXWJ+2lgZ5IdSTYB+4GZ\nh66sqm9W1U1Vtb2qtgOfBPZW1dyaTCxJGmpo3KvqCnAIOAWcA+6uqjNJjibZu9YDSpJWbmOXRVU1\nC8wO7DtylbUvWP1YkqTV8BOqktQg4y5JDTLuktQg4y5JDTLuktQg4y5JDTLuktQg4y5JDTLuktQg\n4y5JDTLuktQg4y5JDTLuktQg4y5JDTLuktQg4y5JDTLuktQg4y5JDTLuktQg4y5JDeoU9yR7kpxP\nMp/k8DLX/0aSzyW5N8m/JNk1+lElSV0NjXuSDcAx4DZgF3BgmXjfVVXPqKpnAW8D3j7ySSVJnXU5\nct8NzFfVhaq6DJwA9vUvqKpv9W0+BqjRjShJWqmNHdZsBi72bS8AzxlclOR1wJuATcALl7uhJAeB\ngwDbtm1b6aySpI5G9oJqVR2rqp8Gfgf4vausOV5V01U1PTU1Naq7liQN6BL3S8DWvu0tvX1XcwJ4\nyWqGkiStTpe4nwZ2JtmRZBOwH5jpX5BkZ9/mi4Evjm5ESdJKDT3nXlVXkhwCTgEbgDur6kySo8Bc\nVc0Ah5LcAnwXeBB41VoOLUm6ti4vqFJVs8DswL4jfZdvH/FckqRV8BOqktQg4y5JDTLuktQg4y5J\nDTLuktQg4y5JDTLuktQg4y5JDTLuktQg4y5JDTLuktQg4y5JDTLuktQg4y5JDTLuktQg4y5JDTLu\nktQg4y5JDTLuktSgTnFPsifJ+STzSQ4vc/2bkpxN8tkkH0ny5NGPKknqamjck2wAjgG3AbuAA0l2\nDSz7DDBdVc8ETgJvG/WgkqTuuhy57wbmq+pCVV0GTgD7+hdU1ceq6n97m58Etox2TEnSSnSJ+2bg\nYt/2Qm/f1bwG+IfVDCVJWp2No7yxJK8ApoFfusr1B4GDANu2bRvlXUuS+nQ5cr8EbO3b3tLb9wOS\n3AL8LrC3qr6z3A1V1fGqmq6q6ampqeuZV5LUQZe4nwZ2JtmRZBOwH5jpX5DkZuCdLIX9/tGPKUla\niaFxr6orwCHgFHAOuLuqziQ5mmRvb9kfAY8F/irJvUlmrnJzkqQboNM596qaBWYH9h3pu3zLiOeS\nJK2Cn1CVpAYZd0lqkHGXpAYZd0lqkHGXpAYZd0lqkHGXpAYZd0lqkHGXpAYZd0lqkHGXpAYZd0lq\nkHGXpAYZd0lqkHGXpAYZd0lqkHGXpAYZd0lqkHGXpAYZd0lqUKe4J9mT5HyS+SSHl7n++Uk+neRK\nkpeNfkxJ0koMjXuSDcAx4DZgF3Agya6BZV8GXg3cNeoBJUkrt7HDmt3AfFVdAEhyAtgHnH1oQVXd\n17vue2swoyRphbqcltkMXOzbXujtW7EkB5PMJZlbXFy8npuQJHVwQ19QrarjVTVdVdNTU1M38q4l\naV3pEvdLwNa+7S29fZKkCdUl7qeBnUl2JNkE7Adm1nYsSdJqDI17VV0BDgGngHPA3VV1JsnRJHsB\nkvx8kgXg5cA7k5xZy6ElSdfW5d0yVNUsMDuw70jf5dMsna6RJE0AP6EqSQ0y7pLUIOMuSQ0y7pLU\nIOMuSQ0y7pLUIOMuSQ0y7pLUIOMuSQ0y7pLUIOMuSQ0y7pLUIOMuSQ0y7pLUIOMuSQ0y7pLUIOMu\nSQ0y7pLUIOMuSQ3qFPcke5KcTzKf5PAy1/9wkg/0rv9Uku2jHlSS1N3QuCfZABwDbgN2AQeS7BpY\n9hrgwar6GeAdwFtHPagkqbsuR+67gfmqulBVl4ETwL6BNfuA9/YunwRelCSjG1OStBJd4r4ZuNi3\nvdDbt+yaqroCfBP4iVEMKElauVTVtRckLwP2VNVre9uvBJ5TVYf61ny+t2aht/3vvTVfG7itg8DB\n3uZTgfOj+h/p6Cbga0NXrS8+Jg/nY7I8H5eHG8dj8uSqmhq2aGOHG7oEbO3b3tLbt9yahSQbgccD\nDwzeUFUdB453uM81kWSuqqbHdf+TyMfk4XxMlufj8nCT/Jh0OS1zGtiZZEeSTcB+YGZgzQzwqt7l\nlwEfrWFPCSRJa2bokXtVXUlyCDgFbADurKozSY4Cc1U1A7wb+Isk88DXWfoFIEkaky6nZaiqWWB2\nYN+RvsvfBl4+2tHWxNhOCU0wH5OH8zFZno/Lw03sYzL0BVVJ0iOPXz8gSQ1aF3FPsjXJx5KcTXIm\nye3jnmlSJNmQ5DNJ/n7cs0yCJD+W5GSSf0tyLskvjHumcUvyxt6/m88neX+SHxn3TOOQ5M4k9/fe\n+v3Qvh9P8uEkX+z99wnjnLHfuog7cAV4c1XtAp4LvG6Zr1BYr24Hzo17iAnyp8A/VtXTgJ9lnT82\nSTYDbwCmq+rpLL2pYr2+YeLPgT0D+w4DH6mqncBHetsTYV3Evaq+UlWf7l3+b5b+wQ5+ynbdSbIF\neDHwrnHPMgmSPB54Pkvv/qKqLlfVN8Y71UTYCPxo7zMsjwb+c8zzjEVVfZyldwP26//qlfcCL7mh\nQ13Duoh7v943Vt4MfGq8k0yEPwF+G/jeuAeZEDuAReA9vVNV70rymHEPNU5VdQn4Y+DLwFeAb1bV\nP413qonyxKr6Su/yV4EnjnOYfusq7kkeC/w18JtV9a1xzzNOSX4FuL+q7hn3LBNkI/Bs4M+q6mbg\nf5igp9nj0DuHvI+lX3w/BTwmySvGO9Vk6n1wc2Lefrhu4p7kh1gK+/uq6oPjnmcCPA/Ym+Q+lr7p\n84VJ/nK8I43dArBQVQ89qzvJUuzXs1uAL1XVYlV9F/gg8ItjnmmS/FeSJwH0/nv/mOf5vnUR997X\nD78bOFdVbx/3PJOgqu6oqi1VtZ2lF8g+WlXr+oisqr4KXEzy1N6uFwFnxzjSJPgy8Nwkj+79O3oR\n6/xF5gH9X73yKuBvxzjLD1gXcWfpKPWVLB2d3tv7+eVxD6WJ9HrgfUk+CzwL+MMxzzNWvWcxJ4FP\nA59jqRkT+6nMtZTk/cAngKcmWUjyGuAtwK1JvsjSs5y3jHPGfn5CVZIatF6O3CVpXTHuktQg4y5J\nDTLuktQg4y5JDTLuktQg4y5JDTLuktSg/wf9INduiyBVbwAAAABJRU5ErkJggg==\n",
      "text/plain": [
       "<matplotlib.figure.Figure at 0x10976ef50>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[0.72614028875012226, 0.66351182276296161, 0.64344991496224135, 0.63535549053504747, 0.70587129537517024, 0.65551485507857687, 0.62929154029109047, 0.59014449181607631, 0.58293044211767187]\n"
     ]
    }
   ],
   "source": [
    "silhouette_avgs = []\n",
    "ks = range(2, 11)\n",
    "for k in ks:\n",
    "    kmeans_fit = KMeans(n_clusters = k).fit(m)\n",
    "    cluster_labels = kmeans_fit.labels_\n",
    "    silhouette_avg = metrics.silhouette_score(m, cluster_labels)\n",
    "    silhouette_avgs.append(silhouette_avg)\n",
    "\n",
    "plt.bar(ks, silhouette_avgs)\n",
    "plt.show()\n",
    "print(silhouette_avgs)\n",
    "plt.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1    6\n",
      "5    5\n",
      "4    3\n",
      "3    3\n",
      "2    3\n",
      "0    2\n",
      "Name: 0, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "kmeans = KMeans(n_clusters=6, random_state=0).fit(m)\n",
    "print pd.DataFrame(kmeans.labels_)[0].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "tmp = pd.DataFrame({'iply_area':tmp_premium.iply_area, 'iply_cluster_6':KMeans(n_clusters=6, random_state=0).fit(m).labels_})\n",
    "policy = pd.merge(policy, tmp, how='left', on='iply_area')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "tmp = policy.groupby('Policy_Number', as_index=False)['iply_cluster_6'].first()\n",
    "tr = pd.merge(tr, tmp , on = 'Policy_Number', how = 'left')\n",
    "te = pd.merge(te, tmp , on = 'Policy_Number', how = 'left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "tmp_count = policy.groupby(['aassured_zip'],as_index=False)[\"Insured's_ID\"].count()\n",
    "tmp_count.columns = ['aassured_zip','zip_count']\n",
    "\n",
    "tmp_premium = policy.groupby(['aassured_zip'],as_index=False).agg({'Premium':['mean','std','median','sum','min','max']})\n",
    "tmp_premium.columns = ['aassured_zip','mean_Premium_zip', 'std_Premium_zip', \n",
    "               'median_Premium_zip', 'sum_Premium_zip', 'min_Premium_zip', 'max_Premium_zip']\n",
    "\n",
    "tmp_policy = policy.groupby(['aassured_zip'],as_index=False).agg({'Policy_Number':'nunique'})\n",
    "tmp_policy.columns = ['aassured_zip','Policy_Number_distinct_count']\n",
    "\n",
    "tmp_insuredID = policy.groupby(['aassured_zip'],as_index=False).agg({\"Insured's_ID\":'nunique'})\n",
    "tmp_insuredID.columns = ['aassured_zip','insuredID_distinct_count']\n",
    "\n",
    "tmp_repl = policy.groupby(['aassured_zip'],as_index=False).agg({'Replacement_cost_of_insured_vehicle':['mean','std','median','sum','min','max']})\n",
    "tmp_premium.columns = ['aassured_zip','mean_Replacement_cost_of_insured_vehicle_Distribution_Channel', 'std_Replacement_cost_of_insured_vehicle_Distribution_Channel', \n",
    "               'median_Replacement_cost_of_insured_vehicle_Distribution_Channel', 'sum_Replacement_cost_of_insured_vehicle_Distribution_Channel', 'min_Replacement_cost_of_insured_vehicle_Distribution_Channel', 'max_Replacement_cost_of_insured_vehicle_Distribution_Channel']\n",
    "\n",
    "tmp_eng = policy.groupby(['aassured_zip'],as_index=False).agg({'Engine_Displacement_(Cubic_Centimeter)':['mean','std','median','sum','min','max']})\n",
    "tmp_eng.columns = ['aassured_zip','mean_eng_Distribution_Channel', 'std_eng_Distribution_Channel', \n",
    "               'median_eng_Distribution_Channel', 'sum_eng_Distribution_Channel', 'min_eng_Distribution_Channel', 'max_eng_Distribution_Channel']\n",
    "\n",
    "tmp_amt1 = policy.groupby(['aassured_zip'],as_index=False).agg({'Insured_Amount1':['mean','std','median','sum','min','max']})\n",
    "tmp_amt1.columns = ['aassured_zip','mean_amt1_Distribution_Channel', 'std_amt1_Distribution_Channel', \n",
    "               'median_amt1_Distribution_Channel', 'sum_amt1_Distribution_Channel', 'min_amt1_Distribution_Channel', 'max_amt1_Distribution_Channel']\n",
    "\n",
    "tmp_amt2 = policy.groupby(['aassured_zip'],as_index=False).agg({'Insured_Amount2':['mean','std','median','sum','min','max']})\n",
    "tmp_amt2.columns = ['aassured_zip','mean_amt2_Distribution_Channel', 'std_amt2_Distribution_Channel', \n",
    "               'median_amt2_Distribution_Channel', 'sum_amt2_Distribution_Channel', 'min_amt2_Distribution_Channel', 'max_amt2_Distribution_Channel']\n",
    "\n",
    "tmp_amt3 = policy.groupby(['aassured_zip'],as_index=False).agg({'Insured_Amount3':['mean','std','median','sum','min','max']})\n",
    "tmp_amt3.columns = ['aassured_zip','mean_amt3_Distribution_Channel', 'std_amt3_Distribution_Channel', \n",
    "               'medianamt3_Distribution_Channel', 'sum_amt3_Distribution_Channel', 'min_amt3_Distribution_Channel', 'max_amt3_Distribution_Channel']\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "m = pd.concat([tmp_count.iloc[:,1:], \n",
    "               tmp_premium.iloc[:,1:],\n",
    "               tmp_policy.iloc[:,1:],\n",
    "               tmp_insuredID.iloc[:,1:],\n",
    "               tmp_repl.iloc[:,1:],\n",
    "               tmp_eng.iloc[:,1:],\n",
    "               tmp_amt1.iloc[:,1:],\n",
    "               tmp_amt2.iloc[:,1:],\n",
    "               tmp_amt3.iloc[:,1:]\n",
    "              ],axis=1)\n",
    "m = m.fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAXcAAAD8CAYAAACMwORRAAAABHNCSVQICAgIfAhkiAAAAAlwSFlz\nAAALEgAACxIB0t1+/AAADLFJREFUeJzt3X+s3fVdx/Hna604xyb+6HXR/lib2KHN/MFyw1ASXYQl\nhZnWxGkg2TITXP+xEx3RdNEQg4lhaqb+UY1km1vmhCEu2rhqZxhmiRmkZUxcW+tqh1DGpCBDo5ld\n49s/7mE53N72ntue8j287/ORND3f7/nknDdf6LPf+z0/SFUhSerlFUMPIEmaPuMuSQ0Zd0lqyLhL\nUkPGXZIaMu6S1JBxl6SGjLskNWTcJamhtUM98bp162rz5s1DPb0kvSw9/PDDz1TV3HLrBov75s2b\nOXTo0FBPL0kvS0n+bZJ1XpaRpIaMuyQ1ZNwlqSHjLkkNGXdJasi4S1JDxl2SGjLuktSQcZekhgb7\nhOrF2Lznky/p8z1251tf0ueTpIvlmbskNWTcJakh4y5JDb0sr7nPEq//S5pFnrlLUkPGXZIaMu6S\n1JDX3Jvw2v/SXsrjcr5jMkv/fmblmOjSMu6aulkKmWbXLP13MkuzTIuXZSSpIeMuSQ0Zd0lqyLhL\nUkPGXZIaMu6S1JBxl6SGjLskNWTcJakh4y5JDRl3SWrIuEtSQ8Zdkhoy7pLUkHGXpIaMuyQ1NFHc\nk2xPcizJ8SR7lrh/U5IHkjyS5NEkN05/VEnSpJaNe5I1wF7gBmAbcHOSbYuW/Tpwb1VdBdwE/OG0\nB5UkTW6SM/ergeNVdaKqTgP3ADsXrSngW0e3rwC+PL0RJUkrNcn/Q3U98MTY9kngTYvW/AbwqSTv\nBi4Hrp/KdJKkCzKtF1RvBj5cVRuAG4GPJjnrsZPsSnIoyaFTp05N6aklSYtNEvcngY1j2xtG+8bd\nAtwLUFWfBV4JrFv8QFV1V1XNV9X83NzchU0sSVrWJHE/CGxNsiXJZSy8YLpv0ZrHgesAknw/C3H3\n1FySBrJs3KvqDLAbOAAcZeFdMYeT3JFkx2jZbcC7kvwjcDfwc1VVl2poSdL5TfKCKlW1H9i/aN/t\nY7ePANdOdzRJ0oXyE6qS1JBxl6SGjLskNWTcJakh4y5JDRl3SWrIuEtSQ8Zdkhoy7pLUkHGXpIaM\nuyQ1ZNwlqSHjLkkNGXdJasi4S1JDxl2SGjLuktSQcZekhoy7JDVk3CWpIeMuSQ0Zd0lqyLhLUkPG\nXZIaMu6S1JBxl6SGjLskNWTcJakh4y5JDRl3SWrIuEtSQ8Zdkhoy7pLUkHGXpIaMuyQ1NFHck2xP\ncizJ8SR7zrHmZ5McSXI4yZ9Nd0xJ0kqsXW5BkjXAXuAtwEngYJJ9VXVkbM1W4L3AtVX1XJLvulQD\nS5KWN8mZ+9XA8ao6UVWngXuAnYvWvAvYW1XPAVTV09MdU5K0EpPEfT3wxNj2ydG+ca8HXp/kH5I8\nmGT7tAaUJK3cspdlVvA4W4E3AxuAzyT5gar66viiJLuAXQCbNm2a0lNLkhab5Mz9SWDj2PaG0b5x\nJ4F9VfX1qvoS8C8sxP5Fququqpqvqvm5ubkLnVmStIxJ4n4Q2JpkS5LLgJuAfYvW/CULZ+0kWcfC\nZZoTU5xTkrQCy8a9qs4Au4EDwFHg3qo6nOSOJDtGyw4AzyY5AjwA/EpVPXuphpYknd9E19yraj+w\nf9G+28duF/Ce0S9J0sD8hKokNWTcJakh4y5JDRl3SWrIuEtSQ8Zdkhoy7pLUkHGXpIaMuyQ1ZNwl\nqSHjLkkNGXdJasi4S1JDxl2SGjLuktSQcZekhoy7JDVk3CWpIeMuSQ0Zd0lqyLhLUkPGXZIaMu6S\n1JBxl6SGjLskNWTcJakh4y5JDRl3SWrIuEtSQ8Zdkhoy7pLUkHGXpIaMuyQ1ZNwlqSHjLkkNGXdJ\namiiuCfZnuRYkuNJ9pxn3U8nqSTz0xtRkrRSy8Y9yRpgL3ADsA24Ocm2Jda9BrgVeGjaQ0qSVmaS\nM/ergeNVdaKqTgP3ADuXWPebwPuAr01xPknSBZgk7uuBJ8a2T472fUOSNwIbq+qT53ugJLuSHEpy\n6NSpUyseVpI0mYt+QTXJK4D3A7ctt7aq7qqq+aqan5ubu9inliSdwyRxfxLYOLa9YbTvBa8B3gD8\nfZLHgGuAfb6oKknDmSTuB4GtSbYkuQy4Cdj3wp1V9XxVrauqzVW1GXgQ2FFVhy7JxJKkZS0b96o6\nA+wGDgBHgXur6nCSO5LsuNQDSpJWbu0ki6pqP7B/0b7bz7H2zRc/liTpYvgJVUlqyLhLUkPGXZIa\nMu6S1JBxl6SGjLskNWTcJakh4y5JDRl3SWrIuEtSQ8Zdkhoy7pLUkHGXpIaMuyQ1ZNwlqSHjLkkN\nGXdJasi4S1JDxl2SGjLuktSQcZekhoy7JDVk3CWpIeMuSQ0Zd0lqyLhLUkPGXZIaMu6S1JBxl6SG\njLskNWTcJakh4y5JDRl3SWrIuEtSQ8ZdkhqaKO5Jtic5luR4kj1L3P+eJEeSPJrk/iSvm/6okqRJ\nLRv3JGuAvcANwDbg5iTbFi17BJivqh8E7gN+e9qDSpImN8mZ+9XA8ao6UVWngXuAneMLquqBqvqf\n0eaDwIbpjilJWolJ4r4eeGJs++Ro37ncAvzNxQwlSbo4a6f5YEneDswDP36O+3cBuwA2bdo0zaeW\nJI2Z5Mz9SWDj2PaG0b4XSXI98GvAjqr636UeqKruqqr5qpqfm5u7kHklSROYJO4Hga1JtiS5DLgJ\n2De+IMlVwB+zEPanpz+mJGkllo17VZ0BdgMHgKPAvVV1OMkdSXaMlv0O8Grgz5N8Psm+czycJOkl\nMNE196raD+xftO/2sdvXT3kuSdJF8BOqktSQcZekhoy7JDVk3CWpIeMuSQ0Zd0lqyLhLUkPGXZIa\nMu6S1JBxl6SGjLskNWTcJakh4y5JDRl3SWrIuEtSQ8Zdkhoy7pLUkHGXpIaMuyQ1ZNwlqSHjLkkN\nGXdJasi4S1JDxl2SGjLuktSQcZekhoy7JDVk3CWpIeMuSQ0Zd0lqyLhLUkPGXZIaMu6S1JBxl6SG\njLskNTRR3JNsT3IsyfEke5a4/5uTfHx0/0NJNk97UEnS5JaNe5I1wF7gBmAbcHOSbYuW3QI8V1Xf\nC/we8L5pDypJmtwkZ+5XA8er6kRVnQbuAXYuWrMT+Mjo9n3AdUkyvTElSSsxSdzXA0+MbZ8c7Vty\nTVWdAZ4HvnMaA0qSVi5Vdf4FyduA7VX186PtdwBvqqrdY2u+MFpzcrT9r6M1zyx6rF3ArtHmlcCx\naf2DTGgd8Myyq1YXj8nZPCZL87icbYhj8rqqmltu0doJHuhJYOPY9obRvqXWnEyyFrgCeHbxA1XV\nXcBdEzznJZHkUFXND/X8s8hjcjaPydI8Lmeb5WMyyWWZg8DWJFuSXAbcBOxbtGYf8M7R7bcBn67l\nfiSQJF0yy565V9WZJLuBA8Aa4ENVdTjJHcChqtoHfBD4aJLjwH+w8BeAJGkgk1yWoar2A/sX7bt9\n7PbXgJ+Z7miXxGCXhGaYx+RsHpOleVzONrPHZNkXVCVJLz9+/YAkNbQq4p5kY5IHkhxJcjjJrUPP\nNCuSrEnySJK/HnqWWZDk25Lcl+SfkxxN8iNDzzS0JL88+nPzhSR3J3nl0DMNIcmHkjw9euv3C/u+\nI8nfJfni6PdvH3LGcasi7sAZ4Laq2gZcA/zCEl+hsFrdChwdeogZ8gfA31bV9wE/xCo/NknWA78I\nzFfVG1h4U8VqfcPEh4Hti/btAe6vqq3A/aPtmbAq4l5VT1XV50a3/4uFP7CLP2W76iTZALwV+MDQ\ns8yCJFcAP8bCu7+oqtNV9dVhp5oJa4FvGX2G5VXAlweeZxBV9RkW3g04bvyrVz4C/NRLOtR5rIq4\njxt9Y+VVwEPDTjITfh/4VeD/hh5kRmwBTgF/MrpU9YEklw891JCq6kngd4HHgaeA56vqU8NONVNe\nW1VPjW5/BXjtkMOMW1VxT/Jq4C+AX6qq/xx6niEl+Ung6ap6eOhZZsha4I3AH1XVVcB/M0M/Zg9h\ndA15Jwt/8X0PcHmStw871WwafXBzZt5+uGrinuSbWAj7x6rqE0PPMwOuBXYkeYyFb/r8iSR/OuxI\ngzsJnKyqF36qu4+F2K9m1wNfqqpTVfV14BPAjw480yz59yTfDTD6/emB5/mGVRH30dcPfxA4WlXv\nH3qeWVBV762qDVW1mYUXyD5dVav6jKyqvgI8keTK0a7rgCMDjjQLHgeuSfKq0Z+j61jlLzIvMv7V\nK+8E/mrAWV5kVcSdhbPUd7Bwdvr50a8bhx5KM+ndwMeSPAr8MPBbA88zqNFPMfcBnwP+iYVmzOyn\nMi+lJHcDnwWuTHIyyS3AncBbknyRhZ9y7hxyxnF+QlWSGlotZ+6StKoYd0lqyLhLUkPGXZIaMu6S\n1JBxl6SGjLskNWTcJamh/wfi/OCmQMy2vwAAAABJRU5ErkJggg==\n",
      "text/plain": [
       "<matplotlib.figure.Figure at 0x109793ed0>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[0.93660131508631905, 0.91897329420813378, 0.90061510511940635, 0.89617319683518337, 0.89850552773592496, 0.89770413881855593, 0.89889135796970443, 0.89355828246732771, 0.89274910066369662]\n"
     ]
    }
   ],
   "source": [
    "silhouette_avgs = []\n",
    "ks = range(2, 11)\n",
    "for k in ks:\n",
    "    kmeans_fit = KMeans(n_clusters = k).fit(m)\n",
    "    cluster_labels = kmeans_fit.labels_\n",
    "    silhouette_avg = metrics.silhouette_score(m, cluster_labels)\n",
    "    silhouette_avgs.append(silhouette_avg)\n",
    "\n",
    "plt.bar(ks, silhouette_avgs)\n",
    "plt.show()\n",
    "print(silhouette_avgs)\n",
    "plt.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2    1524\n",
      "0     111\n",
      "3      44\n",
      "1      24\n",
      "5      17\n",
      "4       2\n",
      "Name: 0, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "kmeans = KMeans(n_clusters=6, random_state=0).fit(m)\n",
    "print pd.DataFrame(kmeans.labels_)[0].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "tmp = pd.DataFrame({'aassured_zip':tmp_premium.aassured_zip, 'zip_cluster_6':KMeans(n_clusters=6, random_state=0).fit(m).labels_})\n",
    "policy = pd.merge(policy, tmp, how='left', on='aassured_zip')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "tmp = policy.groupby('Policy_Number', as_index=False)['zip_cluster_6'].first()\n",
    "tr = pd.merge(tr, tmp , on = 'Policy_Number', how = 'left')\n",
    "te = pd.merge(te, tmp , on = 'Policy_Number', how = 'left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "tmp_count = policy.groupby(['aassured_zip'],as_index=False)[\"Insured's_ID\"].count()\n",
    "tmp_count.columns = ['aassured_zip','zip_count']\n",
    "\n",
    "tmp_premium = policy.groupby(['aassured_zip'],as_index=False).agg({'Premium':['mean','std','median','sum','min','max']})\n",
    "tmp_premium.columns = ['aassured_zip','mean_Premium_zip', 'std_Premium_zip', \n",
    "               'median_Premium_zip', 'sum_Premium_zip', 'min_Premium_zip', 'max_Premium_zip']\n",
    "\n",
    "tmp_policy = policy.groupby(['aassured_zip'],as_index=False).agg({'Policy_Number':'nunique'})\n",
    "tmp_policy.columns = ['aassured_zip','Policy_Number_distinct_count']\n",
    "\n",
    "tmp_insuredID = policy.groupby(['aassured_zip'],as_index=False).agg({\"Insured's_ID\":'nunique'})\n",
    "tmp_insuredID.columns = ['aassured_zip','insuredID_distinct_count']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "m = pd.concat([tmp_count.zip_count, tmp_premium.sum_Premium_zip,\n",
    "               tmp_premium.mean_Premium_zip,\n",
    "               tmp_premium.std_Premium_zip,\n",
    "               tmp_premium.median_Premium_zip,\n",
    "               tmp_premium.min_Premium_zip,\n",
    "               tmp_premium.max_Premium_zip,\n",
    "               tmp_policy.Policy_Number_distinct_count, \n",
    "         tmp_insuredID.insuredID_distinct_count],axis=1)\n",
    "m = m.fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "silhouette_avgs = []\n",
    "ks = range(2, 11)\n",
    "for k in ks:\n",
    "    kmeans_fit = KMeans(n_clusters = k).fit(m)\n",
    "    cluster_labels = kmeans_fit.labels_\n",
    "    silhouette_avg = metrics.silhouette_score(m, cluster_labels)\n",
    "    silhouette_avgs.append(silhouette_avg)\n",
    "\n",
    "plt.bar(ks, silhouette_avgs)\n",
    "plt.show()\n",
    "print(silhouette_avgs)\n",
    "plt.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "kmeans = KMeans(n_clusters=2, random_state=0).fit(m)\n",
    "print pd.DataFrame(kmeans.labels_)[0].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "tmp = pd.DataFrame({'aassured_zip':tmp_premium.aassured_zip, 'zip_cluster_2':KMeans(n_clusters=2, random_state=0).fit(m).labels_})\n",
    "policy = pd.merge(policy, tmp, how='left', on='aassured_zip')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "print len(tr)\n",
    "print len(te)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "tmp_count = policy.groupby(['Vehicle_Make_and_Model1'],as_index=False)[\"Insured's_ID\"].count()\n",
    "tmp_count.columns = ['Vehicle_Make_and_Model1','zip_count']\n",
    "\n",
    "tmp_premium = policy.groupby(['Vehicle_Make_and_Model1'],as_index=False).agg({'Premium':['mean','std','median','sum','min','max']})\n",
    "tmp_premium.columns = ['Vehicle_Make_and_Model1','mean_Premium_zip', 'std_Premium_zip', \n",
    "               'median_Premium_zip', 'sum_Premium_zip', 'min_Premium_zip', 'max_Premium_zip']\n",
    "\n",
    "tmp_policy = policy.groupby(['Vehicle_Make_and_Model1'],as_index=False).agg({'Policy_Number':'nunique'})\n",
    "tmp_policy.columns = ['Vehicle_Make_and_Model1','Policy_Number_distinct_count']\n",
    "\n",
    "tmp_insuredID = policy.groupby(['Vehicle_Make_and_Model1'],as_index=False).agg({\"Insured's_ID\":'nunique'})\n",
    "tmp_insuredID.columns = ['Vehicle_Make_and_Model1','insuredID_distinct_count']\n",
    "##\n",
    "tmp_repl = policy.groupby(['Vehicle_Make_and_Model1'],as_index=False).agg({'Replacement_cost_of_insured_vehicle':['mean','std','median','sum','min','max']})\n",
    "tmp_premium.columns = ['Vehicle_Make_and_Model1','mean_Replacement_cost_of_insured_vehicle_Distribution_Channel', 'std_Replacement_cost_of_insured_vehicle_Distribution_Channel', \n",
    "               'median_Replacement_cost_of_insured_vehicle_Distribution_Channel', 'sum_Replacement_cost_of_insured_vehicle_Distribution_Channel', 'min_Replacement_cost_of_insured_vehicle_Distribution_Channel', 'max_Replacement_cost_of_insured_vehicle_Distribution_Channel']\n",
    "\n",
    "tmp_eng = policy.groupby(['Vehicle_Make_and_Model1'],as_index=False).agg({'Engine_Displacement_(Cubic_Centimeter)':['mean','std','median','sum','min','max']})\n",
    "tmp_eng.columns = ['Vehicle_Make_and_Model1','mean_eng_Distribution_Channel', 'std_eng_Distribution_Channel', \n",
    "               'median_eng_Distribution_Channel', 'sum_eng_Distribution_Channel', 'min_eng_Distribution_Channel', 'max_eng_Distribution_Channel']\n",
    "\n",
    "tmp_amt1 = policy.groupby(['Vehicle_Make_and_Model1'],as_index=False).agg({'Insured_Amount1':['mean','std','median','sum','min','max']})\n",
    "tmp_amt1.columns = ['Vehicle_Make_and_Model1','mean_amt1_Distribution_Channel', 'std_amt1_Distribution_Channel', \n",
    "               'median_amt1_Distribution_Channel', 'sum_amt1_Distribution_Channel', 'min_amt1_Distribution_Channel', 'max_amt1_Distribution_Channel']\n",
    "\n",
    "tmp_amt2 = policy.groupby(['Vehicle_Make_and_Model1'],as_index=False).agg({'Insured_Amount2':['mean','std','median','sum','min','max']})\n",
    "tmp_amt2.columns = ['Vehicle_Make_and_Model1','mean_amt2_Distribution_Channel', 'std_amt2_Distribution_Channel', \n",
    "               'median_amt2_Distribution_Channel', 'sum_amt2_Distribution_Channel', 'min_amt2_Distribution_Channel', 'max_amt2_Distribution_Channel']\n",
    "\n",
    "tmp_amt3 = policy.groupby(['Vehicle_Make_and_Model1'],as_index=False).agg({'Insured_Amount3':['mean','std','median','sum','min','max']})\n",
    "tmp_amt3.columns = ['Vehicle_Make_and_Model1','mean_amt3_Distribution_Channel', 'std_amt3_Distribution_Channel', \n",
    "               'medianamt3_Distribution_Channel', 'sum_amt3_Distribution_Channel', 'min_amt3_Distribution_Channel', 'max_amt3_Distribution_Channel']\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "m = pd.concat([tmp_count.iloc[:,1:], \n",
    "               tmp_premium.iloc[:,1:],\n",
    "               tmp_policy.iloc[:,1:],\n",
    "               tmp_insuredID.iloc[:,1:],\n",
    "               tmp_repl.iloc[:,1:],\n",
    "               tmp_eng.iloc[:,1:],\n",
    "               tmp_amt1.iloc[:,1:],\n",
    "               tmp_amt2.iloc[:,1:],\n",
    "               tmp_amt3.iloc[:,1:]],axis=1)\n",
    "m = m.fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAXcAAAD8CAYAAACMwORRAAAABHNCSVQICAgIfAhkiAAAAAlwSFlz\nAAALEgAACxIB0t1+/AAADWJJREFUeJzt3H+s3fVdx/Hna+1w7oeg9rrMtqxN7NBmUSE3iJIoEZYU\nZloTp6EJcxpc/1knOqLpokGDiWHOzB9JndYNmXOCiIveuKvVMMwSM0gvYyJtrbt2SG/H7IUhGpfZ\nNb794xyWs9vbnnPbc/s9fO7zkTQ93+/5cM6bb9Ln/d7v+ZGqQpLUlld0PYAkafyMuyQ1yLhLUoOM\nuyQ1yLhLUoOMuyQ1aGjck9yb5FSSp85xf5L8bpL5JE8muWb8Y0qSVmKUM/f7gB3nuf9mYFv/zx7g\ngxc/liTpYgyNe1V9CvjSeZbsAv64eh4FrkjyhnENKElaufVjeIyNwImB7YX+vmfP9x9t2LChtmzZ\nMoanl6S14/HHH3+uqqaGrRtH3EeWZA+9SzdceeWVzM3NXcqnl6SXvST/Psq6cbxb5iSweWB7U3/f\nWarqQFVNV9X01NTQHzySpAs0jrjPAD/Zf9fMdcCLVXXeSzKSpNU19LJMkvuBG4ANSRaAXwFeCVBV\nvw/MArcA88CXgZ9erWElSaMZGveq2j3k/gLeNbaJJEkXzU+oSlKDjLskNci4S1KDjLskNci4S1KD\nLuknVMdly75PXNLne/qet17S55Oki+WZuyQ1yLhLUoOMuyQ1yLhLUoOMuyQ1yLhLUoOMuyQ1yLhL\nUoOMuyQ1yLhLUoNell8/MEn8KgRJk8i4N8IfMpIGeVlGkhpk3CWpQcZdkhpk3CWpQcZdkhpk3CWp\nQcZdkhpk3CWpQcZdkhrkJ1Q1dn5aVuqeZ+6S1CDjLkkN8rKMmnYpLxF5eUiTxDN3SWqQZ+7SJeCL\nzLrUjLu0xkzKpSp/4K0u4y5pzWvxB81I19yT7EhyLMl8kn3L3H9lkkeSPJHkySS3jH9USdKohsY9\nyTpgP3AzsB3YnWT7kmW/DDxYVVcDtwK/N+5BJUmjG+XM/VpgvqqOV9Vp4AFg15I1BXxT//blwBfG\nN6IkaaVGiftG4MTA9kJ/36BfBW5LsgDMAu9e7oGS7Ekyl2RucXHxAsaVJI1iXO9z3w3cV1WbgFuA\njyY567Gr6kBVTVfV9NTU1JieWpK01ChxPwlsHtje1N836HbgQYCq+jTwKmDDOAaUJK3cKHE/BGxL\nsjXJZfReMJ1ZsuYZ4EaAJN9FL+5ed5GkjgyNe1WdAfYCB4Gj9N4VczjJ3Ul29pfdCbwzyT8B9wM/\nVVW1WkNLks5vpA8xVdUsvRdKB/fdNXD7CHD9eEeTJF0ovzhMkhpk3CWpQcZdkhpk3CWpQcZdkhpk\n3CWpQcZdkhpk3CWpQcZdkhpk3CWpQcZdkhpk3CWpQcZdkhpk3CWpQcZdkhpk3CWpQcZdkhpk3CWp\nQcZdkhpk3CWpQcZdkhpk3CWpQcZdkhpk3CWpQcZdkhpk3CWpQcZdkhpk3CWpQcZdkhpk3CWpQcZd\nkhpk3CWpQcZdkhpk3CWpQSPFPcmOJMeSzCfZd441P5HkSJLDSf50vGNKklZi/bAFSdYB+4G3AAvA\noSQzVXVkYM024L3A9VX1QpJvW62BJUnDjXLmfi0wX1XHq+o08ACwa8madwL7q+oFgKo6Nd4xJUkr\nMUrcNwInBrYX+vsGvQl4U5J/TPJokh3LPVCSPUnmkswtLi5e2MSSpKHG9YLqemAbcAOwG/jDJFcs\nXVRVB6pquqqmp6amxvTUkqSlRon7SWDzwPam/r5BC8BMVX21qj4P/Cu92EuSOjBK3A8B25JsTXIZ\ncCsws2TNX9I7ayfJBnqXaY6PcU5J0goMjXtVnQH2AgeBo8CDVXU4yd1JdvaXHQSeT3IEeAT4hap6\nfrWGliSd39C3QgJU1Swwu2TfXQO3C3hP/48kqWN+QlWSGmTcJalBxl2SGmTcJalBxl2SGmTcJalB\nxl2SGmTcJalBxl2SGmTcJalBxl2SGmTcJalBxl2SGmTcJalBxl2SGmTcJalBxl2SGmTcJalBxl2S\nGmTcJalBxl2SGmTcJalBxl2SGmTcJalBxl2SGmTcJalBxl2SGmTcJalBxl2SGmTcJalBxl2SGmTc\nJalBxl2SGmTcJalBxl2SGjRS3JPsSHIsyXySfedZ92NJKsn0+EaUJK3U0LgnWQfsB24GtgO7k2xf\nZt3rgDuAx8Y9pCRpZUY5c78WmK+q41V1GngA2LXMul8D3gd8ZYzzSZIuwChx3wicGNhe6O/7miTX\nAJur6hPne6Ake5LMJZlbXFxc8bCSpNFc9AuqSV4BfAC4c9jaqjpQVdNVNT01NXWxTy1JOodR4n4S\n2Dywvam/7yWvA94M/EOSp4HrgBlfVJWk7owS90PAtiRbk1wG3ArMvHRnVb1YVRuqaktVbQEeBXZW\n1dyqTCxJGmpo3KvqDLAXOAgcBR6sqsNJ7k6yc7UHlCSt3PpRFlXVLDC7ZN9d51h7w8WPJUm6GH5C\nVZIaZNwlqUHGXZIaZNwlqUHGXZIaZNwlqUHGXZIaZNwlqUHGXZIaZNwlqUHGXZIaZNwlqUHGXZIa\nZNwlqUHGXZIaZNwlqUHGXZIaZNwlqUHGXZIaZNwlqUHGXZIaZNwlqUHGXZIaZNwlqUHGXZIaZNwl\nqUHGXZIaZNwlqUHGXZIaZNwlqUHGXZIaZNwlqUHGXZIaZNwlqUEjxT3JjiTHkswn2bfM/e9JciTJ\nk0keTvLG8Y8qSRrV0LgnWQfsB24GtgO7k2xfsuwJYLqqvht4CPiNcQ8qSRrdKGfu1wLzVXW8qk4D\nDwC7BhdU1SNV9eX+5qPApvGOKUlaiVHivhE4MbC90N93LrcDf7PcHUn2JJlLMre4uDj6lJKkFRnr\nC6pJbgOmgfcvd39VHaiq6aqanpqaGudTS5IGrB9hzUlg88D2pv6+r5PkJuCXgB+qqv8dz3iSpAsx\nypn7IWBbkq1JLgNuBWYGFyS5GvgDYGdVnRr/mJKklRga96o6A+wFDgJHgQer6nCSu5Ps7C97P/Ba\n4M+TfDbJzDkeTpJ0CYxyWYaqmgVml+y7a+D2TWOeS5J0EfyEqiQ1yLhLUoOMuyQ1yLhLUoOMuyQ1\nyLhLUoOMuyQ1yLhLUoOMuyQ1yLhLUoOMuyQ1yLhLUoOMuyQ1yLhLUoOMuyQ1yLhLUoOMuyQ1yLhL\nUoOMuyQ1yLhLUoOMuyQ1yLhLUoOMuyQ1yLhLUoOMuyQ1yLhLUoOMuyQ1yLhLUoOMuyQ1yLhLUoOM\nuyQ1yLhLUoOMuyQ1yLhLUoNGinuSHUmOJZlPsm+Z+78hyZ/1738syZZxDypJGt3QuCdZB+wHbga2\nA7uTbF+y7Hbghar6DuC3gPeNe1BJ0uhGOXO/FpivquNVdRp4ANi1ZM0u4CP92w8BNybJ+MaUJK3E\nKHHfCJwY2F7o71t2TVWdAV4EvnUcA0qSVi5Vdf4FyduAHVX1M/3ttwPfV1V7B9Y81V+z0N/+t/6a\n55Y81h5gT3/zKuDYuP5HRrQBeG7oqrXFY3I2j8nyPC5n6+KYvLGqpoYtWj/CA50ENg9sb+rvW27N\nQpL1wOXA80sfqKoOAAdGeM5VkWSuqqa7ev5J5DE5m8dkeR6Xs03yMRnlsswhYFuSrUkuA24FZpas\nmQHe0b/9NuCTNexXAknSqhl65l5VZ5LsBQ4C64B7q+pwkruBuaqaAT4MfDTJPPAlej8AJEkdGeWy\nDFU1C8wu2XfXwO2vAD8+3tFWRWeXhCaYx+RsHpPleVzONrHHZOgLqpKklx+/fkCSGrQm4p5kc5JH\nkhxJcjjJHV3PNCmSrEvyRJK/7nqWSZDkiiQPJfmXJEeTfH/XM3Utyc/3/908leT+JK/qeqYuJLk3\nyan+W79f2vctSf4+yef6f39zlzMOWhNxB84Ad1bVduA64F3LfIXCWnUHcLTrISbI7wB/W1XfCXwP\na/zYJNkI/CwwXVVvpvemirX6hon7gB1L9u0DHq6qbcDD/e2JsCbiXlXPVtVn+rf/m94/2KWfsl1z\nkmwC3gp8qOtZJkGSy4EfpPfuL6rqdFX9Z7dTTYT1wDf2P8PyauALHc/Tiar6FL13Aw4a/OqVjwA/\nekmHOo81EfdB/W+svBp4rNtJJsJvA78I/F/Xg0yIrcAi8Ef9S1UfSvKarofqUlWdBH4TeAZ4Fnix\nqv6u26kmyuur6tn+7S8Cr+9ymEFrKu5JXgv8BfBzVfVfXc/TpSQ/Apyqqse7nmWCrAeuAT5YVVcD\n/8ME/Zrdhf415F30fvB9O/CaJLd1O9Vk6n9wc2Lefrhm4p7klfTC/rGq+njX80yA64GdSZ6m902f\nP5zkT7odqXMLwEJVvfRb3UP0Yr+W3QR8vqoWq+qrwMeBH+h4pknyH0neAND/+1TH83zNmoh7/+uH\nPwwcraoPdD3PJKiq91bVpqraQu8Fsk9W1Zo+I6uqLwInklzV33UjcKTDkSbBM8B1SV7d/3d0I2v8\nReYlBr965R3AX3U4y9dZE3Gnd5b6dnpnp5/t/7ml66E0kd4NfCzJk8D3Ar/e8Tyd6v8W8xDwGeCf\n6TVjYj+VuZqS3A98GrgqyUKS24F7gLck+Ry933Lu6XLGQX5CVZIatFbO3CVpTTHuktQg4y5JDTLu\nktQg4y5JDTLuktQg4y5JDTLuktSg/wfZJiD5o98RuAAAAABJRU5ErkJggg==\n",
      "text/plain": [
       "<matplotlib.figure.Figure at 0x116081290>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[0.95356417205196209, 0.89992540508520469, 0.89840281671462652, 0.8531950226511239, 0.83801630793524684, 0.8282670101165639, 0.82261393406388572, 0.8211312872739065, 0.81829590920826001]\n"
     ]
    }
   ],
   "source": [
    "silhouette_avgs = []\n",
    "ks = range(2, 11)\n",
    "for k in ks:\n",
    "    kmeans_fit = KMeans(n_clusters = k).fit(m)\n",
    "    cluster_labels = kmeans_fit.labels_\n",
    "    silhouette_avg = metrics.silhouette_score(m, cluster_labels)\n",
    "    silhouette_avgs.append(silhouette_avg)\n",
    "\n",
    "plt.bar(ks, silhouette_avgs)\n",
    "plt.show()\n",
    "print(silhouette_avgs)\n",
    "plt.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0    114\n",
      "5     11\n",
      "4      6\n",
      "2      2\n",
      "1      2\n",
      "3      1\n",
      "Name: 0, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "kmeans = KMeans(n_clusters=6, random_state=0).fit(m)\n",
    "print pd.DataFrame(kmeans.labels_)[0].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "tmp = pd.DataFrame({'Vehicle_Make_and_Model1':tmp_premium.Vehicle_Make_and_Model1, 'model1_cluster_6':KMeans(n_clusters=6, random_state=0).fit(m).labels_})\n",
    "policy = pd.merge(policy, tmp, how='left', on='Vehicle_Make_and_Model1')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "tmp = policy.groupby('Policy_Number', as_index=False)['model1_cluster_6'].first()\n",
    "tr = pd.merge(tr, tmp , on = 'Policy_Number', how = 'left')\n",
    "te = pd.merge(te, tmp , on = 'Policy_Number', how = 'left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "210763\n",
      "140510\n"
     ]
    }
   ],
   "source": [
    "print len(tr)\n",
    "print len(te)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "policy['dbirth_year'] = policy.dbirth.str.split('/').str[1].fillna(0).astype('int')\n",
    "policy.loc[(policy.dbirth_year ==0)|(policy.dbirth_year > 2016),'dbirth_year'] = float('nan')\n",
    "policy['d_age'] = 2016 - policy.dbirth_year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "tmp = pd.get_dummies(claim[\"Driver's_Relationship_with_Insured\"])\n",
    "tmp.columns = ['relationship1','relationship2','relationship3','relationship4','relationship5','relationship6',\n",
    "              'relationship7']\n",
    "claim = pd.concat([claim, tmp], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "print claim.Accident_Date.head()\n",
    "print claim.Accident_Time.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "tmp = pd.get_dummies(policy.Imported_or_Domestic_Car)\n",
    "tmp.columns = ['car_10','car_20','car_21','car_22','car_23','car_24','car_30','car_40','car_50','car_90']\n",
    "policy = pd.concat([policy, tmp], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 136\n",
    "fh = FeatureHasher(n_features=5, input_type='string')\n",
    "tmp = fh.transform(policy.Vehicle_Make_and_Model1)\n",
    "tmp = pd.DataFrame(tmp.toarray())\n",
    "tmp.columns = [\"model1_haser1\",\"model1_haser2\",\"model1_haser3\",\"model1_haser4\",\"model1_haser5\"]\n",
    "policy = pd.concat([policy, tmp], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 8112\n",
    "fh = FeatureHasher(n_features=10, input_type='string')\n",
    "tmp = fh.transform(policy.Vehicle_Make_and_Model2)\n",
    "tmp = pd.DataFrame(tmp.toarray())\n",
    "tmp.columns = [\"model2_haser1\",\"model2_haser2\",\"model2_haser3\",\"model2_haser4\",\"model2_haser5\",\n",
    "              \"model2_haser6\",\"model2_haser7\",\"model2_haser8\",\"model2_haser9\",\"model2_haser10\"]\n",
    "policy = pd.concat([policy, tmp], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 7405\n",
    "fh = FeatureHasher(n_features=10, input_type='string')\n",
    "tmp = fh.transform(policy[\"Coding_of_Vehicle_Branding_&_Type\"])\n",
    "tmp = pd.DataFrame(tmp.toarray())\n",
    "tmp.columns = [\"Branding_haser1\",\"Branding_haser2\",\"Branding_haser3\",\"Branding_haser4\",\"Branding_haser5\",\n",
    "              \"Branding_haser6\",\"Branding_haser7\",\"Branding_haser8\",\"Branding_haser9\",\"Branding_haser10\"]\n",
    "policy = pd.concat([policy, tmp], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 22\n",
    "fh = FeatureHasher(n_features=5, input_type='string')\n",
    "tmp = fh.transform(policy.iply_area)\n",
    "tmp = pd.DataFrame(tmp.toarray())\n",
    "tmp.columns = [\"iply_haser1\",\"iply_haser2\",\"iply_haser3\",\"iply_haser4\",\"iply_haser5\"]\n",
    "policy = pd.concat([policy, tmp], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 1722\n",
    "fh = FeatureHasher(n_features=10, input_type='string')\n",
    "tmp = fh.transform(policy.aassured_zip)\n",
    "tmp = pd.DataFrame(tmp.toarray())\n",
    "tmp.columns = [\"zip_haser1\",\"zip_haser2\",\"zip_haser3\",\"zip_haser4\",\"zip_haser5\",\n",
    "              \"zip_haser6\",\"zip_haser7\",\"zip_haser8\",\"zip_haser9\",\"zip_haser10\"]\n",
    "policy = pd.concat([policy, tmp], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 875\n",
    "fh = FeatureHasher(n_features=10, input_type='string')\n",
    "tmp = fh.transform(policy.Distribution_Channel)\n",
    "tmp = pd.DataFrame(tmp.toarray())\n",
    "tmp.columns = [\"zip_haser1\",\"zip_haser2\",\"zip_haser3\",\"zip_haser4\",\"zip_haser5\",\n",
    "              \"zip_haser6\",\"zip_haser7\",\"zip_haser8\",\"zip_haser9\",\"zip_haser10\"]\n",
    "policy = pd.concat([policy, tmp], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 賠案性質\n",
    "tmp = pd.get_dummies(claim.Nature_of_the_claim)\n",
    "tmp.columns = ['Nature_1','Nature_2']\n",
    "claim = pd.concat([claim,tmp], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 駕駛人性別\n",
    "tmp = pd.get_dummies(claim[\"Driver's_Gender\"])\n",
    "tmp.columns = ['driver_gender_1','driver_gender_2']\n",
    "claim = pd.concat([claim,tmp], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 肇事駕駛婚姻\n",
    "tmp = pd.get_dummies(claim[\"Marital_Status_of_Driver\"])\n",
    "tmp.columns = ['driver_marriage_1','driver_marriage_2']\n",
    "claim = pd.concat([claim,tmp], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "print len(np.unique(claim[\"Cause_of_Loss\"]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 肇事原因\n",
    "tmp = pd.get_dummies(claim[\"Cause_of_Loss\"])\n",
    "tmp.columns = ['name_{}'.format(i) for i in range(len(np.unique(claim[\"Cause_of_Loss\"])))]\n",
    "claim = pd.concat([claim,tmp], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 險種代號\n",
    "tmp = pd.get_dummies(claim[\"Coverage\"])\n",
    "tmp.columns = ['claim_'+ tmp.columns[i] for i in range(len(np.unique(claim[\"Coverage\"])))]\n",
    "claim = pd.concat([claim,tmp], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 肇事駕駛年齡\n",
    "claim[\"DOB_of_Driver\"] = pd.to_datetime(claim.DOB_of_Driver)\n",
    "claim[\"claim_driver_year\"] = claim.DOB_of_Driver.dt.year\n",
    "claim[\"claim_driver_age\"] = 2016 - claim.claim_driver_year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 單一險種close\n",
    "tmp = pd.get_dummies(claim[\"Claim_Status_(close,_open,_reopen_etc)\"])\n",
    "tmp.columns = ['n_not_close','n_close']\n",
    "claim = pd.concat([claim,tmp], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "print len(np.unique(claim[\"Accident_area\"]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "tmp = pd.get_dummies(claim[\"Accident_area\"])\n",
    "claim = pd.concat([claim,tmp], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "policy.Main_Insurance_Coverage_Group.value_counts() / len(policy)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "print len(tr)\n",
    "print len(te)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Feature Engineering "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 保單金額\n",
    "tmp = policy.groupby([\"Policy_Number\"],as_index=False).agg({'Premium':['mean','std','median','sum','min','max']})\n",
    "tmp.columns = ['Policy_Number','mean_Premium', 'std_Premium', \n",
    "               'median_Premium', 'sum_Premium', 'min_Premium', 'max_Premium']\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 保額一二三\n",
    "tmp = policy.groupby([\"Policy_Number\"],as_index=False).agg({'Insured_Amount':['mean','sum']})\n",
    "tmp.columns = ['Policy_Number','mean_Insured_Amount', 'sum_Insured_Amount']\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 保額1\n",
    "#policy[['Insured_Amount1', 'Insured_Amount2', 'Insured_Amount3']]\n",
    "tmp = policy.groupby([\"Policy_Number\"],as_index=False).agg({'Insured_Amount1':['mean','std','median','sum','min','max']})\n",
    "tmp.columns = ['Policy_Number','mean_Insured_Amount1', 'std_Insured_Amount1', \n",
    "               'median_Insured_Amount1', 'sum_Insured_Amount1', 'min_Insured_Amount1', 'max_Insured_Amount1']\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 保額2\n",
    "#policy[['Insured_Amount1', 'Insured_Amount2', 'Insured_Amount3']]\n",
    "tmp = policy.groupby([\"Policy_Number\"],as_index=False).agg({'Insured_Amount2':['mean','std','median','sum','min','max']})\n",
    "tmp.columns = ['Policy_Number','mean_Insured_Amount2', 'std_Insured_Amount2', \n",
    "               'median_Insured_Amount2', 'sum_Insured_Amount2', 'min_Insured_Amount2', 'max_Insured_Amount2']\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 保額3\n",
    "#policy[['Insured_Amount1', 'Insured_Amount2', 'Insured_Amount3']]\n",
    "tmp = policy.groupby([\"Policy_Number\"],as_index=False).agg({'Insured_Amount3':['mean','std','median','sum','min','max']})\n",
    "tmp.columns = ['Policy_Number','mean_Insured_Amount3', 'std_Insured_Amount3', \n",
    "               'median_Insured_Amount3', 'sum_Insured_Amount3', 'min_Insured_Amount3', 'max_Insured_Amount3']\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Imported car\n",
    "tmp = policy.groupby('Policy_Number', as_index=0)['Imported_or_Domestic_Car'].first()\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 乘載數量\n",
    "tmp = policy.groupby([\"Policy_Number\"],as_index=False).qpt.first()\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 重置成本\n",
    "tmp = policy.groupby([\"Policy_Number\"],as_index=False)[\"Replacement_cost_of_insured_vehicle\"].first()\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 排氣量\n",
    "tmp = policy.groupby([\"Policy_Number\"],as_index=False)[\"Engine_Displacement_(Cubic_Centimeter)\"].first()\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 被保人年齡\n",
    "tmp = policy.groupby([\"Policy_Number\"],as_index=False)['age'].first()\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number') "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "print len(tr)\n",
    "print len(te)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 要保人年齡\n",
    "#tmp = policy.groupby([\"Policy_Number\"],as_index=False)['d_age'].first()\n",
    "#tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "#te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number') "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 前一年度非車險\n",
    "tmp = policy.groupby([\"Policy_Number\"],as_index=False)['Multiple_Products_with_TmNewa_(Yes_or_No?)'].first()\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "##policy[policy.Policy_Number == '000061141b237e8619efedcd6939fddeff05b9a5']['Coverage_Deductible_if_applied']\n",
    "##policy.groupby('Policy_Number', as_index=0).agg({'Coverage_Deductible_if_applied':'std'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 車責、車體係數\n",
    "tmp = policy.groupby([\"Policy_Number\"],as_index=False)['lia_class','plia_acc', 'pdmg_acc'].first()\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 自負額\n",
    "tmp = policy.groupby([\"Policy_Number\"],as_index=False).agg({'Coverage_Deductible_if_applied':['mean','std','median','sum','min','max']})\n",
    "tmp.columns = ['Policy_Number','mean_Deductible', 'std_Deductible', \n",
    "               'median_Deductible', 'sum_Deductible', 'min_Deductible', 'max_Deductible']\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "##policy[['Policy_Number','Coverage_Group0','Coverage_Group1','Coverage_Group2']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 各險種次數\n",
    "tmp = policy.groupby('Policy_Number',as_index=False)[['Coverage_Group0','Coverage_Group1','Coverage_Group2']].sum()\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 是否有各險種\n",
    "tr.loc[tr.Coverage_Group0 > 0 ,'Coverage_Group0_YN'] = 1\n",
    "tr.loc[tr.Coverage_Group1 > 0 ,'Coverage_Group1_YN'] = 1\n",
    "tr.loc[tr.Coverage_Group2 > 0 ,'Coverage_Group2_YN'] = 1\n",
    "te.loc[te.Coverage_Group0 > 0 ,'Coverage_Group0_YN'] = 1\n",
    "te.loc[te.Coverage_Group1 > 0 ,'Coverage_Group1_YN'] = 1\n",
    "te.loc[te.Coverage_Group2 > 0 ,'Coverage_Group2_YN'] = 1\n",
    "\n",
    "tr.loc[tr.Coverage_Group0 == 0 ,'Coverage_Group0_YN'] = 0\n",
    "tr.loc[tr.Coverage_Group1 == 0 ,'Coverage_Group1_YN'] = 0\n",
    "tr.loc[tr.Coverage_Group2 == 0 ,'Coverage_Group2_YN'] = 0\n",
    "te.loc[te.Coverage_Group0 == 0 ,'Coverage_Group0_YN'] = 0\n",
    "te.loc[te.Coverage_Group1 == 0 ,'Coverage_Group1_YN'] = 0\n",
    "te.loc[te.Coverage_Group2 == 0 ,'Coverage_Group2_YN'] = 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 與被保險人關係\n",
    "tmp = claim.groupby('Policy_Number',as_index=False)[['relationship1','relationship2','relationship3','relationship4','relationship5','relationship6',\n",
    "              'relationship7']].sum()\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')\n",
    "\n",
    "tr.loc[tr.relationship1 > 0 ,'relationship1_YN'] = 1\n",
    "tr.loc[tr.relationship2 > 0 ,'relationship2_YN'] = 1\n",
    "tr.loc[tr.relationship3 > 0 ,'relationship3_YN'] = 1\n",
    "tr.loc[tr.relationship4 > 0 ,'relationship4_YN'] = 1\n",
    "tr.loc[tr.relationship5 > 0 ,'relationship5_YN'] = 1\n",
    "tr.loc[tr.relationship6 > 0 ,'relationship6_YN'] = 1\n",
    "tr.loc[tr.relationship7 > 0 ,'relationship7_YN'] = 1\n",
    "te.loc[te.relationship1 > 0 ,'relationship1_YN'] = 1\n",
    "te.loc[te.relationship2 > 0 ,'relationship2_YN'] = 1\n",
    "te.loc[te.relationship3 > 0 ,'relationship3_YN'] = 1\n",
    "te.loc[te.relationship4 > 0 ,'relationship4_YN'] = 1\n",
    "te.loc[te.relationship5 > 0 ,'relationship5_YN'] = 1\n",
    "te.loc[te.relationship6 > 0 ,'relationship6_YN'] = 1\n",
    "te.loc[te.relationship7 > 0 ,'relationship7_YN'] = 1\n",
    "\n",
    "tr.loc[tr.relationship1 == 0 ,'relationship1_YN'] = 0\n",
    "tr.loc[tr.relationship2 == 0 ,'relationship2_YN'] = 0\n",
    "tr.loc[tr.relationship3 == 0 ,'relationship3_YN'] = 0\n",
    "tr.loc[tr.relationship4 == 0 ,'relationship4_YN'] = 0\n",
    "tr.loc[tr.relationship5 == 0 ,'relationship5_YN'] = 0\n",
    "tr.loc[tr.relationship6 == 0 ,'relationship6_YN'] = 0\n",
    "tr.loc[tr.relationship7 == 0 ,'relationship7_YN'] = 0\n",
    "te.loc[te.relationship1 == 0 ,'relationship1_YN'] = 0\n",
    "te.loc[te.relationship2 == 0 ,'relationship2_YN'] = 0\n",
    "te.loc[te.relationship3 == 0 ,'relationship3_YN'] = 0\n",
    "te.loc[te.relationship4 == 0 ,'relationship4_YN'] = 0\n",
    "te.loc[te.relationship5 == 0 ,'relationship5_YN'] = 0\n",
    "te.loc[te.relationship6 == 0 ,'relationship6_YN'] = 0\n",
    "te.loc[te.relationship7 == 0 ,'relationship7_YN'] = 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 賠案性質\n",
    "tmp = claim.groupby('Policy_Number',as_index=False)[['Nature_1','Nature_2']].sum()\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')\n",
    "\n",
    "tr.loc[tr.Nature_1 > 0 ,'Nature_1_YN'] = 1\n",
    "tr.loc[tr.Nature_2 > 0 ,'Nature_2_YN'] = 1\n",
    "\n",
    "te.loc[te.Nature_1 > 0 ,'Nature_1_YN'] = 1\n",
    "te.loc[te.Nature_2 > 0 ,'Nature_2_YN'] = 1\n",
    "\n",
    "\n",
    "tr.loc[tr.Nature_1 == 0 ,'Nature_1_YN'] = 0\n",
    "tr.loc[tr.Nature_2 == 0 ,'Nature_2_YN'] = 0\n",
    "\n",
    "te.loc[te.Nature_1 == 0 ,'Nature_1_YN'] = 0\n",
    "te.loc[te.Nature_2 == 0 ,'Nature_2_YN'] = 0\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 駕駛人性別\n",
    "['driver_gender_1','driver_gender_2']\n",
    "\n",
    "tmp = claim.groupby('Policy_Number',as_index=False)[['driver_gender_1','driver_gender_2']].sum()\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')\n",
    "\n",
    "tr.loc[tr.driver_gender_1 > 0 ,'driver_gender_1_YN'] = 1\n",
    "tr.loc[tr.driver_gender_2 > 0 ,'driver_gender_2_YN'] = 1\n",
    "\n",
    "te.loc[te.driver_gender_1 > 0 ,'driver_gender_1_YN'] = 1\n",
    "te.loc[te.driver_gender_2 > 0 ,'driver_gender_2_YN'] = 1\n",
    "\n",
    "\n",
    "tr.loc[tr.driver_gender_1 == 0 ,'driver_gender_1_YN'] = 0\n",
    "tr.loc[tr.driver_gender_2 == 0 ,'driver_gender_2_YN'] = 0\n",
    "\n",
    "te.loc[te.driver_gender_1 == 0 ,'driver_gender_1_YN'] = 0\n",
    "te.loc[te.driver_gender_2 == 0 ,'driver_gender_2_YN'] = 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 肇事駕駛婚姻\n",
    "['driver_marriage_1','driver_marriage_2']\n",
    "\n",
    "tmp = claim.groupby('Policy_Number',as_index=False)[['driver_marriage_1','driver_marriage_2']].sum()\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')\n",
    "\n",
    "tr.loc[tr.driver_marriage_1 > 0 ,'driver_marriage_1_YN'] = 1\n",
    "tr.loc[tr.driver_marriage_2 > 0 ,'driver_marriage_2_YN'] = 1\n",
    "\n",
    "te.loc[te.driver_marriage_1 > 0 ,'driver_marriage_1_YN'] = 1\n",
    "te.loc[te.driver_marriage_2 > 0 ,'driver_marriage_2_YN'] = 1\n",
    "\n",
    "\n",
    "tr.loc[tr.driver_marriage_1 == 0 ,'driver_marriage_1_YN'] = 0\n",
    "tr.loc[tr.driver_marriage_2 == 0 ,'driver_marriage_2_YN'] = 0\n",
    "\n",
    "te.loc[te.driver_marriage_1 == 0 ,'driver_marriage_1_YN'] = 0\n",
    "te.loc[te.driver_marriage_2 == 0 ,'driver_marriage_2_YN'] = 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 肇事原因\n",
    "tmp = pd.get_dummies(claim[\"Cause_of_Loss\"])\n",
    "tmp = claim.groupby('Policy_Number',as_index=False)[['name_{}'.format(i) for i in range(len(np.unique(claim[\"Cause_of_Loss\"])))]].sum()\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# claim 險種代號\n",
    "tmp = pd.get_dummies(claim[\"Coverage\"])\n",
    "tmp = claim.groupby('Policy_Number',as_index=False)[['claim_'+ tmp.columns[i] for i in range(len(np.unique(claim[\"Coverage\"])))]].sum()\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# claim 險種代號的\n",
    "tmp = policy.pivot_table(index= 'Policy_Number', columns= 'Insurance_Coverage', values= 'Premium')\n",
    "tmp = tmp.reset_index(drop=False)\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number') "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 是否遇到追償\n",
    "#claim.loc[claim[\"Salvage_or_Subrogation?\"] > 0 ,'SoS_or_not'] = 1\n",
    "tmp = claim.groupby('Policy_Number', as_index = 0)[\"SoS_or_not\"].sum()\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number') "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 被保險人性質\n",
    "tmp = policy.groupby([\"Policy_Number\"],as_index=False)[['fassured1','fassured2','fassured3','fassured6']].first()\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 婚姻\n",
    "tmp = policy.groupby([\"Policy_Number\"],as_index=False)[['fmarriage0','fmarriage1','fmarriage2']].first()\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 性別\n",
    "tmp = policy.groupby([\"Policy_Number\"],as_index=False)[['fsex0','fsex1','fsex2']].first()\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 製造年齡\n",
    "tmp = policy.groupby([\"Policy_Number\"],as_index=False)['Manafactured_Year_and_Month'].first()\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Cancellation\n",
    "tmp = policy.groupby([\"Policy_Number\"],as_index=False)['Cancellation'].first()\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 車責\n",
    "tmp = policy.groupby([\"Policy_Number\"],as_index=False).lia.first()\n",
    "tmp.columns = ['Policy_Number','lia']\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 車責\n",
    "tmp = policy.groupby([\"Policy_Number\"],as_index=False).acc.first()\n",
    "tmp.columns = ['Policy_Number','acc']\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    " # 保單被購買次數 = 保單被購買的險種種數，一張保單對應到一個被保險人，但一個人可以買很多保單(不同保單或同一保單不同險種)\n",
    "tmp = policy.groupby([\"Policy_Number\"],as_index=False)[\"Insured's_ID\"].count()\n",
    "#tmp = policy.groupby([\"Policy_Number\"],as_index=False).agg({'Insurance_Coverage':'nunique'})\n",
    "tmp = tmp.rename(columns={\"Insured's_ID\":'Policy_showup_Numbers'})\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# zip cluster\n",
    "#tmp = policy.groupby([\"Policy_Number\"],as_index=False)[\"zip_cluster\"].first()\n",
    "#tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "#te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')\n",
    "\n",
    "# zip cluster 2\n",
    "tmp = policy.groupby([\"Policy_Number\"],as_index=False)[\"zip_cluster_2\"].first()\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# model1 cluster\n",
    "tmp = policy.groupby([\"Policy_Number\"],as_index=False)[\"model1_cluster_6\"].first()\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 保單理賠幾次\n",
    "tmp = claim.groupby(['Policy_Number'], as_index=False)['Claim_Number'].count()\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')\n",
    "tr.Claim_Number = tr.Claim_Number.fillna(0)\n",
    "te.Claim_Number = te.Claim_Number.fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 已決賠款\n",
    "tmp = claim.groupby([\"Policy_Number\"],as_index=False).agg({'Paid_Loss_Amount':['mean','std','median','sum','min','max']})\n",
    "tmp.columns = ['Policy_Number','mean_Loss', 'std_Loss', \n",
    "               'median_Loss', 'sum_Loss', 'min_Loss', 'max_Loss']\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number') "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 已決費用\n",
    "tmp = claim.groupby([\"Policy_Number\"],as_index=False).agg({'paid_Expenses_Amount':['mean','std','median','sum','min','max']})\n",
    "tmp.columns = ['Policy_Number','mean_Expenses', 'std_Expenses', \n",
    "               'median_Expenses', 'sum_Expenses', 'min_Expenses', 'max_Expenses']\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number') "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 已決金額\n",
    "claim['total_paid'] = claim.paid_Expenses_Amount + claim.Paid_Loss_Amount\n",
    "tmp = claim.groupby([\"Policy_Number\"],as_index=False).agg({'total_paid':['mean','std','median','sum','min','max']})\n",
    "tmp.columns = ['Policy_Number','mean_total_paid', 'std_total_paid', \n",
    "               'median_total_paid', 'sum_total_paid', 'min_total_paid', 'max_total_paid']\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number') "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 事故關係人數\n",
    "tmp = claim.groupby([\"Policy_Number\"],as_index=False).agg({'number_of_claimants':['mean','std','median','sum','min','max']})\n",
    "tmp.columns = ['Policy_Number','mean_claimants', 'std_claimants', \n",
    "               'median_claimants', 'sum_claimants', 'min_claimants', 'max_claimants']\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number') "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 追償金額\n",
    "tmp = claim.groupby([\"Policy_Number\"],as_index=False)[\"Salvage_or_Subrogation?\"].sum()\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')\n",
    "\n",
    "tmp = claim.groupby([\"Policy_Number\"],as_index=False)[\"Salvage_or_Subrogation?\"].mean()\n",
    "tmp.columns = [\"Policy_Number\", \"mean_Salvage_or_Subrogation\"]\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 自負額\n",
    "tmp = claim.groupby([\"Policy_Number\"],as_index=False).agg({'Deductible':['mean','sum']})\n",
    "tmp.columns = ['Policy_Number','mean_claim_Deductible','sum_claim_Deductible']\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 自負額\n",
    "tmp = claim.groupby([\"Policy_Number\"],as_index=False).agg({'At_Fault?':['mean','sum']})\n",
    "tmp.columns = ['Policy_Number','mean_Fault','sum_Fault']\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 肇事駕駛年齡\n",
    "tmp = claim.groupby([\"Policy_Number\"],as_index=False).agg({'claim_driver_age':['mean','min','max']})\n",
    "tmp.columns = ['Policy_Number','mean_driver_age','min_driver_age', 'max_driver_age']\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 出險地區\n",
    "tmp = claim.groupby('Policy_Number',as_index=False)[[u'0286dd552c9bea9a69ecb3759e7b94777635514b',\n",
    "       u'17ba0791499db908433b80f37c5fbc89b870084b',\n",
    "       u'1d513c0bcbe33b2e7440e5e14d0b22ef95c9d673',\n",
    "       u'2a459380709e2fe4ac2dae5733c73225ff6cfee1',\n",
    "       u'2d0c8af807ef45ac17cafb2973d866ba8f38caa9',\n",
    "       u'356a192b7913b04c54574d18c28d46e6395428ab',\n",
    "       u'4d89d294cd4ca9f2ca57dc24a53ffb3ef5303122',\n",
    "       u'511a418e72591eb7e33f703f04c3fa16df6c90bd',\n",
    "       u'59129aacfb6cebbe2c52f30ef3424209f7252e82',\n",
    "       u'6c1e671f9af5b46d9c1a52067bdf0e53685674f7',\n",
    "       u'761f22b2c1593d0bb87e0b606f990ba4974706de',\n",
    "       u'76546f9a641ede2beab506b96df1688d889e629a',\n",
    "       u'7b52009b64fd0a2a49e6d8a939753077792b0554',\n",
    "       u'92cfceb39d57d914ed8b14d0e37643de0797ae56',\n",
    "       u'98fbc42faedc02492397cb5962ea3a3ffc0a9243',\n",
    "       u'a17554a0d2b15a664c0e73900184544f19e70227',\n",
    "       u'bd307a3ec329e10a2cff8fb87480823da114f8f4',\n",
    "       u'c1dfd96eea8cc2b62785275bca38ac261256e278',\n",
    "       u'c66c65175fecc3103b3b587be9b5b230889c8628',\n",
    "       u'f1abd670358e036c31296e66b3b66c382ac00812',\n",
    "       u'fa35e192121eabf3dabf9f5ea6abdbcbc107ac3b',\n",
    "       u'fb644351560d8296fe6da332236b1f8d61b2828a']].sum()\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 單一險種close\n",
    "['n_not_close','n_close']\n",
    "tmp = claim.groupby('Policy_Number',as_index=False)[['n_not_close','n_close']].sum()\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')\n",
    "\n",
    "tr.loc[tr.n_not_close > 0 ,'n_not_close_YN'] = 1\n",
    "tr.loc[tr.n_close > 0 ,'n_close_YN'] = 1\n",
    "\n",
    "te.loc[te.n_not_close > 0 ,'n_not_close_YN'] = 1\n",
    "te.loc[te.n_close > 0 ,'n_close_YN'] = 1\n",
    "\n",
    "\n",
    "tr.loc[tr.n_not_close == 0 ,'n_not_close_YN'] = 0\n",
    "tr.loc[tr.n_close == 0 ,'n_close_YN'] = 0\n",
    "\n",
    "te.loc[te.n_not_close == 0 ,'n_not_close_YN'] = 0\n",
    "te.loc[te.n_close == 0 ,'n_close_YN'] = 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#import car\n",
    "['car_10','car_20','car_21','car_22','car_23','car_24','car_30','car_40','car_50','car_90']\n",
    "tmp = policy.groupby([\"Policy_Number\"],as_index=False)[['car_10','car_20','car_21','car_22','car_23','car_24','car_30','car_40','car_50','car_90']].sum()\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')\n",
    "\n",
    "tr.loc[tr.car_10 > 0 ,'car_10_YN'] = 1\n",
    "tr.loc[tr.car_20 > 0 ,'car_20_YN'] = 1\n",
    "tr.loc[tr.car_21 > 0 ,'car_21_YN'] = 1\n",
    "tr.loc[tr.car_22 > 0 ,'car_22_YN'] = 1\n",
    "tr.loc[tr.car_23 > 0 ,'car_23_YN'] = 1\n",
    "tr.loc[tr.car_24 > 0 ,'car_24_YN'] = 1\n",
    "tr.loc[tr.car_30 > 0 ,'car_30_YN'] = 1\n",
    "tr.loc[tr.car_40 > 0 ,'car_40_YN'] = 1\n",
    "tr.loc[tr.car_50 > 0 ,'car_50_YN'] = 1\n",
    "tr.loc[tr.car_90 > 0 ,'car_90_YN'] = 1\n",
    "\n",
    "te.loc[te.car_10 > 0 ,'car_10_YN'] = 1\n",
    "te.loc[te.car_20 > 0 ,'car_20_YN'] = 1\n",
    "te.loc[te.car_21 > 0 ,'car_21_YN'] = 1\n",
    "te.loc[te.car_22 > 0 ,'car_22_YN'] = 1\n",
    "te.loc[te.car_23 > 0 ,'car_23_YN'] = 1\n",
    "te.loc[te.car_24 > 0 ,'car_24_YN'] = 1\n",
    "te.loc[te.car_30 > 0 ,'car_30_YN'] = 1\n",
    "te.loc[te.car_40 > 0 ,'car_40_YN'] = 1\n",
    "te.loc[te.car_50 > 0 ,'car_50_YN'] = 1\n",
    "te.loc[te.car_90 > 0 ,'car_90_YN'] = 1\n",
    "\n",
    "tr.loc[tr.car_10 == 0 ,'car_10_YN'] = 0\n",
    "tr.loc[tr.car_20 == 0 ,'car_20_YN'] = 0\n",
    "tr.loc[tr.car_21 == 0 ,'car_21_YN'] = 0\n",
    "tr.loc[tr.car_22 == 0 ,'car_22_YN'] = 0\n",
    "tr.loc[tr.car_23 == 0 ,'car_23_YN'] = 0\n",
    "tr.loc[tr.car_24 == 0 ,'car_24_YN'] = 0\n",
    "tr.loc[tr.car_30 == 0 ,'car_30_YN'] = 0\n",
    "tr.loc[tr.car_40 == 0 ,'car_40_YN'] = 0\n",
    "tr.loc[tr.car_50 == 0 ,'car_50_YN'] = 0\n",
    "tr.loc[tr.car_90 == 0 ,'car_90_YN'] = 0\n",
    "\n",
    "te.loc[te.car_10 == 0 ,'car_10_YN'] = 0\n",
    "te.loc[te.car_20 == 0 ,'car_20_YN'] = 0\n",
    "te.loc[te.car_21 == 0 ,'car_21_YN'] = 0\n",
    "te.loc[te.car_22 == 0 ,'car_22_YN'] = 0\n",
    "te.loc[te.car_23 == 0 ,'car_23_YN'] = 0\n",
    "te.loc[te.car_24 == 0 ,'car_24_YN'] = 0\n",
    "te.loc[te.car_30 == 0 ,'car_30_YN'] = 0\n",
    "te.loc[te.car_40 == 0 ,'car_40_YN'] = 0\n",
    "te.loc[te.car_50 == 0 ,'car_50_YN'] = 0\n",
    "te.loc[te.car_90 == 0 ,'car_90_YN'] = 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# channel labelencoder\n",
    "le = preprocessing.LabelEncoder()\n",
    "tmp = le.fit_transform(policy.Distribution_Channel)\n",
    "tmp = pd.DataFrame(tmp)\n",
    "tmp.columns = ['Channel']\n",
    "policy = pd.concat([policy, pd.DataFrame(tmp)],axis=1)\n",
    "tmp = policy.groupby('Policy_Number', as_index=False).Channel.first()\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# import car labelencoder\n",
    "le = preprocessing.LabelEncoder()\n",
    "tmp = le.fit_transform(policy.Imported_or_Domestic_Car)\n",
    "tmp = pd.DataFrame(tmp)\n",
    "tmp.columns = ['Imported_cars']\n",
    "policy = pd.concat([policy, pd.DataFrame(tmp)],axis=1)\n",
    "tmp = policy.groupby('Policy_Number', as_index=False).Imported_cars.first()\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Model1\n",
    "le = preprocessing.LabelEncoder()\n",
    "tmp = le.fit_transform(policy.Vehicle_Make_and_Model1)\n",
    "tmp = pd.DataFrame(tmp)\n",
    "tmp.columns = ['Model1']\n",
    "policy = pd.concat([policy, pd.DataFrame(tmp)],axis=1)\n",
    "tmp = policy.groupby('Policy_Number', as_index=False).Model1.first()\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Model2\n",
    "le = preprocessing.LabelEncoder()\n",
    "tmp = le.fit_transform(policy.Vehicle_Make_and_Model2)\n",
    "tmp = pd.DataFrame(tmp)\n",
    "tmp.columns = ['Model2']\n",
    "policy = pd.concat([policy, pd.DataFrame(tmp)],axis=1)\n",
    "tmp = policy.groupby('Policy_Number', as_index=False).Model2.first()\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 154,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Coding_of_Vehicle_Branding_&_Type\n",
    "le = preprocessing.LabelEncoder()\n",
    "tmp = le.fit_transform(policy[\"Coding_of_Vehicle_Branding_&_Type\"])\n",
    "tmp = pd.DataFrame(tmp)\n",
    "tmp.columns = ['Type']\n",
    "policy = pd.concat([policy, pd.DataFrame(tmp)],axis=1)\n",
    "tmp = policy.groupby('Policy_Number', as_index=False).Type.first()\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# iply labelencoder\n",
    "le = preprocessing.LabelEncoder()\n",
    "tmp = le.fit_transform(policy.iply_area)\n",
    "tmp = pd.DataFrame(tmp)\n",
    "tmp.columns = ['iply']\n",
    "policy = pd.concat([policy, pd.DataFrame(tmp)],axis=1)\n",
    "tmp = policy.groupby('Policy_Number', as_index=False).iply.first()\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# zip labelencoder\n",
    "le = preprocessing.LabelEncoder()\n",
    "tmp = le.fit_transform(policy.aassured_zip)\n",
    "tmp = pd.DataFrame(tmp)\n",
    "tmp.columns = ['zip']\n",
    "policy = pd.concat([policy, pd.DataFrame(tmp)],axis=1)\n",
    "tmp = policy.groupby('Policy_Number', as_index=False).zip.first()\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# prior labelencoder\n",
    "le = preprocessing.LabelEncoder()\n",
    "tmp = le.fit_transform(policy.aassured_zip)\n",
    "tmp = pd.DataFrame(tmp)\n",
    "tmp.columns = ['zip']\n",
    "policy = pd.concat([policy, pd.DataFrame(tmp)],axis=1)\n",
    "tmp = policy.groupby('Policy_Number', as_index=False).zip.first()\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Model1 * Model2\n",
    "tr[\"Model\"] = tr.Model1 * tr.Model2\n",
    "te[\"Model\"] = te.Model1 * te.Model2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 156,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Model * Type\n",
    "tr['model_type'] = tr.Model * tr.Type\n",
    "te['model_type'] = te.Model * te.Type"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "tmp = policy.groupby([\"Policy_Number\"],as_index=False)[[\"model1_haser1\",\"model1_haser2\",\"model1_haser3\",\"model1_haser4\",\"model1_haser5\"]].first()\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "tmp = policy.groupby([\"Policy_Number\"],as_index=False)[[\"model2_haser1\",\"model2_haser2\",\"model2_haser3\",\"model2_haser4\",\"model2_haser5\",\n",
    "              \"model2_haser6\",\"model2_haser7\",\"model2_haser8\",\"model2_haser9\",\"model2_haser10\"]].first()\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "tmp = policy.groupby([\"Policy_Number\"],as_index=False)[[\"Branding_haser1\",\"Branding_haser2\",\"Branding_haser3\",\"Branding_haser4\",\"Branding_haser5\",\n",
    "              \"Branding_haser6\",\"Branding_haser7\",\"Branding_haser8\",\"Branding_haser9\",\"Branding_haser10\"]].first()\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "tmp = policy.groupby([\"Policy_Number\"],as_index=False)[[\"iply_haser1\",\"iply_haser2\",\"iply_haser3\",\"iply_haser4\",\"iply_haser5\"]].first()\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "tmp = policy.groupby([\"Policy_Number\"],as_index=False)[[\"zip_haser1\",\"zip_haser2\",\"zip_haser3\",\"zip_haser4\",\"zip_haser5\",\n",
    "              \"zip_haser6\",\"zip_haser7\",\"zip_haser8\",\"zip_haser9\",\"zip_haser10\"]].first()\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 險種代碼 x Premium\n",
    "tmp = policy.pivot_table(index= 'Policy_Number', columns= 'Insurance_Coverage', values= 'Premium')\n",
    "tmp = tmp.reset_index(drop=False)\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number') "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 202,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 險種代碼 x Insured_Amount\n",
    "tmp = policy.pivot_table(index= 'Policy_Number', columns= 'Insurance_Coverage', values= 'Insured_Amount')\n",
    "tmp.columns = [ 'Insured_Amount_'+ str(i) for i in tmp.columns.values]\n",
    "tmp = tmp.reset_index(drop=False)\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number') "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 368,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 險種代碼 x Insured_Amount1\n",
    "tmp = policy.pivot_table(index= 'Policy_Number', columns= 'Insurance_Coverage', values= 'Insured_Amount1')\n",
    "tmp.columns = [ 'Insured_Amount1_'+ str(i) for i in tmp.columns.values]\n",
    "tmp = tmp.reset_index(drop=False)\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number') "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 369,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 險種代碼 x Insured_Amount2\n",
    "tmp = policy.pivot_table(index= 'Policy_Number', columns= 'Insurance_Coverage', values= 'Insured_Amount2')\n",
    "tmp.columns = [ 'Insured_Amount2_'+ str(i) for i in tmp.columns.values]\n",
    "tmp = tmp.reset_index(drop=False)\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number') "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 370,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 險種代碼 x Insured_Amount3\n",
    "tmp = policy.pivot_table(index= 'Policy_Number', columns= 'Insurance_Coverage', values= 'Insured_Amount3')\n",
    "tmp.columns = [ 'Insured_Amount3_'+ str(i) for i in tmp.columns.values]\n",
    "tmp = tmp.reset_index(drop=False)\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number') "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 371,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 險種代碼 x Coverage_Deductible_if_applied\n",
    "tmp = policy.pivot_table(index= 'Policy_Number', columns= 'Insurance_Coverage', values= \"Coverage_Deductible_if_applied\")\n",
    "tmp.columns = [ 'IDeductible_'+ str(i) for i in tmp.columns.values]\n",
    "tmp = tmp.reset_index(drop=False)\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number') "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "7405"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(np.unique(policy[\"Coding_of_Vehicle_Branding_&_Type\"]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "210763\n",
      "140510\n"
     ]
    }
   ],
   "source": [
    "print len(tr)\n",
    "print len(te)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "_all = pd.DataFrame([])\n",
    "skf = StratifiedKFold(n_splits=3, random_state=2018)\n",
    "skf.get_n_splits(tr['Policy_Number'].values, tr['Next_Premium'].values)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def get_feature_coverage_Next_Premium_ratio(df_by_file):\n",
    "\t##\n",
    "\tdf_by_file_Insurance_Coverage = df_by_file[~df_by_file[['Insurance_Coverage']].duplicated()]\n",
    "\tdf_by_file_Insurance_Coverage = df_by_file_Insurance_Coverage.dropna(subset=['Next_Premium'])\n",
    "\tInsurance_Coverage_ratio_mean = df_by_file_Insurance_Coverage.Next_Premium.mean()\n",
    "\tInsurance_Coverage_ratio_std = df_by_file_Insurance_Coverage.Next_Premium.std()\n",
    "\tInsurance_Coverage_ratio_0, Insurance_Coverage_ratio_10, Insurance_Coverage_ratio_20, Insurance_Coverage_ratio_30, Insurance_Coverage_ratio_40, Insurance_Coverage_ratio_50, Insurance_Coverage_ratio_60, Insurance_Coverage_ratio_70, Insurance_Coverage_ratio_80, Insurance_Coverage_ratio_90, Insurance_Coverage_ratio_100 =  df_by_file_Insurance_Coverage['Next_Premium'].quantile(0),\\\n",
    "\t    df_by_file_Insurance_Coverage['Next_Premium'].quantile(0.1),\\\n",
    "\t    df_by_file_Insurance_Coverage['Next_Premium'].quantile(0.2), \\\n",
    "\t    df_by_file_Insurance_Coverage['Next_Premium'].quantile(0.3),\\\n",
    "\t    df_by_file_Insurance_Coverage['Next_Premium'].quantile(0.4), \\\n",
    "\t    df_by_file_Insurance_Coverage['Next_Premium'].quantile(0.5), \\\n",
    "\t    df_by_file_Insurance_Coverage['Next_Premium'].quantile(0.6), \\\n",
    "\t    df_by_file_Insurance_Coverage['Next_Premium'].quantile(0.8),\\\n",
    "\t    df_by_file_Insurance_Coverage['Next_Premium'].quantile(0.8),\\\n",
    "\t    df_by_file_Insurance_Coverage['Next_Premium'].quantile(0.9),\\\n",
    "\t    df_by_file_Insurance_Coverage['Next_Premium'].quantile(1)\n",
    "\tInsurance_Coverage_ratio_range = Insurance_Coverage_ratio_100 - Insurance_Coverage_ratio_0\n",
    "\t##\n",
    "\n",
    "\treturn pd.Series((\n",
    "\t\tInsurance_Coverage_ratio_mean, Insurance_Coverage_ratio_std, Insurance_Coverage_ratio_range, \n",
    "\t\tInsurance_Coverage_ratio_0, Insurance_Coverage_ratio_10, Insurance_Coverage_ratio_20, Insurance_Coverage_ratio_30, Insurance_Coverage_ratio_40, Insurance_Coverage_ratio_50, Insurance_Coverage_ratio_60, Insurance_Coverage_ratio_70, Insurance_Coverage_ratio_80, Insurance_Coverage_ratio_90, Insurance_Coverage_ratio_100),\n",
    "\t\tindex=[\n",
    "\t\t'Insurance_Coverage_ratio_mean', 'Insurance_Coverage_ratio_std', 'Insurance_Coverage_ratio_range',\n",
    "\t\t'Insurance_Coverage_ratio_0', 'Insurance_Coverage_ratio_10', 'Insurance_Coverage_ratio_20', 'Insurance_Coverage_ratio_30', 'Insurance_Coverage_ratio_40', 'Insurance_Coverage_ratio_50', 'Insurance_Coverage_ratio_60', 'Insurance_Coverage_ratio_70', 'Insurance_Coverage_ratio_80', 'Insurance_Coverage_ratio_90', 'Insurance_Coverage_ratio_100'\n",
    "\t\t])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "for train_index, test_index in skf.split(tr['Policy_Number'].values, tr['Next_Premium'].values):\n",
    "\tprint('qq')\n",
    "\ttrain_tmp = tr.iloc[train_index]\n",
    "\ttmp = pd.merge(train_tmp, policy, on='Policy_Number', how='left')\n",
    "\ttmp_mean = tmp.groupby('Insurance_Coverage')['Next_Premium'].mean()\n",
    "\ttmp_mean = pd.DataFrame(tmp_mean)\n",
    "\ttmp_mean['Insurance_Coverage'] = tmp_mean.index\n",
    "\n",
    "\ttest_tmp = tr.iloc[test_index]\n",
    "\ttest_tmp = test_tmp.rename(columns={'Next_Premium':'qq'})\n",
    "\ttest_tmp_query_log = pd.merge(test_tmp, policy, on='Policy_Number',how='left')\n",
    "\ttest_tmp_query_log = pd.merge(test_tmp_query_log, tmp_mean, on='Insurance_Coverage',how='left')\n",
    "\ta = test_tmp_query_log.groupby('Policy_Number').apply(get_feature_coverage_Next_Premium_ratio)\n",
    "\t_all = _all.append(a)\n",
    "\n",
    "_all.to_csv('cv_Insurance_Coverage_ratio.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#train_tmp = pd.read_csv('/Users/charlie/Desktop/TrendMicro/training-set.csv', names=['FileID','res'])\n",
    "#test_tmp = pd.read_csv('/Users/charlie/Desktop/TrendMicro/testing-set.csv', names=['FileID','res'])\n",
    "tmp = pd.merge(tr, policy, on='Policy_Number', how='left')\n",
    "tmp_mean = tmp.groupby('Insurance_Coverage')['Next_Premium'].mean()\n",
    "tmp_mean = pd.DataFrame(tmp_mean)\n",
    "tmp_mean['Insurance_Coverage'] = tmp_mean.index\n",
    "\n",
    "test_tmp = te.rename(columns={'Next_Premium':'qq'})\n",
    "test_tmp_query_log = pd.merge(test_tmp, policy, on='Policy_Number',how='left')\n",
    "test_tmp_query_log = pd.merge(test_tmp_query_log, tmp_mean, on='Insurance_Coverage',how='left')\n",
    "a = test_tmp_query_log.groupby('Policy_Number').apply(get_feature_coverage_Next_Premium_ratio)\n",
    "a.to_csv('cv_Insurance_Coverage_ratio_test.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "cv_Insurance_Coverage_ratio = pd.read_csv('/Users/charlie/cv_Insurance_Coverage_ratio.csv')\n",
    "cv_Insurance_Coverage_ratio_test = pd.read_csv('/Users/charlie/cv_Insurance_Coverage_ratio_test.csv')\n",
    "tr = pd.merge(tr, cv_Insurance_Coverage_ratio, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, cv_Insurance_Coverage_ratio_test, how = 'left', on = 'Policy_Number') "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "tr = tr.drop('Premium_Vehicle1',1)\n",
    "te = te.drop('Premium_Vehicle1',1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# method revised\n",
    "_all = pd.DataFrame([])\n",
    "skf = StratifiedKFold(n_splits=3, random_state=2018)\n",
    "skf.get_n_splits(tr['Policy_Number'].values, tr['Next_Premium'].values)\n",
    "for train_index, test_index in skf.split(tr['Policy_Number'].values, tr['Next_Premium'].values):\n",
    "    print('qq')\n",
    "    train_tmp = tr[['Policy_Number']].iloc[train_index]\n",
    "    tmp = pd.merge(train_tmp, policy[['Policy_Number','Vehicle_Make_and_Model1','Premium']], on='Policy_Number', how='left')\n",
    "    tmp_mean = tmp.groupby('Vehicle_Make_and_Model1')['Premium'].mean()\n",
    "    tmp_mean = pd.DataFrame(tmp_mean)\n",
    "    tmp_mean['Vehicle_Make_and_Model1'] = tmp_mean.index\n",
    "\n",
    "    test_tmp = tr[['Policy_Number']].iloc[test_index]\n",
    "    test_tmp_query_log = pd.merge(test_tmp, policy[['Policy_Number','Vehicle_Make_and_Model1','Premium']], on='Policy_Number',how='left')\n",
    "    test_tmp_query_log = test_tmp_query_log.rename(columns={'Premium':'qq'})\n",
    "    test_tmp_query_log = pd.merge(test_tmp_query_log, tmp_mean, on='Vehicle_Make_and_Model1',how='left')\n",
    "    test_tmp_query_log = test_tmp_query_log[~test_tmp_query_log.duplicated()]\n",
    "    test_tmp_query_log = test_tmp_query_log.groupby('Policy_Number', as_index=False).Premium.mean()\n",
    "    _all = _all.append(test_tmp_query_log)\n",
    "    \n",
    "_all.columns = ['Policy_Number','Premium_Vehicle1']\n",
    "\n",
    "tmp = pd.merge(tr[['Policy_Number']], policy[['Policy_Number','Vehicle_Make_and_Model1','Premium']], on='Policy_Number', how='left')\n",
    "tmp_mean = tmp.groupby('Vehicle_Make_and_Model1')['Premium'].mean()\n",
    "tmp_mean = pd.DataFrame(tmp_mean)\n",
    "tmp_mean['Vehicle_Make_and_Model1'] = tmp_mean.index\n",
    "\n",
    "test_tmp = te[['Policy_Number']]\n",
    "test_tmp_query_log = pd.merge(test_tmp, policy[['Policy_Number','Vehicle_Make_and_Model1','Premium']], on='Policy_Number',how='left')\n",
    "test_tmp_query_log = test_tmp_query_log.rename(columns={'Premium':'qq'})\n",
    "a1 = pd.merge(test_tmp_query_log, tmp_mean, on='Vehicle_Make_and_Model1',how='left')\n",
    "a1 = a1[~a1.duplicated()]\n",
    "a2 = a1.groupby('Policy_Number', as_index=False).Premium.mean()\n",
    "a2.columns = ['Policy_Number','Premium_Vehicle1']\n",
    "\n",
    "#tr = pd.merge(tr, _all, how = 'left', on = 'Policy_Number')\n",
    "#te = pd.merge(te, a2, how = 'left', on = 'Policy_Number') "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "print len(_all)\n",
    "print len(a2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# method revised\n",
    "_all = pd.DataFrame([])\n",
    "skf = StratifiedKFold(n_splits=3, random_state=2018)\n",
    "skf.get_n_splits(tr['Policy_Number'].values, tr['Next_Premium'].values)\n",
    "for train_index, test_index in skf.split(tr['Policy_Number'].values, tr['Next_Premium'].values):\n",
    "    print('qq')\n",
    "    train_tmp = tr[['Policy_Number','Next_Premium']].iloc[train_index]\n",
    "    tmp = pd.merge(train_tmp, policy[['Policy_Number','Vehicle_Make_and_Model1']], on='Policy_Number', how='left')\n",
    "    tmp_mean = tmp.groupby('Vehicle_Make_and_Model1')['Next_Premium'].mean()\n",
    "    tmp_mean = pd.DataFrame(tmp_mean)\n",
    "    tmp_mean['Vehicle_Make_and_Model1'] = tmp_mean.index\n",
    "\n",
    "    test_tmp = tr[['Policy_Number','Next_Premium']].iloc[test_index]\n",
    "    test_tmp_query_log = pd.merge(test_tmp, policy[['Policy_Number','Vehicle_Make_and_Model1']], on='Policy_Number',how='left')\n",
    "    test_tmp_query_log = test_tmp_query_log.rename(columns={'Next_Premium':'qq'})\n",
    "    test_tmp_query_log = pd.merge(test_tmp_query_log, tmp_mean, on='Vehicle_Make_and_Model1',how='left')\n",
    "    test_tmp_query_log = test_tmp_query_log[~test_tmp_query_log.duplicated()]\n",
    "    test_tmp_query_log = test_tmp_query_log.groupby('Policy_Number', as_index=False)['Next_Premium'].mean()\n",
    "    _all = _all.append(test_tmp_query_log)\n",
    "    \n",
    "_all.columns = ['Policy_Number','Next_Premium_Vehicle1']\n",
    "\n",
    "tmp = pd.merge(tr[['Policy_Number','Next_Premium']], policy[['Policy_Number','Vehicle_Make_and_Model1']], on='Policy_Number', how='left')\n",
    "tmp_mean = tmp.groupby('Vehicle_Make_and_Model1')['Next_Premium'].mean()\n",
    "tmp_mean = pd.DataFrame(tmp_mean)\n",
    "tmp_mean['Vehicle_Make_and_Model1'] = tmp_mean.index\n",
    "\n",
    "test_tmp = te[['Policy_Number','Next_Premium']]\n",
    "test_tmp_query_log = pd.merge(test_tmp, policy[['Policy_Number','Vehicle_Make_and_Model1']], on='Policy_Number',how='left')\n",
    "test_tmp_query_log = test_tmp_query_log.rename(columns={'Next_Premium':'qq'})\n",
    "a1 = pd.merge(test_tmp_query_log, tmp_mean, on='Vehicle_Make_and_Model1',how='left')\n",
    "a1 = a1[~a1.duplicated()]\n",
    "a2 = a1.groupby('Policy_Number', as_index=False)['Next_Premium'].mean()\n",
    "a2.columns = ['Policy_Number','Next_Premium_Vehicle1']\n",
    "print len(_all)\n",
    "print len(a2)\n",
    "#tr = pd.merge(tr, _all, how = 'left', on = 'Policy_Number')\n",
    "#te = pd.merge(te, a2, how = 'left', on = 'Policy_Number') "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 134,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# age X d_age\n",
    "policy[\"Age_d_age\"] = policy[\"age\"] * policy.d_age\n",
    "tmp = policy.groupby('Policy_Number', as_index=False)['Age_d_age'].first()\n",
    "tmp.columns = ['Policy_Number','Age_d_age']\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "policy['prior'] = np.where(policy['Prior_Policy_Number'].isnull(),0,1)\n",
    "tmp = policy.groupby('Policy_Number', as_index=0)['prior'].first()\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 265,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# age + d_age\n",
    "policy[\"Age_add_d_age\"] = policy[\"age\"] * policy.d_age\n",
    "tmp = policy.groupby('Policy_Number', as_index=False)['Age_add_d_age'].first()\n",
    "tmp.columns = ['Policy_Number','Age_add_d_age']\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 275,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 被保險戶特徵\n",
    "tmp = policy.groupby(\"Insured's_ID\", as_index=0).agg({\"Policy_Number\":['nunique','count'],\"Vehicle_identifier\":'nunique'})\n",
    "tmp.columns = [\"Insured's_ID\",\"id_nunique_vehicle\",'id_nunique_policy','id_count_policy']\n",
    "policy = pd.merge(policy, tmp, how = 'left', on = \"Insured's_ID\")\n",
    "tmp = policy.groupby('Policy_Number', as_index=False)[[\"id_nunique_vehicle\",'id_nunique_policy','id_count_policy']].first()\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# premium X age\n",
    "policy['premium_age'] = policy.Premium * policy.age\n",
    "policy['premium_d_age'] = policy.Premium * policy.d_age\n",
    "tmp = policy.groupby('Policy_Number', as_index=False).agg({'premium_age':['mean','sum','std','median','min','max']})\n",
    "tmp.columns = ['Policy_Number','mean_premium_age','sum_premium_age','std_premium_age','median_premium_age',\n",
    "              'min_premium_age','max_premium_age']\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')\n",
    "\n",
    "tmp = policy.groupby('Policy_Number', as_index=False).agg({'premium_d_age':['mean','sum']})\n",
    "tmp.columns = ['Policy_Number','mean_premium_d_age','sum_premium_d_age']\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 136,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Engine X age\n",
    "policy[\"Engine_repl\"] = policy[\"Engine_Displacement_(Cubic_Centimeter)\"] * policy.Replacement_cost_of_insured_vehicle\n",
    "tmp = policy.groupby('Policy_Number', as_index=False)['Engine_repl'].first()\n",
    "tmp.columns = ['Policy_Number','Engine_repl']\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Engine X age\n",
    "policy[\"Engine_Age\"] = policy[\"Engine_Displacement_(Cubic_Centimeter)\"] * policy.age\n",
    "tmp = policy.groupby('Policy_Number', as_index=False)['Engine_Age'].first()\n",
    "tmp.columns = ['Policy_Number','Engine_Age']\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Engine X d_age\n",
    "policy[\"Engine_d_Age\"] = policy[\"Engine_Displacement_(Cubic_Centimeter)\"] * policy.d_age\n",
    "tmp = policy.groupby('Policy_Number', as_index=False)['Engine_d_Age'].first()\n",
    "tmp.columns = ['Policy_Number','Engine_d_Age']\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Engine X Premium\n",
    "policy[\"Engine_Premium\"] = policy[\"Engine_Displacement_(Cubic_Centimeter)\"] * policy.Premium\n",
    "tmp = policy.groupby('Policy_Number', as_index=False).agg({'Engine_Premium':['mean','sum','std','median','min','max']})\n",
    "tmp.columns = ['Policy_Number','mean_Engine_Premium','sum_Engine_Premium','std_Engine_Premium','median_Engine_Premium',\n",
    "              'min_Engine_Premium','max_Engine_Premium']\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# car age X age\n",
    "policy['Manafactured_age'] = policy.Manafactured_Year_and_Month * policy.age\n",
    "policy['Manafactured_d_age'] = policy.Manafactured_Year_and_Month * policy.d_age\n",
    "tmp = policy.groupby('Policy_Number', as_index=False)['Manafactured_age'].first()\n",
    "tmp.columns = ['Policy_Number','Manafactured_age']\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')\n",
    "\n",
    "tmp = policy.groupby('Policy_Number', as_index=False)['Manafactured_d_age'].first()\n",
    "tmp.columns = ['Policy_Number','Manafactured_d_age']\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')\n",
    "\n",
    "tr['Manafactured_plus_age'] = tr.Manafactured_age + tr.age\n",
    "te['Manafactured_plus_age'] = te.Manafactured_age + te.age"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# div for replace X premium\n",
    "policy['div_repl_premium'] = policy.Replacement_cost_of_insured_vehicle / policy.Premium\n",
    "tmp = policy.groupby('Policy_Number', as_index=False).agg({'div_repl_premium':['mean','sum','std','median','min','max']})\n",
    "tmp.columns = ['Policy_Number','mean_div_Repl_premium','sum_div_Repl_premium','std_div_Repl_premium','median_div_Repl_premium',\n",
    "              'min_div_Repl_premium','max_div_Repl_premium']\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# multiply for replace X age\n",
    "policy['multiply_repl_age'] = policy.Replacement_cost_of_insured_vehicle * policy.age\n",
    "tmp = policy.groupby('Policy_Number', as_index=False)['multiply_repl_age'].first()\n",
    "tmp.columns = ['Policy_Number','repl_age']\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')\n",
    "\n",
    "policy['multiply_repl_d_age'] = policy.Replacement_cost_of_insured_vehicle * policy.d_age\n",
    "tmp = policy.groupby('Policy_Number', as_index=False)['multiply_repl_d_age'].first()\n",
    "tmp.columns = ['Policy_Number','repl_d_age']\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# multiply for replace X premium\n",
    "policy['multiply_repl_premium'] = policy.Replacement_cost_of_insured_vehicle * policy.Premium\n",
    "tmp = policy.groupby('Policy_Number', as_index=False).agg({'multiply_repl_premium':['mean','sum','std','median','min','max']})\n",
    "tmp.columns = ['Policy_Number','mean_multiply_repl_premium','sum_multiply_repl_premium','std_multiply_repl_premium','median_multiply_repl_premium',\n",
    "              'min_multiply_repl_premium','max_multiply_repl_premium']\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Insured_Aount X age\n",
    "policy['div_repl_premium'] = policy.Replacement_cost_of_insured_vehicle / policy.Premium\n",
    "tmp = policy.groupby('Policy_Number', as_index=False).agg({'div_repl_premium':['mean','sum','std','median','min','max']})\n",
    "tmp.columns = ['Policy_Number','mean_div_Repl_premium','sum_div_Repl_premium','std_div_Repl_premium','median_div_Repl_premium',\n",
    "              'min_div_Repl_premium','max_div_Repl_premium']\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# coverage X premium\n",
    "tmp = policy.groupby(['Policy_Number','Main_Insurance_Coverage_Group'], as_index= 0).agg({'Premium':['mean','sum']})\n",
    "tmp.columns = [\"Policy_Number\",\"Main_Insurance_Coverage_Group\",\"mean_coverage_premium\",\"sum_coverage_premium\"]\n",
    "tmp = tmp.pivot_table(index= 'Policy_Number', columns= 'Main_Insurance_Coverage_Group', values= [\"mean_coverage_premium\",\"sum_coverage_premium\"])\n",
    "tmp = tmp.reset_index(drop=False)\n",
    "tmp.columns = ['Policy_Number','mean_coverage_premium_0','mean_coverage_premium_1','mean_coverage_premium_2',\n",
    "              'sum_coverage_premium_0','sum_coverage_premium_1','sum_coverage_premium_2']\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number') "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# coverage X insured_amount\n",
    "tmp = policy.groupby(['Policy_Number','Main_Insurance_Coverage_Group'], as_index= 0).agg({'Insured_Amount':['mean','sum']})\n",
    "tmp.columns = [\"Policy_Number\",\"Main_Insurance_Coverage_Group\",\"mean_cov_Insured_Amount\",\"sum_cov_Insured_Amount\"]\n",
    "tmp = tmp.pivot_table(index= 'Policy_Number', columns= 'Main_Insurance_Coverage_Group', \n",
    "                      values= [\"mean_cov_Insured_Amount\",\"sum_cov_Insured_Amount\"])\n",
    "tmp = tmp.reset_index(drop=False)\n",
    "tmp.columns = ['Policy_Number','mean_coverage_insured_amount_0','mean_insured_amount_1','mean_insured_amount_2',\n",
    "                           'sum_insured_amount_0','sum_insured_amount_1','sum_insured_amount_2']\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 165,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# coverage X insured_amount1\n",
    "tmp = policy.groupby(['Policy_Number','Main_Insurance_Coverage_Group'], as_index= 0).agg({'Insured_Amount1':['mean','sum']})\n",
    "tmp.columns = [\"Policy_Number\",\"Main_Insurance_Coverage_Group\",\"mean_cov_Insured_Amount1\",\"sum_cov_Insured_Amount1\"]\n",
    "tmp = tmp.pivot_table(index= 'Policy_Number', columns= 'Main_Insurance_Coverage_Group', \n",
    "                      values= [\"mean_cov_Insured_Amount1\",\"sum_cov_Insured_Amount1\"])\n",
    "tmp = tmp.reset_index(drop=False)\n",
    "tmp.columns = ['Policy_Number','mean_coverage_insured_amount1_0','mean_insured_amount1_1','mean_insured_amount1_2',\n",
    "                           'sum_insured_amount1_0','sum_insured_amount1_1','sum_insured_amount1_2']\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 166,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# coverage X insured_amount2\n",
    "tmp = policy.groupby(['Policy_Number','Main_Insurance_Coverage_Group'], as_index= 0).agg({'Insured_Amount2':['mean','sum']})\n",
    "tmp.columns = [\"Policy_Number\",\"Main_Insurance_Coverage_Group\",\"mean_cov_Insured_Amount2\",\"sum_cov_Insured_Amount2\"]\n",
    "tmp = tmp.pivot_table(index= 'Policy_Number', columns= 'Main_Insurance_Coverage_Group', \n",
    "                      values= [\"mean_cov_Insured_Amount2\",\"sum_cov_Insured_Amount2\"])\n",
    "tmp = tmp.reset_index(drop=False)\n",
    "tmp.columns = ['Policy_Number','mean_coverage_insured_amount2_0','mean_insured_amount2_1','mean_insured_amount2_2',\n",
    "                           'sum_insured_amount2_0','sum_insured_amount2_1','sum_insured_amount2_2']\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 167,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# coverage X insured_amount3\n",
    "tmp = policy.groupby(['Policy_Number','Main_Insurance_Coverage_Group'], as_index= 0).agg({'Insured_Amount3':['mean','sum']})\n",
    "tmp.columns = [\"Policy_Number\",\"Main_Insurance_Coverage_Group\",\"mean_cov_Insured_Amount3\",\"sum_cov_Insured_Amount3\"]\n",
    "tmp = tmp.pivot_table(index= 'Policy_Number', columns= 'Main_Insurance_Coverage_Group', \n",
    "                      values= [\"mean_cov_Insured_Amount3\",\"sum_cov_Insured_Amount3\"])\n",
    "tmp = tmp.reset_index(drop=False)\n",
    "tmp.columns = ['Policy_Number','mean_coverage_insured_amount3_0','mean_insured_amount3_1','mean_insured_amount3_2',\n",
    "                           'sum_insured_amount3_0','sum_insured_amount3_1','sum_insured_amount3_2']\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 176,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# lia_class * age\n",
    "policy[\"lia_class_Age\"] = policy.lia_class * policy.age\n",
    "tmp = policy.groupby('Policy_Number', as_index=False)['lia_class_Age'].first()\n",
    "tmp.columns = ['Policy_Number','lia_class_Age']\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 177,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# plia_acc * age\n",
    "policy[\"plia_acc_Age\"] = policy.plia_acc * policy.age\n",
    "tmp = policy.groupby('Policy_Number', as_index=False)['plia_acc_Age'].first()\n",
    "tmp.columns = ['Policy_Number','plia_acc_Age']\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 178,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# pdmg_acc * age\n",
    "policy[\"pdmg_acc_Age\"] = policy.pdmg_acc * policy.age\n",
    "tmp = policy.groupby('Policy_Number', as_index=False)['pdmg_acc_Age'].first()\n",
    "tmp.columns = ['Policy_Number','pdmg_acc_Age']\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 283,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# qpt * Engine_Displacement_(Cubic_Centimeter)\n",
    "policy[\"qpt_engine\"] = policy.qpt * policy[\"Engine_Displacement_(Cubic_Centimeter)\"]\n",
    "tmp = policy.groupby('Policy_Number', as_index=False)['qpt_engine'].first()\n",
    "tmp.columns = ['Policy_Number','qpt_engine']\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 284,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# qpt * Replacement_cost_of_insured_vehicle\n",
    "policy[\"qpt_repl\"] = policy.qpt * policy[\"Replacement_cost_of_insured_vehicle\"]\n",
    "tmp = policy.groupby('Policy_Number', as_index=False)['qpt_repl'].first()\n",
    "tmp.columns = ['Policy_Number','qpt_repl']\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 285,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# qpt * car_age\n",
    "policy[\"qpt_car_age\"] = policy.qpt * policy.car_age\n",
    "tmp = policy.groupby('Policy_Number', as_index=False)['qpt_car_age'].first()\n",
    "tmp.columns = ['Policy_Number','qpt_car_age']\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "policy['car_age'] = 2016 - policy.Manafactured_Year_and_Month"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# repl X manu\n",
    "policy['neg_repl_car_age'] = policy.Replacement_cost_of_insured_vehicle * -policy.car_age\n",
    "tmp = policy.groupby('Policy_Number', as_index=False)[\"neg_repl_car_age\"].first()\n",
    "#tmp.columns = ['Policy_Number','repl_car_age']\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "policy[[\"Policy_Number\",\"Main_Insurance_Coverage_Group\",\"Insured_Amount1\",\"Insured_Amount2\",\"Insured_Amount3\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "tr = tr.drop(['mean_premium_age',\n",
    "       'sum_premium_age', 'mean_premium_d_age', 'sum_premium_d_age'],1)\n",
    "te = te.drop(['mean_premium_age',\n",
    "       'sum_premium_age', 'mean_premium_d_age', 'sum_premium_d_age'],1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 231,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 被保險人年齡分組\n",
    "tr.loc[tr.age < 20, 'age_class'] = 0\n",
    "tr.loc[(tr.age >= 20)&( tr.age < 25), 'age_class'] = 1\n",
    "tr.loc[(tr.age >= 25)&( tr.age < 30), 'age_class'] = 2\n",
    "tr.loc[(tr.age >= 30)&( tr.age < 60), 'age_class'] = 3\n",
    "tr.loc[(tr.age >= 60)&( tr.age < 70), 'age_class'] = 4\n",
    "tr.loc[tr.age >= 70, 'age_class'] = 5\n",
    "\n",
    "te.loc[te.age < 20, 'age_class'] = 0\n",
    "te.loc[(te.age >= 20)&( te.age < 25), 'age_class'] = 1\n",
    "te.loc[(te.age >= 25)&( te.age < 30), 'age_class'] = 2\n",
    "te.loc[(te.age >= 30)&( te.age < 60), 'age_class'] = 3\n",
    "te.loc[(te.age >= 60)&( te.age < 70), 'age_class'] = 4\n",
    "te.loc[te.age >= 70, 'age_class'] = 5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 252,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "tmp = policy.groupby('Policy_Number', as_index=0)['d_age'].first()\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 253,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 要保人年齡分組\n",
    "tr.loc[tr.d_age < 20, 'd_age_class'] = 0\n",
    "tr.loc[(tr.d_age >= 20)&( tr.d_age < 25), 'd_age_class'] = 1\n",
    "tr.loc[(tr.d_age >= 25)&( tr.d_age < 30), 'd_age_class'] = 2\n",
    "tr.loc[(tr.d_age >= 30)&( tr.d_age < 60), 'd_age_class'] = 3\n",
    "tr.loc[(tr.d_age >= 60)&( tr.d_age < 70), 'd_age_class'] = 4\n",
    "tr.loc[tr.d_age > 70, 'd_age_class'] = 5\n",
    "\n",
    "te.loc[te.d_age < 20, 'd_age_class'] = 0\n",
    "te.loc[(te.d_age >= 20)&( te.d_age < 25), 'd_age_class'] = 1\n",
    "te.loc[(te.d_age >= 25)&( te.d_age < 30), 'd_age_class'] = 2\n",
    "te.loc[(te.d_age >= 30)&( te.d_age < 60), 'd_age_class'] = 3\n",
    "te.loc[(te.d_age >= 60)&( te.d_age < 70), 'd_age_class'] = 4\n",
    "te.loc[te.d_age > 70, 'd_age_class'] = 5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 337,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# lia X Premium\n",
    "policy['lia_class_Premium'] = policy.lia_class * policy.Premium\n",
    "tmp = policy.groupby('Policy_Number', as_index=False).agg({\"lia_class_Premium\":['mean','sum']})\n",
    "tmp.columns = ['Policy_Number','mean_lia_class_Premium','sum_lia_class_Premium']\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 319,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# plia_acc X Premium\n",
    "policy['plia_acc_Premium'] = policy.plia_acc * policy.Premium\n",
    "tmp = policy.groupby('Policy_Number', as_index=False).agg({\"plia_acc_Premium\":['mean','sum']})\n",
    "tmp.columns = ['Policy_Number','mean_plia_acc_Premium','sum_plia_acc_Premium']\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 320,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# pdmg_acc X Premium\n",
    "policy['pdmg_acc_Premium'] = policy.pdmg_acc * policy.Premium\n",
    "tmp = policy.groupby('Policy_Number', as_index=False).agg({\"pdmg_acc_Premium\":['mean','sum']})\n",
    "tmp.columns = ['Policy_Number','mean_pdmg_acc_Premium','sum_pdmg_acc_Premium']\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 329,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# acc X Premium\n",
    "policy['acc_Premium'] = policy.acc * policy.Premium\n",
    "tmp = policy.groupby('Policy_Number', as_index=False).agg({\"acc_Premium\":['mean','sum']})\n",
    "tmp.columns = ['Policy_Number','mean_acc_Premium','sum_acc_Premium']\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 338,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# lia X Premium\n",
    "policy['lia_Premium'] = policy.lia * policy.Premium\n",
    "tmp = policy.groupby('Policy_Number', as_index=False).agg({\"lia_Premium\":['mean','sum']})\n",
    "tmp.columns = ['Policy_Number','mean_lia_Premium','sum_lia_Premium']\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 360,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# lia X plia X Premium\n",
    "policy['lia_acc_plia_Premium'] = policy.plia_acc * policy.lia_class *policy.Premium\n",
    "tmp = policy.groupby('Policy_Number', as_index=False).agg({\"lia_acc_plia_Premium\":['mean','sum']})\n",
    "tmp.columns = ['Policy_Number','mean_lia_acc_plia_Premium','sum_lia_acc_plia_Premium']\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 361,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# lia X pdmg X Premium\n",
    "policy['lia_acc_pdmg_Premium'] = policy.pdmg_acc * policy.lia_class *policy.Premium\n",
    "tmp = policy.groupby('Policy_Number', as_index=False).agg({\"lia_acc_pdmg_Premium\":['mean','sum']})\n",
    "tmp.columns = ['Policy_Number','mean_lia_acc_pdmg_Premium','sum_lia_acc_pdmg_Premium']\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 362,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# pdmg X plia X Premium\n",
    "policy['plia_pdmg_Premium'] = policy.pdmg_acc * policy.plia_acc *policy.Premium\n",
    "tmp = policy.groupby('Policy_Number', as_index=False).agg({\"plia_pdmg_Premium\":['mean','sum']})\n",
    "tmp.columns = ['Policy_Number','mean_plia_pdmg_Premium','sum_plia_pdmg_Premium']\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 363,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# pdmg X plia X lia X Premium\n",
    "policy['plia_lia_pdmg_Premium'] = policy.pdmg_acc * policy.plia_acc * policy.lia_class * policy.Premium\n",
    "tmp = policy.groupby('Policy_Number', as_index=False).agg({\"plia_lia_pdmg_Premium\":['mean','sum']})\n",
    "tmp.columns = ['Policy_Number','mean_plia_lia_pdmg_Premium','sum_plia_lia_pdmg_Premium']\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Engine X pdmg X Premium\n",
    "policy['engine_pdmg_premium'] = policy[\"Engine_Displacement_(Cubic_Centimeter)\"] * policy.pdmg_acc * policy.Premium\n",
    "tmp = policy.groupby('Policy_Number', as_index=False).agg({\"engine_pdmg_premium\":['mean','sum']})\n",
    "tmp.columns = ['Policy_Number','mean_engine_pdmg_premium','sum_engine_pdmg_premium']\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Engine X plia X Premium\n",
    "policy['engine_plia_premium'] = policy[\"Engine_Displacement_(Cubic_Centimeter)\"] * policy.plia_acc * policy.Premium\n",
    "tmp = policy.groupby('Policy_Number', as_index=False).agg({\"engine_plia_premium\":['mean','sum']})\n",
    "tmp.columns = ['Policy_Number','mean_engine_plia_premium','sum_engine_plia_premium']\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Engine X lia X Premium\n",
    "policy['engine_lia_class_premium'] = policy[\"Engine_Displacement_(Cubic_Centimeter)\"] * policy.lia_class * policy.Premium\n",
    "tmp = policy.groupby('Policy_Number', as_index=False).agg({\"engine_lia_class_premium\":['mean','sum']})\n",
    "tmp.columns = ['Policy_Number','mean_engine_lia_class_premium','sum_engine_lia_class_premium']\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 241,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#policy[['Policy_Number','Coverage_Deductible_if_applied']]\n",
    "# coverage X premium\n",
    "tmp = policy.groupby(['Policy_Number','Main_Insurance_Coverage_Group'], as_index= 0).agg({'Coverage_Deductible_if_applied':['mean','sum']})\n",
    "tmp.columns = [\"Policy_Number\",\"Main_Insurance_Coverage_Group\",\"mean_coverage_Deductible\",\"sum_coverage_Deductible\"]\n",
    "tmp = tmp.pivot_table(index= 'Policy_Number', columns= 'Main_Insurance_Coverage_Group', values= [\"mean_coverage_Deductible\",\n",
    "                                                                                                 \"sum_coverage_Deductible\"])\n",
    "tmp = tmp.reset_index(drop=False)\n",
    "tmp.columns = ['Policy_Number','mean_coverage_Deductible_0','mean_coverage_Deductible_1','mean_coverage_Deductible_2',\n",
    "              'sum_coverage_Deductible_0','sum_coverage_Deductible_1','sum_coverage_Deductible_2']\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number') "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 414,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from sklearn.decomposition import PCA\n",
    "tmp = tr[['Insured_Amount1_00I',\n",
    "       'Insured_Amount1_01A', 'Insured_Amount1_01J', 'Insured_Amount1_02K',\n",
    "       'Insured_Amount1_03L', 'Insured_Amount1_04M', 'Insured_Amount1_05E',\n",
    "       'Insured_Amount1_05N', 'Insured_Amount1_06F', 'Insured_Amount1_07P',\n",
    "       'Insured_Amount1_08H', 'Insured_Amount1_09@', 'Insured_Amount1_09I',\n",
    "       'Insured_Amount1_10A', 'Insured_Amount1_12L', 'Insured_Amount1_14E',\n",
    "       'Insured_Amount1_14N', 'Insured_Amount1_15F', 'Insured_Amount1_15O',\n",
    "       'Insured_Amount1_16G', 'Insured_Amount1_16P', 'Insured_Amount1_18@',\n",
    "       'Insured_Amount1_18I', 'Insured_Amount1_20B', 'Insured_Amount1_20K',\n",
    "       'Insured_Amount1_25G', 'Insured_Amount1_26H', 'Insured_Amount1_27I',\n",
    "       'Insured_Amount1_29B', 'Insured_Amount1_29K', 'Insured_Amount1_32N',\n",
    "       'Insured_Amount1_33F', 'Insured_Amount1_33O', 'Insured_Amount1_34P',\n",
    "       'Insured_Amount1_35H', 'Insured_Amount1_36I', 'Insured_Amount1_37J',\n",
    "       'Insured_Amount1_40M', 'Insured_Amount1_41E', 'Insured_Amount1_41N',\n",
    "       'Insured_Amount1_42F', 'Insured_Amount1_45@', 'Insured_Amount1_46A',\n",
    "       'Insured_Amount1_47B', 'Insured_Amount1_51O', 'Insured_Amount1_55J',\n",
    "       'Insured_Amount1_56B', 'Insured_Amount1_56K', 'Insured_Amount1_57C',\n",
    "       'Insured_Amount1_57L', 'Insured_Amount1_65K', 'Insured_Amount1_66C',\n",
    "       'Insured_Amount1_66L', 'Insured_Amount1_67D', 'Insured_Amount1_68E',\n",
    "       'Insured_Amount1_68N', 'Insured_Amount1_70G', 'Insured_Amount1_70P',\n",
    "       'Insured_Amount1_71H', 'Insured_Amount1_72@', 'Insured_Amount2_00I',\n",
    "       'Insured_Amount2_01A', 'Insured_Amount2_01J', 'Insured_Amount2_02K',\n",
    "       'Insured_Amount2_03L', 'Insured_Amount2_04M', 'Insured_Amount2_05E',\n",
    "       'Insured_Amount2_05N', 'Insured_Amount2_06F', 'Insured_Amount2_07P',\n",
    "       'Insured_Amount2_08H', 'Insured_Amount2_09@', 'Insured_Amount2_09I',\n",
    "       'Insured_Amount2_10A', 'Insured_Amount2_12L', 'Insured_Amount2_14E',\n",
    "       'Insured_Amount2_14N', 'Insured_Amount2_15F', 'Insured_Amount2_15O',\n",
    "       'Insured_Amount2_16G', 'Insured_Amount2_16P', 'Insured_Amount2_18@',\n",
    "       'Insured_Amount2_18I', 'Insured_Amount2_20B', 'Insured_Amount2_20K',\n",
    "       'Insured_Amount2_25G', 'Insured_Amount2_26H', 'Insured_Amount2_27I',\n",
    "       'Insured_Amount2_29B', 'Insured_Amount2_29K', 'Insured_Amount2_32N',\n",
    "       'Insured_Amount2_33F', 'Insured_Amount2_33O', 'Insured_Amount2_34P',\n",
    "       'Insured_Amount2_35H', 'Insured_Amount2_36I', 'Insured_Amount2_37J',\n",
    "       'Insured_Amount2_40M', 'Insured_Amount2_41E', 'Insured_Amount2_41N',\n",
    "       'Insured_Amount2_42F', 'Insured_Amount2_45@', 'Insured_Amount2_46A',\n",
    "       'Insured_Amount2_47B', 'Insured_Amount2_51O', 'Insured_Amount2_55J',\n",
    "       'Insured_Amount2_56B', 'Insured_Amount2_56K', 'Insured_Amount2_57C',\n",
    "       'Insured_Amount2_57L', 'Insured_Amount2_65K', 'Insured_Amount2_66C',\n",
    "       'Insured_Amount2_66L', 'Insured_Amount2_67D', 'Insured_Amount2_68E',\n",
    "       'Insured_Amount2_68N', 'Insured_Amount2_70G', 'Insured_Amount2_70P',\n",
    "       'Insured_Amount2_71H', 'Insured_Amount2_72@', 'Insured_Amount3_00I',\n",
    "       'Insured_Amount3_01A', 'Insured_Amount3_01J', 'Insured_Amount3_02K',\n",
    "       'Insured_Amount3_03L', 'Insured_Amount3_04M', 'Insured_Amount3_05E',\n",
    "       'Insured_Amount3_05N', 'Insured_Amount3_06F', 'Insured_Amount3_07P',\n",
    "       'Insured_Amount3_08H', 'Insured_Amount3_09@', 'Insured_Amount3_09I',\n",
    "       'Insured_Amount3_10A', 'Insured_Amount3_12L', 'Insured_Amount3_14E',\n",
    "       'Insured_Amount3_14N', 'Insured_Amount3_15F', 'Insured_Amount3_15O',\n",
    "       'Insured_Amount3_16G', 'Insured_Amount3_16P', 'Insured_Amount3_18@',\n",
    "       'Insured_Amount3_18I', 'Insured_Amount3_20B', 'Insured_Amount3_20K',\n",
    "       'Insured_Amount3_25G', 'Insured_Amount3_26H', 'Insured_Amount3_27I',\n",
    "       'Insured_Amount3_29B', 'Insured_Amount3_29K', 'Insured_Amount3_32N',\n",
    "       'Insured_Amount3_33F', 'Insured_Amount3_33O', 'Insured_Amount3_34P',\n",
    "       'Insured_Amount3_35H', 'Insured_Amount3_36I', 'Insured_Amount3_37J',\n",
    "       'Insured_Amount3_40M', 'Insured_Amount3_41E', 'Insured_Amount3_41N',\n",
    "       'Insured_Amount3_42F', 'Insured_Amount3_45@', 'Insured_Amount3_46A',\n",
    "       'Insured_Amount3_47B', 'Insured_Amount3_51O', 'Insured_Amount3_55J',\n",
    "       'Insured_Amount3_56B', 'Insured_Amount3_56K', 'Insured_Amount3_57C',\n",
    "       'Insured_Amount3_57L', 'Insured_Amount3_65K', 'Insured_Amount3_66C',\n",
    "       'Insured_Amount3_66L', 'Insured_Amount3_67D', 'Insured_Amount3_68E',\n",
    "       'Insured_Amount3_68N', 'Insured_Amount3_70G', 'Insured_Amount3_70P',\n",
    "       'Insured_Amount3_71H', 'Insured_Amount3_72@', 'IDeductible_00I',\n",
    "       'IDeductible_01A', 'IDeductible_01J', 'IDeductible_02K',\n",
    "       'IDeductible_03L', 'IDeductible_04M', 'IDeductible_05E',\n",
    "       'IDeductible_05N', 'IDeductible_06F', 'IDeductible_07P',\n",
    "       'IDeductible_08H', 'IDeductible_09@', 'IDeductible_09I',\n",
    "       'IDeductible_10A', 'IDeductible_12L', 'IDeductible_14E',\n",
    "       'IDeductible_14N', 'IDeductible_15F', 'IDeductible_15O',\n",
    "       'IDeductible_16G', 'IDeductible_16P', 'IDeductible_18@',\n",
    "       'IDeductible_18I', 'IDeductible_20B', 'IDeductible_20K',\n",
    "       'IDeductible_25G', 'IDeductible_26H', 'IDeductible_27I',\n",
    "       'IDeductible_29B', 'IDeductible_29K', 'IDeductible_32N',\n",
    "       'IDeductible_33F', 'IDeductible_33O', 'IDeductible_34P',\n",
    "       'IDeductible_35H', 'IDeductible_36I', 'IDeductible_37J',\n",
    "       'IDeductible_40M', 'IDeductible_41E', 'IDeductible_41N',\n",
    "       'IDeductible_42F', 'IDeductible_45@', 'IDeductible_46A',\n",
    "       'IDeductible_47B', 'IDeductible_51O', 'IDeductible_55J',\n",
    "       'IDeductible_56B', 'IDeductible_56K', 'IDeductible_57C',\n",
    "       'IDeductible_57L', 'IDeductible_65K', 'IDeductible_66C',\n",
    "       'IDeductible_66L', 'IDeductible_67D', 'IDeductible_68E',\n",
    "       'IDeductible_68N', 'IDeductible_70G', 'IDeductible_70P',\n",
    "       'IDeductible_71H', 'IDeductible_72@',\n",
    "          '00I', '01A', '01J',\n",
    "       '02K', '03L', '04M', '05E', '05N', '06F', '07P', '08H', '09@',\n",
    "       '09I', '10A', '12L', '14E', '14N', '15F', '15O', '16G', '16P',\n",
    "       '18@', '18I', '20B', '20K', '25G', '26H', '27I', '29B', '29K',\n",
    "       '32N', '33F', '33O', '34P', '35H', '36I', '37J', '40M', '41E',\n",
    "       '41N', '42F', '45@', '46A', '47B', '51O', '55J', '56B', '56K',\n",
    "       '57C', '57L', '65K', '66C', '66L', '67D', '68E', '68N', '70G',\n",
    "       '70P', '71H', '72@'\n",
    "          \n",
    "         ]]\n",
    "tmp2 = te[['Insured_Amount1_00I',\n",
    "       'Insured_Amount1_01A', 'Insured_Amount1_01J', 'Insured_Amount1_02K',\n",
    "       'Insured_Amount1_03L', 'Insured_Amount1_04M', 'Insured_Amount1_05E',\n",
    "       'Insured_Amount1_05N', 'Insured_Amount1_06F', 'Insured_Amount1_07P',\n",
    "       'Insured_Amount1_08H', 'Insured_Amount1_09@', 'Insured_Amount1_09I',\n",
    "       'Insured_Amount1_10A', 'Insured_Amount1_12L', 'Insured_Amount1_14E',\n",
    "       'Insured_Amount1_14N', 'Insured_Amount1_15F', 'Insured_Amount1_15O',\n",
    "       'Insured_Amount1_16G', 'Insured_Amount1_16P', 'Insured_Amount1_18@',\n",
    "       'Insured_Amount1_18I', 'Insured_Amount1_20B', 'Insured_Amount1_20K',\n",
    "       'Insured_Amount1_25G', 'Insured_Amount1_26H', 'Insured_Amount1_27I',\n",
    "       'Insured_Amount1_29B', 'Insured_Amount1_29K', 'Insured_Amount1_32N',\n",
    "       'Insured_Amount1_33F', 'Insured_Amount1_33O', 'Insured_Amount1_34P',\n",
    "       'Insured_Amount1_35H', 'Insured_Amount1_36I', 'Insured_Amount1_37J',\n",
    "       'Insured_Amount1_40M', 'Insured_Amount1_41E', 'Insured_Amount1_41N',\n",
    "       'Insured_Amount1_42F', 'Insured_Amount1_45@', 'Insured_Amount1_46A',\n",
    "       'Insured_Amount1_47B', 'Insured_Amount1_51O', 'Insured_Amount1_55J',\n",
    "       'Insured_Amount1_56B', 'Insured_Amount1_56K', 'Insured_Amount1_57C',\n",
    "       'Insured_Amount1_57L', 'Insured_Amount1_65K', 'Insured_Amount1_66C',\n",
    "       'Insured_Amount1_66L', 'Insured_Amount1_67D', 'Insured_Amount1_68E',\n",
    "       'Insured_Amount1_68N', 'Insured_Amount1_70G', 'Insured_Amount1_70P',\n",
    "       'Insured_Amount1_71H', 'Insured_Amount1_72@', 'Insured_Amount2_00I',\n",
    "       'Insured_Amount2_01A', 'Insured_Amount2_01J', 'Insured_Amount2_02K',\n",
    "       'Insured_Amount2_03L', 'Insured_Amount2_04M', 'Insured_Amount2_05E',\n",
    "       'Insured_Amount2_05N', 'Insured_Amount2_06F', 'Insured_Amount2_07P',\n",
    "       'Insured_Amount2_08H', 'Insured_Amount2_09@', 'Insured_Amount2_09I',\n",
    "       'Insured_Amount2_10A', 'Insured_Amount2_12L', 'Insured_Amount2_14E',\n",
    "       'Insured_Amount2_14N', 'Insured_Amount2_15F', 'Insured_Amount2_15O',\n",
    "       'Insured_Amount2_16G', 'Insured_Amount2_16P', 'Insured_Amount2_18@',\n",
    "       'Insured_Amount2_18I', 'Insured_Amount2_20B', 'Insured_Amount2_20K',\n",
    "       'Insured_Amount2_25G', 'Insured_Amount2_26H', 'Insured_Amount2_27I',\n",
    "       'Insured_Amount2_29B', 'Insured_Amount2_29K', 'Insured_Amount2_32N',\n",
    "       'Insured_Amount2_33F', 'Insured_Amount2_33O', 'Insured_Amount2_34P',\n",
    "       'Insured_Amount2_35H', 'Insured_Amount2_36I', 'Insured_Amount2_37J',\n",
    "       'Insured_Amount2_40M', 'Insured_Amount2_41E', 'Insured_Amount2_41N',\n",
    "       'Insured_Amount2_42F', 'Insured_Amount2_45@', 'Insured_Amount2_46A',\n",
    "       'Insured_Amount2_47B', 'Insured_Amount2_51O', 'Insured_Amount2_55J',\n",
    "       'Insured_Amount2_56B', 'Insured_Amount2_56K', 'Insured_Amount2_57C',\n",
    "       'Insured_Amount2_57L', 'Insured_Amount2_65K', 'Insured_Amount2_66C',\n",
    "       'Insured_Amount2_66L', 'Insured_Amount2_67D', 'Insured_Amount2_68E',\n",
    "       'Insured_Amount2_68N', 'Insured_Amount2_70G', 'Insured_Amount2_70P',\n",
    "       'Insured_Amount2_71H', 'Insured_Amount2_72@', 'Insured_Amount3_00I',\n",
    "       'Insured_Amount3_01A', 'Insured_Amount3_01J', 'Insured_Amount3_02K',\n",
    "       'Insured_Amount3_03L', 'Insured_Amount3_04M', 'Insured_Amount3_05E',\n",
    "       'Insured_Amount3_05N', 'Insured_Amount3_06F', 'Insured_Amount3_07P',\n",
    "       'Insured_Amount3_08H', 'Insured_Amount3_09@', 'Insured_Amount3_09I',\n",
    "       'Insured_Amount3_10A', 'Insured_Amount3_12L', 'Insured_Amount3_14E',\n",
    "       'Insured_Amount3_14N', 'Insured_Amount3_15F', 'Insured_Amount3_15O',\n",
    "       'Insured_Amount3_16G', 'Insured_Amount3_16P', 'Insured_Amount3_18@',\n",
    "       'Insured_Amount3_18I', 'Insured_Amount3_20B', 'Insured_Amount3_20K',\n",
    "       'Insured_Amount3_25G', 'Insured_Amount3_26H', 'Insured_Amount3_27I',\n",
    "       'Insured_Amount3_29B', 'Insured_Amount3_29K', 'Insured_Amount3_32N',\n",
    "       'Insured_Amount3_33F', 'Insured_Amount3_33O', 'Insured_Amount3_34P',\n",
    "       'Insured_Amount3_35H', 'Insured_Amount3_36I', 'Insured_Amount3_37J',\n",
    "       'Insured_Amount3_40M', 'Insured_Amount3_41E', 'Insured_Amount3_41N',\n",
    "       'Insured_Amount3_42F', 'Insured_Amount3_45@', 'Insured_Amount3_46A',\n",
    "       'Insured_Amount3_47B', 'Insured_Amount3_51O', 'Insured_Amount3_55J',\n",
    "       'Insured_Amount3_56B', 'Insured_Amount3_56K', 'Insured_Amount3_57C',\n",
    "       'Insured_Amount3_57L', 'Insured_Amount3_65K', 'Insured_Amount3_66C',\n",
    "       'Insured_Amount3_66L', 'Insured_Amount3_67D', 'Insured_Amount3_68E',\n",
    "       'Insured_Amount3_68N', 'Insured_Amount3_70G', 'Insured_Amount3_70P',\n",
    "       'Insured_Amount3_71H', 'Insured_Amount3_72@', 'IDeductible_00I',\n",
    "       'IDeductible_01A', 'IDeductible_01J', 'IDeductible_02K',\n",
    "       'IDeductible_03L', 'IDeductible_04M', 'IDeductible_05E',\n",
    "       'IDeductible_05N', 'IDeductible_06F', 'IDeductible_07P',\n",
    "       'IDeductible_08H', 'IDeductible_09@', 'IDeductible_09I',\n",
    "       'IDeductible_10A', 'IDeductible_12L', 'IDeductible_14E',\n",
    "       'IDeductible_14N', 'IDeductible_15F', 'IDeductible_15O',\n",
    "       'IDeductible_16G', 'IDeductible_16P', 'IDeductible_18@',\n",
    "       'IDeductible_18I', 'IDeductible_20B', 'IDeductible_20K',\n",
    "       'IDeductible_25G', 'IDeductible_26H', 'IDeductible_27I',\n",
    "       'IDeductible_29B', 'IDeductible_29K', 'IDeductible_32N',\n",
    "       'IDeductible_33F', 'IDeductible_33O', 'IDeductible_34P',\n",
    "       'IDeductible_35H', 'IDeductible_36I', 'IDeductible_37J',\n",
    "       'IDeductible_40M', 'IDeductible_41E', 'IDeductible_41N',\n",
    "       'IDeductible_42F', 'IDeductible_45@', 'IDeductible_46A',\n",
    "       'IDeductible_47B', 'IDeductible_51O', 'IDeductible_55J',\n",
    "       'IDeductible_56B', 'IDeductible_56K', 'IDeductible_57C',\n",
    "       'IDeductible_57L', 'IDeductible_65K', 'IDeductible_66C',\n",
    "       'IDeductible_66L', 'IDeductible_67D', 'IDeductible_68E',\n",
    "       'IDeductible_68N', 'IDeductible_70G', 'IDeductible_70P',\n",
    "       'IDeductible_71H', 'IDeductible_72@',\n",
    "          '00I', '01A', '01J',\n",
    "       '02K', '03L', '04M', '05E', '05N', '06F', '07P', '08H', '09@',\n",
    "       '09I', '10A', '12L', '14E', '14N', '15F', '15O', '16G', '16P',\n",
    "       '18@', '18I', '20B', '20K', '25G', '26H', '27I', '29B', '29K',\n",
    "       '32N', '33F', '33O', '34P', '35H', '36I', '37J', '40M', '41E',\n",
    "       '41N', '42F', '45@', '46A', '47B', '51O', '55J', '56B', '56K',\n",
    "       '57C', '57L', '65K', '66C', '66L', '67D', '68E', '68N', '70G',\n",
    "       '70P', '71H', '72@'\n",
    "          \n",
    "         ]]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 415,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.99108822364656646"
      ]
     },
     "execution_count": 415,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tmp = tmp.fillna(0)\n",
    "tmp2 = tmp2.fillna(0)\n",
    "pca=PCA(n_components=7)\n",
    "tr_Coverage_pca = pca.fit_transform(tmp)\n",
    "te_Coverage_pca = pca.transform(tmp2)\n",
    "sum(pca.explained_variance_ratio_)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 416,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "tmp = pd.DataFrame(tr_Coverage_pca)\n",
    "tmp.columns = ['coverage_pca_' + str(i) for i in range(7)]\n",
    "tr = pd.concat([tr, tmp], axis=1)\n",
    "\n",
    "tmp2 = pd.DataFrame(te_Coverage_pca)\n",
    "tmp2.columns = ['coverage_pca_' + str(i) for i in range(7)]\n",
    "te = pd.concat([te, tmp2], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# imported car X premium\n",
    "policy['Imported_car_premium'] = policy.Imported_or_Domestic_Car * policy.Premium\n",
    "tmp = policy.groupby('Policy_Number', as_index=0).agg({'Imported_car_premium':['mean','sum']})\n",
    "tmp.columns = ['Policy_Number','mean_car_premium','sum_car_premium']\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# iply X premium\n",
    "policy['iply_premium'] = policy.iply * policy.Premium\n",
    "tmp = policy.groupby('Policy_Number', as_index=0).agg({'iply_premium':['mean','sum']})\n",
    "tmp.columns = ['Policy_Number','mean_iply_premium','sum_iply_premium']\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "policy['Manafactured_Year_and_Month_age'] = 2017 - policy['Manafactured_Year_and_Month']\n",
    "tmp = policy.groupby('Policy_Number', as_index= 0 )['Manafactured_Year_and_Month_age'].first()\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#tr[\"At_Fault?_premium\"] = tr.sum_Fault * tr.sum_Premium \n",
    "#te[\"At_Fault?_premium\"] = te.sum_Fault * te.sum_Premium \n",
    "\n",
    "#tr[\"At_Fault?_mean_premium\"] = tr.mean_Fault * tr.sum_Premium \n",
    "#te[\"At_Fault?_mean_premium\"] = te.mean_Fault * te.sum_Premium "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "claim = claim.rename(index=str,columns={'Coverage':'Insurance_Coverage'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#claim = pd.read_csv(path+'claim_0702.csv')\n",
    "_all = pd.merge(policy, claim, on=['Policy_Number','Insurance_Coverage'], how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# At_Fault_Premium\n",
    "_all[\"At_Fault_Premium\"] = _all[\"At_Fault?\"] * _all.Premium\n",
    "tmp = _all.groupby('Policy_Number', as_index=0).agg({'At_Fault_Premium':['mean','sum']})\n",
    "tmp.columns = ['Policy_Number','mean_At_Fault_Premium','sum_At_Fault_Premium']\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "tr = tr.drop(['mean_At_Fault_Premium','sum_At_Fault_Premium'],1)\n",
    "te = te.drop(['mean_At_Fault_Premium','sum_At_Fault_Premium'],1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1763786"
      ]
     },
     "execution_count": 100,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(_all)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1747942\n",
      "69613\n"
     ]
    }
   ],
   "source": [
    "print len(policy)\n",
    "print len(claim)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 區分汽機車\n",
    "tr.loc[tr.qpt == 2, 'is_motor_car_truck'] = 1\n",
    "tr.loc[(tr.qpt > 2 ) & (tr.qpt <= 9) , 'is_motor_car_truck'] = 2\n",
    "tr.loc[tr.qpt > 9, 'is_motor_car_truck'] = 3\n",
    "        \n",
    "te.loc[te.qpt == 2, 'is_motor_car_truck'] = 1\n",
    "te.loc[(te.qpt > 2 ) & (te.qpt <= 9) , 'is_motor_car_truck'] = 2\n",
    "te.loc[te.qpt > 9, 'is_motor_car_truck'] = 3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2.0    132104\n",
      "1.0      8201\n",
      "3.0       205\n",
      "Name: is_motor_car_truck, dtype: int64\n",
      "0\n"
     ]
    }
   ],
   "source": [
    "print te.is_motor_car_truck.value_counts()\n",
    "print len(te[te.is_motor_car_truck.isnull()])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 配備標籤\n",
    "tmp = policy.groupby('Policy_Number', as_index=0)[[\"fequipment1\",\"fequipment2\",\"fequipment3\",\"fequipment4\",\n",
    "                                            \"fequipment5\",\"fequipment6\",\"fequipment9\"]].first()\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "policy = pd.merge(policy, tr[['Policy_Number','Next_Premium']], how= 'left', on='Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "policy.loc[policy.Insured_Amount == 0, 'zero_Insured_Amount'] = 1\n",
    "tmp = policy.groupby(\"Policy_Number\", as_index=0)[\"zero_Insured_Amount\"].sum()\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "tr.loc[tr.Policy_showup_Numbers == tr.zero_Insured_Amount, 'no_future'] = 1\n",
    "tr.loc[tr.Policy_showup_Numbers != tr.zero_Insured_Amount, 'no_future'] = 0\n",
    "te.loc[te.Policy_showup_Numbers == te.zero_Insured_Amount, 'no_future'] = 1\n",
    "te.loc[te.Policy_showup_Numbers != te.zero_Insured_Amount, 'no_future'] = 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 148,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Claim_Number X Premium or Amt123\n",
    "tr['Claim_Number_Premium'] = tr.Claim_Number * tr.sum_Premium\n",
    "tr['Claim_Number_Insured_Amount1'] = tr.Claim_Number * tr.sum_Insured_Amount1\n",
    "tr['Claim_Number_Insured_Amount2'] = tr.Claim_Number * tr.sum_Insured_Amount2\n",
    "tr['Claim_Number_Insured_Amount3'] = tr.Claim_Number * tr.sum_Insured_Amount3\n",
    "\n",
    "te['Claim_Number_Premium'] = te.Claim_Number * te.sum_Premium\n",
    "te['Claim_Number_Insured_Amount1'] = te.Claim_Number * te.sum_Insured_Amount1\n",
    "te['Claim_Number_Insured_Amount2'] = te.Claim_Number * te.sum_Insured_Amount2\n",
    "te['Claim_Number_Insured_Amount3'] = te.Claim_Number * te.sum_Insured_Amount3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 150,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# claimants X Premium or Amt123\n",
    "tr['sum_claimants_Premium'] = tr.sum_claimants * tr.sum_Premium\n",
    "tr['sum_claimants_Insured_Amount1'] = tr.sum_claimants * tr.sum_Insured_Amount1\n",
    "tr['sum_claimants_Insured_Amount2'] = tr.sum_claimants * tr.sum_Insured_Amount2\n",
    "tr['sum_claimants_Insured_Amount3'] = tr.sum_claimants * tr.sum_Insured_Amount3\n",
    "\n",
    "te['sum_claimants_Premium'] = te.sum_claimants * te.sum_Premium\n",
    "te['sum_claimants_Insured_Amount1'] = te.sum_claimants * te.sum_Insured_Amount1\n",
    "te['sum_claimants_Insured_Amount2'] = te.sum_claimants * te.sum_Insured_Amount2\n",
    "te['sum_claimants_Insured_Amount3'] = te.sum_claimants * te.sum_Insured_Amount3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 203,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# claimants X Premium or repl\n",
    "tr['sum_claimants_repl'] = tr.sum_claimants * tr[\"Replacement_cost_of_insured_vehicle\"]\n",
    "te['sum_claimants_repl'] = te.sum_claimants * te[\"Replacement_cost_of_insured_vehicle\"]\n",
    "\n",
    "tr['sum_claimants_repl_premium'] = tr.sum_claimants * tr[\"Replacement_cost_of_insured_vehicle\"] * tr.sum_Premium\n",
    "te['sum_claimants_repl_premium'] = te.sum_claimants * te[\"Replacement_cost_of_insured_vehicle\"] * te.sum_Premium"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 204,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# claimants X Premium or qpt\n",
    "tr['sum_claimants_qpt'] = tr.sum_claimants * tr[\"qpt\"]\n",
    "te['sum_claimants_qpt'] = te.sum_claimants * te[\"qpt\"]\n",
    "\n",
    "tr['sum_claimants_qpt_premium'] = tr.sum_claimants * tr[\"qpt\"] * tr.sum_Premium\n",
    "te['sum_claimants_qpt_premium'] = te.sum_claimants * te[\"qpt\"] * te.sum_Premium"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 205,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# claimants X Premium or engine\n",
    "tr['sum_claimants_engine'] = tr.sum_claimants * tr[\"Engine_Displacement_(Cubic_Centimeter)\"]\n",
    "te['sum_claimants_engine'] = te.sum_claimants * te[\"Engine_Displacement_(Cubic_Centimeter)\"]\n",
    "\n",
    "tr['sum_claimants_engine_premium'] = tr.sum_claimants * tr[\"Engine_Displacement_(Cubic_Centimeter)\"] * tr.sum_Premium\n",
    "te['sum_claimants_engine_premium'] = te.sum_claimants * te[\"Engine_Displacement_(Cubic_Centimeter)\"] * te.sum_Premium"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 206,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Claim_Number X Premium or repl\n",
    "tr['Claim_Number_repl'] = tr.Claim_Number * tr[\"Replacement_cost_of_insured_vehicle\"]\n",
    "te['Claim_Number_repl'] = te.Claim_Number * te[\"Replacement_cost_of_insured_vehicle\"]\n",
    "\n",
    "tr['Claim_Number_repl_premium'] = tr.Claim_Number * tr[\"Replacement_cost_of_insured_vehicle\"] * tr.sum_Premium\n",
    "te['Claim_Number_repl_premium'] = te.Claim_Number * te[\"Replacement_cost_of_insured_vehicle\"] * te.sum_Premium"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 207,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# claimants X Premium or qpt\n",
    "tr['Claim_Number_qpt'] = tr.Claim_Number * tr[\"qpt\"]\n",
    "te['Claim_Number_qpt'] = te.Claim_Number * te[\"qpt\"]\n",
    "\n",
    "tr['Claim_Number_qpt_premium'] = tr.Claim_Number * tr[\"qpt\"] * tr.sum_Premium\n",
    "te['Claim_Number_qpt_premium'] = te.Claim_Number * te[\"qpt\"] * te.sum_Premium"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 208,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# claimants X Premium or repl\n",
    "tr['Claim_Number_repl'] = tr.Claim_Number * tr[\"Replacement_cost_of_insured_vehicle\"]\n",
    "te['Claim_Number_repl'] = te.Claim_Number * te[\"Replacement_cost_of_insured_vehicle\"]\n",
    "\n",
    "tr['Claim_Number_repl_premium'] = tr.Claim_Number * tr[\"Replacement_cost_of_insured_vehicle\"] * tr.sum_Premium\n",
    "te['Claim_Number_repl_premium'] = te.Claim_Number * te[\"Replacement_cost_of_insured_vehicle\"] * te.sum_Premium"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# channel X premium\n",
    "tr['channel_premium'] = tr.Channel * tr.sum_acc_Premium\n",
    "te['channel_premium'] = te.Channel * te.sum_acc_Premium"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "claim = pd.read_csv(path+'claim_0702.csv')\n",
    "claim = claim.rename(index=str,columns={'Coverage':'Insurance_Coverage'})\n",
    "_all = pd.merge(policy, claim, on=['Policy_Number','Insurance_Coverage'], how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 賠案性質計數\n",
    "tmp = claim.groupby(['Policy_Number','Nature_of_the_claim'], as_index=0)['Claim_Number'].count()\n",
    "tmp = tmp.pivot_table(index= 'Policy_Number', columns= 'Nature_of_the_claim', values= 'Claim_Number')\n",
    "tmp = tmp.reset_index(drop=False)\n",
    "tmp.columns = ['Policy_Number','Nature_of_the_claim_1','Nature_of_the_claim_2']\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 與被保險人關係計數\n",
    "tmp = claim.groupby(['Policy_Number',\"Driver's_Relationship_with_Insured\"], as_index=0)['Claim_Number'].count()\n",
    "tmp = tmp.pivot_table(index= 'Policy_Number', columns= \"Driver's_Relationship_with_Insured\", values= 'Claim_Number')\n",
    "tmp = tmp.reset_index(drop=False)\n",
    "tmp.columns = [\"Policy_Number\",\"driver_relation_1\",\"driver_relation_2\",\"driver_relation_3\",\"driver_relation_4\",\n",
    "               \"driver_relation_5\",\"driver_relation_6\",\"driver_relation_7\"]\n",
    "tr = pd.merge(tr, tmp, how = 'left', on = 'Policy_Number')\n",
    "te = pd.merge(te, tmp, how = 'left', on = 'Policy_Number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 195,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 法人、自然人\n",
    "tr.loc[(tr.fassured1 == 0) | (tr.fassured3 == 0), 'legal_man'] = 1\n",
    "tr.loc[(tr.fassured1 != 0) | (tr.fassured3 != 0), 'legal_man'] = 0\n",
    "te.loc[(te.fassured1 == 0) | (te.fassured3 == 0), 'legal_man'] = 1\n",
    "te.loc[(te.fassured1 != 0) | (te.fassured3 != 0), 'legal_man'] = 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "tr['Manu_age_square'] =  np.square(tr.Manafactured_Year_and_Month_age)\n",
    "tr['claim_deductible_premium'] = tr.sum_claim_Deductible * tr.sum_Premium\n",
    "\n",
    "te['Manu_age_square'] =  np.square(te.Manafactured_Year_and_Month_age)\n",
    "te['claim_deductible_premium'] = te.sum_claim_Deductible * te.sum_Premium"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "tr['claim_not_close_rate'] = tr.n_close / (tr.n_close + tr.n_not_close)\n",
    "te['claim_not_close_rate'] = te.n_close / (te.n_close + te.n_not_close)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "tr[\"sum_Premium_div_age\"] = tr.sum_Premium / tr.age\n",
    "te[\"sum_Premium_div_age\"] = te.sum_Premium / te.age"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "tr.to_csv(path+'tr2.csv', index=False, encoding='utf-8')\n",
    "te.to_csv(path+'te2.csv', index=False, encoding='utf-8')\n",
    "#policy.to_csv(path+'policy2.csv',index=False, encoding='utf-8')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}