EHR-Pipeline-Part3.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "bc44d2fa-195c-4331-b421-558e45f43acc",
   "metadata": {},
   "source": [
    "<div style=\"text-align: center; font-weight: bold;\">\n",
    "    <h1>Generating Research Ready EHR Datasets</h1>\n",
    "    <h2>Part 3: Cohort Creation, NLP and Aggregating the processed EHR Data</h2>\n",
    "    <h4>Author: Vidul Ayakulangara Panickan</h3>\n",
    "</div>\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cdb86837-aebd-4595-83d9-e50c15cc69c9",
   "metadata": {},
   "source": [
    "## Step 5 Cohort Creation\n",
    "\n",
    "EHR based studies are typically conducted on a group of patients who meet specific inclusion/exclusion criteria. Some examples are\n",
    "\n",
    "- **Diagnoses based studies**: Patients diagnosed with a particular disease or condition.\n",
    "- **Treatment based studies**: Patients who have undergone a particular procedure or who were prescribed a particular medication.\n",
    "- **Device based studies**: Patients implanted with specific devices.\n",
    "\n",
    "In all the cases above, the first task is to identify the patient cohort based on the defined criteria"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "80a7d6a6-b884-4845-941c-f838fcf32a58",
   "metadata": {},
   "source": [
    "### Creating an Asthma Cohort\n",
    "\n",
    "A common strategy for identifying the patient cohort is to use the ICD codes corresponding to asthma. However, ICD codes can be very granular, and different studies on asthma usually ends up using different sets of ICD codes leading to inconsistencies. An alternative approach is to use PheCodes corresponding to asthma and filter patients based on these codes. The [PheWAS Catalog](https://phewascatalog.org/) provides a mapping between PheCodes and ICD codes.\n",
    "\n",
    "We see that PheCode string Asthma corresponds to PheCode 495. Once we have the PheCode we can identify all ICD codes that falls under that phecode and then grab patients having atleast one of those phecodes. Let's go ahead and implement that."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ef7cc2ae-e718-4af4-b5f4-d221c691d116",
   "metadata": {},
   "source": [
    "### Extract Cohort Data of Interest\n",
    "Now for each dataset of rolled up data, we can extract the data of interest and aggregate them at patient level."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "53ac690e-4c14-4f8f-a213-75f562bc1439",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "----Raw MIMIC IV 3.1 Diagnoses ICD----\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>subject_id</th>\n",
       "      <th>hadm_id</th>\n",
       "      <th>seq_num</th>\n",
       "      <th>code</th>\n",
       "      <th>icd_version</th>\n",
       "      <th>coding_system</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10000032</td>\n",
       "      <td>22595853</td>\n",
       "      <td>1</td>\n",
       "      <td>5723</td>\n",
       "      <td>9</td>\n",
       "      <td>ICD9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10000032</td>\n",
       "      <td>22595853</td>\n",
       "      <td>2</td>\n",
       "      <td>78959</td>\n",
       "      <td>9</td>\n",
       "      <td>ICD9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10000032</td>\n",
       "      <td>22595853</td>\n",
       "      <td>3</td>\n",
       "      <td>5715</td>\n",
       "      <td>9</td>\n",
       "      <td>ICD9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10000032</td>\n",
       "      <td>22595853</td>\n",
       "      <td>4</td>\n",
       "      <td>07070</td>\n",
       "      <td>9</td>\n",
       "      <td>ICD9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10000032</td>\n",
       "      <td>22595853</td>\n",
       "      <td>5</td>\n",
       "      <td>496</td>\n",
       "      <td>9</td>\n",
       "      <td>ICD9</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>6364483</th>\n",
       "      <td>19999987</td>\n",
       "      <td>23865745</td>\n",
       "      <td>7</td>\n",
       "      <td>41401</td>\n",
       "      <td>9</td>\n",
       "      <td>ICD9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6364484</th>\n",
       "      <td>19999987</td>\n",
       "      <td>23865745</td>\n",
       "      <td>8</td>\n",
       "      <td>78039</td>\n",
       "      <td>9</td>\n",
       "      <td>ICD9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6364485</th>\n",
       "      <td>19999987</td>\n",
       "      <td>23865745</td>\n",
       "      <td>9</td>\n",
       "      <td>0413</td>\n",
       "      <td>9</td>\n",
       "      <td>ICD9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6364486</th>\n",
       "      <td>19999987</td>\n",
       "      <td>23865745</td>\n",
       "      <td>10</td>\n",
       "      <td>36846</td>\n",
       "      <td>9</td>\n",
       "      <td>ICD9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6364487</th>\n",
       "      <td>19999987</td>\n",
       "      <td>23865745</td>\n",
       "      <td>11</td>\n",
       "      <td>7810</td>\n",
       "      <td>9</td>\n",
       "      <td>ICD9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6364488 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        subject_id   hadm_id seq_num   code icd_version coding_system\n",
       "0         10000032  22595853       1   5723           9          ICD9\n",
       "1         10000032  22595853       2  78959           9          ICD9\n",
       "2         10000032  22595853       3   5715           9          ICD9\n",
       "3         10000032  22595853       4  07070           9          ICD9\n",
       "4         10000032  22595853       5    496           9          ICD9\n",
       "...            ...       ...     ...    ...         ...           ...\n",
       "6364483   19999987  23865745       7  41401           9          ICD9\n",
       "6364484   19999987  23865745       8  78039           9          ICD9\n",
       "6364485   19999987  23865745       9   0413           9          ICD9\n",
       "6364486   19999987  23865745      10  36846           9          ICD9\n",
       "6364487   19999987  23865745      11   7810           9          ICD9\n",
       "\n",
       "[6364488 rows x 6 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "----ICD to PheCode Rollup file----\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code</th>\n",
       "      <th>PheCode</th>\n",
       "      <th>coding_system</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>001</td>\n",
       "      <td>008</td>\n",
       "      <td>ICD9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0010</td>\n",
       "      <td>008</td>\n",
       "      <td>ICD9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0011</td>\n",
       "      <td>008</td>\n",
       "      <td>ICD9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0019</td>\n",
       "      <td>008</td>\n",
       "      <td>ICD9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>002</td>\n",
       "      <td>008</td>\n",
       "      <td>ICD9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   code PheCode coding_system\n",
       "0   001     008          ICD9\n",
       "1  0010     008          ICD9\n",
       "2  0011     008          ICD9\n",
       "3  0019     008          ICD9\n",
       "4   002     008          ICD9"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "----Comprehensive Diagnoses including ICD and PheCode----\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>subject_id</th>\n",
       "      <th>hadm_id</th>\n",
       "      <th>seq_num</th>\n",
       "      <th>code</th>\n",
       "      <th>icd_version</th>\n",
       "      <th>coding_system</th>\n",
       "      <th>PheCode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10000032</td>\n",
       "      <td>22595853</td>\n",
       "      <td>1</td>\n",
       "      <td>5723</td>\n",
       "      <td>9</td>\n",
       "      <td>ICD9</td>\n",
       "      <td>571.81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10000826</td>\n",
       "      <td>20032235</td>\n",
       "      <td>4</td>\n",
       "      <td>5723</td>\n",
       "      <td>9</td>\n",
       "      <td>ICD9</td>\n",
       "      <td>571.81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10000826</td>\n",
       "      <td>28289260</td>\n",
       "      <td>1</td>\n",
       "      <td>5723</td>\n",
       "      <td>9</td>\n",
       "      <td>ICD9</td>\n",
       "      <td>571.81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10005866</td>\n",
       "      <td>26158160</td>\n",
       "      <td>4</td>\n",
       "      <td>5723</td>\n",
       "      <td>9</td>\n",
       "      <td>ICD9</td>\n",
       "      <td>571.81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10008924</td>\n",
       "      <td>23676183</td>\n",
       "      <td>7</td>\n",
       "      <td>5723</td>\n",
       "      <td>9</td>\n",
       "      <td>ICD9</td>\n",
       "      <td>571.81</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  subject_id   hadm_id seq_num  code icd_version coding_system PheCode\n",
       "0   10000032  22595853       1  5723           9          ICD9  571.81\n",
       "1   10000826  20032235       4  5723           9          ICD9  571.81\n",
       "2   10000826  28289260       1  5723           9          ICD9  571.81\n",
       "3   10005866  26158160       4  5723           9          ICD9  571.81\n",
       "4   10008924  23676183       7  5723           9          ICD9  571.81"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "----Identifying the cohort of interest----\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>subject_id</th>\n",
       "      <th>hadm_id</th>\n",
       "      <th>seq_num</th>\n",
       "      <th>code</th>\n",
       "      <th>icd_version</th>\n",
       "      <th>coding_system</th>\n",
       "      <th>PheCode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2478863</th>\n",
       "      <td>10001725</td>\n",
       "      <td>25563031</td>\n",
       "      <td>4</td>\n",
       "      <td>49390</td>\n",
       "      <td>9</td>\n",
       "      <td>ICD9</td>\n",
       "      <td>495</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2478864</th>\n",
       "      <td>10001884</td>\n",
       "      <td>26679629</td>\n",
       "      <td>7</td>\n",
       "      <td>49390</td>\n",
       "      <td>9</td>\n",
       "      <td>ICD9</td>\n",
       "      <td>495</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2478865</th>\n",
       "      <td>10003019</td>\n",
       "      <td>20030125</td>\n",
       "      <td>5</td>\n",
       "      <td>49390</td>\n",
       "      <td>9</td>\n",
       "      <td>ICD9</td>\n",
       "      <td>495</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2478866</th>\n",
       "      <td>10003019</td>\n",
       "      <td>20277210</td>\n",
       "      <td>10</td>\n",
       "      <td>49390</td>\n",
       "      <td>9</td>\n",
       "      <td>ICD9</td>\n",
       "      <td>495</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2478867</th>\n",
       "      <td>10003019</td>\n",
       "      <td>20962108</td>\n",
       "      <td>15</td>\n",
       "      <td>49390</td>\n",
       "      <td>9</td>\n",
       "      <td>ICD9</td>\n",
       "      <td>495</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5908161</th>\n",
       "      <td>17892612</td>\n",
       "      <td>24109018</td>\n",
       "      <td>1</td>\n",
       "      <td>49382</td>\n",
       "      <td>9</td>\n",
       "      <td>ICD9</td>\n",
       "      <td>495</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5908162</th>\n",
       "      <td>17997063</td>\n",
       "      <td>25519468</td>\n",
       "      <td>11</td>\n",
       "      <td>49382</td>\n",
       "      <td>9</td>\n",
       "      <td>ICD9</td>\n",
       "      <td>495</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5908163</th>\n",
       "      <td>18269165</td>\n",
       "      <td>28966193</td>\n",
       "      <td>6</td>\n",
       "      <td>49382</td>\n",
       "      <td>9</td>\n",
       "      <td>ICD9</td>\n",
       "      <td>495</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5908164</th>\n",
       "      <td>18958101</td>\n",
       "      <td>23643092</td>\n",
       "      <td>8</td>\n",
       "      <td>49382</td>\n",
       "      <td>9</td>\n",
       "      <td>ICD9</td>\n",
       "      <td>495</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5908165</th>\n",
       "      <td>19757198</td>\n",
       "      <td>28680884</td>\n",
       "      <td>9</td>\n",
       "      <td>49382</td>\n",
       "      <td>9</td>\n",
       "      <td>ICD9</td>\n",
       "      <td>495</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>42057 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        subject_id   hadm_id seq_num   code icd_version coding_system PheCode\n",
       "2478863   10001725  25563031       4  49390           9          ICD9     495\n",
       "2478864   10001884  26679629       7  49390           9          ICD9     495\n",
       "2478865   10003019  20030125       5  49390           9          ICD9     495\n",
       "2478866   10003019  20277210      10  49390           9          ICD9     495\n",
       "2478867   10003019  20962108      15  49390           9          ICD9     495\n",
       "...            ...       ...     ...    ...         ...           ...     ...\n",
       "5908161   17892612  24109018       1  49382           9          ICD9     495\n",
       "5908162   17997063  25519468      11  49382           9          ICD9     495\n",
       "5908163   18269165  28966193       6  49382           9          ICD9     495\n",
       "5908164   18958101  23643092       8  49382           9          ICD9     495\n",
       "5908165   19757198  28680884       9  49382           9          ICD9     495\n",
       "\n",
       "[42057 rows x 7 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "       subject_id   hadm_id seq_num    code icd_version coding_system PheCode\n",
      "count       42057     42057   42057   42057       42057         42057   42057\n",
      "unique      20316     42035      39      11           2             2       1\n",
      "top      18676703  24773199       5  J45909          10         ICD10     495\n",
      "freq           60         3    4105   20679       21954         21954   42057\n",
      "----Final Asthma Cohort----\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>subject_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2478863</th>\n",
       "      <td>10001725</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2478864</th>\n",
       "      <td>10001884</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2478865</th>\n",
       "      <td>10003019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2478872</th>\n",
       "      <td>10004457</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2478875</th>\n",
       "      <td>10004749</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5908156</th>\n",
       "      <td>16550589</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5908158</th>\n",
       "      <td>17562616</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5908161</th>\n",
       "      <td>17892612</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5908162</th>\n",
       "      <td>17997063</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5908165</th>\n",
       "      <td>19757198</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>20316 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        subject_id\n",
       "2478863   10001725\n",
       "2478864   10001884\n",
       "2478865   10003019\n",
       "2478872   10004457\n",
       "2478875   10004749\n",
       "...            ...\n",
       "5908156   16550589\n",
       "5908158   17562616\n",
       "5908161   17892612\n",
       "5908162   17997063\n",
       "5908165   19757198\n",
       "\n",
       "[20316 rows x 1 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import os\n",
    "base_directory = os.path.dirname(os.getcwd())\n",
    "\n",
    "\n",
    "diagnoses_icd = ( \n",
    "     pd.read_csv(\n",
    "        os.path.join(base_directory, \"raw_data\", \"physionet.org\", \"files\", \"mimiciv\", \"3.1\", \"hosp\", \"diagnoses_icd.csv\"),\n",
    "        dtype=str\n",
    "    ).rename(columns={'icd_code': 'code'})\n",
    ")\n",
    "diagnoses_icd['coding_system'] = \"ICD\" + diagnoses_icd['icd_version']\n",
    "\n",
    "print(\"----Raw MIMIC IV 3.1 Diagnoses ICD----\")\n",
    "display(diagnoses_icd)\n",
    "\n",
    "\n",
    "icd_to_phecode = pd.read_csv(\n",
    "    os.path.join(base_directory, 'scripts', 'rollup_mappings', \"icd_to_phecode.csv\"),\n",
    "    dtype=str\n",
    ")\n",
    "print(\"----ICD to PheCode Rollup file----\")\n",
    "display(icd_to_phecode.head())\n",
    "\n",
    "\n",
    "print(\"----Comprehensive Diagnoses including ICD and PheCode----\")\n",
    "comprehensive_diagnoses = pd.merge(diagnoses_icd, icd_to_phecode, on=['coding_system','code'])\n",
    "display(comprehensive_diagnoses.head())\n",
    "\n",
    "\n",
    "print(\"----Identifying the cohort of interest----\")\n",
    "comprehensive_asthma_cohort = comprehensive_diagnoses[comprehensive_diagnoses['PheCode']=='495']\n",
    "display(comprehensive_asthma_cohort)\n",
    "print(comprehensive_asthma_cohort.describe())\n",
    "\n",
    "\n",
    "print(\"----Final Asthma Cohort----\")\n",
    "asthma_cohort = comprehensive_asthma_cohort[['subject_id']].drop_duplicates()\n",
    "display(asthma_cohort)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "581604f9-624e-4af1-a0d5-d1f7dc5c19fc",
   "metadata": {},
   "outputs": [],
   "source": [
    "# We will extract data of interest, aggregate them and save them here\n",
    "cohort_aggregateddata_directory =os.path.join(base_directory, 'processed_data', 'step5_cohort_aggregateddata')\n",
    "os.makedirs(cohort_aggregateddata_directory, exist_ok=True)\n",
    "\n",
    "# We will seperate codified aggregated data and nlp aggregated data \n",
    "cohort_aggregateddata_codified_directory = os.path.join(base_directory, 'processed_data', 'step5_cohort_aggregateddata', 'codified')\n",
    "os.makedirs(cohort_aggregateddata_codified_directory, exist_ok=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "a935630e-a9f6-4b4c-ab1e-72410cf34ea8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>subject_id</th>\n",
       "      <th>date</th>\n",
       "      <th>PheCode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10004457</td>\n",
       "      <td>2140-09-17</td>\n",
       "      <td>411.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10004457</td>\n",
       "      <td>2140-09-17</td>\n",
       "      <td>272.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10004457</td>\n",
       "      <td>2140-09-17</td>\n",
       "      <td>401.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10004457</td>\n",
       "      <td>2140-09-17</td>\n",
       "      <td>495</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10004457</td>\n",
       "      <td>2140-09-17</td>\n",
       "      <td>185</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>122569</th>\n",
       "      <td>19990563</td>\n",
       "      <td>2180-11-30</td>\n",
       "      <td>783</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>122570</th>\n",
       "      <td>19990563</td>\n",
       "      <td>2180-11-30</td>\n",
       "      <td>250.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>122571</th>\n",
       "      <td>19990563</td>\n",
       "      <td>2180-11-30</td>\n",
       "      <td>401.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>122572</th>\n",
       "      <td>19990563</td>\n",
       "      <td>2180-11-30</td>\n",
       "      <td>457.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>122573</th>\n",
       "      <td>19990563</td>\n",
       "      <td>2180-11-30</td>\n",
       "      <td>495</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>122574 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       subject_id        date PheCode\n",
       "0        10004457  2140-09-17   411.4\n",
       "1        10004457  2140-09-17   272.1\n",
       "2        10004457  2140-09-17   401.1\n",
       "3        10004457  2140-09-17     495\n",
       "4        10004457  2140-09-17     185\n",
       "...           ...         ...     ...\n",
       "122569   19990563  2180-11-30     783\n",
       "122570   19990563  2180-11-30   250.2\n",
       "122571   19990563  2180-11-30   401.1\n",
       "122572   19990563  2180-11-30   457.3\n",
       "122573   19990563  2180-11-30     495\n",
       "\n",
       "[122574 rows x 3 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>subject_id</th>\n",
       "      <th>date</th>\n",
       "      <th>PheCode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10017393</td>\n",
       "      <td>2179-07-20</td>\n",
       "      <td>694.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10017393</td>\n",
       "      <td>2179-07-20</td>\n",
       "      <td>960.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10017393</td>\n",
       "      <td>2179-07-20</td>\n",
       "      <td>528.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10017393</td>\n",
       "      <td>2179-07-20</td>\n",
       "      <td>361</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10017393</td>\n",
       "      <td>2179-07-20</td>\n",
       "      <td>495</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>118535</th>\n",
       "      <td>19990581</td>\n",
       "      <td>2141-07-23</td>\n",
       "      <td>272.11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>118536</th>\n",
       "      <td>19990581</td>\n",
       "      <td>2141-07-23</td>\n",
       "      <td>250.24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>118537</th>\n",
       "      <td>19990581</td>\n",
       "      <td>2141-07-23</td>\n",
       "      <td>536.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>118538</th>\n",
       "      <td>19990581</td>\n",
       "      <td>2141-07-23</td>\n",
       "      <td>495</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>118539</th>\n",
       "      <td>19990581</td>\n",
       "      <td>2141-07-23</td>\n",
       "      <td>274.1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>118540 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       subject_id        date PheCode\n",
       "0        10017393  2179-07-20   694.3\n",
       "1        10017393  2179-07-20   960.2\n",
       "2        10017393  2179-07-20   528.7\n",
       "3        10017393  2179-07-20     361\n",
       "4        10017393  2179-07-20     495\n",
       "...           ...         ...     ...\n",
       "118535   19990581  2141-07-23  272.11\n",
       "118536   19990581  2141-07-23  250.24\n",
       "118537   19990581  2141-07-23   536.3\n",
       "118538   19990581  2141-07-23     495\n",
       "118539   19990581  2141-07-23   274.1\n",
       "\n",
       "[118540 rows x 3 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>subject_id</th>\n",
       "      <th>date</th>\n",
       "      <th>PheCode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10011912</td>\n",
       "      <td>2176-10-21</td>\n",
       "      <td>800.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10011912</td>\n",
       "      <td>2176-10-21</td>\n",
       "      <td>070.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10011912</td>\n",
       "      <td>2176-10-21</td>\n",
       "      <td>174.11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10011912</td>\n",
       "      <td>2176-10-21</td>\n",
       "      <td>495</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10011912</td>\n",
       "      <td>2176-10-21</td>\n",
       "      <td>317.11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>115431</th>\n",
       "      <td>19999442</td>\n",
       "      <td>2148-11-19</td>\n",
       "      <td>856</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>115432</th>\n",
       "      <td>19999442</td>\n",
       "      <td>2148-11-19</td>\n",
       "      <td>348</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>115433</th>\n",
       "      <td>19999442</td>\n",
       "      <td>2148-11-19</td>\n",
       "      <td>342</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>115434</th>\n",
       "      <td>19999442</td>\n",
       "      <td>2148-11-19</td>\n",
       "      <td>495</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>115435</th>\n",
       "      <td>19999442</td>\n",
       "      <td>2148-11-19</td>\n",
       "      <td>296.2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>115436 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       subject_id        date PheCode\n",
       "0        10011912  2176-10-21   800.3\n",
       "1        10011912  2176-10-21   070.3\n",
       "2        10011912  2176-10-21  174.11\n",
       "3        10011912  2176-10-21     495\n",
       "4        10011912  2176-10-21  317.11\n",
       "...           ...         ...     ...\n",
       "115431   19999442  2148-11-19     856\n",
       "115432   19999442  2148-11-19     348\n",
       "115433   19999442  2148-11-19     342\n",
       "115434   19999442  2148-11-19     495\n",
       "115435   19999442  2148-11-19   296.2\n",
       "\n",
       "[115436 rows x 3 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>subject_id</th>\n",
       "      <th>date</th>\n",
       "      <th>PheCode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10001884</td>\n",
       "      <td>2130-10-05</td>\n",
       "      <td>496.21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10001884</td>\n",
       "      <td>2130-10-05</td>\n",
       "      <td>1013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10001884</td>\n",
       "      <td>2130-10-05</td>\n",
       "      <td>276.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10001884</td>\n",
       "      <td>2130-10-05</td>\n",
       "      <td>401.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10001884</td>\n",
       "      <td>2130-10-05</td>\n",
       "      <td>272.11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>115775</th>\n",
       "      <td>19997760</td>\n",
       "      <td>2187-07-09</td>\n",
       "      <td>286.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>115776</th>\n",
       "      <td>19997760</td>\n",
       "      <td>2187-07-09</td>\n",
       "      <td>594</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>115777</th>\n",
       "      <td>19997760</td>\n",
       "      <td>2187-07-09</td>\n",
       "      <td>972.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>115778</th>\n",
       "      <td>19997760</td>\n",
       "      <td>2187-07-09</td>\n",
       "      <td>972.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>115779</th>\n",
       "      <td>19997760</td>\n",
       "      <td>2187-07-09</td>\n",
       "      <td>974</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>115780 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       subject_id        date PheCode\n",
       "0        10001884  2130-10-05  496.21\n",
       "1        10001884  2130-10-05    1013\n",
       "2        10001884  2130-10-05  276.14\n",
       "3        10001884  2130-10-05   401.1\n",
       "4        10001884  2130-10-05  272.11\n",
       "...           ...         ...     ...\n",
       "115775   19997760  2187-07-09   286.2\n",
       "115776   19997760  2187-07-09     594\n",
       "115777   19997760  2187-07-09   972.6\n",
       "115778   19997760  2187-07-09   972.1\n",
       "115779   19997760  2187-07-09     974\n",
       "\n",
       "[115780 rows x 3 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>subject_id</th>\n",
       "      <th>date</th>\n",
       "      <th>PheCode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10003019</td>\n",
       "      <td>2174-12-25</td>\n",
       "      <td>743.21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10003019</td>\n",
       "      <td>2174-12-25</td>\n",
       "      <td>334</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10003019</td>\n",
       "      <td>2174-12-25</td>\n",
       "      <td>697</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10003019</td>\n",
       "      <td>2174-12-25</td>\n",
       "      <td>510</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10003019</td>\n",
       "      <td>2174-12-25</td>\n",
       "      <td>495</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>124334</th>\n",
       "      <td>19996016</td>\n",
       "      <td>2159-12-10</td>\n",
       "      <td>415</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>124335</th>\n",
       "      <td>19996016</td>\n",
       "      <td>2159-12-10</td>\n",
       "      <td>256.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>124336</th>\n",
       "      <td>19996016</td>\n",
       "      <td>2159-12-10</td>\n",
       "      <td>1010.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>124337</th>\n",
       "      <td>19996016</td>\n",
       "      <td>2159-12-10</td>\n",
       "      <td>652</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>124338</th>\n",
       "      <td>19996016</td>\n",
       "      <td>2159-12-10</td>\n",
       "      <td>663</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>124339 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       subject_id        date PheCode\n",
       "0        10003019  2174-12-25  743.21\n",
       "1        10003019  2174-12-25     334\n",
       "2        10003019  2174-12-25     697\n",
       "3        10003019  2174-12-25     510\n",
       "4        10003019  2174-12-25     495\n",
       "...           ...         ...     ...\n",
       "124334   19996016  2159-12-10     415\n",
       "124335   19996016  2159-12-10   256.4\n",
       "124336   19996016  2159-12-10  1010.6\n",
       "124337   19996016  2159-12-10     652\n",
       "124338   19996016  2159-12-10     663\n",
       "\n",
       "[124339 rows x 3 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>subject_id</th>\n",
       "      <th>date</th>\n",
       "      <th>PheCode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10002800</td>\n",
       "      <td>2164-07-12</td>\n",
       "      <td>649</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10002800</td>\n",
       "      <td>2164-07-12</td>\n",
       "      <td>521.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10002800</td>\n",
       "      <td>2164-07-12</td>\n",
       "      <td>1010.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10002800</td>\n",
       "      <td>2164-07-12</td>\n",
       "      <td>495</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10002800</td>\n",
       "      <td>2164-07-12</td>\n",
       "      <td>646</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>117695</th>\n",
       "      <td>19996832</td>\n",
       "      <td>2179-02-21</td>\n",
       "      <td>296.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>117696</th>\n",
       "      <td>19996832</td>\n",
       "      <td>2179-02-21</td>\n",
       "      <td>297.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>117697</th>\n",
       "      <td>19996832</td>\n",
       "      <td>2179-02-21</td>\n",
       "      <td>495</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>117698</th>\n",
       "      <td>19996832</td>\n",
       "      <td>2179-02-21</td>\n",
       "      <td>318</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>117699</th>\n",
       "      <td>19996832</td>\n",
       "      <td>2179-02-21</td>\n",
       "      <td>301.2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>117700 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       subject_id        date PheCode\n",
       "0        10002800  2164-07-12     649\n",
       "1        10002800  2164-07-12   521.1\n",
       "2        10002800  2164-07-12  1010.6\n",
       "3        10002800  2164-07-12     495\n",
       "4        10002800  2164-07-12     646\n",
       "...           ...         ...     ...\n",
       "117695   19996832  2179-02-21  296.22\n",
       "117696   19996832  2179-02-21   297.2\n",
       "117697   19996832  2179-02-21     495\n",
       "117698   19996832  2179-02-21     318\n",
       "117699   19996832  2179-02-21   301.2\n",
       "\n",
       "[117700 rows x 3 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>subject_id</th>\n",
       "      <th>date</th>\n",
       "      <th>PheCode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10011607</td>\n",
       "      <td>2184-04-26</td>\n",
       "      <td>495.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10011607</td>\n",
       "      <td>2184-04-26</td>\n",
       "      <td>509.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10011607</td>\n",
       "      <td>2184-04-26</td>\n",
       "      <td>411.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10011607</td>\n",
       "      <td>2184-04-26</td>\n",
       "      <td>401.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10011607</td>\n",
       "      <td>2184-04-26</td>\n",
       "      <td>290.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>115303</th>\n",
       "      <td>19998350</td>\n",
       "      <td>2128-02-21</td>\n",
       "      <td>327.32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>115304</th>\n",
       "      <td>19998350</td>\n",
       "      <td>2128-02-21</td>\n",
       "      <td>495</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>115305</th>\n",
       "      <td>19998350</td>\n",
       "      <td>2128-02-21</td>\n",
       "      <td>278.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>115306</th>\n",
       "      <td>19998350</td>\n",
       "      <td>2128-02-21</td>\n",
       "      <td>300.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>115307</th>\n",
       "      <td>19998350</td>\n",
       "      <td>2128-02-21</td>\n",
       "      <td>208</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>115308 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       subject_id        date PheCode\n",
       "0        10011607  2184-04-26   495.2\n",
       "1        10011607  2184-04-26   509.1\n",
       "2        10011607  2184-04-26   411.3\n",
       "3        10011607  2184-04-26   401.1\n",
       "4        10011607  2184-04-26   290.1\n",
       "...           ...         ...     ...\n",
       "115303   19998350  2128-02-21  327.32\n",
       "115304   19998350  2128-02-21     495\n",
       "115305   19998350  2128-02-21   278.1\n",
       "115306   19998350  2128-02-21   300.1\n",
       "115307   19998350  2128-02-21     208\n",
       "\n",
       "[115308 rows x 3 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>subject_id</th>\n",
       "      <th>date</th>\n",
       "      <th>PheCode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10001725</td>\n",
       "      <td>2110-04-11</td>\n",
       "      <td>599.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10001725</td>\n",
       "      <td>2110-04-11</td>\n",
       "      <td>946</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10001725</td>\n",
       "      <td>2110-04-11</td>\n",
       "      <td>618.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10001725</td>\n",
       "      <td>2110-04-11</td>\n",
       "      <td>495</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10001725</td>\n",
       "      <td>2110-04-11</td>\n",
       "      <td>530.11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113325</th>\n",
       "      <td>19997887</td>\n",
       "      <td>2117-04-07</td>\n",
       "      <td>318</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113326</th>\n",
       "      <td>19997887</td>\n",
       "      <td>2117-04-07</td>\n",
       "      <td>288.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113327</th>\n",
       "      <td>19997887</td>\n",
       "      <td>2117-04-07</td>\n",
       "      <td>338.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113328</th>\n",
       "      <td>19997887</td>\n",
       "      <td>2117-04-07</td>\n",
       "      <td>789</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113329</th>\n",
       "      <td>19997887</td>\n",
       "      <td>2117-04-07</td>\n",
       "      <td>458.2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>113330 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       subject_id        date PheCode\n",
       "0        10001725  2110-04-11   599.2\n",
       "1        10001725  2110-04-11     946\n",
       "2        10001725  2110-04-11   618.5\n",
       "3        10001725  2110-04-11     495\n",
       "4        10001725  2110-04-11  530.11\n",
       "...           ...         ...     ...\n",
       "113325   19997887  2117-04-07     318\n",
       "113326   19997887  2117-04-07   288.2\n",
       "113327   19997887  2117-04-07   338.1\n",
       "113328   19997887  2117-04-07     789\n",
       "113329   19997887  2117-04-07   458.2\n",
       "\n",
       "[113330 rows x 3 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>subject_id</th>\n",
       "      <th>date</th>\n",
       "      <th>PheCode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10004457</td>\n",
       "      <td>2140-09-17</td>\n",
       "      <td>411.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10004457</td>\n",
       "      <td>2140-09-17</td>\n",
       "      <td>272.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10004457</td>\n",
       "      <td>2140-09-17</td>\n",
       "      <td>401.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10004457</td>\n",
       "      <td>2140-09-17</td>\n",
       "      <td>495</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10004457</td>\n",
       "      <td>2140-09-17</td>\n",
       "      <td>185</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113325</th>\n",
       "      <td>19997887</td>\n",
       "      <td>2117-04-07</td>\n",
       "      <td>318</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113326</th>\n",
       "      <td>19997887</td>\n",
       "      <td>2117-04-07</td>\n",
       "      <td>288.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113327</th>\n",
       "      <td>19997887</td>\n",
       "      <td>2117-04-07</td>\n",
       "      <td>338.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113328</th>\n",
       "      <td>19997887</td>\n",
       "      <td>2117-04-07</td>\n",
       "      <td>789</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113329</th>\n",
       "      <td>19997887</td>\n",
       "      <td>2117-04-07</td>\n",
       "      <td>458.2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>943007 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       subject_id        date PheCode\n",
       "0        10004457  2140-09-17   411.4\n",
       "1        10004457  2140-09-17   272.1\n",
       "2        10004457  2140-09-17   401.1\n",
       "3        10004457  2140-09-17     495\n",
       "4        10004457  2140-09-17     185\n",
       "...           ...         ...     ...\n",
       "113325   19997887  2117-04-07     318\n",
       "113326   19997887  2117-04-07   288.2\n",
       "113327   19997887  2117-04-07   338.1\n",
       "113328   19997887  2117-04-07     789\n",
       "113329   19997887  2117-04-07   458.2\n",
       "\n",
       "[943007 rows x 3 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Processing data in batches to extract patient data of interest\n",
    "\n",
    "rolledup_diagnoses_directory =  os.path.join(base_directory, 'processed_data', 'step4_rolledup_finaldata', 'Diagnoses')\n",
    "rolledup_diagnoses_batch_files = os.listdir(rolledup_diagnoses_directory)\n",
    "sample_rolled_diagnoses = pd.read_csv(os.path.join(rolledup_diagnoses_directory, rolledup_diagnoses_batch_files[0]), dtype=str)\n",
    "\n",
    "extracted_diagnoses_dfs = []\n",
    "\n",
    "for diagnoses_batch_file in rolledup_diagnoses_batch_files:\n",
    "    diagnoses_batch = pd.read_csv(os.path.join(rolledup_diagnoses_directory, diagnoses_batch_file), dtype=str)\n",
    "    diagnoses_batch_extracted = pd.merge(diagnoses_batch,asthma_cohort, on=['subject_id'], how='inner')\n",
    "    display(diagnoses_batch_extracted)\n",
    "    extracted_diagnoses_dfs.append(diagnoses_batch_extracted)\n",
    "\n",
    "extracted_diagnoses = pd.concat(extracted_diagnoses_dfs)\n",
    "display(extracted_diagnoses)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "16d5fa5c-ac6d-4ca2-a2a5-9fe864c31e82",
   "metadata": {},
   "source": [
    "### Aggregate Data at Patient Level"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "f383cce3-382a-4485-a9c7-0e69342fbfe8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>subject_id</th>\n",
       "      <th>PheCode</th>\n",
       "      <th>counts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10001725</td>\n",
       "      <td>180.1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10001725</td>\n",
       "      <td>296.2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10001725</td>\n",
       "      <td>300.1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10001725</td>\n",
       "      <td>313.1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10001725</td>\n",
       "      <td>318</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>480814</th>\n",
       "      <td>19999442</td>\n",
       "      <td>433.21</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>480815</th>\n",
       "      <td>19999442</td>\n",
       "      <td>495</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>480816</th>\n",
       "      <td>19999442</td>\n",
       "      <td>591</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>480817</th>\n",
       "      <td>19999442</td>\n",
       "      <td>594</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>480818</th>\n",
       "      <td>19999442</td>\n",
       "      <td>856</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>480819 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       subject_id PheCode  counts\n",
       "0        10001725   180.1       1\n",
       "1        10001725   296.2       1\n",
       "2        10001725   300.1       1\n",
       "3        10001725   313.1       1\n",
       "4        10001725     318       1\n",
       "...           ...     ...     ...\n",
       "480814   19999442  433.21       1\n",
       "480815   19999442     495       2\n",
       "480816   19999442     591       1\n",
       "480817   19999442     594       1\n",
       "480818   19999442     856       1\n",
       "\n",
       "[480819 rows x 3 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>PheCode</th>\n",
       "      <th>008</th>\n",
       "      <th>008.5</th>\n",
       "      <th>008.51</th>\n",
       "      <th>008.52</th>\n",
       "      <th>008.6</th>\n",
       "      <th>008.7</th>\n",
       "      <th>010</th>\n",
       "      <th>031</th>\n",
       "      <th>038</th>\n",
       "      <th>038.1</th>\n",
       "      <th>...</th>\n",
       "      <th>983</th>\n",
       "      <th>985</th>\n",
       "      <th>986</th>\n",
       "      <th>987</th>\n",
       "      <th>988</th>\n",
       "      <th>989</th>\n",
       "      <th>990</th>\n",
       "      <th>994.1</th>\n",
       "      <th>994.2</th>\n",
       "      <th>994.21</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>subject_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>10001725</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10001884</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10002800</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10003019</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10004296</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19997760</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19997887</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19998350</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19999112</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19999442</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>20316 rows × 1725 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "PheCode     008  008.5  008.51  008.52  008.6  008.7  010  031  038  038.1  \\\n",
       "subject_id                                                                   \n",
       "10001725      0      0       0       0      0      0    0    0    0      0   \n",
       "10001884      0      0       0       0      0      0    0    0    0      0   \n",
       "10002800      0      0       0       0      0      0    0    0    0      0   \n",
       "10003019      0      0       0       1      0      0    0    0    1      0   \n",
       "10004296      0      0       0       0      0      0    0    0    0      0   \n",
       "...         ...    ...     ...     ...    ...    ...  ...  ...  ...    ...   \n",
       "19997760      0      0       0       0      0      0    0    0    0      0   \n",
       "19997887      0      0       0       0      0      0    0    0    0      0   \n",
       "19998350      0      0       0       0      0      0    0    0    0      0   \n",
       "19999112      0      0       0       0      0      0    0    0    0      0   \n",
       "19999442      0      0       0       0      0      0    0    0    0      0   \n",
       "\n",
       "PheCode     ...  983  985  986  987  988  989  990  994.1  994.2  994.21  \n",
       "subject_id  ...                                                           \n",
       "10001725    ...    0    0    0    0    0    0    0      0      0       0  \n",
       "10001884    ...    0    0    0    0    0    0    0      0      0       0  \n",
       "10002800    ...    0    0    0    0    0    0    0      0      0       0  \n",
       "10003019    ...    0    0    0    0    0    0    0      0      1       1  \n",
       "10004296    ...    0    0    0    0    0    0    0      0      0       0  \n",
       "...         ...  ...  ...  ...  ...  ...  ...  ...    ...    ...     ...  \n",
       "19997760    ...    0    0    0    0    0    0    0      0      0       0  \n",
       "19997887    ...    0    0    0    0    0    0    0      0      0       0  \n",
       "19998350    ...    0    0    0    0    0    0    0      0      0       0  \n",
       "19999112    ...    0    0    0    0    0    0    0      0      0       0  \n",
       "19999442    ...    0    0    0    0    0    0    0      0      0       0  \n",
       "\n",
       "[20316 rows x 1725 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "phecode_counts_per_patient = extracted_diagnoses.groupby(['subject_id', 'PheCode']).size().reset_index(name='counts')\n",
    "display(phecode_counts_per_patient)\n",
    "\n",
    "phecode_counts_per_patient_matrixformat = phecode_counts_per_patient.pivot_table(index='subject_id', columns='PheCode', values='counts', fill_value=0)\n",
    "display(phecode_counts_per_patient_matrixformat)\n",
    "\n",
    "phecode_counts_per_patient_matrixformat.to_csv(os.path.join(cohort_aggregateddata_codified_directory,\"Diagnoses.csv\"), index=None)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3c89af21-3c7b-4899-b317-a54c256bd53b",
   "metadata": {},
   "source": [
    "<span style=\"color:red;\">**Important Note:**</span> The diagnoses data in the MIMIC-IV Hosp dataset does not include a specific time element.\n",
    "For the purpose of analysis, we have inferred the timing as the admission date. Please be cautious when using this assumption in \n",
    "downstream analyses as it may not accurately reflect the actual timing of diagnoses.\n",
    "\n",
    "Please proceed to generate the patient-level data for the other data types: medications, procedures, and labs."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "31b2e657-0074-4c94-adbd-8b95df66be91",
   "metadata": {},
   "source": [
    "# Natural Language Processing\n",
    "Bringing Structure into Unstructured data. \n",
    "\n",
    "To identify and extract clinically relevant concepts from notes, we translate text into codes based on mappings provided by the Unified Medical Language System (UMLS). To do this, we leverage tools such as [cTAKES](https://ctakes.apache.org/), [NILE](https://celehs.hms.harvard.edu/software/NILE.html), and [MetaMap](https://lhncbc.nlm.nih.gov/ii/tools/MetaMap.html).\n",
    "\n",
    "For this tutorial, we use [Petehr](https://pypi.org/project/petehr/)—a Python toolkit custom built for this tutorial. However, for better performance and in real world applications, we highly recommend using [NILE](https://celehs.hms.harvard.edu/software/NILE.html) or other similar software"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "da02bfb6-a276-4903-bf64-61b07e48a369",
   "metadata": {},
   "source": [
    "#### MIMIC Notes and Hosp module version difference \n",
    "\n",
    "In this section, we focus on processing discharge summaries from MIMIC-IV deidentified notes.  It’s important to note that the most recent version of the MIMIC-IV note data is [version 2.2](https://physionet.org/content/mimic-iv-note/2.2/), whereas the MIMIC-IV Hosp data we are using is from a newer version, [version 3.1](https://physionet.org/content/mimiciv/3.1/). This mismatch means there will be patients present in the Hosp data who are missing in the note data.\n",
    "\n",
    "This situation is typical in real world healthcare systems, where data from different sources or timeframes does not fully align. One way to address this is to ensure that the patient cohort you are analyzing has both note and Hosp data available. Additionally, since the Hosp data is newer you may need to truncate each patient's records in the Hosp data to align with the last observed note date for that patient."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "0213b86c-4ed8-460e-b307-709445cb5099",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: petehr in /n/data1/hsph/biostat/celehs/lab/va67/anaconda/anaconda3/lib/python3.11/site-packages (0.0.1)\r\n"
     ]
    }
   ],
   "source": [
    "# Installing Python Toolkit for processing EHR data. For NLP, we recommend you use NILE linked below\n",
    "# https://celehs.hms.harvard.edu/software/NILE.html\n",
    "\n",
    "!pip install petehr \n",
    "\n",
    "import os\n",
    "import pandas as pd\n",
    "from petehr import Text2Code\n",
    "\n",
    "base_directory = os.path.dirname(os.getcwd())\n",
    "cohort_aggregateddata_nlp_directory = os.path.join(base_directory, 'processed_data', 'step5_cohort_aggregateddata', 'nlp')\n",
    "os.makedirs(cohort_aggregateddata_nlp_directory, exist_ok=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "e9961b37-b3da-46f7-95e0-b2f1118dfbc9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['discharge.csv',\n",
       " 'discharge_detail.csv',\n",
       " 'index.html',\n",
       " 'radiology.csv',\n",
       " 'radiology_detail.csv']"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "note_directory = os.path.join(base_directory, 'raw_data', 'nlp', 'physionet.org', 'files', 'mimic-iv-note', '2.2', 'note')\n",
    "os.listdir(note_directory)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "99b2d908-3dcf-49ea-ab7c-e355f0a1f984",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>STR</th>\n",
       "      <th>CUI</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>pulmonary hypertension nos</td>\n",
       "      <td>C0020542</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>hypertensive pulmonary vascular disease</td>\n",
       "      <td>C0020542</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>hypertension pulmonary</td>\n",
       "      <td>C0020542</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>pulmonary hypertension disorder</td>\n",
       "      <td>C0020542</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>pulmonary hypertensions</td>\n",
       "      <td>C0020542</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2975</th>\n",
       "      <td>drug screen qualitative digoxin</td>\n",
       "      <td>C0337449</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2976</th>\n",
       "      <td>electrocardiogram myocardial infarction</td>\n",
       "      <td>C0428953</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2977</th>\n",
       "      <td>roche brand of bumetanide</td>\n",
       "      <td>C0701009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2978</th>\n",
       "      <td>glaxosmithkline brand of carvedilol</td>\n",
       "      <td>C0719509</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2979</th>\n",
       "      <td>pfizer brand of eplerenone</td>\n",
       "      <td>C1144054</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2980 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                          STR       CUI\n",
       "0                  pulmonary hypertension nos  C0020542\n",
       "1     hypertensive pulmonary vascular disease  C0020542\n",
       "2                      hypertension pulmonary  C0020542\n",
       "3             pulmonary hypertension disorder  C0020542\n",
       "4                     pulmonary hypertensions  C0020542\n",
       "...                                       ...       ...\n",
       "2975          drug screen qualitative digoxin  C0337449\n",
       "2976  electrocardiogram myocardial infarction  C0428953\n",
       "2977                roche brand of bumetanide  C0701009\n",
       "2978      glaxosmithkline brand of carvedilol  C0719509\n",
       "2979               pfizer brand of eplerenone  C1144054\n",
       "\n",
       "[2980 rows x 2 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                                          STR       CUI\n",
      "0                  pulmonary hypertension nos  C0020542\n",
      "1     hypertensive pulmonary vascular disease  C0020542\n",
      "2                      hypertension pulmonary  C0020542\n",
      "3             pulmonary hypertension disorder  C0020542\n",
      "4                     pulmonary hypertensions  C0020542\n",
      "...                                       ...       ...\n",
      "2975          drug screen qualitative digoxin  C0337449\n",
      "2976  electrocardiogram myocardial infarction  C0428953\n",
      "2977                roche brand of bumetanide  C0701009\n",
      "2978      glaxosmithkline brand of carvedilol  C0719509\n",
      "2979               pfizer brand of eplerenone  C1144054\n",
      "\n",
      "[2980 rows x 2 columns]\n",
      "                            STR       CUI\n",
      "count                      2976      2980\n",
      "unique                     2833       276\n",
      "top     ventricular tachycardia  C0038454\n",
      "freq                          4        52\n"
     ]
    }
   ],
   "source": [
    "# The Asthma CUI dictionary was downloaded from here. https://shiny.parse-health.org/ONCE/\n",
    "# Please see the bottom of this notebook for further resources \n",
    "\n",
    "asthma_dictionary_file = os.path.join(base_directory, 'scripts', 'meta_files','Asthma_NLP_Dict.csv')\n",
    "asthma_dictionary = pd.read_csv(asthma_dictionary_file, dtype=str)\n",
    "display(asthma_dictionary)\n",
    "\n",
    "print(asthma_dictionary)\n",
    "print(asthma_dictionary.describe())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "afbc811b",
   "metadata": {},
   "source": [
    "In the summary we  can asee we have identified 276 unique CUIs (Concept Unique Identifiers) using ONCE app that are related to Asthma. When we include the synonymous terms for these 276 CUIs, we have around 3000 CUI-term mappings "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "19b51362",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Dictionary loaded sucessfully from /n/scratch/users/v/va67/EHR_TUTORIAL_WORKSPACE/scripts/meta_files/Asthma_NLP_Dict.csv\n"
     ]
    }
   ],
   "source": [
    "text2cui = Text2Code(asthma_dictionary_file)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "009cc561-89f3-41d4-97a2-46c950dd43e0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>note_id</th>\n",
       "      <th>subject_id</th>\n",
       "      <th>hadm_id</th>\n",
       "      <th>note_type</th>\n",
       "      <th>note_seq</th>\n",
       "      <th>charttime</th>\n",
       "      <th>storetime</th>\n",
       "      <th>text</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10000032-DS-21</td>\n",
       "      <td>10000032</td>\n",
       "      <td>22595853</td>\n",
       "      <td>DS</td>\n",
       "      <td>21</td>\n",
       "      <td>2180-05-07 00:00:00</td>\n",
       "      <td>2180-05-09 15:26:00</td>\n",
       "      <td>\\nName:  ___                     Unit No:   _...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10000032-DS-22</td>\n",
       "      <td>10000032</td>\n",
       "      <td>22841357</td>\n",
       "      <td>DS</td>\n",
       "      <td>22</td>\n",
       "      <td>2180-06-27 00:00:00</td>\n",
       "      <td>2180-07-01 10:15:00</td>\n",
       "      <td>\\nName:  ___                     Unit No:   _...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10000032-DS-23</td>\n",
       "      <td>10000032</td>\n",
       "      <td>29079034</td>\n",
       "      <td>DS</td>\n",
       "      <td>23</td>\n",
       "      <td>2180-07-25 00:00:00</td>\n",
       "      <td>2180-07-25 21:42:00</td>\n",
       "      <td>\\nName:  ___                     Unit No:   _...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10000032-DS-24</td>\n",
       "      <td>10000032</td>\n",
       "      <td>25742920</td>\n",
       "      <td>DS</td>\n",
       "      <td>24</td>\n",
       "      <td>2180-08-07 00:00:00</td>\n",
       "      <td>2180-08-10 05:43:00</td>\n",
       "      <td>\\nName:  ___                     Unit No:   _...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10000084-DS-17</td>\n",
       "      <td>10000084</td>\n",
       "      <td>23052089</td>\n",
       "      <td>DS</td>\n",
       "      <td>17</td>\n",
       "      <td>2160-11-25 00:00:00</td>\n",
       "      <td>2160-11-25 15:09:00</td>\n",
       "      <td>\\nName:  ___                    Unit No:   __...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>331788</th>\n",
       "      <td>19999828-DS-6</td>\n",
       "      <td>19999828</td>\n",
       "      <td>29734428</td>\n",
       "      <td>DS</td>\n",
       "      <td>6</td>\n",
       "      <td>2147-08-04 00:00:00</td>\n",
       "      <td>2147-08-12 15:36:00</td>\n",
       "      <td>\\nName:  ___                   Unit No:   ___...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>331789</th>\n",
       "      <td>19999828-DS-7</td>\n",
       "      <td>19999828</td>\n",
       "      <td>25744818</td>\n",
       "      <td>DS</td>\n",
       "      <td>7</td>\n",
       "      <td>2149-01-18 00:00:00</td>\n",
       "      <td>2149-01-19 07:03:00</td>\n",
       "      <td>\\nName:  ___                   Unit No:   ___...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>331790</th>\n",
       "      <td>19999840-DS-20</td>\n",
       "      <td>19999840</td>\n",
       "      <td>26071774</td>\n",
       "      <td>DS</td>\n",
       "      <td>20</td>\n",
       "      <td>2164-07-28 00:00:00</td>\n",
       "      <td>2164-07-29 14:52:00</td>\n",
       "      <td>\\nName:  ___                  Unit No:   ___\\...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>331791</th>\n",
       "      <td>19999840-DS-21</td>\n",
       "      <td>19999840</td>\n",
       "      <td>21033226</td>\n",
       "      <td>DS</td>\n",
       "      <td>21</td>\n",
       "      <td>2164-09-17 00:00:00</td>\n",
       "      <td>2164-09-18 01:36:00</td>\n",
       "      <td>\\nName:  ___                  Unit No:   ___\\...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>331792</th>\n",
       "      <td>19999987-DS-2</td>\n",
       "      <td>19999987</td>\n",
       "      <td>23865745</td>\n",
       "      <td>DS</td>\n",
       "      <td>2</td>\n",
       "      <td>2145-11-11 00:00:00</td>\n",
       "      <td>2145-11-11 13:13:00</td>\n",
       "      <td>\\nName:  ___                    Unit No:   __...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>331793 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               note_id subject_id   hadm_id note_type note_seq  \\\n",
       "0       10000032-DS-21   10000032  22595853        DS       21   \n",
       "1       10000032-DS-22   10000032  22841357        DS       22   \n",
       "2       10000032-DS-23   10000032  29079034        DS       23   \n",
       "3       10000032-DS-24   10000032  25742920        DS       24   \n",
       "4       10000084-DS-17   10000084  23052089        DS       17   \n",
       "...                ...        ...       ...       ...      ...   \n",
       "331788   19999828-DS-6   19999828  29734428        DS        6   \n",
       "331789   19999828-DS-7   19999828  25744818        DS        7   \n",
       "331790  19999840-DS-20   19999840  26071774        DS       20   \n",
       "331791  19999840-DS-21   19999840  21033226        DS       21   \n",
       "331792   19999987-DS-2   19999987  23865745        DS        2   \n",
       "\n",
       "                  charttime            storetime  \\\n",
       "0       2180-05-07 00:00:00  2180-05-09 15:26:00   \n",
       "1       2180-06-27 00:00:00  2180-07-01 10:15:00   \n",
       "2       2180-07-25 00:00:00  2180-07-25 21:42:00   \n",
       "3       2180-08-07 00:00:00  2180-08-10 05:43:00   \n",
       "4       2160-11-25 00:00:00  2160-11-25 15:09:00   \n",
       "...                     ...                  ...   \n",
       "331788  2147-08-04 00:00:00  2147-08-12 15:36:00   \n",
       "331789  2149-01-18 00:00:00  2149-01-19 07:03:00   \n",
       "331790  2164-07-28 00:00:00  2164-07-29 14:52:00   \n",
       "331791  2164-09-17 00:00:00  2164-09-18 01:36:00   \n",
       "331792  2145-11-11 00:00:00  2145-11-11 13:13:00   \n",
       "\n",
       "                                                     text  \n",
       "0        \\nName:  ___                     Unit No:   _...  \n",
       "1        \\nName:  ___                     Unit No:   _...  \n",
       "2        \\nName:  ___                     Unit No:   _...  \n",
       "3        \\nName:  ___                     Unit No:   _...  \n",
       "4        \\nName:  ___                    Unit No:   __...  \n",
       "...                                                   ...  \n",
       "331788   \\nName:  ___                   Unit No:   ___...  \n",
       "331789   \\nName:  ___                   Unit No:   ___...  \n",
       "331790   \\nName:  ___                  Unit No:   ___\\...  \n",
       "331791   \\nName:  ___                  Unit No:   ___\\...  \n",
       "331792   \\nName:  ___                    Unit No:   __...  \n",
       "\n",
       "[331793 rows x 8 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Loading discharge notes data\n",
    "\n",
    "discharge = pd.read_csv(os.path.join(note_directory,\"discharge.csv\"), dtype=str)\n",
    "display(discharge)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "59d873e1-dd85-4005-9ff2-41199fae8768",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "20316\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>note_id</th>\n",
       "      <th>subject_id</th>\n",
       "      <th>hadm_id</th>\n",
       "      <th>note_type</th>\n",
       "      <th>note_seq</th>\n",
       "      <th>charttime</th>\n",
       "      <th>storetime</th>\n",
       "      <th>text</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>50286</td>\n",
       "      <td>50286</td>\n",
       "      <td>50286</td>\n",
       "      <td>50286</td>\n",
       "      <td>50286</td>\n",
       "      <td>50286</td>\n",
       "      <td>50284</td>\n",
       "      <td>50286</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unique</th>\n",
       "      <td>50286</td>\n",
       "      <td>14958</td>\n",
       "      <td>50286</td>\n",
       "      <td>1</td>\n",
       "      <td>135</td>\n",
       "      <td>25983</td>\n",
       "      <td>50232</td>\n",
       "      <td>50285</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>top</th>\n",
       "      <td>10001725-DS-12</td>\n",
       "      <td>12468016</td>\n",
       "      <td>25563031</td>\n",
       "      <td>DS</td>\n",
       "      <td>21</td>\n",
       "      <td>2160-06-09 00:00:00</td>\n",
       "      <td>2116-12-03 14:09:00</td>\n",
       "      <td>\\nName:  ___                   Unit No:   ___...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>freq</th>\n",
       "      <td>1</td>\n",
       "      <td>85</td>\n",
       "      <td>1</td>\n",
       "      <td>50286</td>\n",
       "      <td>2391</td>\n",
       "      <td>9</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               note_id subject_id   hadm_id note_type note_seq  \\\n",
       "count            50286      50286     50286     50286    50286   \n",
       "unique           50286      14958     50286         1      135   \n",
       "top     10001725-DS-12   12468016  25563031        DS       21   \n",
       "freq                 1         85         1     50286     2391   \n",
       "\n",
       "                  charttime            storetime  \\\n",
       "count                 50286                50284   \n",
       "unique                25983                50232   \n",
       "top     2160-06-09 00:00:00  2116-12-03 14:09:00   \n",
       "freq                      9                    2   \n",
       "\n",
       "                                                     text  \n",
       "count                                               50286  \n",
       "unique                                              50285  \n",
       "top      \\nName:  ___                   Unit No:   ___...  \n",
       "freq                                                    2  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Check how many patients from the Asthma cohort can be identified in the notes cohort.\n",
    "\n",
    "print(len(asthma_cohort))\n",
    "\n",
    "discharge_asthma_cohort = discharge[discharge['subject_id'].isin(asthma_cohort['subject_id'])]\n",
    "\n",
    "display(discharge_asthma_cohort.describe())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bf48b691-ef25-44e5-9d91-d56dc005fc93",
   "metadata": {},
   "source": [
    "Out of 20,316 patients in the asthma cohort, only 14,858 patients have discharge notes.\n",
    "Approximately **73%** of the patients have discharge notes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "11276a50-3313-4f75-865d-8b527de942f8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>subject_id</th>\n",
       "      <th>charttime</th>\n",
       "      <th>text</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10000032</td>\n",
       "      <td>2180-05-07 00:00:00</td>\n",
       "      <td>\\nName:  ___                     Unit No:   _...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10000032</td>\n",
       "      <td>2180-06-27 00:00:00</td>\n",
       "      <td>\\nName:  ___                     Unit No:   _...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10000032</td>\n",
       "      <td>2180-07-25 00:00:00</td>\n",
       "      <td>\\nName:  ___                     Unit No:   _...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10000032</td>\n",
       "      <td>2180-08-07 00:00:00</td>\n",
       "      <td>\\nName:  ___                     Unit No:   _...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10000084</td>\n",
       "      <td>2160-11-25 00:00:00</td>\n",
       "      <td>\\nName:  ___                    Unit No:   __...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  subject_id            charttime  \\\n",
       "0   10000032  2180-05-07 00:00:00   \n",
       "1   10000032  2180-06-27 00:00:00   \n",
       "2   10000032  2180-07-25 00:00:00   \n",
       "3   10000032  2180-08-07 00:00:00   \n",
       "4   10000084  2160-11-25 00:00:00   \n",
       "\n",
       "                                                text  \n",
       "0   \\nName:  ___                     Unit No:   _...  \n",
       "1   \\nName:  ___                     Unit No:   _...  \n",
       "2   \\nName:  ___                     Unit No:   _...  \n",
       "3   \\nName:  ___                     Unit No:   _...  \n",
       "4   \\nName:  ___                    Unit No:   __...  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Selecting only the required columns\n",
    "\n",
    "discharge = discharge[['subject_id','charttime','text']]\n",
    "display(discharge.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "6783ed3e-8c50-4bed-92f5-7b26e92196d8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>subject_id</th>\n",
       "      <th>charttime</th>\n",
       "      <th>text</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10001725</td>\n",
       "      <td>2110-04-14 00:00:00</td>\n",
       "      <td>\\nName:  ___             Unit No:   ___\\n \\nA...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10001884</td>\n",
       "      <td>2125-10-20 00:00:00</td>\n",
       "      <td>\\nName:  ___             Unit No:   ___\\n \\nA...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10001884</td>\n",
       "      <td>2125-10-27 00:00:00</td>\n",
       "      <td>\\nName:  ___             Unit No:   ___\\n \\nA...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10001884</td>\n",
       "      <td>2125-12-03 00:00:00</td>\n",
       "      <td>\\nName:  ___             Unit No:   ___\\n \\nA...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10001884</td>\n",
       "      <td>2125-12-27 00:00:00</td>\n",
       "      <td>\\nName:  ___             Unit No:   ___\\n \\nA...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50281</th>\n",
       "      <td>19757198</td>\n",
       "      <td>2189-09-12 00:00:00</td>\n",
       "      <td>\\nName:  ___                    Unit No:   __...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50282</th>\n",
       "      <td>19757198</td>\n",
       "      <td>2191-05-26 00:00:00</td>\n",
       "      <td>\\nName:  ___                    Unit No:   __...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50283</th>\n",
       "      <td>19757198</td>\n",
       "      <td>2193-06-23 00:00:00</td>\n",
       "      <td>\\nName:  ___                    Unit No:   __...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50284</th>\n",
       "      <td>19757198</td>\n",
       "      <td>2194-02-19 00:00:00</td>\n",
       "      <td>\\nName:  ___                    Unit No:   __...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50285</th>\n",
       "      <td>19757198</td>\n",
       "      <td>2194-10-02 00:00:00</td>\n",
       "      <td>\\nName:  ___                    Unit No:   __...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>50286 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      subject_id            charttime  \\\n",
       "0       10001725  2110-04-14 00:00:00   \n",
       "1       10001884  2125-10-20 00:00:00   \n",
       "2       10001884  2125-10-27 00:00:00   \n",
       "3       10001884  2125-12-03 00:00:00   \n",
       "4       10001884  2125-12-27 00:00:00   \n",
       "...          ...                  ...   \n",
       "50281   19757198  2189-09-12 00:00:00   \n",
       "50282   19757198  2191-05-26 00:00:00   \n",
       "50283   19757198  2193-06-23 00:00:00   \n",
       "50284   19757198  2194-02-19 00:00:00   \n",
       "50285   19757198  2194-10-02 00:00:00   \n",
       "\n",
       "                                                    text  \n",
       "0       \\nName:  ___             Unit No:   ___\\n \\nA...  \n",
       "1       \\nName:  ___             Unit No:   ___\\n \\nA...  \n",
       "2       \\nName:  ___             Unit No:   ___\\n \\nA...  \n",
       "3       \\nName:  ___             Unit No:   ___\\n \\nA...  \n",
       "4       \\nName:  ___             Unit No:   ___\\n \\nA...  \n",
       "...                                                  ...  \n",
       "50281   \\nName:  ___                    Unit No:   __...  \n",
       "50282   \\nName:  ___                    Unit No:   __...  \n",
       "50283   \\nName:  ___                    Unit No:   __...  \n",
       "50284   \\nName:  ___                    Unit No:   __...  \n",
       "50285   \\nName:  ___                    Unit No:   __...  \n",
       "\n",
       "[50286 rows x 3 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>subject_id</th>\n",
       "      <th>charttime</th>\n",
       "      <th>text</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10001725</td>\n",
       "      <td>2110-04-14 00:00:00</td>\n",
       "      <td>\\nName:  ___             Unit No:   ___\\n \\nA...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10001884</td>\n",
       "      <td>2125-10-20 00:00:00</td>\n",
       "      <td>\\nName:  ___             Unit No:   ___\\n \\nA...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10001884</td>\n",
       "      <td>2125-10-27 00:00:00</td>\n",
       "      <td>\\nName:  ___             Unit No:   ___\\n \\nA...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10001884</td>\n",
       "      <td>2125-12-03 00:00:00</td>\n",
       "      <td>\\nName:  ___             Unit No:   ___\\n \\nA...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10001884</td>\n",
       "      <td>2125-12-27 00:00:00</td>\n",
       "      <td>\\nName:  ___             Unit No:   ___\\n \\nA...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50281</th>\n",
       "      <td>19757198</td>\n",
       "      <td>2189-09-12 00:00:00</td>\n",
       "      <td>\\nName:  ___                    Unit No:   __...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50282</th>\n",
       "      <td>19757198</td>\n",
       "      <td>2191-05-26 00:00:00</td>\n",
       "      <td>\\nName:  ___                    Unit No:   __...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50283</th>\n",
       "      <td>19757198</td>\n",
       "      <td>2193-06-23 00:00:00</td>\n",
       "      <td>\\nName:  ___                    Unit No:   __...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50284</th>\n",
       "      <td>19757198</td>\n",
       "      <td>2194-02-19 00:00:00</td>\n",
       "      <td>\\nName:  ___                    Unit No:   __...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50285</th>\n",
       "      <td>19757198</td>\n",
       "      <td>2194-10-02 00:00:00</td>\n",
       "      <td>\\nName:  ___                    Unit No:   __...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>50286 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      subject_id            charttime  \\\n",
       "0       10001725  2110-04-14 00:00:00   \n",
       "1       10001884  2125-10-20 00:00:00   \n",
       "2       10001884  2125-10-27 00:00:00   \n",
       "3       10001884  2125-12-03 00:00:00   \n",
       "4       10001884  2125-12-27 00:00:00   \n",
       "...          ...                  ...   \n",
       "50281   19757198  2189-09-12 00:00:00   \n",
       "50282   19757198  2191-05-26 00:00:00   \n",
       "50283   19757198  2193-06-23 00:00:00   \n",
       "50284   19757198  2194-02-19 00:00:00   \n",
       "50285   19757198  2194-10-02 00:00:00   \n",
       "\n",
       "                                                    text  \n",
       "0       \\nName:  ___             Unit No:   ___\\n \\nA...  \n",
       "1       \\nName:  ___             Unit No:   ___\\n \\nA...  \n",
       "2       \\nName:  ___             Unit No:   ___\\n \\nA...  \n",
       "3       \\nName:  ___             Unit No:   ___\\n \\nA...  \n",
       "4       \\nName:  ___             Unit No:   ___\\n \\nA...  \n",
       "...                                                  ...  \n",
       "50281   \\nName:  ___                    Unit No:   __...  \n",
       "50282   \\nName:  ___                    Unit No:   __...  \n",
       "50283   \\nName:  ___                    Unit No:   __...  \n",
       "50284   \\nName:  ___                    Unit No:   __...  \n",
       "50285   \\nName:  ___                    Unit No:   __...  \n",
       "\n",
       "[50286 rows x 3 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Filtering to retain only the notes associated with the patients of interest\n",
    "\n",
    "asthma_cohort_notes = pd.merge(asthma_cohort,discharge, on=['subject_id'],how='inner')\n",
    "display(asthma_cohort_notes)\n",
    "\n",
    "asthma_cohort_notes.dropna(inplace=True)\n",
    "display(asthma_cohort_notes)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "bdb1756a-4f52-4a05-a226-d9e6d4625e92",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>subject_id</th>\n",
       "      <th>charttime</th>\n",
       "      <th>text</th>\n",
       "      <th>note_cui</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10001725</td>\n",
       "      <td>2110-04-14 00:00:00</td>\n",
       "      <td>\\nName:  ___             Unit No:   ___\\n \\nA...</td>\n",
       "      <td>C5441729,C5441729,C5441729,C5441729,C5441729,C...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10001884</td>\n",
       "      <td>2125-10-20 00:00:00</td>\n",
       "      <td>\\nName:  ___             Unit No:   ___\\n \\nA...</td>\n",
       "      <td>C5441729,C5441729,C5441729,C5441729,C5441729,C...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10001884</td>\n",
       "      <td>2125-10-27 00:00:00</td>\n",
       "      <td>\\nName:  ___             Unit No:   ___\\n \\nA...</td>\n",
       "      <td>C5441729,C5441729,C5441729,C5441729,C5441729,C...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10001884</td>\n",
       "      <td>2125-12-03 00:00:00</td>\n",
       "      <td>\\nName:  ___             Unit No:   ___\\n \\nA...</td>\n",
       "      <td>C5441729,C5441729,C5441729,C5441729,C5441729,C...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10001884</td>\n",
       "      <td>2125-12-27 00:00:00</td>\n",
       "      <td>\\nName:  ___             Unit No:   ___\\n \\nA...</td>\n",
       "      <td>C5441729,C5441729,C5441729,C5441729,C5441729,C...</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>50281</th>\n",
       "      <td>19757198</td>\n",
       "      <td>2189-09-12 00:00:00</td>\n",
       "      <td>\\nName:  ___                    Unit No:   __...</td>\n",
       "      <td>C5441729,C5441729,C5441729,C5441729,C5441729,C...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50282</th>\n",
       "      <td>19757198</td>\n",
       "      <td>2191-05-26 00:00:00</td>\n",
       "      <td>\\nName:  ___                    Unit No:   __...</td>\n",
       "      <td>C5441729,C5441729,C5441729,C5441729,C5441729,C...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50283</th>\n",
       "      <td>19757198</td>\n",
       "      <td>2193-06-23 00:00:00</td>\n",
       "      <td>\\nName:  ___                    Unit No:   __...</td>\n",
       "      <td>C5441729,C5441729,C5441729,C5441729,C5441729,C...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50284</th>\n",
       "      <td>19757198</td>\n",
       "      <td>2194-02-19 00:00:00</td>\n",
       "      <td>\\nName:  ___                    Unit No:   __...</td>\n",
       "      <td>C5441729,C5441729,C5441729,C5441729,C5441729,C...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50285</th>\n",
       "      <td>19757198</td>\n",
       "      <td>2194-10-02 00:00:00</td>\n",
       "      <td>\\nName:  ___                    Unit No:   __...</td>\n",
       "      <td>C5441729,C5441729,C5441729,C5441729,C5441729,C...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>50286 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      subject_id            charttime  \\\n",
       "0       10001725  2110-04-14 00:00:00   \n",
       "1       10001884  2125-10-20 00:00:00   \n",
       "2       10001884  2125-10-27 00:00:00   \n",
       "3       10001884  2125-12-03 00:00:00   \n",
       "4       10001884  2125-12-27 00:00:00   \n",
       "...          ...                  ...   \n",
       "50281   19757198  2189-09-12 00:00:00   \n",
       "50282   19757198  2191-05-26 00:00:00   \n",
       "50283   19757198  2193-06-23 00:00:00   \n",
       "50284   19757198  2194-02-19 00:00:00   \n",
       "50285   19757198  2194-10-02 00:00:00   \n",
       "\n",
       "                                                    text  \\\n",
       "0       \\nName:  ___             Unit No:   ___\\n \\nA...   \n",
       "1       \\nName:  ___             Unit No:   ___\\n \\nA...   \n",
       "2       \\nName:  ___             Unit No:   ___\\n \\nA...   \n",
       "3       \\nName:  ___             Unit No:   ___\\n \\nA...   \n",
       "4       \\nName:  ___             Unit No:   ___\\n \\nA...   \n",
       "...                                                  ...   \n",
       "50281   \\nName:  ___                    Unit No:   __...   \n",
       "50282   \\nName:  ___                    Unit No:   __...   \n",
       "50283   \\nName:  ___                    Unit No:   __...   \n",
       "50284   \\nName:  ___                    Unit No:   __...   \n",
       "50285   \\nName:  ___                    Unit No:   __...   \n",
       "\n",
       "                                                note_cui  \n",
       "0      C5441729,C5441729,C5441729,C5441729,C5441729,C...  \n",
       "1      C5441729,C5441729,C5441729,C5441729,C5441729,C...  \n",
       "2      C5441729,C5441729,C5441729,C5441729,C5441729,C...  \n",
       "3      C5441729,C5441729,C5441729,C5441729,C5441729,C...  \n",
       "4      C5441729,C5441729,C5441729,C5441729,C5441729,C...  \n",
       "...                                                  ...  \n",
       "50281  C5441729,C5441729,C5441729,C5441729,C5441729,C...  \n",
       "50282  C5441729,C5441729,C5441729,C5441729,C5441729,C...  \n",
       "50283  C5441729,C5441729,C5441729,C5441729,C5441729,C...  \n",
       "50284  C5441729,C5441729,C5441729,C5441729,C5441729,C...  \n",
       "50285  C5441729,C5441729,C5441729,C5441729,C5441729,C...  \n",
       "\n",
       "[50286 rows x 4 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Converting Text to CUI (CONCEPT UNIQUE IDENTIFIER)\n",
    "\n",
    "asthma_cohort_notes['note_cui'] = asthma_cohort_notes['text'].map(lambda x: text2cui.convert(x))\n",
    "asthma_cohort_notes"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f0387aa5-5a20-45c6-99db-ea87af034123",
   "metadata": {},
   "source": [
    "Now that we have the narrative text translated to CUI codes, we can go ahead with the reformating and cleaning process."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "69bdee65-ebb0-4da5-bb05-cbec0ef797c3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Column</th>\n",
       "      <th>Missing_Values</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>subject_id</th>\n",
       "      <td>subject_id</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>charttime</th>\n",
       "      <td>charttime</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>text</th>\n",
       "      <td>text</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>note_cui</th>\n",
       "      <td>note_cui</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                Column  Missing_Values\n",
       "subject_id  subject_id               0\n",
       "charttime    charttime               0\n",
       "text              text               0\n",
       "note_cui      note_cui               0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>subject_id</th>\n",
       "      <th>charttime</th>\n",
       "      <th>note_cui</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10001725</td>\n",
       "      <td>2110-04-14 00:00:00</td>\n",
       "      <td>C5441729,C5441729,C5441729,C5441729,C5441729,C...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10001884</td>\n",
       "      <td>2125-10-20 00:00:00</td>\n",
       "      <td>C5441729,C5441729,C5441729,C5441729,C5441729,C...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10001884</td>\n",
       "      <td>2125-10-27 00:00:00</td>\n",
       "      <td>C5441729,C5441729,C5441729,C5441729,C5441729,C...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10001884</td>\n",
       "      <td>2125-12-03 00:00:00</td>\n",
       "      <td>C5441729,C5441729,C5441729,C5441729,C5441729,C...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10001884</td>\n",
       "      <td>2125-12-27 00:00:00</td>\n",
       "      <td>C5441729,C5441729,C5441729,C5441729,C5441729,C...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50281</th>\n",
       "      <td>19757198</td>\n",
       "      <td>2189-09-12 00:00:00</td>\n",
       "      <td>C5441729,C5441729,C5441729,C5441729,C5441729,C...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50282</th>\n",
       "      <td>19757198</td>\n",
       "      <td>2191-05-26 00:00:00</td>\n",
       "      <td>C5441729,C5441729,C5441729,C5441729,C5441729,C...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50283</th>\n",
       "      <td>19757198</td>\n",
       "      <td>2193-06-23 00:00:00</td>\n",
       "      <td>C5441729,C5441729,C5441729,C5441729,C5441729,C...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50284</th>\n",
       "      <td>19757198</td>\n",
       "      <td>2194-02-19 00:00:00</td>\n",
       "      <td>C5441729,C5441729,C5441729,C5441729,C5441729,C...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50285</th>\n",
       "      <td>19757198</td>\n",
       "      <td>2194-10-02 00:00:00</td>\n",
       "      <td>C5441729,C5441729,C5441729,C5441729,C5441729,C...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>50286 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      subject_id            charttime  \\\n",
       "0       10001725  2110-04-14 00:00:00   \n",
       "1       10001884  2125-10-20 00:00:00   \n",
       "2       10001884  2125-10-27 00:00:00   \n",
       "3       10001884  2125-12-03 00:00:00   \n",
       "4       10001884  2125-12-27 00:00:00   \n",
       "...          ...                  ...   \n",
       "50281   19757198  2189-09-12 00:00:00   \n",
       "50282   19757198  2191-05-26 00:00:00   \n",
       "50283   19757198  2193-06-23 00:00:00   \n",
       "50284   19757198  2194-02-19 00:00:00   \n",
       "50285   19757198  2194-10-02 00:00:00   \n",
       "\n",
       "                                                note_cui  \n",
       "0      C5441729,C5441729,C5441729,C5441729,C5441729,C...  \n",
       "1      C5441729,C5441729,C5441729,C5441729,C5441729,C...  \n",
       "2      C5441729,C5441729,C5441729,C5441729,C5441729,C...  \n",
       "3      C5441729,C5441729,C5441729,C5441729,C5441729,C...  \n",
       "4      C5441729,C5441729,C5441729,C5441729,C5441729,C...  \n",
       "...                                                  ...  \n",
       "50281  C5441729,C5441729,C5441729,C5441729,C5441729,C...  \n",
       "50282  C5441729,C5441729,C5441729,C5441729,C5441729,C...  \n",
       "50283  C5441729,C5441729,C5441729,C5441729,C5441729,C...  \n",
       "50284  C5441729,C5441729,C5441729,C5441729,C5441729,C...  \n",
       "50285  C5441729,C5441729,C5441729,C5441729,C5441729,C...  \n",
       "\n",
       "[50286 rows x 3 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>subject_id</th>\n",
       "      <th>date</th>\n",
       "      <th>note_cui</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10001725</td>\n",
       "      <td>2110-04-14 00:00:00</td>\n",
       "      <td>C5441729,C5441729,C5441729,C5441729,C5441729,C...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10001884</td>\n",
       "      <td>2125-10-20 00:00:00</td>\n",
       "      <td>C5441729,C5441729,C5441729,C5441729,C5441729,C...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10001884</td>\n",
       "      <td>2125-10-27 00:00:00</td>\n",
       "      <td>C5441729,C5441729,C5441729,C5441729,C5441729,C...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10001884</td>\n",
       "      <td>2125-12-03 00:00:00</td>\n",
       "      <td>C5441729,C5441729,C5441729,C5441729,C5441729,C...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10001884</td>\n",
       "      <td>2125-12-27 00:00:00</td>\n",
       "      <td>C5441729,C5441729,C5441729,C5441729,C5441729,C...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50281</th>\n",
       "      <td>19757198</td>\n",
       "      <td>2189-09-12 00:00:00</td>\n",
       "      <td>C5441729,C5441729,C5441729,C5441729,C5441729,C...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50282</th>\n",
       "      <td>19757198</td>\n",
       "      <td>2191-05-26 00:00:00</td>\n",
       "      <td>C5441729,C5441729,C5441729,C5441729,C5441729,C...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50283</th>\n",
       "      <td>19757198</td>\n",
       "      <td>2193-06-23 00:00:00</td>\n",
       "      <td>C5441729,C5441729,C5441729,C5441729,C5441729,C...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50284</th>\n",
       "      <td>19757198</td>\n",
       "      <td>2194-02-19 00:00:00</td>\n",
       "      <td>C5441729,C5441729,C5441729,C5441729,C5441729,C...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50285</th>\n",
       "      <td>19757198</td>\n",
       "      <td>2194-10-02 00:00:00</td>\n",
       "      <td>C5441729,C5441729,C5441729,C5441729,C5441729,C...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>50286 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      subject_id                 date  \\\n",
       "0       10001725  2110-04-14 00:00:00   \n",
       "1       10001884  2125-10-20 00:00:00   \n",
       "2       10001884  2125-10-27 00:00:00   \n",
       "3       10001884  2125-12-03 00:00:00   \n",
       "4       10001884  2125-12-27 00:00:00   \n",
       "...          ...                  ...   \n",
       "50281   19757198  2189-09-12 00:00:00   \n",
       "50282   19757198  2191-05-26 00:00:00   \n",
       "50283   19757198  2193-06-23 00:00:00   \n",
       "50284   19757198  2194-02-19 00:00:00   \n",
       "50285   19757198  2194-10-02 00:00:00   \n",
       "\n",
       "                                                note_cui  \n",
       "0      C5441729,C5441729,C5441729,C5441729,C5441729,C...  \n",
       "1      C5441729,C5441729,C5441729,C5441729,C5441729,C...  \n",
       "2      C5441729,C5441729,C5441729,C5441729,C5441729,C...  \n",
       "3      C5441729,C5441729,C5441729,C5441729,C5441729,C...  \n",
       "4      C5441729,C5441729,C5441729,C5441729,C5441729,C...  \n",
       "...                                                  ...  \n",
       "50281  C5441729,C5441729,C5441729,C5441729,C5441729,C...  \n",
       "50282  C5441729,C5441729,C5441729,C5441729,C5441729,C...  \n",
       "50283  C5441729,C5441729,C5441729,C5441729,C5441729,C...  \n",
       "50284  C5441729,C5441729,C5441729,C5441729,C5441729,C...  \n",
       "50285  C5441729,C5441729,C5441729,C5441729,C5441729,C...  \n",
       "\n",
       "[50286 rows x 3 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>subject_id</th>\n",
       "      <th>date</th>\n",
       "      <th>note_cui</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10001725</td>\n",
       "      <td>2110-04-14</td>\n",
       "      <td>C5441729,C5441729,C5441729,C5441729,C5441729,C...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10001884</td>\n",
       "      <td>2125-10-20</td>\n",
       "      <td>C5441729,C5441729,C5441729,C5441729,C5441729,C...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10001884</td>\n",
       "      <td>2125-10-27</td>\n",
       "      <td>C5441729,C5441729,C5441729,C5441729,C5441729,C...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10001884</td>\n",
       "      <td>2125-12-03</td>\n",
       "      <td>C5441729,C5441729,C5441729,C5441729,C5441729,C...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10001884</td>\n",
       "      <td>2125-12-27</td>\n",
       "      <td>C5441729,C5441729,C5441729,C5441729,C5441729,C...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50281</th>\n",
       "      <td>19757198</td>\n",
       "      <td>2189-09-12</td>\n",
       "      <td>C5441729,C5441729,C5441729,C5441729,C5441729,C...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50282</th>\n",
       "      <td>19757198</td>\n",
       "      <td>2191-05-26</td>\n",
       "      <td>C5441729,C5441729,C5441729,C5441729,C5441729,C...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50283</th>\n",
       "      <td>19757198</td>\n",
       "      <td>2193-06-23</td>\n",
       "      <td>C5441729,C5441729,C5441729,C5441729,C5441729,C...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50284</th>\n",
       "      <td>19757198</td>\n",
       "      <td>2194-02-19</td>\n",
       "      <td>C5441729,C5441729,C5441729,C5441729,C5441729,C...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50285</th>\n",
       "      <td>19757198</td>\n",
       "      <td>2194-10-02</td>\n",
       "      <td>C5441729,C5441729,C5441729,C5441729,C5441729,C...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>50286 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      subject_id        date  \\\n",
       "0       10001725  2110-04-14   \n",
       "1       10001884  2125-10-20   \n",
       "2       10001884  2125-10-27   \n",
       "3       10001884  2125-12-03   \n",
       "4       10001884  2125-12-27   \n",
       "...          ...         ...   \n",
       "50281   19757198  2189-09-12   \n",
       "50282   19757198  2191-05-26   \n",
       "50283   19757198  2193-06-23   \n",
       "50284   19757198  2194-02-19   \n",
       "50285   19757198  2194-10-02   \n",
       "\n",
       "                                                note_cui  \n",
       "0      C5441729,C5441729,C5441729,C5441729,C5441729,C...  \n",
       "1      C5441729,C5441729,C5441729,C5441729,C5441729,C...  \n",
       "2      C5441729,C5441729,C5441729,C5441729,C5441729,C...  \n",
       "3      C5441729,C5441729,C5441729,C5441729,C5441729,C...  \n",
       "4      C5441729,C5441729,C5441729,C5441729,C5441729,C...  \n",
       "...                                                  ...  \n",
       "50281  C5441729,C5441729,C5441729,C5441729,C5441729,C...  \n",
       "50282  C5441729,C5441729,C5441729,C5441729,C5441729,C...  \n",
       "50283  C5441729,C5441729,C5441729,C5441729,C5441729,C...  \n",
       "50284  C5441729,C5441729,C5441729,C5441729,C5441729,C...  \n",
       "50285  C5441729,C5441729,C5441729,C5441729,C5441729,C...  \n",
       "\n",
       "[50286 rows x 3 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "asthma_cohort_missing_notes = pd.DataFrame({'Column': asthma_cohort_notes.columns,'Missing_Values': asthma_cohort_notes.isna().sum()})\n",
    "display(asthma_cohort_missing_notes)\n",
    "\n",
    "# Selecting only columns of interest\n",
    "\n",
    "asthma_cohort_notes=asthma_cohort_notes[['subject_id','charttime','note_cui']]\n",
    "display(asthma_cohort_notes)\n",
    "\n",
    "# Rename the time columns to be consisten with other datasets\n",
    "asthma_cohort_notes = asthma_cohort_notes.rename(columns = {\"charttime\":\"date\"})\n",
    "display(asthma_cohort_notes)\n",
    "\n",
    "# Cleaning the dates\n",
    "asthma_cohort_notes[\"date\"] = asthma_cohort_notes[\"date\"].str[:10]\n",
    "display(asthma_cohort_notes)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "e6c6effb-56ef-4843-b541-3384c78f4c12",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>subject_id</th>\n",
       "      <th>date</th>\n",
       "      <th>note_cui</th>\n",
       "      <th>note_cui_list</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10001725</td>\n",
       "      <td>2110-04-14</td>\n",
       "      <td>C5441729,C5441729,C5441729,C5441729,C5441729,C...</td>\n",
       "      <td>[C5441729, C5441729, C5441729, C5441729, C5441...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10001884</td>\n",
       "      <td>2125-10-20</td>\n",
       "      <td>C5441729,C5441729,C5441729,C5441729,C5441729,C...</td>\n",
       "      <td>[C5441729, C5441729, C5441729, C5441729, C5441...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10001884</td>\n",
       "      <td>2125-10-27</td>\n",
       "      <td>C5441729,C5441729,C5441729,C5441729,C5441729,C...</td>\n",
       "      <td>[C5441729, C5441729, C5441729, C5441729, C5441...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10001884</td>\n",
       "      <td>2125-12-03</td>\n",
       "      <td>C5441729,C5441729,C5441729,C5441729,C5441729,C...</td>\n",
       "      <td>[C5441729, C5441729, C5441729, C5441729, C5441...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10001884</td>\n",
       "      <td>2125-12-27</td>\n",
       "      <td>C5441729,C5441729,C5441729,C5441729,C5441729,C...</td>\n",
       "      <td>[C5441729, C5441729, C5441729, C5441729, C5441...</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>50281</th>\n",
       "      <td>19757198</td>\n",
       "      <td>2189-09-12</td>\n",
       "      <td>C5441729,C5441729,C5441729,C5441729,C5441729,C...</td>\n",
       "      <td>[C5441729, C5441729, C5441729, C5441729, C5441...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50282</th>\n",
       "      <td>19757198</td>\n",
       "      <td>2191-05-26</td>\n",
       "      <td>C5441729,C5441729,C5441729,C5441729,C5441729,C...</td>\n",
       "      <td>[C5441729, C5441729, C5441729, C5441729, C5441...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50283</th>\n",
       "      <td>19757198</td>\n",
       "      <td>2193-06-23</td>\n",
       "      <td>C5441729,C5441729,C5441729,C5441729,C5441729,C...</td>\n",
       "      <td>[C5441729, C5441729, C5441729, C5441729, C5441...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50284</th>\n",
       "      <td>19757198</td>\n",
       "      <td>2194-02-19</td>\n",
       "      <td>C5441729,C5441729,C5441729,C5441729,C5441729,C...</td>\n",
       "      <td>[C5441729, C5441729, C5441729, C5441729, C5441...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50285</th>\n",
       "      <td>19757198</td>\n",
       "      <td>2194-10-02</td>\n",
       "      <td>C5441729,C5441729,C5441729,C5441729,C5441729,C...</td>\n",
       "      <td>[C5441729, C5441729, C5441729, C5441729, C5441...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>50286 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      subject_id        date  \\\n",
       "0       10001725  2110-04-14   \n",
       "1       10001884  2125-10-20   \n",
       "2       10001884  2125-10-27   \n",
       "3       10001884  2125-12-03   \n",
       "4       10001884  2125-12-27   \n",
       "...          ...         ...   \n",
       "50281   19757198  2189-09-12   \n",
       "50282   19757198  2191-05-26   \n",
       "50283   19757198  2193-06-23   \n",
       "50284   19757198  2194-02-19   \n",
       "50285   19757198  2194-10-02   \n",
       "\n",
       "                                                note_cui  \\\n",
       "0      C5441729,C5441729,C5441729,C5441729,C5441729,C...   \n",
       "1      C5441729,C5441729,C5441729,C5441729,C5441729,C...   \n",
       "2      C5441729,C5441729,C5441729,C5441729,C5441729,C...   \n",
       "3      C5441729,C5441729,C5441729,C5441729,C5441729,C...   \n",
       "4      C5441729,C5441729,C5441729,C5441729,C5441729,C...   \n",
       "...                                                  ...   \n",
       "50281  C5441729,C5441729,C5441729,C5441729,C5441729,C...   \n",
       "50282  C5441729,C5441729,C5441729,C5441729,C5441729,C...   \n",
       "50283  C5441729,C5441729,C5441729,C5441729,C5441729,C...   \n",
       "50284  C5441729,C5441729,C5441729,C5441729,C5441729,C...   \n",
       "50285  C5441729,C5441729,C5441729,C5441729,C5441729,C...   \n",
       "\n",
       "                                           note_cui_list  \n",
       "0      [C5441729, C5441729, C5441729, C5441729, C5441...  \n",
       "1      [C5441729, C5441729, C5441729, C5441729, C5441...  \n",
       "2      [C5441729, C5441729, C5441729, C5441729, C5441...  \n",
       "3      [C5441729, C5441729, C5441729, C5441729, C5441...  \n",
       "4      [C5441729, C5441729, C5441729, C5441729, C5441...  \n",
       "...                                                  ...  \n",
       "50281  [C5441729, C5441729, C5441729, C5441729, C5441...  \n",
       "50282  [C5441729, C5441729, C5441729, C5441729, C5441...  \n",
       "50283  [C5441729, C5441729, C5441729, C5441729, C5441...  \n",
       "50284  [C5441729, C5441729, C5441729, C5441729, C5441...  \n",
       "50285  [C5441729, C5441729, C5441729, C5441729, C5441...  \n",
       "\n",
       "[50286 rows x 4 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Convert the note_cui column to cui list so we can expand them in next step\n",
    "\n",
    "asthma_cohort_notes['note_cui_list'] = asthma_cohort_notes['note_cui'].apply(lambda x: x.split(',') if x else None)\n",
    "display(asthma_cohort_notes)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "abc42d26-f43a-4126-bae5-d6410d956500",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>subject_id</th>\n",
       "      <th>date</th>\n",
       "      <th>note_cui</th>\n",
       "      <th>note_cui_list</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10001725</td>\n",
       "      <td>2110-04-14</td>\n",
       "      <td>C5441729,C5441729,C5441729,C5441729,C5441729,C...</td>\n",
       "      <td>[C5441729, C5441729, C5441729, C5441729, C5441...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10001884</td>\n",
       "      <td>2125-10-20</td>\n",
       "      <td>C5441729,C5441729,C5441729,C5441729,C5441729,C...</td>\n",
       "      <td>[C5441729, C5441729, C5441729, C5441729, C5441...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10001884</td>\n",
       "      <td>2125-10-27</td>\n",
       "      <td>C5441729,C5441729,C5441729,C5441729,C5441729,C...</td>\n",
       "      <td>[C5441729, C5441729, C5441729, C5441729, C5441...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10001884</td>\n",
       "      <td>2125-12-03</td>\n",
       "      <td>C5441729,C5441729,C5441729,C5441729,C5441729,C...</td>\n",
       "      <td>[C5441729, C5441729, C5441729, C5441729, C5441...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10001884</td>\n",
       "      <td>2125-12-27</td>\n",
       "      <td>C5441729,C5441729,C5441729,C5441729,C5441729,C...</td>\n",
       "      <td>[C5441729, C5441729, C5441729, C5441729, C5441...</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>50281</th>\n",
       "      <td>19757198</td>\n",
       "      <td>2189-09-12</td>\n",
       "      <td>C5441729,C5441729,C5441729,C5441729,C5441729,C...</td>\n",
       "      <td>[C5441729, C5441729, C5441729, C5441729, C5441...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50282</th>\n",
       "      <td>19757198</td>\n",
       "      <td>2191-05-26</td>\n",
       "      <td>C5441729,C5441729,C5441729,C5441729,C5441729,C...</td>\n",
       "      <td>[C5441729, C5441729, C5441729, C5441729, C5441...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50283</th>\n",
       "      <td>19757198</td>\n",
       "      <td>2193-06-23</td>\n",
       "      <td>C5441729,C5441729,C5441729,C5441729,C5441729,C...</td>\n",
       "      <td>[C5441729, C5441729, C5441729, C5441729, C5441...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50284</th>\n",
       "      <td>19757198</td>\n",
       "      <td>2194-02-19</td>\n",
       "      <td>C5441729,C5441729,C5441729,C5441729,C5441729,C...</td>\n",
       "      <td>[C5441729, C5441729, C5441729, C5441729, C5441...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50285</th>\n",
       "      <td>19757198</td>\n",
       "      <td>2194-10-02</td>\n",
       "      <td>C5441729,C5441729,C5441729,C5441729,C5441729,C...</td>\n",
       "      <td>[C5441729, C5441729, C5441729, C5441729, C5441...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>50286 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      subject_id        date  \\\n",
       "0       10001725  2110-04-14   \n",
       "1       10001884  2125-10-20   \n",
       "2       10001884  2125-10-27   \n",
       "3       10001884  2125-12-03   \n",
       "4       10001884  2125-12-27   \n",
       "...          ...         ...   \n",
       "50281   19757198  2189-09-12   \n",
       "50282   19757198  2191-05-26   \n",
       "50283   19757198  2193-06-23   \n",
       "50284   19757198  2194-02-19   \n",
       "50285   19757198  2194-10-02   \n",
       "\n",
       "                                                note_cui  \\\n",
       "0      C5441729,C5441729,C5441729,C5441729,C5441729,C...   \n",
       "1      C5441729,C5441729,C5441729,C5441729,C5441729,C...   \n",
       "2      C5441729,C5441729,C5441729,C5441729,C5441729,C...   \n",
       "3      C5441729,C5441729,C5441729,C5441729,C5441729,C...   \n",
       "4      C5441729,C5441729,C5441729,C5441729,C5441729,C...   \n",
       "...                                                  ...   \n",
       "50281  C5441729,C5441729,C5441729,C5441729,C5441729,C...   \n",
       "50282  C5441729,C5441729,C5441729,C5441729,C5441729,C...   \n",
       "50283  C5441729,C5441729,C5441729,C5441729,C5441729,C...   \n",
       "50284  C5441729,C5441729,C5441729,C5441729,C5441729,C...   \n",
       "50285  C5441729,C5441729,C5441729,C5441729,C5441729,C...   \n",
       "\n",
       "                                           note_cui_list  \n",
       "0      [C5441729, C5441729, C5441729, C5441729, C5441...  \n",
       "1      [C5441729, C5441729, C5441729, C5441729, C5441...  \n",
       "2      [C5441729, C5441729, C5441729, C5441729, C5441...  \n",
       "3      [C5441729, C5441729, C5441729, C5441729, C5441...  \n",
       "4      [C5441729, C5441729, C5441729, C5441729, C5441...  \n",
       "...                                                  ...  \n",
       "50281  [C5441729, C5441729, C5441729, C5441729, C5441...  \n",
       "50282  [C5441729, C5441729, C5441729, C5441729, C5441...  \n",
       "50283  [C5441729, C5441729, C5441729, C5441729, C5441...  \n",
       "50284  [C5441729, C5441729, C5441729, C5441729, C5441...  \n",
       "50285  [C5441729, C5441729, C5441729, C5441729, C5441...  \n",
       "\n",
       "[50286 rows x 4 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "asthma_cohort_notes.dropna(inplace=True)\n",
    "display(asthma_cohort_notes)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "681d41ed-c391-4512-bcdb-3d29e022876a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>subject_id</th>\n",
       "      <th>date</th>\n",
       "      <th>note_cui_list</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10001725</td>\n",
       "      <td>2110-04-14</td>\n",
       "      <td>C5441729</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10001725</td>\n",
       "      <td>2110-04-14</td>\n",
       "      <td>C5441729</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10001725</td>\n",
       "      <td>2110-04-14</td>\n",
       "      <td>C5441729</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10001725</td>\n",
       "      <td>2110-04-14</td>\n",
       "      <td>C5441729</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10001725</td>\n",
       "      <td>2110-04-14</td>\n",
       "      <td>C5441729</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50285</th>\n",
       "      <td>19757198</td>\n",
       "      <td>2194-10-02</td>\n",
       "      <td>C5441729</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50285</th>\n",
       "      <td>19757198</td>\n",
       "      <td>2194-10-02</td>\n",
       "      <td>C5441729</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50285</th>\n",
       "      <td>19757198</td>\n",
       "      <td>2194-10-02</td>\n",
       "      <td>C5441729</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50285</th>\n",
       "      <td>19757198</td>\n",
       "      <td>2194-10-02</td>\n",
       "      <td>C5441729</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50285</th>\n",
       "      <td>19757198</td>\n",
       "      <td>2194-10-02</td>\n",
       "      <td>C5441729</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>42090077 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      subject_id        date note_cui_list\n",
       "0       10001725  2110-04-14      C5441729\n",
       "0       10001725  2110-04-14      C5441729\n",
       "0       10001725  2110-04-14      C5441729\n",
       "0       10001725  2110-04-14      C5441729\n",
       "0       10001725  2110-04-14      C5441729\n",
       "...          ...         ...           ...\n",
       "50285   19757198  2194-10-02      C5441729\n",
       "50285   19757198  2194-10-02      C5441729\n",
       "50285   19757198  2194-10-02      C5441729\n",
       "50285   19757198  2194-10-02      C5441729\n",
       "50285   19757198  2194-10-02      C5441729\n",
       "\n",
       "[42090077 rows x 3 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "#Expand the note_cui_list column to have one CUI per row\n",
    "\n",
    "asthma_cohort_notes = asthma_cohort_notes[['subject_id','date','note_cui_list']]\n",
    "asthma_cohort_notes = asthma_cohort_notes.explode('note_cui_list')\n",
    "display(asthma_cohort_notes)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "9c616205-2e6c-4180-8ddc-3614c5d5d221",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>subject_id</th>\n",
       "      <th>date</th>\n",
       "      <th>cui</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10001725</td>\n",
       "      <td>2110-04-14</td>\n",
       "      <td>C5441729</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10001725</td>\n",
       "      <td>2110-04-14</td>\n",
       "      <td>C0016860</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10001725</td>\n",
       "      <td>2110-04-14</td>\n",
       "      <td>C0205082</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10001725</td>\n",
       "      <td>2110-04-14</td>\n",
       "      <td>C0344315</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10001725</td>\n",
       "      <td>2110-04-14</td>\n",
       "      <td>C0013604</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50285</th>\n",
       "      <td>19757198</td>\n",
       "      <td>2194-10-02</td>\n",
       "      <td>C5201148</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50285</th>\n",
       "      <td>19757198</td>\n",
       "      <td>2194-10-02</td>\n",
       "      <td>C4084203</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50285</th>\n",
       "      <td>19757198</td>\n",
       "      <td>2194-10-02</td>\n",
       "      <td>C0033095</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50285</th>\n",
       "      <td>19757198</td>\n",
       "      <td>2194-10-02</td>\n",
       "      <td>C0010957</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50285</th>\n",
       "      <td>19757198</td>\n",
       "      <td>2194-10-02</td>\n",
       "      <td>C0011847</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>729164 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      subject_id        date       cui\n",
       "0       10001725  2110-04-14  C5441729\n",
       "0       10001725  2110-04-14  C0016860\n",
       "0       10001725  2110-04-14  C0205082\n",
       "0       10001725  2110-04-14  C0344315\n",
       "0       10001725  2110-04-14  C0013604\n",
       "...          ...         ...       ...\n",
       "50285   19757198  2194-10-02  C5201148\n",
       "50285   19757198  2194-10-02  C4084203\n",
       "50285   19757198  2194-10-02  C0033095\n",
       "50285   19757198  2194-10-02  C0010957\n",
       "50285   19757198  2194-10-02  C0011847\n",
       "\n",
       "[729164 rows x 3 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Drop Duplicates\n",
    "\n",
    "asthma_cohort_notes.drop_duplicates(inplace=True)\n",
    "asthma_cohort_notes=asthma_cohort_notes.rename(columns={\"note_cui_list\":\"cui\"})\n",
    "display(asthma_cohort_notes)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "e176ff2f-d204-4cb0-82f2-4d0e081da336",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>subject_id</th>\n",
       "      <th>cui</th>\n",
       "      <th>counts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10001725</td>\n",
       "      <td>C0001645</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10001725</td>\n",
       "      <td>C0004238</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10001725</td>\n",
       "      <td>C0013404</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10001725</td>\n",
       "      <td>C0013604</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10001725</td>\n",
       "      <td>C0016860</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>297382</th>\n",
       "      <td>19999442</td>\n",
       "      <td>C4554100</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>297383</th>\n",
       "      <td>19999442</td>\n",
       "      <td>C4554645</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>297384</th>\n",
       "      <td>19999442</td>\n",
       "      <td>C5201148</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>297385</th>\n",
       "      <td>19999442</td>\n",
       "      <td>C5203119</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>297386</th>\n",
       "      <td>19999442</td>\n",
       "      <td>C5441729</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>297387 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       subject_id       cui  counts\n",
       "0        10001725  C0001645       1\n",
       "1        10001725  C0004238       1\n",
       "2        10001725  C0013404       1\n",
       "3        10001725  C0013604       1\n",
       "4        10001725  C0016860       1\n",
       "...           ...       ...     ...\n",
       "297382   19999442  C4554100       1\n",
       "297383   19999442  C4554645       1\n",
       "297384   19999442  C5201148       1\n",
       "297385   19999442  C5203119       1\n",
       "297386   19999442  C5441729       2\n",
       "\n",
       "[297387 rows x 3 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "cui_counts_per_patient = asthma_cohort_notes.groupby(['subject_id', 'cui']).size().reset_index(name='counts')\n",
    "display(cui_counts_per_patient)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "f49441ac-6447-4e02-9d02-89e361847111",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>cui</th>\n",
       "      <th>C0001645</th>\n",
       "      <th>C0002598</th>\n",
       "      <th>C0002962</th>\n",
       "      <th>C0003015</th>\n",
       "      <th>C0003811</th>\n",
       "      <th>C0004093</th>\n",
       "      <th>C0004238</th>\n",
       "      <th>C0006376</th>\n",
       "      <th>C0007166</th>\n",
       "      <th>C0007193</th>\n",
       "      <th>...</th>\n",
       "      <th>C4554158</th>\n",
       "      <th>C4554645</th>\n",
       "      <th>C4699158</th>\n",
       "      <th>C4723804</th>\n",
       "      <th>C4759845</th>\n",
       "      <th>C4759928</th>\n",
       "      <th>C5201148</th>\n",
       "      <th>C5203119</th>\n",
       "      <th>C5424789</th>\n",
       "      <th>C5441729</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>subject_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>10001725</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10001884</th>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10002800</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10003019</th>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10004296</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19992803</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19997072</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19997887</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19998350</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19999442</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>14958 rows × 229 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "cui         C0001645  C0002598  C0002962  C0003015  C0003811  C0004093  \\\n",
       "subject_id                                                               \n",
       "10001725           1         0         0         0         0         0   \n",
       "10001884           1         4         0         0         1         0   \n",
       "10002800           0         0         0         0         0         0   \n",
       "10003019           4         0         0         0         2         0   \n",
       "10004296           0         0         0         0         0         0   \n",
       "...              ...       ...       ...       ...       ...       ...   \n",
       "19992803           0         0         0         0         0         0   \n",
       "19997072           0         0         0         0         0         0   \n",
       "19997887           0         0         0         0         0         0   \n",
       "19998350           0         0         0         0         0         0   \n",
       "19999442           0         0         0         0         0         0   \n",
       "\n",
       "cui         C0004238  C0006376  C0007166  C0007193  ...  C4554158  C4554645  \\\n",
       "subject_id                                          ...                       \n",
       "10001725           1         0         0         0  ...         0         0   \n",
       "10001884           7         0         0         0  ...         0         3   \n",
       "10002800           0         0         0         0  ...         0         0   \n",
       "10003019           1         0         0         0  ...         0         3   \n",
       "10004296           0         0         0         0  ...         0         0   \n",
       "...              ...       ...       ...       ...  ...       ...       ...   \n",
       "19992803           0         0         0         0  ...         0         0   \n",
       "19997072           0         0         0         0  ...         0         0   \n",
       "19997887           0         0         0         0  ...         0         0   \n",
       "19998350           0         0         0         0  ...         0         1   \n",
       "19999442           0         0         0         0  ...         0         1   \n",
       "\n",
       "cui         C4699158  C4723804  C4759845  C4759928  C5201148  C5203119  \\\n",
       "subject_id                                                               \n",
       "10001725           0         0         0         0         0         1   \n",
       "10001884           1         0         0         0         3         8   \n",
       "10002800           0         0         1         0         3         2   \n",
       "10003019           0         0         0         0         7         6   \n",
       "10004296           0         0         0         0         0         0   \n",
       "...              ...       ...       ...       ...       ...       ...   \n",
       "19992803           0         0         0         0         0         1   \n",
       "19997072           0         0         0         0         1         1   \n",
       "19997887           0         0         0         2         1         2   \n",
       "19998350           0         0         0         2         1         2   \n",
       "19999442           0         0         0         0         1         1   \n",
       "\n",
       "cui         C5424789  C5441729  \n",
       "subject_id                      \n",
       "10001725           0         1  \n",
       "10001884           0        15  \n",
       "10002800           0         3  \n",
       "10003019           0        11  \n",
       "10004296           0         1  \n",
       "...              ...       ...  \n",
       "19992803           0         1  \n",
       "19997072           0         2  \n",
       "19997887           0         2  \n",
       "19998350           0         2  \n",
       "19999442           0         2  \n",
       "\n",
       "[14958 rows x 229 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "cui_counts_per_patient_matrixformat = cui_counts_per_patient.pivot_table(index='subject_id', columns='cui', values='counts', fill_value=0)\n",
    "display(cui_counts_per_patient_matrixformat)\n",
    "\n",
    "\n",
    "cui_counts_per_patient_matrixformat.to_csv(os.path.join(cohort_aggregateddata_nlp_directory,\"CUI_counts.csv\"),index=None)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5d58ce67-b7a2-412e-85d7-89415ea133d7",
   "metadata": {},
   "source": [
    "# List of Resources for Downstream Analysis\n",
    "\n",
    "1. [ONCE](https://shiny.parse-health.org/ONCE/): A feature generation app that identifies related nlp and codified features based on an input or target disease. Eg: PheCodes and CUIs\n",
    "   \n",
    "2. [KESER Network](https://shiny.parse-health.org/kesernetwork-linkage/): Tool to identify and visualize codified concepts relevant to diseases, medications, and procedures (PheCodes, RxNorm)\n",
    "\n",
    "3. [NILE](https://celehs.hms.harvard.edu/software/NILE.html): NLP tool for fast and efficient processing of clinical notes to UMLS cuis. You will need an UMLS Licence to use this"
   ]
  }
 ],
 "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.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}