PSI_facility.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "04b9cd36",
   "metadata": {},
   "source": [
    "# Generate Complications & Death 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": "bb46abbb",
   "metadata": {},
   "source": [
    "## Define Custom Functions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "eb3ae56d",
   "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', \"Measure Start Date\", \"Measure End Date\",\n",
    "             'Hospital Name', \n",
    "             ]\n",
    "    \n",
    "    \n",
    "    cols2 = ['Facility ID', \"Start Date\", \"End Date\",\n",
    "             'Facility Name',\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 = [\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"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "05655fc0",
   "metadata": {},
   "source": [
    "## Load Files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "15e6cacb",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2023/hospitals_01_2023/Complications_and_Deaths-Hospital.csv :  (rows, columns) = (92112, 18)\n",
      "2023/hospitals_04_2023/Complications_and_Deaths-Hospital.csv :  (rows, columns) = (92055, 18)\n",
      "2022/hospitals_01_2022/Complications_and_Deaths-Hospital.csv :  (rows, columns) = (92112, 18)\n",
      "2022/hospitals_04_2022/Complications_and_Deaths-Hospital.csv :  (rows, columns) = (92112, 18)\n",
      "2022/hospitals_07_2022/Complications_and_Deaths-Hospital.csv :  (rows, columns) = (92017, 18)\n",
      "2021/hospitals_01_2021/Complications_and_Deaths-Hospital.csv :  (rows, columns) = (91114, 18)\n",
      "2021/hospitals_03_2021/Complications_and_Deaths-Hospital.csv :  (rows, columns) = (91114, 18)\n",
      "2021/hospitals_04_2021/Complications_and_Deaths-Hospital.csv :  (rows, columns) = (91684, 18)\n",
      "2021/hospitals_07_2021/Complications_and_Deaths-Hospital.csv :  (rows, columns) = (92454, 18)\n",
      "2021/hospitals_10_2021/Complications_and_Deaths-Hospital.csv :  (rows, columns) = (92245, 18)\n",
      "2020/hospitals_archive_10_2020/Complications_and_Deaths_Hospital.csv :  (rows, columns) = (91038, 18)\n",
      "2020/hospitals_archive_07_2020/Complications_and_Deaths_Hospital.csv :  (rows, columns) = (91152, 18)\n",
      "2020/HOSArchive_Revised_Flatfiles_20200422/Complications and Deaths - Hospital.csv :  (rows, columns) = (91380, 18)\n",
      "2020/HOSArchive_Revised_Flatfiles_20200129/Complications and Deaths - Hospital.csv :  (rows, columns) = (91395, 18)\n",
      "2019/HOSArchive_Revised_Flatfiles_20191030/Complications and Deaths - Hospital.csv :  (rows, columns) = (91870, 18)\n",
      "2019/HOSArchive_Revised_Flatfiles_20190702/Complications and Deaths - Hospital.csv :  (rows, columns) = (91889, 18)\n",
      "2019/HOSArchive_Revised_FlatFiles_20190424/Complications and Deaths - Hospital.csv :  (rows, columns) = (92155, 18)\n",
      "2019/HOSArchive_Revised_Flatfiles_20190321/Complications and Deaths - Hospital.csv :  (rows, columns) = (92383, 18)\n",
      "2018/HOSArchive_Revised_FlatFiles_20181031/Complications and Deaths - Hospital.csv :  (rows, columns) = (91475, 18)\n",
      "2018/HOSArchive_Revised_FlatFiles_20180725/Complications and Deaths - Hospital.csv :  (rows, columns) = (91143, 18)\n",
      "2018/HOSArchive_Revised_FlatFiles_20180523/Complications and Deaths - Hospital.csv :  (rows, columns) = (91162, 18)\n",
      "2018/HOSArchive_Revised_FlatFiles_20180126/Complications and Deaths - Hospital.csv :  (rows, columns) = (91314, 18)\n",
      "2017/HOSArchive_Revised_FlatFiles_20171024/Complications and Deaths - Hospital.csv :  (rows, columns) = (91428, 18)\n",
      "2017/HOSArchive_Revised_FlatFiles_20170726/Complications and Deaths - Hospital.csv :  (rows, columns) = (81804, 18)\n",
      "2017/HOSArchive_Revised_Flatfiles_20170428/Complications - Hospital.csv :  (rows, columns) = (52855, 18)\n",
      "2016/HOSArchive_Revised_Flatfiles_20161219/Complications - Hospital.csv :  (rows, columns) = (52877, 18)\n",
      "2016/Hospital_Revised_FlatFiles_20161110/Complications - Hospital.csv :  (rows, columns) = (52998, 18)\n",
      "2016/HOSArchive_Revised_FlatFiles_20160810/Complications - Hospital.csv :  (rows, columns) = (50906, 18)\n",
      "2016/HOSArchive_Revised_FlatFiles_20160504/Complications - Hospital.csv :  (rows, columns) = (32630, 18)\n",
      "2015/HOSArchive_Revised_FlatFiles_20151210/Complications - Hospital.csv :  (rows, columns) = (32595, 18)\n",
      "2015/HOSArchive_Revised_FlatFiles_20151008/Complications - Hospital.csv :  (rows, columns) = (32545, 18)\n",
      "2015/HOSArchive_Revised_FlatFiles_20150716/Complications - Hospital.csv :  (rows, columns) = (32721, 18)\n",
      "2015/HOSArchive_Revised_Flatfiles_20150506/Readmissions Complications and Deaths - Hospital.csv :  (rows, columns) = (90109, 18)\n",
      "2015/HOSArchive_Revised_Flatfiles_20150416/Readmissions Complications and Deaths - Hospital.csv :  (rows, columns) = (90109, 18)\n",
      "2015/HOSArchive_Revised_Flatfiles_20150122/Readmissions Complications and Deaths - Hospital.csv :  (rows, columns) = (89438, 18)\n",
      "2014/HOSArchive_Revised_Flatfiles_20141218/Readmissions Complications and Deaths - Hospital.csv :  (rows, columns) = (89438, 18)\n",
      "2014/HOSArchive_Revised_Flatfiles_20141023/Readmissions Complications and Deaths - Hospital.csv :  (rows, columns) = (71406, 18)\n",
      "2014/HOSArchive_Revised_Flatfiles_20140717/Readmissions Complications and Deaths - Hospital.csv :  (rows, columns) = (71406, 18)\n",
      "df.shape: (3032640, 20)\n"
     ]
    }
   ],
   "source": [
    "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',\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',\n",
    "       ]\n",
    "\n",
    "subdirs = ['2023/hospitals_01_2023/Complications_and_Deaths-Hospital.csv', \n",
    "           '2023/hospitals_04_2023/Complications_and_Deaths-Hospital.csv',\n",
    "           \n",
    "           '2022/hospitals_01_2022/Complications_and_Deaths-Hospital.csv', \n",
    "           '2022/hospitals_04_2022/Complications_and_Deaths-Hospital.csv', \n",
    "           '2022/hospitals_07_2022/Complications_and_Deaths-Hospital.csv',\n",
    "           \n",
    "           '2021/hospitals_01_2021/Complications_and_Deaths-Hospital.csv',\n",
    "           '2021/hospitals_03_2021/Complications_and_Deaths-Hospital.csv',\n",
    "           '2021/hospitals_04_2021/Complications_and_Deaths-Hospital.csv',\n",
    "           '2021/hospitals_07_2021/Complications_and_Deaths-Hospital.csv',\n",
    "           '2021/hospitals_10_2021/Complications_and_Deaths-Hospital.csv',\n",
    "           \n",
    "           '2020/hospitals_archive_10_2020/Complications_and_Deaths_Hospital.csv',\n",
    "           '2020/hospitals_archive_07_2020/Complications_and_Deaths_Hospital.csv',\n",
    "           '2020/HOSArchive_Revised_Flatfiles_20200422/Complications and Deaths - Hospital.csv',\n",
    "           '2020/HOSArchive_Revised_Flatfiles_20200129/Complications and Deaths - Hospital.csv',\n",
    "           \n",
    "           '2019/HOSArchive_Revised_Flatfiles_20191030/Complications and Deaths - Hospital.csv',\n",
    "           '2019/HOSArchive_Revised_Flatfiles_20190702/Complications and Deaths - Hospital.csv',\n",
    "           '2019/HOSArchive_Revised_FlatFiles_20190424/Complications and Deaths - Hospital.csv',\n",
    "           '2019/HOSArchive_Revised_Flatfiles_20190321/Complications and Deaths - Hospital.csv',\n",
    "           \n",
    "           '2018/HOSArchive_Revised_FlatFiles_20181031/Complications and Deaths - Hospital.csv',\n",
    "           '2018/HOSArchive_Revised_FlatFiles_20180725/Complications and Deaths - Hospital.csv', \n",
    "           '2018/HOSArchive_Revised_FlatFiles_20180523/Complications and Deaths - Hospital.csv',\n",
    "           '2018/HOSArchive_Revised_FlatFiles_20180126/Complications and Deaths - Hospital.csv',\n",
    "           \n",
    "           '2017/HOSArchive_Revised_FlatFiles_20171024/Complications and Deaths - Hospital.csv',\n",
    "           '2017/HOSArchive_Revised_FlatFiles_20170726/Complications and Deaths - Hospital.csv', \n",
    "           '2017/HOSArchive_Revised_Flatfiles_20170428/Complications - Hospital.csv', \n",
    "           \n",
    "           '2016/HOSArchive_Revised_Flatfiles_20161219/Complications - Hospital.csv',\n",
    "           '2016/Hospital_Revised_FlatFiles_20161110/Complications - Hospital.csv', \n",
    "           '2016/HOSArchive_Revised_FlatFiles_20160810/Complications - Hospital.csv',\n",
    "           '2016/HOSArchive_Revised_FlatFiles_20160504/Complications - Hospital.csv',\n",
    "           \n",
    "           '2015/HOSArchive_Revised_FlatFiles_20151210/Complications - Hospital.csv',\n",
    "           '2015/HOSArchive_Revised_FlatFiles_20151008/Complications - Hospital.csv',\n",
    "           '2015/HOSArchive_Revised_FlatFiles_20150716/Complications - Hospital.csv',\n",
    "           '2015/HOSArchive_Revised_Flatfiles_20150506/Readmissions Complications and Deaths - Hospital.csv',\n",
    "           '2015/HOSArchive_Revised_Flatfiles_20150416/Readmissions Complications and Deaths - Hospital.csv',\n",
    "           '2015/HOSArchive_Revised_Flatfiles_20150122/Readmissions Complications and Deaths - Hospital.csv',\n",
    "           \n",
    "           '2014/HOSArchive_Revised_Flatfiles_20141218/Readmissions Complications and Deaths - Hospital.csv',\n",
    "           '2014/HOSArchive_Revised_Flatfiles_20141023/Readmissions Complications and Deaths - Hospital.csv',\n",
    "           '2014/HOSArchive_Revised_Flatfiles_20140717/Readmissions Complications and Deaths - Hospital.csv',\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 = 'Complications_and_Deaths/CombinedFiles_ComplicationsAndDeaths'\n",
    "df = pd.concat(df_list)\n",
    "print('df.shape:', df.shape)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "a98fe03f",
   "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>Facility ID</th>\n",
       "      <th>file_month</th>\n",
       "      <th>file_year</th>\n",
       "      <th>Measure ID</th>\n",
       "      <th>Measure Name</th>\n",
       "      <th>Denominator</th>\n",
       "      <th>Score</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>COMP_HIP_KNEE</td>\n",
       "      <td>Rate of complications for hip/knee replacement...</td>\n",
       "      <td>102</td>\n",
       "      <td>2.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>010001</td>\n",
       "      <td>01</td>\n",
       "      <td>2023</td>\n",
       "      <td>MORT_30_AMI</td>\n",
       "      <td>Death rate for heart attack patients</td>\n",
       "      <td>317</td>\n",
       "      <td>12.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>010001</td>\n",
       "      <td>01</td>\n",
       "      <td>2023</td>\n",
       "      <td>MORT_30_CABG</td>\n",
       "      <td>Death rate for CABG surgery patients</td>\n",
       "      <td>172</td>\n",
       "      <td>4.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>010001</td>\n",
       "      <td>01</td>\n",
       "      <td>2023</td>\n",
       "      <td>MORT_30_COPD</td>\n",
       "      <td>Death rate for COPD patients</td>\n",
       "      <td>182</td>\n",
       "      <td>8.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>010001</td>\n",
       "      <td>01</td>\n",
       "      <td>2023</td>\n",
       "      <td>MORT_30_HF</td>\n",
       "      <td>Death rate for heart failure patients</td>\n",
       "      <td>630</td>\n",
       "      <td>8.3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Facility ID file_month file_year     Measure ID  \\\n",
       "0      010001         01      2023  COMP_HIP_KNEE   \n",
       "1      010001         01      2023    MORT_30_AMI   \n",
       "2      010001         01      2023   MORT_30_CABG   \n",
       "3      010001         01      2023   MORT_30_COPD   \n",
       "4      010001         01      2023     MORT_30_HF   \n",
       "\n",
       "                                        Measure Name Denominator Score  \n",
       "0  Rate of complications for hip/knee replacement...         102   2.4  \n",
       "1               Death rate for heart attack patients         317  12.4  \n",
       "2               Death rate for CABG surgery patients         172   4.7  \n",
       "3                       Death rate for COPD patients         182   8.5  \n",
       "4              Death rate for heart failure patients         630   8.3  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df.filter(items=['Facility ID', 'file_month', 'file_year',\n",
    "                    #'Start Date', 'End Date', #'Compared to National', 'Footnote', \n",
    "                    'Measure ID', 'Measure Name', 'Denominator', 'Score'], axis=1)\n",
    "\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "38ab4f48",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['COMP HIP KNEE' 'MORT 30 AMI' 'MORT 30 CABG' 'MORT 30 COPD' 'MORT 30 HF'\n",
      " 'MORT 30 PN' 'MORT 30 STK' 'PSI 3' 'PSI 4' 'PSI 6' 'PSI 8' 'PSI 9'\n",
      " 'PSI 10' 'PSI 11' 'PSI 12' 'PSI 13' 'PSI 14' 'PSI 15' 'PSI 90' 'PSI 7'\n",
      " 'READM 30 AMI' 'READM 30 COPD' 'READM 30 HF' 'READM 30 HIP KNEE'\n",
      " 'READM 30 HOSP WIDE' 'READM 30 PN' 'READM 30 STK']\n"
     ]
    }
   ],
   "source": [
    "d = {'COMP_HIP_KNEE': 'COMP HIP KNEE',\n",
    "     'MORT_30_AMI': 'MORT 30 AMI',\n",
    "     'MORT_30_CABG': 'MORT 30 CABG',\n",
    "     'MORT_30_COPD': 'MORT 30 COPD', \n",
    "     'MORT_30_HF': 'MORT 30 HF',\n",
    "     'MORT_30_PN': 'MORT 30 PN',\n",
    "     'MORT_30_STK': 'MORT 30 STK',\n",
    "     'PSI_03': 'PSI 3',\n",
    "     'PSI_04': 'PSI 4',\n",
    "     'PSI_06': 'PSI 6',\n",
    "     'PSI_08': 'PSI 8',\n",
    "     'PSI_09': 'PSI 9',\n",
    "     'PSI_10': 'PSI 10',\n",
    "     'PSI_11': 'PSI 11',\n",
    "     'PSI_12': 'PSI 12',\n",
    "     'PSI_13': 'PSI 13',\n",
    "     'PSI_14': 'PSI 14',\n",
    "     'PSI_15': 'PSI 15',\n",
    "     'PSI_90': 'PSI 90',\n",
    "     'PSI_10_POST_KIDNEY': 'PSI 10',\n",
    "     'PSI_11_POST_RESP': 'PSI 11',\n",
    "     'PSI_12_POSTOP_PULMEMB_DVT': 'PSI 12',\n",
    "     'PSI_13_POST_SEPSIS': 'PSI 13',\n",
    "     'PSI_14_POSTOP_DEHIS': 'PSI 14',\n",
    "     'PSI_15_ACC_LAC': 'PSI 15',\n",
    "     'PSI_3_ULCER': 'PSI 3',\n",
    "     'PSI_4_SURG_COMP': 'PSI 4',\n",
    "     'PSI_6_IAT_PTX': 'PSI 6',\n",
    "     'PSI_8_POST_HIP': 'PSI 8', \n",
    "     'PSI_90_SAFETY': 'PSI 90',\n",
    "     'PSI_9_POST_HEM': 'PSI 9',\n",
    "     'PSI_7_CVCBI': 'PSI 7',\n",
    "     'READM_30_AMI': 'READM 30 AMI',\n",
    "     'READM_30_COPD': 'READM 30 COPD',\n",
    "     'READM_30_HF': 'READM 30 HF',\n",
    "     'READM_30_HIP_KNEE': 'READM 30 HIP KNEE',\n",
    "     'READM_30_HOSP_WIDE': 'READM 30 HOSP WIDE',\n",
    "     'READM_30_PN': 'READM 30 PN',\n",
    "     'READM_30_STK': 'READM 30 STK',\n",
    "}\n",
    "\n",
    "df['Measure ID'].replace(to_replace=d, inplace=True)\n",
    "print(df['Measure ID'].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "be650a44",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['AMI 30-Day Mortality Rate', 'Abdominopelvic accidental puncture or laceration rate', 'Acute Myocardial Infarction (AMI) 30-Day Readmission Rate', 'Death rate among surgical inpatients with serious treatable complications', 'Death rate for CABG surgery patients', 'Death rate for COPD patients', 'Death rate for stroke patients', 'Heart failure (HF) 30-Day Readmission Rate', 'Heart failure 30-Day Mortality Rate', 'Iatrogenic pneumothorax rate', 'In-hospital fall with hip fracture rate', 'Infections from a large venous catheter', 'Patient safety and adverse events composite', 'Perioperative hemorrhage or hematoma rate', 'Perioperative pulmonary embolism or deep vein thrombosis rate', 'Pneumonia (PN) 30-Day Readmission Rate', 'Pneumonia 30-Day Mortality Rate', 'Postoperative acute kidney injury requiring dialysis rate', 'Postoperative hemorrhage or hematoma rate', 'Postoperative respiratory failure rate', 'Postoperative sepsis rate', 'Postoperative wound dehiscence rate', 'Pressure ulcer rate', 'Rate of complications for hip/knee replacement patients', 'Rate of readmission after discharge from hospital (hospital-wide)', 'Rate of readmission after hip/knee surgery', 'Rate of unplanned readmission for chronic obstructive pulmonary disease (COPD) patients', 'Rate of unplanned readmission for stroke patients'] \n",
      "\n"
     ]
    }
   ],
   "source": [
    "d = {'A wound that splits open  after surgery on the abdomen or pelvis': 'Postoperative wound dehiscence rate', \n",
    "     'A wound that splits open after surgery on the abdomen or pelvis' : 'Postoperative wound dehiscence rate', \n",
    "     \n",
    "     \n",
    "     'Acute Myocardial Infarction (AMI) 30-Day Mortality Rate': 'AMI 30-Day Mortality Rate',\n",
    "     'Death rate for heart attack patients': 'AMI 30-Day Mortality Rate', \n",
    "     \n",
    "     'Death rate for CABG': 'Death rate for CABG surgery patients', \n",
    "     'Death rate for COPD patients': 'Death rate for COPD patients', \n",
    "     'Death rate for chronic obstructive pulmonary disease (COPD) patients': 'Death rate for COPD patients', \n",
    "     \n",
    "     'Death rate for heart failure patients'   : 'Heart failure 30-Day Mortality Rate', \n",
    "     'Heart failure (HF) 30-Day Mortality Rate': 'Heart failure 30-Day Mortality Rate', \n",
    "     \n",
    "     'Death rate for pneumonia patients'   : 'Pneumonia 30-Day Mortality Rate',  \n",
    "     'Pneumonia (PN) 30-Day Mortality Rate': 'Pneumonia 30-Day Mortality Rate', \n",
    "     \n",
    "     'Perioperative Hemorrhage or Hematoma Rate': 'Perioperative hemorrhage or hematoma rate', \n",
    "     'Perioperative hemorrhage or hematoma rate': 'Perioperative hemorrhage or hematoma rate',\n",
    "     \n",
    "     'Postoperative Acute Kidney Injury Requiring Dialysis Rate': 'Postoperative acute kidney injury requiring dialysis rate', \n",
    "     'Postoperative acute kidney injury requiring dialysis rate': 'Postoperative acute kidney injury requiring dialysis rate', \n",
    "     \n",
    "     'Postoperative Respiratory Failure Rate': 'Postoperative respiratory failure rate', \n",
    "     'Postoperative respiratory failure rate': 'Postoperative respiratory failure rate', \n",
    "     \n",
    "     'Serious blood clots after surgery': 'Perioperative pulmonary embolism or deep vein thrombosis rate', \n",
    "     'Blood stream infection after surgery': 'Postoperative sepsis rate', \n",
    "     \n",
    "     'Accidental cuts and tears from medical treatment': 'Abdominopelvic accidental puncture or laceration rate', \n",
    "     'Deaths among Patients with Serious Treatable Complications after Surgery': 'Death rate among surgical inpatients with serious treatable complications', \n",
    "     'Pressure sores': 'Pressure ulcer rate', \n",
    "     \n",
    "     'Collapsed lung due to medical treatment': 'Iatrogenic pneumothorax rate', \n",
    "     'Broken hip from a fall after surgery': 'In-hospital fall with hip fracture rate', \n",
    "\n",
    "     'CMS Medicare PSI 90: Patient safety and adverse events composite': 'Patient safety and adverse events composite', \n",
    "     'Serious complications': 'Patient safety and adverse events composite',\n",
    "\n",
    "    }\n",
    "\n",
    "df['Measure Name'].replace(to_replace=d, inplace=True)\n",
    "print(sorted(df['Measure Name'].unique()), '\\n')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "5f47f0e6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "28\n",
      "COMP HIP KNEE — Rate of complications for hip/knee replacement patients\n",
      "MORT 30 AMI — AMI 30-Day Mortality Rate\n",
      "MORT 30 CABG — Death rate for CABG surgery patients\n",
      "MORT 30 COPD — Death rate for COPD patients\n",
      "MORT 30 HF — Heart failure 30-Day Mortality Rate\n",
      "MORT 30 PN — Pneumonia 30-Day Mortality Rate\n",
      "MORT 30 STK — Death rate for stroke patients\n",
      "PSI 10 — Postoperative acute kidney injury requiring dialysis rate\n",
      "PSI 11 — Postoperative respiratory failure rate\n",
      "PSI 12 — Perioperative pulmonary embolism or deep vein thrombosis rate\n",
      "PSI 13 — Postoperative sepsis rate\n",
      "PSI 14 — Postoperative wound dehiscence rate\n",
      "PSI 15 — Abdominopelvic accidental puncture or laceration rate\n",
      "PSI 3 — Pressure ulcer rate\n",
      "PSI 4 — Death rate among surgical inpatients with serious treatable complications\n",
      "PSI 6 — Iatrogenic pneumothorax rate\n",
      "PSI 7 — Infections from a large venous catheter\n",
      "PSI 8 — In-hospital fall with hip fracture rate\n",
      "PSI 9 — Perioperative hemorrhage or hematoma rate\n",
      "PSI 9 — Postoperative hemorrhage or hematoma rate\n",
      "PSI 90 — Patient safety and adverse events composite\n",
      "READM 30 AMI — Acute Myocardial Infarction (AMI) 30-Day Readmission Rate\n",
      "READM 30 COPD — Rate of unplanned readmission for chronic obstructive pulmonary disease (COPD) patients\n",
      "READM 30 HF — Heart failure (HF) 30-Day Readmission Rate\n",
      "READM 30 HIP KNEE — Rate of readmission after hip/knee surgery\n",
      "READM 30 HOSP WIDE — Rate of readmission after discharge from hospital (hospital-wide)\n",
      "READM 30 PN — Pneumonia (PN) 30-Day Readmission Rate\n",
      "READM 30 STK — Rate of unplanned readmission for stroke patients\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>Denominator</th>\n",
       "      <th>Score</th>\n",
       "      <th>Measure</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>102</td>\n",
       "      <td>2.4</td>\n",
       "      <td>COMP HIP KNEE — Rate of complications for hip/...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>010001</td>\n",
       "      <td>01</td>\n",
       "      <td>2023</td>\n",
       "      <td>317</td>\n",
       "      <td>12.4</td>\n",
       "      <td>MORT 30 AMI — AMI 30-Day Mortality Rate</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>010001</td>\n",
       "      <td>01</td>\n",
       "      <td>2023</td>\n",
       "      <td>172</td>\n",
       "      <td>4.7</td>\n",
       "      <td>MORT 30 CABG — Death rate for CABG surgery pat...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>010001</td>\n",
       "      <td>01</td>\n",
       "      <td>2023</td>\n",
       "      <td>182</td>\n",
       "      <td>8.5</td>\n",
       "      <td>MORT 30 COPD — Death rate for COPD patients</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>010001</td>\n",
       "      <td>01</td>\n",
       "      <td>2023</td>\n",
       "      <td>630</td>\n",
       "      <td>8.3</td>\n",
       "      <td>MORT 30 HF — Heart failure 30-Day Mortality Rate</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Facility ID file_month file_year Denominator Score  \\\n",
       "0      010001         01      2023         102   2.4   \n",
       "1      010001         01      2023         317  12.4   \n",
       "2      010001         01      2023         172   4.7   \n",
       "3      010001         01      2023         182   8.5   \n",
       "4      010001         01      2023         630   8.3   \n",
       "\n",
       "                                             Measure  \n",
       "0  COMP HIP KNEE — Rate of complications for hip/...  \n",
       "1            MORT 30 AMI — AMI 30-Day Mortality Rate  \n",
       "2  MORT 30 CABG — Death rate for CABG surgery pat...  \n",
       "3        MORT 30 COPD — Death rate for COPD patients  \n",
       "4   MORT 30 HF — Heart failure 30-Day Mortality Rate  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Measure'] = df['Measure ID'] + ' — ' + df['Measure Name']\n",
    "\n",
    "measures = sorted(df['Measure'].unique())\n",
    "print(len(measures))\n",
    "for m in measures:\n",
    "    print(m)\n",
    "    \n",
    "df.drop(labels=['Measure Name', 'Measure ID'], axis=1, inplace=True)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "76519462",
   "metadata": {},
   "outputs": [],
   "source": [
    "Measures = sorted(df['Measure'].unique())\n",
    "main_df = pd.DataFrame(columns=['Facility ID', 'file_month', 'file_year'])\n",
    "\n",
    "for i, mi in enumerate(Measures):\n",
    "    \n",
    "    tdf = df[df['Measure'] == mi]\n",
    "    measures = sorted(tdf['Measure'].unique())\n",
    "    \n",
    "    df2 = pd.DataFrame(columns=['Facility ID', 'file_month', 'file_year'])\n",
    "\n",
    "    for j, m in enumerate(measures):\n",
    "    \n",
    "        tdf2 = tdf[tdf['Measure'] == m]\n",
    "        for n in list(tdf2):\n",
    "            if n == 'Measure' or n in ['Facility ID', 'file_month', 'file_year']:\n",
    "                continue\n",
    "\n",
    "            else:\n",
    "                tdf2[n] = pd.to_numeric(tdf2[n], errors='coerce')\n",
    "                tdf2.rename(columns={n: m + ' (' + n + ')'}, inplace=True)\n",
    "        \n",
    "        tdf2.drop(labels=['Measure'], axis=1, inplace=True)\n",
    "        \n",
    "        df2 = df2.merge(tdf2, on=['Facility ID', 'file_month', \n",
    "                                          'file_year'], how='outer')\n",
    "    \n",
    "    main_df = main_df.merge(df2, on=['Facility ID', 'file_month', \n",
    "                                          'file_year'], how='outer')\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "ff624040",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(183734, 59)\n",
      "(183734, 58)\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>COMP HIP KNEE — Rate of complications for hip/knee replacement patients (Denominator)</th>\n",
       "      <th>COMP HIP KNEE — Rate of complications for hip/knee replacement patients (Score)</th>\n",
       "      <th>MORT 30 AMI — AMI 30-Day Mortality Rate (Denominator)</th>\n",
       "      <th>MORT 30 AMI — AMI 30-Day Mortality Rate (Score)</th>\n",
       "      <th>MORT 30 CABG — Death rate for CABG surgery patients (Denominator)</th>\n",
       "      <th>MORT 30 CABG — Death rate for CABG surgery patients (Score)</th>\n",
       "      <th>MORT 30 COPD — Death rate for COPD patients (Denominator)</th>\n",
       "      <th>MORT 30 COPD — Death rate for COPD patients (Score)</th>\n",
       "      <th>MORT 30 HF — Heart failure 30-Day Mortality Rate (Denominator)</th>\n",
       "      <th>MORT 30 HF — Heart failure 30-Day Mortality Rate (Score)</th>\n",
       "      <th>MORT 30 PN — Pneumonia 30-Day Mortality Rate (Denominator)</th>\n",
       "      <th>MORT 30 PN — Pneumonia 30-Day Mortality Rate (Score)</th>\n",
       "      <th>MORT 30 STK — Death rate for stroke patients (Denominator)</th>\n",
       "      <th>MORT 30 STK — Death rate for stroke patients (Score)</th>\n",
       "      <th>PSI 10 — Postoperative acute kidney injury requiring dialysis rate (Denominator)</th>\n",
       "      <th>PSI 10 — Postoperative acute kidney injury requiring dialysis rate (Score)</th>\n",
       "      <th>PSI 11 — Postoperative respiratory failure rate (Denominator)</th>\n",
       "      <th>PSI 11 — Postoperative respiratory failure rate (Score)</th>\n",
       "      <th>PSI 12 — Perioperative pulmonary embolism or deep vein thrombosis rate (Denominator)</th>\n",
       "      <th>PSI 12 — Perioperative pulmonary embolism or deep vein thrombosis rate (Score)</th>\n",
       "      <th>PSI 13 — Postoperative sepsis rate (Denominator)</th>\n",
       "      <th>PSI 13 — Postoperative sepsis rate (Score)</th>\n",
       "      <th>PSI 14 — Postoperative wound dehiscence rate (Denominator)</th>\n",
       "      <th>PSI 14 — Postoperative wound dehiscence rate (Score)</th>\n",
       "      <th>PSI 15 — Abdominopelvic accidental puncture or laceration rate (Denominator)</th>\n",
       "      <th>PSI 15 — Abdominopelvic accidental puncture or laceration rate (Score)</th>\n",
       "      <th>PSI 3 — Pressure ulcer rate (Denominator)</th>\n",
       "      <th>PSI 3 — Pressure ulcer rate (Score)</th>\n",
       "      <th>PSI 4 — Death rate among surgical inpatients with serious treatable complications (Denominator)</th>\n",
       "      <th>PSI 4 — Death rate among surgical inpatients with serious treatable complications (Score)</th>\n",
       "      <th>PSI 6 — Iatrogenic pneumothorax rate (Denominator)</th>\n",
       "      <th>PSI 6 — Iatrogenic pneumothorax rate (Score)</th>\n",
       "      <th>PSI 7 — Infections from a large venous catheter (Denominator)</th>\n",
       "      <th>PSI 7 — Infections from a large venous catheter (Score)</th>\n",
       "      <th>PSI 8 — In-hospital fall with hip fracture rate (Denominator)</th>\n",
       "      <th>PSI 8 — In-hospital fall with hip fracture rate (Score)</th>\n",
       "      <th>PSI 9 — Perioperative hemorrhage or hematoma rate (Denominator)</th>\n",
       "      <th>PSI 9 — Perioperative hemorrhage or hematoma rate (Score)</th>\n",
       "      <th>PSI 9 — Postoperative hemorrhage or hematoma rate (Denominator)</th>\n",
       "      <th>PSI 9 — Postoperative hemorrhage or hematoma rate (Score)</th>\n",
       "      <th>PSI 90 — Patient safety and adverse events composite (Score)</th>\n",
       "      <th>READM 30 AMI — Acute Myocardial Infarction (AMI) 30-Day Readmission Rate (Denominator)</th>\n",
       "      <th>READM 30 AMI — Acute Myocardial Infarction (AMI) 30-Day Readmission Rate (Score)</th>\n",
       "      <th>READM 30 COPD — Rate of unplanned readmission for chronic obstructive pulmonary disease (COPD) patients (Denominator)</th>\n",
       "      <th>READM 30 COPD — Rate of unplanned readmission for chronic obstructive pulmonary disease (COPD) patients (Score)</th>\n",
       "      <th>READM 30 HF — Heart failure (HF) 30-Day Readmission Rate (Denominator)</th>\n",
       "      <th>READM 30 HF — Heart failure (HF) 30-Day Readmission Rate (Score)</th>\n",
       "      <th>READM 30 HIP KNEE — Rate of readmission after hip/knee surgery (Denominator)</th>\n",
       "      <th>READM 30 HIP KNEE — Rate of readmission after hip/knee surgery (Score)</th>\n",
       "      <th>READM 30 HOSP WIDE — Rate of readmission after discharge from hospital (hospital-wide) (Denominator)</th>\n",
       "      <th>READM 30 HOSP WIDE — Rate of readmission after discharge from hospital (hospital-wide) (Score)</th>\n",
       "      <th>READM 30 PN — Pneumonia (PN) 30-Day Readmission Rate (Denominator)</th>\n",
       "      <th>READM 30 PN — Pneumonia (PN) 30-Day Readmission Rate (Score)</th>\n",
       "      <th>READM 30 STK — Rate of unplanned readmission for stroke patients (Denominator)</th>\n",
       "      <th>READM 30 STK — Rate of unplanned readmission for stroke patients (Score)</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>102.0</td>\n",
       "      <td>2.4</td>\n",
       "      <td>317.0</td>\n",
       "      <td>12.4</td>\n",
       "      <td>172.0</td>\n",
       "      <td>4.7</td>\n",
       "      <td>182.0</td>\n",
       "      <td>8.5</td>\n",
       "      <td>630.0</td>\n",
       "      <td>8.3</td>\n",
       "      <td>407.0</td>\n",
       "      <td>15.9</td>\n",
       "      <td>489.0</td>\n",
       "      <td>16.4</td>\n",
       "      <td>746.0</td>\n",
       "      <td>0.61</td>\n",
       "      <td>760.0</td>\n",
       "      <td>8.92</td>\n",
       "      <td>2040.0</td>\n",
       "      <td>3.33</td>\n",
       "      <td>735.0</td>\n",
       "      <td>5.98</td>\n",
       "      <td>569.0</td>\n",
       "      <td>0.65</td>\n",
       "      <td>1433.0</td>\n",
       "      <td>1.21</td>\n",
       "      <td>5614.0</td>\n",
       "      <td>0.23</td>\n",
       "      <td>120.0</td>\n",
       "      <td>173.39</td>\n",
       "      <td>7181.0</td>\n",
       "      <td>0.17</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7513.0</td>\n",
       "      <td>0.10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1882.0</td>\n",
       "      <td>2.33</td>\n",
       "      <td>1.01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>010005</td>\n",
       "      <td>01</td>\n",
       "      <td>2023</td>\n",
       "      <td>134.0</td>\n",
       "      <td>1.8</td>\n",
       "      <td>52.0</td>\n",
       "      <td>12.6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>195.0</td>\n",
       "      <td>8.1</td>\n",
       "      <td>153.0</td>\n",
       "      <td>16.9</td>\n",
       "      <td>369.0</td>\n",
       "      <td>21.8</td>\n",
       "      <td>100.0</td>\n",
       "      <td>16.6</td>\n",
       "      <td>371.0</td>\n",
       "      <td>0.76</td>\n",
       "      <td>364.0</td>\n",
       "      <td>6.87</td>\n",
       "      <td>738.0</td>\n",
       "      <td>2.54</td>\n",
       "      <td>326.0</td>\n",
       "      <td>3.44</td>\n",
       "      <td>186.0</td>\n",
       "      <td>0.76</td>\n",
       "      <td>486.0</td>\n",
       "      <td>0.87</td>\n",
       "      <td>2186.0</td>\n",
       "      <td>0.86</td>\n",
       "      <td>35.0</td>\n",
       "      <td>142.88</td>\n",
       "      <td>2901.0</td>\n",
       "      <td>0.17</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2910.0</td>\n",
       "      <td>0.06</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>706.0</td>\n",
       "      <td>2.08</td>\n",
       "      <td>0.91</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>010006</td>\n",
       "      <td>01</td>\n",
       "      <td>2023</td>\n",
       "      <td>234.0</td>\n",
       "      <td>3.4</td>\n",
       "      <td>295.0</td>\n",
       "      <td>16.5</td>\n",
       "      <td>117.0</td>\n",
       "      <td>3.5</td>\n",
       "      <td>209.0</td>\n",
       "      <td>7.8</td>\n",
       "      <td>468.0</td>\n",
       "      <td>12.2</td>\n",
       "      <td>528.0</td>\n",
       "      <td>17.8</td>\n",
       "      <td>261.0</td>\n",
       "      <td>18.9</td>\n",
       "      <td>433.0</td>\n",
       "      <td>0.65</td>\n",
       "      <td>427.0</td>\n",
       "      <td>3.89</td>\n",
       "      <td>1504.0</td>\n",
       "      <td>2.80</td>\n",
       "      <td>430.0</td>\n",
       "      <td>3.72</td>\n",
       "      <td>336.0</td>\n",
       "      <td>0.68</td>\n",
       "      <td>911.0</td>\n",
       "      <td>1.33</td>\n",
       "      <td>4405.0</td>\n",
       "      <td>1.83</td>\n",
       "      <td>84.0</td>\n",
       "      <td>157.42</td>\n",
       "      <td>5559.0</td>\n",
       "      <td>0.26</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5795.0</td>\n",
       "      <td>0.05</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1409.0</td>\n",
       "      <td>3.46</td>\n",
       "      <td>1.10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>010007</td>\n",
       "      <td>01</td>\n",
       "      <td>2023</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>63.0</td>\n",
       "      <td>10.3</td>\n",
       "      <td>45.0</td>\n",
       "      <td>13.9</td>\n",
       "      <td>106.0</td>\n",
       "      <td>21.7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>34.0</td>\n",
       "      <td>0.91</td>\n",
       "      <td>36.0</td>\n",
       "      <td>6.01</td>\n",
       "      <td>75.0</td>\n",
       "      <td>3.80</td>\n",
       "      <td>31.0</td>\n",
       "      <td>4.05</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>46.0</td>\n",
       "      <td>1.02</td>\n",
       "      <td>511.0</td>\n",
       "      <td>0.32</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>681.0</td>\n",
       "      <td>0.18</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>675.0</td>\n",
       "      <td>0.07</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>71.0</td>\n",
       "      <td>2.37</td>\n",
       "      <td>0.99</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>010008</td>\n",
       "      <td>01</td>\n",
       "      <td>2023</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>26.0</td>\n",
       "      <td>19.7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>173.0</td>\n",
       "      <td>0.50</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>193.0</td>\n",
       "      <td>0.19</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>209.0</td>\n",
       "      <td>0.07</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>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  \\\n",
       "0      010001         01      2023   \n",
       "1      010005         01      2023   \n",
       "2      010006         01      2023   \n",
       "3      010007         01      2023   \n",
       "4      010008         01      2023   \n",
       "\n",
       "   COMP HIP KNEE — Rate of complications for hip/knee replacement patients (Denominator)  \\\n",
       "0                                              102.0                                       \n",
       "1                                              134.0                                       \n",
       "2                                              234.0                                       \n",
       "3                                                NaN                                       \n",
       "4                                                NaN                                       \n",
       "\n",
       "   COMP HIP KNEE — Rate of complications for hip/knee replacement patients (Score)  \\\n",
       "0                                                2.4                                 \n",
       "1                                                1.8                                 \n",
       "2                                                3.4                                 \n",
       "3                                                NaN                                 \n",
       "4                                                NaN                                 \n",
       "\n",
       "   MORT 30 AMI — AMI 30-Day Mortality Rate (Denominator)  \\\n",
       "0                                              317.0       \n",
       "1                                               52.0       \n",
       "2                                              295.0       \n",
       "3                                                NaN       \n",
       "4                                                NaN       \n",
       "\n",
       "   MORT 30 AMI — AMI 30-Day Mortality Rate (Score)  \\\n",
       "0                                             12.4   \n",
       "1                                             12.6   \n",
       "2                                             16.5   \n",
       "3                                              NaN   \n",
       "4                                              NaN   \n",
       "\n",
       "   MORT 30 CABG — Death rate for CABG surgery patients (Denominator)  \\\n",
       "0                                              172.0                   \n",
       "1                                                NaN                   \n",
       "2                                              117.0                   \n",
       "3                                                NaN                   \n",
       "4                                                NaN                   \n",
       "\n",
       "   MORT 30 CABG — Death rate for CABG surgery patients (Score)  \\\n",
       "0                                                4.7             \n",
       "1                                                NaN             \n",
       "2                                                3.5             \n",
       "3                                                NaN             \n",
       "4                                                NaN             \n",
       "\n",
       "   MORT 30 COPD — Death rate for COPD patients (Denominator)  \\\n",
       "0                                              182.0           \n",
       "1                                              195.0           \n",
       "2                                              209.0           \n",
       "3                                               63.0           \n",
       "4                                                NaN           \n",
       "\n",
       "   MORT 30 COPD — Death rate for COPD patients (Score)  \\\n",
       "0                                                8.5     \n",
       "1                                                8.1     \n",
       "2                                                7.8     \n",
       "3                                               10.3     \n",
       "4                                                NaN     \n",
       "\n",
       "   MORT 30 HF — Heart failure 30-Day Mortality Rate (Denominator)  \\\n",
       "0                                              630.0                \n",
       "1                                              153.0                \n",
       "2                                              468.0                \n",
       "3                                               45.0                \n",
       "4                                                NaN                \n",
       "\n",
       "   MORT 30 HF — Heart failure 30-Day Mortality Rate (Score)  \\\n",
       "0                                                8.3          \n",
       "1                                               16.9          \n",
       "2                                               12.2          \n",
       "3                                               13.9          \n",
       "4                                                NaN          \n",
       "\n",
       "   MORT 30 PN — Pneumonia 30-Day Mortality Rate (Denominator)  \\\n",
       "0                                              407.0            \n",
       "1                                              369.0            \n",
       "2                                              528.0            \n",
       "3                                              106.0            \n",
       "4                                               26.0            \n",
       "\n",
       "   MORT 30 PN — Pneumonia 30-Day Mortality Rate (Score)  \\\n",
       "0                                               15.9      \n",
       "1                                               21.8      \n",
       "2                                               17.8      \n",
       "3                                               21.7      \n",
       "4                                               19.7      \n",
       "\n",
       "   MORT 30 STK — Death rate for stroke patients (Denominator)  \\\n",
       "0                                              489.0            \n",
       "1                                              100.0            \n",
       "2                                              261.0            \n",
       "3                                                NaN            \n",
       "4                                                NaN            \n",
       "\n",
       "   MORT 30 STK — Death rate for stroke patients (Score)  \\\n",
       "0                                               16.4      \n",
       "1                                               16.6      \n",
       "2                                               18.9      \n",
       "3                                                NaN      \n",
       "4                                                NaN      \n",
       "\n",
       "   PSI 10 — Postoperative acute kidney injury requiring dialysis rate (Denominator)  \\\n",
       "0                                              746.0                                  \n",
       "1                                              371.0                                  \n",
       "2                                              433.0                                  \n",
       "3                                               34.0                                  \n",
       "4                                                NaN                                  \n",
       "\n",
       "   PSI 10 — Postoperative acute kidney injury requiring dialysis rate (Score)  \\\n",
       "0                                               0.61                            \n",
       "1                                               0.76                            \n",
       "2                                               0.65                            \n",
       "3                                               0.91                            \n",
       "4                                                NaN                            \n",
       "\n",
       "   PSI 11 — Postoperative respiratory failure rate (Denominator)  \\\n",
       "0                                              760.0               \n",
       "1                                              364.0               \n",
       "2                                              427.0               \n",
       "3                                               36.0               \n",
       "4                                                NaN               \n",
       "\n",
       "   PSI 11 — Postoperative respiratory failure rate (Score)  \\\n",
       "0                                               8.92         \n",
       "1                                               6.87         \n",
       "2                                               3.89         \n",
       "3                                               6.01         \n",
       "4                                                NaN         \n",
       "\n",
       "   PSI 12 — Perioperative pulmonary embolism or deep vein thrombosis rate (Denominator)  \\\n",
       "0                                             2040.0                                      \n",
       "1                                              738.0                                      \n",
       "2                                             1504.0                                      \n",
       "3                                               75.0                                      \n",
       "4                                                NaN                                      \n",
       "\n",
       "   PSI 12 — Perioperative pulmonary embolism or deep vein thrombosis rate (Score)  \\\n",
       "0                                               3.33                                \n",
       "1                                               2.54                                \n",
       "2                                               2.80                                \n",
       "3                                               3.80                                \n",
       "4                                                NaN                                \n",
       "\n",
       "   PSI 13 — Postoperative sepsis rate (Denominator)  \\\n",
       "0                                             735.0   \n",
       "1                                             326.0   \n",
       "2                                             430.0   \n",
       "3                                              31.0   \n",
       "4                                               NaN   \n",
       "\n",
       "   PSI 13 — Postoperative sepsis rate (Score)  \\\n",
       "0                                        5.98   \n",
       "1                                        3.44   \n",
       "2                                        3.72   \n",
       "3                                        4.05   \n",
       "4                                         NaN   \n",
       "\n",
       "   PSI 14 — Postoperative wound dehiscence rate (Denominator)  \\\n",
       "0                                              569.0            \n",
       "1                                              186.0            \n",
       "2                                              336.0            \n",
       "3                                                NaN            \n",
       "4                                                NaN            \n",
       "\n",
       "   PSI 14 — Postoperative wound dehiscence rate (Score)  \\\n",
       "0                                               0.65      \n",
       "1                                               0.76      \n",
       "2                                               0.68      \n",
       "3                                                NaN      \n",
       "4                                                NaN      \n",
       "\n",
       "   PSI 15 — Abdominopelvic accidental puncture or laceration rate (Denominator)  \\\n",
       "0                                             1433.0                              \n",
       "1                                              486.0                              \n",
       "2                                              911.0                              \n",
       "3                                               46.0                              \n",
       "4                                                NaN                              \n",
       "\n",
       "   PSI 15 — Abdominopelvic accidental puncture or laceration rate (Score)  \\\n",
       "0                                               1.21                        \n",
       "1                                               0.87                        \n",
       "2                                               1.33                        \n",
       "3                                               1.02                        \n",
       "4                                                NaN                        \n",
       "\n",
       "   PSI 3 — Pressure ulcer rate (Denominator)  \\\n",
       "0                                     5614.0   \n",
       "1                                     2186.0   \n",
       "2                                     4405.0   \n",
       "3                                      511.0   \n",
       "4                                      173.0   \n",
       "\n",
       "   PSI 3 — Pressure ulcer rate (Score)  \\\n",
       "0                                 0.23   \n",
       "1                                 0.86   \n",
       "2                                 1.83   \n",
       "3                                 0.32   \n",
       "4                                 0.50   \n",
       "\n",
       "   PSI 4 — Death rate among surgical inpatients with serious treatable complications (Denominator)  \\\n",
       "0                                              120.0                                                 \n",
       "1                                               35.0                                                 \n",
       "2                                               84.0                                                 \n",
       "3                                                NaN                                                 \n",
       "4                                                NaN                                                 \n",
       "\n",
       "   PSI 4 — Death rate among surgical inpatients with serious treatable complications (Score)  \\\n",
       "0                                             173.39                                           \n",
       "1                                             142.88                                           \n",
       "2                                             157.42                                           \n",
       "3                                                NaN                                           \n",
       "4                                                NaN                                           \n",
       "\n",
       "   PSI 6 — Iatrogenic pneumothorax rate (Denominator)  \\\n",
       "0                                             7181.0    \n",
       "1                                             2901.0    \n",
       "2                                             5559.0    \n",
       "3                                              681.0    \n",
       "4                                              193.0    \n",
       "\n",
       "   PSI 6 — Iatrogenic pneumothorax rate (Score)  \\\n",
       "0                                          0.17   \n",
       "1                                          0.17   \n",
       "2                                          0.26   \n",
       "3                                          0.18   \n",
       "4                                          0.19   \n",
       "\n",
       "   PSI 7 — Infections from a large venous catheter (Denominator)  \\\n",
       "0                                                NaN               \n",
       "1                                                NaN               \n",
       "2                                                NaN               \n",
       "3                                                NaN               \n",
       "4                                                NaN               \n",
       "\n",
       "   PSI 7 — Infections from a large venous catheter (Score)  \\\n",
       "0                                                NaN         \n",
       "1                                                NaN         \n",
       "2                                                NaN         \n",
       "3                                                NaN         \n",
       "4                                                NaN         \n",
       "\n",
       "   PSI 8 — In-hospital fall with hip fracture rate (Denominator)  \\\n",
       "0                                             7513.0               \n",
       "1                                             2910.0               \n",
       "2                                             5795.0               \n",
       "3                                              675.0               \n",
       "4                                              209.0               \n",
       "\n",
       "   PSI 8 — In-hospital fall with hip fracture rate (Score)  \\\n",
       "0                                               0.10         \n",
       "1                                               0.06         \n",
       "2                                               0.05         \n",
       "3                                               0.07         \n",
       "4                                               0.07         \n",
       "\n",
       "   PSI 9 — Perioperative hemorrhage or hematoma rate (Denominator)  \\\n",
       "0                                                NaN                 \n",
       "1                                                NaN                 \n",
       "2                                                NaN                 \n",
       "3                                                NaN                 \n",
       "4                                                NaN                 \n",
       "\n",
       "   PSI 9 — Perioperative hemorrhage or hematoma rate (Score)  \\\n",
       "0                                                NaN           \n",
       "1                                                NaN           \n",
       "2                                                NaN           \n",
       "3                                                NaN           \n",
       "4                                                NaN           \n",
       "\n",
       "   PSI 9 — Postoperative hemorrhage or hematoma rate (Denominator)  \\\n",
       "0                                             1882.0                 \n",
       "1                                              706.0                 \n",
       "2                                             1409.0                 \n",
       "3                                               71.0                 \n",
       "4                                                NaN                 \n",
       "\n",
       "   PSI 9 — Postoperative hemorrhage or hematoma rate (Score)  \\\n",
       "0                                               2.33           \n",
       "1                                               2.08           \n",
       "2                                               3.46           \n",
       "3                                               2.37           \n",
       "4                                                NaN           \n",
       "\n",
       "   PSI 90 — Patient safety and adverse events composite (Score)  \\\n",
       "0                                               1.01              \n",
       "1                                               0.91              \n",
       "2                                               1.10              \n",
       "3                                               0.99              \n",
       "4                                                NaN              \n",
       "\n",
       "   READM 30 AMI — Acute Myocardial Infarction (AMI) 30-Day Readmission Rate (Denominator)  \\\n",
       "0                                                NaN                                        \n",
       "1                                                NaN                                        \n",
       "2                                                NaN                                        \n",
       "3                                                NaN                                        \n",
       "4                                                NaN                                        \n",
       "\n",
       "   READM 30 AMI — Acute Myocardial Infarction (AMI) 30-Day Readmission Rate (Score)  \\\n",
       "0                                                NaN                                  \n",
       "1                                                NaN                                  \n",
       "2                                                NaN                                  \n",
       "3                                                NaN                                  \n",
       "4                                                NaN                                  \n",
       "\n",
       "   READM 30 COPD — Rate of unplanned readmission for chronic obstructive pulmonary disease (COPD) patients (Denominator)  \\\n",
       "0                                                NaN                                                                       \n",
       "1                                                NaN                                                                       \n",
       "2                                                NaN                                                                       \n",
       "3                                                NaN                                                                       \n",
       "4                                                NaN                                                                       \n",
       "\n",
       "   READM 30 COPD — Rate of unplanned readmission for chronic obstructive pulmonary disease (COPD) patients (Score)  \\\n",
       "0                                                NaN                                                                 \n",
       "1                                                NaN                                                                 \n",
       "2                                                NaN                                                                 \n",
       "3                                                NaN                                                                 \n",
       "4                                                NaN                                                                 \n",
       "\n",
       "   READM 30 HF — Heart failure (HF) 30-Day Readmission Rate (Denominator)  \\\n",
       "0                                                NaN                        \n",
       "1                                                NaN                        \n",
       "2                                                NaN                        \n",
       "3                                                NaN                        \n",
       "4                                                NaN                        \n",
       "\n",
       "   READM 30 HF — Heart failure (HF) 30-Day Readmission Rate (Score)  \\\n",
       "0                                                NaN                  \n",
       "1                                                NaN                  \n",
       "2                                                NaN                  \n",
       "3                                                NaN                  \n",
       "4                                                NaN                  \n",
       "\n",
       "   READM 30 HIP KNEE — Rate of readmission after hip/knee surgery (Denominator)  \\\n",
       "0                                                NaN                              \n",
       "1                                                NaN                              \n",
       "2                                                NaN                              \n",
       "3                                                NaN                              \n",
       "4                                                NaN                              \n",
       "\n",
       "   READM 30 HIP KNEE — Rate of readmission after hip/knee surgery (Score)  \\\n",
       "0                                                NaN                        \n",
       "1                                                NaN                        \n",
       "2                                                NaN                        \n",
       "3                                                NaN                        \n",
       "4                                                NaN                        \n",
       "\n",
       "   READM 30 HOSP WIDE — Rate of readmission after discharge from hospital (hospital-wide) (Denominator)  \\\n",
       "0                                                NaN                                                      \n",
       "1                                                NaN                                                      \n",
       "2                                                NaN                                                      \n",
       "3                                                NaN                                                      \n",
       "4                                                NaN                                                      \n",
       "\n",
       "   READM 30 HOSP WIDE — Rate of readmission after discharge from hospital (hospital-wide) (Score)  \\\n",
       "0                                                NaN                                                \n",
       "1                                                NaN                                                \n",
       "2                                                NaN                                                \n",
       "3                                                NaN                                                \n",
       "4                                                NaN                                                \n",
       "\n",
       "   READM 30 PN — Pneumonia (PN) 30-Day Readmission Rate (Denominator)  \\\n",
       "0                                                NaN                    \n",
       "1                                                NaN                    \n",
       "2                                                NaN                    \n",
       "3                                                NaN                    \n",
       "4                                                NaN                    \n",
       "\n",
       "   READM 30 PN — Pneumonia (PN) 30-Day Readmission Rate (Score)  \\\n",
       "0                                                NaN              \n",
       "1                                                NaN              \n",
       "2                                                NaN              \n",
       "3                                                NaN              \n",
       "4                                                NaN              \n",
       "\n",
       "   READM 30 STK — Rate of unplanned readmission for stroke patients (Denominator)  \\\n",
       "0                                                NaN                                \n",
       "1                                                NaN                                \n",
       "2                                                NaN                                \n",
       "3                                                NaN                                \n",
       "4                                                NaN                                \n",
       "\n",
       "   READM 30 STK — Rate of unplanned readmission for stroke patients (Score)  \n",
       "0                                                NaN                         \n",
       "1                                                NaN                         \n",
       "2                                                NaN                         \n",
       "3                                                NaN                         \n",
       "4                                                NaN                         "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "del df2\n",
    "df = main_df.copy(deep=True)\n",
    "del main_df\n",
    "\n",
    "print(df.shape)\n",
    "df = df.loc[:, ~df.T.duplicated(keep='first')]\n",
    "df.dropna(how='all', axis=1, inplace=True)\n",
    "print(df.shape)\n",
    "df.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "4e61de6d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(183734, 58)\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>Complications &amp; Death: COMP HIP KNEE — Rate of complications for hip/knee replacement patients (Denominator)</th>\n",
       "      <th>Complications &amp; Death: COMP HIP KNEE — Rate of complications for hip/knee replacement patients (Score)</th>\n",
       "      <th>Complications &amp; Death: MORT 30 AMI — AMI 30-Day Mortality Rate (Denominator)</th>\n",
       "      <th>Complications &amp; Death: MORT 30 AMI — AMI 30-Day Mortality Rate (Score)</th>\n",
       "      <th>Complications &amp; Death: MORT 30 CABG — Death rate for CABG surgery patients (Denominator)</th>\n",
       "      <th>Complications &amp; Death: MORT 30 CABG — Death rate for CABG surgery patients (Score)</th>\n",
       "      <th>Complications &amp; Death: MORT 30 COPD — Death rate for COPD patients (Denominator)</th>\n",
       "      <th>Complications &amp; Death: MORT 30 COPD — Death rate for COPD patients (Score)</th>\n",
       "      <th>Complications &amp; Death: MORT 30 HF — Heart failure 30-Day Mortality Rate (Denominator)</th>\n",
       "      <th>Complications &amp; Death: MORT 30 HF — Heart failure 30-Day Mortality Rate (Score)</th>\n",
       "      <th>Complications &amp; Death: MORT 30 PN — Pneumonia 30-Day Mortality Rate (Denominator)</th>\n",
       "      <th>Complications &amp; Death: MORT 30 PN — Pneumonia 30-Day Mortality Rate (Score)</th>\n",
       "      <th>Complications &amp; Death: MORT 30 STK — Death rate for stroke patients (Denominator)</th>\n",
       "      <th>Complications &amp; Death: MORT 30 STK — Death rate for stroke patients (Score)</th>\n",
       "      <th>Complications &amp; Death: PSI 10 — Postoperative acute kidney injury requiring dialysis rate (Denominator)</th>\n",
       "      <th>Complications &amp; Death: PSI 10 — Postoperative acute kidney injury requiring dialysis rate (Score)</th>\n",
       "      <th>Complications &amp; Death: PSI 11 — Postoperative respiratory failure rate (Denominator)</th>\n",
       "      <th>Complications &amp; Death: PSI 11 — Postoperative respiratory failure rate (Score)</th>\n",
       "      <th>Complications &amp; Death: PSI 12 — Perioperative pulmonary embolism or deep vein thrombosis rate (Denominator)</th>\n",
       "      <th>Complications &amp; Death: PSI 12 — Perioperative pulmonary embolism or deep vein thrombosis rate (Score)</th>\n",
       "      <th>Complications &amp; Death: PSI 13 — Postoperative sepsis rate (Denominator)</th>\n",
       "      <th>Complications &amp; Death: PSI 13 — Postoperative sepsis rate (Score)</th>\n",
       "      <th>Complications &amp; Death: PSI 14 — Postoperative wound dehiscence rate (Denominator)</th>\n",
       "      <th>Complications &amp; Death: PSI 14 — Postoperative wound dehiscence rate (Score)</th>\n",
       "      <th>Complications &amp; Death: PSI 15 — Abdominopelvic accidental puncture or laceration rate (Denominator)</th>\n",
       "      <th>Complications &amp; Death: PSI 15 — Abdominopelvic accidental puncture or laceration rate (Score)</th>\n",
       "      <th>Complications &amp; Death: PSI 3 — Pressure ulcer rate (Denominator)</th>\n",
       "      <th>Complications &amp; Death: PSI 3 — Pressure ulcer rate (Score)</th>\n",
       "      <th>Complications &amp; Death: PSI 4 — Death rate among surgical inpatients with serious treatable complications (Denominator)</th>\n",
       "      <th>Complications &amp; Death: PSI 4 — Death rate among surgical inpatients with serious treatable complications (Score)</th>\n",
       "      <th>Complications &amp; Death: PSI 6 — Iatrogenic pneumothorax rate (Denominator)</th>\n",
       "      <th>Complications &amp; Death: PSI 6 — Iatrogenic pneumothorax rate (Score)</th>\n",
       "      <th>Complications &amp; Death: PSI 7 — Infections from a large venous catheter (Denominator)</th>\n",
       "      <th>Complications &amp; Death: PSI 7 — Infections from a large venous catheter (Score)</th>\n",
       "      <th>Complications &amp; Death: PSI 8 — In-hospital fall with hip fracture rate (Denominator)</th>\n",
       "      <th>Complications &amp; Death: PSI 8 — In-hospital fall with hip fracture rate (Score)</th>\n",
       "      <th>Complications &amp; Death: PSI 9 — Perioperative hemorrhage or hematoma rate (Denominator)</th>\n",
       "      <th>Complications &amp; Death: PSI 9 — Perioperative hemorrhage or hematoma rate (Score)</th>\n",
       "      <th>Complications &amp; Death: PSI 9 — Postoperative hemorrhage or hematoma rate (Denominator)</th>\n",
       "      <th>Complications &amp; Death: PSI 9 — Postoperative hemorrhage or hematoma rate (Score)</th>\n",
       "      <th>Complications &amp; Death: PSI 90 — Patient safety and adverse events composite (Score)</th>\n",
       "      <th>Complications &amp; Death: READM 30 AMI — Acute Myocardial Infarction (AMI) 30-Day Readmission Rate (Denominator)</th>\n",
       "      <th>Complications &amp; Death: READM 30 AMI — Acute Myocardial Infarction (AMI) 30-Day Readmission Rate (Score)</th>\n",
       "      <th>Complications &amp; Death: READM 30 COPD — Rate of unplanned readmission for chronic obstructive pulmonary disease (COPD) patients (Denominator)</th>\n",
       "      <th>Complications &amp; Death: READM 30 COPD — Rate of unplanned readmission for chronic obstructive pulmonary disease (COPD) patients (Score)</th>\n",
       "      <th>Complications &amp; Death: READM 30 HF — Heart failure (HF) 30-Day Readmission Rate (Denominator)</th>\n",
       "      <th>Complications &amp; Death: READM 30 HF — Heart failure (HF) 30-Day Readmission Rate (Score)</th>\n",
       "      <th>Complications &amp; Death: READM 30 HIP KNEE — Rate of readmission after hip/knee surgery (Denominator)</th>\n",
       "      <th>Complications &amp; Death: READM 30 HIP KNEE — Rate of readmission after hip/knee surgery (Score)</th>\n",
       "      <th>Complications &amp; Death: READM 30 HOSP WIDE — Rate of readmission after discharge from hospital (hospital-wide) (Denominator)</th>\n",
       "      <th>Complications &amp; Death: READM 30 HOSP WIDE — Rate of readmission after discharge from hospital (hospital-wide) (Score)</th>\n",
       "      <th>Complications &amp; Death: READM 30 PN — Pneumonia (PN) 30-Day Readmission Rate (Denominator)</th>\n",
       "      <th>Complications &amp; Death: READM 30 PN — Pneumonia (PN) 30-Day Readmission Rate (Score)</th>\n",
       "      <th>Complications &amp; Death: READM 30 STK — Rate of unplanned readmission for stroke patients (Denominator)</th>\n",
       "      <th>Complications &amp; Death: READM 30 STK — Rate of unplanned readmission for stroke patients (Score)</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>102.0</td>\n",
       "      <td>2.4</td>\n",
       "      <td>317.0</td>\n",
       "      <td>12.4</td>\n",
       "      <td>172.0</td>\n",
       "      <td>4.7</td>\n",
       "      <td>182.0</td>\n",
       "      <td>8.5</td>\n",
       "      <td>630.0</td>\n",
       "      <td>8.3</td>\n",
       "      <td>407.0</td>\n",
       "      <td>15.9</td>\n",
       "      <td>489.0</td>\n",
       "      <td>16.4</td>\n",
       "      <td>746.0</td>\n",
       "      <td>0.61</td>\n",
       "      <td>760.0</td>\n",
       "      <td>8.92</td>\n",
       "      <td>2040.0</td>\n",
       "      <td>3.33</td>\n",
       "      <td>735.0</td>\n",
       "      <td>5.98</td>\n",
       "      <td>569.0</td>\n",
       "      <td>0.65</td>\n",
       "      <td>1433.0</td>\n",
       "      <td>1.21</td>\n",
       "      <td>5614.0</td>\n",
       "      <td>0.23</td>\n",
       "      <td>120.0</td>\n",
       "      <td>173.39</td>\n",
       "      <td>7181.0</td>\n",
       "      <td>0.17</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7513.0</td>\n",
       "      <td>0.10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1882.0</td>\n",
       "      <td>2.33</td>\n",
       "      <td>1.01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>010005</td>\n",
       "      <td>01</td>\n",
       "      <td>2023</td>\n",
       "      <td>134.0</td>\n",
       "      <td>1.8</td>\n",
       "      <td>52.0</td>\n",
       "      <td>12.6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>195.0</td>\n",
       "      <td>8.1</td>\n",
       "      <td>153.0</td>\n",
       "      <td>16.9</td>\n",
       "      <td>369.0</td>\n",
       "      <td>21.8</td>\n",
       "      <td>100.0</td>\n",
       "      <td>16.6</td>\n",
       "      <td>371.0</td>\n",
       "      <td>0.76</td>\n",
       "      <td>364.0</td>\n",
       "      <td>6.87</td>\n",
       "      <td>738.0</td>\n",
       "      <td>2.54</td>\n",
       "      <td>326.0</td>\n",
       "      <td>3.44</td>\n",
       "      <td>186.0</td>\n",
       "      <td>0.76</td>\n",
       "      <td>486.0</td>\n",
       "      <td>0.87</td>\n",
       "      <td>2186.0</td>\n",
       "      <td>0.86</td>\n",
       "      <td>35.0</td>\n",
       "      <td>142.88</td>\n",
       "      <td>2901.0</td>\n",
       "      <td>0.17</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2910.0</td>\n",
       "      <td>0.06</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>706.0</td>\n",
       "      <td>2.08</td>\n",
       "      <td>0.91</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>010006</td>\n",
       "      <td>01</td>\n",
       "      <td>2023</td>\n",
       "      <td>234.0</td>\n",
       "      <td>3.4</td>\n",
       "      <td>295.0</td>\n",
       "      <td>16.5</td>\n",
       "      <td>117.0</td>\n",
       "      <td>3.5</td>\n",
       "      <td>209.0</td>\n",
       "      <td>7.8</td>\n",
       "      <td>468.0</td>\n",
       "      <td>12.2</td>\n",
       "      <td>528.0</td>\n",
       "      <td>17.8</td>\n",
       "      <td>261.0</td>\n",
       "      <td>18.9</td>\n",
       "      <td>433.0</td>\n",
       "      <td>0.65</td>\n",
       "      <td>427.0</td>\n",
       "      <td>3.89</td>\n",
       "      <td>1504.0</td>\n",
       "      <td>2.80</td>\n",
       "      <td>430.0</td>\n",
       "      <td>3.72</td>\n",
       "      <td>336.0</td>\n",
       "      <td>0.68</td>\n",
       "      <td>911.0</td>\n",
       "      <td>1.33</td>\n",
       "      <td>4405.0</td>\n",
       "      <td>1.83</td>\n",
       "      <td>84.0</td>\n",
       "      <td>157.42</td>\n",
       "      <td>5559.0</td>\n",
       "      <td>0.26</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5795.0</td>\n",
       "      <td>0.05</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1409.0</td>\n",
       "      <td>3.46</td>\n",
       "      <td>1.10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>010007</td>\n",
       "      <td>01</td>\n",
       "      <td>2023</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>63.0</td>\n",
       "      <td>10.3</td>\n",
       "      <td>45.0</td>\n",
       "      <td>13.9</td>\n",
       "      <td>106.0</td>\n",
       "      <td>21.7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>34.0</td>\n",
       "      <td>0.91</td>\n",
       "      <td>36.0</td>\n",
       "      <td>6.01</td>\n",
       "      <td>75.0</td>\n",
       "      <td>3.80</td>\n",
       "      <td>31.0</td>\n",
       "      <td>4.05</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>46.0</td>\n",
       "      <td>1.02</td>\n",
       "      <td>511.0</td>\n",
       "      <td>0.32</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>681.0</td>\n",
       "      <td>0.18</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>675.0</td>\n",
       "      <td>0.07</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>71.0</td>\n",
       "      <td>2.37</td>\n",
       "      <td>0.99</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>010008</td>\n",
       "      <td>01</td>\n",
       "      <td>2023</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>26.0</td>\n",
       "      <td>19.7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>173.0</td>\n",
       "      <td>0.50</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>193.0</td>\n",
       "      <td>0.19</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>209.0</td>\n",
       "      <td>0.07</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>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  \\\n",
       "0      010001         01      2023   \n",
       "1      010005         01      2023   \n",
       "2      010006         01      2023   \n",
       "3      010007         01      2023   \n",
       "4      010008         01      2023   \n",
       "\n",
       "   Complications & Death: COMP HIP KNEE — Rate of complications for hip/knee replacement patients (Denominator)  \\\n",
       "0                                              102.0                                                              \n",
       "1                                              134.0                                                              \n",
       "2                                              234.0                                                              \n",
       "3                                                NaN                                                              \n",
       "4                                                NaN                                                              \n",
       "\n",
       "   Complications & Death: COMP HIP KNEE — Rate of complications for hip/knee replacement patients (Score)  \\\n",
       "0                                                2.4                                                        \n",
       "1                                                1.8                                                        \n",
       "2                                                3.4                                                        \n",
       "3                                                NaN                                                        \n",
       "4                                                NaN                                                        \n",
       "\n",
       "   Complications & Death: MORT 30 AMI — AMI 30-Day Mortality Rate (Denominator)  \\\n",
       "0                                              317.0                              \n",
       "1                                               52.0                              \n",
       "2                                              295.0                              \n",
       "3                                                NaN                              \n",
       "4                                                NaN                              \n",
       "\n",
       "   Complications & Death: MORT 30 AMI — AMI 30-Day Mortality Rate (Score)  \\\n",
       "0                                               12.4                        \n",
       "1                                               12.6                        \n",
       "2                                               16.5                        \n",
       "3                                                NaN                        \n",
       "4                                                NaN                        \n",
       "\n",
       "   Complications & Death: MORT 30 CABG — Death rate for CABG surgery patients (Denominator)  \\\n",
       "0                                              172.0                                          \n",
       "1                                                NaN                                          \n",
       "2                                              117.0                                          \n",
       "3                                                NaN                                          \n",
       "4                                                NaN                                          \n",
       "\n",
       "   Complications & Death: MORT 30 CABG — Death rate for CABG surgery patients (Score)  \\\n",
       "0                                                4.7                                    \n",
       "1                                                NaN                                    \n",
       "2                                                3.5                                    \n",
       "3                                                NaN                                    \n",
       "4                                                NaN                                    \n",
       "\n",
       "   Complications & Death: MORT 30 COPD — Death rate for COPD patients (Denominator)  \\\n",
       "0                                              182.0                                  \n",
       "1                                              195.0                                  \n",
       "2                                              209.0                                  \n",
       "3                                               63.0                                  \n",
       "4                                                NaN                                  \n",
       "\n",
       "   Complications & Death: MORT 30 COPD — Death rate for COPD patients (Score)  \\\n",
       "0                                                8.5                            \n",
       "1                                                8.1                            \n",
       "2                                                7.8                            \n",
       "3                                               10.3                            \n",
       "4                                                NaN                            \n",
       "\n",
       "   Complications & Death: MORT 30 HF — Heart failure 30-Day Mortality Rate (Denominator)  \\\n",
       "0                                              630.0                                       \n",
       "1                                              153.0                                       \n",
       "2                                              468.0                                       \n",
       "3                                               45.0                                       \n",
       "4                                                NaN                                       \n",
       "\n",
       "   Complications & Death: MORT 30 HF — Heart failure 30-Day Mortality Rate (Score)  \\\n",
       "0                                                8.3                                 \n",
       "1                                               16.9                                 \n",
       "2                                               12.2                                 \n",
       "3                                               13.9                                 \n",
       "4                                                NaN                                 \n",
       "\n",
       "   Complications & Death: MORT 30 PN — Pneumonia 30-Day Mortality Rate (Denominator)  \\\n",
       "0                                              407.0                                   \n",
       "1                                              369.0                                   \n",
       "2                                              528.0                                   \n",
       "3                                              106.0                                   \n",
       "4                                               26.0                                   \n",
       "\n",
       "   Complications & Death: MORT 30 PN — Pneumonia 30-Day Mortality Rate (Score)  \\\n",
       "0                                               15.9                             \n",
       "1                                               21.8                             \n",
       "2                                               17.8                             \n",
       "3                                               21.7                             \n",
       "4                                               19.7                             \n",
       "\n",
       "   Complications & Death: MORT 30 STK — Death rate for stroke patients (Denominator)  \\\n",
       "0                                              489.0                                   \n",
       "1                                              100.0                                   \n",
       "2                                              261.0                                   \n",
       "3                                                NaN                                   \n",
       "4                                                NaN                                   \n",
       "\n",
       "   Complications & Death: MORT 30 STK — Death rate for stroke patients (Score)  \\\n",
       "0                                               16.4                             \n",
       "1                                               16.6                             \n",
       "2                                               18.9                             \n",
       "3                                                NaN                             \n",
       "4                                                NaN                             \n",
       "\n",
       "   Complications & Death: PSI 10 — Postoperative acute kidney injury requiring dialysis rate (Denominator)  \\\n",
       "0                                              746.0                                                         \n",
       "1                                              371.0                                                         \n",
       "2                                              433.0                                                         \n",
       "3                                               34.0                                                         \n",
       "4                                                NaN                                                         \n",
       "\n",
       "   Complications & Death: PSI 10 — Postoperative acute kidney injury requiring dialysis rate (Score)  \\\n",
       "0                                               0.61                                                   \n",
       "1                                               0.76                                                   \n",
       "2                                               0.65                                                   \n",
       "3                                               0.91                                                   \n",
       "4                                                NaN                                                   \n",
       "\n",
       "   Complications & Death: PSI 11 — Postoperative respiratory failure rate (Denominator)  \\\n",
       "0                                              760.0                                      \n",
       "1                                              364.0                                      \n",
       "2                                              427.0                                      \n",
       "3                                               36.0                                      \n",
       "4                                                NaN                                      \n",
       "\n",
       "   Complications & Death: PSI 11 — Postoperative respiratory failure rate (Score)  \\\n",
       "0                                               8.92                                \n",
       "1                                               6.87                                \n",
       "2                                               3.89                                \n",
       "3                                               6.01                                \n",
       "4                                                NaN                                \n",
       "\n",
       "   Complications & Death: PSI 12 — Perioperative pulmonary embolism or deep vein thrombosis rate (Denominator)  \\\n",
       "0                                             2040.0                                                             \n",
       "1                                              738.0                                                             \n",
       "2                                             1504.0                                                             \n",
       "3                                               75.0                                                             \n",
       "4                                                NaN                                                             \n",
       "\n",
       "   Complications & Death: PSI 12 — Perioperative pulmonary embolism or deep vein thrombosis rate (Score)  \\\n",
       "0                                               3.33                                                       \n",
       "1                                               2.54                                                       \n",
       "2                                               2.80                                                       \n",
       "3                                               3.80                                                       \n",
       "4                                                NaN                                                       \n",
       "\n",
       "   Complications & Death: PSI 13 — Postoperative sepsis rate (Denominator)  \\\n",
       "0                                              735.0                         \n",
       "1                                              326.0                         \n",
       "2                                              430.0                         \n",
       "3                                               31.0                         \n",
       "4                                                NaN                         \n",
       "\n",
       "   Complications & Death: PSI 13 — Postoperative sepsis rate (Score)  \\\n",
       "0                                               5.98                   \n",
       "1                                               3.44                   \n",
       "2                                               3.72                   \n",
       "3                                               4.05                   \n",
       "4                                                NaN                   \n",
       "\n",
       "   Complications & Death: PSI 14 — Postoperative wound dehiscence rate (Denominator)  \\\n",
       "0                                              569.0                                   \n",
       "1                                              186.0                                   \n",
       "2                                              336.0                                   \n",
       "3                                                NaN                                   \n",
       "4                                                NaN                                   \n",
       "\n",
       "   Complications & Death: PSI 14 — Postoperative wound dehiscence rate (Score)  \\\n",
       "0                                               0.65                             \n",
       "1                                               0.76                             \n",
       "2                                               0.68                             \n",
       "3                                                NaN                             \n",
       "4                                                NaN                             \n",
       "\n",
       "   Complications & Death: PSI 15 — Abdominopelvic accidental puncture or laceration rate (Denominator)  \\\n",
       "0                                             1433.0                                                     \n",
       "1                                              486.0                                                     \n",
       "2                                              911.0                                                     \n",
       "3                                               46.0                                                     \n",
       "4                                                NaN                                                     \n",
       "\n",
       "   Complications & Death: PSI 15 — Abdominopelvic accidental puncture or laceration rate (Score)  \\\n",
       "0                                               1.21                                               \n",
       "1                                               0.87                                               \n",
       "2                                               1.33                                               \n",
       "3                                               1.02                                               \n",
       "4                                                NaN                                               \n",
       "\n",
       "   Complications & Death: PSI 3 — Pressure ulcer rate (Denominator)  \\\n",
       "0                                             5614.0                  \n",
       "1                                             2186.0                  \n",
       "2                                             4405.0                  \n",
       "3                                              511.0                  \n",
       "4                                              173.0                  \n",
       "\n",
       "   Complications & Death: PSI 3 — Pressure ulcer rate (Score)  \\\n",
       "0                                               0.23            \n",
       "1                                               0.86            \n",
       "2                                               1.83            \n",
       "3                                               0.32            \n",
       "4                                               0.50            \n",
       "\n",
       "   Complications & Death: PSI 4 — Death rate among surgical inpatients with serious treatable complications (Denominator)  \\\n",
       "0                                              120.0                                                                        \n",
       "1                                               35.0                                                                        \n",
       "2                                               84.0                                                                        \n",
       "3                                                NaN                                                                        \n",
       "4                                                NaN                                                                        \n",
       "\n",
       "   Complications & Death: PSI 4 — Death rate among surgical inpatients with serious treatable complications (Score)  \\\n",
       "0                                             173.39                                                                  \n",
       "1                                             142.88                                                                  \n",
       "2                                             157.42                                                                  \n",
       "3                                                NaN                                                                  \n",
       "4                                                NaN                                                                  \n",
       "\n",
       "   Complications & Death: PSI 6 — Iatrogenic pneumothorax rate (Denominator)  \\\n",
       "0                                             7181.0                           \n",
       "1                                             2901.0                           \n",
       "2                                             5559.0                           \n",
       "3                                              681.0                           \n",
       "4                                              193.0                           \n",
       "\n",
       "   Complications & Death: PSI 6 — Iatrogenic pneumothorax rate (Score)  \\\n",
       "0                                               0.17                     \n",
       "1                                               0.17                     \n",
       "2                                               0.26                     \n",
       "3                                               0.18                     \n",
       "4                                               0.19                     \n",
       "\n",
       "   Complications & Death: PSI 7 — Infections from a large venous catheter (Denominator)  \\\n",
       "0                                                NaN                                      \n",
       "1                                                NaN                                      \n",
       "2                                                NaN                                      \n",
       "3                                                NaN                                      \n",
       "4                                                NaN                                      \n",
       "\n",
       "   Complications & Death: PSI 7 — Infections from a large venous catheter (Score)  \\\n",
       "0                                                NaN                                \n",
       "1                                                NaN                                \n",
       "2                                                NaN                                \n",
       "3                                                NaN                                \n",
       "4                                                NaN                                \n",
       "\n",
       "   Complications & Death: PSI 8 — In-hospital fall with hip fracture rate (Denominator)  \\\n",
       "0                                             7513.0                                      \n",
       "1                                             2910.0                                      \n",
       "2                                             5795.0                                      \n",
       "3                                              675.0                                      \n",
       "4                                              209.0                                      \n",
       "\n",
       "   Complications & Death: PSI 8 — In-hospital fall with hip fracture rate (Score)  \\\n",
       "0                                               0.10                                \n",
       "1                                               0.06                                \n",
       "2                                               0.05                                \n",
       "3                                               0.07                                \n",
       "4                                               0.07                                \n",
       "\n",
       "   Complications & Death: PSI 9 — Perioperative hemorrhage or hematoma rate (Denominator)  \\\n",
       "0                                                NaN                                        \n",
       "1                                                NaN                                        \n",
       "2                                                NaN                                        \n",
       "3                                                NaN                                        \n",
       "4                                                NaN                                        \n",
       "\n",
       "   Complications & Death: PSI 9 — Perioperative hemorrhage or hematoma rate (Score)  \\\n",
       "0                                                NaN                                  \n",
       "1                                                NaN                                  \n",
       "2                                                NaN                                  \n",
       "3                                                NaN                                  \n",
       "4                                                NaN                                  \n",
       "\n",
       "   Complications & Death: PSI 9 — Postoperative hemorrhage or hematoma rate (Denominator)  \\\n",
       "0                                             1882.0                                        \n",
       "1                                              706.0                                        \n",
       "2                                             1409.0                                        \n",
       "3                                               71.0                                        \n",
       "4                                                NaN                                        \n",
       "\n",
       "   Complications & Death: PSI 9 — Postoperative hemorrhage or hematoma rate (Score)  \\\n",
       "0                                               2.33                                  \n",
       "1                                               2.08                                  \n",
       "2                                               3.46                                  \n",
       "3                                               2.37                                  \n",
       "4                                                NaN                                  \n",
       "\n",
       "   Complications & Death: PSI 90 — Patient safety and adverse events composite (Score)  \\\n",
       "0                                               1.01                                     \n",
       "1                                               0.91                                     \n",
       "2                                               1.10                                     \n",
       "3                                               0.99                                     \n",
       "4                                                NaN                                     \n",
       "\n",
       "   Complications & Death: READM 30 AMI — Acute Myocardial Infarction (AMI) 30-Day Readmission Rate (Denominator)  \\\n",
       "0                                                NaN                                                               \n",
       "1                                                NaN                                                               \n",
       "2                                                NaN                                                               \n",
       "3                                                NaN                                                               \n",
       "4                                                NaN                                                               \n",
       "\n",
       "   Complications & Death: READM 30 AMI — Acute Myocardial Infarction (AMI) 30-Day Readmission Rate (Score)  \\\n",
       "0                                                NaN                                                         \n",
       "1                                                NaN                                                         \n",
       "2                                                NaN                                                         \n",
       "3                                                NaN                                                         \n",
       "4                                                NaN                                                         \n",
       "\n",
       "   Complications & Death: READM 30 COPD — Rate of unplanned readmission for chronic obstructive pulmonary disease (COPD) patients (Denominator)  \\\n",
       "0                                                NaN                                                                                              \n",
       "1                                                NaN                                                                                              \n",
       "2                                                NaN                                                                                              \n",
       "3                                                NaN                                                                                              \n",
       "4                                                NaN                                                                                              \n",
       "\n",
       "   Complications & Death: READM 30 COPD — Rate of unplanned readmission for chronic obstructive pulmonary disease (COPD) patients (Score)  \\\n",
       "0                                                NaN                                                                                        \n",
       "1                                                NaN                                                                                        \n",
       "2                                                NaN                                                                                        \n",
       "3                                                NaN                                                                                        \n",
       "4                                                NaN                                                                                        \n",
       "\n",
       "   Complications & Death: READM 30 HF — Heart failure (HF) 30-Day Readmission Rate (Denominator)  \\\n",
       "0                                                NaN                                               \n",
       "1                                                NaN                                               \n",
       "2                                                NaN                                               \n",
       "3                                                NaN                                               \n",
       "4                                                NaN                                               \n",
       "\n",
       "   Complications & Death: READM 30 HF — Heart failure (HF) 30-Day Readmission Rate (Score)  \\\n",
       "0                                                NaN                                         \n",
       "1                                                NaN                                         \n",
       "2                                                NaN                                         \n",
       "3                                                NaN                                         \n",
       "4                                                NaN                                         \n",
       "\n",
       "   Complications & Death: READM 30 HIP KNEE — Rate of readmission after hip/knee surgery (Denominator)  \\\n",
       "0                                                NaN                                                     \n",
       "1                                                NaN                                                     \n",
       "2                                                NaN                                                     \n",
       "3                                                NaN                                                     \n",
       "4                                                NaN                                                     \n",
       "\n",
       "   Complications & Death: READM 30 HIP KNEE — Rate of readmission after hip/knee surgery (Score)  \\\n",
       "0                                                NaN                                               \n",
       "1                                                NaN                                               \n",
       "2                                                NaN                                               \n",
       "3                                                NaN                                               \n",
       "4                                                NaN                                               \n",
       "\n",
       "   Complications & Death: READM 30 HOSP WIDE — Rate of readmission after discharge from hospital (hospital-wide) (Denominator)  \\\n",
       "0                                                NaN                                                                             \n",
       "1                                                NaN                                                                             \n",
       "2                                                NaN                                                                             \n",
       "3                                                NaN                                                                             \n",
       "4                                                NaN                                                                             \n",
       "\n",
       "   Complications & Death: READM 30 HOSP WIDE — Rate of readmission after discharge from hospital (hospital-wide) (Score)  \\\n",
       "0                                                NaN                                                                       \n",
       "1                                                NaN                                                                       \n",
       "2                                                NaN                                                                       \n",
       "3                                                NaN                                                                       \n",
       "4                                                NaN                                                                       \n",
       "\n",
       "   Complications & Death: READM 30 PN — Pneumonia (PN) 30-Day Readmission Rate (Denominator)  \\\n",
       "0                                                NaN                                           \n",
       "1                                                NaN                                           \n",
       "2                                                NaN                                           \n",
       "3                                                NaN                                           \n",
       "4                                                NaN                                           \n",
       "\n",
       "   Complications & Death: READM 30 PN — Pneumonia (PN) 30-Day Readmission Rate (Score)  \\\n",
       "0                                                NaN                                     \n",
       "1                                                NaN                                     \n",
       "2                                                NaN                                     \n",
       "3                                                NaN                                     \n",
       "4                                                NaN                                     \n",
       "\n",
       "   Complications & Death: READM 30 STK — Rate of unplanned readmission for stroke patients (Denominator)  \\\n",
       "0                                                NaN                                                       \n",
       "1                                                NaN                                                       \n",
       "2                                                NaN                                                       \n",
       "3                                                NaN                                                       \n",
       "4                                                NaN                                                       \n",
       "\n",
       "   Complications & Death: READM 30 STK — Rate of unplanned readmission for stroke patients (Score)  \n",
       "0                                                NaN                                                \n",
       "1                                                NaN                                                \n",
       "2                                                NaN                                                \n",
       "3                                                NaN                                                \n",
       "4                                                NaN                                                "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "for i, n in enumerate(list(df)):\n",
    "    if n in ['Facility ID', 'file_month', 'file_year']:\n",
    "        continue\n",
    "            \n",
    "    df.rename(columns={n: 'Complications & Death: ' + n}, inplace=True)\n",
    "    \n",
    "print(df.shape)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "a6079ecb",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_pickle('dataframes/complication_and_deaths_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
}