HVBP_efficiency_facility.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "04b9cd36",
   "metadata": {},
   "source": [
    "# Generate HVBP efficiency and cost reduction 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": "23d011eb",
   "metadata": {},
   "source": [
    "## Define Custom Functions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "d9818de3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2023/hospitals_01_2023/hvbp_efficiency_and_cost_reduction.csv :  (rows, columns) = (2517, 15)\n",
      "2023/hospitals_04_2023/hvbp_efficiency_and_cost_reduction.csv :  (rows, columns) = (2517, 15)\n",
      "2022/hospitals_01_2022/hvbp_efficiency_and_cost_reduction.csv :  (rows, columns) = (2676, 15)\n",
      "2022/hospitals_04_2022/hvbp_efficiency_and_cost_reduction.csv :  (rows, columns) = (2236, 15)\n",
      "2022/hospitals_07_2022/hvbp_efficiency_and_cost_reduction.csv :  (rows, columns) = (2236, 15)\n",
      "2021/hospitals_01_2021/hvbp_efficiency_and_cost_reduction.csv :  (rows, columns) = (2676, 15)\n",
      "2021/hospitals_03_2021/hvbp_efficiency_and_cost_reduction.csv :  (rows, columns) = (2676, 15)\n",
      "2021/hospitals_04_2021/hvbp_efficiency_and_cost_reduction.csv :  (rows, columns) = (2676, 15)\n",
      "2021/hospitals_07_2021/hvbp_efficiency_and_cost_reduction.csv :  (rows, columns) = (2676, 15)\n",
      "2021/hospitals_10_2021/hvbp_efficiency_and_cost_reduction.csv :  (rows, columns) = (2676, 15)\n",
      "2020/hospitals_archive_10_2020/hvbp_efficiency_12_09_2019.csv :  (rows, columns) = (2731, 14)\n",
      "2020/hospitals_archive_07_2020/hvbp_efficiency.csv :  (rows, columns) = (2731, 14)\n",
      "2020/HOSArchive_Revised_Flatfiles_20200422/hvbp_efficiency_12_09_2019.csv :  (rows, columns) = (2731, 14)\n",
      "2020/HOSArchive_Revised_Flatfiles_20200129/hvbp_efficiency_12_09_2019.csv :  (rows, columns) = (2731, 14)\n",
      "2019/HOSArchive_Revised_Flatfiles_20191030/hvbp_efficiency_11_09_2018.csv :  (rows, columns) = (2786, 14)\n",
      "2019/HOSArchive_Revised_Flatfiles_20190702/hvbp_efficiency_11_09_2018.csv :  (rows, columns) = (2786, 14)\n",
      "2019/HOSArchive_Revised_FlatFiles_20190424/hvbp_efficiency_11_09_2018.csv :  (rows, columns) = (2786, 14)\n",
      "2019/HOSArchive_Revised_Flatfiles_20190321/hvbp_efficiency_11_09_2018.csv :  (rows, columns) = (2786, 14)\n",
      "2018/HOSArchive_Revised_FlatFiles_20181031/hvbp_efficiency_11_07_2017.csv :  (rows, columns) = (2808, 14)\n",
      "2018/HOSArchive_Revised_FlatFiles_20180725/hvbp_efficiency_11_07_2017.csv :  (rows, columns) = (2808, 14)\n",
      "2018/HOSArchive_Revised_FlatFiles_20180523/hvbp_efficiency_11_07_2017.csv :  (rows, columns) = (2808, 14)\n",
      "2018/HOSArchive_Revised_FlatFiles_20180126/hvbp_efficiency_11_07_2017.csv :  (rows, columns) = (2808, 14)\n",
      "2017/HOSArchive_Revised_FlatFiles_20171024/hvbp_efficiency_11_10_2016.csv :  (rows, columns) = (2955, 14)\n",
      "2017/HOSArchive_Revised_FlatFiles_20170726/hvbp_efficiency_11_10_2016.csv :  (rows, columns) = (2955, 14)\n",
      "2017/HOSArchive_Revised_Flatfiles_20170428/hvbp_efficiency_11_10_2016.csv :  (rows, columns) = (2955, 14)\n",
      "2016/HOSArchive_Revised_Flatfiles_20161219/hvbp_efficiency_11_10_2016.csv :  (rows, columns) = (2955, 14)\n",
      "2016/Hospital_Revised_FlatFiles_20161110/hvbp_efficiency_08_26_2016.csv :  (rows, columns) = (3019, 11)\n",
      "2016/HOSArchive_Revised_FlatFiles_20160810/hvbp_efficiency_06_08_2016.csv :  (rows, columns) = (3029, 11)\n",
      "2016/HOSArchive_Revised_FlatFiles_20160504/hvbp_efficiency_02_18_2016.csv :  (rows, columns) = (3041, 11)\n",
      "2015/HOSArchive_Revised_FlatFiles_20151210/hvbp_Efficiency_10_28_2015.csv :  (rows, columns) = (3041, 11)\n",
      "2015/HOSArchive_Revised_FlatFiles_20151008/hvbp_Efficiency_08_06_2015.csv :  (rows, columns) = (3057, 11)\n",
      "2015/HOSArchive_Revised_FlatFiles_20150716/hvbp_Efficiency_05_20_2015.csv :  (rows, columns) = (3084, 11)\n",
      "2015/HOSArchive_Revised_Flatfiles_20150506/hvbp_Efficiency_02_18_2015.csv :  (rows, columns) = (3084, 11)\n",
      "2015/HOSArchive_Revised_Flatfiles_20150416/hvbp_Efficiency_02_18_2015.csv :  (rows, columns) = (3084, 11)\n",
      "2015/HOSArchive_Revised_Flatfiles_20150122/hvbp_efficiency_10_28_2014.csv :  (rows, columns) = (3089, 11)\n",
      "2014/HOSArchive_Revised_Flatfiles_20141218/hvbp_efficiency_10_28_2014.csv :  (rows, columns) = (3089, 11)\n",
      "hvb_df.shape: (101299, 17)\n"
     ]
    }
   ],
   "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', 'Provider Number', ' ZIP Code', 'Hospital_Name', \n",
    "             'ZIP_Code', 'County_Name', 'Provider_Number',\n",
    "             ]\n",
    "    \n",
    "    \n",
    "    cols2 = ['Facility ID', \"Start Date\", \"End Date\",\n",
    "             'Facility Name', 'Facility ID', 'ZIP Code', 'Facility Name',\n",
    "             'ZIP Code', 'County Name', 'Facility ID',\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 = ['MSPB-1 Achievement Threshold', 'MSPB-1 Baseline Rate', 'MSPB-1 Benchmark', 'Fiscal Year',\n",
    "            'Facility Name',\n",
    "            ]\n",
    "    \n",
    "    for col in cols:\n",
    "        if col not in list(df):\n",
    "            df[col] = float('NaN')\n",
    "    \n",
    "    l = list(df)\n",
    "    l = list(set([x for x in l if l.count(x) > 1]))\n",
    "    if len(l) > 0:\n",
    "        print('duplicates:', l)\n",
    "        sys.exit()\n",
    "        \n",
    "    return df\n",
    "\n",
    "\n",
    "def process2(df, lists, yr, mo):\n",
    "    df = rename_and_fill(df)\n",
    "    df = curate(df)\n",
    "    lists.append(list(df))\n",
    "    df['file_month'] = [mo]* df.shape[0]\n",
    "    df['file_year'] = [yr]* df.shape[0]\n",
    "    df = df.reindex(sorted(df.columns), axis=1)\n",
    "    return df, lists\n",
    "\n",
    "\n",
    "###############################################################################\n",
    "################################  FILES  ######################################\n",
    "###############################################################################\n",
    "\n",
    "\n",
    "df_list = []\n",
    "lists = []\n",
    "\n",
    "yrs = ['2023', '2023',\n",
    "       '2022', '2022', '2022', \n",
    "       '2021','2021','2021', '2021', '2021',\n",
    "       '2020', '2020', '2020', '2020', \n",
    "       '2019', '2019', '2019', '2019', \n",
    "       '2018', '2018', '2018', '2018',\n",
    "       '2017', '2017', '2017',\n",
    "       '2016', '2016', '2016', '2016',\n",
    "       '2015', '2015', '2015', '2015', '2015', '2015',\n",
    "       '2014',\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',\n",
    "       ]\n",
    "\n",
    "\n",
    "subdirs = ['2023/hospitals_01_2023/hvbp_efficiency_and_cost_reduction.csv', \n",
    "           '2023/hospitals_04_2023/hvbp_efficiency_and_cost_reduction.csv',\n",
    "           \n",
    "           '2022/hospitals_01_2022/hvbp_efficiency_and_cost_reduction.csv', \n",
    "           '2022/hospitals_04_2022/hvbp_efficiency_and_cost_reduction.csv',\n",
    "           '2022/hospitals_07_2022/hvbp_efficiency_and_cost_reduction.csv',\n",
    "           \n",
    "           '2021/hospitals_01_2021/hvbp_efficiency_and_cost_reduction.csv',\n",
    "           '2021/hospitals_03_2021/hvbp_efficiency_and_cost_reduction.csv',\n",
    "           '2021/hospitals_04_2021/hvbp_efficiency_and_cost_reduction.csv',\n",
    "           '2021/hospitals_07_2021/hvbp_efficiency_and_cost_reduction.csv',\n",
    "           '2021/hospitals_10_2021/hvbp_efficiency_and_cost_reduction.csv',\n",
    "           \n",
    "           '2020/hospitals_archive_10_2020/hvbp_efficiency_12_09_2019.csv',\n",
    "           '2020/hospitals_archive_07_2020/hvbp_efficiency.csv',\n",
    "           '2020/HOSArchive_Revised_Flatfiles_20200422/hvbp_efficiency_12_09_2019.csv',\n",
    "           '2020/HOSArchive_Revised_Flatfiles_20200129/hvbp_efficiency_12_09_2019.csv',\n",
    "           \n",
    "           '2019/HOSArchive_Revised_Flatfiles_20191030/hvbp_efficiency_11_09_2018.csv',\n",
    "           '2019/HOSArchive_Revised_Flatfiles_20190702/hvbp_efficiency_11_09_2018.csv',\n",
    "           '2019/HOSArchive_Revised_FlatFiles_20190424/hvbp_efficiency_11_09_2018.csv',\n",
    "           '2019/HOSArchive_Revised_Flatfiles_20190321/hvbp_efficiency_11_09_2018.csv',\n",
    "           \n",
    "           '2018/HOSArchive_Revised_FlatFiles_20181031/hvbp_efficiency_11_07_2017.csv',\n",
    "           '2018/HOSArchive_Revised_FlatFiles_20180725/hvbp_efficiency_11_07_2017.csv', \n",
    "           '2018/HOSArchive_Revised_FlatFiles_20180523/hvbp_efficiency_11_07_2017.csv', \n",
    "           '2018/HOSArchive_Revised_FlatFiles_20180126/hvbp_efficiency_11_07_2017.csv',\n",
    "           \n",
    "           '2017/HOSArchive_Revised_FlatFiles_20171024/hvbp_efficiency_11_10_2016.csv',\n",
    "           '2017/HOSArchive_Revised_FlatFiles_20170726/hvbp_efficiency_11_10_2016.csv',\n",
    "           '2017/HOSArchive_Revised_Flatfiles_20170428/hvbp_efficiency_11_10_2016.csv',\n",
    "           \n",
    "           '2016/HOSArchive_Revised_Flatfiles_20161219/hvbp_efficiency_11_10_2016.csv',\n",
    "           '2016/Hospital_Revised_FlatFiles_20161110/hvbp_efficiency_08_26_2016.csv',\n",
    "           '2016/HOSArchive_Revised_FlatFiles_20160810/hvbp_efficiency_06_08_2016.csv',\n",
    "           '2016/HOSArchive_Revised_FlatFiles_20160504/hvbp_efficiency_02_18_2016.csv',\n",
    "           \n",
    "           '2015/HOSArchive_Revised_FlatFiles_20151210/hvbp_Efficiency_10_28_2015.csv',\n",
    "           '2015/HOSArchive_Revised_FlatFiles_20151008/hvbp_Efficiency_08_06_2015.csv',\n",
    "           '2015/HOSArchive_Revised_FlatFiles_20150716/hvbp_Efficiency_05_20_2015.csv',\n",
    "           '2015/HOSArchive_Revised_Flatfiles_20150506/hvbp_Efficiency_02_18_2015.csv',\n",
    "           '2015/HOSArchive_Revised_Flatfiles_20150416/hvbp_Efficiency_02_18_2015.csv',\n",
    "           '2015/HOSArchive_Revised_Flatfiles_20150122/hvbp_efficiency_10_28_2014.csv',\n",
    "           \n",
    "           '2014/HOSArchive_Revised_Flatfiles_20141218/hvbp_efficiency_10_28_2014.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 = 'HVBP/CombinedFiles_HVBP'\n",
    "hvb_df = pd.concat(df_list)\n",
    "print('hvb_df.shape:', hvb_df.shape)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "ab3567bb",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "HAI df: (101299, 17)\n",
      "(101299, 11)\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>Fiscal Year</th>\n",
       "      <th>MSPB-1 Achievement Points</th>\n",
       "      <th>MSPB-1 Achievement Threshold</th>\n",
       "      <th>MSPB-1 Baseline Rate</th>\n",
       "      <th>MSPB-1 Benchmark</th>\n",
       "      <th>MSPB-1 Improvement Points</th>\n",
       "      <th>MSPB-1 Measure Score</th>\n",
       "      <th>MSPB-1 Performance Rate</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>2023</td>\n",
       "      <td>0 out of 10</td>\n",
       "      <td>0.98791</td>\n",
       "      <td>1.020251</td>\n",
       "      <td>0.844806</td>\n",
       "      <td>0 out of 9</td>\n",
       "      <td>0 out of 10</td>\n",
       "      <td>1.016841</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>010005</td>\n",
       "      <td>01</td>\n",
       "      <td>2023</td>\n",
       "      <td>2023</td>\n",
       "      <td>2 out of 10</td>\n",
       "      <td>0.98791</td>\n",
       "      <td>1.011867</td>\n",
       "      <td>0.844806</td>\n",
       "      <td>3 out of 9</td>\n",
       "      <td>3 out of 10</td>\n",
       "      <td>0.95781</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>010006</td>\n",
       "      <td>01</td>\n",
       "      <td>2023</td>\n",
       "      <td>2023</td>\n",
       "      <td>0 out of 10</td>\n",
       "      <td>0.98791</td>\n",
       "      <td>1.016924</td>\n",
       "      <td>0.844806</td>\n",
       "      <td>1 out of 9</td>\n",
       "      <td>1 out of 10</td>\n",
       "      <td>0.994638</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>010007</td>\n",
       "      <td>01</td>\n",
       "      <td>2023</td>\n",
       "      <td>2023</td>\n",
       "      <td>0 out of 10</td>\n",
       "      <td>0.98791</td>\n",
       "      <td>1.110418</td>\n",
       "      <td>0.844806</td>\n",
       "      <td>3 out of 9</td>\n",
       "      <td>3 out of 10</td>\n",
       "      <td>1.016687</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>010011</td>\n",
       "      <td>01</td>\n",
       "      <td>2023</td>\n",
       "      <td>2023</td>\n",
       "      <td>0 out of 10</td>\n",
       "      <td>0.98791</td>\n",
       "      <td>1.029416</td>\n",
       "      <td>0.844806</td>\n",
       "      <td>1 out of 9</td>\n",
       "      <td>1 out of 10</td>\n",
       "      <td>0.993673</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Facility ID file_month file_year Fiscal Year MSPB-1 Achievement Points  \\\n",
       "0      010001         01      2023        2023               0 out of 10   \n",
       "1      010005         01      2023        2023               2 out of 10   \n",
       "2      010006         01      2023        2023               0 out of 10   \n",
       "3      010007         01      2023        2023               0 out of 10   \n",
       "4      010011         01      2023        2023               0 out of 10   \n",
       "\n",
       "   MSPB-1 Achievement Threshold MSPB-1 Baseline Rate  MSPB-1 Benchmark  \\\n",
       "0                       0.98791             1.020251          0.844806   \n",
       "1                       0.98791             1.011867          0.844806   \n",
       "2                       0.98791             1.016924          0.844806   \n",
       "3                       0.98791             1.110418          0.844806   \n",
       "4                       0.98791             1.029416          0.844806   \n",
       "\n",
       "  MSPB-1 Improvement Points MSPB-1 Measure Score MSPB-1 Performance Rate  \n",
       "0                0 out of 9          0 out of 10                1.016841  \n",
       "1                3 out of 9          3 out of 10                 0.95781  \n",
       "2                1 out of 9          1 out of 10                0.994638  \n",
       "3                3 out of 9          3 out of 10                1.016687  \n",
       "4                1 out of 9          1 out of 10                0.993673  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hvb_df = pd.read_pickle(main_dir + 'HVBP/CombinedFiles_HVBP/' + 'hvbp_efficiency.pkl')\n",
    "print('HAI df:', hvb_df.shape)\n",
    "\n",
    "hvb_df = hvb_df.filter(items=['Facility ID', 'file_month', 'file_year',\n",
    "                              'Fiscal Year', 'MSPB-1 Achievement Points',\n",
    "                              'MSPB-1 Achievement Threshold',\n",
    "                              'MSPB-1 Baseline Rate',\n",
    "                              'MSPB-1 Benchmark', \n",
    "                              'MSPB-1 Improvement Points',\n",
    "                              'MSPB-1 Measure Score',\n",
    "                              'MSPB-1 Performance Rate',\n",
    "                             ], axis=1)\n",
    "\n",
    "hvb_df['Fiscal Year'] = hvb_df['Fiscal Year'].astype(str)\n",
    "\n",
    "ls = []\n",
    "for i in hvb_df['Fiscal Year']:\n",
    "    try:\n",
    "        ls.append(i[:-2])\n",
    "    except:\n",
    "        ls.append(np.nan)\n",
    "        \n",
    "hvb_df['Fiscal Year'] = ls\n",
    "\n",
    "print(hvb_df.shape)\n",
    "hvb_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "f20ded31",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(101299, 11)\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>HVBP Efficiency: Fiscal Year</th>\n",
       "      <th>HVBP Efficiency: MSPB-1 Achievement Points</th>\n",
       "      <th>HVBP Efficiency: MSPB-1 Achievement Threshold</th>\n",
       "      <th>HVBP Efficiency: MSPB-1 Baseline Rate</th>\n",
       "      <th>HVBP Efficiency: MSPB-1 Benchmark</th>\n",
       "      <th>HVBP Efficiency: MSPB-1 Improvement Points</th>\n",
       "      <th>HVBP Efficiency: MSPB-1 Measure Score</th>\n",
       "      <th>HVBP Efficiency: MSPB-1 Performance Rate</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>2023</td>\n",
       "      <td>0 out of 10</td>\n",
       "      <td>0.98791</td>\n",
       "      <td>1.020251</td>\n",
       "      <td>0.844806</td>\n",
       "      <td>0 out of 9</td>\n",
       "      <td>0 out of 10</td>\n",
       "      <td>1.016841</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>010005</td>\n",
       "      <td>01</td>\n",
       "      <td>2023</td>\n",
       "      <td>2023</td>\n",
       "      <td>2 out of 10</td>\n",
       "      <td>0.98791</td>\n",
       "      <td>1.011867</td>\n",
       "      <td>0.844806</td>\n",
       "      <td>3 out of 9</td>\n",
       "      <td>3 out of 10</td>\n",
       "      <td>0.95781</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>010006</td>\n",
       "      <td>01</td>\n",
       "      <td>2023</td>\n",
       "      <td>2023</td>\n",
       "      <td>0 out of 10</td>\n",
       "      <td>0.98791</td>\n",
       "      <td>1.016924</td>\n",
       "      <td>0.844806</td>\n",
       "      <td>1 out of 9</td>\n",
       "      <td>1 out of 10</td>\n",
       "      <td>0.994638</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>010007</td>\n",
       "      <td>01</td>\n",
       "      <td>2023</td>\n",
       "      <td>2023</td>\n",
       "      <td>0 out of 10</td>\n",
       "      <td>0.98791</td>\n",
       "      <td>1.110418</td>\n",
       "      <td>0.844806</td>\n",
       "      <td>3 out of 9</td>\n",
       "      <td>3 out of 10</td>\n",
       "      <td>1.016687</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>010011</td>\n",
       "      <td>01</td>\n",
       "      <td>2023</td>\n",
       "      <td>2023</td>\n",
       "      <td>0 out of 10</td>\n",
       "      <td>0.98791</td>\n",
       "      <td>1.029416</td>\n",
       "      <td>0.844806</td>\n",
       "      <td>1 out of 9</td>\n",
       "      <td>1 out of 10</td>\n",
       "      <td>0.993673</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Facility ID file_month file_year HVBP Efficiency: Fiscal Year  \\\n",
       "0      010001         01      2023                         2023   \n",
       "1      010005         01      2023                         2023   \n",
       "2      010006         01      2023                         2023   \n",
       "3      010007         01      2023                         2023   \n",
       "4      010011         01      2023                         2023   \n",
       "\n",
       "  HVBP Efficiency: MSPB-1 Achievement Points  \\\n",
       "0                                0 out of 10   \n",
       "1                                2 out of 10   \n",
       "2                                0 out of 10   \n",
       "3                                0 out of 10   \n",
       "4                                0 out of 10   \n",
       "\n",
       "   HVBP Efficiency: MSPB-1 Achievement Threshold  \\\n",
       "0                                        0.98791   \n",
       "1                                        0.98791   \n",
       "2                                        0.98791   \n",
       "3                                        0.98791   \n",
       "4                                        0.98791   \n",
       "\n",
       "  HVBP Efficiency: MSPB-1 Baseline Rate  HVBP Efficiency: MSPB-1 Benchmark  \\\n",
       "0                              1.020251                           0.844806   \n",
       "1                              1.011867                           0.844806   \n",
       "2                              1.016924                           0.844806   \n",
       "3                              1.110418                           0.844806   \n",
       "4                              1.029416                           0.844806   \n",
       "\n",
       "  HVBP Efficiency: MSPB-1 Improvement Points  \\\n",
       "0                                 0 out of 9   \n",
       "1                                 3 out of 9   \n",
       "2                                 1 out of 9   \n",
       "3                                 3 out of 9   \n",
       "4                                 1 out of 9   \n",
       "\n",
       "  HVBP Efficiency: MSPB-1 Measure Score  \\\n",
       "0                           0 out of 10   \n",
       "1                           3 out of 10   \n",
       "2                           1 out of 10   \n",
       "3                           3 out of 10   \n",
       "4                           1 out of 10   \n",
       "\n",
       "  HVBP Efficiency: MSPB-1 Performance Rate  \n",
       "0                                 1.016841  \n",
       "1                                  0.95781  \n",
       "2                                 0.994638  \n",
       "3                                 1.016687  \n",
       "4                                 0.993673  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "for i, n in enumerate(list(hvb_df)):\n",
    "    if n in ['Facility ID', 'file_month', 'file_year']:\n",
    "        continue\n",
    "            \n",
    "    hvb_df.rename(columns={n: 'HVBP Efficiency: ' + n}, inplace=True)\n",
    "    \n",
    "print(hvb_df.shape)\n",
    "hvb_df.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "f66037b2",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['0 out of 10', '1 out of 10', '10 out of 10', '2 out of 10', '3 out of 10', '4 out of 10', '5 out of 10', '6 out of 10', '7 out of 10', '8 out of 10', '9 out of 10', 'Not Available']\n",
      "[ 0.  2.  1.  8.  3.  4.  7.  9. 10.  5.  6. nan]\n"
     ]
    }
   ],
   "source": [
    "print(sorted(hvb_df['HVBP Efficiency: MSPB-1 Achievement Points'].unique()))\n",
    "hvb_df['HVBP Efficiency: MSPB-1 Achievement Points'] = hvb_df['HVBP Efficiency: MSPB-1 Achievement Points'].replace([\n",
    "                                                    '0 out of 10', '1 out of 10', '2 out of 10', '3 out of 10',\n",
    "                                                    '4 out of 10', '5 out of 10', '6 out of 10', '7 out of 10', \n",
    "                                                    '8 out of 10', '9 out of 10', '10 out of 10'],\n",
    "                                                  [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10])\n",
    "hvb_df['HVBP Efficiency: MSPB-1 Achievement Points'] = pd.to_numeric(hvb_df['HVBP Efficiency: MSPB-1 Achievement Points'], errors='coerce')\n",
    "print(hvb_df['HVBP Efficiency: MSPB-1 Achievement Points'].unique())\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "cb8846e7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['0 out of 9', '1 out of 9', '2 out of 9', '3 out of 9', '4 out of 9', '5 out of 9', '6 out of 9', '7 out of 9', '8 out of 9', '9 out of 9', 'Not Available']\n",
      "[0.0, 1.0, 2.0, 3.0, 4.0, 5.0, 6.0, 7.0, 8.0, 9.0, 10.0, nan]\n"
     ]
    }
   ],
   "source": [
    "print(sorted(hvb_df['HVBP Efficiency: MSPB-1 Improvement Points'].unique()))\n",
    "hvb_df['HVBP Efficiency: MSPB-1 Improvement Points'] = hvb_df['HVBP Efficiency: MSPB-1 Achievement Points'].replace([\n",
    "                                                    '0 out of 9', '1 out of 9', '2 out of 9', '3 out of 9',\n",
    "                                                    '4 out of 9', '5 out of 9', '6 out of 9', '7 out of 9', \n",
    "                                                    '8 out of 9', '9 out of 9', ],\n",
    "                                                  [0, 1, 2, 3, 4, 5, 6, 7, 8, 9])\n",
    "hvb_df['HVBP Efficiency: MSPB-1 Improvement Points'] = pd.to_numeric(hvb_df['HVBP Efficiency: MSPB-1 Improvement Points'], errors='coerce')\n",
    "print(sorted(hvb_df['HVBP Efficiency: MSPB-1 Improvement Points'].unique()))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "ed19d4e4",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['0 out of 10', '1 out of 10', '10 out of 10', '2 out of 10', '3 out of 10', '4 out of 10', '5 out of 10', '6 out of 10', '7 out of 10', '8 out of 10', '9 out of 10', 'Not Available']\n",
      "[0.0, 1.0, 2.0, 3.0, 4.0, 5.0, 6.0, 7.0, 8.0, 9.0, 10.0, nan]\n"
     ]
    }
   ],
   "source": [
    "print(sorted(hvb_df['HVBP Efficiency: MSPB-1 Measure Score'].unique()))\n",
    "hvb_df['HVBP Efficiency: MSPB-1 Measure Score'] = hvb_df['HVBP Efficiency: MSPB-1 Measure Score'].replace([\n",
    "                                                    '0 out of 10', '1 out of 10', '2 out of 10', '3 out of 10',\n",
    "                                                    '4 out of 10', '5 out of 10', '6 out of 10', '7 out of 10', \n",
    "                                                    '8 out of 10', '9 out of 10', '10 out of 10'],\n",
    "                                                  [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10])\n",
    "hvb_df['HVBP Efficiency: MSPB-1 Measure Score'] = pd.to_numeric(hvb_df['HVBP Efficiency: MSPB-1 Measure Score'], errors='coerce')\n",
    "print(sorted(hvb_df['HVBP Efficiency: MSPB-1 Measure Score'].unique()))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "f956b25a",
   "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>HVBP Efficiency: Fiscal Year</th>\n",
       "      <th>HVBP Efficiency: MSPB-1 Achievement Points</th>\n",
       "      <th>HVBP Efficiency: MSPB-1 Achievement Threshold</th>\n",
       "      <th>HVBP Efficiency: MSPB-1 Baseline Rate</th>\n",
       "      <th>HVBP Efficiency: MSPB-1 Benchmark</th>\n",
       "      <th>HVBP Efficiency: MSPB-1 Improvement Points</th>\n",
       "      <th>HVBP Efficiency: MSPB-1 Measure Score</th>\n",
       "      <th>HVBP Efficiency: MSPB-1 Performance Rate</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>2023</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.98791</td>\n",
       "      <td>1.020251</td>\n",
       "      <td>0.844806</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.016841</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>010005</td>\n",
       "      <td>01</td>\n",
       "      <td>2023</td>\n",
       "      <td>2023</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.98791</td>\n",
       "      <td>1.011867</td>\n",
       "      <td>0.844806</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.95781</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>010006</td>\n",
       "      <td>01</td>\n",
       "      <td>2023</td>\n",
       "      <td>2023</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.98791</td>\n",
       "      <td>1.016924</td>\n",
       "      <td>0.844806</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.994638</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>010007</td>\n",
       "      <td>01</td>\n",
       "      <td>2023</td>\n",
       "      <td>2023</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.98791</td>\n",
       "      <td>1.110418</td>\n",
       "      <td>0.844806</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.016687</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>010011</td>\n",
       "      <td>01</td>\n",
       "      <td>2023</td>\n",
       "      <td>2023</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.98791</td>\n",
       "      <td>1.029416</td>\n",
       "      <td>0.844806</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.993673</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Facility ID file_month file_year HVBP Efficiency: Fiscal Year  \\\n",
       "0      010001         01      2023                         2023   \n",
       "1      010005         01      2023                         2023   \n",
       "2      010006         01      2023                         2023   \n",
       "3      010007         01      2023                         2023   \n",
       "4      010011         01      2023                         2023   \n",
       "\n",
       "   HVBP Efficiency: MSPB-1 Achievement Points  \\\n",
       "0                                         0.0   \n",
       "1                                         2.0   \n",
       "2                                         0.0   \n",
       "3                                         0.0   \n",
       "4                                         0.0   \n",
       "\n",
       "   HVBP Efficiency: MSPB-1 Achievement Threshold  \\\n",
       "0                                        0.98791   \n",
       "1                                        0.98791   \n",
       "2                                        0.98791   \n",
       "3                                        0.98791   \n",
       "4                                        0.98791   \n",
       "\n",
       "  HVBP Efficiency: MSPB-1 Baseline Rate  HVBP Efficiency: MSPB-1 Benchmark  \\\n",
       "0                              1.020251                           0.844806   \n",
       "1                              1.011867                           0.844806   \n",
       "2                              1.016924                           0.844806   \n",
       "3                              1.110418                           0.844806   \n",
       "4                              1.029416                           0.844806   \n",
       "\n",
       "   HVBP Efficiency: MSPB-1 Improvement Points  \\\n",
       "0                                         0.0   \n",
       "1                                         2.0   \n",
       "2                                         0.0   \n",
       "3                                         0.0   \n",
       "4                                         0.0   \n",
       "\n",
       "   HVBP Efficiency: MSPB-1 Measure Score  \\\n",
       "0                                    0.0   \n",
       "1                                    3.0   \n",
       "2                                    1.0   \n",
       "3                                    3.0   \n",
       "4                                    1.0   \n",
       "\n",
       "  HVBP Efficiency: MSPB-1 Performance Rate  \n",
       "0                                 1.016841  \n",
       "1                                  0.95781  \n",
       "2                                 0.994638  \n",
       "3                                 1.016687  \n",
       "4                                 0.993673  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hvb_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "a6079ecb",
   "metadata": {},
   "outputs": [],
   "source": [
    "hvb_df.to_pickle('dataframes/hvbp_efficiency_df.pkl.gz', protocol=5, compression='gzip')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "49ca1c19",
   "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
}