Amiodarone Cohort Analysis II - Data Cleaning & Reshaping.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "66730130",
   "metadata": {},
   "source": [
    "## An OMOP Cohort: Patients Exposed to Amiodarone Hydrochloride > 30 Days\n",
    "\n",
    "### Part II: Data Cleaning & Reshaping\n",
    "\n",
    "An examination of de-identified longitudinal visit and procedure data (derived to OMOP from Medicare DeSYNPuf data, 2008-2010, by OHDSI) and associated ICD9CM diagnosis codes to analyze the incidence and prevalence of AEs associated with amiodarone toxicity to the lung, thyroid and liver.\n",
    "\n",
    "Kyle P. Rasku MS BSN RN"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "fa9d242d",
   "metadata": {},
   "outputs": [],
   "source": [
    "%matplotlib inline\n",
    "import random\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import seaborn as sb\n",
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b57b0d79",
   "metadata": {},
   "source": [
    "Load data sets."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "14dac4f4",
   "metadata": {},
   "outputs": [],
   "source": [
    "cohort_ds = pd.read_csv(\"amiodarone_exposed_patients.csv\", parse_dates=[\"patient_dob\"])\n",
    "visits_ds = pd.read_csv(\"amiodarone_exposed_visits.csv\", parse_dates=[\"treatment_start\", \"vstart\", \"vend\"])\n",
    "procedures_ds = pd.read_csv(\"amiodarone_exposed_procedures.csv\", parse_dates=[\"drug_treatment_start\", \"vstart\", \"vend\"])\n",
    "cpt_codes = pd.read_csv(\"cpt_lookup.csv\")\n",
    "conditions_ds = pd.read_csv(\"conditions_all.csv\", parse_dates=[\"treatment_start\", \"condition_start_date\", \"condition_end_date\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eb025e3b",
   "metadata": {},
   "source": [
    "Clean and reshape visits with diagnoses."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "41db4caf",
   "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>patient_id</th>\n",
       "      <th>treatment_start</th>\n",
       "      <th>treatment_period</th>\n",
       "      <th>vtype</th>\n",
       "      <th>visit_id</th>\n",
       "      <th>diagnosis_name</th>\n",
       "      <th>snomed_code</th>\n",
       "      <th>ICD9CODE</th>\n",
       "      <th>vstart</th>\n",
       "      <th>vend</th>\n",
       "      <th>vduration</th>\n",
       "      <th>vprovider</th>\n",
       "      <th>care_site</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>111598</td>\n",
       "      <td>2008-01-15</td>\n",
       "      <td>90</td>\n",
       "      <td>VISIT</td>\n",
       "      <td>5351372</td>\n",
       "      <td>Vomiting</td>\n",
       "      <td>422400008</td>\n",
       "      <td>78703</td>\n",
       "      <td>2008-01-28</td>\n",
       "      <td>2008-01-28</td>\n",
       "      <td>NaN</td>\n",
       "      <td>50058.0</td>\n",
       "      <td>11234.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>111598</td>\n",
       "      <td>2008-01-15</td>\n",
       "      <td>90</td>\n",
       "      <td>VISIT</td>\n",
       "      <td>5351372</td>\n",
       "      <td>Epidemic vertigo</td>\n",
       "      <td>186738001</td>\n",
       "      <td>38612</td>\n",
       "      <td>2008-01-28</td>\n",
       "      <td>2008-01-28</td>\n",
       "      <td>NaN</td>\n",
       "      <td>50058.0</td>\n",
       "      <td>11234.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>111598</td>\n",
       "      <td>2008-01-15</td>\n",
       "      <td>90</td>\n",
       "      <td>VISIT</td>\n",
       "      <td>5351385</td>\n",
       "      <td>Atrial fibrillation</td>\n",
       "      <td>49436004</td>\n",
       "      <td>42731</td>\n",
       "      <td>2008-01-28</td>\n",
       "      <td>2008-01-28</td>\n",
       "      <td>NaN</td>\n",
       "      <td>315573.0</td>\n",
       "      <td>11283.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>111598</td>\n",
       "      <td>2008-01-15</td>\n",
       "      <td>90</td>\n",
       "      <td>VISIT</td>\n",
       "      <td>5351385</td>\n",
       "      <td>Atrial fibrillation</td>\n",
       "      <td>49436004</td>\n",
       "      <td>42731</td>\n",
       "      <td>2008-01-28</td>\n",
       "      <td>2008-01-28</td>\n",
       "      <td>NaN</td>\n",
       "      <td>315573.0</td>\n",
       "      <td>11283.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>111598</td>\n",
       "      <td>2008-01-15</td>\n",
       "      <td>90</td>\n",
       "      <td>VISIT</td>\n",
       "      <td>5351385</td>\n",
       "      <td>Paroxysmal supraventricular tachycardia</td>\n",
       "      <td>67198005</td>\n",
       "      <td>4270</td>\n",
       "      <td>2008-01-28</td>\n",
       "      <td>2008-01-28</td>\n",
       "      <td>NaN</td>\n",
       "      <td>315573.0</td>\n",
       "      <td>11283.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   patient_id treatment_start  treatment_period  vtype  visit_id  \\\n",
       "0      111598      2008-01-15                90  VISIT   5351372   \n",
       "1      111598      2008-01-15                90  VISIT   5351372   \n",
       "2      111598      2008-01-15                90  VISIT   5351385   \n",
       "3      111598      2008-01-15                90  VISIT   5351385   \n",
       "4      111598      2008-01-15                90  VISIT   5351385   \n",
       "\n",
       "                            diagnosis_name snomed_code ICD9CODE     vstart  \\\n",
       "0                                 Vomiting   422400008    78703 2008-01-28   \n",
       "1                         Epidemic vertigo   186738001    38612 2008-01-28   \n",
       "2                      Atrial fibrillation    49436004    42731 2008-01-28   \n",
       "3                      Atrial fibrillation    49436004    42731 2008-01-28   \n",
       "4  Paroxysmal supraventricular tachycardia    67198005     4270 2008-01-28   \n",
       "\n",
       "        vend  vduration  vprovider  care_site  \n",
       "0 2008-01-28        NaN    50058.0    11234.0  \n",
       "1 2008-01-28        NaN    50058.0    11234.0  \n",
       "2 2008-01-28        NaN   315573.0    11283.0  \n",
       "3 2008-01-28        NaN   315573.0    11283.0  \n",
       "4 2008-01-28        NaN   315573.0    11283.0  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "visits_ds.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "c013a1c3",
   "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>visit_id</th>\n",
       "      <th>diagnosis</th>\n",
       "      <th>snomed_code</th>\n",
       "      <th>icd9_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5351372</td>\n",
       "      <td>Vomiting</td>\n",
       "      <td>422400008</td>\n",
       "      <td>78703</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5351372</td>\n",
       "      <td>Epidemic vertigo</td>\n",
       "      <td>186738001</td>\n",
       "      <td>38612</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5351385</td>\n",
       "      <td>Atrial fibrillation</td>\n",
       "      <td>49436004</td>\n",
       "      <td>42731</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5351385</td>\n",
       "      <td>Atrial fibrillation</td>\n",
       "      <td>49436004</td>\n",
       "      <td>42731</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5351385</td>\n",
       "      <td>Paroxysmal supraventricular tachycardia</td>\n",
       "      <td>67198005</td>\n",
       "      <td>4270</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   visit_id                                diagnosis snomed_code icd9_code\n",
       "0   5351372                                 Vomiting   422400008     78703\n",
       "1   5351372                         Epidemic vertigo   186738001     38612\n",
       "2   5351385                      Atrial fibrillation    49436004     42731\n",
       "3   5351385                      Atrial fibrillation    49436004     42731\n",
       "4   5351385  Paroxysmal supraventricular tachycardia    67198005      4270"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "visit_diagnoses = visits_ds[[\"visit_id\", \"diagnosis_name\", \"snomed_code\", \"ICD9CODE\"]].copy()\n",
    "visit_diagnoses.columns = [\"visit_id\", \"diagnosis\", \"snomed_code\", \"icd9_code\"]\n",
    "visit_diagnoses.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "75ba8abf",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 5595 entries, 0 to 5594\n",
      "Data columns (total 34 columns):\n",
      " #   Column        Non-Null Count  Dtype \n",
      "---  ------        --------------  ----- \n",
      " 0   diagnosis_1   5595 non-null   object\n",
      " 1   diagnosis_2   4257 non-null   object\n",
      " 2   diagnosis_3   2125 non-null   object\n",
      " 3   diagnosis_4   1026 non-null   object\n",
      " 4   diagnosis_5   439 non-null    object\n",
      " 5   diagnosis_6   185 non-null    object\n",
      " 6   diagnosis_7   85 non-null     object\n",
      " 7   diagnosis_8   34 non-null     object\n",
      " 8   diagnosis_9   10 non-null     object\n",
      " 9   diagnosis_10  2 non-null      object\n",
      " 10  diagnosis_11  1 non-null      object\n",
      " 11  snomed_1      5595 non-null   object\n",
      " 12  snomed_2      4257 non-null   object\n",
      " 13  snomed_3      2125 non-null   object\n",
      " 14  snomed_4      1026 non-null   object\n",
      " 15  snomed_5      439 non-null    object\n",
      " 16  snomed_6      185 non-null    object\n",
      " 17  snomed_7      85 non-null     object\n",
      " 18  snomed_8      34 non-null     object\n",
      " 19  snomed_9      10 non-null     object\n",
      " 20  snomed_10     2 non-null      object\n",
      " 21  snomed_11     1 non-null      object\n",
      " 22  icd9_1        5595 non-null   object\n",
      " 23  icd9_2        4284 non-null   object\n",
      " 24  icd9_3        2086 non-null   object\n",
      " 25  icd9_4        991 non-null    object\n",
      " 26  icd9_5        414 non-null    object\n",
      " 27  icd9_6        177 non-null    object\n",
      " 28  icd9_7        81 non-null     object\n",
      " 29  icd9_8        32 non-null     object\n",
      " 30  icd9_9        10 non-null     object\n",
      " 31  icd9_10       1 non-null      object\n",
      " 32  icd9_11       1 non-null      object\n",
      " 33  visit_id      5595 non-null   int64 \n",
      "dtypes: int64(1), object(33)\n",
      "memory usage: 1.5+ MB\n"
     ]
    }
   ],
   "source": [
    "grouped = visit_diagnoses.groupby(['visit_id'], as_index = False).agg({'diagnosis': lambda x: ';'.join(set(x)), \n",
    "                                                                'snomed_code': lambda x: ';'.join(set(x)),\n",
    "                                                                'icd9_code': lambda x: ';'.join(set(x))})\n",
    "long = {}\n",
    "long[0] = grouped['diagnosis'].str.split(pat=\";\", expand=True)\n",
    "long[1] = grouped['snomed_code'].str.split(pat=\";\",expand=True)\n",
    "long[2] = grouped['icd9_code'].str.split(pat=\";\",expand=True)\n",
    "wide = pd.concat(long, axis=1)\n",
    "wide[\"visit_id\"] = grouped[\"visit_id\"].copy()\n",
    "\n",
    "wide.columns = [\"diagnosis_1\", \"diagnosis_2\", \"diagnosis_3\", \"diagnosis_4\", \"diagnosis_5\", \"diagnosis_6\", \"diagnosis_7\", \"diagnosis_8\", \n",
    "                \"diagnosis_9\", \"diagnosis_10\", \"diagnosis_11\", \"snomed_1\", \"snomed_2\", \"snomed_3\", \"snomed_4\", \"snomed_5\", \"snomed_6\", \n",
    "                \"snomed_7\", \"snomed_8\", \"snomed_9\", \"snomed_10\", \"snomed_11\", \"icd9_1\", \"icd9_2\", \"icd9_3\", \"icd9_4\", \"icd9_5\", \n",
    "                \"icd9_6\", \"icd9_7\", \"icd9_8\", \"icd9_9\", \"icd9_10\", \"icd9_11\", \"visit_id\"]\n",
    "\n",
    "wide.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "906e64f1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(5596, 7)"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "visits_ds = visits_ds[[\"patient_id\", \"treatment_start\", \"treatment_period\", \"visit_id\", \"vstart\", \"vprovider\",\"care_site\"]].copy()\n",
    "visits_ds = visits_ds.drop_duplicates()\n",
    "visits_ds.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "cdd14de1",
   "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>patient_id</th>\n",
       "      <th>treatment_start</th>\n",
       "      <th>treatment_period</th>\n",
       "      <th>visit_id</th>\n",
       "      <th>vstart</th>\n",
       "      <th>vprovider</th>\n",
       "      <th>care_site</th>\n",
       "      <th>diagnosis_1</th>\n",
       "      <th>diagnosis_2</th>\n",
       "      <th>diagnosis_3</th>\n",
       "      <th>...</th>\n",
       "      <th>icd9_2</th>\n",
       "      <th>icd9_3</th>\n",
       "      <th>icd9_4</th>\n",
       "      <th>icd9_5</th>\n",
       "      <th>icd9_6</th>\n",
       "      <th>icd9_7</th>\n",
       "      <th>icd9_8</th>\n",
       "      <th>icd9_9</th>\n",
       "      <th>icd9_10</th>\n",
       "      <th>icd9_11</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>111598</td>\n",
       "      <td>2008-01-15</td>\n",
       "      <td>90</td>\n",
       "      <td>5351372</td>\n",
       "      <td>2008-01-28</td>\n",
       "      <td>50058.0</td>\n",
       "      <td>11234.0</td>\n",
       "      <td>Vomiting</td>\n",
       "      <td>Epidemic vertigo</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>78703</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>111598</td>\n",
       "      <td>2008-01-15</td>\n",
       "      <td>90</td>\n",
       "      <td>5351385</td>\n",
       "      <td>2008-01-28</td>\n",
       "      <td>315573.0</td>\n",
       "      <td>11283.0</td>\n",
       "      <td>Atrial fibrillation</td>\n",
       "      <td>Paroxysmal supraventricular tachycardia</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>42731</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>111598</td>\n",
       "      <td>2008-01-15</td>\n",
       "      <td>90</td>\n",
       "      <td>5351386</td>\n",
       "      <td>2008-01-31</td>\n",
       "      <td>218450.0</td>\n",
       "      <td>11234.0</td>\n",
       "      <td>Atrial fibrillation</td>\n",
       "      <td>Premature beats</td>\n",
       "      <td>Type 2 diabetes mellitus</td>\n",
       "      <td>...</td>\n",
       "      <td>42760</td>\n",
       "      <td>42731</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>111598</td>\n",
       "      <td>2008-01-15</td>\n",
       "      <td>90</td>\n",
       "      <td>5351403</td>\n",
       "      <td>2008-02-05</td>\n",
       "      <td>382137.0</td>\n",
       "      <td>28276.0</td>\n",
       "      <td>Angina decubitus</td>\n",
       "      <td>Mitral and aortic stenosis</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>4130</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>111598</td>\n",
       "      <td>2008-01-15</td>\n",
       "      <td>90</td>\n",
       "      <td>5351324</td>\n",
       "      <td>2008-02-05</td>\n",
       "      <td>447710.0</td>\n",
       "      <td>11222.0</td>\n",
       "      <td>Conduction disorder of the heart</td>\n",
       "      <td>Atrial fibrillation</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>42731</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 40 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   patient_id treatment_start  treatment_period  visit_id     vstart  \\\n",
       "0      111598      2008-01-15                90   5351372 2008-01-28   \n",
       "1      111598      2008-01-15                90   5351385 2008-01-28   \n",
       "2      111598      2008-01-15                90   5351386 2008-01-31   \n",
       "3      111598      2008-01-15                90   5351403 2008-02-05   \n",
       "4      111598      2008-01-15                90   5351324 2008-02-05   \n",
       "\n",
       "   vprovider  care_site                       diagnosis_1  \\\n",
       "0    50058.0    11234.0                          Vomiting   \n",
       "1   315573.0    11283.0               Atrial fibrillation   \n",
       "2   218450.0    11234.0               Atrial fibrillation   \n",
       "3   382137.0    28276.0                  Angina decubitus   \n",
       "4   447710.0    11222.0  Conduction disorder of the heart   \n",
       "\n",
       "                               diagnosis_2               diagnosis_3  ...  \\\n",
       "0                         Epidemic vertigo                      None  ...   \n",
       "1  Paroxysmal supraventricular tachycardia                      None  ...   \n",
       "2                          Premature beats  Type 2 diabetes mellitus  ...   \n",
       "3               Mitral and aortic stenosis                      None  ...   \n",
       "4                      Atrial fibrillation                      None  ...   \n",
       "\n",
       "  icd9_2 icd9_3 icd9_4 icd9_5 icd9_6 icd9_7 icd9_8 icd9_9 icd9_10 icd9_11  \n",
       "0  78703   None   None   None   None   None   None   None    None    None  \n",
       "1  42731   None   None   None   None   None   None   None    None    None  \n",
       "2  42760  42731   None   None   None   None   None   None    None    None  \n",
       "3   4130   None   None   None   None   None   None   None    None    None  \n",
       "4  42731   None   None   None   None   None   None   None    None    None  \n",
       "\n",
       "[5 rows x 40 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "visits_ds = pd.merge(visits_ds, wide, on=\"visit_id\")\n",
    "visits_ds.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "6e02ebdb",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 5596 entries, 0 to 5595\n",
      "Data columns (total 40 columns):\n",
      " #   Column            Non-Null Count  Dtype         \n",
      "---  ------            --------------  -----         \n",
      " 0   patient_id        5596 non-null   int64         \n",
      " 1   treatment_start   5596 non-null   datetime64[ns]\n",
      " 2   treatment_period  5596 non-null   int64         \n",
      " 3   visit_id          5596 non-null   int64         \n",
      " 4   vstart            5596 non-null   datetime64[ns]\n",
      " 5   vprovider         5587 non-null   float64       \n",
      " 6   care_site         5591 non-null   float64       \n",
      " 7   diagnosis_1       5596 non-null   object        \n",
      " 8   diagnosis_2       4258 non-null   object        \n",
      " 9   diagnosis_3       2125 non-null   object        \n",
      " 10  diagnosis_4       1026 non-null   object        \n",
      " 11  diagnosis_5       439 non-null    object        \n",
      " 12  diagnosis_6       185 non-null    object        \n",
      " 13  diagnosis_7       85 non-null     object        \n",
      " 14  diagnosis_8       34 non-null     object        \n",
      " 15  diagnosis_9       10 non-null     object        \n",
      " 16  diagnosis_10      2 non-null      object        \n",
      " 17  diagnosis_11      1 non-null      object        \n",
      " 18  snomed_1          5596 non-null   object        \n",
      " 19  snomed_2          4258 non-null   object        \n",
      " 20  snomed_3          2125 non-null   object        \n",
      " 21  snomed_4          1026 non-null   object        \n",
      " 22  snomed_5          439 non-null    object        \n",
      " 23  snomed_6          185 non-null    object        \n",
      " 24  snomed_7          85 non-null     object        \n",
      " 25  snomed_8          34 non-null     object        \n",
      " 26  snomed_9          10 non-null     object        \n",
      " 27  snomed_10         2 non-null      object        \n",
      " 28  snomed_11         1 non-null      object        \n",
      " 29  icd9_1            5596 non-null   object        \n",
      " 30  icd9_2            4285 non-null   object        \n",
      " 31  icd9_3            2086 non-null   object        \n",
      " 32  icd9_4            991 non-null    object        \n",
      " 33  icd9_5            414 non-null    object        \n",
      " 34  icd9_6            177 non-null    object        \n",
      " 35  icd9_7            81 non-null     object        \n",
      " 36  icd9_8            32 non-null     object        \n",
      " 37  icd9_9            10 non-null     object        \n",
      " 38  icd9_10           1 non-null      object        \n",
      " 39  icd9_11           1 non-null      object        \n",
      "dtypes: datetime64[ns](2), float64(2), int64(3), object(33)\n",
      "memory usage: 1.8+ MB\n"
     ]
    }
   ],
   "source": [
    "visits_ds.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eeb7fc0f",
   "metadata": {},
   "source": [
    "Clean and reshape procedures.\n",
    "\n",
    "Begin by bringing in CPT descriptions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "1628375b",
   "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>patient_id</th>\n",
       "      <th>drug_treatment_start</th>\n",
       "      <th>drug_treatment_period</th>\n",
       "      <th>vtype</th>\n",
       "      <th>visit_id</th>\n",
       "      <th>procedure_name</th>\n",
       "      <th>procedure_code</th>\n",
       "      <th>code_type</th>\n",
       "      <th>vstart</th>\n",
       "      <th>vend</th>\n",
       "      <th>vduration</th>\n",
       "      <th>vprovider</th>\n",
       "      <th>care_site</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>111598</td>\n",
       "      <td>2008-01-15</td>\n",
       "      <td>90</td>\n",
       "      <td>PROCEDURE</td>\n",
       "      <td>5351385</td>\n",
       "      <td>NaN</td>\n",
       "      <td>36415</td>\n",
       "      <td>CPT4</td>\n",
       "      <td>2008-01-28</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>315573.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>111598</td>\n",
       "      <td>2008-01-15</td>\n",
       "      <td>90</td>\n",
       "      <td>PROCEDURE</td>\n",
       "      <td>5351372</td>\n",
       "      <td>NaN</td>\n",
       "      <td>99214</td>\n",
       "      <td>CPT4</td>\n",
       "      <td>2008-01-28</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>50058.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>111598</td>\n",
       "      <td>2008-01-15</td>\n",
       "      <td>90</td>\n",
       "      <td>PROCEDURE</td>\n",
       "      <td>5351372</td>\n",
       "      <td>Bone graft, carpals and metacarpals</td>\n",
       "      <td>78.04</td>\n",
       "      <td>ICD9Proc</td>\n",
       "      <td>2008-01-28</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>50058.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>111598</td>\n",
       "      <td>2008-01-15</td>\n",
       "      <td>90</td>\n",
       "      <td>PROCEDURE</td>\n",
       "      <td>5351403</td>\n",
       "      <td>No matching concept</td>\n",
       "      <td>No matching concept</td>\n",
       "      <td>None</td>\n",
       "      <td>2008-02-05</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>382137.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>111598</td>\n",
       "      <td>2008-01-15</td>\n",
       "      <td>90</td>\n",
       "      <td>PROCEDURE</td>\n",
       "      <td>5351403</td>\n",
       "      <td>No matching concept</td>\n",
       "      <td>No matching concept</td>\n",
       "      <td>None</td>\n",
       "      <td>2008-02-05</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>382137.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   patient_id drug_treatment_start  drug_treatment_period      vtype  \\\n",
       "0      111598           2008-01-15                     90  PROCEDURE   \n",
       "1      111598           2008-01-15                     90  PROCEDURE   \n",
       "2      111598           2008-01-15                     90  PROCEDURE   \n",
       "3      111598           2008-01-15                     90  PROCEDURE   \n",
       "4      111598           2008-01-15                     90  PROCEDURE   \n",
       "\n",
       "   visit_id                       procedure_name       procedure_code  \\\n",
       "0   5351385                                  NaN                36415   \n",
       "1   5351372                                  NaN                99214   \n",
       "2   5351372  Bone graft, carpals and metacarpals                78.04   \n",
       "3   5351403                  No matching concept  No matching concept   \n",
       "4   5351403                  No matching concept  No matching concept   \n",
       "\n",
       "  code_type     vstart vend  vduration  vprovider  care_site  \n",
       "0      CPT4 2008-01-28  NaT        NaN   315573.0        NaN  \n",
       "1      CPT4 2008-01-28  NaT        NaN    50058.0        NaN  \n",
       "2  ICD9Proc 2008-01-28  NaT        NaN    50058.0        NaN  \n",
       "3      None 2008-02-05  NaT        NaN   382137.0        NaN  \n",
       "4      None 2008-02-05  NaT        NaN   382137.0        NaN  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "procedures_ds.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "b0c23ed4",
   "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>cpt_code</th>\n",
       "      <th>cpt_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>86890</td>\n",
       "      <td>Autologous blood process</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>86891</td>\n",
       "      <td>Autologous blood op salvage</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>86927</td>\n",
       "      <td>Plasma fresh frozen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>86930</td>\n",
       "      <td>Frozen blood prep</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>86931</td>\n",
       "      <td>Frozen blood thaw</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  cpt_code                     cpt_name\n",
       "0    86890     Autologous blood process\n",
       "1    86891  Autologous blood op salvage\n",
       "2    86927          Plasma fresh frozen\n",
       "3    86930            Frozen blood prep\n",
       "4    86931            Frozen blood thaw"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cpt_codes.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "1865e8d5",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 45206 entries, 0 to 45205\n",
      "Data columns (total 11 columns):\n",
      " #   Column            Non-Null Count  Dtype         \n",
      "---  ------            --------------  -----         \n",
      " 0   patient_id        45206 non-null  int64         \n",
      " 1   treatment_start   45206 non-null  datetime64[ns]\n",
      " 2   treatment_period  45206 non-null  int64         \n",
      " 3   vtype             45206 non-null  object        \n",
      " 4   visit_id          45206 non-null  int64         \n",
      " 5   procedure_name    11775 non-null  object        \n",
      " 6   procedure_code    45206 non-null  object        \n",
      " 7   code_type         45206 non-null  object        \n",
      " 8   vstart            45206 non-null  datetime64[ns]\n",
      " 9   vprovider         44975 non-null  float64       \n",
      " 10  cpt_name          45206 non-null  object        \n",
      "dtypes: datetime64[ns](2), float64(1), int64(3), object(5)\n",
      "memory usage: 4.1+ MB\n"
     ]
    }
   ],
   "source": [
    "cpt_procedures = procedures_ds[procedures_ds[\"code_type\"]==\"CPT4\"].copy()\n",
    "cpt_procedures = pd.merge(cpt_procedures, cpt_codes, left_on=\"procedure_code\", right_on=\"cpt_code\")\n",
    "procedures2 = pd.merge(procedures_ds, cpt_procedures, on=[\"patient_id\", \"visit_id\"])\n",
    "\n",
    "procedures2.drop([\"vend_x\", \"vduration_x\", \"care_site_x\", \"drug_treatment_start_y\", \"drug_treatment_period_y\", \"vtype_y\", \n",
    "                  \"procedure_name_y\", \"procedure_code_y\", \"code_type_y\", \"vstart_y\", \"vend_y\", \"vduration_y\", \"vprovider_y\", \n",
    "                  \"care_site_y\", \"cpt_code\"], axis=1, inplace=True)\n",
    "\n",
    "procedures2.columns = [\"patient_id\", \"treatment_start\", \"treatment_period\", \"vtype\", \"visit_id\", \"procedure_name\", \"procedure_code\",\n",
    "                       \"code_type\", \"vstart\", \"vprovider\", \"cpt_name\"]\n",
    "\n",
    "procedures2.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "2bd697d6",
   "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>patient_id</th>\n",
       "      <th>treatment_start</th>\n",
       "      <th>treatment_period</th>\n",
       "      <th>vtype</th>\n",
       "      <th>visit_id</th>\n",
       "      <th>procedure_name</th>\n",
       "      <th>procedure_code</th>\n",
       "      <th>code_type</th>\n",
       "      <th>vstart</th>\n",
       "      <th>vprovider</th>\n",
       "      <th>cpt_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>111598</td>\n",
       "      <td>2008-01-15</td>\n",
       "      <td>90</td>\n",
       "      <td>PROCEDURE</td>\n",
       "      <td>5351385</td>\n",
       "      <td>NaN</td>\n",
       "      <td>36415</td>\n",
       "      <td>CPT4</td>\n",
       "      <td>2008-01-28</td>\n",
       "      <td>315573.0</td>\n",
       "      <td>Routine venipuncture</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>111598</td>\n",
       "      <td>2008-01-15</td>\n",
       "      <td>90</td>\n",
       "      <td>PROCEDURE</td>\n",
       "      <td>5351372</td>\n",
       "      <td>NaN</td>\n",
       "      <td>99214</td>\n",
       "      <td>CPT4</td>\n",
       "      <td>2008-01-28</td>\n",
       "      <td>50058.0</td>\n",
       "      <td>established patient office visit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>111598</td>\n",
       "      <td>2008-01-15</td>\n",
       "      <td>90</td>\n",
       "      <td>PROCEDURE</td>\n",
       "      <td>5351372</td>\n",
       "      <td>Bone graft, carpals and metacarpals</td>\n",
       "      <td>78.04</td>\n",
       "      <td>ICD9Proc</td>\n",
       "      <td>2008-01-28</td>\n",
       "      <td>50058.0</td>\n",
       "      <td>established patient office visit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>111598</td>\n",
       "      <td>2008-01-15</td>\n",
       "      <td>90</td>\n",
       "      <td>PROCEDURE</td>\n",
       "      <td>5351324</td>\n",
       "      <td>NaN</td>\n",
       "      <td>99291</td>\n",
       "      <td>CPT4</td>\n",
       "      <td>2008-02-05</td>\n",
       "      <td>447710.0</td>\n",
       "      <td>Routine venipuncture</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>111598</td>\n",
       "      <td>2008-01-15</td>\n",
       "      <td>90</td>\n",
       "      <td>PROCEDURE</td>\n",
       "      <td>5351324</td>\n",
       "      <td>NaN</td>\n",
       "      <td>99291</td>\n",
       "      <td>CPT4</td>\n",
       "      <td>2008-02-05</td>\n",
       "      <td>447710.0</td>\n",
       "      <td>critical care visit</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   patient_id treatment_start  treatment_period      vtype  visit_id  \\\n",
       "0      111598      2008-01-15                90  PROCEDURE   5351385   \n",
       "1      111598      2008-01-15                90  PROCEDURE   5351372   \n",
       "2      111598      2008-01-15                90  PROCEDURE   5351372   \n",
       "3      111598      2008-01-15                90  PROCEDURE   5351324   \n",
       "4      111598      2008-01-15                90  PROCEDURE   5351324   \n",
       "\n",
       "                        procedure_name procedure_code code_type     vstart  \\\n",
       "0                                  NaN          36415      CPT4 2008-01-28   \n",
       "1                                  NaN          99214      CPT4 2008-01-28   \n",
       "2  Bone graft, carpals and metacarpals          78.04  ICD9Proc 2008-01-28   \n",
       "3                                  NaN          99291      CPT4 2008-02-05   \n",
       "4                                  NaN          99291      CPT4 2008-02-05   \n",
       "\n",
       "   vprovider                          cpt_name  \n",
       "0   315573.0              Routine venipuncture  \n",
       "1    50058.0  established patient office visit  \n",
       "2    50058.0  established patient office visit  \n",
       "3   447710.0              Routine venipuncture  \n",
       "4   447710.0               critical care visit  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "procedures_ds = procedures2.copy()\n",
    "procedures_ds.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "78a4de3c",
   "metadata": {},
   "source": [
    "Align and 'explode' procedure codes, code types and procedure descriptions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "45466500",
   "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>visit_id</th>\n",
       "      <th>procedure_code</th>\n",
       "      <th>code_type</th>\n",
       "      <th>description</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>44245</td>\n",
       "      <td>94640</td>\n",
       "      <td>CPT4</td>\n",
       "      <td>Airway inhalation treatment</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>44246</td>\n",
       "      <td>36415</td>\n",
       "      <td>CPT4</td>\n",
       "      <td>Routine venipuncture</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>44260</td>\n",
       "      <td>99334</td>\n",
       "      <td>CPT4</td>\n",
       "      <td>home health care, established patient</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>44262</td>\n",
       "      <td>88305</td>\n",
       "      <td>CPT4</td>\n",
       "      <td>Surgical pathology</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>44270</td>\n",
       "      <td>92012</td>\n",
       "      <td>CPT4;CPT4</td>\n",
       "      <td>FUNDUS PHOTOGRAPHY WITH INTERPRETATION AND REP...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   visit_id procedure_code  code_type  \\\n",
       "0     44245          94640       CPT4   \n",
       "1     44246          36415       CPT4   \n",
       "2     44260          99334       CPT4   \n",
       "3     44262          88305       CPT4   \n",
       "4     44270          92012  CPT4;CPT4   \n",
       "\n",
       "                                         description  \n",
       "0                        Airway inhalation treatment  \n",
       "1                               Routine venipuncture  \n",
       "2              home health care, established patient  \n",
       "3                                 Surgical pathology  \n",
       "4  FUNDUS PHOTOGRAPHY WITH INTERPRETATION AND REP...  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "procedures2 = procedures_ds[[\"visit_id\", \"code_type\", \"procedure_code\", \"procedure_name\", \"cpt_name\"]].copy()\n",
    "\n",
    "procedures2.loc[procedures2[\"procedure_code\"]==\"None\", \"procedure_code\"] = np.nan\n",
    "procedures2.loc[procedures2[\"procedure_code\"]==\"No matching concept\", \"procedure_code\"] = np.nan\n",
    "procedures2.loc[procedures2[\"code_type\"]==\"None\", \"code_type\"] = np.nan\n",
    "procedures2.loc[procedures2[\"procedure_name\"]==\"No matching concept\", \"procedure_name\"] = np.nan\n",
    "\n",
    "procedures2[\"procedure_name\"] = procedures2[\"procedure_name\"].fillna('')\n",
    "procedures2[\"cpt_name\"] = procedures2[\"cpt_name\"].fillna('')\n",
    "procedures2[\"code_type\"] = procedures2[\"code_type\"].fillna('')\n",
    "\n",
    "procedures2 = procedures2.groupby(['visit_id', 'procedure_code'], as_index = False).agg({'code_type': lambda x: ';'.join(x), \n",
    "                                                                                       'procedure_name': lambda x: ';'.join(set(x)),\n",
    "                                                                                       'cpt_name': lambda x: '; '.join(set(x)) })\n",
    "\n",
    "procedures2[\"description\"] = procedures2[\"procedure_name\"] + procedures2[\"cpt_name\"]\n",
    "procedures2.drop([\"procedure_name\", \"cpt_name\"], axis=1, inplace=True, errors=\"ignore\")\n",
    "\n",
    "procedures2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "f97b3a6a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 4730 entries, 0 to 4729\n",
      "Data columns (total 49 columns):\n",
      " #   Column    Non-Null Count  Dtype \n",
      "---  ------    --------------  ----- \n",
      " 0   pc_1      4730 non-null   object\n",
      " 1   pc_2      3093 non-null   object\n",
      " 2   pc_3      1618 non-null   object\n",
      " 3   pc_4      711 non-null    object\n",
      " 4   pc_5      323 non-null    object\n",
      " 5   pc_6      135 non-null    object\n",
      " 6   pc_7      68 non-null     object\n",
      " 7   pc_8      41 non-null     object\n",
      " 8   pc_9      22 non-null     object\n",
      " 9   pc_10     11 non-null     object\n",
      " 10  pc_11     8 non-null      object\n",
      " 11  pc_12     5 non-null      object\n",
      " 12  pc_13     4 non-null      object\n",
      " 13  pc_14     4 non-null      object\n",
      " 14  pc_15     2 non-null      object\n",
      " 15  pc_16     1 non-null      object\n",
      " 16  pct_1     4730 non-null   object\n",
      " 17  pct_2     3093 non-null   object\n",
      " 18  pct_3     1618 non-null   object\n",
      " 19  pct_4     711 non-null    object\n",
      " 20  pct_5     323 non-null    object\n",
      " 21  pct_6     135 non-null    object\n",
      " 22  pct_7     68 non-null     object\n",
      " 23  pct_8     41 non-null     object\n",
      " 24  pct_9     22 non-null     object\n",
      " 25  pct_10    11 non-null     object\n",
      " 26  pct_11    8 non-null      object\n",
      " 27  pct_12    5 non-null      object\n",
      " 28  pct_13    4 non-null      object\n",
      " 29  pct_14    4 non-null      object\n",
      " 30  pct_15    2 non-null      object\n",
      " 31  pct_16    1 non-null      object\n",
      " 32  pdesc_1   4730 non-null   object\n",
      " 33  pdesc_2   3093 non-null   object\n",
      " 34  pdesc_3   1618 non-null   object\n",
      " 35  pdesc_4   711 non-null    object\n",
      " 36  pdesc_5   323 non-null    object\n",
      " 37  pdesc_6   135 non-null    object\n",
      " 38  pdesc_7   68 non-null     object\n",
      " 39  pdesc_8   41 non-null     object\n",
      " 40  pdesc_9   22 non-null     object\n",
      " 41  pdesc_10  11 non-null     object\n",
      " 42  pdesc_11  8 non-null      object\n",
      " 43  pdesc_12  5 non-null      object\n",
      " 44  pdesc_13  4 non-null      object\n",
      " 45  pdesc_14  4 non-null      object\n",
      " 46  pdesc_15  2 non-null      object\n",
      " 47  pdesc_16  1 non-null      object\n",
      " 48  visit_id  4730 non-null   int64 \n",
      "dtypes: int64(1), object(48)\n",
      "memory usage: 1.8+ MB\n"
     ]
    }
   ],
   "source": [
    "grouped_p = procedures2.groupby(['visit_id'], as_index = False).agg({'procedure_code': lambda x: '~'.join(x), \n",
    "                                                                'code_type': lambda x: '~'.join(x),\n",
    "                                                                'description': lambda x: '~'.join(x)})\n",
    "long_p = {}\n",
    "long_p[0] = grouped_p['procedure_code'].str.split(pat=\"~\", expand=True)\n",
    "long_p[1] = grouped_p['code_type'].str.split(pat=\"~\",expand=True)\n",
    "long_p[2] = grouped_p['description'].str.split(pat=\"~\",expand=True)\n",
    "wide_p = pd.concat(long_p, axis=1)\n",
    "wide_p[\"visit_id\"] = grouped_p[\"visit_id\"].copy()\n",
    "\n",
    "\n",
    "wide_p.columns = [\"pc_1\", \"pc_2\", \"pc_3\", \"pc_4\", \"pc_5\", \"pc_6\", \"pc_7\", \"pc_8\", \"pc_9\", \"pc_10\", \"pc_11\", \"pc_12\", \"pc_13\", \"pc_14\", \n",
    "                  \"pc_15\", \"pc_16\", \"pct_1\", \"pct_2\", \"pct_3\", \"pct_4\", \"pct_5\", \"pct_6\", \"pct_7\", \"pct_8\", \"pct_9\", \"pct_10\", \"pct_11\", \n",
    "                  \"pct_12\", \"pct_13\", \"pct_14\", \"pct_15\", \"pct_16\", \"pdesc_1\", \"pdesc_2\", \"pdesc_3\", \"pdesc_4\", \"pdesc_5\", \n",
    "                  \"pdesc_6\", \"pdesc_7\", \"pdesc_8\", \"pdesc_9\", \"pdesc_10\", \"pdesc_11\", \"pdesc_12\", \"pdesc_13\", \n",
    "                  \"pdesc_14\", \"pdesc_15\", \"pdesc_16\", \"visit_id\"]\n",
    "\n",
    "wide_p.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "c0ca4bef",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(4730, 49)"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wide_p.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "944b7cd0",
   "metadata": {},
   "source": [
    "Clean procedure types, so each type is listed once per field."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "abea6040",
   "metadata": {},
   "outputs": [],
   "source": [
    "code_types = [\"pct_1\", \"pct_2\", \"pct_3\", \"pct_4\", \"pct_5\", \"pct_6\", \"pct_7\", \"pct_8\", \"pct_9\", \"pct_10\", \"pct_11\", \n",
    "       \"pct_12\", \"pct_13\", \"pct_14\", \"pct_15\", \"pct_16\"]\n",
    "\n",
    "for r in range(0, len(wide_p)):\n",
    "    for c in code_types:\n",
    "        if wide_p.at[r, c] != None:\n",
    "            if len(wide_p.at[r, c].split(\";\")) > 1:\n",
    "                wide_p.at[r, c] = wide_p.at[r, c].split(\";\")[0]\n",
    "                if isinstance(wide_p.at[r, c], (list, tuple)):\n",
    "                     wide_p.at[r, c] = wide_p.at[r, c][0]\n",
    "        "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c3e57108",
   "metadata": {},
   "source": [
    "Combine procedure code type, code and description into a single field for each procedure code associated with a visit."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "0fb7bf2d",
   "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>pc_1</th>\n",
       "      <th>pc_2</th>\n",
       "      <th>pc_3</th>\n",
       "      <th>pc_4</th>\n",
       "      <th>pc_5</th>\n",
       "      <th>pc_6</th>\n",
       "      <th>pc_7</th>\n",
       "      <th>pc_8</th>\n",
       "      <th>pc_9</th>\n",
       "      <th>pc_10</th>\n",
       "      <th>...</th>\n",
       "      <th>pdesc_8</th>\n",
       "      <th>pdesc_9</th>\n",
       "      <th>pdesc_10</th>\n",
       "      <th>pdesc_11</th>\n",
       "      <th>pdesc_12</th>\n",
       "      <th>pdesc_13</th>\n",
       "      <th>pdesc_14</th>\n",
       "      <th>pdesc_15</th>\n",
       "      <th>pdesc_16</th>\n",
       "      <th>visit_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>94640</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>44245</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>36415</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>44246</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>99334</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>44260</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>88305</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>44262</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>92012</td>\n",
       "      <td>92250</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>44270</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 49 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    pc_1   pc_2  pc_3  pc_4  pc_5  pc_6  pc_7  pc_8  pc_9 pc_10  ... pdesc_8  \\\n",
       "0  94640   None  None  None  None  None  None  None  None  None  ...    None   \n",
       "1  36415   None  None  None  None  None  None  None  None  None  ...    None   \n",
       "2  99334   None  None  None  None  None  None  None  None  None  ...    None   \n",
       "3  88305   None  None  None  None  None  None  None  None  None  ...    None   \n",
       "4  92012  92250  None  None  None  None  None  None  None  None  ...    None   \n",
       "\n",
       "  pdesc_9 pdesc_10 pdesc_11 pdesc_12 pdesc_13 pdesc_14 pdesc_15 pdesc_16  \\\n",
       "0    None     None     None     None     None     None     None     None   \n",
       "1    None     None     None     None     None     None     None     None   \n",
       "2    None     None     None     None     None     None     None     None   \n",
       "3    None     None     None     None     None     None     None     None   \n",
       "4    None     None     None     None     None     None     None     None   \n",
       "\n",
       "  visit_id  \n",
       "0    44245  \n",
       "1    44246  \n",
       "2    44260  \n",
       "3    44262  \n",
       "4    44270  \n",
       "\n",
       "[5 rows x 49 columns]"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "desc_cols = [\"pdesc_1\", \"pdesc_2\", \"pdesc_3\", \"pdesc_4\", \"pdesc_5\", \"pdesc_6\", \"pdesc_7\", \"pdesc_8\", \"pdesc_9\", \"pdesc_10\", \"pdesc_11\", \n",
    "             \"pdesc_12\", \"pdesc_13\", \"pdesc_14\", \"pdesc_15\", \"pdesc_16\"]\n",
    "\n",
    "for r in range(0, len(wide_p)):\n",
    "    count = 1\n",
    "    for d in desc_cols:\n",
    "        c1 = \"pc_\" + str(count)\n",
    "        c2 = \"pct_\" + str(count) \n",
    "        if wide_p.at[r, d] != None:\n",
    "            if  wide_p.at[r, c2] != None:\n",
    "                if wide_p.at[r, c1] != None:\n",
    "                    wide_p.at[r, d] = wide_p.at[r, c2] + \": \" + wide_p.at[r, c1] + \" \" + wide_p.at[r, d]\n",
    "                else:\n",
    "                    wide_p.at[r, d] = wide_p.at[r, c2] + \": \" + wide_p.at[r, d]\n",
    "            else:\n",
    "                 wide_p.at[r, d] = wide_p.at[r, c1] + \": \" + wide_p.at[r, d]\n",
    "        else:\n",
    "            pass\n",
    "        count += 1\n",
    "    \n",
    "wide_p.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9aeb6948",
   "metadata": {},
   "source": [
    "Now that the procedure information is combined, drop the unnecessary columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "1a8d6b52",
   "metadata": {},
   "outputs": [],
   "source": [
    "drop_cols = [\"pc_1\", \"pc_2\", \"pc_3\", \"pc_4\", \"pc_5\", \"pc_6\", \"pc_7\", \"pc_8\", \"pc_9\", \"pc_10\", \"pc_11\", \"pc_12\", \"pc_13\", \"pc_14\",\n",
    "             \"pc_15\", \"pc_16\", \"pct_1\", \"pct_2\", \"pct_3\", \"pct_4\", \"pct_5\", \"pct_6\", \"pct_7\", \"pct_8\", \"pct_9\", \"pct_10\", \"pct_11\", \n",
    "             \"pct_12\", \"pct_13\", \"pct_14\", \"pct_15\", \"pct_16\"]\n",
    "\n",
    "wide_p.drop(drop_cols, inplace=True, axis=1, errors=\"ignore\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "1269186c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 4730 entries, 0 to 4729\n",
      "Data columns (total 17 columns):\n",
      " #   Column    Non-Null Count  Dtype \n",
      "---  ------    --------------  ----- \n",
      " 0   pdesc_1   4730 non-null   object\n",
      " 1   pdesc_2   3093 non-null   object\n",
      " 2   pdesc_3   1618 non-null   object\n",
      " 3   pdesc_4   711 non-null    object\n",
      " 4   pdesc_5   323 non-null    object\n",
      " 5   pdesc_6   135 non-null    object\n",
      " 6   pdesc_7   68 non-null     object\n",
      " 7   pdesc_8   41 non-null     object\n",
      " 8   pdesc_9   22 non-null     object\n",
      " 9   pdesc_10  11 non-null     object\n",
      " 10  pdesc_11  8 non-null      object\n",
      " 11  pdesc_12  5 non-null      object\n",
      " 12  pdesc_13  4 non-null      object\n",
      " 13  pdesc_14  4 non-null      object\n",
      " 14  pdesc_15  2 non-null      object\n",
      " 15  pdesc_16  1 non-null      object\n",
      " 16  visit_id  4730 non-null   int64 \n",
      "dtypes: int64(1), object(16)\n",
      "memory usage: 628.3+ KB\n"
     ]
    }
   ],
   "source": [
    "wide_p.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "90f67008",
   "metadata": {},
   "source": [
    "Flatten the procedures data frame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "915226ef",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(4734, 7)"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "procedures_ds = procedures_ds[[\"patient_id\", \"treatment_start\", \"treatment_period\", \"vtype\", \"visit_id\", \"vstart\", \"vprovider\"]].copy()\n",
    "procedures_ds  = procedures_ds .drop_duplicates()\n",
    "procedures_ds.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ccb5a8b6",
   "metadata": {},
   "source": [
    "Merge it with the detailed procedure information."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "10576ad0",
   "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>patient_id</th>\n",
       "      <th>treatment_start</th>\n",
       "      <th>treatment_period</th>\n",
       "      <th>vtype</th>\n",
       "      <th>visit_id</th>\n",
       "      <th>vstart</th>\n",
       "      <th>vprovider</th>\n",
       "      <th>pdesc_1</th>\n",
       "      <th>pdesc_2</th>\n",
       "      <th>pdesc_3</th>\n",
       "      <th>...</th>\n",
       "      <th>pdesc_7</th>\n",
       "      <th>pdesc_8</th>\n",
       "      <th>pdesc_9</th>\n",
       "      <th>pdesc_10</th>\n",
       "      <th>pdesc_11</th>\n",
       "      <th>pdesc_12</th>\n",
       "      <th>pdesc_13</th>\n",
       "      <th>pdesc_14</th>\n",
       "      <th>pdesc_15</th>\n",
       "      <th>pdesc_16</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>111598</td>\n",
       "      <td>2008-01-15</td>\n",
       "      <td>90</td>\n",
       "      <td>PROCEDURE</td>\n",
       "      <td>5351385</td>\n",
       "      <td>2008-01-28</td>\n",
       "      <td>315573.0</td>\n",
       "      <td>CPT4: 36415 Routine venipuncture</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>111598</td>\n",
       "      <td>2008-01-15</td>\n",
       "      <td>90</td>\n",
       "      <td>PROCEDURE</td>\n",
       "      <td>5351372</td>\n",
       "      <td>2008-01-28</td>\n",
       "      <td>50058.0</td>\n",
       "      <td>ICD9Proc: 78.04 Bone graft, carpals and metaca...</td>\n",
       "      <td>CPT4: 99214 established patient office visit</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>111598</td>\n",
       "      <td>2008-01-15</td>\n",
       "      <td>90</td>\n",
       "      <td>PROCEDURE</td>\n",
       "      <td>5351324</td>\n",
       "      <td>2008-02-05</td>\n",
       "      <td>447710.0</td>\n",
       "      <td>CPT4: 36415 Routine venipuncture; critical car...</td>\n",
       "      <td>CPT4: 90935 Routine venipuncture; critical car...</td>\n",
       "      <td>CPT4: 99291 Routine venipuncture; critical car...</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>111598</td>\n",
       "      <td>2008-01-15</td>\n",
       "      <td>90</td>\n",
       "      <td>PROCEDURE</td>\n",
       "      <td>5351415</td>\n",
       "      <td>2008-02-08</td>\n",
       "      <td>449900.0</td>\n",
       "      <td>CPT4: 00142 Anesthesia, head</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>111598</td>\n",
       "      <td>2008-01-15</td>\n",
       "      <td>90</td>\n",
       "      <td>PROCEDURE</td>\n",
       "      <td>5351333</td>\n",
       "      <td>2008-02-13</td>\n",
       "      <td>50055.0</td>\n",
       "      <td>ICD9Proc: 22.6 Other nasal sinusectomyestablis...</td>\n",
       "      <td>CPT4: 99213 established patient office visit</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   patient_id treatment_start  treatment_period      vtype  visit_id  \\\n",
       "0      111598      2008-01-15                90  PROCEDURE   5351385   \n",
       "1      111598      2008-01-15                90  PROCEDURE   5351372   \n",
       "2      111598      2008-01-15                90  PROCEDURE   5351324   \n",
       "3      111598      2008-01-15                90  PROCEDURE   5351415   \n",
       "4      111598      2008-01-15                90  PROCEDURE   5351333   \n",
       "\n",
       "      vstart  vprovider                                            pdesc_1  \\\n",
       "0 2008-01-28   315573.0                   CPT4: 36415 Routine venipuncture   \n",
       "1 2008-01-28    50058.0  ICD9Proc: 78.04 Bone graft, carpals and metaca...   \n",
       "2 2008-02-05   447710.0  CPT4: 36415 Routine venipuncture; critical car...   \n",
       "3 2008-02-08   449900.0                       CPT4: 00142 Anesthesia, head   \n",
       "4 2008-02-13    50055.0  ICD9Proc: 22.6 Other nasal sinusectomyestablis...   \n",
       "\n",
       "                                             pdesc_2  \\\n",
       "0                                               None   \n",
       "1       CPT4: 99214 established patient office visit   \n",
       "2  CPT4: 90935 Routine venipuncture; critical car...   \n",
       "3                                               None   \n",
       "4       CPT4: 99213 established patient office visit   \n",
       "\n",
       "                                             pdesc_3  ... pdesc_7 pdesc_8  \\\n",
       "0                                               None  ...    None    None   \n",
       "1                                               None  ...    None    None   \n",
       "2  CPT4: 99291 Routine venipuncture; critical car...  ...    None    None   \n",
       "3                                               None  ...    None    None   \n",
       "4                                               None  ...    None    None   \n",
       "\n",
       "  pdesc_9 pdesc_10 pdesc_11 pdesc_12 pdesc_13 pdesc_14 pdesc_15 pdesc_16  \n",
       "0    None     None     None     None     None     None     None     None  \n",
       "1    None     None     None     None     None     None     None     None  \n",
       "2    None     None     None     None     None     None     None     None  \n",
       "3    None     None     None     None     None     None     None     None  \n",
       "4    None     None     None     None     None     None     None     None  \n",
       "\n",
       "[5 rows x 23 columns]"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "procedures_ds = pd.merge(procedures_ds, wide_p, on=\"visit_id\", how=\"left\")\n",
    "procedures_ds.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "97d5f0b3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(5596, 40)"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "visits_ds.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "24b31479",
   "metadata": {},
   "source": [
    "Combine procedure information with visits for the rows that have procedure information."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "7b354c75",
   "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>patient_id_x</th>\n",
       "      <th>treatment_start_x</th>\n",
       "      <th>treatment_period_x</th>\n",
       "      <th>visit_id</th>\n",
       "      <th>vstart_x</th>\n",
       "      <th>vprovider_x</th>\n",
       "      <th>care_site</th>\n",
       "      <th>diagnosis_1</th>\n",
       "      <th>diagnosis_2</th>\n",
       "      <th>diagnosis_3</th>\n",
       "      <th>...</th>\n",
       "      <th>pdesc_7</th>\n",
       "      <th>pdesc_8</th>\n",
       "      <th>pdesc_9</th>\n",
       "      <th>pdesc_10</th>\n",
       "      <th>pdesc_11</th>\n",
       "      <th>pdesc_12</th>\n",
       "      <th>pdesc_13</th>\n",
       "      <th>pdesc_14</th>\n",
       "      <th>pdesc_15</th>\n",
       "      <th>pdesc_16</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>111598</td>\n",
       "      <td>2008-01-15</td>\n",
       "      <td>90</td>\n",
       "      <td>5351372</td>\n",
       "      <td>2008-01-28</td>\n",
       "      <td>50058.0</td>\n",
       "      <td>11234.0</td>\n",
       "      <td>Vomiting</td>\n",
       "      <td>Epidemic vertigo</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>111598</td>\n",
       "      <td>2008-01-15</td>\n",
       "      <td>90</td>\n",
       "      <td>5351385</td>\n",
       "      <td>2008-01-28</td>\n",
       "      <td>315573.0</td>\n",
       "      <td>11283.0</td>\n",
       "      <td>Atrial fibrillation</td>\n",
       "      <td>Paroxysmal supraventricular tachycardia</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>111598</td>\n",
       "      <td>2008-01-15</td>\n",
       "      <td>90</td>\n",
       "      <td>5351386</td>\n",
       "      <td>2008-01-31</td>\n",
       "      <td>218450.0</td>\n",
       "      <td>11234.0</td>\n",
       "      <td>Atrial fibrillation</td>\n",
       "      <td>Premature beats</td>\n",
       "      <td>Type 2 diabetes mellitus</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>111598</td>\n",
       "      <td>2008-01-15</td>\n",
       "      <td>90</td>\n",
       "      <td>5351403</td>\n",
       "      <td>2008-02-05</td>\n",
       "      <td>382137.0</td>\n",
       "      <td>28276.0</td>\n",
       "      <td>Angina decubitus</td>\n",
       "      <td>Mitral and aortic stenosis</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>111598</td>\n",
       "      <td>2008-01-15</td>\n",
       "      <td>90</td>\n",
       "      <td>5351324</td>\n",
       "      <td>2008-02-05</td>\n",
       "      <td>447710.0</td>\n",
       "      <td>11222.0</td>\n",
       "      <td>Conduction disorder of the heart</td>\n",
       "      <td>Atrial fibrillation</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 62 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   patient_id_x treatment_start_x  treatment_period_x  visit_id   vstart_x  \\\n",
       "0        111598        2008-01-15                  90   5351372 2008-01-28   \n",
       "1        111598        2008-01-15                  90   5351385 2008-01-28   \n",
       "2        111598        2008-01-15                  90   5351386 2008-01-31   \n",
       "3        111598        2008-01-15                  90   5351403 2008-02-05   \n",
       "4        111598        2008-01-15                  90   5351324 2008-02-05   \n",
       "\n",
       "   vprovider_x  care_site                       diagnosis_1  \\\n",
       "0      50058.0    11234.0                          Vomiting   \n",
       "1     315573.0    11283.0               Atrial fibrillation   \n",
       "2     218450.0    11234.0               Atrial fibrillation   \n",
       "3     382137.0    28276.0                  Angina decubitus   \n",
       "4     447710.0    11222.0  Conduction disorder of the heart   \n",
       "\n",
       "                               diagnosis_2               diagnosis_3  ...  \\\n",
       "0                         Epidemic vertigo                      None  ...   \n",
       "1  Paroxysmal supraventricular tachycardia                      None  ...   \n",
       "2                          Premature beats  Type 2 diabetes mellitus  ...   \n",
       "3               Mitral and aortic stenosis                      None  ...   \n",
       "4                      Atrial fibrillation                      None  ...   \n",
       "\n",
       "  pdesc_7 pdesc_8 pdesc_9 pdesc_10 pdesc_11 pdesc_12 pdesc_13 pdesc_14  \\\n",
       "0    None    None    None     None     None     None     None     None   \n",
       "1    None    None    None     None     None     None     None     None   \n",
       "2     NaN     NaN     NaN      NaN      NaN      NaN      NaN      NaN   \n",
       "3     NaN     NaN     NaN      NaN      NaN      NaN      NaN      NaN   \n",
       "4    None    None    None     None     None     None     None     None   \n",
       "\n",
       "  pdesc_15 pdesc_16  \n",
       "0     None     None  \n",
       "1     None     None  \n",
       "2      NaN      NaN  \n",
       "3      NaN      NaN  \n",
       "4     None     None  \n",
       "\n",
       "[5 rows x 62 columns]"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "visits_procedures_ds = pd.merge(visits_ds, procedures_ds, on=\"visit_id\", how=\"left\")\n",
    "visits_procedures_ds.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "42a00834",
   "metadata": {},
   "outputs": [],
   "source": [
    "visits_procedures_ds.to_csv(\"visits_procedures_wide.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a7dbe947",
   "metadata": {},
   "source": [
    "Reimport the file after row-title cleanup in Excel."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "f9777092",
   "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>patient_id</th>\n",
       "      <th>treatment_start</th>\n",
       "      <th>treatment_period</th>\n",
       "      <th>visit_id</th>\n",
       "      <th>vstart</th>\n",
       "      <th>vprovider</th>\n",
       "      <th>care_site</th>\n",
       "      <th>diagnosis_1</th>\n",
       "      <th>diagnosis_2</th>\n",
       "      <th>diagnosis_3</th>\n",
       "      <th>...</th>\n",
       "      <th>pdesc_7</th>\n",
       "      <th>pdesc_8</th>\n",
       "      <th>pdesc_9</th>\n",
       "      <th>pdesc_10</th>\n",
       "      <th>pdesc_11</th>\n",
       "      <th>pdesc_12</th>\n",
       "      <th>pdesc_13</th>\n",
       "      <th>pdesc_14</th>\n",
       "      <th>pdesc_15</th>\n",
       "      <th>pdesc_16</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>111598</td>\n",
       "      <td>2008-01-15</td>\n",
       "      <td>90</td>\n",
       "      <td>5351372</td>\n",
       "      <td>2008-01-28</td>\n",
       "      <td>50058.0</td>\n",
       "      <td>11234.0</td>\n",
       "      <td>Vomiting</td>\n",
       "      <td>Epidemic vertigo</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>111598</td>\n",
       "      <td>2008-01-15</td>\n",
       "      <td>90</td>\n",
       "      <td>5351385</td>\n",
       "      <td>2008-01-28</td>\n",
       "      <td>315573.0</td>\n",
       "      <td>11283.0</td>\n",
       "      <td>Atrial fibrillation</td>\n",
       "      <td>Paroxysmal supraventricular tachycardia</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>111598</td>\n",
       "      <td>2008-01-15</td>\n",
       "      <td>90</td>\n",
       "      <td>5351386</td>\n",
       "      <td>2008-01-31</td>\n",
       "      <td>218450.0</td>\n",
       "      <td>11234.0</td>\n",
       "      <td>Atrial fibrillation</td>\n",
       "      <td>Premature beats</td>\n",
       "      <td>Type 2 diabetes mellitus</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>111598</td>\n",
       "      <td>2008-01-15</td>\n",
       "      <td>90</td>\n",
       "      <td>5351403</td>\n",
       "      <td>2008-02-05</td>\n",
       "      <td>382137.0</td>\n",
       "      <td>28276.0</td>\n",
       "      <td>Angina decubitus</td>\n",
       "      <td>Mitral and aortic stenosis</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>111598</td>\n",
       "      <td>2008-01-15</td>\n",
       "      <td>90</td>\n",
       "      <td>5351324</td>\n",
       "      <td>2008-02-05</td>\n",
       "      <td>447710.0</td>\n",
       "      <td>11222.0</td>\n",
       "      <td>Conduction disorder of the heart</td>\n",
       "      <td>Atrial fibrillation</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 56 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   patient_id treatment_start  treatment_period  visit_id     vstart  \\\n",
       "0      111598      2008-01-15                90   5351372 2008-01-28   \n",
       "1      111598      2008-01-15                90   5351385 2008-01-28   \n",
       "2      111598      2008-01-15                90   5351386 2008-01-31   \n",
       "3      111598      2008-01-15                90   5351403 2008-02-05   \n",
       "4      111598      2008-01-15                90   5351324 2008-02-05   \n",
       "\n",
       "   vprovider  care_site                       diagnosis_1  \\\n",
       "0    50058.0    11234.0                          Vomiting   \n",
       "1   315573.0    11283.0               Atrial fibrillation   \n",
       "2   218450.0    11234.0               Atrial fibrillation   \n",
       "3   382137.0    28276.0                  Angina decubitus   \n",
       "4   447710.0    11222.0  Conduction disorder of the heart   \n",
       "\n",
       "                               diagnosis_2               diagnosis_3  ...  \\\n",
       "0                         Epidemic vertigo                       NaN  ...   \n",
       "1  Paroxysmal supraventricular tachycardia                       NaN  ...   \n",
       "2                          Premature beats  Type 2 diabetes mellitus  ...   \n",
       "3               Mitral and aortic stenosis                       NaN  ...   \n",
       "4                      Atrial fibrillation                       NaN  ...   \n",
       "\n",
       "  pdesc_7 pdesc_8 pdesc_9 pdesc_10 pdesc_11 pdesc_12 pdesc_13 pdesc_14  \\\n",
       "0     NaN     NaN     NaN      NaN      NaN      NaN      NaN      NaN   \n",
       "1     NaN     NaN     NaN      NaN      NaN      NaN      NaN      NaN   \n",
       "2     NaN     NaN     NaN      NaN      NaN      NaN      NaN      NaN   \n",
       "3     NaN     NaN     NaN      NaN      NaN      NaN      NaN      NaN   \n",
       "4     NaN     NaN     NaN      NaN      NaN      NaN      NaN      NaN   \n",
       "\n",
       "  pdesc_15 pdesc_16  \n",
       "0      NaN      NaN  \n",
       "1      NaN      NaN  \n",
       "2      NaN      NaN  \n",
       "3      NaN      NaN  \n",
       "4      NaN      NaN  \n",
       "\n",
       "[5 rows x 56 columns]"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "visits_procedures_ds = pd.read_csv(\"visits_procedures_wide.csv\", parse_dates=[\"treatment_start\", \"vstart\"])\n",
    "visits_procedures_ds.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "86bd4a36",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(5600, 56)"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "visits_procedures_ds.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fafba653",
   "metadata": {},
   "source": [
    "There are 5,600 total visits for the 203-person cohort.\n",
    "\n",
    "Now, we'll look at conditions information to determine which of these visits happened during a condition period for the 97 people who had a code for a lung, liver or thyroid-related disorder following amiodarone therapy."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "233e9746",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(307, 12)"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conditions_ds.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "89c59da0",
   "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>patient_id</th>\n",
       "      <th>treatment_start</th>\n",
       "      <th>visit_id</th>\n",
       "      <th>ICD9CODE</th>\n",
       "      <th>ICD9_name</th>\n",
       "      <th>snomed_name</th>\n",
       "      <th>condition_type</th>\n",
       "      <th>snomed_code</th>\n",
       "      <th>condition_start_date</th>\n",
       "      <th>condition_end_date</th>\n",
       "      <th>condition_interval_days</th>\n",
       "      <th>treatment_condition_interval_days</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>11392</td>\n",
       "      <td>2008-06-08</td>\n",
       "      <td>552989</td>\n",
       "      <td>242.9</td>\n",
       "      <td>Thyrotoxicosis without mention of goiter or ot...</td>\n",
       "      <td>Thyrotoxicosis without goiter OR other cause</td>\n",
       "      <td>THYROID</td>\n",
       "      <td>89719007</td>\n",
       "      <td>2008-06-16</td>\n",
       "      <td>2008-06-16</td>\n",
       "      <td>0</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>18427</td>\n",
       "      <td>2009-08-04</td>\n",
       "      <td>893707</td>\n",
       "      <td>242.9</td>\n",
       "      <td>Thyrotoxicosis without mention of goiter or ot...</td>\n",
       "      <td>Thyrotoxicosis without goiter OR other cause</td>\n",
       "      <td>THYROID</td>\n",
       "      <td>89719007</td>\n",
       "      <td>2010-10-02</td>\n",
       "      <td>2010-10-02</td>\n",
       "      <td>0</td>\n",
       "      <td>424</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>30161</td>\n",
       "      <td>2008-05-07</td>\n",
       "      <td>1457808</td>\n",
       "      <td>242.9</td>\n",
       "      <td>Thyrotoxicosis without mention of goiter or ot...</td>\n",
       "      <td>Thyrotoxicosis without goiter OR other cause</td>\n",
       "      <td>THYROID</td>\n",
       "      <td>89719007</td>\n",
       "      <td>2010-06-08</td>\n",
       "      <td>2010-06-08</td>\n",
       "      <td>0</td>\n",
       "      <td>762</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>38696</td>\n",
       "      <td>2009-09-15</td>\n",
       "      <td>1871971</td>\n",
       "      <td>242.9</td>\n",
       "      <td>Thyrotoxicosis without mention of goiter or ot...</td>\n",
       "      <td>Thyrotoxicosis without goiter OR other cause</td>\n",
       "      <td>THYROID</td>\n",
       "      <td>89719007</td>\n",
       "      <td>2010-10-13</td>\n",
       "      <td>2010-10-13</td>\n",
       "      <td>0</td>\n",
       "      <td>393</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>38696</td>\n",
       "      <td>2009-09-15</td>\n",
       "      <td>1871971</td>\n",
       "      <td>242.9</td>\n",
       "      <td>Thyrotoxicosis without mention of goiter or ot...</td>\n",
       "      <td>Thyrotoxicosis without goiter OR other cause</td>\n",
       "      <td>THYROID</td>\n",
       "      <td>89719007</td>\n",
       "      <td>2010-10-13</td>\n",
       "      <td>2010-10-13</td>\n",
       "      <td>0</td>\n",
       "      <td>393</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   patient_id treatment_start  visit_id  ICD9CODE  \\\n",
       "0       11392      2008-06-08    552989     242.9   \n",
       "1       18427      2009-08-04    893707     242.9   \n",
       "2       30161      2008-05-07   1457808     242.9   \n",
       "3       38696      2009-09-15   1871971     242.9   \n",
       "4       38696      2009-09-15   1871971     242.9   \n",
       "\n",
       "                                           ICD9_name  \\\n",
       "0  Thyrotoxicosis without mention of goiter or ot...   \n",
       "1  Thyrotoxicosis without mention of goiter or ot...   \n",
       "2  Thyrotoxicosis without mention of goiter or ot...   \n",
       "3  Thyrotoxicosis without mention of goiter or ot...   \n",
       "4  Thyrotoxicosis without mention of goiter or ot...   \n",
       "\n",
       "                                    snomed_name condition_type  snomed_code  \\\n",
       "0  Thyrotoxicosis without goiter OR other cause        THYROID     89719007   \n",
       "1  Thyrotoxicosis without goiter OR other cause        THYROID     89719007   \n",
       "2  Thyrotoxicosis without goiter OR other cause        THYROID     89719007   \n",
       "3  Thyrotoxicosis without goiter OR other cause        THYROID     89719007   \n",
       "4  Thyrotoxicosis without goiter OR other cause        THYROID     89719007   \n",
       "\n",
       "  condition_start_date condition_end_date  condition_interval_days  \\\n",
       "0           2008-06-16         2008-06-16                        0   \n",
       "1           2010-10-02         2010-10-02                        0   \n",
       "2           2010-06-08         2010-06-08                        0   \n",
       "3           2010-10-13         2010-10-13                        0   \n",
       "4           2010-10-13         2010-10-13                        0   \n",
       "\n",
       "   treatment_condition_interval_days  \n",
       "0                                  8  \n",
       "1                                424  \n",
       "2                                762  \n",
       "3                                393  \n",
       "4                                393  "
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conditions_ds.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "f4feb2b2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(33, 12)"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conditions_ds[conditions_ds[\"condition_interval_days\"]>0].shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "150b5d9e",
   "metadata": {},
   "source": [
    "There are 307 visits associated with a flagged condition that happened after amiodarone therapy began.  Only 33 of these visits were about a condition recorded as lasting > 1 day."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "f841c3e1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0       4549\n",
       "1       5680\n",
       "2       5894\n",
       "3       7134\n",
       "4       7630\n",
       "       ...  \n",
       "92    111598\n",
       "93    113140\n",
       "94    114821\n",
       "95    115881\n",
       "96    116232\n",
       "Length: 97, dtype: int64"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "common_patient_ids = pd.Series(np.intersect1d(conditions_ds[\"patient_id\"], visits_procedures_ds[\"patient_id\"]))\n",
    "common_patient_ids"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "643cadfc",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0       224884\n",
       "1       279377\n",
       "2       289319\n",
       "3       289337\n",
       "4       347276\n",
       "        ...   \n",
       "251    5555700\n",
       "252    5555705\n",
       "253    5573746\n",
       "254    5573775\n",
       "255    5573841\n",
       "Length: 256, dtype: int64"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "common_visit_ids = pd.Series(np.intersect1d(conditions_ds[\"visit_id\"], visits_procedures_ds[\"visit_id\"]))\n",
    "common_visit_ids"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "60cb61c2",
   "metadata": {},
   "source": [
    "97 patients had one of the sentinel conditions, and there were 256 common visit ids."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "b09b4556",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(5617, 66)"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "condition_visits_ds = pd.merge(visits_procedures_ds, conditions_ds, on=[\"patient_id\", \"visit_id\"], how=\"left\")\n",
    "condition_visits_ds = condition_visits_ds.drop_duplicates()\n",
    "condition_visits_ds.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "42173ba7",
   "metadata": {},
   "outputs": [],
   "source": [
    "condition_visits_ds.to_csv(\"conditions_visits_procedures.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0e390f49",
   "metadata": {},
   "source": [
    "Reimport the file after row-title cleanup in Excel."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "69d335af",
   "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>patient_id</th>\n",
       "      <th>treatment_start</th>\n",
       "      <th>treatment_period</th>\n",
       "      <th>visit_id</th>\n",
       "      <th>vstart</th>\n",
       "      <th>vprovider</th>\n",
       "      <th>care_site</th>\n",
       "      <th>diagnosis_1</th>\n",
       "      <th>diagnosis_2</th>\n",
       "      <th>diagnosis_3</th>\n",
       "      <th>...</th>\n",
       "      <th>pdesc_16</th>\n",
       "      <th>sentinel_condition_code</th>\n",
       "      <th>sentinel_condition_name</th>\n",
       "      <th>sentinel_snomed_name</th>\n",
       "      <th>sentinel_condition_type</th>\n",
       "      <th>sentinel_snomed_code</th>\n",
       "      <th>condition_start_date</th>\n",
       "      <th>condition_end_date</th>\n",
       "      <th>condition_interval_days</th>\n",
       "      <th>treatment_condition_interval_days</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>111598</td>\n",
       "      <td>2008-01-15</td>\n",
       "      <td>90</td>\n",
       "      <td>5351372</td>\n",
       "      <td>2008-01-28</td>\n",
       "      <td>50058.0</td>\n",
       "      <td>11234.0</td>\n",
       "      <td>Vomiting</td>\n",
       "      <td>Epidemic vertigo</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>111598</td>\n",
       "      <td>2008-01-15</td>\n",
       "      <td>90</td>\n",
       "      <td>5351385</td>\n",
       "      <td>2008-01-28</td>\n",
       "      <td>315573.0</td>\n",
       "      <td>11283.0</td>\n",
       "      <td>Atrial fibrillation</td>\n",
       "      <td>Paroxysmal supraventricular tachycardia</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>111598</td>\n",
       "      <td>2008-01-15</td>\n",
       "      <td>90</td>\n",
       "      <td>5351386</td>\n",
       "      <td>2008-01-31</td>\n",
       "      <td>218450.0</td>\n",
       "      <td>11234.0</td>\n",
       "      <td>Atrial fibrillation</td>\n",
       "      <td>Premature beats</td>\n",
       "      <td>Type 2 diabetes mellitus</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>111598</td>\n",
       "      <td>2008-01-15</td>\n",
       "      <td>90</td>\n",
       "      <td>5351403</td>\n",
       "      <td>2008-02-05</td>\n",
       "      <td>382137.0</td>\n",
       "      <td>28276.0</td>\n",
       "      <td>Angina decubitus</td>\n",
       "      <td>Mitral and aortic stenosis</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>111598</td>\n",
       "      <td>2008-01-15</td>\n",
       "      <td>90</td>\n",
       "      <td>5351324</td>\n",
       "      <td>2008-02-05</td>\n",
       "      <td>447710.0</td>\n",
       "      <td>11222.0</td>\n",
       "      <td>Conduction disorder of the heart</td>\n",
       "      <td>Atrial fibrillation</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 65 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   patient_id treatment_start  treatment_period  visit_id     vstart  \\\n",
       "0      111598      2008-01-15                90   5351372 2008-01-28   \n",
       "1      111598      2008-01-15                90   5351385 2008-01-28   \n",
       "2      111598      2008-01-15                90   5351386 2008-01-31   \n",
       "3      111598      2008-01-15                90   5351403 2008-02-05   \n",
       "4      111598      2008-01-15                90   5351324 2008-02-05   \n",
       "\n",
       "   vprovider  care_site                       diagnosis_1  \\\n",
       "0    50058.0    11234.0                          Vomiting   \n",
       "1   315573.0    11283.0               Atrial fibrillation   \n",
       "2   218450.0    11234.0               Atrial fibrillation   \n",
       "3   382137.0    28276.0                  Angina decubitus   \n",
       "4   447710.0    11222.0  Conduction disorder of the heart   \n",
       "\n",
       "                               diagnosis_2               diagnosis_3  ...  \\\n",
       "0                         Epidemic vertigo                       NaN  ...   \n",
       "1  Paroxysmal supraventricular tachycardia                       NaN  ...   \n",
       "2                          Premature beats  Type 2 diabetes mellitus  ...   \n",
       "3               Mitral and aortic stenosis                       NaN  ...   \n",
       "4                      Atrial fibrillation                       NaN  ...   \n",
       "\n",
       "  pdesc_16 sentinel_condition_code sentinel_condition_name  \\\n",
       "0      NaN                     NaN                     NaN   \n",
       "1      NaN                     NaN                     NaN   \n",
       "2      NaN                     NaN                     NaN   \n",
       "3      NaN                     NaN                     NaN   \n",
       "4      NaN                     NaN                     NaN   \n",
       "\n",
       "  sentinel_snomed_name sentinel_condition_type sentinel_snomed_code  \\\n",
       "0                  NaN                     NaN                  NaN   \n",
       "1                  NaN                     NaN                  NaN   \n",
       "2                  NaN                     NaN                  NaN   \n",
       "3                  NaN                     NaN                  NaN   \n",
       "4                  NaN                     NaN                  NaN   \n",
       "\n",
       "  condition_start_date condition_end_date condition_interval_days  \\\n",
       "0                  NaN                NaN                     NaN   \n",
       "1                  NaN                NaN                     NaN   \n",
       "2                  NaN                NaN                     NaN   \n",
       "3                  NaN                NaN                     NaN   \n",
       "4                  NaN                NaN                     NaN   \n",
       "\n",
       "  treatment_condition_interval_days  \n",
       "0                               NaN  \n",
       "1                               NaN  \n",
       "2                               NaN  \n",
       "3                               NaN  \n",
       "4                               NaN  \n",
       "\n",
       "[5 rows x 65 columns]"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "condition_visits_ds = pd.read_csv(\"conditions_visits_procedures.csv\", parse_dates=[\"treatment_start\", \"vstart\"])\n",
    "condition_visits_ds.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "0c217f09",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 5617 entries, 0 to 5616\n",
      "Data columns (total 65 columns):\n",
      " #   Column                             Non-Null Count  Dtype         \n",
      "---  ------                             --------------  -----         \n",
      " 0   patient_id                         5617 non-null   int64         \n",
      " 1   treatment_start                    5617 non-null   datetime64[ns]\n",
      " 2   treatment_period                   5617 non-null   int64         \n",
      " 3   visit_id                           5617 non-null   int64         \n",
      " 4   vstart                             5617 non-null   datetime64[ns]\n",
      " 5   vprovider                          5608 non-null   float64       \n",
      " 6   care_site                          5612 non-null   float64       \n",
      " 7   diagnosis_1                        5617 non-null   object        \n",
      " 8   diagnosis_2                        4279 non-null   object        \n",
      " 9   diagnosis_3                        2137 non-null   object        \n",
      " 10  diagnosis_4                        1033 non-null   object        \n",
      " 11  diagnosis_5                        443 non-null    object        \n",
      " 12  diagnosis_6                        187 non-null    object        \n",
      " 13  diagnosis_7                        86 non-null     object        \n",
      " 14  diagnosis_8                        35 non-null     object        \n",
      " 15  diagnosis_9                        10 non-null     object        \n",
      " 16  diagnosis_10                       2 non-null      object        \n",
      " 17  diagnosis_11                       1 non-null      object        \n",
      " 18  snomed_1                           5617 non-null   object        \n",
      " 19  snomed_2                           4279 non-null   object        \n",
      " 20  snomed_3                           2137 non-null   object        \n",
      " 21  snomed_4                           1033 non-null   object        \n",
      " 22  snomed_5                           443 non-null    object        \n",
      " 23  snomed_6                           187 non-null    float64       \n",
      " 24  snomed_7                           86 non-null     float64       \n",
      " 25  snomed_8                           35 non-null     float64       \n",
      " 26  snomed_9                           10 non-null     float64       \n",
      " 27  snomed_10                          2 non-null      float64       \n",
      " 28  snomed_11                          1 non-null      float64       \n",
      " 29  icd9_1                             5617 non-null   object        \n",
      " 30  icd9_2                             4306 non-null   object        \n",
      " 31  icd9_3                             2096 non-null   object        \n",
      " 32  icd9_4                             997 non-null    object        \n",
      " 33  icd9_5                             418 non-null    object        \n",
      " 34  icd9_6                             179 non-null    object        \n",
      " 35  icd9_7                             82 non-null     object        \n",
      " 36  icd9_8                             33 non-null     object        \n",
      " 37  icd9_9                             10 non-null     float64       \n",
      " 38  icd9_10                            1 non-null      float64       \n",
      " 39  icd9_11                            1 non-null      float64       \n",
      " 40  pdesc_1                            4346 non-null   object        \n",
      " 41  pdesc_2                            2747 non-null   object        \n",
      " 42  pdesc_3                            1415 non-null   object        \n",
      " 43  pdesc_4                            651 non-null    object        \n",
      " 44  pdesc_5                            298 non-null    object        \n",
      " 45  pdesc_6                            127 non-null    object        \n",
      " 46  pdesc_7                            66 non-null     object        \n",
      " 47  pdesc_8                            41 non-null     object        \n",
      " 48  pdesc_9                            22 non-null     object        \n",
      " 49  pdesc_10                           11 non-null     object        \n",
      " 50  pdesc_11                           8 non-null      object        \n",
      " 51  pdesc_12                           5 non-null      object        \n",
      " 52  pdesc_13                           4 non-null      object        \n",
      " 53  pdesc_14                           4 non-null      object        \n",
      " 54  pdesc_15                           2 non-null      object        \n",
      " 55  pdesc_16                           1 non-null      object        \n",
      " 56  sentinel_condition_code            277 non-null    float64       \n",
      " 57  sentinel_condition_name            277 non-null    object        \n",
      " 58  sentinel_snomed_name               277 non-null    object        \n",
      " 59  sentinel_condition_type            277 non-null    object        \n",
      " 60  sentinel_snomed_code               277 non-null    float64       \n",
      " 61  condition_start_date               277 non-null    object        \n",
      " 62  condition_end_date                 277 non-null    object        \n",
      " 63  condition_interval_days            277 non-null    float64       \n",
      " 64  treatment_condition_interval_days  277 non-null    float64       \n",
      "dtypes: datetime64[ns](2), float64(15), int64(3), object(45)\n",
      "memory usage: 2.8+ MB\n"
     ]
    }
   ],
   "source": [
    "condition_visits_ds.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3dac0fbe",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}