medical_fraud_data_preprocessing.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "medical_fraud_data_preprocessing.ipynb",
      "provenance": [],
      "collapsed_sections": []
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    }
  },
  "cells": [
    {
      "cell_type": "code",
      "metadata": {
        "id": "1qoL9t4pYcnX",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "\n",
        "import numpy as np # linear algebra\n",
        "import pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\n",
        "import os"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "OcOjy0tLaKey",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "import pandas as pd\n",
        "import numpy as np\n",
        "import scipy as sc\n",
        "import seaborn as sns\n",
        "import matplotlib.pyplot as plt\n",
        "import pandas_profiling as profile   # To check data distributions and correlations\n",
        "import warnings     # for supressing a warning when importing large files\n",
        "warnings.filterwarnings(\"ignore\")\n",
        "from sklearn.preprocessing import StandardScaler,MinMaxScaler\n",
        "from sklearn.model_selection import train_test_split\n",
        "import pickle\n",
        "import matplotlib.pyplot as plt\n",
        "from scipy import stats\n",
        "import tensorflow as tf\n",
        "from pylab import rcParams\n",
        "from keras.models import Model, load_model\n",
        "from keras.layers import Input, Dense\n",
        "from keras.callbacks import ModelCheckpoint, TensorBoard\n",
        "from keras import regularizers\n",
        "%matplotlib inline\n",
        "sns.set(style='whitegrid', palette='muted', font_scale=1.5)\n",
        "rcParams['figure.figsize'] = 14, 8\n",
        "RANDOM_SEED = 42\n",
        "\n",
        "LABELS = [\"Normal\", \"Fraud\"]"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "zN2vE79faklk",
        "colab_type": "text"
      },
      "source": [
        "Loading the Data\n",
        "\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "XAQTDu2XaRkJ",
        "colab_type": "code",
        "outputId": "856eb1b8-b7a2-4da6-e25f-2760f5b5a6b4",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 33
        }
      },
      "source": [
        "from google.colab import drive\n",
        "drive.mount('/content/drive')\n"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Drive already mounted at /content/drive; to attempt to forcibly remount, call drive.mount(\"/content/drive\", force_remount=True).\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "jgvdp82omokW",
        "colab_type": "text"
      },
      "source": [
        "**LOAD DATASETS**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "vf7G-yi45mIe",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        " \n",
        "Train=pd.read_csv(\"/content/drive/My Drive/Final_year_Project/Datasets/Train-1542865627584.csv\")\n",
        "Train_Beneficiarydata=pd.read_csv(\"/content/drive//My Drive/Final_year_Project/Datasets/Train_Beneficiarydata-1542865627584.csv\")\n",
        "Train_Inpatientdata=pd.read_csv(\"/content/drive/My Drive/Final_year_Project/Datasets/Train_Inpatientdata-1542865627584.csv\")\n",
        "Train_Outpatientdata=pd.read_csv(\"/content/drive/My Drive//Final_year_Project/Datasets/Train_Outpatientdata-1542865627584.csv\")\n",
        "\n",
        "# Load Test Dataset\n",
        "\n",
        "Test=pd.read_csv(\"/content/drive/My Drive/Final_year_Project/Datasets/Test-1542969243754.csv\")\n",
        "Test_Beneficiarydata=pd.read_csv(\"/content/drive/My Drive/Final_year_Project/Datasets/Test_Beneficiarydata-1542969243754.csv\")\n",
        "Test_Inpatientdata=pd.read_csv(\"/content/drive/My Drive/Final_year_Project/Datasets/Test_Inpatientdata-1542969243754.csv\")\n",
        "Test_Outpatientdata=pd.read_csv(\"/content/drive/My Drive//Final_year_Project/Datasets/Test_Outpatientdata-1542969243754.csv\")"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "OE4N13OH76Mh",
        "colab_type": "code",
        "outputId": "44094765-50ff-453f-c649-a208df4fcaa0",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 150
        }
      },
      "source": [
        "print('Shape of Train data :',Train.shape)\n",
        "print('Shape of Train_Beneficiarydata data :',Train_Beneficiarydata.shape)\n",
        "print('Shape of Train_Inpatientdata data :',Train_Inpatientdata.shape)\n",
        "print('Shape of Train_Outpatientdata data :',Train_Outpatientdata.shape)\n",
        "\n",
        "print('Shape of Test data :',Test.shape)\n",
        "print('Shape of Test_Beneficiarydata data :',Test_Beneficiarydata.shape)\n",
        "print('Shape of Test_Inpatientdata data :',Test_Inpatientdata.shape)\n",
        "print('Shape of Test_Outpatientdata data :',Test_Outpatientdata.shape)"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Shape of Train data : (5410, 2)\n",
            "Shape of Train_Beneficiarydata data : (138556, 22)\n",
            "Shape of Train_Inpatientdata data : (40474, 30)\n",
            "Shape of Train_Outpatientdata data : (517737, 27)\n",
            "Shape of Test data : (1353, 1)\n",
            "Shape of Test_Beneficiarydata data : (63968, 22)\n",
            "Shape of Test_Inpatientdata data : (9551, 30)\n",
            "Shape of Test_Outpatientdata data : (125841, 27)\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "lMXMY6gDnIia",
        "colab_type": "text"
      },
      "source": [
        "**Basic Data Understanding**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "HbpkV-aE8RBe",
        "colab_type": "code",
        "outputId": "06176673-8eb3-4ae2-c75c-abd595d175d0",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 250
        }
      },
      "source": [
        "## Lets check shape of Train and Test Data\n",
        "\n",
        "print('Train Shape:',Train.shape,'\\n')\n",
        "print('Train Sample:\\n',Train.head(2),'\\n')\n",
        "\n",
        "print('\\n Test Shape:',Test.shape,'\\n')\n",
        "print('Test Sample: \\n',Test.head(2))"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Train Shape: (5410, 2) \n",
            "\n",
            "Train Sample:\n",
            "    Provider PotentialFraud\n",
            "0  PRV51001             No\n",
            "1  PRV51003            Yes \n",
            "\n",
            "\n",
            " Test Shape: (1353, 1) \n",
            "\n",
            "Test Sample: \n",
            "    Provider\n",
            "0  PRV51002\n",
            "1  PRV51006\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "sfrp0IjpnZoK",
        "colab_type": "code",
        "outputId": "16b77144-94b6-4670-cbc0-31d497847001",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 133
        }
      },
      "source": [
        "## Lets check whether  providers details are unique or not in train data\n",
        "print(Train.Provider.value_counts(sort=True,ascending=False).head(2))  # number of unique providers in train data.Check for duplicates\n",
        "\n",
        "print('\\n Total missing values in Train :',Train.isna().sum().sum())\n",
        "\n",
        "print('\\n Total missing values in Train :',Test.isna().sum().sum())"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "PRV54227    1\n",
            "PRV52206    1\n",
            "Name: Provider, dtype: int64\n",
            "\n",
            " Total missing values in Train : 0\n",
            "\n",
            " Total missing values in Train : 0\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "9ofBv6sFszjx",
        "colab_type": "text"
      },
      "source": [
        "**Beneficiary Data Understanding**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "FmYETzicnlH-",
        "colab_type": "code",
        "outputId": "960b19c3-b4b4-4b3d-bc25-95853e8713db",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 400
        }
      },
      "source": [
        "Train_Beneficiarydata.isna().sum()\n",
        "Test_Beneficiarydata.isna().sum()"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "BeneID                                 0\n",
              "DOB                                    0\n",
              "DOD                                63394\n",
              "Gender                                 0\n",
              "RenalDiseaseIndicator                  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",
              "dtype: int64"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 150
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "XkLzHpa9s7Cf",
        "colab_type": "code",
        "outputId": "ca4b39a2-7e7b-462a-f695-3c023904587f",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 400
        }
      },
      "source": [
        "Train_Beneficiarydata.dtypes\n",
        "Test_Beneficiarydata.dtypes"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "BeneID                             object\n",
              "DOB                                object\n",
              "DOD                                object\n",
              "Gender                              int64\n",
              "RenalDiseaseIndicator              object\n",
              "NoOfMonths_PartACov                 int64\n",
              "NoOfMonths_PartBCov                 int64\n",
              "ChronicCond_Alzheimer               int64\n",
              "ChronicCond_Heartfailure            int64\n",
              "ChronicCond_KidneyDisease           int64\n",
              "ChronicCond_Cancer                  int64\n",
              "ChronicCond_ObstrPulmonary          int64\n",
              "ChronicCond_Depression              int64\n",
              "ChronicCond_Diabetes                int64\n",
              "ChronicCond_IschemicHeart           int64\n",
              "ChronicCond_Osteoporasis            int64\n",
              "ChronicCond_rheumatoidarthritis     int64\n",
              "ChronicCond_stroke                  int64\n",
              "IPAnnualReimbursementAmt            int64\n",
              "IPAnnualDeductibleAmt               int64\n",
              "OPAnnualReimbursementAmt            int64\n",
              "OPAnnualDeductibleAmt               int64\n",
              "dtype: object"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 151
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ynMMvuiRvXe9",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "#Replacing 2 with 0 for chronic conditions ,that means chronic condition \"No\" is 0 and \"yes\" is 1\n",
        "#FOR GENDER 1: Male 2:Female\n",
        "\n",
        "Train_Beneficiarydata = Train_Beneficiarydata.replace({'ChronicCond_Alzheimer': 2, 'ChronicCond_Heartfailure': 2, 'ChronicCond_KidneyDisease': 2,\n",
        "                           'ChronicCond_Cancer': 2, 'ChronicCond_ObstrPulmonary': 2, 'ChronicCond_Depression': 2, \n",
        "                           'ChronicCond_Diabetes': 2, 'ChronicCond_IschemicHeart': 2, 'ChronicCond_Osteoporasis': 2, \n",
        "                           'ChronicCond_rheumatoidarthritis': 2, 'ChronicCond_stroke': 2 }, 0)\n",
        "\n",
        "Train_Beneficiarydata = Train_Beneficiarydata.replace({'RenalDiseaseIndicator': 'Y'}, 1)\n",
        "\n",
        "Test_Beneficiarydata = Test_Beneficiarydata.replace({'ChronicCond_Alzheimer': 2, 'ChronicCond_Heartfailure': 2, 'ChronicCond_KidneyDisease': 2,\n",
        "                           'ChronicCond_Cancer': 2, 'ChronicCond_ObstrPulmonary': 2, 'ChronicCond_Depression': 2, \n",
        "                           'ChronicCond_Diabetes': 2, 'ChronicCond_IschemicHeart': 2, 'ChronicCond_Osteoporasis': 2, \n",
        "                           'ChronicCond_rheumatoidarthritis': 2, 'ChronicCond_stroke': 2 }, 0)\n",
        "\n",
        "Test_Beneficiarydata = Test_Beneficiarydata.replace({'RenalDiseaseIndicator': 'Y'}, 1)\n"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "gx_u_uodv3L1",
        "colab_type": "code",
        "outputId": "6865fc78-9c60-437f-ae89-155173273b4d",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 367
        }
      },
      "source": [
        "Train_Beneficiarydata.head(10)"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>DOB</th>\n",
              "      <th>DOD</th>\n",
              "      <th>Gender</th>\n",
              "      <th>RenalDiseaseIndicator</th>\n",
              "      <th>NoOfMonths_PartACov</th>\n",
              "      <th>NoOfMonths_PartBCov</th>\n",
              "      <th>ChronicCond_Alzheimer</th>\n",
              "      <th>ChronicCond_Heartfailure</th>\n",
              "      <th>ChronicCond_KidneyDisease</th>\n",
              "      <th>ChronicCond_Cancer</th>\n",
              "      <th>ChronicCond_ObstrPulmonary</th>\n",
              "      <th>ChronicCond_Depression</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",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>BENE11001</td>\n",
              "      <td>1/1/1943</td>\n",
              "      <td>NaN</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>12</td>\n",
              "      <td>12</td>\n",
              "      <td>1</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>1</td>\n",
              "      <td>0</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",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>BENE11002</td>\n",
              "      <td>9/1/1936</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2</td>\n",
              "      <td>0</td>\n",
              "      <td>12</td>\n",
              "      <td>12</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>30</td>\n",
              "      <td>50</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>BENE11003</td>\n",
              "      <td>8/1/1936</td>\n",
              "      <td>NaN</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>12</td>\n",
              "      <td>12</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</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>0</td>\n",
              "      <td>90</td>\n",
              "      <td>40</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>BENE11004</td>\n",
              "      <td>7/1/1922</td>\n",
              "      <td>NaN</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>12</td>\n",
              "      <td>12</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",
              "      <td>1</td>\n",
              "      <td>1</td>\n",
              "      <td>1</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1810</td>\n",
              "      <td>760</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>BENE11005</td>\n",
              "      <td>9/1/1935</td>\n",
              "      <td>NaN</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>12</td>\n",
              "      <td>12</td>\n",
              "      <td>0</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>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1790</td>\n",
              "      <td>1200</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>BENE11006</td>\n",
              "      <td>9/1/1976</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2</td>\n",
              "      <td>0</td>\n",
              "      <td>12</td>\n",
              "      <td>12</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>500</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>BENE11007</td>\n",
              "      <td>9/1/1940</td>\n",
              "      <td>12/1/2009</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>12</td>\n",
              "      <td>12</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",
              "      <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>0</td>\n",
              "      <td>1490</td>\n",
              "      <td>160</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>BENE11008</td>\n",
              "      <td>2/1/1934</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2</td>\n",
              "      <td>0</td>\n",
              "      <td>12</td>\n",
              "      <td>12</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</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>0</td>\n",
              "      <td>0</td>\n",
              "      <td>30</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>BENE11009</td>\n",
              "      <td>6/1/1929</td>\n",
              "      <td>NaN</td>\n",
              "      <td>1</td>\n",
              "      <td>1</td>\n",
              "      <td>12</td>\n",
              "      <td>12</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>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>100</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9</th>\n",
              "      <td>BENE11010</td>\n",
              "      <td>7/1/1936</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2</td>\n",
              "      <td>0</td>\n",
              "      <td>12</td>\n",
              "      <td>12</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>1</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",
              "      <td>1170</td>\n",
              "      <td>660</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "      BeneID       DOB  ... OPAnnualReimbursementAmt  OPAnnualDeductibleAmt\n",
              "0  BENE11001  1/1/1943  ...                       60                     70\n",
              "1  BENE11002  9/1/1936  ...                       30                     50\n",
              "2  BENE11003  8/1/1936  ...                       90                     40\n",
              "3  BENE11004  7/1/1922  ...                     1810                    760\n",
              "4  BENE11005  9/1/1935  ...                     1790                   1200\n",
              "5  BENE11006  9/1/1976  ...                      500                      0\n",
              "6  BENE11007  9/1/1940  ...                     1490                    160\n",
              "7  BENE11008  2/1/1934  ...                       30                      0\n",
              "8  BENE11009  6/1/1929  ...                      100                      0\n",
              "9  BENE11010  7/1/1936  ...                     1170                    660\n",
              "\n",
              "[10 rows x 22 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 153
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "mFeVWuGXwERr",
        "colab_type": "code",
        "outputId": "a93d4ed9-17a6-4f99-fc7d-59fc7b080a33",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 367
        }
      },
      "source": [
        "Test_Beneficiarydata.head(10)"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>DOB</th>\n",
              "      <th>DOD</th>\n",
              "      <th>Gender</th>\n",
              "      <th>RenalDiseaseIndicator</th>\n",
              "      <th>NoOfMonths_PartACov</th>\n",
              "      <th>NoOfMonths_PartBCov</th>\n",
              "      <th>ChronicCond_Alzheimer</th>\n",
              "      <th>ChronicCond_Heartfailure</th>\n",
              "      <th>ChronicCond_KidneyDisease</th>\n",
              "      <th>ChronicCond_Cancer</th>\n",
              "      <th>ChronicCond_ObstrPulmonary</th>\n",
              "      <th>ChronicCond_Depression</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",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>BENE11001</td>\n",
              "      <td>1/1/1943</td>\n",
              "      <td>NaN</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>12</td>\n",
              "      <td>12</td>\n",
              "      <td>1</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>1</td>\n",
              "      <td>0</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",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>BENE11007</td>\n",
              "      <td>9/1/1940</td>\n",
              "      <td>12/1/2009</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>12</td>\n",
              "      <td>12</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",
              "      <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>0</td>\n",
              "      <td>1490</td>\n",
              "      <td>160</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>BENE11010</td>\n",
              "      <td>7/1/1936</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2</td>\n",
              "      <td>0</td>\n",
              "      <td>12</td>\n",
              "      <td>12</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>1</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",
              "      <td>1170</td>\n",
              "      <td>660</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>BENE11011</td>\n",
              "      <td>3/1/1914</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2</td>\n",
              "      <td>0</td>\n",
              "      <td>12</td>\n",
              "      <td>12</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>1</td>\n",
              "      <td>0</td>\n",
              "      <td>0</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",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>BENE11014</td>\n",
              "      <td>4/1/1938</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2</td>\n",
              "      <td>1</td>\n",
              "      <td>12</td>\n",
              "      <td>12</td>\n",
              "      <td>0</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",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>21260</td>\n",
              "      <td>2136</td>\n",
              "      <td>120</td>\n",
              "      <td>100</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>BENE11016</td>\n",
              "      <td>9/1/1919</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2</td>\n",
              "      <td>0</td>\n",
              "      <td>12</td>\n",
              "      <td>12</td>\n",
              "      <td>1</td>\n",
              "      <td>1</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\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>2320</td>\n",
              "      <td>1480</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>BENE11017</td>\n",
              "      <td>6/1/1940</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2</td>\n",
              "      <td>0</td>\n",
              "      <td>12</td>\n",
              "      <td>12</td>\n",
              "      <td>1</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",
              "      <td>1</td>\n",
              "      <td>1</td>\n",
              "      <td>22000</td>\n",
              "      <td>2136</td>\n",
              "      <td>1400</td>\n",
              "      <td>840</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>BENE11019</td>\n",
              "      <td>5/1/1924</td>\n",
              "      <td>NaN</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>12</td>\n",
              "      <td>12</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",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>780</td>\n",
              "      <td>290</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>BENE11021</td>\n",
              "      <td>12/1/1927</td>\n",
              "      <td>NaN</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>12</td>\n",
              "      <td>12</td>\n",
              "      <td>1</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>1</td>\n",
              "      <td>1</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>520</td>\n",
              "      <td>150</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9</th>\n",
              "      <td>BENE11026</td>\n",
              "      <td>4/1/1938</td>\n",
              "      <td>NaN</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>12</td>\n",
              "      <td>12</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>2000</td>\n",
              "      <td>1068</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "      BeneID        DOB  ... OPAnnualReimbursementAmt  OPAnnualDeductibleAmt\n",
              "0  BENE11001   1/1/1943  ...                       60                     70\n",
              "1  BENE11007   9/1/1940  ...                     1490                    160\n",
              "2  BENE11010   7/1/1936  ...                     1170                    660\n",
              "3  BENE11011   3/1/1914  ...                      250                    320\n",
              "4  BENE11014   4/1/1938  ...                      120                    100\n",
              "5  BENE11016   9/1/1919  ...                     2320                   1480\n",
              "6  BENE11017   6/1/1940  ...                     1400                    840\n",
              "7  BENE11019   5/1/1924  ...                      780                    290\n",
              "8  BENE11021  12/1/1927  ...                      520                    150\n",
              "9  BENE11026   4/1/1938  ...                        0                      0\n",
              "\n",
              "[10 rows x 22 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 154
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "t74BTjZpw516",
        "colab_type": "text"
      },
      "source": [
        "**ADDING AN EXTRA COL : Add Age of Person based on his/her DOD(Date of death ) and DOB (Date of Birth)**\n",
        "\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ACtr2HLCwLfN",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "# Lets Create Age column to the dataset\n",
        "\n",
        "Train_Beneficiarydata['DOB'] = pd.to_datetime(Train_Beneficiarydata['DOB'] , format = '%d/%m/%Y')\n",
        "Train_Beneficiarydata['DOD'] = pd.to_datetime(Train_Beneficiarydata['DOD'],format = '%d/%m/%Y',errors='ignore')\n",
        "Train_Beneficiarydata['Age'] = round(((Train_Beneficiarydata['DOD'] - Train_Beneficiarydata['DOB']).dt.days)/365)\n",
        "\n",
        "\n",
        "Test_Beneficiarydata['DOB'] = pd.to_datetime(Test_Beneficiarydata['DOB'] , format = '%d/%m/%Y')\n",
        "Test_Beneficiarydata['DOD'] = pd.to_datetime(Test_Beneficiarydata['DOD'],format = '%d/%m/%Y',errors='ignore')\n",
        "Test_Beneficiarydata['Age'] = round(((Test_Beneficiarydata['DOD'] - Test_Beneficiarydata['DOB']).dt.days)/365)\n"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ZYM1rvWJxPww",
        "colab_type": "code",
        "outputId": "e0857619-7468-4374-9c26-f882bc1d2f67",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 161
        }
      },
      "source": [
        "Train_Beneficiarydata.head(2)\n",
        "Test_Beneficiarydata.head(2)\n"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>DOB</th>\n",
              "      <th>DOD</th>\n",
              "      <th>Gender</th>\n",
              "      <th>RenalDiseaseIndicator</th>\n",
              "      <th>NoOfMonths_PartACov</th>\n",
              "      <th>NoOfMonths_PartBCov</th>\n",
              "      <th>ChronicCond_Alzheimer</th>\n",
              "      <th>ChronicCond_Heartfailure</th>\n",
              "      <th>ChronicCond_KidneyDisease</th>\n",
              "      <th>ChronicCond_Cancer</th>\n",
              "      <th>ChronicCond_ObstrPulmonary</th>\n",
              "      <th>ChronicCond_Depression</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>Age</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>BENE11001</td>\n",
              "      <td>1943-01-01</td>\n",
              "      <td>NaT</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>12</td>\n",
              "      <td>12</td>\n",
              "      <td>1</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>1</td>\n",
              "      <td>0</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>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>BENE11007</td>\n",
              "      <td>1940-01-09</td>\n",
              "      <td>2009-01-12</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>12</td>\n",
              "      <td>12</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",
              "      <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>0</td>\n",
              "      <td>1490</td>\n",
              "      <td>160</td>\n",
              "      <td>69.0</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "      BeneID        DOB  ... OPAnnualDeductibleAmt   Age\n",
              "0  BENE11001 1943-01-01  ...                    70   NaN\n",
              "1  BENE11007 1940-01-09  ...                   160  69.0\n",
              "\n",
              "[2 rows x 23 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 156
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "iSe_9nRkyaet",
        "colab_type": "text"
      },
      "source": [
        "**Add Flag column 'WhetherDead' using DOD values to tell whether beneficiary is dead on not**\n",
        "\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "G2Ak3uP7yRif",
        "colab_type": "code",
        "outputId": "f30ab8b4-33f5-4e1b-d573-c10ac0285ed0",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 150
        }
      },
      "source": [
        "Train_Beneficiarydata.loc[Train_Beneficiarydata.DOD.isna(),'WhetherDead']=0\n",
        "Train_Beneficiarydata.loc[Train_Beneficiarydata.DOD.notna(),'WhetherDead']=1\n",
        "Train_Beneficiarydata.loc[:,'WhetherDead'].head(7)\n",
        "\n"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "0    0.0\n",
              "1    0.0\n",
              "2    0.0\n",
              "3    0.0\n",
              "4    0.0\n",
              "5    0.0\n",
              "6    1.0\n",
              "Name: WhetherDead, dtype: float64"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 157
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "4PLMBbOvykwQ",
        "colab_type": "code",
        "outputId": "d21a1984-591d-428d-f092-e8879eee8d74",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 83
        }
      },
      "source": [
        "Test_Beneficiarydata.loc[Test_Beneficiarydata.DOD.isna(),'WhetherDead']=0\n",
        "Test_Beneficiarydata.loc[Test_Beneficiarydata.DOD.notna(),'WhetherDead']=1\n",
        "Test_Beneficiarydata.loc[:,'WhetherDead'].head(3)"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "0    0.0\n",
              "1    1.0\n",
              "2    0.0\n",
              "Name: WhetherDead, dtype: float64"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 158
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "n4x5Q9GnzDTa",
        "colab_type": "text"
      },
      "source": [
        "**Inpatient Data Understanding**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "1R-5uRSTy23C",
        "colab_type": "code",
        "outputId": "54ac9edf-e6a3-415a-b34c-0e2774315a7c",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 533
        }
      },
      "source": [
        "# checking the missing values in each column in inpatient data\n",
        "Train_Inpatientdata.isna().sum()\n",
        "Test_Inpatientdata.isna().sum()"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "BeneID                       0\n",
              "ClaimID                      0\n",
              "ClaimStartDt                 0\n",
              "ClaimEndDt                   0\n",
              "Provider                     0\n",
              "InscClaimAmtReimbursed       0\n",
              "AttendingPhysician          31\n",
              "OperatingPhysician        3962\n",
              "OtherPhysician            8538\n",
              "AdmissionDt                  0\n",
              "ClmAdmitDiagnosisCode        0\n",
              "DeductibleAmtPaid          196\n",
              "DischargeDt                  0\n",
              "DiagnosisGroupCode           0\n",
              "ClmDiagnosisCode_1           0\n",
              "ClmDiagnosisCode_2          54\n",
              "ClmDiagnosisCode_3         169\n",
              "ClmDiagnosisCode_4         404\n",
              "ClmDiagnosisCode_5         719\n",
              "ClmDiagnosisCode_6        1197\n",
              "ClmDiagnosisCode_7        1736\n",
              "ClmDiagnosisCode_8        2360\n",
              "ClmDiagnosisCode_9        3238\n",
              "ClmDiagnosisCode_10       8664\n",
              "ClmProcedureCode_1        4118\n",
              "ClmProcedureCode_2        8297\n",
              "ClmProcedureCode_3        9328\n",
              "ClmProcedureCode_4        9522\n",
              "ClmProcedureCode_5        9549\n",
              "ClmProcedureCode_6        9551\n",
              "dtype: int64"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 159
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "_xWuGgHL1Dm3",
        "colab_type": "text"
      },
      "source": [
        "**Create new column 'AdmitForDays' indicating number of days patient was admitted in hospital**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "XAksEc0ry99N",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "Train_Inpatientdata['AdmissionDt'] = pd.to_datetime(Train_Inpatientdata['AdmissionDt'] , format = '%Y-%m-%d')\n",
        "Train_Inpatientdata['DischargeDt'] = pd.to_datetime(Train_Inpatientdata['DischargeDt'],format = '%Y-%m-%d')\n",
        "Train_Inpatientdata['AdmitForDays'] = ((Train_Inpatientdata['DischargeDt'] - Train_Inpatientdata['AdmissionDt']).dt.days)+1"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "6sDxN-j81O0P",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "Test_Inpatientdata['AdmissionDt'] = pd.to_datetime(Test_Inpatientdata['AdmissionDt'] , format = '%Y-%m-%d')\n",
        "Test_Inpatientdata['DischargeDt'] = pd.to_datetime(Test_Inpatientdata['DischargeDt'],format = '%Y-%m-%d')\n",
        "Test_Inpatientdata['AdmitForDays'] = ((Test_Inpatientdata['DischargeDt'] - Test_Inpatientdata['AdmissionDt']).dt.days)+1"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "0Gc4vDfv1jjV",
        "colab_type": "code",
        "outputId": "25c209d9-5917-456c-c183-e1db2a237722",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 100
        }
      },
      "source": [
        "## Checking Min and Max values of AdmitforDays column in Train and Test.\n",
        "print('Min AdmitForDays Train:- ',Train_Inpatientdata.AdmitForDays.min())\n",
        "print('Max AdmitForDays Train:- ',Train_Inpatientdata.AdmitForDays.max())\n",
        "Train_Inpatientdata.AdmitForDays.isnull().sum()   #Check Null values.\n",
        "\n",
        "print('Min AdmitForDays Test:- ',Test_Inpatientdata.AdmitForDays.min())\n",
        "print('Max AdmitForDays Test:- ',Test_Inpatientdata.AdmitForDays.max())\n",
        "Test_Inpatientdata.AdmitForDays.isnull().sum()   #Check Null values."
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Min AdmitForDays Train:-  1\n",
            "Max AdmitForDays Train:-  36\n",
            "Min AdmitForDays Test:-  1\n",
            "Max AdmitForDays Test:-  36\n"
          ],
          "name": "stdout"
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "0"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 162
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "xwHMb4tR2GAl",
        "colab_type": "text"
      },
      "source": [
        "**Outpatient Data understanding**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "PNAX1e6x1nj8",
        "colab_type": "code",
        "outputId": "ada62257-48f3-45f7-a4fe-05e9709d2e68",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 483
        }
      },
      "source": [
        "#checking null values in each columnn of outpatient data\n",
        "Train_Outpatientdata.isna().sum()\n",
        "\n",
        "Test_Outpatientdata.isna().sum()"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "BeneID                         0\n",
              "ClaimID                        0\n",
              "ClaimStartDt                   0\n",
              "ClaimEndDt                     0\n",
              "Provider                       0\n",
              "InscClaimAmtReimbursed         0\n",
              "AttendingPhysician           316\n",
              "OperatingPhysician        104237\n",
              "OtherPhysician             78222\n",
              "ClmDiagnosisCode_1          2578\n",
              "ClmDiagnosisCode_2         47731\n",
              "ClmDiagnosisCode_3         76575\n",
              "ClmDiagnosisCode_4         95371\n",
              "ClmDiagnosisCode_5        107875\n",
              "ClmDiagnosisCode_6        114035\n",
              "ClmDiagnosisCode_7        117871\n",
              "ClmDiagnosisCode_8        120310\n",
              "ClmDiagnosisCode_9        122278\n",
              "ClmDiagnosisCode_10       125578\n",
              "ClmProcedureCode_1        125807\n",
              "ClmProcedureCode_2        125832\n",
              "ClmProcedureCode_3        125839\n",
              "ClmProcedureCode_4        125841\n",
              "ClmProcedureCode_5        125841\n",
              "ClmProcedureCode_6        125841\n",
              "DeductibleAmtPaid              0\n",
              "ClmAdmitDiagnosisCode     100036\n",
              "dtype: int64"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 163
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "IAw6bqRx2cWg",
        "colab_type": "code",
        "outputId": "64554f22-c930-44ef-e048-5500419c22fe",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 150
        }
      },
      "source": [
        "## Lets Check Shape of datasets after adding new variables\n",
        "\n",
        "print('Shape of Train data :',Train.shape)\n",
        "print('Shape of Train_Beneficiarydata data :',Train_Beneficiarydata.shape)\n",
        "print('Shape of Train_Inpatientdata data :',Train_Inpatientdata.shape)\n",
        "print('Shape of Train_Outpatientdata data :',Train_Outpatientdata.shape)\n",
        "\n",
        "print('Shape of Test data :',Test.shape)\n",
        "print('Shape of Test_Beneficiarydata data :',Test_Beneficiarydata.shape)\n",
        "print('Shape of Test_Inpatientdata data :',Test_Inpatientdata.shape)\n",
        "print('Shape of Test_Outpatientdata data :',Test_Outpatientdata.shape)"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Shape of Train data : (5410, 2)\n",
            "Shape of Train_Beneficiarydata data : (138556, 24)\n",
            "Shape of Train_Inpatientdata data : (40474, 31)\n",
            "Shape of Train_Outpatientdata data : (517737, 27)\n",
            "Shape of Test data : (1353, 1)\n",
            "Shape of Test_Beneficiarydata data : (63968, 24)\n",
            "Shape of Test_Inpatientdata data : (9551, 31)\n",
            "Shape of Test_Outpatientdata data : (125841, 27)\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "xbMAAXJj3abH",
        "colab_type": "text"
      },
      "source": [
        "## **DATA MERGING**\n",
        "Merging of inpatient and outpatient data"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "gBBmbR5744W3",
        "colab_type": "code",
        "outputId": "3bb6c24b-8883-4e91-fc8e-b2e85b23d5ad",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 183
        }
      },
      "source": [
        "merge_keys=Train_Outpatientdata.columns\n",
        "print(merge_keys)"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Index(['BeneID', 'ClaimID', 'ClaimStartDt', 'ClaimEndDt', 'Provider',\n",
            "       'InscClaimAmtReimbursed', 'AttendingPhysician', 'OperatingPhysician',\n",
            "       'OtherPhysician', 'ClmDiagnosisCode_1', 'ClmDiagnosisCode_2',\n",
            "       'ClmDiagnosisCode_3', 'ClmDiagnosisCode_4', 'ClmDiagnosisCode_5',\n",
            "       'ClmDiagnosisCode_6', 'ClmDiagnosisCode_7', 'ClmDiagnosisCode_8',\n",
            "       'ClmDiagnosisCode_9', 'ClmDiagnosisCode_10', 'ClmProcedureCode_1',\n",
            "       'ClmProcedureCode_2', 'ClmProcedureCode_3', 'ClmProcedureCode_4',\n",
            "       'ClmProcedureCode_5', 'ClmProcedureCode_6', 'DeductibleAmtPaid',\n",
            "       'ClmAdmitDiagnosisCode'],\n",
            "      dtype='object')\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "WaswSm4b2wql",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "Train_Allpatientdata=pd.merge(Train_Outpatientdata,Train_Inpatientdata,\n",
        "                              left_on=['BeneID', 'ClaimID', 'ClaimStartDt', 'ClaimEndDt', 'Provider',\n",
        "       'InscClaimAmtReimbursed', 'AttendingPhysician', 'OperatingPhysician',\n",
        "       'OtherPhysician', 'ClmDiagnosisCode_1', 'ClmDiagnosisCode_2',\n",
        "       'ClmDiagnosisCode_3', 'ClmDiagnosisCode_4', 'ClmDiagnosisCode_5',\n",
        "       'ClmDiagnosisCode_6', 'ClmDiagnosisCode_7', 'ClmDiagnosisCode_8',\n",
        "       'ClmDiagnosisCode_9', 'ClmDiagnosisCode_10', 'ClmProcedureCode_1',\n",
        "       'ClmProcedureCode_2', 'ClmProcedureCode_3', 'ClmProcedureCode_4',\n",
        "       'ClmProcedureCode_5', 'ClmProcedureCode_6', 'DeductibleAmtPaid',\n",
        "       'ClmAdmitDiagnosisCode'],\n",
        "                              right_on=['BeneID', 'ClaimID', 'ClaimStartDt', 'ClaimEndDt', 'Provider',\n",
        "       'InscClaimAmtReimbursed', 'AttendingPhysician', 'OperatingPhysician',\n",
        "       'OtherPhysician', 'ClmDiagnosisCode_1', 'ClmDiagnosisCode_2',\n",
        "       'ClmDiagnosisCode_3', 'ClmDiagnosisCode_4', 'ClmDiagnosisCode_5',\n",
        "       'ClmDiagnosisCode_6', 'ClmDiagnosisCode_7', 'ClmDiagnosisCode_8',\n",
        "       'ClmDiagnosisCode_9', 'ClmDiagnosisCode_10', 'ClmProcedureCode_1',\n",
        "       'ClmProcedureCode_2', 'ClmProcedureCode_3', 'ClmProcedureCode_4',\n",
        "       'ClmProcedureCode_5', 'ClmProcedureCode_6', 'DeductibleAmtPaid',\n",
        "       'ClmAdmitDiagnosisCode']\n",
        "                              ,how='outer')\n",
        "\n",
        "\n",
        "Test_Allpatientdata=pd.merge(Test_Outpatientdata,Test_Inpatientdata,\n",
        "                              left_on=['BeneID', 'ClaimID', 'ClaimStartDt', 'ClaimEndDt', 'Provider',\n",
        "       'InscClaimAmtReimbursed', 'AttendingPhysician', 'OperatingPhysician',\n",
        "       'OtherPhysician', 'ClmDiagnosisCode_1', 'ClmDiagnosisCode_2',\n",
        "       'ClmDiagnosisCode_3', 'ClmDiagnosisCode_4', 'ClmDiagnosisCode_5',\n",
        "       'ClmDiagnosisCode_6', 'ClmDiagnosisCode_7', 'ClmDiagnosisCode_8',\n",
        "       'ClmDiagnosisCode_9', 'ClmDiagnosisCode_10', 'ClmProcedureCode_1',\n",
        "       'ClmProcedureCode_2', 'ClmProcedureCode_3', 'ClmProcedureCode_4',\n",
        "       'ClmProcedureCode_5', 'ClmProcedureCode_6', 'DeductibleAmtPaid',\n",
        "       'ClmAdmitDiagnosisCode'],\n",
        "                              right_on=['BeneID', 'ClaimID', 'ClaimStartDt', 'ClaimEndDt', 'Provider',\n",
        "       'InscClaimAmtReimbursed', 'AttendingPhysician', 'OperatingPhysician',\n",
        "       'OtherPhysician', 'ClmDiagnosisCode_1', 'ClmDiagnosisCode_2',\n",
        "       'ClmDiagnosisCode_3', 'ClmDiagnosisCode_4', 'ClmDiagnosisCode_5',\n",
        "       'ClmDiagnosisCode_6', 'ClmDiagnosisCode_7', 'ClmDiagnosisCode_8',\n",
        "       'ClmDiagnosisCode_9', 'ClmDiagnosisCode_10', 'ClmProcedureCode_1',\n",
        "       'ClmProcedureCode_2', 'ClmProcedureCode_3', 'ClmProcedureCode_4',\n",
        "       'ClmProcedureCode_5', 'ClmProcedureCode_6', 'DeductibleAmtPaid',\n",
        "       'ClmAdmitDiagnosisCode']\n",
        "                              ,how='outer')"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "NEuBXf5t5GG5",
        "colab_type": "code",
        "outputId": "8ecce400-6c2b-4cd9-d68a-f30eb688600e",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 52
        }
      },
      "source": [
        "print('Train_Allpatientdata',Train_Allpatientdata.shape)\n",
        "print('Test_Allpatientdata',Test_Allpatientdata.shape)"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Train_Allpatientdata (558211, 31)\n",
            "Test_Allpatientdata (135392, 31)\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ceC--AqQ64Qz",
        "colab_type": "code",
        "outputId": "720733f3-9a00-475a-9fb4-9e25e0bc177c",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 191
        }
      },
      "source": [
        "Train_Allpatientdata.head(4)\n",
        "Test_Allpatientdata.head(4)"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>ClaimID</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>ClmDiagnosisCode_1</th>\n",
              "      <th>ClmDiagnosisCode_2</th>\n",
              "      <th>ClmDiagnosisCode_3</th>\n",
              "      <th>ClmDiagnosisCode_4</th>\n",
              "      <th>ClmDiagnosisCode_5</th>\n",
              "      <th>ClmDiagnosisCode_6</th>\n",
              "      <th>ClmDiagnosisCode_7</th>\n",
              "      <th>ClmDiagnosisCode_8</th>\n",
              "      <th>ClmDiagnosisCode_9</th>\n",
              "      <th>ClmDiagnosisCode_10</th>\n",
              "      <th>ClmProcedureCode_1</th>\n",
              "      <th>ClmProcedureCode_2</th>\n",
              "      <th>ClmProcedureCode_3</th>\n",
              "      <th>ClmProcedureCode_4</th>\n",
              "      <th>ClmProcedureCode_5</th>\n",
              "      <th>ClmProcedureCode_6</th>\n",
              "      <th>DeductibleAmtPaid</th>\n",
              "      <th>ClmAdmitDiagnosisCode</th>\n",
              "      <th>AdmissionDt</th>\n",
              "      <th>DischargeDt</th>\n",
              "      <th>DiagnosisGroupCode</th>\n",
              "      <th>AdmitForDays</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>BENE11001</td>\n",
              "      <td>CLM392397</td>\n",
              "      <td>2009-06-02</td>\n",
              "      <td>2009-06-02</td>\n",
              "      <td>PRV55962</td>\n",
              "      <td>30</td>\n",
              "      <td>PHY347633</td>\n",
              "      <td>NaN</td>\n",
              "      <td>PHY347633</td>\n",
              "      <td>V5832</td>\n",
              "      <td>V5861</td>\n",
              "      <td>2724</td>\n",
              "      <td>3182</td>\n",
              "      <td>V5869</td>\n",
              "      <td>42731</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>0.0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaT</td>\n",
              "      <td>NaT</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>BENE11001</td>\n",
              "      <td>CLM430760</td>\n",
              "      <td>2009-06-23</td>\n",
              "      <td>2009-06-23</td>\n",
              "      <td>PRV56112</td>\n",
              "      <td>30</td>\n",
              "      <td>PHY381777</td>\n",
              "      <td>NaN</td>\n",
              "      <td>PHY381777</td>\n",
              "      <td>9594</td>\n",
              "      <td>E9174</td>\n",
              "      <td>4019</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>0.0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaT</td>\n",
              "      <td>NaT</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>BENE11007</td>\n",
              "      <td>CLM233081</td>\n",
              "      <td>2009-03-07</td>\n",
              "      <td>2009-03-07</td>\n",
              "      <td>PRV56979</td>\n",
              "      <td>200</td>\n",
              "      <td>PHY425311</td>\n",
              "      <td>NaN</td>\n",
              "      <td>PHY425311</td>\n",
              "      <td>7248</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>0.0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaT</td>\n",
              "      <td>NaT</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>BENE11007</td>\n",
              "      <td>CLM496381</td>\n",
              "      <td>2009-07-29</td>\n",
              "      <td>2009-07-29</td>\n",
              "      <td>PRV56573</td>\n",
              "      <td>10</td>\n",
              "      <td>PHY393253</td>\n",
              "      <td>PHY347995</td>\n",
              "      <td>NaN</td>\n",
              "      <td>58889</td>\n",
              "      <td>2449</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>0.0</td>\n",
              "      <td>5939</td>\n",
              "      <td>NaT</td>\n",
              "      <td>NaT</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "      BeneID    ClaimID  ... DiagnosisGroupCode AdmitForDays\n",
              "0  BENE11001  CLM392397  ...                NaN          NaN\n",
              "1  BENE11001  CLM430760  ...                NaN          NaN\n",
              "2  BENE11007  CLM233081  ...                NaN          NaN\n",
              "3  BENE11007  CLM496381  ...                NaN          NaN\n",
              "\n",
              "[4 rows x 31 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 168
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Xpzo03ID7A6E",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "## Merging All patient data with beneficiary details data based on 'BeneID' as joining key for inner join\n",
        "Train_AllPatientDetailsdata=pd.merge(Train_Allpatientdata,Train_Beneficiarydata,left_on='BeneID',right_on='BeneID',how='inner')\n",
        "Test_AllPatientDetailsdata=pd.merge(Test_Allpatientdata,Test_Beneficiarydata,left_on='BeneID',right_on='BeneID',how='inner')"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "lvohyRid7RcN",
        "colab_type": "code",
        "outputId": "28f6f1cc-ae51-4876-87ce-a61f2df5fc8b",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 199
        }
      },
      "source": [
        "## Lets Print shape of data \n",
        "print('Shape of All Patient Details Train : ',Train_AllPatientDetailsdata.shape)\n",
        "Train_AllPatientDetailsdata.head()\n",
        "\n",
        "print('Shape of All Patient Details Test : ',Test_AllPatientDetailsdata.shape)\n",
        "Test_AllPatientDetailsdata.head(2)\n",
        "\n"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Shape of All Patient Details Train :  (558211, 54)\n",
            "Shape of All Patient Details Test :  (135392, 54)\n"
          ],
          "name": "stdout"
        },
        {
          "output_type": "execute_result",
          "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>ClaimID</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>ClmDiagnosisCode_1</th>\n",
              "      <th>ClmDiagnosisCode_2</th>\n",
              "      <th>ClmDiagnosisCode_3</th>\n",
              "      <th>ClmDiagnosisCode_4</th>\n",
              "      <th>ClmDiagnosisCode_5</th>\n",
              "      <th>ClmDiagnosisCode_6</th>\n",
              "      <th>ClmDiagnosisCode_7</th>\n",
              "      <th>ClmDiagnosisCode_8</th>\n",
              "      <th>ClmDiagnosisCode_9</th>\n",
              "      <th>ClmDiagnosisCode_10</th>\n",
              "      <th>ClmProcedureCode_1</th>\n",
              "      <th>ClmProcedureCode_2</th>\n",
              "      <th>ClmProcedureCode_3</th>\n",
              "      <th>ClmProcedureCode_4</th>\n",
              "      <th>ClmProcedureCode_5</th>\n",
              "      <th>ClmProcedureCode_6</th>\n",
              "      <th>DeductibleAmtPaid</th>\n",
              "      <th>ClmAdmitDiagnosisCode</th>\n",
              "      <th>AdmissionDt</th>\n",
              "      <th>DischargeDt</th>\n",
              "      <th>DiagnosisGroupCode</th>\n",
              "      <th>AdmitForDays</th>\n",
              "      <th>DOB</th>\n",
              "      <th>DOD</th>\n",
              "      <th>Gender</th>\n",
              "      <th>RenalDiseaseIndicator</th>\n",
              "      <th>NoOfMonths_PartACov</th>\n",
              "      <th>NoOfMonths_PartBCov</th>\n",
              "      <th>ChronicCond_Alzheimer</th>\n",
              "      <th>ChronicCond_Heartfailure</th>\n",
              "      <th>ChronicCond_KidneyDisease</th>\n",
              "      <th>ChronicCond_Cancer</th>\n",
              "      <th>ChronicCond_ObstrPulmonary</th>\n",
              "      <th>ChronicCond_Depression</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>Age</th>\n",
              "      <th>WhetherDead</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>BENE11001</td>\n",
              "      <td>CLM392397</td>\n",
              "      <td>2009-06-02</td>\n",
              "      <td>2009-06-02</td>\n",
              "      <td>PRV55962</td>\n",
              "      <td>30</td>\n",
              "      <td>PHY347633</td>\n",
              "      <td>NaN</td>\n",
              "      <td>PHY347633</td>\n",
              "      <td>V5832</td>\n",
              "      <td>V5861</td>\n",
              "      <td>2724</td>\n",
              "      <td>3182</td>\n",
              "      <td>V5869</td>\n",
              "      <td>42731</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>0.0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaT</td>\n",
              "      <td>NaT</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>1943-01-01</td>\n",
              "      <td>NaT</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>12</td>\n",
              "      <td>12</td>\n",
              "      <td>1</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>1</td>\n",
              "      <td>0</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>NaN</td>\n",
              "      <td>0.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>BENE11001</td>\n",
              "      <td>CLM430760</td>\n",
              "      <td>2009-06-23</td>\n",
              "      <td>2009-06-23</td>\n",
              "      <td>PRV56112</td>\n",
              "      <td>30</td>\n",
              "      <td>PHY381777</td>\n",
              "      <td>NaN</td>\n",
              "      <td>PHY381777</td>\n",
              "      <td>9594</td>\n",
              "      <td>E9174</td>\n",
              "      <td>4019</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>0.0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaT</td>\n",
              "      <td>NaT</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>1943-01-01</td>\n",
              "      <td>NaT</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>12</td>\n",
              "      <td>12</td>\n",
              "      <td>1</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>1</td>\n",
              "      <td>0</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>NaN</td>\n",
              "      <td>0.0</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "      BeneID    ClaimID ClaimStartDt  ... OPAnnualDeductibleAmt Age  WhetherDead\n",
              "0  BENE11001  CLM392397   2009-06-02  ...                    70 NaN          0.0\n",
              "1  BENE11001  CLM430760   2009-06-23  ...                    70 NaN          0.0\n",
              "\n",
              "[2 rows x 54 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 170
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "adh4y6q47k9N",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "# Lets merge patient data with fradulent providers details data with \"Provider\" as joining key for inner join\n",
        "\n",
        "Train_ProviderWithPatientDetailsdata=pd.merge(Train,Train_AllPatientDetailsdata,on='Provider')\n",
        "Train_ProviderWithPatientDetailsdata.to_csv('/content/drive/My Drive/Final_year_Project/Datasets/Train_ProviderWithPatientDetailsdata.csv')\n",
        "\n",
        "Test_ProviderWithPatientDetailsdata=pd.merge(Test,Test_ProviderWithPatientDetailsdata,on='Provider')\n",
        "Test_ProviderWithPatientDetailsdata.to_csv('/content/drive/My Drive/Final_year_Project/Datasets/Test_ProviderWithPatientDetailsdata.csv')"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "AeySN3d08HWM",
        "colab_type": "code",
        "outputId": "8fcc1d02-bc6d-40e0-d8ec-6c2b4b6043ac",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 52
        }
      },
      "source": [
        "## Lets Print the shape of dataset \n",
        "print(\"Shape of Train Provider with Patient Details data :-\",Train_ProviderWithPatientDetailsdata.shape)\n",
        "Train_ProviderWithPatientDetailsdata.head()\n",
        "\n",
        "print(\"Shape of Test Provider with Patient Details data :-\",Test_ProviderWithPatientDetailsdata.shape)\n",
        "#Test_ProviderWithPatientDetailsdata.head()"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Shape of Train Provider with Patient Details data :- (558211, 55)\n",
            "Shape of Test Provider with Patient Details data :- (135392, 54)\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "9qDMTjFCOktZ",
        "colab_type": "code",
        "outputId": "1958c35e-0a1a-431b-a5d3-6b558c59d9be",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 1000
        }
      },
      "source": [
        "### Lets check missing value percentage in full datasets\n",
        "\n",
        "#print('Percent missing values in Train Provider with patient details data :\\n')\n",
        "Train_ProviderWithPatientDetailsdata.isnull().sum()*100/len(Train_ProviderWithPatientDetailsdata)\n",
        "\n",
        "\n",
        "print('Percent missing values in Test Provider with patient details data :\\n')\n",
        "Test_ProviderWithPatientDetailsdata.isnull().sum()*100/len(Test_ProviderWithPatientDetailsdata)"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Percent missing values in Test Provider with patient details data :\n",
            "\n"
          ],
          "name": "stdout"
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "Provider                             0.000000\n",
              "BeneID                               0.000000\n",
              "ClaimID                              0.000000\n",
              "ClaimStartDt                         0.000000\n",
              "ClaimEndDt                           0.000000\n",
              "InscClaimAmtReimbursed               0.000000\n",
              "AttendingPhysician                   0.256293\n",
              "OperatingPhysician                  79.915357\n",
              "OtherPhysician                      64.080596\n",
              "ClmDiagnosisCode_1                   1.904101\n",
              "ClmDiagnosisCode_2                  35.293814\n",
              "ClmDiagnosisCode_3                  56.682817\n",
              "ClmDiagnosisCode_4                  70.739039\n",
              "ClmDiagnosisCode_5                  80.207102\n",
              "ClmDiagnosisCode_6                  85.109903\n",
              "ClmDiagnosisCode_7                  88.341261\n",
              "ClmDiagnosisCode_8                  90.603581\n",
              "ClmDiagnosisCode_9                  92.705625\n",
              "ClmDiagnosisCode_10                 99.150615\n",
              "ClmProcedureCode_1                  95.962095\n",
              "ClmProcedureCode_2                  99.067153\n",
              "ClmProcedureCode_3                  99.833816\n",
              "ClmProcedureCode_4                  99.978581\n",
              "ClmProcedureCode_5                  99.998523\n",
              "ClmProcedureCode_6                 100.000000\n",
              "DeductibleAmtPaid                    0.144765\n",
              "ClmAdmitDiagnosisCode               73.886197\n",
              "AdmissionDt                         92.945669\n",
              "DischargeDt                         92.945669\n",
              "DiagnosisGroupCode                  92.945669\n",
              "AdmitForDays                        92.945669\n",
              "DOB                                  0.000000\n",
              "DOD                                 99.231860\n",
              "Gender                               0.000000\n",
              "RenalDiseaseIndicator                0.000000\n",
              "NoOfMonths_PartACov                  0.000000\n",
              "NoOfMonths_PartBCov                  0.000000\n",
              "ChronicCond_Alzheimer                0.000000\n",
              "ChronicCond_Heartfailure             0.000000\n",
              "ChronicCond_KidneyDisease            0.000000\n",
              "ChronicCond_Cancer                   0.000000\n",
              "ChronicCond_ObstrPulmonary           0.000000\n",
              "ChronicCond_Depression               0.000000\n",
              "ChronicCond_Diabetes                 0.000000\n",
              "ChronicCond_IschemicHeart            0.000000\n",
              "ChronicCond_Osteoporasis             0.000000\n",
              "ChronicCond_rheumatoidarthritis      0.000000\n",
              "ChronicCond_stroke                   0.000000\n",
              "IPAnnualReimbursementAmt             0.000000\n",
              "IPAnnualDeductibleAmt                0.000000\n",
              "OPAnnualReimbursementAmt             0.000000\n",
              "OPAnnualDeductibleAmt                0.000000\n",
              "Age                                 99.231860\n",
              "WhetherDead                          0.000000\n",
              "dtype: float64"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 174
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "GMG3d3ZzOvys",
        "colab_type": "code",
        "outputId": "a9b040fe-f881-49a4-cb1e-7c848cfe5f46",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 971
        }
      },
      "source": [
        "## Lets Check dtypes of both the datasets\n",
        "Train_ProviderWithPatientDetailsdata.dtypes\n",
        "Test_ProviderWithPatientDetailsdata.dtypes"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "Provider                                   object\n",
              "BeneID                                     object\n",
              "ClaimID                                    object\n",
              "ClaimStartDt                               object\n",
              "ClaimEndDt                                 object\n",
              "InscClaimAmtReimbursed                      int64\n",
              "AttendingPhysician                         object\n",
              "OperatingPhysician                         object\n",
              "OtherPhysician                             object\n",
              "ClmDiagnosisCode_1                         object\n",
              "ClmDiagnosisCode_2                         object\n",
              "ClmDiagnosisCode_3                         object\n",
              "ClmDiagnosisCode_4                         object\n",
              "ClmDiagnosisCode_5                         object\n",
              "ClmDiagnosisCode_6                         object\n",
              "ClmDiagnosisCode_7                         object\n",
              "ClmDiagnosisCode_8                         object\n",
              "ClmDiagnosisCode_9                         object\n",
              "ClmDiagnosisCode_10                        object\n",
              "ClmProcedureCode_1                        float64\n",
              "ClmProcedureCode_2                        float64\n",
              "ClmProcedureCode_3                        float64\n",
              "ClmProcedureCode_4                        float64\n",
              "ClmProcedureCode_5                        float64\n",
              "ClmProcedureCode_6                        float64\n",
              "DeductibleAmtPaid                         float64\n",
              "ClmAdmitDiagnosisCode                      object\n",
              "AdmissionDt                        datetime64[ns]\n",
              "DischargeDt                        datetime64[ns]\n",
              "DiagnosisGroupCode                         object\n",
              "AdmitForDays                              float64\n",
              "DOB                                datetime64[ns]\n",
              "DOD                                datetime64[ns]\n",
              "Gender                                      int64\n",
              "RenalDiseaseIndicator                      object\n",
              "NoOfMonths_PartACov                         int64\n",
              "NoOfMonths_PartBCov                         int64\n",
              "ChronicCond_Alzheimer                       int64\n",
              "ChronicCond_Heartfailure                    int64\n",
              "ChronicCond_KidneyDisease                   int64\n",
              "ChronicCond_Cancer                          int64\n",
              "ChronicCond_ObstrPulmonary                  int64\n",
              "ChronicCond_Depression                      int64\n",
              "ChronicCond_Diabetes                        int64\n",
              "ChronicCond_IschemicHeart                   int64\n",
              "ChronicCond_Osteoporasis                    int64\n",
              "ChronicCond_rheumatoidarthritis             int64\n",
              "ChronicCond_stroke                          int64\n",
              "IPAnnualReimbursementAmt                    int64\n",
              "IPAnnualDeductibleAmt                       int64\n",
              "OPAnnualReimbursementAmt                    int64\n",
              "OPAnnualDeductibleAmt                       int64\n",
              "Age                                       float64\n",
              "WhetherDead                               float64\n",
              "dtype: object"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 175
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "i2qLnyIJR84c",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        ""
      ],
      "execution_count": 0,
      "outputs": []
    }
  ]
}