Transformer.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "40decfa8",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "from sklearn.base import BaseEstimator, TransformerMixin\n",
    "from sklearn.pipeline import Pipeline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "5bfc5a34",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.preprocessing import OneHotEncoder, MinMaxScaler, StandardScaler"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "62cde1f6",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.compose import ColumnTransformer"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "984641e1",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "80a147b3",
   "metadata": {},
   "source": [
    "### Load and merge data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "fe14d770",
   "metadata": {},
   "outputs": [],
   "source": [
    "# load the data from csv to pandas dataframe\n",
    "Provider_raw = pd.read_csv(\"data/Train-1542865627584.csv\")\n",
    "Beneficiary_raw = pd.read_csv(\"data/Train_Beneficiarydata-1542865627584.csv\")\n",
    "Inpatient_raw = pd.read_csv(\"data/Train_Inpatientdata-1542865627584.csv\")\n",
    "Outpatient_raw = pd.read_csv(\"data/Train_Outpatientdata-1542865627584.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "f6e34dcc",
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>BeneID</th>\n",
       "      <th>ClaimStartDt</th>\n",
       "      <th>ClaimEndDt</th>\n",
       "      <th>Provider</th>\n",
       "      <th>InscClaimAmtReimbursed</th>\n",
       "      <th>AttendingPhysician</th>\n",
       "      <th>OperatingPhysician</th>\n",
       "      <th>OtherPhysician</th>\n",
       "      <th>AdmissionDt</th>\n",
       "      <th>ClmAdmitDiagnosisCode</th>\n",
       "      <th>...</th>\n",
       "      <th>ChronicCond_Diabetes</th>\n",
       "      <th>ChronicCond_IschemicHeart</th>\n",
       "      <th>ChronicCond_Osteoporasis</th>\n",
       "      <th>ChronicCond_rheumatoidarthritis</th>\n",
       "      <th>ChronicCond_stroke</th>\n",
       "      <th>IPAnnualReimbursementAmt</th>\n",
       "      <th>IPAnnualDeductibleAmt</th>\n",
       "      <th>OPAnnualReimbursementAmt</th>\n",
       "      <th>OPAnnualDeductibleAmt</th>\n",
       "      <th>PotentialFraud</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BENE11001</td>\n",
       "      <td>2009-04-12</td>\n",
       "      <td>2009-04-18</td>\n",
       "      <td>PRV55912</td>\n",
       "      <td>26000</td>\n",
       "      <td>PHY390922</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2009-04-12</td>\n",
       "      <td>7866</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>36000</td>\n",
       "      <td>3204</td>\n",
       "      <td>60</td>\n",
       "      <td>70</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>BENE11001</td>\n",
       "      <td>2009-08-31</td>\n",
       "      <td>2009-09-02</td>\n",
       "      <td>PRV55907</td>\n",
       "      <td>5000</td>\n",
       "      <td>PHY318495</td>\n",
       "      <td>PHY318495</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2009-08-31</td>\n",
       "      <td>6186</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>36000</td>\n",
       "      <td>3204</td>\n",
       "      <td>60</td>\n",
       "      <td>70</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BENE11001</td>\n",
       "      <td>2009-09-17</td>\n",
       "      <td>2009-09-20</td>\n",
       "      <td>PRV56046</td>\n",
       "      <td>5000</td>\n",
       "      <td>PHY372395</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PHY324689</td>\n",
       "      <td>2009-09-17</td>\n",
       "      <td>29590</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>36000</td>\n",
       "      <td>3204</td>\n",
       "      <td>60</td>\n",
       "      <td>70</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BENE11011</td>\n",
       "      <td>2009-02-14</td>\n",
       "      <td>2009-02-22</td>\n",
       "      <td>PRV52405</td>\n",
       "      <td>5000</td>\n",
       "      <td>PHY369659</td>\n",
       "      <td>PHY392961</td>\n",
       "      <td>PHY349768</td>\n",
       "      <td>2009-02-14</td>\n",
       "      <td>431</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5000</td>\n",
       "      <td>1068</td>\n",
       "      <td>250</td>\n",
       "      <td>320</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BENE11014</td>\n",
       "      <td>2009-08-13</td>\n",
       "      <td>2009-08-30</td>\n",
       "      <td>PRV56614</td>\n",
       "      <td>10000</td>\n",
       "      <td>PHY379376</td>\n",
       "      <td>PHY398258</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2009-08-13</td>\n",
       "      <td>78321</td>\n",
       "      <td>...</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>21260</td>\n",
       "      <td>2136</td>\n",
       "      <td>120</td>\n",
       "      <td>100</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>558206</th>\n",
       "      <td>BENE159198</td>\n",
       "      <td>2009-08-06</td>\n",
       "      <td>2009-08-06</td>\n",
       "      <td>PRV53699</td>\n",
       "      <td>800</td>\n",
       "      <td>PHY364188</td>\n",
       "      <td>PHY364188</td>\n",
       "      <td>PHY385752</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5470</td>\n",
       "      <td>1870</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>558207</th>\n",
       "      <td>BENE159198</td>\n",
       "      <td>2009-08-29</td>\n",
       "      <td>2009-08-29</td>\n",
       "      <td>PRV53702</td>\n",
       "      <td>400</td>\n",
       "      <td>PHY423019</td>\n",
       "      <td>PHY332284</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5470</td>\n",
       "      <td>1870</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>558208</th>\n",
       "      <td>BENE159198</td>\n",
       "      <td>2009-09-24</td>\n",
       "      <td>2009-09-24</td>\n",
       "      <td>PRV53676</td>\n",
       "      <td>60</td>\n",
       "      <td>PHY361063</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5470</td>\n",
       "      <td>1870</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>558209</th>\n",
       "      <td>BENE159198</td>\n",
       "      <td>2009-10-18</td>\n",
       "      <td>2009-10-18</td>\n",
       "      <td>PRV53689</td>\n",
       "      <td>70</td>\n",
       "      <td>PHY403198</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PHY419379</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5470</td>\n",
       "      <td>1870</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>558210</th>\n",
       "      <td>BENE159198</td>\n",
       "      <td>2009-11-17</td>\n",
       "      <td>2009-11-18</td>\n",
       "      <td>PRV53689</td>\n",
       "      <td>80</td>\n",
       "      <td>PHY419379</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PHY419379</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5470</td>\n",
       "      <td>1870</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>558211 rows × 54 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            BeneID ClaimStartDt  ClaimEndDt  Provider  InscClaimAmtReimbursed  \\\n",
       "0        BENE11001   2009-04-12  2009-04-18  PRV55912                   26000   \n",
       "1        BENE11001   2009-08-31  2009-09-02  PRV55907                    5000   \n",
       "2        BENE11001   2009-09-17  2009-09-20  PRV56046                    5000   \n",
       "3        BENE11011   2009-02-14  2009-02-22  PRV52405                    5000   \n",
       "4        BENE11014   2009-08-13  2009-08-30  PRV56614                   10000   \n",
       "...            ...          ...         ...       ...                     ...   \n",
       "558206  BENE159198   2009-08-06  2009-08-06  PRV53699                     800   \n",
       "558207  BENE159198   2009-08-29  2009-08-29  PRV53702                     400   \n",
       "558208  BENE159198   2009-09-24  2009-09-24  PRV53676                      60   \n",
       "558209  BENE159198   2009-10-18  2009-10-18  PRV53689                      70   \n",
       "558210  BENE159198   2009-11-17  2009-11-18  PRV53689                      80   \n",
       "\n",
       "       AttendingPhysician OperatingPhysician OtherPhysician AdmissionDt  \\\n",
       "0               PHY390922                NaN            NaN  2009-04-12   \n",
       "1               PHY318495          PHY318495            NaN  2009-08-31   \n",
       "2               PHY372395                NaN      PHY324689  2009-09-17   \n",
       "3               PHY369659          PHY392961      PHY349768  2009-02-14   \n",
       "4               PHY379376          PHY398258            NaN  2009-08-13   \n",
       "...                   ...                ...            ...         ...   \n",
       "558206          PHY364188          PHY364188      PHY385752         NaN   \n",
       "558207          PHY423019          PHY332284            NaN         NaN   \n",
       "558208          PHY361063                NaN            NaN         NaN   \n",
       "558209          PHY403198                NaN      PHY419379         NaN   \n",
       "558210          PHY419379                NaN      PHY419379         NaN   \n",
       "\n",
       "       ClmAdmitDiagnosisCode  ...  ChronicCond_Diabetes  \\\n",
       "0                       7866  ...                     1   \n",
       "1                       6186  ...                     1   \n",
       "2                      29590  ...                     1   \n",
       "3                        431  ...                     1   \n",
       "4                      78321  ...                     2   \n",
       "...                      ...  ...                   ...   \n",
       "558206                   NaN  ...                     1   \n",
       "558207                   NaN  ...                     1   \n",
       "558208                   NaN  ...                     1   \n",
       "558209                   NaN  ...                     1   \n",
       "558210                   NaN  ...                     1   \n",
       "\n",
       "       ChronicCond_IschemicHeart ChronicCond_Osteoporasis  \\\n",
       "0                              1                        2   \n",
       "1                              1                        2   \n",
       "2                              1                        2   \n",
       "3                              2                        2   \n",
       "4                              1                        2   \n",
       "...                          ...                      ...   \n",
       "558206                         2                        2   \n",
       "558207                         2                        2   \n",
       "558208                         2                        2   \n",
       "558209                         2                        2   \n",
       "558210                         2                        2   \n",
       "\n",
       "       ChronicCond_rheumatoidarthritis ChronicCond_stroke  \\\n",
       "0                                    1                  1   \n",
       "1                                    1                  1   \n",
       "2                                    1                  1   \n",
       "3                                    1                  1   \n",
       "4                                    2                  2   \n",
       "...                                ...                ...   \n",
       "558206                               1                  2   \n",
       "558207                               1                  2   \n",
       "558208                               1                  2   \n",
       "558209                               1                  2   \n",
       "558210                               1                  2   \n",
       "\n",
       "       IPAnnualReimbursementAmt IPAnnualDeductibleAmt  \\\n",
       "0                         36000                  3204   \n",
       "1                         36000                  3204   \n",
       "2                         36000                  3204   \n",
       "3                          5000                  1068   \n",
       "4                         21260                  2136   \n",
       "...                         ...                   ...   \n",
       "558206                        0                     0   \n",
       "558207                        0                     0   \n",
       "558208                        0                     0   \n",
       "558209                        0                     0   \n",
       "558210                        0                     0   \n",
       "\n",
       "       OPAnnualReimbursementAmt OPAnnualDeductibleAmt PotentialFraud  \n",
       "0                            60                    70            Yes  \n",
       "1                            60                    70             No  \n",
       "2                            60                    70             No  \n",
       "3                           250                   320             No  \n",
       "4                           120                   100             No  \n",
       "...                         ...                   ...            ...  \n",
       "558206                     5470                  1870             No  \n",
       "558207                     5470                  1870             No  \n",
       "558208                     5470                  1870             No  \n",
       "558209                     5470                  1870             No  \n",
       "558210                     5470                  1870             No  \n",
       "\n",
       "[558211 rows x 54 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Assign inpatient data as 1, outpatient data as 0\n",
    "Inpatient_raw['In_Out']=1\n",
    "Outpatient_raw['In_Out']=0\n",
    "\n",
    "# union/concat the inpatient and outpatient data\n",
    "concat_df=pd.concat([Inpatient_raw,Outpatient_raw],axis=0)\n",
    "\n",
    "# left join tables\n",
    "merge_bene_df=concat_df.merge(Beneficiary_raw, on='BeneID', how='left')\n",
    "merge_provider_df=merge_bene_df.merge(Provider_raw, on = 'Provider', how ='left')\n",
    "merge_provider_df.drop_duplicates(inplace=True)\n",
    "\n",
    "# remove columns with all null values\n",
    "merge_provider_df.dropna(axis=1, how='all',inplace=True)\n",
    "\n",
    "# drop ClaimID since it behaves as index\n",
    "merge_provider_df.drop('ClaimID',axis=1, inplace=True)\n",
    "\n",
    "# check the final merged dataframe\n",
    "merge_provider_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f2913a60",
   "metadata": {},
   "source": [
    "## label 1 as fraud, 0 as genuine"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "id": "78cd266f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>BeneID</th>\n",
       "      <th>ClaimStartDt</th>\n",
       "      <th>ClaimEndDt</th>\n",
       "      <th>Provider</th>\n",
       "      <th>InscClaimAmtReimbursed</th>\n",
       "      <th>AttendingPhysician</th>\n",
       "      <th>OperatingPhysician</th>\n",
       "      <th>OtherPhysician</th>\n",
       "      <th>AdmissionDt</th>\n",
       "      <th>ClmAdmitDiagnosisCode</th>\n",
       "      <th>...</th>\n",
       "      <th>ChronicCond_Diabetes</th>\n",
       "      <th>ChronicCond_IschemicHeart</th>\n",
       "      <th>ChronicCond_Osteoporasis</th>\n",
       "      <th>ChronicCond_rheumatoidarthritis</th>\n",
       "      <th>ChronicCond_stroke</th>\n",
       "      <th>IPAnnualReimbursementAmt</th>\n",
       "      <th>IPAnnualDeductibleAmt</th>\n",
       "      <th>OPAnnualReimbursementAmt</th>\n",
       "      <th>OPAnnualDeductibleAmt</th>\n",
       "      <th>PotentialFraud</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BENE11001</td>\n",
       "      <td>2009-04-12</td>\n",
       "      <td>2009-04-18</td>\n",
       "      <td>PRV55912</td>\n",
       "      <td>26000</td>\n",
       "      <td>PHY390922</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2009-04-12</td>\n",
       "      <td>7866</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>36000</td>\n",
       "      <td>3204</td>\n",
       "      <td>60</td>\n",
       "      <td>70</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>BENE11001</td>\n",
       "      <td>2009-08-31</td>\n",
       "      <td>2009-09-02</td>\n",
       "      <td>PRV55907</td>\n",
       "      <td>5000</td>\n",
       "      <td>PHY318495</td>\n",
       "      <td>PHY318495</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2009-08-31</td>\n",
       "      <td>6186</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>36000</td>\n",
       "      <td>3204</td>\n",
       "      <td>60</td>\n",
       "      <td>70</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BENE11001</td>\n",
       "      <td>2009-09-17</td>\n",
       "      <td>2009-09-20</td>\n",
       "      <td>PRV56046</td>\n",
       "      <td>5000</td>\n",
       "      <td>PHY372395</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PHY324689</td>\n",
       "      <td>2009-09-17</td>\n",
       "      <td>29590</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>36000</td>\n",
       "      <td>3204</td>\n",
       "      <td>60</td>\n",
       "      <td>70</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BENE11011</td>\n",
       "      <td>2009-02-14</td>\n",
       "      <td>2009-02-22</td>\n",
       "      <td>PRV52405</td>\n",
       "      <td>5000</td>\n",
       "      <td>PHY369659</td>\n",
       "      <td>PHY392961</td>\n",
       "      <td>PHY349768</td>\n",
       "      <td>2009-02-14</td>\n",
       "      <td>431</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5000</td>\n",
       "      <td>1068</td>\n",
       "      <td>250</td>\n",
       "      <td>320</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BENE11014</td>\n",
       "      <td>2009-08-13</td>\n",
       "      <td>2009-08-30</td>\n",
       "      <td>PRV56614</td>\n",
       "      <td>10000</td>\n",
       "      <td>PHY379376</td>\n",
       "      <td>PHY398258</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2009-08-13</td>\n",
       "      <td>78321</td>\n",
       "      <td>...</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>21260</td>\n",
       "      <td>2136</td>\n",
       "      <td>120</td>\n",
       "      <td>100</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 54 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      BeneID ClaimStartDt  ClaimEndDt  Provider  InscClaimAmtReimbursed  \\\n",
       "0  BENE11001   2009-04-12  2009-04-18  PRV55912                   26000   \n",
       "1  BENE11001   2009-08-31  2009-09-02  PRV55907                    5000   \n",
       "2  BENE11001   2009-09-17  2009-09-20  PRV56046                    5000   \n",
       "3  BENE11011   2009-02-14  2009-02-22  PRV52405                    5000   \n",
       "4  BENE11014   2009-08-13  2009-08-30  PRV56614                   10000   \n",
       "\n",
       "  AttendingPhysician OperatingPhysician OtherPhysician AdmissionDt  \\\n",
       "0          PHY390922                NaN            NaN  2009-04-12   \n",
       "1          PHY318495          PHY318495            NaN  2009-08-31   \n",
       "2          PHY372395                NaN      PHY324689  2009-09-17   \n",
       "3          PHY369659          PHY392961      PHY349768  2009-02-14   \n",
       "4          PHY379376          PHY398258            NaN  2009-08-13   \n",
       "\n",
       "  ClmAdmitDiagnosisCode  ...  ChronicCond_Diabetes ChronicCond_IschemicHeart  \\\n",
       "0                  7866  ...                     1                         1   \n",
       "1                  6186  ...                     1                         1   \n",
       "2                 29590  ...                     1                         1   \n",
       "3                   431  ...                     1                         2   \n",
       "4                 78321  ...                     2                         1   \n",
       "\n",
       "  ChronicCond_Osteoporasis ChronicCond_rheumatoidarthritis ChronicCond_stroke  \\\n",
       "0                        2                               1                  1   \n",
       "1                        2                               1                  1   \n",
       "2                        2                               1                  1   \n",
       "3                        2                               1                  1   \n",
       "4                        2                               2                  2   \n",
       "\n",
       "  IPAnnualReimbursementAmt IPAnnualDeductibleAmt OPAnnualReimbursementAmt  \\\n",
       "0                    36000                  3204                       60   \n",
       "1                    36000                  3204                       60   \n",
       "2                    36000                  3204                       60   \n",
       "3                     5000                  1068                      250   \n",
       "4                    21260                  2136                      120   \n",
       "\n",
       "  OPAnnualDeductibleAmt PotentialFraud  \n",
       "0                    70              1  \n",
       "1                    70              0  \n",
       "2                    70              0  \n",
       "3                   320              0  \n",
       "4                   100              0  \n",
       "\n",
       "[5 rows x 54 columns]"
      ]
     },
     "execution_count": 98,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# get a copy of the merged data, prepare change all to numerical\n",
    "df_num=merge_provider_df.copy(deep=True)\n",
    "\n",
    "# transform target to binary code\n",
    "\n",
    "df_num['PotentialFraud']=df_num['PotentialFraud'].map(lambda x:1 if (x=='Yes' or x==1) else 0)\n",
    "\n",
    "df_num.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "id": "c03d8718",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "BeneID                                  0\n",
       "ClaimStartDt                            0\n",
       "ClaimEndDt                              0\n",
       "Provider                                0\n",
       "InscClaimAmtReimbursed                  0\n",
       "AttendingPhysician                   1508\n",
       "OperatingPhysician                 443764\n",
       "OtherPhysician                     358475\n",
       "AdmissionDt                        517737\n",
       "ClmAdmitDiagnosisCode              412312\n",
       "DeductibleAmtPaid                     899\n",
       "DischargeDt                        517737\n",
       "DiagnosisGroupCode                 517737\n",
       "ClmDiagnosisCode_1                  10453\n",
       "ClmDiagnosisCode_2                 195606\n",
       "ClmDiagnosisCode_3                 315156\n",
       "ClmDiagnosisCode_4                 393675\n",
       "ClmDiagnosisCode_5                 446287\n",
       "ClmDiagnosisCode_6                 473819\n",
       "ClmDiagnosisCode_7                 492034\n",
       "ClmDiagnosisCode_8                 504767\n",
       "ClmDiagnosisCode_9                 516396\n",
       "ClmDiagnosisCode_10                553201\n",
       "ClmProcedureCode_1                 534901\n",
       "ClmProcedureCode_2                 552721\n",
       "ClmProcedureCode_3                 557242\n",
       "ClmProcedureCode_4                 558093\n",
       "ClmProcedureCode_5                 558202\n",
       "In_Out                                  0\n",
       "DOB                                     0\n",
       "DOD                                554080\n",
       "Gender                                  0\n",
       "Race                                    0\n",
       "RenalDiseaseIndicator                   0\n",
       "State                                   0\n",
       "County                                  0\n",
       "NoOfMonths_PartACov                     0\n",
       "NoOfMonths_PartBCov                     0\n",
       "ChronicCond_Alzheimer                   0\n",
       "ChronicCond_Heartfailure                0\n",
       "ChronicCond_KidneyDisease               0\n",
       "ChronicCond_Cancer                      0\n",
       "ChronicCond_ObstrPulmonary              0\n",
       "ChronicCond_Depression                  0\n",
       "ChronicCond_Diabetes                    0\n",
       "ChronicCond_IschemicHeart               0\n",
       "ChronicCond_Osteoporasis                0\n",
       "ChronicCond_rheumatoidarthritis         0\n",
       "ChronicCond_stroke                      0\n",
       "IPAnnualReimbursementAmt                0\n",
       "IPAnnualDeductibleAmt                   0\n",
       "OPAnnualReimbursementAmt                0\n",
       "OPAnnualDeductibleAmt                   0\n",
       "PotentialFraud                          0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 99,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_num.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "id": "cf15fe2e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# seperate columns into target, num, datetime, categorical\n",
    "target=['PotentialFraud','Provider']\n",
    "colnames=df_num.columns.to_list()\n",
    "\n",
    "datetime_col_list=['ClaimStartDt','ClaimEndDt','AdmissionDt','DischargeDt','DOB','DOD']\n",
    "\n",
    "diagnosis_code_columns = [x for x in colnames if 'ClmDiag' in x]\n",
    "procedure_code_columns = [x for x in colnames if 'ClmProc' in x]\n",
    "physican_columns = [x for x in colnames if 'Physician' in x]\n",
    "Chronic_columns = [x for x in colnames if 'ChronicCond' in x]\n",
    "Amt_columns=[x for x in colnames if 'Amt' in x]\n",
    "NoOfMonths_columns=[x for x in colnames if 'NoOfMonth' in x]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5ecde407",
   "metadata": {},
   "source": [
    "# Datetime Columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "id": "8e94c50e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# change date column datatype to datetime\n",
    "for datetime_col_name in datetime_col_list:\n",
    "    df_num[datetime_col_name]=pd.to_datetime(df_num[datetime_col_name])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0baae49a",
   "metadata": {},
   "source": [
    "# Numerical Columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "id": "c999e0df",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>size</th>\n",
       "      <th>count</th>\n",
       "      <th>nunique</th>\n",
       "      <th>min</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>InscClaimAmtReimbursed</th>\n",
       "      <td>558211.0</td>\n",
       "      <td>558211.0</td>\n",
       "      <td>438.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>125000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DeductibleAmtPaid</th>\n",
       "      <td>558211.0</td>\n",
       "      <td>557312.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1068.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IPAnnualReimbursementAmt</th>\n",
       "      <td>558211.0</td>\n",
       "      <td>558211.0</td>\n",
       "      <td>3004.0</td>\n",
       "      <td>-8000.0</td>\n",
       "      <td>161470.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IPAnnualDeductibleAmt</th>\n",
       "      <td>558211.0</td>\n",
       "      <td>558211.0</td>\n",
       "      <td>147.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>38272.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>OPAnnualReimbursementAmt</th>\n",
       "      <td>558211.0</td>\n",
       "      <td>558211.0</td>\n",
       "      <td>2078.0</td>\n",
       "      <td>-70.0</td>\n",
       "      <td>102960.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>OPAnnualDeductibleAmt</th>\n",
       "      <td>558211.0</td>\n",
       "      <td>558211.0</td>\n",
       "      <td>789.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>13840.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NoOfMonths_PartACov</th>\n",
       "      <td>558211.0</td>\n",
       "      <td>558211.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NoOfMonths_PartBCov</th>\n",
       "      <td>558211.0</td>\n",
       "      <td>558211.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>In_Out</th>\n",
       "      <td>558211.0</td>\n",
       "      <td>558211.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              size     count  nunique     min       max\n",
       "InscClaimAmtReimbursed    558211.0  558211.0    438.0     0.0  125000.0\n",
       "DeductibleAmtPaid         558211.0  557312.0     17.0     0.0    1068.0\n",
       "IPAnnualReimbursementAmt  558211.0  558211.0   3004.0 -8000.0  161470.0\n",
       "IPAnnualDeductibleAmt     558211.0  558211.0    147.0     0.0   38272.0\n",
       "OPAnnualReimbursementAmt  558211.0  558211.0   2078.0   -70.0  102960.0\n",
       "OPAnnualDeductibleAmt     558211.0  558211.0    789.0     0.0   13840.0\n",
       "NoOfMonths_PartACov       558211.0  558211.0     13.0     0.0      12.0\n",
       "NoOfMonths_PartBCov       558211.0  558211.0     13.0     0.0      12.0\n",
       "In_Out                    558211.0  558211.0      2.0     0.0       1.0"
      ]
     },
     "execution_count": 102,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "numerical_columns=Amt_columns+NoOfMonths_columns+['In_Out']\n",
    "\n",
    "df_num[numerical_columns].agg(['size','count','nunique','min','max']).T"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c5f3a7b5",
   "metadata": {},
   "source": [
    "## Binary Categorical Columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "id": "a694746b",
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>size</th>\n",
       "      <th>count</th>\n",
       "      <th>nunique</th>\n",
       "      <th>min</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>ChronicCond_Alzheimer</th>\n",
       "      <td>558211</td>\n",
       "      <td>558211</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ChronicCond_Cancer</th>\n",
       "      <td>558211</td>\n",
       "      <td>558211</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ChronicCond_Depression</th>\n",
       "      <td>558211</td>\n",
       "      <td>558211</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ChronicCond_Diabetes</th>\n",
       "      <td>558211</td>\n",
       "      <td>558211</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ChronicCond_Heartfailure</th>\n",
       "      <td>558211</td>\n",
       "      <td>558211</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ChronicCond_IschemicHeart</th>\n",
       "      <td>558211</td>\n",
       "      <td>558211</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ChronicCond_KidneyDisease</th>\n",
       "      <td>558211</td>\n",
       "      <td>558211</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ChronicCond_ObstrPulmonary</th>\n",
       "      <td>558211</td>\n",
       "      <td>558211</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ChronicCond_Osteoporasis</th>\n",
       "      <td>558211</td>\n",
       "      <td>558211</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ChronicCond_rheumatoidarthritis</th>\n",
       "      <td>558211</td>\n",
       "      <td>558211</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ChronicCond_stroke</th>\n",
       "      <td>558211</td>\n",
       "      <td>558211</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Gender</th>\n",
       "      <td>558211</td>\n",
       "      <td>558211</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RenalDiseaseIndicator</th>\n",
       "      <td>558211</td>\n",
       "      <td>558211</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                   size   count nunique min max\n",
       "ChronicCond_Alzheimer            558211  558211       2   1   2\n",
       "ChronicCond_Cancer               558211  558211       2   1   2\n",
       "ChronicCond_Depression           558211  558211       2   1   2\n",
       "ChronicCond_Diabetes             558211  558211       2   1   2\n",
       "ChronicCond_Heartfailure         558211  558211       2   1   2\n",
       "ChronicCond_IschemicHeart        558211  558211       2   1   2\n",
       "ChronicCond_KidneyDisease        558211  558211       2   1   2\n",
       "ChronicCond_ObstrPulmonary       558211  558211       2   1   2\n",
       "ChronicCond_Osteoporasis         558211  558211       2   1   2\n",
       "ChronicCond_rheumatoidarthritis  558211  558211       2   1   2\n",
       "ChronicCond_stroke               558211  558211       2   1   2\n",
       "Gender                           558211  558211       2   1   2\n",
       "RenalDiseaseIndicator            558211  558211       2   0   Y"
      ]
     },
     "execution_count": 103,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cat_col_list=list(set(colnames)-set(numerical_columns)-set(target)-set(datetime_col_list))\n",
    "cat_col_list.sort()\n",
    "cat_nunique=df_num[cat_col_list].nunique()\n",
    "cat_binary_list=list(cat_nunique[cat_nunique==2].index)\n",
    "\n",
    "df_num[cat_binary_list].agg(['size','count','nunique','min','max']).T"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e949606f",
   "metadata": {},
   "source": [
    "## Categorical Columns (to be transformed by dummy)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "id": "1ae5377a",
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\pucke\\AppData\\Local\\Temp\\ipykernel_328\\590806527.py:3: FutureWarning: ['AttendingPhysician', 'ClmAdmitDiagnosisCode', 'ClmDiagnosisCode_1', 'ClmDiagnosisCode_10', 'ClmDiagnosisCode_2', 'ClmDiagnosisCode_3', 'ClmDiagnosisCode_4', 'ClmDiagnosisCode_5', 'ClmDiagnosisCode_6', 'ClmDiagnosisCode_7', 'ClmDiagnosisCode_8', 'ClmDiagnosisCode_9', 'DiagnosisGroupCode', 'OperatingPhysician', 'OtherPhysician'] did not aggregate successfully. If any error is raised this will raise in a future version of pandas. Drop these columns/ops to avoid this warning.\n",
      "  df_num[cat_ohe_list].agg(['size','count','nunique','min','max']).T\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>size</th>\n",
       "      <th>count</th>\n",
       "      <th>nunique</th>\n",
       "      <th>min</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>AttendingPhysician</th>\n",
       "      <td>558211.0</td>\n",
       "      <td>556703.0</td>\n",
       "      <td>82063.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BeneID</th>\n",
       "      <td>558211</td>\n",
       "      <td>558211</td>\n",
       "      <td>138556</td>\n",
       "      <td>BENE100000</td>\n",
       "      <td>BENE99999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ClmAdmitDiagnosisCode</th>\n",
       "      <td>558211.0</td>\n",
       "      <td>145899.0</td>\n",
       "      <td>4098.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ClmDiagnosisCode_1</th>\n",
       "      <td>558211.0</td>\n",
       "      <td>547758.0</td>\n",
       "      <td>10450.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ClmDiagnosisCode_10</th>\n",
       "      <td>558211.0</td>\n",
       "      <td>5010.0</td>\n",
       "      <td>1158.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ClmDiagnosisCode_2</th>\n",
       "      <td>558211.0</td>\n",
       "      <td>362605.0</td>\n",
       "      <td>5300.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ClmDiagnosisCode_3</th>\n",
       "      <td>558211.0</td>\n",
       "      <td>243055.0</td>\n",
       "      <td>4756.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ClmDiagnosisCode_4</th>\n",
       "      <td>558211.0</td>\n",
       "      <td>164536.0</td>\n",
       "      <td>4359.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ClmDiagnosisCode_5</th>\n",
       "      <td>558211.0</td>\n",
       "      <td>111924.0</td>\n",
       "      <td>3970.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ClmDiagnosisCode_6</th>\n",
       "      <td>558211.0</td>\n",
       "      <td>84392.0</td>\n",
       "      <td>3607.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ClmDiagnosisCode_7</th>\n",
       "      <td>558211.0</td>\n",
       "      <td>66177.0</td>\n",
       "      <td>3388.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ClmDiagnosisCode_8</th>\n",
       "      <td>558211.0</td>\n",
       "      <td>53444.0</td>\n",
       "      <td>3070.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ClmDiagnosisCode_9</th>\n",
       "      <td>558211.0</td>\n",
       "      <td>41815.0</td>\n",
       "      <td>2774.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ClmProcedureCode_1</th>\n",
       "      <td>558211.0</td>\n",
       "      <td>23310.0</td>\n",
       "      <td>1117.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>9999.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ClmProcedureCode_2</th>\n",
       "      <td>558211.0</td>\n",
       "      <td>5490.0</td>\n",
       "      <td>300.0</td>\n",
       "      <td>42.0</td>\n",
       "      <td>9999.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ClmProcedureCode_3</th>\n",
       "      <td>558211.0</td>\n",
       "      <td>969.0</td>\n",
       "      <td>154.0</td>\n",
       "      <td>42.0</td>\n",
       "      <td>9999.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ClmProcedureCode_4</th>\n",
       "      <td>558211.0</td>\n",
       "      <td>118.0</td>\n",
       "      <td>48.0</td>\n",
       "      <td>42.0</td>\n",
       "      <td>9986.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ClmProcedureCode_5</th>\n",
       "      <td>558211.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>2724.0</td>\n",
       "      <td>9982.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>County</th>\n",
       "      <td>558211</td>\n",
       "      <td>558211</td>\n",
       "      <td>314</td>\n",
       "      <td>0</td>\n",
       "      <td>999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DiagnosisGroupCode</th>\n",
       "      <td>558211.0</td>\n",
       "      <td>40474.0</td>\n",
       "      <td>736.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>OperatingPhysician</th>\n",
       "      <td>558211.0</td>\n",
       "      <td>114447.0</td>\n",
       "      <td>35315.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>OtherPhysician</th>\n",
       "      <td>558211.0</td>\n",
       "      <td>199736.0</td>\n",
       "      <td>46457.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Race</th>\n",
       "      <td>558211</td>\n",
       "      <td>558211</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>State</th>\n",
       "      <td>558211</td>\n",
       "      <td>558211</td>\n",
       "      <td>52</td>\n",
       "      <td>1</td>\n",
       "      <td>54</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           size     count  nunique         min        max\n",
       "AttendingPhysician     558211.0  556703.0  82063.0         NaN        NaN\n",
       "BeneID                   558211    558211   138556  BENE100000  BENE99999\n",
       "ClmAdmitDiagnosisCode  558211.0  145899.0   4098.0         NaN        NaN\n",
       "ClmDiagnosisCode_1     558211.0  547758.0  10450.0         NaN        NaN\n",
       "ClmDiagnosisCode_10    558211.0    5010.0   1158.0         NaN        NaN\n",
       "ClmDiagnosisCode_2     558211.0  362605.0   5300.0         NaN        NaN\n",
       "ClmDiagnosisCode_3     558211.0  243055.0   4756.0         NaN        NaN\n",
       "ClmDiagnosisCode_4     558211.0  164536.0   4359.0         NaN        NaN\n",
       "ClmDiagnosisCode_5     558211.0  111924.0   3970.0         NaN        NaN\n",
       "ClmDiagnosisCode_6     558211.0   84392.0   3607.0         NaN        NaN\n",
       "ClmDiagnosisCode_7     558211.0   66177.0   3388.0         NaN        NaN\n",
       "ClmDiagnosisCode_8     558211.0   53444.0   3070.0         NaN        NaN\n",
       "ClmDiagnosisCode_9     558211.0   41815.0   2774.0         NaN        NaN\n",
       "ClmProcedureCode_1     558211.0   23310.0   1117.0        11.0     9999.0\n",
       "ClmProcedureCode_2     558211.0    5490.0    300.0        42.0     9999.0\n",
       "ClmProcedureCode_3     558211.0     969.0    154.0        42.0     9999.0\n",
       "ClmProcedureCode_4     558211.0     118.0     48.0        42.0     9986.0\n",
       "ClmProcedureCode_5     558211.0       9.0      6.0      2724.0     9982.0\n",
       "County                   558211    558211      314           0        999\n",
       "DiagnosisGroupCode     558211.0   40474.0    736.0         NaN        NaN\n",
       "OperatingPhysician     558211.0  114447.0  35315.0         NaN        NaN\n",
       "OtherPhysician         558211.0  199736.0  46457.0         NaN        NaN\n",
       "Race                     558211    558211        4           1          5\n",
       "State                    558211    558211       52           1         54"
      ]
     },
     "execution_count": 104,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cat_ohe_list=list(cat_nunique[cat_nunique!=2].index)\n",
    "\n",
    "df_num[cat_ohe_list].agg(['size','count','nunique','min','max']).T"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b07cf8e5",
   "metadata": {},
   "source": [
    "# Transformation Classes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "id": "e975d011",
   "metadata": {},
   "outputs": [],
   "source": [
    "class Freq_Transformer(object):\n",
    "    def __init__(self):\n",
    "        self.freq={}\n",
    "    \n",
    "    def fit(self, X, y=None):\n",
    "        self.cat_colnames=X.columns\n",
    "        for column_name in self.cat_colnames:\n",
    "            self.freq[column_name] = X[column_name].value_counts()\n",
    "    \n",
    "    def transform(self, X, y=None):\n",
    "        df = pd.DataFrame()\n",
    "        for column_name in self.cat_colnames:\n",
    "            df[column_name + '_freq']=X[column_name].map(self.freq[column_name])\n",
    "        return df.fillna(0)\n",
    "    \n",
    "    def fit_transform(self, X, y=None):\n",
    "        self.fit(X)\n",
    "        return self.transform(X)    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "id": "627ede64",
   "metadata": {},
   "outputs": [],
   "source": [
    "class Binary_Transformer(object):\n",
    "    def __init__(self):\n",
    "        self.freq={}\n",
    "    \n",
    "    def fit(self, X, y=None):\n",
    "        self.cat_colnames=X.columns\n",
    "        df=pd.get_dummies(X[self.cat_colnames], \n",
    "                          prefix={i for i in self.cat_colnames}, \n",
    "                          columns=self.cat_colnames, \n",
    "                          drop_first=True)\n",
    "    \n",
    "    def transform(self, X, y=None):\n",
    "        df=pd.get_dummies(X[self.cat_colnames], \n",
    "                          prefix={i for i in self.cat_colnames}, \n",
    "                          columns=self.cat_colnames, \n",
    "                          drop_first=True)\n",
    "        return df.fillna(0)\n",
    "    \n",
    "    def fit_transform(self, X, y=None):\n",
    "        self.fit(X)\n",
    "        return self.transform(X)    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "id": "e19a167d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ChronicCond_Cancer_2</th>\n",
       "      <th>ChronicCond_Diabetes_2</th>\n",
       "      <th>ChronicCond_KidneyDisease_2</th>\n",
       "      <th>ChronicCond_rheumatoidarthritis_2</th>\n",
       "      <th>ChronicCond_IschemicHeart_2</th>\n",
       "      <th>RenalDiseaseIndicator_2</th>\n",
       "      <th>Gender_2</th>\n",
       "      <th>ChronicCond_Osteoporasis_2</th>\n",
       "      <th>ChronicCond_Heartfailure_2</th>\n",
       "      <th>ChronicCond_ObstrPulmonary_2</th>\n",
       "      <th>ChronicCond_Depression_2</th>\n",
       "      <th>ChronicCond_Alzheimer_2</th>\n",
       "      <th>ChronicCond_stroke_Y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>558206</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>558207</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>558208</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>558209</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>558210</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>558211 rows × 13 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        ChronicCond_Cancer_2  ChronicCond_Diabetes_2  \\\n",
       "0                          0                       1   \n",
       "1                          0                       1   \n",
       "2                          0                       1   \n",
       "3                          1                       1   \n",
       "4                          1                       1   \n",
       "...                      ...                     ...   \n",
       "558206                     0                       1   \n",
       "558207                     0                       1   \n",
       "558208                     0                       1   \n",
       "558209                     0                       1   \n",
       "558210                     0                       1   \n",
       "\n",
       "        ChronicCond_KidneyDisease_2  ChronicCond_rheumatoidarthritis_2  \\\n",
       "0                                 0                                  0   \n",
       "1                                 0                                  0   \n",
       "2                                 0                                  0   \n",
       "3                                 0                                  0   \n",
       "4                                 0                                  1   \n",
       "...                             ...                                ...   \n",
       "558206                            0                                  0   \n",
       "558207                            0                                  0   \n",
       "558208                            0                                  0   \n",
       "558209                            0                                  0   \n",
       "558210                            0                                  0   \n",
       "\n",
       "        ChronicCond_IschemicHeart_2  RenalDiseaseIndicator_2  Gender_2  \\\n",
       "0                                 1                        0         0   \n",
       "1                                 1                        0         0   \n",
       "2                                 1                        0         0   \n",
       "3                                 0                        1         0   \n",
       "4                                 0                        0         0   \n",
       "...                             ...                      ...       ...   \n",
       "558206                            0                        1         1   \n",
       "558207                            0                        1         1   \n",
       "558208                            0                        1         1   \n",
       "558209                            0                        1         1   \n",
       "558210                            0                        1         1   \n",
       "\n",
       "        ChronicCond_Osteoporasis_2  ChronicCond_Heartfailure_2  \\\n",
       "0                                1                           1   \n",
       "1                                1                           1   \n",
       "2                                1                           1   \n",
       "3                                1                           1   \n",
       "4                                0                           1   \n",
       "...                            ...                         ...   \n",
       "558206                           1                           1   \n",
       "558207                           1                           1   \n",
       "558208                           1                           1   \n",
       "558209                           1                           1   \n",
       "558210                           1                           1   \n",
       "\n",
       "        ChronicCond_ObstrPulmonary_2  ChronicCond_Depression_2  \\\n",
       "0                                  0                         0   \n",
       "1                                  0                         0   \n",
       "2                                  0                         0   \n",
       "3                                  0                         0   \n",
       "4                                  1                         1   \n",
       "...                              ...                       ...   \n",
       "558206                             0                         1   \n",
       "558207                             0                         1   \n",
       "558208                             0                         1   \n",
       "558209                             0                         1   \n",
       "558210                             0                         1   \n",
       "\n",
       "        ChronicCond_Alzheimer_2  ChronicCond_stroke_Y  \n",
       "0                             0                     0  \n",
       "1                             0                     0  \n",
       "2                             0                     0  \n",
       "3                             1                     0  \n",
       "4                             1                     1  \n",
       "...                         ...                   ...  \n",
       "558206                        1                     0  \n",
       "558207                        1                     0  \n",
       "558208                        1                     0  \n",
       "558209                        1                     0  \n",
       "558210                        1                     0  \n",
       "\n",
       "[558211 rows x 13 columns]"
      ]
     },
     "execution_count": 107,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bi_pipe = Pipeline([('bixf',Binary_Transformer())])\n",
    "test_bi=bi_pipe.fit_transform(df_num[cat_binary_list])\n",
    "test_bi"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "id": "3a88b825",
   "metadata": {},
   "outputs": [],
   "source": [
    "class OHE_Transformer(object):\n",
    "    def __init__(self):\n",
    "        self.dummy = {}\n",
    "    \n",
    "    def fit(self, X, y=None):\n",
    "        ohe = OneHotEncoder(categories='auto', drop='if_binary',sparse=False, max_categories=15)\n",
    "        ohe_df = pd.DataFrame(ohe.fit_transform(X[cat_dummy_list]))\n",
    "        ohe_df.columns=ohe.get_feature_names_out(cat_dummy_list)\n",
    "        self.columns=ohe_df.columns\n",
    "        \n",
    "    def transform(self,X,y=None):\n",
    "        ohe = OneHotEncoder(categories='auto', drop='if_binary',sparse=False, max_categories=15)\n",
    "        ohe_df = pd.DataFrame(ohe.fit_transform(X[cat_dummy_list]))\n",
    "        ohe_df.columns=ohe.get_feature_names_out(cat_dummy_list)\n",
    "        df=pd.DataFrame(columns=ohe_df.columns)\n",
    "        common_col_list=list(set(self.columns).intersection(ohe_df.columns))\n",
    "        df=pd.concat([df,ohe_df[common_col_list]],axis=0)\n",
    "        return df.fillna(-1)\n",
    "    \n",
    "    def fit_transform(self, X, y=None):\n",
    "        self.fit(X)\n",
    "        return self.transform(X)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "id": "6531b9f2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>AttendingPhysician_PHY314027</th>\n",
       "      <th>AttendingPhysician_PHY327046</th>\n",
       "      <th>AttendingPhysician_PHY330576</th>\n",
       "      <th>AttendingPhysician_PHY337425</th>\n",
       "      <th>AttendingPhysician_PHY338032</th>\n",
       "      <th>AttendingPhysician_PHY341578</th>\n",
       "      <th>AttendingPhysician_PHY347064</th>\n",
       "      <th>AttendingPhysician_PHY350277</th>\n",
       "      <th>AttendingPhysician_PHY351121</th>\n",
       "      <th>AttendingPhysician_PHY357120</th>\n",
       "      <th>...</th>\n",
       "      <th>State_23</th>\n",
       "      <th>State_31</th>\n",
       "      <th>State_33</th>\n",
       "      <th>State_34</th>\n",
       "      <th>State_36</th>\n",
       "      <th>State_39</th>\n",
       "      <th>State_44</th>\n",
       "      <th>State_45</th>\n",
       "      <th>State_49</th>\n",
       "      <th>State_infrequent_sklearn</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>558206</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>558207</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>558208</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>558209</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>558210</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>558211 rows × 341 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        AttendingPhysician_PHY314027  AttendingPhysician_PHY327046  \\\n",
       "0                                0.0                           0.0   \n",
       "1                                0.0                           0.0   \n",
       "2                                0.0                           0.0   \n",
       "3                                0.0                           0.0   \n",
       "4                                0.0                           0.0   \n",
       "...                              ...                           ...   \n",
       "558206                           0.0                           0.0   \n",
       "558207                           0.0                           0.0   \n",
       "558208                           0.0                           0.0   \n",
       "558209                           0.0                           0.0   \n",
       "558210                           0.0                           0.0   \n",
       "\n",
       "        AttendingPhysician_PHY330576  AttendingPhysician_PHY337425  \\\n",
       "0                                0.0                           0.0   \n",
       "1                                0.0                           0.0   \n",
       "2                                0.0                           0.0   \n",
       "3                                0.0                           0.0   \n",
       "4                                0.0                           0.0   \n",
       "...                              ...                           ...   \n",
       "558206                           0.0                           0.0   \n",
       "558207                           0.0                           0.0   \n",
       "558208                           0.0                           0.0   \n",
       "558209                           0.0                           0.0   \n",
       "558210                           0.0                           0.0   \n",
       "\n",
       "        AttendingPhysician_PHY338032  AttendingPhysician_PHY341578  \\\n",
       "0                                0.0                           0.0   \n",
       "1                                0.0                           0.0   \n",
       "2                                0.0                           0.0   \n",
       "3                                0.0                           0.0   \n",
       "4                                0.0                           0.0   \n",
       "...                              ...                           ...   \n",
       "558206                           0.0                           0.0   \n",
       "558207                           0.0                           0.0   \n",
       "558208                           0.0                           0.0   \n",
       "558209                           0.0                           0.0   \n",
       "558210                           0.0                           0.0   \n",
       "\n",
       "        AttendingPhysician_PHY347064  AttendingPhysician_PHY350277  \\\n",
       "0                                0.0                           0.0   \n",
       "1                                0.0                           0.0   \n",
       "2                                0.0                           0.0   \n",
       "3                                0.0                           0.0   \n",
       "4                                0.0                           0.0   \n",
       "...                              ...                           ...   \n",
       "558206                           0.0                           0.0   \n",
       "558207                           0.0                           0.0   \n",
       "558208                           0.0                           0.0   \n",
       "558209                           0.0                           0.0   \n",
       "558210                           0.0                           0.0   \n",
       "\n",
       "        AttendingPhysician_PHY351121  AttendingPhysician_PHY357120  ...  \\\n",
       "0                                0.0                           0.0  ...   \n",
       "1                                0.0                           0.0  ...   \n",
       "2                                0.0                           0.0  ...   \n",
       "3                                0.0                           0.0  ...   \n",
       "4                                0.0                           0.0  ...   \n",
       "...                              ...                           ...  ...   \n",
       "558206                           0.0                           0.0  ...   \n",
       "558207                           0.0                           0.0  ...   \n",
       "558208                           0.0                           0.0  ...   \n",
       "558209                           0.0                           0.0  ...   \n",
       "558210                           0.0                           0.0  ...   \n",
       "\n",
       "        State_23  State_31  State_33  State_34  State_36  State_39  State_44  \\\n",
       "0            0.0       0.0       0.0       0.0       0.0       1.0       0.0   \n",
       "1            0.0       0.0       0.0       0.0       0.0       1.0       0.0   \n",
       "2            0.0       0.0       0.0       0.0       0.0       1.0       0.0   \n",
       "3            0.0       0.0       0.0       0.0       0.0       0.0       0.0   \n",
       "4            0.0       0.0       0.0       0.0       0.0       0.0       0.0   \n",
       "...          ...       ...       ...       ...       ...       ...       ...   \n",
       "558206       0.0       0.0       0.0       0.0       0.0       0.0       0.0   \n",
       "558207       0.0       0.0       0.0       0.0       0.0       0.0       0.0   \n",
       "558208       0.0       0.0       0.0       0.0       0.0       0.0       0.0   \n",
       "558209       0.0       0.0       0.0       0.0       0.0       0.0       0.0   \n",
       "558210       0.0       0.0       0.0       0.0       0.0       0.0       0.0   \n",
       "\n",
       "        State_45  State_49  State_infrequent_sklearn  \n",
       "0            0.0       0.0                       0.0  \n",
       "1            0.0       0.0                       0.0  \n",
       "2            0.0       0.0                       0.0  \n",
       "3            0.0       0.0                       1.0  \n",
       "4            1.0       0.0                       0.0  \n",
       "...          ...       ...                       ...  \n",
       "558206       0.0       0.0                       1.0  \n",
       "558207       0.0       0.0                       1.0  \n",
       "558208       0.0       0.0                       1.0  \n",
       "558209       0.0       0.0                       1.0  \n",
       "558210       0.0       0.0                       1.0  \n",
       "\n",
       "[558211 rows x 341 columns]"
      ]
     },
     "execution_count": 109,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# One Hot Encoder Pipeline\n",
    "\n",
    "ohe_pipe = Pipeline([('ohexf',OHE_Transformer())])\n",
    "test_dummy=ohe_pipe.fit_transform(df_num[cat_dummy_list])\n",
    "test_dummy"
   ]
  },
  {
   "cell_type": "raw",
   "id": "5dca2394",
   "metadata": {},
   "source": [
    "test_dropduplicates=test_dummy.T.drop_duplicates()\n",
    "test_dropduplicates"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "557293e1",
   "metadata": {},
   "source": [
    "# Preprocessor"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "id": "19ce2e44",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Combine the above pipelines\n",
    "preprocessor = ColumnTransformer([\n",
    "    ('bi', bi_pipe,cat_binary_list),\n",
    "    ('ohe',ohe_pipe,cat_dummy_list)\n",
    "])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "id": "c400323e",
   "metadata": {},
   "outputs": [],
   "source": [
    "model= Pipeline([\n",
    "    ('preprocessor', preprocessor),\n",
    "])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "id": "2d98bb10",
   "metadata": {},
   "outputs": [],
   "source": [
    "temp_df=model.fit_transform(df_num)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "id": "9ec4ef34",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(354, 558211)"
      ]
     },
     "execution_count": 113,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(temp_df[1]),len(temp_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "id": "fc8feac8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([[0., 1., 0., ..., 0., 0., 0.],\n",
       "       [0., 1., 0., ..., 0., 0., 0.],\n",
       "       [0., 1., 0., ..., 0., 0., 0.],\n",
       "       ...,\n",
       "       [0., 1., 0., ..., 0., 0., 1.],\n",
       "       [0., 1., 0., ..., 0., 0., 1.],\n",
       "       [0., 1., 0., ..., 0., 0., 1.]])"
      ]
     },
     "execution_count": 114,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "temp_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a7ee5d29",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "37c8153d",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "e6dc9080",
   "metadata": {},
   "outputs": [],
   "source": [
    "class DateTransform(BaseEstimator, TransformerMixin):\n",
    "    # initializer \n",
    "    def __init__(self, start, end, newColumn):\n",
    "        # save the features list internally in the class\n",
    "        self.start = start\n",
    "        self.end = end\n",
    "        self.newColumn = newColumn\n",
    "    \n",
    "    def fit(self, X, y=None):\n",
    "        return self\n",
    "      \n",
    "    def transform(self, X, y=None):\n",
    "        X[self.newColumn] = self.convertDateToPeriod(X, self.start, self.end)\n",
    "        X[self.newColumn] = X[self.newColumn].fillna(0)\n",
    "        return X\n",
    "        \n",
    "    def fit_transform(self, X, y=None):\n",
    "        self.fit(X)\n",
    "        return self.transform(X)    \n",
    "    \n",
    "    def convertDateToPeriod(self, df, startDate, endDate):\n",
    "        return (pd.to_datetime(df[endDate]) - pd.to_datetime(df[startDate])).dt.days + 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "a5dea51b",
   "metadata": {},
   "outputs": [
    {
     "ename": "TypeError",
     "evalue": "DateTransform.__init__() missing 3 required positional arguments: 'start', 'end', and 'newColumn'",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mTypeError\u001b[0m                                 Traceback (most recent call last)",
      "Input \u001b[1;32mIn [11]\u001b[0m, in \u001b[0;36m<cell line: 1>\u001b[1;34m()\u001b[0m\n\u001b[1;32m----> 1\u001b[0m Date_Xfer\u001b[38;5;241m=\u001b[39m\u001b[43mDateTransform\u001b[49m\u001b[43m(\u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m      2\u001b[0m Date_Xfer\u001b[38;5;241m.\u001b[39mfit_transform(df_num)\n",
      "\u001b[1;31mTypeError\u001b[0m: DateTransform.__init__() missing 3 required positional arguments: 'start', 'end', and 'newColumn'"
     ]
    }
   ],
   "source": [
    "Date_Xfer=DateTransform()\n",
    "Date_Xfer.fit_transform(df_num)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "7006871e",
   "metadata": {},
   "outputs": [],
   "source": [
    "from datetime import date, datetime\n",
    "\n",
    "class AgeTransform(BaseEstimator, TransformerMixin):\n",
    "    # initializer \n",
    "    def __init__(self, dob, dod, ageColumn, deceasedColumn):\n",
    "        # save the features list internally in the class\n",
    "        self.dob = dob\n",
    "        self.dod = dod\n",
    "        self.ageColumn = ageColumn\n",
    "        self.deceasedColumn = deceasedColumn\n",
    "    \n",
    "    def fit(self, X, y=None):\n",
    "        return self\n",
    "      \n",
    "    def transform(self, X, y=None):\n",
    "        X[self.ageColumn] = X.apply(lambda x: self.calculateAge(dob = x[self.dob], dod = x[self.dod], calulationDate = '2009-12-01'), axis = 1)\n",
    "        X[self.deceasedColumn] = X[self.dod].apply(lambda x : 0 if pd.isna(x) else 1)\n",
    "        return X\n",
    "        \n",
    "    def fit_transform(self, X, y=None):\n",
    "        self.fit(X)\n",
    "        return self.transform(X)    \n",
    "    \n",
    "    def calculateAge(self, dob, dod, calulationDate):\n",
    "        born = datetime.strptime(dob, \"%Y-%m-%d\").date()\n",
    "        if not pd.isna(dod):\n",
    "            calulationDate = datetime.strptime(dod, \"%Y-%m-%d\").date()\n",
    "        else:\n",
    "            calulationDate = datetime.strptime(calulationDate, \"%Y-%m-%d\").date()\n",
    "        return calulationDate.year - born.year - ((calulationDate.month, calulationDate.day) < (born.month, born.day))  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "b6f32fcb",
   "metadata": {},
   "outputs": [],
   "source": [
    "diagnosis_code_columns = ['ClmDiagnosisCode_1', 'ClmDiagnosisCode_2', 'ClmDiagnosisCode_3', 'ClmDiagnosisCode_4', 'ClmDiagnosisCode_5', \n",
    "           'ClmDiagnosisCode_6', 'ClmDiagnosisCode_7', 'ClmDiagnosisCode_8', 'ClmDiagnosisCode_9', 'ClmDiagnosisCode_10' ]\n",
    "procedure_code_columns = ['ClmProcedureCode_1', 'ClmProcedureCode_2', 'ClmProcedureCode_3',\n",
    "       'ClmProcedureCode_4', 'ClmProcedureCode_5', 'ClmProcedureCode_6']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "id": "5bb3c710",
   "metadata": {},
   "outputs": [],
   "source": [
    "class CodeCountTransform(BaseEstimator, TransformerMixin):\n",
    "    # initializer \n",
    "    def __init__(self, colunmsToCount, newColumn):\n",
    "        # save the features list internally in the class\n",
    "        self.colunmsToCount = colunmsToCount\n",
    "        self.newColumn = newColumn\n",
    "        \n",
    "    def fit(self, X, y=None):\n",
    "        return self\n",
    "      \n",
    "    def transform(self, X, y=None):\n",
    "        X[self.newColumn] = self.countCodeNumber(X, self.colunmsToCount)\n",
    "        return X\n",
    "        \n",
    "    def fit_transform(self, X, y=None):\n",
    "        self.fit(X)\n",
    "        return self.transform(X)    \n",
    "    \n",
    "    def countCodeNumber(self, df, colunmsToCount):\n",
    "        df_codes = df.loc[:, colunmsToCount]\n",
    "        codecount = df_codes.notnull().sum(axis=1)\n",
    "        return codecount"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "id": "b9aa0454",
   "metadata": {},
   "outputs": [],
   "source": [
    "class CodeFrequencyGroupTransform(BaseEstimator, TransformerMixin):\n",
    "    '''code_columns: the column list containing the claim codes\n",
    "       new_column: list of new_columns\n",
    "       '''\n",
    "    def __init__(self, code_columns, new_columns_prefix, high, medium_high, medium, low):\n",
    "        # save the features list internally in the class\n",
    "        self.code_columns = code_columns\n",
    "        self.new_columns_prefix = new_columns_prefix\n",
    "        self.high = high\n",
    "        self.medium_high = medium_high\n",
    "        self.medium = medium\n",
    "        self.low = low\n",
    "    \n",
    "    def fit(self, X, y=None):\n",
    "        self.frequency_counts = self.getTotalCodeCounts(X, self.code_columns)\n",
    "        self.frequency_groups = self.getFrequencyGroups(self.frequency_counts, self.high, self.medium_high, \n",
    "                                                        self.medium, self.low)\n",
    "        \n",
    "    def transform(self, X, y=None):\n",
    "        X[self.new_columns_prefix+'HighFreqCount'] = self.codeForfrequencyGroupCounts(X, self.code_columns, self.frequency_groups[0])\n",
    "        X[self.new_columns_prefix+'MediumHighFreqCount'] = self.codeForfrequencyGroupCounts(X, self.code_columns, self.frequency_groups[1])\n",
    "        X[self.new_columns_prefix+'MediumFreqCount'] = self.codeForfrequencyGroupCounts(X, self.code_columns, self.frequency_groups[2])\n",
    "        X[self.new_columns_prefix+'LowFreqCount'] = self.codeForfrequencyGroupCounts(X, self.code_columns, self.frequency_groups[3])\n",
    "        X[self.new_columns_prefix+'RareFreqCount'] = self.codeForfrequencyGroupCounts(X, self.code_columns, self.frequency_groups[4])\n",
    "        return X\n",
    "        \n",
    "    def fit_transform(self, X, y=None):\n",
    "        self.fit(X)\n",
    "        return self.transform(X)    \n",
    "    \n",
    "    def mergeDictionaryWithUpdate(self, dict_1, dict_2):\n",
    "        for key in dict_2:\n",
    "            value = dict_2[key]\n",
    "       \n",
    "            if key not in dict_1:\n",
    "                dict_1[key] = value\n",
    "            else:\n",
    "                old_value = int(dict_1[key])\n",
    "                dict_1[key] = old_value + value\n",
    "        return dict_1    \n",
    "    \n",
    "    '''Get total counts of each code'''\n",
    "    def getTotalCodeCounts(self, df, columns):\n",
    "        code_counts = {}\n",
    "        for column in columns:\n",
    "            value_counts = df[column].value_counts().to_dict()\n",
    "            code_counts = self.mergeDictionaryWithUpdate(code_counts, value_counts)\n",
    "        sorted_counts = dict(sorted(code_counts.items(), key=lambda item: item[1], reverse=True))\n",
    "        return sorted_counts  \n",
    "    \n",
    "    def getFrequencyGroups(self, dictionary, high, medium_high, medium, low):\n",
    "        high_frequency = []\n",
    "        medium_high_frequency = []\n",
    "        medium_frequency = []\n",
    "        low_fequency = []\n",
    "        rare_frequency = []\n",
    "        for key in dictionary:\n",
    "            value = dictionary[key]\n",
    "            if value >= high:\n",
    "                high_frequency.append(key)\n",
    "            elif value < high and value >= medium_high:\n",
    "                medium_high_frequency.append(key)\n",
    "            elif value < medium_high and value >= medium:\n",
    "                medium_frequency.append(key)\n",
    "            elif value < medium and value >= low:\n",
    "                low_fequency.append(key)\n",
    "            else:\n",
    "                rare_frequency.append(key)\n",
    "        results = []\n",
    "        results.extend((high_frequency, medium_high_frequency, medium_frequency, low_fequency, rare_frequency))\n",
    "        return results\n",
    "\n",
    "    def codeForfrequencyGroupCounts(self, df, columns, frequency_group):\n",
    "        df_codes = df.loc[:, columns]\n",
    "        codecount = df_codes.isin(frequency_group).sum(axis=1)\n",
    "        return codecount"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "id": "39119435",
   "metadata": {},
   "outputs": [],
   "source": [
    "class Top15OneHotTransform(BaseEstimator, TransformerMixin):\n",
    "    def __init__(self, column_list, top_15_codes, new_column_prefix):\n",
    "        self.column_list = column_list\n",
    "        self.top_15_codes = top_15_codes\n",
    "        self.new_column_prefix = new_column_prefix\n",
    "        self.codes_df = pd.DataFrame()\n",
    "    \n",
    "    def fit(self, X, y=None):\n",
    "        #change codes not in top15 with 'Other'\n",
    "        codes_df = X[self.column_list]\n",
    "        for column in self.column_list:\n",
    "            codes_df[column] =  codes_df[column].apply(lambda x : 'Other' if x not in self.top_15_codes else x )\n",
    "        self.codes_df = codes_df    \n",
    "        \n",
    "    def transform(self, X, y=None):\n",
    "        for code in self.top_15_codes:\n",
    "            column_name = self.new_column_prefix + code\n",
    "            X[column_name] = self.codeForNHotCounts(self.codes_df, self.column_list, code)\n",
    "        return X\n",
    "        \n",
    "    def fit_transform(self, X, y=None):\n",
    "        self.fit(X)\n",
    "        return self.transform(X)  \n",
    "    \n",
    "    def codeForNHotCounts(self, df, columns, code):\n",
    "        df_codes = df.loc[:, columns]\n",
    "        codecount = (df_codes==code).sum(axis=1)\n",
    "        return codecount\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "720f57b2",
   "metadata": {},
   "outputs": [],
   "source": [
    "# import category_encoders as ce\n",
    "# class TargetCodingTransform(BaseEstimator, TransformerMixin):\n",
    "#     def __init__(self, column_list, new_column):\n",
    "#         self.column_list = column_list\n",
    "#         self.new_column = new_column\n",
    "#         self.codes_df = pd.DataFrame()\n",
    "    \n",
    "#     def fit(self, X, y=None):\n",
    "#         #Change NA to -9999 and change PotentialFraud to 0/1\n",
    "#         codes_df = X[self.column_list]\n",
    "#         for column in self.column_list:\n",
    "#             codes_df[column] = codes_df[column].apply(lambda x: '-9999' if pd.isna(x) else x)\n",
    "#         codes_df['PotentialFraud'] = X['PotentialFraud'].apply(lambda x: 1 if x == 'Yes' else 0)     \n",
    "#         self.codes_df = codes_df\n",
    "        \n",
    "        \n",
    "#     def transform(self, X, y=None):\n",
    "        \n",
    "#         return X\n",
    "        \n",
    "#     def fit_transform(self, X, y=None):\n",
    "#         self.fit(X)\n",
    "#         return self.transform(X)  \n",
    "    \n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 142,
   "id": "43a545c6",
   "metadata": {},
   "outputs": [],
   "source": [
    "class ProviderLevelAggregateTransform(BaseEstimator, TransformerMixin):\n",
    "    def __init__(self, fraction_column_list):\n",
    "        self.fraction_column_list = fraction_column_list\n",
    "    \n",
    "    def fit(self, X, y=None):\n",
    "        #change codes not in top15 with 'Other'\n",
    "        return self \n",
    "        \n",
    "    def transform(self, X, y=None):\n",
    "        agg_df = pd.DataFrame()\n",
    "        \n",
    "        agg_df = X.groupby('Provider').agg(     MedianAge = ('Age', 'median'),\n",
    "                                                MeanInscClaimAmtReimbursed = ('InscClaimAmtReimbursed', 'mean'),\n",
    "                                                MaxInscClaimAmtReimbursed = ('InscClaimAmtReimbursed', 'max'),\n",
    "                                                TotalInscClaimAmtReimbursed = ('InscClaimAmtReimbursed', 'sum'),\n",
    "                                                MeanDeductibleAmtPaid = ('DeductibleAmtPaid', 'mean'),\n",
    "                                                MaxDeductibleAmtPaid = ('DeductibleAmtPaid', 'max'),\n",
    "                                                MeanNumMonth_PartACov = ('NoOfMonths_PartACov','mean'),\n",
    "                                                MeanNumMonth_PartBCov = ('NoOfMonths_PartBCov','mean'),\n",
    "                                                MeanOPAnnualReimbursementAmt = ('OPAnnualReimbursementAmt', 'mean'),\n",
    "                                                MaxOPAnnualReimbursementAmt = ('OPAnnualReimbursementAmt', 'max'),\n",
    "                                                TotalOPAnnualReimbursementAmt = ('OPAnnualReimbursementAmt', 'sum'),\n",
    "                                                MeanOPAnnualDeductibleAmt = ('OPAnnualDeductibleAmt', 'mean'),\n",
    "                                                MaxOPAnnualDeductibleAmt = ('OPAnnualDeductibleAmt', 'max'),\n",
    "                                                TotalOPAnnualDeductibleAmt = ('OPAnnualDeductibleAmt', 'sum'),\n",
    "                                                MeanClaimPeriods = ('ClaimPeriod', 'mean'),\n",
    "                                                MaxHospitalDays = ('HospitalDays', 'max'), \n",
    "                                                MedianHospitalDays = ('HospitalDays', 'median'),\n",
    "                                                MeanHospitalDays = ('HospitalDays', 'mean'),\n",
    "                                                MaxDiagCodeNumPerClaim = ('DiagCodeCounts', 'max'),\n",
    "                                                MeanDiagCodeNumPerClaim = ('DiagCodeCounts', 'mean'),\n",
    "                                                MaxProcCodeNumPerClaim = ('ProcCodeCounts', 'max'),\n",
    "                                                MeanProcCodeNumPerClaim = ('ProcCodeCounts', 'mean'),\n",
    "                                                TotalDiagCodeNum = ('DiagCodeCounts', 'sum'),\n",
    "                                                TotalProcCodeNum = ('ProcCodeCounts', 'sum'),\n",
    "                                                MeanHighFreqDiagCodeNumPerClaim = ('ClmDiagHighFreqCount', 'mean'),\n",
    "                                                MeanMediumHighFreqDiagCodeNumPerClaim = ('ClmDiagMediumHighFreqCount', 'mean'),\n",
    "                                                MeanMediumFreqDiagCodeNumPerClaim = ('ClmDiagMediumFreqCount', 'mean'),\n",
    "                                                MeanLowFreqDiagCodeNumPerClaim = ('ClmDiagLowFreqCount', 'mean'),\n",
    "                                                MeanRareFreqDiagCodeNumPerClaim = ('ClmDiagRareFreqCount', 'mean'),\n",
    "                                                TotalHighFreqProcCodeNumPerClaim = ('ClmProcHighFreqCount', 'sum'),\n",
    "                                                TotalMediumHighFreqProcCodeNumPerClaim = ('ClmProcMediumHighFreqCount', 'sum'),\n",
    "                                                TotalMediumFreqProcCodeNumPerClaim = ('ClmProcMediumFreqCount', 'sum'),\n",
    "                                                TotalLowFreqProcCodeNumPerClaim = ('ClmProcLowFreqCount', 'sum'),\n",
    "                                                TotalRareFreqProcCodeNumPerClaim = ('ClmProcRareFreqCount', 'sum'),\n",
    "                                                totalDiagCode_4019 = ('DiagCode_4019', 'sum'),      \n",
    "                                                totalDiagCode_25000 = ('DiagCode_25000','sum'),\n",
    "                                                totalDiagCode_2724 = ('DiagCode_2724', 'sum'),\n",
    "                                                totalDiagCode_V5869 = ('DiagCode_V5869', 'sum'),\n",
    "                                                totalDiagCode_4011 = ('DiagCode_4011', 'sum'),\n",
    "                                                totalDiagCode_42731 =  ('DiagCode_42731', 'sum'),\n",
    "                                                totalDiagCode_V5861 = ('DiagCode_V5861', 'sum'),\n",
    "                                                totalDiagCode_2720 = ('DiagCode_2720', 'sum'),\n",
    "                                                totalDiagCode_2449 = ('DiagCode_2449', 'sum'),\n",
    "                                                totalDiagCode_4280 = ('DiagCode_4280', 'sum'),\n",
    "                                                totalDiagCode_53081 = ('DiagCode_53081', 'sum'),\n",
    "                                                totalDiagCode_41401 = ('DiagCode_41401', 'sum'),\n",
    "                                                totalDiagCode_496 = ('DiagCode_496', 'sum'),\n",
    "                                                totalDiagCode_2589 = ('DiagCode_2859', 'sum'),\n",
    "                                                totalDiagCode_41400 = ('DiagCode_41400', 'sum'),\n",
    "                                                totalDiagCode_Other = ('DiagCode_Other', 'sum'),\n",
    "                                                totalProcCode_4019 = ('ProcCode_4019.0', 'sum'),\n",
    "                                                totalProcCode_9904 = ('ProcCode_9904.0', 'sum'),\n",
    "                                                totalProcCode_2724 = ('ProcCode_2724.0', 'sum'),\n",
    "                                                totalProcCode_8154 = ('ProcCode_8154.0', 'sum'),\n",
    "                                                totalProcCode_66 = ('ProcCode_66.0', 'sum'),\n",
    "                                                totalProcCode_3893 = ('ProcCode_3893.0', 'sum'),\n",
    "                                                totalProcCode_3995 = ('ProcCode_3995.0', 'sum'),\n",
    "                                                totalProcCode_4516 = ('ProcCode_4516.0', 'sum'),\n",
    "                                                totalProcCode_3722 = ('ProcCode_3722.0', 'sum'),\n",
    "                                                totalProcCode_8151 = ('ProcCode_8151.0', 'sum'),\n",
    "                                                totalProcCode_8872 = ('ProcCode_8872.0', 'sum'),\n",
    "                                                totalProcCode_9671 = ('ProcCode_9671.0', 'sum'),\n",
    "                                                totalProcCode_4513 = ('ProcCode_4513.0', 'sum'),\n",
    "                                                totalProcCode_5849 = ('ProcCode_5849.0', 'sum'),\n",
    "                                                totalProcCode_9390 = ('ProcCode_9390.0', 'sum'),\n",
    "                                                totalProcCode_Other = ('ProcCode_Other', 'sum'))\n",
    "        # Caculate aggregted fraction \n",
    "        for column in self.fraction_column_list:\n",
    "            new_colunm = column + 'Frac_'\n",
    "            agg_df[new_colunm] = (X.groupby('Provider').apply(lambda x: (x[column] == 1).sum()/x[column].count())).values                             \n",
    "        \n",
    "        # Total Claims per provider\n",
    "        agg_df['ClaimNumbers'] = (X.groupby('Provider')[['ClaimID']].count()).values\n",
    "        agg_df['uniqBeneCount'] = (X.groupby('Provider')[['BeneID']].nunique()).values\n",
    "                                           \n",
    "        return agg_df\n",
    "        \n",
    "    def fit_transform(self, X, y=None):\n",
    "        self.fit(X)\n",
    "        return self.transform(X)  \n",
    "    \n",
    "                                           "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "id": "1bee5445",
   "metadata": {},
   "outputs": [],
   "source": [
    "train_top_15_diag_codes = ['4019', '25000', '2724', 'V5869', '4011', '42731', 'V5861', '2720', '2449',\n",
    " '4280', '53081', '41401', '496', '2859', '41400', 'Other']\n",
    "train_top_15_proc_codes = ['4019.0', '9904.0', '2724.0', '8154.0', '66.0', '3893.0', '3995.0', '4516.0',\n",
    " '3722.0', '8151.0', '8872.0', '9671.0','4513.0','5849.0', '9390.0', 'Other']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "id": "2113b705",
   "metadata": {},
   "outputs": [],
   "source": [
    "fraction_column_list= ['ChronicCond_Alzheimer','ChronicCond_Heartfailure','ChronicCond_KidneyDisease','ChronicCond_Cancer',\n",
    "'ChronicCond_ObstrPulmonary','ChronicCond_Depression','ChronicCond_Diabetes','ChronicCond_IschemicHeart','ChronicCond_Osteoporasis',\n",
    "'ChronicCond_rheumatoidarthritis','ChronicCond_stroke', 'RenalDiseaseIndicator', 'Deceased', 'Gender', 'Race']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "020cf121",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/4r/5r7tbtx178l60wyz85w2n3l4l0q0z9/T/ipykernel_1398/4251918558.py:12: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  codes_df[column] =  codes_df[column].apply(lambda x : 'Other' if x not in self.top_15_codes else x )\n",
      "/var/folders/4r/5r7tbtx178l60wyz85w2n3l4l0q0z9/T/ipykernel_1398/4251918558.py:12: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  codes_df[column] =  codes_df[column].apply(lambda x : 'Other' if x not in self.top_15_codes else x )\n"
     ]
    }
   ],
   "source": [
    "steps = [('claim_period_transform', DateTransform(start='ClaimStartDt', end='ClaimEndDt', newColumn='ClaimPeriod')), \n",
    "         ('hospital_period_transform', DateTransform(start='AdmissionDt', end='DischargeDt', newColumn='HospitalDays')),\n",
    "         ('age_transform', AgeTransform(dob='DOB', dod='DOD', ageColumn='Age', deceasedColumn='Deceased')),\n",
    "         \n",
    "         ('diag_code_count', CodeCountTransform(colunmsToCount = diagnosis_code_columns, newColumn='DiagCodeCounts')),\n",
    "         ('proc_code_count', CodeCountTransform(colunmsToCount = procedure_code_columns, newColumn='ProcCodeCounts')),\n",
    "         \n",
    "         ('diag_code_frequency_group', CodeFrequencyGroupTransform(code_columns=diagnosis_code_columns, \n",
    "                                                                   new_columns_prefix='ClmDiag',\n",
    "                                                                  high=10000, medium_high=5000, medium=800, low=500)),\n",
    "         ('proc_code_frequency_group', CodeFrequencyGroupTransform(code_columns=procedure_code_columns, \n",
    "                                                                   new_columns_prefix='ClmProc',\n",
    "                                                                  high=500, medium_high=100, medium=10, low=5)),\n",
    "         \n",
    "         ('diag_code_top15_onehot', Top15OneHotTransform(column_list=diagnosis_code_columns, \n",
    "                                                         top_15_codes=train_top_15_diag_codes,\n",
    "                                                         new_column_prefix='DiagCode_')),\n",
    "         ('proc_code_top15_onehot', Top15OneHotTransform(column_list=procedure_code_columns, \n",
    "                                                         top_15_codes=train_top_15_proc_codes,\n",
    "                                                         new_column_prefix='ProcCode_')),\n",
    "         \n",
    "         ('aggregation', ProviderLevelAggregateTransform(fraction_column_list=fraction_column_list))]\n",
    "\n",
    "pipe = Pipeline(steps)\n",
    "pipe.fit_transform(merge_provider_df)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "61af4b29",
   "metadata": {},
   "outputs": [],
   "source": [
    "drop_column_list = []"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 141,
   "id": "583a7467",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>BeneID</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Provider</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>PRV51001</th>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PRV51003</th>\n",
       "      <td>117</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PRV51004</th>\n",
       "      <td>138</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PRV51005</th>\n",
       "      <td>495</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PRV51007</th>\n",
       "      <td>58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PRV57759</th>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PRV57760</th>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PRV57761</th>\n",
       "      <td>67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PRV57762</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PRV57763</th>\n",
       "      <td>70</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5410 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          BeneID\n",
       "Provider        \n",
       "PRV51001      24\n",
       "PRV51003     117\n",
       "PRV51004     138\n",
       "PRV51005     495\n",
       "PRV51007      58\n",
       "...          ...\n",
       "PRV57759      24\n",
       "PRV57760       9\n",
       "PRV57761      67\n",
       "PRV57762       1\n",
       "PRV57763      70\n",
       "\n",
       "[5410 rows x 1 columns]"
      ]
     },
     "execution_count": 141,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merge_provider_df.groupby('Provider')[['BeneID']].nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b003c31c",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.10.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}