analyseDataMySql.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import csv\n",
    "import pymysql.cursors\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "database = \"healthCC\"\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 1:  Quick Data Exploration\n",
    "\n",
    "First step in Data Analysis is to see the basic information and structure of our data. We start with our file *'hospital_gen_info'* which contains basic information and comparison of all the hospitals."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 4806 entries, 0 to 4805\n",
      "Data columns (total 28 columns):\n",
      "Provider ID                                                      4806 non-null int64\n",
      "Hospital Name                                                    4806 non-null object\n",
      "Address                                                          4806 non-null object\n",
      "City                                                             4806 non-null object\n",
      "State                                                            4806 non-null object\n",
      "ZIP Code                                                         4806 non-null int64\n",
      "County Name                                                      4791 non-null object\n",
      "Phone Number                                                     4806 non-null int64\n",
      "Hospital Type                                                    4806 non-null object\n",
      "Hospital Ownership                                               4806 non-null object\n",
      "Emergency Services                                               4806 non-null object\n",
      "Meets criteria for meaningful use of EHRs                        4654 non-null object\n",
      "Hospital overall rating                                          4806 non-null object\n",
      "Hospital overall rating footnote                                 1264 non-null object\n",
      "Mortality national comparison                                    4806 non-null object\n",
      "Mortality national comparison footnote                           1365 non-null object\n",
      "Safety of care national comparison                               4806 non-null object\n",
      "Safety of care national comparison footnote                      2165 non-null object\n",
      "Readmission national comparison                                  4806 non-null object\n",
      "Readmission national comparison footnote                         917 non-null object\n",
      "Patient experience national comparison                           4806 non-null object\n",
      "Patient experience national comparison footnote                  1321 non-null object\n",
      "Effectiveness of care national comparison                        4806 non-null object\n",
      "Effectiveness of care national comparison footnote               1067 non-null object\n",
      "Timeliness of care national comparison                           4806 non-null object\n",
      "Timeliness of care national comparison footnote                  1128 non-null object\n",
      "Efficient use of medical imaging national comparison             4806 non-null object\n",
      "Efficient use of medical imaging national comparison footnote    1886 non-null object\n",
      "dtypes: int64(3), object(25)\n",
      "memory usage: 1.0+ MB\n"
     ]
    }
   ],
   "source": [
    "df = pd.read_csv(\"data/hospital_gen_info.csv\")\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We observe that **'Provider Id'** column has 4806 non-null entries. Which means that we have 4806 entries in our csv. However we observe that some columns like 'County Name' have 4791 non-null entries. Which implies that some of the rows have 'county name' missing. As 'County Name' is not an information whose emptiness impacts our analysis, we will leave it as it is. Similarly we can evaluate the basic features of other columns in our dataset.\n",
    "\n",
    "We can also view  part of our dataset to get the better picture of overall dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Provider ID</th>\n",
       "      <th>Hospital Name</th>\n",
       "      <th>Address</th>\n",
       "      <th>City</th>\n",
       "      <th>State</th>\n",
       "      <th>ZIP Code</th>\n",
       "      <th>County Name</th>\n",
       "      <th>Phone Number</th>\n",
       "      <th>Hospital Type</th>\n",
       "      <th>Hospital Ownership</th>\n",
       "      <th>...</th>\n",
       "      <th>Readmission national comparison</th>\n",
       "      <th>Readmission national comparison footnote</th>\n",
       "      <th>Patient experience national comparison</th>\n",
       "      <th>Patient experience national comparison footnote</th>\n",
       "      <th>Effectiveness of care national comparison</th>\n",
       "      <th>Effectiveness of care national comparison footnote</th>\n",
       "      <th>Timeliness of care national comparison</th>\n",
       "      <th>Timeliness of care national comparison footnote</th>\n",
       "      <th>Efficient use of medical imaging national comparison</th>\n",
       "      <th>Efficient use of medical imaging national comparison footnote</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10001</td>\n",
       "      <td>SOUTHEAST ALABAMA MEDICAL CENTER</td>\n",
       "      <td>1108 ROSS CLARK CIRCLE</td>\n",
       "      <td>DOTHAN</td>\n",
       "      <td>AL</td>\n",
       "      <td>36301</td>\n",
       "      <td>HOUSTON</td>\n",
       "      <td>3347938701</td>\n",
       "      <td>Acute Care Hospitals</td>\n",
       "      <td>Government - Hospital District or Authority</td>\n",
       "      <td>...</td>\n",
       "      <td>Below the national average</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Below the national average</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Same as the national average</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Same as the national average</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Same as the national average</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10005</td>\n",
       "      <td>MARSHALL MEDICAL CENTER SOUTH</td>\n",
       "      <td>2505 U S HIGHWAY 431 NORTH</td>\n",
       "      <td>BOAZ</td>\n",
       "      <td>AL</td>\n",
       "      <td>35957</td>\n",
       "      <td>MARSHALL</td>\n",
       "      <td>2565938310</td>\n",
       "      <td>Acute Care Hospitals</td>\n",
       "      <td>Government - Hospital District or Authority</td>\n",
       "      <td>...</td>\n",
       "      <td>Above the national average</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Same as the national average</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Same as the national average</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Above the national average</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Below the national average</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10006</td>\n",
       "      <td>ELIZA COFFEE MEMORIAL HOSPITAL</td>\n",
       "      <td>205 MARENGO STREET</td>\n",
       "      <td>FLORENCE</td>\n",
       "      <td>AL</td>\n",
       "      <td>35631</td>\n",
       "      <td>LAUDERDALE</td>\n",
       "      <td>2567688400</td>\n",
       "      <td>Acute Care Hospitals</td>\n",
       "      <td>Government - Hospital District or Authority</td>\n",
       "      <td>...</td>\n",
       "      <td>Below the national average</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Below the national average</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Same as the national average</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Above the national average</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Below the national average</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3 rows × 28 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Provider ID                     Hospital Name                     Address  \\\n",
       "0        10001  SOUTHEAST ALABAMA MEDICAL CENTER      1108 ROSS CLARK CIRCLE   \n",
       "1        10005     MARSHALL MEDICAL CENTER SOUTH  2505 U S HIGHWAY 431 NORTH   \n",
       "2        10006    ELIZA COFFEE MEMORIAL HOSPITAL          205 MARENGO STREET   \n",
       "\n",
       "       City State  ZIP Code County Name  Phone Number         Hospital Type  \\\n",
       "0    DOTHAN    AL     36301     HOUSTON    3347938701  Acute Care Hospitals   \n",
       "1      BOAZ    AL     35957    MARSHALL    2565938310  Acute Care Hospitals   \n",
       "2  FLORENCE    AL     35631  LAUDERDALE    2567688400  Acute Care Hospitals   \n",
       "\n",
       "                            Hospital Ownership  \\\n",
       "0  Government - Hospital District or Authority   \n",
       "1  Government - Hospital District or Authority   \n",
       "2  Government - Hospital District or Authority   \n",
       "\n",
       "                               ...                               \\\n",
       "0                              ...                                \n",
       "1                              ...                                \n",
       "2                              ...                                \n",
       "\n",
       "  Readmission national comparison Readmission national comparison footnote  \\\n",
       "0      Below the national average                                      NaN   \n",
       "1      Above the national average                                      NaN   \n",
       "2      Below the national average                                      NaN   \n",
       "\n",
       "  Patient experience national comparison  \\\n",
       "0             Below the national average   \n",
       "1           Same as the national average   \n",
       "2             Below the national average   \n",
       "\n",
       "  Patient experience national comparison footnote  \\\n",
       "0                                             NaN   \n",
       "1                                             NaN   \n",
       "2                                             NaN   \n",
       "\n",
       "  Effectiveness of care national comparison  \\\n",
       "0              Same as the national average   \n",
       "1              Same as the national average   \n",
       "2              Same as the national average   \n",
       "\n",
       "  Effectiveness of care national comparison footnote  \\\n",
       "0                                                NaN   \n",
       "1                                                NaN   \n",
       "2                                                NaN   \n",
       "\n",
       "  Timeliness of care national comparison  \\\n",
       "0           Same as the national average   \n",
       "1             Above the national average   \n",
       "2             Above the national average   \n",
       "\n",
       "  Timeliness of care national comparison footnote  \\\n",
       "0                                             NaN   \n",
       "1                                             NaN   \n",
       "2                                             NaN   \n",
       "\n",
       "  Efficient use of medical imaging national comparison  \\\n",
       "0                       Same as the national average     \n",
       "1                         Below the national average     \n",
       "2                         Below the national average     \n",
       "\n",
       "  Efficient use of medical imaging national comparison footnote  \n",
       "0                                                NaN             \n",
       "1                                                NaN             \n",
       "2                                                NaN             \n",
       "\n",
       "[3 rows x 28 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Provider ID</th>\n",
       "      <th>Hospital Name</th>\n",
       "      <th>Address</th>\n",
       "      <th>City</th>\n",
       "      <th>State</th>\n",
       "      <th>ZIP Code</th>\n",
       "      <th>County Name</th>\n",
       "      <th>Phone Number</th>\n",
       "      <th>Hospital Type</th>\n",
       "      <th>Hospital Ownership</th>\n",
       "      <th>...</th>\n",
       "      <th>Readmission national comparison</th>\n",
       "      <th>Readmission national comparison footnote</th>\n",
       "      <th>Patient experience national comparison</th>\n",
       "      <th>Patient experience national comparison footnote</th>\n",
       "      <th>Effectiveness of care national comparison</th>\n",
       "      <th>Effectiveness of care national comparison footnote</th>\n",
       "      <th>Timeliness of care national comparison</th>\n",
       "      <th>Timeliness of care national comparison footnote</th>\n",
       "      <th>Efficient use of medical imaging national comparison</th>\n",
       "      <th>Efficient use of medical imaging national comparison footnote</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4801</th>\n",
       "      <td>670120</td>\n",
       "      <td>THE HOSPITALS OF PROVIDENCE TRANSMOUNTAIN CAMPUS</td>\n",
       "      <td>2000 TRANSMOUNTAIN RD</td>\n",
       "      <td>EL PASO</td>\n",
       "      <td>TX</td>\n",
       "      <td>79911</td>\n",
       "      <td>EL PASO</td>\n",
       "      <td>9158778136</td>\n",
       "      <td>Acute Care Hospitals</td>\n",
       "      <td>Proprietary</td>\n",
       "      <td>...</td>\n",
       "      <td>Not Available</td>\n",
       "      <td>There are too few measures or measure groups r...</td>\n",
       "      <td>Not Available</td>\n",
       "      <td>There are too few measures or measure groups r...</td>\n",
       "      <td>Not Available</td>\n",
       "      <td>There are too few measures or measure groups r...</td>\n",
       "      <td>Not Available</td>\n",
       "      <td>There are too few measures or measure groups r...</td>\n",
       "      <td>Not Available</td>\n",
       "      <td>There are too few measures or measure groups r...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4802</th>\n",
       "      <td>640001</td>\n",
       "      <td>LBJ TROPICAL MEDICAL CENTER</td>\n",
       "      <td>FAGAALU VILLAGE</td>\n",
       "      <td>PAGO PAGO</td>\n",
       "      <td>AS</td>\n",
       "      <td>96799</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6846334590</td>\n",
       "      <td>Acute Care Hospitals</td>\n",
       "      <td>Government - Hospital District or Authority</td>\n",
       "      <td>...</td>\n",
       "      <td>Not Available</td>\n",
       "      <td>Results are not available for this reporting p...</td>\n",
       "      <td>Not Available</td>\n",
       "      <td>Results are not available for this reporting p...</td>\n",
       "      <td>Not Available</td>\n",
       "      <td>Results are not available for this reporting p...</td>\n",
       "      <td>Not Available</td>\n",
       "      <td>Results are not available for this reporting p...</td>\n",
       "      <td>Not Available</td>\n",
       "      <td>Results are not available for this reporting p...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4803</th>\n",
       "      <td>650001</td>\n",
       "      <td>GUAM MEMORIAL HOSPITAL AUTHORITY</td>\n",
       "      <td>85O GOV CARLOS G CAMACHO ROAD</td>\n",
       "      <td>TAMUNING</td>\n",
       "      <td>GU</td>\n",
       "      <td>96913</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6716472552</td>\n",
       "      <td>Acute Care Hospitals</td>\n",
       "      <td>Government - Local</td>\n",
       "      <td>...</td>\n",
       "      <td>Same as the national average</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Not Available</td>\n",
       "      <td>There are too few measures or measure groups r...</td>\n",
       "      <td>Same as the national average</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Not Available</td>\n",
       "      <td>Results are not available for this reporting p...</td>\n",
       "      <td>Not Available</td>\n",
       "      <td>Results are not available for this reporting p...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4804</th>\n",
       "      <td>650003</td>\n",
       "      <td>GUAM REGIONAL MEDICAL CITY</td>\n",
       "      <td>133 ROUTE 3</td>\n",
       "      <td>DEDEDO</td>\n",
       "      <td>GU</td>\n",
       "      <td>96929</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6716455500</td>\n",
       "      <td>Acute Care Hospitals</td>\n",
       "      <td>Voluntary non-profit - Private</td>\n",
       "      <td>...</td>\n",
       "      <td>Not Available</td>\n",
       "      <td>Data are shown only for hospitals that partici...</td>\n",
       "      <td>Not Available</td>\n",
       "      <td>Data are shown only for hospitals that partici...</td>\n",
       "      <td>Not Available</td>\n",
       "      <td>Data are shown only for hospitals that partici...</td>\n",
       "      <td>Not Available</td>\n",
       "      <td>Data are shown only for hospitals that partici...</td>\n",
       "      <td>Not Available</td>\n",
       "      <td>Data are shown only for hospitals that partici...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4805</th>\n",
       "      <td>660001</td>\n",
       "      <td>COMMONWEALTH HEALTH CENTER</td>\n",
       "      <td>PO BOX 409CK, NAVY HILL ROAD</td>\n",
       "      <td>GARAPAN</td>\n",
       "      <td>MP</td>\n",
       "      <td>96950</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6702348950</td>\n",
       "      <td>Acute Care Hospitals</td>\n",
       "      <td>Proprietary</td>\n",
       "      <td>...</td>\n",
       "      <td>Below the national average</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Not Available</td>\n",
       "      <td>There are too few measures or measure groups r...</td>\n",
       "      <td>Not Available</td>\n",
       "      <td>There are too few measures or measure groups r...</td>\n",
       "      <td>Not Available</td>\n",
       "      <td>There are too few measures or measure groups r...</td>\n",
       "      <td>Not Available</td>\n",
       "      <td>There are too few measures or measure groups r...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 28 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Provider ID                                     Hospital Name  \\\n",
       "4801       670120  THE HOSPITALS OF PROVIDENCE TRANSMOUNTAIN CAMPUS   \n",
       "4802       640001                       LBJ TROPICAL MEDICAL CENTER   \n",
       "4803       650001                  GUAM MEMORIAL HOSPITAL AUTHORITY   \n",
       "4804       650003                        GUAM REGIONAL MEDICAL CITY   \n",
       "4805       660001                        COMMONWEALTH HEALTH CENTER   \n",
       "\n",
       "                            Address       City State  ZIP Code County Name  \\\n",
       "4801          2000 TRANSMOUNTAIN RD    EL PASO    TX     79911     EL PASO   \n",
       "4802                FAGAALU VILLAGE  PAGO PAGO    AS     96799         NaN   \n",
       "4803  85O GOV CARLOS G CAMACHO ROAD   TAMUNING    GU     96913         NaN   \n",
       "4804                    133 ROUTE 3     DEDEDO    GU     96929         NaN   \n",
       "4805   PO BOX 409CK, NAVY HILL ROAD    GARAPAN    MP     96950         NaN   \n",
       "\n",
       "      Phone Number         Hospital Type  \\\n",
       "4801    9158778136  Acute Care Hospitals   \n",
       "4802    6846334590  Acute Care Hospitals   \n",
       "4803    6716472552  Acute Care Hospitals   \n",
       "4804    6716455500  Acute Care Hospitals   \n",
       "4805    6702348950  Acute Care Hospitals   \n",
       "\n",
       "                               Hospital Ownership  \\\n",
       "4801                                  Proprietary   \n",
       "4802  Government - Hospital District or Authority   \n",
       "4803                           Government - Local   \n",
       "4804               Voluntary non-profit - Private   \n",
       "4805                                  Proprietary   \n",
       "\n",
       "                                  ...                               \\\n",
       "4801                              ...                                \n",
       "4802                              ...                                \n",
       "4803                              ...                                \n",
       "4804                              ...                                \n",
       "4805                              ...                                \n",
       "\n",
       "     Readmission national comparison  \\\n",
       "4801                   Not Available   \n",
       "4802                   Not Available   \n",
       "4803    Same as the national average   \n",
       "4804                   Not Available   \n",
       "4805      Below the national average   \n",
       "\n",
       "               Readmission national comparison footnote  \\\n",
       "4801  There are too few measures or measure groups r...   \n",
       "4802  Results are not available for this reporting p...   \n",
       "4803                                                NaN   \n",
       "4804  Data are shown only for hospitals that partici...   \n",
       "4805                                                NaN   \n",
       "\n",
       "     Patient experience national comparison  \\\n",
       "4801                          Not Available   \n",
       "4802                          Not Available   \n",
       "4803                          Not Available   \n",
       "4804                          Not Available   \n",
       "4805                          Not Available   \n",
       "\n",
       "        Patient experience national comparison footnote  \\\n",
       "4801  There are too few measures or measure groups r...   \n",
       "4802  Results are not available for this reporting p...   \n",
       "4803  There are too few measures or measure groups r...   \n",
       "4804  Data are shown only for hospitals that partici...   \n",
       "4805  There are too few measures or measure groups r...   \n",
       "\n",
       "     Effectiveness of care national comparison  \\\n",
       "4801                             Not Available   \n",
       "4802                             Not Available   \n",
       "4803              Same as the national average   \n",
       "4804                             Not Available   \n",
       "4805                             Not Available   \n",
       "\n",
       "     Effectiveness of care national comparison footnote  \\\n",
       "4801  There are too few measures or measure groups r...   \n",
       "4802  Results are not available for this reporting p...   \n",
       "4803                                                NaN   \n",
       "4804  Data are shown only for hospitals that partici...   \n",
       "4805  There are too few measures or measure groups r...   \n",
       "\n",
       "     Timeliness of care national comparison  \\\n",
       "4801                          Not Available   \n",
       "4802                          Not Available   \n",
       "4803                          Not Available   \n",
       "4804                          Not Available   \n",
       "4805                          Not Available   \n",
       "\n",
       "        Timeliness of care national comparison footnote  \\\n",
       "4801  There are too few measures or measure groups r...   \n",
       "4802  Results are not available for this reporting p...   \n",
       "4803  Results are not available for this reporting p...   \n",
       "4804  Data are shown only for hospitals that partici...   \n",
       "4805  There are too few measures or measure groups r...   \n",
       "\n",
       "     Efficient use of medical imaging national comparison  \\\n",
       "4801                                      Not Available     \n",
       "4802                                      Not Available     \n",
       "4803                                      Not Available     \n",
       "4804                                      Not Available     \n",
       "4805                                      Not Available     \n",
       "\n",
       "     Efficient use of medical imaging national comparison footnote  \n",
       "4801  There are too few measures or measure groups r...             \n",
       "4802  Results are not available for this reporting p...             \n",
       "4803  Results are not available for this reporting p...             \n",
       "4804  Data are shown only for hospitals that partici...             \n",
       "4805  There are too few measures or measure groups r...             \n",
       "\n",
       "[5 rows x 28 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 2: Audit / Validate Data\n",
    "\n",
    "In next step, we will examine our dataset. Data validation includes checking for:\n",
    "\n",
    "* **Completeness**: It means, to check what percentage of data we have in our dataset compared to data available in the real world. Also we check if the critical data (like hospital names, provider ID) are not missing\n",
    "\n",
    "* **Uniqueness**: Once we have examined that our data is complete, we check that duplicate entries do not exist for columns which should be unique\n",
    "\n",
    "* **Accuracy**: We also check how much the data in our dataset reflects the data present in the real world. \n",
    "\n",
    "* **Consistency**: To check for data consistency, we check various attributes of our dataset if they align with preconceived pattern. For example, we expect Provider ID of hospitals to be integer values for all the hospitals in the United States."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#####  *Completeness*\n",
    "As per [American Hospital Association](https://aha.org/statistics/fast-facts-us-hospitals) there are 5,534 Hospitals in the United States. Most (but not all), hospitals are registered with Medicare. To check for completeness of data, we will see how many hospitals our dataset has, by checking the count of Provider IDs.\n",
    "\n",
    "As per [Medicare Gov](https://www.medicare.gov/), Massachusetts has 66, Ohio has 174, California has 350, New York has 181, Texas has 413 hospitals registered under Medicare. We will verify the completeness of data by checking count of these hospitals in our dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total Medicare enrolled hospitals in our dataset : 4806\n",
      "Total number of hospitals in the US are 5,534, hence our dataset has 86% of hospitals in the US\n",
      "Total Medicare Hospitals in our dataset for MA: 63 out of 66 (95% of actual)\n",
      "Total Medicare Hospitals in our dataset for OH: 170 out of 174 (97% of actual)\n",
      "Total Medicare Hospitals in our dataset for CA: 342 out of 350 (97% of actual)\n",
      "Total Medicare Hospitals in our dataset for NY: 171 out of 181 (94% of actual)\n",
      "Total Medicare Hospitals in our dataset for TX: 408 out of 413 (98% of actual)\n"
     ]
    }
   ],
   "source": [
    "print('Total Medicare enrolled hospitals in our dataset : '+str(df['Provider ID'].count()))\n",
    "print('Total number of hospitals in the US are 5,534, hence our dataset has '+\n",
    "      str(int((df['Provider ID'].count()/5534)*100))+\n",
    "      '% of hospitals in the US')\n",
    "#df['Hospital Ownership'].unique()\n",
    "ma_dataset = df['State'][df['State']=='MA'].count()\n",
    "oh_dataset = df['State'][df['State']=='OH'].count()\n",
    "ca_dataset = df['State'][df['State']=='CA'].count()\n",
    "ny_dataset = df['State'][df['State']=='NY'].count()\n",
    "tx_dataset = df['State'][df['State']=='TX'].count()\n",
    "print('Total Medicare Hospitals in our dataset for MA: '+\n",
    "      str(ma_dataset)+\n",
    "      ' out of 66 ('+str(int((ma_dataset/66)*100))+'% of actual)')\n",
    "print('Total Medicare Hospitals in our dataset for OH: '+\n",
    "      str(oh_dataset)+' out of 174 ('+\n",
    "      str(int((oh_dataset/174)*100))+'% of actual)')\n",
    "print('Total Medicare Hospitals in our dataset for CA: '+\n",
    "      str(ca_dataset)+' out of 350 ('+\n",
    "      str(int((ca_dataset/350)*100))+'% of actual)')\n",
    "print('Total Medicare Hospitals in our dataset for NY: '+\n",
    "      str(ny_dataset)+' out of 181 ('+\n",
    "      str(int((ny_dataset/181)*100))+'% of actual)')\n",
    "print('Total Medicare Hospitals in our dataset for TX: '+\n",
    "      str(tx_dataset)+' out of 413 ('+\n",
    "      str(int((tx_dataset/413)*100))+'% of actual)')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Based on above statistics, we can infer that our dataset has over 95% of uniqueness, which is fairly good for a dataset.\n",
    "\n",
    "#### *Uniqueness*\n",
    "\n",
    "Provider ID for each hospital is a Unique ID, hence to check uniqueness of our dataset, we check if we have any Provider ID duplicated in our dataset.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([False])"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.duplicated('Provider ID').unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Based on above output, we can infer that our dataset doesnt have any duplicate Provider ID and hence it satisfies Uniqueness property.\n",
    "\n",
    "#### *Accuracy*\n",
    "\n",
    "To check for accuracy, we matched addresses of [**SOUTHEAST ALABAMA MEDICAL CENTER**](http://www.samc.org/contact-information-home/) , [**MARSHALL MEDICAL CENTER SOUTH**](https://www.mmcenters.com/contact) and [**MIZELL MEMORIAL HOSPITAL**](http://www.mizellmh.com/getpage.php?name=contact&sub=Static) and found them to be same as the addresses in our dataset for respective hospitals. \n",
    "\n",
    "Hence we can infer that our data is fairly updated and accurate.\n",
    "\n",
    "#### *Consistency*\n",
    "\n",
    "In order to check consistency of our dataset, we check following properties:\n",
    "\n",
    "* Datatype of Provider ID must be of integer type\n",
    "* There should not be any Null values in Provider ID\n",
    "* There should not be any Null values in ZIP\n",
    "* Hospital Name must be of object type\n",
    "* Hospital Name should not have any Null values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Datatype of Provider ID is: int64\n",
      "Provider ID has any Null values? False\n",
      "ZIP has any Null values? False\n",
      "Datatype of Hospital Name is: object\n",
      "Hospital Name has any Null values? False\n"
     ]
    }
   ],
   "source": [
    "print('Datatype of Provider ID is: '+str(df['Provider ID'].dtype))\n",
    "print('Provider ID has any Null values? '+str(df['Provider ID'].isnull().values.any()))\n",
    "print('ZIP has any Null values? '+str(df['ZIP Code'].isnull().values.any()))\n",
    "print('Datatype of Hospital Name is: '+str(df['Hospital Name'].dtype))\n",
    "print('Hospital Name has any Null values? '+str(df['Hospital Name'].isnull().values.any()))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can infer from above output that our dataset is fairly consistent.\n",
    "\n",
    "### Step 3: Clean Data\n",
    "\n",
    "As can be seen from **df.head()** output, many columns contain a lot of NaN values as well as blank values. In this step we locate such columns and replace missing or bad data with a meaningful data. \n",
    "\n",
    "We will also replace some string values with float values, so that it becomes easier to compute on data later. For example, we replace 'Yes' values with 1, 'No' values with 0 and so on."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Hospital Type ## Replace 'Acute Care Hospitals'=1, 'Critical Access Hospitals'=2, 'Childrens'=3\n",
    "HType_mapping={'Acute Care Hospitals':1, 'Critical Access Hospitals':2, 'Childrens':3}\n",
    "df['Hospital Type'].replace(HType_mapping, inplace=True)\n",
    "# 'Meets criteria for meaningful use of EHRs' ## Replace Y=1, nan=NaN\n",
    "EHR_mapping={'Y':1, 'nan':np.NaN}\n",
    "df['Meets criteria for meaningful use of EHRs'].replace(EHR_mapping, inplace=True)\n",
    "\n",
    "# 'Emergency Services' ## Replace Yes=1, No=0\n",
    "ES_mapping = {'Yes': 1, 'No': 0, 'nan':np.NaN}\n",
    "df['Emergency Services'].replace(ES_mapping, inplace=True)\n",
    "\n",
    "# 'Hospital overall rating ## Replace Not Available as NaN \n",
    "df['Hospital overall rating'].replace({'Not Available':np.NaN}, inplace=True)\n",
    "\n",
    "# Same as the national average = 0, Below the national average = -1, Above the national average = 1, Not Available = NaN\n",
    "MC_mapping = {'Same as the national average' : 0,\n",
    "              'Below the national average' : -1, \n",
    "              'Above the national average' : 1, \n",
    "              'Not Available' : np.NaN }\n",
    "\n",
    "df['Mortality national comparison'].replace(MC_mapping, inplace=True)\n",
    "df['Safety of care national comparison'].replace(MC_mapping, inplace=True)\n",
    "df['Readmission national comparison'].replace(MC_mapping, inplace=True)\n",
    "df['Patient experience national comparison'].replace(MC_mapping, inplace=True)\n",
    "df['Effectiveness of care national comparison'].replace(MC_mapping, inplace=True)\n",
    "df['Timeliness of care national comparison'].replace(MC_mapping, inplace=True)\n",
    "df['Efficient use of medical imaging national comparison'].replace(MC_mapping, inplace=True)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 4: Reformat Data\n",
    "\n",
    "We need to reformat our data from this csv file to fit into our database. We dont need all the columns from this csv, also not all of these columns belong to single table. For instance, information like Address, City, Zip will needed to be inserted into 'address' table, while information like Provider ID, Hospital Name, Hospital Type will needed to be inserted into 'hospital' table. Therefore, it becomes important that our data is reformated from csv as per our database design.\n",
    "\n",
    "We need following columns for respective tables:\n",
    "\n",
    "* **hospital**: Provider ID, Hospital Name, Hospital Type, Phone Number, Hospital Ownership\n",
    "* **address**: Address, City, State, Zip, County Name\n",
    "* **ownership_type**: Hospital Ownership\n",
    "* **hospital_address**: hospital_id, address_id\n",
    "* **hospital_ownership**: ownership_id, hospital_id\n",
    "* **hospital_service_comparison**: Provider Id, Emergency Services, Meets criteria for meaningful use of EHRs, Hospital overall rating, Mortality national comparison, Safety of care national comparison, Readmission national comparison, Patient experience national comparison, Effectiveness of care national comparison, Timeliness of care national comparison, Efficient use of medical imaging national comparison"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Hospital Ownership</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Government - Hospital District or Authority</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Government - Hospital District or Authority</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Government - Hospital District or Authority</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Voluntary non-profit - Private</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Proprietary</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                            Hospital Ownership\n",
       "0  Government - Hospital District or Authority\n",
       "1  Government - Hospital District or Authority\n",
       "2  Government - Hospital District or Authority\n",
       "3               Voluntary non-profit - Private\n",
       "4                                  Proprietary"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hospital = df.filter(['Provider ID','Hospital Name','Hospital Type', 'Phone Number', 'Hospital overall rating'], axis=1)\n",
    "address = df.filter(['Address', 'City', 'State', 'ZIP Code', 'County Name'], axis=1)\n",
    "ownership_type = df.filter(['Hospital Ownership'], axis=1)\n",
    "ownership_type.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 5: Insert Data in Database"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Script to insert the complete dataset is available at [Insert Data](insertData.ipynb)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 6: Data Analysis\n",
    "\n",
    "Finally, once we have all our cleaned data inserted in the database, we can run the analysis and infer questions from it.\n",
    "\n",
    "#### **Case 1**\n",
    "First we would like to see the distribution of Medicare registered hospitals in each State."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "def create_connection(db_file):\n",
    "    \"\"\" create a database connection to the MySQL database\n",
    "        specified by db_file\n",
    "    :param db_file: database file\n",
    "    :return: Connection object or None\n",
    "    \"\"\"\n",
    "    try:\n",
    "        connection = pymysql.connect(host='localhost',\n",
    "                             user='root',\n",
    "                             password='root',\n",
    "                             db=db_file)\n",
    "        return connection\n",
    "    except pymysql.InternalError as e:\n",
    "        return False"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  State  Count\n",
      "0    TX    408\n",
      "1    CA    342\n",
      "2    FL    188\n",
      "3    IL    180\n",
      "4    NY    171\n",
      "  \n",
      "   State  Count\n",
      "51    DE      7\n",
      "52    VI      2\n",
      "53    GU      2\n",
      "54    AS      1\n",
      "55    MP      1\n"
     ]
    }
   ],
   "source": [
    "conn = create_connection(database)\n",
    "if conn is not False:    \n",
    "    cur = conn.cursor()\n",
    "    query = \"\"\"SELECT a.state AS `State`, Count(h.provider_id) as Count\n",
    "                    FROM hospitals h \n",
    "                    JOIN hospital_address ha ON ha.hospital_id = h.provider_id\n",
    "                    JOIN address a ON a.id = ha.address_id\n",
    "                    GROUP BY a.state\n",
    "                    ORDER BY count DESC\n",
    "                    \"\"\"\n",
    "    results = pd.read_sql_query(query, conn)\n",
    "\n",
    "conn.commit()\n",
    "cur.close()\n",
    "conn.close()\n",
    "\n",
    "print(results.head())\n",
    "print('  ')\n",
    "print(results.tail())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Infereing from **Case 1** we see that Texas has most number of Medicare registered hospitals followed by California, Florida, Illinois and New York. Northern Mariana Islands, American Samoa and Dover have the least number of Medicare registered hospitals.\n",
    "\n",
    "#### **Case 2** \n",
    "In this use-case we see the distribution of different *Hospital Owners* with *Hospital rating* more than 3 (good)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                                Ownership Type  Count\n",
      "0               Voluntary non-profit - Private    736\n",
      "1                 Voluntary non-profit - Other    181\n",
      "2                                  Proprietary    175\n",
      "3                Voluntary non-profit - Church    150\n",
      "4  Government - Hospital District or Authority    117\n",
      "5                           Government - Local     93\n",
      "6                                    Physician     21\n",
      "7                           Government - State     13\n",
      "8                         Government - Federal      5\n",
      "9                                       Tribal      1\n"
     ]
    }
   ],
   "source": [
    "conn = create_connection(database)\n",
    "if conn is not None:    \n",
    "    cur = conn.cursor()\n",
    "    query = \"\"\"SELECT o.ow_type AS `Ownership Type`, Count(h.provider_id) as Count\n",
    "                        FROM hospitals h\n",
    "                        JOIN hospital_ownership ho ON ho.hospital_id = h.provider_id\n",
    "                        JOIN ownership_type o ON o.id = ho.ownership_id\n",
    "                        WHERE h.rating > 3\n",
    "                        GROUP BY o.ow_type\n",
    "                        ORDER BY count DESC\n",
    "                        \"\"\"\n",
    "    results = pd.read_sql_query(query, conn)\n",
    "\n",
    "conn.commit()\n",
    "cur.close()\n",
    "conn.close()\n",
    "\n",
    "print(results)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We observe that non-Profit Private hospitals have most number of **Good** ratings, while Federal Government dont have many **GOOD** ratings for their hospitals.\n",
    "\n",
    "#### **Case 3** \n",
    "In this Use-case we see which of the *High rated hospitals* (ratings more than 3) have *Readmission* rate above *National Average* . Which means, how frequent are readmissions for high rated hospitals.\n",
    "\n",
    "Readmission measure focuses on whether patients who were discharged from a hospitalization were hospitalized again within 30 days."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                                   Hospital Name State\n",
      "0                    LAKELAND COMMUNITY HOSPITAL    AL\n",
      "1                    VERDE VALLEY MEDICAL CENTER    AZ\n",
      "2                  BANNER BOSWELL MEDICAL CENTER    AZ\n",
      "3      SUMMIT HEALTHCARE REGIONAL MEDICAL CENTER    AZ\n",
      "4                           MAYO CLINIC HOSPITAL    AZ\n",
      "5                          BANNER HEART HOSPITAL    AZ\n",
      "6                   MERCY GILBERT MEDICAL CENTER    AZ\n",
      "7        SCOTTSDALE THOMPSON PEAK MEDICAL CENTER    AZ\n",
      "8                  CROSSRIDGE COMMUNITY HOSPITAL    AR\n",
      "9                 MILLS-PENINSULA MEDICAL CENTER    CA\n",
      "10                       SHARP MEMORIAL HOSPITAL    CA\n",
      "11  COMMUNITY HOSPITAL OF THE MONTEREY PENINSULA    CA\n",
      "12                              SEQUOIA HOSPITAL    CA\n",
      "13              SHARP CHULA VISTA MEDICAL CENTER    CA\n",
      "14             METHODIST HOSPITAL OF SOUTHERN CA    CA\n"
     ]
    }
   ],
   "source": [
    "conn = create_connection(database)\n",
    "if conn is not None:    \n",
    "    cur = conn.cursor()\n",
    "    query = \"\"\"SELECT h.name AS `Hospital Name`, a.state AS `State`\n",
    "                            FROM hospitals h\n",
    "                            JOIN hospital_comparison hc ON hc.hospital_id = h.provider_id\n",
    "                            JOIN hospital_address ha ON ha.hospital_id = h.provider_id\n",
    "                            JOIN address a ON a.id = ha.address_id\n",
    "                            WHERE hc.readmission = 1 AND h.rating > 3\n",
    "                            ORDER BY h.rating DESC\n",
    "                            LIMIT 15\n",
    "                            \"\"\"\n",
    "    \n",
    "    results = pd.read_sql_query(query, conn)\n",
    "\n",
    "conn.commit()\n",
    "cur.close()\n",
    "conn.close()\n",
    "\n",
    "print(results)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We observe that a lot of Hospitals in California and Arizona, although have high overall ratings, but the rate at which patients get hospitalied within 30-Days is Above National Average. Which means, these hospitals perform well on other quality measures, however, patients tend to get hospitalized back more often.\n",
    "\n",
    "#### **Case 4** \n",
    "In this Use-case we focus on *Low rated* Hospitals, and would like to see which of them have *mortality* rate *higher than national average*."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                             Hospital Name                  Ownership Type\n",
      "0                        OROVILLE HOSPITAL  Voluntary non-profit - Private\n",
      "1      REGIONAL MEDICAL CENTER OF SAN JOSE            Government - Federal\n",
      "2   CITRUS VALLEY MEDICAL CENTER-IC CAMPUS                     Proprietary\n",
      "3                      BRIDGEPORT HOSPITAL  Voluntary non-profit - Private\n",
      "4           MEMORIAL HOSPITAL JACKSONVILLE                     Proprietary\n",
      "5     PRESENCE SAINT JOSEPH MEDICAL CENTER   Voluntary non-profit - Church\n",
      "6        MERCY HOSPITAL AND MEDICAL CENTER  Voluntary non-profit - Private\n",
      "7   JEWISH HOSPITAL & ST MARY'S HEALTHCARE  Voluntary non-profit - Private\n",
      "8        UMASS MEMORIAL MEDICAL CENTER INC  Voluntary non-profit - Private\n",
      "9                     SINAI-GRACE HOSPITAL                     Proprietary\n",
      "10                     HENRY FORD HOSPITAL  Voluntary non-profit - Private\n",
      "11       SUMMERLIN HOSPITAL MEDICAL CENTER                     Proprietary\n",
      "12            EAST ORANGE GENERAL HOSPITAL  Voluntary non-profit - Private\n",
      "13               MONTEFIORE MEDICAL CENTER  Voluntary non-profit - Private\n",
      "14      GOOD SAMARITAN HOSPITAL OF SUFFERN   Voluntary non-profit - Church\n"
     ]
    }
   ],
   "source": [
    "conn = create_connection(database)\n",
    "if conn is not None:    \n",
    "    cur = conn.cursor()\n",
    "    query = \"\"\"SELECT h.name AS `Hospital Name`, o.ow_type AS `Ownership Type`\n",
    "                            FROM hospitals h\n",
    "                            JOIN hospital_comparison hc ON hc.hospital_id = h.provider_id\n",
    "                            JOIN hospital_ownership ho ON ho.hospital_id = h.provider_id\n",
    "                            JOIN ownership_type o ON  o.id = ho.ownership_id\n",
    "                            WHERE hc.mortality = 1 AND h.rating < 3\n",
    "                            ORDER BY h.rating ASC\n",
    "                            LIMIT 15\n",
    "                            \"\"\"\n",
    "    results = pd.read_sql_query(query, conn)\n",
    "\n",
    "conn.commit()\n",
    "cur.close()\n",
    "conn.close()\n",
    "\n",
    "print(results)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We observe that a lot of *Voluntary non-Profit Private* hospitals have a very **low rating** and **high mortality rate**. \n",
    "\n",
    "#### **Case 5**\n",
    "In our last case, we would like to see the state-wise distribution of high (above national average) mortality rate."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  State  Count\n",
      "0    CA     54\n",
      "1    IL     33\n",
      "2    FL     31\n",
      "3    MA     28\n",
      "4    OH     27\n",
      "5    NY     26\n",
      "6    TX     25\n",
      "7    MI     19\n",
      "8    NJ     17\n",
      "9    PA     16\n"
     ]
    }
   ],
   "source": [
    "conn = create_connection(database)\n",
    "if conn is not None:    \n",
    "    cur = conn.cursor()\n",
    "    query = \"\"\"SELECT a.state AS `State`, Count(h.provider_id) as Count\n",
    "                        FROM hospitals h\n",
    "                        JOIN hospital_comparison hc ON hc.hospital_id = h.provider_id\n",
    "                        JOIN hospital_address ha ON ha.hospital_id = h.provider_id\n",
    "                        JOIN address a ON a.id = ha.address_id\n",
    "                        WHERE hc.mortality = 1\n",
    "                        GROUP BY a.state\n",
    "                        ORDER BY Count DESC\n",
    "                        LIMIT 10\n",
    "                        \"\"\"\n",
    "    \n",
    "    results = pd.read_sql_query(query, conn)\n",
    "\n",
    "conn.commit()\n",
    "cur.close()\n",
    "conn.close()\n",
    "\n",
    "print(results)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We observe that many Medicare registered hospitals in states like California, Florida, Massachusetts have Mortality Rate higher than National Average. This can be a very important indicator for Hospitals, either Private or Federal. It could mean that more patients in CA, FL and MA visit hospitals in critical conditions. Hence hospitals in these states must recruit more nurses and care-takers for emergency services."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python (healthCC)",
   "language": "python",
   "name": "healthcc"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}