notebooks/1_patient_selection.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 1 Patient Selection\n",
    "Prior to this script it is important to extract the necessary data from HIX: \n",
    "- Run sql/GetReport_RA.sql to get the Conclusion section of all RA patients\n",
    "- Run sql/GetAppointments_RA.sql to get the Conclusion section of all RA patients\n",
    "\n",
    "Summary:\n",
    "1. In this script we elucidate the first consult for each patient in HIX\n",
    "2. We remove patients that were admitted before August 29, 2011 (because we cannot guarantee that we have all the data available)\n",
    "3. We export / save different patient selections to be used in the proceeding research"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sys\n",
    "sys.path.append(r'../src/1_emr_scripts')\n",
    "\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.1 Gather all first consults\n",
    "- SQL : Run GetAppointments_RA.sql to gather all first consults (financial code=NP) of the patients that visited the Rheumatology departement\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.2 Assess first visit date\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total Patients in HIX (with registered NP) : 45592\n",
      "Patients with baseline after 2011 : 25821\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "# Preload function\n",
    "def getFirstPEC(pat):\n",
    "    if pat in clean_dict.keys():\n",
    "        return clean_dict[int(pat)]\n",
    "    else :\n",
    "        return np.nan\n",
    "\n",
    "# import appointments \n",
    "df_rapp = pd.read_csv(r'../new_data/1_raw/Appointments_REU.csv', sep=\";\")\n",
    "\n",
    "df_rapp['period_start_date'] = df_rapp['period_start_date'].apply(lambda x : pd.to_datetime(x, format='%d-%m-%Y', errors='ignore'))\n",
    "df_rapp = df_rapp.sort_values(by='period_start_date')\n",
    "\n",
    "print('Total Patients in HIX (with registered NP) :', len(df_rapp['subject_Patient_value'].unique()))\n",
    "\n",
    "# Filter out visits prior to 2011\n",
    "df_rapp = df_rapp[(df_rapp['period_start_date'] > pd.to_datetime('20110829', format='%Y%m%d', errors='ignore'))]\n",
    "\n",
    "print('Patients with baseline after 2011 :', len(df_rapp['subject_Patient_value'].unique()))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### #1.2.1 Write all patient ids in convenient format "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "import math\n",
    "l_pat = list(df_rapp['subject_Patient_value'].unique())\n",
    "\n",
    "stepsize = 1000\n",
    "with open('Pat_selection_sql.txt', 'w') as f:\n",
    "    for j in range(int(math.ceil(float(len(l_pat)/stepsize)))):\n",
    "        f.write('(' + '),('.join([str(int(i)) for i in l_pat[stepsize*j:stepsize*(j+1)]]) + ')\\n')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## #1.3 Acquire Physician notes\n",
    "\n",
    "- SQL : Run GetReport_RA.sql to gather all conclusion sections of the patients from the list of selected patients"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## #1.4 Screen Physician notes\n",
    "We will leverage the conclusion section of the DDR_A table (HIX) for our prediction of the RA diagnosis.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/tdmaarseveen/.conda/envs/ra_clustering2/lib/python3.8/site-packages/IPython/core/interactiveshell.py:3145: DtypeWarning: Columns (1,7) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  has_raised = await self.run_ast_nodes(code_ast.body, cell_name,\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "24645"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "df_REUCON = pd.read_csv(r'../new_data/1_raw/processed_Notes_Entry.csv', sep=\"|\", index_col=0)\n",
    "\n",
    "# Only select patients after initialization of HIX (after 2011) \n",
    "df_REUCON = df_REUCON[df_REUCON['PATNR'].isin(l_pat)].copy()\n",
    "\n",
    "# Save relevant selection\n",
    "df_REUCON.to_csv(r'../new_data/1_raw/NewPatSelection_Notes_header.csv', sep=';', index=False) # 1 year\n",
    "len(df_REUCON['PATNR'].unique())\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### #1.4.1 Prepare the consultation data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import sys\n",
    "sys.path.append(r'../src/1_emr_scripts')\n",
    "import Preprocessing as func\n",
    "\n",
    "d_first_date =df_rapp.groupby(['subject_Patient_value'])['period_start_date'].agg('max').to_dict()\n",
    "\n",
    "df_REUCON = pd.read_csv(r'../new_data/1_raw/NewPatSelection_Notes_header.csv', sep=\";\")\n",
    "\n",
    "df_REUCON['XANTWOORD'] = df_REUCON['XANTWOORD'].apply(lambda x : func.processArtefactsXML(str(x)))\n",
    "df_REUCON['created'] =df_REUCON['created'].apply(lambda x: pd.to_datetime(x, utc=True, format='%Y-%m-%d', errors='ignore'))\n",
    "df_REUCON = df_REUCON[df_REUCON['PATNR'].isin(d_first_date)]\n",
    "df_REUCON['PEC'] = df_REUCON['PATNR'].apply(lambda x : d_first_date[x])\n",
    "\n",
    "df_REUCON.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### #1.4.2 Aggregate Conclusion sections "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "import Preprocessing as func\n",
    "import pickle\n",
    "import time\n",
    "\n",
    "#model_name='savedModels/SVM.sav'\n",
    "X_column='XANTWOORD'\n",
    "y_column=\"\"#'Outcome'\n",
    "id_column='PATNR'\n",
    "\n",
    "def entriesPatientMerge(pat_df, id_column, X_column, y_column=\"\"):\n",
    "    \"\"\"\n",
    "    Merges the entries into one entry per patient (according to the id_column)\n",
    "    \n",
    "    Input: \n",
    "        id_column = column with patient id\n",
    "        X_column = column with \n",
    "    \"\"\"\n",
    "    field = ''\n",
    "    for i in pat_df[X_column]:\n",
    "        field += \" \" + i + \" \"\n",
    "    if y_column!=\"\":\n",
    "        return {X_column: field, id_column : pat_df[id_column].iloc[0], y_column : pat_df[y_column].iloc[0]}\n",
    "    else :\n",
    "        return {X_column: field, id_column : pat_df[id_column].iloc[0]}\n",
    "\n",
    "if y_column != \"\":\n",
    "    df_ult = pd.DataFrame(columns=[X_column,  id_column, y_column])\n",
    "else : \n",
    "    df_ult = pd.DataFrame(columns=[X_column,  id_column])\n",
    "    \n",
    "    \n",
    "for pat in df_REUCON[id_column].unique():\n",
    "    pat_df = df_REUCON[df_REUCON[id_column]==pat]\n",
    "    if y_column != \"\":\n",
    "        df_ult = df_ult.append(entriesPatientMerge(pat_df, id_column, X_column, y_column), ignore_index=True)\n",
    "    else : \n",
    "        df_ult = df_ult.append(entriesPatientMerge(pat_df, id_column, X_column), ignore_index=True)\n",
    "\n",
    "df_ult=df_ult.fillna('')\n",
    "df_ult['XANTWOORD'] = df_ult['XANTWOORD'].apply(lambda x : func.processArtefactsXML(str(x)))\n",
    "\n",
    "df_ult.to_csv(r'../new_data/1_raw/processed_Notes_Total.csv', sep='|') # 1 year"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## #1.5 Apply ML"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Time for Making predictions (n=24645) : 39.02286243438721\n"
     ]
    }
   ],
   "source": [
    "import pickle\n",
    "import time\n",
    "import sklearn\n",
    "\n",
    "df_ult =  pd.read_csv(r'../new_data/1_raw/processed_Notes_Total.csv', sep='|') \n",
    "df_ult = df_ult.fillna('')\n",
    "\n",
    "# get EMR text\n",
    "text_column = 'XANTWOORD'\n",
    "X = df_ult[text_column].values\n",
    "\n",
    "# apply built model on provided text\n",
    "loaded_model = pickle.load(open(r'../models/SVM_20221201_ALL.sav', 'rb')) # SVM_20221201  SVM_29042020.sav\n",
    "\n",
    "t0 = time.time()\n",
    "\n",
    "probas_ = loaded_model.predict_proba(X)\n",
    "pred = probas_[:,1]\n",
    "\n",
    "t1 = time.time()\n",
    "print('Time for Making predictions (n=' + str(len(df_ult['PATNR'].unique())) + ') : ' + str(t1-t0))\n",
    "\n",
    "# add predictions to table\n",
    "df_ult['prediction'] = df_ult[text_column].copy()\n",
    "df_ult['prediction'] = pred\n",
    "\n",
    "# export predictions\n",
    "df_ult[['PATNR', 'prediction', 'XANTWOORD']].to_csv(r'../new_data/1_raw/RA_patients_pred_3.csv', sep='|', index=False)\n",
    "\n",
    "# export patient selection with corresponding text and subsequent predictions\n",
    "df_ult[df_ult['prediction']>0.83][['PATNR', 'prediction', 'XANTWOORD']].to_csv(r'../new_data/1_raw/RA_patients_083_Total.csv', sep='|', index=False)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Check patients that reach the threshold\n",
    "\n",
    "Threshold of 0.83 was defined in previous paper: https://arthritis-research.biomedcentral.com/articles/10.1186/s13075-021-02553-4  \n",
    "The benefit of using this threshold is that it balances sensitivity and precision on 0.85"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_ult[df_ult['prediction']>0.83]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## #1.6 Create new pseudoIds \n",
    "Create a composite identifier \"pseudoId\" by combining the patient id and the visit nr\n",
    "\n",
    "We want to include all first Consults "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### #1.6.1 Select RA patients"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Patients predicted to have RA : 2128\n"
     ]
    }
   ],
   "source": [
    "l_found = df_ult[df_ult['prediction']>0.83]['PATNR'].unique()\n",
    "print('Patients predicted to have RA :', len(l_found))\n",
    "df_rapp = df_rapp[df_rapp['subject_Patient_value'].isin(l_found)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### #1.6.2 assign pseudoIds\n",
    "\n",
    "Write down important patient info in a single table:\n",
    "1. Pseudo Id\n",
    "2. Date of first consult (or imputed)\n",
    "3. Patient id\n",
    "4. Visit number"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def create_dict_pseudoIds(df_rapp):\n",
    "    \"\"\"\n",
    "    Create new pseudo ids for all first dates\n",
    "    \"\"\"\n",
    "    new_pat = {}\n",
    "    for pat in df_rapp['subject_Patient_value'].unique():\n",
    "        cnt = 0\n",
    "        sub_df = df_rapp[df_rapp['subject_Patient_value']==pat].copy()\n",
    "        for index, row in sub_df.iterrows():\n",
    "            identity = '%s_%s' % (pat, cnt)\n",
    "            new_pat[identity] = pd.to_datetime(row['period_start_date'] , format='%d-%m-%Y', errors='ignore') \n",
    "            cnt += 1\n",
    "    return new_pat\n",
    "\n",
    "df_rapp = df_rapp[df_rapp['type1_code']!='NPSI'] # exclude patients that visit the 'InloopPoli'\n",
    "new_pat = create_dict_pseudoIds(df_rapp)\n",
    "df_pat = pd.DataFrame.from_dict(new_pat, orient='index')\n",
    "df_pat = df_pat.reset_index()\n",
    "df_pat.columns = ['pseudoId', 'date']\n",
    "df_pat['patnr'] = df_pat['pseudoId'].apply(lambda x: x.split('_')[0])\n",
    "df_pat['NP_visit_nr'] = df_pat['pseudoId'].apply(lambda x: x.split('_')[1])\n",
    "\n",
    "# Remove patients with consultation before 2011\n",
    "#df_pat = df_pat[df_pat['period_start_date'] > pd.to_datetime('20110829', format='%Y%m%d', errors='ignore')]\n",
    "\n",
    "# Export\n",
    "df_pat.to_csv('../filters/RA_patients_AllNP_11-10-2022.csv')\n",
    "df_pat.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### #1.6.3 Visualize number of 'first' visits per patient "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[1, 2, 3, 4, 5] [1654, 394, 69, 8, 2]\n"
     ]
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAYsAAAEDCAYAAADEAyg+AAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjMuMSwgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy/d3fzzAAAACXBIWXMAAAsTAAALEwEAmpwYAAAYF0lEQVR4nO3dfbRddX3n8ffHoDyoVFgETBNooitKg0WEK0PrQ1FaoeoQrKXGpUN00IxMpj60Tk10VnHNWlkrzvjI6uCYKvJQBwxUJerQglRlzQwYr4BCwAxpQYiJ5FpnBlQmGPjOH2fHOV5O7j4J95xzk/t+rXXW3ee7f3vv7zWYT/ZzqgpJkqbylFE3IEma+QwLSVIrw0KS1MqwkCS1MiwkSa0MC0lSq4NG3cCgHHXUUbVw4cJRtyFJ+5XvfOc7P66quZPrB2xYLFy4kPHx8VG3IUn7lSQ/6FX3MJQkqZVhIUlqZVhIkloZFpKkVoaFJKmVYSFJamVYSJJaGRaSpFYH7E15T8bCVV8ddQtDd9/a14y6BUkzmHsWkqRWhoUkqdXAwiLJJUl2JLlzUv1PkmxOsinJf+iqr06ypZl3Zlf9lCR3NPMuSpJB9SxJ6m2QexaXAmd1F5K8AlgKnFhVJwAfbupLgGXACc0yFyeZ0yz2SWAFsLj5/Mo6JUmDN7CwqKqbgJ9MKl8ArK2qnc2YHU19KXBVVe2sqnuBLcCpSeYBh1fVzVVVwOXAOYPqWZLU27DPWTwPeFmSbyX5ZpIXN/X5wANd47Y2tfnN9OS6JGmIhn3p7EHAEcBpwIuB9UmeA/Q6D1FT1HtKsoLOISuOO+64J92sJKlj2HsWW4EvVMdG4HHgqKZ+bNe4BcC2pr6gR72nqlpXVWNVNTZ37hNe9CRJ2kfDDosvAa8ESPI84GnAj4ENwLIkBydZROdE9saq2g48nOS05iqo84Brh9yzJM16AzsMleRK4HTgqCRbgQuBS4BLmstpHwWWNyeuNyVZD9wF7AJWVtVjzaouoHNl1aHAdc1HkjREAwuLqnrjHma9eQ/j1wBretTHgRdMY2uSpL3kHdySpFaGhSSplWEhSWplWEiSWhkWkqRWhoUkqZVhIUlqZVhIkloZFpKkVoaFJKmVYSFJamVYSJJaGRaSpFaGhSSplWEhSWplWEiSWg0sLJJckmRH81a8yfPem6SSHNVVW51kS5LNSc7sqp+S5I5m3kXN61UlSUM0yD2LS4GzJheTHAv8PnB/V20JsAw4oVnm4iRzmtmfBFbQeS/34l7rlCQN1sDCoqpuAn7SY9bHgD8Hqqu2FLiqqnZW1b3AFuDUJPOAw6vq5uZd3ZcD5wyqZ0lSb0M9Z5HkbOCHVfXdSbPmAw90fd/a1OY305PrkqQhOmhYG0pyGPAB4FW9Zveo1RT1PW1jBZ1DVhx33HH70KUkqZdh7lk8F1gEfDfJfcAC4NYkz6azx3Bs19gFwLamvqBHvaeqWldVY1U1Nnfu3GluX5Jmr6GFRVXdUVVHV9XCqlpIJwhOrqofARuAZUkOTrKIzonsjVW1HXg4yWnNVVDnAdcOq2dJUscgL529ErgZeH6SrUnO39PYqtoErAfuAv4WWFlVjzWzLwA+Teek9z8A1w2qZ0lSbwM7Z1FVb2yZv3DS9zXAmh7jxoEXTGtzkqS94h3ckqRWhoUkqZVhIUlqZVhIkloZFpKkVoaFJKmVYSFJamVYSJJaGRaSpFaGhSSplWEhSWplWEiSWhkWkqRWhoUkqZVhIUlqZVhIkloN8k15lyTZkeTOrtp/TPL9JN9L8sUkz+qatzrJliSbk5zZVT8lyR3NvIua16tKkoZokHsWlwJnTardALygqk4E/iewGiDJEmAZcEKzzMVJ5jTLfBJYQee93It7rFOSNGADC4uqugn4yaTa9VW1q/l6C7CgmV4KXFVVO6vqXjrv2z41yTzg8Kq6uaoKuBw4Z1A9S5J6G+U5i38JXNdMzwce6Jq3tanNb6Yn1yVJQzSSsEjyAWAX8LndpR7Daor6nta7Isl4kvGJiYkn36gkCRhBWCRZDrwWeFNzaAk6ewzHdg1bAGxr6gt61HuqqnVVNVZVY3Pnzp3exiVpFhtqWCQ5C3gfcHZV/bxr1gZgWZKDkyyicyJ7Y1VtBx5OclpzFdR5wLXD7FmSBAcNasVJrgROB45KshW4kM7VTwcDNzRXwN5SVe+oqk1J1gN30Tk8tbKqHmtWdQGdK6sOpXOO4zokSUM1sLCoqjf2KH9mivFrgDU96uPAC6axNUnSXvIObklSK8NCktTKsJAktTIsJEmtDAtJUivDQpLUyrCQJLUyLCRJrQwLSVIrw0KS1MqwkCS1MiwkSa0MC0lSq77CIolPfZWkWazfPYv/nGRjkn+d5FmDbEiSNPP0FRZV9VLgTXRefTqe5L8k+f2BdiZJmjH6PmdRVfcA/47Oa1F/F7goyfeT/GGv8UkuSbIjyZ1dtSOT3JDknubnEV3zVifZkmRzkjO76qckuaOZd1HzelVJ0hD1e87ixCQfA+4GXgn886r6zWb6Y3tY7FLgrEm1VcCNVbUYuLH5TpIlwDLghGaZi5PMaZb5JLCCznu5F/dYpyRpwPrds/hL4FbghVW1sqpuBaiqbXT2Np6gqm4CfjKpvBS4rJm+DDinq35VVe2sqnuBLcCpSeYBh1fVzVVVwOVdy0iShqTfd3C/Gnikqh4DSPIU4JCq+nlVXbEX2zumqrYDVNX2JEc39fnALV3jtja1XzTTk+uSpCHqd8/ia8ChXd8Pa2rTpdd5iJqi3nslyYok40nGJyYmpq05SZrt+g2LQ6rqp7u/NNOH7cP2HmwOLdH83NHUt9K50mq3BcC2pr6gR72nqlpXVWNVNTZ37tx9aE+S1Eu/YfGzJCfv/pLkFOCRfdjeBmB5M70cuLarvizJwUkW0TmRvbE5ZPVwktOaq6DO61pGkjQk/Z6zeDdwdZLd/6qfB7xhqgWSXAmcDhyVZCtwIbAWWJ/kfOB+4FyAqtqUZD1wF7ALWLn7/AhwAZ0rqw4Frms+kqQh6issqurbSY4Hnk/nPML3q+oXLcu8cQ+zztjD+DXAmh71ccDHjUjSCPW7ZwHwYmBhs8yLklBVlw+kK0nSjNJXWCS5AngucDuw+/DQ7vseJEkHuH73LMaAJc2NcZKkWabfq6HuBJ49yEYkSTNXv3sWRwF3JdkI7NxdrKqzB9KVJGlG6TcsPjjIJiRJM1u/l85+M8lvAIur6mtJDgPmtC0nSTow9PuI8rcD1wCfakrzgS8NqCdJ0gzT7wnulcBLgIfgly9COnrKJSRJB4x+w2JnVT26+0uSg5ji6a+SpANLv2HxzSTvBw5t3r19NfDlwbUlSZpJ+g2LVcAEcAfwr4D/yh7ekCdJOvD0ezXU48BfNR9J0izT77Oh7qXHOYqqes60dyRJmnH25tlQux1C5z0UR05/O5KkmaivcxZV9U9dnx9W1ceBVw62NUnSTNHvTXknd33GkrwDeOa+bjTJe5JsSnJnkiuTHJLkyCQ3JLmn+XlE1/jVSbYk2ZzkzH3driRp3/R7GOojXdO7gPuAP96XDSaZD7yTziPPH2lep7oMWALcWFVrk6yicwXW+5IsaeafAPw68LUkz+t67aokacD6vRrqFQPY7qFJfgEcBmwDVtN5ZzfAZcA3gPcBS4GrqmoncG+SLcCpwM3T3JMkaQ/6vRrqT6eaX1Uf7XeDVfXDJB8G7gceAa6vquuTHFNV25sx25PsfpzIfOCWrlVsbWqSpCHp96a8MeACOn9JzwfeQeew0TPZy3MXzbmIpcAiOoeVnp7kzVMt0qPW81EjSVYkGU8yPjExsTdtSZKmsDcvPzq5qh4GSPJB4Oqqets+bPP3gHuraqJZ1xeA3wEeTDKv2auYB+xoxm8Fju1afgGdw1ZPUFXrgHUAY2NjPrtKkqZJv3sWxwGPdn1/FFi4j9u8HzgtyWFJApwB3A1sAJY3Y5YD1zbTG4BlSQ5OsghYDGzcx21LkvZBv3sWVwAbk3yRziGg1wGX78sGq+pbSa4BbqVzZdVtdPYGngGsT3I+nUA5txm/qbli6q5m/EqvhJKk4er3aqg1Sa4DXtaU3lpVt+3rRqvqQuDCSeWddPYyem4fWLOv25MkPTn9HoaCziWuD1XVJ4CtzSEhSdIs0O8d3BfSuedhdVN6KvDXg2pKkjSz9Ltn8TrgbOBnAFW1jSfxuA9J0v6l37B4tKqK5v6GJE8fXEuSpJmm37BYn+RTwLOSvB34Gr4ISZJmjdaroZp7IT4PHA88BDwf+IuqumHAvUmSZojWsKiqSvKlqjoFMCAkaRbq9zDULUlePNBOJEkzVr93cL8CeEeS++hcERU6Ox0nDqoxSdLMMWVYJDmuqu4H/mBI/UiSZqC2PYsv0Xna7A+S/E1VvX4IPUmSZpi2cxbd75J4ziAbkSTNXG1hUXuYliTNIm2HoV6Y5CE6exiHNtPw/09wHz7Q7iRJM8KUYVFVc4bViCRp5tqbR5RLkmYpw0KS1GokYZHkWUmuSfL9JHcn+e0kRya5Ick9zc8jusavTrIlyeYkZ46iZ0mazUa1Z/EJ4G+r6njghcDdwCrgxqpaDNzYfCfJEmAZcAJwFnBxEs+lSNIQDT0skhwOvBz4DEBVPVpV/xtYClzWDLsMOKeZXgpcVVU7q+peYAtw6jB7lqTZbhR7Fs8BJoDPJrktyaeblykdU1XbAZqfRzfj5wMPdC2/tak9QZIVScaTjE9MTAzuN5CkWWYUYXEQcDLwyap6EZ0HE66aYnx61HreIFhV66pqrKrG5s6d++Q7lSQBowmLrcDWqvpW8/0aOuHxYJJ5AM3PHV3jj+1afgGwbUi9SpIYQVhU1Y+AB5I8vymdAdwFbACWN7XlwLXN9AZgWZKDkywCFgMbh9iyJM16/b7PYrr9CfC5JE8D/hF4K53gWp/kfOB+4FyAqtqUZD2dQNkFrKyqx0bTtiTNTiMJi6q6HRjrMeuMPYxfA6wZZE+SpD3zDm5JUivDQpLUyrCQJLUyLCRJrQwLSVIrw0KS1MqwkCS1MiwkSa0MC0lSK8NCktTKsJAktTIsJEmtDAtJUivDQpLUyrCQJLUyLCRJrUYWFknmJLktyVea70cmuSHJPc3PI7rGrk6yJcnmJGeOqmdJmq1GuWfxLuDuru+rgBurajFwY/OdJEuAZcAJwFnAxUnmDLlXSZrVRhIWSRYArwE+3VVeClzWTF8GnNNVv6qqdlbVvcAW4NQhtSpJYnR7Fh8H/hx4vKt2TFVtB2h+Ht3U5wMPdI3b2tSeIMmKJONJxicmJqa9aUmarQ4a9gaTvBbYUVXfSXJ6P4v0qFWvgVW1DlgHMDY21nOMelu46qujbmHo7lv7mlG3IO03hh4WwEuAs5O8GjgEODzJXwMPJplXVduTzAN2NOO3Asd2Lb8A2DbUjiVplhv6YaiqWl1VC6pqIZ0T139fVW8GNgDLm2HLgWub6Q3AsiQHJ1kELAY2DrltSZrVRrFnsSdrgfVJzgfuB84FqKpNSdYDdwG7gJVV9djo2pSk2WekYVFV3wC+0Uz/E3DGHsatAdYMrTFJ0q/wDm5JUivDQpLUyrCQJLUyLCRJrQwLSVIrw0KS1MqwkCS1MiwkSa0MC0lSK8NCktTKsJAktTIsJEmtDAtJUivDQpLUyrCQJLUyLCRJrYYeFkmOTfL1JHcn2ZTkXU39yCQ3JLmn+XlE1zKrk2xJsjnJmcPuWZJmu1HsWewC/qyqfhM4DViZZAmwCrixqhYDNzbfaeYtA04AzgIuTjJnBH1L0qw19LCoqu1VdWsz/TBwNzAfWApc1gy7DDinmV4KXFVVO6vqXmALcOpQm5akWW6k5yySLAReBHwLOKaqtkMnUICjm2HzgQe6Ftva1Hqtb0WS8STjExMTA+tbkmabkYVFkmcAfwO8u6oemmpoj1r1GlhV66pqrKrG5s6dOx1tSpIYUVgkeSqdoPhcVX2hKT+YZF4zfx6wo6lvBY7tWnwBsG1YvUqSRnM1VIDPAHdX1Ue7Zm0AljfTy4Fru+rLkhycZBGwGNg4rH4lSXDQCLb5EuBfAHckub2pvR9YC6xPcj5wP3AuQFVtSrIeuIvOlVQrq+qxoXctSbPY0MOiqv4bvc9DAJyxh2XWAGsG1pQkaUrewS1JamVYSJJaGRaSpFaGhSSplWEhSWplWEiSWhkWkqRWhoUkqZVhIUlqZVhIkloZFpKkVqN4kKA0Iyxc9dVRtzB09619zahb0H7KPQtJUivDQpLUyrCQJLUyLCRJrfabsEhyVpLNSbYkWTXqfiRpNtkvwiLJHOA/AX8ALAHemGTJaLuSpNljf7l09lRgS1X9I0CSq4CldN7LLalPs+1yYS8Vnj77S1jMBx7o+r4V+GeTByVZAaxovv40yeYh9DadjgJ+POomhszfeXYYye+cDw17i79if/1z/o1exf0lLNKjVk8oVK0D1g2+ncFIMl5VY6PuY5j8nWcHf+f9335xzoLOnsSxXd8XANtG1IskzTr7S1h8G1icZFGSpwHLgA0j7kmSZo394jBUVe1K8m+AvwPmAJdU1aYRtzUI++0htCfB33l28Hfez6XqCYf+JUn6FfvLYShJ0ggZFpKkVoaFJKmVYaGhSnJ8kjOSPGNS/axR9TRoSU5N8uJmekmSP03y6lH3NSxJLh91D8OW5KXNn/OrRt3LdPEE9wyU5K1V9dlR9zHdkrwTWAncDZwEvKuqrm3m3VpVJ4+wvYFIciGdZ5odBNxA58kD3wB+D/i7qlozuu6mX5LJl7QHeAXw9wBVdfbQmxqCJBur6tRm+u10/jv/IvAq4MtVtXaU/U0Hw2IGSnJ/VR036j6mW5I7gN+uqp8mWQhcA1xRVZ9IcltVvWi0HU6/5nc+CTgY+BGwoKoeSnIo8K2qOnGU/U23JLfSeWbbp+k8ZSHAlXTujaKqvjm67gan+7/fJN8GXl1VE0meDtxSVb812g6fvP3iPosDUZLv7WkWcMwwexmiOVX1U4Cqui/J6cA1SX6D3o90ORDsqqrHgJ8n+Yeqegigqh5J8viIexuEMeBdwAeAf1tVtyd55EANiS5PSXIEnUP7qaoJgKr6WZJdo21tehgWo3MMcCbwvybVA/yP4bczFD9KclJV3Q7Q7GG8FrgE2O//5bUHjyY5rKp+Dpyyu5jk14ADLiyq6nHgY0mubn4+yOz4e+bXgO/Q+f9vJXl2Vf2oOTd3QPxDaDb8Ic5UXwGesfsvzm5JvjH0bobjPOBX/pVVVbuA85J8ajQtDdzLq2on/PIv0t2eCiwfTUuDV1VbgXOTvAZ4aNT9DFpVLdzDrMeB1w2xlYHxnIUkqZWXzkqSWhkWkqRWhoUOGEkqyUe6vr83yQef5DqvTPK9JO+ZVP9gkrckubS5qoskL0uyKcntSeYnuWYvt/X+Sd/vS7Jwb85hJRlLctEU8399d19JTppNNwfqyTEsdCDZCfxhkqOmGpSkrws7kjwb+J2qOrGqPtbHIm8CPlxVJ1XVD6vqj/Zy2++fYl5fqmq8qt45xfxtXX2dBBgW6othoQPJLjrvEHjP5BnNHsBHk3wd+NCkeYck+WySO5LcluQVzazrgaObPYWXTVrlT4FHgP9D5/LYtwF/DPxFks81ewR3Nut/S5Krk3wZuD7JvCQ3Neu9s9kjWQsc2tQ+12xjAngM+EmP3+fz3XsFze/3+iSnJ/lKU/vdZn23N7/XM3f31bxE7N8Db2jmv6HX+L35H18HuKry4+eA+ND5C/xw4D46172/F/hgM+9SOpcrz+mx3J8Bn22mjwfuBw4BFgJ37sX2LwX+qJn+5bLAW+i8GvjIru19oJmeAzxzd/97sa3XAZc1008DHgAOBU4HvtLUvwy8pJl+Bp1L5Sf39Zdd63zC+FH/mfqZOR/3LHRAqc4d0pcDvQ7FXF2du6kneylwRbP894EfAM+b5tZuqKrdewjfBt7anE/5rap6eB/Wdx3wyiQH03n21E1V9cikMf8d+GjzTK5nVeeelqns7XjNIoaFDkQfB84Hnj6p/rM9jB/GHba/3HZV3QS8HPghcEWS8/Z2ZVX1f+k8kPBM4A3AVT3GrAXeRmeP45Ykx7esc6/Ga3YxLHTAaf4Fv55OYPTjJjonp0nyPOA4YPNguoPmWVg7quqvgM8Au5+2+4skT92LVV0FvBV4GZ3300/eznOr6o6q+hAwTucQW7eHgWfuxXjNYoaFDlQfAaa8KqrLxcCc5gmxnwfeUs0jOgbkdOD2JLcBrwc+0dTXAd/rOsHd5no6eyhfq6pHe8x/d3My+7t0TsZfN2n+14Elu09w9zFes5iP+5AktXLPQpLUyrCQJLUyLCRJrQwLSVIrw0KS1MqwkCS1MiwkSa0MC0lSq/8Hbqf41zDIq6sAAAAASUVORK5CYII=\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "## Create new id system\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "from collections import Counter\n",
    "df_symptom = pd.DataFrame.from_dict(dict(Counter(list(Counter(df_rapp['subject_Patient_value']).values())).items()), orient='index').sort_index()\n",
    "\n",
    "labels, values = list(range(1, len(df_symptom)+1)), list(df_symptom[0])\n",
    "print(labels, values)\n",
    "\n",
    "indexes = np.arange(len(labels))\n",
    "width = 1\n",
    "\n",
    "plt.bar(indexes, values, width)\n",
    "plt.xticks(indexes , labels, rotation=90)\n",
    "plt.xlabel('Nr of \"first\" visits')\n",
    "plt.ylabel('Frequency')\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### #1.6.4 Export all patient ids for SQL queries\n",
    "\n",
    "We will create a copy of all identified patients. This list will be used to query clinical data from the SQL database. The data is formatted in a convenient structure for SQL."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "stepsize = 1000\n",
    "with open('../keys/HIX_RA_selection.txt', 'w') as f:\n",
    "    for j in range(int(math.ceil(float(len(l_found)/stepsize)))):\n",
    "        f.write('(' + '),('.join([str(int(i)) for i in l_found[stepsize*j:stepsize*(j+1)]]) + ')\\n')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "clustering_ra",
   "language": "python",
   "name": "clustering_ra"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}