Hospital_Info.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "04b9cd36",
   "metadata": {},
   "source": [
    "# Generate Hospital Information dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "bd2f4650",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import warnings\n",
    "from IPython.utils import io\n",
    "import sys\n",
    "warnings.filterwarnings('ignore')\n",
    "pd.set_option('display.max_columns', None)\n",
    "pd.set_option('display.max_rows', None)\n",
    "\n",
    "main_dir = '/Users/kenlocey/Desktop/Rush/CMS_HospitalArchives/'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6c300852",
   "metadata": {},
   "source": [
    "## Custom Functions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "4d72d091",
   "metadata": {},
   "outputs": [],
   "source": [
    "def check_lists(lists):\n",
    "    for i, ls in enumerate(lists):\n",
    "        for i2, ls2 in enumerate(lists):\n",
    "            for i3 in ls:\n",
    "                if i3 not in ls2:\n",
    "                    print('\\n')\n",
    "                    print(i3 + ': NOT FOUND IN')\n",
    "                    print(ls2)\n",
    "                    sys.exit()\n",
    "                        \n",
    "                        \n",
    "def curate(df):\n",
    "\n",
    "    try:\n",
    "        df = df[df['Facility ID'] != np.nan]\n",
    "        df['Facility ID'] = df['Facility ID'].values.astype(str)\n",
    "        \n",
    "        ids = df['Facility ID'].tolist()\n",
    "        ids2 = []\n",
    "        for i in ids:\n",
    "            if len(i) < 6:\n",
    "                i = '0' + i\n",
    "            ids2.append(i)\n",
    "        df['Facility ID'] = ids2\n",
    "        \n",
    "    except:\n",
    "        pass\n",
    "    try:\n",
    "        df = df[df['Facility Name'] != np.nan]\n",
    "    except:\n",
    "        pass\n",
    "    \n",
    "    for c in list(df):    \n",
    "        try:\n",
    "            df[c] = df[c].str.replace(\"\\t\",\"\")\n",
    "        except:\n",
    "            pass\n",
    "\n",
    "    return df\n",
    "\n",
    "\n",
    "def rename_and_fill(df):\n",
    "    \n",
    "    cols1 = ['Provider ID', 'Hospital Name', 'Provider Number', 'County Name', \n",
    "             'Mortality national comparison', 'Mortality national comparison footnote',\n",
    "             'Safety of care national comparison', 'Safety of care national comparison footnote',\n",
    "             'Readmission national comparison', 'Readmission national comparison footnote',\n",
    "             'Patient experience national comparison',\n",
    "             'Patient experience national comparison footnote',\n",
    "             'Effectiveness of care national comparison',\n",
    "             'Effectiveness of care national comparison footnote',\n",
    "             'Timeliness of care national comparison',\n",
    "             'Timeliness of care national comparison footnote',\n",
    "             'Efficient use of medical imaging national comparison',\n",
    "             'Efficient use of medical imaging national comparison footnote',\n",
    "             'Meets criteria for meaningful use of EHRs',\n",
    "             ]\n",
    "    \n",
    "    \n",
    "    cols2 = ['Facility ID', 'Facility Name', 'Facility ID', 'County',\n",
    "             'Mortality National Comparison', 'Mortality National Comparison Footnote',\n",
    "             'Safety of Care National Comparison', 'Safety of Care National Comparison Footnote',\n",
    "             'Readmission National Comparison', 'Readmission National Comparison Footnote',\n",
    "             'Patient Experience National Comparison',\n",
    "             'Patient Experience National Comparison Footnote',\n",
    "             'Effectiveness of Care National Comparison',\n",
    "             'Effectiveness of Care National Comparison Footnote',\n",
    "             'Timeliness of Care National Comparison',\n",
    "             'Timeliness of Care National Comparison Footnote',\n",
    "             'Efficient Use of Medical Imaging National Comparison',\n",
    "             'Efficient Use of Medical Imaging National Comparison Footnote',\n",
    "             'Meets Criteria for Meaningful Use of EHRs',\n",
    "             ]\n",
    "    \n",
    "    for i, col in enumerate(cols1):\n",
    "        if col in list(df):\n",
    "            df.rename(columns={col: cols2[i]}, inplace=True)\n",
    "            \n",
    "            \n",
    "    cols = ['MORT Group Measure Count', 'Count of Facility MORT Measures',\n",
    "            'Count of MORT Measures Better', 'Count of Facility MORT Measures',\n",
    "            'Count of MORT Measures No Different', 'Count of MORT Measures Worse',\n",
    "            'MORT Group Footnote', 'Safety Group Measure Count',\n",
    "            'Count of Facility Safety Measures', 'Count of Safety Measures Better',\n",
    "            'Count of Safety Measures Worse', 'Count of Safety Measures No Different',\n",
    "            'Safety Group Footnote', 'READM Group Measure Count',\n",
    "            'Count of Facility READM Measures', 'Count of READM Measures Better',\n",
    "            'Count of READM Measures Worse', 'Count of READM Measures No Different',\n",
    "            'READM Group Footnote',\n",
    "            'Pt Exp Group Measure Count', 'Count of Facility Pt Exp Measures',\n",
    "            'Pt Exp Group Footnote', 'TE Group Measure Count', 'TE Group Measure Count',\n",
    "            'Count of Facility TE Measures', 'TE Group Footnote',\n",
    "            'Meets criteria for promoting interoperability of EHRs',\n",
    "            'Hospital overall rating', 'Hospital overall rating footnote', \n",
    "            'Address', 'Mortality National Comparison',\n",
    "            'Mortality National Comparison Footnote', 'Safety of Care National Comparison',\n",
    "            'Safety of Care National Comparison Footnote',\n",
    "            'Readmission National Comparison',\n",
    "            'Readmission National Comparison Footnote',\n",
    "            'Patient Experience National Comparison',\n",
    "            'Patient Experience National Comparison Footnote',\n",
    "            'Effectiveness of Care National Comparison',\n",
    "            'Effectiveness of Care National Comparison Footnote',\n",
    "            'Timeliness of Care National Comparison',\n",
    "            'Timeliness of Care National Comparison Footnote',\n",
    "            'Efficient Use of Medical Imaging National Comparison',\n",
    "            'Efficient Use of Medical Imaging National Comparison Footnote',\n",
    "            'Meets Criteria for Meaningful Use of EHRs',\n",
    "            'Address 1',\n",
    "            'Address 2',\n",
    "            'Address 3',\n",
    "            ]\n",
    "    \n",
    "    for col in cols:\n",
    "        if col not in list(df):\n",
    "            df[col] = float('NaN')\n",
    "    \n",
    "    l = list(df)\n",
    "    l = list(set([x for x in l if l.count(x) > 1]))\n",
    "    if len(l) > 0:\n",
    "        print('duplicates:', l)\n",
    "        sys.exit()\n",
    "        \n",
    "    return df\n",
    "\n",
    "\n",
    "def process2(df, lists, yr, mo):\n",
    "    df = rename_and_fill(df)\n",
    "    df = curate(df)\n",
    "    lists.append(list(df))\n",
    "    df['file_month'] = [mo]* df.shape[0]\n",
    "    df['file_year'] = [yr]* df.shape[0]\n",
    "    df = df.reindex(sorted(df.columns), axis=1)\n",
    "    return df, lists\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4730fbe2",
   "metadata": {},
   "source": [
    "## Load Files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "140afde6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2023/hospitals_01_2023/Hospital_General_Information.csv :  (rows, columns) = (5317, 38)\n",
      "2023/hospitals_04_2023/Hospital_General_Information.csv :  (rows, columns) = (5317, 38)\n",
      "2022/hospitals_01_2022/Hospital_General_Information.csv :  (rows, columns) = (5306, 38)\n",
      "2022/hospitals_04_2022/Hospital_General_Information.csv :  (rows, columns) = (5306, 38)\n",
      "2022/hospitals_07_2022/Hospital_General_Information.csv :  (rows, columns) = (5299, 38)\n",
      "2021/hospitals_01_2021/Hospital_General_Information.csv :  (rows, columns) = (5324, 28)\n",
      "2021/hospitals_03_2021/Hospital_General_Information.csv :  (rows, columns) = (5324, 28)\n",
      "2021/hospitals_04_2021/Hospital_General_Information.csv :  (rows, columns) = (5382, 38)\n",
      "2021/hospitals_07_2021/Hospital_General_Information.csv :  (rows, columns) = (5336, 38)\n",
      "2021/hospitals_10_2021/Hospital_General_Information.csv :  (rows, columns) = (5325, 38)\n",
      "2020/hospitals_archive_10_2020/Hospital_General_Information.csv :  (rows, columns) = (5314, 28)\n",
      "2020/hospitals_archive_07_2020/Hospital_General_Information.csv :  (rows, columns) = (5320, 28)\n",
      "2020/HOSArchive_Revised_Flatfiles_20200422/Hospital General Information.csv :  (rows, columns) = (5326, 28)\n",
      "2020/HOSArchive_Revised_Flatfiles_20200129/Hospital General Information.csv :  (rows, columns) = (5339, 28)\n",
      "2019/HOSArchive_Revised_Flatfiles_20191030/Hospital General Information.csv :  (rows, columns) = (5344, 28)\n",
      "2019/HOSArchive_Revised_Flatfiles_20190702/Hospital General Information.csv :  (rows, columns) = (5334, 28)\n",
      "2019/HOSArchive_Revised_FlatFiles_20190424/Hospital General Information.csv :  (rows, columns) = (4772, 28)\n",
      "2019/HOSArchive_Revised_Flatfiles_20190321/Hospital General Information.csv :  (rows, columns) = (4784, 28)\n",
      "2018/HOSArchive_Revised_FlatFiles_20181031/Hospital General Information.csv :  (rows, columns) = (4793, 28)\n",
      "2018/HOSArchive_Revised_FlatFiles_20180725/Hospital General Information.csv :  (rows, columns) = (4797, 28)\n",
      "2018/HOSArchive_Revised_FlatFiles_20180523/Hospital General Information.csv :  (rows, columns) = (4798, 28)\n",
      "2018/HOSArchive_Revised_FlatFiles_20180126/Hospital General Information.csv :  (rows, columns) = (4806, 28)\n",
      "2017/HOSArchive_Revised_FlatFiles_20171024/Hospital General Information.csv :  (rows, columns) = (4812, 28)\n",
      "2017/HOSArchive_Revised_FlatFiles_20170726/Hospital General Information.csv :  (rows, columns) = (4812, 28)\n",
      "2017/HOSArchive_Revised_Flatfiles_20170428/Hospital General Information.csv :  (rows, columns) = (4805, 28)\n",
      "2016/HOSArchive_Revised_Flatfiles_20161219/Hospital General Information.csv :  (rows, columns) = (4807, 28)\n",
      "2016/Hospital_Revised_FlatFiles_20161110/Hospital General Information.csv :  (rows, columns) = (4818, 28)\n",
      "2016/HOSArchive_Revised_FlatFiles_20160810/Hospital General Information.csv :  (rows, columns) = (4788, 28)\n",
      "2016/HOSArchive_Revised_FlatFiles_20160504/Hospital General Information.csv :  (rows, columns) = (4799, 11)\n",
      "2015/HOSArchive_Revised_FlatFiles_20151210/Hospital General Information.csv :  (rows, columns) = (4805, 11)\n",
      "2015/HOSArchive_Revised_FlatFiles_20151008/Hospital General Information.csv :  (rows, columns) = (4803, 11)\n",
      "2015/HOSArchive_Revised_FlatFiles_20150716/Hospital General Information.csv :  (rows, columns) = (4824, 11)\n",
      "2015/HOSArchive_Revised_Flatfiles_20150506/Hospital General Information.csv :  (rows, columns) = (4813, 11)\n",
      "2015/HOSArchive_Revised_Flatfiles_20150416/Hospital General Information.csv :  (rows, columns) = (4813, 11)\n",
      "2015/HOSArchive_Revised_Flatfiles_20150122/Hospital General Information.csv :  (rows, columns) = (4783, 11)\n",
      "2014/HOSArchive_Revised_Flatfiles_20141218/Hospital General Information.csv :  (rows, columns) = (4783, 11)\n",
      "2014/HOSArchive_Revised_Flatfiles_20141023/Hospital General Information.csv :  (rows, columns) = (4861, 11)\n",
      "2014/HOSArchive_Revised_Flatfiles_20140717/Hospital General Information.csv :  (rows, columns) = (4861, 11)\n",
      "2014/HOSArchive_Revised_Flatfiles_20140417/Hospital_Data.csv :  (rows, columns) = (4860, 13)\n",
      "2014/HOSArchive_Revised_Flatfiles_20140101/Hospital_Data.csv :  (rows, columns) = (4859, 13)\n",
      "2013/HOSArchive_Revised_Flatfiles_20131001/Hospital_Data.csv :  (rows, columns) = (4822, 13)\n",
      "2013/HOSArchive_Revised_Flatfiles_20130701/Hospital_Data.csv :  (rows, columns) = (4830, 13)\n",
      "2013/HOSArchive_Revised_Flatfiles_20130401/Hospital_Data.csv :  (rows, columns) = (4838, 13)\n",
      "hos_df.shape: (215159, 58)\n"
     ]
    }
   ],
   "source": [
    "#########################################################################################\n",
    "#########################  COMPLICATIONS & DEATHS FILES  ################################\n",
    "#########################################################################################\n",
    "\n",
    "df_list = []\n",
    "lists = []\n",
    "\n",
    "yrs = ['2023', '2023',\n",
    "       '2022', '2022', '2022',\n",
    "       '2021','2021','2021', '2021', '2021',\n",
    "       '2020', '2020', '2020', '2020', \n",
    "       '2019', '2019', '2019', '2019', \n",
    "       '2018', '2018', '2018', '2018',\n",
    "       '2017', '2017', '2017',\n",
    "       '2016', '2016', '2016', '2016',\n",
    "       '2015', '2015', '2015', '2015', '2015', '2015',\n",
    "       '2014', '2014', '2014', '2014', '2014',\n",
    "       '2013', '2013', '2013',\n",
    "       ]\n",
    "\n",
    "mos = ['01', '04',\n",
    "       '01', '04', '07',\n",
    "       '01', '03', '04', '07', '10', \n",
    "       '10', '07', '04', '01', \n",
    "       '10', '07', '04', '03', \n",
    "       '10', '07', '05', '01',\n",
    "       '10', '07', '04',\n",
    "       '12', '11', '08', '05',\n",
    "       '12', '10', '07', '05', '04', '01',\n",
    "       '12', '10', '07', '04', '01',\n",
    "       '10', '07' ,'04',\n",
    "       ]\n",
    "\n",
    "subdirs = ['2023/hospitals_01_2023/Hospital_General_Information.csv', \n",
    "           '2023/hospitals_04_2023/Hospital_General_Information.csv',\n",
    "           \n",
    "           '2022/hospitals_01_2022/Hospital_General_Information.csv', \n",
    "           '2022/hospitals_04_2022/Hospital_General_Information.csv',\n",
    "           '2022/hospitals_07_2022/Hospital_General_Information.csv',\n",
    "           \n",
    "           '2021/hospitals_01_2021/Hospital_General_Information.csv',\n",
    "           '2021/hospitals_03_2021/Hospital_General_Information.csv',\n",
    "           '2021/hospitals_04_2021/Hospital_General_Information.csv',\n",
    "           '2021/hospitals_07_2021/Hospital_General_Information.csv',\n",
    "           '2021/hospitals_10_2021/Hospital_General_Information.csv',\n",
    "           \n",
    "           '2020/hospitals_archive_10_2020/Hospital_General_Information.csv',\n",
    "           '2020/hospitals_archive_07_2020/Hospital_General_Information.csv',\n",
    "           '2020/HOSArchive_Revised_Flatfiles_20200422/Hospital General Information.csv',\n",
    "           '2020/HOSArchive_Revised_Flatfiles_20200129/Hospital General Information.csv',\n",
    "           \n",
    "           '2019/HOSArchive_Revised_Flatfiles_20191030/Hospital General Information.csv',\n",
    "           '2019/HOSArchive_Revised_Flatfiles_20190702/Hospital General Information.csv',\n",
    "           '2019/HOSArchive_Revised_FlatFiles_20190424/Hospital General Information.csv',\n",
    "           '2019/HOSArchive_Revised_Flatfiles_20190321/Hospital General Information.csv',\n",
    "           \n",
    "           '2018/HOSArchive_Revised_FlatFiles_20181031/Hospital General Information.csv',\n",
    "           '2018/HOSArchive_Revised_FlatFiles_20180725/Hospital General Information.csv', \n",
    "           '2018/HOSArchive_Revised_FlatFiles_20180523/Hospital General Information.csv',\n",
    "           '2018/HOSArchive_Revised_FlatFiles_20180126/Hospital General Information.csv',\n",
    "           \n",
    "           '2017/HOSArchive_Revised_FlatFiles_20171024/Hospital General Information.csv',\n",
    "           '2017/HOSArchive_Revised_FlatFiles_20170726/Hospital General Information.csv', \n",
    "           '2017/HOSArchive_Revised_Flatfiles_20170428/Hospital General Information.csv', \n",
    "           \n",
    "           '2016/HOSArchive_Revised_Flatfiles_20161219/Hospital General Information.csv',\n",
    "           '2016/Hospital_Revised_FlatFiles_20161110/Hospital General Information.csv', \n",
    "           '2016/HOSArchive_Revised_FlatFiles_20160810/Hospital General Information.csv',\n",
    "           '2016/HOSArchive_Revised_FlatFiles_20160504/Hospital General Information.csv',\n",
    "           \n",
    "           '2015/HOSArchive_Revised_FlatFiles_20151210/Hospital General Information.csv',\n",
    "           '2015/HOSArchive_Revised_FlatFiles_20151008/Hospital General Information.csv',\n",
    "           '2015/HOSArchive_Revised_FlatFiles_20150716/Hospital General Information.csv',\n",
    "           '2015/HOSArchive_Revised_Flatfiles_20150506/Hospital General Information.csv',\n",
    "           '2015/HOSArchive_Revised_Flatfiles_20150416/Hospital General Information.csv',\n",
    "           '2015/HOSArchive_Revised_Flatfiles_20150122/Hospital General Information.csv',\n",
    "           \n",
    "           '2014/HOSArchive_Revised_Flatfiles_20141218/Hospital General Information.csv',\n",
    "           '2014/HOSArchive_Revised_Flatfiles_20141023/Hospital General Information.csv',\n",
    "           '2014/HOSArchive_Revised_Flatfiles_20140717/Hospital General Information.csv',\n",
    "           '2014/HOSArchive_Revised_Flatfiles_20140417/Hospital_Data.csv',\n",
    "           '2014/HOSArchive_Revised_Flatfiles_20140101/Hospital_Data.csv',\n",
    "           \n",
    "           '2013/HOSArchive_Revised_Flatfiles_20131001/Hospital_Data.csv', \n",
    "           '2013/HOSArchive_Revised_Flatfiles_20130701/Hospital_Data.csv',\n",
    "           '2013/HOSArchive_Revised_Flatfiles_20130401/Hospital_Data.csv',\n",
    "           \n",
    "           ]\n",
    "\n",
    "for i, subdir in enumerate(subdirs):\n",
    "    with io.capture_output() as captured: df = pd.read_csv(main_dir + subdir, encoding = \"ISO-8859-1\")\n",
    "    print(subdir + ' :  (rows, columns) =', df.shape)\n",
    "    df, lists = process2(df, lists, yrs[i], mos[i])\n",
    "    df_list.append(df)\n",
    "\n",
    "check_lists(lists)\n",
    "subdir = 'Hospital_Info/CombinedFiles_Hospital_Info'\n",
    "hos_df = pd.concat(df_list)\n",
    "print('hos_df.shape:', hos_df.shape)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "a98fe03f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(215159, 22)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Facility ID</th>\n",
       "      <th>file_month</th>\n",
       "      <th>file_year</th>\n",
       "      <th>Facility Name</th>\n",
       "      <th>Address</th>\n",
       "      <th>City</th>\n",
       "      <th>County</th>\n",
       "      <th>State</th>\n",
       "      <th>ZIP Code</th>\n",
       "      <th>Effectiveness of Care National Comparison</th>\n",
       "      <th>Efficient Use of Medical Imaging National Comparison</th>\n",
       "      <th>Emergency Services</th>\n",
       "      <th>Hospital Ownership</th>\n",
       "      <th>Hospital Type</th>\n",
       "      <th>Hospital overall rating</th>\n",
       "      <th>Meets Criteria for Meaningful Use of EHRs</th>\n",
       "      <th>Meets criteria for promoting interoperability of EHRs</th>\n",
       "      <th>Mortality National Comparison</th>\n",
       "      <th>Patient Experience National Comparison</th>\n",
       "      <th>Readmission National Comparison</th>\n",
       "      <th>Safety of Care National Comparison</th>\n",
       "      <th>Timeliness of Care National Comparison</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>010001</td>\n",
       "      <td>01</td>\n",
       "      <td>2023</td>\n",
       "      <td>SOUTHEAST HEALTH MEDICAL CENTER</td>\n",
       "      <td>1108 ROSS CLARK CIRCLE</td>\n",
       "      <td>DOTHAN</td>\n",
       "      <td>HOUSTON</td>\n",
       "      <td>AL</td>\n",
       "      <td>36301</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Government - Hospital District or Authority</td>\n",
       "      <td>Acute Care Hospitals</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Y</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>010005</td>\n",
       "      <td>01</td>\n",
       "      <td>2023</td>\n",
       "      <td>MARSHALL MEDICAL CENTERS</td>\n",
       "      <td>2505 U S HIGHWAY 431 NORTH</td>\n",
       "      <td>BOAZ</td>\n",
       "      <td>MARSHALL</td>\n",
       "      <td>AL</td>\n",
       "      <td>35957</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Government - Hospital District or Authority</td>\n",
       "      <td>Acute Care Hospitals</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Y</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>010006</td>\n",
       "      <td>01</td>\n",
       "      <td>2023</td>\n",
       "      <td>NORTH ALABAMA MEDICAL CENTER</td>\n",
       "      <td>1701 VETERANS DRIVE</td>\n",
       "      <td>FLORENCE</td>\n",
       "      <td>LAUDERDALE</td>\n",
       "      <td>AL</td>\n",
       "      <td>35630</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Proprietary</td>\n",
       "      <td>Acute Care Hospitals</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Y</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>010007</td>\n",
       "      <td>01</td>\n",
       "      <td>2023</td>\n",
       "      <td>MIZELL MEMORIAL HOSPITAL</td>\n",
       "      <td>702 N MAIN ST</td>\n",
       "      <td>OPP</td>\n",
       "      <td>COVINGTON</td>\n",
       "      <td>AL</td>\n",
       "      <td>36467</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Voluntary non-profit - Private</td>\n",
       "      <td>Acute Care Hospitals</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Y</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>010008</td>\n",
       "      <td>01</td>\n",
       "      <td>2023</td>\n",
       "      <td>CRENSHAW COMMUNITY HOSPITAL</td>\n",
       "      <td>101 HOSPITAL CIRCLE</td>\n",
       "      <td>LUVERNE</td>\n",
       "      <td>CRENSHAW</td>\n",
       "      <td>AL</td>\n",
       "      <td>36049</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Proprietary</td>\n",
       "      <td>Acute Care Hospitals</td>\n",
       "      <td>Not Available</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Y</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Facility ID file_month file_year                    Facility Name  \\\n",
       "0      010001         01      2023  SOUTHEAST HEALTH MEDICAL CENTER   \n",
       "1      010005         01      2023         MARSHALL MEDICAL CENTERS   \n",
       "2      010006         01      2023     NORTH ALABAMA MEDICAL CENTER   \n",
       "3      010007         01      2023         MIZELL MEMORIAL HOSPITAL   \n",
       "4      010008         01      2023      CRENSHAW COMMUNITY HOSPITAL   \n",
       "\n",
       "                      Address      City      County State  ZIP Code  \\\n",
       "0      1108 ROSS CLARK CIRCLE    DOTHAN     HOUSTON    AL     36301   \n",
       "1  2505 U S HIGHWAY 431 NORTH      BOAZ    MARSHALL    AL     35957   \n",
       "2         1701 VETERANS DRIVE  FLORENCE  LAUDERDALE    AL     35630   \n",
       "3               702 N MAIN ST       OPP   COVINGTON    AL     36467   \n",
       "4         101 HOSPITAL CIRCLE   LUVERNE    CRENSHAW    AL     36049   \n",
       "\n",
       "  Effectiveness of Care National Comparison  \\\n",
       "0                                       NaN   \n",
       "1                                       NaN   \n",
       "2                                       NaN   \n",
       "3                                       NaN   \n",
       "4                                       NaN   \n",
       "\n",
       "  Efficient Use of Medical Imaging National Comparison Emergency Services  \\\n",
       "0                                                NaN                  Yes   \n",
       "1                                                NaN                  Yes   \n",
       "2                                                NaN                  Yes   \n",
       "3                                                NaN                  Yes   \n",
       "4                                                NaN                  Yes   \n",
       "\n",
       "                            Hospital Ownership         Hospital Type  \\\n",
       "0  Government - Hospital District or Authority  Acute Care Hospitals   \n",
       "1  Government - Hospital District or Authority  Acute Care Hospitals   \n",
       "2                                  Proprietary  Acute Care Hospitals   \n",
       "3               Voluntary non-profit - Private  Acute Care Hospitals   \n",
       "4                                  Proprietary  Acute Care Hospitals   \n",
       "\n",
       "  Hospital overall rating Meets Criteria for Meaningful Use of EHRs  \\\n",
       "0                       3                                       NaN   \n",
       "1                       3                                       NaN   \n",
       "2                       2                                       NaN   \n",
       "3                       3                                       NaN   \n",
       "4           Not Available                                       NaN   \n",
       "\n",
       "  Meets criteria for promoting interoperability of EHRs  \\\n",
       "0                                                  Y      \n",
       "1                                                  Y      \n",
       "2                                                  Y      \n",
       "3                                                  Y      \n",
       "4                                                  Y      \n",
       "\n",
       "  Mortality National Comparison Patient Experience National Comparison  \\\n",
       "0                           NaN                                    NaN   \n",
       "1                           NaN                                    NaN   \n",
       "2                           NaN                                    NaN   \n",
       "3                           NaN                                    NaN   \n",
       "4                           NaN                                    NaN   \n",
       "\n",
       "  Readmission National Comparison Safety of Care National Comparison  \\\n",
       "0                             NaN                                NaN   \n",
       "1                             NaN                                NaN   \n",
       "2                             NaN                                NaN   \n",
       "3                             NaN                                NaN   \n",
       "4                             NaN                                NaN   \n",
       "\n",
       "  Timeliness of Care National Comparison  \n",
       "0                                    NaN  \n",
       "1                                    NaN  \n",
       "2                                    NaN  \n",
       "3                                    NaN  \n",
       "4                                    NaN  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hos_df.drop(labels=['Address 1', 'Address 2', 'Address 3',\n",
    "                    'Count of Facility MORT Measures', 'Count of Facility Pt Exp Measures', \n",
    "                    'Count of Facility READM Measures', 'Count of Facility Safety Measures',\n",
    "                    'Count of Facility TE Measures', 'Count of MORT Measures Better',\n",
    "                    'Count of MORT Measures No Different', 'Count of MORT Measures Worse', \n",
    "                    'Count of READM Measures Better', 'Count of READM Measures No Different',\n",
    "                    'Count of READM Measures Worse', 'Count of Safety Measures Better',\n",
    "                    'Count of Safety Measures No Different', 'Count of Safety Measures Worse',\n",
    "                    'Hospital overall rating footnote', 'MORT Group Footnote', 'MORT Group Measure Count',\n",
    "                    'Mortality National Comparison Footnote', 'Patient Experience National Comparison Footnote',\n",
    "                    'Phone Number', 'Pt Exp Group Footnote', 'Pt Exp Group Measure Count',\n",
    "                    'READM Group Footnote', 'READM Group Measure Count', \n",
    "                    'Readmission National Comparison Footnote', 'Safety Group Footnote', \n",
    "                    'Safety Group Measure Count', 'Safety of Care National Comparison Footnote',\n",
    "                    'TE Group Footnote', 'TE Group Measure Count', 'Timeliness of Care National Comparison Footnote',\n",
    "                    'Effectiveness of Care National Comparison Footnote', \n",
    "                    'Efficient Use of Medical Imaging National Comparison Footnote',\n",
    "                    ], axis=1, inplace=True)\n",
    "\n",
    "hos_df = hos_df.filter(items=['Facility ID', 'file_month', 'file_year', 'Facility Name',\n",
    "                              'Address', 'City', 'County', 'State', 'ZIP Code',\n",
    "                              'Effectiveness of Care National Comparison', \n",
    "                              'Efficient Use of Medical Imaging National Comparison',\n",
    "                              'Emergency Services', 'Hospital Ownership', 'Hospital Type',\n",
    "                              'Hospital overall rating', 'Meets Criteria for Meaningful Use of EHRs',\n",
    "                              'Meets criteria for promoting interoperability of EHRs', \n",
    "                              'Mortality National Comparison', 'Patient Experience National Comparison',\n",
    "                              'Readmission National Comparison', 'Safety of Care National Comparison', \n",
    "                              'Timeliness of Care National Comparison',\n",
    "                             ], axis=1)\n",
    "\n",
    "print(hos_df.shape)\n",
    "hos_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "8c0fc0ab",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(215159, 22)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Facility ID</th>\n",
       "      <th>file_month</th>\n",
       "      <th>file_year</th>\n",
       "      <th>General Info: Facility Name</th>\n",
       "      <th>General Info: Address</th>\n",
       "      <th>General Info: City</th>\n",
       "      <th>General Info: County</th>\n",
       "      <th>General Info: State</th>\n",
       "      <th>General Info: ZIP Code</th>\n",
       "      <th>General Info: Effectiveness of Care National Comparison</th>\n",
       "      <th>General Info: Efficient Use of Medical Imaging National Comparison</th>\n",
       "      <th>General Info: Emergency Services</th>\n",
       "      <th>General Info: Hospital Ownership</th>\n",
       "      <th>General Info: Hospital Type</th>\n",
       "      <th>General Info: Hospital overall rating</th>\n",
       "      <th>General Info: Meets Criteria for Meaningful Use of EHRs</th>\n",
       "      <th>General Info: Meets criteria for promoting interoperability of EHRs</th>\n",
       "      <th>General Info: Mortality National Comparison</th>\n",
       "      <th>General Info: Patient Experience National Comparison</th>\n",
       "      <th>General Info: Readmission National Comparison</th>\n",
       "      <th>General Info: Safety of Care National Comparison</th>\n",
       "      <th>General Info: Timeliness of Care National Comparison</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>010001</td>\n",
       "      <td>01</td>\n",
       "      <td>2023</td>\n",
       "      <td>SOUTHEAST HEALTH MEDICAL CENTER</td>\n",
       "      <td>1108 ROSS CLARK CIRCLE</td>\n",
       "      <td>DOTHAN</td>\n",
       "      <td>HOUSTON</td>\n",
       "      <td>AL</td>\n",
       "      <td>36301</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Government - Hospital District or Authority</td>\n",
       "      <td>Acute Care Hospitals</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Y</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>010005</td>\n",
       "      <td>01</td>\n",
       "      <td>2023</td>\n",
       "      <td>MARSHALL MEDICAL CENTERS</td>\n",
       "      <td>2505 U S HIGHWAY 431 NORTH</td>\n",
       "      <td>BOAZ</td>\n",
       "      <td>MARSHALL</td>\n",
       "      <td>AL</td>\n",
       "      <td>35957</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Government - Hospital District or Authority</td>\n",
       "      <td>Acute Care Hospitals</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Y</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>010006</td>\n",
       "      <td>01</td>\n",
       "      <td>2023</td>\n",
       "      <td>NORTH ALABAMA MEDICAL CENTER</td>\n",
       "      <td>1701 VETERANS DRIVE</td>\n",
       "      <td>FLORENCE</td>\n",
       "      <td>LAUDERDALE</td>\n",
       "      <td>AL</td>\n",
       "      <td>35630</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Proprietary</td>\n",
       "      <td>Acute Care Hospitals</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Y</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>010007</td>\n",
       "      <td>01</td>\n",
       "      <td>2023</td>\n",
       "      <td>MIZELL MEMORIAL HOSPITAL</td>\n",
       "      <td>702 N MAIN ST</td>\n",
       "      <td>OPP</td>\n",
       "      <td>COVINGTON</td>\n",
       "      <td>AL</td>\n",
       "      <td>36467</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Voluntary non-profit - Private</td>\n",
       "      <td>Acute Care Hospitals</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Y</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>010008</td>\n",
       "      <td>01</td>\n",
       "      <td>2023</td>\n",
       "      <td>CRENSHAW COMMUNITY HOSPITAL</td>\n",
       "      <td>101 HOSPITAL CIRCLE</td>\n",
       "      <td>LUVERNE</td>\n",
       "      <td>CRENSHAW</td>\n",
       "      <td>AL</td>\n",
       "      <td>36049</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Proprietary</td>\n",
       "      <td>Acute Care Hospitals</td>\n",
       "      <td>Not Available</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Y</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Facility ID file_month file_year      General Info: Facility Name  \\\n",
       "0      010001         01      2023  SOUTHEAST HEALTH MEDICAL CENTER   \n",
       "1      010005         01      2023         MARSHALL MEDICAL CENTERS   \n",
       "2      010006         01      2023     NORTH ALABAMA MEDICAL CENTER   \n",
       "3      010007         01      2023         MIZELL MEMORIAL HOSPITAL   \n",
       "4      010008         01      2023      CRENSHAW COMMUNITY HOSPITAL   \n",
       "\n",
       "        General Info: Address General Info: City General Info: County  \\\n",
       "0      1108 ROSS CLARK CIRCLE             DOTHAN              HOUSTON   \n",
       "1  2505 U S HIGHWAY 431 NORTH               BOAZ             MARSHALL   \n",
       "2         1701 VETERANS DRIVE           FLORENCE           LAUDERDALE   \n",
       "3               702 N MAIN ST                OPP            COVINGTON   \n",
       "4         101 HOSPITAL CIRCLE            LUVERNE             CRENSHAW   \n",
       "\n",
       "  General Info: State  General Info: ZIP Code  \\\n",
       "0                  AL                   36301   \n",
       "1                  AL                   35957   \n",
       "2                  AL                   35630   \n",
       "3                  AL                   36467   \n",
       "4                  AL                   36049   \n",
       "\n",
       "  General Info: Effectiveness of Care National Comparison  \\\n",
       "0                                                NaN        \n",
       "1                                                NaN        \n",
       "2                                                NaN        \n",
       "3                                                NaN        \n",
       "4                                                NaN        \n",
       "\n",
       "  General Info: Efficient Use of Medical Imaging National Comparison  \\\n",
       "0                                                NaN                   \n",
       "1                                                NaN                   \n",
       "2                                                NaN                   \n",
       "3                                                NaN                   \n",
       "4                                                NaN                   \n",
       "\n",
       "  General Info: Emergency Services  \\\n",
       "0                              Yes   \n",
       "1                              Yes   \n",
       "2                              Yes   \n",
       "3                              Yes   \n",
       "4                              Yes   \n",
       "\n",
       "              General Info: Hospital Ownership General Info: Hospital Type  \\\n",
       "0  Government - Hospital District or Authority        Acute Care Hospitals   \n",
       "1  Government - Hospital District or Authority        Acute Care Hospitals   \n",
       "2                                  Proprietary        Acute Care Hospitals   \n",
       "3               Voluntary non-profit - Private        Acute Care Hospitals   \n",
       "4                                  Proprietary        Acute Care Hospitals   \n",
       "\n",
       "  General Info: Hospital overall rating  \\\n",
       "0                                     3   \n",
       "1                                     3   \n",
       "2                                     2   \n",
       "3                                     3   \n",
       "4                         Not Available   \n",
       "\n",
       "  General Info: Meets Criteria for Meaningful Use of EHRs  \\\n",
       "0                                                NaN        \n",
       "1                                                NaN        \n",
       "2                                                NaN        \n",
       "3                                                NaN        \n",
       "4                                                NaN        \n",
       "\n",
       "  General Info: Meets criteria for promoting interoperability of EHRs  \\\n",
       "0                                                  Y                    \n",
       "1                                                  Y                    \n",
       "2                                                  Y                    \n",
       "3                                                  Y                    \n",
       "4                                                  Y                    \n",
       "\n",
       "  General Info: Mortality National Comparison  \\\n",
       "0                                         NaN   \n",
       "1                                         NaN   \n",
       "2                                         NaN   \n",
       "3                                         NaN   \n",
       "4                                         NaN   \n",
       "\n",
       "  General Info: Patient Experience National Comparison  \\\n",
       "0                                                NaN     \n",
       "1                                                NaN     \n",
       "2                                                NaN     \n",
       "3                                                NaN     \n",
       "4                                                NaN     \n",
       "\n",
       "  General Info: Readmission National Comparison  \\\n",
       "0                                           NaN   \n",
       "1                                           NaN   \n",
       "2                                           NaN   \n",
       "3                                           NaN   \n",
       "4                                           NaN   \n",
       "\n",
       "  General Info: Safety of Care National Comparison  \\\n",
       "0                                              NaN   \n",
       "1                                              NaN   \n",
       "2                                              NaN   \n",
       "3                                              NaN   \n",
       "4                                              NaN   \n",
       "\n",
       "  General Info: Timeliness of Care National Comparison  \n",
       "0                                                NaN    \n",
       "1                                                NaN    \n",
       "2                                                NaN    \n",
       "3                                                NaN    \n",
       "4                                                NaN    "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "for i, n in enumerate(list(hos_df)):\n",
    "    if n in ['Facility ID', 'file_month', 'file_year']:\n",
    "        continue\n",
    "            \n",
    "    hos_df.rename(columns={n: 'General Info: ' + n}, inplace=True)\n",
    "    \n",
    "print(hos_df.shape)\n",
    "hos_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "a6079ecb",
   "metadata": {},
   "outputs": [],
   "source": [
    "hos_df.to_pickle('dataframes/hospital_info_df.pkl.gz', protocol=5, compression='gzip')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4325a936",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}