Data Cleaning FINAL-NCGC.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# ***RUN NOTEBOOK IN ORDER"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Dealing with multicomponent compounds"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Cleaning Full (antag)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "import requests\n",
    "from requests import exceptions\n",
    "import re\n",
    "%matplotlib inline\n",
    "\n",
    "import urllib\n",
    "from urllib.request import urlopen\n",
    "from bs4 import BeautifulSoup\n",
    "\n",
    "#hiding warning messages\n",
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Read the antagonist assay data into a data frame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2858, 114)"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Reading in Summary ANTAGONIST CSV\n",
    "antag_pre = pd.read_csv('AID_588533_datatable_all.csv')\n",
    "antag_pre = antag_pre.iloc[5:]\n",
    "antag_pre.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>PUBCHEM_CID</th>\n",
       "      <th>PUBCHEM_ACTIVITY_OUTCOME</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2860</th>\n",
       "      <td>10788.0</td>\n",
       "      <td>Inactive</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2861</th>\n",
       "      <td>12901.0</td>\n",
       "      <td>Inactive</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2862</th>\n",
       "      <td>6872.0</td>\n",
       "      <td>Inconclusive</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      PUBCHEM_CID PUBCHEM_ACTIVITY_OUTCOME\n",
       "2860      10788.0                 Inactive\n",
       "2861      12901.0                 Inactive\n",
       "2862       6872.0             Inconclusive"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "antag_pre = antag_pre[['PUBCHEM_CID', 'PUBCHEM_ACTIVITY_OUTCOME']]\n",
    "antag_pre.tail(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Deal with the NULL values: remove the rows without CIDs."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "PUBCHEM_CID                 14\n",
       "PUBCHEM_ACTIVITY_OUTCOME     0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "antag_pre.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2844, 2)"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "antag_pre = antag_pre[ antag_pre['PUBCHEM_CID'].notnull() ]\n",
    "antag_pre.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Convert the PUBCHEM_CID (floats) into an integer type."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>PUBCHEM_CID</th>\n",
       "      <th>Activity Summary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5995</td>\n",
       "      <td>Active</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>26041</td>\n",
       "      <td>Inconclusive</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>5281576</td>\n",
       "      <td>Inconclusive</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   PUBCHEM_CID Activity Summary\n",
       "5         5995           Active\n",
       "6        26041     Inconclusive\n",
       "7      5281576     Inconclusive"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "antag_pre['PUBCHEM_CID'] = antag_pre['PUBCHEM_CID'].astype(int)\n",
    "antag_pre = antag_pre.rename(columns = {'PUBCHEM_ACTIVITY_OUTCOME' : 'Activity Summary'})\n",
    "antag_pre.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Inactive        2066\n",
       "Inconclusive     712\n",
       "Active            66\n",
       "Name: Activity Summary, dtype: int64"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "antag_pre['Activity Summary'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2844, 2)"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "antag_pre.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>PUBCHEM_CID</th>\n",
       "      <th>Activity Summary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5995</td>\n",
       "      <td>Active</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>26041</td>\n",
       "      <td>Inconclusive</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>5281576</td>\n",
       "      <td>Inconclusive</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   PUBCHEM_CID Activity Summary\n",
       "5         5995           Active\n",
       "6        26041     Inconclusive\n",
       "7      5281576     Inconclusive"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "antag_pre.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Identifying Covalent Unit Count (antag)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Compute the number of CID chunks based on the number of CIDs and the chunk size."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "# CIDs =  2844\n",
      "# CID Chunks =  15 (chunked by  200 )\n"
     ]
    }
   ],
   "source": [
    "chunk_size = 200\n",
    "num_cids = len(antag_pre['PUBCHEM_CID'])\n",
    "\n",
    "if num_cids % chunk_size == 0 :\n",
    "    num_chunks = int( num_cids / chunk_size )\n",
    "else :\n",
    "    num_chunks = int( num_cids / chunk_size ) + 1\n",
    "\n",
    "print(\"# CIDs = \", num_cids)\n",
    "print(\"# CID Chunks = \", num_chunks, \"(chunked by \", chunk_size, \")\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Retrieve the covalent unit counts for each chunk of CIDs."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(2844, 1)\n"
     ]
    }
   ],
   "source": [
    "import time\n",
    "\n",
    "frames = []  # temporary list to store data frames from each PUG-REST request.\n",
    "\n",
    "#print(\"chunkid\", 'idx1', 'idx2', 'length', sep=\"\\t\")\n",
    "\n",
    "for i in range(0, num_chunks) :\n",
    "    \n",
    "    idx1 = chunk_size * i\n",
    "    idx2 = chunk_size * (i + 1)\n",
    "    cidstr = \",\".join(antag_pre.PUBCHEM_CID[idx1:idx2].astype(str).tolist())\n",
    "\n",
    "    url = ('https://pubchem.ncbi.nlm.nih.gov/rest/pug/compound/cid/' + cidstr + '/property/CovalentUnitCount/TXT')\n",
    "    data = pd.read_csv( urllib.request.urlopen(url), header=None, names=['cov_units'] )\n",
    "    frames.append(data)\n",
    "    \n",
    "    # Uncomment this line to check the progress\n",
    "    #print(i, idx1, idx2, len(data), sep=\"\\t\")\n",
    "    \n",
    "    if (i % 5 == 0 ) :\n",
    "        time.sleep(1)  # To comply with the PubChem usage polocy (no more than 5 requests per sec)\n",
    "\n",
    "#    if ( i == 2 ) : break  #- for debugging\n",
    "\n",
    "cov_units = pd.concat(frames,ignore_index=True)\n",
    "print(cov_units.shape)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Dealing with multicomponent molecules (antag)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "antag_pre['CovalentUnitCount'] = cov_units.cov_units.tolist()\n",
    "antag_pre.CovalentUnitCount.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1     2616\n",
       "2      159\n",
       "3       41\n",
       "5       16\n",
       "4        5\n",
       "9        3\n",
       "6        3\n",
       "10       1\n",
       "Name: CovalentUnitCount, dtype: int64"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "antag_pre['CovalentUnitCount'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "228"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cid_multicompo = antag_pre[ antag_pre.CovalentUnitCount > 1 ].PUBCHEM_CID.tolist()\n",
    "len(cid_multicompo)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The multicomponent CID list has some duplicate CIDs.  Removing them will reduce the number of PUG-REST requests later."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "31\n",
      "[5807, 5963, 8478, 8691, 8722, 8813, 11057, 11065, 11933, 13266, 15106, 22420, 22456, 23392, 62882, 108005, 443939, 2733525, 3032581, 3423265, 5284441, 5284484, 11074431, 11972286, 23668193, 23668195, 23668198, 23675274, 23678874, 44134384, 54680782]\n"
     ]
    }
   ],
   "source": [
    "cid_multicompo.sort()\n",
    " \n",
    "cid_multicompo_uniq = sorted( set(cid_multicompo) )\n",
    "cid_multicompo_dup = []\n",
    "    \n",
    "for mycid in cid_multicompo_uniq:\n",
    "        if ( cid_multicompo.count( mycid ) > 1 ):\n",
    "            cid_multicompo_dup.append( mycid )\n",
    "\n",
    "print(len(cid_multicompo_dup))\n",
    "print(cid_multicompo_dup)\n",
    "\n",
    "cid_multicompo = cid_multicompo_uniq"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Because downloading parent compound information through PUG-REST takes a long time, we want to store the data in a file when they are downloaded for later use. When the cell is run next time, it will read the data from the file, rather than downloading them from PubChem."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CID counts mismatch: 195 cid_multicompo 1748 cid_parent\n",
      "# len(cid_parent): 1748\n"
     ]
    }
   ],
   "source": [
    "import os.path\n",
    "import json\n",
    "\n",
    "cid_parent= {}\n",
    "file_parent = 'cid_parent.json'\n",
    "\n",
    "if os.path.isfile(file_parent) and os.path.getsize(file_parent) :\n",
    "    \n",
    "    with open('cid_parent.json') as f:\n",
    "        cid_parent = json.load(f)\n",
    "\n",
    "    # By default, the keys will be loaded as strings, but\n",
    "    # they are expected to be integers later in the notebook.\n",
    "    cid_parent = { int(k):v for k,v in cid_parent.items()}\n",
    "        \n",
    "    if ( len(cid_parent) != len(cid_multicompo) ) :\n",
    "        print(\"CID counts mismatch:\", len(cid_multicompo), \"cid_multicompo\", len(cid_parent), \"cid_parent\")\n",
    "\n",
    "else:\n",
    "\n",
    "    for i in range(0, len(cid_multicompo)) :\n",
    "\n",
    "        try:\n",
    "        \n",
    "            mycid = cid_multicompo[i]\n",
    "            url = ('https://pubchem.ncbi.nlm.nih.gov/rest/pug/compound/cid/' + str(mycid) + '/cids/TXT?cids_type=parent')\n",
    "            res = urllib.request.urlopen(url)\n",
    "            parent = res.read()\n",
    "            cid_parent[ mycid ] = int(parent.decode().rstrip())\n",
    "    \n",
    "        except:\n",
    "    \n",
    "            cid_parent[ mycid ] = None\n",
    "    \n",
    "        if (i % 5 == 0 ) : time.sleep(1)\n",
    "        if (i % 200 == 0 ) : print(\"processing \", i , \"of \", len(cid_multicompo))\n",
    "\n",
    "    print(\"# Download of parent CIDs has been complete\")\n",
    "\n",
    "    with open('cid_parent.json', 'w') as f:\n",
    "        json.dump(cid_parent, f)\n",
    "    \n",
    "print(\"# len(cid_parent):\", len(cid_parent) )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "207\n"
     ]
    }
   ],
   "source": [
    "# Store CIDs without parent compounds.\n",
    "cid_noparent = [ key for key,val in cid_parent.items() if not val  ]\n",
    "print(len(cid_noparent))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(228, 3)"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Making copy of antag_pre\n",
    "antag_cov = antag_pre\n",
    "\n",
    "#dropping rows where covalent unit count is = 1\n",
    "antag_cov = antag_cov[antag_cov.CovalentUnitCount != 1]\n",
    "antag_cov.shape\n",
    "#228 multicomponent CIDs (2844 total)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "#getting CID list of multicomponent compounds\n",
    "CID_list_multi = antag_cov['PUBCHEM_CID'].tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "228"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Getting list of covalent unit number for the multicomponent compounds\n",
    "list_cov_multi = antag_cov['CovalentUnitCount'].tolist()\n",
    "\n",
    "parent_CID_list = []\n",
    "i = 0\n",
    "while i < len(CID_list_multi):\n",
    "    try:\n",
    "    # (list_cov_multi[i] == '2'):\n",
    "        url1 = 'https://pubchem.ncbi.nlm.nih.gov/rest/pug/compound/cid/' + str(CID_list_multi[i]) + '/cids/TXT?cids_type=parent'\n",
    "        html1 = urlopen(url1) \n",
    "        soup1 = BeautifulSoup(html1, 'lxml')\n",
    "        parent1 = soup1.get_text()\n",
    "        #print(parent1)\n",
    "        parent_CID_list.append(parent1)\n",
    "        i = i + 1\n",
    "       # else:\n",
    "        #    i = i + 1\n",
    "    except:\n",
    "        #contine\n",
    "        #print('eek')\n",
    "        #if err.code == ('404: PUGREST.NotFound'):\n",
    "        parent_CID_list.append('0')\n",
    "        i = i + 1\n",
    "        \n",
    "\n",
    "\n",
    "\n",
    "len(parent_CID_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Removing '\\n from the end of each CID in parent_CID_list'\n",
    "bad_chars = ['\\n']\n",
    "\n",
    "i = 0\n",
    "while i < len(parent_CID_list):\n",
    "    if parent_CID_list[i] == '0':\n",
    "        i = i + 1\n",
    "    else:\n",
    "        for j in bad_chars: \n",
    "            parent_CID_list[i] = parent_CID_list[i].replace(j, '')\n",
    "            i = i + 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "#adding parent_CID's to antag_cov DF\n",
    "antag_cov['Parent_CIDs'] = parent_CID_list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "#now that we have a list of the parent compounds for each multiple component molecule we:\n",
    "#delete all values with a parent of 0\n",
    "#make a new df called antag_multi_to_parent\n",
    "antag_multi_to_parent = antag_cov.drop(antag_cov.loc[antag_cov['Parent_CIDs'] == '0'].index)\n",
    "\n",
    "##replace PUBCHEM_CID with parent CID's\n",
    "###Drop CID column and rename Parent_CID's to just PUBCHEM_CID\n",
    "antag_multi_to_parent = antag_multi_to_parent.drop(columns = ['PUBCHEM_CID'], axis = 1)\n",
    "antag_multi_to_parent = antag_multi_to_parent.rename(index=str, columns={\"Parent_CIDs\" : \"PUBCHEM_CID\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Removing all CID's with multicomponent compounds from antag_pre (original DF of all antag compounds)\n",
    "antag_pre_minus_multi = antag_pre[~antag_pre.PUBCHEM_CID.isin(CID_list_multi)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Combining antag_pre with antag_multi_to_parent\n",
    "antag = antag_pre_minus_multi.append(antag_multi_to_parent)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2811, 3)"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "antag_pre = antag\n",
    "antag_pre.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Dealing with conflicts (antag)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Inactive        2046\n",
       "Inconclusive     702\n",
       "Active            63\n",
       "Name: Activity Summary, dtype: int64"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "antag_pre['Activity Summary'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "5        5995\n",
       "7     5281576\n",
       "8     5281575\n",
       "9     5280795\n",
       "10       7529\n",
       "11       8419\n",
       "13      10215\n",
       "14      61186\n",
       "15       8061\n",
       "16    6440940\n",
       "Name: PUBCHEM_CID, dtype: object"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "antag_pre[ 'PUBCHEM_CID'].head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Generate unique CID list\n",
    "antag_cid = antag_pre['PUBCHEM_CID'].tolist()\n",
    "\n",
    "# Drop the CIDs with conflicting activity declaration.\n",
    "for mycid in antag_cid :\n",
    "    \n",
    "    activities = set( antag_pre[ antag_pre['PUBCHEM_CID'] == mycid].loc[:,'Activity Summary'].tolist())\n",
    "    if ( len(activities) > 1 ):\n",
    "        #print(mycid, len(activities), activities)\n",
    "        antag_pre = antag_pre[ antag_pre['PUBCHEM_CID'] != mycid ]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2484, 3)"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "antag_pre.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Inactive        1887\n",
       "Inconclusive     550\n",
       "Active            47\n",
       "Name: Activity Summary, dtype: int64"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "antag_pre['Activity Summary'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Cleaning Full (ag)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Read the antagonist assay data into a data frame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "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>PUBCHEM_RESULT_TAG</th>\n",
       "      <th>PUBCHEM_SID</th>\n",
       "      <th>PUBCHEM_CID</th>\n",
       "      <th>PUBCHEM_ACTIVITY_OUTCOME</th>\n",
       "      <th>PUBCHEM_ACTIVITY_SCORE</th>\n",
       "      <th>PUBCHEM_ACTIVITY_URL</th>\n",
       "      <th>PUBCHEM_ASSAYDATA_COMMENT</th>\n",
       "      <th>Phenotype</th>\n",
       "      <th>Potency</th>\n",
       "      <th>Efficacy</th>\n",
       "      <th>...</th>\n",
       "      <th>W530-Activity at 76.79 uM</th>\n",
       "      <th>W530-Activity at 182.0 uM</th>\n",
       "      <th>W530-Activity at 407.0 uM</th>\n",
       "      <th>W530-Activity at 910.0 uM</th>\n",
       "      <th>W530-Activity at 2034.7 uM</th>\n",
       "      <th>W530-Activity at 4832.4 uM</th>\n",
       "      <th>W530-Activity at 10805.3 uM</th>\n",
       "      <th>W530-Activity at 24160.5 uM</th>\n",
       "      <th>W530-Activity at 54023.0 uM</th>\n",
       "      <th>Compound QC</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1</td>\n",
       "      <td>11112138.0</td>\n",
       "      <td>5995.0</td>\n",
       "      <td>Inconclusive</td>\n",
       "      <td>10.0</td>\n",
       "      <td>http://assay.nih.gov/htsws/rest/display/nucrec...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Inconclusive</td>\n",
       "      <td>25.1189</td>\n",
       "      <td>40.4377</td>\n",
       "      <td>...</td>\n",
       "      <td>6.7296</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>QC'd by Prestwick</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2</td>\n",
       "      <td>17388662.0</td>\n",
       "      <td>26041.0</td>\n",
       "      <td>Inconclusive</td>\n",
       "      <td>10.0</td>\n",
       "      <td>http://assay.nih.gov/htsws/rest/display/nucrec...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Inconclusive</td>\n",
       "      <td>1.4125</td>\n",
       "      <td>34.0858</td>\n",
       "      <td>...</td>\n",
       "      <td>-15.5195</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>QC'd by NIEHS/NTP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>3</td>\n",
       "      <td>17388663.0</td>\n",
       "      <td>5281576.0</td>\n",
       "      <td>Inactive</td>\n",
       "      <td>0.0</td>\n",
       "      <td>http://assay.nih.gov/htsws/rest/display/nucrec...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Inactive</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>-20.9504</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>QC'd by NIEHS/NTP</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3 rows × 114 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  PUBCHEM_RESULT_TAG  PUBCHEM_SID  PUBCHEM_CID PUBCHEM_ACTIVITY_OUTCOME  \\\n",
       "5                  1   11112138.0       5995.0             Inconclusive   \n",
       "6                  2   17388662.0      26041.0             Inconclusive   \n",
       "7                  3   17388663.0    5281576.0                 Inactive   \n",
       "\n",
       "   PUBCHEM_ACTIVITY_SCORE                               PUBCHEM_ACTIVITY_URL  \\\n",
       "5                    10.0  http://assay.nih.gov/htsws/rest/display/nucrec...   \n",
       "6                    10.0  http://assay.nih.gov/htsws/rest/display/nucrec...   \n",
       "7                     0.0  http://assay.nih.gov/htsws/rest/display/nucrec...   \n",
       "\n",
       "   PUBCHEM_ASSAYDATA_COMMENT     Phenotype  Potency Efficacy  ...  \\\n",
       "5                        NaN  Inconclusive  25.1189  40.4377  ...   \n",
       "6                        NaN  Inconclusive   1.4125  34.0858  ...   \n",
       "7                        NaN      Inactive      NaN      NaN  ...   \n",
       "\n",
       "  W530-Activity at 76.79 uM W530-Activity at 182.0 uM  \\\n",
       "5                    6.7296                       NaN   \n",
       "6                  -15.5195                       NaN   \n",
       "7                  -20.9504                       NaN   \n",
       "\n",
       "  W530-Activity at 407.0 uM W530-Activity at 910.0 uM  \\\n",
       "5                       NaN                       NaN   \n",
       "6                       NaN                       NaN   \n",
       "7                       NaN                       NaN   \n",
       "\n",
       "  W530-Activity at 2034.7 uM W530-Activity at 4832.4 uM  \\\n",
       "5                        NaN                        NaN   \n",
       "6                        NaN                        NaN   \n",
       "7                        NaN                        NaN   \n",
       "\n",
       "  W530-Activity at 10805.3 uM W530-Activity at 24160.5 uM  \\\n",
       "5                         NaN                         NaN   \n",
       "6                         NaN                         NaN   \n",
       "7                         NaN                         NaN   \n",
       "\n",
       "  W530-Activity at 54023.0 uM        Compound QC  \n",
       "5                         NaN  QC'd by Prestwick  \n",
       "6                         NaN  QC'd by NIEHS/NTP  \n",
       "7                         NaN  QC'd by NIEHS/NTP  \n",
       "\n",
       "[3 rows x 114 columns]"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Reading in Summary ANTAGONIST CSV\n",
    "ag_pre = pd.read_csv('AID_588532_datatable_all.csv')\n",
    "ag_pre = ag_pre.iloc[5:]\n",
    "ag_pre.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "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>PUBCHEM_CID</th>\n",
       "      <th>PUBCHEM_ACTIVITY_OUTCOME</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5995.0</td>\n",
       "      <td>Inconclusive</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>26041.0</td>\n",
       "      <td>Inconclusive</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>5281576.0</td>\n",
       "      <td>Inactive</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   PUBCHEM_CID PUBCHEM_ACTIVITY_OUTCOME\n",
       "5       5995.0             Inconclusive\n",
       "6      26041.0             Inconclusive\n",
       "7    5281576.0                 Inactive"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ag_pre = ag_pre[['PUBCHEM_CID', 'PUBCHEM_ACTIVITY_OUTCOME']]\n",
    "ag_pre.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Deal with the NULL values: remove the rows without CIDs."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "PUBCHEM_CID                 14\n",
       "PUBCHEM_ACTIVITY_OUTCOME     0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ag_pre.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2844, 2)"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ag_pre = ag_pre[ ag_pre['PUBCHEM_CID'].notnull() ]\n",
    "ag_pre.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "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>PUBCHEM_CID</th>\n",
       "      <th>Activity Summary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5995</td>\n",
       "      <td>Inconclusive</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>26041</td>\n",
       "      <td>Inconclusive</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>5281576</td>\n",
       "      <td>Inactive</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   PUBCHEM_CID Activity Summary\n",
       "5         5995     Inconclusive\n",
       "6        26041     Inconclusive\n",
       "7      5281576         Inactive"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ag_pre['PUBCHEM_CID'] = ag_pre['PUBCHEM_CID'].astype(int)\n",
    "ag_pre = ag_pre.rename(columns = {'PUBCHEM_ACTIVITY_OUTCOME' : 'Activity Summary'})\n",
    "ag_pre.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Convert the PUBCHEM_CID (floats) into an integer type."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "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>PUBCHEM_CID</th>\n",
       "      <th>Activity Summary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5995</td>\n",
       "      <td>Inconclusive</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>26041</td>\n",
       "      <td>Inconclusive</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>5281576</td>\n",
       "      <td>Inactive</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   PUBCHEM_CID Activity Summary\n",
       "5         5995     Inconclusive\n",
       "6        26041     Inconclusive\n",
       "7      5281576         Inactive"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ag_pre['PUBCHEM_CID'] = ag_pre['PUBCHEM_CID'].astype(int)\n",
    "ag_pre.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Inactive        2398\n",
       "Inconclusive     438\n",
       "Active             8\n",
       "Name: Activity Summary, dtype: int64"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ag_pre['Activity Summary'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2844, 2)"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ag_pre.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "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>PUBCHEM_CID</th>\n",
       "      <th>Activity Summary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5995</td>\n",
       "      <td>Inconclusive</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>26041</td>\n",
       "      <td>Inconclusive</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>5281576</td>\n",
       "      <td>Inactive</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   PUBCHEM_CID Activity Summary\n",
       "5         5995     Inconclusive\n",
       "6        26041     Inconclusive\n",
       "7      5281576         Inactive"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ag_pre.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Identifying Covalent Unit Count (ag)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Compute the number of CID chunks based on the number of CIDs and the chunk size."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "# CIDs =  2844\n",
      "# CID Chunks =  15 (chunked by  200 )\n"
     ]
    }
   ],
   "source": [
    "chunk_size = 200\n",
    "num_cids = len(ag_pre['PUBCHEM_CID'])\n",
    "\n",
    "if num_cids % chunk_size == 0 :\n",
    "    num_chunks = int( num_cids / chunk_size )\n",
    "else :\n",
    "    num_chunks = int( num_cids / chunk_size ) + 1\n",
    "\n",
    "print(\"# CIDs = \", num_cids)\n",
    "print(\"# CID Chunks = \", num_chunks, \"(chunked by \", chunk_size, \")\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Retrieve the covalent unit counts for each chunk of CIDs."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(2844, 1)\n"
     ]
    }
   ],
   "source": [
    "import time\n",
    "\n",
    "frames = []  # temporary list to store data frames from each PUG-REST request.\n",
    "\n",
    "#print(\"chunkid\", 'idx1', 'idx2', 'length', sep=\"\\t\")\n",
    "\n",
    "for i in range(0, num_chunks) :\n",
    "    \n",
    "    idx1 = chunk_size * i\n",
    "    idx2 = chunk_size * (i + 1)\n",
    "    cidstr = \",\".join(ag_pre.PUBCHEM_CID[idx1:idx2].astype(str).tolist())\n",
    "\n",
    "    url = ('https://pubchem.ncbi.nlm.nih.gov/rest/pug/compound/cid/' + cidstr + '/property/CovalentUnitCount/TXT')\n",
    "    data = pd.read_csv( urllib.request.urlopen(url), header=None, names=['cov_units'] )\n",
    "    frames.append(data)\n",
    "    \n",
    "    # Uncomment this line to check the progress\n",
    "    #print(i, idx1, idx2, len(data), sep=\"\\t\")\n",
    "    \n",
    "    if (i % 5 == 0 ) :\n",
    "        time.sleep(1)  # To comply with the PubChem usage polocy (no more than 5 requests per sec)\n",
    "\n",
    "#    if ( i == 2 ) : break  #- for debugging\n",
    "\n",
    "cov_units = pd.concat(frames,ignore_index=True)\n",
    "print(cov_units.shape)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Dealing with Multicomponent Molecules (ag)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ag_pre['CovalentUnitCount'] = cov_units.cov_units.tolist()\n",
    "ag_pre.CovalentUnitCount.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1     2616\n",
       "2      159\n",
       "3       41\n",
       "5       16\n",
       "4        5\n",
       "9        3\n",
       "6        3\n",
       "10       1\n",
       "Name: CovalentUnitCount, dtype: int64"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ag_pre['CovalentUnitCount'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "228"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cid_multicompo = ag_pre[ ag_pre.CovalentUnitCount > 1 ].PUBCHEM_CID.tolist()\n",
    "len(cid_multicompo)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The multicomponent CID list has some duplicate CIDs.  Removing them will reduce the number of PUG-REST requests later."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "31\n",
      "[5807, 5963, 8478, 8691, 8722, 8813, 11057, 11065, 11933, 13266, 15106, 22420, 22456, 23392, 62882, 108005, 443939, 2733525, 3032581, 3423265, 5284441, 5284484, 11074431, 11972286, 23668193, 23668195, 23668198, 23675274, 23678874, 44134384, 54680782]\n"
     ]
    }
   ],
   "source": [
    "cid_multicompo.sort()\n",
    " \n",
    "cid_multicompo_uniq = sorted( set(cid_multicompo) )\n",
    "cid_multicompo_dup = []\n",
    "    \n",
    "for mycid in cid_multicompo_uniq:\n",
    "        if ( cid_multicompo.count( mycid ) > 1 ):\n",
    "            cid_multicompo_dup.append( mycid )\n",
    "\n",
    "print(len(cid_multicompo_dup))\n",
    "print(cid_multicompo_dup)\n",
    "\n",
    "cid_multicompo = cid_multicompo_uniq"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Because downloading parent compound information through PUG-REST takes a long time, we want to store the data in a file when they are downloaded for later use. When the cell is run next time, it will read the data from the file, rather than downloading them from PubChem."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CID counts mismatch: 195 cid_multicompo 1748 cid_parent\n",
      "# len(cid_parent): 1748\n"
     ]
    }
   ],
   "source": [
    "import os.path\n",
    "import json\n",
    "\n",
    "cid_parent= {}\n",
    "file_parent = 'cid_parent_ag.json'\n",
    "\n",
    "if os.path.isfile(file_parent) and os.path.getsize(file_parent) :\n",
    "    \n",
    "    with open(file_parent) as f:\n",
    "        cid_parent = json.load(f)\n",
    "\n",
    "    # By default, the keys will be loaded as strings, but\n",
    "    # they are expected to be integers later in the notebook.\n",
    "    cid_parent = { int(k):v for k,v in cid_parent.items()}\n",
    "        \n",
    "    if ( len(cid_parent) != len(cid_multicompo) ) :\n",
    "        print(\"CID counts mismatch:\", len(cid_multicompo), \"cid_multicompo\", len(cid_parent), \"cid_parent\")\n",
    "\n",
    "else:\n",
    "\n",
    "    for i in range(0, len(cid_multicompo)) :\n",
    "\n",
    "        try:\n",
    "        \n",
    "            mycid = cid_multicompo[i]\n",
    "            url = ('https://pubchem.ncbi.nlm.nih.gov/rest/pug/compound/cid/' + str(mycid) + '/cids/TXT?cids_type=parent')\n",
    "            res = urllib.request.urlopen(url)\n",
    "            parent = res.read()\n",
    "            cid_parent[ mycid ] = int(parent.decode().rstrip())\n",
    "    \n",
    "        except:\n",
    "    \n",
    "            cid_parent[ mycid ] = None\n",
    "    \n",
    "        if (i % 5 == 0 ) : time.sleep(1)\n",
    "        if (i % 200 == 0 ) : print(\"processing \", i , \"of \", len(cid_multicompo))\n",
    "\n",
    "    print(\"# Download of parent CIDs has been complete\")\n",
    "\n",
    "    with open(file_parent, 'w') as f:\n",
    "        json.dump(cid_parent, f)\n",
    "    \n",
    "print(\"# len(cid_parent):\", len(cid_parent) )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "220\n"
     ]
    }
   ],
   "source": [
    "# Store CIDs without parent compounds.\n",
    "cid_noparent = [ key for key,val in cid_parent.items() if not val  ]\n",
    "print(len(cid_noparent))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(228, 3)"
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Making copy of antag_pre\n",
    "ag_cov = ag_pre\n",
    "\n",
    "#dropping rows where covalent unit count is = 1\n",
    "ag_cov = ag_cov[ag_cov.CovalentUnitCount != 1]\n",
    "ag_cov.shape\n",
    "#228 multicomponent CIDs (2844 total)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [],
   "source": [
    "#getting CID list of multicomponent compounds\n",
    "CID_list_multi = ag_cov['PUBCHEM_CID'].tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "228"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Getting list of covalent unit number for the multicomponent compounds\n",
    "list_cov_multi = ag_cov['CovalentUnitCount'].tolist()\n",
    "\n",
    "parent_CID_list = []\n",
    "i = 0\n",
    "while i < len(CID_list_multi):\n",
    "    try:\n",
    "    # (list_cov_multi[i] == '2'):\n",
    "        url1 = 'https://pubchem.ncbi.nlm.nih.gov/rest/pug/compound/cid/' + str(CID_list_multi[i]) + '/cids/TXT?cids_type=parent'\n",
    "        html1 = urlopen(url1) \n",
    "        soup1 = BeautifulSoup(html1, 'lxml')\n",
    "        parent1 = soup1.get_text()\n",
    "        #print(parent1)\n",
    "        parent_CID_list.append(parent1)\n",
    "        i = i + 1\n",
    "       # else:\n",
    "        #    i = i + 1\n",
    "    except:\n",
    "        #contine\n",
    "        #print('eek')\n",
    "        #if err.code == ('404: PUGREST.NotFound'):\n",
    "        parent_CID_list.append('0')\n",
    "        i = i + 1\n",
    "        \n",
    "\n",
    "\n",
    "\n",
    "len(parent_CID_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Removing '\\n from the end of each CID in parent_CID_list'\n",
    "bad_chars = ['\\n']\n",
    "\n",
    "i = 0\n",
    "while i < len(parent_CID_list):\n",
    "    if parent_CID_list[i] == '0':\n",
    "        i = i + 1\n",
    "    else:\n",
    "        for j in bad_chars: \n",
    "            parent_CID_list[i] = parent_CID_list[i].replace(j, '')\n",
    "            i = i + 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [],
   "source": [
    "#adding parent_CID's to antag_cov DF\n",
    "ag_cov['Parent_CIDs'] = parent_CID_list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [],
   "source": [
    "#now that we have a list of the parent compounds for each multiple component molecule we:\n",
    "#delete all values with a parent of 0\n",
    "#make a new df called antag_multi_to_parent\n",
    "ag_multi_to_parent = ag_cov.drop(ag_cov.loc[ag_cov['Parent_CIDs'] == '0'].index)\n",
    "\n",
    "##replace PUBCHEM_CID with parent CID's\n",
    "###Drop CID column and rename Parent_CID's to just PUBCHEM_CID\n",
    "ag_multi_to_parent = ag_multi_to_parent.drop(columns = ['PUBCHEM_CID'], axis = 1)\n",
    "ag_multi_to_parent = ag_multi_to_parent.rename(index=str, columns={\"Parent_CIDs\" : \"PUBCHEM_CID\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Removing all CID's with multicomponent compounds from antag_pre (original DF of all antag compounds)\n",
    "ag_pre_minus_multi = ag_pre[~ag_pre.PUBCHEM_CID.isin(CID_list_multi)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Combining ag_pre with ag_multi_to_parent\n",
    "ag = ag_pre_minus_multi.append(ag_multi_to_parent)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2616, 3)"
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ag_pre = ag\n",
    "ag_pre.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Dealing with conflicts (ag)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Activity Summary     0\n",
       "CovalentUnitCount    0\n",
       "PUBCHEM_CID          0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ag_pre.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Inactive        2218\n",
       "Inconclusive     391\n",
       "Active             7\n",
       "Name: Activity Summary, dtype: int64"
      ]
     },
     "execution_count": 95,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ag_pre['Activity Summary'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Inactive        2218\n",
       "Inconclusive     391\n",
       "Active             7\n",
       "Name: Activity Summary, dtype: int64"
      ]
     },
     "execution_count": 96,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ag_pre['Activity Summary'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "5        5995.0\n",
       "7     5281576.0\n",
       "8     5281575.0\n",
       "9     5280795.0\n",
       "10       7529.0\n",
       "11       8419.0\n",
       "13      10215.0\n",
       "14      61186.0\n",
       "15       8061.0\n",
       "16    6440940.0\n",
       "Name: PUBCHEM_CID, dtype: float64"
      ]
     },
     "execution_count": 97,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ag_pre[ 'PUBCHEM_CID'].head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Generate unique CID list\n",
    "ag_cid = ag_pre['PUBCHEM_CID'].tolist()\n",
    "\n",
    "# Drop the CIDs with conflicting activity declaration.\n",
    "for mycid in ag_cid :\n",
    "    \n",
    "    activities = set( ag_pre[ ag_pre['PUBCHEM_CID'] == mycid].loc[:,'Activity Summary'].tolist())\n",
    "    if ( len(activities) > 1 ):\n",
    "        #print(mycid, len(activities), activities)\n",
    "        ag_pre = ag_pre[ ag_pre['PUBCHEM_CID'] != mycid ]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "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>Activity Summary</th>\n",
       "      <th>CovalentUnitCount</th>\n",
       "      <th>PUBCHEM_CID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Inconclusive</td>\n",
       "      <td>1</td>\n",
       "      <td>5995.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Inconclusive</td>\n",
       "      <td>1</td>\n",
       "      <td>5281575.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Inactive</td>\n",
       "      <td>1</td>\n",
       "      <td>5280795.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Activity Summary  CovalentUnitCount  PUBCHEM_CID\n",
       "5     Inconclusive                  1       5995.0\n",
       "8     Inconclusive                  1    5281575.0\n",
       "9         Inactive                  1    5280795.0"
      ]
     },
     "execution_count": 99,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ag_pre.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2376, 3)"
      ]
     },
     "execution_count": 100,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ag_pre.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Inactive        2088\n",
       "Inconclusive     284\n",
       "Active             4\n",
       "Name: Activity Summary, dtype: int64"
      ]
     },
     "execution_count": 108,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ag_pre['Activity Summary'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Combining Dataframes and Cleaning (both)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Method 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 246,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2275"
      ]
     },
     "execution_count": 246,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "antag_cid = antag_pre['PUBCHEM_CID'].tolist()\n",
    "ag_cid = ag_pre['PUBCHEM_CID'].tolist()\n",
    "all_cid = list(set( antag_cid + ag_cid ))\n",
    "len(all_cid)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 254,
   "metadata": {},
   "outputs": [
    {
     "ename": "ValueError",
     "evalue": "No axis named PUBCHEM_CID for object type <class 'type'>",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mValueError\u001b[0m                                Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-254-14d87f304d5b>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m     12\u001b[0m \u001b[0mdf_activity\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mpd\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mDataFrame\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mfinal_act\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mitems\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcolumns\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'PUBCHEM_CID'\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;34m'Activity Summary'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     13\u001b[0m \u001b[0mdf_activity\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mdf_activity\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'PUBCHEM_CID'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mastype\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mint\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 14\u001b[1;33m \u001b[0mdf_activity\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mdf_activity\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0msort_values\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'PUBCHEM_CID'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mreset_index\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mdrop\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;32mTrue\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m     15\u001b[0m df_activity = df_activity[ (df_activity['Activity Summary'] == 'Active') | \\\n\u001b[0;32m     16\u001b[0m                            \u001b[1;33m(\u001b[0m\u001b[0mdf_activity\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'Activity Summary'\u001b[0m\u001b[1;33m]\u001b[0m \u001b[1;33m==\u001b[0m \u001b[1;34m'Active'\u001b[0m \u001b[1;33m)\u001b[0m \u001b[1;33m|\u001b[0m\u001b[0;31m \u001b[0m\u001b[0;31m\\\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\users\\singhs29\\appdata\\local\\continuum\\anaconda3\\envs\\my-rdkit-env\\lib\\site-packages\\pandas\\core\\series.py\u001b[0m in \u001b[0;36msort_values\u001b[1;34m(self, axis, ascending, inplace, kind, na_position)\u001b[0m\n\u001b[0;32m   2774\u001b[0m         \u001b[0minplace\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mvalidate_bool_kwarg\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0minplace\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;34m'inplace'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   2775\u001b[0m         \u001b[1;31m# Validate the axis parameter\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 2776\u001b[1;33m         \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_get_axis_number\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0maxis\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   2777\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   2778\u001b[0m         \u001b[1;31m# GH 5856/5853\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\users\\singhs29\\appdata\\local\\continuum\\anaconda3\\envs\\my-rdkit-env\\lib\\site-packages\\pandas\\core\\generic.py\u001b[0m in \u001b[0;36m_get_axis_number\u001b[1;34m(cls, axis)\u001b[0m\n\u001b[0;32m    359\u001b[0m                 \u001b[1;32mpass\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    360\u001b[0m         raise ValueError('No axis named {0} for object type {1}'\n\u001b[1;32m--> 361\u001b[1;33m                          .format(axis, type(cls)))\n\u001b[0m\u001b[0;32m    362\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    363\u001b[0m     \u001b[1;33m@\u001b[0m\u001b[0mclassmethod\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mValueError\u001b[0m: No axis named PUBCHEM_CID for object type <class 'type'>"
     ]
    }
   ],
   "source": [
    "final_act = {}\n",
    "\n",
    "for mycid in list(all_cid) :\n",
    "    \n",
    "    antag_act = list(set( antag_pre[ antag_pre['PUBCHEM_CID'] == mycid].loc[:,'Activity Summary'].tolist()))\n",
    "    ag_act = list(set( ag_pre[ ag_pre['PUBCHEM_CID'] == mycid].loc[:,'Activity Summary'].tolist()))\n",
    "    all_act = list(set( antag_act + ag_act ))\n",
    "    \n",
    "    if ( len(all_act) == 1) :\n",
    "        final_act[ mycid ] = all_act[0]\n",
    "\n",
    "df_activity = pd.DataFrame(final_act.items(), columns=['PUBCHEM_CID', 'Activity Summary'])\n",
    "df_activity = df_activity['PUBCHEM_CID'].astype(int)\n",
    "df_activity = df_activity.sort_values('PUBCHEM_CID').reset_index(drop=True)\n",
    "df_activity = df_activity[ (df_activity['Activity Summary'] == 'Active') | \\\n",
    "                           (df_activity['Activity Summary'] == 'Active' ) | \\\n",
    "                           (df_activity['Activity Summary'] == 'Inactive') ]\n",
    "df_activity['Activity Summary'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Method 2 - USING"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "#antag_cid_df = antag_pre[ antag_pre['Activity Summary'] == 'active antagonist' ]\n",
    "#ag_cid_df = ag_pre[ ag_pre['Activity Summary'] == 'active agonist' ]\n",
    "#inact_cid_df_antag = antag_pre[ antag_pre['Activity Summary'] == 'inactive' ]\n",
    "#inact_cid_df_ag = ag_pre[ ag_pre['Activity Summary'] == 'inactive' ]\n",
    "\n",
    "#df_activity2 = pd.concat([ antag_cid_df, ag_cid_df, inact_cid_df_antag, inact_cid_df_ag])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 280,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4"
      ]
     },
     "execution_count": 280,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(cids_ago)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 291,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Inactive    2101\n",
       "Active        23\n",
       "Name: Activity Summary, dtype: int64"
      ]
     },
     "execution_count": 291,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cids_ant = antag_pre[ antag_pre['Activity Summary'] == 'Active' ].PUBCHEM_CID.tolist()\n",
    "cids_ago = ag_pre[ ag_pre['Activity Summary'] == 'Active' ].PUBCHEM_CID.tolist()\n",
    "cids_inact1 = antag_pre[ antag_pre['Activity Summary'] == 'Inactive' ].PUBCHEM_CID.tolist()\n",
    "cids_inact2 = ag_pre[ ag_pre['Activity Summary'] == 'Inactive' ].PUBCHEM_CID.tolist()\n",
    "\n",
    "#cids_inact = [i for i in set(cids_inact1) if i in cids_inact2]\n",
    "cids_inact = cids_inact1 + cids_inact2\n",
    "\n",
    "final_act = {}\n",
    "\n",
    "for mycid in cids_ant :\n",
    "    final_act[ mycid ] = 'Active'\n",
    "\n",
    "    \n",
    "for mycid in cids_ago :\n",
    "    final_act[ mycid ] = 'Active'\n",
    "    \n",
    "\n",
    "for mycid in cids_inact :\n",
    "    final_act[ mycid ] = 'Inactive'\n",
    "\n",
    "df_activity2 = pd.DataFrame(final_act.items(), columns=['PUBCHEM_CID', 'Activity Summary']) \n",
    "df_activity2['PUBCHEM_CID'] = df_activity2['PUBCHEM_CID'].astype(int)    \n",
    "#df_activity2 = df_activity2.duplicated('PUBCHEM_CID', keep = 'first')\n",
    "df_activity2 = df_activity2.sort_values('PUBCHEM_CID').reset_index(drop=True)\n",
    "df_activity2['Activity Summary'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 233,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2124, 2)"
      ]
     },
     "execution_count": 233,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Removing all CID's in Tox21 Data\n",
    "\n",
    "#Reading in Molecular Properties CSV\n",
    "tox21 = pd.read_csv('Molecular_Properties_CSV')\n",
    "tox21 = tox21.astype(float, errors = 'ignore')\n",
    "\n",
    "#getting list of CID's from tox21 data\n",
    "tox21_CIDs = tox21['PUBCHEM_CID']\n",
    "tox21_CIDs = tox21_CIDs.tolist()\n",
    "df_activity2.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 234,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Inactive    723\n",
       "Active       16\n",
       "Name: Activity Summary, dtype: int64"
      ]
     },
     "execution_count": 234,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Removing all CID's in tox21 data from NCGC cleaned data (final_clean)\n",
    "final_ncgc = df_activity2[~df_activity2.PUBCHEM_CID.isin(tox21_CIDs)]\n",
    "final_ncgc.shape\n",
    "final_ncgc['Activity Summary'].value_counts()\n",
    "#only 46 active values total"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 235,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(16, 2)"
      ]
     },
     "execution_count": 235,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Taking Dataframe of only Active values\n",
    "##Dropping all inactive values\n",
    "df_active = final_ncgc.drop(final_ncgc.loc[final_ncgc['Activity Summary'] == 'Inactive'].index)\n",
    "df_active.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 260,
   "metadata": {
    "scrolled": true
   },
   "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>PUBCHEM_CID</th>\n",
       "      <th>Activity Summary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>76</th>\n",
       "      <td>1486</td>\n",
       "      <td>Inactive</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>77</th>\n",
       "      <td>1486</td>\n",
       "      <td>Inactive</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>561</th>\n",
       "      <td>7242</td>\n",
       "      <td>Inactive</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>562</th>\n",
       "      <td>7242</td>\n",
       "      <td>Inactive</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>713</th>\n",
       "      <td>7732</td>\n",
       "      <td>Inactive</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>714</th>\n",
       "      <td>7732</td>\n",
       "      <td>Inactive</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>877</th>\n",
       "      <td>8411</td>\n",
       "      <td>Inactive</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>878</th>\n",
       "      <td>8411</td>\n",
       "      <td>Inactive</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1241</th>\n",
       "      <td>15553</td>\n",
       "      <td>Inactive</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1242</th>\n",
       "      <td>15553</td>\n",
       "      <td>Inactive</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1453</th>\n",
       "      <td>29249</td>\n",
       "      <td>Inactive</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1454</th>\n",
       "      <td>29249</td>\n",
       "      <td>Inactive</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1539</th>\n",
       "      <td>37175</td>\n",
       "      <td>Inactive</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1540</th>\n",
       "      <td>37175</td>\n",
       "      <td>Inactive</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      PUBCHEM_CID Activity Summary\n",
       "76           1486         Inactive\n",
       "77           1486         Inactive\n",
       "561          7242         Inactive\n",
       "562          7242         Inactive\n",
       "713          7732         Inactive\n",
       "714          7732         Inactive\n",
       "877          8411         Inactive\n",
       "878          8411         Inactive\n",
       "1241        15553         Inactive\n",
       "1242        15553         Inactive\n",
       "1453        29249         Inactive\n",
       "1454        29249         Inactive\n",
       "1539        37175         Inactive\n",
       "1540        37175         Inactive"
      ]
     },
     "execution_count": 260,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_dups = final_ncgc[final_ncgc.duplicated('PUBCHEM_CID', keep = False)] \n",
    "final_dups"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Writing df_activity2 to csv\n",
    "\n",
    "df_activity2.to_csv('FINAL_Merged_Cleaned_CSV_7-19', index = False)"
   ]
  },
  {
   "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": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}