{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "# COVID-19 - unvaccinated high risk patients\n",
    "\n",
    "Analysis of FHIR source data using Python, the Python standard library and the [Pandas](https://pandas.pydata.org/) data analysis package.\n",
    "\n",
    "This query counts patients, grouped by whether they have received a COVID-19 vaccination and whether they are high-risk based upon a number of factors (CKD, heart disease, BMI).\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "import json\n",
    "from http.client import HTTPConnection\n",
    "from time import time\n",
    "from urllib.parse import urlencode\n",
    "\n",
    "import pandas as pd\n",
    "\n",
    "start = time()\n",
    "tx_conn = HTTPConnection('localhost', 8081)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "## Load and extract required data\n",
    "\n",
    "Load the FHIR NDJSON data, extract the required fields and construct a Pandas dataframe for each resource type."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "def get_resources(filename):\n",
    "    with open(filename) as file:\n",
    "        lines = file.readlines()\n",
    "        return [json.loads(line.rstrip()) for line in lines]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "resources = ['Patient', 'Immunization', 'Condition', 'Observation']\n",
    "resource_data = {\n",
    "    resource: get_resources(\n",
    "        f'/Users/gri306/Library/CloudStorage/OneDrive-CSIRO/Data/synthea/paper-md/fhir/{resource}.ndjson')\n",
    "    for resource in resources\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "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>name</th>\n",
       "      <th>birth_date</th>\n",
       "      <th>telephone</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>53cad937-1b82-880f-50bc-2c76f3742b8f</td>\n",
       "      <td>Mueller846, Pei116</td>\n",
       "      <td>1987-10-28</td>\n",
       "      <td>555-819-4627</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>13866099-28d7-1249-3e44-ecb490d40fef</td>\n",
       "      <td>Buckridge80, Dominique369</td>\n",
       "      <td>1980-09-14</td>\n",
       "      <td>555-181-6346</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6b8e2a56-af0b-9081-78bf-3eb0de39271d</td>\n",
       "      <td>Skiles927, Mila257</td>\n",
       "      <td>1993-12-11</td>\n",
       "      <td>555-308-4688</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>71cb26ff-d626-8884-c6e8-9c82e9fe850d</td>\n",
       "      <td>Heaney114, Jacquetta148</td>\n",
       "      <td>2016-09-01</td>\n",
       "      <td>555-655-6520</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>cfd691b9-97ef-fb65-b6b3-5333822005b3</td>\n",
       "      <td>McDermott739, Brady998</td>\n",
       "      <td>1995-11-08</td>\n",
       "      <td>555-161-8715</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1149</th>\n",
       "      <td>7d8f64ee-9a70-b76c-a665-ee9d32af405e</td>\n",
       "      <td>Conn188, Corrinne176</td>\n",
       "      <td>1942-11-27</td>\n",
       "      <td>555-794-1056</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1150</th>\n",
       "      <td>8803754a-1f93-87ef-e39c-19a112f32688</td>\n",
       "      <td>Nitzsche158, Dwain139</td>\n",
       "      <td>1944-10-06</td>\n",
       "      <td>555-586-7566</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1151</th>\n",
       "      <td>41d07a5e-1d6d-0f12-af60-b44038d6a0d8</td>\n",
       "      <td>Wisozk929, Ray930</td>\n",
       "      <td>1928-04-06</td>\n",
       "      <td>555-755-2501</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1152</th>\n",
       "      <td>2c3e3e76-b51e-3196-7454-f4ee47b0cf7e</td>\n",
       "      <td>Balistreri607, Michael441</td>\n",
       "      <td>1928-04-06</td>\n",
       "      <td>555-269-9842</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1153</th>\n",
       "      <td>a85c904c-19e1-7218-4869-994bcecd96d9</td>\n",
       "      <td>Zboncak558, Maximo817</td>\n",
       "      <td>1928-04-06</td>\n",
       "      <td>555-269-9854</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1154 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                        id                       name  \\\n",
       "0     53cad937-1b82-880f-50bc-2c76f3742b8f         Mueller846, Pei116   \n",
       "1     13866099-28d7-1249-3e44-ecb490d40fef  Buckridge80, Dominique369   \n",
       "2     6b8e2a56-af0b-9081-78bf-3eb0de39271d         Skiles927, Mila257   \n",
       "3     71cb26ff-d626-8884-c6e8-9c82e9fe850d    Heaney114, Jacquetta148   \n",
       "4     cfd691b9-97ef-fb65-b6b3-5333822005b3     McDermott739, Brady998   \n",
       "...                                    ...                        ...   \n",
       "1149  7d8f64ee-9a70-b76c-a665-ee9d32af405e       Conn188, Corrinne176   \n",
       "1150  8803754a-1f93-87ef-e39c-19a112f32688      Nitzsche158, Dwain139   \n",
       "1151  41d07a5e-1d6d-0f12-af60-b44038d6a0d8          Wisozk929, Ray930   \n",
       "1152  2c3e3e76-b51e-3196-7454-f4ee47b0cf7e  Balistreri607, Michael441   \n",
       "1153  a85c904c-19e1-7218-4869-994bcecd96d9      Zboncak558, Maximo817   \n",
       "\n",
       "      birth_date     telephone  \n",
       "0     1987-10-28  555-819-4627  \n",
       "1     1980-09-14  555-181-6346  \n",
       "2     1993-12-11  555-308-4688  \n",
       "3     2016-09-01  555-655-6520  \n",
       "4     1995-11-08  555-161-8715  \n",
       "...          ...           ...  \n",
       "1149  1942-11-27  555-794-1056  \n",
       "1150  1944-10-06  555-586-7566  \n",
       "1151  1928-04-06  555-755-2501  \n",
       "1152  1928-04-06  555-269-9842  \n",
       "1153  1928-04-06  555-269-9854  \n",
       "\n",
       "[1154 rows x 4 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "patient_data = [\n",
    "    {\n",
    "        'id': patient['id'],\n",
    "        'name': [\n",
    "            f\"{name['family']}, {name['given'][0]}\"\n",
    "            for name\n",
    "            in patient['name']\n",
    "            if name['use'] == 'official'\n",
    "        ][0],\n",
    "        'birth_date': patient['birthDate'],\n",
    "        'telephone': [\n",
    "            telecom['value']\n",
    "            for telecom\n",
    "            in patient['telecom']\n",
    "            if telecom['system'] == 'phone'\n",
    "        ][0]\n",
    "    } for patient in resource_data['Patient']\n",
    "]\n",
    "patient_df = pd.DataFrame.from_records(data=patient_data)\n",
    "patient_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "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>patient_id</th>\n",
       "      <th>codes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>71cb26ff-d626-8884-c6e8-9c82e9fe850d</td>\n",
       "      <td>[{'system': 'http://hl7.org/fhir/sid/cvx', 'co...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>cfd691b9-97ef-fb65-b6b3-5333822005b3</td>\n",
       "      <td>[{'system': 'http://hl7.org/fhir/sid/cvx', 'co...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>71cb26ff-d626-8884-c6e8-9c82e9fe850d</td>\n",
       "      <td>[{'system': 'http://hl7.org/fhir/sid/cvx', 'co...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>cfd691b9-97ef-fb65-b6b3-5333822005b3</td>\n",
       "      <td>[{'system': 'http://hl7.org/fhir/sid/cvx', 'co...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>71cb26ff-d626-8884-c6e8-9c82e9fe850d</td>\n",
       "      <td>[{'system': 'http://hl7.org/fhir/sid/cvx', 'co...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16461</th>\n",
       "      <td>a85c904c-19e1-7218-4869-994bcecd96d9</td>\n",
       "      <td>[{'system': 'http://hl7.org/fhir/sid/cvx', 'co...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16462</th>\n",
       "      <td>a85c904c-19e1-7218-4869-994bcecd96d9</td>\n",
       "      <td>[{'system': 'http://hl7.org/fhir/sid/cvx', 'co...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16463</th>\n",
       "      <td>a85c904c-19e1-7218-4869-994bcecd96d9</td>\n",
       "      <td>[{'system': 'http://hl7.org/fhir/sid/cvx', 'co...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16464</th>\n",
       "      <td>a85c904c-19e1-7218-4869-994bcecd96d9</td>\n",
       "      <td>[{'system': 'http://hl7.org/fhir/sid/cvx', 'co...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16465</th>\n",
       "      <td>a85c904c-19e1-7218-4869-994bcecd96d9</td>\n",
       "      <td>[{'system': 'http://hl7.org/fhir/sid/cvx', 'co...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>16466 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 patient_id  \\\n",
       "0      71cb26ff-d626-8884-c6e8-9c82e9fe850d   \n",
       "1      cfd691b9-97ef-fb65-b6b3-5333822005b3   \n",
       "2      71cb26ff-d626-8884-c6e8-9c82e9fe850d   \n",
       "3      cfd691b9-97ef-fb65-b6b3-5333822005b3   \n",
       "4      71cb26ff-d626-8884-c6e8-9c82e9fe850d   \n",
       "...                                     ...   \n",
       "16461  a85c904c-19e1-7218-4869-994bcecd96d9   \n",
       "16462  a85c904c-19e1-7218-4869-994bcecd96d9   \n",
       "16463  a85c904c-19e1-7218-4869-994bcecd96d9   \n",
       "16464  a85c904c-19e1-7218-4869-994bcecd96d9   \n",
       "16465  a85c904c-19e1-7218-4869-994bcecd96d9   \n",
       "\n",
       "                                                   codes  \n",
       "0      [{'system': 'http://hl7.org/fhir/sid/cvx', 'co...  \n",
       "1      [{'system': 'http://hl7.org/fhir/sid/cvx', 'co...  \n",
       "2      [{'system': 'http://hl7.org/fhir/sid/cvx', 'co...  \n",
       "3      [{'system': 'http://hl7.org/fhir/sid/cvx', 'co...  \n",
       "4      [{'system': 'http://hl7.org/fhir/sid/cvx', 'co...  \n",
       "...                                                  ...  \n",
       "16461  [{'system': 'http://hl7.org/fhir/sid/cvx', 'co...  \n",
       "16462  [{'system': 'http://hl7.org/fhir/sid/cvx', 'co...  \n",
       "16463  [{'system': 'http://hl7.org/fhir/sid/cvx', 'co...  \n",
       "16464  [{'system': 'http://hl7.org/fhir/sid/cvx', 'co...  \n",
       "16465  [{'system': 'http://hl7.org/fhir/sid/cvx', 'co...  \n",
       "\n",
       "[16466 rows x 2 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "immunization_data = [\n",
    "    {\n",
    "        'patient_id': immunization['patient']['reference'].split('/')[-1],\n",
    "        'codes': immunization['vaccineCode']['coding']\n",
    "    } for immunization in resource_data['Immunization']\n",
    "]\n",
    "immunization_df = pd.DataFrame.from_records(data=immunization_data)\n",
    "immunization_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "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>patient_id</th>\n",
       "      <th>codes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4c56ded6-0af3-a047-3960-32364fcf467e</td>\n",
       "      <td>[{'system': 'http://snomed.info/sct', 'code': ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6b8e2a56-af0b-9081-78bf-3eb0de39271d</td>\n",
       "      <td>[{'system': 'http://snomed.info/sct', 'code': ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>13866099-28d7-1249-3e44-ecb490d40fef</td>\n",
       "      <td>[{'system': 'http://snomed.info/sct', 'code': ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>13866099-28d7-1249-3e44-ecb490d40fef</td>\n",
       "      <td>[{'system': 'http://snomed.info/sct', 'code': ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>53cad937-1b82-880f-50bc-2c76f3742b8f</td>\n",
       "      <td>[{'system': 'http://snomed.info/sct', 'code': ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37096</th>\n",
       "      <td>a85c904c-19e1-7218-4869-994bcecd96d9</td>\n",
       "      <td>[{'system': 'http://snomed.info/sct', 'code': ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37097</th>\n",
       "      <td>a85c904c-19e1-7218-4869-994bcecd96d9</td>\n",
       "      <td>[{'system': 'http://snomed.info/sct', 'code': ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37098</th>\n",
       "      <td>a85c904c-19e1-7218-4869-994bcecd96d9</td>\n",
       "      <td>[{'system': 'http://snomed.info/sct', 'code': ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37099</th>\n",
       "      <td>a85c904c-19e1-7218-4869-994bcecd96d9</td>\n",
       "      <td>[{'system': 'http://snomed.info/sct', 'code': ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37100</th>\n",
       "      <td>a85c904c-19e1-7218-4869-994bcecd96d9</td>\n",
       "      <td>[{'system': 'http://snomed.info/sct', 'code': ...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>37101 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 patient_id  \\\n",
       "0      4c56ded6-0af3-a047-3960-32364fcf467e   \n",
       "1      6b8e2a56-af0b-9081-78bf-3eb0de39271d   \n",
       "2      13866099-28d7-1249-3e44-ecb490d40fef   \n",
       "3      13866099-28d7-1249-3e44-ecb490d40fef   \n",
       "4      53cad937-1b82-880f-50bc-2c76f3742b8f   \n",
       "...                                     ...   \n",
       "37096  a85c904c-19e1-7218-4869-994bcecd96d9   \n",
       "37097  a85c904c-19e1-7218-4869-994bcecd96d9   \n",
       "37098  a85c904c-19e1-7218-4869-994bcecd96d9   \n",
       "37099  a85c904c-19e1-7218-4869-994bcecd96d9   \n",
       "37100  a85c904c-19e1-7218-4869-994bcecd96d9   \n",
       "\n",
       "                                                   codes  \n",
       "0      [{'system': 'http://snomed.info/sct', 'code': ...  \n",
       "1      [{'system': 'http://snomed.info/sct', 'code': ...  \n",
       "2      [{'system': 'http://snomed.info/sct', 'code': ...  \n",
       "3      [{'system': 'http://snomed.info/sct', 'code': ...  \n",
       "4      [{'system': 'http://snomed.info/sct', 'code': ...  \n",
       "...                                                  ...  \n",
       "37096  [{'system': 'http://snomed.info/sct', 'code': ...  \n",
       "37097  [{'system': 'http://snomed.info/sct', 'code': ...  \n",
       "37098  [{'system': 'http://snomed.info/sct', 'code': ...  \n",
       "37099  [{'system': 'http://snomed.info/sct', 'code': ...  \n",
       "37100  [{'system': 'http://snomed.info/sct', 'code': ...  \n",
       "\n",
       "[37101 rows x 2 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "condition_data = [\n",
    "    {\n",
    "        'patient_id': condition['subject']['reference'].split('/')[-1],\n",
    "        'codes': condition['code']['coding']\n",
    "    } for condition in resource_data['Condition']\n",
    "]\n",
    "condition_df = pd.DataFrame.from_records(data=condition_data)\n",
    "condition_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "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>patient_id</th>\n",
       "      <th>code</th>\n",
       "      <th>value_quantity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>71cb26ff-d626-8884-c6e8-9c82e9fe850d</td>\n",
       "      <td>{'system': 'http://loinc.org', 'code': '8302-2...</td>\n",
       "      <td>{'value': 52.7, 'unit': 'cm', 'system': 'http:...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>71cb26ff-d626-8884-c6e8-9c82e9fe850d</td>\n",
       "      <td>{'system': 'http://loinc.org', 'code': '72514-...</td>\n",
       "      <td>{'value': 1, 'unit': '{score}', 'system': 'htt...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>71cb26ff-d626-8884-c6e8-9c82e9fe850d</td>\n",
       "      <td>{'system': 'http://loinc.org', 'code': '29463-...</td>\n",
       "      <td>{'value': 4.2, 'unit': 'kg', 'system': 'http:/...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>71cb26ff-d626-8884-c6e8-9c82e9fe850d</td>\n",
       "      <td>{'system': 'http://loinc.org', 'code': '77606-...</td>\n",
       "      <td>{'value': 49.773, 'unit': '%', 'system': 'http...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>71cb26ff-d626-8884-c6e8-9c82e9fe850d</td>\n",
       "      <td>{'system': 'http://loinc.org', 'code': '9843-4...</td>\n",
       "      <td>{'value': 35.4, 'unit': 'cm', 'system': 'http:...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>278470</th>\n",
       "      <td>a85c904c-19e1-7218-4869-994bcecd96d9</td>\n",
       "      <td>{'system': 'http://loinc.org', 'code': '72166-...</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>278471</th>\n",
       "      <td>a85c904c-19e1-7218-4869-994bcecd96d9</td>\n",
       "      <td>{'system': 'http://loinc.org', 'code': '93025-...</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>278472</th>\n",
       "      <td>a85c904c-19e1-7218-4869-994bcecd96d9</td>\n",
       "      <td>{'system': 'http://loinc.org', 'code': '59460-...</td>\n",
       "      <td>{'value': 80, 'unit': '{#}', 'system': 'http:/...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>278473</th>\n",
       "      <td>a85c904c-19e1-7218-4869-994bcecd96d9</td>\n",
       "      <td>{'system': 'http://loinc.org', 'code': '59461-...</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>278474</th>\n",
       "      <td>a85c904c-19e1-7218-4869-994bcecd96d9</td>\n",
       "      <td>{'system': 'http://loinc.org', 'code': '55758-...</td>\n",
       "      <td>{'value': 0, 'unit': '{score}', 'system': 'htt...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>280360 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                  patient_id  \\\n",
       "0       71cb26ff-d626-8884-c6e8-9c82e9fe850d   \n",
       "1       71cb26ff-d626-8884-c6e8-9c82e9fe850d   \n",
       "2       71cb26ff-d626-8884-c6e8-9c82e9fe850d   \n",
       "3       71cb26ff-d626-8884-c6e8-9c82e9fe850d   \n",
       "4       71cb26ff-d626-8884-c6e8-9c82e9fe850d   \n",
       "...                                      ...   \n",
       "278470  a85c904c-19e1-7218-4869-994bcecd96d9   \n",
       "278471  a85c904c-19e1-7218-4869-994bcecd96d9   \n",
       "278472  a85c904c-19e1-7218-4869-994bcecd96d9   \n",
       "278473  a85c904c-19e1-7218-4869-994bcecd96d9   \n",
       "278474  a85c904c-19e1-7218-4869-994bcecd96d9   \n",
       "\n",
       "                                                     code  \\\n",
       "0       {'system': 'http://loinc.org', 'code': '8302-2...   \n",
       "1       {'system': 'http://loinc.org', 'code': '72514-...   \n",
       "2       {'system': 'http://loinc.org', 'code': '29463-...   \n",
       "3       {'system': 'http://loinc.org', 'code': '77606-...   \n",
       "4       {'system': 'http://loinc.org', 'code': '9843-4...   \n",
       "...                                                   ...   \n",
       "278470  {'system': 'http://loinc.org', 'code': '72166-...   \n",
       "278471  {'system': 'http://loinc.org', 'code': '93025-...   \n",
       "278472  {'system': 'http://loinc.org', 'code': '59460-...   \n",
       "278473  {'system': 'http://loinc.org', 'code': '59461-...   \n",
       "278474  {'system': 'http://loinc.org', 'code': '55758-...   \n",
       "\n",
       "                                           value_quantity  \n",
       "0       {'value': 52.7, 'unit': 'cm', 'system': 'http:...  \n",
       "1       {'value': 1, 'unit': '{score}', 'system': 'htt...  \n",
       "2       {'value': 4.2, 'unit': 'kg', 'system': 'http:/...  \n",
       "3       {'value': 49.773, 'unit': '%', 'system': 'http...  \n",
       "4       {'value': 35.4, 'unit': 'cm', 'system': 'http:...  \n",
       "...                                                   ...  \n",
       "278470                                               None  \n",
       "278471                                               None  \n",
       "278472  {'value': 80, 'unit': '{#}', 'system': 'http:/...  \n",
       "278473                                               None  \n",
       "278474  {'value': 0, 'unit': '{score}', 'system': 'htt...  \n",
       "\n",
       "[280360 rows x 3 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "observation_data = [\n",
    "    {\n",
    "        'patient_id': observation['subject']['reference'].split('/')[-1],\n",
    "        'code': observation['code']['coding'],\n",
    "        'value_quantity': observation.get('valueQuantity')\n",
    "    } for observation in resource_data['Observation']\n",
    "]\n",
    "observation_df = pd.DataFrame.from_records(data=observation_data)\n",
    "observation_df = observation_df.explode('code')\n",
    "observation_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "## Terminology operations\n",
    "\n",
    "Next we need to create a bunch of derived columns within our dataframes that contain the results of the required terminology queries against coded fields.\n",
    "\n",
    "This will give us the necessary data to perform the following groupings:\n",
    "\n",
    "* COVID-19 vaccine administered\n",
    "* Chronic kidney disease\n",
    "* Heart disease\n",
    "* BMI > 30"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "Create a function for testing membership of value sets using the [validate-code operation](https://hl7.org/fhir/valueset-operation-validate-code.html) on the terminology server. This uses memoization to remove redundant calls for duplicate codings."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "member_of_memo = {}\n",
    "\n",
    "\n",
    "def any_member_of(url, codings):\n",
    "    return any(map(lambda coding: member_of(url, coding), codings))\n",
    "\n",
    "\n",
    "def member_of(url, coding):\n",
    "    key = (url, coding['system'], coding['code'])\n",
    "    if key not in member_of_memo:\n",
    "        params = {\n",
    "            'url': url,\n",
    "            'system': coding['system'],\n",
    "            'code': coding['code']\n",
    "        }\n",
    "        validate_code_url = f'/fhir/ValueSet/$validate-code?{urlencode(params)}'\n",
    "        tx_conn.request('GET', validate_code_url)\n",
    "        response = tx_conn.getresponse()\n",
    "        json_string = response.read()\n",
    "        parsed = json.loads(json_string)\n",
    "        member_of_memo[key] = [\n",
    "                                  parameter['valueBoolean']\n",
    "                                  for parameter in parsed['parameter']\n",
    "                                  if parameter['name'] == 'result'\n",
    "                              ][0] == True\n",
    "    return member_of_memo[key]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "Test the membership of each immunization code against our COVID-19 vaccine value set."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "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>patient_id</th>\n",
       "      <th>codes</th>\n",
       "      <th>covid_19</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>71cb26ff-d626-8884-c6e8-9c82e9fe850d</td>\n",
       "      <td>[{'system': 'http://hl7.org/fhir/sid/cvx', 'co...</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>cfd691b9-97ef-fb65-b6b3-5333822005b3</td>\n",
       "      <td>[{'system': 'http://hl7.org/fhir/sid/cvx', 'co...</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>71cb26ff-d626-8884-c6e8-9c82e9fe850d</td>\n",
       "      <td>[{'system': 'http://hl7.org/fhir/sid/cvx', 'co...</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>cfd691b9-97ef-fb65-b6b3-5333822005b3</td>\n",
       "      <td>[{'system': 'http://hl7.org/fhir/sid/cvx', 'co...</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>71cb26ff-d626-8884-c6e8-9c82e9fe850d</td>\n",
       "      <td>[{'system': 'http://hl7.org/fhir/sid/cvx', 'co...</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16461</th>\n",
       "      <td>a85c904c-19e1-7218-4869-994bcecd96d9</td>\n",
       "      <td>[{'system': 'http://hl7.org/fhir/sid/cvx', 'co...</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16462</th>\n",
       "      <td>a85c904c-19e1-7218-4869-994bcecd96d9</td>\n",
       "      <td>[{'system': 'http://hl7.org/fhir/sid/cvx', 'co...</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16463</th>\n",
       "      <td>a85c904c-19e1-7218-4869-994bcecd96d9</td>\n",
       "      <td>[{'system': 'http://hl7.org/fhir/sid/cvx', 'co...</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16464</th>\n",
       "      <td>a85c904c-19e1-7218-4869-994bcecd96d9</td>\n",
       "      <td>[{'system': 'http://hl7.org/fhir/sid/cvx', 'co...</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16465</th>\n",
       "      <td>a85c904c-19e1-7218-4869-994bcecd96d9</td>\n",
       "      <td>[{'system': 'http://hl7.org/fhir/sid/cvx', 'co...</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>16466 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 patient_id  \\\n",
       "0      71cb26ff-d626-8884-c6e8-9c82e9fe850d   \n",
       "1      cfd691b9-97ef-fb65-b6b3-5333822005b3   \n",
       "2      71cb26ff-d626-8884-c6e8-9c82e9fe850d   \n",
       "3      cfd691b9-97ef-fb65-b6b3-5333822005b3   \n",
       "4      71cb26ff-d626-8884-c6e8-9c82e9fe850d   \n",
       "...                                     ...   \n",
       "16461  a85c904c-19e1-7218-4869-994bcecd96d9   \n",
       "16462  a85c904c-19e1-7218-4869-994bcecd96d9   \n",
       "16463  a85c904c-19e1-7218-4869-994bcecd96d9   \n",
       "16464  a85c904c-19e1-7218-4869-994bcecd96d9   \n",
       "16465  a85c904c-19e1-7218-4869-994bcecd96d9   \n",
       "\n",
       "                                                   codes  covid_19  \n",
       "0      [{'system': 'http://hl7.org/fhir/sid/cvx', 'co...     False  \n",
       "1      [{'system': 'http://hl7.org/fhir/sid/cvx', 'co...     False  \n",
       "2      [{'system': 'http://hl7.org/fhir/sid/cvx', 'co...     False  \n",
       "3      [{'system': 'http://hl7.org/fhir/sid/cvx', 'co...     False  \n",
       "4      [{'system': 'http://hl7.org/fhir/sid/cvx', 'co...     False  \n",
       "...                                                  ...       ...  \n",
       "16461  [{'system': 'http://hl7.org/fhir/sid/cvx', 'co...     False  \n",
       "16462  [{'system': 'http://hl7.org/fhir/sid/cvx', 'co...     False  \n",
       "16463  [{'system': 'http://hl7.org/fhir/sid/cvx', 'co...     False  \n",
       "16464  [{'system': 'http://hl7.org/fhir/sid/cvx', 'co...     False  \n",
       "16465  [{'system': 'http://hl7.org/fhir/sid/cvx', 'co...     False  \n",
       "\n",
       "[16466 rows x 3 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "immunization_df['covid_19'] = list(map(\n",
    "    lambda codes: any_member_of('https://aehrc.csiro.au/fhir/ValueSet/covid-19-vaccines', codes),\n",
    "    immunization_df['codes']\n",
    "))\n",
    "immunization_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "Create a function for testing subsumption using the [subsumes operation](https://hl7.org/fhir/codesystem-operation-subsumes.html) on the terminology server. This uses memoization to remove redundant calls for duplicate codings."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "subsumes_memo = {}\n",
    "\n",
    "\n",
    "def any_subsumes(system, code_a, codes):\n",
    "    return any(map(lambda code_b: subsumes(system, code_a, code_b), codes))\n",
    "\n",
    "\n",
    "def subsumes(system, code_a, code_b):\n",
    "    key = (system, code_a, code_b)\n",
    "    if key not in subsumes_memo:\n",
    "        params = {\n",
    "            'system': system,\n",
    "            'codeA': code_a,\n",
    "            'codeB': code_b\n",
    "        }\n",
    "        subsumes_url = f'/fhir/CodeSystem/$subsumes?{urlencode(params)}'\n",
    "        tx_conn.request('GET', subsumes_url)\n",
    "        response = tx_conn.getresponse()\n",
    "        if response.status != 200:\n",
    "            return False\n",
    "        json_string = response.read()\n",
    "        parsed = json.loads(json_string)\n",
    "        subsumes_memo[key] = [\n",
    "                                 parameter['valueCode']\n",
    "                                 for parameter in parsed['parameter']\n",
    "                                 if parameter['name'] == 'outcome'\n",
    "                             ][0] == 'subsumes'\n",
    "    return subsumes_memo[key]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "Test whether each condition code is subsumed by `709044004 |Chronic kidney disease|`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "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>patient_id</th>\n",
       "      <th>codes</th>\n",
       "      <th>ckd</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4c56ded6-0af3-a047-3960-32364fcf467e</td>\n",
       "      <td>[{'system': 'http://snomed.info/sct', 'code': ...</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6b8e2a56-af0b-9081-78bf-3eb0de39271d</td>\n",
       "      <td>[{'system': 'http://snomed.info/sct', 'code': ...</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>13866099-28d7-1249-3e44-ecb490d40fef</td>\n",
       "      <td>[{'system': 'http://snomed.info/sct', 'code': ...</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>13866099-28d7-1249-3e44-ecb490d40fef</td>\n",
       "      <td>[{'system': 'http://snomed.info/sct', 'code': ...</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>53cad937-1b82-880f-50bc-2c76f3742b8f</td>\n",
       "      <td>[{'system': 'http://snomed.info/sct', 'code': ...</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37096</th>\n",
       "      <td>a85c904c-19e1-7218-4869-994bcecd96d9</td>\n",
       "      <td>[{'system': 'http://snomed.info/sct', 'code': ...</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37097</th>\n",
       "      <td>a85c904c-19e1-7218-4869-994bcecd96d9</td>\n",
       "      <td>[{'system': 'http://snomed.info/sct', 'code': ...</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37098</th>\n",
       "      <td>a85c904c-19e1-7218-4869-994bcecd96d9</td>\n",
       "      <td>[{'system': 'http://snomed.info/sct', 'code': ...</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37099</th>\n",
       "      <td>a85c904c-19e1-7218-4869-994bcecd96d9</td>\n",
       "      <td>[{'system': 'http://snomed.info/sct', 'code': ...</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37100</th>\n",
       "      <td>a85c904c-19e1-7218-4869-994bcecd96d9</td>\n",
       "      <td>[{'system': 'http://snomed.info/sct', 'code': ...</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>37101 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 patient_id  \\\n",
       "0      4c56ded6-0af3-a047-3960-32364fcf467e   \n",
       "1      6b8e2a56-af0b-9081-78bf-3eb0de39271d   \n",
       "2      13866099-28d7-1249-3e44-ecb490d40fef   \n",
       "3      13866099-28d7-1249-3e44-ecb490d40fef   \n",
       "4      53cad937-1b82-880f-50bc-2c76f3742b8f   \n",
       "...                                     ...   \n",
       "37096  a85c904c-19e1-7218-4869-994bcecd96d9   \n",
       "37097  a85c904c-19e1-7218-4869-994bcecd96d9   \n",
       "37098  a85c904c-19e1-7218-4869-994bcecd96d9   \n",
       "37099  a85c904c-19e1-7218-4869-994bcecd96d9   \n",
       "37100  a85c904c-19e1-7218-4869-994bcecd96d9   \n",
       "\n",
       "                                                   codes    ckd  \n",
       "0      [{'system': 'http://snomed.info/sct', 'code': ...  False  \n",
       "1      [{'system': 'http://snomed.info/sct', 'code': ...  False  \n",
       "2      [{'system': 'http://snomed.info/sct', 'code': ...  False  \n",
       "3      [{'system': 'http://snomed.info/sct', 'code': ...  False  \n",
       "4      [{'system': 'http://snomed.info/sct', 'code': ...  False  \n",
       "...                                                  ...    ...  \n",
       "37096  [{'system': 'http://snomed.info/sct', 'code': ...  False  \n",
       "37097  [{'system': 'http://snomed.info/sct', 'code': ...  False  \n",
       "37098  [{'system': 'http://snomed.info/sct', 'code': ...  False  \n",
       "37099  [{'system': 'http://snomed.info/sct', 'code': ...  False  \n",
       "37100  [{'system': 'http://snomed.info/sct', 'code': ...  False  \n",
       "\n",
       "[37101 rows x 3 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ckd_df = condition_df.copy()\n",
    "ckd_df['ckd'] = list(map(\n",
    "    lambda codes: any_subsumes('http://snomed.info/sct', '709044004', codes),\n",
    "    list(map(lambda codings: [\n",
    "        coding.get('code')\n",
    "        for coding in codings\n",
    "        if coding.get('system') == 'http://snomed.info/sct'\n",
    "    ], condition_df['codes']))\n",
    "))\n",
    "ckd_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "Test whether each condition code is a type of heart disease, using an [implicit value set](https://hl7.org/fhir/r4/snomedct.html#implicit) defined by the following ECL expression:\n",
    "\n",
    "```\n",
    "<< 49601007|Cardiovascular disease| :\n",
    "<< 363698007|Finding site| = << 80891009|Structure of heart|\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "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>patient_id</th>\n",
       "      <th>codes</th>\n",
       "      <th>heart_disease</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4c56ded6-0af3-a047-3960-32364fcf467e</td>\n",
       "      <td>[{'system': 'http://snomed.info/sct', 'code': ...</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6b8e2a56-af0b-9081-78bf-3eb0de39271d</td>\n",
       "      <td>[{'system': 'http://snomed.info/sct', 'code': ...</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>13866099-28d7-1249-3e44-ecb490d40fef</td>\n",
       "      <td>[{'system': 'http://snomed.info/sct', 'code': ...</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>13866099-28d7-1249-3e44-ecb490d40fef</td>\n",
       "      <td>[{'system': 'http://snomed.info/sct', 'code': ...</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>53cad937-1b82-880f-50bc-2c76f3742b8f</td>\n",
       "      <td>[{'system': 'http://snomed.info/sct', 'code': ...</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37096</th>\n",
       "      <td>a85c904c-19e1-7218-4869-994bcecd96d9</td>\n",
       "      <td>[{'system': 'http://snomed.info/sct', 'code': ...</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37097</th>\n",
       "      <td>a85c904c-19e1-7218-4869-994bcecd96d9</td>\n",
       "      <td>[{'system': 'http://snomed.info/sct', 'code': ...</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37098</th>\n",
       "      <td>a85c904c-19e1-7218-4869-994bcecd96d9</td>\n",
       "      <td>[{'system': 'http://snomed.info/sct', 'code': ...</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37099</th>\n",
       "      <td>a85c904c-19e1-7218-4869-994bcecd96d9</td>\n",
       "      <td>[{'system': 'http://snomed.info/sct', 'code': ...</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37100</th>\n",
       "      <td>a85c904c-19e1-7218-4869-994bcecd96d9</td>\n",
       "      <td>[{'system': 'http://snomed.info/sct', 'code': ...</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>37101 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 patient_id  \\\n",
       "0      4c56ded6-0af3-a047-3960-32364fcf467e   \n",
       "1      6b8e2a56-af0b-9081-78bf-3eb0de39271d   \n",
       "2      13866099-28d7-1249-3e44-ecb490d40fef   \n",
       "3      13866099-28d7-1249-3e44-ecb490d40fef   \n",
       "4      53cad937-1b82-880f-50bc-2c76f3742b8f   \n",
       "...                                     ...   \n",
       "37096  a85c904c-19e1-7218-4869-994bcecd96d9   \n",
       "37097  a85c904c-19e1-7218-4869-994bcecd96d9   \n",
       "37098  a85c904c-19e1-7218-4869-994bcecd96d9   \n",
       "37099  a85c904c-19e1-7218-4869-994bcecd96d9   \n",
       "37100  a85c904c-19e1-7218-4869-994bcecd96d9   \n",
       "\n",
       "                                                   codes  heart_disease  \n",
       "0      [{'system': 'http://snomed.info/sct', 'code': ...          False  \n",
       "1      [{'system': 'http://snomed.info/sct', 'code': ...          False  \n",
       "2      [{'system': 'http://snomed.info/sct', 'code': ...          False  \n",
       "3      [{'system': 'http://snomed.info/sct', 'code': ...          False  \n",
       "4      [{'system': 'http://snomed.info/sct', 'code': ...          False  \n",
       "...                                                  ...            ...  \n",
       "37096  [{'system': 'http://snomed.info/sct', 'code': ...          False  \n",
       "37097  [{'system': 'http://snomed.info/sct', 'code': ...          False  \n",
       "37098  [{'system': 'http://snomed.info/sct', 'code': ...          False  \n",
       "37099  [{'system': 'http://snomed.info/sct', 'code': ...          False  \n",
       "37100  [{'system': 'http://snomed.info/sct', 'code': ...          False  \n",
       "\n",
       "[37101 rows x 3 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "heart_disease_value_set_url = 'http://snomed.info/sct?fhir_vs=ecl/%3C%3C%2049601007%20%3A%20%3C%3C%20363698007%20''%3D%20%3C%3C%2080891009%20'\n",
    "\n",
    "heart_disease_df = condition_df.copy()\n",
    "heart_disease_df['heart_disease'] = list(map(\n",
    "    lambda codes: any_member_of(heart_disease_value_set_url, codes),\n",
    "    condition_df['codes']\n",
    "))\n",
    "heart_disease_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "Test whether each observation is for BMI, and whether the value is greater than 30 kg/m<sup>2</sup>."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "def is_bmi_over_30(code_and_quantity):\n",
    "    code = code_and_quantity[0]\n",
    "    quantity = code_and_quantity[1]\n",
    "    if quantity is None:\n",
    "        return False\n",
    "    if code['system'] != 'http://loinc.org' or code['code'] != '39156-5':\n",
    "        return False\n",
    "    if quantity['system'] != 'http://unitsofmeasure.org' or quantity['code'] != 'kg/m2' or quantity[\n",
    "        'value'] is None:\n",
    "        return False\n",
    "    return quantity['value'] > 30"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "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>patient_id</th>\n",
       "      <th>code</th>\n",
       "      <th>value_quantity</th>\n",
       "      <th>bmi_over_30</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>71cb26ff-d626-8884-c6e8-9c82e9fe850d</td>\n",
       "      <td>{'system': 'http://loinc.org', 'code': '8302-2...</td>\n",
       "      <td>{'value': 52.7, 'unit': 'cm', 'system': 'http:...</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>71cb26ff-d626-8884-c6e8-9c82e9fe850d</td>\n",
       "      <td>{'system': 'http://loinc.org', 'code': '72514-...</td>\n",
       "      <td>{'value': 1, 'unit': '{score}', 'system': 'htt...</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>71cb26ff-d626-8884-c6e8-9c82e9fe850d</td>\n",
       "      <td>{'system': 'http://loinc.org', 'code': '29463-...</td>\n",
       "      <td>{'value': 4.2, 'unit': 'kg', 'system': 'http:/...</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>71cb26ff-d626-8884-c6e8-9c82e9fe850d</td>\n",
       "      <td>{'system': 'http://loinc.org', 'code': '77606-...</td>\n",
       "      <td>{'value': 49.773, 'unit': '%', 'system': 'http...</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>71cb26ff-d626-8884-c6e8-9c82e9fe850d</td>\n",
       "      <td>{'system': 'http://loinc.org', 'code': '9843-4...</td>\n",
       "      <td>{'value': 35.4, 'unit': 'cm', 'system': 'http:...</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>278470</th>\n",
       "      <td>a85c904c-19e1-7218-4869-994bcecd96d9</td>\n",
       "      <td>{'system': 'http://loinc.org', 'code': '72166-...</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>278471</th>\n",
       "      <td>a85c904c-19e1-7218-4869-994bcecd96d9</td>\n",
       "      <td>{'system': 'http://loinc.org', 'code': '93025-...</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>278472</th>\n",
       "      <td>a85c904c-19e1-7218-4869-994bcecd96d9</td>\n",
       "      <td>{'system': 'http://loinc.org', 'code': '59460-...</td>\n",
       "      <td>{'value': 80, 'unit': '{#}', 'system': 'http:/...</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>278473</th>\n",
       "      <td>a85c904c-19e1-7218-4869-994bcecd96d9</td>\n",
       "      <td>{'system': 'http://loinc.org', 'code': '59461-...</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>278474</th>\n",
       "      <td>a85c904c-19e1-7218-4869-994bcecd96d9</td>\n",
       "      <td>{'system': 'http://loinc.org', 'code': '55758-...</td>\n",
       "      <td>{'value': 0, 'unit': '{score}', 'system': 'htt...</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>280360 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                  patient_id  \\\n",
       "0       71cb26ff-d626-8884-c6e8-9c82e9fe850d   \n",
       "1       71cb26ff-d626-8884-c6e8-9c82e9fe850d   \n",
       "2       71cb26ff-d626-8884-c6e8-9c82e9fe850d   \n",
       "3       71cb26ff-d626-8884-c6e8-9c82e9fe850d   \n",
       "4       71cb26ff-d626-8884-c6e8-9c82e9fe850d   \n",
       "...                                      ...   \n",
       "278470  a85c904c-19e1-7218-4869-994bcecd96d9   \n",
       "278471  a85c904c-19e1-7218-4869-994bcecd96d9   \n",
       "278472  a85c904c-19e1-7218-4869-994bcecd96d9   \n",
       "278473  a85c904c-19e1-7218-4869-994bcecd96d9   \n",
       "278474  a85c904c-19e1-7218-4869-994bcecd96d9   \n",
       "\n",
       "                                                     code  \\\n",
       "0       {'system': 'http://loinc.org', 'code': '8302-2...   \n",
       "1       {'system': 'http://loinc.org', 'code': '72514-...   \n",
       "2       {'system': 'http://loinc.org', 'code': '29463-...   \n",
       "3       {'system': 'http://loinc.org', 'code': '77606-...   \n",
       "4       {'system': 'http://loinc.org', 'code': '9843-4...   \n",
       "...                                                   ...   \n",
       "278470  {'system': 'http://loinc.org', 'code': '72166-...   \n",
       "278471  {'system': 'http://loinc.org', 'code': '93025-...   \n",
       "278472  {'system': 'http://loinc.org', 'code': '59460-...   \n",
       "278473  {'system': 'http://loinc.org', 'code': '59461-...   \n",
       "278474  {'system': 'http://loinc.org', 'code': '55758-...   \n",
       "\n",
       "                                           value_quantity  bmi_over_30  \n",
       "0       {'value': 52.7, 'unit': 'cm', 'system': 'http:...        False  \n",
       "1       {'value': 1, 'unit': '{score}', 'system': 'htt...        False  \n",
       "2       {'value': 4.2, 'unit': 'kg', 'system': 'http:/...        False  \n",
       "3       {'value': 49.773, 'unit': '%', 'system': 'http...        False  \n",
       "4       {'value': 35.4, 'unit': 'cm', 'system': 'http:...        False  \n",
       "...                                                   ...          ...  \n",
       "278470                                               None        False  \n",
       "278471                                               None        False  \n",
       "278472  {'value': 80, 'unit': '{#}', 'system': 'http:/...        False  \n",
       "278473                                               None        False  \n",
       "278474  {'value': 0, 'unit': '{score}', 'system': 'htt...        False  \n",
       "\n",
       "[280360 rows x 4 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "codes_and_quantities = list(observation_df[['code', 'value_quantity']].itertuples(index=False))\n",
    "observation_df['bmi_over_30'] = list(map(is_bmi_over_30, codes_and_quantities))\n",
    "observation_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "## Filter and join dataframes\n",
    "\n",
    "Next we need to join the dataframes together, so that we can link patients to immunizations, conditions and observations for which they are the subject."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "First let's filter the patients according to our cohort specification (ages 18-60)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "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>name</th>\n",
       "      <th>birth_date</th>\n",
       "      <th>telephone</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>53cad937-1b82-880f-50bc-2c76f3742b8f</td>\n",
       "      <td>Mueller846, Pei116</td>\n",
       "      <td>1987-10-28</td>\n",
       "      <td>555-819-4627</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>13866099-28d7-1249-3e44-ecb490d40fef</td>\n",
       "      <td>Buckridge80, Dominique369</td>\n",
       "      <td>1980-09-14</td>\n",
       "      <td>555-181-6346</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6b8e2a56-af0b-9081-78bf-3eb0de39271d</td>\n",
       "      <td>Skiles927, Mila257</td>\n",
       "      <td>1993-12-11</td>\n",
       "      <td>555-308-4688</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>cfd691b9-97ef-fb65-b6b3-5333822005b3</td>\n",
       "      <td>McDermott739, Brady998</td>\n",
       "      <td>1995-11-08</td>\n",
       "      <td>555-161-8715</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>72df0f76-2758-fac4-67cd-de33c4a2c95e</td>\n",
       "      <td>Reynolds644, Devin82</td>\n",
       "      <td>1962-10-07</td>\n",
       "      <td>555-214-5571</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1144</th>\n",
       "      <td>5e033ebf-3829-b2d6-0621-5cfa91ee0342</td>\n",
       "      <td>Parisian75, Philomena945</td>\n",
       "      <td>1985-03-19</td>\n",
       "      <td>555-482-5382</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1145</th>\n",
       "      <td>5dbcf49d-64d0-0326-012e-4236366d81c9</td>\n",
       "      <td>Mojica921, Adela471</td>\n",
       "      <td>1982-10-28</td>\n",
       "      <td>555-514-9877</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1146</th>\n",
       "      <td>ff29bfd9-6a2c-5b21-0ece-fe592c676ad2</td>\n",
       "      <td>Torp761, Rod343</td>\n",
       "      <td>2002-10-03</td>\n",
       "      <td>555-694-4635</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1147</th>\n",
       "      <td>b26e4e5e-0296-5256-d53a-1976700b0a9e</td>\n",
       "      <td>Kuhic920, Gabriele201</td>\n",
       "      <td>1968-08-02</td>\n",
       "      <td>555-173-4696</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1148</th>\n",
       "      <td>666ec565-34dc-b06f-16b1-e35560492621</td>\n",
       "      <td>Samaniego616, Raúl937</td>\n",
       "      <td>1981-11-16</td>\n",
       "      <td>555-515-7316</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>647 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                        id                       name  \\\n",
       "0     53cad937-1b82-880f-50bc-2c76f3742b8f         Mueller846, Pei116   \n",
       "1     13866099-28d7-1249-3e44-ecb490d40fef  Buckridge80, Dominique369   \n",
       "2     6b8e2a56-af0b-9081-78bf-3eb0de39271d         Skiles927, Mila257   \n",
       "4     cfd691b9-97ef-fb65-b6b3-5333822005b3     McDermott739, Brady998   \n",
       "5     72df0f76-2758-fac4-67cd-de33c4a2c95e       Reynolds644, Devin82   \n",
       "...                                    ...                        ...   \n",
       "1144  5e033ebf-3829-b2d6-0621-5cfa91ee0342   Parisian75, Philomena945   \n",
       "1145  5dbcf49d-64d0-0326-012e-4236366d81c9        Mojica921, Adela471   \n",
       "1146  ff29bfd9-6a2c-5b21-0ece-fe592c676ad2            Torp761, Rod343   \n",
       "1147  b26e4e5e-0296-5256-d53a-1976700b0a9e      Kuhic920, Gabriele201   \n",
       "1148  666ec565-34dc-b06f-16b1-e35560492621      Samaniego616, Raúl937   \n",
       "\n",
       "     birth_date     telephone  \n",
       "0    1987-10-28  555-819-4627  \n",
       "1    1980-09-14  555-181-6346  \n",
       "2    1993-12-11  555-308-4688  \n",
       "4    1995-11-08  555-161-8715  \n",
       "5    1962-10-07  555-214-5571  \n",
       "...         ...           ...  \n",
       "1144 1985-03-19  555-482-5382  \n",
       "1145 1982-10-28  555-514-9877  \n",
       "1146 2002-10-03  555-694-4635  \n",
       "1147 1968-08-02  555-173-4696  \n",
       "1148 1981-11-16  555-515-7316  \n",
       "\n",
       "[647 rows x 4 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "patient_df['birth_date'] = pd.to_datetime(patient_df['birth_date'])\n",
    "filtered_patients = patient_df[\n",
    "    (patient_df['birth_date'] < pd.Timestamp(2004, 7, 30)) &\n",
    "    (patient_df['birth_date'] > pd.Timestamp(1962, 7, 30))\n",
    "    ]\n",
    "filtered_patients"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "Now we can join patients across to their immunizations, conditions and observations.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "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>covid_19</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00b8696d-15e5-4149-88fd-1dbc43ac2491</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>00ec181c-6ccc-853d-708e-39ef00380b74</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>019c1492-1d38-6884-d015-20b1a65035d5</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>02af773e-cd68-cca7-4c42-a3e9210dc1ec</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>03383a87-552f-463e-d5dd-310234b05fa5</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>642</th>\n",
       "      <td>ff1aa4b1-7d6a-6d17-3c36-ee15c4730863</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>643</th>\n",
       "      <td>ff29bfd9-6a2c-5b21-0ece-fe592c676ad2</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>644</th>\n",
       "      <td>ff851980-71c6-25a4-ba86-802b77ac3674</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>645</th>\n",
       "      <td>ff93fd71-8378-60f8-3597-03632b7148a0</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>646</th>\n",
       "      <td>ffa15a7b-1f27-30ef-37d5-58981e5ce3dc</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>647 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                       id  covid_19\n",
       "0    00b8696d-15e5-4149-88fd-1dbc43ac2491      True\n",
       "1    00ec181c-6ccc-853d-708e-39ef00380b74     False\n",
       "2    019c1492-1d38-6884-d015-20b1a65035d5      True\n",
       "3    02af773e-cd68-cca7-4c42-a3e9210dc1ec      True\n",
       "4    03383a87-552f-463e-d5dd-310234b05fa5      True\n",
       "..                                    ...       ...\n",
       "642  ff1aa4b1-7d6a-6d17-3c36-ee15c4730863     False\n",
       "643  ff29bfd9-6a2c-5b21-0ece-fe592c676ad2      True\n",
       "644  ff851980-71c6-25a4-ba86-802b77ac3674     False\n",
       "645  ff93fd71-8378-60f8-3597-03632b7148a0      True\n",
       "646  ffa15a7b-1f27-30ef-37d5-58981e5ce3dc     False\n",
       "\n",
       "[647 rows x 2 columns]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "patients_immunizations = pd.merge(filtered_patients, immunization_df, left_on='id',\n",
    "                                  right_on='patient_id')\n",
    "patients_immunizations = patients_immunizations.groupby(\n",
    "    'id').covid_19.max().to_frame().reset_index()\n",
    "patients_immunizations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "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>covid_19</th>\n",
       "      <th>ckd</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00b8696d-15e5-4149-88fd-1dbc43ac2491</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>00ec181c-6ccc-853d-708e-39ef00380b74</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>019c1492-1d38-6884-d015-20b1a65035d5</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>02af773e-cd68-cca7-4c42-a3e9210dc1ec</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>03383a87-552f-463e-d5dd-310234b05fa5</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>642</th>\n",
       "      <td>ff1aa4b1-7d6a-6d17-3c36-ee15c4730863</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>643</th>\n",
       "      <td>ff29bfd9-6a2c-5b21-0ece-fe592c676ad2</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>644</th>\n",
       "      <td>ff851980-71c6-25a4-ba86-802b77ac3674</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>645</th>\n",
       "      <td>ff93fd71-8378-60f8-3597-03632b7148a0</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>646</th>\n",
       "      <td>ffa15a7b-1f27-30ef-37d5-58981e5ce3dc</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>647 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                       id  covid_19    ckd\n",
       "0    00b8696d-15e5-4149-88fd-1dbc43ac2491      True  False\n",
       "1    00ec181c-6ccc-853d-708e-39ef00380b74     False  False\n",
       "2    019c1492-1d38-6884-d015-20b1a65035d5      True  False\n",
       "3    02af773e-cd68-cca7-4c42-a3e9210dc1ec      True  False\n",
       "4    03383a87-552f-463e-d5dd-310234b05fa5      True  False\n",
       "..                                    ...       ...    ...\n",
       "642  ff1aa4b1-7d6a-6d17-3c36-ee15c4730863     False  False\n",
       "643  ff29bfd9-6a2c-5b21-0ece-fe592c676ad2      True  False\n",
       "644  ff851980-71c6-25a4-ba86-802b77ac3674     False  False\n",
       "645  ff93fd71-8378-60f8-3597-03632b7148a0      True  False\n",
       "646  ffa15a7b-1f27-30ef-37d5-58981e5ce3dc     False  False\n",
       "\n",
       "[647 rows x 3 columns]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "patients_ckd = pd.merge(patients_immunizations, ckd_df, left_on='id',\n",
    "                        right_on='patient_id')\n",
    "patients_ckd = patients_ckd.groupby(['id', 'covid_19']).ckd.max().to_frame().reset_index()\n",
    "patients_ckd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "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>covid_19</th>\n",
       "      <th>ckd</th>\n",
       "      <th>heart_disease</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00b8696d-15e5-4149-88fd-1dbc43ac2491</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>00ec181c-6ccc-853d-708e-39ef00380b74</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>019c1492-1d38-6884-d015-20b1a65035d5</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>02af773e-cd68-cca7-4c42-a3e9210dc1ec</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>03383a87-552f-463e-d5dd-310234b05fa5</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>642</th>\n",
       "      <td>ff1aa4b1-7d6a-6d17-3c36-ee15c4730863</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>643</th>\n",
       "      <td>ff29bfd9-6a2c-5b21-0ece-fe592c676ad2</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>644</th>\n",
       "      <td>ff851980-71c6-25a4-ba86-802b77ac3674</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>645</th>\n",
       "      <td>ff93fd71-8378-60f8-3597-03632b7148a0</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>646</th>\n",
       "      <td>ffa15a7b-1f27-30ef-37d5-58981e5ce3dc</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>647 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                       id  covid_19    ckd  heart_disease\n",
       "0    00b8696d-15e5-4149-88fd-1dbc43ac2491      True  False          False\n",
       "1    00ec181c-6ccc-853d-708e-39ef00380b74     False  False          False\n",
       "2    019c1492-1d38-6884-d015-20b1a65035d5      True  False          False\n",
       "3    02af773e-cd68-cca7-4c42-a3e9210dc1ec      True  False          False\n",
       "4    03383a87-552f-463e-d5dd-310234b05fa5      True  False          False\n",
       "..                                    ...       ...    ...            ...\n",
       "642  ff1aa4b1-7d6a-6d17-3c36-ee15c4730863     False  False          False\n",
       "643  ff29bfd9-6a2c-5b21-0ece-fe592c676ad2      True  False          False\n",
       "644  ff851980-71c6-25a4-ba86-802b77ac3674     False  False           True\n",
       "645  ff93fd71-8378-60f8-3597-03632b7148a0      True  False          False\n",
       "646  ffa15a7b-1f27-30ef-37d5-58981e5ce3dc     False  False          False\n",
       "\n",
       "[647 rows x 4 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "patients_heart_disease = pd.merge(patients_ckd, heart_disease_df, left_on='id',\n",
    "                                  right_on='patient_id')\n",
    "patients_heart_disease = patients_heart_disease.groupby(\n",
    "    ['id', 'covid_19', 'ckd']).heart_disease.max().to_frame().reset_index()\n",
    "patients_heart_disease"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "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>covid_19</th>\n",
       "      <th>ckd</th>\n",
       "      <th>heart_disease</th>\n",
       "      <th>bmi_over_30</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00b8696d-15e5-4149-88fd-1dbc43ac2491</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>00ec181c-6ccc-853d-708e-39ef00380b74</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>019c1492-1d38-6884-d015-20b1a65035d5</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>02af773e-cd68-cca7-4c42-a3e9210dc1ec</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>03383a87-552f-463e-d5dd-310234b05fa5</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>642</th>\n",
       "      <td>ff1aa4b1-7d6a-6d17-3c36-ee15c4730863</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>643</th>\n",
       "      <td>ff29bfd9-6a2c-5b21-0ece-fe592c676ad2</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>644</th>\n",
       "      <td>ff851980-71c6-25a4-ba86-802b77ac3674</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>645</th>\n",
       "      <td>ff93fd71-8378-60f8-3597-03632b7148a0</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>646</th>\n",
       "      <td>ffa15a7b-1f27-30ef-37d5-58981e5ce3dc</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>647 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                       id  covid_19    ckd  heart_disease  \\\n",
       "0    00b8696d-15e5-4149-88fd-1dbc43ac2491      True  False          False   \n",
       "1    00ec181c-6ccc-853d-708e-39ef00380b74     False  False          False   \n",
       "2    019c1492-1d38-6884-d015-20b1a65035d5      True  False          False   \n",
       "3    02af773e-cd68-cca7-4c42-a3e9210dc1ec      True  False          False   \n",
       "4    03383a87-552f-463e-d5dd-310234b05fa5      True  False          False   \n",
       "..                                    ...       ...    ...            ...   \n",
       "642  ff1aa4b1-7d6a-6d17-3c36-ee15c4730863     False  False          False   \n",
       "643  ff29bfd9-6a2c-5b21-0ece-fe592c676ad2      True  False          False   \n",
       "644  ff851980-71c6-25a4-ba86-802b77ac3674     False  False           True   \n",
       "645  ff93fd71-8378-60f8-3597-03632b7148a0      True  False          False   \n",
       "646  ffa15a7b-1f27-30ef-37d5-58981e5ce3dc     False  False          False   \n",
       "\n",
       "     bmi_over_30  \n",
       "0           True  \n",
       "1          False  \n",
       "2           True  \n",
       "3           True  \n",
       "4           True  \n",
       "..           ...  \n",
       "642        False  \n",
       "643         True  \n",
       "644        False  \n",
       "645         True  \n",
       "646        False  \n",
       "\n",
       "[647 rows x 5 columns]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "patients_all = pd.merge(patients_heart_disease, observation_df, left_on='id', right_on='patient_id')\n",
    "patients_all = patients_all.groupby(\n",
    "    ['id', 'covid_19', 'ckd', 'heart_disease']).bmi_over_30.max().to_frame().reset_index()\n",
    "patients_all"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "Next we will create a derived column that indicates whether the patient exhibits any of the risk factors."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "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>covid_19</th>\n",
       "      <th>ckd</th>\n",
       "      <th>heart_disease</th>\n",
       "      <th>bmi_over_30</th>\n",
       "      <th>high_risk</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00b8696d-15e5-4149-88fd-1dbc43ac2491</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>00ec181c-6ccc-853d-708e-39ef00380b74</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>019c1492-1d38-6884-d015-20b1a65035d5</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>02af773e-cd68-cca7-4c42-a3e9210dc1ec</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>03383a87-552f-463e-d5dd-310234b05fa5</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>True</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>642</th>\n",
       "      <td>ff1aa4b1-7d6a-6d17-3c36-ee15c4730863</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>643</th>\n",
       "      <td>ff29bfd9-6a2c-5b21-0ece-fe592c676ad2</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>644</th>\n",
       "      <td>ff851980-71c6-25a4-ba86-802b77ac3674</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>645</th>\n",
       "      <td>ff93fd71-8378-60f8-3597-03632b7148a0</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>646</th>\n",
       "      <td>ffa15a7b-1f27-30ef-37d5-58981e5ce3dc</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>647 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                       id  covid_19    ckd  heart_disease  \\\n",
       "0    00b8696d-15e5-4149-88fd-1dbc43ac2491      True  False          False   \n",
       "1    00ec181c-6ccc-853d-708e-39ef00380b74     False  False          False   \n",
       "2    019c1492-1d38-6884-d015-20b1a65035d5      True  False          False   \n",
       "3    02af773e-cd68-cca7-4c42-a3e9210dc1ec      True  False          False   \n",
       "4    03383a87-552f-463e-d5dd-310234b05fa5      True  False          False   \n",
       "..                                    ...       ...    ...            ...   \n",
       "642  ff1aa4b1-7d6a-6d17-3c36-ee15c4730863     False  False          False   \n",
       "643  ff29bfd9-6a2c-5b21-0ece-fe592c676ad2      True  False          False   \n",
       "644  ff851980-71c6-25a4-ba86-802b77ac3674     False  False           True   \n",
       "645  ff93fd71-8378-60f8-3597-03632b7148a0      True  False          False   \n",
       "646  ffa15a7b-1f27-30ef-37d5-58981e5ce3dc     False  False          False   \n",
       "\n",
       "     bmi_over_30  high_risk  \n",
       "0           True       True  \n",
       "1          False      False  \n",
       "2           True       True  \n",
       "3           True       True  \n",
       "4           True       True  \n",
       "..           ...        ...  \n",
       "642        False      False  \n",
       "643         True       True  \n",
       "644        False       True  \n",
       "645         True       True  \n",
       "646        False      False  \n",
       "\n",
       "[647 rows x 6 columns]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "patients_all['high_risk'] = patients_all.ckd | patients_all.heart_disease | patients_all.bmi_over_30\n",
    "patients_all"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "Now we can get the counts for high-risk patients and their vaccination status."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "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>id</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>covid_19</th>\n",
       "      <th>high_risk</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">False</th>\n",
       "      <th>False</th>\n",
       "      <td>81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>True</th>\n",
       "      <td>85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">True</th>\n",
       "      <th>False</th>\n",
       "      <td>266</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>True</th>\n",
       "      <td>215</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     id\n",
       "covid_19 high_risk     \n",
       "False    False       81\n",
       "         True        85\n",
       "True     False      266\n",
       "         True       215"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "counts = patients_all.groupby(['covid_19', 'high_risk']).id.nunique()\n",
    "counts.to_frame()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "## List the high risk unvaccinated patients\n",
    "\n",
    "Finally, we list out the patients that are high-risk and have not been vaccinated, along with the specific risk factors that were identified for each patient.\n",
    "\n",
    "Sort the data frame by patient name before display, so that our result is deterministic."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "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>name</th>\n",
       "      <th>telephone</th>\n",
       "      <th>ckd</th>\n",
       "      <th>heart_disease</th>\n",
       "      <th>bmi_over_30</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Abernathy524, Kathline630</td>\n",
       "      <td>555-746-7353</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bartell116, Rhett759</td>\n",
       "      <td>555-257-6514</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72</th>\n",
       "      <td>Bashirian201, Aldo414</td>\n",
       "      <td>555-300-9051</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Beahan375, Neva514</td>\n",
       "      <td>555-809-1747</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Bednar518, Chase54</td>\n",
       "      <td>555-812-1196</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70</th>\n",
       "      <td>Wintheiser220, Georgene966</td>\n",
       "      <td>555-630-3157</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>Wunsch504, Domenic627</td>\n",
       "      <td>555-973-4643</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Wyman904, Joey457</td>\n",
       "      <td>555-833-6044</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Zulauf375, Elvin140</td>\n",
       "      <td>555-925-7111</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Zulauf375, Kaitlin600</td>\n",
       "      <td>555-859-1355</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>85 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                          name     telephone    ckd  heart_disease  \\\n",
       "24   Abernathy524, Kathline630  555-746-7353   True          False   \n",
       "0         Bartell116, Rhett759  555-257-6514  False          False   \n",
       "72       Bashirian201, Aldo414  555-300-9051  False          False   \n",
       "15          Beahan375, Neva514  555-809-1747  False          False   \n",
       "25          Bednar518, Chase54  555-812-1196  False          False   \n",
       "..                         ...           ...    ...            ...   \n",
       "70  Wintheiser220, Georgene966  555-630-3157  False          False   \n",
       "34       Wunsch504, Domenic627  555-973-4643  False           True   \n",
       "4            Wyman904, Joey457  555-833-6044  False          False   \n",
       "21         Zulauf375, Elvin140  555-925-7111  False           True   \n",
       "2        Zulauf375, Kaitlin600  555-859-1355  False          False   \n",
       "\n",
       "    bmi_over_30  \n",
       "24         True  \n",
       "0          True  \n",
       "72         True  \n",
       "15         True  \n",
       "25         True  \n",
       "..          ...  \n",
       "70         True  \n",
       "34         True  \n",
       "4          True  \n",
       "21        False  \n",
       "2          True  \n",
       "\n",
       "[85 rows x 5 columns]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "filtered_patients = patients_all[patients_all.high_risk & ~patients_all.covid_19]\n",
    "patient_list = pd.merge(filtered_patients, patient_df, left_on='id', right_on='id')\n",
    "patient_list[['name', 'telephone', 'ckd', 'heart_disease', 'bmi_over_30']].sort_values('name')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total execution time: 61.606 s\n"
     ]
    }
   ],
   "source": [
    "print(f\"Total execution time: {time() - start:.3f} s\")\n"
   ]
  }
 ],
 "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.10.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}