demo/SemanticSearch.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "661d6cb4-2080-41e3-93c1-dee1a984fd74",
   "metadata": {},
   "source": [
    "<b> Make sure to start the IRIS database before running this file. A quick way to do that: </b> \\\n",
    "docker run -d --name iris-comm -p 1972:1972 -p 52773:52773 -e IRIS_PASSWORD=demo -e IRIS_USERNAME=demo intersystemsdc/iris-community:latest"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6b00011c-8e40-4bae-9007-d7147891b571",
   "metadata": {},
   "source": [
    "# Loading the data to a dataframe\n",
    "The data used here is the D_ICD_DIAGNOSES table from the Medical Information Mart for Intensive Care (MIMIC)-IV Demo database \\\n",
    "<i>\" Medical Information Mart for Intensive Care (MIMIC)-IV database is comprised of deidentified electronic health records for patients admitted to the Beth Israel Deaconess Medical Center \" </i> \\\n",
    "Johnson, A., Bulgarelli, L., Pollard, T., Horng, S., Celi, L. A., & Mark, R. (2023). MIMIC-IV Clinical Database Demo (version 2.2). PhysioNet. https://doi.org/10.13026/dp1f-ex47."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "c6b6088d-f73c-42cc-ba1a-aef808797fa9",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "f5df150c-4747-4f99-8ec5-80856475320e",
   "metadata": {},
   "outputs": [],
   "source": [
    "diagnoses = pd.read_csv(\"D_ICD_DIAGNOSES.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "341ac527-0d40-46ab-9c72-938369e2b1e5",
   "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>row_id</th>\n",
       "      <th>icd9_code</th>\n",
       "      <th>short_title</th>\n",
       "      <th>long_title</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>01716</td>\n",
       "      <td>Erythem nod tb-oth test</td>\n",
       "      <td>Erythema nodosum with hypersensitivity reactio...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>01720</td>\n",
       "      <td>TB periph lymph-unspec</td>\n",
       "      <td>Tuberculosis of peripheral lymph nodes, unspec...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>01721</td>\n",
       "      <td>TB periph lymph-no exam</td>\n",
       "      <td>Tuberculosis of peripheral lymph nodes, bacter...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>01722</td>\n",
       "      <td>TB periph lymph-exam unk</td>\n",
       "      <td>Tuberculosis of peripheral lymph nodes, bacter...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>01723</td>\n",
       "      <td>TB periph lymph-micro dx</td>\n",
       "      <td>Tuberculosis of peripheral lymph nodes, tuberc...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   row_id icd9_code               short_title  \\\n",
       "0       1     01716   Erythem nod tb-oth test   \n",
       "1       2     01720    TB periph lymph-unspec   \n",
       "2       3     01721   TB periph lymph-no exam   \n",
       "3       4     01722  TB periph lymph-exam unk   \n",
       "4       5     01723  TB periph lymph-micro dx   \n",
       "\n",
       "                                          long_title  \n",
       "0  Erythema nodosum with hypersensitivity reactio...  \n",
       "1  Tuberculosis of peripheral lymph nodes, unspec...  \n",
       "2  Tuberculosis of peripheral lymph nodes, bacter...  \n",
       "3  Tuberculosis of peripheral lymph nodes, bacter...  \n",
       "4  Tuberculosis of peripheral lymph nodes, tuberc...  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "diagnoses.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3dd4356d-5f3d-43af-836b-ca50d0665c3c",
   "metadata": {},
   "source": [
    "# Generating embeddings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "11c2ef56-da1b-405a-8a5c-7527a864764f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# !pip install sentence_transformers\n",
    "from sentence_transformers import SentenceTransformer"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f5617556-5e06-4858-acbe-1ca8a471e145",
   "metadata": {},
   "outputs": [],
   "source": [
    "model = SentenceTransformer('all-MiniLM-L6-v2')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "87e788cd-ca8e-4da8-961a-a571a0b2d0e9",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Generate embeddings for all descriptions at once. \n",
    "embeddings = model.encode(diagnoses['long_title'].tolist(), normalize_embeddings=True)\n",
    "# Add the embeddings to the DataFrame\n",
    "diagnoses['long_title_vector'] = embeddings.tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "55274130-523a-43ec-b129-fe2e2041ab86",
   "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>row_id</th>\n",
       "      <th>icd9_code</th>\n",
       "      <th>short_title</th>\n",
       "      <th>long_title</th>\n",
       "      <th>long_title_vector</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>01716</td>\n",
       "      <td>Erythem nod tb-oth test</td>\n",
       "      <td>Erythema nodosum with hypersensitivity reactio...</td>\n",
       "      <td>[0.050381243228912354, -0.01662275940179825, -...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>01720</td>\n",
       "      <td>TB periph lymph-unspec</td>\n",
       "      <td>Tuberculosis of peripheral lymph nodes, unspec...</td>\n",
       "      <td>[0.03993239626288414, 0.006474997382611036, -0...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>01721</td>\n",
       "      <td>TB periph lymph-no exam</td>\n",
       "      <td>Tuberculosis of peripheral lymph nodes, bacter...</td>\n",
       "      <td>[0.09322471916675568, 0.0053528607822954655, -...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>01722</td>\n",
       "      <td>TB periph lymph-exam unk</td>\n",
       "      <td>Tuberculosis of peripheral lymph nodes, bacter...</td>\n",
       "      <td>[0.07309558987617493, -0.0028698795940726995, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>01723</td>\n",
       "      <td>TB periph lymph-micro dx</td>\n",
       "      <td>Tuberculosis of peripheral lymph nodes, tuberc...</td>\n",
       "      <td>[0.03763028606772423, -0.02204110100865364, -0...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   row_id icd9_code               short_title  \\\n",
       "0       1     01716   Erythem nod tb-oth test   \n",
       "1       2     01720    TB periph lymph-unspec   \n",
       "2       3     01721   TB periph lymph-no exam   \n",
       "3       4     01722  TB periph lymph-exam unk   \n",
       "4       5     01723  TB periph lymph-micro dx   \n",
       "\n",
       "                                          long_title  \\\n",
       "0  Erythema nodosum with hypersensitivity reactio...   \n",
       "1  Tuberculosis of peripheral lymph nodes, unspec...   \n",
       "2  Tuberculosis of peripheral lymph nodes, bacter...   \n",
       "3  Tuberculosis of peripheral lymph nodes, bacter...   \n",
       "4  Tuberculosis of peripheral lymph nodes, tuberc...   \n",
       "\n",
       "                                   long_title_vector  \n",
       "0  [0.050381243228912354, -0.01662275940179825, -...  \n",
       "1  [0.03993239626288414, 0.006474997382611036, -0...  \n",
       "2  [0.09322471916675568, 0.0053528607822954655, -...  \n",
       "3  [0.07309558987617493, -0.0028698795940726995, ...  \n",
       "4  [0.03763028606772423, -0.02204110100865364, -0...  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "diagnoses.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e08138cb-65d8-483b-b8c8-9287a6e889ef",
   "metadata": {},
   "source": [
    "# IRIS database operations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9af97385-3712-4409-b96b-373024aed114",
   "metadata": {},
   "outputs": [],
   "source": [
    "!pip install intersystems_irispython-3.2.0-py3-none-any.whl\n",
    "import iris\n",
    "import time\n",
    "import os"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "defbded9-65f8-4a5a-bcaf-897dcfe7a81e",
   "metadata": {},
   "source": [
    "## Database connection settings\n",
    "### NOTE: You might have to change the hostname to \"iris\" instead of \"localhost\" depending on how your started your IRIS db container."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "c4119e53-b6db-4b2c-8ca1-a565c18523f0",
   "metadata": {},
   "outputs": [],
   "source": [
    "namespace=\"USER\"\n",
    "port = 1972\n",
    "hostname = os.getenv('IRIS_HOSTNAME', 'localhost')\n",
    "connection_string = f\"{hostname}:{port}/{namespace}\"\n",
    "username = \"demo\"\n",
    "password = \"demo\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "646faaa3-f2f7-4e34-887f-c80831d1aed1",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Note: Ideally conn and cursor should be used with context manager or with try-execpt-finally \n",
    "conn = iris.connect(connection_string, username, password)\n",
    "cursor = conn.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "f3653f16-2f3f-48eb-8524-ebfad983373a",
   "metadata": {},
   "outputs": [],
   "source": [
    "tableName = \"Demo.VectorDiagnoses\"\n",
    "tableDefinition = \"(row_id INTEGER, icd9_code VARCHAR(255), short_title VARCHAR(255), long_title VARCHAR(''), long_title_vector VECTOR(DOUBLE,384))\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "dcb02ecb-ff0a-42ef-b04c-c4c48074136b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "try:\n",
    "    cursor.execute(f\"DROP TABLE {tableName}\")  \n",
    "except:\n",
    "    pass\n",
    "cursor.execute(f\"CREATE TABLE {tableName} {tableDefinition}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "888fdc28-15da-4aa8-b5d6-9d19d90fb5ec",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "time taken to add 14567 entries: 40.83518600463867 seconds\n"
     ]
    }
   ],
   "source": [
    "##looping through dataframe and adding all the data to IRIS table\n",
    "sql = \"Insert into Demo.VectorDiagnoses (row_id, icd9_code,short_title,long_title,long_title_vector) values (?, ?, ?, ?, ?)\"\n",
    "start_time = time.time()\n",
    "for index,row in diagnoses.iterrows():\n",
    "    cursor.execute(sql, [row.row_id,row.icd9_code,row.short_title,row.long_title,str(row.long_title_vector)])\n",
    "end_time = time.time()\n",
    "print(f\"time taken to add {len(diagnoses)} entries: {end_time-start_time} seconds\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "35d6fbf1-7ed6-4001-908b-c13c1341eec9",
   "metadata": {},
   "source": [
    "## difficulty walking"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "1ab99a13-bc57-417c-9916-0d24b86331fe",
   "metadata": {},
   "outputs": [],
   "source": [
    "issueDescription = \"difficulty walking\"\n",
    "issueDescription_vector = model.encode(issueDescription, normalize_embeddings=True).tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "b24f9dec-5f76-42e1-9686-d33b7725e4e4",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[14143, '7197', 'Difficulty in walking', 'Difficulty in walking']\n",
      "[13460, 'E0010', 'Walking,marching,hiking', 'Activities involving walking, marching and hiking']\n",
      "[12108, '7812', 'Abnormality of gait', 'Abnormality of gait']\n",
      "[9681, 'E0190', 'Walking an animal', 'Activities involving walking an animal']\n",
      "[14034, 'V690', 'Lack of physical exercse', 'Lack of physical exercise']\n",
      "[9838, 'E8859', 'Fall from slipping NEC', 'Fall from other slipping, tripping, or stumbling']\n",
      "[3596, '32751', 'Periodic limb movement', 'Periodic limb movement disorder']\n",
      "[11690, 'E9272', 'Excess physical exert', 'Excessive physical exertion']\n",
      "[10205, 'V499', 'Probl influ health NOS', 'Unspecified problems with limbs and other problems']\n",
      "[10176, 'V491', 'Mechanical prob w limbs', 'Mechanical problems with limbs']\n"
     ]
    }
   ],
   "source": [
    "sql = \"select Top ? row_id, icd9_code, short_title, long_title from Demo.VectorDiagnoses ORDER BY VECTOR_DOT_PRODUCT(long_title_vector, TO_VECTOR(?)) DESC\"\n",
    "numberOfResults = 10\n",
    "cursor.execute(sql,[numberOfResults,str(issueDescription_vector)])\n",
    "fetched_data = cursor.fetchall()\n",
    "for row in fetched_data:\n",
    "    print(row)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a574a3e4-00b5-428e-b1ab-2f935da8da9c",
   "metadata": {},
   "source": [
    "## respiratory issues"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "18c50441-d5d7-4048-a6b5-7cb9014246f1",
   "metadata": {},
   "outputs": [],
   "source": [
    "issueDescription = \"respiratory issues\"\n",
    "issueDescription_vector = model.encode(issueDescription, normalize_embeddings=True).tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "abe73b1f-d0be-4da8-a700-34abb6119d5e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[12369, '99739', 'Respiratory comp NEC', 'Other respiratory complications']\n",
      "[13003, '78609', 'Respiratory abnorm NEC', 'Other respiratory abnormalities']\n",
      "[12995, '78600', 'Respiratory abnorm NOS', 'Respiratory abnormality, unspecified']\n",
      "[5281, '51883', 'Chronic respiratory fail', 'Chronic respiratory failure']\n",
      "[5282, '51884', 'Acute & chronc resp fail', 'Acute and chronic respiratory failure']\n",
      "[2933, '3061', 'Psychogenic respir dis', 'Respiratory malfunction arising from mental factors']\n",
      "[14505, 'V814', 'Screen-respir cond NEC', 'Screening for other and unspecified respiratory conditions']\n",
      "[9040, '77089', 'Resp prob after brth NEC', 'Other respiratory problems after birth']\n",
      "[7376, '7488', 'Respiratory anomaly NEC', 'Other specified anomalies of respiratory system']\n"
     ]
    }
   ],
   "source": [
    "sql = \"select Top ? row_id, icd9_code, short_title, long_title from Demo.VectorDiagnoses ORDER BY VECTOR_DOT_PRODUCT(long_title_vector, TO_VECTOR(?)) DESC\"\n",
    "numberOfResults = 9\n",
    "cursor.execute(sql,[numberOfResults,str(issueDescription_vector)])\n",
    "fetched_data = cursor.fetchall()\n",
    "for row in fetched_data:\n",
    "    print(row)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "f487a2b9-7652-400a-b607-9a405559a8c4",
   "metadata": {},
   "outputs": [],
   "source": [
    "##close the connection\n",
    "cursor.close()\n",
    "conn.commit()\n",
    "conn.close()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}