GCAF_Preprocessing.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "971e4de4",
   "metadata": {},
   "outputs": [],
   "source": [
    "'''\n",
    "@File:   GCAF_Preprocessing.ipynb\n",
    "@ Author: Asra Aslam\n",
    "@ Create Time: 2024-09-17 11:26:39\n",
    "@ Modified time: 2024-09-17 12:09:44\n",
    "@ License :   (C)Copyright Asra Aslam DynAIRX\n",
    "@ Description: GCAF_Preprocessing\n",
    "'''\n",
    "\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt \n",
    "import numpy as np\n",
    "import seaborn as sns\n",
    "from sklearn.linear_model import LogisticRegression\n",
    "from sklearn.model_selection import train_test_split\n",
    "\n",
    "from sklearn.metrics import classification_report, recall_score, precision_score, accuracy_score\n",
    "from sklearn.metrics import precision_recall_curve\n",
    "from sklearn.metrics import f1_score\n",
    "from sklearn.metrics import auc\n",
    "from sklearn.metrics import roc_curve\n",
    "from sklearn.metrics import confusion_matrix\n",
    "\n",
    "from lifelines import KaplanMeierFitter"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "2b502036",
   "metadata": {},
   "outputs": [],
   "source": [
    "filepath_snomed=\"Input/Mappings/Multimorbidity_Codelist_16.11.2021.xlsx\"\n",
    "filepath_Baseline1_codelist=\"Input/Baselines/Baseline1_codelist.csv\"\n",
    "filepath_snomed_readcode=\"Intermediate_Input_Output/Snomed_Generated_for_Baseline1_codelist.csv\"\n",
    "filepath_missing_snomed=\"Intermediate_Input_Output/Missing_Snomed_in_Mapping_for_Baseline1_codelist.csv\""
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "1510d2ea",
   "metadata": {},
   "source": [
    "# Analyzing Lauren's code file which has ReadCodes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "b58681bf",
   "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>Read_code</th>\n",
       "      <th>Term</th>\n",
       "      <th>Disease</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7A11000</td>\n",
       "      <td>Emerg repl aneurysm bifurc aorta by anast aort...</td>\n",
       "      <td>Abdominal Aortic Aneurysm</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7A11211</td>\n",
       "      <td>Y graft of abdominal Aortic aneurysm (emergency)</td>\n",
       "      <td>Abdominal Aortic Aneurysm</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7A13.00</td>\n",
       "      <td>Emergency replacement of aneurysmal segment of...</td>\n",
       "      <td>Abdominal Aortic Aneurysm</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7A13.11</td>\n",
       "      <td>Emergency repair of aortic aneurysm</td>\n",
       "      <td>Abdominal Aortic Aneurysm</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>7A13411</td>\n",
       "      <td>Tube graft abdominal Aortic aneurysm (emergency)</td>\n",
       "      <td>Abdominal Aortic Aneurysm</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5719</th>\n",
       "      <td>C32y.00</td>\n",
       "      <td>Other disorders of lipoid metabolism</td>\n",
       "      <td>Lipid Disorder</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5720</th>\n",
       "      <td>C32yz00</td>\n",
       "      <td>Other disorder of lipoid metabolism NOS</td>\n",
       "      <td>Lipid Disorder</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5721</th>\n",
       "      <td>C32z.00</td>\n",
       "      <td>Disorder of lipoid metabolism NOS</td>\n",
       "      <td>Lipid Disorder</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5722</th>\n",
       "      <td>Cyu8D00</td>\n",
       "      <td>[X]Other hyperlipidaemia</td>\n",
       "      <td>Lipid Disorder</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5723</th>\n",
       "      <td>Cyu8E00</td>\n",
       "      <td>[X]Other disorders of lipoprotein metabolism</td>\n",
       "      <td>Lipid Disorder</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5724 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     Read_code                                               Term  \\\n",
       "0      7A11000  Emerg repl aneurysm bifurc aorta by anast aort...   \n",
       "1      7A11211   Y graft of abdominal Aortic aneurysm (emergency)   \n",
       "2      7A13.00  Emergency replacement of aneurysmal segment of...   \n",
       "3      7A13.11                Emergency repair of aortic aneurysm   \n",
       "4      7A13411   Tube graft abdominal Aortic aneurysm (emergency)   \n",
       "...        ...                                                ...   \n",
       "5719   C32y.00               Other disorders of lipoid metabolism   \n",
       "5720   C32yz00            Other disorder of lipoid metabolism NOS   \n",
       "5721   C32z.00                  Disorder of lipoid metabolism NOS   \n",
       "5722   Cyu8D00                           [X]Other hyperlipidaemia   \n",
       "5723   Cyu8E00       [X]Other disorders of lipoprotein metabolism   \n",
       "\n",
       "                        Disease  \n",
       "0     Abdominal Aortic Aneurysm  \n",
       "1     Abdominal Aortic Aneurysm  \n",
       "2     Abdominal Aortic Aneurysm  \n",
       "3     Abdominal Aortic Aneurysm  \n",
       "4     Abdominal Aortic Aneurysm  \n",
       "...                         ...  \n",
       "5719             Lipid Disorder  \n",
       "5720             Lipid Disorder  \n",
       "5721             Lipid Disorder  \n",
       "5722             Lipid Disorder  \n",
       "5723             Lipid Disorder  \n",
       "\n",
       "[5724 rows x 3 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_Baseline1_codelist = pd.read_csv(filepath_Baseline1_codelist) \n",
    "df_Baseline1_codelist"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "977a02b8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Read_code', 'Term', 'Disease'], dtype='object')"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_Baseline1_codelist.columns"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "e180f8cf",
   "metadata": {},
   "source": [
    "# Analyzing Pieta's file which has Snomed Codes with ReadCodes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "907f38eb",
   "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>CleansedReadCode</th>\n",
       "      <th>Disease</th>\n",
       "      <th>MedCodeId</th>\n",
       "      <th>Term</th>\n",
       "      <th>OriginalReadCode</th>\n",
       "      <th>SnomedCTConceptId</th>\n",
       "      <th>SnomedCTDescriptionId</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7A11000</td>\n",
       "      <td>ABDOMINALAORTICANEURYSM</td>\n",
       "      <td>639641000006113</td>\n",
       "      <td>Emerg repl aneurysm bifurc aorta by anast aort...</td>\n",
       "      <td>7A110</td>\n",
       "      <td>175283003</td>\n",
       "      <td>639641000006113</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7A11211</td>\n",
       "      <td>ABDOMINALAORTICANEURYSM</td>\n",
       "      <td>349689013</td>\n",
       "      <td>Y graft of abdominal Aortic aneurysm (emergency)</td>\n",
       "      <td>7A112-1</td>\n",
       "      <td>233374003</td>\n",
       "      <td>349689013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7A13.00</td>\n",
       "      <td>ABDOMINALAORTICANEURYSM</td>\n",
       "      <td>271446014</td>\n",
       "      <td>Emergency replacement of aneurysmal segment of...</td>\n",
       "      <td>7A13</td>\n",
       "      <td>175297006</td>\n",
       "      <td>271446014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7A13.11</td>\n",
       "      <td>ABDOMINALAORTICANEURYSM</td>\n",
       "      <td>271447017</td>\n",
       "      <td>Emergency repair of aortic aneurysm</td>\n",
       "      <td>7A13-1</td>\n",
       "      <td>175297006</td>\n",
       "      <td>271447017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>7A13411</td>\n",
       "      <td>ABDOMINALAORTICANEURYSM</td>\n",
       "      <td>349694013</td>\n",
       "      <td>Tube graft abdominal Aortic aneurysm (emergency)</td>\n",
       "      <td>7A134-1</td>\n",
       "      <td>233377005</td>\n",
       "      <td>349694013</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>5719</th>\n",
       "      <td>C32y.00</td>\n",
       "      <td>LIPIDDISORDER</td>\n",
       "      <td>293342011</td>\n",
       "      <td>Other disorders of lipoid metabolism</td>\n",
       "      <td>C32y</td>\n",
       "      <td>267431006</td>\n",
       "      <td>32501000006111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5720</th>\n",
       "      <td>C32yz00</td>\n",
       "      <td>LIPIDDISORDER</td>\n",
       "      <td>293353017</td>\n",
       "      <td>Other disorder of lipoid metabolism NOS</td>\n",
       "      <td>C32yz</td>\n",
       "      <td>267431006</td>\n",
       "      <td>32311000006115</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5721</th>\n",
       "      <td>C32z.00</td>\n",
       "      <td>LIPIDDISORDER</td>\n",
       "      <td>293354011</td>\n",
       "      <td>Disorder of lipoid metabolism NOS</td>\n",
       "      <td>C32z</td>\n",
       "      <td>267431006</td>\n",
       "      <td>624051000006117</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5722</th>\n",
       "      <td>Cyu8D00</td>\n",
       "      <td>LIPIDDISORDER</td>\n",
       "      <td>293821018</td>\n",
       "      <td>[X]Other hyperlipidaemia</td>\n",
       "      <td>Cyu8D</td>\n",
       "      <td>55822004</td>\n",
       "      <td>410871000006119</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5723</th>\n",
       "      <td>Cyu8E00</td>\n",
       "      <td>LIPIDDISORDER</td>\n",
       "      <td>293823015</td>\n",
       "      <td>[X]Other disorders of lipoprotein metabolism</td>\n",
       "      <td>Cyu8E</td>\n",
       "      <td>238037008</td>\n",
       "      <td>409301000006110</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5724 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     CleansedReadCode                  Disease        MedCodeId  \\\n",
       "0             7A11000  ABDOMINALAORTICANEURYSM  639641000006113   \n",
       "1             7A11211  ABDOMINALAORTICANEURYSM        349689013   \n",
       "2             7A13.00  ABDOMINALAORTICANEURYSM        271446014   \n",
       "3             7A13.11  ABDOMINALAORTICANEURYSM        271447017   \n",
       "4             7A13411  ABDOMINALAORTICANEURYSM        349694013   \n",
       "...               ...                      ...              ...   \n",
       "5719          C32y.00            LIPIDDISORDER        293342011   \n",
       "5720          C32yz00            LIPIDDISORDER        293353017   \n",
       "5721          C32z.00            LIPIDDISORDER        293354011   \n",
       "5722          Cyu8D00            LIPIDDISORDER        293821018   \n",
       "5723          Cyu8E00            LIPIDDISORDER        293823015   \n",
       "\n",
       "                                                   Term OriginalReadCode  \\\n",
       "0     Emerg repl aneurysm bifurc aorta by anast aort...            7A110   \n",
       "1      Y graft of abdominal Aortic aneurysm (emergency)          7A112-1   \n",
       "2     Emergency replacement of aneurysmal segment of...             7A13   \n",
       "3                   Emergency repair of aortic aneurysm           7A13-1   \n",
       "4      Tube graft abdominal Aortic aneurysm (emergency)          7A134-1   \n",
       "...                                                 ...              ...   \n",
       "5719               Other disorders of lipoid metabolism             C32y   \n",
       "5720            Other disorder of lipoid metabolism NOS            C32yz   \n",
       "5721                  Disorder of lipoid metabolism NOS             C32z   \n",
       "5722                           [X]Other hyperlipidaemia            Cyu8D   \n",
       "5723       [X]Other disorders of lipoprotein metabolism            Cyu8E   \n",
       "\n",
       "     SnomedCTConceptId SnomedCTDescriptionId  \n",
       "0            175283003       639641000006113  \n",
       "1            233374003             349689013  \n",
       "2            175297006             271446014  \n",
       "3            175297006             271447017  \n",
       "4            233377005             349694013  \n",
       "...                ...                   ...  \n",
       "5719         267431006        32501000006111  \n",
       "5720         267431006        32311000006115  \n",
       "5721         267431006       624051000006117  \n",
       "5722          55822004       410871000006119  \n",
       "5723         238037008       409301000006110  \n",
       "\n",
       "[5724 rows x 7 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_snomed = pd.read_excel(filepath_snomed, sheet_name=0, dtype={'SnomedCTConceptId': str, 'SnomedCTDescriptionId': str, 'MedCodeId': str})\n",
    "df_snomed"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "1bf1a903",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "CleansedReadCode         object\n",
       "Disease                  object\n",
       "MedCodeId                object\n",
       "Term                     object\n",
       "OriginalReadCode         object\n",
       "SnomedCTConceptId        object\n",
       "SnomedCTDescriptionId    object\n",
       "dtype: object"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_snomed.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "32cc3ddf",
   "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>CleansedReadCode</th>\n",
       "      <th>SnomedCTConceptId</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7A11000</td>\n",
       "      <td>175283003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7A11211</td>\n",
       "      <td>233374003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7A13.00</td>\n",
       "      <td>175297006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7A13.11</td>\n",
       "      <td>175297006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>7A13411</td>\n",
       "      <td>233377005</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  CleansedReadCode SnomedCTConceptId\n",
       "0          7A11000         175283003\n",
       "1          7A11211         233374003\n",
       "2          7A13.00         175297006\n",
       "3          7A13.11         175297006\n",
       "4          7A13411         233377005"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_snomed_readcode=df_snomed[['CleansedReadCode', 'SnomedCTConceptId']]\n",
    "df_snomed_readcode.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "e642abbc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['CleansedReadCode', 'SnomedCTConceptId'], dtype='object')"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_snomed_readcode.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "4342ac26",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Read_code', 'Term', 'Disease'], dtype='object')"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_Baseline1_codelist.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "47b4f2e4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['CleansedReadCode', 'Term', 'Disease'], dtype='object')"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_Baseline1_codelist.rename(columns={'Read_code':'CleansedReadCode'}, inplace=True)\n",
    "df_Baseline1_codelist.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "e9c8ae9f",
   "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>CleansedReadCode</th>\n",
       "      <th>Term</th>\n",
       "      <th>Disease</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7A11000</td>\n",
       "      <td>Emerg repl aneurysm bifurc aorta by anast aort...</td>\n",
       "      <td>Abdominal Aortic Aneurysm</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7A11211</td>\n",
       "      <td>Y graft of abdominal Aortic aneurysm (emergency)</td>\n",
       "      <td>Abdominal Aortic Aneurysm</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7A13.00</td>\n",
       "      <td>Emergency replacement of aneurysmal segment of...</td>\n",
       "      <td>Abdominal Aortic Aneurysm</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7A13.11</td>\n",
       "      <td>Emergency repair of aortic aneurysm</td>\n",
       "      <td>Abdominal Aortic Aneurysm</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>7A13411</td>\n",
       "      <td>Tube graft abdominal Aortic aneurysm (emergency)</td>\n",
       "      <td>Abdominal Aortic Aneurysm</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  CleansedReadCode                                               Term  \\\n",
       "0          7A11000  Emerg repl aneurysm bifurc aorta by anast aort...   \n",
       "1          7A11211   Y graft of abdominal Aortic aneurysm (emergency)   \n",
       "2          7A13.00  Emergency replacement of aneurysmal segment of...   \n",
       "3          7A13.11                Emergency repair of aortic aneurysm   \n",
       "4          7A13411   Tube graft abdominal Aortic aneurysm (emergency)   \n",
       "\n",
       "                     Disease  \n",
       "0  Abdominal Aortic Aneurysm  \n",
       "1  Abdominal Aortic Aneurysm  \n",
       "2  Abdominal Aortic Aneurysm  \n",
       "3  Abdominal Aortic Aneurysm  \n",
       "4  Abdominal Aortic Aneurysm  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_Baseline1_codelist.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "19dca207",
   "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>CleansedReadCode</th>\n",
       "      <th>Term</th>\n",
       "      <th>Disease</th>\n",
       "      <th>SnomedCTConceptId</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7A11000</td>\n",
       "      <td>Emerg repl aneurysm bifurc aorta by anast aort...</td>\n",
       "      <td>Abdominal Aortic Aneurysm</td>\n",
       "      <td>175283003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7A11211</td>\n",
       "      <td>Y graft of abdominal Aortic aneurysm (emergency)</td>\n",
       "      <td>Abdominal Aortic Aneurysm</td>\n",
       "      <td>233374003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7A13.00</td>\n",
       "      <td>Emergency replacement of aneurysmal segment of...</td>\n",
       "      <td>Abdominal Aortic Aneurysm</td>\n",
       "      <td>175297006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7A13.11</td>\n",
       "      <td>Emergency repair of aortic aneurysm</td>\n",
       "      <td>Abdominal Aortic Aneurysm</td>\n",
       "      <td>175297006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>7A13411</td>\n",
       "      <td>Tube graft abdominal Aortic aneurysm (emergency)</td>\n",
       "      <td>Abdominal Aortic Aneurysm</td>\n",
       "      <td>233377005</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  CleansedReadCode                                               Term  \\\n",
       "0          7A11000  Emerg repl aneurysm bifurc aorta by anast aort...   \n",
       "1          7A11211   Y graft of abdominal Aortic aneurysm (emergency)   \n",
       "2          7A13.00  Emergency replacement of aneurysmal segment of...   \n",
       "3          7A13.11                Emergency repair of aortic aneurysm   \n",
       "4          7A13411   Tube graft abdominal Aortic aneurysm (emergency)   \n",
       "\n",
       "                     Disease SnomedCTConceptId  \n",
       "0  Abdominal Aortic Aneurysm         175283003  \n",
       "1  Abdominal Aortic Aneurysm         233374003  \n",
       "2  Abdominal Aortic Aneurysm         175297006  \n",
       "3  Abdominal Aortic Aneurysm         175297006  \n",
       "4  Abdominal Aortic Aneurysm         233377005  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_map=pd.merge(df_Baseline1_codelist, df_snomed_readcode, on='CleansedReadCode')\n",
    "df_map.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "50527954",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Lauren's Codelist shape: (5724, 3)\n",
      "SNOMED Mapping shape: (5724, 2)\n",
      "Resultant Mapping Shape: (6108, 4)\n"
     ]
    }
   ],
   "source": [
    "print(\"Lauren's Codelist shape: {}\".format(df_Baseline1_codelist.shape))\n",
    "print(\"SNOMED Mapping shape: {}\".format(df_snomed_readcode.shape))\n",
    "print(\"Resultant Mapping Shape: {}\".format(df_map.shape))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "79753356",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True    5724\n",
       "Name: CleansedReadCode, dtype: int64"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_Baseline1_codelist['CleansedReadCode'].isin(df_snomed_readcode['CleansedReadCode']).value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "b7628408",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Lauren's Codelist shape: (5724, 3)\n",
      "SNOMED Mapping shape: (5724, 2)\n",
      "Resultant Mapping Shape: (6108, 4)\n",
      "There are 10 SnomedCTConceptId missing values in mapping.\n",
      "There are 0 Disease missing values in mapping.\n",
      "There are 10 Term missing values in mapping.\n",
      "There are 0 CleansedReadCode missing values in mapping.\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>CleansedReadCode</th>\n",
       "      <th>Term</th>\n",
       "      <th>Disease</th>\n",
       "      <th>SnomedCTConceptId</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7A11000</td>\n",
       "      <td>Emerg repl aneurysm bifurc aorta by anast aort...</td>\n",
       "      <td>Abdominal Aortic Aneurysm</td>\n",
       "      <td>175283003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7A11211</td>\n",
       "      <td>Y graft of abdominal Aortic aneurysm (emergency)</td>\n",
       "      <td>Abdominal Aortic Aneurysm</td>\n",
       "      <td>233374003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7A13.00</td>\n",
       "      <td>Emergency replacement of aneurysmal segment of...</td>\n",
       "      <td>Abdominal Aortic Aneurysm</td>\n",
       "      <td>175297006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7A13.11</td>\n",
       "      <td>Emergency repair of aortic aneurysm</td>\n",
       "      <td>Abdominal Aortic Aneurysm</td>\n",
       "      <td>175297006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>7A13411</td>\n",
       "      <td>Tube graft abdominal Aortic aneurysm (emergency)</td>\n",
       "      <td>Abdominal Aortic Aneurysm</td>\n",
       "      <td>233377005</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  CleansedReadCode                                               Term  \\\n",
       "0          7A11000  Emerg repl aneurysm bifurc aorta by anast aort...   \n",
       "1          7A11211   Y graft of abdominal Aortic aneurysm (emergency)   \n",
       "2          7A13.00  Emergency replacement of aneurysmal segment of...   \n",
       "3          7A13.11                Emergency repair of aortic aneurysm   \n",
       "4          7A13411   Tube graft abdominal Aortic aneurysm (emergency)   \n",
       "\n",
       "                     Disease SnomedCTConceptId  \n",
       "0  Abdominal Aortic Aneurysm         175283003  \n",
       "1  Abdominal Aortic Aneurysm         233374003  \n",
       "2  Abdominal Aortic Aneurysm         175297006  \n",
       "3  Abdominal Aortic Aneurysm         175297006  \n",
       "4  Abdominal Aortic Aneurysm         233377005  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_map=pd.merge(df_Baseline1_codelist, df_snomed_readcode, on='CleansedReadCode', how='left')\n",
    "print(\"Lauren's Codelist shape: {}\".format(df_Baseline1_codelist.shape))\n",
    "print(\"SNOMED Mapping shape: {}\".format(df_snomed_readcode.shape))\n",
    "print(\"Resultant Mapping Shape: {}\".format(df_map.shape))\n",
    "print(\"There are {} SnomedCTConceptId missing values in mapping.\".format(df_map['SnomedCTConceptId'].isnull().sum()))\n",
    "print(\"There are {} Disease missing values in mapping.\".format(df_map['Disease'].isnull().sum()))\n",
    "print(\"There are {} Term missing values in mapping.\".format(df_map['Term'].isnull().sum()))\n",
    "print(\"There are {} CleansedReadCode missing values in mapping.\".format(df_map['CleansedReadCode'].isnull().sum()))\n",
    "df_map.head()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "4d036c6a",
   "metadata": {},
   "source": [
    "## Analyze Missing Values and Duplicates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "dd2cac6c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Lauren's Codelist shape: (5724, 3)\n",
      "SNOMED Mapping shape: (5724, 2)\n",
      "Resultant Mapping Shape: (5724, 4)\n",
      "There are 10 SnomedCTConceptId missing values in mapping.\n",
      "There are 0 Disease missing values in mapping.\n",
      "There are 10 Term missing values in mapping.\n",
      "There are 0 CleansedReadCode missing values in mapping.\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>CleansedReadCode</th>\n",
       "      <th>Term</th>\n",
       "      <th>Disease</th>\n",
       "      <th>SnomedCTConceptId</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7A11000</td>\n",
       "      <td>Emerg repl aneurysm bifurc aorta by anast aort...</td>\n",
       "      <td>Abdominal Aortic Aneurysm</td>\n",
       "      <td>175283003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7A11211</td>\n",
       "      <td>Y graft of abdominal Aortic aneurysm (emergency)</td>\n",
       "      <td>Abdominal Aortic Aneurysm</td>\n",
       "      <td>233374003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7A13.00</td>\n",
       "      <td>Emergency replacement of aneurysmal segment of...</td>\n",
       "      <td>Abdominal Aortic Aneurysm</td>\n",
       "      <td>175297006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7A13.11</td>\n",
       "      <td>Emergency repair of aortic aneurysm</td>\n",
       "      <td>Abdominal Aortic Aneurysm</td>\n",
       "      <td>175297006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>7A13411</td>\n",
       "      <td>Tube graft abdominal Aortic aneurysm (emergency)</td>\n",
       "      <td>Abdominal Aortic Aneurysm</td>\n",
       "      <td>233377005</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  CleansedReadCode                                               Term  \\\n",
       "0          7A11000  Emerg repl aneurysm bifurc aorta by anast aort...   \n",
       "1          7A11211   Y graft of abdominal Aortic aneurysm (emergency)   \n",
       "2          7A13.00  Emergency replacement of aneurysmal segment of...   \n",
       "3          7A13.11                Emergency repair of aortic aneurysm   \n",
       "4          7A13411   Tube graft abdominal Aortic aneurysm (emergency)   \n",
       "\n",
       "                     Disease SnomedCTConceptId  \n",
       "0  Abdominal Aortic Aneurysm         175283003  \n",
       "1  Abdominal Aortic Aneurysm         233374003  \n",
       "2  Abdominal Aortic Aneurysm         175297006  \n",
       "3  Abdominal Aortic Aneurysm         175297006  \n",
       "4  Abdominal Aortic Aneurysm         233377005  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_map=df_map.drop_duplicates()\n",
    "print(\"Lauren's Codelist shape: {}\".format(df_Baseline1_codelist.shape))\n",
    "print(\"SNOMED Mapping shape: {}\".format(df_snomed_readcode.shape))\n",
    "print(\"Resultant Mapping Shape: {}\".format(df_map.shape))\n",
    "print(\"There are {} SnomedCTConceptId missing values in mapping.\".format(df_map['SnomedCTConceptId'].isnull().sum()))\n",
    "print(\"There are {} Disease missing values in mapping.\".format(df_map['Disease'].isnull().sum()))\n",
    "print(\"There are {} Term missing values in mapping.\".format(df_map['Term'].isnull().sum()))\n",
    "print(\"There are {} CleansedReadCode missing values in mapping.\".format(df_map['CleansedReadCode'].isnull().sum()))\n",
    "df_map.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "c0c30d59",
   "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>CleansedReadCode</th>\n",
       "      <th>Term</th>\n",
       "      <th>Disease</th>\n",
       "      <th>SnomedCTConceptId</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1619</th>\n",
       "      <td>na</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Chronic Kidney Disease</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5107</th>\n",
       "      <td>3355</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Dermatitis atopic contact</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5318</th>\n",
       "      <td>1594</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Female genital Prolapse</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5548</th>\n",
       "      <td>ZRq8.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Lupus Erythematosus</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5549</th>\n",
       "      <td>ZRq8.11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Lupus Erythematosus</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5550</th>\n",
       "      <td>ZRq9.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Lupus Erythematosus</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5551</th>\n",
       "      <td>ZRq9.11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Lupus Erythematosus</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5898</th>\n",
       "      <td>1464</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Schizophrenia</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6030</th>\n",
       "      <td>2376</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Scoliosis</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6090</th>\n",
       "      <td>1442</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Lipid Disorder</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     CleansedReadCode Term                    Disease SnomedCTConceptId\n",
       "1619               na  NaN     Chronic Kidney Disease               NaN\n",
       "5107             3355  NaN  Dermatitis atopic contact               NaN\n",
       "5318             1594  NaN    Female genital Prolapse               NaN\n",
       "5548          ZRq8.00  NaN        Lupus Erythematosus               NaN\n",
       "5549          ZRq8.11  NaN        Lupus Erythematosus               NaN\n",
       "5550          ZRq9.00  NaN        Lupus Erythematosus               NaN\n",
       "5551          ZRq9.11  NaN        Lupus Erythematosus               NaN\n",
       "5898             1464  NaN              Schizophrenia               NaN\n",
       "6030             2376  NaN                  Scoliosis               NaN\n",
       "6090             1442  NaN             Lipid Disorder               NaN"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_map_missing_snomed=df_map[df_map.isnull().any(axis=1)]\n",
    "df_map_missing_snomed"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "54952c6b",
   "metadata": {},
   "source": [
    "# Save SNOMED and missing SNOMED files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "fa82dc17",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save\n",
    "df_map.to_csv(filepath_snomed_readcode, index=False)\n",
    "df_map_missing_snomed.to_csv(filepath_missing_snomed, index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}