positive_control.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Positive Control Compound\n",
    "\n",
    "In this notebook, we try to use existing database to find compounds which have been tested on U2OS cell-line."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## CCLE Database\n",
    "\n",
    "I will use sheet_11 of the [Excel file](https://www.nature.com/articles/nature11003#supplementary-information) to find positive compounds."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "from rdkit import Chem\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import requests"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "all_df = pd.read_csv('./resource/ccle_drug.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "24"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "u2os_index = [True if i == 'U2OS_BONE' else False for i in\n",
    "              all_df['CCLE Cell Line Name']]\n",
    "np.sum(u2os_index)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are only 24 drugs testing on U2OS on this Excel file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>CCLE Cell Line Name</th>\n",
       "      <th>Primary Cell Line Name</th>\n",
       "      <th>Compound</th>\n",
       "      <th>Target</th>\n",
       "      <th>Doses (uM)</th>\n",
       "      <th>Activity Data (median)</th>\n",
       "      <th>Activity SD</th>\n",
       "      <th>Num Data</th>\n",
       "      <th>FitType</th>\n",
       "      <th>EC50 (µM)</th>\n",
       "      <th>IC50 (µM)</th>\n",
       "      <th>Amax</th>\n",
       "      <th>ActArea</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>484</th>\n",
       "      <td>U2OS_BONE</td>\n",
       "      <td>U-2 OS</td>\n",
       "      <td>17-AAG</td>\n",
       "      <td>HSP90</td>\n",
       "      <td>.0025,.0080,.025,.080,.25,.80,2.53,8</td>\n",
       "      <td>-4.4,-13,-23,-39,-74,-91,-92,-92</td>\n",
       "      <td>16.2,7.93,.26,14.8,6.43,1.34,1.08,.64</td>\n",
       "      <td>8</td>\n",
       "      <td>Sigmoid</td>\n",
       "      <td>0.107041</td>\n",
       "      <td>0.099057</td>\n",
       "      <td>-93.582008</td>\n",
       "      <td>4.6678</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>987</th>\n",
       "      <td>U2OS_BONE</td>\n",
       "      <td>U-2 OS</td>\n",
       "      <td>AEW541</td>\n",
       "      <td>IGF1R</td>\n",
       "      <td>.0025,.0080,.025,.080,.25,.80,2.53,8</td>\n",
       "      <td>4.08,14.3,4.61,-2.5,-14,-13,-44,-78</td>\n",
       "      <td>5.12,17.2,7.62,3.22,1.63,4.39,4.94,14.3</td>\n",
       "      <td>8</td>\n",
       "      <td>Sigmoid</td>\n",
       "      <td>8.763501</td>\n",
       "      <td>3.435356</td>\n",
       "      <td>-78.528435</td>\n",
       "      <td>1.5148</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1491</th>\n",
       "      <td>U2OS_BONE</td>\n",
       "      <td>U-2 OS</td>\n",
       "      <td>AZD0530</td>\n",
       "      <td>ABL</td>\n",
       "      <td>.0025,.0080,.025,.080,.25,.80,2.53,8</td>\n",
       "      <td>-2.2,3.02,-.14,-3.8,-3.3,-23,-30,-41</td>\n",
       "      <td>7.72,14.6,15.9,.24,.33,5.58,5.49,14.1</td>\n",
       "      <td>8</td>\n",
       "      <td>Sigmoid</td>\n",
       "      <td>0.766910</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>-37.888985</td>\n",
       "      <td>1.3457</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1994</th>\n",
       "      <td>U2OS_BONE</td>\n",
       "      <td>U-2 OS</td>\n",
       "      <td>AZD6244</td>\n",
       "      <td>MEK</td>\n",
       "      <td>.0025,.0080,.025,.080,.25,.80,2.53,8</td>\n",
       "      <td>4.15,-3.2,-.014,-20,-16,-26,-18,-29</td>\n",
       "      <td>.64,21.7,8.42,18.4,1.37,11.0,.21,28.7</td>\n",
       "      <td>8</td>\n",
       "      <td>Constant</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>-16.204763</td>\n",
       "      <td>1.2003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2497</th>\n",
       "      <td>U2OS_BONE</td>\n",
       "      <td>U-2 OS</td>\n",
       "      <td>Erlotinib</td>\n",
       "      <td>EGFR</td>\n",
       "      <td>.0025,.0080,.025,.080,.25,.80,2.53,8</td>\n",
       "      <td>2.79,5.16,2.32,2.76,-8.9,-10,-5.4,-28</td>\n",
       "      <td>1.18,15.3,5.45,6.35,5.14,.31,6.00,17.9</td>\n",
       "      <td>8</td>\n",
       "      <td>Sigmoid</td>\n",
       "      <td>8.832891</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>-25.136881</td>\n",
       "      <td>0.4888</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2820</th>\n",
       "      <td>U2OS_BONE</td>\n",
       "      <td>U-2 OS</td>\n",
       "      <td>Irinotecan</td>\n",
       "      <td>TOP2</td>\n",
       "      <td>.0025,.0080,.025,.080,.25,.80,2.53,8</td>\n",
       "      <td>.75,-3.3,1.35,-13,-14,-63,-78,-90</td>\n",
       "      <td>5.23,8.88,3.48,1.28,2.34,19.3,16.8,7.24</td>\n",
       "      <td>8</td>\n",
       "      <td>Sigmoid</td>\n",
       "      <td>0.591141</td>\n",
       "      <td>0.658781</td>\n",
       "      <td>-88.524544</td>\n",
       "      <td>2.6425</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3306</th>\n",
       "      <td>U2OS_BONE</td>\n",
       "      <td>U-2 OS</td>\n",
       "      <td>L-685458</td>\n",
       "      <td>GS</td>\n",
       "      <td>.0025,.0080,.025,.080,.25,.80,2.53,8</td>\n",
       "      <td>2.36,6.79,8.80,9.54,10.7,-4.7,-35,-70</td>\n",
       "      <td>1.31,10.5,6.99,.30,.29,3.00,44.0,26.6</td>\n",
       "      <td>8</td>\n",
       "      <td>Sigmoid</td>\n",
       "      <td>1.068852</td>\n",
       "      <td>1.558048</td>\n",
       "      <td>-58.775295</td>\n",
       "      <td>1.1630</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3808</th>\n",
       "      <td>U2OS_BONE</td>\n",
       "      <td>U-2 OS</td>\n",
       "      <td>LBW242</td>\n",
       "      <td>XIAP</td>\n",
       "      <td>.0025,.0080,.025,.080,.25,.80,2.53,8</td>\n",
       "      <td>-19,-5.9,-2.0,4.67,6.56,10.5,-1.1,1.62</td>\n",
       "      <td>8.68,.37,9.34,18.6,9.57,18.0,14.3,3.54</td>\n",
       "      <td>8</td>\n",
       "      <td>Constant</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>-1.130814</td>\n",
       "      <td>0.3309</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4312</th>\n",
       "      <td>U2OS_BONE</td>\n",
       "      <td>U-2 OS</td>\n",
       "      <td>Lapatinib</td>\n",
       "      <td>EGFR</td>\n",
       "      <td>.0025,.0080,.025,.080,.25,.80,2.53,8</td>\n",
       "      <td>2.47,7.06,6.19,-8.6,-4.3,-27,-17,-23</td>\n",
       "      <td>4.85,1.92,8.03,6.40,6.62,6.04,10.5,2.70</td>\n",
       "      <td>8</td>\n",
       "      <td>Sigmoid</td>\n",
       "      <td>0.190182</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>-22.993229</td>\n",
       "      <td>0.7341</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4737</th>\n",
       "      <td>U2OS_BONE</td>\n",
       "      <td>U-2 OS</td>\n",
       "      <td>Nilotinib</td>\n",
       "      <td>ABL</td>\n",
       "      <td>.0025,.0080,.025,.080,.25,.80,2.53,8</td>\n",
       "      <td>9.76,8.24,17.3,9.59,25.8,12.2,23.8,-69</td>\n",
       "      <td>4.43,2.10,9.11,10.4,8.32,1.48,13.4,19.1</td>\n",
       "      <td>8</td>\n",
       "      <td>Sigmoid</td>\n",
       "      <td>8.163845</td>\n",
       "      <td>7.420892</td>\n",
       "      <td>-69.161781</td>\n",
       "      <td>0.6846</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5236</th>\n",
       "      <td>U2OS_BONE</td>\n",
       "      <td>U-2 OS</td>\n",
       "      <td>Nutlin-3</td>\n",
       "      <td>MDM2</td>\n",
       "      <td>.0025,.0080,.025,.080,.25,.80,2.53,8</td>\n",
       "      <td>9.03,-8.2,-4.7,-7.2,-6.3,-3.1,-8.1,-41</td>\n",
       "      <td>4.69,13.0,2.17,17.6,7.25,40.1,4.27,4.13</td>\n",
       "      <td>8</td>\n",
       "      <td>Sigmoid</td>\n",
       "      <td>5.466269</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>-41.035385</td>\n",
       "      <td>1.0122</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5740</th>\n",
       "      <td>U2OS_BONE</td>\n",
       "      <td>U-2 OS</td>\n",
       "      <td>PD-0325901</td>\n",
       "      <td>MEK</td>\n",
       "      <td>.0025,.0080,.025,.080,.25,.80,2.53,8</td>\n",
       "      <td>-1.6,-11,-7.4,-30,-12,-21,-24,-72</td>\n",
       "      <td>2.50,9.62,7.70,5.56,3.68,4.67,17.6,4.59</td>\n",
       "      <td>8</td>\n",
       "      <td>Sigmoid</td>\n",
       "      <td>8.523539</td>\n",
       "      <td>5.427963</td>\n",
       "      <td>-71.969208</td>\n",
       "      <td>1.6830</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6179</th>\n",
       "      <td>U2OS_BONE</td>\n",
       "      <td>U-2 OS</td>\n",
       "      <td>PD-0332991</td>\n",
       "      <td>CDK4</td>\n",
       "      <td>.0025,.0080,.025,.080,.25,.80,2.53,8</td>\n",
       "      <td>1.78,.092,8.20,9.20,.97,-4.6,-12,-24</td>\n",
       "      <td>3.73,5.94,16.0,20.6,8.37,2.72,12.0,14.8</td>\n",
       "      <td>8</td>\n",
       "      <td>Sigmoid</td>\n",
       "      <td>3.618547</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>-23.653048</td>\n",
       "      <td>0.3956</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6678</th>\n",
       "      <td>U2OS_BONE</td>\n",
       "      <td>U-2 OS</td>\n",
       "      <td>PF2341066</td>\n",
       "      <td>c-MET</td>\n",
       "      <td>.0025,.0080,.025,.080,.25,.80,2.53,8</td>\n",
       "      <td>-2.5,-6.5,-15,-6.7,6.10,9.61,-4.7,-52</td>\n",
       "      <td>1.57,.43,2.80,2.27,7.16,7.31,4.00,3.63</td>\n",
       "      <td>8</td>\n",
       "      <td>Sigmoid</td>\n",
       "      <td>5.499296</td>\n",
       "      <td>7.505041</td>\n",
       "      <td>-52.324730</td>\n",
       "      <td>1.0034</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7181</th>\n",
       "      <td>U2OS_BONE</td>\n",
       "      <td>U-2 OS</td>\n",
       "      <td>PHA-665752</td>\n",
       "      <td>c-MET</td>\n",
       "      <td>.0025,.0080,.025,.080,.25,.80,2.53,8</td>\n",
       "      <td>4.90,-6.1,2.23,-5.5,8.93,1.12,4.61,-15</td>\n",
       "      <td>6.89,2.47,2.56,20.0,11.3,6.51,8.30,9.55</td>\n",
       "      <td>8</td>\n",
       "      <td>Constant</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>-0.862762</td>\n",
       "      <td>0.2498</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7677</th>\n",
       "      <td>U2OS_BONE</td>\n",
       "      <td>U-2 OS</td>\n",
       "      <td>PLX4720</td>\n",
       "      <td>RAF</td>\n",
       "      <td>.0025,.0080,.025,.080,.25,.80,2.53,8</td>\n",
       "      <td>-20,-4.8,-14,-.14,-2.7,11.5,3.88,9.66</td>\n",
       "      <td>1.00,9.27,11.0,10.9,13.4,10.0,18.9,1.71</td>\n",
       "      <td>8</td>\n",
       "      <td>Linear</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>-18.725622</td>\n",
       "      <td>0.6723</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8180</th>\n",
       "      <td>U2OS_BONE</td>\n",
       "      <td>U-2 OS</td>\n",
       "      <td>Paclitaxel</td>\n",
       "      <td>TUBB1</td>\n",
       "      <td>.0025,.0080,.025,.080,.25,.80,2.53,8</td>\n",
       "      <td>-5.2,-29,-50,-73,-82,-85,-88,-89</td>\n",
       "      <td>30.1,4.66,4.25,3.03,7.45,4.73,2.73,.76</td>\n",
       "      <td>8</td>\n",
       "      <td>Sigmoid</td>\n",
       "      <td>0.031713</td>\n",
       "      <td>0.026384</td>\n",
       "      <td>-87.500412</td>\n",
       "      <td>5.1548</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8680</th>\n",
       "      <td>U2OS_BONE</td>\n",
       "      <td>U-2 OS</td>\n",
       "      <td>Panobinostat</td>\n",
       "      <td>HDAC</td>\n",
       "      <td>.0025,.0080,.025,.080,.25,.80,2.53,8</td>\n",
       "      <td>-3.9,-14,-34,-85,-93,-94,-94,-98</td>\n",
       "      <td>9.88,9.27,.78,.77,.31,.90,.37,.17</td>\n",
       "      <td>8</td>\n",
       "      <td>Sigmoid</td>\n",
       "      <td>0.035370</td>\n",
       "      <td>0.034502</td>\n",
       "      <td>-95.113258</td>\n",
       "      <td>5.1185</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9141</th>\n",
       "      <td>U2OS_BONE</td>\n",
       "      <td>U-2 OS</td>\n",
       "      <td>RAF265</td>\n",
       "      <td>RAF</td>\n",
       "      <td>.0025,.0080,.025,.080,.25,.80,2.53,8</td>\n",
       "      <td>12.8,.65,17.2,9.60,1.82,-29,-40,-58</td>\n",
       "      <td>8.11,8.39,5.99,3.73,14.2,14.4,6.96,4.70</td>\n",
       "      <td>8</td>\n",
       "      <td>Sigmoid</td>\n",
       "      <td>0.973752</td>\n",
       "      <td>3.867313</td>\n",
       "      <td>-56.534000</td>\n",
       "      <td>1.2490</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9643</th>\n",
       "      <td>U2OS_BONE</td>\n",
       "      <td>U-2 OS</td>\n",
       "      <td>Sorafenib</td>\n",
       "      <td>RTK</td>\n",
       "      <td>.0025,.0080,.025,.080,.25,.80,2.53,8</td>\n",
       "      <td>-3.8,.42,-7.4,-6.4,10.5,9.42,12.2,-31</td>\n",
       "      <td>4.66,11.5,.57,2.77,2.44,8.19,3.34,2.67</td>\n",
       "      <td>8</td>\n",
       "      <td>Sigmoid</td>\n",
       "      <td>7.162780</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>-30.477428</td>\n",
       "      <td>0.5541</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10147</th>\n",
       "      <td>U2OS_BONE</td>\n",
       "      <td>U-2 OS</td>\n",
       "      <td>TAE684</td>\n",
       "      <td>ALK</td>\n",
       "      <td>.0025,.0080,.025,.080,.25,.80,2.53,8</td>\n",
       "      <td>-14,-.082,-11,-1.5,-7.3,5.33,-19,-61</td>\n",
       "      <td>2.87,9.02,.58,4.43,5.47,4.96,14.0,6.40</td>\n",
       "      <td>8</td>\n",
       "      <td>Sigmoid</td>\n",
       "      <td>3.161715</td>\n",
       "      <td>4.141691</td>\n",
       "      <td>-61.122650</td>\n",
       "      <td>1.3240</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10651</th>\n",
       "      <td>U2OS_BONE</td>\n",
       "      <td>U-2 OS</td>\n",
       "      <td>TKI258</td>\n",
       "      <td>FGFR</td>\n",
       "      <td>.0025,.0080,.025,.080,.25,.80,2.53,8</td>\n",
       "      <td>11.9,2.83,17.1,3.43,10.8,1.36,-25,-60</td>\n",
       "      <td>13.9,1.10,7.02,6.51,7.19,2.34,5.14,.082</td>\n",
       "      <td>8</td>\n",
       "      <td>Sigmoid</td>\n",
       "      <td>3.098287</td>\n",
       "      <td>5.236875</td>\n",
       "      <td>-60.296680</td>\n",
       "      <td>0.8411</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11155</th>\n",
       "      <td>U2OS_BONE</td>\n",
       "      <td>U-2 OS</td>\n",
       "      <td>Topotecan</td>\n",
       "      <td>TOP2</td>\n",
       "      <td>.0025,.0080,.025,.080,.25,.80,2.53,8</td>\n",
       "      <td>-2.5,-7.1,-6.7,-20,-.32,-30,-75,-89</td>\n",
       "      <td>5.73,.63,5.91,4.37,4.06,2.76,13.9,.82</td>\n",
       "      <td>8</td>\n",
       "      <td>Sigmoid</td>\n",
       "      <td>1.209096</td>\n",
       "      <td>1.269840</td>\n",
       "      <td>-88.289536</td>\n",
       "      <td>2.6347</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11651</th>\n",
       "      <td>U2OS_BONE</td>\n",
       "      <td>U-2 OS</td>\n",
       "      <td>ZD-6474</td>\n",
       "      <td>EGFR</td>\n",
       "      <td>.0025,.0080,.025,.080,.25,.80,2.53,8</td>\n",
       "      <td>-13,-1.7,-13,-4.0,-5.6,-21,-36,-63</td>\n",
       "      <td>.026,3.80,10.4,12.9,6.15,1.66,3.83,5.49</td>\n",
       "      <td>8</td>\n",
       "      <td>Sigmoid</td>\n",
       "      <td>4.034433</td>\n",
       "      <td>4.457736</td>\n",
       "      <td>-62.986660</td>\n",
       "      <td>2.1597</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      CCLE Cell Line Name Primary Cell Line Name      Compound Target  \\\n",
       "484             U2OS_BONE                 U-2 OS        17-AAG  HSP90   \n",
       "987             U2OS_BONE                 U-2 OS        AEW541  IGF1R   \n",
       "1491            U2OS_BONE                 U-2 OS       AZD0530    ABL   \n",
       "1994            U2OS_BONE                 U-2 OS       AZD6244    MEK   \n",
       "2497            U2OS_BONE                 U-2 OS     Erlotinib   EGFR   \n",
       "2820            U2OS_BONE                 U-2 OS    Irinotecan   TOP2   \n",
       "3306            U2OS_BONE                 U-2 OS      L-685458     GS   \n",
       "3808            U2OS_BONE                 U-2 OS        LBW242   XIAP   \n",
       "4312            U2OS_BONE                 U-2 OS     Lapatinib   EGFR   \n",
       "4737            U2OS_BONE                 U-2 OS     Nilotinib    ABL   \n",
       "5236            U2OS_BONE                 U-2 OS      Nutlin-3   MDM2   \n",
       "5740            U2OS_BONE                 U-2 OS    PD-0325901    MEK   \n",
       "6179            U2OS_BONE                 U-2 OS    PD-0332991   CDK4   \n",
       "6678            U2OS_BONE                 U-2 OS     PF2341066  c-MET   \n",
       "7181            U2OS_BONE                 U-2 OS    PHA-665752  c-MET   \n",
       "7677            U2OS_BONE                 U-2 OS       PLX4720    RAF   \n",
       "8180            U2OS_BONE                 U-2 OS    Paclitaxel  TUBB1   \n",
       "8680            U2OS_BONE                 U-2 OS  Panobinostat   HDAC   \n",
       "9141            U2OS_BONE                 U-2 OS        RAF265    RAF   \n",
       "9643            U2OS_BONE                 U-2 OS     Sorafenib    RTK   \n",
       "10147           U2OS_BONE                 U-2 OS        TAE684    ALK   \n",
       "10651           U2OS_BONE                 U-2 OS        TKI258   FGFR   \n",
       "11155           U2OS_BONE                 U-2 OS     Topotecan   TOP2   \n",
       "11651           U2OS_BONE                 U-2 OS       ZD-6474   EGFR   \n",
       "\n",
       "                                 Doses (uM)  \\\n",
       "484    .0025,.0080,.025,.080,.25,.80,2.53,8   \n",
       "987    .0025,.0080,.025,.080,.25,.80,2.53,8   \n",
       "1491   .0025,.0080,.025,.080,.25,.80,2.53,8   \n",
       "1994   .0025,.0080,.025,.080,.25,.80,2.53,8   \n",
       "2497   .0025,.0080,.025,.080,.25,.80,2.53,8   \n",
       "2820   .0025,.0080,.025,.080,.25,.80,2.53,8   \n",
       "3306   .0025,.0080,.025,.080,.25,.80,2.53,8   \n",
       "3808   .0025,.0080,.025,.080,.25,.80,2.53,8   \n",
       "4312   .0025,.0080,.025,.080,.25,.80,2.53,8   \n",
       "4737   .0025,.0080,.025,.080,.25,.80,2.53,8   \n",
       "5236   .0025,.0080,.025,.080,.25,.80,2.53,8   \n",
       "5740   .0025,.0080,.025,.080,.25,.80,2.53,8   \n",
       "6179   .0025,.0080,.025,.080,.25,.80,2.53,8   \n",
       "6678   .0025,.0080,.025,.080,.25,.80,2.53,8   \n",
       "7181   .0025,.0080,.025,.080,.25,.80,2.53,8   \n",
       "7677   .0025,.0080,.025,.080,.25,.80,2.53,8   \n",
       "8180   .0025,.0080,.025,.080,.25,.80,2.53,8   \n",
       "8680   .0025,.0080,.025,.080,.25,.80,2.53,8   \n",
       "9141   .0025,.0080,.025,.080,.25,.80,2.53,8   \n",
       "9643   .0025,.0080,.025,.080,.25,.80,2.53,8   \n",
       "10147  .0025,.0080,.025,.080,.25,.80,2.53,8   \n",
       "10651  .0025,.0080,.025,.080,.25,.80,2.53,8   \n",
       "11155  .0025,.0080,.025,.080,.25,.80,2.53,8   \n",
       "11651  .0025,.0080,.025,.080,.25,.80,2.53,8   \n",
       "\n",
       "                       Activity Data (median)  \\\n",
       "484          -4.4,-13,-23,-39,-74,-91,-92,-92   \n",
       "987       4.08,14.3,4.61,-2.5,-14,-13,-44,-78   \n",
       "1491     -2.2,3.02,-.14,-3.8,-3.3,-23,-30,-41   \n",
       "1994      4.15,-3.2,-.014,-20,-16,-26,-18,-29   \n",
       "2497    2.79,5.16,2.32,2.76,-8.9,-10,-5.4,-28   \n",
       "2820        .75,-3.3,1.35,-13,-14,-63,-78,-90   \n",
       "3306    2.36,6.79,8.80,9.54,10.7,-4.7,-35,-70   \n",
       "3808   -19,-5.9,-2.0,4.67,6.56,10.5,-1.1,1.62   \n",
       "4312     2.47,7.06,6.19,-8.6,-4.3,-27,-17,-23   \n",
       "4737   9.76,8.24,17.3,9.59,25.8,12.2,23.8,-69   \n",
       "5236   9.03,-8.2,-4.7,-7.2,-6.3,-3.1,-8.1,-41   \n",
       "5740        -1.6,-11,-7.4,-30,-12,-21,-24,-72   \n",
       "6179     1.78,.092,8.20,9.20,.97,-4.6,-12,-24   \n",
       "6678    -2.5,-6.5,-15,-6.7,6.10,9.61,-4.7,-52   \n",
       "7181   4.90,-6.1,2.23,-5.5,8.93,1.12,4.61,-15   \n",
       "7677    -20,-4.8,-14,-.14,-2.7,11.5,3.88,9.66   \n",
       "8180         -5.2,-29,-50,-73,-82,-85,-88,-89   \n",
       "8680         -3.9,-14,-34,-85,-93,-94,-94,-98   \n",
       "9141      12.8,.65,17.2,9.60,1.82,-29,-40,-58   \n",
       "9643    -3.8,.42,-7.4,-6.4,10.5,9.42,12.2,-31   \n",
       "10147    -14,-.082,-11,-1.5,-7.3,5.33,-19,-61   \n",
       "10651   11.9,2.83,17.1,3.43,10.8,1.36,-25,-60   \n",
       "11155     -2.5,-7.1,-6.7,-20,-.32,-30,-75,-89   \n",
       "11651      -13,-1.7,-13,-4.0,-5.6,-21,-36,-63   \n",
       "\n",
       "                                   Activity SD  Num Data   FitType  EC50 (µM)  \\\n",
       "484      16.2,7.93,.26,14.8,6.43,1.34,1.08,.64         8   Sigmoid   0.107041   \n",
       "987    5.12,17.2,7.62,3.22,1.63,4.39,4.94,14.3         8   Sigmoid   8.763501   \n",
       "1491     7.72,14.6,15.9,.24,.33,5.58,5.49,14.1         8   Sigmoid   0.766910   \n",
       "1994     .64,21.7,8.42,18.4,1.37,11.0,.21,28.7         8  Constant        NaN   \n",
       "2497    1.18,15.3,5.45,6.35,5.14,.31,6.00,17.9         8   Sigmoid   8.832891   \n",
       "2820   5.23,8.88,3.48,1.28,2.34,19.3,16.8,7.24         8   Sigmoid   0.591141   \n",
       "3306     1.31,10.5,6.99,.30,.29,3.00,44.0,26.6         8   Sigmoid   1.068852   \n",
       "3808    8.68,.37,9.34,18.6,9.57,18.0,14.3,3.54         8  Constant        NaN   \n",
       "4312   4.85,1.92,8.03,6.40,6.62,6.04,10.5,2.70         8   Sigmoid   0.190182   \n",
       "4737   4.43,2.10,9.11,10.4,8.32,1.48,13.4,19.1         8   Sigmoid   8.163845   \n",
       "5236   4.69,13.0,2.17,17.6,7.25,40.1,4.27,4.13         8   Sigmoid   5.466269   \n",
       "5740   2.50,9.62,7.70,5.56,3.68,4.67,17.6,4.59         8   Sigmoid   8.523539   \n",
       "6179   3.73,5.94,16.0,20.6,8.37,2.72,12.0,14.8         8   Sigmoid   3.618547   \n",
       "6678    1.57,.43,2.80,2.27,7.16,7.31,4.00,3.63         8   Sigmoid   5.499296   \n",
       "7181   6.89,2.47,2.56,20.0,11.3,6.51,8.30,9.55         8  Constant        NaN   \n",
       "7677   1.00,9.27,11.0,10.9,13.4,10.0,18.9,1.71         8    Linear        NaN   \n",
       "8180    30.1,4.66,4.25,3.03,7.45,4.73,2.73,.76         8   Sigmoid   0.031713   \n",
       "8680         9.88,9.27,.78,.77,.31,.90,.37,.17         8   Sigmoid   0.035370   \n",
       "9141   8.11,8.39,5.99,3.73,14.2,14.4,6.96,4.70         8   Sigmoid   0.973752   \n",
       "9643    4.66,11.5,.57,2.77,2.44,8.19,3.34,2.67         8   Sigmoid   7.162780   \n",
       "10147   2.87,9.02,.58,4.43,5.47,4.96,14.0,6.40         8   Sigmoid   3.161715   \n",
       "10651  13.9,1.10,7.02,6.51,7.19,2.34,5.14,.082         8   Sigmoid   3.098287   \n",
       "11155    5.73,.63,5.91,4.37,4.06,2.76,13.9,.82         8   Sigmoid   1.209096   \n",
       "11651  .026,3.80,10.4,12.9,6.15,1.66,3.83,5.49         8   Sigmoid   4.034433   \n",
       "\n",
       "       IC50 (µM)       Amax  ActArea  \n",
       "484     0.099057 -93.582008   4.6678  \n",
       "987     3.435356 -78.528435   1.5148  \n",
       "1491    8.000000 -37.888985   1.3457  \n",
       "1994    8.000000 -16.204763   1.2003  \n",
       "2497    8.000000 -25.136881   0.4888  \n",
       "2820    0.658781 -88.524544   2.6425  \n",
       "3306    1.558048 -58.775295   1.1630  \n",
       "3808    8.000000  -1.130814   0.3309  \n",
       "4312    8.000000 -22.993229   0.7341  \n",
       "4737    7.420892 -69.161781   0.6846  \n",
       "5236    8.000000 -41.035385   1.0122  \n",
       "5740    5.427963 -71.969208   1.6830  \n",
       "6179    8.000000 -23.653048   0.3956  \n",
       "6678    7.505041 -52.324730   1.0034  \n",
       "7181    8.000000  -0.862762   0.2498  \n",
       "7677    8.000000 -18.725622   0.6723  \n",
       "8180    0.026384 -87.500412   5.1548  \n",
       "8680    0.034502 -95.113258   5.1185  \n",
       "9141    3.867313 -56.534000   1.2490  \n",
       "9643    8.000000 -30.477428   0.5541  \n",
       "10147   4.141691 -61.122650   1.3240  \n",
       "10651   5.236875 -60.296680   0.8411  \n",
       "11155   1.269840 -88.289536   2.6347  \n",
       "11651   4.457736 -62.986660   2.1597  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ccle_overlapping_df = all_df.iloc[u2os_index,:]\n",
    "ccle_overlapping_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Try to use clue.io API to find Broad ID for these compounds. Unfortunately, the clue.io API doesn't support use compound synonym to find their `pert_iname` (drug name). Instead, I will use SMILEs to match these drugs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# c1c655d96dfc7a422a2f2025bf290261"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Sanger Database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(196, 5)\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>ID</th>\n",
       "      <th>Drug Name</th>\n",
       "      <th>Targets</th>\n",
       "      <th>Z Score</th>\n",
       "      <th>Count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1133</td>\n",
       "      <td>Serdemetan</td>\n",
       "      <td>MDM2</td>\n",
       "      <td>-1.498650</td>\n",
       "      <td>196</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>154</td>\n",
       "      <td>CHIR-99021</td>\n",
       "      <td>GSK3A, GSK3B</td>\n",
       "      <td>-1.485962</td>\n",
       "      <td>196</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>179</td>\n",
       "      <td>5-Fluorouracil</td>\n",
       "      <td>Antimetabolite (DNA &amp; RNA)</td>\n",
       "      <td>-1.216920</td>\n",
       "      <td>196</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1024</td>\n",
       "      <td>Lestaurtinib</td>\n",
       "      <td>FLT3, JAK2, NTRK1, NTRK2, NTRK3</td>\n",
       "      <td>-1.152742</td>\n",
       "      <td>196</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1175</td>\n",
       "      <td>Rucaparib</td>\n",
       "      <td>PARP1, PARP2</td>\n",
       "      <td>-1.142966</td>\n",
       "      <td>196</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     ID       Drug Name                          Targets   Z Score  Count\n",
       "0  1133      Serdemetan                             MDM2 -1.498650    196\n",
       "1   154      CHIR-99021                     GSK3A, GSK3B -1.485962    196\n",
       "2   179  5-Fluorouracil       Antimetabolite (DNA & RNA) -1.216920    196\n",
       "3  1024    Lestaurtinib  FLT3, JAK2, NTRK1, NTRK2, NTRK3 -1.152742    196\n",
       "4  1175       Rucaparib                     PARP1, PARP2 -1.142966    196"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv('./resource/sanger_db.csv')\n",
    "print(df.shape)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Use pubchem API to convert drug name into canonical smiles\n",
    "smiles_list = ['' for i in range(df.shape[0])]\n",
    "get_url = (\"https://pubchem.ncbi.nlm.nih.gov/rest/pug/compound/name/\"\n",
    "           \"{}/property/CanonicalSMILES/TXT\")\n",
    "\n",
    "for i, r in df.iterrows():\n",
    "    cpd_name = r['Drug Name']\n",
    "    r = requests.get(get_url.format(cpd_name))\n",
    "    smiles = r.text.encode(\"utf-8\").replace('\\n', '')\n",
    "    smiles_list[i] = smiles"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Find cpds that pubchem fails to find smiles\n",
    "failed_index = []\n",
    "for i in range(len(smiles_list)):\n",
    "    if '404' in smiles_list[i]:\n",
    "        failed_index.append(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "QL-XII-61\n",
      "Bleomycin (50 uM)CC1=C(N=C(N=C1N)C(CC(=O)N)NCC(C(=O)N)N)C(=O)NC(C(C2=CN=CN2)OC3C(C(C(C(O3)CO)O)O)OC4C(C(C(C(O4)CO)O)OC(=O)N)O)C(=O)NC(C)C(C(C)C(=O)NC(C(C)O)C(=O)NCCC5=NC(=CS5)C6=NC(=CS6)C(=O)NCCC[S+](C)C)O\n",
      "HG-5-113-01CC(C)S(=O)(=O)C1=CC=CC=C1NC2=CC(=NC3=C2C=CN3)NC4=NC=C(C=C4)C(=O)NC\n",
      "Cetuximab\n",
      "Cisplatin\n",
      "XMD15-27\n",
      "HG-5-88-01CC(C)S(=O)(=O)C1=CC=CC=C1NC2=NC(=NC(=C2Cl)N)NC3=C(C=C(C=C3)N4CCC(CC4)C(=O)N)OC\n",
      "ZG-10O=C(C1=CC=C(NC(/C=C/CN(C)C)=O)C=C1)NC2=CC(NC3=NC(C4=CC=CN=C4)=CC=N3)=CC=C2\n",
      "rTRAIL\n",
      "ICL1100013\n",
      "XMD14-99CN1CCN(CC1)C2=CC(=C(C=C2)NC3=NC(=C(S3)C(=O)C4=C(C=CC=C4Cl)Cl)N)OC\n",
      "VNLG/124\n",
      "XMD13-2O=C(C1CC1)NC2=NNC3=CC(C4=CC(C(NC5CC5)=O)=CC=C4)=CC=C32\n",
      "JQ12\n",
      "QL-VIII-58\n",
      "TL-2-105\n",
      "QL-XI-92CC1=C(C=C(C=C1)C(=O)NC2=CC(=CC(=C2)C(F)(F)F)NC(=O)C=C)NC(=O)C3=CC=NO3\n",
      "XMD11-85hO=C1CCN(C2CCCC2)C3=NC(NC4=CC=C(C(NC5CCN(C)CC5)=O)C=C4OC)=NC=C3N1C(C)C\n",
      "KIN001-270CC1=C(C=C(C=C1)NC2=NC=NC(=C2)C3=CC(=CC=C3)N4C(=O)C5=CC=CC=C5C4=O)NS(=O)(=O)C\n",
      "KIN001-236\n",
      "JW-7-24-1COC1=CC(=CC(=C1)C2=CC3=C4C(=CN=C3C=C2)C=CC(=O)N4C5=CC(=C(C=C5)N6CCNCC6)C(F)(F)F)OC\n",
      "KIN001-260C1CC1COC\\2=CC=CC(=O)/C2=C\\3/C=C(C(=C(N3)N)C#N)C4CCNCC4\n",
      "SB52334\n",
      "TL-1-85\n",
      "NPK76-II-72-1CC1CC(=O)N(C2=CN=C(N=C2N1C3CCCC3)NC4=C(C=C(C=C4)C(=O)NC5CCN(CC5)C)OC)C\n",
      "Genentech Cpd 10\n",
      "THZ-2-49\n",
      "MPS-1-IN-1CC(C)S(=O)(=O)C1=CC=CC=C1NC2=CC(=NC3=C2C=CN3)NC4=C(C=C(C=C4)N5CCC(CC5)O)OC\n",
      "QL-XII-47CN1C=C(C=N1)C2=CC3=C4C(=CN=C3C=C2)C=CC(=O)N4C5=CC6=C(CCN6C(=O)C=C)C=C5\n",
      "WZ3105CC(C)N1C=NC2=C1N=C(N=C2NC3=CC(=CC=C3)NC(=O)C=C)NC4CCC(CC4)N(C)C\n",
      "QL-X-138CC1=C(C=C(C=C1)N2C(=O)C=CC3=CN=C4C=CC(=CC4=C32)C5=CNN=C5)NC(=O)C=C\n",
      "KIN001-244C1=CC=C(C=C1)[C@H](COC2=CC3=C(C=C2)NC(=O)N3)NC(=O)C4=CC=CN(C4=O)CC5=CC(=C(C=C5)F)F\n",
      "KIN001-266CCN1CCC(CC1)N2C=C(N=N2)CNC3=CC(=C4C(=C3)C(=C(C=N4)C#N)NC5=CC(=C(C=C5)F)Cl)Cl\n",
      "THZ-2-102-1\n"
     ]
    }
   ],
   "source": [
    "# Manually get the SMILES for those cpds\n",
    "for f in failed_index:\n",
    "    smiles_list[f] = raw_input(df['Drug Name'][f])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "16"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len([i for i in smiles_list if i == ''])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are still 16 compounds that I couldn't find SMILES.\n",
    "\n",
    "Since some SMILES come from Harvard's LINCS library, I will re-produce the canonical SMILES for all SMILES."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CN1C=C(C2=CC=CC=C21)C=C3C4=C(C=CC=N4)NC3=OCN1C=C(C2=CC=CC=C21)C=C3C4=C(C=CC=N4)NC3=O.Cl\n"
     ]
    }
   ],
   "source": [
    "canonical_smiles_list = []\n",
    "\n",
    "for s in smiles_list:\n",
    "    if s == '':\n",
    "        canonical_smiles_list.append(s)\n",
    "    else:\n",
    "        molecule = Chem.MolFromSmiles(s)\n",
    "        if molecule is None:\n",
    "            print(s)\n",
    "            canonical_smiles_list.append('')\n",
    "            continue\n",
    "        canonical_smiles = Chem.MolToSmiles(molecule)\n",
    "        canonical_smiles_list.append(canonical_smiles)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "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": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Find the intersect with cpds used in our image dataset\n",
    "image_df = pd.read_csv('./data/test/meta_data/chemical_annotations_inchi.csv')\n",
    "image_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [],
   "source": [
    "image_smiles = image_df['CPD_CANONICAL_SMILES']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'C=CCc1cccc(C=NNC(=O)CN2CCN(Cc3ccccc3)CC2)c1O',\n",
       " 'CCC(=C(c1ccccc1)c1ccc(OCCN(C)C)cc1)c1ccccc1',\n",
       " 'CCc1cc(-c2n[nH]c(C)c2-c2ccc3c(c2)OCCO3)c(O)cc1O',\n",
       " 'CN(Cc1cnc2nc(N)nc(N)c2n1)c1ccc(C(=O)NC(CCC(=O)O)C(=O)O)cc1',\n",
       " 'Cn1cc(C2=C(c3ccc(Cl)cc3Cl)C(=O)NC2=O)c2ccccc21',\n",
       " 'NC(=O)Nc1sc(-c2ccc(F)cc2)cc1C(N)=O',\n",
       " 'O=S(=O)(c1ccccc1)N(CC(F)(F)F)c1ccc(C(O)(C(F)(F)F)C(F)(F)F)cc1'}"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "overlapping_1 = set(canonical_smiles_list).intersection(image_smiles)\n",
    "overlapping_1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are only 7 overlapping compounds. We can try to intersect using InChi instead of SMILES."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Use pubchem API to convert drug name into canonical smiles\n",
    "inchi_list = ['' for i in range(df.shape[0])]\n",
    "get_url = (\"https://pubchem.ncbi.nlm.nih.gov/rest/pug/compound/name/\"\n",
    "           \"{}/property/InChI/TXT\")\n",
    "\n",
    "for i, r in df.iterrows():\n",
    "    cpd_name = r['Drug Name']\n",
    "    r = requests.get(get_url.format(cpd_name))\n",
    "    inchi = r.text.encode(\"utf-8\").replace('\\n', '')\n",
    "    inchi_list[i] = inchi"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "QL-XII-61: \n",
      "Bleomycin (50 uM): InChI=1S/C55H83N17O21S3/c1-20-33(69-46(72-44(20)58)25(12-31(57)76)64-13-24(56)45(59)82)50(86)71-35(41(26-14-61-19-65-26)91-54-43(39(80)37(78)29(15-73)90-54)92-53-40(81)42(93-55(60)88)38(79)30(16-74)89-53)51(87)66-22(3)36(77)21(2)47(83)70-34(23(4)75)49(85)63-10-8-32-67-28(18-94-32)52-68-27(17-95-52)48(84)62-9-7-11-96(5)6/h14,17-19,21-25,29-30,34-43,53-54,64,73-75,77-81H,7-13,15-16,56H2,1-6H3,(H13-,57,58,59,60,61,62,63,65,66,69,70,71,72,76,82,83,84,85,86,87,88)/p+1/t21-,22+,23+,24-,25-,29-,30+,34-,35-,36-,37+,38+,39-,40-,41-,42-,43-,53+,54-/m0/s1\n",
      "HG-5-113-01: InChI=1S/C23H24N6O3S/c1-14(2)33(31,32)19-7-5-4-6-17(19)27-18-12-21(29-22-16(18)10-11-25-22)28-20-9-8-15(13-26-20)23(30)24-3/h4-14H,1-3H3,(H,24,30)(H3,25,26,27,28,29)\n",
      "Cetuximab: \n",
      "Cisplatin: \n",
      "XMD15-27: \n",
      "HG-5-88-01: InChI=1S/C26H32ClN7O4S/c1-15(2)39(36,37)21-7-5-4-6-19(21)30-25-22(27)23(28)32-26(33-25)31-18-9-8-17(14-20(18)38-3)34-12-10-16(11-13-34)24(29)35/h4-9,14-16H,10-13H2,1-3H3,(H2,29,35)(H4,28,30,31,32,33)\n",
      "ZG-10: nChI=1S/C28H27N7O2/c1-35(2)17-5-9-26(36)31-22-12-10-20(11-13-22)27(37)32-23-7-3-8-24(18-23)33-28-30-16-14-25(34-28)21-6-4-15-29-19-21/h3-16,18-19H,17H2,1-2H3,(H,31,36)(H,32,37)(H,30,33,34)/b9-5+\n",
      "rTRAIL: \n",
      "ICL1100013: \n",
      "XMD14-99: InChI=1S/C22H23Cl2N5O2S/c1-28-8-10-29(11-9-28)13-6-7-16(17(12-13)31-2)26-22-27-21(25)20(32-22)19(30)18-14(23)4-3-5-15(18)24/h3-7,12H,8-11,25H2,1-2H3,(H,26,27)\n",
      "VNLG/124: \n",
      "XMD13-2: InChI=1S/C21H20N4O2/c26-20(12-4-5-12)23-19-17-9-6-14(11-18(17)24-25-19)13-2-1-3-15(10-13)21(27)22-16-7-8-16/h1-3,6,9-12,16H,4-5,7-8H2,(H,22,27)(H2,23,24,25,26)\n",
      "JQ12: \n",
      "QL-VIII-58: \n",
      "TL-2-105: \n",
      "QL-XI-92: InChI=1S/C22H17F3N4O4/c1-3-19(30)27-15-9-14(22(23,24)25)10-16(11-15)28-20(31)13-5-4-12(2)17(8-13)29-21(32)18-6-7-26-33-18/h3-11H,1H2,2H3,(H,27,30)(H,28,31)(H,29,32)\n",
      "XMD11-85h: InChI=1S/C29H41N7O3/c1-19(2)36-24-18-30-29(33-27(24)35(16-13-26(36)37)22-7-5-6-8-22)32-23-10-9-20(17-25(23)39-4)28(38)31-21-11-14-34(3)15-12-21/h9-10,17-19,21-22H,5-8,11-16H2,1-4H3,(H,31,38)(H,30,32,33)\n",
      "KIN001-270: InChI=1S/C26H21N5O4S/c1-16-10-11-18(13-22(16)30-36(2,34)35)29-24-14-23(27-15-28-24)17-6-5-7-19(12-17)31-25(32)20-8-3-4-9-21(20)26(31)33/h3-15,30H,1-2H3,(H,27,28,29)\n",
      "KIN001-236: \n",
      "JW-7-24-1: InChI=1S/C31H27F3N4O3/c1-40-23-13-21(14-24(17-23)41-2)19-3-6-27-25(15-19)30-20(18-36-27)4-8-29(39)38(30)22-5-7-28(26(16-22)31(32,33)34)37-11-9-35-10-12-37/h3-8,13-18,35H,9-12H2,1-2H3\n",
      "KIN001-260: InChI=1S/C21H24N4O2/c22-11-16-15(14-6-8-24-9-7-14)10-17(25-21(16)23)20-18(26)2-1-3-19(20)27-12-13-4-5-13/h1-3,10,13-14,24-25H,4-9,12,23H2/b20-17+\n",
      "SB52334: \n",
      "TL-1-85: \n",
      "NPK76-II-72-1: InChI=1S/C28H39N7O3/c1-18-15-25(36)34(3)23-17-29-28(32-26(23)35(18)21-7-5-6-8-21)31-22-10-9-19(16-24(22)38-4)27(37)30-20-11-13-33(2)14-12-20/h9-10,16-18,20-21H,5-8,11-15H2,1-4H3,(H,30,37)(H,29,31,32)\n",
      "Genentech Cpd 10: \n",
      "THZ-2-49: \n",
      "MPS-1-IN-1: InChI=1S/C28H33N5O4S/c1-18(2)38(35,36)26-7-5-4-6-23(26)30-24-17-27(32-28-21(24)10-13-29-28)31-22-9-8-19(16-25(22)37-3)33-14-11-20(34)12-15-33/h4-10,13,16-18,20,34H,11-12,14-15H2,1-3H3,(H3,29,30,31,32)\n",
      "QL-XII-47: InChI=1S/C27H21N5O2/c1-3-25(33)31-11-10-17-4-7-21(13-24(17)31)32-26(34)9-6-19-14-28-23-8-5-18(12-22(23)27(19)32)20-15-29-30(2)16-20/h3-9,12-16H,1,10-11H2,2H3\n",
      "WZ3105: InChI=1S/C25H34N8O/c1-6-21(34)27-18-8-7-9-19(14-18)28-23-22-24(33(15-26-22)16(2)3)31-25(30-23)29-17-10-12-20(13-11-17)32(4)5/h6-9,14-17,20H,1,10-13H2,2-5H3,(H,27,34)(H2,28,29,30,31)\n",
      "QL-X-138: InChI=1S/C25H19N5O2/c1-3-23(31)29-22-11-19(7-4-15(22)2)30-24(32)9-6-17-12-26-21-8-5-16(10-20(21)25(17)30)18-13-27-28-14-18/h3-14H,1H2,2H3,(H,27,28)(H,29,31)\n",
      "KIN001-244: InChI=1S/C28H22F2N4O4/c29-21-10-8-17(13-22(21)30)15-34-12-4-7-20(27(34)36)26(35)31-25(18-5-2-1-3-6-18)16-38-19-9-11-23-24(14-19)33-28(37)32-23/h1-14,25H,15-16H2,(H,31,35)(H2,32,33,37)/t25-/m0/s1\n",
      "KIN001-266: InChI=1S/C26H25Cl2FN8/c1-2-36-7-5-20(6-8-36)37-15-19(34-35-37)14-31-18-9-21-25(33-17-3-4-24(29)22(27)10-17)16(12-30)13-32-26(21)23(28)11-18/h3-4,9-11,13,15,20,31H,2,5-8,14H2,1H3,(H,32,33)\n",
      "THZ-2-102-1: \n"
     ]
    }
   ],
   "source": [
    "# Manually get the SMILES for those cpds\n",
    "for f in failed_index:\n",
    "    inchi_list[f] = raw_input(\"{}: \".format(df['Drug Name'][f]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Failed to read inchi\n",
      "Failed to read inchi\n",
      "Failed to read inchi\n",
      "Failed to read inchi\n",
      "Failed to read inchi\n",
      "Failed to read inchi\n",
      "Failed to read inchi\n",
      "Failed to read inchi\n",
      "Failed to read inchi\n",
      "Failed to read inchi\n"
     ]
    }
   ],
   "source": [
    "# Convert inchi again\n",
    "converted_inchi_list = []\n",
    "\n",
    "for i in inchi_list:\n",
    "    if i == '':\n",
    "        converted_inchi_list.append(i)\n",
    "    else:\n",
    "        molecule = Chem.MolFromInchi(i)\n",
    "        if molecule is None:\n",
    "            print('Failed to read inchi')\n",
    "            converted_inchi_list.append(i)\n",
    "            continue\n",
    "        converted_inchi = Chem.MolToInchi(molecule)\n",
    "        converted_inchi_list.append(converted_inchi)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'InChI=1S/C12H10FN3O2S/c13-7-3-1-6(2-4-7)9-5-8(10(14)17)11(19-9)16-12(15)18/h1-5H,(H2,14,17)(H3,15,16,18)',\n",
       " 'InChI=1S/C15H14N2O4S/c18-15(16-19)10-9-12-5-4-8-14(11-12)22(20,21)17-13-6-2-1-3-7-13/h1-11,17,19H,(H,16,18)/b10-9+',\n",
       " 'InChI=1S/C17H12F9NO3S/c18-14(19,20)10-27(31(29,30)13-4-2-1-3-5-13)12-8-6-11(7-9-12)15(28,16(21,22)23)17(24,25)26/h1-9,28H,10H2',\n",
       " 'InChI=1S/C17H19NO5/c1-21-13-10-12(11-14(22-2)17(13)23-3)7-8-16(20)18-9-5-4-6-15(18)19/h4,6-8,10-11H,5,9H2,1-3H3/b8-7+',\n",
       " 'InChI=1S/C19H12Cl2N2O2/c1-23-9-13(11-4-2-3-5-15(11)23)17-16(18(24)22-19(17)25)12-7-6-10(20)8-14(12)21/h2-9H,1H3,(H,22,24,25)',\n",
       " 'InChI=1S/C20H20N2O4/c1-3-12-8-14(16(24)10-15(12)23)20-19(11(2)21-22-20)13-4-5-17-18(9-13)26-7-6-25-17/h4-5,8-10,23-24H,3,6-7H2,1-2H3,(H,21,22)',\n",
       " 'InChI=1S/C26H29NO/c1-4-25(21-11-7-5-8-12-21)26(22-13-9-6-10-14-22)23-15-17-24(18-16-23)28-20-19-27(2)3/h5-18H,4,19-20H2,1-3H3/b26-25-',\n",
       " 'InChI=1S/C27H29NO11/c1-10-22(31)13(28)6-17(38-10)39-15-8-27(36,16(30)9-29)7-12-19(15)26(35)21-20(24(12)33)23(32)11-4-3-5-14(37-2)18(11)25(21)34/h3-5,10,13,15,17,22,29,31,33,35-36H,6-9,28H2,1-2H3/t10-,13-,15-,17-,22+,27-/m0/s1',\n",
       " 'InChI=1S/C29H32O13/c1-11-36-9-20-27(40-11)24(31)25(32)29(41-20)42-26-14-7-17-16(38-10-39-17)6-13(14)21(22-15(26)8-37-28(22)33)12-4-18(34-2)23(30)19(5-12)35-3/h4-7,11,15,20-22,24-27,29-32H,8-10H2,1-3H3/t11-,15+,20-,21-,22+,24-,25-,26-,27-,29+/m1/s1',\n",
       " 'InChI=1S/C34H50O12/c1-9-12-13-14-15-17-24(37)43-28-26-25(20(5)27(28)44-30(38)19(4)11-3)29-34(41,33(8,40)31(39)45-29)22(42-23(36)16-10-2)18-32(26,7)46-21(6)35/h11,22,26-29,40-41H,9-10,12-18H2,1-8H3/b19-11-/t22-,26+,27-,28-,29-,32-,33+,34+/m0/s1'}"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Find the intersect with cpds used in our image dataset\n",
    "overlapping_inchi = set(converted_inchi_list).intersection(set(image_df['INCHI']))\n",
    "overlapping_inchi"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here we have 11 overlapping compounds."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[7, 20, 26, 29, 69, 78, 133, 172, 182, 186]"
      ]
     },
     "execution_count": 96,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Find associated scores for these inchi\n",
    "inchi_index = []\n",
    "for i in range(len(converted_inchi_list)):\n",
    "    if converted_inchi_list[i] in overlapping_inchi:\n",
    "        inchi_index.append(i)\n",
    "inchi_index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'InChI=1S/C12H10FN3O2S/c13-7-3-1-6(2-4-7)9-5-8(10(14)17)11(19-9)16-12(15)18/h1-5H,(H2,14,17)(H3,15,16,18)': 1.4170812991639807,\n",
       " 'InChI=1S/C15H14N2O4S/c18-15(16-19)10-9-12-5-4-8-14(11-12)22(20,21)17-13-6-2-1-3-7-13/h1-11,17,19H,(H,16,18)/b10-9+': -0.95548114460381517,\n",
       " 'InChI=1S/C17H12F9NO3S/c18-14(19,20)10-27(31(29,30)13-4-2-1-3-5-13)12-8-6-11(7-9-12)15(28,16(21,22)23)17(24,25)26/h1-9,28H,10H2': 0.038028979264701737,\n",
       " 'InChI=1S/C17H19NO5/c1-21-13-10-12(11-14(22-2)17(13)23-3)7-8-16(20)18-9-5-4-6-15(18)19/h4,6-8,10-11H,5,9H2,1-3H3/b8-7+': -0.59774322856227036,\n",
       " 'InChI=1S/C19H12Cl2N2O2/c1-23-9-13(11-4-2-3-5-15(11)23)17-16(18(24)22-19(17)25)12-7-6-10(20)8-14(12)21/h2-9H,1H3,(H,22,24,25)': -0.63420497314175805,\n",
       " 'InChI=1S/C20H20N2O4/c1-3-12-8-14(16(24)10-15(12)23)20-19(11(2)21-22-20)13-4-5-17-18(9-13)26-7-6-25-17/h4-5,8-10,23-24H,3,6-7H2,1-2H3,(H,21,22)': -0.69909760833211643,\n",
       " 'InChI=1S/C26H29NO/c1-4-25(21-11-7-5-8-12-21)26(22-13-9-6-10-14-22)23-15-17-24(18-16-23)28-20-19-27(2)3/h5-18H,4,19-20H2,1-3H3/b26-25-': 0.83160357303321086,\n",
       " 'InChI=1S/C27H29NO11/c1-10-22(31)13(28)6-17(38-10)39-15-8-27(36,16(30)9-29)7-12-19(15)26(35)21-20(24(12)33)23(32)11-4-3-5-14(37-2)18(11)25(21)34/h3-5,10,13,15,17,22,29,31,33,35-36H,6-9,28H2,1-2H3/t10-,13-,15-,17-,22+,27-/m0/s1': 1.919535291573689,\n",
       " 'InChI=1S/C29H32O13/c1-11-36-9-20-27(40-11)24(31)25(32)29(41-20)42-26-14-7-17-16(38-10-39-17)6-13(14)21(22-15(26)8-37-28(22)33)12-4-18(34-2)23(30)19(5-12)35-3/h4-7,11,15,20-22,24-27,29-32H,8-10H2,1-3H3/t11-,15+,20-,21-,22+,24-,25-,26-,27-,29+/m1/s1': 1.7845468967387552,\n",
       " 'InChI=1S/C34H50O12/c1-9-12-13-14-15-17-24(37)43-28-26-25(20(5)27(28)44-30(38)19(4)11-3)29-34(41,33(8,40)31(39)45-29)22(42-23(36)16-10-2)18-32(26,7)46-21(6)35/h11,22,26-29,40-41H,9-10,12-18H2,1-8H3/b19-11-/t22-,26+,27-,28-,29-,32-,33+,34+/m0/s1': 0.18927683315036906}"
      ]
     },
     "execution_count": 98,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "inchi_score_dict = {}\n",
    "\n",
    "for i in inchi_index:\n",
    "    inchi_score_dict[converted_inchi_list[i]] = df['Z Score'][i]\n",
    "    \n",
    "inchi_score_dict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {},
   "outputs": [],
   "source": [
    "overlapping_index = []\n",
    "overlapping_score = []\n",
    "for i in range(len(image_df['INCHI'])):\n",
    "    if image_df['INCHI'][i] in overlapping_inchi:\n",
    "        overlapping_index.append(i)\n",
    "        overlapping_score.append(inchi_score_dict[image_df['INCHI'][i]])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "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>54</th>\n",
       "      <td>BRD-K17743125-001-01-9</td>\n",
       "      <td>belinostat</td>\n",
       "      <td>USAN</td>\n",
       "      <td>SA68090</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Broad Institute CMLD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ONC(=O)\\C=C\\c1cccc(c1)S(=O)(=O)Nc1ccccc1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>O=C(C=Cc1cccc(S(=O)(=O)Nc2ccccc2)c1)NO</td>\n",
       "      <td>InChI=1S/C15H14N2O4S/c18-15(16-19)10-9-12-5-4-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>230</th>\n",
       "      <td>BRD-K93754473-001-02-9</td>\n",
       "      <td>tamoxifen</td>\n",
       "      <td>INN</td>\n",
       "      <td>SA82346</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Biomol International Inc.</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S-650</td>\n",
       "      <td>CC\\C(c1ccccc1)=C(/c1ccccc1)c1ccc(OCCN(C)C)cc1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CCC(=C(c1ccccc1)c1ccc(OCCN(C)C)cc1)c1ccccc1</td>\n",
       "      <td>InChI=1S/C26H29NO/c1-4-25(21-11-7-5-8-12-21)26...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>314</th>\n",
       "      <td>BRD-K92093830-003-05-0</td>\n",
       "      <td>doxorubicin</td>\n",
       "      <td>INN</td>\n",
       "      <td>SA82891</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Prestwick Chemical Inc.</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Prestw-438</td>\n",
       "      <td>COc1cccc2C(=O)c3c(O)c4C[C@](O)(C[C@H](O[C@H]5C...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>COc1cccc2c1C(=O)c1c(O)c3c(c(O)c1C2=O)CC(O)(C(=...</td>\n",
       "      <td>InChI=1S/C27H29NO11/c1-10-22(31)13(28)6-17(38-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1542</th>\n",
       "      <td>BRD-K65503129-001-01-4</td>\n",
       "      <td>CCT018159</td>\n",
       "      <td>primary-common</td>\n",
       "      <td>SA792853</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Tocris Bioscience</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2435</td>\n",
       "      <td>CCc1cc(-c2n[nH]c(C)c2-c2ccc3OCCOc3c2)c(O)cc1O</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CCc1cc(-c2n[nH]c(C)c2-c2ccc3c(c2)OCCO3)c(O)cc1O</td>\n",
       "      <td>InChI=1S/C20H20N2O4/c1-3-12-8-14(16(24)10-15(1...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1545</th>\n",
       "      <td>BRD-K69023402-001-02-5</td>\n",
       "      <td>thapsigargin</td>\n",
       "      <td>common</td>\n",
       "      <td>SA82112</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Biomol International Inc.</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PE-180</td>\n",
       "      <td>CCCCCCCC(=O)O[C@@H]1[C@@H](OC(=O)C(\\C)=C/C)C(C...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CC=C(C)C(=O)OC1C(C)=C2C(C1OC(=O)CCCCCCC)C(C)(O...</td>\n",
       "      <td>InChI=1S/C34H50O12/c1-9-12-13-14-15-17-24(37)4...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1575</th>\n",
       "      <td>BRD-K24132293-001-02-0</td>\n",
       "      <td>piperlongumine</td>\n",
       "      <td>primary-common</td>\n",
       "      <td>SA37589</td>\n",
       "      <td>NaN</td>\n",
       "      <td>AnalytiCon Discovery GmbH</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NP-003716</td>\n",
       "      <td>COc1cc(cc(OC)c1OC)\\C=C\\C(=O)N1CCC=CC1=O</td>\n",
       "      <td>NaN</td>\n",
       "      <td>COc1cc(C=CC(=O)N2CCC=CC2=O)cc(OC)c1OC</td>\n",
       "      <td>InChI=1S/C17H19NO5/c1-21-13-10-12(11-14(22-2)1...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1614</th>\n",
       "      <td>BRD-K23383398-001-01-6</td>\n",
       "      <td>T-0901317</td>\n",
       "      <td>primary-common</td>\n",
       "      <td>SA792830</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Tocris Bioscience</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2373</td>\n",
       "      <td>OC(c1ccc(cc1)N(CC(F)(F)F)S(=O)(=O)c1ccccc1)(C(...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>O=S(=O)(c1ccccc1)N(CC(F)(F)F)c1ccc(C(O)(C(F)(F...</td>\n",
       "      <td>InChI=1S/C17H12F9NO3S/c18-14(19,20)10-27(31(29...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1653</th>\n",
       "      <td>BRD-K37798499-001-02-5</td>\n",
       "      <td>etoposide</td>\n",
       "      <td>INN</td>\n",
       "      <td>SA82914</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Prestwick Chemical Inc.</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Prestw-396</td>\n",
       "      <td>COc1cc(cc(OC)c1O)[C@H]1[C@@H]2[C@H](COC2=O)[C@...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>COc1cc(C2c3cc4c(cc3C(OC3OC5COC(C)OC5C(O)C3O)C3...</td>\n",
       "      <td>InChI=1S/C29H32O13/c1-11-36-9-20-27(40-11)24(3...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1873</th>\n",
       "      <td>BRD-K51575138-001-01-7</td>\n",
       "      <td>TPCA-1</td>\n",
       "      <td>primary-common</td>\n",
       "      <td>SA792920</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Tocris Bioscience</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2559</td>\n",
       "      <td>NC(=O)Nc1sc(cc1C(N)=O)-c1ccc(F)cc1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NC(=O)Nc1sc(-c2ccc(F)cc2)cc1C(N)=O</td>\n",
       "      <td>InChI=1S/C12H10FN3O2S/c13-7-3-1-6(2-4-7)9-5-8(...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2233</th>\n",
       "      <td>BRD-K59184148-001-04-2</td>\n",
       "      <td>SB-216763</td>\n",
       "      <td>primary-common</td>\n",
       "      <td>SA792563</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Tocris Bioscience</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1616</td>\n",
       "      <td>Cn1cc(C2=C(C(=O)NC2=O)c2ccc(Cl)cc2Cl)c2ccccc12</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Cn1cc(C2=C(c3ccc(Cl)cc3Cl)C(=O)NC2=O)c2ccccc21</td>\n",
       "      <td>InChI=1S/C19H12Cl2N2O2/c1-23-9-13(11-4-2-3-5-1...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19552</th>\n",
       "      <td>BRD-K59184148-001-10-9</td>\n",
       "      <td>SB-216763</td>\n",
       "      <td>primary-common</td>\n",
       "      <td>SA1861519</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Sigma-Aldrich</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S3442</td>\n",
       "      <td>Cn1cc(C2=C(C(=O)NC2=O)c2ccc(Cl)cc2Cl)c2ccccc12</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Cn1cc(C2=C(c3ccc(Cl)cc3Cl)C(=O)NC2=O)c2ccccc21</td>\n",
       "      <td>InChI=1S/C19H12Cl2N2O2/c1-23-9-13(11-4-2-3-5-1...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     BROAD_ID        CPD_NAME   CPD_NAME_TYPE CPD_SAMPLE_ID  \\\n",
       "54     BRD-K17743125-001-01-9      belinostat            USAN       SA68090   \n",
       "230    BRD-K93754473-001-02-9       tamoxifen             INN       SA82346   \n",
       "314    BRD-K92093830-003-05-0     doxorubicin             INN       SA82891   \n",
       "1542   BRD-K65503129-001-01-4       CCT018159  primary-common      SA792853   \n",
       "1545   BRD-K69023402-001-02-5    thapsigargin          common       SA82112   \n",
       "1575   BRD-K24132293-001-02-0  piperlongumine  primary-common       SA37589   \n",
       "1614   BRD-K23383398-001-01-6       T-0901317  primary-common      SA792830   \n",
       "1653   BRD-K37798499-001-02-5       etoposide             INN       SA82914   \n",
       "1873   BRD-K51575138-001-01-7          TPCA-1  primary-common      SA792920   \n",
       "2233   BRD-K59184148-001-04-2       SB-216763  primary-common      SA792563   \n",
       "19552  BRD-K59184148-001-10-9       SB-216763  primary-common     SA1861519   \n",
       "\n",
       "      DOS_LIBRARY                SOURCE_NAME CHEMIST_NAME VENDOR_CATALOG_ID  \\\n",
       "54            NaN       Broad Institute CMLD          NaN               NaN   \n",
       "230           NaN  Biomol International Inc.          NaN             S-650   \n",
       "314           NaN    Prestwick Chemical Inc.          NaN        Prestw-438   \n",
       "1542          NaN          Tocris Bioscience          NaN              2435   \n",
       "1545          NaN  Biomol International Inc.          NaN            PE-180   \n",
       "1575          NaN  AnalytiCon Discovery GmbH          NaN         NP-003716   \n",
       "1614          NaN          Tocris Bioscience          NaN              2373   \n",
       "1653          NaN    Prestwick Chemical Inc.          NaN        Prestw-396   \n",
       "1873          NaN          Tocris Bioscience          NaN              2559   \n",
       "2233          NaN          Tocris Bioscience          NaN              1616   \n",
       "19552         NaN              Sigma-Aldrich          NaN             S3442   \n",
       "\n",
       "                                              CPD_SMILES USERCOMMENT  \\\n",
       "54              ONC(=O)\\C=C\\c1cccc(c1)S(=O)(=O)Nc1ccccc1         NaN   \n",
       "230        CC\\C(c1ccccc1)=C(/c1ccccc1)c1ccc(OCCN(C)C)cc1         NaN   \n",
       "314    COc1cccc2C(=O)c3c(O)c4C[C@](O)(C[C@H](O[C@H]5C...         NaN   \n",
       "1542       CCc1cc(-c2n[nH]c(C)c2-c2ccc3OCCOc3c2)c(O)cc1O         NaN   \n",
       "1545   CCCCCCCC(=O)O[C@@H]1[C@@H](OC(=O)C(\\C)=C/C)C(C...         NaN   \n",
       "1575             COc1cc(cc(OC)c1OC)\\C=C\\C(=O)N1CCC=CC1=O         NaN   \n",
       "1614   OC(c1ccc(cc1)N(CC(F)(F)F)S(=O)(=O)c1ccccc1)(C(...         NaN   \n",
       "1653   COc1cc(cc(OC)c1O)[C@H]1[C@@H]2[C@H](COC2=O)[C@...         NaN   \n",
       "1873                  NC(=O)Nc1sc(cc1C(N)=O)-c1ccc(F)cc1         NaN   \n",
       "2233      Cn1cc(C2=C(C(=O)NC2=O)c2ccc(Cl)cc2Cl)c2ccccc12         NaN   \n",
       "19552     Cn1cc(C2=C(C(=O)NC2=O)c2ccc(Cl)cc2Cl)c2ccccc12         NaN   \n",
       "\n",
       "                                    CPD_CANONICAL_SMILES  \\\n",
       "54                O=C(C=Cc1cccc(S(=O)(=O)Nc2ccccc2)c1)NO   \n",
       "230          CCC(=C(c1ccccc1)c1ccc(OCCN(C)C)cc1)c1ccccc1   \n",
       "314    COc1cccc2c1C(=O)c1c(O)c3c(c(O)c1C2=O)CC(O)(C(=...   \n",
       "1542     CCc1cc(-c2n[nH]c(C)c2-c2ccc3c(c2)OCCO3)c(O)cc1O   \n",
       "1545   CC=C(C)C(=O)OC1C(C)=C2C(C1OC(=O)CCCCCCC)C(C)(O...   \n",
       "1575               COc1cc(C=CC(=O)N2CCC=CC2=O)cc(OC)c1OC   \n",
       "1614   O=S(=O)(c1ccccc1)N(CC(F)(F)F)c1ccc(C(O)(C(F)(F...   \n",
       "1653   COc1cc(C2c3cc4c(cc3C(OC3OC5COC(C)OC5C(O)C3O)C3...   \n",
       "1873                  NC(=O)Nc1sc(-c2ccc(F)cc2)cc1C(N)=O   \n",
       "2233      Cn1cc(C2=C(c3ccc(Cl)cc3Cl)C(=O)NC2=O)c2ccccc21   \n",
       "19552     Cn1cc(C2=C(c3ccc(Cl)cc3Cl)C(=O)NC2=O)c2ccccc21   \n",
       "\n",
       "                                                   INCHI  \n",
       "54     InChI=1S/C15H14N2O4S/c18-15(16-19)10-9-12-5-4-...  \n",
       "230    InChI=1S/C26H29NO/c1-4-25(21-11-7-5-8-12-21)26...  \n",
       "314    InChI=1S/C27H29NO11/c1-10-22(31)13(28)6-17(38-...  \n",
       "1542   InChI=1S/C20H20N2O4/c1-3-12-8-14(16(24)10-15(1...  \n",
       "1545   InChI=1S/C34H50O12/c1-9-12-13-14-15-17-24(37)4...  \n",
       "1575   InChI=1S/C17H19NO5/c1-21-13-10-12(11-14(22-2)1...  \n",
       "1614   InChI=1S/C17H12F9NO3S/c18-14(19,20)10-27(31(29...  \n",
       "1653   InChI=1S/C29H32O13/c1-11-36-9-20-27(40-11)24(3...  \n",
       "1873   InChI=1S/C12H10FN3O2S/c13-7-3-1-6(2-4-7)9-5-8(...  \n",
       "2233   InChI=1S/C19H12Cl2N2O2/c1-23-9-13(11-4-2-3-5-1...  \n",
       "19552  InChI=1S/C19H12Cl2N2O2/c1-23-9-13(11-4-2-3-5-1...  "
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "image_df.iloc[overlapping_index, :]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'BRD-K17743125-001-01-9': -0.95548114460381517,\n",
       " 'BRD-K23383398-001-01-6': 0.038028979264701737,\n",
       " 'BRD-K24132293-001-02-0': -0.59774322856227036,\n",
       " 'BRD-K37798499-001-02-5': 1.7845468967387552,\n",
       " 'BRD-K51575138-001-01-7': 1.4170812991639807,\n",
       " 'BRD-K59184148-001-04-2': -0.63420497314175805,\n",
       " 'BRD-K59184148-001-10-9': -0.63420497314175805,\n",
       " 'BRD-K65503129-001-01-4': -0.69909760833211643,\n",
       " 'BRD-K69023402-001-02-5': 0.18927683315036906,\n",
       " 'BRD-K92093830-003-05-0': 1.919535291573689,\n",
       " 'BRD-K93754473-001-02-9': 0.83160357303321086}"
      ]
     },
     "execution_count": 103,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "overlapping_bid = image_df.iloc[overlapping_index, :]['BROAD_ID'].tolist()\n",
    "overlapping_bid_dict = dict(zip(overlapping_bid, overlapping_score))\n",
    "overlapping_bid_dict"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Find Corresponding wells\n",
    "\n",
    "Now, we have the overlapping bids. We can trace back the wells and plates where these compounds were tested."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [],
   "source": [
    "plate_table = pd.read_csv('./resource/merged_table_406.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "84\n"
     ]
    }
   ],
   "source": [
    "bids = plate_table['Metadata_broad_sample'].tolist()\n",
    "plate_table_overlapping = {}\n",
    "plate_table_index = []\n",
    "\n",
    "for b in range(len(bids)):\n",
    "    if bids[b] in overlapping_bid_dict:\n",
    "        plate_table_index.append(b)\n",
    "        if bids[b] not in plate_table_overlapping:\n",
    "            plate_table_overlapping[bids[b]] = [b]\n",
    "        else:\n",
    "            plate_table_overlapping[bids[b]].append(b)\n",
    "        \n",
    "print(len(plate_table_index))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are 84 wells having these compounds."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{(24277, 'c24'): 0.038028979264701737,\n",
       " (24277, 'd13'): -0.59774322856227036,\n",
       " (24277, 'o08'): 1.7845468967387552,\n",
       " (24278, 'k04'): 1.919535291573689,\n",
       " (24278, 'l21'): 0.83160357303321086,\n",
       " (24279, 'k04'): 1.919535291573689,\n",
       " (24279, 'l21'): 0.83160357303321086,\n",
       " (24280, 'k04'): 1.919535291573689,\n",
       " (24280, 'l21'): 0.83160357303321086,\n",
       " (24293, 'k04'): 1.919535291573689,\n",
       " (24293, 'l21'): 0.83160357303321086,\n",
       " (24296, 'c24'): 0.038028979264701737,\n",
       " (24296, 'd13'): -0.59774322856227036,\n",
       " (24296, 'o08'): 1.7845468967387552,\n",
       " (24305, 'p14'): -0.95548114460381517,\n",
       " (24306, 'p14'): -0.95548114460381517,\n",
       " (24307, 'p14'): -0.95548114460381517,\n",
       " (24308, 'c24'): 0.038028979264701737,\n",
       " (24308, 'd13'): -0.59774322856227036,\n",
       " (24308, 'o08'): 1.7845468967387552,\n",
       " (24309, 'c24'): 0.038028979264701737,\n",
       " (24309, 'd13'): -0.59774322856227036,\n",
       " (24309, 'o08'): 1.7845468967387552,\n",
       " (24310, 'i23'): 1.4170812991639807,\n",
       " (24310, 'o15'): -0.63420497314175805,\n",
       " (24311, 'i23'): 1.4170812991639807,\n",
       " (24311, 'o15'): -0.63420497314175805,\n",
       " (24312, 'i23'): 1.4170812991639807,\n",
       " (24312, 'o15'): -0.63420497314175805,\n",
       " (24313, 'i23'): 1.4170812991639807,\n",
       " (24313, 'o15'): -0.63420497314175805,\n",
       " (24319, 'f13'): -0.69909760833211643,\n",
       " (24319, 'i18'): 0.18927683315036906,\n",
       " (24320, 'f13'): -0.69909760833211643,\n",
       " (24320, 'i18'): 0.18927683315036906,\n",
       " (24321, 'f13'): -0.69909760833211643,\n",
       " (24321, 'i18'): 0.18927683315036906,\n",
       " (24352, 'p14'): -0.95548114460381517,\n",
       " (24357, 'f13'): -0.69909760833211643,\n",
       " (24357, 'i18'): 0.18927683315036906,\n",
       " (25391, 'n09'): -0.63420497314175805,\n",
       " (25392, 'n09'): -0.63420497314175805,\n",
       " (25403, 'n09'): -0.63420497314175805,\n",
       " (25416, 'n09'): -0.63420497314175805,\n",
       " (25955, 'p14'): -0.95548114460381517,\n",
       " (25962, 'p14'): -0.95548114460381517,\n",
       " (25965, 'p14'): -0.95548114460381517,\n",
       " (25966, 'p14'): -0.95548114460381517,\n",
       " (25967, 'c24'): 0.038028979264701737,\n",
       " (25967, 'd13'): -0.59774322856227036,\n",
       " (25967, 'o08'): 1.7845468967387552,\n",
       " (25968, 'c24'): 0.038028979264701737,\n",
       " (25968, 'd13'): -0.59774322856227036,\n",
       " (25968, 'o08'): 1.7845468967387552,\n",
       " (25983, 'c24'): 0.038028979264701737,\n",
       " (25983, 'd13'): -0.59774322856227036,\n",
       " (25983, 'o08'): 1.7845468967387552,\n",
       " (25984, 'c24'): 0.038028979264701737,\n",
       " (25984, 'd13'): -0.59774322856227036,\n",
       " (25984, 'o08'): 1.7845468967387552,\n",
       " (25985, 'i23'): 1.4170812991639807,\n",
       " (25985, 'o15'): -0.63420497314175805,\n",
       " (25986, 'i23'): 1.4170812991639807,\n",
       " (25986, 'o15'): -0.63420497314175805,\n",
       " (25987, 'i23'): 1.4170812991639807,\n",
       " (25987, 'o15'): -0.63420497314175805,\n",
       " (25988, 'i23'): 1.4170812991639807,\n",
       " (25988, 'o15'): -0.63420497314175805,\n",
       " (25989, 'f13'): -0.69909760833211643,\n",
       " (25989, 'i18'): 0.18927683315036906,\n",
       " (25990, 'f13'): -0.69909760833211643,\n",
       " (25990, 'i18'): 0.18927683315036906,\n",
       " (25991, 'f13'): -0.69909760833211643,\n",
       " (25991, 'i18'): 0.18927683315036906,\n",
       " (25992, 'f13'): -0.69909760833211643,\n",
       " (25992, 'i18'): 0.18927683315036906,\n",
       " (26224, 'k04'): 1.919535291573689,\n",
       " (26224, 'l21'): 0.83160357303321086,\n",
       " (26232, 'k04'): 1.919535291573689,\n",
       " (26232, 'l21'): 0.83160357303321086,\n",
       " (26239, 'k04'): 1.919535291573689,\n",
       " (26239, 'l21'): 0.83160357303321086,\n",
       " (26247, 'k04'): 1.919535291573689,\n",
       " (26247, 'l21'): 0.83160357303321086}"
      ]
     },
     "execution_count": 114,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pids = plate_table['Metadata_Plate'][plate_table_index]\n",
    "wids = plate_table['Metadata_Well'][plate_table_index]\n",
    "\n",
    "pairs = []\n",
    "pw_dict = {}\n",
    "for k in plate_table_overlapping:\n",
    "    for i in plate_table_overlapping[k]:\n",
    "        pw_dict[(pids[i], wids[i])] = overlapping_bid_dict[k]\n",
    "        pairs.append((pids[i], wids[i]))\n",
    "        \n",
    "pw_dict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "metadata": {},
   "outputs": [],
   "source": [
    "with open('args.txt', 'w') as fp:\n",
    "    for p in set(pairs):\n",
    "        for sid in [1, 2, 3, 4, 5, 6]:\n",
    "            fp.write(\"{},{},{},{}\\n\".format(p[0], p[1], sid, 1))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "metadata": {},
   "outputs": [],
   "source": [
    "temp_dict = {}\n",
    "for k in pw_dict:\n",
    "    cw = '{}_{}'.format(k[0], k[1])\n",
    "    temp_dict[cw] = pw_dict[k]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "metadata": {},
   "outputs": [],
   "source": [
    "pre_norm = pd.read_csv('./resource/umap_combined_feature_406.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n"
     ]
    }
   ],
   "source": [
    "pre_norm_index = []\n",
    "for i in range(len(pre_norm['label'])):\n",
    "    if pre_norm['label'][i] in temp_dict:\n",
    "        pre_norm_index.append(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "458"
      ]
     },
     "execution_count": 128,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(pre_norm_index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 135,
   "metadata": {},
   "outputs": [],
   "source": [
    "pre_norm_overlap = pre_norm.iloc[pre_norm_index,:].copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 136,
   "metadata": {},
   "outputs": [],
   "source": [
    "cor_scores = []\n",
    "for i, r in pre_norm_overlap.iterrows():\n",
    "    cor_scores.append(temp_dict[r['label']])\n",
    "pre_norm_overlap['score'] = cor_scores"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 138,
   "metadata": {},
   "outputs": [],
   "source": [
    "pre_norm_overlap.to_csv('pre_nrom_overlap.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "metadata": {},
   "outputs": [],
   "source": [
    "post_norm = pd.read_csv('./resource/umap_normed_features.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 141,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "458"
      ]
     },
     "execution_count": 141,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "post_norm_index = []\n",
    "for i in range(len(pre_norm['label'])):\n",
    "    if post_norm['label'][i] in temp_dict:\n",
    "        post_norm_index.append(i)\n",
    "len(pre_norm_index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 142,
   "metadata": {},
   "outputs": [],
   "source": [
    "post_norm_overlap = post_norm.iloc[post_norm_index,:].copy()\n",
    "cor_scores = []\n",
    "for i, r in post_norm_overlap.iterrows():\n",
    "    cor_scores.append(temp_dict[r['label']])\n",
    "post_norm_overlap['score'] = cor_scores"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 143,
   "metadata": {},
   "outputs": [],
   "source": [
    "post_norm_overlap.to_csv('post_nrom_overlap.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 144,
   "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>x</th>\n",
       "      <th>y</th>\n",
       "      <th>label</th>\n",
       "      <th>label_2</th>\n",
       "      <th>score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>9077</th>\n",
       "      <td>2.148799</td>\n",
       "      <td>-3.753407</td>\n",
       "      <td>24305_p14</td>\n",
       "      <td>5</td>\n",
       "      <td>-0.955481</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9078</th>\n",
       "      <td>2.353336</td>\n",
       "      <td>-3.621856</td>\n",
       "      <td>24305_p14</td>\n",
       "      <td>4</td>\n",
       "      <td>-0.955481</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9079</th>\n",
       "      <td>2.571708</td>\n",
       "      <td>-3.003010</td>\n",
       "      <td>24305_p14</td>\n",
       "      <td>3</td>\n",
       "      <td>-0.955481</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9080</th>\n",
       "      <td>1.858499</td>\n",
       "      <td>-4.042998</td>\n",
       "      <td>24305_p14</td>\n",
       "      <td>1</td>\n",
       "      <td>-0.955481</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9081</th>\n",
       "      <td>1.394501</td>\n",
       "      <td>-4.192612</td>\n",
       "      <td>24305_p14</td>\n",
       "      <td>6</td>\n",
       "      <td>-0.955481</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9082</th>\n",
       "      <td>2.024525</td>\n",
       "      <td>-3.983737</td>\n",
       "      <td>24305_p14</td>\n",
       "      <td>2</td>\n",
       "      <td>-0.955481</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28255</th>\n",
       "      <td>1.575198</td>\n",
       "      <td>-3.963285</td>\n",
       "      <td>24306_p14</td>\n",
       "      <td>4</td>\n",
       "      <td>-0.955481</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28256</th>\n",
       "      <td>2.843200</td>\n",
       "      <td>-2.593352</td>\n",
       "      <td>24306_p14</td>\n",
       "      <td>2</td>\n",
       "      <td>-0.955481</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28257</th>\n",
       "      <td>2.320187</td>\n",
       "      <td>-0.740441</td>\n",
       "      <td>24306_p14</td>\n",
       "      <td>6</td>\n",
       "      <td>-0.955481</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28258</th>\n",
       "      <td>2.330403</td>\n",
       "      <td>-3.323104</td>\n",
       "      <td>24306_p14</td>\n",
       "      <td>3</td>\n",
       "      <td>-0.955481</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28259</th>\n",
       "      <td>2.613381</td>\n",
       "      <td>-2.965644</td>\n",
       "      <td>24306_p14</td>\n",
       "      <td>1</td>\n",
       "      <td>-0.955481</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28260</th>\n",
       "      <td>2.641313</td>\n",
       "      <td>-2.785738</td>\n",
       "      <td>24306_p14</td>\n",
       "      <td>5</td>\n",
       "      <td>-0.955481</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28665</th>\n",
       "      <td>1.063598</td>\n",
       "      <td>-3.998532</td>\n",
       "      <td>25968_d13</td>\n",
       "      <td>2</td>\n",
       "      <td>-0.597743</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28666</th>\n",
       "      <td>2.699500</td>\n",
       "      <td>-2.994486</td>\n",
       "      <td>25968_d13</td>\n",
       "      <td>4</td>\n",
       "      <td>-0.597743</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28667</th>\n",
       "      <td>-0.695452</td>\n",
       "      <td>-3.768137</td>\n",
       "      <td>25968_d13</td>\n",
       "      <td>1</td>\n",
       "      <td>-0.597743</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28668</th>\n",
       "      <td>-0.009505</td>\n",
       "      <td>-4.000948</td>\n",
       "      <td>25968_d13</td>\n",
       "      <td>6</td>\n",
       "      <td>-0.597743</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28669</th>\n",
       "      <td>0.211755</td>\n",
       "      <td>-3.982119</td>\n",
       "      <td>25968_d13</td>\n",
       "      <td>5</td>\n",
       "      <td>-0.597743</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28670</th>\n",
       "      <td>-0.531786</td>\n",
       "      <td>-4.020964</td>\n",
       "      <td>25968_d13</td>\n",
       "      <td>3</td>\n",
       "      <td>-0.597743</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43002</th>\n",
       "      <td>-2.983720</td>\n",
       "      <td>0.802242</td>\n",
       "      <td>25392_n09</td>\n",
       "      <td>4</td>\n",
       "      <td>-0.634205</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43003</th>\n",
       "      <td>-1.514959</td>\n",
       "      <td>0.105787</td>\n",
       "      <td>25392_n09</td>\n",
       "      <td>6</td>\n",
       "      <td>-0.634205</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43004</th>\n",
       "      <td>-0.675870</td>\n",
       "      <td>3.031757</td>\n",
       "      <td>25392_n09</td>\n",
       "      <td>2</td>\n",
       "      <td>-0.634205</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43005</th>\n",
       "      <td>1.528070</td>\n",
       "      <td>-2.490467</td>\n",
       "      <td>25392_n09</td>\n",
       "      <td>3</td>\n",
       "      <td>-0.634205</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43006</th>\n",
       "      <td>-0.689217</td>\n",
       "      <td>0.537436</td>\n",
       "      <td>25392_n09</td>\n",
       "      <td>1</td>\n",
       "      <td>-0.634205</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43007</th>\n",
       "      <td>-0.564993</td>\n",
       "      <td>-0.836390</td>\n",
       "      <td>25392_n09</td>\n",
       "      <td>5</td>\n",
       "      <td>-0.634205</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48804</th>\n",
       "      <td>-3.434887</td>\n",
       "      <td>-0.912135</td>\n",
       "      <td>24310_i23</td>\n",
       "      <td>3</td>\n",
       "      <td>1.417081</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48805</th>\n",
       "      <td>-0.157533</td>\n",
       "      <td>3.516917</td>\n",
       "      <td>24310_i23</td>\n",
       "      <td>2</td>\n",
       "      <td>1.417081</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48806</th>\n",
       "      <td>1.559547</td>\n",
       "      <td>-3.141021</td>\n",
       "      <td>24310_i23</td>\n",
       "      <td>1</td>\n",
       "      <td>1.417081</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48807</th>\n",
       "      <td>-0.496875</td>\n",
       "      <td>2.779028</td>\n",
       "      <td>24310_i23</td>\n",
       "      <td>5</td>\n",
       "      <td>1.417081</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48808</th>\n",
       "      <td>2.614867</td>\n",
       "      <td>-2.863869</td>\n",
       "      <td>24310_i23</td>\n",
       "      <td>6</td>\n",
       "      <td>1.417081</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48809</th>\n",
       "      <td>2.682886</td>\n",
       "      <td>-3.146484</td>\n",
       "      <td>24310_i23</td>\n",
       "      <td>4</td>\n",
       "      <td>1.417081</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>857172</th>\n",
       "      <td>0.806934</td>\n",
       "      <td>-3.475133</td>\n",
       "      <td>24308_d13</td>\n",
       "      <td>1</td>\n",
       "      <td>-0.597743</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>862562</th>\n",
       "      <td>0.518938</td>\n",
       "      <td>-4.060803</td>\n",
       "      <td>25992_i18</td>\n",
       "      <td>6</td>\n",
       "      <td>0.189277</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>862563</th>\n",
       "      <td>0.049863</td>\n",
       "      <td>-4.232517</td>\n",
       "      <td>25992_i18</td>\n",
       "      <td>3</td>\n",
       "      <td>0.189277</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>876453</th>\n",
       "      <td>1.815249</td>\n",
       "      <td>-4.033098</td>\n",
       "      <td>24309_d13</td>\n",
       "      <td>5</td>\n",
       "      <td>-0.597743</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>876454</th>\n",
       "      <td>0.948275</td>\n",
       "      <td>-3.574609</td>\n",
       "      <td>24309_d13</td>\n",
       "      <td>2</td>\n",
       "      <td>-0.597743</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>876455</th>\n",
       "      <td>1.818461</td>\n",
       "      <td>-3.662368</td>\n",
       "      <td>24309_d13</td>\n",
       "      <td>4</td>\n",
       "      <td>-0.597743</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>876456</th>\n",
       "      <td>1.470372</td>\n",
       "      <td>-3.799833</td>\n",
       "      <td>24309_d13</td>\n",
       "      <td>6</td>\n",
       "      <td>-0.597743</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>876457</th>\n",
       "      <td>2.279154</td>\n",
       "      <td>-3.564791</td>\n",
       "      <td>24309_d13</td>\n",
       "      <td>1</td>\n",
       "      <td>-0.597743</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>876458</th>\n",
       "      <td>1.386737</td>\n",
       "      <td>-3.722932</td>\n",
       "      <td>24309_d13</td>\n",
       "      <td>3</td>\n",
       "      <td>-0.597743</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>881012</th>\n",
       "      <td>3.057847</td>\n",
       "      <td>-2.192921</td>\n",
       "      <td>25984_c24</td>\n",
       "      <td>2</td>\n",
       "      <td>0.038029</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>881013</th>\n",
       "      <td>2.339253</td>\n",
       "      <td>1.686356</td>\n",
       "      <td>25984_c24</td>\n",
       "      <td>5</td>\n",
       "      <td>0.038029</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>881014</th>\n",
       "      <td>2.888512</td>\n",
       "      <td>0.002905</td>\n",
       "      <td>25984_c24</td>\n",
       "      <td>4</td>\n",
       "      <td>0.038029</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>881015</th>\n",
       "      <td>2.497838</td>\n",
       "      <td>2.128955</td>\n",
       "      <td>25984_c24</td>\n",
       "      <td>6</td>\n",
       "      <td>0.038029</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>881016</th>\n",
       "      <td>2.748937</td>\n",
       "      <td>1.933773</td>\n",
       "      <td>25984_c24</td>\n",
       "      <td>1</td>\n",
       "      <td>0.038029</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>881017</th>\n",
       "      <td>3.235942</td>\n",
       "      <td>2.065968</td>\n",
       "      <td>25984_c24</td>\n",
       "      <td>3</td>\n",
       "      <td>0.038029</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>888551</th>\n",
       "      <td>-0.161135</td>\n",
       "      <td>-4.144051</td>\n",
       "      <td>25991_i18</td>\n",
       "      <td>6</td>\n",
       "      <td>0.189277</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>888552</th>\n",
       "      <td>1.415040</td>\n",
       "      <td>-3.975116</td>\n",
       "      <td>25991_i18</td>\n",
       "      <td>1</td>\n",
       "      <td>0.189277</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>888553</th>\n",
       "      <td>-0.022564</td>\n",
       "      <td>-4.106763</td>\n",
       "      <td>25991_i18</td>\n",
       "      <td>3</td>\n",
       "      <td>0.189277</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>890824</th>\n",
       "      <td>-0.448049</td>\n",
       "      <td>-3.628910</td>\n",
       "      <td>24296_d13</td>\n",
       "      <td>5</td>\n",
       "      <td>-0.597743</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>890825</th>\n",
       "      <td>1.398722</td>\n",
       "      <td>-4.029688</td>\n",
       "      <td>24296_d13</td>\n",
       "      <td>2</td>\n",
       "      <td>-0.597743</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>890826</th>\n",
       "      <td>0.880775</td>\n",
       "      <td>-4.083206</td>\n",
       "      <td>24296_d13</td>\n",
       "      <td>1</td>\n",
       "      <td>-0.597743</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>890827</th>\n",
       "      <td>2.156253</td>\n",
       "      <td>-4.353092</td>\n",
       "      <td>24296_d13</td>\n",
       "      <td>3</td>\n",
       "      <td>-0.597743</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>890828</th>\n",
       "      <td>0.884452</td>\n",
       "      <td>-4.079876</td>\n",
       "      <td>24296_d13</td>\n",
       "      <td>6</td>\n",
       "      <td>-0.597743</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>890829</th>\n",
       "      <td>1.234801</td>\n",
       "      <td>-3.811764</td>\n",
       "      <td>24296_d13</td>\n",
       "      <td>4</td>\n",
       "      <td>-0.597743</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>910936</th>\n",
       "      <td>-1.099256</td>\n",
       "      <td>3.539294</td>\n",
       "      <td>26224_l21</td>\n",
       "      <td>2</td>\n",
       "      <td>0.831604</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>910937</th>\n",
       "      <td>-0.821924</td>\n",
       "      <td>3.615143</td>\n",
       "      <td>26224_l21</td>\n",
       "      <td>1</td>\n",
       "      <td>0.831604</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>910938</th>\n",
       "      <td>-0.159093</td>\n",
       "      <td>-0.134303</td>\n",
       "      <td>26224_l21</td>\n",
       "      <td>6</td>\n",
       "      <td>0.831604</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>910939</th>\n",
       "      <td>0.288884</td>\n",
       "      <td>2.913434</td>\n",
       "      <td>26224_l21</td>\n",
       "      <td>3</td>\n",
       "      <td>0.831604</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>910940</th>\n",
       "      <td>0.371060</td>\n",
       "      <td>-2.324379</td>\n",
       "      <td>26224_l21</td>\n",
       "      <td>5</td>\n",
       "      <td>0.831604</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>910941</th>\n",
       "      <td>2.196322</td>\n",
       "      <td>-2.487217</td>\n",
       "      <td>26224_l21</td>\n",
       "      <td>4</td>\n",
       "      <td>0.831604</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>458 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               x         y      label  label_2     score\n",
       "9077    2.148799 -3.753407  24305_p14        5 -0.955481\n",
       "9078    2.353336 -3.621856  24305_p14        4 -0.955481\n",
       "9079    2.571708 -3.003010  24305_p14        3 -0.955481\n",
       "9080    1.858499 -4.042998  24305_p14        1 -0.955481\n",
       "9081    1.394501 -4.192612  24305_p14        6 -0.955481\n",
       "9082    2.024525 -3.983737  24305_p14        2 -0.955481\n",
       "28255   1.575198 -3.963285  24306_p14        4 -0.955481\n",
       "28256   2.843200 -2.593352  24306_p14        2 -0.955481\n",
       "28257   2.320187 -0.740441  24306_p14        6 -0.955481\n",
       "28258   2.330403 -3.323104  24306_p14        3 -0.955481\n",
       "28259   2.613381 -2.965644  24306_p14        1 -0.955481\n",
       "28260   2.641313 -2.785738  24306_p14        5 -0.955481\n",
       "28665   1.063598 -3.998532  25968_d13        2 -0.597743\n",
       "28666   2.699500 -2.994486  25968_d13        4 -0.597743\n",
       "28667  -0.695452 -3.768137  25968_d13        1 -0.597743\n",
       "28668  -0.009505 -4.000948  25968_d13        6 -0.597743\n",
       "28669   0.211755 -3.982119  25968_d13        5 -0.597743\n",
       "28670  -0.531786 -4.020964  25968_d13        3 -0.597743\n",
       "43002  -2.983720  0.802242  25392_n09        4 -0.634205\n",
       "43003  -1.514959  0.105787  25392_n09        6 -0.634205\n",
       "43004  -0.675870  3.031757  25392_n09        2 -0.634205\n",
       "43005   1.528070 -2.490467  25392_n09        3 -0.634205\n",
       "43006  -0.689217  0.537436  25392_n09        1 -0.634205\n",
       "43007  -0.564993 -0.836390  25392_n09        5 -0.634205\n",
       "48804  -3.434887 -0.912135  24310_i23        3  1.417081\n",
       "48805  -0.157533  3.516917  24310_i23        2  1.417081\n",
       "48806   1.559547 -3.141021  24310_i23        1  1.417081\n",
       "48807  -0.496875  2.779028  24310_i23        5  1.417081\n",
       "48808   2.614867 -2.863869  24310_i23        6  1.417081\n",
       "48809   2.682886 -3.146484  24310_i23        4  1.417081\n",
       "...          ...       ...        ...      ...       ...\n",
       "857172  0.806934 -3.475133  24308_d13        1 -0.597743\n",
       "862562  0.518938 -4.060803  25992_i18        6  0.189277\n",
       "862563  0.049863 -4.232517  25992_i18        3  0.189277\n",
       "876453  1.815249 -4.033098  24309_d13        5 -0.597743\n",
       "876454  0.948275 -3.574609  24309_d13        2 -0.597743\n",
       "876455  1.818461 -3.662368  24309_d13        4 -0.597743\n",
       "876456  1.470372 -3.799833  24309_d13        6 -0.597743\n",
       "876457  2.279154 -3.564791  24309_d13        1 -0.597743\n",
       "876458  1.386737 -3.722932  24309_d13        3 -0.597743\n",
       "881012  3.057847 -2.192921  25984_c24        2  0.038029\n",
       "881013  2.339253  1.686356  25984_c24        5  0.038029\n",
       "881014  2.888512  0.002905  25984_c24        4  0.038029\n",
       "881015  2.497838  2.128955  25984_c24        6  0.038029\n",
       "881016  2.748937  1.933773  25984_c24        1  0.038029\n",
       "881017  3.235942  2.065968  25984_c24        3  0.038029\n",
       "888551 -0.161135 -4.144051  25991_i18        6  0.189277\n",
       "888552  1.415040 -3.975116  25991_i18        1  0.189277\n",
       "888553 -0.022564 -4.106763  25991_i18        3  0.189277\n",
       "890824 -0.448049 -3.628910  24296_d13        5 -0.597743\n",
       "890825  1.398722 -4.029688  24296_d13        2 -0.597743\n",
       "890826  0.880775 -4.083206  24296_d13        1 -0.597743\n",
       "890827  2.156253 -4.353092  24296_d13        3 -0.597743\n",
       "890828  0.884452 -4.079876  24296_d13        6 -0.597743\n",
       "890829  1.234801 -3.811764  24296_d13        4 -0.597743\n",
       "910936 -1.099256  3.539294  26224_l21        2  0.831604\n",
       "910937 -0.821924  3.615143  26224_l21        1  0.831604\n",
       "910938 -0.159093 -0.134303  26224_l21        6  0.831604\n",
       "910939  0.288884  2.913434  26224_l21        3  0.831604\n",
       "910940  0.371060 -2.324379  26224_l21        5  0.831604\n",
       "910941  2.196322 -2.487217  26224_l21        4  0.831604\n",
       "\n",
       "[458 rows x 5 columns]"
      ]
     },
     "execution_count": 144,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "post_norm_overlap"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 148,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1.919535291573689"
      ]
     },
     "execution_count": 148,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.max(cor_scores)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.15"
  },
  "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
}