HDS 823 Models 1 and 2 PUF Outliers Disaggregated.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Outlier Handling Notebook: Models 1 & 2\n",
    "\n",
    "**Medicare DeSYNPuf Data: \n",
    "2009 Inpatient Claims for Risk Adjustment Modeling and Correspondence Analysis**\n",
    "\n",
    "HDS 823 Final Project: Advanced Statistics in Healthcare\n",
    "Kyle P. Rasku RN BSN"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "%matplotlib inline\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import seaborn as sb\n",
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load 2009 IP & ELIX Data, Created by the Cleaning Notebook\n",
    "desynpuf = pd.read_csv(\"/home/kylier/python/DS/data/PUF/PUF_RWJF_ALLIP2009.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>Unnamed: 0.1</th>\n",
       "      <th>DESYNPUF_ID</th>\n",
       "      <th>FIPS_COUNTY_CODE</th>\n",
       "      <th>FULL_FIPS_CODE</th>\n",
       "      <th>CBSA</th>\n",
       "      <th>CBSA_NAME</th>\n",
       "      <th>FIPS_STATE_CODE</th>\n",
       "      <th>CLM_ID</th>\n",
       "      <th>BENE_AGE</th>\n",
       "      <th>...</th>\n",
       "      <th>COUNTY</th>\n",
       "      <th>NUM_COUNTIES</th>\n",
       "      <th>HO_RANK</th>\n",
       "      <th>HO_PROP_RANK</th>\n",
       "      <th>HO_QUARTILE</th>\n",
       "      <th>HF_RANK</th>\n",
       "      <th>HF_PROP_RANK</th>\n",
       "      <th>HF_QUARTILE</th>\n",
       "      <th>StateCode</th>\n",
       "      <th>CountyFIPS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0000438E79D01BEA</td>\n",
       "      <td>57</td>\n",
       "      <td>12057</td>\n",
       "      <td>45300</td>\n",
       "      <td>Tampa-St. Petersburg-Clearwater, FL</td>\n",
       "      <td>12</td>\n",
       "      <td>NaN</td>\n",
       "      <td>73.0</td>\n",
       "      <td>...</td>\n",
       "      <td>Hillsborough</td>\n",
       "      <td>67.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>0.597</td>\n",
       "      <td>2.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>0.537</td>\n",
       "      <td>2.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>57.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>00010E7BEA69142C</td>\n",
       "      <td>3</td>\n",
       "      <td>10003</td>\n",
       "      <td>48864</td>\n",
       "      <td>Wilmington, DE-MD-NJ</td>\n",
       "      <td>10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>72.0</td>\n",
       "      <td>...</td>\n",
       "      <td>New Castle</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.667</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.667</td>\n",
       "      <td>1.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>00015BF6509E1DF7</td>\n",
       "      <td>119</td>\n",
       "      <td>37119</td>\n",
       "      <td>16740</td>\n",
       "      <td>Charlotte-Gastonia-Concord, NC-SC</td>\n",
       "      <td>37</td>\n",
       "      <td>NaN</td>\n",
       "      <td>76.0</td>\n",
       "      <td>...</td>\n",
       "      <td>Mecklenburg</td>\n",
       "      <td>100.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.950</td>\n",
       "      <td>1.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>0.860</td>\n",
       "      <td>1.0</td>\n",
       "      <td>37.0</td>\n",
       "      <td>119.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>000240D599ED789C</td>\n",
       "      <td>35</td>\n",
       "      <td>55035</td>\n",
       "      <td>20740</td>\n",
       "      <td>Eau Claire, WI</td>\n",
       "      <td>55</td>\n",
       "      <td>6.925911e+14</td>\n",
       "      <td>67.0</td>\n",
       "      <td>...</td>\n",
       "      <td>Eau Claire</td>\n",
       "      <td>72.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>0.819</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.944</td>\n",
       "      <td>1.0</td>\n",
       "      <td>55.0</td>\n",
       "      <td>35.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>0003E73AFBA840A8</td>\n",
       "      <td>55</td>\n",
       "      <td>36055</td>\n",
       "      <td>40380</td>\n",
       "      <td>Rochester, NY</td>\n",
       "      <td>36</td>\n",
       "      <td>NaN</td>\n",
       "      <td>68.0</td>\n",
       "      <td>...</td>\n",
       "      <td>Monroe</td>\n",
       "      <td>62.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>0.500</td>\n",
       "      <td>2.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>0.726</td>\n",
       "      <td>2.0</td>\n",
       "      <td>36.0</td>\n",
       "      <td>55.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 52 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0  Unnamed: 0.1       DESYNPUF_ID  FIPS_COUNTY_CODE  \\\n",
       "0           0             0  0000438E79D01BEA                57   \n",
       "1           1             1  00010E7BEA69142C                 3   \n",
       "2           2             2  00015BF6509E1DF7               119   \n",
       "3           3             3  000240D599ED789C                35   \n",
       "4           4             4  0003E73AFBA840A8                55   \n",
       "\n",
       "   FULL_FIPS_CODE   CBSA                            CBSA_NAME  \\\n",
       "0           12057  45300  Tampa-St. Petersburg-Clearwater, FL   \n",
       "1           10003  48864                 Wilmington, DE-MD-NJ   \n",
       "2           37119  16740    Charlotte-Gastonia-Concord, NC-SC   \n",
       "3           55035  20740                       Eau Claire, WI   \n",
       "4           36055  40380                        Rochester, NY   \n",
       "\n",
       "   FIPS_STATE_CODE        CLM_ID  BENE_AGE  ...        COUNTY  NUM_COUNTIES  \\\n",
       "0               12           NaN      73.0  ...  Hillsborough          67.0   \n",
       "1               10           NaN      72.0  ...    New Castle           3.0   \n",
       "2               37           NaN      76.0  ...   Mecklenburg         100.0   \n",
       "3               55  6.925911e+14      67.0  ...    Eau Claire          72.0   \n",
       "4               36           NaN      68.0  ...        Monroe          62.0   \n",
       "\n",
       "   HO_RANK  HO_PROP_RANK  HO_QUARTILE  HF_RANK  HF_PROP_RANK  HF_QUARTILE  \\\n",
       "0     27.0         0.597          2.0     31.0         0.537          2.0   \n",
       "1      1.0         0.667          1.0      1.0         0.667          1.0   \n",
       "2      5.0         0.950          1.0     14.0         0.860          1.0   \n",
       "3     13.0         0.819          1.0      4.0         0.944          1.0   \n",
       "4     31.0         0.500          2.0     17.0         0.726          2.0   \n",
       "\n",
       "   StateCode  CountyFIPS  \n",
       "0       12.0        57.0  \n",
       "1       10.0         3.0  \n",
       "2       37.0       119.0  \n",
       "3       55.0        35.0  \n",
       "4       36.0        55.0  \n",
       "\n",
       "[5 rows x 52 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "desynpuf.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DESYNPUF_ID             0\n",
       "FIPS_COUNTY_CODE        0\n",
       "FULL_FIPS_CODE          0\n",
       "CBSA                    0\n",
       "CBSA_NAME           27839\n",
       "FIPS_STATE_CODE         0\n",
       "CLM_ID              95763\n",
       "BENE_AGE                0\n",
       "AGE_GROUP               0\n",
       "B_SEX                   0\n",
       "B_ALZHDMTA              0\n",
       "B_CHF                   0\n",
       "B_CNCR                  0\n",
       "B_COPD                  0\n",
       "B_DEPRESSN              0\n",
       "B_DIABETES              0\n",
       "B_ISCHMCHT              0\n",
       "B_OSTEOPRS              0\n",
       "B_CHRNKIDN              0\n",
       "B_RA_OA                 0\n",
       "B_STRKETIA              0\n",
       "B_ESRD                  0\n",
       "NH_WHITE                0\n",
       "AA_BLACK                0\n",
       "OTHER                   0\n",
       "NW_HISPANIC             0\n",
       "MEAN_ELIX_SCORE         0\n",
       "CODES_COUNT             0\n",
       "TOTCHRONIC              0\n",
       "ALLCOSTS                0\n",
       "CVRG_MOS                0\n",
       "RX_CVRG_MOS             0\n",
       "LENGTH_OF_STAY      95763\n",
       "PHYS_COUNT              0\n",
       "B_DIED                  0\n",
       "PERCENT_COSTS           0\n",
       "ANY_COSTS               0\n",
       "POC                     0\n",
       "FIPS                 1819\n",
       "STATE                1819\n",
       "COUNTY               1819\n",
       "NUM_COUNTIES         1819\n",
       "HO_RANK              1920\n",
       "HO_PROP_RANK         1920\n",
       "HO_QUARTILE          1920\n",
       "HF_RANK              1920\n",
       "HF_PROP_RANK         1920\n",
       "HF_QUARTILE          1920\n",
       "StateCode            1819\n",
       "CountyFIPS           1819\n",
       "dtype: int64"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "desynpuf.drop([\"Unnamed: 0\", \"Unnamed: 0.1\"], axis=1, inplace=True, errors=\"ignore\")\n",
    "desynpuf.isnull().sum() "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "desynpuf[\"LENGTH_OF_STAY\"].fillna(0, inplace=True)\n",
    "desynpuf.drop([\"FIPS\", \"NUM_COUNTIES\", \"StateCode\", \"CountyFIPS\"], axis=1, inplace=True, errors=\"ignore\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>DESYNPUF_ID</th>\n",
       "      <th>FIPS_COUNTY_CODE</th>\n",
       "      <th>FULL_FIPS_CODE</th>\n",
       "      <th>CBSA</th>\n",
       "      <th>CBSA_NAME</th>\n",
       "      <th>FIPS_STATE_CODE</th>\n",
       "      <th>CLM_ID</th>\n",
       "      <th>BENE_AGE</th>\n",
       "      <th>AGE_GROUP</th>\n",
       "      <th>B_SEX</th>\n",
       "      <th>...</th>\n",
       "      <th>ANY_COSTS</th>\n",
       "      <th>POC</th>\n",
       "      <th>STATE</th>\n",
       "      <th>COUNTY</th>\n",
       "      <th>HO_RANK</th>\n",
       "      <th>HO_PROP_RANK</th>\n",
       "      <th>HO_QUARTILE</th>\n",
       "      <th>HF_RANK</th>\n",
       "      <th>HF_PROP_RANK</th>\n",
       "      <th>HF_QUARTILE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>00187098A769FC5E</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>55.0</td>\n",
       "      <td>45-64</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>77</th>\n",
       "      <td>002B54E82E2DD278</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>63.0</td>\n",
       "      <td>45-64</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113</th>\n",
       "      <td>003F7869F3D9735E</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>61.0</td>\n",
       "      <td>45-64</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>176</th>\n",
       "      <td>005F4826CFA35337</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>53.0</td>\n",
       "      <td>45-64</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>196</th>\n",
       "      <td>006D06D8AD310405</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>61.0</td>\n",
       "      <td>45-64</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</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",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 46 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          DESYNPUF_ID  FIPS_COUNTY_CODE  FULL_FIPS_CODE  CBSA CBSA_NAME  \\\n",
       "43   00187098A769FC5E                 0               0     0       NaN   \n",
       "77   002B54E82E2DD278                 0               0     0       NaN   \n",
       "113  003F7869F3D9735E                 0               0     0       NaN   \n",
       "176  005F4826CFA35337                 0               0     0       NaN   \n",
       "196  006D06D8AD310405                 0               0     0       NaN   \n",
       "\n",
       "     FIPS_STATE_CODE  CLM_ID  BENE_AGE AGE_GROUP  B_SEX  ...  ANY_COSTS  POC  \\\n",
       "43                 0     NaN      55.0     45-64      0  ...        0.0  1.0   \n",
       "77                 0     NaN      63.0     45-64      0  ...        0.0  0.0   \n",
       "113                0     NaN      61.0     45-64      0  ...        0.0  0.0   \n",
       "176                0     NaN      53.0     45-64      1  ...        0.0  0.0   \n",
       "196                0     NaN      61.0     45-64      0  ...        0.0  0.0   \n",
       "\n",
       "     STATE  COUNTY  HO_RANK  HO_PROP_RANK  HO_QUARTILE  HF_RANK  HF_PROP_RANK  \\\n",
       "43     NaN     NaN      NaN           NaN          NaN      NaN           NaN   \n",
       "77     NaN     NaN      NaN           NaN          NaN      NaN           NaN   \n",
       "113    NaN     NaN      NaN           NaN          NaN      NaN           NaN   \n",
       "176    NaN     NaN      NaN           NaN          NaN      NaN           NaN   \n",
       "196    NaN     NaN      NaN           NaN          NaN      NaN           NaN   \n",
       "\n",
       "     HF_QUARTILE  \n",
       "43           NaN  \n",
       "77           NaN  \n",
       "113          NaN  \n",
       "176          NaN  \n",
       "196          NaN  \n",
       "\n",
       "[5 rows x 46 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# These are the foreign beneficiaries with no FIPS codes\n",
    "# They will also have not Health Outcomes or Proportional Ranks\n",
    "\n",
    "null_states = desynpuf[desynpuf[\"STATE\"].isnull()]\n",
    "null_states.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(120623, 46)"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "desynpuf.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.0    118896\n",
       "1.0      1727\n",
       "Name: B_DIED, dtype: int64"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "desynpuf[\"B_DIED\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.0    0.985683\n",
       "1.0    0.014317\n",
       "Name: B_DIED, dtype: float64"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "desynpuf[\"B_DIED\"].value_counts(normalize=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "s = desynpuf[[\"MEAN_ELIX_SCORE\", \"LENGTH_OF_STAY\", \"CODES_COUNT\", \"PHYS_COUNT\", \"ALLCOSTS\", \"TOTCHRONIC\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>std</th>\n",
       "      <th>min</th>\n",
       "      <th>25%</th>\n",
       "      <th>50%</th>\n",
       "      <th>75%</th>\n",
       "      <th>max</th>\n",
       "      <th>X6</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>MEAN_ELIX_SCORE</th>\n",
       "      <td>120623.0</td>\n",
       "      <td>1.434454</td>\n",
       "      <td>2.300053</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.285714</td>\n",
       "      <td>34.0</td>\n",
       "      <td>13.800320</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>LENGTH_OF_STAY</th>\n",
       "      <td>120623.0</td>\n",
       "      <td>1.159356</td>\n",
       "      <td>3.659783</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>140.0</td>\n",
       "      <td>21.958701</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CODES_COUNT</th>\n",
       "      <td>120623.0</td>\n",
       "      <td>1.971117</td>\n",
       "      <td>4.053850</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>16.0</td>\n",
       "      <td>24.323098</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PHYS_COUNT</th>\n",
       "      <td>120623.0</td>\n",
       "      <td>0.411613</td>\n",
       "      <td>0.808224</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4.849343</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ALLCOSTS</th>\n",
       "      <td>120623.0</td>\n",
       "      <td>4040.375849</td>\n",
       "      <td>11649.869488</td>\n",
       "      <td>-2932.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>196336.0</td>\n",
       "      <td>69899.216927</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TOTCHRONIC</th>\n",
       "      <td>120623.0</td>\n",
       "      <td>2.798521</td>\n",
       "      <td>2.654667</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>11.0</td>\n",
       "      <td>15.928004</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    count         mean           std     min  25%  50%  \\\n",
       "MEAN_ELIX_SCORE  120623.0     1.434454      2.300053    -1.0  0.0  0.0   \n",
       "LENGTH_OF_STAY   120623.0     1.159356      3.659783     0.0  0.0  0.0   \n",
       "CODES_COUNT      120623.0     1.971117      4.053850     0.0  0.0  0.0   \n",
       "PHYS_COUNT       120623.0     0.411613      0.808224     0.0  0.0  0.0   \n",
       "ALLCOSTS         120623.0  4040.375849  11649.869488 -2932.0  0.0  0.0   \n",
       "TOTCHRONIC       120623.0     2.798521      2.654667     0.0  0.0  2.0   \n",
       "\n",
       "                      75%       max            X6  \n",
       "MEAN_ELIX_SCORE  2.285714      34.0     13.800320  \n",
       "LENGTH_OF_STAY   0.000000     140.0     21.958701  \n",
       "CODES_COUNT      0.000000      16.0     24.323098  \n",
       "PHYS_COUNT       0.000000       2.0      4.849343  \n",
       "ALLCOSTS         0.000000  196336.0  69899.216927  \n",
       "TOTCHRONIC       5.000000      11.0     15.928004  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sb = s.describe().T\n",
    "sb[\"X6\"] = sb[\"std\"]*6\n",
    "sb"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Outlier Removal\n",
    "\n",
    "Remove: \n",
    "\n",
    "Rows where MEAN_ELIX_SCORE > 13\n",
    "\n",
    "Rows where LENGTH_OF_STAY > 22\n",
    "\n",
    "Rows where CODES_COUNT > 24\n",
    "\n",
    "Rows where PHYS_COUNT > 5\n",
    "\n",
    "Rows where ALLCOSTS > 100000\n",
    "\n",
    "Rows where TOTCHRONIC > 11"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(120623, 46)"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "desynpuf.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(120203, 46)"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "desynpuf = desynpuf[desynpuf[\"MEAN_ELIX_SCORE\"]<14]\n",
    "desynpuf.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(119655, 46)"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "desynpuf = desynpuf[desynpuf[\"LENGTH_OF_STAY\"]<23]\n",
    "desynpuf.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(119655, 46)"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "desynpuf = desynpuf[desynpuf[\"CODES_COUNT\"]<25]\n",
    "desynpuf.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(119655, 46)"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "desynpuf = desynpuf[desynpuf[\"PHYS_COUNT\"]<6]\n",
    "desynpuf.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(119508, 46)"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "desynpuf = desynpuf[desynpuf[\"ALLCOSTS\"]<100001]\n",
    "desynpuf.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(119508, 46)"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "desynpuf = desynpuf[desynpuf[\"TOTCHRONIC\"]<12]\n",
    "desynpuf.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Total Rows Removed: 1115"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "desynpuf.to_csv(\"/home/kylier/python/DS/data/PUF/PUF_RWJF_ALLIP2009_NO.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}