notebooks/drug-indications.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "db889ade",
   "metadata": {},
   "source": [
    "# Working with Drug Indications\n",
    "\n",
    "This notebook shows how to get drug indications from ChEMBL. Note that on your first run, the download step will take longer depending on your internet connection."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "1ee9de80",
   "metadata": {},
   "outputs": [],
   "source": [
    "import sys\n",
    "import time\n",
    "\n",
    "import pandas as pd\n",
    "from IPython.display import Markdown\n",
    "from tqdm.auto import tqdm\n",
    "\n",
    "from chembl_downloader import get_date, latest, queries, query"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "9a2fd78d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "3.11.0 (main, Oct 25 2022, 14:13:24) [Clang 14.0.0 (clang-1400.0.29.202)]\n"
     ]
    }
   ],
   "source": [
    "print(sys.version)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "a9e2ec2b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Fri Oct 28 11:55:25 2022\n"
     ]
    }
   ],
   "source": [
    "print(time.asctime())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1c109d0f",
   "metadata": {},
   "source": [
    "## Building a SQL Query\n",
    "\n",
    "In this example, we're going to re-use a common query from the `chembl_downloader.queries` module.\n",
    "\n",
    "The following SQL query is used to combine the `DRUG_INDICATION` table with extra information from the `MOLECULE_DICTIONARY` table using join. This guarantees that we only get rows about molecules that have indications."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "621a2286",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/markdown": [
       "```sql\n",
       "SELECT\n",
       "    MOLECULE_DICTIONARY.chembl_id,\n",
       "    MOLECULE_DICTIONARY.pref_name,\n",
       "    MOLECULE_DICTIONARY.chebi_par_id,\n",
       "    DRUG_INDICATION.mesh_id,\n",
       "    DRUG_INDICATION.mesh_heading,\n",
       "    DRUG_INDICATION.efo_id AS indication_curie,\n",
       "    DRUG_INDICATION.efo_term AS indication_label,\n",
       "    DRUG_INDICATION.max_phase_for_ind\n",
       "FROM MOLECULE_DICTIONARY\n",
       "JOIN DRUG_INDICATION ON MOLECULE_DICTIONARY.molregno == DRUG_INDICATION.molregno\n",
       "```"
      ],
      "text/plain": [
       "<IPython.core.display.Markdown object>"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queries.markdown(queries.DRUG_INDICATIONS_SQL)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dc7ea247",
   "metadata": {},
   "source": [
    "## Running the SQL Query\n",
    "\n",
    "We're first going to explicitly get the latest ChEMBL version so we can document it in the notebook, then execute the query using `chembl_downloader.query` to get a Pandas DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "e000559e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Using latest ChEMBL version: 31\n"
     ]
    }
   ],
   "source": [
    "latest_version = latest()\n",
    "\n",
    "print(f\"Using latest ChEMBL version: {latest_version}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "517a08e9",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 2 µs, sys: 1 µs, total: 3 µs\n",
      "Wall time: 6.91 µs\n"
     ]
    }
   ],
   "source": [
    "%time\n",
    "df = query(queries.DRUG_INDICATIONS_SQL, version=latest_version)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "f2687b93",
   "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>chembl_id</th>\n",
       "      <th>pref_name</th>\n",
       "      <th>chebi_par_id</th>\n",
       "      <th>mesh_id</th>\n",
       "      <th>mesh_heading</th>\n",
       "      <th>indication_curie</th>\n",
       "      <th>indication_label</th>\n",
       "      <th>max_phase_for_ind</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>CHEMBL1201823</td>\n",
       "      <td>ABATACEPT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>D045743</td>\n",
       "      <td>Scleroderma, Diffuse</td>\n",
       "      <td>EFO:0000404</td>\n",
       "      <td>diffuse scleroderma</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>CHEMBL1201823</td>\n",
       "      <td>ABATACEPT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>D001172</td>\n",
       "      <td>Arthritis, Rheumatoid</td>\n",
       "      <td>EFO:0000685</td>\n",
       "      <td>rheumatoid arthritis</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>CHEMBL1201823</td>\n",
       "      <td>ABATACEPT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>D008180</td>\n",
       "      <td>Lupus Erythematosus, Systemic</td>\n",
       "      <td>EFO:0002690</td>\n",
       "      <td>systemic lupus erythematosus</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>CHEMBL1201584</td>\n",
       "      <td>ABCIXIMAB</td>\n",
       "      <td>NaN</td>\n",
       "      <td>D009203</td>\n",
       "      <td>Myocardial Infarction</td>\n",
       "      <td>EFO:0000612</td>\n",
       "      <td>myocardial infarction</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>CHEMBL271227</td>\n",
       "      <td>ABIRATERONE ACETATE</td>\n",
       "      <td>68639.0</td>\n",
       "      <td>D011471</td>\n",
       "      <td>Prostatic Neoplasms</td>\n",
       "      <td>EFO:0001663</td>\n",
       "      <td>prostate carcinoma</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48811</th>\n",
       "      <td>CHEMBL245807</td>\n",
       "      <td>OXYPHENISATINE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>D003248</td>\n",
       "      <td>Constipation</td>\n",
       "      <td>HP:0002019</td>\n",
       "      <td>Constipation</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48812</th>\n",
       "      <td>CHEMBL4802233</td>\n",
       "      <td>ROXATIDINE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>D005764</td>\n",
       "      <td>Gastroesophageal Reflux</td>\n",
       "      <td>EFO:0003948</td>\n",
       "      <td>gastroesophageal reflux disease</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48813</th>\n",
       "      <td>CHEMBL4802233</td>\n",
       "      <td>ROXATIDINE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>D010437</td>\n",
       "      <td>Peptic Ulcer</td>\n",
       "      <td>HP:0004398</td>\n",
       "      <td>Peptic ulcer</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48814</th>\n",
       "      <td>CHEMBL4802223</td>\n",
       "      <td>LEUPROLIDE MESYLATE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>D011471</td>\n",
       "      <td>Prostatic Neoplasms</td>\n",
       "      <td>MONDO:0008315</td>\n",
       "      <td>prostate cancer</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48815</th>\n",
       "      <td>CHEMBL4802239</td>\n",
       "      <td>MOBOCERTINIB SUCCINATE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>D002289</td>\n",
       "      <td>Carcinoma, Non-Small-Cell Lung</td>\n",
       "      <td>EFO:0003060</td>\n",
       "      <td>non-small cell lung carcinoma</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>48816 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           chembl_id               pref_name  chebi_par_id  mesh_id  \\\n",
       "0      CHEMBL1201823               ABATACEPT           NaN  D045743   \n",
       "1      CHEMBL1201823               ABATACEPT           NaN  D001172   \n",
       "2      CHEMBL1201823               ABATACEPT           NaN  D008180   \n",
       "3      CHEMBL1201584               ABCIXIMAB           NaN  D009203   \n",
       "4       CHEMBL271227     ABIRATERONE ACETATE       68639.0  D011471   \n",
       "...              ...                     ...           ...      ...   \n",
       "48811   CHEMBL245807          OXYPHENISATINE           NaN  D003248   \n",
       "48812  CHEMBL4802233              ROXATIDINE           NaN  D005764   \n",
       "48813  CHEMBL4802233              ROXATIDINE           NaN  D010437   \n",
       "48814  CHEMBL4802223     LEUPROLIDE MESYLATE           NaN  D011471   \n",
       "48815  CHEMBL4802239  MOBOCERTINIB SUCCINATE           NaN  D002289   \n",
       "\n",
       "                         mesh_heading indication_curie  \\\n",
       "0                Scleroderma, Diffuse      EFO:0000404   \n",
       "1               Arthritis, Rheumatoid      EFO:0000685   \n",
       "2       Lupus Erythematosus, Systemic      EFO:0002690   \n",
       "3               Myocardial Infarction      EFO:0000612   \n",
       "4                 Prostatic Neoplasms      EFO:0001663   \n",
       "...                               ...              ...   \n",
       "48811                    Constipation       HP:0002019   \n",
       "48812         Gastroesophageal Reflux      EFO:0003948   \n",
       "48813                    Peptic Ulcer       HP:0004398   \n",
       "48814             Prostatic Neoplasms    MONDO:0008315   \n",
       "48815  Carcinoma, Non-Small-Cell Lung      EFO:0003060   \n",
       "\n",
       "                      indication_label  max_phase_for_ind  \n",
       "0                  diffuse scleroderma                  2  \n",
       "1                 rheumatoid arthritis                  4  \n",
       "2         systemic lupus erythematosus                  2  \n",
       "3                myocardial infarction                  3  \n",
       "4                   prostate carcinoma                  4  \n",
       "...                                ...                ...  \n",
       "48811                     Constipation                  4  \n",
       "48812  gastroesophageal reflux disease                  0  \n",
       "48813                     Peptic ulcer                  0  \n",
       "48814                  prostate cancer                  4  \n",
       "48815    non-small cell lung carcinoma                  4  \n",
       "\n",
       "[48816 rows x 8 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "90d015ec",
   "metadata": {},
   "source": [
    "## Comparison\n",
    "\n",
    "We'll take the query of the drug indications to the next level by comparing results across ChEMBL versions. We chose a minimum version of 26 to show a bit of variety, but not eat up too much disk space, since each version is around 20-25GB."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "273be786",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "  0%|          | 0/6 [00:00<?, ?version/s]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "rows = []\n",
    "it = tqdm(\n",
    "    list(reversed(range(26, int(latest_version) + 1))),\n",
    "    unit=\"version\",\n",
    "    leave=False,\n",
    ")\n",
    "for version in it:\n",
    "    version = str(version)\n",
    "    it.set_postfix(version=version)\n",
    "    df = query(queries.DRUG_INDICATIONS_SQL, version=version)\n",
    "    rows.append(\n",
    "        (\n",
    "            version,\n",
    "            get_date(version),\n",
    "            len(df.index),\n",
    "            len(df[\"chembl_id\"].unique()),\n",
    "            len(df[\"mesh_id\"].unique()),\n",
    "        )\n",
    "    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "e86d42b5",
   "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>version</th>\n",
       "      <th>date</th>\n",
       "      <th>associations</th>\n",
       "      <th>unique_molecules</th>\n",
       "      <th>unique_indications</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>31</td>\n",
       "      <td>2022-07-12</td>\n",
       "      <td>48816</td>\n",
       "      <td>8074</td>\n",
       "      <td>1888</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>30</td>\n",
       "      <td>2022-02-22</td>\n",
       "      <td>48816</td>\n",
       "      <td>8074</td>\n",
       "      <td>1888</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>29</td>\n",
       "      <td>2021-07-01</td>\n",
       "      <td>45902</td>\n",
       "      <td>7792</td>\n",
       "      <td>1781</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>28</td>\n",
       "      <td>2021-01-15</td>\n",
       "      <td>42988</td>\n",
       "      <td>7446</td>\n",
       "      <td>1730</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>27</td>\n",
       "      <td>2020-05-18</td>\n",
       "      <td>37259</td>\n",
       "      <td>6700</td>\n",
       "      <td>1523</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>26</td>\n",
       "      <td>2020-02-14</td>\n",
       "      <td>37259</td>\n",
       "      <td>6700</td>\n",
       "      <td>1523</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  version        date  associations  unique_molecules  unique_indications\n",
       "0      31  2022-07-12         48816              8074                1888\n",
       "1      30  2022-02-22         48816              8074                1888\n",
       "2      29  2021-07-01         45902              7792                1781\n",
       "3      28  2021-01-15         42988              7446                1730\n",
       "4      27  2020-05-18         37259              6700                1523\n",
       "5      26  2020-02-14         37259              6700                1523"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "counts_df = pd.DataFrame(\n",
    "    rows,\n",
    "    columns=[\"version\", \"date\", \"associations\", \"unique_molecules\", \"unique_indications\"],\n",
    ")\n",
    "counts_df"
   ]
  }
 ],
 "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.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}