SPARCS NY Mortality 2014.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this notebook we will calculate the rate of in-hospital mortality across all facilities in New York State. Then we will look at a single hospital's mortality data by [APR DRG](http://solutions.3m.com/wps/portal/3M/en_US/Health-Information-Systems/HIS/Products-and-Services/Products-List-A-Z/APR-DRG-Software/). APR DRGs are a form of [diagnosis related group](https://en.wikipedia.org/wiki/Diagnosis-related_group) which are often tied to payment. They take into account both the diagnoses and procedures performed during an inpatient stay."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We leverage the server side to do the heavy lifting in computing the in-hospital mortality rates across the state."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "base_url = \"https://health.data.ny.gov/resource/rmwa-zns4\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2365208</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     count\n",
       "0  2365208"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_json(base_url + \"?\" + \"$select=count(*)\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "https://health.data.ny.gov/resource/rmwa-zns4?$select=count(*),patient_disposition&$group=patient_disposition\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>patient_disposition</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>152</td>\n",
       "      <td>Medicaid Cert Nursing Facility</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>121</td>\n",
       "      <td>Critical Access Hospital</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3789</td>\n",
       "      <td>Medicare Cert Long Term Care Hospital</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2504</td>\n",
       "      <td>Cancer Center or Children's Hospital</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1544</td>\n",
       "      <td>Hosp Basd Medicare Approved Swing Bed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>725</td>\n",
       "      <td>Federal Health Care Facility</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>8441</td>\n",
       "      <td>Another Type Not Listed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>3731</td>\n",
       "      <td>Court/Law Enforcement</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>10540</td>\n",
       "      <td>Hospice - Home</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>6268</td>\n",
       "      <td>Facility w/ Custodial/Supportive Care</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>12565</td>\n",
       "      <td>Psychiatric Hospital or Unit of Hosp</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>41524</td>\n",
       "      <td>Inpatient Rehabilitation Facility</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>12513</td>\n",
       "      <td>Hospice - Medical Facility</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>50913</td>\n",
       "      <td>Expired</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>317027</td>\n",
       "      <td>Home w/ Home Health Services</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>40817</td>\n",
       "      <td>Short-term Hospital</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>1581401</td>\n",
       "      <td>Home or Self Care</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>47012</td>\n",
       "      <td>Left Against Medical Advice</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>223621</td>\n",
       "      <td>Skilled Nursing Home</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      count                    patient_disposition\n",
       "0       152         Medicaid Cert Nursing Facility\n",
       "1       121               Critical Access Hospital\n",
       "2      3789  Medicare Cert Long Term Care Hospital\n",
       "3      2504   Cancer Center or Children's Hospital\n",
       "4      1544  Hosp Basd Medicare Approved Swing Bed\n",
       "5       725           Federal Health Care Facility\n",
       "6      8441                Another Type Not Listed\n",
       "7      3731                  Court/Law Enforcement\n",
       "8     10540                         Hospice - Home\n",
       "9      6268  Facility w/ Custodial/Supportive Care\n",
       "10    12565   Psychiatric Hospital or Unit of Hosp\n",
       "11    41524      Inpatient Rehabilitation Facility\n",
       "12    12513             Hospice - Medical Facility\n",
       "13    50913                                Expired\n",
       "14   317027           Home w/ Home Health Services\n",
       "15    40817                    Short-term Hospital\n",
       "16  1581401                      Home or Self Care\n",
       "17    47012            Left Against Medical Advice\n",
       "18   223621                   Skilled Nursing Home"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "url_count_by_disposition = base_url + \"?\" + \"$select=count(*),patient_disposition&$group=patient_disposition\"\n",
    "print(url_count_by_disposition)\n",
    "pd.read_json(url_count_by_disposition)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "https://health.data.ny.gov/resource/rmwa-zns4?$select=count(*),facility_name&$group=facility_name\n"
     ]
    }
   ],
   "source": [
    "url_facility_discharge_count = base_url + \"?$select=count(*),facility_name&$group=facility_name\"\n",
    "print(url_facility_discharge_count)\n",
    "facility_discharge_count_df = pd.read_json(url_facility_discharge_count)\n",
    "facility_discharge_count_df.columns = [\"discharge_count\", \"facility_name\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>discharge_count</th>\n",
       "      <th>facility_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>86</td>\n",
       "      <td>Cuba Memorial Hospital Inc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1966</td>\n",
       "      <td>Memorial Hosp of Wm F &amp; Gertrude F Jones A/K/A...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5262</td>\n",
       "      <td>Olean General Hospital</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2857</td>\n",
       "      <td>Brooks Memorial Hospital</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1227</td>\n",
       "      <td>Womans Christian Assoc Hospital - WCA Hosp at ...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   discharge_count                                      facility_name\n",
       "0               86                         Cuba Memorial Hospital Inc\n",
       "1             1966  Memorial Hosp of Wm F & Gertrude F Jones A/K/A...\n",
       "2             5262                             Olean General Hospital\n",
       "3             2857                           Brooks Memorial Hospital\n",
       "4             1227  Womans Christian Assoc Hospital - WCA Hosp at ..."
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "facility_discharge_count_df.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We pull separately the mortality data from SPARCS and then merge / join the data to the discharge count."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "https://health.data.ny.gov/resource/rmwa-zns4?$select=count(*),facility_name&$group=facility_name&patient_disposition=Expired\n"
     ]
    }
   ],
   "source": [
    "url_facility_expired_count = base_url + \"?$select=count(*),facility_name&$group=facility_name&patient_disposition=Expired\"\n",
    "print(url_facility_expired_count)\n",
    "facility_expired_count_df = pd.read_json(url_facility_expired_count)\n",
    "facility_expired_count_df.columns = [\"expired_count\", \"facility_name\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>expired_count</th>\n",
       "      <th>facility_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>52</td>\n",
       "      <td>Memorial Hosp of Wm F &amp; Gertrude F Jones A/K/A...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>171</td>\n",
       "      <td>Olean General Hospital</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>73</td>\n",
       "      <td>Brooks Memorial Hospital</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>157</td>\n",
       "      <td>Woman's Christian Association</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>Westfield Memorial Hospital Inc</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   expired_count                                      facility_name\n",
       "0             52  Memorial Hosp of Wm F & Gertrude F Jones A/K/A...\n",
       "1            171                             Olean General Hospital\n",
       "2             73                           Brooks Memorial Hospital\n",
       "3            157                      Woman's Christian Association\n",
       "4              1                    Westfield Memorial Hospital Inc"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "facility_expired_count_df.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "facility_discharged_with_expired_df = pd.merge(facility_discharge_count_df, facility_expired_count_df, on=\"facility_name\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "facility_discharged_with_expired_df[\"expired_rate_per_discharge\"] = facility_discharged_with_expired_df[\"expired_count\"] / facility_discharged_with_expired_df[\"discharge_count\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Mortality rates will vary by type of services that a hospital performs. For example, a hospital with a large number of live births will have a lower mortality rate. This should be considered a first look of the data and more significant conclusions can be drawn when looking at the APR DRG level."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>discharge_count</th>\n",
       "      <th>facility_name</th>\n",
       "      <th>expired_count</th>\n",
       "      <th>expired_rate_per_discharge</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>125</th>\n",
       "      <td>2900</td>\n",
       "      <td>Calvary Hospital Inc</td>\n",
       "      <td>2391</td>\n",
       "      <td>0.824483</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>54</td>\n",
       "      <td>Monroe Community Hospital</td>\n",
       "      <td>7</td>\n",
       "      <td>0.129630</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>153</th>\n",
       "      <td>252</td>\n",
       "      <td>Henry J. Carter Specialty Hospital</td>\n",
       "      <td>17</td>\n",
       "      <td>0.067460</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73</th>\n",
       "      <td>189</td>\n",
       "      <td>O'Connor Hospital</td>\n",
       "      <td>12</td>\n",
       "      <td>0.063492</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>18</td>\n",
       "      <td>Westfield Memorial Hospital Inc</td>\n",
       "      <td>1</td>\n",
       "      <td>0.055556</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>759</td>\n",
       "      <td>Soldiers and Sailors Memorial Hospital of Yate...</td>\n",
       "      <td>42</td>\n",
       "      <td>0.055336</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143</th>\n",
       "      <td>10125</td>\n",
       "      <td>Mount Sinai Beth Israel Brooklyn</td>\n",
       "      <td>483</td>\n",
       "      <td>0.047704</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>556</td>\n",
       "      <td>Ira Davenport Memorial Hospital Inc</td>\n",
       "      <td>26</td>\n",
       "      <td>0.046763</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>184</th>\n",
       "      <td>6013</td>\n",
       "      <td>St. Joseph Hospital</td>\n",
       "      <td>275</td>\n",
       "      <td>0.045734</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>167</th>\n",
       "      <td>8884</td>\n",
       "      <td>St Johns Episcopal Hospital So Shore</td>\n",
       "      <td>403</td>\n",
       "      <td>0.045362</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>3642</td>\n",
       "      <td>Geneva General Hospital</td>\n",
       "      <td>165</td>\n",
       "      <td>0.045305</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>87</th>\n",
       "      <td>14150</td>\n",
       "      <td>Ellis Hospital</td>\n",
       "      <td>632</td>\n",
       "      <td>0.044664</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>1734</td>\n",
       "      <td>Degraff Memorial Hospital</td>\n",
       "      <td>70</td>\n",
       "      <td>0.040369</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>3957</td>\n",
       "      <td>Sisters of Charity Hospital - St Joseph Campus</td>\n",
       "      <td>159</td>\n",
       "      <td>0.040182</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>7713</td>\n",
       "      <td>Kenmore Mercy Hospital</td>\n",
       "      <td>303</td>\n",
       "      <td>0.039284</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>21186</td>\n",
       "      <td>Mercy Hospital of Buffalo</td>\n",
       "      <td>828</td>\n",
       "      <td>0.039082</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59</th>\n",
       "      <td>19705</td>\n",
       "      <td>University Hospital SUNY Health Science Center</td>\n",
       "      <td>745</td>\n",
       "      <td>0.037808</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>21928</td>\n",
       "      <td>Buffalo General Hospital</td>\n",
       "      <td>825</td>\n",
       "      <td>0.037623</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>187</th>\n",
       "      <td>13760</td>\n",
       "      <td>Brookhaven Memorial Hospital Medical Center Inc</td>\n",
       "      <td>515</td>\n",
       "      <td>0.037427</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>107</th>\n",
       "      <td>7795</td>\n",
       "      <td>HealthAlliance Hospital Broadway Campus</td>\n",
       "      <td>283</td>\n",
       "      <td>0.036305</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>2237</td>\n",
       "      <td>Clifton Springs Hospital and Clinic</td>\n",
       "      <td>81</td>\n",
       "      <td>0.036209</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>168</th>\n",
       "      <td>31218</td>\n",
       "      <td>New York Hospital Medical Center of Queens</td>\n",
       "      <td>1046</td>\n",
       "      <td>0.033506</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94</th>\n",
       "      <td>20302</td>\n",
       "      <td>Vassar Brothers Medical Center</td>\n",
       "      <td>676</td>\n",
       "      <td>0.033297</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76</th>\n",
       "      <td>271</td>\n",
       "      <td>The University of Vermont Health Network - Eli...</td>\n",
       "      <td>9</td>\n",
       "      <td>0.033210</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>130</th>\n",
       "      <td>15815</td>\n",
       "      <td>Brookdale Hospital Medical Center</td>\n",
       "      <td>522</td>\n",
       "      <td>0.033007</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     discharge_count                                      facility_name  \\\n",
       "125             2900                               Calvary Hospital Inc   \n",
       "30                54                          Monroe Community Hospital   \n",
       "153              252                 Henry J. Carter Specialty Hospital   \n",
       "73               189                                  O'Connor Hospital   \n",
       "4                 18                    Westfield Memorial Hospital Inc   \n",
       "40               759  Soldiers and Sailors Memorial Hospital of Yate...   \n",
       "143            10125                   Mount Sinai Beth Israel Brooklyn   \n",
       "38               556                Ira Davenport Memorial Hospital Inc   \n",
       "184             6013                                St. Joseph Hospital   \n",
       "167             8884               St Johns Episcopal Hospital So Shore   \n",
       "32              3642                            Geneva General Hospital   \n",
       "87             14150                                     Ellis Hospital   \n",
       "18              1734                          Degraff Memorial Hospital   \n",
       "14              3957     Sisters of Charity Hospital - St Joseph Campus   \n",
       "12              7713                             Kenmore Mercy Hospital   \n",
       "9              21186                          Mercy Hospital of Buffalo   \n",
       "59             19705     University Hospital SUNY Health Science Center   \n",
       "6              21928                           Buffalo General Hospital   \n",
       "187            13760    Brookhaven Memorial Hospital Medical Center Inc   \n",
       "107             7795            HealthAlliance Hospital Broadway Campus   \n",
       "33              2237                Clifton Springs Hospital and Clinic   \n",
       "168            31218         New York Hospital Medical Center of Queens   \n",
       "94             20302                     Vassar Brothers Medical Center   \n",
       "76               271  The University of Vermont Health Network - Eli...   \n",
       "130            15815                  Brookdale Hospital Medical Center   \n",
       "\n",
       "     expired_count  expired_rate_per_discharge  \n",
       "125           2391                    0.824483  \n",
       "30               7                    0.129630  \n",
       "153             17                    0.067460  \n",
       "73              12                    0.063492  \n",
       "4                1                    0.055556  \n",
       "40              42                    0.055336  \n",
       "143            483                    0.047704  \n",
       "38              26                    0.046763  \n",
       "184            275                    0.045734  \n",
       "167            403                    0.045362  \n",
       "32             165                    0.045305  \n",
       "87             632                    0.044664  \n",
       "18              70                    0.040369  \n",
       "14             159                    0.040182  \n",
       "12             303                    0.039284  \n",
       "9              828                    0.039082  \n",
       "59             745                    0.037808  \n",
       "6              825                    0.037623  \n",
       "187            515                    0.037427  \n",
       "107            283                    0.036305  \n",
       "33              81                    0.036209  \n",
       "168           1046                    0.033506  \n",
       "94             676                    0.033297  \n",
       "76               9                    0.033210  \n",
       "130            522                    0.033007  "
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "facility_discharged_with_expired_df.sort_values(\"expired_rate_per_discharge\", ascending=False).head(25)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Anaconda2\\lib\\site-packages\\ipykernel\\__main__.py:1: UserWarning: Boolean Series key will be reindexed to match DataFrame index.\n",
      "  if __name__ == '__main__':\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>discharge_count</th>\n",
       "      <th>facility_name</th>\n",
       "      <th>expired_count</th>\n",
       "      <th>expired_rate_per_discharge</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>125</th>\n",
       "      <td>2900</td>\n",
       "      <td>Calvary Hospital Inc</td>\n",
       "      <td>2391</td>\n",
       "      <td>0.824483</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143</th>\n",
       "      <td>10125</td>\n",
       "      <td>Mount Sinai Beth Israel Brooklyn</td>\n",
       "      <td>483</td>\n",
       "      <td>0.047704</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>184</th>\n",
       "      <td>6013</td>\n",
       "      <td>St. Joseph Hospital</td>\n",
       "      <td>275</td>\n",
       "      <td>0.045734</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>167</th>\n",
       "      <td>8884</td>\n",
       "      <td>St Johns Episcopal Hospital So Shore</td>\n",
       "      <td>403</td>\n",
       "      <td>0.045362</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>3642</td>\n",
       "      <td>Geneva General Hospital</td>\n",
       "      <td>165</td>\n",
       "      <td>0.045305</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>87</th>\n",
       "      <td>14150</td>\n",
       "      <td>Ellis Hospital</td>\n",
       "      <td>632</td>\n",
       "      <td>0.044664</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>1734</td>\n",
       "      <td>Degraff Memorial Hospital</td>\n",
       "      <td>70</td>\n",
       "      <td>0.040369</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>3957</td>\n",
       "      <td>Sisters of Charity Hospital - St Joseph Campus</td>\n",
       "      <td>159</td>\n",
       "      <td>0.040182</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>7713</td>\n",
       "      <td>Kenmore Mercy Hospital</td>\n",
       "      <td>303</td>\n",
       "      <td>0.039284</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>21186</td>\n",
       "      <td>Mercy Hospital of Buffalo</td>\n",
       "      <td>828</td>\n",
       "      <td>0.039082</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59</th>\n",
       "      <td>19705</td>\n",
       "      <td>University Hospital SUNY Health Science Center</td>\n",
       "      <td>745</td>\n",
       "      <td>0.037808</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>21928</td>\n",
       "      <td>Buffalo General Hospital</td>\n",
       "      <td>825</td>\n",
       "      <td>0.037623</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>187</th>\n",
       "      <td>13760</td>\n",
       "      <td>Brookhaven Memorial Hospital Medical Center Inc</td>\n",
       "      <td>515</td>\n",
       "      <td>0.037427</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>107</th>\n",
       "      <td>7795</td>\n",
       "      <td>HealthAlliance Hospital Broadway Campus</td>\n",
       "      <td>283</td>\n",
       "      <td>0.036305</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>2237</td>\n",
       "      <td>Clifton Springs Hospital and Clinic</td>\n",
       "      <td>81</td>\n",
       "      <td>0.036209</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>168</th>\n",
       "      <td>31218</td>\n",
       "      <td>New York Hospital Medical Center of Queens</td>\n",
       "      <td>1046</td>\n",
       "      <td>0.033506</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94</th>\n",
       "      <td>20302</td>\n",
       "      <td>Vassar Brothers Medical Center</td>\n",
       "      <td>676</td>\n",
       "      <td>0.033297</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>130</th>\n",
       "      <td>15815</td>\n",
       "      <td>Brookdale Hospital Medical Center</td>\n",
       "      <td>522</td>\n",
       "      <td>0.033007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5262</td>\n",
       "      <td>Olean General Hospital</td>\n",
       "      <td>171</td>\n",
       "      <td>0.032497</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>4466</td>\n",
       "      <td>Roswell Park Cancer Institute</td>\n",
       "      <td>145</td>\n",
       "      <td>0.032468</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>4984</td>\n",
       "      <td>Auburn Memorial Hospital</td>\n",
       "      <td>160</td>\n",
       "      <td>0.032103</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>185</th>\n",
       "      <td>8625</td>\n",
       "      <td>Plainview Hospital</td>\n",
       "      <td>275</td>\n",
       "      <td>0.031884</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83</th>\n",
       "      <td>9748</td>\n",
       "      <td>Mary Imogene Bassett Hospital</td>\n",
       "      <td>304</td>\n",
       "      <td>0.031186</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>133</th>\n",
       "      <td>15436</td>\n",
       "      <td>Coney Island Hospital</td>\n",
       "      <td>481</td>\n",
       "      <td>0.031161</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>112</th>\n",
       "      <td>7531</td>\n",
       "      <td>Montefiore New Rochelle Hospital</td>\n",
       "      <td>230</td>\n",
       "      <td>0.030540</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61</th>\n",
       "      <td>4230</td>\n",
       "      <td>Oswego Hospital</td>\n",
       "      <td>128</td>\n",
       "      <td>0.030260</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>132</th>\n",
       "      <td>6982</td>\n",
       "      <td>New York Community Hospital of Brooklyn, Inc</td>\n",
       "      <td>210</td>\n",
       "      <td>0.030077</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100</th>\n",
       "      <td>6650</td>\n",
       "      <td>Putnam Hospital Center</td>\n",
       "      <td>198</td>\n",
       "      <td>0.029774</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>152</th>\n",
       "      <td>22051</td>\n",
       "      <td>Memorial Hospital for Cancer and Allied Diseases</td>\n",
       "      <td>650</td>\n",
       "      <td>0.029477</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>140</th>\n",
       "      <td>8440</td>\n",
       "      <td>Kingsbrook Jewish Medical Center</td>\n",
       "      <td>247</td>\n",
       "      <td>0.029265</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>18918</td>\n",
       "      <td>Highland Hospital</td>\n",
       "      <td>256</td>\n",
       "      <td>0.013532</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>148</th>\n",
       "      <td>11837</td>\n",
       "      <td>Harlem Hospital Center</td>\n",
       "      <td>157</td>\n",
       "      <td>0.013263</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>121</th>\n",
       "      <td>20327</td>\n",
       "      <td>Jacobi Medical Center</td>\n",
       "      <td>256</td>\n",
       "      <td>0.012594</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>115</th>\n",
       "      <td>9852</td>\n",
       "      <td>Northern Westchester Hospital</td>\n",
       "      <td>124</td>\n",
       "      <td>0.012586</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>4370</td>\n",
       "      <td>United Health Services Hospitals Inc. - Bingha...</td>\n",
       "      <td>55</td>\n",
       "      <td>0.012586</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84</th>\n",
       "      <td>3985</td>\n",
       "      <td>St. Mary's Hospital</td>\n",
       "      <td>50</td>\n",
       "      <td>0.012547</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70</th>\n",
       "      <td>24662</td>\n",
       "      <td>St Peters Hospital</td>\n",
       "      <td>301</td>\n",
       "      <td>0.012205</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>5315</td>\n",
       "      <td>Newark-Wayne Community Hospital</td>\n",
       "      <td>63</td>\n",
       "      <td>0.011853</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>122</th>\n",
       "      <td>16929</td>\n",
       "      <td>Montefiore Medical Center-Wakefield Hospital</td>\n",
       "      <td>200</td>\n",
       "      <td>0.011814</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>124</th>\n",
       "      <td>24229</td>\n",
       "      <td>Lincoln Medical &amp; Mental Health Center</td>\n",
       "      <td>285</td>\n",
       "      <td>0.011763</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126</th>\n",
       "      <td>16585</td>\n",
       "      <td>SBH Health System</td>\n",
       "      <td>194</td>\n",
       "      <td>0.011697</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>151</th>\n",
       "      <td>31778</td>\n",
       "      <td>Lenox Hill Hospital</td>\n",
       "      <td>348</td>\n",
       "      <td>0.010951</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>1518</td>\n",
       "      <td>Community Memorial Hospital Inc</td>\n",
       "      <td>15</td>\n",
       "      <td>0.009881</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>146</th>\n",
       "      <td>27532</td>\n",
       "      <td>Bellevue Hospital Center</td>\n",
       "      <td>266</td>\n",
       "      <td>0.009661</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>1023</td>\n",
       "      <td>Carthage Area Hospital Inc</td>\n",
       "      <td>9</td>\n",
       "      <td>0.008798</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>191</th>\n",
       "      <td>10208</td>\n",
       "      <td>St Charles Hospital</td>\n",
       "      <td>83</td>\n",
       "      <td>0.008131</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>157</th>\n",
       "      <td>31795</td>\n",
       "      <td>NYU Hospitals Center</td>\n",
       "      <td>252</td>\n",
       "      <td>0.007926</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62</th>\n",
       "      <td>2943</td>\n",
       "      <td>Claxton-Hepburn Medical Center</td>\n",
       "      <td>23</td>\n",
       "      <td>0.007815</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>1641</td>\n",
       "      <td>Lewis County General Hospital</td>\n",
       "      <td>12</td>\n",
       "      <td>0.007313</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159</th>\n",
       "      <td>26534</td>\n",
       "      <td>Mount Sinai Roosevelt</td>\n",
       "      <td>168</td>\n",
       "      <td>0.006331</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>128</th>\n",
       "      <td>5432</td>\n",
       "      <td>North Central Bronx Hospital</td>\n",
       "      <td>29</td>\n",
       "      <td>0.005339</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>154</th>\n",
       "      <td>11613</td>\n",
       "      <td>Metropolitan Hospital Center</td>\n",
       "      <td>55</td>\n",
       "      <td>0.004736</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>12742</td>\n",
       "      <td>Women And Children's Hospital Of Buffalo</td>\n",
       "      <td>60</td>\n",
       "      <td>0.004709</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>174</th>\n",
       "      <td>17042</td>\n",
       "      <td>Long Island Jewish Schneiders Children's Hospi...</td>\n",
       "      <td>75</td>\n",
       "      <td>0.004401</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>2310</td>\n",
       "      <td>Sunnyview Hospital and Rehabilitation Center</td>\n",
       "      <td>8</td>\n",
       "      <td>0.003463</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>92</th>\n",
       "      <td>2312</td>\n",
       "      <td>The Burdett Care Center</td>\n",
       "      <td>2</td>\n",
       "      <td>0.000865</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>196</th>\n",
       "      <td>3475</td>\n",
       "      <td>Abortion Record - Facility Name Redacted</td>\n",
       "      <td>2</td>\n",
       "      <td>0.000576</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89</th>\n",
       "      <td>5503</td>\n",
       "      <td>Ellis Hospital - Bellevue Woman's Care Center ...</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000182</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149</th>\n",
       "      <td>6129</td>\n",
       "      <td>NYU Hospital for Joint Diseases</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000163</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>150</th>\n",
       "      <td>14532</td>\n",
       "      <td>Hospital for Special Surgery</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000069</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>174 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     discharge_count                                      facility_name  \\\n",
       "125             2900                               Calvary Hospital Inc   \n",
       "143            10125                   Mount Sinai Beth Israel Brooklyn   \n",
       "184             6013                                St. Joseph Hospital   \n",
       "167             8884               St Johns Episcopal Hospital So Shore   \n",
       "32              3642                            Geneva General Hospital   \n",
       "87             14150                                     Ellis Hospital   \n",
       "18              1734                          Degraff Memorial Hospital   \n",
       "14              3957     Sisters of Charity Hospital - St Joseph Campus   \n",
       "12              7713                             Kenmore Mercy Hospital   \n",
       "9              21186                          Mercy Hospital of Buffalo   \n",
       "59             19705     University Hospital SUNY Health Science Center   \n",
       "6              21928                           Buffalo General Hospital   \n",
       "187            13760    Brookhaven Memorial Hospital Medical Center Inc   \n",
       "107             7795            HealthAlliance Hospital Broadway Campus   \n",
       "33              2237                Clifton Springs Hospital and Clinic   \n",
       "168            31218         New York Hospital Medical Center of Queens   \n",
       "94             20302                     Vassar Brothers Medical Center   \n",
       "130            15815                  Brookdale Hospital Medical Center   \n",
       "1               5262                             Olean General Hospital   \n",
       "10              4466                      Roswell Park Cancer Institute   \n",
       "44              4984                           Auburn Memorial Hospital   \n",
       "185             8625                                 Plainview Hospital   \n",
       "83              9748                      Mary Imogene Bassett Hospital   \n",
       "133            15436                              Coney Island Hospital   \n",
       "112             7531                   Montefiore New Rochelle Hospital   \n",
       "61              4230                                    Oswego Hospital   \n",
       "132             6982       New York Community Hospital of Brooklyn, Inc   \n",
       "100             6650                             Putnam Hospital Center   \n",
       "152            22051   Memorial Hospital for Cancer and Allied Diseases   \n",
       "140             8440                   Kingsbrook Jewish Medical Center   \n",
       "..               ...                                                ...   \n",
       "27             18918                                  Highland Hospital   \n",
       "148            11837                             Harlem Hospital Center   \n",
       "121            20327                              Jacobi Medical Center   \n",
       "115             9852                      Northern Westchester Hospital   \n",
       "41              4370  United Health Services Hospitals Inc. - Bingha...   \n",
       "84              3985                                St. Mary's Hospital   \n",
       "70             24662                                 St Peters Hospital   \n",
       "39              5315                    Newark-Wayne Community Hospital   \n",
       "122            16929       Montefiore Medical Center-Wakefield Hospital   \n",
       "124            24229             Lincoln Medical & Mental Health Center   \n",
       "126            16585                                  SBH Health System   \n",
       "151            31778                                Lenox Hill Hospital   \n",
       "53              1518                    Community Memorial Hospital Inc   \n",
       "146            27532                           Bellevue Hospital Center   \n",
       "50              1023                         Carthage Area Hospital Inc   \n",
       "191            10208                                St Charles Hospital   \n",
       "157            31795                               NYU Hospitals Center   \n",
       "62              2943                     Claxton-Hepburn Medical Center   \n",
       "51              1641                      Lewis County General Hospital   \n",
       "159            26534                              Mount Sinai Roosevelt   \n",
       "128             5432                       North Central Bronx Hospital   \n",
       "154            11613                       Metropolitan Hospital Center   \n",
       "7              12742           Women And Children's Hospital Of Buffalo   \n",
       "174            17042  Long Island Jewish Schneiders Children's Hospi...   \n",
       "88              2310       Sunnyview Hospital and Rehabilitation Center   \n",
       "92              2312                            The Burdett Care Center   \n",
       "196             3475           Abortion Record - Facility Name Redacted   \n",
       "89              5503  Ellis Hospital - Bellevue Woman's Care Center ...   \n",
       "149             6129                    NYU Hospital for Joint Diseases   \n",
       "150            14532                       Hospital for Special Surgery   \n",
       "\n",
       "     expired_count  expired_rate_per_discharge  \n",
       "125           2391                    0.824483  \n",
       "143            483                    0.047704  \n",
       "184            275                    0.045734  \n",
       "167            403                    0.045362  \n",
       "32             165                    0.045305  \n",
       "87             632                    0.044664  \n",
       "18              70                    0.040369  \n",
       "14             159                    0.040182  \n",
       "12             303                    0.039284  \n",
       "9              828                    0.039082  \n",
       "59             745                    0.037808  \n",
       "6              825                    0.037623  \n",
       "187            515                    0.037427  \n",
       "107            283                    0.036305  \n",
       "33              81                    0.036209  \n",
       "168           1046                    0.033506  \n",
       "94             676                    0.033297  \n",
       "130            522                    0.033007  \n",
       "1              171                    0.032497  \n",
       "10             145                    0.032468  \n",
       "44             160                    0.032103  \n",
       "185            275                    0.031884  \n",
       "83             304                    0.031186  \n",
       "133            481                    0.031161  \n",
       "112            230                    0.030540  \n",
       "61             128                    0.030260  \n",
       "132            210                    0.030077  \n",
       "100            198                    0.029774  \n",
       "152            650                    0.029477  \n",
       "140            247                    0.029265  \n",
       "..             ...                         ...  \n",
       "27             256                    0.013532  \n",
       "148            157                    0.013263  \n",
       "121            256                    0.012594  \n",
       "115            124                    0.012586  \n",
       "41              55                    0.012586  \n",
       "84              50                    0.012547  \n",
       "70             301                    0.012205  \n",
       "39              63                    0.011853  \n",
       "122            200                    0.011814  \n",
       "124            285                    0.011763  \n",
       "126            194                    0.011697  \n",
       "151            348                    0.010951  \n",
       "53              15                    0.009881  \n",
       "146            266                    0.009661  \n",
       "50               9                    0.008798  \n",
       "191             83                    0.008131  \n",
       "157            252                    0.007926  \n",
       "62              23                    0.007815  \n",
       "51              12                    0.007313  \n",
       "159            168                    0.006331  \n",
       "128             29                    0.005339  \n",
       "154             55                    0.004736  \n",
       "7               60                    0.004709  \n",
       "174             75                    0.004401  \n",
       "88               8                    0.003463  \n",
       "92               2                    0.000865  \n",
       "196              2                    0.000576  \n",
       "89               1                    0.000182  \n",
       "149              1                    0.000163  \n",
       "150              1                    0.000069  \n",
       "\n",
       "[174 rows x 4 columns]"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "facility_discharged_with_expired_df.sort_values(\"expired_rate_per_discharge\", ascending=False)[facility_discharged_with_expired_df[\"discharge_count\"] >= 1000]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "https://health.data.ny.gov/resource/rmwa-zns4?facility_name=Coney%20Island%20Hospital&$limit=50000\n"
     ]
    }
   ],
   "source": [
    "hospital_url = base_url + \"?facility_name=Coney%20Island%20Hospital&$limit=50000\"\n",
    "print(hospital_url)\n",
    "hospital_df = pd.read_json(hospital_url)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Uncomment the last line if there are API issues\n",
    "#hospital_df.to_csv(\"./data/sparcs_hospital_apr_drg_2014.csv\")\n",
    "#hospital_df = pd.read_csv(\"./data/sparcs_hospital_apr_drg_2014.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>abortion_edit_indicator</th>\n",
       "      <th>admit_day_of_week</th>\n",
       "      <th>age_group</th>\n",
       "      <th>apr_drg_code</th>\n",
       "      <th>apr_drg_description</th>\n",
       "      <th>apr_mdc_code</th>\n",
       "      <th>apr_mdc_description</th>\n",
       "      <th>apr_medical_surgical_description</th>\n",
       "      <th>apr_risk_of_mortality</th>\n",
       "      <th>apr_severity_of_illness_code</th>\n",
       "      <th>...</th>\n",
       "      <th>operating_provider_license_number</th>\n",
       "      <th>patient_disposition</th>\n",
       "      <th>race</th>\n",
       "      <th>source_of_payment_1</th>\n",
       "      <th>source_of_payment_2</th>\n",
       "      <th>source_of_payment_3</th>\n",
       "      <th>total_charges</th>\n",
       "      <th>total_costs</th>\n",
       "      <th>type_of_admission</th>\n",
       "      <th>zip_code_3_digits</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>N</td>\n",
       "      <td>WED</td>\n",
       "      <td>30 to 49</td>\n",
       "      <td>753</td>\n",
       "      <td>BIPOLAR DISORDERS</td>\n",
       "      <td>19</td>\n",
       "      <td>Mental Diseases and Disorders</td>\n",
       "      <td>Medical</td>\n",
       "      <td>Minor</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>234646.0</td>\n",
       "      <td>Home or Self Care</td>\n",
       "      <td>Other Race</td>\n",
       "      <td>Medicaid</td>\n",
       "      <td>Medicaid</td>\n",
       "      <td>NaN</td>\n",
       "      <td>23450.98</td>\n",
       "      <td>17820.05</td>\n",
       "      <td>Emergency</td>\n",
       "      <td>112</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>N</td>\n",
       "      <td>WED</td>\n",
       "      <td>50 to 69</td>\n",
       "      <td>750</td>\n",
       "      <td>SCHIZOPHRENIA</td>\n",
       "      <td>19</td>\n",
       "      <td>Mental Diseases and Disorders</td>\n",
       "      <td>Medical</td>\n",
       "      <td>Minor</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>218003.0</td>\n",
       "      <td>Home or Self Care</td>\n",
       "      <td>Other Race</td>\n",
       "      <td>Medicare</td>\n",
       "      <td>Medicaid</td>\n",
       "      <td>NaN</td>\n",
       "      <td>30716.00</td>\n",
       "      <td>23340.63</td>\n",
       "      <td>Emergency</td>\n",
       "      <td>112</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>N</td>\n",
       "      <td>WED</td>\n",
       "      <td>30 to 49</td>\n",
       "      <td>140</td>\n",
       "      <td>CHRONIC OBSTRUCTIVE PULMONARY DISEASE</td>\n",
       "      <td>4</td>\n",
       "      <td>Diseases and Disorders of the Respiratory System</td>\n",
       "      <td>Medical</td>\n",
       "      <td>Minor</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>235776.0</td>\n",
       "      <td>Home or Self Care</td>\n",
       "      <td>Black/African American</td>\n",
       "      <td>Medicare</td>\n",
       "      <td>Medicare</td>\n",
       "      <td>Medicaid</td>\n",
       "      <td>30174.63</td>\n",
       "      <td>22929.25</td>\n",
       "      <td>Emergency</td>\n",
       "      <td>112</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>N</td>\n",
       "      <td>WED</td>\n",
       "      <td>70 or Older</td>\n",
       "      <td>720</td>\n",
       "      <td>SEPTICEMIA &amp; DISSEMINATED INFECTIONS</td>\n",
       "      <td>18</td>\n",
       "      <td>Infectious and Parasitic Diseases, Systemic or...</td>\n",
       "      <td>Medical</td>\n",
       "      <td>Major</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>217963.0</td>\n",
       "      <td>Skilled Nursing Home</td>\n",
       "      <td>White</td>\n",
       "      <td>Medicare</td>\n",
       "      <td>Medicaid</td>\n",
       "      <td>NaN</td>\n",
       "      <td>30204.59</td>\n",
       "      <td>22952.01</td>\n",
       "      <td>Emergency</td>\n",
       "      <td>112</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>N</td>\n",
       "      <td>WED</td>\n",
       "      <td>70 or Older</td>\n",
       "      <td>140</td>\n",
       "      <td>CHRONIC OBSTRUCTIVE PULMONARY DISEASE</td>\n",
       "      <td>4</td>\n",
       "      <td>Diseases and Disorders of the Respiratory System</td>\n",
       "      <td>Medical</td>\n",
       "      <td>Major</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>221582.0</td>\n",
       "      <td>Home or Self Care</td>\n",
       "      <td>Black/African American</td>\n",
       "      <td>Medicare</td>\n",
       "      <td>Medicare</td>\n",
       "      <td>Medicaid</td>\n",
       "      <td>26651.11</td>\n",
       "      <td>20251.78</td>\n",
       "      <td>Emergency</td>\n",
       "      <td>112</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>N</td>\n",
       "      <td>WED</td>\n",
       "      <td>50 to 69</td>\n",
       "      <td>190</td>\n",
       "      <td>ACUTE MYOCARDIAL INFARCTION</td>\n",
       "      <td>5</td>\n",
       "      <td>Diseases and Disorders of the Circulatory System</td>\n",
       "      <td>Medical</td>\n",
       "      <td>Major</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>199382.0</td>\n",
       "      <td>Short-term Hospital</td>\n",
       "      <td>White</td>\n",
       "      <td>Medicare</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10611.60</td>\n",
       "      <td>8063.60</td>\n",
       "      <td>Emergency</td>\n",
       "      <td>112</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>N</td>\n",
       "      <td>WED</td>\n",
       "      <td>30 to 49</td>\n",
       "      <td>750</td>\n",
       "      <td>SCHIZOPHRENIA</td>\n",
       "      <td>19</td>\n",
       "      <td>Mental Diseases and Disorders</td>\n",
       "      <td>Medical</td>\n",
       "      <td>Minor</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>204304.0</td>\n",
       "      <td>Home or Self Care</td>\n",
       "      <td>Other Race</td>\n",
       "      <td>Medicaid</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20958.05</td>\n",
       "      <td>15925.71</td>\n",
       "      <td>Emergency</td>\n",
       "      <td>112</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>N</td>\n",
       "      <td>WED</td>\n",
       "      <td>70 or Older</td>\n",
       "      <td>463</td>\n",
       "      <td>KIDNEY &amp; URINARY TRACT INFECTIONS</td>\n",
       "      <td>11</td>\n",
       "      <td>Diseases and Disorders of the Kidney and Urina...</td>\n",
       "      <td>Medical</td>\n",
       "      <td>Moderate</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>241803.0</td>\n",
       "      <td>Home w/ Home Health Services</td>\n",
       "      <td>White</td>\n",
       "      <td>Medicare</td>\n",
       "      <td>Medicare</td>\n",
       "      <td>Medicaid</td>\n",
       "      <td>27150.89</td>\n",
       "      <td>20631.55</td>\n",
       "      <td>Emergency</td>\n",
       "      <td>112</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>N</td>\n",
       "      <td>WED</td>\n",
       "      <td>30 to 49</td>\n",
       "      <td>750</td>\n",
       "      <td>SCHIZOPHRENIA</td>\n",
       "      <td>19</td>\n",
       "      <td>Mental Diseases and Disorders</td>\n",
       "      <td>Medical</td>\n",
       "      <td>Minor</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>218003.0</td>\n",
       "      <td>Home or Self Care</td>\n",
       "      <td>White</td>\n",
       "      <td>Self-Pay</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15168.03</td>\n",
       "      <td>11525.96</td>\n",
       "      <td>Emergency</td>\n",
       "      <td>112</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>N</td>\n",
       "      <td>WED</td>\n",
       "      <td>70 or Older</td>\n",
       "      <td>720</td>\n",
       "      <td>SEPTICEMIA &amp; DISSEMINATED INFECTIONS</td>\n",
       "      <td>18</td>\n",
       "      <td>Infectious and Parasitic Diseases, Systemic or...</td>\n",
       "      <td>Medical</td>\n",
       "      <td>Extreme</td>\n",
       "      <td>4</td>\n",
       "      <td>...</td>\n",
       "      <td>235776.0</td>\n",
       "      <td>Skilled Nursing Home</td>\n",
       "      <td>White</td>\n",
       "      <td>Medicare</td>\n",
       "      <td>Medicare</td>\n",
       "      <td>Private Health Insurance</td>\n",
       "      <td>30454.78</td>\n",
       "      <td>23142.13</td>\n",
       "      <td>Emergency</td>\n",
       "      <td>112</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10 rows × 38 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  abortion_edit_indicator admit_day_of_week    age_group  apr_drg_code  \\\n",
       "0                       N               WED     30 to 49           753   \n",
       "1                       N               WED     50 to 69           750   \n",
       "2                       N               WED     30 to 49           140   \n",
       "3                       N               WED  70 or Older           720   \n",
       "4                       N               WED  70 or Older           140   \n",
       "5                       N               WED     50 to 69           190   \n",
       "6                       N               WED     30 to 49           750   \n",
       "7                       N               WED  70 or Older           463   \n",
       "8                       N               WED     30 to 49           750   \n",
       "9                       N               WED  70 or Older           720   \n",
       "\n",
       "                     apr_drg_description  apr_mdc_code  \\\n",
       "0                      BIPOLAR DISORDERS            19   \n",
       "1                          SCHIZOPHRENIA            19   \n",
       "2  CHRONIC OBSTRUCTIVE PULMONARY DISEASE             4   \n",
       "3   SEPTICEMIA & DISSEMINATED INFECTIONS            18   \n",
       "4  CHRONIC OBSTRUCTIVE PULMONARY DISEASE             4   \n",
       "5            ACUTE MYOCARDIAL INFARCTION             5   \n",
       "6                          SCHIZOPHRENIA            19   \n",
       "7      KIDNEY & URINARY TRACT INFECTIONS            11   \n",
       "8                          SCHIZOPHRENIA            19   \n",
       "9   SEPTICEMIA & DISSEMINATED INFECTIONS            18   \n",
       "\n",
       "                                 apr_mdc_description  \\\n",
       "0                      Mental Diseases and Disorders   \n",
       "1                      Mental Diseases and Disorders   \n",
       "2   Diseases and Disorders of the Respiratory System   \n",
       "3  Infectious and Parasitic Diseases, Systemic or...   \n",
       "4   Diseases and Disorders of the Respiratory System   \n",
       "5   Diseases and Disorders of the Circulatory System   \n",
       "6                      Mental Diseases and Disorders   \n",
       "7  Diseases and Disorders of the Kidney and Urina...   \n",
       "8                      Mental Diseases and Disorders   \n",
       "9  Infectious and Parasitic Diseases, Systemic or...   \n",
       "\n",
       "  apr_medical_surgical_description apr_risk_of_mortality  \\\n",
       "0                          Medical                 Minor   \n",
       "1                          Medical                 Minor   \n",
       "2                          Medical                 Minor   \n",
       "3                          Medical                 Major   \n",
       "4                          Medical                 Major   \n",
       "5                          Medical                 Major   \n",
       "6                          Medical                 Minor   \n",
       "7                          Medical              Moderate   \n",
       "8                          Medical                 Minor   \n",
       "9                          Medical               Extreme   \n",
       "\n",
       "   apr_severity_of_illness_code        ...         \\\n",
       "0                             2        ...          \n",
       "1                             1        ...          \n",
       "2                             2        ...          \n",
       "3                             3        ...          \n",
       "4                             3        ...          \n",
       "5                             3        ...          \n",
       "6                             1        ...          \n",
       "7                             2        ...          \n",
       "8                             2        ...          \n",
       "9                             4        ...          \n",
       "\n",
       "  operating_provider_license_number           patient_disposition  \\\n",
       "0                          234646.0             Home or Self Care   \n",
       "1                          218003.0             Home or Self Care   \n",
       "2                          235776.0             Home or Self Care   \n",
       "3                          217963.0          Skilled Nursing Home   \n",
       "4                          221582.0             Home or Self Care   \n",
       "5                          199382.0           Short-term Hospital   \n",
       "6                          204304.0             Home or Self Care   \n",
       "7                          241803.0  Home w/ Home Health Services   \n",
       "8                          218003.0             Home or Self Care   \n",
       "9                          235776.0          Skilled Nursing Home   \n",
       "\n",
       "                     race  source_of_payment_1 source_of_payment_2  \\\n",
       "0              Other Race             Medicaid            Medicaid   \n",
       "1              Other Race             Medicare            Medicaid   \n",
       "2  Black/African American             Medicare            Medicare   \n",
       "3                   White             Medicare            Medicaid   \n",
       "4  Black/African American             Medicare            Medicare   \n",
       "5                   White             Medicare                 NaN   \n",
       "6              Other Race             Medicaid                 NaN   \n",
       "7                   White             Medicare            Medicare   \n",
       "8                   White             Self-Pay                 NaN   \n",
       "9                   White             Medicare            Medicare   \n",
       "\n",
       "        source_of_payment_3 total_charges total_costs  type_of_admission  \\\n",
       "0                       NaN      23450.98    17820.05          Emergency   \n",
       "1                       NaN      30716.00    23340.63          Emergency   \n",
       "2                  Medicaid      30174.63    22929.25          Emergency   \n",
       "3                       NaN      30204.59    22952.01          Emergency   \n",
       "4                  Medicaid      26651.11    20251.78          Emergency   \n",
       "5                       NaN      10611.60     8063.60          Emergency   \n",
       "6                       NaN      20958.05    15925.71          Emergency   \n",
       "7                  Medicaid      27150.89    20631.55          Emergency   \n",
       "8                       NaN      15168.03    11525.96          Emergency   \n",
       "9  Private Health Insurance      30454.78    23142.13          Emergency   \n",
       "\n",
       "  zip_code_3_digits  \n",
       "0               112  \n",
       "1               112  \n",
       "2               112  \n",
       "3               112  \n",
       "4               112  \n",
       "5               112  \n",
       "6               112  \n",
       "7               112  \n",
       "8               112  \n",
       "9               112  \n",
       "\n",
       "[10 rows x 38 columns]"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hospital_df.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We create a new column in the dataframe which contains the DRG and the description."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "hospital_df[\"apr_drg_with_code\"] = hospital_df.apply(\n",
    "    lambda x: str(\"00\" + str(int(x[\"apr_drg_code\"])))[-3:] + \" - \" + x[\"apr_drg_description\"], axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For hospital stays longer than 120 days are coded as `\"120 +\"`. We convert these to 120 days so we can convert the values into a numeric values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "hospital_df[\"length_of_stay\"] = hospital_df.apply(\n",
    "    lambda x: 120 if \"120 +\" == x[\"length_of_stay\"] else int(x[\"length_of_stay\"]), axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "15436"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hospital_df[\"length_of_stay\"].count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We need to separately group the data for all discharges and for the mortality data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "apr_drgs_df = hospital_df.groupby(\"apr_drg_with_code\")[\"length_of_stay\"].agg({\"discharges\": np.size, \"total_days\": np.sum, \n",
    "                                                                \"mean_length_of_stay\": np.mean})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "apr_drgs_expired_df=hospital_df[hospital_df[\"patient_disposition\"] == \"Expired\"].groupby(\"apr_drg_with_code\")[\"length_of_stay\"].agg({\"number_of_in_hospital_deaths\": np.size})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "merged_apr_drgs_df = pd.merge(apr_drgs_df.reset_index(level=[\"apr_drg_with_code\"]),apr_drgs_expired_df.reset_index(level=[\"apr_drg_with_code\"]), on=\"apr_drg_with_code\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>apr_drg_with_code</th>\n",
       "      <th>discharges</th>\n",
       "      <th>total_days</th>\n",
       "      <th>mean_length_of_stay</th>\n",
       "      <th>number_of_in_hospital_deaths</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>004 - TRACHEOSTOMY W MV 96+ HOURS W EXTENSIVE ...</td>\n",
       "      <td>9</td>\n",
       "      <td>524</td>\n",
       "      <td>58.222222</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>005 - TRACHEOSTOMY W MV 96+ HOURS W/O EXTENSIV...</td>\n",
       "      <td>58</td>\n",
       "      <td>2901</td>\n",
       "      <td>50.017241</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>020 - CRANIOTOMY FOR TRAUMA</td>\n",
       "      <td>13</td>\n",
       "      <td>142</td>\n",
       "      <td>10.923077</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>021 - CRANIOTOMY EXCEPT FOR TRAUMA</td>\n",
       "      <td>27</td>\n",
       "      <td>624</td>\n",
       "      <td>23.111111</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>024 - EXTRACRANIAL VASCULAR PROCEDURES</td>\n",
       "      <td>6</td>\n",
       "      <td>17</td>\n",
       "      <td>2.833333</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                   apr_drg_with_code  discharges  total_days  \\\n",
       "0  004 - TRACHEOSTOMY W MV 96+ HOURS W EXTENSIVE ...           9         524   \n",
       "1  005 - TRACHEOSTOMY W MV 96+ HOURS W/O EXTENSIV...          58        2901   \n",
       "2                        020 - CRANIOTOMY FOR TRAUMA          13         142   \n",
       "3                 021 - CRANIOTOMY EXCEPT FOR TRAUMA          27         624   \n",
       "4             024 - EXTRACRANIAL VASCULAR PROCEDURES           6          17   \n",
       "\n",
       "   mean_length_of_stay  number_of_in_hospital_deaths  \n",
       "0            58.222222                             4  \n",
       "1            50.017241                            16  \n",
       "2            10.923077                             2  \n",
       "3            23.111111                             5  \n",
       "4             2.833333                             1  "
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_apr_drgs_df.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "90"
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_apr_drgs_df.apr_drg_with_code.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "merged_apr_drgs_df[\"number_hospital_deaths_per_discharge\"] = merged_apr_drgs_df[\"number_of_in_hospital_deaths\"] / merged_apr_drgs_df[\"discharges\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "merged_apr_drgs_df[\"number_hospital_deaths_per_patient_days\"] = merged_apr_drgs_df[\"number_of_in_hospital_deaths\"] / merged_apr_drgs_df[\"total_days\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Anaconda2\\lib\\site-packages\\ipykernel\\__main__.py:1: UserWarning: Boolean Series key will be reindexed to match DataFrame index.\n",
      "  if __name__ == '__main__':\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>apr_drg_with_code</th>\n",
       "      <th>discharges</th>\n",
       "      <th>total_days</th>\n",
       "      <th>mean_length_of_stay</th>\n",
       "      <th>number_of_in_hospital_deaths</th>\n",
       "      <th>number_hospital_deaths_per_discharge</th>\n",
       "      <th>number_hospital_deaths_per_patient_days</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>196 - CARDIAC ARREST</td>\n",
       "      <td>13</td>\n",
       "      <td>37</td>\n",
       "      <td>2.846154</td>\n",
       "      <td>11</td>\n",
       "      <td>0.846154</td>\n",
       "      <td>0.297297</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>130 - RESPIRATORY SYSTEM DIAGNOSIS W VENTILATO...</td>\n",
       "      <td>41</td>\n",
       "      <td>907</td>\n",
       "      <td>22.121951</td>\n",
       "      <td>20</td>\n",
       "      <td>0.487805</td>\n",
       "      <td>0.022051</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>044 - INTRACRANIAL HEMORRHAGE</td>\n",
       "      <td>44</td>\n",
       "      <td>365</td>\n",
       "      <td>8.295455</td>\n",
       "      <td>14</td>\n",
       "      <td>0.318182</td>\n",
       "      <td>0.038356</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>281 - MALIGNANCY OF HEPATOBILIARY SYSTEM &amp; PAN...</td>\n",
       "      <td>19</td>\n",
       "      <td>266</td>\n",
       "      <td>14.000000</td>\n",
       "      <td>6</td>\n",
       "      <td>0.315789</td>\n",
       "      <td>0.022556</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74</th>\n",
       "      <td>720 - SEPTICEMIA &amp; DISSEMINATED INFECTIONS</td>\n",
       "      <td>563</td>\n",
       "      <td>6977</td>\n",
       "      <td>12.392540</td>\n",
       "      <td>177</td>\n",
       "      <td>0.314387</td>\n",
       "      <td>0.025369</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>052 - NONTRAUMATIC STUPOR &amp; COMA</td>\n",
       "      <td>28</td>\n",
       "      <td>188</td>\n",
       "      <td>6.714286</td>\n",
       "      <td>8</td>\n",
       "      <td>0.285714</td>\n",
       "      <td>0.042553</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>005 - TRACHEOSTOMY W MV 96+ HOURS W/O EXTENSIV...</td>\n",
       "      <td>58</td>\n",
       "      <td>2901</td>\n",
       "      <td>50.017241</td>\n",
       "      <td>16</td>\n",
       "      <td>0.275862</td>\n",
       "      <td>0.005515</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>136 - RESPIRATORY MALIGNANCY</td>\n",
       "      <td>20</td>\n",
       "      <td>357</td>\n",
       "      <td>17.850000</td>\n",
       "      <td>4</td>\n",
       "      <td>0.200000</td>\n",
       "      <td>0.011204</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>021 - CRANIOTOMY EXCEPT FOR TRAUMA</td>\n",
       "      <td>27</td>\n",
       "      <td>624</td>\n",
       "      <td>23.111111</td>\n",
       "      <td>5</td>\n",
       "      <td>0.185185</td>\n",
       "      <td>0.008013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72</th>\n",
       "      <td>710 - INFECTIOUS &amp; PARASITIC DISEASES INCLUDIN...</td>\n",
       "      <td>43</td>\n",
       "      <td>1114</td>\n",
       "      <td>25.906977</td>\n",
       "      <td>7</td>\n",
       "      <td>0.162791</td>\n",
       "      <td>0.006284</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>020 - CRANIOTOMY FOR TRAUMA</td>\n",
       "      <td>13</td>\n",
       "      <td>142</td>\n",
       "      <td>10.923077</td>\n",
       "      <td>2</td>\n",
       "      <td>0.153846</td>\n",
       "      <td>0.014085</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>240 - DIGESTIVE MALIGNANCY</td>\n",
       "      <td>21</td>\n",
       "      <td>256</td>\n",
       "      <td>12.190476</td>\n",
       "      <td>3</td>\n",
       "      <td>0.142857</td>\n",
       "      <td>0.011719</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>041 - NERVOUS SYSTEM MALIGNANCY</td>\n",
       "      <td>14</td>\n",
       "      <td>212</td>\n",
       "      <td>15.142857</td>\n",
       "      <td>2</td>\n",
       "      <td>0.142857</td>\n",
       "      <td>0.009434</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>121 - OTHER RESPIRATORY &amp; CHEST PROCEDURES</td>\n",
       "      <td>14</td>\n",
       "      <td>209</td>\n",
       "      <td>14.928571</td>\n",
       "      <td>2</td>\n",
       "      <td>0.142857</td>\n",
       "      <td>0.009569</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>133 - PULMONARY EDEMA &amp; RESPIRATORY FAILURE</td>\n",
       "      <td>113</td>\n",
       "      <td>1164</td>\n",
       "      <td>10.300885</td>\n",
       "      <td>16</td>\n",
       "      <td>0.141593</td>\n",
       "      <td>0.013746</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>346 - CONNECTIVE TISSUE DISORDERS</td>\n",
       "      <td>15</td>\n",
       "      <td>259</td>\n",
       "      <td>17.266667</td>\n",
       "      <td>2</td>\n",
       "      <td>0.133333</td>\n",
       "      <td>0.007722</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62</th>\n",
       "      <td>421 - MALNUTRITION, FAILURE TO THRIVE &amp; OTHER ...</td>\n",
       "      <td>31</td>\n",
       "      <td>232</td>\n",
       "      <td>7.483871</td>\n",
       "      <td>4</td>\n",
       "      <td>0.129032</td>\n",
       "      <td>0.017241</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>137 - MAJOR RESPIRATORY INFECTIONS &amp; INFLAMMAT...</td>\n",
       "      <td>66</td>\n",
       "      <td>702</td>\n",
       "      <td>10.636364</td>\n",
       "      <td>7</td>\n",
       "      <td>0.106061</td>\n",
       "      <td>0.009972</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>242 - MAJOR ESOPHAGEAL DISORDERS</td>\n",
       "      <td>30</td>\n",
       "      <td>175</td>\n",
       "      <td>5.833333</td>\n",
       "      <td>3</td>\n",
       "      <td>0.100000</td>\n",
       "      <td>0.017143</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>207 - OTHER CIRCULATORY SYSTEM DIAGNOSES</td>\n",
       "      <td>61</td>\n",
       "      <td>305</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>6</td>\n",
       "      <td>0.098361</td>\n",
       "      <td>0.019672</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>815 - OTHER INJURY, POISONING &amp; TOXIC EFFECT D...</td>\n",
       "      <td>22</td>\n",
       "      <td>176</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>2</td>\n",
       "      <td>0.090909</td>\n",
       "      <td>0.011364</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>304 - DORSAL &amp; LUMBAR FUSION PROC EXCEPT FOR C...</td>\n",
       "      <td>11</td>\n",
       "      <td>120</td>\n",
       "      <td>10.909091</td>\n",
       "      <td>1</td>\n",
       "      <td>0.090909</td>\n",
       "      <td>0.008333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>134 - PULMONARY EMBOLISM</td>\n",
       "      <td>33</td>\n",
       "      <td>277</td>\n",
       "      <td>8.393939</td>\n",
       "      <td>3</td>\n",
       "      <td>0.090909</td>\n",
       "      <td>0.010830</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86</th>\n",
       "      <td>890 - HIV W MULTIPLE MAJOR HIV RELATED CONDITIONS</td>\n",
       "      <td>12</td>\n",
       "      <td>341</td>\n",
       "      <td>28.416667</td>\n",
       "      <td>1</td>\n",
       "      <td>0.083333</td>\n",
       "      <td>0.002933</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78</th>\n",
       "      <td>776 - OTHER DRUG ABUSE &amp; DEPENDENCE</td>\n",
       "      <td>12</td>\n",
       "      <td>54</td>\n",
       "      <td>4.500000</td>\n",
       "      <td>1</td>\n",
       "      <td>0.083333</td>\n",
       "      <td>0.018519</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89</th>\n",
       "      <td>952 - NONEXTENSIVE PROCEDURE UNRELATED TO PRIN...</td>\n",
       "      <td>25</td>\n",
       "      <td>410</td>\n",
       "      <td>16.400000</td>\n",
       "      <td>2</td>\n",
       "      <td>0.080000</td>\n",
       "      <td>0.004878</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>812 - POISONING OF MEDICINAL AGENTS</td>\n",
       "      <td>69</td>\n",
       "      <td>369</td>\n",
       "      <td>5.347826</td>\n",
       "      <td>5</td>\n",
       "      <td>0.072464</td>\n",
       "      <td>0.013550</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>045 - CVA &amp; PRECEREBRAL OCCLUSION W INFARCT</td>\n",
       "      <td>211</td>\n",
       "      <td>1825</td>\n",
       "      <td>8.649289</td>\n",
       "      <td>15</td>\n",
       "      <td>0.071090</td>\n",
       "      <td>0.008219</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>950 - EXTENSIVE PROCEDURE UNRELATED TO PRINCIP...</td>\n",
       "      <td>15</td>\n",
       "      <td>314</td>\n",
       "      <td>20.933333</td>\n",
       "      <td>1</td>\n",
       "      <td>0.066667</td>\n",
       "      <td>0.003185</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>253 - OTHER &amp; UNSPECIFIED GASTROINTESTINAL HEM...</td>\n",
       "      <td>108</td>\n",
       "      <td>685</td>\n",
       "      <td>6.342593</td>\n",
       "      <td>7</td>\n",
       "      <td>0.064815</td>\n",
       "      <td>0.010219</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>190 - ACUTE MYOCARDIAL INFARCTION</td>\n",
       "      <td>119</td>\n",
       "      <td>610</td>\n",
       "      <td>5.126050</td>\n",
       "      <td>4</td>\n",
       "      <td>0.033613</td>\n",
       "      <td>0.006557</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81</th>\n",
       "      <td>816 - TOXIC EFFECTS OF NON-MEDICINAL SUBSTANCES</td>\n",
       "      <td>64</td>\n",
       "      <td>381</td>\n",
       "      <td>5.953125</td>\n",
       "      <td>2</td>\n",
       "      <td>0.031250</td>\n",
       "      <td>0.005249</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>143 - OTHER RESPIRATORY DIAGNOSES EXCEPT SIGNS...</td>\n",
       "      <td>32</td>\n",
       "      <td>232</td>\n",
       "      <td>7.250000</td>\n",
       "      <td>1</td>\n",
       "      <td>0.031250</td>\n",
       "      <td>0.004310</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>194 - HEART FAILURE</td>\n",
       "      <td>495</td>\n",
       "      <td>3932</td>\n",
       "      <td>7.943434</td>\n",
       "      <td>15</td>\n",
       "      <td>0.030303</td>\n",
       "      <td>0.003815</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59</th>\n",
       "      <td>385 - OTHER SKIN, SUBCUTANEOUS TISSUE &amp; BREAST...</td>\n",
       "      <td>35</td>\n",
       "      <td>127</td>\n",
       "      <td>3.628571</td>\n",
       "      <td>1</td>\n",
       "      <td>0.028571</td>\n",
       "      <td>0.007874</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>201 - CARDIAC ARRHYTHMIA &amp; CONDUCTION DISORDERS</td>\n",
       "      <td>187</td>\n",
       "      <td>976</td>\n",
       "      <td>5.219251</td>\n",
       "      <td>5</td>\n",
       "      <td>0.026738</td>\n",
       "      <td>0.005123</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>282 - DISORDERS OF PANCREAS EXCEPT MALIGNANCY</td>\n",
       "      <td>118</td>\n",
       "      <td>759</td>\n",
       "      <td>6.432203</td>\n",
       "      <td>3</td>\n",
       "      <td>0.025424</td>\n",
       "      <td>0.003953</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>77</th>\n",
       "      <td>774 - COCAINE ABUSE &amp; DEPENDENCE</td>\n",
       "      <td>46</td>\n",
       "      <td>247</td>\n",
       "      <td>5.369565</td>\n",
       "      <td>1</td>\n",
       "      <td>0.021739</td>\n",
       "      <td>0.004049</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>308 - HIP &amp; FEMUR PROCEDURES FOR TRAUMA EXCEPT...</td>\n",
       "      <td>96</td>\n",
       "      <td>1072</td>\n",
       "      <td>11.166667</td>\n",
       "      <td>2</td>\n",
       "      <td>0.020833</td>\n",
       "      <td>0.001866</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>301 - HIP JOINT REPLACEMENT</td>\n",
       "      <td>50</td>\n",
       "      <td>508</td>\n",
       "      <td>10.160000</td>\n",
       "      <td>1</td>\n",
       "      <td>0.020000</td>\n",
       "      <td>0.001969</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>139 - OTHER PNEUMONIA</td>\n",
       "      <td>312</td>\n",
       "      <td>2376</td>\n",
       "      <td>7.615385</td>\n",
       "      <td>6</td>\n",
       "      <td>0.019231</td>\n",
       "      <td>0.002525</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>254 - OTHER DIGESTIVE SYSTEM DIAGNOSES</td>\n",
       "      <td>161</td>\n",
       "      <td>660</td>\n",
       "      <td>4.099379</td>\n",
       "      <td>3</td>\n",
       "      <td>0.018634</td>\n",
       "      <td>0.004545</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>247 - INTESTINAL OBSTRUCTION</td>\n",
       "      <td>56</td>\n",
       "      <td>310</td>\n",
       "      <td>5.535714</td>\n",
       "      <td>1</td>\n",
       "      <td>0.017857</td>\n",
       "      <td>0.003226</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76</th>\n",
       "      <td>757 - ORGANIC MENTAL HEALTH DISTURBANCES</td>\n",
       "      <td>58</td>\n",
       "      <td>597</td>\n",
       "      <td>10.293103</td>\n",
       "      <td>1</td>\n",
       "      <td>0.017241</td>\n",
       "      <td>0.001675</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>280 - ALCOHOLIC LIVER DISEASE</td>\n",
       "      <td>61</td>\n",
       "      <td>402</td>\n",
       "      <td>6.590164</td>\n",
       "      <td>1</td>\n",
       "      <td>0.016393</td>\n",
       "      <td>0.002488</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>191 - CARDIAC CATHETERIZATION W CIRC DISORD EX...</td>\n",
       "      <td>63</td>\n",
       "      <td>595</td>\n",
       "      <td>9.444444</td>\n",
       "      <td>1</td>\n",
       "      <td>0.015873</td>\n",
       "      <td>0.001681</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67</th>\n",
       "      <td>468 - OTHER KIDNEY &amp; URINARY TRACT DIAGNOSES, ...</td>\n",
       "      <td>68</td>\n",
       "      <td>459</td>\n",
       "      <td>6.750000</td>\n",
       "      <td>1</td>\n",
       "      <td>0.014706</td>\n",
       "      <td>0.002179</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>140 - CHRONIC OBSTRUCTIVE PULMONARY DISEASE</td>\n",
       "      <td>296</td>\n",
       "      <td>1771</td>\n",
       "      <td>5.983108</td>\n",
       "      <td>4</td>\n",
       "      <td>0.013514</td>\n",
       "      <td>0.002259</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54</th>\n",
       "      <td>347 - OTHER BACK &amp; NECK DISORDERS, FRACTURES &amp;...</td>\n",
       "      <td>77</td>\n",
       "      <td>483</td>\n",
       "      <td>6.272727</td>\n",
       "      <td>1</td>\n",
       "      <td>0.012987</td>\n",
       "      <td>0.002070</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>199 - HYPERTENSION</td>\n",
       "      <td>100</td>\n",
       "      <td>419</td>\n",
       "      <td>4.190000</td>\n",
       "      <td>1</td>\n",
       "      <td>0.010000</td>\n",
       "      <td>0.002387</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>053 - SEIZURE</td>\n",
       "      <td>242</td>\n",
       "      <td>1169</td>\n",
       "      <td>4.830579</td>\n",
       "      <td>2</td>\n",
       "      <td>0.008264</td>\n",
       "      <td>0.001711</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84</th>\n",
       "      <td>860 - REHABILITATION</td>\n",
       "      <td>134</td>\n",
       "      <td>2234</td>\n",
       "      <td>16.671642</td>\n",
       "      <td>1</td>\n",
       "      <td>0.007463</td>\n",
       "      <td>0.000448</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58</th>\n",
       "      <td>383 - CELLULITIS &amp; OTHER BACTERIAL SKIN INFECT...</td>\n",
       "      <td>269</td>\n",
       "      <td>1673</td>\n",
       "      <td>6.219331</td>\n",
       "      <td>2</td>\n",
       "      <td>0.007435</td>\n",
       "      <td>0.001195</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65</th>\n",
       "      <td>463 - KIDNEY &amp; URINARY TRACT INFECTIONS</td>\n",
       "      <td>280</td>\n",
       "      <td>1919</td>\n",
       "      <td>6.853571</td>\n",
       "      <td>2</td>\n",
       "      <td>0.007143</td>\n",
       "      <td>0.001042</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55</th>\n",
       "      <td>351 - OTHER MUSCULOSKELETAL SYSTEM &amp; CONNECTIV...</td>\n",
       "      <td>147</td>\n",
       "      <td>843</td>\n",
       "      <td>5.734694</td>\n",
       "      <td>1</td>\n",
       "      <td>0.006803</td>\n",
       "      <td>0.001186</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>249 - NON-BACTERIAL GASTROENTERITIS, NAUSEA &amp; ...</td>\n",
       "      <td>161</td>\n",
       "      <td>809</td>\n",
       "      <td>5.024845</td>\n",
       "      <td>1</td>\n",
       "      <td>0.006211</td>\n",
       "      <td>0.001236</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>204 - SYNCOPE &amp; COLLAPSE</td>\n",
       "      <td>391</td>\n",
       "      <td>1914</td>\n",
       "      <td>4.895141</td>\n",
       "      <td>2</td>\n",
       "      <td>0.005115</td>\n",
       "      <td>0.001045</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>203 - CHEST PAIN</td>\n",
       "      <td>197</td>\n",
       "      <td>645</td>\n",
       "      <td>3.274112</td>\n",
       "      <td>1</td>\n",
       "      <td>0.005076</td>\n",
       "      <td>0.001550</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61</th>\n",
       "      <td>420 - DIABETES</td>\n",
       "      <td>199</td>\n",
       "      <td>890</td>\n",
       "      <td>4.472362</td>\n",
       "      <td>1</td>\n",
       "      <td>0.005025</td>\n",
       "      <td>0.001124</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>198 - ANGINA PECTORIS &amp; CORONARY ATHEROSCLEROSIS</td>\n",
       "      <td>223</td>\n",
       "      <td>918</td>\n",
       "      <td>4.116592</td>\n",
       "      <td>1</td>\n",
       "      <td>0.004484</td>\n",
       "      <td>0.001089</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>74 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                    apr_drg_with_code  discharges  total_days  \\\n",
       "24                               196 - CARDIAC ARREST          13          37   \n",
       "13  130 - RESPIRATORY SYSTEM DIAGNOSIS W VENTILATO...          41         907   \n",
       "6                       044 - INTRACRANIAL HEMORRHAGE          44         365   \n",
       "45  281 - MALIGNANCY OF HEPATOBILIARY SYSTEM & PAN...          19         266   \n",
       "74         720 - SEPTICEMIA & DISSEMINATED INFECTIONS         563        6977   \n",
       "8                    052 - NONTRAUMATIC STUPOR & COMA          28         188   \n",
       "1   005 - TRACHEOSTOMY W MV 96+ HOURS W/O EXTENSIV...          58        2901   \n",
       "16                       136 - RESPIRATORY MALIGNANCY          20         357   \n",
       "3                  021 - CRANIOTOMY EXCEPT FOR TRAUMA          27         624   \n",
       "72  710 - INFECTIOUS & PARASITIC DISEASES INCLUDIN...          43        1114   \n",
       "2                         020 - CRANIOTOMY FOR TRAUMA          13         142   \n",
       "36                         240 - DIGESTIVE MALIGNANCY          21         256   \n",
       "5                     041 - NERVOUS SYSTEM MALIGNANCY          14         212   \n",
       "12         121 - OTHER RESPIRATORY & CHEST PROCEDURES          14         209   \n",
       "14        133 - PULMONARY EDEMA & RESPIRATORY FAILURE         113        1164   \n",
       "53                  346 - CONNECTIVE TISSUE DISORDERS          15         259   \n",
       "62  421 - MALNUTRITION, FAILURE TO THRIVE & OTHER ...          31         232   \n",
       "17  137 - MAJOR RESPIRATORY INFECTIONS & INFLAMMAT...          66         702   \n",
       "37                   242 - MAJOR ESOPHAGEAL DISORDERS          30         175   \n",
       "32           207 - OTHER CIRCULATORY SYSTEM DIAGNOSES          61         305   \n",
       "80  815 - OTHER INJURY, POISONING & TOXIC EFFECT D...          22         176   \n",
       "48  304 - DORSAL & LUMBAR FUSION PROC EXCEPT FOR C...          11         120   \n",
       "15                           134 - PULMONARY EMBOLISM          33         277   \n",
       "86  890 - HIV W MULTIPLE MAJOR HIV RELATED CONDITIONS          12         341   \n",
       "78                776 - OTHER DRUG ABUSE & DEPENDENCE          12          54   \n",
       "89  952 - NONEXTENSIVE PROCEDURE UNRELATED TO PRIN...          25         410   \n",
       "79                812 - POISONING OF MEDICINAL AGENTS          69         369   \n",
       "7         045 - CVA & PRECEREBRAL OCCLUSION W INFARCT         211        1825   \n",
       "88  950 - EXTENSIVE PROCEDURE UNRELATED TO PRINCIP...          15         314   \n",
       "41  253 - OTHER & UNSPECIFIED GASTROINTESTINAL HEM...         108         685   \n",
       "..                                                ...         ...         ...   \n",
       "21                  190 - ACUTE MYOCARDIAL INFARCTION         119         610   \n",
       "81    816 - TOXIC EFFECTS OF NON-MEDICINAL SUBSTANCES          64         381   \n",
       "20  143 - OTHER RESPIRATORY DIAGNOSES EXCEPT SIGNS...          32         232   \n",
       "23                                194 - HEART FAILURE         495        3932   \n",
       "59  385 - OTHER SKIN, SUBCUTANEOUS TISSUE & BREAST...          35         127   \n",
       "28    201 - CARDIAC ARRHYTHMIA & CONDUCTION DISORDERS         187         976   \n",
       "46      282 - DISORDERS OF PANCREAS EXCEPT MALIGNANCY         118         759   \n",
       "77                   774 - COCAINE ABUSE & DEPENDENCE          46         247   \n",
       "49  308 - HIP & FEMUR PROCEDURES FOR TRAUMA EXCEPT...          96        1072   \n",
       "47                        301 - HIP JOINT REPLACEMENT          50         508   \n",
       "18                              139 - OTHER PNEUMONIA         312        2376   \n",
       "42             254 - OTHER DIGESTIVE SYSTEM DIAGNOSES         161         660   \n",
       "38                       247 - INTESTINAL OBSTRUCTION          56         310   \n",
       "76           757 - ORGANIC MENTAL HEALTH DISTURBANCES          58         597   \n",
       "44                      280 - ALCOHOLIC LIVER DISEASE          61         402   \n",
       "22  191 - CARDIAC CATHETERIZATION W CIRC DISORD EX...          63         595   \n",
       "67  468 - OTHER KIDNEY & URINARY TRACT DIAGNOSES, ...          68         459   \n",
       "19        140 - CHRONIC OBSTRUCTIVE PULMONARY DISEASE         296        1771   \n",
       "54  347 - OTHER BACK & NECK DISORDERS, FRACTURES &...          77         483   \n",
       "27                                 199 - HYPERTENSION         100         419   \n",
       "9                                       053 - SEIZURE         242        1169   \n",
       "84                               860 - REHABILITATION         134        2234   \n",
       "58  383 - CELLULITIS & OTHER BACTERIAL SKIN INFECT...         269        1673   \n",
       "65            463 - KIDNEY & URINARY TRACT INFECTIONS         280        1919   \n",
       "55  351 - OTHER MUSCULOSKELETAL SYSTEM & CONNECTIV...         147         843   \n",
       "39  249 - NON-BACTERIAL GASTROENTERITIS, NAUSEA & ...         161         809   \n",
       "30                           204 - SYNCOPE & COLLAPSE         391        1914   \n",
       "29                                   203 - CHEST PAIN         197         645   \n",
       "61                                     420 - DIABETES         199         890   \n",
       "26   198 - ANGINA PECTORIS & CORONARY ATHEROSCLEROSIS         223         918   \n",
       "\n",
       "    mean_length_of_stay  number_of_in_hospital_deaths  \\\n",
       "24             2.846154                            11   \n",
       "13            22.121951                            20   \n",
       "6              8.295455                            14   \n",
       "45            14.000000                             6   \n",
       "74            12.392540                           177   \n",
       "8              6.714286                             8   \n",
       "1             50.017241                            16   \n",
       "16            17.850000                             4   \n",
       "3             23.111111                             5   \n",
       "72            25.906977                             7   \n",
       "2             10.923077                             2   \n",
       "36            12.190476                             3   \n",
       "5             15.142857                             2   \n",
       "12            14.928571                             2   \n",
       "14            10.300885                            16   \n",
       "53            17.266667                             2   \n",
       "62             7.483871                             4   \n",
       "17            10.636364                             7   \n",
       "37             5.833333                             3   \n",
       "32             5.000000                             6   \n",
       "80             8.000000                             2   \n",
       "48            10.909091                             1   \n",
       "15             8.393939                             3   \n",
       "86            28.416667                             1   \n",
       "78             4.500000                             1   \n",
       "89            16.400000                             2   \n",
       "79             5.347826                             5   \n",
       "7              8.649289                            15   \n",
       "88            20.933333                             1   \n",
       "41             6.342593                             7   \n",
       "..                  ...                           ...   \n",
       "21             5.126050                             4   \n",
       "81             5.953125                             2   \n",
       "20             7.250000                             1   \n",
       "23             7.943434                            15   \n",
       "59             3.628571                             1   \n",
       "28             5.219251                             5   \n",
       "46             6.432203                             3   \n",
       "77             5.369565                             1   \n",
       "49            11.166667                             2   \n",
       "47            10.160000                             1   \n",
       "18             7.615385                             6   \n",
       "42             4.099379                             3   \n",
       "38             5.535714                             1   \n",
       "76            10.293103                             1   \n",
       "44             6.590164                             1   \n",
       "22             9.444444                             1   \n",
       "67             6.750000                             1   \n",
       "19             5.983108                             4   \n",
       "54             6.272727                             1   \n",
       "27             4.190000                             1   \n",
       "9              4.830579                             2   \n",
       "84            16.671642                             1   \n",
       "58             6.219331                             2   \n",
       "65             6.853571                             2   \n",
       "55             5.734694                             1   \n",
       "39             5.024845                             1   \n",
       "30             4.895141                             2   \n",
       "29             3.274112                             1   \n",
       "61             4.472362                             1   \n",
       "26             4.116592                             1   \n",
       "\n",
       "    number_hospital_deaths_per_discharge  \\\n",
       "24                              0.846154   \n",
       "13                              0.487805   \n",
       "6                               0.318182   \n",
       "45                              0.315789   \n",
       "74                              0.314387   \n",
       "8                               0.285714   \n",
       "1                               0.275862   \n",
       "16                              0.200000   \n",
       "3                               0.185185   \n",
       "72                              0.162791   \n",
       "2                               0.153846   \n",
       "36                              0.142857   \n",
       "5                               0.142857   \n",
       "12                              0.142857   \n",
       "14                              0.141593   \n",
       "53                              0.133333   \n",
       "62                              0.129032   \n",
       "17                              0.106061   \n",
       "37                              0.100000   \n",
       "32                              0.098361   \n",
       "80                              0.090909   \n",
       "48                              0.090909   \n",
       "15                              0.090909   \n",
       "86                              0.083333   \n",
       "78                              0.083333   \n",
       "89                              0.080000   \n",
       "79                              0.072464   \n",
       "7                               0.071090   \n",
       "88                              0.066667   \n",
       "41                              0.064815   \n",
       "..                                   ...   \n",
       "21                              0.033613   \n",
       "81                              0.031250   \n",
       "20                              0.031250   \n",
       "23                              0.030303   \n",
       "59                              0.028571   \n",
       "28                              0.026738   \n",
       "46                              0.025424   \n",
       "77                              0.021739   \n",
       "49                              0.020833   \n",
       "47                              0.020000   \n",
       "18                              0.019231   \n",
       "42                              0.018634   \n",
       "38                              0.017857   \n",
       "76                              0.017241   \n",
       "44                              0.016393   \n",
       "22                              0.015873   \n",
       "67                              0.014706   \n",
       "19                              0.013514   \n",
       "54                              0.012987   \n",
       "27                              0.010000   \n",
       "9                               0.008264   \n",
       "84                              0.007463   \n",
       "58                              0.007435   \n",
       "65                              0.007143   \n",
       "55                              0.006803   \n",
       "39                              0.006211   \n",
       "30                              0.005115   \n",
       "29                              0.005076   \n",
       "61                              0.005025   \n",
       "26                              0.004484   \n",
       "\n",
       "    number_hospital_deaths_per_patient_days  \n",
       "24                                 0.297297  \n",
       "13                                 0.022051  \n",
       "6                                  0.038356  \n",
       "45                                 0.022556  \n",
       "74                                 0.025369  \n",
       "8                                  0.042553  \n",
       "1                                  0.005515  \n",
       "16                                 0.011204  \n",
       "3                                  0.008013  \n",
       "72                                 0.006284  \n",
       "2                                  0.014085  \n",
       "36                                 0.011719  \n",
       "5                                  0.009434  \n",
       "12                                 0.009569  \n",
       "14                                 0.013746  \n",
       "53                                 0.007722  \n",
       "62                                 0.017241  \n",
       "17                                 0.009972  \n",
       "37                                 0.017143  \n",
       "32                                 0.019672  \n",
       "80                                 0.011364  \n",
       "48                                 0.008333  \n",
       "15                                 0.010830  \n",
       "86                                 0.002933  \n",
       "78                                 0.018519  \n",
       "89                                 0.004878  \n",
       "79                                 0.013550  \n",
       "7                                  0.008219  \n",
       "88                                 0.003185  \n",
       "41                                 0.010219  \n",
       "..                                      ...  \n",
       "21                                 0.006557  \n",
       "81                                 0.005249  \n",
       "20                                 0.004310  \n",
       "23                                 0.003815  \n",
       "59                                 0.007874  \n",
       "28                                 0.005123  \n",
       "46                                 0.003953  \n",
       "77                                 0.004049  \n",
       "49                                 0.001866  \n",
       "47                                 0.001969  \n",
       "18                                 0.002525  \n",
       "42                                 0.004545  \n",
       "38                                 0.003226  \n",
       "76                                 0.001675  \n",
       "44                                 0.002488  \n",
       "22                                 0.001681  \n",
       "67                                 0.002179  \n",
       "19                                 0.002259  \n",
       "54                                 0.002070  \n",
       "27                                 0.002387  \n",
       "9                                  0.001711  \n",
       "84                                 0.000448  \n",
       "58                                 0.001195  \n",
       "65                                 0.001042  \n",
       "55                                 0.001186  \n",
       "39                                 0.001236  \n",
       "30                                 0.001045  \n",
       "29                                 0.001550  \n",
       "61                                 0.001124  \n",
       "26                                 0.001089  \n",
       "\n",
       "[74 rows x 7 columns]"
      ]
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_apr_drgs_df.sort_values(\"number_hospital_deaths_per_discharge\", ascending=False)[merged_apr_drgs_df[\"discharges\"] >= 10]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "This analysis could be extended by computing the APR DRGs based mortality across one or more hospitals. That would allow an analyst to compare the rate at one facility against another."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2.0
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}