notebooks/chembl/initial_database_parsing.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "aerial-liquid",
   "metadata": {},
   "source": [
    "## Download data base"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "arbitrary-feature",
   "metadata": {},
   "outputs": [],
   "source": [
    "wget -O chembl_30_sqlite.tar.gz https://ftp.ebi.ac.uk/pub/databases/chembl/ChEMBLdb/latest/chembl_30_sqlite.tar.gz"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "german-doctrine",
   "metadata": {},
   "outputs": [],
   "source": [
    "# at farm\n",
    "# download checksums file\n",
    "wget -O db30_chechsums.txt https://ftp.ebi.ac.uk/pub/databases/chembl/ChEMBLdb/latest/checksums.txt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "handmade-canada",
   "metadata": {},
   "outputs": [],
   "source": [
    "!cat db30_checksums.txt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bizarre-reservation",
   "metadata": {},
   "outputs": [],
   "source": [
    "!sha256sum chembl_30_sqlite.tar.gz"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "biblical-banana",
   "metadata": {},
   "outputs": [],
   "source": [
    "# decompress\n",
    "tar -xzvf chembl_30_sqlite.tar.gz"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "raising-devon",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "consistent-andorra",
   "metadata": {},
   "source": [
    "## Import required modules"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "confidential-reaction",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "pd.set_option('display.max_rows', 600)\n",
    "import numpy as np\n",
    "import sqlite3"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "following-absolute",
   "metadata": {},
   "source": [
    "## Connect to the downloaded database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "patient-digit",
   "metadata": {},
   "outputs": [],
   "source": [
    "con = sqlite3.connect('chembl_30.db')\n",
    "cur = con.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "unnecessary-wonder",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('assay_type',), ('chembl_id_lookup',), ('target_type',), ('go_classification',), ('atc_classification',), ('source',), ('action_type',), ('variant_sequences',), ('protein_classification',), ('activity_smid',), ('irac_classification',), ('bioassay_ontology',), ('structural_alert_sets',), ('relationship_type',), ('confidence_score_lookup',), ('curation_lookup',), ('assay_classification',), ('data_validity_lookup',), ('products',), ('patent_use_codes',), ('research_stem',), ('hrac_classification',), ('component_sequences',), ('domains',), ('bio_component_sequences',), ('protein_family_classification',), ('version',), ('activity_stds_lookup',), ('frac_classification',), ('usan_stems',), ('organism_class',), ('target_dictionary',), ('molecule_dictionary',), ('docs',), ('protein_class_synonyms',), ('structural_alerts',), ('cell_dictionary',), ('tissue_dictionary',), ('product_patents',), ('component_synonyms',), ('component_domains',), ('research_companies',), ('component_go',), ('defined_daily_dose',), ('activity_supp',), ('component_class',), ('target_relations',), ('biotherapeutics',), ('compound_records',), ('binding_sites',), ('compound_structural_alerts',), ('assays',), ('molecule_hierarchy',), ('molecule_synonyms',), ('molecule_hrac_classification',), ('target_components',), ('compound_properties',), ('molecule_frac_classification',), ('molecule_irac_classification',), ('molecule_atc_classification',), ('compound_structures',), ('drug_indication',), ('drug_mechanism',), ('assay_parameters',), ('activities',), ('drug_warning',), ('metabolism',), ('biotherapeutic_components',), ('assay_class_map',), ('formulations',), ('site_components',), ('indication_refs',), ('mechanism_refs',), ('activity_properties',), ('warning_refs',), ('metabolism_refs',), ('predicted_binding_domains',), ('activity_supp_map',), ('ligand_eff',), ('sqlite_stat1',)]\n"
     ]
    }
   ],
   "source": [
    "cur. execute(\"SELECT name FROM sqlite_master WHERE type='table';\")\n",
    "print(cur.fetchall())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "driving-surrey",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "spoken-wallace",
   "metadata": {},
   "source": [
    "## Activity data loading"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "uniform-assurance",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(19286751, 27)\n",
      "(19286751, 27)\n",
      "CPU times: user 3min 39s, sys: 20.6 s, total: 4min\n",
      "Wall time: 4min 5s\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>activities|activity_id</th>\n",
       "      <th>activities|assay_id</th>\n",
       "      <th>activities|doc_id</th>\n",
       "      <th>activities|record_id</th>\n",
       "      <th>activities|molregno</th>\n",
       "      <th>activities|standard_relation</th>\n",
       "      <th>activities|standard_value</th>\n",
       "      <th>activities|standard_units</th>\n",
       "      <th>activities|standard_flag</th>\n",
       "      <th>activities|standard_type</th>\n",
       "      <th>...</th>\n",
       "      <th>activities|toid</th>\n",
       "      <th>activities|upper_value</th>\n",
       "      <th>activities|standard_upper_value</th>\n",
       "      <th>activities|src_id</th>\n",
       "      <th>activities|type</th>\n",
       "      <th>activities|relation</th>\n",
       "      <th>activities|value</th>\n",
       "      <th>activities|units</th>\n",
       "      <th>activities|text_value</th>\n",
       "      <th>activities|standard_text_value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>31863</td>\n",
       "      <td>54505</td>\n",
       "      <td>6424</td>\n",
       "      <td>206172</td>\n",
       "      <td>180094</td>\n",
       "      <td>&gt;</td>\n",
       "      <td>100000.0</td>\n",
       "      <td>nM</td>\n",
       "      <td>1</td>\n",
       "      <td>IC50</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>IC50</td>\n",
       "      <td>&gt;</td>\n",
       "      <td>100.0</td>\n",
       "      <td>uM</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>31864</td>\n",
       "      <td>83907</td>\n",
       "      <td>6432</td>\n",
       "      <td>208970</td>\n",
       "      <td>182268</td>\n",
       "      <td>=</td>\n",
       "      <td>2500.0</td>\n",
       "      <td>nM</td>\n",
       "      <td>1</td>\n",
       "      <td>IC50</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>IC50</td>\n",
       "      <td>=</td>\n",
       "      <td>2.5</td>\n",
       "      <td>uM</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>31865</td>\n",
       "      <td>88152</td>\n",
       "      <td>6432</td>\n",
       "      <td>208970</td>\n",
       "      <td>182268</td>\n",
       "      <td>&gt;</td>\n",
       "      <td>50000.0</td>\n",
       "      <td>nM</td>\n",
       "      <td>1</td>\n",
       "      <td>IC50</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>IC50</td>\n",
       "      <td>&gt;</td>\n",
       "      <td>50.0</td>\n",
       "      <td>uM</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>31866</td>\n",
       "      <td>83907</td>\n",
       "      <td>6432</td>\n",
       "      <td>208987</td>\n",
       "      <td>182855</td>\n",
       "      <td>=</td>\n",
       "      <td>9000.0</td>\n",
       "      <td>nM</td>\n",
       "      <td>1</td>\n",
       "      <td>IC50</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>IC50</td>\n",
       "      <td>=</td>\n",
       "      <td>9.0</td>\n",
       "      <td>uM</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>31867</td>\n",
       "      <td>88153</td>\n",
       "      <td>6432</td>\n",
       "      <td>208987</td>\n",
       "      <td>182855</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nM</td>\n",
       "      <td>0</td>\n",
       "      <td>IC50</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>IC50</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>uM</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 27 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   activities|activity_id  activities|assay_id  activities|doc_id  \\\n",
       "0                   31863                54505               6424   \n",
       "1                   31864                83907               6432   \n",
       "2                   31865                88152               6432   \n",
       "3                   31866                83907               6432   \n",
       "4                   31867                88153               6432   \n",
       "\n",
       "   activities|record_id  activities|molregno activities|standard_relation  \\\n",
       "0                206172               180094                            >   \n",
       "1                208970               182268                            =   \n",
       "2                208970               182268                            >   \n",
       "3                208987               182855                            =   \n",
       "4                208987               182855                         None   \n",
       "\n",
       "   activities|standard_value activities|standard_units  \\\n",
       "0                   100000.0                        nM   \n",
       "1                     2500.0                        nM   \n",
       "2                    50000.0                        nM   \n",
       "3                     9000.0                        nM   \n",
       "4                        NaN                        nM   \n",
       "\n",
       "   activities|standard_flag activities|standard_type  ... activities|toid  \\\n",
       "0                         1                     IC50  ...             NaN   \n",
       "1                         1                     IC50  ...             NaN   \n",
       "2                         1                     IC50  ...             NaN   \n",
       "3                         1                     IC50  ...             NaN   \n",
       "4                         0                     IC50  ...             NaN   \n",
       "\n",
       "  activities|upper_value  activities|standard_upper_value  activities|src_id  \\\n",
       "0                    NaN                             None                  1   \n",
       "1                    NaN                             None                  1   \n",
       "2                    NaN                             None                  1   \n",
       "3                    NaN                             None                  1   \n",
       "4                    NaN                             None                  1   \n",
       "\n",
       "  activities|type activities|relation activities|value  activities|units  \\\n",
       "0            IC50                   >            100.0                uM   \n",
       "1            IC50                   =              2.5                uM   \n",
       "2            IC50                   >             50.0                uM   \n",
       "3            IC50                   =              9.0                uM   \n",
       "4            IC50                None              NaN                uM   \n",
       "\n",
       "   activities|text_value activities|standard_text_value  \n",
       "0                   None                           None  \n",
       "1                   None                           None  \n",
       "2                   None                           None  \n",
       "3                   None                           None  \n",
       "4                   None                           None  \n",
       "\n",
       "[5 rows x 27 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%time\n",
    "activities = pd.read_sql_query(\"SELECT * from activities\", con)\n",
    "print(activities.shape)\n",
    "\n",
    "# rename columns to be able to track back\n",
    "activities.columns=[f'activities|{x}' for x in activities.columns]\n",
    "\n",
    "print(activities.shape)\n",
    "activities.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "above-police",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "unexpected-packet",
   "metadata": {},
   "source": [
    "## Assay data loading"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "actual-incidence",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1458215, 24)\n",
      "(1458215, 24)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>assays|assay_id</th>\n",
       "      <th>assays|doc_id</th>\n",
       "      <th>assays|description</th>\n",
       "      <th>assays|assay_type</th>\n",
       "      <th>assays|assay_test_type</th>\n",
       "      <th>assays|assay_category</th>\n",
       "      <th>assays|assay_organism</th>\n",
       "      <th>assays|assay_tax_id</th>\n",
       "      <th>assays|assay_strain</th>\n",
       "      <th>assays|assay_tissue</th>\n",
       "      <th>...</th>\n",
       "      <th>assays|confidence_score</th>\n",
       "      <th>assays|curated_by</th>\n",
       "      <th>assays|src_id</th>\n",
       "      <th>assays|src_assay_id</th>\n",
       "      <th>assays|chembl_id</th>\n",
       "      <th>assays|cell_id</th>\n",
       "      <th>assays|bao_format</th>\n",
       "      <th>assays|tissue_id</th>\n",
       "      <th>assays|variant_id</th>\n",
       "      <th>assays|aidx</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>11087</td>\n",
       "      <td>The compound was tested for the in vitro inhib...</td>\n",
       "      <td>B</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>8</td>\n",
       "      <td>Autocuration</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>CHEMBL615117</td>\n",
       "      <td>NaN</td>\n",
       "      <td>BAO_0000019</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CLD0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>684</td>\n",
       "      <td>Compound was evaluated for its ability to mobi...</td>\n",
       "      <td>F</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>Autocuration</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>CHEMBL615118</td>\n",
       "      <td>NaN</td>\n",
       "      <td>BAO_0000219</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CLD0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>15453</td>\n",
       "      <td>None</td>\n",
       "      <td>B</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>Autocuration</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>CHEMBL615119</td>\n",
       "      <td>NaN</td>\n",
       "      <td>BAO_0000019</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CLD0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>17841</td>\n",
       "      <td>Binding affinity against A2 adenosine receptor...</td>\n",
       "      <td>B</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Bos taurus</td>\n",
       "      <td>9913.0</td>\n",
       "      <td>None</td>\n",
       "      <td>Striatum</td>\n",
       "      <td>...</td>\n",
       "      <td>4</td>\n",
       "      <td>Autocuration</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>CHEMBL615120</td>\n",
       "      <td>NaN</td>\n",
       "      <td>BAO_0000249</td>\n",
       "      <td>2435.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CLD0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>17430</td>\n",
       "      <td>In vitro cell cytotoxicity against 143-B cell ...</td>\n",
       "      <td>F</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Homo sapiens</td>\n",
       "      <td>9606.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>Intermediate</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>CHEMBL615121</td>\n",
       "      <td>163.0</td>\n",
       "      <td>BAO_0000219</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CLD0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   assays|assay_id  assays|doc_id  \\\n",
       "0                1          11087   \n",
       "1                2            684   \n",
       "2                3          15453   \n",
       "3                4          17841   \n",
       "4                5          17430   \n",
       "\n",
       "                                  assays|description assays|assay_type  \\\n",
       "0  The compound was tested for the in vitro inhib...                 B   \n",
       "1  Compound was evaluated for its ability to mobi...                 F   \n",
       "2                                               None                 B   \n",
       "3  Binding affinity against A2 adenosine receptor...                 B   \n",
       "4  In vitro cell cytotoxicity against 143-B cell ...                 F   \n",
       "\n",
       "  assays|assay_test_type assays|assay_category assays|assay_organism  \\\n",
       "0                   None                  None                  None   \n",
       "1                   None                  None                  None   \n",
       "2                   None                  None                  None   \n",
       "3                   None                  None            Bos taurus   \n",
       "4                   None                  None          Homo sapiens   \n",
       "\n",
       "   assays|assay_tax_id assays|assay_strain assays|assay_tissue  ...  \\\n",
       "0                  NaN                None                None  ...   \n",
       "1                  NaN                None                None  ...   \n",
       "2                  NaN                None                None  ...   \n",
       "3               9913.0                None            Striatum  ...   \n",
       "4               9606.0                None                None  ...   \n",
       "\n",
       "  assays|confidence_score assays|curated_by  assays|src_id  \\\n",
       "0                       8      Autocuration              1   \n",
       "1                       0      Autocuration              1   \n",
       "2                       0      Autocuration              1   \n",
       "3                       4      Autocuration              1   \n",
       "4                       1      Intermediate              1   \n",
       "\n",
       "  assays|src_assay_id  assays|chembl_id assays|cell_id  assays|bao_format  \\\n",
       "0                None      CHEMBL615117            NaN        BAO_0000019   \n",
       "1                None      CHEMBL615118            NaN        BAO_0000219   \n",
       "2                None      CHEMBL615119            NaN        BAO_0000019   \n",
       "3                None      CHEMBL615120            NaN        BAO_0000249   \n",
       "4                None      CHEMBL615121          163.0        BAO_0000219   \n",
       "\n",
       "  assays|tissue_id assays|variant_id  assays|aidx  \n",
       "0              NaN               NaN         CLD0  \n",
       "1              NaN               NaN         CLD0  \n",
       "2              NaN               NaN         CLD0  \n",
       "3           2435.0               NaN         CLD0  \n",
       "4              NaN               NaN         CLD0  \n",
       "\n",
       "[5 rows x 24 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# assay data\n",
    "assays = pd.read_sql_query(\"SELECT * from assays\", con)\n",
    "print(assays.shape)\n",
    "\n",
    "# rename columns to be able to track back\n",
    "assays.columns=[f'assays|{x}' for x in assays.columns]\n",
    "\n",
    "print(assays.shape)\n",
    "assays.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "touched-insulation",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "independent-slave",
   "metadata": {},
   "source": [
    "## Target data loading"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "numeric-warrant",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(14855, 7)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>target_dictionary|tid</th>\n",
       "      <th>target_dictionary|target_type</th>\n",
       "      <th>target_dictionary|pref_name</th>\n",
       "      <th>target_dictionary|tax_id</th>\n",
       "      <th>target_dictionary|organism</th>\n",
       "      <th>target_dictionary|chembl_id</th>\n",
       "      <th>target_dictionary|species_group_flag</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>SINGLE PROTEIN</td>\n",
       "      <td>Maltase-glucoamylase</td>\n",
       "      <td>9606.0</td>\n",
       "      <td>Homo sapiens</td>\n",
       "      <td>CHEMBL2074</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>SINGLE PROTEIN</td>\n",
       "      <td>Sulfonylurea receptor 2</td>\n",
       "      <td>9606.0</td>\n",
       "      <td>Homo sapiens</td>\n",
       "      <td>CHEMBL1971</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>SINGLE PROTEIN</td>\n",
       "      <td>Phosphodiesterase 5A</td>\n",
       "      <td>9606.0</td>\n",
       "      <td>Homo sapiens</td>\n",
       "      <td>CHEMBL1827</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>SINGLE PROTEIN</td>\n",
       "      <td>Voltage-gated T-type calcium channel alpha-1H ...</td>\n",
       "      <td>9606.0</td>\n",
       "      <td>Homo sapiens</td>\n",
       "      <td>CHEMBL1859</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>SINGLE PROTEIN</td>\n",
       "      <td>Nicotinic acetylcholine receptor alpha subunit</td>\n",
       "      <td>6253.0</td>\n",
       "      <td>Ascaris suum</td>\n",
       "      <td>CHEMBL1884</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   target_dictionary|tid target_dictionary|target_type  \\\n",
       "0                      1                SINGLE PROTEIN   \n",
       "1                      2                SINGLE PROTEIN   \n",
       "2                      3                SINGLE PROTEIN   \n",
       "3                      4                SINGLE PROTEIN   \n",
       "4                      5                SINGLE PROTEIN   \n",
       "\n",
       "                         target_dictionary|pref_name  \\\n",
       "0                               Maltase-glucoamylase   \n",
       "1                            Sulfonylurea receptor 2   \n",
       "2                               Phosphodiesterase 5A   \n",
       "3  Voltage-gated T-type calcium channel alpha-1H ...   \n",
       "4     Nicotinic acetylcholine receptor alpha subunit   \n",
       "\n",
       "   target_dictionary|tax_id target_dictionary|organism  \\\n",
       "0                    9606.0               Homo sapiens   \n",
       "1                    9606.0               Homo sapiens   \n",
       "2                    9606.0               Homo sapiens   \n",
       "3                    9606.0               Homo sapiens   \n",
       "4                    6253.0               Ascaris suum   \n",
       "\n",
       "  target_dictionary|chembl_id  target_dictionary|species_group_flag  \n",
       "0                  CHEMBL2074                                     0  \n",
       "1                  CHEMBL1971                                     0  \n",
       "2                  CHEMBL1827                                     0  \n",
       "3                  CHEMBL1859                                     0  \n",
       "4                  CHEMBL1884                                     0  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "target_dictionary = pd.read_sql_query(\"SELECT * from target_dictionary\", con)\n",
    "print(target_dictionary.shape)\n",
    "\n",
    "# rename columns to be able to track back\n",
    "target_dictionary.columns=[f'target_dictionary|{x}' for x in target_dictionary.columns]\n",
    "\n",
    "target_dictionary.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "athletic-producer",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(13558, 4)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>target_components|tid</th>\n",
       "      <th>target_components|component_id</th>\n",
       "      <th>target_components|targcomp_id</th>\n",
       "      <th>target_components|homologue</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>11004</td>\n",
       "      <td>3090</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>11028</td>\n",
       "      <td>1166</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>11037</td>\n",
       "      <td>1888</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>11043</td>\n",
       "      <td>1294</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>11056</td>\n",
       "      <td>2159</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   target_components|tid  target_components|component_id  \\\n",
       "0                  11004                            3090   \n",
       "1                  11028                            1166   \n",
       "2                  11037                            1888   \n",
       "3                  11043                            1294   \n",
       "4                  11056                            2159   \n",
       "\n",
       "   target_components|targcomp_id  target_components|homologue  \n",
       "0                              1                            0  \n",
       "1                              4                            0  \n",
       "2                              5                            0  \n",
       "3                              7                            0  \n",
       "4                              8                            0  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "target_components = pd.read_sql_query(\"SELECT * from target_components\", con)\n",
    "print(target_components.shape)\n",
    "\n",
    "# rename columns to be able to track back\n",
    "target_components.columns=[f'target_components|{x}' for x in target_components.columns]\n",
    "\n",
    "target_components.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "immune-animal",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(97172, 4)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>component_synonyms|compsyn_id</th>\n",
       "      <th>component_synonyms|component_id</th>\n",
       "      <th>component_synonyms|component_synonym</th>\n",
       "      <th>component_synonyms|syn_type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>860862</td>\n",
       "      <td>48</td>\n",
       "      <td>Gabra-1</td>\n",
       "      <td>GENE_SYMBOL_OTHER</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>860867</td>\n",
       "      <td>49</td>\n",
       "      <td>Gabrb-3</td>\n",
       "      <td>GENE_SYMBOL_OTHER</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>860872</td>\n",
       "      <td>50</td>\n",
       "      <td>Gabrb-2</td>\n",
       "      <td>GENE_SYMBOL_OTHER</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>860877</td>\n",
       "      <td>51</td>\n",
       "      <td>CDKN5</td>\n",
       "      <td>GENE_SYMBOL_OTHER</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>860915</td>\n",
       "      <td>56</td>\n",
       "      <td>NMDAR1</td>\n",
       "      <td>GENE_SYMBOL_OTHER</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   component_synonyms|compsyn_id  component_synonyms|component_id  \\\n",
       "0                         860862                               48   \n",
       "1                         860867                               49   \n",
       "2                         860872                               50   \n",
       "3                         860877                               51   \n",
       "4                         860915                               56   \n",
       "\n",
       "  component_synonyms|component_synonym component_synonyms|syn_type  \n",
       "0                              Gabra-1           GENE_SYMBOL_OTHER  \n",
       "1                              Gabrb-3           GENE_SYMBOL_OTHER  \n",
       "2                              Gabrb-2           GENE_SYMBOL_OTHER  \n",
       "3                                CDKN5           GENE_SYMBOL_OTHER  \n",
       "4                               NMDAR1           GENE_SYMBOL_OTHER  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "component_synonyms = pd.read_sql_query(\"SELECT * from component_synonyms\", con)\n",
    "print(component_synonyms.shape)\n",
    "\n",
    "# rename columns to be able to track back\n",
    "component_synonyms.columns=[f'component_synonyms|{x}' for x in component_synonyms.columns]\n",
    "\n",
    "component_synonyms.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "alternate-stable",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "sound-receiver",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "molecular-finland",
   "metadata": {},
   "source": [
    "## Compound data loading"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "responsible-architect",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(6656, 14)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>drug_mechanism|mec_id</th>\n",
       "      <th>drug_mechanism|record_id</th>\n",
       "      <th>drug_mechanism|molregno</th>\n",
       "      <th>drug_mechanism|mechanism_of_action</th>\n",
       "      <th>drug_mechanism|tid</th>\n",
       "      <th>drug_mechanism|site_id</th>\n",
       "      <th>drug_mechanism|action_type</th>\n",
       "      <th>drug_mechanism|direct_interaction</th>\n",
       "      <th>drug_mechanism|molecular_mechanism</th>\n",
       "      <th>drug_mechanism|disease_efficacy</th>\n",
       "      <th>drug_mechanism|mechanism_comment</th>\n",
       "      <th>drug_mechanism|selectivity_comment</th>\n",
       "      <th>drug_mechanism|binding_site_comment</th>\n",
       "      <th>drug_mechanism|variant_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>13</td>\n",
       "      <td>1343810</td>\n",
       "      <td>1124</td>\n",
       "      <td>Carbonic anhydrase VII inhibitor</td>\n",
       "      <td>11060.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>INHIBITOR</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>14</td>\n",
       "      <td>1344053</td>\n",
       "      <td>675068</td>\n",
       "      <td>Carbonic anhydrase I inhibitor</td>\n",
       "      <td>10193.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>INHIBITOR</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>15</td>\n",
       "      <td>1344649</td>\n",
       "      <td>674765</td>\n",
       "      <td>Carbonic anhydrase I inhibitor</td>\n",
       "      <td>10193.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>INHIBITOR</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Expressed in eye</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>16</td>\n",
       "      <td>1343255</td>\n",
       "      <td>1085</td>\n",
       "      <td>Carbonic anhydrase I inhibitor</td>\n",
       "      <td>10193.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>INHIBITOR</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>17</td>\n",
       "      <td>1344903</td>\n",
       "      <td>1125</td>\n",
       "      <td>Carbonic anhydrase I inhibitor</td>\n",
       "      <td>10193.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>INHIBITOR</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Expressed in eye</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   drug_mechanism|mec_id  drug_mechanism|record_id  drug_mechanism|molregno  \\\n",
       "0                     13                   1343810                     1124   \n",
       "1                     14                   1344053                   675068   \n",
       "2                     15                   1344649                   674765   \n",
       "3                     16                   1343255                     1085   \n",
       "4                     17                   1344903                     1125   \n",
       "\n",
       "  drug_mechanism|mechanism_of_action  drug_mechanism|tid  \\\n",
       "0   Carbonic anhydrase VII inhibitor             11060.0   \n",
       "1     Carbonic anhydrase I inhibitor             10193.0   \n",
       "2     Carbonic anhydrase I inhibitor             10193.0   \n",
       "3     Carbonic anhydrase I inhibitor             10193.0   \n",
       "4     Carbonic anhydrase I inhibitor             10193.0   \n",
       "\n",
       "   drug_mechanism|site_id drug_mechanism|action_type  \\\n",
       "0                     NaN                  INHIBITOR   \n",
       "1                     NaN                  INHIBITOR   \n",
       "2                     NaN                  INHIBITOR   \n",
       "3                     NaN                  INHIBITOR   \n",
       "4                     NaN                  INHIBITOR   \n",
       "\n",
       "   drug_mechanism|direct_interaction  drug_mechanism|molecular_mechanism  \\\n",
       "0                                  1                                   1   \n",
       "1                                  1                                   1   \n",
       "2                                  1                                   1   \n",
       "3                                  1                                   1   \n",
       "4                                  1                                   1   \n",
       "\n",
       "   drug_mechanism|disease_efficacy drug_mechanism|mechanism_comment  \\\n",
       "0                                1                             None   \n",
       "1                                1                             None   \n",
       "2                                1                 Expressed in eye   \n",
       "3                                1                             None   \n",
       "4                                1                 Expressed in eye   \n",
       "\n",
       "  drug_mechanism|selectivity_comment drug_mechanism|binding_site_comment  \\\n",
       "0                               None                                None   \n",
       "1                               None                                None   \n",
       "2                               None                                None   \n",
       "3                               None                                None   \n",
       "4                               None                                None   \n",
       "\n",
       "   drug_mechanism|variant_id  \n",
       "0                        NaN  \n",
       "1                        NaN  \n",
       "2                        NaN  \n",
       "3                        NaN  \n",
       "4                        NaN  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "drug_mechanism = pd.read_sql_query(\"SELECT * from drug_mechanism\", con)\n",
    "print(drug_mechanism.shape)\n",
    "\n",
    "# rename columns to be able to track back\n",
    "drug_mechanism.columns=[f'drug_mechanism|{x}' for x in drug_mechanism.columns]\n",
    "\n",
    "drug_mechanism.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "challenging-swing",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(2157379, 31)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>molecule_dictionary|molregno</th>\n",
       "      <th>molecule_dictionary|pref_name</th>\n",
       "      <th>molecule_dictionary|chembl_id</th>\n",
       "      <th>molecule_dictionary|max_phase</th>\n",
       "      <th>molecule_dictionary|therapeutic_flag</th>\n",
       "      <th>molecule_dictionary|dosed_ingredient</th>\n",
       "      <th>molecule_dictionary|structure_type</th>\n",
       "      <th>molecule_dictionary|chebi_par_id</th>\n",
       "      <th>molecule_dictionary|molecule_type</th>\n",
       "      <th>molecule_dictionary|first_approval</th>\n",
       "      <th>...</th>\n",
       "      <th>molecule_dictionary|usan_stem</th>\n",
       "      <th>molecule_dictionary|polymer_flag</th>\n",
       "      <th>molecule_dictionary|usan_substem</th>\n",
       "      <th>molecule_dictionary|usan_stem_definition</th>\n",
       "      <th>molecule_dictionary|indication_class</th>\n",
       "      <th>molecule_dictionary|withdrawn_flag</th>\n",
       "      <th>molecule_dictionary|withdrawn_year</th>\n",
       "      <th>molecule_dictionary|withdrawn_country</th>\n",
       "      <th>molecule_dictionary|withdrawn_reason</th>\n",
       "      <th>molecule_dictionary|withdrawn_class</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>CHEMBL6329</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>MOL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Small molecule</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>None</td>\n",
       "      <td>CHEMBL6328</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>MOL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Small molecule</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>None</td>\n",
       "      <td>CHEMBL265667</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>MOL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Small molecule</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>None</td>\n",
       "      <td>CHEMBL6362</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>MOL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Small molecule</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>None</td>\n",
       "      <td>CHEMBL267864</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>MOL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Small molecule</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 31 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   molecule_dictionary|molregno molecule_dictionary|pref_name  \\\n",
       "0                             1                          None   \n",
       "1                             2                          None   \n",
       "2                             3                          None   \n",
       "3                             4                          None   \n",
       "4                             5                          None   \n",
       "\n",
       "  molecule_dictionary|chembl_id  molecule_dictionary|max_phase  \\\n",
       "0                    CHEMBL6329                              0   \n",
       "1                    CHEMBL6328                              0   \n",
       "2                  CHEMBL265667                              0   \n",
       "3                    CHEMBL6362                              0   \n",
       "4                  CHEMBL267864                              0   \n",
       "\n",
       "   molecule_dictionary|therapeutic_flag  molecule_dictionary|dosed_ingredient  \\\n",
       "0                                     0                                     0   \n",
       "1                                     0                                     0   \n",
       "2                                     0                                     0   \n",
       "3                                     0                                     0   \n",
       "4                                     0                                     0   \n",
       "\n",
       "  molecule_dictionary|structure_type  molecule_dictionary|chebi_par_id  \\\n",
       "0                                MOL                               NaN   \n",
       "1                                MOL                               NaN   \n",
       "2                                MOL                               NaN   \n",
       "3                                MOL                               NaN   \n",
       "4                                MOL                               NaN   \n",
       "\n",
       "  molecule_dictionary|molecule_type  molecule_dictionary|first_approval  ...  \\\n",
       "0                    Small molecule                                 NaN  ...   \n",
       "1                    Small molecule                                 NaN  ...   \n",
       "2                    Small molecule                                 NaN  ...   \n",
       "3                    Small molecule                                 NaN  ...   \n",
       "4                    Small molecule                                 NaN  ...   \n",
       "\n",
       "   molecule_dictionary|usan_stem  molecule_dictionary|polymer_flag  \\\n",
       "0                           None                                 0   \n",
       "1                           None                                 0   \n",
       "2                           None                                 0   \n",
       "3                           None                                 0   \n",
       "4                           None                                 0   \n",
       "\n",
       "   molecule_dictionary|usan_substem  molecule_dictionary|usan_stem_definition  \\\n",
       "0                              None                                      None   \n",
       "1                              None                                      None   \n",
       "2                              None                                      None   \n",
       "3                              None                                      None   \n",
       "4                              None                                      None   \n",
       "\n",
       "   molecule_dictionary|indication_class  molecule_dictionary|withdrawn_flag  \\\n",
       "0                                  None                                   0   \n",
       "1                                  None                                   0   \n",
       "2                                  None                                   0   \n",
       "3                                  None                                   0   \n",
       "4                                  None                                   0   \n",
       "\n",
       "   molecule_dictionary|withdrawn_year  molecule_dictionary|withdrawn_country  \\\n",
       "0                                 NaN                                   None   \n",
       "1                                 NaN                                   None   \n",
       "2                                 NaN                                   None   \n",
       "3                                 NaN                                   None   \n",
       "4                                 NaN                                   None   \n",
       "\n",
       "   molecule_dictionary|withdrawn_reason  molecule_dictionary|withdrawn_class  \n",
       "0                                  None                                 None  \n",
       "1                                  None                                 None  \n",
       "2                                  None                                 None  \n",
       "3                                  None                                 None  \n",
       "4                                  None                                 None  \n",
       "\n",
       "[5 rows x 31 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "molecule_dictionary = pd.read_sql_query(\"SELECT * from molecule_dictionary\", con)\n",
    "print(molecule_dictionary.shape)\n",
    "\n",
    "# rename columns to be able to track back\n",
    "molecule_dictionary.columns=[f'molecule_dictionary|{x}' for x in molecule_dictionary.columns]\n",
    "\n",
    "molecule_dictionary.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "gothic-fantasy",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(4470, 3)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>molecule_atc_classification|mol_atc_id</th>\n",
       "      <th>molecule_atc_classification|level5</th>\n",
       "      <th>molecule_atc_classification|molregno</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>59409</td>\n",
       "      <td>L01EX15</td>\n",
       "      <td>2089491</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>59410</td>\n",
       "      <td>L01EX10</td>\n",
       "      <td>608601</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>59411</td>\n",
       "      <td>L01EM03</td>\n",
       "      <td>1567700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>59412</td>\n",
       "      <td>D06BX03</td>\n",
       "      <td>579824</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>59413</td>\n",
       "      <td>L01EX13</td>\n",
       "      <td>1763584</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   molecule_atc_classification|mol_atc_id molecule_atc_classification|level5  \\\n",
       "0                                   59409                            L01EX15   \n",
       "1                                   59410                            L01EX10   \n",
       "2                                   59411                            L01EM03   \n",
       "3                                   59412                            D06BX03   \n",
       "4                                   59413                            L01EX13   \n",
       "\n",
       "   molecule_atc_classification|molregno  \n",
       "0                               2089491  \n",
       "1                                608601  \n",
       "2                               1567700  \n",
       "3                                579824  \n",
       "4                               1763584  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "molecule_atc_classification = pd.read_sql_query(\"SELECT * from molecule_atc_classification\", con)\n",
    "print(molecule_atc_classification.shape)\n",
    "\n",
    "# rename columns to be able to track back\n",
    "molecule_atc_classification.columns=[f'molecule_atc_classification|{x}' for x in molecule_atc_classification.columns]\n",
    "\n",
    "molecule_atc_classification.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "spatial-creation",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(5148, 10)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>atc_classification|who_name</th>\n",
       "      <th>atc_classification|level1</th>\n",
       "      <th>atc_classification|level2</th>\n",
       "      <th>atc_classification|level3</th>\n",
       "      <th>atc_classification|level4</th>\n",
       "      <th>atc_classification|level5</th>\n",
       "      <th>atc_classification|level1_description</th>\n",
       "      <th>atc_classification|level2_description</th>\n",
       "      <th>atc_classification|level3_description</th>\n",
       "      <th>atc_classification|level4_description</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>sodium fluoride</td>\n",
       "      <td>A</td>\n",
       "      <td>A01</td>\n",
       "      <td>A01A</td>\n",
       "      <td>A01AA</td>\n",
       "      <td>A01AA01</td>\n",
       "      <td>ALIMENTARY TRACT AND METABOLISM</td>\n",
       "      <td>STOMATOLOGICAL PREPARATIONS</td>\n",
       "      <td>STOMATOLOGICAL PREPARATIONS</td>\n",
       "      <td>Caries prophylactic agents</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>sodium monofluorophosphate</td>\n",
       "      <td>A</td>\n",
       "      <td>A01</td>\n",
       "      <td>A01A</td>\n",
       "      <td>A01AA</td>\n",
       "      <td>A01AA02</td>\n",
       "      <td>ALIMENTARY TRACT AND METABOLISM</td>\n",
       "      <td>STOMATOLOGICAL PREPARATIONS</td>\n",
       "      <td>STOMATOLOGICAL PREPARATIONS</td>\n",
       "      <td>Caries prophylactic agents</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>olaflur</td>\n",
       "      <td>A</td>\n",
       "      <td>A01</td>\n",
       "      <td>A01A</td>\n",
       "      <td>A01AA</td>\n",
       "      <td>A01AA03</td>\n",
       "      <td>ALIMENTARY TRACT AND METABOLISM</td>\n",
       "      <td>STOMATOLOGICAL PREPARATIONS</td>\n",
       "      <td>STOMATOLOGICAL PREPARATIONS</td>\n",
       "      <td>Caries prophylactic agents</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>stannous fluoride</td>\n",
       "      <td>A</td>\n",
       "      <td>A01</td>\n",
       "      <td>A01A</td>\n",
       "      <td>A01AA</td>\n",
       "      <td>A01AA04</td>\n",
       "      <td>ALIMENTARY TRACT AND METABOLISM</td>\n",
       "      <td>STOMATOLOGICAL PREPARATIONS</td>\n",
       "      <td>STOMATOLOGICAL PREPARATIONS</td>\n",
       "      <td>Caries prophylactic agents</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>combinations</td>\n",
       "      <td>A</td>\n",
       "      <td>A01</td>\n",
       "      <td>A01A</td>\n",
       "      <td>A01AA</td>\n",
       "      <td>A01AA30</td>\n",
       "      <td>ALIMENTARY TRACT AND METABOLISM</td>\n",
       "      <td>STOMATOLOGICAL PREPARATIONS</td>\n",
       "      <td>STOMATOLOGICAL PREPARATIONS</td>\n",
       "      <td>Caries prophylactic agents</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  atc_classification|who_name atc_classification|level1  \\\n",
       "0             sodium fluoride                         A   \n",
       "1  sodium monofluorophosphate                         A   \n",
       "2                     olaflur                         A   \n",
       "3           stannous fluoride                         A   \n",
       "4                combinations                         A   \n",
       "\n",
       "  atc_classification|level2 atc_classification|level3  \\\n",
       "0                       A01                      A01A   \n",
       "1                       A01                      A01A   \n",
       "2                       A01                      A01A   \n",
       "3                       A01                      A01A   \n",
       "4                       A01                      A01A   \n",
       "\n",
       "  atc_classification|level4 atc_classification|level5  \\\n",
       "0                     A01AA                   A01AA01   \n",
       "1                     A01AA                   A01AA02   \n",
       "2                     A01AA                   A01AA03   \n",
       "3                     A01AA                   A01AA04   \n",
       "4                     A01AA                   A01AA30   \n",
       "\n",
       "  atc_classification|level1_description atc_classification|level2_description  \\\n",
       "0       ALIMENTARY TRACT AND METABOLISM           STOMATOLOGICAL PREPARATIONS   \n",
       "1       ALIMENTARY TRACT AND METABOLISM           STOMATOLOGICAL PREPARATIONS   \n",
       "2       ALIMENTARY TRACT AND METABOLISM           STOMATOLOGICAL PREPARATIONS   \n",
       "3       ALIMENTARY TRACT AND METABOLISM           STOMATOLOGICAL PREPARATIONS   \n",
       "4       ALIMENTARY TRACT AND METABOLISM           STOMATOLOGICAL PREPARATIONS   \n",
       "\n",
       "  atc_classification|level3_description atc_classification|level4_description  \n",
       "0           STOMATOLOGICAL PREPARATIONS            Caries prophylactic agents  \n",
       "1           STOMATOLOGICAL PREPARATIONS            Caries prophylactic agents  \n",
       "2           STOMATOLOGICAL PREPARATIONS            Caries prophylactic agents  \n",
       "3           STOMATOLOGICAL PREPARATIONS            Caries prophylactic agents  \n",
       "4           STOMATOLOGICAL PREPARATIONS            Caries prophylactic agents  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "atc_classification = pd.read_sql_query(\"SELECT * from atc_classification\", con)\n",
    "print(atc_classification.shape)\n",
    "\n",
    "# rename columns to be able to track back\n",
    "atc_classification.columns=[f'atc_classification|{x}' for x in atc_classification.columns]\n",
    "\n",
    "atc_classification.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "renewable-engineering",
   "metadata": {},
   "outputs": [],
   "source": [
    "# atc_classification = pd.read_sql_query(\"SELECT * from atc_classification\", con)\n",
    "# atc_classification.to_csv('/home/jovyan/projects/P50_ChEMBL/csv/atc_classification_db30.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "expressed-program",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "plain-indonesian",
   "metadata": {},
   "source": [
    "## Concatenate information"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "rubber-compromise",
   "metadata": {},
   "source": [
    "* activities_final:<br> \n",
    "'activities|activity_id', 'activities|assay_id', 'activities|molregno',\n",
    "'activities|pchembl_value', 'activities|type', 'activities|standard_relation', 'activities|standard_value',\n",
    "'activities|standard_units', 'activities|standard_flag',\n",
    "'activities|standard_type', 'activities|activity_comment',\n",
    "'assays|description','assays|assay_type','assays|tid', 'assays|confidence_score','assays|curated_by','assays|chembl_id',<br>\n",
    "<br>\n",
    "* targets_final:<br>\n",
    "'target_dictionary|tid','target_dictionary|target_type','target_dictionary|pref_name','target_dictionary|organism','target_dictionary|chembl_id',\n",
    "'component_synonyms|component_synonym', 'component_synonyms|syn_type'<br>\n",
    "<br>\n",
    "* molecule_dictionary:<br>\n",
    "'molecule_dictionary|molregno', 'molecule_dictionary|pref_name','molecule_dictionary|chembl_id', 'molecule_dictionary|max_phase', 'molecule_dictionary|molecule_type','molecule_dictionary|oral',\n",
    "'molecule_dictionary|parenteral', 'molecule_dictionary|topical','molecule_dictionary|black_box_warning','molecule_dictionary|natural_product'<br>\n",
    "<br>    \n",
    "* drug_mechanism:<br>\n",
    "'drug_mechanism|molregno','drug_mechanism|mechanism_of_action','drug_mechanism|tid','drug_mechanism|action_type',\n",
    "<br>\n",
    "* molecule_atc_classification:<br>\n",
    "'molecule_atc_classification|mol_atc_id','molecule_atc_classification|level5','molecule_atc_classification|molregno'\n",
    "<br>\n",
    "* atc_classification:<br>\n",
    "'atc_classification|who_name', 'atc_classification|level1','atc_classification|level2', 'atc_classification|level3',\n",
    "'atc_classification|level4', 'atc_classification|level5','atc_classification|level1_description','atc_classification|level2_description',\n",
    "'atc_classification|level3_description','atc_classification|level4_description'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "spread-programmer",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "accurate-document",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "activities+assays: (19286751, 17)\n",
      "added drug mechanism: (19291779, 21)\n",
      "19291779\n",
      "19291382\n",
      "added compound info: (20181918, 45)\n",
      "UNIPROT              55495\n",
      "GENE_SYMBOL_OTHER    39293\n",
      "GENE_SYMBOL          13028\n",
      "EC_NUMBER             7943\n",
      "Name: component_synonyms|syn_type, dtype: int64\n",
      "added targets info: (21292684, 52)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>activities|activity_id</th>\n",
       "      <th>activities|assay_id</th>\n",
       "      <th>activities|molregno</th>\n",
       "      <th>activities|pchembl_value</th>\n",
       "      <th>activities|type</th>\n",
       "      <th>activities|standard_relation</th>\n",
       "      <th>activities|standard_value</th>\n",
       "      <th>activities|standard_units</th>\n",
       "      <th>activities|standard_flag</th>\n",
       "      <th>activities|standard_type</th>\n",
       "      <th>...</th>\n",
       "      <th>atc_classification|level3_description</th>\n",
       "      <th>atc_classification|level4_description</th>\n",
       "      <th>atc_classification|who_name</th>\n",
       "      <th>target_dictionary|tid</th>\n",
       "      <th>target_dictionary|target_type</th>\n",
       "      <th>target_dictionary|pref_name</th>\n",
       "      <th>target_dictionary|organism</th>\n",
       "      <th>target_dictionary|chembl_id</th>\n",
       "      <th>component_synonyms|component_synonym</th>\n",
       "      <th>component_synonyms|syn_type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>31863.0</td>\n",
       "      <td>54505.0</td>\n",
       "      <td>180094.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>IC50</td>\n",
       "      <td>&gt;</td>\n",
       "      <td>100000.00</td>\n",
       "      <td>nM</td>\n",
       "      <td>1.0</td>\n",
       "      <td>IC50</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>63.0</td>\n",
       "      <td>SINGLE PROTEIN</td>\n",
       "      <td>DNA topoisomerase II alpha</td>\n",
       "      <td>Homo sapiens</td>\n",
       "      <td>CHEMBL1806</td>\n",
       "      <td>TOP2A</td>\n",
       "      <td>GENE_SYMBOL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>31864.0</td>\n",
       "      <td>83907.0</td>\n",
       "      <td>182268.0</td>\n",
       "      <td>5.60</td>\n",
       "      <td>IC50</td>\n",
       "      <td>=</td>\n",
       "      <td>2500.00</td>\n",
       "      <td>nM</td>\n",
       "      <td>1.0</td>\n",
       "      <td>IC50</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11653.0</td>\n",
       "      <td>SINGLE PROTEIN</td>\n",
       "      <td>Heparanase</td>\n",
       "      <td>Homo sapiens</td>\n",
       "      <td>CHEMBL3921</td>\n",
       "      <td>HPSE</td>\n",
       "      <td>GENE_SYMBOL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2224237.0</td>\n",
       "      <td>531583.0</td>\n",
       "      <td>182268.0</td>\n",
       "      <td>5.60</td>\n",
       "      <td>pIC50</td>\n",
       "      <td>=</td>\n",
       "      <td>2511.89</td>\n",
       "      <td>nM</td>\n",
       "      <td>1.0</td>\n",
       "      <td>IC50</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11653.0</td>\n",
       "      <td>SINGLE PROTEIN</td>\n",
       "      <td>Heparanase</td>\n",
       "      <td>Homo sapiens</td>\n",
       "      <td>CHEMBL3921</td>\n",
       "      <td>HPSE</td>\n",
       "      <td>GENE_SYMBOL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>31865.0</td>\n",
       "      <td>88152.0</td>\n",
       "      <td>182268.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>IC50</td>\n",
       "      <td>&gt;</td>\n",
       "      <td>50000.00</td>\n",
       "      <td>nM</td>\n",
       "      <td>1.0</td>\n",
       "      <td>IC50</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>31866.0</td>\n",
       "      <td>83907.0</td>\n",
       "      <td>182855.0</td>\n",
       "      <td>5.05</td>\n",
       "      <td>IC50</td>\n",
       "      <td>=</td>\n",
       "      <td>9000.00</td>\n",
       "      <td>nM</td>\n",
       "      <td>1.0</td>\n",
       "      <td>IC50</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11653.0</td>\n",
       "      <td>SINGLE PROTEIN</td>\n",
       "      <td>Heparanase</td>\n",
       "      <td>Homo sapiens</td>\n",
       "      <td>CHEMBL3921</td>\n",
       "      <td>HPSE</td>\n",
       "      <td>GENE_SYMBOL</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 52 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   activities|activity_id  activities|assay_id  activities|molregno  \\\n",
       "0                 31863.0              54505.0             180094.0   \n",
       "1                 31864.0              83907.0             182268.0   \n",
       "2               2224237.0             531583.0             182268.0   \n",
       "3                 31865.0              88152.0             182268.0   \n",
       "4                 31866.0              83907.0             182855.0   \n",
       "\n",
       "   activities|pchembl_value activities|type activities|standard_relation  \\\n",
       "0                       NaN            IC50                            >   \n",
       "1                      5.60            IC50                            =   \n",
       "2                      5.60           pIC50                            =   \n",
       "3                       NaN            IC50                            >   \n",
       "4                      5.05            IC50                            =   \n",
       "\n",
       "   activities|standard_value activities|standard_units  \\\n",
       "0                  100000.00                        nM   \n",
       "1                    2500.00                        nM   \n",
       "2                    2511.89                        nM   \n",
       "3                   50000.00                        nM   \n",
       "4                    9000.00                        nM   \n",
       "\n",
       "   activities|standard_flag activities|standard_type  ...  \\\n",
       "0                       1.0                     IC50  ...   \n",
       "1                       1.0                     IC50  ...   \n",
       "2                       1.0                     IC50  ...   \n",
       "3                       1.0                     IC50  ...   \n",
       "4                       1.0                     IC50  ...   \n",
       "\n",
       "  atc_classification|level3_description atc_classification|level4_description  \\\n",
       "0                                   NaN                                   NaN   \n",
       "1                                   NaN                                   NaN   \n",
       "2                                   NaN                                   NaN   \n",
       "3                                   NaN                                   NaN   \n",
       "4                                   NaN                                   NaN   \n",
       "\n",
       "  atc_classification|who_name  target_dictionary|tid  \\\n",
       "0                         NaN                   63.0   \n",
       "1                         NaN                11653.0   \n",
       "2                         NaN                11653.0   \n",
       "3                         NaN                    NaN   \n",
       "4                         NaN                11653.0   \n",
       "\n",
       "   target_dictionary|target_type target_dictionary|pref_name  \\\n",
       "0                 SINGLE PROTEIN  DNA topoisomerase II alpha   \n",
       "1                 SINGLE PROTEIN                  Heparanase   \n",
       "2                 SINGLE PROTEIN                  Heparanase   \n",
       "3                            NaN                         NaN   \n",
       "4                 SINGLE PROTEIN                  Heparanase   \n",
       "\n",
       "  target_dictionary|organism  target_dictionary|chembl_id  \\\n",
       "0               Homo sapiens                   CHEMBL1806   \n",
       "1               Homo sapiens                   CHEMBL3921   \n",
       "2               Homo sapiens                   CHEMBL3921   \n",
       "3                        NaN                          NaN   \n",
       "4               Homo sapiens                   CHEMBL3921   \n",
       "\n",
       "  component_synonyms|component_synonym  component_synonyms|syn_type  \n",
       "0                                TOP2A                  GENE_SYMBOL  \n",
       "1                                 HPSE                  GENE_SYMBOL  \n",
       "2                                 HPSE                  GENE_SYMBOL  \n",
       "3                                  NaN                          NaN  \n",
       "4                                 HPSE                  GENE_SYMBOL  \n",
       "\n",
       "[5 rows x 52 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# merge activities and assays data\n",
    "final_df = activities.merge(assays,how='left',left_on='activities|assay_id',right_on='assays|assay_id')\n",
    "final_df = final_df[['activities|activity_id', 'activities|assay_id', 'activities|molregno',\n",
    "                             'activities|pchembl_value', 'activities|type', 'activities|standard_relation', 'activities|standard_value',\n",
    "                             'activities|standard_units', 'activities|standard_flag','activities|standard_type', 'activities|activity_comment',\n",
    "                             'assays|description','assays|assay_type','assays|tid', 'assays|confidence_score','assays|curated_by','assays|chembl_id']]\n",
    "print(f'activities+assays: {final_df.shape}')\n",
    "\n",
    "# merge activities and drug_mechanism based on 'molregno': how='outer' to capture all\n",
    "final_df = final_df.merge(drug_mechanism[['drug_mechanism|molregno','drug_mechanism|mechanism_of_action','drug_mechanism|tid','drug_mechanism|action_type',]],\n",
    "                          how='outer',left_on=['activities|molregno','assays|tid'],right_on=['drug_mechanism|molregno','drug_mechanism|tid'])\n",
    "print(f'added drug mechanism: {final_df.shape}')\n",
    "\n",
    "## remake molregno column by combining\n",
    "ind = final_df[(final_df['drug_mechanism|molregno']==final_df['drug_mechanism|molregno'])& \\\n",
    "         (final_df['activities|molregno']!=final_df['activities|molregno'])].index\n",
    "final_df['activities_drug_mechanism|molregno']=final_df['activities|molregno'].copy()\n",
    "final_df.loc[ind,'activities_drug_mechanism|molregno']=final_df.loc[ind,'drug_mechanism|molregno']\n",
    "print(sum(final_df['activities_drug_mechanism|molregno']==final_df['activities_drug_mechanism|molregno']))\n",
    "del ind\n",
    "\n",
    "## remake tid column by combining\n",
    "ind = final_df[(final_df['drug_mechanism|tid']==final_df['drug_mechanism|tid'])& \\\n",
    "         (final_df['assays|tid']!=final_df['assays|tid'])].index\n",
    "final_df['assays_drug_mechanism|tid']=final_df['assays|tid'].copy()\n",
    "final_df.loc[ind,'assays_drug_mechanism|tid']=final_df.loc[ind,'drug_mechanism|tid']\n",
    "print(sum(final_df['assays_drug_mechanism|tid']==final_df['assays_drug_mechanism|tid']))\n",
    "\n",
    "\n",
    "# merge compound informations\n",
    "final_df = final_df.merge(molecule_dictionary[['molecule_dictionary|molregno', 'molecule_dictionary|pref_name','molecule_dictionary|chembl_id', 'molecule_dictionary|max_phase', \n",
    "                                               'molecule_dictionary|molecule_type','molecule_dictionary|oral','molecule_dictionary|parenteral', 'molecule_dictionary|topical',\n",
    "                                               'molecule_dictionary|black_box_warning','molecule_dictionary|natural_product']],\n",
    "                          how='left',left_on='activities_drug_mechanism|molregno',right_on='molecule_dictionary|molregno')\n",
    "\n",
    "final_df = final_df.merge(molecule_atc_classification[['molecule_atc_classification|molregno','molecule_atc_classification|level5']],\n",
    "                          how='left',left_on='activities_drug_mechanism|molregno',right_on='molecule_atc_classification|molregno')\n",
    "\n",
    "final_df = final_df.merge(atc_classification[['atc_classification|level1','atc_classification|level2','atc_classification|level3','atc_classification|level4','atc_classification|level5',\n",
    "                                              'atc_classification|level1_description','atc_classification|level2_description',\n",
    "                                              'atc_classification|level3_description','atc_classification|level4_description','atc_classification|who_name']],\n",
    "                          how='left',left_on='molecule_atc_classification|level5',right_on='atc_classification|level5')\n",
    "print(f'added compound info: {final_df.shape}')\n",
    "\n",
    "# merge targets\n",
    "## merging target dataframes first (to link tid with gene symbol)\n",
    "targets_final = target_dictionary.merge(target_components,how='left',left_on='target_dictionary|tid',right_on='target_components|tid')\n",
    "targets_final = targets_final.merge(component_synonyms,how='left',left_on='target_components|component_id',right_on='component_synonyms|component_id')\n",
    "print(targets_final['component_synonyms|syn_type'].value_counts())\n",
    "\n",
    "## selecting targets which have 'gene symbol'\n",
    "## syn_type == 'GENE_SYMBOL'\n",
    "targets_final = targets_final[targets_final['component_synonyms|syn_type']=='GENE_SYMBOL']\n",
    "\n",
    "## merge\n",
    "final_df = final_df.merge(targets_final[['target_dictionary|tid','target_dictionary|target_type','target_dictionary|pref_name','target_dictionary|organism','target_dictionary|chembl_id',\n",
    "                                         'component_synonyms|component_synonym', 'component_synonyms|syn_type']],\n",
    "                          how='left',left_on='assays_drug_mechanism|tid',right_on='target_dictionary|tid')\n",
    "\n",
    "print(f'added targets info: {final_df.shape}')\n",
    "final_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "piano-gibraltar",
   "metadata": {},
   "source": [
    "## Save the data frame above for access to non-human entries"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "vertical-spiritual",
   "metadata": {},
   "source": [
    "## Filtering drugs targeting human molecules"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "handed-bracket",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(6708016, 53)"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_df = final_df[final_df['target_dictionary|organism']=='Homo sapiens']\n",
    "final_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "identified-logan",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "available-sharp",
   "metadata": {},
   "source": [
    "## Add target class"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dependent-oregon",
   "metadata": {},
   "source": [
    "- Based on [IDG Protein list](https://druggablegenome.net/IDGProteinList)\n",
    "- add 'Ion channel' from [HGNC, GID:177](https://www.genenames.org/data/genegroup/#!/group/177)\n",
    "- add 'GPCR' from [HGNC, GID:139](https://www.genenames.org/data/genegroup/#!/group/139)\n",
    "- add 'NHR' (Nuclear Hormone Receptors) from [HGNC, GID:71](https://www.genenames.org/data/genegroup/#!/group/71)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "expensive-occurrence",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dict_keys(['Kinase', 'GPCR', 'Ion Channel', 'NHR'])"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# create dictionary for protein classes\n",
    "idg = pd.read_csv('IDG_TargetList_Y4.csv')\n",
    "\n",
    "targetclass_dict={}\n",
    "for c in set(idg['IDGFamily']):\n",
    "    targetclass_dict[c]=list(idg[idg['IDGFamily']==c]['Gene'])\n",
    "\n",
    "ion = pd.read_csv('HGNC_GID177_Ion-channels.txt',sep='\\t')\n",
    "gpcr = pd.read_csv('HGNC_GID139_G-protein-coupled-receptors.txt',sep='\\t')\n",
    "nr = pd.read_csv('HGNC_GID71_Nuclear-hormone-receptors.txt',sep='\\t')\n",
    "\n",
    "targetclass_dict['Ion Channel']=list(set(targetclass_dict['Ion Channel']+list(ion['Approved symbol'])))\n",
    "targetclass_dict['GPCR']=list(set(targetclass_dict['GPCR']+list(gpcr['Approved symbol'])))\n",
    "targetclass_dict['NHR']=list(nr['Approved symbol'].unique())\n",
    "targetclass_dict.keys()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "understood-graduate",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/jovyan/my-conda-envs/cellpymc/lib/python3.7/site-packages/ipykernel_launcher.py:13: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  del sys.path[0]\n",
      "/home/jovyan/my-conda-envs/cellpymc/lib/python3.7/site-packages/ipykernel_launcher.py:14: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  \n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "none           5347947\n",
       "GPCR            903731\n",
       "NHR             177300\n",
       "Ion Channel     174983\n",
       "Kinase          104055\n",
       "Name: target_class, dtype: int64"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# assin protein class to each target\n",
    "def which_class(dictionary, value):\n",
    "    out='none'\n",
    "    for k in dictionary.keys():\n",
    "        if value in dictionary[k]:\n",
    "            if out=='none':\n",
    "                out=k\n",
    "            else:\n",
    "                out=f'{out};{k}'\n",
    "    return out\n",
    "\n",
    "# add target class\n",
    "final_df['target_class']=final_df['component_synonyms|component_synonym'].copy()\n",
    "final_df['target_class']=[which_class(targetclass_dict,t) for t in final_df['target_class']]\n",
    "final_df['target_class'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "continued-brick",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "fatal-ranch",
   "metadata": {},
   "source": [
    "## Save"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "metric-arnold",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 25.7 s, sys: 5.28 s, total: 31 s\n",
      "Wall time: 50.3 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "final_df.to_pickle('chembl_30_merged_genesymbols_humans.pkl')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "southern-furniture",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "apart-scheme",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "herbal-baltimore",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "compatible-intersection",
   "metadata": {},
   "source": [
    "## Session info"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "bored-structure",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<details>\n",
       "<summary>Click to view session information</summary>\n",
       "<pre>\n",
       "-----\n",
       "numpy               1.21.5\n",
       "pandas              1.2.0\n",
       "session_info        1.0.0\n",
       "-----\n",
       "</pre>\n",
       "<details>\n",
       "<summary>Click to view modules imported as dependencies</summary>\n",
       "<pre>\n",
       "backcall            0.2.0\n",
       "colorama            0.4.4\n",
       "cython_runtime      NA\n",
       "dateutil            2.8.1\n",
       "decorator           4.4.2\n",
       "google              NA\n",
       "ipykernel           5.4.2\n",
       "ipython_genutils    0.2.0\n",
       "jedi                0.17.2\n",
       "mpl_toolkits        NA\n",
       "numexpr             2.7.2\n",
       "parso               0.7.1\n",
       "pexpect             4.8.0\n",
       "pickleshare         0.7.5\n",
       "prompt_toolkit      3.0.10\n",
       "ptyprocess          0.7.0\n",
       "pygments            2.7.4\n",
       "pytz                2020.5\n",
       "six                 1.14.0\n",
       "sphinxcontrib       NA\n",
       "storemagic          NA\n",
       "tornado             6.1\n",
       "traitlets           5.0.5\n",
       "wcwidth             0.2.5\n",
       "zmq                 21.0.1\n",
       "zope                NA\n",
       "</pre>\n",
       "</details> <!-- seems like this ends pre, so might as well be explicit -->\n",
       "<pre>\n",
       "-----\n",
       "IPython             7.19.0\n",
       "jupyter_client      6.1.11\n",
       "jupyter_core        4.7.0\n",
       "notebook            6.2.0\n",
       "-----\n",
       "Python 3.7.9 | packaged by conda-forge | (default, Dec  9 2020, 21:08:20) [GCC 9.3.0]\n",
       "Linux-4.15.0-112-generic-x86_64-with-debian-bullseye-sid\n",
       "-----\n",
       "Session information updated at 2022-07-12 21:29\n",
       "</pre>\n",
       "</details>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import session_info\n",
    "session_info.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "opposed-memory",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "heard-newman",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "cellpymc",
   "language": "python",
   "name": "cellpymc"
  },
  "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.7.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}