EDA.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "from datetime import datetime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "encounters = pd.read_csv(\"data/encounters.csv\")\n",
    "organizations = pd.read_csv(\"data/organizations.csv\")\n",
    "patients = pd.read_csv(\"data/patients.csv\")\n",
    "payers = pd.read_csv(\"data/payers.csv\")\n",
    "procedures = pd.read_csv(\"data/procedures.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "encounters['START'] = pd.to_datetime(encounters['START'])\n",
    "encounters['STOP'] = pd.to_datetime(encounters['STOP'])\n",
    "encounters['START_MONTH'] = encounters['START'].dt.strftime('%Y-%m')\n",
    "encounters['STAY_DURATION'] = (encounters['STOP'] - encounters['START']) / pd.Timedelta(hours=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "datetime.date(2022, 2, 5)"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "current_date = encounters['START'].max().date()\n",
    "current_date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "age_groups = {\n",
    "    '0 – 14 years': [0, 14],\n",
    "    '15 – 24 years': [15, 24],\n",
    "    '25 – 54 years': [25, 54],\n",
    "    '55 – 64 years': [55, 64],\n",
    "    '65 years and over': [65, 999]\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Id</th>\n",
       "      <th>START</th>\n",
       "      <th>START_MONTH</th>\n",
       "      <th>PATIENT</th>\n",
       "      <th>ENCOUNTERCLASS</th>\n",
       "      <th>BIRTHDATE</th>\n",
       "      <th>GENDER</th>\n",
       "      <th>AGE</th>\n",
       "      <th>AGE_GROUP</th>\n",
       "      <th>IS_ADMISSION</th>\n",
       "      <th>ADMISSION_ORDER</th>\n",
       "      <th>IS_READMISSION</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>32c84703-2481-49cd-d571-3899d5820253</td>\n",
       "      <td>2011-01-02 09:26:36+00:00</td>\n",
       "      <td>2011-01</td>\n",
       "      <td>3de74169-7f67-9304-91d4-757e0f3a14d2</td>\n",
       "      <td>ambulatory</td>\n",
       "      <td>1928-08-25</td>\n",
       "      <td>M</td>\n",
       "      <td>93</td>\n",
       "      <td>65 years and over</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>c98059da-320a-c0a6-fced-c8815f3e3f39</td>\n",
       "      <td>2011-01-03 05:44:39+00:00</td>\n",
       "      <td>2011-01</td>\n",
       "      <td>d9ec2e44-32e9-9148-179a-1653348cc4e2</td>\n",
       "      <td>outpatient</td>\n",
       "      <td>1964-01-05</td>\n",
       "      <td>F</td>\n",
       "      <td>58</td>\n",
       "      <td>55 – 64 years</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4ad28a3a-2479-782b-f29c-d5b3f41a001e</td>\n",
       "      <td>2011-01-03 14:32:11+00:00</td>\n",
       "      <td>2011-01</td>\n",
       "      <td>73babadf-5b2b-fee7-189e-6f41ff213e01</td>\n",
       "      <td>outpatient</td>\n",
       "      <td>1924-06-30</td>\n",
       "      <td>F</td>\n",
       "      <td>97</td>\n",
       "      <td>65 years and over</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>c3f4da61-e4b4-21d5-587a-fbc89943bc19</td>\n",
       "      <td>2011-01-03 16:24:45+00:00</td>\n",
       "      <td>2011-01</td>\n",
       "      <td>3b46a0b7-0f34-9b9a-c319-ace4a1f58c0b</td>\n",
       "      <td>wellness</td>\n",
       "      <td>1923-05-21</td>\n",
       "      <td>M</td>\n",
       "      <td>98</td>\n",
       "      <td>65 years and over</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>a9183b4f-2572-72ea-54c2-b3cd038b4be7</td>\n",
       "      <td>2011-01-03 17:36:53+00:00</td>\n",
       "      <td>2011-01</td>\n",
       "      <td>fa006887-d93c-d302-8b89-f3c25f88c0e1</td>\n",
       "      <td>ambulatory</td>\n",
       "      <td>1952-11-02</td>\n",
       "      <td>M</td>\n",
       "      <td>69</td>\n",
       "      <td>65 years and over</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27886</th>\n",
       "      <td>917bb534-2ed3-e1f5-ac27-74f314d19c63</td>\n",
       "      <td>2022-01-29 11:42:06+00:00</td>\n",
       "      <td>2022-01</td>\n",
       "      <td>0c122290-8c15-77ac-b418-2fea4e8a8e6a</td>\n",
       "      <td>outpatient</td>\n",
       "      <td>1974-04-27</td>\n",
       "      <td>M</td>\n",
       "      <td>47</td>\n",
       "      <td>25 – 54 years</td>\n",
       "      <td>0</td>\n",
       "      <td>27</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27887</th>\n",
       "      <td>07710480-9d6b-9c9b-87c3-c1d54df4069d</td>\n",
       "      <td>2022-01-29 20:12:53+00:00</td>\n",
       "      <td>2022-01</td>\n",
       "      <td>20a4bc24-6b69-2f5c-dc74-1df390cae25b</td>\n",
       "      <td>urgentcare</td>\n",
       "      <td>1933-12-16</td>\n",
       "      <td>M</td>\n",
       "      <td>88</td>\n",
       "      <td>65 years and over</td>\n",
       "      <td>0</td>\n",
       "      <td>14</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27888</th>\n",
       "      <td>01b57f06-cebe-a3e4-4423-a796ffb0c35d</td>\n",
       "      <td>2022-01-29 20:35:37+00:00</td>\n",
       "      <td>2022-01</td>\n",
       "      <td>ff1b3c26-53a6-4590-ce79-a3f7269274ea</td>\n",
       "      <td>ambulatory</td>\n",
       "      <td>1990-09-22</td>\n",
       "      <td>F</td>\n",
       "      <td>31</td>\n",
       "      <td>25 – 54 years</td>\n",
       "      <td>0</td>\n",
       "      <td>71</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27889</th>\n",
       "      <td>7d435668-0813-eb3b-0f26-cb741fb39561</td>\n",
       "      <td>2022-01-29 20:35:37+00:00</td>\n",
       "      <td>2022-01</td>\n",
       "      <td>ff1b3c26-53a6-4590-ce79-a3f7269274ea</td>\n",
       "      <td>wellness</td>\n",
       "      <td>1990-09-22</td>\n",
       "      <td>F</td>\n",
       "      <td>31</td>\n",
       "      <td>25 – 54 years</td>\n",
       "      <td>0</td>\n",
       "      <td>71</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27890</th>\n",
       "      <td>64dfd3ce-7123-fa23-ec24-f74b492553e2</td>\n",
       "      <td>2022-02-05 20:27:36+00:00</td>\n",
       "      <td>2022-02</td>\n",
       "      <td>e2d8e1ed-6f63-54e2-d14e-8346799e92ef</td>\n",
       "      <td>wellness</td>\n",
       "      <td>1939-09-23</td>\n",
       "      <td>M</td>\n",
       "      <td>82</td>\n",
       "      <td>65 years and over</td>\n",
       "      <td>0</td>\n",
       "      <td>92</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>27891 rows × 12 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                         Id                     START  \\\n",
       "0      32c84703-2481-49cd-d571-3899d5820253 2011-01-02 09:26:36+00:00   \n",
       "1      c98059da-320a-c0a6-fced-c8815f3e3f39 2011-01-03 05:44:39+00:00   \n",
       "2      4ad28a3a-2479-782b-f29c-d5b3f41a001e 2011-01-03 14:32:11+00:00   \n",
       "3      c3f4da61-e4b4-21d5-587a-fbc89943bc19 2011-01-03 16:24:45+00:00   \n",
       "4      a9183b4f-2572-72ea-54c2-b3cd038b4be7 2011-01-03 17:36:53+00:00   \n",
       "...                                     ...                       ...   \n",
       "27886  917bb534-2ed3-e1f5-ac27-74f314d19c63 2022-01-29 11:42:06+00:00   \n",
       "27887  07710480-9d6b-9c9b-87c3-c1d54df4069d 2022-01-29 20:12:53+00:00   \n",
       "27888  01b57f06-cebe-a3e4-4423-a796ffb0c35d 2022-01-29 20:35:37+00:00   \n",
       "27889  7d435668-0813-eb3b-0f26-cb741fb39561 2022-01-29 20:35:37+00:00   \n",
       "27890  64dfd3ce-7123-fa23-ec24-f74b492553e2 2022-02-05 20:27:36+00:00   \n",
       "\n",
       "      START_MONTH                               PATIENT ENCOUNTERCLASS  \\\n",
       "0         2011-01  3de74169-7f67-9304-91d4-757e0f3a14d2     ambulatory   \n",
       "1         2011-01  d9ec2e44-32e9-9148-179a-1653348cc4e2     outpatient   \n",
       "2         2011-01  73babadf-5b2b-fee7-189e-6f41ff213e01     outpatient   \n",
       "3         2011-01  3b46a0b7-0f34-9b9a-c319-ace4a1f58c0b       wellness   \n",
       "4         2011-01  fa006887-d93c-d302-8b89-f3c25f88c0e1     ambulatory   \n",
       "...           ...                                   ...            ...   \n",
       "27886     2022-01  0c122290-8c15-77ac-b418-2fea4e8a8e6a     outpatient   \n",
       "27887     2022-01  20a4bc24-6b69-2f5c-dc74-1df390cae25b     urgentcare   \n",
       "27888     2022-01  ff1b3c26-53a6-4590-ce79-a3f7269274ea     ambulatory   \n",
       "27889     2022-01  ff1b3c26-53a6-4590-ce79-a3f7269274ea       wellness   \n",
       "27890     2022-02  e2d8e1ed-6f63-54e2-d14e-8346799e92ef       wellness   \n",
       "\n",
       "        BIRTHDATE GENDER  AGE          AGE_GROUP  IS_ADMISSION  \\\n",
       "0      1928-08-25      M   93  65 years and over             0   \n",
       "1      1964-01-05      F   58      55 – 64 years             0   \n",
       "2      1924-06-30      F   97  65 years and over             0   \n",
       "3      1923-05-21      M   98  65 years and over             0   \n",
       "4      1952-11-02      M   69  65 years and over             0   \n",
       "...           ...    ...  ...                ...           ...   \n",
       "27886  1974-04-27      M   47      25 – 54 years             0   \n",
       "27887  1933-12-16      M   88  65 years and over             0   \n",
       "27888  1990-09-22      F   31      25 – 54 years             0   \n",
       "27889  1990-09-22      F   31      25 – 54 years             0   \n",
       "27890  1939-09-23      M   82  65 years and over             0   \n",
       "\n",
       "       ADMISSION_ORDER  IS_READMISSION  \n",
       "0                    1               0  \n",
       "1                    1               0  \n",
       "2                    1               0  \n",
       "3                    1               0  \n",
       "4                    1               0  \n",
       "...                ...             ...  \n",
       "27886               27               0  \n",
       "27887               14               0  \n",
       "27888               71               0  \n",
       "27889               71               0  \n",
       "27890               92               0  \n",
       "\n",
       "[27891 rows x 12 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_admissions = pd.merge(\n",
    "    encounters[['Id', 'START', 'START_MONTH', 'PATIENT', 'ENCOUNTERCLASS']],\n",
    "    patients[['Id', 'BIRTHDATE', 'GENDER']].rename(columns={'Id': 'PATIENT'}),\n",
    "    on='PATIENT',\n",
    "    how='left'\n",
    ")\n",
    "\n",
    "df_admissions['AGE'] = (pd.to_datetime(current_date) - pd.to_datetime(df_admissions['BIRTHDATE'])).apply(lambda x: np.floor(x.days/365.25)).astype(int)\n",
    "df_admissions['AGE_GROUP'] = None\n",
    "for age_group, age_range in age_groups.items():\n",
    "    df_admissions['AGE_GROUP'] = np.where(\n",
    "        (df_admissions['AGE'] >= age_range[0]) & (df_admissions['AGE'] <= age_range[1]),\n",
    "        age_group,\n",
    "        df_admissions['AGE_GROUP']\n",
    "    )\n",
    "\n",
    "df_admissions['IS_ADMISSION'] = np.where(df_admissions['ENCOUNTERCLASS'] == 'inpatient', 1, 0)\n",
    "df_admissions['ADMISSION_ORDER'] = df_admissions.groupby(['PATIENT', 'IS_ADMISSION'])['START'].rank().astype(int)\n",
    "df_admissions['IS_READMISSION'] = np.where((df_admissions['IS_ADMISSION'] == 1) & (df_admissions['ADMISSION_ORDER'] > 1), 1, 0)\n",
    "\n",
    "df_admissions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>PATIENTS</th>\n",
       "      <th>ADMISSIONS</th>\n",
       "      <th>READMISSIONS</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>START_MONTH</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2011-01</th>\n",
       "      <td>68</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011-02</th>\n",
       "      <td>67</td>\n",
       "      <td>8</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011-03</th>\n",
       "      <td>85</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011-04</th>\n",
       "      <td>72</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011-05</th>\n",
       "      <td>78</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-10</th>\n",
       "      <td>119</td>\n",
       "      <td>7</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-11</th>\n",
       "      <td>134</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-12</th>\n",
       "      <td>110</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-01</th>\n",
       "      <td>103</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-02</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>134 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             PATIENTS  ADMISSIONS  READMISSIONS\n",
       "START_MONTH                                    \n",
       "2011-01            68           7             0\n",
       "2011-02            67           8             5\n",
       "2011-03            85           6             6\n",
       "2011-04            72           5             5\n",
       "2011-05            78           7             7\n",
       "...               ...         ...           ...\n",
       "2021-10           119           7             5\n",
       "2021-11           134           4             4\n",
       "2021-12           110           2             1\n",
       "2022-01           103           4             4\n",
       "2022-02             1           0             0\n",
       "\n",
       "[134 rows x 3 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_admissions_grouped = df_admissions.groupby('START_MONTH').agg(\n",
    "    PATIENTS = ('PATIENT', 'nunique'),\n",
    "    ADMISSIONS = ('IS_ADMISSION', 'sum'),\n",
    "    READMISSIONS = ('IS_READMISSION', 'sum'), \n",
    ")\n",
    "\n",
    "df_admissions_grouped"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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></th>\n",
       "      <th>PATIENTS</th>\n",
       "      <th>ADMISSIONS</th>\n",
       "      <th>READMISSIONS</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>START_MONTH</th>\n",
       "      <th>AGE_GROUP</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">2011-01</th>\n",
       "      <th>25 – 54 years</th>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55 – 64 years</th>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65 years and over</th>\n",
       "      <td>53</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">2011-02</th>\n",
       "      <th>25 – 54 years</th>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65 years and over</th>\n",
       "      <td>60</td>\n",
       "      <td>8</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-12</th>\n",
       "      <th>65 years and over</th>\n",
       "      <td>76</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">2022-01</th>\n",
       "      <th>25 – 54 years</th>\n",
       "      <td>25</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55 – 64 years</th>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65 years and over</th>\n",
       "      <td>66</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-02</th>\n",
       "      <th>65 years and over</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>399 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                               PATIENTS  ADMISSIONS  READMISSIONS\n",
       "START_MONTH AGE_GROUP                                            \n",
       "2011-01     25 – 54 years            10           1             0\n",
       "            55 – 64 years             5           0             0\n",
       "            65 years and over        53           6             0\n",
       "2011-02     25 – 54 years             7           0             0\n",
       "            65 years and over        60           8             5\n",
       "...                                 ...         ...           ...\n",
       "2021-12     65 years and over        76           2             1\n",
       "2022-01     25 – 54 years            25           1             1\n",
       "            55 – 64 years            12           0             0\n",
       "            65 years and over        66           3             3\n",
       "2022-02     65 years and over         1           0             0\n",
       "\n",
       "[399 rows x 3 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_readmissions_grouped = df_admissions.groupby(['START_MONTH', 'AGE_GROUP']).agg(\n",
    "    PATIENTS = ('PATIENT', 'nunique'),\n",
    "    ADMISSIONS = ('IS_ADMISSION', 'sum'),\n",
    "    READMISSIONS = ('IS_READMISSION', 'sum'),\n",
    ")\n",
    "\n",
    "df_readmissions_grouped"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>Id</th>\n",
       "      <th>START</th>\n",
       "      <th>STOP</th>\n",
       "      <th>START_MONTH</th>\n",
       "      <th>PATIENT</th>\n",
       "      <th>STAY_DURATION</th>\n",
       "      <th>BIRTHDATE</th>\n",
       "      <th>GENDER</th>\n",
       "      <th>AGE</th>\n",
       "      <th>AGE_GROUP</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>32c84703-2481-49cd-d571-3899d5820253</td>\n",
       "      <td>2011-01-02 09:26:36+00:00</td>\n",
       "      <td>2011-01-02 12:58:36+00:00</td>\n",
       "      <td>2011-01</td>\n",
       "      <td>3de74169-7f67-9304-91d4-757e0f3a14d2</td>\n",
       "      <td>3.533333</td>\n",
       "      <td>1928-08-25</td>\n",
       "      <td>M</td>\n",
       "      <td>93</td>\n",
       "      <td>65 years and over</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>c98059da-320a-c0a6-fced-c8815f3e3f39</td>\n",
       "      <td>2011-01-03 05:44:39+00:00</td>\n",
       "      <td>2011-01-03 06:01:42+00:00</td>\n",
       "      <td>2011-01</td>\n",
       "      <td>d9ec2e44-32e9-9148-179a-1653348cc4e2</td>\n",
       "      <td>0.284167</td>\n",
       "      <td>1964-01-05</td>\n",
       "      <td>F</td>\n",
       "      <td>58</td>\n",
       "      <td>55 – 64 years</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4ad28a3a-2479-782b-f29c-d5b3f41a001e</td>\n",
       "      <td>2011-01-03 14:32:11+00:00</td>\n",
       "      <td>2011-01-03 14:47:11+00:00</td>\n",
       "      <td>2011-01</td>\n",
       "      <td>73babadf-5b2b-fee7-189e-6f41ff213e01</td>\n",
       "      <td>0.250000</td>\n",
       "      <td>1924-06-30</td>\n",
       "      <td>F</td>\n",
       "      <td>97</td>\n",
       "      <td>65 years and over</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>c3f4da61-e4b4-21d5-587a-fbc89943bc19</td>\n",
       "      <td>2011-01-03 16:24:45+00:00</td>\n",
       "      <td>2011-01-03 16:39:45+00:00</td>\n",
       "      <td>2011-01</td>\n",
       "      <td>3b46a0b7-0f34-9b9a-c319-ace4a1f58c0b</td>\n",
       "      <td>0.250000</td>\n",
       "      <td>1923-05-21</td>\n",
       "      <td>M</td>\n",
       "      <td>98</td>\n",
       "      <td>65 years and over</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>a9183b4f-2572-72ea-54c2-b3cd038b4be7</td>\n",
       "      <td>2011-01-03 17:36:53+00:00</td>\n",
       "      <td>2011-01-03 17:51:53+00:00</td>\n",
       "      <td>2011-01</td>\n",
       "      <td>fa006887-d93c-d302-8b89-f3c25f88c0e1</td>\n",
       "      <td>0.250000</td>\n",
       "      <td>1952-11-02</td>\n",
       "      <td>M</td>\n",
       "      <td>69</td>\n",
       "      <td>65 years and over</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27886</th>\n",
       "      <td>917bb534-2ed3-e1f5-ac27-74f314d19c63</td>\n",
       "      <td>2022-01-29 11:42:06+00:00</td>\n",
       "      <td>2022-01-29 11:57:06+00:00</td>\n",
       "      <td>2022-01</td>\n",
       "      <td>0c122290-8c15-77ac-b418-2fea4e8a8e6a</td>\n",
       "      <td>0.250000</td>\n",
       "      <td>1974-04-27</td>\n",
       "      <td>M</td>\n",
       "      <td>47</td>\n",
       "      <td>25 – 54 years</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27887</th>\n",
       "      <td>07710480-9d6b-9c9b-87c3-c1d54df4069d</td>\n",
       "      <td>2022-01-29 20:12:53+00:00</td>\n",
       "      <td>2022-01-29 20:27:53+00:00</td>\n",
       "      <td>2022-01</td>\n",
       "      <td>20a4bc24-6b69-2f5c-dc74-1df390cae25b</td>\n",
       "      <td>0.250000</td>\n",
       "      <td>1933-12-16</td>\n",
       "      <td>M</td>\n",
       "      <td>88</td>\n",
       "      <td>65 years and over</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27888</th>\n",
       "      <td>01b57f06-cebe-a3e4-4423-a796ffb0c35d</td>\n",
       "      <td>2022-01-29 20:35:37+00:00</td>\n",
       "      <td>2022-01-29 20:50:37+00:00</td>\n",
       "      <td>2022-01</td>\n",
       "      <td>ff1b3c26-53a6-4590-ce79-a3f7269274ea</td>\n",
       "      <td>0.250000</td>\n",
       "      <td>1990-09-22</td>\n",
       "      <td>F</td>\n",
       "      <td>31</td>\n",
       "      <td>25 – 54 years</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27889</th>\n",
       "      <td>7d435668-0813-eb3b-0f26-cb741fb39561</td>\n",
       "      <td>2022-01-29 20:35:37+00:00</td>\n",
       "      <td>2022-01-29 20:50:37+00:00</td>\n",
       "      <td>2022-01</td>\n",
       "      <td>ff1b3c26-53a6-4590-ce79-a3f7269274ea</td>\n",
       "      <td>0.250000</td>\n",
       "      <td>1990-09-22</td>\n",
       "      <td>F</td>\n",
       "      <td>31</td>\n",
       "      <td>25 – 54 years</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27890</th>\n",
       "      <td>64dfd3ce-7123-fa23-ec24-f74b492553e2</td>\n",
       "      <td>2022-02-05 20:27:36+00:00</td>\n",
       "      <td>2022-02-05 20:42:36+00:00</td>\n",
       "      <td>2022-02</td>\n",
       "      <td>e2d8e1ed-6f63-54e2-d14e-8346799e92ef</td>\n",
       "      <td>0.250000</td>\n",
       "      <td>1939-09-23</td>\n",
       "      <td>M</td>\n",
       "      <td>82</td>\n",
       "      <td>65 years and over</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>27891 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                         Id                     START  \\\n",
       "0      32c84703-2481-49cd-d571-3899d5820253 2011-01-02 09:26:36+00:00   \n",
       "1      c98059da-320a-c0a6-fced-c8815f3e3f39 2011-01-03 05:44:39+00:00   \n",
       "2      4ad28a3a-2479-782b-f29c-d5b3f41a001e 2011-01-03 14:32:11+00:00   \n",
       "3      c3f4da61-e4b4-21d5-587a-fbc89943bc19 2011-01-03 16:24:45+00:00   \n",
       "4      a9183b4f-2572-72ea-54c2-b3cd038b4be7 2011-01-03 17:36:53+00:00   \n",
       "...                                     ...                       ...   \n",
       "27886  917bb534-2ed3-e1f5-ac27-74f314d19c63 2022-01-29 11:42:06+00:00   \n",
       "27887  07710480-9d6b-9c9b-87c3-c1d54df4069d 2022-01-29 20:12:53+00:00   \n",
       "27888  01b57f06-cebe-a3e4-4423-a796ffb0c35d 2022-01-29 20:35:37+00:00   \n",
       "27889  7d435668-0813-eb3b-0f26-cb741fb39561 2022-01-29 20:35:37+00:00   \n",
       "27890  64dfd3ce-7123-fa23-ec24-f74b492553e2 2022-02-05 20:27:36+00:00   \n",
       "\n",
       "                           STOP START_MONTH  \\\n",
       "0     2011-01-02 12:58:36+00:00     2011-01   \n",
       "1     2011-01-03 06:01:42+00:00     2011-01   \n",
       "2     2011-01-03 14:47:11+00:00     2011-01   \n",
       "3     2011-01-03 16:39:45+00:00     2011-01   \n",
       "4     2011-01-03 17:51:53+00:00     2011-01   \n",
       "...                         ...         ...   \n",
       "27886 2022-01-29 11:57:06+00:00     2022-01   \n",
       "27887 2022-01-29 20:27:53+00:00     2022-01   \n",
       "27888 2022-01-29 20:50:37+00:00     2022-01   \n",
       "27889 2022-01-29 20:50:37+00:00     2022-01   \n",
       "27890 2022-02-05 20:42:36+00:00     2022-02   \n",
       "\n",
       "                                    PATIENT  STAY_DURATION   BIRTHDATE GENDER  \\\n",
       "0      3de74169-7f67-9304-91d4-757e0f3a14d2       3.533333  1928-08-25      M   \n",
       "1      d9ec2e44-32e9-9148-179a-1653348cc4e2       0.284167  1964-01-05      F   \n",
       "2      73babadf-5b2b-fee7-189e-6f41ff213e01       0.250000  1924-06-30      F   \n",
       "3      3b46a0b7-0f34-9b9a-c319-ace4a1f58c0b       0.250000  1923-05-21      M   \n",
       "4      fa006887-d93c-d302-8b89-f3c25f88c0e1       0.250000  1952-11-02      M   \n",
       "...                                     ...            ...         ...    ...   \n",
       "27886  0c122290-8c15-77ac-b418-2fea4e8a8e6a       0.250000  1974-04-27      M   \n",
       "27887  20a4bc24-6b69-2f5c-dc74-1df390cae25b       0.250000  1933-12-16      M   \n",
       "27888  ff1b3c26-53a6-4590-ce79-a3f7269274ea       0.250000  1990-09-22      F   \n",
       "27889  ff1b3c26-53a6-4590-ce79-a3f7269274ea       0.250000  1990-09-22      F   \n",
       "27890  e2d8e1ed-6f63-54e2-d14e-8346799e92ef       0.250000  1939-09-23      M   \n",
       "\n",
       "       AGE          AGE_GROUP  \n",
       "0       93  65 years and over  \n",
       "1       58      55 – 64 years  \n",
       "2       97  65 years and over  \n",
       "3       98  65 years and over  \n",
       "4       69  65 years and over  \n",
       "...    ...                ...  \n",
       "27886   47      25 – 54 years  \n",
       "27887   88  65 years and over  \n",
       "27888   31      25 – 54 years  \n",
       "27889   31      25 – 54 years  \n",
       "27890   82  65 years and over  \n",
       "\n",
       "[27891 rows x 10 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_length = pd.merge(\n",
    "    encounters[['Id', 'START', 'STOP', 'START_MONTH', 'PATIENT', 'STAY_DURATION']],\n",
    "    patients[['Id', 'BIRTHDATE', 'GENDER']].rename(columns={'Id': 'PATIENT'}),\n",
    "    on='PATIENT',\n",
    "    how='left'\n",
    ")\n",
    "\n",
    "df_length['AGE'] = (pd.to_datetime(current_date) - pd.to_datetime(df_length['BIRTHDATE'])).apply(lambda x: np.floor(x.days/365.25)).astype(int)\n",
    "df_length['AGE_GROUP'] = None\n",
    "for age_group, age_range in age_groups.items():\n",
    "    df_length['AGE_GROUP'] = np.where(\n",
    "        (df_length['AGE'] >= age_range[0]) & (df_length['AGE'] <= age_range[1]),\n",
    "        age_group,\n",
    "        df_length['AGE_GROUP']\n",
    "    )\n",
    "\n",
    "df_length"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>AVERAGE_DURATION</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>START_MONTH</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2011-01</th>\n",
       "      <td>2.651618</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011-02</th>\n",
       "      <td>2.816424</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011-03</th>\n",
       "      <td>1.641191</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011-04</th>\n",
       "      <td>1.578609</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011-05</th>\n",
       "      <td>1.757937</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-10</th>\n",
       "      <td>1.500546</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-11</th>\n",
       "      <td>1.076332</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-12</th>\n",
       "      <td>0.973694</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-01</th>\n",
       "      <td>1.159334</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-02</th>\n",
       "      <td>0.250000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>134 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             AVERAGE_DURATION\n",
       "START_MONTH                  \n",
       "2011-01              2.651618\n",
       "2011-02              2.816424\n",
       "2011-03              1.641191\n",
       "2011-04              1.578609\n",
       "2011-05              1.757937\n",
       "...                       ...\n",
       "2021-10              1.500546\n",
       "2021-11              1.076332\n",
       "2021-12              0.973694\n",
       "2022-01              1.159334\n",
       "2022-02              0.250000\n",
       "\n",
       "[134 rows x 1 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_length_grouped = df_length.groupby('START_MONTH').agg(\n",
    "    AVERAGE_DURATION = ('STAY_DURATION', 'mean')\n",
    ")\n",
    "\n",
    "df_length_grouped"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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></th>\n",
       "      <th>AVERAGE_DURATION</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>START_MONTH</th>\n",
       "      <th>AGE_GROUP</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">2011-01</th>\n",
       "      <th>25 – 54 years</th>\n",
       "      <td>2.625222</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55 – 64 years</th>\n",
       "      <td>0.256833</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65 years and over</th>\n",
       "      <td>2.823983</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">2011-02</th>\n",
       "      <th>25 – 54 years</th>\n",
       "      <td>0.250000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65 years and over</th>\n",
       "      <td>3.043829</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-12</th>\n",
       "      <th>65 years and over</th>\n",
       "      <td>1.116704</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">2022-01</th>\n",
       "      <th>25 – 54 years</th>\n",
       "      <td>1.050118</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55 – 64 years</th>\n",
       "      <td>0.250000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65 years and over</th>\n",
       "      <td>1.254304</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-02</th>\n",
       "      <th>65 years and over</th>\n",
       "      <td>0.250000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>399 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                               AVERAGE_DURATION\n",
       "START_MONTH AGE_GROUP                          \n",
       "2011-01     25 – 54 years              2.625222\n",
       "            55 – 64 years              0.256833\n",
       "            65 years and over          2.823983\n",
       "2011-02     25 – 54 years              0.250000\n",
       "            65 years and over          3.043829\n",
       "...                                         ...\n",
       "2021-12     65 years and over          1.116704\n",
       "2022-01     25 – 54 years              1.050118\n",
       "            55 – 64 years              0.250000\n",
       "            65 years and over          1.254304\n",
       "2022-02     65 years and over          0.250000\n",
       "\n",
       "[399 rows x 1 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_length_grouped = df_length.groupby(['START_MONTH', 'AGE_GROUP']).agg(\n",
    "    AVERAGE_DURATION = ('STAY_DURATION', 'mean')\n",
    ")\n",
    "\n",
    "df_length_grouped"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_cost = encounters[['Id', 'START', 'START_MONTH', 'ENCOUNTERCLASS', 'TOTAL_CLAIM_COST']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>AVERAGE_COST</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>START_MONTH</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2011-01</th>\n",
       "      <td>3013.043488</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011-02</th>\n",
       "      <td>3555.830000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011-03</th>\n",
       "      <td>2536.631860</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011-04</th>\n",
       "      <td>3420.855221</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011-05</th>\n",
       "      <td>3187.153893</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-10</th>\n",
       "      <td>4575.415244</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-11</th>\n",
       "      <td>3104.703296</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-12</th>\n",
       "      <td>3448.615758</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-01</th>\n",
       "      <td>3157.415799</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-02</th>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>134 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             AVERAGE_COST\n",
       "START_MONTH              \n",
       "2011-01       3013.043488\n",
       "2011-02       3555.830000\n",
       "2011-03       2536.631860\n",
       "2011-04       3420.855221\n",
       "2011-05       3187.153893\n",
       "...                   ...\n",
       "2021-10       4575.415244\n",
       "2021-11       3104.703296\n",
       "2021-12       3448.615758\n",
       "2022-01       3157.415799\n",
       "2022-02          0.000000\n",
       "\n",
       "[134 rows x 1 columns]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_cost.groupby('START_MONTH').agg(\n",
    "    AVERAGE_COST = ('TOTAL_CLAIM_COST', 'mean')\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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></th>\n",
       "      <th>AVERAGE_COST</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>START_MONTH</th>\n",
       "      <th>ENCOUNTERCLASS</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">2011-01</th>\n",
       "      <th>ambulatory</th>\n",
       "      <td>2543.549677</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>emergency</th>\n",
       "      <td>586.692500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>inpatient</th>\n",
       "      <td>10305.511429</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>outpatient</th>\n",
       "      <td>1771.302581</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>urgentcare</th>\n",
       "      <td>278.580000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">2022-01</th>\n",
       "      <th>inpatient</th>\n",
       "      <td>743.057500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>outpatient</th>\n",
       "      <td>3810.240811</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>urgentcare</th>\n",
       "      <td>5684.328571</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>wellness</th>\n",
       "      <td>4687.473636</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-02</th>\n",
       "      <th>wellness</th>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>797 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                            AVERAGE_COST\n",
       "START_MONTH ENCOUNTERCLASS              \n",
       "2011-01     ambulatory       2543.549677\n",
       "            emergency         586.692500\n",
       "            inpatient       10305.511429\n",
       "            outpatient       1771.302581\n",
       "            urgentcare        278.580000\n",
       "...                                  ...\n",
       "2022-01     inpatient         743.057500\n",
       "            outpatient       3810.240811\n",
       "            urgentcare       5684.328571\n",
       "            wellness         4687.473636\n",
       "2022-02     wellness            0.000000\n",
       "\n",
       "[797 rows x 1 columns]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_cost.groupby(['START_MONTH', 'ENCOUNTERCLASS']).agg(\n",
    "    AVERAGE_COST = ('TOTAL_CLAIM_COST', 'mean')\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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>Id</th>\n",
       "      <th>PATIENT</th>\n",
       "      <th>START</th>\n",
       "      <th>START_MONTH</th>\n",
       "      <th>TOTAL_CLAIM_COST</th>\n",
       "      <th>PAYER_COVERAGE</th>\n",
       "      <th>PAYER</th>\n",
       "      <th>NAME</th>\n",
       "      <th>IS_COVERED</th>\n",
       "      <th>PROCEDURES</th>\n",
       "      <th>TOTAL_PROCEDURE_COST</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>32c84703-2481-49cd-d571-3899d5820253</td>\n",
       "      <td>3de74169-7f67-9304-91d4-757e0f3a14d2</td>\n",
       "      <td>2011-01-02 09:26:36+00:00</td>\n",
       "      <td>2011-01</td>\n",
       "      <td>1018.02</td>\n",
       "      <td>0.00</td>\n",
       "      <td>b1c428d6-4f07-31e0-90f0-68ffa6ff8c76</td>\n",
       "      <td>NO_INSURANCE</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>903.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>c98059da-320a-c0a6-fced-c8815f3e3f39</td>\n",
       "      <td>d9ec2e44-32e9-9148-179a-1653348cc4e2</td>\n",
       "      <td>2011-01-03 05:44:39+00:00</td>\n",
       "      <td>2011-01</td>\n",
       "      <td>2619.36</td>\n",
       "      <td>0.00</td>\n",
       "      <td>b1c428d6-4f07-31e0-90f0-68ffa6ff8c76</td>\n",
       "      <td>NO_INSURANCE</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2477.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4ad28a3a-2479-782b-f29c-d5b3f41a001e</td>\n",
       "      <td>73babadf-5b2b-fee7-189e-6f41ff213e01</td>\n",
       "      <td>2011-01-03 14:32:11+00:00</td>\n",
       "      <td>2011-01</td>\n",
       "      <td>461.59</td>\n",
       "      <td>305.27</td>\n",
       "      <td>7caa7254-5050-3b5e-9eae-bd5ea30e809c</td>\n",
       "      <td>Medicare</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>c3f4da61-e4b4-21d5-587a-fbc89943bc19</td>\n",
       "      <td>3b46a0b7-0f34-9b9a-c319-ace4a1f58c0b</td>\n",
       "      <td>2011-01-03 16:24:45+00:00</td>\n",
       "      <td>2011-01</td>\n",
       "      <td>1784.24</td>\n",
       "      <td>0.00</td>\n",
       "      <td>b1c428d6-4f07-31e0-90f0-68ffa6ff8c76</td>\n",
       "      <td>NO_INSURANCE</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>a9183b4f-2572-72ea-54c2-b3cd038b4be7</td>\n",
       "      <td>fa006887-d93c-d302-8b89-f3c25f88c0e1</td>\n",
       "      <td>2011-01-03 17:36:53+00:00</td>\n",
       "      <td>2011-01</td>\n",
       "      <td>234.72</td>\n",
       "      <td>0.00</td>\n",
       "      <td>42c4fca7-f8a9-3cd1-982a-dd9751bf3e2a</td>\n",
       "      <td>Anthem</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27886</th>\n",
       "      <td>917bb534-2ed3-e1f5-ac27-74f314d19c63</td>\n",
       "      <td>0c122290-8c15-77ac-b418-2fea4e8a8e6a</td>\n",
       "      <td>2022-01-29 11:42:06+00:00</td>\n",
       "      <td>2022-01</td>\n",
       "      <td>85.55</td>\n",
       "      <td>24.27</td>\n",
       "      <td>7c4411ce-02f1-39b5-b9ec-dfbea9ad3c1a</td>\n",
       "      <td>Medicaid</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27887</th>\n",
       "      <td>07710480-9d6b-9c9b-87c3-c1d54df4069d</td>\n",
       "      <td>20a4bc24-6b69-2f5c-dc74-1df390cae25b</td>\n",
       "      <td>2022-01-29 20:12:53+00:00</td>\n",
       "      <td>2022-01</td>\n",
       "      <td>10588.34</td>\n",
       "      <td>8438.67</td>\n",
       "      <td>7caa7254-5050-3b5e-9eae-bd5ea30e809c</td>\n",
       "      <td>Medicare</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>10446.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27888</th>\n",
       "      <td>01b57f06-cebe-a3e4-4423-a796ffb0c35d</td>\n",
       "      <td>ff1b3c26-53a6-4590-ce79-a3f7269274ea</td>\n",
       "      <td>2022-01-29 20:35:37+00:00</td>\n",
       "      <td>2022-01</td>\n",
       "      <td>11984.29</td>\n",
       "      <td>0.00</td>\n",
       "      <td>b1c428d6-4f07-31e0-90f0-68ffa6ff8c76</td>\n",
       "      <td>NO_INSURANCE</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>12686.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27889</th>\n",
       "      <td>7d435668-0813-eb3b-0f26-cb741fb39561</td>\n",
       "      <td>ff1b3c26-53a6-4590-ce79-a3f7269274ea</td>\n",
       "      <td>2022-01-29 20:35:37+00:00</td>\n",
       "      <td>2022-01</td>\n",
       "      <td>408.80</td>\n",
       "      <td>0.00</td>\n",
       "      <td>b1c428d6-4f07-31e0-90f0-68ffa6ff8c76</td>\n",
       "      <td>NO_INSURANCE</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27890</th>\n",
       "      <td>64dfd3ce-7123-fa23-ec24-f74b492553e2</td>\n",
       "      <td>e2d8e1ed-6f63-54e2-d14e-8346799e92ef</td>\n",
       "      <td>2022-02-05 20:27:36+00:00</td>\n",
       "      <td>2022-02</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>7caa7254-5050-3b5e-9eae-bd5ea30e809c</td>\n",
       "      <td>Medicare</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>27891 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                         Id  \\\n",
       "0      32c84703-2481-49cd-d571-3899d5820253   \n",
       "1      c98059da-320a-c0a6-fced-c8815f3e3f39   \n",
       "2      4ad28a3a-2479-782b-f29c-d5b3f41a001e   \n",
       "3      c3f4da61-e4b4-21d5-587a-fbc89943bc19   \n",
       "4      a9183b4f-2572-72ea-54c2-b3cd038b4be7   \n",
       "...                                     ...   \n",
       "27886  917bb534-2ed3-e1f5-ac27-74f314d19c63   \n",
       "27887  07710480-9d6b-9c9b-87c3-c1d54df4069d   \n",
       "27888  01b57f06-cebe-a3e4-4423-a796ffb0c35d   \n",
       "27889  7d435668-0813-eb3b-0f26-cb741fb39561   \n",
       "27890  64dfd3ce-7123-fa23-ec24-f74b492553e2   \n",
       "\n",
       "                                    PATIENT                     START  \\\n",
       "0      3de74169-7f67-9304-91d4-757e0f3a14d2 2011-01-02 09:26:36+00:00   \n",
       "1      d9ec2e44-32e9-9148-179a-1653348cc4e2 2011-01-03 05:44:39+00:00   \n",
       "2      73babadf-5b2b-fee7-189e-6f41ff213e01 2011-01-03 14:32:11+00:00   \n",
       "3      3b46a0b7-0f34-9b9a-c319-ace4a1f58c0b 2011-01-03 16:24:45+00:00   \n",
       "4      fa006887-d93c-d302-8b89-f3c25f88c0e1 2011-01-03 17:36:53+00:00   \n",
       "...                                     ...                       ...   \n",
       "27886  0c122290-8c15-77ac-b418-2fea4e8a8e6a 2022-01-29 11:42:06+00:00   \n",
       "27887  20a4bc24-6b69-2f5c-dc74-1df390cae25b 2022-01-29 20:12:53+00:00   \n",
       "27888  ff1b3c26-53a6-4590-ce79-a3f7269274ea 2022-01-29 20:35:37+00:00   \n",
       "27889  ff1b3c26-53a6-4590-ce79-a3f7269274ea 2022-01-29 20:35:37+00:00   \n",
       "27890  e2d8e1ed-6f63-54e2-d14e-8346799e92ef 2022-02-05 20:27:36+00:00   \n",
       "\n",
       "      START_MONTH  TOTAL_CLAIM_COST  PAYER_COVERAGE  \\\n",
       "0         2011-01           1018.02            0.00   \n",
       "1         2011-01           2619.36            0.00   \n",
       "2         2011-01            461.59          305.27   \n",
       "3         2011-01           1784.24            0.00   \n",
       "4         2011-01            234.72            0.00   \n",
       "...           ...               ...             ...   \n",
       "27886     2022-01             85.55           24.27   \n",
       "27887     2022-01          10588.34         8438.67   \n",
       "27888     2022-01          11984.29            0.00   \n",
       "27889     2022-01            408.80            0.00   \n",
       "27890     2022-02              0.00            0.00   \n",
       "\n",
       "                                      PAYER          NAME  IS_COVERED  \\\n",
       "0      b1c428d6-4f07-31e0-90f0-68ffa6ff8c76  NO_INSURANCE           0   \n",
       "1      b1c428d6-4f07-31e0-90f0-68ffa6ff8c76  NO_INSURANCE           0   \n",
       "2      7caa7254-5050-3b5e-9eae-bd5ea30e809c      Medicare           1   \n",
       "3      b1c428d6-4f07-31e0-90f0-68ffa6ff8c76  NO_INSURANCE           0   \n",
       "4      42c4fca7-f8a9-3cd1-982a-dd9751bf3e2a        Anthem           1   \n",
       "...                                     ...           ...         ...   \n",
       "27886  7c4411ce-02f1-39b5-b9ec-dfbea9ad3c1a      Medicaid           1   \n",
       "27887  7caa7254-5050-3b5e-9eae-bd5ea30e809c      Medicare           1   \n",
       "27888  b1c428d6-4f07-31e0-90f0-68ffa6ff8c76  NO_INSURANCE           0   \n",
       "27889  b1c428d6-4f07-31e0-90f0-68ffa6ff8c76  NO_INSURANCE           0   \n",
       "27890  7caa7254-5050-3b5e-9eae-bd5ea30e809c      Medicare           1   \n",
       "\n",
       "       PROCEDURES  TOTAL_PROCEDURE_COST  \n",
       "0               1                 903.0  \n",
       "1               1                2477.0  \n",
       "2               0                   0.0  \n",
       "3               0                   0.0  \n",
       "4               0                   0.0  \n",
       "...           ...                   ...  \n",
       "27886           0                   0.0  \n",
       "27887           1               10446.0  \n",
       "27888           5               12686.0  \n",
       "27889           0                   0.0  \n",
       "27890           0                   0.0  \n",
       "\n",
       "[27891 rows x 11 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_encounter_coverage = pd.merge(\n",
    "    encounters[['Id', 'PATIENT', 'START', 'START_MONTH', 'TOTAL_CLAIM_COST', 'PAYER_COVERAGE', 'PAYER']],\n",
    "    payers[['Id', 'NAME']].rename(columns={'Id': 'PAYER'}),\n",
    "    on='PAYER',\n",
    "    how='left'\n",
    ")\n",
    "\n",
    "df_encounter_coverage['IS_COVERED'] = np.where(df_encounter_coverage['NAME'] == 'NO_INSURANCE', 0, 1)\n",
    "\n",
    "df_procedures = procedures.groupby(['ENCOUNTER', 'PATIENT']).agg(\n",
    "    PROCEDURES = ('ENCOUNTER', 'count'),\n",
    "    TOTAL_PROCEDURE_COST = ('BASE_COST', 'sum')\n",
    ").reset_index()\n",
    "\n",
    "df_encounter_coverage = df_encounter_coverage.merge(\n",
    "    df_procedures.rename(columns={'ENCOUNTER': 'Id'}),\n",
    "    on=['Id', 'PATIENT'],\n",
    "    how='left'\n",
    ")\n",
    "\n",
    "df_encounter_coverage['PROCEDURES'] = df_encounter_coverage['PROCEDURES'].fillna(0).astype(int)\n",
    "df_encounter_coverage['TOTAL_PROCEDURE_COST'] = df_encounter_coverage['TOTAL_PROCEDURE_COST'].fillna(0)\n",
    "\n",
    "df_encounter_coverage"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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></th>\n",
       "      <th>PROCEDURES</th>\n",
       "      <th>TOTAL_PROCEDURE_COST</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>START_MONTH</th>\n",
       "      <th>IS_COVERED</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">2011-01</th>\n",
       "      <th>0</th>\n",
       "      <td>27</td>\n",
       "      <td>43122.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>51</td>\n",
       "      <td>168711.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">2011-02</th>\n",
       "      <th>0</th>\n",
       "      <td>33</td>\n",
       "      <td>70880.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>92</td>\n",
       "      <td>194606.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011-03</th>\n",
       "      <th>0</th>\n",
       "      <td>26</td>\n",
       "      <td>97430.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">2021-12</th>\n",
       "      <th>0</th>\n",
       "      <td>81</td>\n",
       "      <td>512162.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>235</td>\n",
       "      <td>312540.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">2022-01</th>\n",
       "      <th>0</th>\n",
       "      <td>99</td>\n",
       "      <td>340579.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>167</td>\n",
       "      <td>373484.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-02</th>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>267 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                        PROCEDURES  TOTAL_PROCEDURE_COST\n",
       "START_MONTH IS_COVERED                                  \n",
       "2011-01     0                   27               43122.0\n",
       "            1                   51              168711.0\n",
       "2011-02     0                   33               70880.0\n",
       "            1                   92              194606.0\n",
       "2011-03     0                   26               97430.0\n",
       "...                            ...                   ...\n",
       "2021-12     0                   81              512162.0\n",
       "            1                  235              312540.0\n",
       "2022-01     0                   99              340579.0\n",
       "            1                  167              373484.0\n",
       "2022-02     1                    0                   0.0\n",
       "\n",
       "[267 rows x 2 columns]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_encounter_coverage_grouped = df_encounter_coverage.groupby(['START_MONTH', 'IS_COVERED']).agg(\n",
    "    PROCEDURES = ('PROCEDURES', 'sum'),\n",
    "    TOTAL_PROCEDURE_COST = ('TOTAL_PROCEDURE_COST', 'sum')\n",
    ")\n",
    "\n",
    "df_encounter_coverage_grouped"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "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>START</th>\n",
       "      <th>STOP</th>\n",
       "      <th>PATIENT</th>\n",
       "      <th>ENCOUNTER</th>\n",
       "      <th>CODE</th>\n",
       "      <th>DESCRIPTION</th>\n",
       "      <th>BASE_COST</th>\n",
       "      <th>REASONCODE</th>\n",
       "      <th>REASONDESCRIPTION</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2011-01-02T09:26:36Z</td>\n",
       "      <td>2011-01-02T12:58:36Z</td>\n",
       "      <td>3de74169-7f67-9304-91d4-757e0f3a14d2</td>\n",
       "      <td>32c84703-2481-49cd-d571-3899d5820253</td>\n",
       "      <td>265764009</td>\n",
       "      <td>Renal dialysis (procedure)</td>\n",
       "      <td>903</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2011-01-03T05:44:39Z</td>\n",
       "      <td>2011-01-03T06:01:42Z</td>\n",
       "      <td>d9ec2e44-32e9-9148-179a-1653348cc4e2</td>\n",
       "      <td>c98059da-320a-c0a6-fced-c8815f3e3f39</td>\n",
       "      <td>76601001</td>\n",
       "      <td>Intramuscular injection</td>\n",
       "      <td>2477</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2011-01-04T14:49:55Z</td>\n",
       "      <td>2011-01-04T15:04:55Z</td>\n",
       "      <td>d856d6e6-4c98-e7a2-129b-44076c63d008</td>\n",
       "      <td>2cfd4ddd-ad13-fe1e-528b-15051cea2ec3</td>\n",
       "      <td>703423002</td>\n",
       "      <td>Combined chemotherapy and radiation therapy (p...</td>\n",
       "      <td>11620</td>\n",
       "      <td>363406005.0</td>\n",
       "      <td>Malignant tumor of colon</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2011-01-05T04:02:09Z</td>\n",
       "      <td>2011-01-05T04:17:09Z</td>\n",
       "      <td>bc9d59c3-0a30-6e3b-f47d-022e4f03c8de</td>\n",
       "      <td>17966936-0878-f4db-128b-a43ae10d0878</td>\n",
       "      <td>173160006</td>\n",
       "      <td>Diagnostic fiberoptic bronchoscopy (procedure)</td>\n",
       "      <td>9796</td>\n",
       "      <td>162573006.0</td>\n",
       "      <td>Suspected lung cancer (situation)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2011-01-05T12:58:36Z</td>\n",
       "      <td>2011-01-05T16:42:36Z</td>\n",
       "      <td>3de74169-7f67-9304-91d4-757e0f3a14d2</td>\n",
       "      <td>9de5f0b0-4ba4-ce6f-45fb-b55c202f31a5</td>\n",
       "      <td>265764009</td>\n",
       "      <td>Renal dialysis (procedure)</td>\n",
       "      <td>1255</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47696</th>\n",
       "      <td>2022-01-29T20:35:37Z</td>\n",
       "      <td>2022-01-29T20:50:37Z</td>\n",
       "      <td>ff1b3c26-53a6-4590-ce79-a3f7269274ea</td>\n",
       "      <td>01b57f06-cebe-a3e4-4423-a796ffb0c35d</td>\n",
       "      <td>430193006</td>\n",
       "      <td>Medication Reconciliation (procedure)</td>\n",
       "      <td>413</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47697</th>\n",
       "      <td>2022-01-29T20:35:37Z</td>\n",
       "      <td>2022-01-29T20:50:37Z</td>\n",
       "      <td>ff1b3c26-53a6-4590-ce79-a3f7269274ea</td>\n",
       "      <td>01b57f06-cebe-a3e4-4423-a796ffb0c35d</td>\n",
       "      <td>274804006</td>\n",
       "      <td>Evaluation of uterine fundal height</td>\n",
       "      <td>3238</td>\n",
       "      <td>72892002.0</td>\n",
       "      <td>Normal pregnancy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47698</th>\n",
       "      <td>2022-01-29T20:35:37Z</td>\n",
       "      <td>2022-01-29T20:50:37Z</td>\n",
       "      <td>ff1b3c26-53a6-4590-ce79-a3f7269274ea</td>\n",
       "      <td>01b57f06-cebe-a3e4-4423-a796ffb0c35d</td>\n",
       "      <td>225158009</td>\n",
       "      <td>Auscultation of the fetal heart</td>\n",
       "      <td>7045</td>\n",
       "      <td>72892002.0</td>\n",
       "      <td>Normal pregnancy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47699</th>\n",
       "      <td>2022-01-29T20:35:37Z</td>\n",
       "      <td>2022-01-29T20:50:37Z</td>\n",
       "      <td>ff1b3c26-53a6-4590-ce79-a3f7269274ea</td>\n",
       "      <td>01b57f06-cebe-a3e4-4423-a796ffb0c35d</td>\n",
       "      <td>118001005</td>\n",
       "      <td>Streptococcus pneumoniae group B antigen test</td>\n",
       "      <td>1559</td>\n",
       "      <td>72892002.0</td>\n",
       "      <td>Normal pregnancy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47700</th>\n",
       "      <td>2022-01-29T20:35:37Z</td>\n",
       "      <td>2022-01-29T21:08:12Z</td>\n",
       "      <td>ff1b3c26-53a6-4590-ce79-a3f7269274ea</td>\n",
       "      <td>01b57f06-cebe-a3e4-4423-a796ffb0c35d</td>\n",
       "      <td>710824005</td>\n",
       "      <td>Assessment of health and social care needs (pr...</td>\n",
       "      <td>431</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>47701 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                      START                  STOP  \\\n",
       "0      2011-01-02T09:26:36Z  2011-01-02T12:58:36Z   \n",
       "1      2011-01-03T05:44:39Z  2011-01-03T06:01:42Z   \n",
       "2      2011-01-04T14:49:55Z  2011-01-04T15:04:55Z   \n",
       "3      2011-01-05T04:02:09Z  2011-01-05T04:17:09Z   \n",
       "4      2011-01-05T12:58:36Z  2011-01-05T16:42:36Z   \n",
       "...                     ...                   ...   \n",
       "47696  2022-01-29T20:35:37Z  2022-01-29T20:50:37Z   \n",
       "47697  2022-01-29T20:35:37Z  2022-01-29T20:50:37Z   \n",
       "47698  2022-01-29T20:35:37Z  2022-01-29T20:50:37Z   \n",
       "47699  2022-01-29T20:35:37Z  2022-01-29T20:50:37Z   \n",
       "47700  2022-01-29T20:35:37Z  2022-01-29T21:08:12Z   \n",
       "\n",
       "                                    PATIENT  \\\n",
       "0      3de74169-7f67-9304-91d4-757e0f3a14d2   \n",
       "1      d9ec2e44-32e9-9148-179a-1653348cc4e2   \n",
       "2      d856d6e6-4c98-e7a2-129b-44076c63d008   \n",
       "3      bc9d59c3-0a30-6e3b-f47d-022e4f03c8de   \n",
       "4      3de74169-7f67-9304-91d4-757e0f3a14d2   \n",
       "...                                     ...   \n",
       "47696  ff1b3c26-53a6-4590-ce79-a3f7269274ea   \n",
       "47697  ff1b3c26-53a6-4590-ce79-a3f7269274ea   \n",
       "47698  ff1b3c26-53a6-4590-ce79-a3f7269274ea   \n",
       "47699  ff1b3c26-53a6-4590-ce79-a3f7269274ea   \n",
       "47700  ff1b3c26-53a6-4590-ce79-a3f7269274ea   \n",
       "\n",
       "                                  ENCOUNTER       CODE  \\\n",
       "0      32c84703-2481-49cd-d571-3899d5820253  265764009   \n",
       "1      c98059da-320a-c0a6-fced-c8815f3e3f39   76601001   \n",
       "2      2cfd4ddd-ad13-fe1e-528b-15051cea2ec3  703423002   \n",
       "3      17966936-0878-f4db-128b-a43ae10d0878  173160006   \n",
       "4      9de5f0b0-4ba4-ce6f-45fb-b55c202f31a5  265764009   \n",
       "...                                     ...        ...   \n",
       "47696  01b57f06-cebe-a3e4-4423-a796ffb0c35d  430193006   \n",
       "47697  01b57f06-cebe-a3e4-4423-a796ffb0c35d  274804006   \n",
       "47698  01b57f06-cebe-a3e4-4423-a796ffb0c35d  225158009   \n",
       "47699  01b57f06-cebe-a3e4-4423-a796ffb0c35d  118001005   \n",
       "47700  01b57f06-cebe-a3e4-4423-a796ffb0c35d  710824005   \n",
       "\n",
       "                                             DESCRIPTION  BASE_COST  \\\n",
       "0                             Renal dialysis (procedure)        903   \n",
       "1                                Intramuscular injection       2477   \n",
       "2      Combined chemotherapy and radiation therapy (p...      11620   \n",
       "3         Diagnostic fiberoptic bronchoscopy (procedure)       9796   \n",
       "4                             Renal dialysis (procedure)       1255   \n",
       "...                                                  ...        ...   \n",
       "47696              Medication Reconciliation (procedure)        413   \n",
       "47697                Evaluation of uterine fundal height       3238   \n",
       "47698                    Auscultation of the fetal heart       7045   \n",
       "47699      Streptococcus pneumoniae group B antigen test       1559   \n",
       "47700  Assessment of health and social care needs (pr...        431   \n",
       "\n",
       "        REASONCODE                  REASONDESCRIPTION  \n",
       "0              NaN                                NaN  \n",
       "1              NaN                                NaN  \n",
       "2      363406005.0           Malignant tumor of colon  \n",
       "3      162573006.0  Suspected lung cancer (situation)  \n",
       "4              NaN                                NaN  \n",
       "...            ...                                ...  \n",
       "47696          NaN                                NaN  \n",
       "47697   72892002.0                   Normal pregnancy  \n",
       "47698   72892002.0                   Normal pregnancy  \n",
       "47699   72892002.0                   Normal pregnancy  \n",
       "47700          NaN                                NaN  \n",
       "\n",
       "[47701 rows x 9 columns]"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "procedures"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "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>Id</th>\n",
       "      <th>PATIENT</th>\n",
       "      <th>DESCRIPTION</th>\n",
       "      <th>BASE_COST</th>\n",
       "      <th>START</th>\n",
       "      <th>START_MONTH</th>\n",
       "      <th>IS_COVERED</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>32c84703-2481-49cd-d571-3899d5820253</td>\n",
       "      <td>3de74169-7f67-9304-91d4-757e0f3a14d2</td>\n",
       "      <td>Renal dialysis (procedure)</td>\n",
       "      <td>903</td>\n",
       "      <td>2011-01-02 09:26:36+00:00</td>\n",
       "      <td>2011-01</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>c98059da-320a-c0a6-fced-c8815f3e3f39</td>\n",
       "      <td>d9ec2e44-32e9-9148-179a-1653348cc4e2</td>\n",
       "      <td>Intramuscular injection</td>\n",
       "      <td>2477</td>\n",
       "      <td>2011-01-03 05:44:39+00:00</td>\n",
       "      <td>2011-01</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2cfd4ddd-ad13-fe1e-528b-15051cea2ec3</td>\n",
       "      <td>d856d6e6-4c98-e7a2-129b-44076c63d008</td>\n",
       "      <td>Combined chemotherapy and radiation therapy (p...</td>\n",
       "      <td>11620</td>\n",
       "      <td>2011-01-04 14:49:55+00:00</td>\n",
       "      <td>2011-01</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>17966936-0878-f4db-128b-a43ae10d0878</td>\n",
       "      <td>bc9d59c3-0a30-6e3b-f47d-022e4f03c8de</td>\n",
       "      <td>Diagnostic fiberoptic bronchoscopy (procedure)</td>\n",
       "      <td>9796</td>\n",
       "      <td>2011-01-05 04:02:09+00:00</td>\n",
       "      <td>2011-01</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>9de5f0b0-4ba4-ce6f-45fb-b55c202f31a5</td>\n",
       "      <td>3de74169-7f67-9304-91d4-757e0f3a14d2</td>\n",
       "      <td>Renal dialysis (procedure)</td>\n",
       "      <td>1255</td>\n",
       "      <td>2011-01-05 12:58:36+00:00</td>\n",
       "      <td>2011-01</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47696</th>\n",
       "      <td>01b57f06-cebe-a3e4-4423-a796ffb0c35d</td>\n",
       "      <td>ff1b3c26-53a6-4590-ce79-a3f7269274ea</td>\n",
       "      <td>Medication Reconciliation (procedure)</td>\n",
       "      <td>413</td>\n",
       "      <td>2022-01-29 20:35:37+00:00</td>\n",
       "      <td>2022-01</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47697</th>\n",
       "      <td>01b57f06-cebe-a3e4-4423-a796ffb0c35d</td>\n",
       "      <td>ff1b3c26-53a6-4590-ce79-a3f7269274ea</td>\n",
       "      <td>Evaluation of uterine fundal height</td>\n",
       "      <td>3238</td>\n",
       "      <td>2022-01-29 20:35:37+00:00</td>\n",
       "      <td>2022-01</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47698</th>\n",
       "      <td>01b57f06-cebe-a3e4-4423-a796ffb0c35d</td>\n",
       "      <td>ff1b3c26-53a6-4590-ce79-a3f7269274ea</td>\n",
       "      <td>Auscultation of the fetal heart</td>\n",
       "      <td>7045</td>\n",
       "      <td>2022-01-29 20:35:37+00:00</td>\n",
       "      <td>2022-01</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47699</th>\n",
       "      <td>01b57f06-cebe-a3e4-4423-a796ffb0c35d</td>\n",
       "      <td>ff1b3c26-53a6-4590-ce79-a3f7269274ea</td>\n",
       "      <td>Streptococcus pneumoniae group B antigen test</td>\n",
       "      <td>1559</td>\n",
       "      <td>2022-01-29 20:35:37+00:00</td>\n",
       "      <td>2022-01</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47700</th>\n",
       "      <td>01b57f06-cebe-a3e4-4423-a796ffb0c35d</td>\n",
       "      <td>ff1b3c26-53a6-4590-ce79-a3f7269274ea</td>\n",
       "      <td>Assessment of health and social care needs (pr...</td>\n",
       "      <td>431</td>\n",
       "      <td>2022-01-29 20:35:37+00:00</td>\n",
       "      <td>2022-01</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>47701 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                         Id  \\\n",
       "0      32c84703-2481-49cd-d571-3899d5820253   \n",
       "1      c98059da-320a-c0a6-fced-c8815f3e3f39   \n",
       "2      2cfd4ddd-ad13-fe1e-528b-15051cea2ec3   \n",
       "3      17966936-0878-f4db-128b-a43ae10d0878   \n",
       "4      9de5f0b0-4ba4-ce6f-45fb-b55c202f31a5   \n",
       "...                                     ...   \n",
       "47696  01b57f06-cebe-a3e4-4423-a796ffb0c35d   \n",
       "47697  01b57f06-cebe-a3e4-4423-a796ffb0c35d   \n",
       "47698  01b57f06-cebe-a3e4-4423-a796ffb0c35d   \n",
       "47699  01b57f06-cebe-a3e4-4423-a796ffb0c35d   \n",
       "47700  01b57f06-cebe-a3e4-4423-a796ffb0c35d   \n",
       "\n",
       "                                    PATIENT  \\\n",
       "0      3de74169-7f67-9304-91d4-757e0f3a14d2   \n",
       "1      d9ec2e44-32e9-9148-179a-1653348cc4e2   \n",
       "2      d856d6e6-4c98-e7a2-129b-44076c63d008   \n",
       "3      bc9d59c3-0a30-6e3b-f47d-022e4f03c8de   \n",
       "4      3de74169-7f67-9304-91d4-757e0f3a14d2   \n",
       "...                                     ...   \n",
       "47696  ff1b3c26-53a6-4590-ce79-a3f7269274ea   \n",
       "47697  ff1b3c26-53a6-4590-ce79-a3f7269274ea   \n",
       "47698  ff1b3c26-53a6-4590-ce79-a3f7269274ea   \n",
       "47699  ff1b3c26-53a6-4590-ce79-a3f7269274ea   \n",
       "47700  ff1b3c26-53a6-4590-ce79-a3f7269274ea   \n",
       "\n",
       "                                             DESCRIPTION  BASE_COST  \\\n",
       "0                             Renal dialysis (procedure)        903   \n",
       "1                                Intramuscular injection       2477   \n",
       "2      Combined chemotherapy and radiation therapy (p...      11620   \n",
       "3         Diagnostic fiberoptic bronchoscopy (procedure)       9796   \n",
       "4                             Renal dialysis (procedure)       1255   \n",
       "...                                                  ...        ...   \n",
       "47696              Medication Reconciliation (procedure)        413   \n",
       "47697                Evaluation of uterine fundal height       3238   \n",
       "47698                    Auscultation of the fetal heart       7045   \n",
       "47699      Streptococcus pneumoniae group B antigen test       1559   \n",
       "47700  Assessment of health and social care needs (pr...        431   \n",
       "\n",
       "                          START START_MONTH  IS_COVERED  \n",
       "0     2011-01-02 09:26:36+00:00     2011-01           0  \n",
       "1     2011-01-03 05:44:39+00:00     2011-01           0  \n",
       "2     2011-01-04 14:49:55+00:00     2011-01           1  \n",
       "3     2011-01-05 04:02:09+00:00     2011-01           1  \n",
       "4     2011-01-05 12:58:36+00:00     2011-01           0  \n",
       "...                         ...         ...         ...  \n",
       "47696 2022-01-29 20:35:37+00:00     2022-01           0  \n",
       "47697 2022-01-29 20:35:37+00:00     2022-01           0  \n",
       "47698 2022-01-29 20:35:37+00:00     2022-01           0  \n",
       "47699 2022-01-29 20:35:37+00:00     2022-01           0  \n",
       "47700 2022-01-29 20:35:37+00:00     2022-01           0  \n",
       "\n",
       "[47701 rows x 7 columns]"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_procedure_coverage = pd.merge(\n",
    "    procedures[['ENCOUNTER', 'PATIENT', 'DESCRIPTION', 'BASE_COST']].rename(columns={'ENCOUNTER': 'Id'}),\n",
    "    df_encounter_coverage[['Id', 'PATIENT', 'START', 'START_MONTH', 'IS_COVERED']],\n",
    "    on=['Id', 'PATIENT'],\n",
    "    how='left'\n",
    ")\n",
    "\n",
    "df_procedure_coverage"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "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></th>\n",
       "      <th>BASE_COST</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>START_MONTH</th>\n",
       "      <th>DESCRIPTION</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">2011-01</th>\n",
       "      <th>Assessment of anxiety (procedure)</th>\n",
       "      <td>431.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Assessment of health and social care needs (procedure)</th>\n",
       "      <td>431.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Assessment of substance use (procedure)</th>\n",
       "      <td>431.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Assessment using Morse Fall Scale (procedure)</th>\n",
       "      <td>431.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Combined chemotherapy and radiation therapy (procedure)</th>\n",
       "      <td>11081.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">2022-01</th>\n",
       "      <th>Screening for domestic abuse (procedure)</th>\n",
       "      <td>431.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Screening for drug abuse (procedure)</th>\n",
       "      <td>431.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Streptococcus pneumoniae group B antigen test</th>\n",
       "      <td>1559.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Urine screening for glucose</th>\n",
       "      <td>1619.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Vaccination for diphtheria  pertussis  and tetanus</th>\n",
       "      <td>3167.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4046 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                BASE_COST\n",
       "START_MONTH DESCRIPTION                                                  \n",
       "2011-01     Assessment of anxiety (procedure)                       431.0\n",
       "            Assessment of health and social care needs (pro...      431.0\n",
       "            Assessment of substance use (procedure)                 431.0\n",
       "            Assessment using Morse Fall Scale (procedure)           431.0\n",
       "            Combined chemotherapy and radiation therapy (pr...    11081.0\n",
       "...                                                                   ...\n",
       "2022-01     Screening for domestic abuse (procedure)                431.0\n",
       "            Screening for drug abuse (procedure)                    431.0\n",
       "            Streptococcus pneumoniae group B antigen test          1559.0\n",
       "            Urine screening for glucose                            1619.0\n",
       "            Vaccination for diphtheria  pertussis  and tetanus     3167.0\n",
       "\n",
       "[4046 rows x 1 columns]"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_procedure_coverage_grouped = df_procedure_coverage[df_procedure_coverage['IS_COVERED'] == 0].groupby(['START_MONTH', 'DESCRIPTION']).agg(\n",
    "    BASE_COST = ('BASE_COST', 'mean')\n",
    ")\n",
    "\n",
    "df_procedure_coverage_grouped"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}