Preprocessing and Modeling with UniqueKey.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h1>Greetings</h1>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This IPython file showcases our approach to the Republic Day Hackathon organised by TEG Analytics and Analytics India Magazine. The task was to “Predict Market Competitiveness For Insurance Products”.<br>\n",
    "The IPython file has been divided into the following categories:<br>\n",
    "1. Preprocessing: This is where we spent the majority of our time by cleaning, imputing data and feature generation.<br><br>\n",
    "2. Modeling: We have used several different models for our prediction.They include:\n",
    "<ul>\n",
    "    <li>XGBoost Regressor</li>\n",
    "    <li>StackNet Regressor</li>\n",
    "    <li>Random Forest Regressor</li>\n",
    "</ul>\n",
    "<br>\n",
    "3. Submimission: Where we created our submission file."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is one of the part of the series of notebooks that we have used to create our final prediction. In this notebook we have used the the key columns that are the unique or different in both the train and test data set. After merging and creating the new data we cleaned the new data, created new features  and used different models to make our prediction. The key columns are 'County' ,'key_Segment1' and 'State'."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h4>Importing the Libraries</h4>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import seaborn as sns\n",
    "import matplotlib.pyplot as plt\n",
    "import re"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Importing the dataset below"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\samde\\Miniconda3\\envs\\AI\\lib\\site-packages\\IPython\\core\\interactiveshell.py:2785: DtypeWarning: Columns (16,17,18,19,20,21,22,23,24,26,27,29,46) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n",
      "C:\\Users\\samde\\Miniconda3\\envs\\AI\\lib\\site-packages\\IPython\\core\\interactiveshell.py:2785: DtypeWarning: Columns (22,24,25,26,47) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    }
   ],
   "source": [
    "df5=pd.read_csv('C:/Users/samde/Desktop/Data Science/Datasets/Medicare Hackathon/Landscape/Plandata_jan18.csv',sep = ',',encoding='mac_roman')\n",
    "df2=pd.read_csv('C:/Users/samde/Desktop/Data Science/Datasets/Medicare Hackathon/TestData/Landscape_Test.csv',sep = ',',encoding='mac_roman')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Extra data which was given later on in the competition."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "extra=pd.read_csv('C:/Users/samde/Desktop/Data Science/Datasets/Medicare Hackathon/Crosswalk/landscape2018_enrollments_jan17.csv',sep = ',',encoding='mac_roman')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "extra1=pd.read_csv('C:/Users/samde/Desktop/Data Science/Datasets/Medicare Hackathon/Crosswalk/landscape2019_enrollments_jan18.csv',sep = ',',encoding='mac_roman')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h1>Preprocessing</h1>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "df5['bid_id']=df5['key_Segment1']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "278.81246501539323"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "extra1['Enrollment_jan_18'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#df6=pd.merge(df5, extra, on=['statecode','County','State','bid_id'],how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#df6.drop(['Enrollment_jan_17'],axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#extra1[\"key_Segment1\"]=extra1[\"bid_id\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "df5['Previous_Year']=extra['Enrollment_jan_17']\n",
    "df2=pd.merge(df2,extra1, on=['County','statecode',\"bid_id\"],how=\"left\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2['Previous_Year']=df2[\"Enrollment_jan_18\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "#df2[\"Previous_Year\"].isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2.drop(\"Enrollment_jan_18\",axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "#df6['Enrollment_jan_17']=extra['Enrollment_jan_17']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "#df6.drop(['enrollments_jan18'],axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "#df6['enrollments_jan18']=df6['Enrollment_jan_17']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "#df6.drop(['Enrollment_jan_17'],axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "#df5.drop(['bid_id'],axis=1,inplace=True)\n",
    "#df6.drop(['bid_id'],axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "#df=pd.concat([df5,df6])\n",
    "df=df5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "#df[\"key_Segment1\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "#df[df[\"key_Segment1\"] == \"H5435-024-000\"][\"Organization_Name\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "#df[(df.State==\"Alabama\") & (df.County==\"Autauga\") & (df.Plan_Name==\"Blue Advantage Complete (PPO)\") & (df.Organization_Name==\"Blue Advantage (PPO)\")]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "#df[\"state_county\"]=df[\"State\"].astype(str) + \"_\" + df[\"County\"].astype(str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_key=pd.DataFrame()\n",
    "test_key=pd.DataFrame()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "#train_key['Organization_Name']=df['Organization_Name']\n",
    "train_key['County']=df['County']\n",
    "train_key['State']=df['State']\n",
    "train_key['key_Segment1']=df['key_Segment1']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "#test_key['Organization_Name']=df2['Organization_Name']\n",
    "test_key['County']=df2['County']\n",
    "test_key['State']=df2['State']\n",
    "test_key['key_Segment1']=df2['key_Segment1']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(57168, 59)"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "same_key=pd.merge(train_key,test_key, on=['County','State','key_Segment1'], how='inner')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2778"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "same_key['key_Segment1'].nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "#same_key.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "#same_key1=pd.merge(train_key,test_key, on=[\"Organization_Name\",'County','State','key_Segment1'], how='inner')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "#set(same_key[\"key_Segment1\"])-set(same_key1[\"key_Segment1\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "#test_key[test_key[\"key_Segment1\"]=='H0028-004-000']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "#train_key[train_key[\"key_Segment1\"]=='H0028-004-000']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "unique_key=pd.concat([train_key,test_key])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(106904, 3)"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "unique_key.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "unique_key.drop_duplicates(keep=False,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "#unique_key[\"Organization_Name\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "uniquekeyonlytest=pd.merge(unique_key,test_key, on=['County','State','key_Segment1'], how='inner')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(15862, 3)"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "uniquekeyonlytest.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(57168, 59)"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(41306, 3)"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "same_key.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2=pd.merge(uniquekeyonlytest,df2,how='left',on=['County','State','key_Segment1'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(15862, 59)"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "df=pd.merge(uniquekeyonlytest,df2,how='left',on=['County','State','key_Segment1'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "#df=pd.merge(uniquekeyonlytest,df2,how='left',on=['County','State','key_Segment1'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>County</th>\n",
       "      <th>State</th>\n",
       "      <th>key_Segment1</th>\n",
       "      <th>bid_id</th>\n",
       "      <th>Organization_Name</th>\n",
       "      <th>Plan_Name</th>\n",
       "      <th>plan_type</th>\n",
       "      <th>Monthly_Consolidated_Premium_C_D</th>\n",
       "      <th>Annual_Drug_Deductible</th>\n",
       "      <th>Drug_Benefit_Type</th>\n",
       "      <th>...</th>\n",
       "      <th>Extra_Coverage_in_Gap</th>\n",
       "      <th>statecode</th>\n",
       "      <th>SSA_State_County_Code</th>\n",
       "      <th>FIPS_State_County_Code</th>\n",
       "      <th>SalesMarket</th>\n",
       "      <th>star_rating</th>\n",
       "      <th>Parent_Organization</th>\n",
       "      <th>segment_id</th>\n",
       "      <th>TotalEstimatedAnnualCost</th>\n",
       "      <th>Previous_Year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Autauga</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>H4513-044-000</td>\n",
       "      <td>H4513_044_0</td>\n",
       "      <td>Cigna-HealthSpring</td>\n",
       "      <td>Cigna-HealthSpring TotalCare (HMO SNP)</td>\n",
       "      <td>Local HMO</td>\n",
       "      <td>$27.10</td>\n",
       "      <td>$415.00</td>\n",
       "      <td>Basic</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>AL</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>1001.0</td>\n",
       "      <td>ALABAMA</td>\n",
       "      <td>4.5</td>\n",
       "      <td>CIGNA</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>234</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Autauga</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>H4513-045-000</td>\n",
       "      <td>H4513_045_0</td>\n",
       "      <td>Cigna-HealthSpring</td>\n",
       "      <td>Cigna-HealthSpring Advantage (HMO)</td>\n",
       "      <td>Local HMO *</td>\n",
       "      <td>$0.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>AL</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>1001.0</td>\n",
       "      <td>ALABAMA</td>\n",
       "      <td>4.5</td>\n",
       "      <td>CIGNA</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Autauga</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>H4513-046-001</td>\n",
       "      <td>H4513_046_1</td>\n",
       "      <td>Cigna-HealthSpring</td>\n",
       "      <td>Cigna-HealthSpring Preferred (HMO)</td>\n",
       "      <td>Local HMO</td>\n",
       "      <td>$0.00</td>\n",
       "      <td>$200.00</td>\n",
       "      <td>Enhanced</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>AL</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>1001.0</td>\n",
       "      <td>ALABAMA</td>\n",
       "      <td>4.5</td>\n",
       "      <td>CIGNA</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>325</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Autauga</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>H4513-047-000</td>\n",
       "      <td>H4513_047_0</td>\n",
       "      <td>Cigna-HealthSpring</td>\n",
       "      <td>Cigna-HealthSpring PreferredPlus Direct (HMO)</td>\n",
       "      <td>Local HMO</td>\n",
       "      <td>$49.00</td>\n",
       "      <td>$0.00</td>\n",
       "      <td>Enhanced</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>AL</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>1001.0</td>\n",
       "      <td>ALABAMA</td>\n",
       "      <td>4.5</td>\n",
       "      <td>CIGNA</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Autauga</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>H5521-224-000</td>\n",
       "      <td>H5521_224_0</td>\n",
       "      <td>Aetna Medicare</td>\n",
       "      <td>Aetna Medicare Freedom Plan (PPO)</td>\n",
       "      <td>Local PPO</td>\n",
       "      <td>$0.00</td>\n",
       "      <td>$95.00</td>\n",
       "      <td>Enhanced</td>\n",
       "      <td>...</td>\n",
       "      <td>Y</td>\n",
       "      <td>AL</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>1001.0</td>\n",
       "      <td>ALABAMA</td>\n",
       "      <td>4.0</td>\n",
       "      <td>Aetna Inc.</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 59 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    County    State   key_Segment1       bid_id   Organization_Name  \\\n",
       "0  Autauga  Alabama  H4513-044-000  H4513_044_0  Cigna-HealthSpring   \n",
       "1  Autauga  Alabama  H4513-045-000  H4513_045_0  Cigna-HealthSpring   \n",
       "2  Autauga  Alabama  H4513-046-001  H4513_046_1  Cigna-HealthSpring   \n",
       "3  Autauga  Alabama  H4513-047-000  H4513_047_0  Cigna-HealthSpring   \n",
       "4  Autauga  Alabama  H5521-224-000  H5521_224_0      Aetna Medicare   \n",
       "\n",
       "                                       Plan_Name    plan_type  \\\n",
       "0         Cigna-HealthSpring TotalCare (HMO SNP)    Local HMO   \n",
       "1             Cigna-HealthSpring Advantage (HMO)  Local HMO *   \n",
       "2             Cigna-HealthSpring Preferred (HMO)    Local HMO   \n",
       "3  Cigna-HealthSpring PreferredPlus Direct (HMO)    Local HMO   \n",
       "4              Aetna Medicare Freedom Plan (PPO)    Local PPO   \n",
       "\n",
       "  Monthly_Consolidated_Premium_C_D Annual_Drug_Deductible Drug_Benefit_Type  \\\n",
       "0                           $27.10                $415.00             Basic   \n",
       "1                            $0.00                    NaN               NaN   \n",
       "2                            $0.00                $200.00          Enhanced   \n",
       "3                           $49.00                  $0.00          Enhanced   \n",
       "4                            $0.00                 $95.00          Enhanced   \n",
       "\n",
       "       ...      Extra_Coverage_in_Gap statecode SSA_State_County_Code  \\\n",
       "0      ...                        NaN        AL                1000.0   \n",
       "1      ...                        NaN        AL                1000.0   \n",
       "2      ...                        NaN        AL                1000.0   \n",
       "3      ...                        NaN        AL                1000.0   \n",
       "4      ...                          Y        AL                1000.0   \n",
       "\n",
       "  FIPS_State_County_Code  SalesMarket star_rating Parent_Organization  \\\n",
       "0                 1001.0      ALABAMA         4.5               CIGNA   \n",
       "1                 1001.0      ALABAMA         4.5               CIGNA   \n",
       "2                 1001.0      ALABAMA         4.5               CIGNA   \n",
       "3                 1001.0      ALABAMA         4.5               CIGNA   \n",
       "4                 1001.0      ALABAMA         4.0          Aetna Inc.   \n",
       "\n",
       "  segment_id TotalEstimatedAnnualCost Previous_Year  \n",
       "0          0                        0           234  \n",
       "1          0                        0             0  \n",
       "2          1                        0           325  \n",
       "3          0                        0             0  \n",
       "4          0                        0             0  \n",
       "\n",
       "[5 rows x 59 columns]"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "#df.drop(\"Previous_Year\",axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2.drop(\"Previous_Year\",axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(15862, 58)"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "#df2[df2[\"Previous_Year\"]!=0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [],
   "source": [
    "#extra1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": [
    "#extra1[(extra1.County==\"Autauga\") & (extra1.statecode==\"AL\") & (extra1.bid_id==\"H4513_044_0\")]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [],
   "source": [
    "#df2.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>County</th>\n",
       "      <th>State</th>\n",
       "      <th>key_Segment1</th>\n",
       "      <th>bid_id</th>\n",
       "      <th>Organization_Name</th>\n",
       "      <th>Plan_Name</th>\n",
       "      <th>plan_type</th>\n",
       "      <th>Monthly_Consolidated_Premium_C_D</th>\n",
       "      <th>Annual_Drug_Deductible</th>\n",
       "      <th>Drug_Benefit_Type</th>\n",
       "      <th>...</th>\n",
       "      <th>Increased_Initial_Coverage_Limit</th>\n",
       "      <th>Extra_Coverage_in_Gap</th>\n",
       "      <th>statecode</th>\n",
       "      <th>SSA_State_County_Code</th>\n",
       "      <th>FIPS_State_County_Code</th>\n",
       "      <th>SalesMarket</th>\n",
       "      <th>star_rating</th>\n",
       "      <th>Parent_Organization</th>\n",
       "      <th>segment_id</th>\n",
       "      <th>TotalEstimatedAnnualCost</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Autauga</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>H4513-044-000</td>\n",
       "      <td>H4513_044_0</td>\n",
       "      <td>Cigna-HealthSpring</td>\n",
       "      <td>Cigna-HealthSpring TotalCare (HMO SNP)</td>\n",
       "      <td>Local HMO</td>\n",
       "      <td>$27.10</td>\n",
       "      <td>$415.00</td>\n",
       "      <td>Basic</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>AL</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>1001.0</td>\n",
       "      <td>ALABAMA</td>\n",
       "      <td>4.5</td>\n",
       "      <td>CIGNA</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Autauga</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>H4513-045-000</td>\n",
       "      <td>H4513_045_0</td>\n",
       "      <td>Cigna-HealthSpring</td>\n",
       "      <td>Cigna-HealthSpring Advantage (HMO)</td>\n",
       "      <td>Local HMO *</td>\n",
       "      <td>$0.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>AL</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>1001.0</td>\n",
       "      <td>ALABAMA</td>\n",
       "      <td>4.5</td>\n",
       "      <td>CIGNA</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Autauga</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>H4513-046-001</td>\n",
       "      <td>H4513_046_1</td>\n",
       "      <td>Cigna-HealthSpring</td>\n",
       "      <td>Cigna-HealthSpring Preferred (HMO)</td>\n",
       "      <td>Local HMO</td>\n",
       "      <td>$0.00</td>\n",
       "      <td>$200.00</td>\n",
       "      <td>Enhanced</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>AL</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>1001.0</td>\n",
       "      <td>ALABAMA</td>\n",
       "      <td>4.5</td>\n",
       "      <td>CIGNA</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Autauga</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>H4513-047-000</td>\n",
       "      <td>H4513_047_0</td>\n",
       "      <td>Cigna-HealthSpring</td>\n",
       "      <td>Cigna-HealthSpring PreferredPlus Direct (HMO)</td>\n",
       "      <td>Local HMO</td>\n",
       "      <td>$49.00</td>\n",
       "      <td>$0.00</td>\n",
       "      <td>Enhanced</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>AL</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>1001.0</td>\n",
       "      <td>ALABAMA</td>\n",
       "      <td>4.5</td>\n",
       "      <td>CIGNA</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Autauga</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>H5521-224-000</td>\n",
       "      <td>H5521_224_0</td>\n",
       "      <td>Aetna Medicare</td>\n",
       "      <td>Aetna Medicare Freedom Plan (PPO)</td>\n",
       "      <td>Local PPO</td>\n",
       "      <td>$0.00</td>\n",
       "      <td>$95.00</td>\n",
       "      <td>Enhanced</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Y</td>\n",
       "      <td>AL</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>1001.0</td>\n",
       "      <td>ALABAMA</td>\n",
       "      <td>4.0</td>\n",
       "      <td>Aetna Inc.</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 58 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    County    State   key_Segment1       bid_id   Organization_Name  \\\n",
       "0  Autauga  Alabama  H4513-044-000  H4513_044_0  Cigna-HealthSpring   \n",
       "1  Autauga  Alabama  H4513-045-000  H4513_045_0  Cigna-HealthSpring   \n",
       "2  Autauga  Alabama  H4513-046-001  H4513_046_1  Cigna-HealthSpring   \n",
       "3  Autauga  Alabama  H4513-047-000  H4513_047_0  Cigna-HealthSpring   \n",
       "4  Autauga  Alabama  H5521-224-000  H5521_224_0      Aetna Medicare   \n",
       "\n",
       "                                       Plan_Name    plan_type  \\\n",
       "0         Cigna-HealthSpring TotalCare (HMO SNP)    Local HMO   \n",
       "1             Cigna-HealthSpring Advantage (HMO)  Local HMO *   \n",
       "2             Cigna-HealthSpring Preferred (HMO)    Local HMO   \n",
       "3  Cigna-HealthSpring PreferredPlus Direct (HMO)    Local HMO   \n",
       "4              Aetna Medicare Freedom Plan (PPO)    Local PPO   \n",
       "\n",
       "  Monthly_Consolidated_Premium_C_D Annual_Drug_Deductible Drug_Benefit_Type  \\\n",
       "0                           $27.10                $415.00             Basic   \n",
       "1                            $0.00                    NaN               NaN   \n",
       "2                            $0.00                $200.00          Enhanced   \n",
       "3                           $49.00                  $0.00          Enhanced   \n",
       "4                            $0.00                 $95.00          Enhanced   \n",
       "\n",
       "            ...            Increased_Initial_Coverage_Limit  \\\n",
       "0           ...                                         NaN   \n",
       "1           ...                                         NaN   \n",
       "2           ...                                         NaN   \n",
       "3           ...                                         NaN   \n",
       "4           ...                                         NaN   \n",
       "\n",
       "  Extra_Coverage_in_Gap statecode SSA_State_County_Code  \\\n",
       "0                   NaN        AL                1000.0   \n",
       "1                   NaN        AL                1000.0   \n",
       "2                   NaN        AL                1000.0   \n",
       "3                   NaN        AL                1000.0   \n",
       "4                     Y        AL                1000.0   \n",
       "\n",
       "   FIPS_State_County_Code SalesMarket star_rating Parent_Organization  \\\n",
       "0                  1001.0     ALABAMA         4.5               CIGNA   \n",
       "1                  1001.0     ALABAMA         4.5               CIGNA   \n",
       "2                  1001.0     ALABAMA         4.5               CIGNA   \n",
       "3                  1001.0     ALABAMA         4.5               CIGNA   \n",
       "4                  1001.0     ALABAMA         4.0          Aetna Inc.   \n",
       "\n",
       "  segment_id TotalEstimatedAnnualCost  \n",
       "0          0                        0  \n",
       "1          0                        0  \n",
       "2          1                        0  \n",
       "3          0                        0  \n",
       "4          0                        0  \n",
       "\n",
       "[5 rows x 58 columns]"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "#set(df2[\"bid_id\"])-set(df['bid_id']).shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1=df.drop([\"FIPS_State_County_Code\",\"SSA_State_County_Code\",\"statecode\",\"Contract_ID\",\"Plan_ID\",\"segment_id\",\"Parent_Organization\",\"TotalEstimatedAnnualCost\",\"SalesMarket\",\"Drug_Benefit_Type_Detail\",\"snp\",\"National_PDP\",'Crdiovsculr_Disordrs_Diabetes'],axis=1)\n",
    "df3=df2.drop([\"FIPS_State_County_Code\",\"SSA_State_County_Code\",\"statecode\",\"Contract_ID\",\"Plan_ID\",\"segment_id\",\"Parent_Organization\",\"TotalEstimatedAnnualCost\",\"SalesMarket\",\"Drug_Benefit_Type_Detail\",\"snp\",\"National_PDP\",'Crdiovsculr_Disordrs_Diabetes'],axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(15862, 45)"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [],
   "source": [
    "#df1.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1.drop(\"Organization_Name\",axis=1,inplace=True)\n",
    "df3.drop(\"Organization_Name\",axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1[\"plan_type\"].fillna(\"NA\",inplace=True)\n",
    "df3[\"plan_type\"].fillna(\"NA\",inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [],
   "source": [
    "#For train\n",
    "plantype=[]\n",
    "for i, row in df1.iterrows():\n",
    "    if row[\"plan_type\"] == 'NA':\n",
    "        plantype.append(row[\"plan_type_p\"])\n",
    "    else:\n",
    "        plantype.append(row[\"plan_type\"])\n",
    "#For test\n",
    "plantype_2=[]\n",
    "for i, row in df3.iterrows():\n",
    "    if row[\"plan_type\"] == 'NA':\n",
    "        plantype_2.append(row[\"plan_type_p\"])\n",
    "    else:\n",
    "        plantype_2.append(row[\"plan_type\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1[\"plantype\"]=plantype\n",
    "df3[\"plantype\"]=plantype_2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1.drop([\"plan_type_p\",\"plan_type\"],axis=1,inplace=True)\n",
    "df3.drop([\"plan_type_p\",\"plan_type\"],axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1[\"Monthly_Consolidated_Premium_C_D\"].fillna(\"$0.00\",inplace=True)\n",
    "df3[\"Monthly_Consolidated_Premium_C_D\"].fillna(\"$0.00\",inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"PartD_Basic_Premium3\"].fillna(\"$0.00\",inplace=True)\n",
    "df2[\"PartD_Basic_Premium3\"].fillna(\"$0.00\",inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"PartC_Premium2\"].fillna(\"$0.00\",inplace=True)\n",
    "df2[\"PartC_Premium2\"].fillna(\"$0.00\",inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"PartD_Supplemental_Premium4\"].fillna(\"$0.00\",inplace=True)\n",
    "df2[\"PartD_Supplemental_Premium4\"].fillna(\"$0.00\",inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"PartD_Total_Premium5\"].fillna(\"$0.00\",inplace=True)\n",
    "df2[\"PartD_Total_Premium5\"].fillna(\"$0.00\",inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"PartD_Prm_oblgtn_Full_prm_assist\"].fillna(\"$0.00\",inplace=True)\n",
    "df2[\"PartD_Prm_oblgtn_Full_prm_assist\"].fillna(\"$0.00\",inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"PartD_Prm_oblgtn_75_prm_assist\"].fillna(\"$0.00\",inplace=True)\n",
    "df2[\"PartD_Prm_oblgtn_75_prm_assist\"].fillna(\"$0.00\",inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"PartD_Prm_oblgtn_50_prm_assist\"].fillna(\"$0.00\",inplace=True)\n",
    "df2[\"PartD_Prm_oblgtn_50_prm_assist\"].fillna(\"$0.00\",inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"PartD_Prm_oblgtn_25_prm_assist\"].fillna(\"$0.00\",inplace=True)\n",
    "df2[\"PartD_Prm_oblgtn_25_prm_assist\"].fillna(\"$0.00\",inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"PartD_Drug_Deductible\"].fillna(\"$0.00\",inplace=True)\n",
    "df2[\"PartD_Drug_Deductible\"].fillna(\"$0.00\",inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dtype('O')"
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3[\"Monthly_Consolidated_Premium_C_D\"].dtype"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [],
   "source": [
    "#For train\n",
    "MonthlyConsolidatedPremiumCD=[]\n",
    "\n",
    "for i, row in df1.iterrows():\n",
    "    MonthlyConsolidatedPremiumCD.append(re.sub('[!@#$]', '', str(row[\"Monthly_Consolidated_Premium_C_D\"])))\n",
    "\n",
    "#For test\n",
    "MonthlyConsolidatedPremiumCD_2=[]\n",
    "\n",
    "for i, row in df3.iterrows():\n",
    "    MonthlyConsolidatedPremiumCD_2.append(re.sub('[!@#$]', '', str(row[\"Monthly_Consolidated_Premium_C_D\"])))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [],
   "source": [
    "#For train\n",
    "MonthlyConsolidatedPremiumCD_=[]\n",
    "for i in range(0,len(MonthlyConsolidatedPremiumCD)):\n",
    "    MonthlyConsolidatedPremiumCD_.append(float(MonthlyConsolidatedPremiumCD[i]))\n",
    "#For test\n",
    "MonthlyConsolidatedPremiumCD_3=[]\n",
    "for i in range(0,len(MonthlyConsolidatedPremiumCD_2)):\n",
    "    MonthlyConsolidatedPremiumCD_3.append(float(MonthlyConsolidatedPremiumCD_2[i]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1[\"monthlycd\"]=MonthlyConsolidatedPremiumCD_\n",
    "df3[\"monthlycd\"]=MonthlyConsolidatedPremiumCD_3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [],
   "source": [
    "#df1[\"monthlycd\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1.drop(\"Monthly_Consolidated_Premium_C_D\",axis=1,inplace=True)\n",
    "df3.drop(\"Monthly_Consolidated_Premium_C_D\",axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [],
   "source": [
    "#For train\n",
    "PartDBasicPremium3=[]\n",
    "\n",
    "for i, row in df.iterrows():\n",
    "    PartDBasicPremium3.append(re.sub('[!@#$]', '', str(row[\"PartD_Basic_Premium3\"])))\n",
    "#For test\n",
    "PartDBasicPremium3_2=[]\n",
    "\n",
    "for i, row in df2.iterrows():\n",
    "    PartDBasicPremium3_2.append(re.sub('[!@#$]', '', str(row[\"PartD_Basic_Premium3\"])))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [],
   "source": [
    "#For train\n",
    "PartDBasicPremium3_=[]\n",
    "for i in range(0,len(PartDBasicPremium3)):\n",
    "    PartDBasicPremium3_.append(float(PartDBasicPremium3[i]))\n",
    "#For test\n",
    "PartDBasicPremium3_3=[]\n",
    "for i in range(0,len(PartDBasicPremium3_2)):\n",
    "    PartDBasicPremium3_3.append(float(PartDBasicPremium3_2[i]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1[\"partdbasicpremium3\"]=PartDBasicPremium3_\n",
    "df3[\"partdbasicpremium3\"]=PartDBasicPremium3_3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [],
   "source": [
    "#For train\n",
    "PartCPremium2=[]\n",
    "\n",
    "for i, row in df.iterrows():\n",
    "    PartCPremium2.append(re.sub('[!@#$]', '', row[\"PartC_Premium2\"]))\n",
    "#For test\n",
    "PartCPremium2_2=[]\n",
    "\n",
    "for i, row in df2.iterrows():\n",
    "    PartCPremium2_2.append(re.sub('[!@#$]', '', row[\"PartC_Premium2\"]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [],
   "source": [
    "#For train\n",
    "PartCPremium2_=[]\n",
    "for i in range(0,len(PartDBasicPremium3)):\n",
    "    PartCPremium2_.append(float(PartCPremium2[i]))\n",
    "#For test\n",
    "PartCPremium2_3=[]\n",
    "for i in range(0,len(PartDBasicPremium3_2)):\n",
    "    PartCPremium2_3.append(float(PartCPremium2_2[i]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1[\"partcpremium2\"]=PartCPremium2_\n",
    "df3[\"partcpremium2\"]=PartCPremium2_3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [],
   "source": [
    "#For train\n",
    "PartDSupplementalPremium4=[]\n",
    "\n",
    "for i, row in df.iterrows():\n",
    "    PartDSupplementalPremium4.append(re.sub('[!@#$]', '', row[\"PartD_Supplemental_Premium4\"]))\n",
    "#For test\n",
    "PartDSupplementalPremium4_2=[]\n",
    "\n",
    "for i, row in df2.iterrows():\n",
    "    PartDSupplementalPremium4_2.append(re.sub('[!@#$]', '', row[\"PartD_Supplemental_Premium4\"]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [],
   "source": [
    "#For train\n",
    "PartDSupplementalPremium4_=[]\n",
    "for i in range(0,len(PartDBasicPremium3)):\n",
    "    PartDSupplementalPremium4_.append(float(PartDSupplementalPremium4[i]))\n",
    "#For test\n",
    "PartDSupplementalPremium4_3=[]\n",
    "for i in range(0,len(PartDBasicPremium3_2)):\n",
    "    PartDSupplementalPremium4_3.append(float(PartDSupplementalPremium4_2[i]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1[\"partdsupplementalpremium4\"]=PartDSupplementalPremium4_\n",
    "df3[\"partdsupplementalpremium4\"]=PartDSupplementalPremium4_3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [],
   "source": [
    "#For train\n",
    "PartDPrmoblgtnFullprmassist=[]\n",
    "\n",
    "for i, row in df.iterrows():\n",
    "    PartDPrmoblgtnFullprmassist.append(re.sub('[!@#$]', '', row[\"PartD_Prm_oblgtn_Full_prm_assist\"]))\n",
    "#For test\n",
    "PartDPrmoblgtnFullprmassist_2=[]\n",
    "\n",
    "for i, row in df2.iterrows():\n",
    "    PartDPrmoblgtnFullprmassist_2.append(re.sub('[!@#$]', '', row[\"PartD_Prm_oblgtn_Full_prm_assist\"]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [],
   "source": [
    "#For train\n",
    "PartDPrmoblgtnFullprmassist_=[]\n",
    "for i in range(0,len(PartDBasicPremium3)):\n",
    "    PartDPrmoblgtnFullprmassist_.append(float(PartDPrmoblgtnFullprmassist[i]))\n",
    "#For test\n",
    "PartDPrmoblgtnFullprmassist_3=[]\n",
    "for i in range(0,len(PartDBasicPremium3_2)):\n",
    "    PartDPrmoblgtnFullprmassist_3.append(float(PartDPrmoblgtnFullprmassist_2[i]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1[\"partdfull\"]=PartDPrmoblgtnFullprmassist_\n",
    "df3[\"partdfull\"]=PartDPrmoblgtnFullprmassist_3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {},
   "outputs": [],
   "source": [
    "#For train\n",
    "PartDPrmoblgtn75prmassist=[]\n",
    "\n",
    "for i, row in df.iterrows():\n",
    "    PartDPrmoblgtn75prmassist.append(re.sub('[!@#$]', '', row[\"PartD_Prm_oblgtn_75_prm_assist\"]))\n",
    "#For test\n",
    "PartDPrmoblgtn75prmassist_2=[]\n",
    "\n",
    "for i, row in df2.iterrows():\n",
    "    PartDPrmoblgtn75prmassist_2.append(re.sub('[!@#$]', '', row[\"PartD_Prm_oblgtn_75_prm_assist\"]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [],
   "source": [
    "#For train\n",
    "PartDPrmoblgtn75prmassist_=[]\n",
    "for i in range(0,len(PartDBasicPremium3)):\n",
    "    PartDPrmoblgtn75prmassist_.append(float(PartDPrmoblgtn75prmassist[i]))\n",
    "#For test\n",
    "PartDPrmoblgtn75prmassist_3=[]\n",
    "for i in range(0,len(PartDBasicPremium3_2)):\n",
    "    PartDPrmoblgtn75prmassist_3.append(float(PartDPrmoblgtn75prmassist_2[i]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1[\"partd50\"]=PartDPrmoblgtn75prmassist_\n",
    "df3[\"partd50\"]=PartDPrmoblgtn75prmassist_3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [],
   "source": [
    "#For train\n",
    "PartDPrmoblgtn50prmassist=[]\n",
    "\n",
    "for i, row in df.iterrows():\n",
    "    PartDPrmoblgtn50prmassist.append(re.sub('[!@#$]', '', row[\"PartD_Prm_oblgtn_50_prm_assist\"]))\n",
    "#For test\n",
    "PartDPrmoblgtn50prmassist_2=[]\n",
    "\n",
    "for i, row in df2.iterrows():\n",
    "    PartDPrmoblgtn50prmassist_2.append(re.sub('[!@#$]', '', row[\"PartD_Prm_oblgtn_50_prm_assist\"]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [],
   "source": [
    "#For train\n",
    "PartDPrmoblgtn50prmassist_=[]\n",
    "for i in range(0,len(PartDBasicPremium3)):\n",
    "    PartDPrmoblgtn50prmassist_.append(float(PartDPrmoblgtn50prmassist[i]))\n",
    "#For test\n",
    "PartDPrmoblgtn50prmassist_3=[]\n",
    "for i in range(0,len(PartDBasicPremium3_2)):\n",
    "    PartDPrmoblgtn50prmassist_3.append(float(PartDPrmoblgtn50prmassist_2[i]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1[\"partd50\"]=PartDPrmoblgtn50prmassist_\n",
    "df3[\"partd50\"]=PartDPrmoblgtn50prmassist_3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {},
   "outputs": [],
   "source": [
    "#For train\n",
    "PartDPrmoblgtn25prmassist=[]\n",
    "\n",
    "for i, row in df.iterrows():\n",
    "    PartDPrmoblgtn25prmassist.append(re.sub('[!@#$]', '', row[\"PartD_Prm_oblgtn_25_prm_assist\"]))\n",
    "#For test\n",
    "PartDPrmoblgtn25prmassist_2=[]\n",
    "\n",
    "for i, row in df2.iterrows():\n",
    "    PartDPrmoblgtn25prmassist_2.append(re.sub('[!@#$]', '', row[\"PartD_Prm_oblgtn_25_prm_assist\"]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {},
   "outputs": [],
   "source": [
    "#For train\n",
    "PartDPrmoblgtn25prmassist_=[]\n",
    "for i in range(0,len(PartDBasicPremium3)):\n",
    "    PartDPrmoblgtn25prmassist_.append(float(PartDPrmoblgtn25prmassist[i]))\n",
    "#For test\n",
    "PartDPrmoblgtn25prmassist_3=[]\n",
    "for i in range(0,len(PartDBasicPremium3_2)):\n",
    "    PartDPrmoblgtn25prmassist_3.append(float(PartDPrmoblgtn25prmassist_2[i]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1[\"partd25\"]=PartDPrmoblgtn25prmassist_\n",
    "df3[\"partd25\"]=PartDPrmoblgtn25prmassist_3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {},
   "outputs": [],
   "source": [
    "#For train\n",
    "PartDTotalPremium5=[]\n",
    "\n",
    "for i, row in df.iterrows():\n",
    "    PartDTotalPremium5.append(re.sub('[!@#$]', '', row[\"PartD_Total_Premium5\"]))\n",
    "#For test\n",
    "PartDTotalPremium5_2=[]\n",
    "\n",
    "for i, row in df2.iterrows():\n",
    "    PartDTotalPremium5_2.append(re.sub('[!@#$]', '', row[\"PartD_Total_Premium5\"]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {},
   "outputs": [],
   "source": [
    "#For train\n",
    "PartDTotalPremium5_=[]\n",
    "for i in range(0,len(PartDBasicPremium3)):\n",
    "    PartDTotalPremium5_.append(float(PartDTotalPremium5[i]))\n",
    "#For test\n",
    "PartDTotalPremium5_3=[]\n",
    "for i in range(0,len(PartDBasicPremium3_2)):\n",
    "    PartDTotalPremium5_3.append(float(PartDTotalPremium5_2[i]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1[\"partdtotal5\"]=PartDTotalPremium5_\n",
    "df3[\"partdtotal5\"]=PartDTotalPremium5_3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {},
   "outputs": [],
   "source": [
    "#For train\n",
    "PartDDrugDeductible=[]\n",
    "\n",
    "for i, row in df.iterrows():\n",
    "    PartDDrugDeductible.append(re.sub('[!@#$]', '', row[\"PartD_Drug_Deductible\"]))\n",
    "#For test\n",
    "PartDDrugDeductible_2=[]\n",
    "\n",
    "for i, row in df2.iterrows():\n",
    "    PartDDrugDeductible_2.append(re.sub('[!@#$]', '', row[\"PartD_Drug_Deductible\"]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {},
   "outputs": [],
   "source": [
    "#For train\n",
    "PartDDrugDeductible_=[]\n",
    "for i in range(0,len(PartDBasicPremium3)):\n",
    "    PartDDrugDeductible_.append(float(PartDDrugDeductible[i]))\n",
    "#For test\n",
    "PartDDrugDeductible_3=[]\n",
    "for i in range(0,len(PartDBasicPremium3_2)):\n",
    "    PartDDrugDeductible_3.append(float(PartDDrugDeductible_2[i]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1[\"partddrugdeuctible\"]=PartDDrugDeductible_\n",
    "df3[\"partddrugdeuctible\"]=PartDDrugDeductible_3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1.drop([\"PartC_Premium2\",\"PartD_Prm_oblgtn_25_prm_assist\",\"PartD_Prm_oblgtn_50_prm_assist\",\"PartD_Prm_oblgtn_75_prm_assist\",\"PartD_Prm_oblgtn_Full_prm_assist\",\"PartD_Supplemental_Premium4\",],axis=1,inplace=True)\n",
    "df3.drop([\"PartC_Premium2\",\"PartD_Prm_oblgtn_25_prm_assist\",\"PartD_Prm_oblgtn_50_prm_assist\",\"PartD_Prm_oblgtn_75_prm_assist\",\"PartD_Prm_oblgtn_Full_prm_assist\",\"PartD_Supplemental_Premium4\",],axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1.drop([\"PartD_Total_Premium5\",\"PartD_Basic_Premium3\",\"PartD_Drug_Deductible\"],axis=1,inplace=True)\n",
    "df3.drop([\"PartD_Total_Premium5\",\"PartD_Basic_Premium3\",\"PartD_Drug_Deductible\"],axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1.drop([\"Annual_Drug_Deductible\",\"star_rating\"],axis=1,inplace=True)\n",
    "df3.drop([\"Annual_Drug_Deductible\",\"star_rating\"],axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1[\"Drug_Benefit_TypeNA\"]=df[\"Drug_Benefit_Type\"].isna()\n",
    "df3[\"Drug_Benefit_TypeNA\"]=df2[\"Drug_Benefit_Type\"].isna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1[\"Drug_Benefit_Type\"].fillna(\"NA\",inplace=True)\n",
    "df3[\"Drug_Benefit_Type\"].fillna(\"NA\",inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1[\"PartD\"].fillna(\"Yes\",inplace=True)\n",
    "df3[\"PartD\"].fillna(\"Yes\",inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>County</th>\n",
       "      <th>State</th>\n",
       "      <th>key_Segment1</th>\n",
       "      <th>bid_id</th>\n",
       "      <th>Plan_Name</th>\n",
       "      <th>Drug_Benefit_Type</th>\n",
       "      <th>In_network_MOOP_Amount</th>\n",
       "      <th>Overall_Star_Rating</th>\n",
       "      <th>Add_Cvrg_Offrd_in_Gap</th>\n",
       "      <th>PartD</th>\n",
       "      <th>...</th>\n",
       "      <th>monthlycd</th>\n",
       "      <th>partdbasicpremium3</th>\n",
       "      <th>partcpremium2</th>\n",
       "      <th>partdsupplementalpremium4</th>\n",
       "      <th>partdfull</th>\n",
       "      <th>partd50</th>\n",
       "      <th>partd25</th>\n",
       "      <th>partdtotal5</th>\n",
       "      <th>partddrugdeuctible</th>\n",
       "      <th>Drug_Benefit_TypeNA</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Autauga</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>H4513-044-000</td>\n",
       "      <td>H4513_044_0</td>\n",
       "      <td>Cigna-HealthSpring TotalCare (HMO SNP)</td>\n",
       "      <td>Basic</td>\n",
       "      <td>$6,700.00</td>\n",
       "      <td>4.5 Stars</td>\n",
       "      <td>No</td>\n",
       "      <td>Yes</td>\n",
       "      <td>...</td>\n",
       "      <td>27.1</td>\n",
       "      <td>27.1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>13.5</td>\n",
       "      <td>20.3</td>\n",
       "      <td>27.1</td>\n",
       "      <td>415.0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Autauga</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>H4513-045-000</td>\n",
       "      <td>H4513_045_0</td>\n",
       "      <td>Cigna-HealthSpring Advantage (HMO)</td>\n",
       "      <td>NA</td>\n",
       "      <td>$6,700.00</td>\n",
       "      <td>4.5 Stars</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Autauga</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>H4513-046-001</td>\n",
       "      <td>H4513_046_1</td>\n",
       "      <td>Cigna-HealthSpring Preferred (HMO)</td>\n",
       "      <td>Enhanced</td>\n",
       "      <td>$6,700.00</td>\n",
       "      <td>4.5 Stars</td>\n",
       "      <td>No</td>\n",
       "      <td>Yes</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>200.0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Autauga</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>H4513-047-000</td>\n",
       "      <td>H4513_047_0</td>\n",
       "      <td>Cigna-HealthSpring PreferredPlus Direct (HMO)</td>\n",
       "      <td>Enhanced</td>\n",
       "      <td>$4,900.00</td>\n",
       "      <td>4.5 Stars</td>\n",
       "      <td>No</td>\n",
       "      <td>Yes</td>\n",
       "      <td>...</td>\n",
       "      <td>49.0</td>\n",
       "      <td>20.2</td>\n",
       "      <td>28.8</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10.1</td>\n",
       "      <td>15.1</td>\n",
       "      <td>20.2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Autauga</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>H5521-224-000</td>\n",
       "      <td>H5521_224_0</td>\n",
       "      <td>Aetna Medicare Freedom Plan (PPO)</td>\n",
       "      <td>Enhanced</td>\n",
       "      <td>$5,700.00</td>\n",
       "      <td>4 Stars</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Yes</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>95.0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 42 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    County    State   key_Segment1       bid_id  \\\n",
       "0  Autauga  Alabama  H4513-044-000  H4513_044_0   \n",
       "1  Autauga  Alabama  H4513-045-000  H4513_045_0   \n",
       "2  Autauga  Alabama  H4513-046-001  H4513_046_1   \n",
       "3  Autauga  Alabama  H4513-047-000  H4513_047_0   \n",
       "4  Autauga  Alabama  H5521-224-000  H5521_224_0   \n",
       "\n",
       "                                       Plan_Name Drug_Benefit_Type  \\\n",
       "0         Cigna-HealthSpring TotalCare (HMO SNP)             Basic   \n",
       "1             Cigna-HealthSpring Advantage (HMO)                NA   \n",
       "2             Cigna-HealthSpring Preferred (HMO)          Enhanced   \n",
       "3  Cigna-HealthSpring PreferredPlus Direct (HMO)          Enhanced   \n",
       "4              Aetna Medicare Freedom Plan (PPO)          Enhanced   \n",
       "\n",
       "  In_network_MOOP_Amount Overall_Star_Rating Add_Cvrg_Offrd_in_Gap PartD  \\\n",
       "0              $6,700.00           4.5 Stars                    No   Yes   \n",
       "1              $6,700.00           4.5 Stars                   NaN    No   \n",
       "2              $6,700.00           4.5 Stars                    No   Yes   \n",
       "3              $4,900.00           4.5 Stars                    No   Yes   \n",
       "4              $5,700.00             4 Stars                   Yes   Yes   \n",
       "\n",
       "          ...         monthlycd partdbasicpremium3 partcpremium2  \\\n",
       "0         ...              27.1               27.1           0.0   \n",
       "1         ...               0.0                0.0           0.0   \n",
       "2         ...               0.0                0.0           0.0   \n",
       "3         ...              49.0               20.2          28.8   \n",
       "4         ...               0.0                0.0           0.0   \n",
       "\n",
       "  partdsupplementalpremium4 partdfull partd50 partd25 partdtotal5  \\\n",
       "0                       0.0       0.0    13.5    20.3        27.1   \n",
       "1                       0.0       0.0     0.0     0.0         0.0   \n",
       "2                       0.0       0.0     0.0     0.0         0.0   \n",
       "3                       0.0       0.0    10.1    15.1        20.2   \n",
       "4                       0.0       0.0     0.0     0.0         0.0   \n",
       "\n",
       "  partddrugdeuctible Drug_Benefit_TypeNA  \n",
       "0              415.0               False  \n",
       "1                0.0                True  \n",
       "2              200.0               False  \n",
       "3                0.0               False  \n",
       "4               95.0               False  \n",
       "\n",
       "[5 rows x 42 columns]"
      ]
     },
     "execution_count": 114,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1[\"Previous_Year\"].fillna(int(0),inplace=True)\n",
    "#df3['P'].fillna(int(0),inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1[\"Special_Needs_Plan_TypeNA\"]=df1[\"Special_Needs_Plan_Type\"].isna()\n",
    "df3[\"Special_Needs_Plan_TypeNA\"]=df3[\"Special_Needs_Plan_Type\"].isna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1['PartDDrugDeductibleNA']=df['PartD_Drug_Deductible'].isna()\n",
    "df3['PartDDrugDeductibleNA']=df2['PartD_Drug_Deductible'].isna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1[\"Special_Needs_Plan_Type\"].fillna(\"NA\",inplace=True)\n",
    "df3[\"Special_Needs_Plan_Type\"].fillna(\"NA\",inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1[\"Cardiovascular_Disorders\"].fillna(int(0),inplace=True)\n",
    "df3[\"Cardiovascular_Disorders\"].fillna(int(0),inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1[\"Dementia\"].fillna(int(0),inplace=True)\n",
    "df3[\"Dementia\"].fillna(int(0),inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1[\"Diabetes_Mellitus\"].fillna(int(0),inplace=True)\n",
    "df3[\"Diabetes_Mellitus\"].fillna(int(0),inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1[\"HIV_AIDS\"].fillna(int(0),inplace=True)\n",
    "df3[\"HIV_AIDS\"].fillna(int(0),inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1[\"Chronic_Lung_Disorders\"].fillna(int(0),inplace=True)\n",
    "df3[\"Chronic_Lung_Disorders\"].fillna(int(0),inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1[\"Chrnic_Mental_hlth_Cdtn\"].fillna(int(0),inplace=True)\n",
    "df3[\"Chrnic_Mental_hlth_Cdtn\"].fillna(int(0),inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1[\"Crdiovsculr_Disordrs_hrt_fail\"].fillna(int(0),inplace=True)\n",
    "df3[\"Crdiovsculr_Disordrs_hrt_fail\"].fillna(int(0),inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1[\"Cardio_Disordrs_hrtfail_diabetes\"].fillna(int(0),inplace=True)\n",
    "df3[\"Cardio_Disordrs_hrtfail_diabetes\"].fillna(int(0),inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1[\"End_stge_Renal_Disease_dialysis\"].fillna(int(0),inplace=True)\n",
    "df3[\"End_stge_Renal_Disease_dialysis\"].fillna(int(0),inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1[\"Chronic_Heart_Failure\"].fillna(int(0),inplace=True)\n",
    "df3[\"Chronic_Heart_Failure\"].fillna(int(0),inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1[\"Chronic_Heart_Fail_Diabetes\"].fillna(int(0),inplace=True)\n",
    "df3[\"Chronic_Heart_Fail_Diabetes\"].fillna(int(0),inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1[\"Add_Cvrg_Offrd_in_GapNA\"]=df1[\"Add_Cvrg_Offrd_in_Gap\"].isna()\n",
    "df3[\"Add_Cvrg_Offrd_in_GapNA\"]=df2[\"Add_Cvrg_Offrd_in_Gap\"].isna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1[\"Add_Cvrg_Offrd_in_Gap\"].fillna(\"NA\",inplace=True)\n",
    "df3[\"Add_Cvrg_Offrd_in_Gap\"].fillna(\"NA\",inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1.drop(\"Extra_Coverage_in_Gap\",axis=1,inplace=True)\n",
    "df3.drop(\"Extra_Coverage_in_Gap\",axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1.drop(\"Organization_Type\",axis=1,inplace=True)\n",
    "df3.drop(\"Organization_Type\",axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 134,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1[\"MMP\"].fillna(\"No\",inplace=True)\n",
    "df3[\"MMP\"].fillna(\"No\",inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 135,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1[\"Special_Needs_PlanNA\"]=df1[\"Special_Needs_Plan\"].isna()\n",
    "df3[\"Special_Needs_PlanNA\"]=df3[\"Special_Needs_Plan\"].isna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 136,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1[\"Special_Needs_Plan\"].fillna(\"NA\",inplace=True)\n",
    "df3[\"Special_Needs_Plan\"].fillna(\"NA\",inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 137,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1.drop(\"Benefit_Type\",axis=1,inplace=True)\n",
    "df3.drop(\"Benefit_Type\",axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 138,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1[\"PartD_Basic_Prm_Blw_Rgnl_bnchmrk\"].fillna(\"Above\",inplace=True)\n",
    "df3[\"PartD_Basic_Prm_Blw_Rgnl_bnchmrk\"].fillna(\"Above\",inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 139,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1[\"Tiers_Not_Subject_to_Deductible7\"].fillna(float(0.00),inplace=True)\n",
    "df3[\"Tiers_Not_Subject_to_Deductible7\"].fillna(float(0.00),inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1[\"Increased_Initial_Coverage_Limit\"].fillna(\"N\",inplace=True)\n",
    "df3[\"Increased_Initial_Coverage_Limit\"].fillna(\"N\",inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 141,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1[\"In_network_MOOP_AmountNA\"]=df1[\"In_network_MOOP_Amount\"].isna()\n",
    "df3[\"In_network_MOOP_AmountNA\"]=df3[\"In_network_MOOP_Amount\"].isna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 142,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1['In_network_MOOP_Amount'].fillna(\"NA\",inplace=True)\n",
    "df3['In_network_MOOP_Amount'].fillna(\"NA\",inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 143,
   "metadata": {},
   "outputs": [],
   "source": [
    "#For train\n",
    "MOOP=[]\n",
    "\n",
    "for i, row in df1.iterrows():\n",
    "    if (row['In_network_MOOP_Amount']!=\"NA\"):\n",
    "        MOOP.append(row['In_network_MOOP_Amount'])\n",
    "    elif (row['In_network_MOOP_Amount']==\"NA\") & (row[\"Add_Cvrg_Offrd_in_Gap\"]=='Yes'):\n",
    "        MOOP.append(\"$0.00\")\n",
    "    else:\n",
    "        MOOP.append(str(row[\"partddrugdeuctible\"]))\n",
    "#For test\n",
    "MOOP_2=[]\n",
    "\n",
    "for i, row in df3.iterrows():\n",
    "    if (row['In_network_MOOP_Amount']!=\"NA\"):\n",
    "        MOOP_2.append(row['In_network_MOOP_Amount'])\n",
    "    elif (row['In_network_MOOP_Amount']==\"NA\") & (row[\"Add_Cvrg_Offrd_in_Gap\"]=='Yes'):\n",
    "        MOOP_2.append(\"$0.00\")\n",
    "    else:\n",
    "        MOOP_2.append(str(row[\"partddrugdeuctible\"]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 144,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1[\"MOOP\"]=MOOP\n",
    "df3[\"MOOP\"]=MOOP_2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 145,
   "metadata": {},
   "outputs": [],
   "source": [
    "#For train\n",
    "MOOP1=[]\n",
    "\n",
    "for i, row in df1.iterrows():\n",
    "    MOOP1.append(re.sub('[!@#$,]', '', row[\"MOOP\"]))\n",
    "#For test\n",
    "MOOP1_2=[]\n",
    "\n",
    "for i, row in df3.iterrows():\n",
    "    MOOP1_2.append(re.sub('[!@#$,]', '', row[\"MOOP\"]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 146,
   "metadata": {},
   "outputs": [],
   "source": [
    "#For train\n",
    "MOOP2=[]\n",
    "for i in range(0,len(PartDBasicPremium3)):\n",
    "    MOOP2.append(float(MOOP1[i]))\n",
    "#For test\n",
    "MOOP2_2=[]\n",
    "for i in range(0,len(PartDBasicPremium3_2)):\n",
    "    MOOP2_2.append(float(MOOP1_2[i]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 147,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1[\"MOOPFINAL\"]=MOOP2\n",
    "df3[\"MOOPFINAL\"]=MOOP2_2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 148,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1.drop('In_network_MOOP_Amount',axis=1,inplace=True)\n",
    "df3.drop('In_network_MOOP_Amount',axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 149,
   "metadata": {},
   "outputs": [],
   "source": [
    "df3.drop(['MOOP'],axis=1,inplace=True)\n",
    "df1.drop(['MOOP'],axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 150,
   "metadata": {},
   "outputs": [],
   "source": [
    "#df['Annual_Drug_Deductible '].nunique()\n",
    "#df1['Overall_Star_Rating'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 151,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Combinning\n",
    "df1['Location']=df1['State'].astype(str)+\"_\"+df1['County'].astype(str)\n",
    "df3['Location']=df3['State'].astype(str)+\"_\"+df3['County'].astype(str)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h2>Mean Encoding</h2>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 152,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Key Segment Mean Encoding\n",
    "groupbykey=df1.groupby('key_Segment1')['Previous_Year'].mean()\n",
    "df1['keysegmentmean']=df1['key_Segment1'].map(groupbykey)\n",
    "df3['keysegmentmean']=df3['key_Segment1'].map(groupbykey)\n",
    "#df1.drop(['keysegmentmean'],axis=1,inplace=True)\n",
    "#df3.drop(['keysegmentmean'],axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 154,
   "metadata": {},
   "outputs": [],
   "source": [
    "mean_key=df3['keysegmentmean'].mean()\n",
    "df3['keysegmentmean'].fillna(value=mean_key,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 155,
   "metadata": {},
   "outputs": [],
   "source": [
    "#PartD Mean Encoding\n",
    "groupbypartd=df1.groupby('PartD')['Previous_Year'].mean()\n",
    "df1['PartDmean']=df1['PartD'].map(groupbypartd)\n",
    "df3['PartDmean']=df3['PartD'].map(groupbypartd)\n",
    "#df1.drop(['PartDmean'],axis=1,inplace=True)\n",
    "#df3.drop(['PartDmean'],axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 156,
   "metadata": {},
   "outputs": [],
   "source": [
    "mean_partd=df3['PartDmean'].mean()\n",
    "df3['PartDmean'].fillna(value=mean_partd,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 157,
   "metadata": {},
   "outputs": [],
   "source": [
    "#State Mean Encoding\n",
    "groupbystate=df1.groupby('State')['Previous_Year'].mean()\n",
    "df1['Statemean']=df1['State'].map(groupbystate)\n",
    "df3['Statemean']=df3['State'].map(groupbystate)\n",
    "#df1.drop(['Statemean'],axis=1,inplace=True)\n",
    "#df3.drop(['Statemean'],axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 158,
   "metadata": {},
   "outputs": [],
   "source": [
    "mean_state=df3['Statemean'].mean()\n",
    "df3['Statemean'].fillna(value=mean_state,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Organisation Name Encoding\n",
    "#groupbyorg=df1.groupby('Organization_Name')['enrollments_jan18'].mean()\n",
    "#df1['Orgmean']=df1['Organization_Name'].map(groupbyorg)\n",
    "#df3['Orgmean']=df3['Organization_Name'].map(groupbyorg)\n",
    "#df1.drop(['Orgmean'],axis=1,inplace=True)\n",
    "#df3.drop(['Orgmean'],axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#mean_org=df3['Orgmean'].mean()\n",
    "#df3['Orgmean'].fillna(value=mean_org,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 159,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Location Mean Encoding\n",
    "groupbyloc=df1.groupby('Location')['Previous_Year'].mean()\n",
    "df1['Locmean']=df1['Location'].map(groupbyloc)\n",
    "df3['Locmean']=df3['Location'].map(groupbyloc)\n",
    "#df1.drop(['Locmean'],axis=1,inplace=True)\n",
    "#df3.drop(['Locmean'],axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 160,
   "metadata": {},
   "outputs": [],
   "source": [
    "mean_loc=df3['Locmean'].mean()\n",
    "df3['Locmean'].fillna(value=mean_loc,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 161,
   "metadata": {},
   "outputs": [],
   "source": [
    "#df3.isna().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h3>Converting Categorical Variables</h3>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 162,
   "metadata": {},
   "outputs": [],
   "source": [
    "#df1.drop(['Organization_Name'],axis=1,inplace=True)\n",
    "#df3.drop(['Organization_Name'],axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 163,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1.drop(['Plan_Name'],axis=1,inplace=True)\n",
    "df3.drop(['Plan_Name'],axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 164,
   "metadata": {},
   "outputs": [],
   "source": [
    "#df1.drop(['Location'],axis=1,inplace=True)\n",
    "#df3.drop(['Location'],axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 165,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Shape of df1: (15862, 48)\n"
     ]
    }
   ],
   "source": [
    "#Didnt one hot encode state, county and Organisation_Name as they had 51, 1845 and 325 categories\n",
    "from sklearn.preprocessing import LabelEncoder\n",
    "cols=('Drug_Benefit_Type','PartD','Add_Cvrg_Offrd_in_Gap',\n",
    "      'MMP','PartD_Basic_Prm_Blw_Rgnl_bnchmrk','Increased_Initial_Coverage_Limit','plantype',\n",
    "      'Drug_Benefit_TypeNA','Add_Cvrg_Offrd_in_GapNA','Special_Needs_PlanNA',\n",
    "      'In_network_MOOP_AmountNA','Cardiovascular_Disorders',\n",
    "      'Chronic_Heart_Failure','Dementia','Diabetes_Mellitus','End_stge_Renal_Disease_dialysis',\n",
    "      'HIV_AIDS','Chronic_Lung_Disorders','Chrnic_Mental_hlth_Cdtn','Cardio_Disordrs_hrtfail_diabetes'\n",
    "     ,'Crdiovsculr_Disordrs_hrt_fail','Chronic_Heart_Fail_Diabetes','Overall_Star_Rating',\n",
    "     'Special_Needs_Plan','Special_Needs_Plan_Type','PartDDrugDeductibleNA','State',\n",
    "      'key_Segment1','County','Location','Special_Needs_Plan_TypeNA')\n",
    "for c in cols:\n",
    "    lbl=LabelEncoder()\n",
    "    lbl.fit(list(df1[c].values))\n",
    "    df1[c]=lbl.transform(list(df1[c].values))\n",
    "    df3[c]=lbl.transform(list(df3[c].values))\n",
    "    \n",
    "#Checking Shape\n",
    "print('Shape of df1: {}'.format(df1.shape))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 166,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1.drop(['County'],axis=1,inplace=True)\n",
    "df3.drop(['County'],axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 167,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Because they had 3 categories\n",
    "#Overall_Star_Rating=pd.get_dummies(df1['Overall_Star_Rating'],drop_first=True)\n",
    "#Special_Needs_Plan_Type=pd.get_dummies(df1['Special_Needs_Plan_Type'],drop_first=True)\n",
    "#Special_Needs_Plan=pd.get_dummies(df1['Special_Needs_Plan'],drop_first=True)\n",
    "#df1.drop(['Overall_Star_Rating','Special_Needs_Plan_Type','Special_Needs_Plan'],axis=1,inplace=True)\n",
    "#df1 = pd.concat([df1,Overall_Star_Rating,Special_Needs_Plan_Type,Special_Needs_Plan],axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 168,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Because they had 3 categories\n",
    "#Overall_Star_Rating=pd.get_dummies(df3['Overall_Star_Rating'],drop_first=True)\n",
    "#Special_Needs_Plan_Type=pd.get_dummies(df3['Special_Needs_Plan_Type'],drop_first=True)\n",
    "#Special_Needs_Plan=pd.get_dummies(df3['Special_Needs_Plan'],drop_first=True)\n",
    "#df3.drop(['Overall_Star_Rating','Special_Needs_Plan_Type','Special_Needs_Plan'],axis=1,inplace=True)\n",
    "#df3 = pd.concat([df3,Overall_Star_Rating,Special_Needs_Plan_Type,Special_Needs_Plan],axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 169,
   "metadata": {},
   "outputs": [],
   "source": [
    "#df3[\"Organization_Name\"].nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 170,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>State</th>\n",
       "      <th>key_Segment1</th>\n",
       "      <th>bid_id</th>\n",
       "      <th>Drug_Benefit_Type</th>\n",
       "      <th>Overall_Star_Rating</th>\n",
       "      <th>Add_Cvrg_Offrd_in_Gap</th>\n",
       "      <th>PartD</th>\n",
       "      <th>Special_Needs_Plan_Type</th>\n",
       "      <th>Cardiovascular_Disorders</th>\n",
       "      <th>Chronic_Heart_Failure</th>\n",
       "      <th>...</th>\n",
       "      <th>PartDDrugDeductibleNA</th>\n",
       "      <th>Add_Cvrg_Offrd_in_GapNA</th>\n",
       "      <th>Special_Needs_PlanNA</th>\n",
       "      <th>In_network_MOOP_AmountNA</th>\n",
       "      <th>MOOPFINAL</th>\n",
       "      <th>Location</th>\n",
       "      <th>keysegmentmean</th>\n",
       "      <th>PartDmean</th>\n",
       "      <th>Statemean</th>\n",
       "      <th>Locmean</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>869</td>\n",
       "      <td>H4513_044_0</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>6700.0</td>\n",
       "      <td>0</td>\n",
       "      <td>673.535714</td>\n",
       "      <td>86.892524</td>\n",
       "      <td>178.728873</td>\n",
       "      <td>93.166667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>870</td>\n",
       "      <td>H4513_045_0</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>6700.0</td>\n",
       "      <td>0</td>\n",
       "      <td>15.321429</td>\n",
       "      <td>16.780935</td>\n",
       "      <td>178.728873</td>\n",
       "      <td>93.166667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0</td>\n",
       "      <td>871</td>\n",
       "      <td>H4513_046_1</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>6700.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1448.928571</td>\n",
       "      <td>86.892524</td>\n",
       "      <td>178.728873</td>\n",
       "      <td>93.166667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0</td>\n",
       "      <td>873</td>\n",
       "      <td>H4513_047_0</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4900.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>86.892524</td>\n",
       "      <td>178.728873</td>\n",
       "      <td>93.166667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0</td>\n",
       "      <td>1253</td>\n",
       "      <td>H5521_224_0</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5700.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>86.892524</td>\n",
       "      <td>178.728873</td>\n",
       "      <td>93.166667</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 46 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   State  key_Segment1       bid_id  Drug_Benefit_Type  Overall_Star_Rating  \\\n",
       "0      0           869  H4513_044_0                  0                    4   \n",
       "1      0           870  H4513_045_0                  2                    4   \n",
       "2      0           871  H4513_046_1                  1                    4   \n",
       "3      0           873  H4513_047_0                  1                    4   \n",
       "4      0          1253  H5521_224_0                  1                    3   \n",
       "\n",
       "   Add_Cvrg_Offrd_in_Gap  PartD  Special_Needs_Plan_Type  \\\n",
       "0                      1      1                        1   \n",
       "1                      0      0                        3   \n",
       "2                      1      1                        3   \n",
       "3                      1      1                        3   \n",
       "4                      2      1                        3   \n",
       "\n",
       "   Cardiovascular_Disorders  Chronic_Heart_Failure    ...      \\\n",
       "0                         0                      1    ...       \n",
       "1                         0                      1    ...       \n",
       "2                         0                      1    ...       \n",
       "3                         0                      1    ...       \n",
       "4                         0                      1    ...       \n",
       "\n",
       "   PartDDrugDeductibleNA  Add_Cvrg_Offrd_in_GapNA  Special_Needs_PlanNA  \\\n",
       "0                      0                        0                     0   \n",
       "1                      0                        1                     1   \n",
       "2                      0                        0                     0   \n",
       "3                      0                        0                     0   \n",
       "4                      0                        0                     0   \n",
       "\n",
       "   In_network_MOOP_AmountNA  MOOPFINAL  Location  keysegmentmean  PartDmean  \\\n",
       "0                         0     6700.0         0      673.535714  86.892524   \n",
       "1                         0     6700.0         0       15.321429  16.780935   \n",
       "2                         0     6700.0         0     1448.928571  86.892524   \n",
       "3                         0     4900.0         0        0.000000  86.892524   \n",
       "4                         0     5700.0         0        0.000000  86.892524   \n",
       "\n",
       "    Statemean    Locmean  \n",
       "0  178.728873  93.166667  \n",
       "1  178.728873  93.166667  \n",
       "2  178.728873  93.166667  \n",
       "3  178.728873  93.166667  \n",
       "4  178.728873  93.166667  \n",
       "\n",
       "[5 rows x 46 columns]"
      ]
     },
     "execution_count": 170,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 171,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1[\"YearlyMin\"]=df1[\"monthlycd\"]*12\n",
    "df3[\"YearlyMin\"]=df3[\"monthlycd\"]*12"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 172,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1[\"YearlyMax\"]=df1[\"YearlyMin\"]+df1[\"MOOPFINAL\"]\n",
    "df3[\"YearlyMax\"]=df3[\"YearlyMin\"]+df3[\"MOOPFINAL\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h1>Modeling</h1>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 173,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Normal one\n",
    "final=df1.drop([\"Previous_Year\",'bid_id'],axis=1)\n",
    "y=df1['Previous_Year']\n",
    "test=df3.drop(['bid_id'],axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 174,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Tiers_Not_Subject_to_Deductible7', 'monthlycd', 'partdbasicpremium3',\n",
       "       'partcpremium2', 'partdsupplementalpremium4', 'partdfull', 'partd50',\n",
       "       'partd25', 'partdtotal5', 'partddrugdeuctible', 'MOOPFINAL',\n",
       "       'keysegmentmean', 'PartDmean', 'Statemean', 'Locmean', 'YearlyMin',\n",
       "       'YearlyMax'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 174,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final.select_dtypes(include=['float64']).columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 175,
   "metadata": {},
   "outputs": [],
   "source": [
    "test[\"keysegmentmean\"]=test[\"keysegmentmean\"]+205\n",
    "test[\"PartDmean\"]=test[\"PartDmean\"]\n",
    "test[\"Statemean\"]=test[\"Statemean\"]+117\n",
    "test[\"Locmean\"]=test[\"Locmean\"]+61"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 176,
   "metadata": {},
   "outputs": [],
   "source": [
    "from datetime import datetime\n",
    "from sklearn.model_selection import RandomizedSearchCV, GridSearchCV\n",
    "from sklearn.metrics import roc_auc_score\n",
    "from sklearn.model_selection import KFold"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 177,
   "metadata": {},
   "outputs": [],
   "source": [
    "def timer(start_time=None):\n",
    "    if not start_time:\n",
    "        start_time = datetime.now()\n",
    "        return start_time\n",
    "    elif start_time:\n",
    "        thour, temp_sec = divmod((datetime.now() - start_time).total_seconds(), 3600)\n",
    "        tmin, tsec = divmod(temp_sec, 60)\n",
    "        print('\\n Time taken: %i hours %i minutes and %s seconds.' % (thour, tmin, round(tsec, 2)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 178,
   "metadata": {},
   "outputs": [],
   "source": [
    "params = {\n",
    "        'min_child_weight': [1, 5, 10],\n",
    "        'gamma': [0.5, 1, 1.5, 2, 5],\n",
    "        'subsample': [0.6, 0.8, 1.0],\n",
    "        'colsample_bytree': [0.6, 0.8, 1.0],\n",
    "        'max_depth': [3, 4, 5]\n",
    "        }"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 201,
   "metadata": {},
   "outputs": [],
   "source": [
    "folds = 3\n",
    "param_comb = 5\n",
    "\n",
    "skf = KFold(n_splits=folds, shuffle = True, random_state = 1001)\n",
    "\n",
    "random_search = RandomizedSearchCV(rfc, param_distributions=params, n_iter=param_comb, scoring=\"neg_mean_absolute_error\", n_jobs=4, cv=final, verbose=3, random_state=1001 )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "start_time = timer(None)\n",
    "random_search.fit(final, y)\n",
    "timer(start_time)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h2>Random Forest</h2>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 179,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\samde\\Miniconda3\\envs\\AI\\lib\\site-packages\\sklearn\\ensemble\\weight_boosting.py:29: DeprecationWarning: numpy.core.umath_tests is an internal NumPy module and should not be imported. It will be removed in a future NumPy release.\n",
      "  from numpy.core.umath_tests import inner1d\n"
     ]
    }
   ],
   "source": [
    "from sklearn.ensemble import RandomForestRegressor"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 180,
   "metadata": {},
   "outputs": [],
   "source": [
    "rfc=RandomForestRegressor(n_estimators=500)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 181,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "RandomForestRegressor(bootstrap=True, criterion='mse', max_depth=None,\n",
       "           max_features='auto', max_leaf_nodes=None,\n",
       "           min_impurity_decrease=0.0, min_impurity_split=None,\n",
       "           min_samples_leaf=1, min_samples_split=2,\n",
       "           min_weight_fraction_leaf=0.0, n_estimators=500, n_jobs=1,\n",
       "           oob_score=False, random_state=None, verbose=0, warm_start=False)"
      ]
     },
     "execution_count": 181,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rfc.fit(final,y) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 182,
   "metadata": {},
   "outputs": [],
   "source": [
    "predictions_rfc=rfc.predict(test)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 183,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "299.83670747698903"
      ]
     },
     "execution_count": 183,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "predictions_rfc.mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 184,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "74.60465262892447"
      ]
     },
     "execution_count": 184,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "y.mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 185,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['State', 'key_Segment1', 'Drug_Benefit_Type', 'Overall_Star_Rating',\n",
       "       'Add_Cvrg_Offrd_in_Gap', 'PartD', 'Special_Needs_Plan_Type',\n",
       "       'Cardiovascular_Disorders', 'Chronic_Heart_Failure', 'Dementia',\n",
       "       'Diabetes_Mellitus', 'End_stge_Renal_Disease_dialysis', 'HIV_AIDS',\n",
       "       'Chronic_Lung_Disorders', 'Chrnic_Mental_hlth_Cdtn',\n",
       "       'Crdiovsculr_Disordrs_hrt_fail', 'Chronic_Heart_Fail_Diabetes',\n",
       "       'Cardio_Disordrs_hrtfail_diabetes', 'MMP', 'Special_Needs_Plan',\n",
       "       'PartD_Basic_Prm_Blw_Rgnl_bnchmrk', 'Tiers_Not_Subject_to_Deductible7',\n",
       "       'Increased_Initial_Coverage_Limit', 'plantype', 'monthlycd',\n",
       "       'partdbasicpremium3', 'partcpremium2', 'partdsupplementalpremium4',\n",
       "       'partdfull', 'partd50', 'partd25', 'partdtotal5', 'partddrugdeuctible',\n",
       "       'Drug_Benefit_TypeNA', 'Special_Needs_Plan_TypeNA',\n",
       "       'PartDDrugDeductibleNA', 'Add_Cvrg_Offrd_in_GapNA',\n",
       "       'Special_Needs_PlanNA', 'In_network_MOOP_AmountNA', 'MOOPFINAL',\n",
       "       'Location', 'keysegmentmean', 'PartDmean', 'Statemean', 'Locmean',\n",
       "       'YearlyMin', 'YearlyMax'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 185,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 187,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Feature ranking:\n",
      "1. feature 41 (0.581082)\n",
      "2. feature 44 (0.309821)\n",
      "3. feature 1 (0.019638)\n",
      "4. feature 40 (0.019624)\n",
      "5. feature 43 (0.015989)\n",
      "6. feature 32 (0.009748)\n",
      "7. feature 3 (0.008386)\n",
      "8. feature 46 (0.008290)\n",
      "9. feature 0 (0.005985)\n",
      "10. feature 39 (0.004830)\n",
      "11. feature 21 (0.002783)\n",
      "12. feature 4 (0.002465)\n",
      "13. feature 24 (0.001450)\n",
      "14. feature 45 (0.001321)\n",
      "15. feature 25 (0.001302)\n",
      "16. feature 31 (0.001189)\n",
      "17. feature 30 (0.001099)\n",
      "18. feature 29 (0.001066)\n",
      "19. feature 26 (0.000898)\n",
      "20. feature 6 (0.000604)\n",
      "21. feature 19 (0.000523)\n",
      "22. feature 28 (0.000501)\n",
      "23. feature 23 (0.000371)\n",
      "24. feature 34 (0.000315)\n",
      "25. feature 20 (0.000268)\n",
      "26. feature 2 (0.000202)\n",
      "27. feature 22 (0.000118)\n",
      "28. feature 27 (0.000030)\n",
      "29. feature 10 (0.000015)\n",
      "30. feature 37 (0.000014)\n",
      "31. feature 5 (0.000014)\n",
      "32. feature 42 (0.000013)\n",
      "33. feature 36 (0.000013)\n",
      "34. feature 38 (0.000012)\n",
      "35. feature 33 (0.000012)\n",
      "36. feature 17 (0.000009)\n",
      "37. feature 11 (0.000001)\n",
      "38. feature 18 (0.000000)\n",
      "39. feature 35 (0.000000)\n",
      "40. feature 16 (0.000000)\n",
      "41. feature 7 (0.000000)\n",
      "42. feature 15 (0.000000)\n",
      "43. feature 9 (0.000000)\n",
      "44. feature 12 (0.000000)\n",
      "45. feature 13 (0.000000)\n",
      "46. feature 14 (0.000000)\n",
      "47. feature 8 (0.000000)\n"
     ]
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAXcAAAEICAYAAACktLTqAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADl0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uIDMuMC4xLCBodHRwOi8vbWF0cGxvdGxpYi5vcmcvDW2N/gAAIABJREFUeJzt3XuYHVWd7vHvSy4oF0GhkUsCiRoZGUYutoFnVOgR0IASQEWDouANdYyIjBeQmYgo5+DlyNERLwgKg4MR4TwQNRpQjDoomEYjkoRIE4Npo9BAEpB7wu/8sdZOr67s7t7d6aST4v08z356V61Vq9aqWvWrVVW791ZEYGZm9bLNaFfAzMxGnoO7mVkNObibmdWQg7uZWQ05uJuZ1ZCDu5lZDTm429OCpK9J+o/RrofZ5iJ/zt0GImk58FxgXTH7hRGxciPK7AC+HRETNq52WydJlwHdEfHvo10Xqy+P3K0Vx0bEDsVr2IF9JEgaO5rr3xiSxox2HezpwcHdhk3SoZJ+JWm1pN/nEXkj7e2Slkh6SNIySe/J87cHfgTsKenv+bWnpMskfbpYvkNSdzG9XNLHJN0GPCxpbF7uGkk9kv4k6fQB6rq+/EbZkj4q6V5Jf5V0vKRjJP1R0gOSPl4se66kqyV9N7fnt5IOKNJfJGl+3g6LJE2vrPerkuZKehh4J/AW4KO57d/P+c6SdFcuf7GkE4oyTpX0P5I+L2lVbuvRRfpzJH1L0sqcfm2R9lpJC3PdfiXpxUXaxyT9Ja9zqaQjWtjttrWICL/86vcFLAeObDJ/L+B+4BjSIOGoPN2W018DPB8QcDjwCHBwTusg3ZYoy7sM+HQx3SdPrsdCYCLwzLzOW4FZwHjgecAy4NX9tGN9+bnstXnZccC7gR7gSmBH4B+Bx4Dn5fznAk8Cb8j5Pwz8Kb8fB3QBH8/1eCXwELBvsd41wMtynZ9RbWvOdyKwZ87zJuBhYI+cdmpe/7uBMcD7gJX03lb9IfBd4Nm5Pofn+QcD9wKH5OVOydtxW2BfYAWwZ847CXj+aPc3v0bu5ZG7teLaPPJbXYwKTwbmRsTciHgqIm4AOknBnoj4YUTcFcnPgeuBV2xkPb4UESsi4lHgpaQTyXkR8URELAO+AcxosawngfMj4klgNrAr8MWIeCgiFgGLgBcX+W+NiKtz/i+QgvSh+bUDcEGux43AD4CTimWvi4ib8nZ6rFllIuJ7EbEy5/kucCcwtchyd0R8IyLWAZcDewDPlbQHcDTw3ohYFRFP5u0N6WTw9Yi4JSLWRcTlwOO5zutIQX4/SeMiYnlE3NXitrOtgIO7teL4iNg5v47P8/YBTiyC/mrg5aSgg6SjJd2cb3GsJgX9XTeyHiuK9/uQbu2U6/846eFvK+7PgRLg0fz3niL9UVLQ3mDdEfEU0E0aae8JrMjzGu4mXdk0q3dTkt5W3D5ZDexP3+31t2L9j+S3O5CuZB6IiFVNit0H+LfKNppIGq13AWeQrkrulTRb0p6D1dO2Hg7uNlwrgCuKoL9zRGwfERdI2ha4Bvg88NyI2BmYS7pFA9DsI1oPA9sV07s3yVMutwL4U2X9O0bEMRvdsuYmNt5I2gaYQLo1shKYmOc17A38pZ96bzAtaR/SVcdMYJe8vW6nd3sNZAXwHEk795N2fmUbbRcR3wGIiCsj4uWkk0AAn2lhfbaVcHC34fo2cKykV0saI+kZ+UHlBNK9521J97HX5od/ryqWvQfYRdJOxbyFwDH54eDupFHlQH4DPJgfCj4z12F/SS8dsRb29RJJr1P6pM4ZpNsbNwO3kE5MH5U0Lj9UPpZ0q6c/95CeETRsTwquPZAeRpNG7oOKiL+SHlB/RdKzcx0Oy8nfAN4r6RAl20t6jaQdJe0r6ZX5RPwY6UplXT+rsa2Qg7sNS0SsAI4j3QrpIY0SPwJsExEPAacDVwGrgDcDc4pl7wC+AyzLtwv2BK4Afk964Hc96QHhQOtfRwqiB5Iebt4HXALsNNByG+E60oPOVcBbgdfl+9tPANNJ973vA74CvC23sT+Xku51r5Z0bUQsBv4P8GtS4P8n4KYh1O2tpGcId5AeoJ4BEBGdpPvuX8717iI9nIV08r0g1/lvwG6kfWk14X9iMhuEpHOBF0TEyaNdF7NWeeRuZlZDDu5mZjXk2zJmZjXkkbuZWQ2N2hcw7brrrjFp0qTRWr2Z2Vbp1ltvvS8i2gbLN2rBfdKkSXR2do7W6s3MtkqS7m4ln2/LmJnVUEvBXdK0/JWgXZLOapJ+Yf5ejIX5K1NXj3xVzcysVYPellH6cYGLSF/p2g0skDQn/1cdABHxoSL/B4CDNkFdzcysRa2M3KcCXRGxLP+r9WzSv5335yTSv5abmdkoaSW470Xfryztpu/Xma6Xv91uMnBjP+mnSeqU1NnT0zPUupqZWYtaCe7Nvna0v/98mgFcXXxPdt+FIi6OiPaIaG9rG/STPGZmNkytBPduiu+ypvd7rJuZgW/JmJmNulaC+wJgiqTJksaTAvicaiZJ+5J+w/HXI1tFMzMbqkGDe0SsJf1CzDxgCXBVRCySdF75K++kB6mzYyO/rKajo4OOjo6NKcLM7Gmvpf9QjYi5pJ9JK+fNqkyfO3LVMjOzjeH/UDUzqyEHdzOzGnJwNzOrIQd3M7MacnA3M6shB3czsxpycDczqyEHdzOzGnJwNzOrIQd3M7MacnA3M6shB3czsxpycDczqyEHdzOzGnJwNzOrIQd3M7MacnA3M6shB3czsxpycDczq6GWgrukaZKWSuqSdFY/ed4oabGkRZKuHNlqmpnZUAz6A9mSxgAXAUcB3cACSXMiYnGRZwpwNvCyiFglabdNVWEzMxtcKyP3qUBXRCyLiCeA2cBxlTzvBi6KiFUAEXHvyFbTzMyGopXgvhewopjuzvNKLwReKOkmSTdLmtasIEmnSeqU1NnT0zO8GpuZ2aBaCe5qMi8q02OBKUAHcBJwiaSdN1go4uKIaI+I9ra2tqHW1czMWtRKcO8GJhbTE4CVTfJcFxFPRsSfgKWkYG9mZqOgleC+AJgiabKk8cAMYE4lz7XAvwBI2pV0m2bZSFbUzMxaN2hwj4i1wExgHrAEuCoiFkk6T9L0nG0ecL+kxcDPgI9ExP2bqtJmZjawQT8KCRARc4G5lXmzivcBnJlfZmY2yloK7puF1Hw6qs9uzcxsMP76ATOzGnJwNzOrIQd3M7MacnA3M6shB3czsxpycDczqyEHdzOzGnJwNzOrIQd3M7MacnA3M6shB3czsxpycDczqyEHdzOzGnJwNzOrIQd3M7MacnA3M6shB3czsxpycDczqyEHdzOzGmopuEuaJmmppC5JZzVJP1VSj6SF+fWuka+qmZm1atAfyJY0BrgIOAroBhZImhMRiytZvxsRMzdBHc3MbIhaGblPBboiYllEPAHMBo7btNUyM7ON0Upw3wtYUUx353lVr5d0m6SrJU1sVpCk0yR1Surs6ekZRnXNzKwVrQR3NZkXlenvA5Mi4sXAT4DLmxUUERdHRHtEtLe1tQ2tpmZm1rJWgns3UI7EJwArywwRcX9EPJ4nvwG8ZGSqZ2Zmw9FKcF8ATJE0WdJ4YAYwp8wgaY9icjqwZOSqaGZmQzXop2UiYq2kmcA8YAzwzYhYJOk8oDMi5gCnS5oOrAUeAE7dhHU2M7NBDBrcASJiLjC3Mm9W8f5s4OyRrZqZmQ1XS8F9c5o/2hUwM6sBf/2AmVkNObibmdWQg7uZWQ05uJuZ1ZCDu5lZDTm4m5nVkIO7mVkNObibmdWQg7uZWQ05uJuZ1ZCDu5lZDTm4m5nVkIO7mVkNObibmdWQg7uZWQ05uJuZ1ZCDu5lZDTm4m5nVUEvBXdI0SUsldUk6a4B8b5AUktpHropmZjZUgwZ3SWOAi4Cjgf2AkyTt1yTfjsDpwC0jXUkzMxuaVkbuU4GuiFgWEU8As4HjmuT7FPBZ4LERrF8fHR0ddHR0bKrizcxqo5XgvhewopjuzvPWk3QQMDEifjBQQZJOk9QpqbOnp2fIlTUzs9a0EtzVZF6sT5S2AS4E/m2wgiLi4ohoj4j2tra21mtpZmZD0kpw7wYmFtMTgJXF9I7A/sB8ScuBQ4E5fqhqZjZ6WgnuC4ApkiZLGg/MAOY0EiNiTUTsGhGTImIScDMwPSI6N0mNzcxsUIMG94hYC8wE5gFLgKsiYpGk8yRN39QVNDOzoRvbSqaImAvMrcyb1U/ejo2vlpmZbQz/h6qZWQ05uJuZ1ZCDu5lZDTm4m5nVkIO7mVkNObibmdWQg7uZWQ05uJuZ1ZCDu5lZDTm4m5nVkIO7mVkNObibmdWQg7uZWQ05uJuZ1ZCDu5lZDTm4m5nVkIO7mVkNObibmdWQg7uZWQ21FNwlTZO0VFKXpLOapL9X0h8kLZT0P5L2G/mqmplZqwb9gWxJY4CLgKOAbmCBpDkRsbjIdmVEfC3nnw58AZg2YrWUmk9HjNgqzMzqpJWR+1SgKyKWRcQTwGzguDJDRDxYTG4POOqamY2iQUfuwF7AimK6GzikmknS+4EzgfHAK0ekdmZmNiytjNzVZN4GI/OIuCging98DPj3pgVJp0nqlNTZ09MztJqamVnLWgnu3cDEYnoCsHKA/LOB45slRMTFEdEeEe1tbW2t19LMzIakleC+AJgiabKk8cAMYE6ZQdKUYvI1wJ0jV0UzMxuqQe+5R8RaSTOBecAY4JsRsUjSeUBnRMwBZko6EngSWAWcsikrbWZmA2vlgSoRMReYW5k3q3j/wRGul5mZbQT/h6qZWQ05uJuZ1ZCDu5lZDTm4m5nVkIO7mVkNObibmdWQg7uZWQ05uJuZ1ZCDu5lZDTm4m5nVkIO7mVkNObibmdWQg7uZWQ05uJuZ1ZCDu5lZDTm4m5nVkIO7mVkNObibmdWQg7uZWQ219BuqW4r5o10BM7OtREsjd0nTJC2V1CXprCbpZ0paLOk2ST+VtM/IV9XMzFo1aHCXNAa4CDga2A84SdJ+lWy/A9oj4sXA1cBnR7qiZmbWulZG7lOBrohYFhFPALOB48oMEfGziHgkT94MTBjZapqZ2VC0Etz3AlYU0915Xn/eCfyoWYKk0yR1Surs6elpvZZmZjYkrQR3NZkXTTNKJwPtwOeapUfExRHRHhHtbW1trdfSzMyGpJVPy3QDE4vpCcDKaiZJRwLnAIdHxOMjUz0zMxuOVkbuC4ApkiZLGg/MAOaUGSQdBHwdmB4R9458Nc3MbCgGDe4RsRaYCcwDlgBXRcQiSedJmp6zfQ7YAfiepIWS5vRTnJmZbQYt/RNTRMwF5lbmzSreHznC9TIzs43grx8wM6shB3czsxpycDczqyEHdzOzGnJwNzOrIQd3M7MacnA3M6shB3czsxpycDczqyEHdzOzGnJwNzOrIQd3M7MacnA3M6shB3czsxpycDczqyEHdzOzGnJwNzOrIQd3M7MacnA3M6uhloK7pGmSlkrqknRWk/TDJP1W0lpJbxj5apqZ2VAMGtwljQEuAo4G9gNOkrRfJdufgVOBK0e6gmZmNnRjW8gzFeiKiGUAkmYDxwGLGxkiYnlOe2oT1NHMzIaoldsyewEriunuPG/IJJ0mqVNSZ09Pz3CKMDOzFrQS3NVkXgxnZRFxcUS0R0R7W1vbcIowM7MWtBLcu4GJxfQEYOWmqY6ZmY2EVoL7AmCKpMmSxgMzgDmbtlpmZrYxBg3uEbEWmAnMA5YAV0XEIknnSZoOIOmlkrqBE4GvS1q0KSttZmYDa+XTMkTEXGBuZd6s4v0C0u0aMzPbAvg/VM3MasjB3cyshhzczcxqyMHdzKyGHNzNzGrIwd3MrIYc3M3MasjB3cyshhzczcxqyMHdzKyGnhbBvaOjg46OjtGuhpnZZvO0CO5mZk83tQnuHp2bmfWqTXAfLp8UzKyOnvbB3cysjhzch8kjfjPbkjm4D2C4AdyB38xGW0u/xLRFk5pPRwycZmZWY1t/cB+uUQr8jRH9/Pnzh5RmZjYUvi1jZlZDLY3cJU0DvgiMAS6JiAsq6dsC/wW8BLgfeFNELB/Zqm5Gw73VswmvBjyqN7OhGDS4SxoDXAQcBXQDCyTNiYjFRbZ3Aqsi4gWSZgCfAd60KSq81dqEJ4VNcatnazmZbC31NNvcWhm5TwW6ImIZgKTZwHFAGdyPA87N768GvixJEZvvyeX8zbWizW1TXEXk9x05aX4jT3/LNXZjs+U2Ni3bUk5QfiZiddFKcN8LWFFMdwOH9JcnItZKWgPsAtxXZpJ0GnAawN577923hIHOAxub1vhYYnlQ1iitmLP1pWWbImAOp8yBlnFQt61JK8FdTeZVI2oreYiIi4GLAdrb2zffqH6YB+xwD+bNXWbdOeCaDV0rwb0bmFhMTwBW9pOnW9JYYCfggRGp4dOIA5WZjZRWgvsCYIqkycBfgBnAmyt55gCnAL8G3gDcuDnvt48GB2Iz25INGtzzPfSZwDzSRyG/GRGLJJ0HdEbEHOBS4ApJXaQR+4xNWWkzMxtYS59zj4i5wNzKvFnF+8eAE0e2amZmNlz+D1UzsxpycDczqyEHdzOzGnJwNzOrIQd3M7MacnA3M6shB3czsxrSaP0jqaQe4O5+knel8qVjTnPaZkzbUurhNKc1s09EtPWT1isitrgX6T9fnea0UUnbUurhNKdtzMu3ZczMasjB3cyshrbU4H6x05w2imlbSj2c5rRhG7UHqmZmtulsqSN3MzPbCA7uZmZ1tLEftxmJF+lHQH4H/CBPzwS6SL/DuluZVizzn8Df8/tvAvcCtw9Q5mTgFuBO4CpgYZF2KXAb8AiwGlgCfDKn/TewFLg9r2cc8AzgN8DvgUXAJ5usT8D5wB9zeacD03JZXcC/V8vIy70S+APw91yXRcAHc9qJefopoL1JW38C/Cyvb/1yRb4P5216W1HPy4A/Aw8BjwF3Fes7l/TrWw8Dj5L+L+GTxT66K5d3e6UNk3N9HgEeBBYXZR4A3Jrb91Cu6weLOn4gb6NFwGeBnYGrgTvy9nigsp8PIP0C2B2kzwU3lm2s70Dgt3ldjwLLirRP5W39cE5f2mhDTp9I+gnJpyplPge4gdSXbgD2r2z3TzTbD8Dn8r7foO3Ad4H7gSeBJ4CFlXV1AWuabOtfkvryQtLPX14LLM/tWgh0smHfbPTpRUAPG/ZBARfk7fUY8Nci7Yhc1sO5HXcWaZfm/bM213WHom0Lc/vWAY8W27jRp6PJvj0QuDnX72HSsVTW87K8HZ/MdT1wgH60b7GdFuZt/FBlfeeS+nsjzzHAB4ttfgbNY83nSP3vAeBxYHGR9inS8XZ/TrujSfxrHJe7Ah/K67od+A7wjGHH1dEO7LlxZwJXFp3vIGBS7qT/Uabl9HbgCnqD+2HAwZUNXi3zKmBGfv8rUmBtpD0rd+gdgC8AHyedCA7NO1j59R3gfY28edlxOe8XK+t7O/BfwDZ5endSMHweMD532PZKGf8MrABelttzHvCvpE69H/Ci3Enn0ze4N9p6A3BwnrdjY7kiUM3LHfAa+gb3dzVbjtTZP9ykrYcW++ju3CnLtKuA9+Y2fI10UDTKXACckNPeAXymSPsX0glq27y+3YDLgXcVJ77DKvt5AXA4sEeu76cqbbie9LOQB+d9+csirdzvp5MeYt0CHJrLfjXwQ1IQK8v8LHBWznMW8OXK9rsLeH2T7fkq0m8QH5zbfWFlHzX68X3ArDzvs3kdAmbl5dZv68pxdA3wNtJxs+sAx0LZp79H6tPl/mv03R1z/j2LtD+S+uEOpL55eZH2rEobzqrU7zDScXtPMa/Rp38HvKWyb68Hjs71fB2p35f1vIwUH6rH/gb9qFKPMaRgewwbBvcPF9P7k4LsdqQfNvoJvX2pXO5VOf2wXKeeIu1ZRds/CzxQqUvjuLw7r+9PwDOLmHXqcOPqqN+WkTQBeA1wSWNeRPwuIpaTdsJRZZqkMaQz5UeL/L+g+EHuapmSRAoMV+e0caQO01j+wUhb82HgmaTbVeNSUsyNjHRCmJAn/54XH0fa+f9c1pN0wJwXEU/l6clAV0Qsi4gngNm5bY0yxpFGNY9HxE0R8VtSsH4taYS3V0QsiYilA2y/x/NyRERjZLhXznoh8Pncvivoa/UAy1Fpa2O7NPZRVNPytr4kl3k56QBtlLkvcG3RvmOLtPcBF0TE47nMx0gHxaW5HjeSrjJK+wK/iIi/5nyvr7QhgHV5fTuRTp6N7flgsS+3J/WJcUDkfnYW6cRX3S7H5XaR/x5V2X63k65Y+iwXEddHRHfOezPp5LV+Wxf9eCfSQILGunL/+wZwfGVbAyBpx7zdry03Tj/HV9mnf0U64ZRlNvruQ3mR1UVakALW33M976G3TzxYtEFl/bJfkkb+a4q6NPr0msY2KzTWFcC2pCuTatuXUBz7WZ9+FBH3VtKPII20FzOwFwE3R8QjEbEW+DlpW/VZX96va3Pbf5Xr10hr9INf0Lv9SheSYllj/ljgmZLGkuLKykHq2K9RD+7A/yU17qkmac8h3fIo02YCc/LB3GqZu5AC2Nqc9gnSaHM9Sd8C/gacTDqob4iIW4r0ccBbgR/n6TGSFpIu0caRRqplPZ8PvElSp6QfAS8hBZaGbmBCUcYNpJPHOEntOc8bSCP9g0ijlVba2qjvpMZykqaTLjffA6xiww52vqTbJF0o6YWV9c3Mafc36llul+xnRRvuondbN9q5vi6kwDc9p50I7F2kvRB4haRbJP2cFPh7gG9J+p2kS0gnp1K1vIll20lXDZ+TtIJ0cruobJ+k83PaLOCkon19+lmlzOc25ue/uzXb7s2mC+8gXXVU06YCayPizjz93KKv30vqV832wwnAT3MwCeB6SbcC19HP8VX06TdWyiz77oOkfdBIexcwV1I36Tj617Iu+ThaQArG/1lZ5StII/onqnXpR3XfHdCk7ecDPwJ2l7RtntenH0l6aaXcGfSePKsa/f2bpIHEYZJ2kbQdaaQ/cZA6v5F0u2e9oo8dn+vfmD8d+EtE/D7P+ltu559Jt8LWRMT1g6yvX6Ma3CW9Frg3Im7tJ20d6fZFY96epAO42mkGK1NlGul+ZJ8AFxFvJ11+XkE6GKZK2r/I8hXSCPGXOf+6iDgQeCfp0vtx+toWeCwi2kkjrvc3qe5TuYwJpIP6H0kd70JJvyGNXPcGzmiMAFpoK5J2IF2in0G6/3kOcFNue/XAOhv4B+ClpCA1v1jfV0kH+oGk2yvXNtkukC6DG214USVtu0ob3gG8PweeXUjPLxppY4Fnky65P0K6jD0Y+GpEHES6snpfpfyyvB1z+64pynwf8KGImEg6af+o3J4RcU5OO490e2WqpMPYsJ+VZTZVbveIeLA6XeQ7h9T/3tqkzOmkkfIGImIdKXBMYMP9cBK9AetlEXEw6RbjPqSrkmYafXpKpcyy755KOgYbaR8CjomICaRbIv+vrEs+jg4hHQ9vqqzvJGBOP3Vpptx3HyIFvLKejb57HOkq/2N5uWo/uipfvSNpPGkbf6/J+sr+/lfSYOgzpEHLj/N2WNtkOXLZ55Bi1ppyftHHriX1efLJ4hzSoKJhp9yWyaRYtL2kkwfaQAOK0b3X/r9JI7vlpLPWI8C3i7S1pLNYI21Vfr88v54i3eqANDq8vZ8y/5s0Yrggp/2V1PnWr6+o0+HAD0ijkg/neZ/IO2abftqwhnSptr4NpMu+STmPSPdt5xXLnQ2cXUyvX1/03t/uJD9Yq6xzPum5Q39tnQecmfP+Eymor87bM0gdsE/b8/p+AyzqZ181tm+1nsvJ93dz2kfyth6by7yZ5g+RxpEu0/9czPsx0FEpu0x/BXAjxf3OSpn75X1xZjFvDb23W+aRglazZfcp2vcJ+vazAO4r8i4F9sjv98jTjfLPLNo3r6xLnn9K3iY3NEkbSxol3zHQuqr9hRQw7qfy8C33jwep9M3++nSjTDbsu2uKfXtXkX9v0q2Nap+YRHpw/YNK2+4hBdwN9h+pTx9L33vZa+j9XxwBD/ZzrEwi3atuPFOo9qO7gLb8/jjg+rJPD9TfK/P+F+lKpVnaKaQH+/8wQJkvI/c/eo/L5fm1Nu/7K4v8bwO+MtS42niN6sg9Is6OiAkRMYk0Yr0xIk5upJEC18FF2rMjYveImJSXeSQiXtBCmW8h3TpYGGnEcR1p1HIj8FZJL5DUJmlnUgfrAo4E7pD0LtKDtZMi3z8v8kIa8d1G2hFlG64l3QOFdMJYAkyRNDmPHhp1QtIzi/XtlkcZ3yKNpM8cyvYjBe4lEfGFnOcPEbFbROwcEWNJD25e16inpD3y+i4lBaTvN8rPaY22npDbcCTp4EdSG/nqr2jDktyuN+QytwO+VJS5W7G+XUgPsRrWb7N8e2gMcLekfXP6EaRPZ6wnabf8d5tc91sabc9W5u1/KekEu6hYdkrRvumkh4VHArdGxO6kEdQvgCcjoryNN4d0MJP/XpfLXxIRXyjat6Ssi6RppNHl3aSTaFlP8rrvou/ocA5wSt7W7wGuK/tLznMiKbA9Jmn7fP8d4NOkE9abKfpm0ac/QHoISqXMa4Hpebscnrd5Y9/uJOmQnHYU6eRzJLBUUnksPquoH0XZf6N1K4HDc9uPBe6sHCt7FHl3zG2FDfvReHq/YbG8wumjUt4JwO1F/9qbdNxssGyxX6eTrrbLtCnF5JHkK/ziuGzEsm5STDhA0na5Dx1B2ubDM9yzwki/gA56z7yn58auJe3gH1L5KGTO1/i0zHdIo/En83LvbFLm80gj0y7SJdlRpBH6NqRbFneSPk61ijQaaXxaYS3pgGt8PGoW8GLS0/3bSB1qVpP17Zzr/QfSGf0A0j27P+byvtRPGZ+jd7S4kr4fyzoht+9x0ihoXmX73UTvRx3XL1fZZstJo5dGPW+k9yONq4pljyHdompslzWkg3NWsY/uycs9SRodziq29eKctpp0OVt+tOzPOe3eSvvGk656bid9fPGVpEvkzlyvblJwWL+fc3l/LMq8rVLmy3O9gzRyXVqkXVO078GyfbkdL6f3Sqcscxfgp3nZn5IeGJfrvrOfunQV2+xRUsBZv49Io901lfbbBH6xAAAA30lEQVQ11nU36ZZM42NyZT3nA9OKbf97ej/eeE6Tvtno03fkbfI3+vbBnUkntUdJt8K6irQTim3297w9Z9F7HK3O9X+K9JyncSxeRroX3+c4pbdPr8uvp4q0l5M+Nrs01+POSj1vbLY+mvSjnH870hVO44F1tS5XkI7X20gn1T1IV5eL8/Y8op/lukjP01bl+euKtGtyPVaTAn+fGFU5LnclPWO8Iy9zBfkTP8N5+esHzMxqaEv4tIyZmY0wB3czsxpycDczqyEHdzOzGnJwNzOrIQd3M7MacnA3M6uh/w/XXtJeokGhIwAAAABJRU5ErkJggg==\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "importances = rfc.feature_importances_\n",
    "std = np.std([tree.feature_importances_ for tree in rfc.estimators_],\n",
    "             axis=0)\n",
    "indices = np.argsort(importances)[::-1]\n",
    "\n",
    "# Print the feature ranking\n",
    "print(\"Feature ranking:\")\n",
    "\n",
    "for f in range(final.shape[1]):\n",
    "    print(\"%d. feature %d (%f)\" % (f + 1, indices[f], importances[indices[f]]))\n",
    "\n",
    "# Plot the feature importances of the forest\n",
    "plt.figure()\n",
    "plt.title(\"Feature importances\")\n",
    "plt.bar(range(final.shape[1]), importances[indices],\n",
    "       color=\"r\", yerr=std[indices], align=\"center\")\n",
    "plt.xticks(range(final.shape[1]), indices)\n",
    "plt.xlim([-1, final.shape[1]])\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 188,
   "metadata": {},
   "outputs": [],
   "source": [
    "params = {\n",
    "        'min_child_weight': [1, 5, 10],\n",
    "        \"n_estimators\": [100,200,500,1000],\n",
    "        'subsample': [0.6, 0.8, 1.0],\n",
    "        'colsample_bytree': [0.6, 0.8, 1.0],\n",
    "        'max_depth': [3, 4, 5]\n",
    "        }"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 189,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.metrics import mean_absolute_error"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 194,
   "metadata": {},
   "outputs": [],
   "source": [
    "random_search = RandomizedSearchCV(rfc, param_distributions=params, n_iter=10, scoring='neg_mean_squared_error', n_jobs=4, cv=final, verbose=3, random_state=1001 )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 195,
   "metadata": {},
   "outputs": [],
   "source": [
    "importances = rfc.feature_importances_"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 196,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([5.98527778e-03, 1.96376070e-02, 2.01818634e-04, 8.38565653e-03,\n",
       "       2.46473423e-03, 1.35320757e-05, 6.04136514e-04, 0.00000000e+00,\n",
       "       0.00000000e+00, 0.00000000e+00, 1.50944999e-05, 1.06476254e-06,\n",
       "       0.00000000e+00, 0.00000000e+00, 0.00000000e+00, 0.00000000e+00,\n",
       "       0.00000000e+00, 8.70894075e-06, 2.75924370e-07, 5.22751419e-04,\n",
       "       2.67608136e-04, 2.78261202e-03, 1.18080208e-04, 3.71367452e-04,\n",
       "       1.45046821e-03, 1.30190846e-03, 8.98212679e-04, 2.95023092e-05,\n",
       "       5.00642211e-04, 1.06575299e-03, 1.09899476e-03, 1.18879881e-03,\n",
       "       9.74813693e-03, 1.15103952e-05, 3.15324017e-04, 0.00000000e+00,\n",
       "       1.25967503e-05, 1.44525334e-05, 1.21123356e-05, 4.83041379e-03,\n",
       "       1.96244755e-02, 5.81081823e-01, 1.31971179e-05, 1.59892119e-02,\n",
       "       3.09821227e-01, 1.32110508e-03, 8.28980769e-03])"
      ]
     },
     "execution_count": 196,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "importances"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h4>After dropping all the excessive columns</h4>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 197,
   "metadata": {},
   "outputs": [],
   "source": [
    "final2=df1.drop([\"Previous_Year\",'bid_id',\"YearlyMin\",\"YearlyMax\",'MOOPFINAL','partdbasicpremium3',\n",
    "       'partcpremium2', 'partdsupplementalpremium4', 'partdfull', 'partd50',\n",
    "       'partd25', 'partdtotal5','Cardiovascular_Disorders', 'Chronic_Heart_Failure', 'Dementia',\n",
    "       'Diabetes_Mellitus', 'End_stge_Renal_Disease_dialysis', 'HIV_AIDS',\n",
    "       'Chronic_Lung_Disorders', 'Chrnic_Mental_hlth_Cdtn',\n",
    "       'Crdiovsculr_Disordrs_hrt_fail', 'Chronic_Heart_Fail_Diabetes',\n",
    "       'Cardio_Disordrs_hrtfail_diabetes'],axis=1)\n",
    "y2=df1['Previous_Year']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 198,
   "metadata": {},
   "outputs": [],
   "source": [
    "test2=df3.drop(['bid_id',\"YearlyMin\",\"YearlyMax\",'MOOPFINAL','partdbasicpremium3',\n",
    "       'partcpremium2', 'partdsupplementalpremium4', 'partdfull', 'partd50',\n",
    "       'partd25', 'partdtotal5','Cardiovascular_Disorders', 'Chronic_Heart_Failure', 'Dementia',\n",
    "       'Diabetes_Mellitus', 'End_stge_Renal_Disease_dialysis', 'HIV_AIDS',\n",
    "       'Chronic_Lung_Disorders', 'Chrnic_Mental_hlth_Cdtn',\n",
    "     'Crdiovsculr_Disordrs_hrt_fail', 'Chronic_Heart_Fail_Diabetes',\n",
    "    'Cardio_Disordrs_hrtfail_diabetes'],axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 199,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "RandomForestRegressor(bootstrap=True, criterion='mse', max_depth=None,\n",
       "           max_features='auto', max_leaf_nodes=None,\n",
       "           min_impurity_decrease=0.0, min_impurity_split=None,\n",
       "           min_samples_leaf=1, min_samples_split=2,\n",
       "           min_weight_fraction_leaf=0.0, n_estimators=500, n_jobs=1,\n",
       "           oob_score=False, random_state=None, verbose=0, warm_start=False)"
      ]
     },
     "execution_count": 199,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rfc.fit(final2,y2) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 203,
   "metadata": {},
   "outputs": [],
   "source": [
    "test2[\"keysegmentmean\"]=test2[\"keysegmentmean\"]-205\n",
    "test2[\"PartDmean\"]=test2[\"PartDmean\"]-200\n",
    "test2[\"Statemean\"]=test2[\"Statemean\"]-117\n",
    "test2[\"Locmean\"]=test2[\"Locmean\"]-61"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 204,
   "metadata": {},
   "outputs": [],
   "source": [
    "predictions_rfc2=rfc.predict(test2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 205,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "46.04240814525281"
      ]
     },
     "execution_count": 205,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "predictions_rfc2.mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 206,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "74.60465262892447"
      ]
     },
     "execution_count": 206,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "y.mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h2>LightGBM</h2>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 207,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "LGBMRegressor(boosting_type='dart', class_weight='balanced',\n",
       "       colsample_bytree=0.9843467236959204,\n",
       "       learning_rate=0.11598629586769524, max_depth=-1,\n",
       "       min_child_samples=44, min_child_weight=0.001, min_split_gain=0.0,\n",
       "       n_estimators=5000, n_jobs=-1, num_leaves=49, objective='mape',\n",
       "       random_state=None, reg_alpha=0.35397370408131534,\n",
       "       reg_lambda=0.5904910774606467, silent=True,\n",
       "       subsample=0.6299872254632797, subsample_for_bin=60611,\n",
       "       subsample_freq=0)"
      ]
     },
     "execution_count": 207,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import lightgbm as lgb\n",
    "hyp_OPTaaS = { 'boosting_type': 'dart',\n",
    "              'colsample_bytree': 0.9843467236959204,\n",
    "              'learning_rate': 0.11598629586769524,\n",
    "              'min_child_samples': 44,\n",
    "              'num_leaves': 49,\n",
    "              'reg_alpha': 0.35397370408131534,\n",
    "              'reg_lambda': 0.5904910774606467,\n",
    "              'subsample': 0.6299872254632797,\n",
    "              'subsample_for_bin': 60611}\n",
    "model = lgb.LGBMRegressor(**hyp_OPTaaS, class_weight = 'balanced',objective = 'mape', n_jobs = -1, n_estimators = 5000)\n",
    "model.fit(final, y)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 208,
   "metadata": {},
   "outputs": [],
   "source": [
    "predictions_lgb=model.predict(test)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 209,
   "metadata": {},
   "outputs": [],
   "source": [
    "predict_lgb=pd.DataFrame(data=predictions_lgb)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 210,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1771.2730814528188"
      ]
     },
     "execution_count": 210,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "predict_lgb[0].max()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h2>StackNET</h2>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 215,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pystacknet.pystacknet import StackNetRegressor\n",
    "from sklearn.ensemble import RandomForestRegressor,GradientBoostingRegressor,ExtraTreesRegressor, AdaBoostRegressor\n",
    "from sklearn.tree import DecisionTreeRegressor\n",
    "from sklearn.neighbors import KNeighborsRegressor"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 225,
   "metadata": {},
   "outputs": [],
   "source": [
    "models=[[KNeighborsRegressor (n_neighbors=300), GradientBoostingRegressor (n_estimators=300),\n",
    "         ExtraTreesRegressor (n_estimators=300) , DecisionTreeRegressor (),\n",
    "         AdaBoostRegressor (n_estimators=300)\n",
    "        ],\n",
    "        [RandomForestRegressor (n_estimators=400)]\n",
    "       ]\n",
    "        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 226,
   "metadata": {},
   "outputs": [],
   "source": [
    "model=StackNetRegressor(models, folds=4,restacking=True, n_jobs=1, verbose=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 227,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "====================== Start of Level 0 ======================\n",
      "Input Dimensionality 47 at Level 0 \n",
      "5 models included in Level 0 \n",
      "Fold 1/4 , model 0 , rmse===419.324171 \n",
      "Fold 1/4 , model 1 , rmse===232.116840 \n",
      "Fold 1/4 , model 2 , rmse===198.049157 \n",
      "Fold 1/4 , model 3 , rmse===276.094604 \n",
      "Fold 1/4 , model 4 , rmse===253.049176 \n",
      "=========== end of fold 1 in level 0 ===========\n",
      "Fold 2/4 , model 0 , rmse===360.735641 \n",
      "Fold 2/4 , model 1 , rmse===590.848489 \n",
      "Fold 2/4 , model 2 , rmse===591.655299 \n",
      "Fold 2/4 , model 3 , rmse===673.628612 \n",
      "Fold 2/4 , model 4 , rmse===728.253558 \n",
      "=========== end of fold 2 in level 0 ===========\n",
      "Fold 3/4 , model 0 , rmse===750.443833 \n",
      "Fold 3/4 , model 1 , rmse===588.147494 \n",
      "Fold 3/4 , model 2 , rmse===613.664984 \n",
      "Fold 3/4 , model 3 , rmse===698.860320 \n",
      "Fold 3/4 , model 4 , rmse===652.576331 \n",
      "=========== end of fold 3 in level 0 ===========\n",
      "Fold 4/4 , model 0 , rmse===522.842170 \n",
      "Fold 4/4 , model 1 , rmse===257.502241 \n",
      "Fold 4/4 , model 2 , rmse===213.426503 \n",
      "Fold 4/4 , model 3 , rmse===371.298640 \n",
      "Fold 4/4 , model 4 , rmse===380.206112 \n",
      "=========== end of fold 4 in level 0 ===========\n",
      "Output dimensionality of level 0 is 5 \n",
      "====================== End of Level 0 ======================\n",
      " level 0 lasted 102.525091 seconds \n",
      "====================== Start of Level 1 ======================\n",
      "Input Dimensionality 52 at Level 1 \n",
      "1 models included in Level 1 \n",
      "Fold 1/4 , model 0 , rmse===220.813042 \n",
      "=========== end of fold 1 in level 1 ===========\n",
      "Fold 2/4 , model 0 , rmse===445.320892 \n",
      "=========== end of fold 2 in level 1 ===========\n",
      "Fold 3/4 , model 0 , rmse===606.689905 \n",
      "=========== end of fold 3 in level 1 ===========\n",
      "Fold 4/4 , model 0 , rmse===204.181997 \n",
      "=========== end of fold 4 in level 1 ===========\n",
      "Output dimensionality of level 1 is 1 \n",
      "====================== End of Level 1 ======================\n",
      " level 1 lasted 162.295629 seconds \n",
      "====================== End of fit ======================\n",
      " fit() lasted 264.823714 seconds \n"
     ]
    }
   ],
   "source": [
    "model.fit(final,y)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 228,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "====================== Start of Level 0 ======================\n",
      "1 estimators included in Level 0 \n",
      "====================== Start of Level 1 ======================\n",
      "1 estimators included in Level 1 \n"
     ]
    }
   ],
   "source": [
    "predictions_stk=model.predict(test)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 229,
   "metadata": {},
   "outputs": [],
   "source": [
    "pred_stk=pd.DataFrame(predictions_stk)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h2>Mape Score Check</h2>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def mean_absolute_percentage_error(y_true, y_pred, sample_weights=None):\n",
    "    y_true = np.array(y_true)\n",
    "    y_pred = np.array(y_pred)\n",
    "    assert len(y_true) == len(y_pred)\n",
    "    \n",
    "    if np.any(y_true==0):\n",
    "        print(\"Found zeroes in y_true. MAPE undefined. Removing from set...\")\n",
    "        idx = np.where(y_true==0)\n",
    "        y_true = np.delete(y_true, idx)\n",
    "        y_pred = np.delete(y_pred, idx)\n",
    "        if type(sample_weights) != type(None):\n",
    "            sample_weights = np.array(sample_weights)\n",
    "            sample_weights = np.delete(sample_weights, idx)\n",
    "        \n",
    "    if type(sample_weights) == type(None):\n",
    "        return(np.mean(np.abs((y_true - y_pred) / y_true)) * 100)\n",
    "    else:\n",
    "        sample_weights = np.array(sample_weights)\n",
    "        assert len(sample_weights) == len(y_true)\n",
    "        return(100/sum(sample_weights)*np.dot(\n",
    "                sample_weights, (np.abs((y_true - y_pred) / y_true))\n",
    "        ))\n",
    "    \n",
    "loss_function = mean_absolute_percentage_error"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Enter Parameters\n",
    "mean_absolute_percentage_error(y, predictions_lgb, sample_weights=None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pred_rfc=pd.DataFrame({'State': df2.State, 'County': df2.County, 'bid_id': df2.bid_id,'Organization_Name': df2.Organization_Name,'PredictedEnr':predictions_})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pred_rfc.to_csv('C:/Users/samde/Desktop/Data Science/Datasets/Medicare Hackathon/Unique.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h1>Submission</h1>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pred_rfc.to_excel('C:/Users/samde/Desktop/Data Science/Datasets/Medicare Hackathon/Unique.xlsx', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.6"
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "position": {
    "height": "341.723px",
    "left": "793.636px",
    "right": "20px",
    "top": "120px",
    "width": "350px"
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}