{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import rdkit\n",
    "import rdkit.Chem\n",
    "import rdkit.Chem.inchi\n",
    "import rdkit.Chem.AllChem\n",
    "import rdkit.DataStructs\n",
    "import pandas\n",
    "import sqlite3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Establish database connection\n",
    "connection = sqlite3.connect('data/l1000.db')\n",
    "cursor = connection.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "query = \"\"\"\n",
    "SELECT * FROM perts\n",
    "WHERE pert_type == 'trt_cp'\n",
    "AND inchi_string NOTNULL;\n",
    "\"\"\"\n",
    "pert_df = pandas.read_sql(query, connection)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>pert_uid</th>\n",
       "      <th>pert_id</th>\n",
       "      <th>pert_iname</th>\n",
       "      <th>pert_type</th>\n",
       "      <th>num_gold</th>\n",
       "      <th>num_inst</th>\n",
       "      <th>num_sig</th>\n",
       "      <th>in_summly</th>\n",
       "      <th>inchi_string</th>\n",
       "      <th>inchi_key</th>\n",
       "      <th>pubchem_cid</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>12</td>\n",
       "      <td>BRD-K68741898</td>\n",
       "      <td>BRD-K68741898</td>\n",
       "      <td>trt_cp</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>InChI=1S/C21H37N5O5/c1-15(2)13-31-21(29)24(5)1...</td>\n",
       "      <td>MNAJUJYQFCFYAB-YQVWRLOYSA-N</td>\n",
       "      <td>44505553</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>13</td>\n",
       "      <td>BRD-A05457250</td>\n",
       "      <td>BAY-K8644</td>\n",
       "      <td>trt_cp</td>\n",
       "      <td>1</td>\n",
       "      <td>23</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>InChI=1S/C16H15F3N2O4/c1-8-12(15(22)25-3)13(14...</td>\n",
       "      <td>ZFLWDHHVRRZMEI-UHFFFAOYSA-N</td>\n",
       "      <td>2303</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>14</td>\n",
       "      <td>BRD-K72034655</td>\n",
       "      <td>peucedanin</td>\n",
       "      <td>trt_cp</td>\n",
       "      <td>2</td>\n",
       "      <td>33</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>InChI=1S/C15H14O4/c1-8(2)14-15(17-3)10-6-9-4-5...</td>\n",
       "      <td>YQBNJPACAUPNLV-UHFFFAOYSA-N</td>\n",
       "      <td>8616</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>15</td>\n",
       "      <td>BRD-K02458594</td>\n",
       "      <td>KU-C103869</td>\n",
       "      <td>trt_cp</td>\n",
       "      <td>2</td>\n",
       "      <td>44</td>\n",
       "      <td>13</td>\n",
       "      <td>0</td>\n",
       "      <td>InChI=1S/C15H13NO/c1-11-6-2-5-9-14(11)16-10-12...</td>\n",
       "      <td>YCUIAYUVYLNFFS-UHFFFAOYSA-N</td>\n",
       "      <td>21785456</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>16</td>\n",
       "      <td>BRD-K18814832</td>\n",
       "      <td>BRD-K18814832</td>\n",
       "      <td>trt_cp</td>\n",
       "      <td>1</td>\n",
       "      <td>49</td>\n",
       "      <td>13</td>\n",
       "      <td>0</td>\n",
       "      <td>InChI=1S/C27H23ClN2O3S/c1-18-12-13-19(2)25(16-...</td>\n",
       "      <td>ZQJTYJZLKBRKPC-UHFFFAOYSA-N</td>\n",
       "      <td>2228302</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   pert_uid        pert_id     pert_iname pert_type  num_gold  num_inst  \\\n",
       "0        12  BRD-K68741898  BRD-K68741898    trt_cp         0         3   \n",
       "1        13  BRD-A05457250      BAY-K8644    trt_cp         1        23   \n",
       "2        14  BRD-K72034655     peucedanin    trt_cp         2        33   \n",
       "3        15  BRD-K02458594     KU-C103869    trt_cp         2        44   \n",
       "4        16  BRD-K18814832  BRD-K18814832    trt_cp         1        49   \n",
       "\n",
       "   num_sig  in_summly                                       inchi_string  \\\n",
       "0        1          0  InChI=1S/C21H37N5O5/c1-15(2)13-31-21(29)24(5)1...   \n",
       "1        8          0  InChI=1S/C16H15F3N2O4/c1-8-12(15(22)25-3)13(14...   \n",
       "2        8          0  InChI=1S/C15H14O4/c1-8(2)14-15(17-3)10-6-9-4-5...   \n",
       "3       13          0  InChI=1S/C15H13NO/c1-11-6-2-5-9-14(11)16-10-12...   \n",
       "4       13          0  InChI=1S/C27H23ClN2O3S/c1-18-12-13-19(2)25(16-...   \n",
       "\n",
       "                     inchi_key  pubchem_cid  \n",
       "0  MNAJUJYQFCFYAB-YQVWRLOYSA-N     44505553  \n",
       "1  ZFLWDHHVRRZMEI-UHFFFAOYSA-N         2303  \n",
       "2  YQBNJPACAUPNLV-UHFFFAOYSA-N         8616  \n",
       "3  YCUIAYUVYLNFFS-UHFFFAOYSA-N     21785456  \n",
       "4  ZQJTYJZLKBRKPC-UHFFFAOYSA-N      2228302  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pert_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "rows = list()\n",
    "\n",
    "for i, series in pert_df.iterrows():\n",
    "    # check InChI Keys\n",
    "    inchi = series.inchi_string\n",
    "    inchi_key = rdkit.Chem.inchi.InchiToInchiKey(inchi)\n",
    "    assert inchi_key == series.inchi_key\n",
    "    \n",
    "    # molecule\n",
    "    mol = rdkit.Chem.MolFromInchi(inchi)\n",
    "    \n",
    "    # fingerprint\n",
    "    fingerprint = rdkit.Chem.AllChem.GetMorganFingerprint(mol, 2)\n",
    "    \n",
    "    rows.append([series.pert_uid, inchi_key, inchi, mol, fingerprint])\n",
    "\n",
    "inchi_df = pandas.DataFrame(rows, columns=['pert_uid', 'inchi_key', 'inchi_string', 'mol', 'fingerprint'])\n",
    "inchi_df.sort('inchi_key', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>pert_uid</th>\n",
       "      <th>inchi_key</th>\n",
       "      <th>inchi_string</th>\n",
       "      <th>mol</th>\n",
       "      <th>fingerprint</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>9081</th>\n",
       "      <td>39929</td>\n",
       "      <td>AAALVYBICLMAMA-UHFFFAOYSA-N</td>\n",
       "      <td>InChI=1S/C20H15N3O2/c24-19-15-11-17(21-13-7-3-...</td>\n",
       "      <td>&lt;rdkit.Chem.rdchem.Mol object at 0x7fdfc0ee07b8&gt;</td>\n",
       "      <td>&lt;rdkit.DataStructs.cDataStructs.UIntSparseIntV...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2664</th>\n",
       "      <td>9777</td>\n",
       "      <td>AACFPJSJOWQNBN-UHFFFAOYSA-N</td>\n",
       "      <td>InChI=1S/C12H11NO3/c14-7-3-4-10-9(6-7)8-2-1-5-...</td>\n",
       "      <td>&lt;rdkit.Chem.rdchem.Mol object at 0x7fdfc18cf668&gt;</td>\n",
       "      <td>&lt;rdkit.DataStructs.cDataStructs.UIntSparseIntV...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>789</th>\n",
       "      <td>2195</td>\n",
       "      <td>AADCDMQTJNYOSS-LBPRGKRZSA-N</td>\n",
       "      <td>InChI=1S/C17H25ClN2O3/c1-4-11-9-13(18)16(23-3)...</td>\n",
       "      <td>&lt;rdkit.Chem.rdchem.Mol object at 0x7fdfc1917518&gt;</td>\n",
       "      <td>&lt;rdkit.DataStructs.cDataStructs.UIntSparseIntV...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16462</th>\n",
       "      <td>47318</td>\n",
       "      <td>AADVJQLQUVDEBP-GQIGUUNPSA-N</td>\n",
       "      <td>InChI=1S/C22H36N4O4/c1-14(2)23-22(28)24-17-8-9...</td>\n",
       "      <td>&lt;rdkit.Chem.rdchem.Mol object at 0x7fdfc0860828&gt;</td>\n",
       "      <td>&lt;rdkit.DataStructs.cDataStructs.UIntSparseIntV...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19637</th>\n",
       "      <td>50497</td>\n",
       "      <td>AADVJQLQUVDEBP-GUXCAODWSA-N</td>\n",
       "      <td>InChI=1S/C22H36N4O4/c1-14(2)23-22(28)24-17-8-9...</td>\n",
       "      <td>&lt;rdkit.Chem.rdchem.Mol object at 0x7fdfc06fdb38&gt;</td>\n",
       "      <td>&lt;rdkit.DataStructs.cDataStructs.UIntSparseIntV...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       pert_uid                    inchi_key  \\\n",
       "9081      39929  AAALVYBICLMAMA-UHFFFAOYSA-N   \n",
       "2664       9777  AACFPJSJOWQNBN-UHFFFAOYSA-N   \n",
       "789        2195  AADCDMQTJNYOSS-LBPRGKRZSA-N   \n",
       "16462     47318  AADVJQLQUVDEBP-GQIGUUNPSA-N   \n",
       "19637     50497  AADVJQLQUVDEBP-GUXCAODWSA-N   \n",
       "\n",
       "                                            inchi_string  \\\n",
       "9081   InChI=1S/C20H15N3O2/c24-19-15-11-17(21-13-7-3-...   \n",
       "2664   InChI=1S/C12H11NO3/c14-7-3-4-10-9(6-7)8-2-1-5-...   \n",
       "789    InChI=1S/C17H25ClN2O3/c1-4-11-9-13(18)16(23-3)...   \n",
       "16462  InChI=1S/C22H36N4O4/c1-14(2)23-22(28)24-17-8-9...   \n",
       "19637  InChI=1S/C22H36N4O4/c1-14(2)23-22(28)24-17-8-9...   \n",
       "\n",
       "                                                    mol  \\\n",
       "9081   <rdkit.Chem.rdchem.Mol object at 0x7fdfc0ee07b8>   \n",
       "2664   <rdkit.Chem.rdchem.Mol object at 0x7fdfc18cf668>   \n",
       "789    <rdkit.Chem.rdchem.Mol object at 0x7fdfc1917518>   \n",
       "16462  <rdkit.Chem.rdchem.Mol object at 0x7fdfc0860828>   \n",
       "19637  <rdkit.Chem.rdchem.Mol object at 0x7fdfc06fdb38>   \n",
       "\n",
       "                                             fingerprint  \n",
       "9081   <rdkit.DataStructs.cDataStructs.UIntSparseIntV...  \n",
       "2664   <rdkit.DataStructs.cDataStructs.UIntSparseIntV...  \n",
       "789    <rdkit.DataStructs.cDataStructs.UIntSparseIntV...  \n",
       "16462  <rdkit.DataStructs.cDataStructs.UIntSparseIntV...  \n",
       "19637  <rdkit.DataStructs.cDataStructs.UIntSparseIntV...  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "inchi_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# # Delete similarities table\n",
    "# cursor.execute('DELETE FROM similarities;')\n",
    "# connection.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": []
    }
   ],
   "source": [
    "pert_uids = [int(x) for x in inchi_df.pert_uid]\n",
    "fingerprints = list(inchi_df.fingerprint)\n",
    "\n",
    "for i, series in inchi_df.iterrows():\n",
    "    print(series.inchi_key, '\\r', end='')\n",
    "    similarities = rdkit.DataStructs.BulkDiceSimilarity(series.fingerprint, fingerprints)\n",
    "    similarities = [round(x, 4) for x in similarities]\n",
    "    rows = ((series.pert_uid, pert_uid, similarity) for pert_uid, similarity in zip(pert_uids, similarities))\n",
    "    cursor.executemany('INSERT INTO similarities VALUES (?,?,?)', rows)\n",
    "    connection.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# close database connection\n",
    "connection.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Example usage"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "connection = sqlite3.connect('file:data/l1000.db', uri=True)\n",
    "cursor = connection.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "def get_similarities(pert_ids, connection):\n",
    "    \"\"\"Retrieve chemical similarity scores between perts.\"\"\"\n",
    "    \n",
    "    cursor = connection.cursor()\n",
    "\n",
    "    pandas.DataFrame({'pert_id': pert_ids}).to_sql('pert_id_subset', connection, if_exists='replace', index=False)\n",
    "\n",
    "    query = \"\"\"\n",
    "    CREATE TEMPORARY TABLE perts_subset AS\n",
    "    SELECT *\n",
    "    FROM pert_id_subset\n",
    "    JOIN perts USING (pert_id);\n",
    "    \"\"\"\n",
    "    cursor.execute(query)\n",
    "\n",
    "    query = \"\"\"\n",
    "    SELECT P0.pert_id as pert_id_0, P1.pert_id as pert_id_1, chemical\n",
    "    FROM perts_subset P0, perts_subset P1, similarities\n",
    "    WHERE P0.pert_uid = similarities.pert_uid_0 AND P1.pert_uid = similarities.pert_uid_1\n",
    "    \"\"\"\n",
    "    similarity_df = pandas.read_sql(query, connection)\n",
    "\n",
    "    query = \"\"\"\n",
    "    DROP TABLE pert_id_subset;\n",
    "    DROP TABLE perts_subset;\n",
    "    \"\"\"\n",
    "    cursor.executescript(query)\n",
    "\n",
    "    return similarity_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>pert_id_0</th>\n",
       "      <th>pert_id_1</th>\n",
       "      <th>chemical</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BRD-K68741898</td>\n",
       "      <td>BRD-K68741898</td>\n",
       "      <td>1.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>BRD-K68741898</td>\n",
       "      <td>BRD-A05457250</td>\n",
       "      <td>0.1589</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BRD-A05457250</td>\n",
       "      <td>BRD-K68741898</td>\n",
       "      <td>0.1589</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BRD-A05457250</td>\n",
       "      <td>BRD-A05457250</td>\n",
       "      <td>1.0000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       pert_id_0      pert_id_1  chemical\n",
       "0  BRD-K68741898  BRD-K68741898    1.0000\n",
       "1  BRD-K68741898  BRD-A05457250    0.1589\n",
       "2  BRD-A05457250  BRD-K68741898    0.1589\n",
       "3  BRD-A05457250  BRD-A05457250    1.0000"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# test simple case\n",
    "pert_ids = ['BRD-K68741898', 'BRD-A05457250']\n",
    "get_similarities(pert_ids, connection)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>pert_id_0</th>\n",
       "      <th>pert_id_1</th>\n",
       "      <th>chemical</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1209995</th>\n",
       "      <td>BRD-A25143711</td>\n",
       "      <td>BRD-K99946902</td>\n",
       "      <td>0.2205</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1209996</th>\n",
       "      <td>BRD-A25143711</td>\n",
       "      <td>BRD-K86465814</td>\n",
       "      <td>0.2041</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1209997</th>\n",
       "      <td>BRD-A25143711</td>\n",
       "      <td>BRD-A77722753</td>\n",
       "      <td>0.0650</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1209998</th>\n",
       "      <td>BRD-A25143711</td>\n",
       "      <td>BRD-K02715688</td>\n",
       "      <td>0.1786</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1209999</th>\n",
       "      <td>BRD-A25143711</td>\n",
       "      <td>BRD-A25143711</td>\n",
       "      <td>1.0000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             pert_id_0      pert_id_1  chemical\n",
       "1209995  BRD-A25143711  BRD-K99946902    0.2205\n",
       "1209996  BRD-A25143711  BRD-K86465814    0.2041\n",
       "1209997  BRD-A25143711  BRD-A77722753    0.0650\n",
       "1209998  BRD-A25143711  BRD-K02715688    0.1786\n",
       "1209999  BRD-A25143711  BRD-A25143711    1.0000"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# test scalability\n",
    "pert_ids = pert_df.pert_id[:1100]\n",
    "large_df = get_similarities(pert_ids, connection)\n",
    "large_df.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>pert_uid_0</th>\n",
       "      <th>pert_uid_1</th>\n",
       "      <th>chemical</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>39929</td>\n",
       "      <td>39929</td>\n",
       "      <td>1.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>39929</td>\n",
       "      <td>9777</td>\n",
       "      <td>0.2689</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>39929</td>\n",
       "      <td>2195</td>\n",
       "      <td>0.1324</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>39929</td>\n",
       "      <td>47318</td>\n",
       "      <td>0.2078</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>39929</td>\n",
       "      <td>50497</td>\n",
       "      <td>0.2078</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   pert_uid_0  pert_uid_1  chemical\n",
       "0       39929       39929    1.0000\n",
       "1       39929        9777    0.2689\n",
       "2       39929        2195    0.1324\n",
       "3       39929       47318    0.2078\n",
       "4       39929       50497    0.2078"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# head of similarities table\n",
    "pandas.read_sql('SELECT * FROM similarities LIMIT 5', connection)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>pert_uid</th>\n",
       "      <th>pert_id</th>\n",
       "      <th>pert_iname</th>\n",
       "      <th>pert_type</th>\n",
       "      <th>num_gold</th>\n",
       "      <th>num_inst</th>\n",
       "      <th>num_sig</th>\n",
       "      <th>in_summly</th>\n",
       "      <th>inchi_string</th>\n",
       "      <th>inchi_key</th>\n",
       "      <th>pubchem_cid</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>CSS001-ATTGCAT</td>\n",
       "      <td>ATTGCAT</td>\n",
       "      <td>trt_sh.css</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>CSS001-GAGGATA</td>\n",
       "      <td>GAGGATA</td>\n",
       "      <td>trt_sh.css</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>CSS001-TCAATGA</td>\n",
       "      <td>TCAATGA</td>\n",
       "      <td>trt_sh.css</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>CSS001-TCAGTTC</td>\n",
       "      <td>TCAGTTC</td>\n",
       "      <td>trt_sh.css</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>CSS001-TCCATCA</td>\n",
       "      <td>TCCATCA</td>\n",
       "      <td>trt_sh.css</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   pert_uid         pert_id pert_iname   pert_type  num_gold  num_inst  \\\n",
       "0         1  CSS001-ATTGCAT    ATTGCAT  trt_sh.css         0         0   \n",
       "1         2  CSS001-GAGGATA    GAGGATA  trt_sh.css         0         0   \n",
       "2         3  CSS001-TCAATGA    TCAATGA  trt_sh.css         0         0   \n",
       "3         4  CSS001-TCAGTTC    TCAGTTC  trt_sh.css         0         0   \n",
       "4         5  CSS001-TCCATCA    TCCATCA  trt_sh.css         0         0   \n",
       "\n",
       "   num_sig  in_summly inchi_string inchi_key pubchem_cid  \n",
       "0        7          0         None      None        None  \n",
       "1        1          0         None      None        None  \n",
       "2        7          0         None      None        None  \n",
       "3        7          0         None      None        None  \n",
       "4        1          0         None      None        None  "
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# head of perts table\n",
    "pandas.read_sql('SELECT * FROM perts LIMIT 5', connection)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.4.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}