explore_excape_db.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## ExCAPE-DB\n",
    "\n",
    "Explore the ExCAPE dataset. Processing tsv file with pandas is slow. We can consider convert the dataset to sqlite if we need to do more heavy analysis."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import re\n",
    "from collections import Counter\n",
    "from random import sample\n",
    "from json import load, dump\n",
    "import matplotlib.mlab as mlab\n",
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv(\"./resource/pubchem.chembl.dataset4publication_inchi_smiles.tsv\", sep='\\t')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 196,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(70850163, 12)\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>Ambit_InchiKey</th>\n",
       "      <th>Original_Entry_ID</th>\n",
       "      <th>Entrez_ID</th>\n",
       "      <th>Activity_Flag</th>\n",
       "      <th>pXC50</th>\n",
       "      <th>DB</th>\n",
       "      <th>Original_Assay_ID</th>\n",
       "      <th>Tax_ID</th>\n",
       "      <th>Gene_Symbol</th>\n",
       "      <th>Ortholog_Group</th>\n",
       "      <th>InChI</th>\n",
       "      <th>SMILES</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AAAAZQPHATYWOK-YRBRRWAQNA-N</td>\n",
       "      <td>11399331</td>\n",
       "      <td>2064</td>\n",
       "      <td>A</td>\n",
       "      <td>7.19382</td>\n",
       "      <td>pubchem</td>\n",
       "      <td>248914</td>\n",
       "      <td>9606</td>\n",
       "      <td>ERBB2</td>\n",
       "      <td>1346</td>\n",
       "      <td>InChI=1/C32H29ClN6O3S/c1-4-41-28-16-25-22(15-2...</td>\n",
       "      <td>ClC=1C=C(NC=2C=3C(N=CC2C#N)=CC(OCC)=C(NC(=O)/C...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AAAAZQPHATYWOK-YRBRRWAQNA-N</td>\n",
       "      <td>CHEMBL175513</td>\n",
       "      <td>1956</td>\n",
       "      <td>A</td>\n",
       "      <td>6.73000</td>\n",
       "      <td>chembl20</td>\n",
       "      <td>312997</td>\n",
       "      <td>9606</td>\n",
       "      <td>EGFR</td>\n",
       "      <td>1260</td>\n",
       "      <td>InChI=1/C32H29ClN6O3S/c1-4-41-28-16-25-22(15-2...</td>\n",
       "      <td>C1=2C(=C(C#N)C=NC1=CC(=C(C2)NC(/C=C/CN(C)C)=O)...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AAABHMIRDIOYOK-NPVYFSBINA-N</td>\n",
       "      <td>CHEMBL1527551</td>\n",
       "      <td>10919</td>\n",
       "      <td>N</td>\n",
       "      <td>4.55000</td>\n",
       "      <td>chembl20</td>\n",
       "      <td>737344</td>\n",
       "      <td>9606</td>\n",
       "      <td>EHMT2</td>\n",
       "      <td>6822</td>\n",
       "      <td>InChI=1/C18H14N6O3/c1-23-10-15(24(26)27)16(22-...</td>\n",
       "      <td>O=C(NC=1C=C2N=C(NC2=CC1)C=3C=CC=CC3)C4=NN(C=C4...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AAABHMIRDIOYOK-NPVYFSBINA-N</td>\n",
       "      <td>CHEMBL1527551</td>\n",
       "      <td>19885</td>\n",
       "      <td>A</td>\n",
       "      <td>5.35000</td>\n",
       "      <td>chembl20</td>\n",
       "      <td>688759</td>\n",
       "      <td>10090</td>\n",
       "      <td>RORC</td>\n",
       "      <td>3770</td>\n",
       "      <td>InChI=1/C18H14N6O3/c1-23-10-15(24(26)27)16(22-...</td>\n",
       "      <td>O=C(NC=1C=C2N=C(NC2=CC1)C=3C=CC=CC3)C4=NN(C=C4...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AAABHMIRDIOYOK-NPVYFSBINA-N</td>\n",
       "      <td>CHEMBL1527551</td>\n",
       "      <td>216</td>\n",
       "      <td>N</td>\n",
       "      <td>4.40000</td>\n",
       "      <td>chembl20</td>\n",
       "      <td>688238</td>\n",
       "      <td>9606</td>\n",
       "      <td>ALDH1A1</td>\n",
       "      <td>143</td>\n",
       "      <td>InChI=1/C18H14N6O3/c1-23-10-15(24(26)27)16(22-...</td>\n",
       "      <td>O=C(NC=1C=C2N=C(NC2=CC1)C=3C=CC=CC3)C4=NN(C=C4...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                Ambit_InchiKey Original_Entry_ID  Entrez_ID Activity_Flag  \\\n",
       "0  AAAAZQPHATYWOK-YRBRRWAQNA-N          11399331       2064             A   \n",
       "1  AAAAZQPHATYWOK-YRBRRWAQNA-N      CHEMBL175513       1956             A   \n",
       "2  AAABHMIRDIOYOK-NPVYFSBINA-N     CHEMBL1527551      10919             N   \n",
       "3  AAABHMIRDIOYOK-NPVYFSBINA-N     CHEMBL1527551      19885             A   \n",
       "4  AAABHMIRDIOYOK-NPVYFSBINA-N     CHEMBL1527551        216             N   \n",
       "\n",
       "     pXC50        DB  Original_Assay_ID  Tax_ID Gene_Symbol Ortholog_Group  \\\n",
       "0  7.19382   pubchem             248914    9606       ERBB2           1346   \n",
       "1  6.73000  chembl20             312997    9606        EGFR           1260   \n",
       "2  4.55000  chembl20             737344    9606       EHMT2           6822   \n",
       "3  5.35000  chembl20             688759   10090        RORC           3770   \n",
       "4  4.40000  chembl20             688238    9606     ALDH1A1            143   \n",
       "\n",
       "                                               InChI  \\\n",
       "0  InChI=1/C32H29ClN6O3S/c1-4-41-28-16-25-22(15-2...   \n",
       "1  InChI=1/C32H29ClN6O3S/c1-4-41-28-16-25-22(15-2...   \n",
       "2  InChI=1/C18H14N6O3/c1-23-10-15(24(26)27)16(22-...   \n",
       "3  InChI=1/C18H14N6O3/c1-23-10-15(24(26)27)16(22-...   \n",
       "4  InChI=1/C18H14N6O3/c1-23-10-15(24(26)27)16(22-...   \n",
       "\n",
       "                                              SMILES  \n",
       "0  ClC=1C=C(NC=2C=3C(N=CC2C#N)=CC(OCC)=C(NC(=O)/C...  \n",
       "1  C1=2C(=C(C#N)C=NC1=CC(=C(C2)NC(/C=C/CN(C)C)=O)...  \n",
       "2  O=C(NC=1C=C2N=C(NC2=CC1)C=3C=CC=CC3)C4=NN(C=C4...  \n",
       "3  O=C(NC=1C=C2N=C(NC2=CC1)C=3C=CC=CC3)C4=NN(C=C4...  \n",
       "4  O=C(NC=1C=C2N=C(NC2=CC1)C=3C=CC=CC3)C4=NN(C=C4...  "
      ]
     },
     "execution_count": 196,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(df.shape)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "85307"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "assay_ids = df[\"Original_Assay_ID\"].tolist()\n",
    "len(set(assay_ids))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1667"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "target_ids = df[\"Entrez_ID\"].tolist()\n",
    "len(set(target_ids))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Counter({'A': 1332426, 'N': 69517737})"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Counter(df[\"Activity_Flag\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Check Overlapping with InChI"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>BROAD_ID</th>\n",
       "      <th>CPD_NAME</th>\n",
       "      <th>CPD_NAME_TYPE</th>\n",
       "      <th>CPD_SAMPLE_ID</th>\n",
       "      <th>DOS_LIBRARY</th>\n",
       "      <th>SOURCE_NAME</th>\n",
       "      <th>CHEMIST_NAME</th>\n",
       "      <th>VENDOR_CATALOG_ID</th>\n",
       "      <th>CPD_SMILES</th>\n",
       "      <th>USERCOMMENT</th>\n",
       "      <th>CPD_CANONICAL_SMILES</th>\n",
       "      <th>INCHI</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BRD-A56675431-001-04-0</td>\n",
       "      <td>altizide</td>\n",
       "      <td>INN</td>\n",
       "      <td>SA82748</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Prestwick Chemical Inc.</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Prestw-721</td>\n",
       "      <td>NS(=O)(=O)c1cc2c(NC(CSCC=C)NS2(=O)=O)cc1Cl</td>\n",
       "      <td>NaN</td>\n",
       "      <td>C=CCSCC1Nc2cc(Cl)c(S(N)(=O)=O)cc2S(=O)(=O)N1</td>\n",
       "      <td>InChI=1S/C11H14ClN3O4S3/c1-2-3-20-6-11-14-8-4-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>BRD-A51829654-001-01-4</td>\n",
       "      <td>BRL-15,572</td>\n",
       "      <td>common</td>\n",
       "      <td>SA82481</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Biomol International Inc.</td>\n",
       "      <td>NaN</td>\n",
       "      <td>AC-536</td>\n",
       "      <td>OC(CN1CCN(CC1)c1cccc(Cl)c1)C(c1ccccc1)c1ccccc1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>OC(CN1CCN(c2cccc(Cl)c2)CC1)C(c1ccccc1)c1ccccc1</td>\n",
       "      <td>InChI=1S/C25H27ClN2O/c26-22-12-7-13-23(18-22)2...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BRD-K04046242-001-03-6</td>\n",
       "      <td>equilin</td>\n",
       "      <td>primary-common</td>\n",
       "      <td>SA82922</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Prestwick Chemical Inc.</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Prestw-850</td>\n",
       "      <td>C[C@]12CC[C@H]3C(=CCc4cc(O)ccc34)[C@@H]1CCC2=O</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CC12CCC3C(=CCc4cc(O)ccc43)C1CCC2=O</td>\n",
       "      <td>InChI=1S/C18H20O2/c1-18-9-8-14-13-5-3-12(19)10...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BRD-K16508793-001-01-8</td>\n",
       "      <td>diazepam</td>\n",
       "      <td>INN</td>\n",
       "      <td>SA59660</td>\n",
       "      <td>NaN</td>\n",
       "      <td>MicroSource Discovery Systems Inc.</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1900003</td>\n",
       "      <td>CN1c2ccc(Cl)cc2C(=NCC1=O)c1ccccc1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CN1C(=O)CN=C(c2ccccc2)c2cc(Cl)ccc21</td>\n",
       "      <td>InChI=1S/C16H13ClN2O/c1-19-14-8-7-12(17)9-13(1...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BRD-K09397065-001-01-6</td>\n",
       "      <td>SR 57227A</td>\n",
       "      <td>to-be-curated</td>\n",
       "      <td>SA82504</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Biomol International Inc.</td>\n",
       "      <td>NaN</td>\n",
       "      <td>AC-561</td>\n",
       "      <td>NC1CCN(CC1)c1cccc(Cl)n1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NC1CCN(c2cccc(Cl)n2)CC1</td>\n",
       "      <td>InChI=1S/C10H14ClN3/c11-9-2-1-3-10(13-9)14-6-4...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 BROAD_ID    CPD_NAME   CPD_NAME_TYPE CPD_SAMPLE_ID  \\\n",
       "0  BRD-A56675431-001-04-0    altizide             INN       SA82748   \n",
       "1  BRD-A51829654-001-01-4  BRL-15,572          common       SA82481   \n",
       "2  BRD-K04046242-001-03-6     equilin  primary-common       SA82922   \n",
       "3  BRD-K16508793-001-01-8    diazepam             INN       SA59660   \n",
       "4  BRD-K09397065-001-01-6   SR 57227A   to-be-curated       SA82504   \n",
       "\n",
       "  DOS_LIBRARY                         SOURCE_NAME CHEMIST_NAME  \\\n",
       "0         NaN             Prestwick Chemical Inc.          NaN   \n",
       "1         NaN           Biomol International Inc.          NaN   \n",
       "2         NaN             Prestwick Chemical Inc.          NaN   \n",
       "3         NaN  MicroSource Discovery Systems Inc.          NaN   \n",
       "4         NaN           Biomol International Inc.          NaN   \n",
       "\n",
       "  VENDOR_CATALOG_ID                                      CPD_SMILES  \\\n",
       "0        Prestw-721      NS(=O)(=O)c1cc2c(NC(CSCC=C)NS2(=O)=O)cc1Cl   \n",
       "1            AC-536  OC(CN1CCN(CC1)c1cccc(Cl)c1)C(c1ccccc1)c1ccccc1   \n",
       "2        Prestw-850  C[C@]12CC[C@H]3C(=CCc4cc(O)ccc34)[C@@H]1CCC2=O   \n",
       "3           1900003               CN1c2ccc(Cl)cc2C(=NCC1=O)c1ccccc1   \n",
       "4            AC-561                         NC1CCN(CC1)c1cccc(Cl)n1   \n",
       "\n",
       "  USERCOMMENT                            CPD_CANONICAL_SMILES  \\\n",
       "0         NaN    C=CCSCC1Nc2cc(Cl)c(S(N)(=O)=O)cc2S(=O)(=O)N1   \n",
       "1         NaN  OC(CN1CCN(c2cccc(Cl)c2)CC1)C(c1ccccc1)c1ccccc1   \n",
       "2         NaN              CC12CCC3C(=CCc4cc(O)ccc43)C1CCC2=O   \n",
       "3         NaN             CN1C(=O)CN=C(c2ccccc2)c2cc(Cl)ccc21   \n",
       "4         NaN                         NC1CCN(c2cccc(Cl)n2)CC1   \n",
       "\n",
       "                                               INCHI  \n",
       "0  InChI=1S/C11H14ClN3O4S3/c1-2-3-20-6-11-14-8-4-...  \n",
       "1  InChI=1S/C25H27ClN2O/c26-22-12-7-13-23(18-22)2...  \n",
       "2  InChI=1S/C18H20O2/c1-18-9-8-14-13-5-3-12(19)10...  \n",
       "3  InChI=1S/C16H13ClN2O/c1-19-14-8-7-12(17)9-13(1...  \n",
       "4  InChI=1S/C10H14ClN3/c11-9-2-1-3-10(13-9)14-6-4...  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bone_df = pd.read_csv(\"./data/test/meta_data/chemical_annotations_inchi.csv\")\n",
    "bone_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are 30405 compounds in U2OS, and 998535 compounds in ExCAPE.\n"
     ]
    }
   ],
   "source": [
    "total_inchi = set(df[\"InChI\"])\n",
    "bone_inchi = set(bone_df[\"INCHI\"])\n",
    "print(\"There are {} compounds in U2OS, and {} compounds in ExCAPE.\".format(len(bone_inchi),\n",
    "                                                                           len(total_inchi)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "set()"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_inchi & bone_inchi"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['InChI=1S/C22H18N2O5S2/c1-10-3-6-12(7-4-10)24-20(26)16-15(11-5-8-13(25)14(9-11)29-2)17-19(23-22(28)31-17)30-18(16)21(24)27/h3-9,15-16,18,25H,1-2H3,(H,23,28)',\n",
       " 'InChI=1S/C15H15N/c1-2-6-12(7-3-1)15-11-16-10-13-8-4-5-9-14(13)15/h1-9,15-16H,10-11H2',\n",
       " 'InChI=1S/C28H34FN3O5/c1-17-14-32(28(35)21-7-5-6-8-23(21)29)18(2)16-37-24-12-11-20(30-26(33)19-9-10-19)13-22(24)27(34)31(3)15-25(17)36-4/h5-8,11-13,17-19,25H,9-10,14-16H2,1-4H3,(H,30,33)/t17-,18-,25+/m1/s1',\n",
       " 'InChI=1S/C17H16FNO3/c18-14-4-2-1-3-13(14)17(20)19-8-7-12-5-6-15-16(11-12)22-10-9-21-15/h1-6,11H,7-10H2,(H,19,20)',\n",
       " 'InChI=1S/C10H10O4/c1-14-10(13)5-2-7-6-8(11)3-4-9(7)12/h2-6,11-12H,1H3',\n",
       " 'InChI=1S/C25H23N5O4/c1-15-2-4-17(5-3-15)22-21-20(19(14-26)23(27)34-24(21)29-28-22)16-6-8-18(9-7-16)33-25(31)30-10-12-32-13-11-30/h2-9,20H,10-13,27H2,1H3,(H,28,29)',\n",
       " 'InChI=1S/C31H42N4O6/c1-20-17-35(21(2)19-36)30(38)25-11-8-12-26(33-29(37)22-9-6-5-7-10-22)28(25)41-27(20)18-34(3)31(39)32-23-13-15-24(40-4)16-14-23/h8,11-16,20-22,27,36H,5-7,9-10,17-19H2,1-4H3,(H,32,39)(H,33,37)/t20-,21-,27+/m1/s1',\n",
       " 'InChI=1S/C27H33FN4O5/c1-16(2)30-27(35)31-19-8-11-23-21(12-19)26(34)32(3)22-10-9-20(37-24(22)15-36-23)13-25(33)29-14-17-4-6-18(28)7-5-17/h4-8,11-12,16,20,22,24H,9-10,13-15H2,1-3H3,(H,29,33)(H2,30,31,35)/t20-,22-,24-/m0/s1',\n",
       " 'InChI=1S/C25H31N5O5S/c1-28-7-9-30(10-8-28)23(31)12-17-4-5-20-22(35-17)13-34-21-6-3-16(11-18(21)25(33)29(20)2)27-24(32)19-14-36-15-26-19/h3,6,11,14-15,17,20,22H,4-5,7-10,12-13H2,1-2H3,(H,27,32)/t17-,20+,22-/m1/s1',\n",
       " 'InChI=1S/C18H20ClN3OS/c1-2-3-4-9-22-10-15(23)16(17(22)20)18-21-14(11-24-18)12-5-7-13(19)8-6-12/h5-8,11H,2-4,9-10,20H2,1H3']"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sample(bone_inchi, 10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['InChI=1/C23H14ClF2N3O/c24-18-11-15(9-10-19(18)26)27-23-28-21(13-5-7-14(25)8-6-13)17-12-30-20-4-2-1-3-16(20)22(17)29-23/h1-11H,12H2,(H,27,28,29)/f/h27H',\n",
       " 'InChI=1/C20H21N3O5S/c1-22-17-7-6-16(12-18(17)28-20(22)25)29(26,27)21-10-8-19(24)23-11-9-14-4-2-3-5-15(14)13-23/h2-7,12,21H,8-11,13H2,1H3',\n",
       " 'InChI=1/C26H27N3O8S/c1-16-11-22(17(2)29(16)20-7-10-24-25(12-20)37-15-36-24)23(31)14-35-26(32)13-28(4)38(33,34)21-8-5-19(6-9-21)27-18(3)30/h5-12H,13-15H2,1-4H3,(H,27,30)/f/h27H',\n",
       " 'InChI=1/C20H25F3N4O2/c21-20(22,23)16-6-4-15(5-7-16)19-24-18(29-25-19)14-27-8-2-1-3-17(27)13-26-9-11-28-12-10-26/h4-7,17H,1-3,8-14H2',\n",
       " 'InChI=1/C20H16Cl3N3O2/c21-11-1-2-12(16(22)6-11)14-8-24-9-15(14)20(28)26-18-5-10-3-4-25-19(27)13(10)7-17(18)23/h1-7,14-15,24H,8-9H2,(H,25,27)(H,26,28)/f/h26-27H',\n",
       " 'InChI=1/C22H20N2O4/c1-27-19-10-4-8-17(13-19)23-21(25)15-6-3-7-16(12-15)22(26)24-18-9-5-11-20(14-18)28-2/h3-14H,1-2H3,(H,23,25)(H,24,26)/f/h23-24H',\n",
       " 'InChI=1/C20H29N3O3/c1-15-7-5-9-18(16(15)2)26-14-11-21-10-3-4-12-23-19(24)17-8-6-13-22(17)20(23)25/h5,7,9,17,21H,3-4,6,8,10-14H2,1-2H3',\n",
       " 'InChI=1/C22H30N4O4/c1-4-5-12-29-13-8-11-26-20(23)18(22(27)30-15(2)14-28-3)19-21(26)25-17-10-7-6-9-16(17)24-19/h6-7,9-10,15H,4-5,8,11-14,23H2,1-3H3',\n",
       " 'InChI=1/C11H13N3O/c1-9-11(14-15-13-9)8-12-7-10-5-3-2-4-6-10/h2-6,12H,7-8H2,1H3',\n",
       " 'InChI=1/C24H32N2O3/c1-4-29-24(28)22-17(2)23(19-12-8-7-9-13-19)26(18(22)3)16-21(27)25-20-14-10-5-6-11-15-20/h7-9,12-13,20H,4-6,10-11,14-16H2,1-3H3,(H,25,27)/f/h25H']"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sample(total_inchi, 10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are no overlapping compounds. After inspecting on the random 10 samples from each list, we notice the `rdkit` converted InChI is in standard format (`InChI=1S`) while ExCAPE-DB is using version 1 (`InchI=1`) ."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "9468"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "modified_bone_inchi = set([re.sub(r'(InChI=1S)(.+)', r'InChI=1\\2', s) for s in bone_inchi if not pd.isna(s)])\n",
    "len(total_inchi & modified_bone_inchi)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "After naively remove \"S\" from our InChI strings, we got 9468 intersections with ExCAPE-DB. In he openReview paper, they got 11,585 interactions.\n",
    "\n",
    "The difference can be explained by:\n",
    "1. We are using a cleaned dataset instead of raw ChEMBL.\n",
    "2. It is a wrong way to convert version 1 InChI to standard InChI."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Check Overlapping with Converted InChI\n",
    "\n",
    "ExCAPE-DB also provides SMILES encoding. Hopefully an intersection with SMILES gives the same result as above. We are not sure if the encoding is the same as the canonical SMILES generated by `rdkit`. We can try to intersect with the canonical SMILES from U2OS first."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [],
   "source": [
    "total_smiles = set(df[\"SMILES\"])\n",
    "bone_smiles = set(bone_df[\"CPD_CANONICAL_SMILES\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "set()"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_smiles & bone_smiles"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It seems the the SMILES from ExCAPE-DB are not canonical. We can use Condor to convert all InChI entries from ExCAPE-DB to the standard InChI format, and compute the overlapping again."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "984413"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "converted_inchi = load(open(\"./resource/std_inchi.json\", 'r'))\n",
    "len(set(converted_inchi))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The number of compounds in ExCAPE-DB drops after conversion, since there are some entries `rdkit` fail to convert. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "27000"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(set(converted_inchi) & set(bone_df[\"INCHI\"]))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The number of overlapping compounds increases from 9468 to 27000. It shows that changing '1' to `1S` manually is wrong. However, `rdkit` gives warning `Omitted undefined stereo` and/or `Charges were rearranged` for 258831 compounds during conversion. Due to the high number overlapping compounds, theses two warnings may not directly lead to accidental overlapping with U2OS InChI, but we still want to understand the warnings before committing to this method."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Thresholding Assays\n",
    "\n",
    "According to the [ICLR paper](https://openreview.net/pdf?id=S1gBgnR9Y7), we want to further filter the assays to use.\n",
    "\n",
    "Some filtering ideas from the paper:\n",
    "1. Use assays having results from at least 10 different compounds.\n",
    "1. Use assays having at least 10 positive and 10 negative samples (stronger).\n",
    "2. Multi-threshold concentration response (CR) on positive results at level 5.5, 6.5, 7.5, then concatenate three matrices (how?).\n",
    "\n",
    "**Note: The following filtering are using the 9,468 overlapping compounds instead of 27,000.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(849383, 12)"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "intersection = total_inchi & modified_bone_inchi\n",
    "\n",
    "# Construct a subset dataframe with the intersection compounds\n",
    "intersect_index = [True if i in intersection else False for i in df[\"InChI\"]]\n",
    "intersect_df = df[intersect_index]\n",
    "intersect_df.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Filter 1: At least 10 different compounds"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are 945 targets for those 9468 compounds, and 575 of the assays have at least 10 compounds tested.\n"
     ]
    }
   ],
   "source": [
    "# Filtering targets with at least 10 compounds\n",
    "target_count = Counter(intersect_df[\"Entrez_ID\"])\n",
    "filter_1_targets = [t for t in target_count if target_count[t] >= 10]\n",
    "\n",
    "print(\"There are {} targets for those {} compounds, and {} of the assays have at least 10 compounds tested.\".\\\n",
    "      format(len(target_count), len(intersection), len(filter_1_targets)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Filter 2: At least 10 positive and 10 negative samples"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are 19462 positive results and 829921 negative results. 141 assays haivng both results greater or equal to 10.\n"
     ]
    }
   ],
   "source": [
    "# Filtering targets with at least 10 positive results and 10 negative results\n",
    "pos_intersect = intersect_df[intersect_df[\"Activity_Flag\"] != \"N\"]\n",
    "pos_count = Counter(pos_intersect[\"Entrez_ID\"])\n",
    "\n",
    "neg_intersect = intersect_df[intersect_df[\"Activity_Flag\"] == \"N\"]\n",
    "neg_count = Counter(neg_intersect[\"Entrez_ID\"])\n",
    "\n",
    "filter_2_targets = [t for t in target_count if (t in neg_count) and (neg_count[t] >= 10) and\n",
    "                    (t in pos_count) and (pos_count[t] >= 10)]\n",
    "\n",
    "print(\"There are {} positive results and {} negative results. {} assays haivng both results greater or equal to 10.\".\\\n",
    "      format(len(pos_intersect), len(neg_intersect), len(filter_2_targets)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Under filter 2, there are 9404 compounds left.\n"
     ]
    }
   ],
   "source": [
    "filter_2_df = intersect_df[intersect_df[\"Entrez_ID\"].isin(filter_2_targets)]\n",
    "filter_2_compounds = set(filter_2_df[\"InChI\"])\n",
    "\n",
    "print(\"Under filter 2, there are {} compounds left.\".format(len(filter_2_compounds)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Filter 3: Concentration response thresholds"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 145,
   "metadata": {},
   "outputs": [],
   "source": [
    "dump(pos_intersect[\"pXC50\"].tolist(), open(\"./plots/xc_all.json\", 'w'), indent=2)\n",
    "filter_2_index = [True if t in filter_2_targets else False for t in pos_intersect[\"Entrez_ID\"]]\n",
    "dump(pos_intersect[filter_2_index][\"pXC50\"].tolist(), open(\"./plots/xc_filter_2.json\", 'w'), indent=2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![](./plots/pxc50.png)\n",
    "\n",
    "Here is the plot of the `pxc50` distribution. Higher value indicates the higher confidence for positive effect. According to the paper of ExCAPE, they should have removed all samples with `pxc50 > 10`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Comments\n",
    "\n",
    "1. For now, I think filter 2 alone is good enough. We can get a $9404 \\times 141$ output matrix.\n",
    "2. We can discuss how to concatenate three matrices with different `pxc50` thresholds."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 159,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(357796, 12)\n"
     ]
    }
   ],
   "source": [
    "filter_2_df = intersect_df[intersect_df[\"Entrez_ID\"].isin(filter_2_targets)]\n",
    "print(filter_2_df.shape)\n",
    "\n",
    "# Save the new csv\n",
    "intersect_df.to_csv(\"intersect_df.csv\", index=False)\n",
    "filter_2_df.to_csv(\"filtered_df.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "9404\n"
     ]
    }
   ],
   "source": [
    "# Save the selected compound identifier\n",
    "filter_2_compounds = set(filter_2_df[\"InChI\"])\n",
    "board_ids = []\n",
    "inchis = []\n",
    "\n",
    "for c in filter_2_compounds:\n",
    "    # Convert InChI v1 to standard InChI\n",
    "    std_name = re.sub(r'(InChI=1)(.+)', r'InChI=1S\\2', c)\n",
    "    board_id = bone_df.loc[bone_df[\"INCHI\"] == std_name][\"BROAD_ID\"].values[0]\n",
    "    board_ids.append(board_id)\n",
    "    \n",
    "    # We also want to collect inchis, because the order of set() is unstable\n",
    "    # We want to keep the board_ids and inchis have the same order\n",
    "    # Collect the v1 InChI here\n",
    "    inchis.append(c)\n",
    "    \n",
    "print(len(board_ids))\n",
    "dump({\"board_ids\": board_ids,\n",
    "      \"inchis\": inchis},\n",
    "     open(\"./resource/selected_compounds.json\", 'w'), indent=2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Build Output Matrix\n",
    "\n",
    "Let's build our multi-task output matrix with size $9404\\times 141$."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Ambit_InchiKey</th>\n",
       "      <th>Original_Entry_ID</th>\n",
       "      <th>Entrez_ID</th>\n",
       "      <th>Activity_Flag</th>\n",
       "      <th>pXC50</th>\n",
       "      <th>DB</th>\n",
       "      <th>Original_Assay_ID</th>\n",
       "      <th>Tax_ID</th>\n",
       "      <th>Gene_Symbol</th>\n",
       "      <th>Ortholog_Group</th>\n",
       "      <th>InChI</th>\n",
       "      <th>SMILES</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AACRWZVDRSTLKY-UHFFFAOYNA-N</td>\n",
       "      <td>796971</td>\n",
       "      <td>1081</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>pubchem</td>\n",
       "      <td>624291</td>\n",
       "      <td>9606</td>\n",
       "      <td>CGA</td>\n",
       "      <td>731</td>\n",
       "      <td>InChI=1/C11H14N2O4S/c14-13(15)10-6-2-3-7-11(10...</td>\n",
       "      <td>S(=O)(=O)(N1CCCCC1)C=2C(N(=O)=O)=CC=CC2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AACRWZVDRSTLKY-UHFFFAOYNA-N</td>\n",
       "      <td>796971</td>\n",
       "      <td>10951</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>pubchem</td>\n",
       "      <td>540317</td>\n",
       "      <td>9606</td>\n",
       "      <td>CBX1</td>\n",
       "      <td>6848</td>\n",
       "      <td>InChI=1/C11H14N2O4S/c14-13(15)10-6-2-3-7-11(10...</td>\n",
       "      <td>S(=O)(=O)(N1CCCCC1)C=2C(N(=O)=O)=CC=CC2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AACRWZVDRSTLKY-UHFFFAOYNA-N</td>\n",
       "      <td>796971</td>\n",
       "      <td>11201</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>pubchem</td>\n",
       "      <td>588590</td>\n",
       "      <td>9606</td>\n",
       "      <td>POLI</td>\n",
       "      <td>7040</td>\n",
       "      <td>InChI=1/C11H14N2O4S/c14-13(15)10-6-2-3-7-11(10...</td>\n",
       "      <td>S(=O)(=O)(N1CCCCC1)C=2C(N(=O)=O)=CC=CC2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AACRWZVDRSTLKY-UHFFFAOYNA-N</td>\n",
       "      <td>796971</td>\n",
       "      <td>1128</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>pubchem_screening</td>\n",
       "      <td>588852</td>\n",
       "      <td>9606</td>\n",
       "      <td>CHRM1</td>\n",
       "      <td>752</td>\n",
       "      <td>InChI=1/C11H14N2O4S/c14-13(15)10-6-2-3-7-11(10...</td>\n",
       "      <td>S(=O)(=O)(N1CCCCC1)C=2C(N(=O)=O)=CC=CC2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AACRWZVDRSTLKY-UHFFFAOYNA-N</td>\n",
       "      <td>796971</td>\n",
       "      <td>1132</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>pubchem_screening</td>\n",
       "      <td>624127</td>\n",
       "      <td>9606</td>\n",
       "      <td>CHRM4</td>\n",
       "      <td>756</td>\n",
       "      <td>InChI=1/C11H14N2O4S/c14-13(15)10-6-2-3-7-11(10...</td>\n",
       "      <td>S(=O)(=O)(N1CCCCC1)C=2C(N(=O)=O)=CC=CC2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                Ambit_InchiKey Original_Entry_ID  Entrez_ID Activity_Flag  \\\n",
       "0  AACRWZVDRSTLKY-UHFFFAOYNA-N            796971       1081             N   \n",
       "1  AACRWZVDRSTLKY-UHFFFAOYNA-N            796971      10951             N   \n",
       "2  AACRWZVDRSTLKY-UHFFFAOYNA-N            796971      11201             N   \n",
       "3  AACRWZVDRSTLKY-UHFFFAOYNA-N            796971       1128             N   \n",
       "4  AACRWZVDRSTLKY-UHFFFAOYNA-N            796971       1132             N   \n",
       "\n",
       "   pXC50                 DB  Original_Assay_ID  Tax_ID Gene_Symbol  \\\n",
       "0    NaN            pubchem             624291    9606         CGA   \n",
       "1    NaN            pubchem             540317    9606        CBX1   \n",
       "2    NaN            pubchem             588590    9606        POLI   \n",
       "3    NaN  pubchem_screening             588852    9606       CHRM1   \n",
       "4    NaN  pubchem_screening             624127    9606       CHRM4   \n",
       "\n",
       "  Ortholog_Group                                              InChI  \\\n",
       "0            731  InChI=1/C11H14N2O4S/c14-13(15)10-6-2-3-7-11(10...   \n",
       "1           6848  InChI=1/C11H14N2O4S/c14-13(15)10-6-2-3-7-11(10...   \n",
       "2           7040  InChI=1/C11H14N2O4S/c14-13(15)10-6-2-3-7-11(10...   \n",
       "3            752  InChI=1/C11H14N2O4S/c14-13(15)10-6-2-3-7-11(10...   \n",
       "4            756  InChI=1/C11H14N2O4S/c14-13(15)10-6-2-3-7-11(10...   \n",
       "\n",
       "                                    SMILES  \n",
       "0  S(=O)(=O)(N1CCCCC1)C=2C(N(=O)=O)=CC=CC2  \n",
       "1  S(=O)(=O)(N1CCCCC1)C=2C(N(=O)=O)=CC=CC2  \n",
       "2  S(=O)(=O)(N1CCCCC1)C=2C(N(=O)=O)=CC=CC2  \n",
       "3  S(=O)(=O)(N1CCCCC1)C=2C(N(=O)=O)=CC=CC2  \n",
       "4  S(=O)(=O)(N1CCCCC1)C=2C(N(=O)=O)=CC=CC2  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "filter_2_df = pd.read_csv(\"./resource/filtered_df.csv\")\n",
    "filter_2_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "141\n"
     ]
    }
   ],
   "source": [
    "# The schema we use: -1: NA, 0: Neg, 1: Pos\n",
    "output_matrix = np.zeros((9404, 141)) - 1\n",
    "\n",
    "# Create the order dictionary for all selected comounds\n",
    "selected_compounds = load(open(\"./resource/selected_compounds.json\", 'r'))\n",
    "inchi_order_dict = {}\n",
    "inchi_names = []\n",
    "broad_ids = []\n",
    "for i in range(len(selected_compounds[\"inchis\"])):\n",
    "    inchi_order_dict[selected_compounds[\"inchis\"][i]] = i\n",
    "    \n",
    "    inchi_names.append(selected_compounds[\"inchis\"][i])\n",
    "    broad_ids.append(selected_compounds[\"board_ids\"][i])\n",
    "    \n",
    "# Create the order dictionary for all assays\n",
    "assay_order_dict = {}\n",
    "assays = []\n",
    "count = 0\n",
    "for i in set(filter_2_df[\"Entrez_ID\"]):\n",
    "    assay_order_dict[i] = count\n",
    "    assays.append(i)\n",
    "    count += 1\n",
    "print(count)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "# The most efficient way to build this matrix is to iterate through the rows of filter_2_df\n",
    "# while filling in the pre-inited matrx\n",
    "\n",
    "for index, row in filter_2_df.iterrows():\n",
    "    cur_inchi = row[\"InChI\"]\n",
    "    cur_assay = row[\"Entrez_ID\"]\n",
    "    output_matrix[inchi_order_dict[cur_inchi], assay_order_dict[cur_assay]] = 0 if \\\n",
    "        row[\"Activity_Flag\"] == 'N' else 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([[-1., -1., -1., ...,  0.,  0.,  0.],\n",
       "       [-1., -1., -1., ..., -1., -1., -1.],\n",
       "       [-1., -1., -1., ..., -1.,  1., -1.],\n",
       "       ...,\n",
       "       [-1., -1., -1., ..., -1., -1., -1.],\n",
       "       [-1., -1., -1., ..., -1., -1., -1.],\n",
       "       [-1., -1., -1., ...,  0., -1., -1.]])"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "output_matrix"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "In our output matrix, there are 73.02% NA, 1.06% Positive, and 25.92% Negative.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "Counter({-1.0: 968221, 0.0: 343664, 1.0: 14079})"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "count = Counter(output_matrix.reshape(-1).tolist())\n",
    "total = output_matrix.shape[0] * output_matrix.shape[1]\n",
    "print(\"In our output matrix, there are {:.2f}% NA, {:.2f}% Positive, and {:.2f}% Negative.\".format(\n",
    "    count[-1]/total*100, count[1]/total*100, count[0]/total*100))\n",
    "count"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save the output matrix along with row/column label info\n",
    "np.savez(\"./resource/output_matrix.npz\", output_matrix=output_matrix,\n",
    "         compound_inchi=inchi_names, compound_broad_id=broad_ids, assay=assays)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Build Output Matrix with Converted InChI"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "27000"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_inchi = load(open(\"./resource/std_inchi.json\", 'r'))\n",
    "converted_inchi = new_inchi[\"converted_inchi\"]\n",
    "original_inchi = new_inchi[\"old_inchi\"]\n",
    "len(set(converted_inchi) & set(bone_df[\"INCHI\"]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "27307"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get the number of matched original inchi entries (with replicate allowed)\n",
    "intersection = set(converted_inchi) & set(bone_df[\"INCHI\"])\n",
    "intersect_index_of_conv_inchi = [i for i in range(len(converted_inchi)) if converted_inchi[i] in intersection]\n",
    "intersect_original_array = np.array(original_inchi)[intersect_index_of_conv_inchi]\n",
    "intersect_original = set(intersect_original_array)\n",
    "len(intersect_original)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We found 27,000 unique matches of converted ExCAPE InChI and U2OS InChI. However, those 27,000 converted InChI map back to 27,307 unique original InChI in ExCAPE. It shows that our InChI conversion is not one-to-one.\n",
    "\n",
    "We can find those problematic original InChI and exclude them from the intersection set."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [],
   "source": [
    "intersect_con_array = np.array(converted_inchi)[intersect_index_of_conv_inchi]\n",
    "intersect_con_array_count = Counter(intersect_con_array)\n",
    "duplicates = [key for key in intersect_con_array_count if intersect_con_array_count[key] > 1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [],
   "source": [
    "intersect_con_array = np.array(converted_inchi)[intersect_index_of_conv_inchi]\n",
    "converted_inchi_count = Counter(intersect_con_array)\n",
    "\n",
    "# Find the duplicates in the converted inchi array\n",
    "duplicates = set([key for key in converted_inchi_count if converted_inchi_count[key] > 1])\n",
    "duplicated_index = [True if converted_inchi[i] in duplicates else False for i in range(len(converted_inchi))]\n",
    "duplicated_original = np.array(original_inchi)[duplicated_index]\n",
    "duplicated_converted = np.array(converted_inchi)[duplicated_index]\n",
    "duplicated_dict = dict(zip(duplicated_original, duplicated_converted))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "301"
      ]
     },
     "execution_count": 114,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(duplicates)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Counter({2: 296, 4: 1, 3: 4})"
      ]
     },
     "execution_count": 117,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Build a collision dictionary\n",
    "collision_dict = {}\n",
    "for d in duplicated_dict:\n",
    "    if duplicated_dict[d] in collision_dict:\n",
    "        if d not in collision_dict[duplicated_dict[d]]:\n",
    "            collision_dict[duplicated_dict[d]].append(d)\n",
    "    else:\n",
    "        collision_dict[duplicated_dict[d]] = [d]\n",
    "\n",
    "# Save the collision information\n",
    "dump(collision_dict, open(\"./resource/excape_inchi_collision.npz\", 'w'))\n",
    "\n",
    "collision_count = Counter([len(collision_dict[k]) for k in collision_dict])\n",
    "collision_count"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are 296 converted InChI having 2-collision, 4 conversions having 3-collision, and 1 with 4-collision."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "# Strings: \n",
      " \t['InChI=1/C13H10ClF3N4O2/c1-21-6-9(14)10(19-21)11(18)20-23-12(22)7-4-2-3-5-8(7)13(15,16)17/h2-6H,1H3,(H2,18,20)/f/h18H2', 'InChI=1/C13H10ClF3N4O2/c1-21-6-9(14)10(19-21)11(18)20-23-12(22)7-4-2-3-5-8(7)13(15,16)17/h2-6H,1H3,(H2,18,20)/f/h18H2/b20-11-'] \n",
      "# Converted to: \n",
      " \tInChI=1S/C13H10ClF3N4O2/c1-21-6-9(14)10(19-21)11(18)20-23-12(22)7-4-2-3-5-8(7)13(15,16)17/h2-6H,1H3,(H2,18,20)\n",
      "\n",
      "# Strings: \n",
      " \t['InChI=1/C15H12BrN3S/c16-12-6-8-13(9-7-12)19-10-17-14(18-15(19)20)11-4-2-1-3-5-11/h1-9H,10H2,(H,17,18,20)/f/h18H', 'InChI=1/C15H12BrN3S/c16-12-6-8-13(9-7-12)19-10-17-14(18-15(19)20)11-4-2-1-3-5-11/h1-9H,10H2,(H,17,18,20)/f/h17H'] \n",
      "# Converted to: \n",
      " \tInChI=1S/C15H12BrN3S/c16-12-6-8-13(9-7-12)19-10-17-14(18-15(19)20)11-4-2-1-3-5-11/h1-9H,10H2,(H,17,18,20)\n",
      "\n",
      "# Strings: \n",
      " \t['InChI=1/C16H17N5O2/c1-8-9(2)17-16(20-14(8)22)21-15-18-10(3)12-6-5-11(23-4)7-13(12)19-15/h5-7H,1-4H3,(H2,17,18,19,20,21,22)/f/h21-22H', 'InChI=1/C16H17N5O2/c1-8-9(2)17-16(20-14(8)22)21-15-18-10(3)12-6-5-11(23-4)7-13(12)19-15/h5-7H,1-4H3,(H2,17,18,19,20,21,22)/f/h19,22H'] \n",
      "# Converted to: \n",
      " \tInChI=1S/C16H17N5O2/c1-8-9(2)17-16(20-14(8)22)21-15-18-10(3)12-6-5-11(23-4)7-13(12)19-15/h5-7H,1-4H3,(H2,17,18,19,20,21,22)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "times = 0\n",
    "for k, v in collision_dict.items():\n",
    "    print(\"# Strings: \\n \\t{} \\n# Converted to: \\n \\t{}\\n\".format(v, k))\n",
    "    times += 1\n",
    "    if times == 3:\n",
    "        break"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The original strings causing collisions are pretty similar except the very ending proportion. We can exclude those compounds from our intersection."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2179339, 12)"
      ]
     },
     "execution_count": 133,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Exclude the collisions\n",
    "intersect_original_array = np.array(original_inchi)[intersect_index_of_conv_inchi]\n",
    "intersect_original = set(intersect_original_array)\n",
    "intersect_original = intersect_original - set(duplicated_original)\n",
    "\n",
    "# Construct a subset dataframe with the intersection compounds\n",
    "intersect_index = [True if i in intersect_original else False for i in df[\"InChI\"]]\n",
    "intersect_df = df[intersect_index]\n",
    "intersect_df.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "####  Filter 2: At least 10 positive and 10 negative samples"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 147,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are 45748 positive results and 2133591 negative results. 209 assays haivng both results greater or equal to 10.\n"
     ]
    }
   ],
   "source": [
    "# Filtering targets with at least 10 positive results and 10 negative results\n",
    "pos_intersect = intersect_df[intersect_df[\"Activity_Flag\"] != \"N\"]\n",
    "pos_count = Counter(pos_intersect[\"Entrez_ID\"])\n",
    "\n",
    "neg_intersect = intersect_df[intersect_df[\"Activity_Flag\"] == \"N\"]\n",
    "neg_count = Counter(neg_intersect[\"Entrez_ID\"])\n",
    "\n",
    "target_count = Counter(intersect_df[\"Entrez_ID\"])\n",
    "filter_2_targets = [t for t in target_count if (t in neg_count) and (neg_count[t] >= 10) and\n",
    "                    (t in pos_count) and (pos_count[t] >= 10)]\n",
    "\n",
    "print(\"There are {} positive results and {} negative results. {} assays haivng both results greater or equal to 10.\".\\\n",
    "      format(len(pos_intersect), len(neg_intersect), len(filter_2_targets)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*If we don't exclude collisions, there are 212 assays passing filter 2.*"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Build the Output Matrix\n",
    "\n",
    "We now want to construct a $26638 \\times 209$ output matrix."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1218901, 12)\n"
     ]
    }
   ],
   "source": [
    "filter_2_df = intersect_df[intersect_df[\"Entrez_ID\"].isin(set(filter_2_targets))]\n",
    "print(filter_2_df.shape)\n",
    "\n",
    "# Save the new csv\n",
    "intersect_df.to_csv(\"./resource/intersect_df_convert.csv\", index=False)\n",
    "filter_2_df.to_csv(\"./resource/filtered_df_convert.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 144,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "26638\n"
     ]
    }
   ],
   "source": [
    "# Save the selected compound identifier\n",
    "filter_2_compounds = set(filter_2_df[\"InChI\"])\n",
    "board_ids = []\n",
    "inchis = []\n",
    "conversion_dict = dict(zip(original_inchi, converted_inchi))\n",
    "\n",
    "for c in filter_2_compounds:\n",
    "    # Convert InChI v1 to standard InChI\n",
    "    std_name = conversion_dict[c]\n",
    "    board_id = bone_df.loc[bone_df[\"INCHI\"] == std_name][\"BROAD_ID\"].values[0]\n",
    "    board_ids.append(board_id)\n",
    "    \n",
    "    # We also want to collect inchis, because the order of set() is unstable\n",
    "    # We want to keep the board_ids and inchis have the same order\n",
    "    # Collect the v1 InChI here\n",
    "    inchis.append(c)\n",
    "    \n",
    "print(len(board_ids))\n",
    "dump({\"board_ids\": board_ids,\n",
    "      \"inchis\": inchis},\n",
    "     open(\"./resource/selected_compounds.json\", 'w'), indent=2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 165,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1218901, 12)\n",
      "1218901\n"
     ]
    }
   ],
   "source": [
    "# The schema we use: -1: NA, 0: Neg, 1: Pos\n",
    "output_matrix = np.zeros((26638, 209)) - 1\n",
    "\n",
    "# Create the order dictionary for all selected comounds\n",
    "inchi_order_dict = {}\n",
    "inchi_names = []\n",
    "broad_id_names = []\n",
    "\n",
    "for i in range(len(inchis)):\n",
    "    inchi_order_dict[inchis[i]] = i\n",
    "    \n",
    "    inchi_names.append(inchis[i])\n",
    "    broad_id_names.append(board_ids[i])\n",
    "    \n",
    "# Create the order dictionary for all assays\n",
    "assay_order_dict = {}\n",
    "assays = []\n",
    "count = 0\n",
    "for i in set(filter_2_df[\"Entrez_ID\"]):\n",
    "    assay_order_dict[i] = count\n",
    "    assays.append(i)\n",
    "    count += 1\n",
    "\n",
    "# The most efficient way to build this matrix is to iterate through the rows of filter_2_df\n",
    "# while filling in the pre-inited matrx\n",
    "count = 0\n",
    "print(filter_2_df.shape)\n",
    "for index, row in filter_2_df.iterrows():\n",
    "    count += 1\n",
    "    cur_inchi = row[\"InChI\"]\n",
    "    cur_assay = row[\"Entrez_ID\"]\n",
    "    output_matrix[inchi_order_dict[cur_inchi], assay_order_dict[cur_assay]] = 0 if \\\n",
    "        row[\"Activity_Flag\"] == 'N' else 1\n",
    "print(count)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 166,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "In our output matrix, there are 78.11% NA, 0.60% Positive, and 21.29% Negative.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "Counter({-1.0: 4348553, 0.0: 1185283, 1.0: 33506})"
      ]
     },
     "execution_count": 166,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "count = Counter(output_matrix.reshape(-1).tolist())\n",
    "total = output_matrix.shape[0] * output_matrix.shape[1]\n",
    "print(\"In our output matrix, there are {:.2f}% NA, {:.2f}% Positive, and {:.2f}% Negative.\".format(\n",
    "    count[-1]/total*100, count[1]/total*100, count[0]/total*100))\n",
    "count"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 167,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save the output matrix along with row/column label info\n",
    "np.savez(\"./resource/output_matrix_convert.npz\", output_matrix=output_matrix,\n",
    "         compound_inchi=inchi_names, compound_broad_id=broad_id_names, assay=assays)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Build Output Matrix Ignoring Collision\n",
    "\n",
    "Thanks to Scott Wildman's help, we decide to ignore the warning and collision for now. Below is the email from Scott.\n",
    "\n",
    "> For the ‘charges’ example, it seems that the person who initially drew the structure just decided to represent the nitro group differently. This can often happen for a few different groups. Some people draw O=N=O, while others draw O=N(+)-O(-). These are really the same molecule, even if they have different identifiers, and should be corrected to the single InChI.\n",
    ">\n",
    "> The ‘stereochemistry’ example is more difficult, but I think I know the answer. For molecules with known stereochemistry, it is possible to draw the structure with or without this information, and this will result in different InChI (or different isomeric SMILES, etc.). In some database systems, molecules with unknown stereochemistry, or with mixtures of known stereochemistry, the person drawing the structure may choose to represent all possible stereochemical combinations. This is not the ideal solution, as the standard practice is to omit stereochemistry information for any combination of stereoisomers. In this case (alexidine), the stereochemistry is undefined everywhere I can find it, so I suspect it is either unknown or a mixture, and the correct thing is to treat these as a single compound InChI.\n",
    ">\n",
    "> Unfortunately, I cannot promise that all of the issues, particularly the stereochemistry cases, are correctly resolved by reduction to a single compound InChI, but I suspect this would be true for the vast majority of situations / errors you find."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "27307\n",
      "27000\n"
     ]
    }
   ],
   "source": [
    "new_inchi = load(open(\"./resource/std_inchi.json\", 'r'))\n",
    "converted_inchi = new_inchi[\"converted_inchi\"]\n",
    "original_inchi = new_inchi[\"old_inchi\"]\n",
    "\n",
    "# Get the number of matched original inchi entries (with replicate allowed)\n",
    "intersection = set(converted_inchi) & set(bone_df[\"INCHI\"])\n",
    "intersect_index_of_conv_inchi = [i for i in range(len(converted_inchi)) if converted_inchi[i] in intersection]\n",
    "intersect_original_array = np.array(original_inchi)[intersect_index_of_conv_inchi]\n",
    "intersect_original = set(intersect_original_array)\n",
    "\n",
    "print(len(intersect_original))\n",
    "print(len(set(converted_inchi) & set(bone_df[\"INCHI\"])))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ignoring the 307 collisions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2241625, 12)"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "intersect_original_array = np.array(original_inchi)[intersect_index_of_conv_inchi]\n",
    "intersect_original = set(intersect_original_array)\n",
    "\n",
    "# Construct a subset dataframe with the intersection compounds\n",
    "intersect_index = [True if i in intersect_original else False for i in df[\"InChI\"]]\n",
    "intersect_df = df[intersect_index]\n",
    "intersect_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are 47616 positive results and 2194009 negative results. 212 assays haivng both results greater or equal to 10.\n"
     ]
    }
   ],
   "source": [
    "# Filtering targets with at least 10 positive results and 10 negative results\n",
    "pos_intersect = intersect_df[intersect_df[\"Activity_Flag\"] != \"N\"]\n",
    "pos_count = Counter(pos_intersect[\"Entrez_ID\"])\n",
    "\n",
    "neg_intersect = intersect_df[intersect_df[\"Activity_Flag\"] == \"N\"]\n",
    "neg_count = Counter(neg_intersect[\"Entrez_ID\"])\n",
    "\n",
    "target_count = Counter(intersect_df[\"Entrez_ID\"])\n",
    "filter_2_targets = [t for t in target_count if (t in neg_count) and (neg_count[t] >= 10) and\n",
    "                    (t in pos_count) and (pos_count[t] >= 10)]\n",
    "\n",
    "print(\"There are {} positive results and {} negative results. {} assays haivng both results greater or equal to 10.\".\\\n",
    "      format(len(pos_intersect), len(neg_intersect), len(filter_2_targets)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1262259, 12)\n",
      "27241\n",
      "27241\n"
     ]
    }
   ],
   "source": [
    "filter_2_df = intersect_df[intersect_df[\"Entrez_ID\"].isin(set(filter_2_targets))]\n",
    "print(filter_2_df.shape)\n",
    "\n",
    "# Save the selected compound identifier\n",
    "filter_2_compounds = set(filter_2_df[\"InChI\"])\n",
    "print(len(filter_2_compounds))\n",
    "board_ids = []\n",
    "inchis = []\n",
    "conversion_dict = dict(zip(original_inchi, converted_inchi))\n",
    "\n",
    "for c in filter_2_compounds:\n",
    "    # Convert InChI v1 to standard InChI\n",
    "    std_name = conversion_dict[c]\n",
    "    board_id = bone_df.loc[bone_df[\"INCHI\"] == std_name][\"BROAD_ID\"].values[0]\n",
    "    board_ids.append(board_id)\n",
    "    \n",
    "    # We also want to collect inchis, because the order of set() is unstable\n",
    "    # We want to keep the board_ids and inchis have the same order\n",
    "    # Collect the v1 InChI here\n",
    "    inchis.append(c)\n",
    "    \n",
    "print(len(board_ids))\n",
    "dump({\"board_ids\": board_ids,\n",
    "      \"inchis\": inchis},\n",
    "     open(\"./resource/selected_compounds_collision.json\", 'w'), indent=2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1262259, 12)\n",
      "1262259\n"
     ]
    }
   ],
   "source": [
    "# The schema we use: -1: NA, 0: Neg, 1: Pos\n",
    "output_matrix = np.zeros((27241, 212)) - 1\n",
    "\n",
    "# Create the order dictionary for all selected comounds\n",
    "inchi_order_dict = {}\n",
    "inchi_names = []\n",
    "broad_id_names = []\n",
    "\n",
    "for i in range(len(inchis)):\n",
    "    inchi_order_dict[inchis[i]] = i\n",
    "    \n",
    "    inchi_names.append(inchis[i])\n",
    "    broad_id_names.append(board_ids[i])\n",
    "    \n",
    "# Create the order dictionary for all assays\n",
    "assay_order_dict = {}\n",
    "assays = []\n",
    "count = 0\n",
    "for i in set(filter_2_df[\"Entrez_ID\"]):\n",
    "    assay_order_dict[i] = count\n",
    "    assays.append(i)\n",
    "    count += 1\n",
    "\n",
    "# The most efficient way to build this matrix is to iterate through the rows of filter_2_df\n",
    "# while filling in the pre-inited matrx\n",
    "count = 0\n",
    "print(filter_2_df.shape)\n",
    "for index, row in filter_2_df.iterrows():\n",
    "    count += 1\n",
    "    cur_inchi = row[\"InChI\"]\n",
    "    cur_assay = row[\"Entrez_ID\"]\n",
    "    output_matrix[inchi_order_dict[cur_inchi], assay_order_dict[cur_assay]] = 0 if \\\n",
    "        row[\"Activity_Flag\"] == 'N' else 1\n",
    "print(count)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "In our output matrix, there are 78.14% NA, 0.61% Positive, and 21.25% Negative.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "Counter({-1.0: 4512945, 0.0: 1227153, 1.0: 34994})"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "count = Counter(output_matrix.reshape(-1).tolist())\n",
    "total = output_matrix.shape[0] * output_matrix.shape[1]\n",
    "print(\"In our output matrix, there are {:.2f}% NA, {:.2f}% Positive, and {:.2f}% Negative.\".format(\n",
    "    count[-1]/total*100, count[1]/total*100, count[0]/total*100))\n",
    "count"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save the output matrix along with row/column label info\n",
    "np.savez(\"./resource/output_matrix_convert_collision.npz\", output_matrix=output_matrix,\n",
    "         compound_inchi=inchi_names, compound_broad_id=broad_id_names, assay=assays)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.2"
  },
  "toc": {
   "nav_menu": {},
   "number_sections": false,
   "sideBar": true,
   "skip_h1_title": true,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}