data_preparation.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Description"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This notebook will prepare data by convert Health Heritage Prize Data Release 3 in Claim level with categories type to Member level with one-hot type and summing type (for columns with large number value).\n",
    "Each column in Claims will convert and save to csv file for later use, due to long time processing.\n",
    "\n",
    "Files input:\n",
    "\n",
    "1. Members.csv\n",
    "2. Claims.csv\n",
    "3. DrugCount.csv\n",
    "4. LabCount.csv\n",
    "5. DaysInHospital_Y2.csv\n",
    "6. DaysInHospital_Y3.csv\n",
    "\n",
    "\n",
    "\n",
    "Files output:\n",
    "\n",
    "- MemberInfo_df.csv\n",
    "- DaysInHos_Y2.csv\n",
    "- DaysInHos_Y3.csv\n",
    "- Claims_count_Y1.csv\n",
    "- Claims_count_Y2.csv\n",
    "- Charlson_Y1.csv\n",
    "- Charlson_Y2.csv\n",
    "- DrugCount_Y1.csv\n",
    "- DrugCount_Y2.csv\n",
    "- DSFS_Y1.csv\n",
    "- DSFS_Y2.csv\n",
    "- LabCount_Y1.csv\n",
    "- LabCount_Y2.csv\n",
    "- LOS_Y1.csv\n",
    "- LOS_Y2.csv\n",
    "- PayDelay_Y1.csv\n",
    "- PayDelay_Y2.csv\n",
    "- PCPs_Y1.csv\n",
    "- PCPs_Y2.csv\n",
    "- PlaceSvc_Y1.csv\n",
    "- PlaceSvc_Y2.csv\n",
    "- PrimCondition_Y1.csv\n",
    "- PrimCondition_Y2.csv\n",
    "- Procedures_Y1.csv\n",
    "- Procedures_Y2.csv\n",
    "- Providers_Y1.csv\n",
    "- Providers_Y2.csv\n",
    "- Specialty_Y1.csv\n",
    "- Specialty_Y2.csv\n",
    "- Vendors_Y1.csv\n",
    "- Vendors_Y2.csv"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Import Library"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-02-10T12:26:11.112982Z",
     "start_time": "2019-02-10T12:26:10.068372Z"
    },
    "colab": {},
    "colab_type": "code",
    "id": "S9o2BGBp2hRj"
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 1. Member_Info Process\n",
    "- Sex: One-Hot with Male, Female, LGBT\n",
    "- AgeAtFirstClaim: replace categories string by integer mean average, exp. '10-19' to 15"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-02-09T16:20:08.255777Z",
     "start_time": "2019-02-09T16:20:07.340332Z"
    }
   },
   "outputs": [],
   "source": [
    "MemInfo_df = pd.read_csv('data/hhp_release3/Members.csv')\n",
    "\n",
    "# One-Hot to Male, Female and LGBT\n",
    "MemInfo_df['Male'] = 0\n",
    "MemInfo_df['Female'] = 0\n",
    "MemInfo_df['LGBT'] = 0\n",
    "MemInfo_df.loc[MemInfo_df.Sex == 'M', 'Male'] = 1\n",
    "MemInfo_df.loc[MemInfo_df.Sex == 'F', 'Female'] = 1\n",
    "MemInfo_df.loc[MemInfo_df.Sex.isna(), 'LGBT'] = 1\n",
    "MemInfo_df.drop(columns=['Sex'], inplace=True)\n",
    "\n",
    "# Replace Age with mean\n",
    "MemInfo_df.loc[MemInfo_df.AgeAtFirstClaim == '0-9', 'AgeAtFirstClaim'] = 5\n",
    "MemInfo_df.loc[MemInfo_df.AgeAtFirstClaim == '10-19', 'AgeAtFirstClaim'] = 15\n",
    "MemInfo_df.loc[MemInfo_df.AgeAtFirstClaim == '20-29', 'AgeAtFirstClaim'] = 25\n",
    "MemInfo_df.loc[MemInfo_df.AgeAtFirstClaim == '30-39', 'AgeAtFirstClaim'] = 35\n",
    "MemInfo_df.loc[MemInfo_df.AgeAtFirstClaim == '40-49', 'AgeAtFirstClaim'] = 45\n",
    "MemInfo_df.loc[MemInfo_df.AgeAtFirstClaim == '50-59', 'AgeAtFirstClaim'] = 55\n",
    "MemInfo_df.loc[MemInfo_df.AgeAtFirstClaim == '60-69', 'AgeAtFirstClaim'] = 65\n",
    "MemInfo_df.loc[MemInfo_df.AgeAtFirstClaim == '70-79', 'AgeAtFirstClaim'] = 75\n",
    "MemInfo_df.loc[MemInfo_df.AgeAtFirstClaim == '80+', 'AgeAtFirstClaim'] = 80\n",
    "MemInfo_df.loc[MemInfo_df.AgeAtFirstClaim.isna(), 'AgeAtFirstClaim'] = 45 # Average\n",
    "\n",
    "# Save to CSV file\n",
    "MemInfo_df.to_csv(path_or_buf='processed_data/MemberInfo_df.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    " # 2. Claims processing\n",
    " \n",
    "Claims table is claims level information with features:\n",
    " - MemberID: Patient ID\n",
    " - ProviderID: Claims provider ID\n",
    " - Vendor: Vendor ID\n",
    " - PCP: PCP ID\n",
    " - Year: Y1, Y2 or Y3\n",
    " - Specialty: Categorical data of Specialty\n",
    " - PlaceSvc: Categorical data of service place\n",
    " - PayDelay: Number in range 0 - 161 with 95% percentile top-coded as string '162+'\n",
    " - LengthOfSay: Categorical data of day length up to 6 days and weeks length range up to 8 weeks. If above that length, Supression is applied and value at SupLOS column is 1, else 0\n",
    " - DSFS: Categorical data of number of month, range from 1 to 12.\n",
    " - PrimaryConditionGroup: Categorical data of diagnostic \n",
    " - CharlsonIndex: Categorical data of CharlsonIndex\n",
    " - ProcedureGroup: Categorical data of procedures\n",
    " - SupLOS: 0 and 1 for none supresstion and suppressed in Length of Stay values, respectively.\n",
    "\n",
    "This processing step will only use Y1 data for training and Y2 for testing. Each will convert features from claims level to member level following rules:\n",
    " - ID data like Provider, Vendor and PCP will sum the unique values for each MemberID.\n",
    " - Numeric data like PayDelay will convert to integer and sum for each MemberID.\n",
    " - Other Categorical data will One-Hot with value_counts based on MemberID.\n",
    " \n",
    "Each features will be converted and save to seperated csv file."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.0. Import and Split (Y1)Train / (Y2)Test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-02-09T14:54:17.892330Z",
     "start_time": "2019-02-09T14:54:12.599762Z"
    }
   },
   "outputs": [],
   "source": [
    "claims_Y12_df = pd.read_csv('data/hhp_release3/Claims.csv').query(\"Year == 'Y1' or Year == 'Y2'\")\n",
    "claims_Y1 = claims_Y12_df.query(\"Year == 'Y1'\")\n",
    "claims_Y2 = claims_Y12_df.query(\"Year == 'Y2'\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.1. Claims Count"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-02-02T04:53:03.266460Z",
     "start_time": "2019-02-02T04:53:03.257485Z"
    },
    "code_folding": [],
    "colab": {},
    "colab_type": "code",
    "id": "ZYv51b0x-2NN"
   },
   "outputs": [],
   "source": [
    "def Claims_count(df, length=None):\n",
    "    \"\"\"\n",
    "    df: DataFrame (Y1_df or Y2_df)\n",
    "    length: int, slice [0: length] to tranfer, None for full\n",
    "    return pd.DataFrame with columns=['MemberID', 'ClaimsCount']\n",
    "    \"\"\"\n",
    "    length = df.MemberID.unique().shape[0] + 1 if length is None else length\n",
    "    member_list_count = list()\n",
    "    for mem_id, count in df.MemberID.value_counts().items():\n",
    "        member_list_count.append([mem_id, count])\n",
    "\n",
    "    return pd.DataFrame(np.array(member_list_count), columns=['MemberID', 'ClaimsCount'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-02-02T04:53:04.351659Z",
     "start_time": "2019-02-02T04:53:03.565153Z"
    }
   },
   "outputs": [],
   "source": [
    "Claims_countY1_df = Claims_count(claims_Y1)\n",
    "Claims_countY2_df = Claims_count(claims_Y2)\n",
    "\n",
    "# Save to CSV\n",
    "Claims_countY1_df.sort_values('MemberID').to_csv(path_or_buf='processed_data/Claims_count_Y1.csv', index=False)\n",
    "Claims_countY2_df.sort_values('MemberID').to_csv(path_or_buf='processed_data/Claims_count_Y2.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.2.  Count number of Unique ID"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Provider\n",
    "- Vendor\n",
    "- PCP"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-02-01T16:00:28.110654Z",
     "start_time": "2019-02-01T16:00:28.099121Z"
    },
    "code_folding": [],
    "colab": {},
    "colab_type": "code",
    "id": "ZYv51b0x-2NN"
   },
   "outputs": [],
   "source": [
    "def unique_counter(claims_df, claims_count_df, feature_name, length=None):\n",
    "    \"\"\"\n",
    "    claims_df: DataFrame (claims_Y1_df or claims_Y2_df)\n",
    "    length: int, slice [0: length] to tranfer, None for full\n",
    "    return pd.DataFrame with columns=['MemberID', 'Unique Providers']\n",
    "    \"\"\"\n",
    "    length = claims_count_df.shape[0] if length is None else length\n",
    "    member_list_count = list()\n",
    "    for mem_id in claims_count_df.MemberID:\n",
    "        feature_count = claims_df[claims_df.MemberID == mem_id][feature_name].unique().shape[0]\n",
    "        member_list_count.append([mem_id, feature_count])\n",
    "        if len(member_list_count) % 1000 == 0:\n",
    "            print(len(member_list_count), '/', length)\n",
    "        if len(member_list_count) == length:\n",
    "            break\n",
    "            \n",
    "    return pd.DataFrame(np.array(member_list_count), columns=['MemberID', \"Unique \" + feature_name])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.2.1. Providers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-02-01T16:07:46.084620Z",
     "start_time": "2019-02-01T16:02:35.711696Z"
    },
    "collapsed": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1000 / 76038\n",
      "2000 / 76038\n",
      "3000 / 76038\n",
      "4000 / 76038\n",
      "5000 / 76038\n",
      "6000 / 76038\n",
      "7000 / 76038\n",
      "8000 / 76038\n",
      "9000 / 76038\n",
      "10000 / 76038\n",
      "11000 / 76038\n",
      "12000 / 76038\n",
      "13000 / 76038\n",
      "14000 / 76038\n",
      "15000 / 76038\n",
      "16000 / 76038\n",
      "17000 / 76038\n",
      "18000 / 76038\n",
      "19000 / 76038\n",
      "20000 / 76038\n",
      "21000 / 76038\n",
      "22000 / 76038\n",
      "23000 / 76038\n",
      "24000 / 76038\n",
      "25000 / 76038\n",
      "26000 / 76038\n",
      "27000 / 76038\n",
      "28000 / 76038\n",
      "29000 / 76038\n",
      "30000 / 76038\n",
      "31000 / 76038\n",
      "32000 / 76038\n",
      "33000 / 76038\n",
      "34000 / 76038\n",
      "35000 / 76038\n",
      "36000 / 76038\n",
      "37000 / 76038\n",
      "38000 / 76038\n",
      "39000 / 76038\n",
      "40000 / 76038\n",
      "41000 / 76038\n",
      "42000 / 76038\n",
      "43000 / 76038\n",
      "44000 / 76038\n",
      "45000 / 76038\n",
      "46000 / 76038\n",
      "47000 / 76038\n",
      "48000 / 76038\n",
      "49000 / 76038\n",
      "50000 / 76038\n",
      "51000 / 76038\n",
      "52000 / 76038\n",
      "53000 / 76038\n",
      "54000 / 76038\n",
      "55000 / 76038\n",
      "56000 / 76038\n",
      "57000 / 76038\n",
      "58000 / 76038\n",
      "59000 / 76038\n",
      "60000 / 76038\n",
      "61000 / 76038\n",
      "62000 / 76038\n",
      "63000 / 76038\n",
      "64000 / 76038\n",
      "65000 / 76038\n",
      "66000 / 76038\n",
      "67000 / 76038\n",
      "68000 / 76038\n",
      "69000 / 76038\n",
      "70000 / 76038\n",
      "71000 / 76038\n",
      "72000 / 76038\n",
      "73000 / 76038\n",
      "74000 / 76038\n",
      "75000 / 76038\n",
      "76000 / 76038\n",
      "1000 / 71435\n",
      "2000 / 71435\n",
      "3000 / 71435\n",
      "4000 / 71435\n",
      "5000 / 71435\n",
      "6000 / 71435\n",
      "7000 / 71435\n",
      "8000 / 71435\n",
      "9000 / 71435\n",
      "10000 / 71435\n",
      "11000 / 71435\n",
      "12000 / 71435\n",
      "13000 / 71435\n",
      "14000 / 71435\n",
      "15000 / 71435\n",
      "16000 / 71435\n",
      "17000 / 71435\n",
      "18000 / 71435\n",
      "19000 / 71435\n",
      "20000 / 71435\n",
      "21000 / 71435\n",
      "22000 / 71435\n",
      "23000 / 71435\n",
      "24000 / 71435\n",
      "25000 / 71435\n",
      "26000 / 71435\n",
      "27000 / 71435\n",
      "28000 / 71435\n",
      "29000 / 71435\n",
      "30000 / 71435\n",
      "31000 / 71435\n",
      "32000 / 71435\n",
      "33000 / 71435\n",
      "34000 / 71435\n",
      "35000 / 71435\n",
      "36000 / 71435\n",
      "37000 / 71435\n",
      "38000 / 71435\n",
      "39000 / 71435\n",
      "40000 / 71435\n",
      "41000 / 71435\n",
      "42000 / 71435\n",
      "43000 / 71435\n",
      "44000 / 71435\n",
      "45000 / 71435\n",
      "46000 / 71435\n",
      "47000 / 71435\n",
      "48000 / 71435\n",
      "49000 / 71435\n",
      "50000 / 71435\n",
      "51000 / 71435\n",
      "52000 / 71435\n",
      "53000 / 71435\n",
      "54000 / 71435\n",
      "55000 / 71435\n",
      "56000 / 71435\n",
      "57000 / 71435\n",
      "58000 / 71435\n",
      "59000 / 71435\n",
      "60000 / 71435\n",
      "61000 / 71435\n",
      "62000 / 71435\n",
      "63000 / 71435\n",
      "64000 / 71435\n",
      "65000 / 71435\n",
      "66000 / 71435\n",
      "67000 / 71435\n",
      "68000 / 71435\n",
      "69000 / 71435\n",
      "70000 / 71435\n",
      "71000 / 71435\n"
     ]
    }
   ],
   "source": [
    "ProvidersY1_df = unique_counter(claims_Y1, Claims_countY1_df, 'ProviderID')\n",
    "ProvidersY2_df = unique_counter(claims_Y2, Claims_countY2_df, 'ProviderID')\n",
    "\n",
    "# Save to CSV\n",
    "ProvidersY1_df.sort_values('MemberID').to_csv(path_or_buf='processed_data/Providers_Y1.csv', index=False)\n",
    "ProvidersY2_df.sort_values('MemberID').to_csv(path_or_buf='processed_data/Providers_Y2.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.2.2. Vendors"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "start_time": "2019-02-01T16:02:35.709Z"
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "VendorsY1_df = unique_counter(claims_Y1, Claims_countY1_df, 'Vendor')\n",
    "VendorsY2_df = unique_counter(claims_Y2, Claims_countY2_df, 'Vendor')\n",
    "\n",
    "# Save to CSV\n",
    "VendorsY1_df.sort_values('MemberID').to_csv(path_or_buf='processed_data/Vendors_Y1.csv', index=False)\n",
    "VendorsY2_df.sort_values('MemberID').to_csv(path_or_buf='processed_data/Vendors_Y2.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.2.3. PCPs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "start_time": "2019-02-01T16:02:35.709Z"
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "PCPsY1_df = unique_counter(claims_Y1, Claims_countY1_df, 'PCP')\n",
    "PCPsY2_df = unique_counter(claims_Y2, Claims_countY2_df, 'PCP')\n",
    "\n",
    "# Save to CSV\n",
    "PCPsY1_df.sort_values('MemberID').to_csv(path_or_buf='processed_data/PCPs_Y1.csv', index=False)\n",
    "PCPsY2_df.sort_values('MemberID').to_csv(path_or_buf='processed_data/PCPs_Y2.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.3. One-Hot with value_counts for categories feature"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Specialty\n",
    "- PlaceSvc\n",
    "- PrimaryConditionGroup\n",
    "- CharlsonIndex\n",
    "- ProcedureGroup\n",
    "- DSFS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-02-02T13:09:13.893201Z",
     "start_time": "2019-02-02T13:09:13.880229Z"
    },
    "code_folding": [
     0
    ]
   },
   "outputs": [],
   "source": [
    "def one_hot_value_counts(claim_count_df, claims_df, claimY_df, col_name):\n",
    "    # add base-0 column\n",
    "    feature_df = pd.DataFrame(claim_count_df.MemberID)\n",
    "    for value, count in claims_df[col_name].value_counts().items():\n",
    "        feature_df[value] = 0\n",
    "    print('Added {} base column'.format(feature_df.shape[1] - 1))\n",
    "    \n",
    "    i = 0\n",
    "    n = len(feature_df)\n",
    "    # count and add count value to column name keys\n",
    "    for mem_id in claim_count_df.MemberID:\n",
    "        for name, count in claimY_df[claimY_df.MemberID == mem_id][col_name].value_counts().items():\n",
    "            feature_df.loc[feature_df.MemberID == mem_id, str(name)] = count\n",
    "        # print out\n",
    "        i += 1\n",
    "        if i % 1000 == 0:\n",
    "            print(i, '/', n)\n",
    "    feature_df.rename(columns={\"Other\": \"Other\" + col_name}, inplace=True)\n",
    "    print('DONE')\n",
    "    \n",
    "    return feature_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.3.1. Specialty"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-02-02T06:03:43.423701Z",
     "start_time": "2019-02-02T05:42:00.146917Z"
    },
    "collapsed": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Added 12 base column\n",
      "1000 / 71435\n",
      "2000 / 71435\n",
      "3000 / 71435\n",
      "4000 / 71435\n",
      "5000 / 71435\n",
      "6000 / 71435\n",
      "7000 / 71435\n",
      "8000 / 71435\n",
      "9000 / 71435\n",
      "10000 / 71435\n",
      "11000 / 71435\n",
      "12000 / 71435\n",
      "13000 / 71435\n",
      "14000 / 71435\n",
      "15000 / 71435\n",
      "16000 / 71435\n",
      "17000 / 71435\n",
      "18000 / 71435\n",
      "19000 / 71435\n",
      "20000 / 71435\n",
      "21000 / 71435\n",
      "22000 / 71435\n",
      "23000 / 71435\n",
      "24000 / 71435\n",
      "25000 / 71435\n",
      "26000 / 71435\n",
      "27000 / 71435\n",
      "28000 / 71435\n",
      "29000 / 71435\n",
      "30000 / 71435\n",
      "31000 / 71435\n",
      "32000 / 71435\n",
      "33000 / 71435\n",
      "34000 / 71435\n",
      "35000 / 71435\n",
      "36000 / 71435\n",
      "37000 / 71435\n",
      "38000 / 71435\n",
      "39000 / 71435\n",
      "40000 / 71435\n",
      "41000 / 71435\n",
      "42000 / 71435\n",
      "43000 / 71435\n",
      "44000 / 71435\n",
      "45000 / 71435\n",
      "46000 / 71435\n",
      "47000 / 71435\n",
      "48000 / 71435\n",
      "49000 / 71435\n",
      "50000 / 71435\n",
      "51000 / 71435\n",
      "52000 / 71435\n",
      "53000 / 71435\n",
      "54000 / 71435\n",
      "55000 / 71435\n",
      "56000 / 71435\n",
      "57000 / 71435\n",
      "58000 / 71435\n",
      "59000 / 71435\n",
      "60000 / 71435\n",
      "61000 / 71435\n",
      "62000 / 71435\n",
      "63000 / 71435\n",
      "64000 / 71435\n",
      "65000 / 71435\n",
      "66000 / 71435\n",
      "67000 / 71435\n",
      "68000 / 71435\n",
      "69000 / 71435\n",
      "70000 / 71435\n",
      "71000 / 71435\n",
      "DONE\n"
     ]
    }
   ],
   "source": [
    "SpecialtyY1_df = one_hot_value_counts(Claims_countY1_df, claims_Y12_df, claims_Y1, 'Specialty')\n",
    "SpecialtyY2_df = one_hot_value_counts(Claims_countY2_df, claims_Y12_df, claims_Y2, 'Specialty')\n",
    "\n",
    "# Save to CSV\n",
    "SpecialtyY1_df.sort_values('MemberID').to_csv(path_or_buf='processed_data/Specialty_Y1.csv', index=False)\n",
    "SpecialtyY2_df.sort_values('MemberID').to_csv(path_or_buf='processed_data/Specialty_Y2.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.3.2. PlaceSvc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-02-02T06:19:16.671210Z",
     "start_time": "2019-02-02T06:03:43.426695Z"
    },
    "collapsed": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Added 8 base column\n",
      "1000 / 71435\n",
      "2000 / 71435\n",
      "3000 / 71435\n",
      "4000 / 71435\n",
      "5000 / 71435\n",
      "6000 / 71435\n",
      "7000 / 71435\n",
      "8000 / 71435\n",
      "9000 / 71435\n",
      "10000 / 71435\n",
      "11000 / 71435\n",
      "12000 / 71435\n",
      "13000 / 71435\n",
      "14000 / 71435\n",
      "15000 / 71435\n",
      "16000 / 71435\n",
      "17000 / 71435\n",
      "18000 / 71435\n",
      "19000 / 71435\n",
      "20000 / 71435\n",
      "21000 / 71435\n",
      "22000 / 71435\n",
      "23000 / 71435\n",
      "24000 / 71435\n",
      "25000 / 71435\n",
      "26000 / 71435\n",
      "27000 / 71435\n",
      "28000 / 71435\n",
      "29000 / 71435\n",
      "30000 / 71435\n",
      "31000 / 71435\n",
      "32000 / 71435\n",
      "33000 / 71435\n",
      "34000 / 71435\n",
      "35000 / 71435\n",
      "36000 / 71435\n",
      "37000 / 71435\n",
      "38000 / 71435\n",
      "39000 / 71435\n",
      "40000 / 71435\n",
      "41000 / 71435\n",
      "42000 / 71435\n",
      "43000 / 71435\n",
      "44000 / 71435\n",
      "45000 / 71435\n",
      "46000 / 71435\n",
      "47000 / 71435\n",
      "48000 / 71435\n",
      "49000 / 71435\n",
      "50000 / 71435\n",
      "51000 / 71435\n",
      "52000 / 71435\n",
      "53000 / 71435\n",
      "54000 / 71435\n",
      "55000 / 71435\n",
      "56000 / 71435\n",
      "57000 / 71435\n",
      "58000 / 71435\n",
      "59000 / 71435\n",
      "60000 / 71435\n",
      "61000 / 71435\n",
      "62000 / 71435\n",
      "63000 / 71435\n",
      "64000 / 71435\n",
      "65000 / 71435\n",
      "66000 / 71435\n",
      "67000 / 71435\n",
      "68000 / 71435\n",
      "69000 / 71435\n",
      "70000 / 71435\n",
      "71000 / 71435\n",
      "DONE\n"
     ]
    }
   ],
   "source": [
    "PlaceSvc_Y1_df = one_hot_value_counts(Claims_countY1_df, claims_Y12_df, claims_Y1, 'PlaceSvc')\n",
    "PlaceSvc_Y2_df = one_hot_value_counts(Claims_countY2_df, claims_Y12_df, claims_Y2, 'PlaceSvc')\n",
    "\n",
    "# Save to CSV\n",
    "PlaceSvc_Y1_df.sort_values('MemberID').to_csv(path_or_buf='processed_data/PlaceSvc_Y1.csv', index=False)\n",
    "PlaceSvc_Y2_df.sort_values('MemberID').to_csv(path_or_buf='processed_data/PlaceSvc_Y2.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.3.3. PrimaryConditionGroup"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-02-02T06:42:05.244016Z",
     "start_time": "2019-02-02T06:19:16.673203Z"
    },
    "collapsed": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Added 45 base column\n",
      "1000 / 71435\n",
      "2000 / 71435\n",
      "3000 / 71435\n",
      "4000 / 71435\n",
      "5000 / 71435\n",
      "6000 / 71435\n",
      "7000 / 71435\n",
      "8000 / 71435\n",
      "9000 / 71435\n",
      "10000 / 71435\n",
      "11000 / 71435\n",
      "12000 / 71435\n",
      "13000 / 71435\n",
      "14000 / 71435\n",
      "15000 / 71435\n",
      "16000 / 71435\n",
      "17000 / 71435\n",
      "18000 / 71435\n",
      "19000 / 71435\n",
      "20000 / 71435\n",
      "21000 / 71435\n",
      "22000 / 71435\n",
      "23000 / 71435\n",
      "24000 / 71435\n",
      "25000 / 71435\n",
      "26000 / 71435\n",
      "27000 / 71435\n",
      "28000 / 71435\n",
      "29000 / 71435\n",
      "30000 / 71435\n",
      "31000 / 71435\n",
      "32000 / 71435\n",
      "33000 / 71435\n",
      "34000 / 71435\n",
      "35000 / 71435\n",
      "36000 / 71435\n",
      "37000 / 71435\n",
      "38000 / 71435\n",
      "39000 / 71435\n",
      "40000 / 71435\n",
      "41000 / 71435\n",
      "42000 / 71435\n",
      "43000 / 71435\n",
      "44000 / 71435\n",
      "45000 / 71435\n",
      "46000 / 71435\n",
      "47000 / 71435\n",
      "48000 / 71435\n",
      "49000 / 71435\n",
      "50000 / 71435\n",
      "51000 / 71435\n",
      "52000 / 71435\n",
      "53000 / 71435\n",
      "54000 / 71435\n",
      "55000 / 71435\n",
      "56000 / 71435\n",
      "57000 / 71435\n",
      "58000 / 71435\n",
      "59000 / 71435\n",
      "60000 / 71435\n",
      "61000 / 71435\n",
      "62000 / 71435\n",
      "63000 / 71435\n",
      "64000 / 71435\n",
      "65000 / 71435\n",
      "66000 / 71435\n",
      "67000 / 71435\n",
      "68000 / 71435\n",
      "69000 / 71435\n",
      "70000 / 71435\n",
      "71000 / 71435\n",
      "DONE\n"
     ]
    }
   ],
   "source": [
    "PrimCondition_Y1_df = one_hot_value_counts(Claims_countY1_df, claims_Y12_df, claims_Y1, 'PrimaryConditionGroup')\n",
    "PrimCondition_Y2_df = one_hot_value_counts(Claims_countY2_df, claims_Y12_df, claims_Y2, 'PrimaryConditionGroup')\n",
    "\n",
    "# Save to CSV\n",
    "PrimCondition_Y1_df.sort_values('MemberID').to_csv(path_or_buf='processed_data/PrimCondition_Y1.csv', index=False)\n",
    "PrimCondition_Y2_df.sort_values('MemberID').to_csv(path_or_buf='processed_data/PrimCondition_Y2.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.3.4. CharlsonIndex"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {
    "ExecuteTime": {
     "start_time": "2019-02-02T02:34:08.083Z"
    },
    "collapsed": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Added 4 base column\n",
      "10000 / 76038\n",
      "20000 / 76038\n",
      "30000 / 76038\n",
      "40000 / 76038\n",
      "50000 / 76038\n",
      "60000 / 76038\n",
      "70000 / 76038\n",
      "DONE\n",
      "Added 4 base column\n",
      "10000 / 76038\n",
      "20000 / 76038\n",
      "30000 / 76038\n",
      "40000 / 76038\n",
      "50000 / 76038\n",
      "60000 / 76038\n",
      "70000 / 76038\n",
      "DONE\n"
     ]
    }
   ],
   "source": [
    "Charlson_Y1_df = one_hot_value_counts(Claims_countY1_df, claims_Y12_df, claims_Y1, 'CharlsonIndex')\n",
    "Charlson_Y2_df = one_hot_value_counts(Claims_countY2_df, claims_Y12_df, claims_Y2, 'CharlsonIndex')\n",
    "\n",
    "# Save to CSV\n",
    "Charlson_Y1_df.sort_values('MemberID').to_csv(path_or_buf='processed_data/Charlson_Y1.csv', index=False)\n",
    "Charlson_Y1_df.sort_values('MemberID').to_csv(path_or_buf='processed_data/Charlson_Y2.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.3.5. ProcedureGroup"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-02-02T05:27:32.652470Z",
     "start_time": "2019-02-02T05:26:51.822388Z"
    },
    "collapsed": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Added 17 base column\n",
      "1000 / 76038\n"
     ]
    },
    {
     "ename": "KeyboardInterrupt",
     "evalue": "",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mKeyboardInterrupt\u001b[0m                         Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-17-e86f8912d8e7>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[1;31m# Procedures_Y1_df = one_hot_value_counts(Claims_countY1_df, claims_Y12_df, claims_Y1, 'ProcedureGroup')\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 2\u001b[1;33m \u001b[0mProcedures_Y2_df\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mone_hot_value_counts\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mClaims_countY2_df\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mclaims_Y12_df\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mclaims_Y2\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;34m'ProcedureGroup'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      3\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      4\u001b[0m \u001b[1;31m# Save to CSV\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      5\u001b[0m \u001b[1;31m# Procedures_Y1_df.to_csv(path_or_buf='processed_data/process_1/Procedures_Y1.csv', index=False)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m<ipython-input-9-4c5eb2f6e232>\u001b[0m in \u001b[0;36mone_hot_value_counts\u001b[1;34m(claim_count_df, claims_df, claimY_df, col_name)\u001b[0m\n\u001b[0;32m     10\u001b[0m     \u001b[1;31m# count and add count value to column name keys\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     11\u001b[0m     \u001b[1;32mfor\u001b[0m \u001b[0mmem_id\u001b[0m \u001b[1;32min\u001b[0m \u001b[0mclaim_count_df\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mMemberID\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 12\u001b[1;33m         \u001b[1;32mfor\u001b[0m \u001b[0mname\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcount\u001b[0m \u001b[1;32min\u001b[0m \u001b[0mclaimY_df\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mclaimY_df\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mMemberID\u001b[0m \u001b[1;33m==\u001b[0m \u001b[0mmem_id\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mcol_name\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mvalue_counts\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mitems\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m     13\u001b[0m             \u001b[0mfeature_df\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mloc\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mfeature_df\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mMemberID\u001b[0m \u001b[1;33m==\u001b[0m \u001b[0mmem_id\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mstr\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mname\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m]\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mcount\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     14\u001b[0m         \u001b[1;31m# print out\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\users\\rednam\\envs\\vietai\\lib\\site-packages\\pandas\\core\\ops.py\u001b[0m in \u001b[0;36mwrapper\u001b[1;34m(self, other, axis)\u001b[0m\n\u001b[0;32m   1764\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1765\u001b[0m             \u001b[1;32mwith\u001b[0m \u001b[0mnp\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0merrstate\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mall\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;34m'ignore'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1766\u001b[1;33m                 \u001b[0mres\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mna_op\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mvalues\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mother\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1767\u001b[0m             \u001b[1;32mif\u001b[0m \u001b[0mis_scalar\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mres\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1768\u001b[0m                 raise TypeError('Could not compare {typ} type with Series'\n",
      "\u001b[1;32mc:\\users\\rednam\\envs\\vietai\\lib\\site-packages\\pandas\\core\\ops.py\u001b[0m in \u001b[0;36mna_op\u001b[1;34m(x, y)\u001b[0m\n\u001b[0;32m   1647\u001b[0m             \u001b[1;32mif\u001b[0m \u001b[0mmethod\u001b[0m \u001b[1;32mis\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[1;32mNone\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1648\u001b[0m                 \u001b[1;32mwith\u001b[0m \u001b[0mnp\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0merrstate\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mall\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;34m'ignore'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1649\u001b[1;33m                     \u001b[0mresult\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mmethod\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0my\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1650\u001b[0m                 \u001b[1;32mif\u001b[0m \u001b[0mresult\u001b[0m \u001b[1;32mis\u001b[0m \u001b[0mNotImplemented\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1651\u001b[0m                     \u001b[1;32mreturn\u001b[0m \u001b[0minvalid_comparison\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mx\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0my\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mop\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mKeyboardInterrupt\u001b[0m: "
     ]
    }
   ],
   "source": [
    "Procedures_Y1_df = one_hot_value_counts(Claims_countY1_df, claims_Y12_df, claims_Y1, 'ProcedureGroup')\n",
    "Procedures_Y2_df = one_hot_value_counts(Claims_countY2_df, claims_Y12_df, claims_Y2, 'ProcedureGroup')\n",
    "\n",
    "# Save to CSV\n",
    "Procedures_Y1_df.sort_values('MemberID').to_csv(path_or_buf='processed_data/Procedures_Y1.csv', index=False)\n",
    "Procedures_Y2_df.sort_values('MemberID').to_csv(path_or_buf='processed_data/Procedures_Y2.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.3.6. DSFS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-02-02T14:02:58.896335Z",
     "start_time": "2019-02-02T13:09:18.030882Z"
    },
    "collapsed": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Added 12 base column\n",
      "1000 / 76038\n",
      "2000 / 76038\n",
      "3000 / 76038\n",
      "4000 / 76038\n",
      "5000 / 76038\n",
      "6000 / 76038\n",
      "7000 / 76038\n",
      "8000 / 76038\n",
      "9000 / 76038\n",
      "10000 / 76038\n",
      "11000 / 76038\n",
      "12000 / 76038\n",
      "13000 / 76038\n",
      "14000 / 76038\n",
      "15000 / 76038\n",
      "16000 / 76038\n",
      "17000 / 76038\n",
      "18000 / 76038\n",
      "19000 / 76038\n",
      "20000 / 76038\n",
      "21000 / 76038\n",
      "22000 / 76038\n",
      "23000 / 76038\n",
      "24000 / 76038\n",
      "25000 / 76038\n",
      "26000 / 76038\n",
      "27000 / 76038\n",
      "28000 / 76038\n",
      "29000 / 76038\n",
      "30000 / 76038\n",
      "31000 / 76038\n",
      "32000 / 76038\n",
      "33000 / 76038\n",
      "34000 / 76038\n",
      "35000 / 76038\n",
      "36000 / 76038\n",
      "37000 / 76038\n",
      "38000 / 76038\n",
      "39000 / 76038\n",
      "40000 / 76038\n",
      "41000 / 76038\n",
      "42000 / 76038\n",
      "43000 / 76038\n",
      "44000 / 76038\n",
      "45000 / 76038\n",
      "46000 / 76038\n",
      "47000 / 76038\n",
      "48000 / 76038\n",
      "49000 / 76038\n",
      "50000 / 76038\n",
      "51000 / 76038\n",
      "52000 / 76038\n",
      "53000 / 76038\n",
      "54000 / 76038\n",
      "55000 / 76038\n",
      "56000 / 76038\n",
      "57000 / 76038\n",
      "58000 / 76038\n",
      "59000 / 76038\n",
      "60000 / 76038\n",
      "61000 / 76038\n",
      "62000 / 76038\n",
      "63000 / 76038\n",
      "64000 / 76038\n",
      "65000 / 76038\n",
      "66000 / 76038\n",
      "67000 / 76038\n",
      "68000 / 76038\n",
      "69000 / 76038\n",
      "70000 / 76038\n",
      "71000 / 76038\n",
      "72000 / 76038\n",
      "73000 / 76038\n",
      "74000 / 76038\n",
      "75000 / 76038\n",
      "76000 / 76038\n",
      "DONE\n",
      "Added 12 base column\n",
      "1000 / 71435\n",
      "2000 / 71435\n",
      "3000 / 71435\n",
      "4000 / 71435\n",
      "5000 / 71435\n",
      "6000 / 71435\n",
      "7000 / 71435\n",
      "8000 / 71435\n",
      "9000 / 71435\n",
      "10000 / 71435\n",
      "11000 / 71435\n",
      "12000 / 71435\n",
      "13000 / 71435\n",
      "14000 / 71435\n",
      "15000 / 71435\n",
      "16000 / 71435\n",
      "17000 / 71435\n",
      "18000 / 71435\n",
      "19000 / 71435\n",
      "20000 / 71435\n",
      "21000 / 71435\n",
      "22000 / 71435\n",
      "23000 / 71435\n",
      "24000 / 71435\n",
      "25000 / 71435\n",
      "26000 / 71435\n",
      "27000 / 71435\n",
      "28000 / 71435\n",
      "29000 / 71435\n",
      "30000 / 71435\n",
      "31000 / 71435\n",
      "32000 / 71435\n",
      "33000 / 71435\n",
      "34000 / 71435\n",
      "35000 / 71435\n",
      "36000 / 71435\n",
      "37000 / 71435\n",
      "38000 / 71435\n",
      "39000 / 71435\n",
      "40000 / 71435\n",
      "41000 / 71435\n",
      "42000 / 71435\n",
      "43000 / 71435\n",
      "44000 / 71435\n",
      "45000 / 71435\n",
      "46000 / 71435\n",
      "47000 / 71435\n",
      "48000 / 71435\n",
      "49000 / 71435\n",
      "50000 / 71435\n",
      "51000 / 71435\n",
      "52000 / 71435\n",
      "53000 / 71435\n",
      "54000 / 71435\n",
      "55000 / 71435\n",
      "56000 / 71435\n",
      "57000 / 71435\n",
      "58000 / 71435\n",
      "59000 / 71435\n",
      "60000 / 71435\n",
      "61000 / 71435\n",
      "62000 / 71435\n",
      "63000 / 71435\n",
      "64000 / 71435\n",
      "65000 / 71435\n",
      "66000 / 71435\n",
      "67000 / 71435\n",
      "68000 / 71435\n",
      "69000 / 71435\n",
      "70000 / 71435\n",
      "71000 / 71435\n",
      "DONE\n"
     ]
    }
   ],
   "source": [
    "DSFS_Y1_df = one_hot_value_counts(Claims_countY1_df, claims_Y12_df, claims_Y1, 'DSFS')\n",
    "DSFS_Y2_df = one_hot_value_counts(Claims_countY2_df, claims_Y12_df, claims_Y2, 'DSFS')\n",
    "\n",
    "# Save to CSV\n",
    "DSFS_Y1_df.sort_values('MemberID').to_csv(path_or_buf='processed_data/DSFS_Y1.csv', index=False)\n",
    "DSFS_Y2_df.sort_values('MemberID').to_csv(path_or_buf='processed_data/DSFS_Y2.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.4. Other"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.4.1. LengthOfStay with SupLOS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "code_folding": []
   },
   "outputs": [],
   "source": [
    "def LOS_with_SupLOS_count(claim_count_df, claims_df, claimY_df):\n",
    "    # add base-0 column\n",
    "    feature_df = pd.DataFrame(claim_count_df.MemberID)\n",
    "    for value in claims_df.LengthOfStay.unique():\n",
    "        feature_df['LOS_' + str(value)] = 0\n",
    "    \n",
    "    print('Added {} base column'.format(feature_df.shape[1] - 1))\n",
    "    i = 0\n",
    "    n = len(feature_df)\n",
    "    # count and add count value to column name keys\n",
    "    for mem_id in claim_count_df.MemberID:\n",
    "        for name, count in claimY_df[claimY_df.MemberID == mem_id].LengthOfStay.value_counts().items():\n",
    "            if str(name) is not 'nan':\n",
    "                feature_df.loc[feature_df.MemberID == mem_id, 'LOS_' + str(name)] = count\n",
    "        \n",
    "        feature_df.loc[feature_df.MemberID == mem_id, 'LOS_nan'] = \\\n",
    "            len(claimY_df[claimY_df.MemberID == mem_id].query(\"SupLOS == 0 and LengthOfStay.isna() == True\"))\n",
    "        mem_df.loc[mem_df.MemberID == mem_id, 'SupLOS_nan'] = \\\n",
    "            len(claimY_df[claimY_df.MemberID == mem_id].query(\"SupLOS == 1\"))\n",
    "            \n",
    "        # print out\n",
    "        i += 1\n",
    "        if i % 1000 == 0:\n",
    "            print(i, '/', n)\n",
    "\n",
    "    print('DONE')\n",
    "    return feature_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-02-02T12:47:29.057798Z",
     "start_time": "2019-02-02T12:47:03.677856Z"
    },
    "collapsed": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Added 9 base column\n",
      "1000 / 76038\n",
      "2000 / 76038\n",
      "3000 / 76038\n",
      "4000 / 76038\n"
     ]
    },
    {
     "ename": "KeyboardInterrupt",
     "evalue": "",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mKeyboardInterrupt\u001b[0m                         Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-23-a49f93e8db57>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[1;32m----> 1\u001b[1;33m \u001b[0mLOS_Y1_df\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mone_hot_value_counts\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mClaims_countY1_df\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mclaims_Y12_df\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mclaims_Y1\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;34m'LengthOfStay'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      2\u001b[0m \u001b[0mLOS_Y2_df\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mone_hot_value_counts\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mClaims_countY2_df\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mclaims_Y12_df\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mclaims_Y2\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;34m'LengthOfStay'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      3\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      4\u001b[0m \u001b[1;31m# Save to CSV\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      5\u001b[0m \u001b[0mLOS_Y1_df\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mto_csv\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mpath_or_buf\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;34m'processed_data/process_1/LOS_Y1.csv'\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mindex\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;32mFalse\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m<ipython-input-19-9a07a659355b>\u001b[0m in \u001b[0;36mone_hot_value_counts\u001b[1;34m(claim_count_df, claims_df, claimY_df, col_name)\u001b[0m\n\u001b[0;32m     10\u001b[0m     \u001b[1;31m# count and add count value to column name keys\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     11\u001b[0m     \u001b[1;32mfor\u001b[0m \u001b[0mmem_id\u001b[0m \u001b[1;32min\u001b[0m \u001b[0mclaim_count_df\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mMemberID\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 12\u001b[1;33m         \u001b[1;32mfor\u001b[0m \u001b[0mname\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcount\u001b[0m \u001b[1;32min\u001b[0m \u001b[0mclaimY_df\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mclaimY_df\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mMemberID\u001b[0m \u001b[1;33m==\u001b[0m \u001b[0mmem_id\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mcol_name\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mvalue_counts\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mitems\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m     13\u001b[0m             \u001b[0mfeature_df\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mloc\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mfeature_df\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mMemberID\u001b[0m \u001b[1;33m==\u001b[0m \u001b[0mmem_id\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mstr\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mname\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m]\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mcount\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     14\u001b[0m         \u001b[1;31m# print out\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\users\\rednam\\envs\\vietai\\lib\\site-packages\\pandas\\core\\frame.py\u001b[0m in \u001b[0;36m__getitem__\u001b[1;34m(self, key)\u001b[0m\n\u001b[0;32m   2906\u001b[0m         \u001b[1;31m# Do we have a (boolean) 1d indexer?\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   2907\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0mcom\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mis_bool_indexer\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 2908\u001b[1;33m             \u001b[1;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_getitem_bool_array\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   2909\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   2910\u001b[0m         \u001b[1;31m# We are left with two options: a single key, and a collection of keys,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\users\\rednam\\envs\\vietai\\lib\\site-packages\\pandas\\core\\frame.py\u001b[0m in \u001b[0;36m_getitem_bool_array\u001b[1;34m(self, key)\u001b[0m\n\u001b[0;32m   2957\u001b[0m         \u001b[0mkey\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mcheck_bool_indexer\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mindex\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mkey\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   2958\u001b[0m         \u001b[0mindexer\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mkey\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mnonzero\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;36m0\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 2959\u001b[1;33m         \u001b[1;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_take\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mindexer\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0maxis\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;36m0\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   2960\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   2961\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0m_getitem_multilevel\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mkey\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\users\\rednam\\envs\\vietai\\lib\\site-packages\\pandas\\core\\generic.py\u001b[0m in \u001b[0;36m_take\u001b[1;34m(self, indices, axis, is_copy)\u001b[0m\n\u001b[0;32m   3347\u001b[0m         new_data = self._data.take(indices,\n\u001b[0;32m   3348\u001b[0m                                    \u001b[0maxis\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_get_block_manager_axis\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0maxis\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 3349\u001b[1;33m                                    verify=True)\n\u001b[0m\u001b[0;32m   3350\u001b[0m         \u001b[0mresult\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_constructor\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mnew_data\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m__finalize__\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   3351\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\users\\rednam\\envs\\vietai\\lib\\site-packages\\pandas\\core\\internals\\managers.py\u001b[0m in \u001b[0;36mtake\u001b[1;34m(self, indexer, axis, verify, convert)\u001b[0m\n\u001b[0;32m   1348\u001b[0m         \u001b[0mnew_labels\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0maxes\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0maxis\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mtake\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mindexer\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1349\u001b[0m         return self.reindex_indexer(new_axis=new_labels, indexer=indexer,\n\u001b[1;32m-> 1350\u001b[1;33m                                     axis=axis, allow_dups=True)\n\u001b[0m\u001b[0;32m   1351\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1352\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0mmerge\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mother\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mlsuffix\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;34m''\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mrsuffix\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;34m''\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\users\\rednam\\envs\\vietai\\lib\\site-packages\\pandas\\core\\internals\\managers.py\u001b[0m in \u001b[0;36mreindex_indexer\u001b[1;34m(self, new_axis, indexer, axis, fill_value, allow_dups, copy)\u001b[0m\n\u001b[0;32m   1233\u001b[0m             new_blocks = [blk.take_nd(indexer, axis=axis, fill_tuple=(\n\u001b[0;32m   1234\u001b[0m                 fill_value if fill_value is not None else blk.fill_value,))\n\u001b[1;32m-> 1235\u001b[1;33m                 for blk in self.blocks]\n\u001b[0m\u001b[0;32m   1236\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1237\u001b[0m         \u001b[0mnew_axes\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mlist\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0maxes\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\users\\rednam\\envs\\vietai\\lib\\site-packages\\pandas\\core\\internals\\managers.py\u001b[0m in \u001b[0;36m<listcomp>\u001b[1;34m(.0)\u001b[0m\n\u001b[0;32m   1233\u001b[0m             new_blocks = [blk.take_nd(indexer, axis=axis, fill_tuple=(\n\u001b[0;32m   1234\u001b[0m                 fill_value if fill_value is not None else blk.fill_value,))\n\u001b[1;32m-> 1235\u001b[1;33m                 for blk in self.blocks]\n\u001b[0m\u001b[0;32m   1236\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1237\u001b[0m         \u001b[0mnew_axes\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mlist\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0maxes\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\users\\rednam\\envs\\vietai\\lib\\site-packages\\pandas\\core\\internals\\blocks.py\u001b[0m in \u001b[0;36mtake_nd\u001b[1;34m(self, indexer, axis, new_mgr_locs, fill_tuple)\u001b[0m\n\u001b[0;32m   1236\u001b[0m             \u001b[0mfill_value\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mfill_tuple\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;36m0\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1237\u001b[0m             new_values = algos.take_nd(values, indexer, axis=axis,\n\u001b[1;32m-> 1238\u001b[1;33m                                        allow_fill=True, fill_value=fill_value)\n\u001b[0m\u001b[0;32m   1239\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1240\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0mnew_mgr_locs\u001b[0m \u001b[1;32mis\u001b[0m \u001b[1;32mNone\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\users\\rednam\\envs\\vietai\\lib\\site-packages\\pandas\\core\\algorithms.py\u001b[0m in \u001b[0;36mtake_nd\u001b[1;34m(arr, indexer, axis, out, fill_value, mask_info, allow_fill)\u001b[0m\n\u001b[0;32m   1643\u001b[0m             \u001b[0mout\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mnp\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mempty\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mout_shape\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[0morder\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;34m'F'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1644\u001b[0m         \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1645\u001b[1;33m             \u001b[0mout\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mnp\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mempty\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mout_shape\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   1646\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1647\u001b[0m     func = _get_take_nd_function(arr.ndim, arr.dtype, out.dtype, axis=axis,\n",
      "\u001b[1;31mKeyboardInterrupt\u001b[0m: "
     ]
    }
   ],
   "source": [
    "LOS_Y1_df = LOS_with_SupLOS_count(Claims_countY1_df, claims_Y12_df, claims_Y1)\n",
    "LOS_Y2_df = LOS_with_SupLOS_count(Claims_countY2_df, claims_Y12_df, claims_Y2)\n",
    "\n",
    "# Save to CSV\n",
    "LOS_Y1_df.sort_values('MemberID').to_csv(path_or_buf='processed_data/LOS_Y1.csv', index=False)\n",
    "LOS_Y2_df.sort_values('MemberID').to_csv(path_or_buf='processed_data/LOS_Y2.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.4.2. PayDelay"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-02-02T14:46:02.062367Z",
     "start_time": "2019-02-02T14:46:01.583649Z"
    },
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0           28\n",
       "6          162\n",
       "7           29\n",
       "11          51\n",
       "12          22\n",
       "14         143\n",
       "15         162\n",
       "16          62\n",
       "18          46\n",
       "21          24\n",
       "22         162\n",
       "26         162\n",
       "31          17\n",
       "32          28\n",
       "35          35\n",
       "51          49\n",
       "54          93\n",
       "55          23\n",
       "56          16\n",
       "57          29\n",
       "58          74\n",
       "61         162\n",
       "65         135\n",
       "66          23\n",
       "69         112\n",
       "70          65\n",
       "73          29\n",
       "74          36\n",
       "76         148\n",
       "78          63\n",
       "          ... \n",
       "2668892     80\n",
       "2668899     55\n",
       "2668906     21\n",
       "2668907     51\n",
       "2668910     30\n",
       "2668911     28\n",
       "2668914     35\n",
       "2668917    129\n",
       "2668918    162\n",
       "2668919     62\n",
       "2668920     21\n",
       "2668924     62\n",
       "2668927     29\n",
       "2668928     37\n",
       "2668930     36\n",
       "2668932     44\n",
       "2668937     37\n",
       "2668939     69\n",
       "2668944     31\n",
       "2668948     12\n",
       "2668949     17\n",
       "2668950     74\n",
       "2668951     59\n",
       "2668958     29\n",
       "2668965     37\n",
       "2668972     44\n",
       "2668975     17\n",
       "2668977     35\n",
       "2668978     38\n",
       "2668989     15\n",
       "Name: PayDelay, Length: 865689, dtype: int64"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Replace PayDelay 162+ with 162\n",
    "claims_Y1.PayDelay.replace({'162+': '162'}, inplace=True)\n",
    "claims_Y2.PayDelay.replace({'162+': '162'}, inplace=True)\n",
    "\n",
    "# Change to int with downcast\n",
    "claims_Y1['PayDelay'] = pd.to_numeric(claims_Y1.PayDelay, downcast='integer')\n",
    "claims_Y2['PayDelay'] = pd.to_numeric(claims_Y2.PayDelay, downcast='integer')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "code_folding": [
     0
    ]
   },
   "outputs": [],
   "source": [
    "def PayDelay_summing(claim_count_df, claimY_df):\n",
    "    print('PayDelay summing')\n",
    "    feature_df = pd.DataFrame(claim_count_df.MemberID)\n",
    "    feature_df['PayDelay'] = 0\n",
    "    \n",
    "    i = 0\n",
    "    n = len(feature_df)\n",
    "    # count and sum\n",
    "    for mem_id in feature_df.MemberID:\n",
    "        feature_df.loc[feature_df.MemberID == mem_id, 'PayDelay'] = \\\n",
    "            claimY_df[claimY_df.MemberID == mem_id].PayDelay.sum()\n",
    "        \n",
    "        # print out\n",
    "        i += 1\n",
    "        if i % 1000 == 0:\n",
    "            print(i, '/', n)\n",
    "    \n",
    "    print('DONE')\n",
    "    return feature_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "PayDelay_Y1_df = PayDelay_summing(Claims_countY1_df, claims_Y1)\n",
    "PayDelay_Y2_df = PayDelay_summing(Claims_countY2_df, claims_Y2)\n",
    "\n",
    "# Save to CSV\n",
    "PayDelay_Y1_df.sort_values('MemberID').to_csv(path_or_buf='processed_data/PayDelay_Y1.csv', index=False)\n",
    "PayDelay_Y2_df.sort_values('MemberID').to_csv(path_or_buf='processed_data/PayDelay_Y2.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 3 & 4. Drug and Lab Count\n",
    "\n",
    "- DrugCount is count of unique prescription drugs filled and top-coded at 7\n",
    "- LabCount is count of unique laboratory and pathology tests and top-coded at 10\n",
    "\n",
    "Both is numeric data and will be converted to integer and sum for each MemberID for each Year."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-02-02T15:18:45.167283Z",
     "start_time": "2019-02-02T15:18:45.158303Z"
    },
    "code_folding": [
     0
    ]
   },
   "outputs": [],
   "source": [
    "def Count_summing(df, claims_df, feature_name):\n",
    "    feature_df = pd.DataFrame(claims_df.MemberID)\n",
    "    feature_df[feature_name] = 0\n",
    "    \n",
    "    i = 0\n",
    "    n = len(feature_df)\n",
    "    # count and sum\n",
    "    for mem_id in feature_df.MemberID:\n",
    "        feature_df.loc[feature_df.MemberID == mem_id, feature_name] = \\\n",
    "            df[df.MemberID == mem_id][feature_name].sum()\n",
    "        \n",
    "        # print out\n",
    "        i += 1\n",
    "        if i % 1000 == 0:\n",
    "            print(i, '/', n)\n",
    "    \n",
    "    print('DONE')\n",
    "    return feature_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Import"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-02-02T15:18:43.813579Z",
     "start_time": "2019-02-02T15:18:40.991130Z"
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "DrugCount_df = pd.read_csv('data/hhp_release3/DrugCount.csv').query(\"Year == 'Y1' or Year == 'Y2'\")\n",
    "LabCount_df = pd.read_csv('data/hhp_release3/LabCount.csv').query(\"Year == 'Y1' or Year == 'Y2'\")\n",
    "\n",
    "# Replace DrugCount 7+ with 7\n",
    "DrugCount_df.DrugCount.replace({'7+': '7'}, inplace=True)\n",
    "\n",
    "# Replace LabCount 10+ with 10\n",
    "LabCount_df.LabCount.replace({'10+': '10'}, inplace=True)\n",
    "\n",
    "# Downcast to minimum int data type\n",
    "DrugCount_df.DrugCount = pd.to_numeric(DrugCount_df.DrugCount, downcast='integer')\n",
    "LabCount_df.LabCount = pd.to_numeric(LabCount_df.LabCount, downcast='integer')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Count sum"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.1. DrugCount"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-02-02T15:12:15.452722Z",
     "start_time": "2019-02-02T15:11:34.948972Z"
    },
    "collapsed": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1000 / 76038\n",
      "2000 / 76038\n",
      "3000 / 76038\n",
      "4000 / 76038\n",
      "5000 / 76038\n",
      "6000 / 76038\n",
      "7000 / 76038\n",
      "8000 / 76038\n",
      "9000 / 76038\n"
     ]
    },
    {
     "ename": "KeyboardInterrupt",
     "evalue": "",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mKeyboardInterrupt\u001b[0m                         Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-54-3b92e0524505>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[1;32m----> 1\u001b[1;33m \u001b[0mDrugCountY1_df\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mCount_summing\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mDrugCount_df\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mquery\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m\"Year == 'Y1'\"\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mClaims_countY1_df\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;34m'DrugCount'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      2\u001b[0m \u001b[0mDrugCountY2_df\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mCount_summing\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mDrugCount_df\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mquery\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m\"Year == 'Y2'\"\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mClaims_countY2_df\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;34m'DrugCount'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      3\u001b[0m \u001b[1;31m# Save to CSV\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      4\u001b[0m \u001b[0mDrugCountY1_df\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mto_csv\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mpath_or_buf\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;34m'processed_data/process_1/DrugCount_Y1.csv'\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mindex\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;32mFalse\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      5\u001b[0m \u001b[0mDrugCountY2_df\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mto_csv\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mpath_or_buf\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;34m'processed_data/process_1/DrugCount_Y2.csv'\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mindex\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;32mFalse\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m<ipython-input-53-638fa75eb3f9>\u001b[0m in \u001b[0;36mCount_summing\u001b[1;34m(df, claims_df, feature_name)\u001b[0m\n\u001b[0;32m      8\u001b[0m     \u001b[1;32mfor\u001b[0m \u001b[0mmem_id\u001b[0m \u001b[1;32min\u001b[0m \u001b[0mfeature_df\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mMemberID\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      9\u001b[0m         \u001b[0mfeature_df\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mloc\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mfeature_df\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mMemberID\u001b[0m \u001b[1;33m==\u001b[0m \u001b[0mmem_id\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mfeature_name\u001b[0m\u001b[1;33m]\u001b[0m \u001b[1;33m=\u001b[0m\u001b[0;31m \u001b[0m\u001b[0;31m\\\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 10\u001b[1;33m             \u001b[0mdf\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mdf\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mMemberID\u001b[0m \u001b[1;33m==\u001b[0m \u001b[0mmem_id\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mfeature_name\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0msum\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m     11\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     12\u001b[0m         \u001b[1;31m# print out\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\users\\rednam\\envs\\vietai\\lib\\site-packages\\pandas\\core\\indexing.py\u001b[0m in \u001b[0;36m__setitem__\u001b[1;34m(self, key, value)\u001b[0m\n\u001b[0;32m    187\u001b[0m         \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    188\u001b[0m             \u001b[0mkey\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mcom\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mapply_if_callable\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mobj\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 189\u001b[1;33m         \u001b[0mindexer\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_get_setitem_indexer\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    190\u001b[0m         \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_setitem_with_indexer\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mindexer\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mvalue\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    191\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\users\\rednam\\envs\\vietai\\lib\\site-packages\\pandas\\core\\indexing.py\u001b[0m in \u001b[0;36m_get_setitem_indexer\u001b[1;34m(self, key)\u001b[0m\n\u001b[0;32m    165\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0misinstance\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mtuple\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    166\u001b[0m             \u001b[1;32mtry\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 167\u001b[1;33m                 \u001b[1;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_convert_tuple\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mis_setter\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;32mTrue\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    168\u001b[0m             \u001b[1;32mexcept\u001b[0m \u001b[0mIndexingError\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    169\u001b[0m                 \u001b[1;32mpass\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\users\\rednam\\envs\\vietai\\lib\\site-packages\\pandas\\core\\indexing.py\u001b[0m in \u001b[0;36m_convert_tuple\u001b[1;34m(self, key, is_setter)\u001b[0m\n\u001b[0;32m    246\u001b[0m                 \u001b[1;32mif\u001b[0m \u001b[0mi\u001b[0m \u001b[1;33m>=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mobj\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mndim\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    247\u001b[0m                     \u001b[1;32mraise\u001b[0m \u001b[0mIndexingError\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'Too many indexers'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 248\u001b[1;33m                 \u001b[0midx\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_convert_to_indexer\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mk\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0maxis\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mi\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mis_setter\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mis_setter\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    249\u001b[0m                 \u001b[0mkeyidx\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mappend\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0midx\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    250\u001b[0m         \u001b[1;32mreturn\u001b[0m \u001b[0mtuple\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mkeyidx\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\users\\rednam\\envs\\vietai\\lib\\site-packages\\pandas\\core\\indexing.py\u001b[0m in \u001b[0;36m_convert_to_indexer\u001b[1;34m(self, obj, axis, is_setter, raise_missing)\u001b[0m\n\u001b[0;32m   1308\u001b[0m         \u001b[1;31m# if we are a label return me\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1309\u001b[0m         \u001b[1;32mtry\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1310\u001b[1;33m             \u001b[1;32mreturn\u001b[0m \u001b[0mlabels\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mget_loc\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mobj\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1311\u001b[0m         \u001b[1;32mexcept\u001b[0m \u001b[0mLookupError\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1312\u001b[0m             \u001b[1;32mif\u001b[0m \u001b[0misinstance\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mobj\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mtuple\u001b[0m\u001b[1;33m)\u001b[0m \u001b[1;32mand\u001b[0m \u001b[0misinstance\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mlabels\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mMultiIndex\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\users\\rednam\\envs\\vietai\\lib\\site-packages\\pandas\\core\\indexes\\base.py\u001b[0m in \u001b[0;36mget_loc\u001b[1;34m(self, key, method, tolerance)\u001b[0m\n\u001b[0;32m   2600\u001b[0m                                  'backfill or nearest lookups')\n\u001b[0;32m   2601\u001b[0m             \u001b[1;32mtry\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 2602\u001b[1;33m                 \u001b[1;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_engine\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mget_loc\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   2603\u001b[0m             \u001b[1;32mexcept\u001b[0m \u001b[0mKeyError\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   2604\u001b[0m                 \u001b[1;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_engine\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mget_loc\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_maybe_cast_indexer\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mpandas\\_libs\\index.pyx\u001b[0m in \u001b[0;36mpandas._libs.index.IndexEngine.get_loc\u001b[1;34m()\u001b[0m\n",
      "\u001b[1;32mpandas\\_libs\\index.pyx\u001b[0m in \u001b[0;36mpandas._libs.index.IndexEngine.get_loc\u001b[1;34m()\u001b[0m\n",
      "\u001b[1;32mc:\\users\\rednam\\envs\\vietai\\lib\\site-packages\\pandas\\core\\base.py\u001b[0m in \u001b[0;36m__str__\u001b[1;34m(self)\u001b[0m\n\u001b[0;32m     55\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     56\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0mcompat\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mPY3\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 57\u001b[1;33m             \u001b[1;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m__unicode__\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m     58\u001b[0m         \u001b[1;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m__bytes__\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     59\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\users\\rednam\\envs\\vietai\\lib\\site-packages\\pandas\\core\\series.py\u001b[0m in \u001b[0;36m__unicode__\u001b[1;34m(self)\u001b[0m\n\u001b[0;32m   1381\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1382\u001b[0m         self.to_string(buf=buf, name=self.name, dtype=self.dtype,\n\u001b[1;32m-> 1383\u001b[1;33m                        max_rows=max_rows, length=show_dimensions)\n\u001b[0m\u001b[0;32m   1384\u001b[0m         \u001b[0mresult\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mbuf\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mgetvalue\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1385\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\users\\rednam\\envs\\vietai\\lib\\site-packages\\pandas\\core\\series.py\u001b[0m in \u001b[0;36mto_string\u001b[1;34m(self, buf, na_rep, float_format, header, index, length, dtype, name, max_rows)\u001b[0m\n\u001b[0;32m   1424\u001b[0m                                         \u001b[0mdtype\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mdtype\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mna_rep\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mna_rep\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1425\u001b[0m                                         \u001b[0mfloat_format\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mfloat_format\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1426\u001b[1;33m                                         max_rows=max_rows)\n\u001b[0m\u001b[0;32m   1427\u001b[0m         \u001b[0mresult\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mformatter\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mto_string\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1428\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\users\\rednam\\envs\\vietai\\lib\\site-packages\\pandas\\io\\formats\\format.py\u001b[0m in \u001b[0;36m__init__\u001b[1;34m(self, series, buf, length, header, index, na_rep, name, float_format, dtype, max_rows)\u001b[0m\n\u001b[0;32m    181\u001b[0m         \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0madj\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0m_get_adjustment\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    182\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 183\u001b[1;33m         \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_chk_truncate\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    184\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    185\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0m_chk_truncate\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\users\\rednam\\envs\\vietai\\lib\\site-packages\\pandas\\io\\formats\\format.py\u001b[0m in \u001b[0;36m_chk_truncate\u001b[1;34m(self)\u001b[0m\n\u001b[0;32m    195\u001b[0m                 \u001b[0mrow_num\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mmax_rows\u001b[0m \u001b[1;33m//\u001b[0m \u001b[1;36m2\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    196\u001b[0m                 series = concat((series.iloc[:row_num],\n\u001b[1;32m--> 197\u001b[1;33m                                  series.iloc[-row_num:]))\n\u001b[0m\u001b[0;32m    198\u001b[0m             \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mtr_row_num\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mrow_num\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    199\u001b[0m         \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mtr_series\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mseries\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\users\\rednam\\envs\\vietai\\lib\\site-packages\\pandas\\core\\reshape\\concat.py\u001b[0m in \u001b[0;36mconcat\u001b[1;34m(objs, axis, join, join_axes, ignore_index, keys, levels, names, verify_integrity, sort, copy)\u001b[0m\n\u001b[0;32m    227\u001b[0m                        \u001b[0mverify_integrity\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mverify_integrity\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    228\u001b[0m                        copy=copy, sort=sort)\n\u001b[1;32m--> 229\u001b[1;33m     \u001b[1;32mreturn\u001b[0m \u001b[0mop\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mget_result\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    230\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    231\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\users\\rednam\\envs\\vietai\\lib\\site-packages\\pandas\\core\\reshape\\concat.py\u001b[0m in \u001b[0;36mget_result\u001b[1;34m(self)\u001b[0m\n\u001b[0;32m    391\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    392\u001b[0m                 mgr = self.objs[0]._data.concat([x._data for x in self.objs],\n\u001b[1;32m--> 393\u001b[1;33m                                                 self.new_axes)\n\u001b[0m\u001b[0;32m    394\u001b[0m                 \u001b[0mcons\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0m_concat\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_get_series_result_type\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mmgr\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mobjs\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    395\u001b[0m                 \u001b[1;32mreturn\u001b[0m \u001b[0mcons\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mmgr\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mname\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mname\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m__finalize__\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mmethod\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;34m'concat'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\users\\rednam\\envs\\vietai\\lib\\site-packages\\pandas\\core\\internals\\managers.py\u001b[0m in \u001b[0;36mconcat\u001b[1;34m(self, to_concat, new_axis)\u001b[0m\n\u001b[0;32m   1619\u001b[0m             \u001b[0mblocks\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;33m[\u001b[0m\u001b[0mobj\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mblocks\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;36m0\u001b[0m\u001b[1;33m]\u001b[0m \u001b[1;32mfor\u001b[0m \u001b[0mobj\u001b[0m \u001b[1;32min\u001b[0m \u001b[0mnon_empties\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1620\u001b[0m             \u001b[1;32mif\u001b[0m \u001b[0mlen\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m{\u001b[0m\u001b[0mb\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mdtype\u001b[0m \u001b[1;32mfor\u001b[0m \u001b[0mb\u001b[0m \u001b[1;32min\u001b[0m \u001b[0mblocks\u001b[0m\u001b[1;33m}\u001b[0m\u001b[1;33m)\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[1;32m-> 1621\u001b[1;33m                 \u001b[0mnew_block\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mblocks\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;36m0\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mconcat_same_type\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mblocks\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1622\u001b[0m             \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1623\u001b[0m                 \u001b[0mvalues\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;33m[\u001b[0m\u001b[0mx\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mvalues\u001b[0m \u001b[1;32mfor\u001b[0m \u001b[0mx\u001b[0m \u001b[1;32min\u001b[0m \u001b[0mblocks\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\users\\rednam\\envs\\vietai\\lib\\site-packages\\pandas\\core\\internals\\blocks.py\u001b[0m in \u001b[0;36mconcat_same_type\u001b[1;34m(self, to_concat, placement)\u001b[0m\n\u001b[0;32m    326\u001b[0m         \"\"\"\n\u001b[0;32m    327\u001b[0m         values = self._concatenator([blk.values for blk in to_concat],\n\u001b[1;32m--> 328\u001b[1;33m                                     axis=self.ndim - 1)\n\u001b[0m\u001b[0;32m    329\u001b[0m         return self.make_block_same_class(\n\u001b[0;32m    330\u001b[0m             values, placement=placement or slice(0, len(values), 1))\n",
      "\u001b[1;31mKeyboardInterrupt\u001b[0m: "
     ]
    }
   ],
   "source": [
    "DrugCountY1_df = Count_summing(DrugCount_df.query(\"Year == 'Y1'\"), Claims_countY1_df,'DrugCount')\n",
    "DrugCountY2_df = Count_summing(DrugCount_df.query(\"Year == 'Y2'\"), Claims_countY2_df,'DrugCount')\n",
    "\n",
    "# Save to CSV\n",
    "DrugCountY1_df.sort_values('MemberID').to_csv(path_or_buf='processed_data/DrugCount_Y1.csv', index=False)\n",
    "DrugCountY2_df.sort_values('MemberID').to_csv(path_or_buf='processed_data/DrugCount_Y2.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.2. LabCount"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-02-02T15:29:49.553394Z",
     "start_time": "2019-02-02T15:19:01.809538Z"
    },
    "collapsed": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1000 / 76038\n",
      "2000 / 76038\n",
      "3000 / 76038\n",
      "4000 / 76038\n",
      "5000 / 76038\n",
      "6000 / 76038\n",
      "7000 / 76038\n",
      "8000 / 76038\n",
      "9000 / 76038\n",
      "10000 / 76038\n",
      "11000 / 76038\n",
      "12000 / 76038\n",
      "13000 / 76038\n",
      "14000 / 76038\n",
      "15000 / 76038\n",
      "16000 / 76038\n",
      "17000 / 76038\n",
      "18000 / 76038\n",
      "19000 / 76038\n",
      "20000 / 76038\n",
      "21000 / 76038\n",
      "22000 / 76038\n",
      "23000 / 76038\n",
      "24000 / 76038\n",
      "25000 / 76038\n",
      "26000 / 76038\n",
      "27000 / 76038\n",
      "28000 / 76038\n",
      "29000 / 76038\n",
      "30000 / 76038\n",
      "31000 / 76038\n",
      "32000 / 76038\n",
      "33000 / 76038\n",
      "34000 / 76038\n",
      "35000 / 76038\n",
      "36000 / 76038\n",
      "37000 / 76038\n",
      "38000 / 76038\n",
      "39000 / 76038\n",
      "40000 / 76038\n",
      "41000 / 76038\n",
      "42000 / 76038\n",
      "43000 / 76038\n",
      "44000 / 76038\n",
      "45000 / 76038\n",
      "46000 / 76038\n",
      "47000 / 76038\n",
      "48000 / 76038\n",
      "49000 / 76038\n",
      "50000 / 76038\n",
      "51000 / 76038\n",
      "52000 / 76038\n",
      "53000 / 76038\n",
      "54000 / 76038\n",
      "55000 / 76038\n",
      "56000 / 76038\n",
      "57000 / 76038\n",
      "58000 / 76038\n",
      "59000 / 76038\n",
      "60000 / 76038\n",
      "61000 / 76038\n",
      "62000 / 76038\n",
      "63000 / 76038\n",
      "64000 / 76038\n",
      "65000 / 76038\n",
      "66000 / 76038\n",
      "67000 / 76038\n",
      "68000 / 76038\n",
      "69000 / 76038\n",
      "70000 / 76038\n",
      "71000 / 76038\n",
      "72000 / 76038\n",
      "73000 / 76038\n",
      "74000 / 76038\n",
      "75000 / 76038\n",
      "76000 / 76038\n",
      "DONE\n",
      "1000 / 71435\n",
      "2000 / 71435\n",
      "3000 / 71435\n",
      "4000 / 71435\n",
      "5000 / 71435\n",
      "6000 / 71435\n",
      "7000 / 71435\n",
      "8000 / 71435\n",
      "9000 / 71435\n",
      "10000 / 71435\n",
      "11000 / 71435\n",
      "12000 / 71435\n",
      "13000 / 71435\n",
      "14000 / 71435\n",
      "15000 / 71435\n",
      "16000 / 71435\n",
      "17000 / 71435\n",
      "18000 / 71435\n",
      "19000 / 71435\n",
      "20000 / 71435\n",
      "21000 / 71435\n",
      "22000 / 71435\n",
      "23000 / 71435\n",
      "24000 / 71435\n",
      "25000 / 71435\n",
      "26000 / 71435\n",
      "27000 / 71435\n",
      "28000 / 71435\n",
      "29000 / 71435\n",
      "30000 / 71435\n",
      "31000 / 71435\n",
      "32000 / 71435\n",
      "33000 / 71435\n",
      "34000 / 71435\n",
      "35000 / 71435\n",
      "36000 / 71435\n",
      "37000 / 71435\n",
      "38000 / 71435\n",
      "39000 / 71435\n",
      "40000 / 71435\n",
      "41000 / 71435\n",
      "42000 / 71435\n",
      "43000 / 71435\n",
      "44000 / 71435\n",
      "45000 / 71435\n",
      "46000 / 71435\n",
      "47000 / 71435\n",
      "48000 / 71435\n",
      "49000 / 71435\n",
      "50000 / 71435\n",
      "51000 / 71435\n",
      "52000 / 71435\n",
      "53000 / 71435\n",
      "54000 / 71435\n",
      "55000 / 71435\n",
      "56000 / 71435\n",
      "57000 / 71435\n",
      "58000 / 71435\n",
      "59000 / 71435\n",
      "60000 / 71435\n",
      "61000 / 71435\n",
      "62000 / 71435\n",
      "63000 / 71435\n",
      "64000 / 71435\n",
      "65000 / 71435\n",
      "66000 / 71435\n",
      "67000 / 71435\n",
      "68000 / 71435\n",
      "69000 / 71435\n",
      "70000 / 71435\n",
      "71000 / 71435\n",
      "DONE\n"
     ]
    }
   ],
   "source": [
    "LabCountY1_df = Count_summing(LabCount_df.query(\"Year == 'Y1'\"), Claims_countY1_df,'LabCount')\n",
    "LabCountY2_df = Count_summing(LabCount_df.query(\"Year == 'Y2'\"), Claims_countY2_df,'LabCount')\n",
    "\n",
    "# Save to CSV\n",
    "LabCountY1_df.sort_values('MemberID').to_csv(path_or_buf='processed_data/LabCount_Y1.csv', index=False)\n",
    "LabCountY2_df.sort_values('MemberID').to_csv(path_or_buf='processed_data/LabCount_Y2.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 5 & 6. Label\n",
    "\n",
    "Drop ClaimsTruncated, sort based on MemberID and save"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-02-10T12:26:16.084271Z",
     "start_time": "2019-02-10T12:26:15.411247Z"
    }
   },
   "outputs": [],
   "source": [
    "# Save to CSV\n",
    "pd.read_csv('data/HHP_release3/DaysInHospital_Y2.csv').sort_values('MemberID').to_csv(path_or_buf='processed_data/DaysInHos_Y2.csv', index=False)\n",
    "pd.read_csv('data/HHP_release3/DaysInHospital_Y3.csv').sort_values('MemberID').to_csv(path_or_buf='processed_data/DaysInHos_Y3.csv', index=False)"
   ]
  }
 ],
 "metadata": {
  "colab": {
   "collapsed_sections": [],
   "name": "VEF_final.ipynb",
   "provenance": [
    {
     "file_id": "15yxVyMLvFPzWS13j9Rq-cfjyfKq-0NYM",
     "timestamp": 1547877570663
    },
    {
     "file_id": "1Lq4ex0pyOb3MC9F6zGygU9QxDbD-hlD_",
     "timestamp": 1547818820003
    }
   ],
   "version": "0.3.2"
  },
  "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.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}