Preprocessing and Modeling with SameKey .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 cleaned the new data, created new features out of the key columns that are the same in both the train and test dataset. 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": "markdown",
   "metadata": {},
   "source": [
    "Creating the new data by merging."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "df5['bid_id']=df5['key_Segment1']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2['Previous_Year']=df2[\"Enrollment_jan_18\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2.drop(\"Enrollment_jan_18\",axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "df=df5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "#df[\"state_county\"]=df[\"State\"].astype(str) + \"_\" + df[\"County\"].astype(str)\n",
    "#df2[\"state_county\"]=df[\"State\"].astype(str) + \"_\" + df[\"County\"].astype(str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_key=pd.DataFrame()\n",
    "test_key=pd.DataFrame()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_key['County']=df['County']\n",
    "train_key['State']=df['State']\n",
    "train_key['key_Segment1']=df['key_Segment1']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "test_key['County']=df2['County']\n",
    "test_key['State']=df2['State']\n",
    "test_key['key_Segment1']=df2['key_Segment1']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "same_key=pd.merge(train_key,test_key, on=['County','State','key_Segment1'], how='inner')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "#test_key[test_key[\"key_Segment1\"]=='H0028-004-000']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "#train_key[train_key[\"key_Segment1\"]=='H0028-004-000']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2=pd.merge(same_key,df2,how='left',on=['County','State','key_Segment1'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1.drop([\"Organization_Name\"],axis=1,inplace=True)\n",
    "df3.drop([\"Organization_Name\"],axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Data Cleaning and Imputing."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1[\"plan_type\"].fillna(\"NA\",inplace=True)\n",
    "df3[\"plan_type\"].fillna(\"NA\",inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "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": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1[\"plantype\"]=plantype\n",
    "df3[\"plantype\"]=plantype_2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "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": 24,
   "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": 25,
   "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": 26,
   "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": 27,
   "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": 28,
   "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": 29,
   "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": 30,
   "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": 31,
   "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": 32,
   "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": 33,
   "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": 34,
   "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": 35,
   "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": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1[\"monthlycd\"]=MonthlyConsolidatedPremiumCD_\n",
    "df3[\"monthlycd\"]=MonthlyConsolidatedPremiumCD_3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "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": 38,
   "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": 39,
   "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": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1[\"partdbasicpremium3\"]=PartDBasicPremium3_\n",
    "df3[\"partdbasicpremium3\"]=PartDBasicPremium3_3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "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": 42,
   "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": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1[\"partcpremium2\"]=PartCPremium2_\n",
    "df3[\"partcpremium2\"]=PartCPremium2_3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "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": 45,
   "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": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1[\"partdsupplementalpremium4\"]=PartDSupplementalPremium4_\n",
    "df3[\"partdsupplementalpremium4\"]=PartDSupplementalPremium4_3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "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": 48,
   "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": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1[\"partdfull\"]=PartDPrmoblgtnFullprmassist_\n",
    "df3[\"partdfull\"]=PartDPrmoblgtnFullprmassist_3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "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": 51,
   "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": 52,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1[\"partd50\"]=PartDPrmoblgtn75prmassist_\n",
    "df3[\"partd50\"]=PartDPrmoblgtn75prmassist_3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "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": 54,
   "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": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1[\"partd50\"]=PartDPrmoblgtn50prmassist_\n",
    "df3[\"partd50\"]=PartDPrmoblgtn50prmassist_3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "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": 57,
   "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": 58,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1[\"partd25\"]=PartDPrmoblgtn25prmassist_\n",
    "df3[\"partd25\"]=PartDPrmoblgtn25prmassist_3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "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": 60,
   "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": 61,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1[\"partdtotal5\"]=PartDTotalPremium5_\n",
    "df3[\"partdtotal5\"]=PartDTotalPremium5_3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "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": 63,
   "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": 64,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1[\"partddrugdeuctible\"]=PartDDrugDeductible_\n",
    "df3[\"partddrugdeuctible\"]=PartDDrugDeductible_3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "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": 66,
   "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": 67,
   "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": 68,
   "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": 69,
   "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": 70,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1[\"PartD\"].fillna(\"Yes\",inplace=True)\n",
    "df3[\"PartD\"].fillna(\"Yes\",inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1[\"enrollments_jan18\"].fillna(int(0),inplace=True)\n",
    "df3['Previous_Year'].fillna(int(0),inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"Special_Needs_Plan_TypeNA\"]=df[\"Special_Needs_Plan_Type\"].isna()\n",
    "df2[\"Special_Needs_Plan_TypeNA\"]=df2[\"Special_Needs_Plan_Type\"].isna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1['PartDDrugDeductibleNA']=df['PartD_Drug_Deductible'].isna()\n",
    "df3['PartDDrugDeductibleNA']=df2['PartD_Drug_Deductible'].isna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "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": 75,
   "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": 76,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1[\"Dementia\"].fillna(int(0),inplace=True)\n",
    "df3[\"Dementia\"].fillna(int(0),inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "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": 78,
   "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": 79,
   "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": 80,
   "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": 81,
   "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": 82,
   "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": 83,
   "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": 84,
   "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": 85,
   "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": 86,
   "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": 87,
   "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": 88,
   "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": 89,
   "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": 90,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1[\"MMP\"].fillna(\"No\",inplace=True)\n",
    "df3[\"MMP\"].fillna(\"No\",inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "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": 92,
   "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": 93,
   "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": 94,
   "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": 95,
   "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": 96,
   "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": 97,
   "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": 98,
   "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": 99,
   "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": 100,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1[\"MOOP\"]=MOOP\n",
    "df3[\"MOOP\"]=MOOP_2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "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": 102,
   "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": 103,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1[\"MOOPFINAL\"]=MOOP2\n",
    "df3[\"MOOPFINAL\"]=MOOP2_2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "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": 105,
   "metadata": {},
   "outputs": [],
   "source": [
    "df3.drop(['MOOP'],axis=1,inplace=True)\n",
    "df1.drop(['MOOP'],axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1[\"state_county\"]=df[\"State\"].astype(str) + \"_\" + df[\"County\"].astype(str)\n",
    "df3[\"state_county\"]=df[\"State\"].astype(str) + \"_\" + df[\"County\"].astype(str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1.drop(\"County\",axis=1,inplace=True)\n",
    "df3.drop(\"County\",axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h2>Mean Encoding</h2>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Key Segment Mean Encoding\n",
    "groupbykey=df1.groupby('key_Segment1')['enrollments_jan18'].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": 109,
   "metadata": {},
   "outputs": [],
   "source": [
    "mean_key=df3['keysegmentmean'].mean()\n",
    "df3['keysegmentmean'].fillna(value=mean_key,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {},
   "outputs": [],
   "source": [
    "#PartD Mean Encoding\n",
    "groupbypartd=df1.groupby('PartD')['enrollments_jan18'].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": 111,
   "metadata": {},
   "outputs": [],
   "source": [
    "mean_partd=df3['PartDmean'].mean()\n",
    "df3['PartDmean'].fillna(value=mean_partd,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {},
   "outputs": [],
   "source": [
    "#State Mean Encoding\n",
    "groupbystate=df1.groupby('State')['enrollments_jan18'].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": 113,
   "metadata": {},
   "outputs": [],
   "source": [
    "mean_state=df3['Statemean'].mean()\n",
    "df3['Statemean'].fillna(value=mean_state,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Location Mean Encoding\n",
    "groupbyloc=df1.groupby('state_county')['enrollments_jan18'].mean()\n",
    "df1['Locmean']=df1['state_county'].map(groupbyloc)\n",
    "df3['Locmean']=df3['state_county'].map(groupbyloc)\n",
    "#df1.drop(['Locmean'],axis=1,inplace=True)\n",
    "#df3.drop(['Locmean'],axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "metadata": {},
   "outputs": [],
   "source": [
    "mean_loc=df3['Locmean'].mean()\n",
    "df3['Locmean'].fillna(value=mean_loc,inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h3>Converting Categorical Variables</h3>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "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": 117,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "State                               0\n",
       "key_Segment1                        0\n",
       "bid_id                              0\n",
       "Drug_Benefit_Type                   0\n",
       "Overall_Star_Rating                 0\n",
       "Add_Cvrg_Offrd_in_Gap               0\n",
       "PartD                               0\n",
       "Special_Needs_Plan_Type             0\n",
       "Cardiovascular_Disorders            0\n",
       "Chronic_Heart_Failure               0\n",
       "Dementia                            0\n",
       "Diabetes_Mellitus                   0\n",
       "End_stge_Renal_Disease_dialysis     0\n",
       "HIV_AIDS                            0\n",
       "Chronic_Lung_Disorders              0\n",
       "Chrnic_Mental_hlth_Cdtn             0\n",
       "Crdiovsculr_Disordrs_hrt_fail       0\n",
       "Chronic_Heart_Fail_Diabetes         0\n",
       "Cardio_Disordrs_hrtfail_diabetes    0\n",
       "MMP                                 0\n",
       "Special_Needs_Plan                  0\n",
       "PartD_Basic_Prm_Blw_Rgnl_bnchmrk    0\n",
       "Tiers_Not_Subject_to_Deductible7    0\n",
       "Increased_Initial_Coverage_Limit    0\n",
       "Previous_Year                       0\n",
       "plantype                            0\n",
       "monthlycd                           0\n",
       "partdbasicpremium3                  0\n",
       "partcpremium2                       0\n",
       "partdsupplementalpremium4           0\n",
       "partdfull                           0\n",
       "partd50                             0\n",
       "partd25                             0\n",
       "partdtotal5                         0\n",
       "partddrugdeuctible                  0\n",
       "Drug_Benefit_TypeNA                 0\n",
       "PartDDrugDeductibleNA               0\n",
       "Add_Cvrg_Offrd_in_GapNA             0\n",
       "Special_Needs_PlanNA                0\n",
       "In_network_MOOP_AmountNA            0\n",
       "MOOPFINAL                           0\n",
       "state_county                        0\n",
       "keysegmentmean                      0\n",
       "PartDmean                           0\n",
       "Statemean                           0\n",
       "Locmean                             0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 117,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Shape of df1: (49736, 47)\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','state_county')\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": 119,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1[\"YearlyMin\"]=df1[\"monthlycd\"]*12\n",
    "df3[\"YearlyMin\"]=df3[\"monthlycd\"]*12"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "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": 121,
   "metadata": {},
   "outputs": [],
   "source": [
    "final=df1.drop([\"enrollments_jan18\",'bid_id'],axis=1)\n",
    "y=df1['enrollments_jan18']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "metadata": {},
   "outputs": [],
   "source": [
    "test=df3.drop(['bid_id'],axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h2>Random Forest</h2>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "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": 124,
   "metadata": {},
   "outputs": [],
   "source": [
    "rfc=RandomForestRegressor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "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=10, n_jobs=1,\n",
       "           oob_score=False, random_state=None, verbose=0, warm_start=False)"
      ]
     },
     "execution_count": 125,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rfc.fit(final,y)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "metadata": {},
   "outputs": [],
   "source": [
    "predictions_rfc=rfc.predict(test)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "409.05307219290177"
      ]
     },
     "execution_count": 127,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "predictions_rfc.mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "331.16663985845264"
      ]
     },
     "execution_count": 128,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "y.mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Feature ranking:\n",
      "1. feature 23 (0.884617)\n",
      "2. feature 41 (0.061749)\n",
      "3. feature 44 (0.030558)\n",
      "4. feature 43 (0.005078)\n",
      "5. feature 40 (0.003755)\n",
      "6. feature 46 (0.002189)\n",
      "7. feature 22 (0.001895)\n",
      "8. feature 0 (0.001230)\n",
      "9. feature 33 (0.001129)\n",
      "10. feature 4 (0.001033)\n",
      "11. feature 39 (0.000998)\n",
      "12. feature 25 (0.000876)\n",
      "13. feature 2 (0.000792)\n",
      "14. feature 24 (0.000626)\n",
      "15. feature 26 (0.000598)\n",
      "16. feature 32 (0.000557)\n",
      "17. feature 45 (0.000325)\n",
      "18. feature 27 (0.000312)\n",
      "19. feature 30 (0.000301)\n",
      "20. feature 20 (0.000263)\n",
      "21. feature 31 (0.000236)\n",
      "22. feature 29 (0.000212)\n",
      "23. feature 5 (0.000183)\n",
      "24. feature 18 (0.000126)\n",
      "25. feature 19 (0.000108)\n",
      "26. feature 28 (0.000089)\n",
      "27. feature 1 (0.000088)\n",
      "28. feature 17 (0.000013)\n",
      "29. feature 3 (0.000012)\n",
      "30. feature 36 (0.000011)\n",
      "31. feature 21 (0.000009)\n",
      "32. feature 34 (0.000009)\n",
      "33. feature 16 (0.000007)\n",
      "34. feature 38 (0.000004)\n",
      "35. feature 37 (0.000004)\n",
      "36. feature 9 (0.000003)\n",
      "37. feature 42 (0.000003)\n",
      "38. feature 6 (0.000002)\n",
      "39. feature 15 (0.000000)\n",
      "40. feature 12 (0.000000)\n",
      "41. feature 7 (0.000000)\n",
      "42. feature 10 (0.000000)\n",
      "43. feature 11 (0.000000)\n",
      "44. feature 13 (0.000000)\n",
      "45. feature 14 (0.000000)\n",
      "46. feature 8 (0.000000)\n",
      "47. feature 35 (0.000000)\n"
     ]
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAXcAAAEICAYAAACktLTqAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADl0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uIDMuMC4xLCBodHRwOi8vbWF0cGxvdGxpYi5vcmcvDW2N/gAAHdpJREFUeJzt3Xu8VXWd//HXW0DzlqRgqaBooiPTRY2s38/Sk1opFeSkpZPdzcmGKbtrNaZO/n5lF2easRrz+rC8ZTNIRmmTkd00D4kkIgqIcUIDTcw7op/54/vd8D2Lfc7ZwLED397Px2M/2Gt91+W7vuu73ntdNmcrIjAzs7psNtQVMDOzwedwNzOrkMPdzKxCDnczswo53M3MKuRwNzOrkMPd/ipI+qakfx7qepj9pcjfc7f+SFoMPB94uhi9V0Qs3YBldgHfjogxG1a7TZOki4CeiPjsUNfF6uUzd+vEmyJim+K13sE+GCQNH8r1bwhJw4a6DvbXweFu603SKyX9StIKSbfmM/JW2XskzZP0sKRFkv4hj98a+CGws6RH8mtnSRdJ+nwxf5eknmJ4saRPSZoDPCppeJ7ve5KWS7pb0of6qevq5beWLemTkpZJulfSmyVNknSnpD9J+nQx72mSrpJ0Rd6e30p6aVG+j6SZuR3mSprcWO83JM2Q9CjwPuDtwCfztn8/T3eypIV5+bdLOrJYxrsl/ULSlyU9mLf1iKJ8e0kXSlqay6cVZW+UNDvX7VeSXlKUfUrSH/I650s6tIPdbpuKiPDLrz5fwGLgsDbjdwEeACaRThJem4dH5/I3AC8EBBwMPAbsn8u6SLclyuVdBHy+GO41Ta7HbGAssGVe5yzgVGBzYA9gEfD6PrZj9fLzslfleUcA7weWA5cC2wJ/CzwB7JGnPw14CjgqT/9x4O78fgSwAPh0rschwMPA3sV6HwIOzHV+TnNb83RHAzvnad4GPArslMvendf/fmAYcCKwlDW3VX8AXAE8L9fn4Dx+f2AZ8Io837tyO24B7A0sAXbO044DXjjU/c2vwXv5zN06MS2f+a0ozgqPA2ZExIyIeCYifgx0k8KeiPhBRCyM5GfAdcCrN7AeX4uIJRHxOPBy0gfJGRGxMiIWAd8CjulwWU8BZ0bEU8DlwCjg3yLi4YiYC8wFXlJMPysirsrTf5UU0q/Mr22AL+R6XA9cAxxbzHt1RPwyt9MT7SoTEd+NiKV5miuAu4ADiknuiYhvRcTTwMXATsDzJe0EHAF8ICIejIincntD+jD4z4i4KSKejoiLgSdznZ8mhfwESSMiYnFELOyw7WwT4HC3Trw5Ikbm15vzuN2Ao4vQXwG8ihQ6SDpC0o35FscKUuiP2sB6LCne70a6tVOu/9Okh7+deCAHJcDj+d8/FuWPk0J7rXVHxDNAD+lMe2dgSR7Xcg/pyqZdvduS9M7i9skK4EX0bq/7ivU/lt9uQ7qS+VNEPNhmsbsBH2u00VjS2foC4CTSVckySZdL2nmgetqmw+Fu62sJcEkR+iMjYuuI+IKkLYDvAV8Gnh8RI4EZpFs0AO2+ovUosFUx/II205TzLQHubqx/24iYtMFb1t7Y1htJmwFjSLdGlgJj87iWXYE/9FHvtYYl7Ua66pgK7JDb6zbWtFd/lgDbSxrZR9mZjTbaKiIuA4iISyPiVaQPgQC+2MH6bBPhcLf19W3gTZJeL2mYpOfkB5VjSPeetyDdx16VH/69rpj3j8AOkrYrxs0GJuWHgy8gnVX25zfAn/NDwS1zHV4k6eWDtoW9vUzS3yl9U+ck0u2NG4GbSB9Mn5Q0Ij9UfhPpVk9f/kh6RtCyNSlcl0N6GE06cx9QRNxLekD9dUnPy3U4KBd/C/iApFco2VrSGyRtK2lvSYfkD+InSFcqT/exGtsEOdxtvUTEEmAK6VbIctJZ4ieAzSLiYeBDwJXAg8DfA9OLee8ALgMW5dsFOwOXALeSHvhdR3pA2N/6nyaF6L6kh5v3A+cB2/U33wa4mvSg80HgHcDf5fvbK4HJpPve9wNfB96Zt7Ev55Puda+QNC0ibge+AvyaFPwvBn65DnV7B+kZwh2kB6gnAUREN+m++3/kei8gPZyF9OH7hVzn+4AdSfvSKuH/xGQ2AEmnAXtGxHFDXRezTvnM3cysQg53M7MK+baMmVmFfOZuZlahIfsDTKNGjYpx48YN1erNzDZJs2bNuj8iRg803ZCF+7hx4+ju7h6q1ZuZbZIk3dPJdL4tY2ZWIYe7mVmFHO5mZhVyuJuZVcjhbmZWIYe7mVmFHO5mZhVyuJuZVWijC/euri66urqGuhpmZpu0jS7czcxswznczcwqNGR/W2YtUvth/0liM7N15jN3M7MKOdzNzCq08dyWyWYOdQXMzCrgM3czswo53M3MKuRwNzOrkMPdzKxCDnczswo53M3MKuRwNzOrkMPdzKxCDnczswo53M3MKuRwNzOrkMPdzKxCHYW7pMMlzZe0QNLJbcp3lfRTSbdImiNp0uBX1czMOjVguEsaBpwDHAFMAI6VNKEx2WeBKyNiP+AY4OuDXVEzM+tcJ2fuBwALImJRRKwELgemNKYJ4Ln5/XbA0sGropmZratOwn0XYEkx3JPHlU4DjpPUA8wA/qndgiSdIKlbUvfy5cvXo7pmZtaJTsJdbcY1f9j0WOCiiBgDTAIukbTWsiPi3IiYGBETR48eve61NTOzjnQS7j3A2GJ4DGvfdnkfcCVARPwaeA4wajAqaGZm666TcL8ZGC9pd0mbkx6YTm9M83vgUABJ+5DC3fddzMyGyIDhHhGrgKnAtcA80rdi5ko6Q9LkPNnHgPdLuhW4DHh3RDRv3ZiZ2V9IRz+QHREzSA9Ky3GnFu9vBw4c3KqZmdn68v9QNTOrkMPdzKxCDnczswo53M3MKuRwNzOrkMPdzKxCDnczswo53M3MKuRwNzOrkMPdzKxCDnczswo53M3MKuRwNzOrkMPdzKxCDnczswo53M3MKuRwNzOrkMPdzKxCDnczswo53M3MKuRwNzOrkMPdzKxCDnczswo53M3MKuRwNzOrkMPdzKxCDnczswo53M3MKuRwNzOrkMPdzKxCDnczswo53M3MKuRwNzOrkMPdzKxCDnczswp1FO6SDpc0X9ICSSf3Mc1bJd0uaa6kSwe3mmZmti6GDzSBpGHAOcBrgR7gZknTI+L2YprxwCnAgRHxoKQdn60Km5nZwDo5cz8AWBARiyJiJXA5MKUxzfuBcyLiQYCIWDa41TQzs3XRSbjvAiwphnvyuNJewF6SfinpRkmHt1uQpBMkdUvqXr58+frV2MzMBtRJuKvNuGgMDwfGA13AscB5kkauNVPEuRExMSImjh49el3ramZmHeok3HuAscXwGGBpm2mujoinIuJuYD4p7M3MbAh0Eu43A+Ml7S5pc+AYYHpjmmnAawAkjSLdplk0mBU1M7PODRjuEbEKmApcC8wDroyIuZLOkDQ5T3Yt8ICk24GfAp+IiAeerUqbmVn/FNG8ff6XMXHixOju7i5q0u7WPjBE9TMz2xhJmhUREweazv9D1cysQg53M7MKOdzNzCrkcDczq5DD3cysQg53M7MKOdzNzCrkcDczq5DD3cysQg53M7MKOdzNzCrkcDczq5DD3cysQg53M7MKOdzNzCrkcDczq5DD3cysQg53M7MKOdzNzCrkcDczq5DD3cysQg53M7MKOdzNzCrkcDczq5DD3cysQg53M7MKOdzNzCrkcDczq5DD3cysQg53M7MKOdzNzCrkcDczq5DD3cysQg53M7MKOdzNzCrUUbhLOlzSfEkLJJ3cz3RHSQpJEwevimZmtq4GDHdJw4BzgCOACcCxkia0mW5b4EPATYNdSTMzWzednLkfACyIiEURsRK4HJjSZrp/Ac4CnhjE+pmZ2XroJNx3AZYUwz153GqS9gPGRsQ1/S1I0gmSuiV1L1++fJ0ra2Zmnekk3NVmXKwulDYDzgY+NtCCIuLciJgYERNHjx7deS3NzGyddBLuPcDYYngMsLQY3hZ4ETBT0mLglcB0P1Q1Mxs6nYT7zcB4SbtL2hw4BpjeKoyIhyJiVESMi4hxwI3A5IjoflZqbGZmAxow3CNiFTAVuBaYB1wZEXMlnSFp8rNdQTMzW3fDO5koImYAMxrjTu1j2q4Nr5aZmW0I/w9VM7MKOdzNzCrkcDczq5DD3cysQg53M7MKOdzNzCrkcDczq5DD3cysQg53M7MKOdzNzCrkcDczq5DD3cysQg53M7MKOdzNzCrkcDczq5DD3cysQg53M7MKOdzNzCrkcDczq5DD3cysQg53M7MKOdzNzCrkcDczq5DD3cysQg53M7MKOdzNzCrkcDczq5DD3cysQg53M7MKOdzNzCrkcDczq5DD3cysQg53M7MKOdzNzCrkcDczq1BH4S7pcEnzJS2QdHKb8o9Kul3SHEk/kbTb4FfVzMw6NWC4SxoGnAMcAUwAjpU0oTHZLcDEiHgJcBVw1mBX1MzMOtfJmfsBwIKIWBQRK4HLgSnlBBHx04h4LA/eCIwZ3Gqamdm66CTcdwGWFMM9eVxf3gf8sF2BpBMkdUvqXr58eee1NDOzddJJuKvNuGg7oXQcMBH4UrvyiDg3IiZGxMTRo0d3XkszM1snwzuYpgcYWwyPAZY2J5J0GPAZ4OCIeHJwqmdmZuujkzP3m4HxknaXtDlwDDC9nEDSfsB/ApMjYtngV9PMzNbFgOEeEauAqcC1wDzgyoiYK+kMSZPzZF8CtgG+K2m2pOl9LM7MzP4COrktQ0TMAGY0xp1avD9skOtlZmYbwP9D1cysQg53M7MKOdzNzCrkcDczq5DD3cysQg53M7MKOdzNzCrkcDczq5DD3cysQg53M7MKOdzNzCrkcDczq5DD3cysQg53M7MKOdzNzCrkcDczq5DD3cysQg53M7MKOdzNzCrkcDczq5DD3cysQg53M7MKOdzNzCq0SYV7V1cXXV1dQ10NM7ON3vChrkBHpPbDEX/5upiZbQI2qTN3MzPrzKZx5p7NHOoKmJltInzmbmZWIYe7mVmFHO5mZhWqJtz9NUkzszU2qQeqbflrkmZma9n0wz2bOdQVMDPbiFRzW8bMzNZwuJuZVaijcJd0uKT5khZIOrlN+RaSrsjlN0kaN9gVNTOzzg0Y7pKGAecARwATgGMlTWhM9j7gwYjYEzgb+OJgV3RD+Js0ZvbXppMHqgcACyJiEYCky4EpwO3FNFOA0/L7q4D/kKSIIf7KSn/fpGmWtRRlXXnUzE7LstYHycyZq0s3uKw/6zufmdVLA+WvpKOAwyPi+Dz8DuAVETG1mOa2PE1PHl6Yp7m/sawTgBMAdt1115fdc889g7ktZmbVkzQrIiYONF0n99zbneI2PxE6mYaIODciJkbExNGjR3ewajMzWx+dhHsPMLYYHgMs7WsaScOB7YA/DUYFzcxs3XUS7jcD4yXtLmlz4BhgemOa6cC78vujgOuH/H67mdlfsQEfqEbEKklTgWuBYcAFETFX0hlAd0RMB84HLpG0gHTGfsyzWWkzM+tfR39+ICJmADMa404t3j8BHD24VTMzs/Xl/6FqZlYhh7uZWYUc7mZmFXK4m5lVaMD/ofqsrVhaDvT1X1RHAfe7zGVDVLax1MNlLmtnt4gY+H+BRsRG9yJ9xdJlLhuSso2lHi5z2Ya8fFvGzKxCDnczswptrOF+rstcNoRlG0s9XOay9TZkD1TNzOzZs7GeuZuZ2QZwuJuZ1WhDv26zIS/S34D/KTAPmAt8OI//F2AOMBu4DvgdcE0umwosIP0YyI7ALa2yYrn/DjxSlpH+cuWteblX5fetst2Bm4C7gCvzeltlAr4GPAY8Cdxb1PNLwB15mf8NjAQOB+bnOn4W+E1e11zg9D7qsk0ePyzXeUYf8x2S2+IRYEWjzU4D/pDrPht4Z6NtP9eurYv90GrTOxplHwEeB54gfe+2VZfv5O28Dbiw0dYXAb8HHs7zLSzqeUXerkeBlXnZpxf74Zbc1n8m/ZRja77tgZ/k5T2ct+P0ok8szvW/s9EuLwV+ndvt+8Bz87S/y+10P7AMuK3Y5n2BG4EHgKeAhUVZa3l35HnnN9bXmnc20E36c9irlw9c0GZ9VxT7bXH+9zn07gOfp32f6K9/Hp3bsGyX04v1XpDne7px/PxTnv6xXNdyfYfmffQoqR/eVZT9vNiOpcA04MOkPjIXOIk1ffyaNv3oAmBEHj+SdGzcQerrf2q02Wmk/t7aR4uLsnbH5d5F3WaT+t7DjWVuD/wYeCiX316UHZ23IdrUpZVXD+R9cEebrPt4nncU6Ziam7f5CtKfVW/u14uAu4v67rvO+TrE4b4TsH9+v23uUBOA5xbT/HfuQK3OsB8wjnQQ/DNwKUW4AxOBS3IjX1rMVy5zJr0D/ErgmPz+V6SDqFX2HuC7wMuKAGrV83XA8Dz+i8BZpCDbA9g877CJuXwE6QPklY26fBU4Ob//aKvOrAn81nz/F1gCHAjsD5wBfLCoy2nAx/tp24XAW5ptnYdflrf7HmC3YpmvAf4H2D5Pt3OxDZNIwSLgt402uwg4vt2+LQJpG+Arud6tZV4JfCBv3zdJYdCqy1nAyXm+k0kHcGu+/YCXkwJlFL370s3AwXm97yUdiIuBUXncQXl95cF6HekH4Q8iBd2jRdnNwMG5fU/LyyvXdx1wRJ52EinIVi+/3foax8RXgFNbbdToA69p05f665/7kELt56TjYvV8edr3Az+gCPdin2+R23rHxvruzMvdhtT/Li6XWSzne8AppADbivQXaP8H+H/0Pi7LfnQZcGIefzFwfH5/SG63Zrh/vI/91zwuv9io2zBSEE9qzNfqYweRPjCXF2WttrwFeHtjvucW+/Ys4E+N9Y0l/cn0e4AXkUJ7yyJ7/qHNfr0IOGpD8nVIb8tExL0R8dv8vnU2tktE/BlA0hjgxXl8a55bImIxaQe9FjivVSZpGOmgPzuXn1fMVy5zT1IYIUmkznNVLhtB758NPBE4JSJm5eXcXdTzuohYlae7kXRWtyAiFkXESuDyXEfyckekRayui4AtgcjrfkOrzhHxSGO+p4EnI+KXuc1+DLyxVZcO2vY20tlwr7bOk5+StzNIZ2StshOBL0RE61e1VhTbMCNSj9yFdKDf2ajCinb7Ng+31vNWUgiMyOs+BDgvz3cxKWBb800BLs7tcnEebtXlloi4mXS21Vzf3sANuU4/Bt7SaKcbWPtXw4J0wLbme6oo2xu4ISLuJV2BvaWxviBdHUD6RbJ55fL7WB+wuj+8FbgskmYfeKwxHPTfP+dFxHxS3+k1Xz5W/p41P2zf0trnT0bEIxGxrLG+Vts8krfvj0VZazu2Je3LPwA3RsRj+Ti5hXQGXB6XM/K2BumYHCPpuaSgPD9Pcz3pSnAt7dqzzXE5pjHboaQz+9sb41t97AbSCWJrP1K05UPk46goax1XNxRtVDob+GQxfjiwZf7Vuq1IYQ+923mDbTT33CWNI52B3ZSHz2RNYJzfZpbtgdOBZ4pxU0mXwZ8mHehlGZIuJN1+uJcUKgA7kIJoFfCvpNsXo4rZXgi8TVK3pB9K6irrWXgvqcMsKcb1kDrrbNLl7Y8jorV9FwL3AX9Duo30r6QO8EwuH1bOR+r4IyS1fhj3KNIVQlmXqZLmSLpA0vOK7R5H77ZdPSxpMvCHiLg1Tz62mHYv4NWSbpL0COk2xOptyP6NFB7N9jgz1+VsSXu1abODSW3/67x9C1mzH1ptV9b7+RFxbw6lHwLj29Sl3fbeBkzORUfn7QvgOkmz8o+2N50EfEnSEuAzpABrWWt5jfWV836Z9MHZqVcDf4yIu/J2NPtAd5u+1Gn//E5jvtaxsrwxXbnPfyZpfmO+44EZknpIx8oHWXs/HEm6hXYzcJCkHSRtRTpGZtE4LvO2jgDeAfyI1K+XAxdKukXSeaSToKapkuaQzpb7yrL3kvpL6RjSVULT8/OHNnn9Hf3eRa7/mXmfv5nUXq3xzePrPlK/+D0phx4CftIuI+h9DG3RaV1aNopwl7QNKWxPan0Kkg76i0jBfmRj+jeSAuXWYtzOpINtIamR1upAeR0Xkjrda1qzFstcRroXW35ybgE8EenXxi8h3bct64mkzwCrgF+0WeczEbEv6ezhAEkvAoiI95Buc8wjXaoua5195fKny/mAvyV1yrMl/YZ073nXoi7fIB3o+5I6zVdy3Xq1bTmc6/wZ0m2AVltcWCxzOPA80mXiIaQOv3obcpvtQ7rk/F2xzaeQPrReTrqsn9lsM+BtpNtqre3bp9FuWzW2r9kuD5V1KWxN7770XuAfJc0i3T5ZCRwYEfuTrgz+Ma+/dCLwkYgYS7rtUl4ZtVteub5y3o/Q/sSkL8dShE6bPrBPm740YP/M3l7MdxDpWPn3NnUo9/knSLcXy/V9BJgUEWNI+++/WHs/HEu6+phHui3SOjm5N7/a+TrpiujnuQ77A9+IiP1IV3knNqYv+/sy0m2yXorj8jvFuM1JH87f7aMe6yUiPpP3+TTSSQv5A608viBd7Uwh3T7bmdRfj22zX8tjaHvgU+tTqaG+7z6CFA4fbYz//6SztyWky+LHgG8XZatIn3735bIH8/sVuSzyv835ekgd7Ilc9h3SGekXirInW/ORzsbHFfV8vFHPd5E+iLYC/g9wbVF2CumSuTX8OYr74nncwaSriR7SveDW9ny7r/lyXbqB2X206TjSGWavtm0z/GLSgbE4v57J7fiCXP4joKtY7kLSwfrxPPxz0gPRvuo9gnRQz23UbzjpbHhMsX2fyPtheJ7vRooHU6SHbjvl9zvl4Wa7LAaup9GXivK9gN80xp0GnEnve6gPseb/gIyj8cCxmG5Cnvajfcwr0iX8uMbyew23a5M262pu6+dI95z77Z952pmsefbzufy6L7dXD+lYWdDPPh9d7KPy4fKupFsbq+tGCrYHgOe0OZ7/nMt69ZU8/zRgszz8Ano/IH113q99Pad4FekDru1x2Rg/Bbiu3X5o9LGXk26DtmvLN/VTlwNbdWHt42sV6QTp0mL6dwJfHyAjumh8aaST15Ceued7jOcD8yLiq8X48RFxSqSzg7NID/uuj4jjACLiFFKn3J90Nnt9RDwvIl4QESMjYjjp0/4wUqd4h6Q983xjSYF+dZ7v7aRvkczO67uadBbRWt800lnr+eSn60U9Dyd9ok6OiMdY+8fEW8tG0pa5PvMl7Vls/5uAaRExJiLG5e35BemyuZzvDkk75nkuJJ0Rf7SoS3nmcmSu5+q2bdfWEfG7iNiRdBZxA+nbD+Mj4r68nGnAGyWNzLdWtiA92L1D0vGk5xrbF/W+PiKOk7RTsb4RpLPJ0luAuyKip9i+ebmtjsrzbUV6qNUyHfigpJGkA/cHrXYp2nIUcGejL+2Y/92M9O2lC/I9YSRtTXr41nxesJT0oUve3pV9LO/7wE3l+hrzHkL6MkAnDiN9mPXk5Y/O29rqA0eQb/mVfYJ++mdrOeTbC8V8s/KxMo4UjM9ExJ55lmnAIXn9LyOduT/Cmn20naRX5Lq9lhSIq/cD6Yrgmkg/vbm6vUhn2ktJz7vKvnI88HrS2eszALn/LZG0d5730GY7Nvr760gna62y5nFZ6nV11DCd1Lcg9cPm1U9bksYXg4eRTg5XH18RMS63dQ8pE14qaavcZyeRgr95rO+Ux4l0q6fXfu3Iun4aDOaL1LGCNV97nJ039nt5Y+aQDqCjWPN0/UO5kVaROssPaPOpRuqQXaRvnmwG/JJ06+A2UrhPKpa5B+kMcwHpcu21RdlI0odL6yHgHUU9F5AOuFbdv5nH30k64/ka6SHSnLzeU/uoS/ntmS7gZ835ctmXWPOVv6WNNrskL3MOqZNOabTtXe3aurEfVrLmK4KTSAf2NaSz88dJD35adVmVt7G1rAuKNrs+lwXpSmBOY31Xk666mtu3B2u+ureCdNutVZcdSPeRy69Cnlr0iWV5vqdIZ4et+T6c98edpKuzPfJyW189u5V0tfYUqV+9L7fHrFz3lY2y1vJ+3649i3lvzfX9UWP5NzXXl7fhIuADRT94Cb37wDm07xP99c8jSVcDz+T1PdyaL897Wa5LFNu3OemK9S7S2fXCxvqOzGWPk46x+Y1lzgQOL4Z/nvfprcChzTNR1u5HrfXsS7o6nZPrdl9jP7T6+wpSnyjL1jou8zK3IvWN7YptL+fbgfSs4GFSQJdlR+b3T+fXM0VZK6/WqksjkxaTTkBOz/vpNlK+zW6zX69nTUZ8m/zNqXV5+c8PmJlVaKN4oGpmZoPL4W5mViGHu5lZhRzuZmYVcribmVXI4W5mViGHu5lZhf4XcIJQS4H/KToAAAAASUVORK5CYII=\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": 130,
   "metadata": {},
   "outputs": [],
   "source": [
    "finalimp=df1.drop([\"enrollments_jan18\",'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",
    "y=df1['enrollments_jan18']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "metadata": {},
   "outputs": [],
   "source": [
    "testimp=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": 132,
   "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=10, n_jobs=1,\n",
       "           oob_score=False, random_state=None, verbose=0, warm_start=False)"
      ]
     },
     "execution_count": 132,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from sklearn.ensemble import RandomForestRegressor\n",
    "rfcimp=RandomForestRegressor()\n",
    "rfcimp.fit(finalimp,y) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "metadata": {},
   "outputs": [],
   "source": [
    "predictions_rfc1=rfcimp.predict(testimp)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 134,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "405.7765239916719"
      ]
     },
     "execution_count": 134,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "predictions_rfc1.mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h2>Stacknet Regressor</h2>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 142,
   "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": 143,
   "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": 144,
   "metadata": {},
   "outputs": [],
   "source": [
    "model=StackNetRegressor(models, folds=4,restacking=True, n_jobs=1, verbose=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 145,
   "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===885.252030 \n",
      "Fold 1/4 , model 1 , rmse===318.854501 \n",
      "Fold 1/4 , model 2 , rmse===299.127652 \n",
      "Fold 1/4 , model 3 , rmse===400.658673 \n",
      "Fold 1/4 , model 4 , rmse===2638.644269 \n",
      "=========== end of fold 1 in level 0 ===========\n",
      "Fold 2/4 , model 0 , rmse===925.357630 \n",
      "Fold 2/4 , model 1 , rmse===545.120336 \n",
      "Fold 2/4 , model 2 , rmse===495.410024 \n",
      "Fold 2/4 , model 3 , rmse===680.341757 \n",
      "Fold 2/4 , model 4 , rmse===675.489298 \n",
      "=========== end of fold 2 in level 0 ===========\n",
      "Fold 3/4 , model 0 , rmse===864.775802 \n",
      "Fold 3/4 , model 1 , rmse===357.034931 \n",
      "Fold 3/4 , model 2 , rmse===466.217644 \n",
      "Fold 3/4 , model 3 , rmse===359.360304 \n",
      "Fold 3/4 , model 4 , rmse===1220.086644 \n",
      "=========== end of fold 3 in level 0 ===========\n",
      "Fold 4/4 , model 0 , rmse===1617.600468 \n",
      "Fold 4/4 , model 1 , rmse===897.709949 \n",
      "Fold 4/4 , model 2 , rmse===918.676599 \n",
      "Fold 4/4 , model 3 , rmse===946.568516 \n",
      "Fold 4/4 , model 4 , rmse===1103.655304 \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 836.188813 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===317.481690 \n",
      "=========== end of fold 1 in level 1 ===========\n",
      "Fold 2/4 , model 0 , rmse===503.667620 \n",
      "=========== end of fold 2 in level 1 ===========\n",
      "Fold 3/4 , model 0 , rmse===392.875815 \n",
      "=========== end of fold 3 in level 1 ===========\n",
      "Fold 4/4 , model 0 , rmse===951.314243 \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 1510.081807 seconds \n",
      "====================== End of fit ======================\n",
      " fit() lasted 2346.280748 seconds \n"
     ]
    }
   ],
   "source": [
    "model.fit(final,y)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 146,
   "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": 147,
   "metadata": {},
   "outputs": [],
   "source": [
    "pred_stk=pd.DataFrame(predictions_stk)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h3> Mape Score Check</h3>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 135,
   "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": "markdown",
   "metadata": {},
   "source": [
    "<h1>Submission</h1>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 148,
   "metadata": {},
   "outputs": [
    {
     "ename": "Exception",
     "evalue": "Data must be 1-dimensional",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mException\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-148-e246680c7dda>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m()\u001b[0m\n\u001b[1;32m----> 1\u001b[1;33m \u001b[0mpred_rfc\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mpd\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mDataFrame\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m{\u001b[0m\u001b[1;34m'State'\u001b[0m\u001b[1;33m:\u001b[0m \u001b[0mdf2\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mState\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;34m'County'\u001b[0m\u001b[1;33m:\u001b[0m \u001b[0mdf2\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mCounty\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;34m'bid_id'\u001b[0m\u001b[1;33m:\u001b[0m \u001b[0mdf2\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mbid_id\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;34m'Organization_Name'\u001b[0m\u001b[1;33m:\u001b[0m \u001b[0mdf2\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mOrganization_Name\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;34m'PredictedEnr'\u001b[0m\u001b[1;33m:\u001b[0m\u001b[0mpredictions_stk\u001b[0m\u001b[1;33m}\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;32m~\\Miniconda3\\envs\\AI\\lib\\site-packages\\pandas\\core\\frame.py\u001b[0m in \u001b[0;36m__init__\u001b[1;34m(self, data, index, columns, dtype, copy)\u001b[0m\n\u001b[0;32m    346\u001b[0m                                  dtype=dtype, copy=copy)\n\u001b[0;32m    347\u001b[0m         \u001b[1;32melif\u001b[0m \u001b[0misinstance\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mdata\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mdict\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 348\u001b[1;33m             \u001b[0mmgr\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_init_dict\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mdata\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mindex\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcolumns\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mdtype\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mdtype\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    349\u001b[0m         \u001b[1;32melif\u001b[0m \u001b[0misinstance\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mdata\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mma\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mMaskedArray\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    350\u001b[0m             \u001b[1;32mimport\u001b[0m \u001b[0mnumpy\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mma\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mmrecords\u001b[0m \u001b[1;32mas\u001b[0m \u001b[0mmrecords\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\Miniconda3\\envs\\AI\\lib\\site-packages\\pandas\\core\\frame.py\u001b[0m in \u001b[0;36m_init_dict\u001b[1;34m(self, data, index, columns, dtype)\u001b[0m\n\u001b[0;32m    457\u001b[0m             \u001b[0marrays\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;33m[\u001b[0m\u001b[0mdata\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mk\u001b[0m\u001b[1;33m]\u001b[0m \u001b[1;32mfor\u001b[0m \u001b[0mk\u001b[0m \u001b[1;32min\u001b[0m \u001b[0mkeys\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    458\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 459\u001b[1;33m         \u001b[1;32mreturn\u001b[0m \u001b[0m_arrays_to_mgr\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0marrays\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mdata_names\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mindex\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcolumns\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mdtype\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mdtype\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    460\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    461\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0m_init_ndarray\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mvalues\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mindex\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcolumns\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mdtype\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;32mNone\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcopy\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;32mFalse\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\Miniconda3\\envs\\AI\\lib\\site-packages\\pandas\\core\\frame.py\u001b[0m in \u001b[0;36m_arrays_to_mgr\u001b[1;34m(arrays, arr_names, index, columns, dtype)\u001b[0m\n\u001b[0;32m   7357\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   7358\u001b[0m     \u001b[1;31m# don't force copy because getting jammed in an ndarray anyway\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 7359\u001b[1;33m     \u001b[0marrays\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0m_homogenize\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0marrays\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mindex\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mdtype\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   7360\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   7361\u001b[0m     \u001b[1;31m# from BlockManager perspective\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\Miniconda3\\envs\\AI\\lib\\site-packages\\pandas\\core\\frame.py\u001b[0m in \u001b[0;36m_homogenize\u001b[1;34m(data, index, dtype)\u001b[0m\n\u001b[0;32m   7667\u001b[0m                 \u001b[0mv\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mlib\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mfast_multiget\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mv\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0moindex\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mvalues\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mdefault\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mnp\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mnan\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   7668\u001b[0m             v = _sanitize_array(v, index, dtype=dtype, copy=False,\n\u001b[1;32m-> 7669\u001b[1;33m                                 raise_cast_failure=False)\n\u001b[0m\u001b[0;32m   7670\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   7671\u001b[0m         \u001b[0mhomogenized\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mappend\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mv\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\Miniconda3\\envs\\AI\\lib\\site-packages\\pandas\\core\\series.py\u001b[0m in \u001b[0;36m_sanitize_array\u001b[1;34m(data, index, dtype, copy, raise_cast_failure)\u001b[0m\n\u001b[0;32m   4163\u001b[0m     \u001b[1;32melif\u001b[0m \u001b[0msubarr\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mndim\u001b[0m \u001b[1;33m>\u001b[0m \u001b[1;36m1\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   4164\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0misinstance\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mdata\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mnp\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mndarray\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 4165\u001b[1;33m             \u001b[1;32mraise\u001b[0m \u001b[0mException\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'Data must be 1-dimensional'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   4166\u001b[0m         \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   4167\u001b[0m             \u001b[0msubarr\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mcom\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_asarray_tuplesafe\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mdata\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mdtype\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mdtype\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mException\u001b[0m: Data must be 1-dimensional"
     ]
    }
   ],
   "source": [
    "pred_rfc=pd.DataFrame({'State': df2.State, 'County': df2.County, 'bid_id': df2.bid_id,'Organization_Name': df2.Organization_Name,'PredictedEnr':predictions_stk})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 138,
   "metadata": {},
   "outputs": [],
   "source": [
    "pred_rfc.to_csv('C:/Users/samde/Desktop/Data Science/Datasets/Medicare Hackathon/SameKey.csv', 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
}