HowTo_queryDB_pandas.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Querying databases and converting to `pandas`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Main commands to:  \n",
    "- Access and query an online database:  \n",
    "  - When data are in `.json`, we use the python package `request`  \n",
    "  - When data are in RDF triples, we use the python package `SPARQLWrapper`  \n",
    "- Convert information from `.json` to `pandas` dataframe   \n",
    "\n",
    "Examples are from this notebook: https://github.com/OpenRiskNet/notebooks/blob/master/BridgeDb/DataCure%2BAOPlink.ipynb  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 1. `.json` using `requests`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`requests` is a package to access .json databases (https://realpython.com/python-requests/)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Using `get` to access database, fields, and values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# database url\n",
    "chemidconvert = 'https://chemidconvert.cloud.douglasconnect.com/v1/'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# query the database (function: get())\n",
    "response1 = requests.get(chemidconvert + 'name/to/smiles', \n",
    "                        params={'name': 'paracetamol'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Successful\n"
     ]
    }
   ],
   "source": [
    "# if the query is successful, we get 200, otherwise 404 (function: status_code)\n",
    "if response1.status_code == 200:\n",
    "    print('Successful')\n",
    "elif response1.status_code == 404:\n",
    "    print('Not successful')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'smiles': 'CC(=O)Nc1ccc(O)cc1'}\n"
     ]
    }
   ],
   "source": [
    "# extract field in .json format (functions: get(), .json())\n",
    "response1 = requests.get(chemidconvert + 'name/to/smiles', params={'name': 'paracetamol'}).json()\n",
    "print (response1) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CC(=O)Nc1ccc(O)cc1\n"
     ]
    }
   ],
   "source": [
    "# extract field value (functions: get(), .json(), ['field_name'])\n",
    "response1 = requests.get(chemidconvert + 'name/to/smiles', params={'name': 'paracetamol'}).json()['smiles']\n",
    "print (response1) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Using `params` to query the database  \n",
    "Parameters can be passed as dictionary, list of tuples, or bytes. Here example with dictionary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# database url\n",
    "tggatesconvert = 'http://open-tggates-api.cloud.douglasconnect.com/v2/'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'aggregations': {'compoundName': {'buckets': [{'doc_count': 2, 'key': '2,4-dinitrophenol'}, {'doc_count': 2, 'key': '2-nitrofluorene'}, {'doc_count': 1, 'key': 'LPS'}, {'doc_count': 2, 'key': 'N-methyl-N-nitrosourea'}, {'doc_count': 2, 'key': 'N-nitrosomorpholine'}, {'doc_count': 2, 'key': 'TNFalpha'}, {'doc_count': 2, 'key': 'WY-14643'}, {'doc_count': 2, 'key': 'acarbose'}, {'doc_count': 2, 'key': 'acetamide'}, {'doc_count': 2, 'key': 'acetamidofluorene'}, {'doc_count': 2, 'key': 'acetaminophen'}, {'doc_count': 2, 'key': 'acetazolamide'}, {'doc_count': 2, 'key': 'adapin'}, {'doc_count': 2, 'key': 'aflatoxin B1'}, {'doc_count': 2, 'key': 'ajmaline'}, {'doc_count': 2, 'key': 'allopurinol'}, {'doc_count': 2, 'key': 'allyl alcohol'}, {'doc_count': 2, 'key': 'alpidem'}, {'doc_count': 2, 'key': 'amiodarone'}, {'doc_count': 2, 'key': 'amitriptyline'}, {'doc_count': 2, 'key': 'amphotericin B'}, {'doc_count': 2, 'key': 'aspirin'}, {'doc_count': 2, 'key': 'azathioprine'}, {'doc_count': 2, 'key': 'bendazac'}, {'doc_count': 2, 'key': 'benzbromarone'}, {'doc_count': 2, 'key': 'benziodarone'}, {'doc_count': 2, 'key': 'bromobenzene'}, {'doc_count': 1, 'key': 'bromoethylamine'}, {'doc_count': 2, 'key': 'bucetin'}, {'doc_count': 2, 'key': 'buspirone'}, {'doc_count': 2, 'key': 'buthionine sulfoximine'}, {'doc_count': 2, 'key': 'butylated hydroxyanisole'}, {'doc_count': 2, 'key': 'caffeine'}, {'doc_count': 2, 'key': 'captopril'}, {'doc_count': 2, 'key': 'carbamazepine'}, {'doc_count': 2, 'key': 'carbon tetrachloride'}, {'doc_count': 2, 'key': 'chloramphenicol'}, {'doc_count': 2, 'key': 'chlormadinone'}, {'doc_count': 2, 'key': 'chlormezanone'}, {'doc_count': 2, 'key': 'chlorpheniramine'}, {'doc_count': 2, 'key': 'chlorpromazine'}, {'doc_count': 2, 'key': 'chlorpropamide'}, {'doc_count': 2, 'key': 'cimetidine'}, {'doc_count': 2, 'key': 'ciprofloxacin'}, {'doc_count': 2, 'key': 'clofibrate'}, {'doc_count': 2, 'key': 'clomipramine'}, {'doc_count': 2, 'key': 'clozapine'}, {'doc_count': 2, 'key': 'colchicine'}, {'doc_count': 2, 'key': 'coumarin'}, {'doc_count': 2, 'key': 'cycloheximide'}, {'doc_count': 2, 'key': 'cyclophosphamide'}, {'doc_count': 2, 'key': 'cyclosporine A'}, {'doc_count': 2, 'key': 'danazol'}, {'doc_count': 2, 'key': 'dantrolene'}, {'doc_count': 2, 'key': 'dexamethasone'}, {'doc_count': 2, 'key': 'diazepam'}, {'doc_count': 2, 'key': 'diclofenac'}, {'doc_count': 2, 'key': 'diethyl maleate'}, {'doc_count': 2, 'key': 'diltiazem'}, {'doc_count': 2, 'key': 'disopyramide'}, {'doc_count': 2, 'key': 'disulfiram'}, {'doc_count': 2, 'key': 'doxorubicin'}, {'doc_count': 2, 'key': 'enalapril'}, {'doc_count': 2, 'key': 'erythromycin ethylsuccinate'}, {'doc_count': 2, 'key': 'ethambutol'}, {'doc_count': 2, 'key': 'ethanol'}, {'doc_count': 2, 'key': 'ethinylestradiol'}, {'doc_count': 2, 'key': 'ethionamide'}, {'doc_count': 2, 'key': 'ethionine'}, {'doc_count': 2, 'key': 'etoposide'}, {'doc_count': 2, 'key': 'famotidine'}, {'doc_count': 2, 'key': 'fenofibrate'}, {'doc_count': 2, 'key': 'fluoxetine hydrochloride'}, {'doc_count': 2, 'key': 'fluphenazine'}, {'doc_count': 2, 'key': 'flutamide'}, {'doc_count': 2, 'key': 'furosemide'}, {'doc_count': 2, 'key': 'galactosamine'}, {'doc_count': 2, 'key': 'gemfibrozil'}, {'doc_count': 2, 'key': 'glibenclamide'}, {'doc_count': 2, 'key': 'griseofulvin'}, {'doc_count': 2, 'key': 'haloperidol'}, {'doc_count': 2, 'key': 'hepatocyte growth factor, human'}, {'doc_count': 2, 'key': 'hexachlorobenzene'}, {'doc_count': 2, 'key': 'hydroxyzine'}, {'doc_count': 2, 'key': 'ibuprofen'}, {'doc_count': 2, 'key': 'imipramine'}, {'doc_count': 2, 'key': 'indomethacin'}, {'doc_count': 2, 'key': 'interleukin 1 beta, human'}, {'doc_count': 2, 'key': 'interleukin 6, human'}, {'doc_count': 2, 'key': 'iproniazid'}, {'doc_count': 2, 'key': 'isoniazid'}, {'doc_count': 2, 'key': 'ketoconazole'}, {'doc_count': 2, 'key': 'labetalol'}, {'doc_count': 2, 'key': 'lomustine'}, {'doc_count': 2, 'key': 'lornoxicam'}, {'doc_count': 2, 'key': 'mefenamic acid'}, {'doc_count': 2, 'key': 'meloxicam'}, {'doc_count': 2, 'key': 'metformin'}, {'doc_count': 2, 'key': 'methapyrilene'}, {'doc_count': 2, 'key': 'methimazole'}, {'doc_count': 2, 'key': 'methyldopa'}, {'doc_count': 2, 'key': 'methylene dianiline'}, {'doc_count': 2, 'key': 'methyltestosterone'}, {'doc_count': 2, 'key': 'mexiletine'}, {'doc_count': 2, 'key': 'monocrotaline'}, {'doc_count': 2, 'key': 'moxisylyte'}, {'doc_count': 2, 'key': 'naphthyl isothiocyanate'}, {'doc_count': 2, 'key': 'naproxen'}, {'doc_count': 2, 'key': 'nefazodone'}, {'doc_count': 2, 'key': 'nicotinic acid'}, {'doc_count': 2, 'key': 'nifedipine'}, {'doc_count': 2, 'key': 'nimesulide'}, {'doc_count': 2, 'key': 'nitrofurantoin'}, {'doc_count': 2, 'key': 'nitrosodiethylamine'}, {'doc_count': 2, 'key': 'omeprazole'}, {'doc_count': 2, 'key': 'papaverine'}, {'doc_count': 2, 'key': 'pemoline'}, {'doc_count': 2, 'key': 'penicillamine'}, {'doc_count': 2, 'key': 'perhexiline'}, {'doc_count': 2, 'key': 'phalloidin'}, {'doc_count': 2, 'key': 'phenacetin'}, {'doc_count': 2, 'key': 'phenobarbital'}, {'doc_count': 2, 'key': 'phenylanthranilic acid'}, {'doc_count': 2, 'key': 'phenylbutazone'}, {'doc_count': 2, 'key': 'phenytoin'}, {'doc_count': 2, 'key': 'promethazine'}, {'doc_count': 2, 'key': 'propranolol'}, {'doc_count': 2, 'key': 'propylthiouracil'}, {'doc_count': 2, 'key': 'quinidine'}, {'doc_count': 2, 'key': 'ranitidine'}, {'doc_count': 2, 'key': 'rifampicin'}, {'doc_count': 2, 'key': 'rosiglitazone maleate'}, {'doc_count': 2, 'key': 'rotenone'}, {'doc_count': 2, 'key': 'simvastatin'}, {'doc_count': 2, 'key': 'sulfasalazine'}, {'doc_count': 2, 'key': 'sulindac'}, {'doc_count': 2, 'key': 'sulpiride'}, {'doc_count': 2, 'key': 'tacrine'}, {'doc_count': 2, 'key': 'tamoxifen'}, {'doc_count': 2, 'key': 'tannic acid'}, {'doc_count': 2, 'key': 'terbinafine'}, {'doc_count': 2, 'key': 'tetracycline'}, {'doc_count': 2, 'key': 'theophylline'}, {'doc_count': 2, 'key': 'thioacetamide'}, {'doc_count': 2, 'key': 'thioridazine'}, {'doc_count': 2, 'key': 'ticlopidine'}, {'doc_count': 2, 'key': 'tiopronin'}, {'doc_count': 2, 'key': 'tolbutamide'}, {'doc_count': 2, 'key': 'transforming growth factor beta 1'}, {'doc_count': 2, 'key': 'triazolam'}, {'doc_count': 1, 'key': 'trimethadione'}, {'doc_count': 2, 'key': 'tunicamycin'}, {'doc_count': 2, 'key': 'valproic acid'}, {'doc_count': 2, 'key': 'venlafaxine'}, {'doc_count': 2, 'key': 'vitamin A'}], 'filterTerm': 'compoundNameFilter', 'name': 'CompoundName'}, 'timepointHr': {'buckets': [], 'filterTerm': 'timepointHrFilter', 'name': 'TimepointHr'}, 'organism': {'buckets': [], 'filterTerm': 'organismFilter', 'name': 'Organism'}, 'tissue': {'buckets': [], 'filterTerm': 'tissueFilter', 'name': 'Tissue'}, 'doseLevel': {'buckets': [], 'filterTerm': 'doseLevelFilter', 'name': 'DoseLevel'}, 'repeatType': {'buckets': [], 'filterTerm': 'repeatTypeFilter', 'name': 'RepeatType'}, 'cellType': {'buckets': [], 'filterTerm': 'cellTypeFilter', 'name': 'CellType'}}, 'total': 0, 'samples': []}\n"
     ]
    }
   ],
   "source": [
    "# filter the data you want (functions: get() with keyword \"params\", followed by dictionary with parameter fields and corresponding values ) \n",
    "response2 = requests.get(tggatesconvert + 'samples',\n",
    "                         params={'limit': 10000, \n",
    "                                 'compoundNameFilter': 'paracetamol',\n",
    "                                 'organismFilter': 'Human', \n",
    "                                 'tissueFilter': 'Liver',\n",
    "                                 'cellTypeFilter': 'in vitro', \n",
    "                                 'repeatTypeFilter': 'Single',\n",
    "                                 'timepointHrFilter': '24.0', \n",
    "                                 'doseLevelFilter': 'High'\n",
    "                          })\n",
    "print(response2.json())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2. `RDF` format using `SPARQLWrapper`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`SPARQLWrapper` is a package to access RDF databases (https://rdflib.github.io/sparqlwrapper/)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "from SPARQLWrapper import SPARQLWrapper, JSON"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Set database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "sparql = SPARQLWrapper(\"http://sparql.wikipathways.org\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Query database using `SPARQL`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create query\n",
    "gene = \"ENSG00000106258\"\n",
    "pathwayQuery = '''\n",
    "      SELECT DISTINCT ?ensembl ?pathwayRes (str(?wpid) as ?pathway) (str(?title) as ?pathwayTitle)\n",
    "      WHERE {{\n",
    "        ?gene a wp:GeneProduct ;\n",
    "          dcterms:identifier ?id ;\n",
    "          dcterms:isPartOf ?pathwayRes ;\n",
    "          wp:bdbEnsembl <http://identifiers.org/ensembl/{0}> .\n",
    "        ?pathwayRes a wp:Pathway ;\n",
    "          dcterms:identifier ?wpid ;\n",
    "          dc:title ?title .\n",
    "        BIND ( \"gene\" AS ?ensembl )\n",
    "      }}\n",
    "    '''.format(gene)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# set query\n",
    "sparql.setQuery(pathwayQuery)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# set return format (JSON, XML, N3 - Note: the format must be the one imported: e.g. from SPARQLWrapper import SPARQLWrapper, JSON) \n",
    "sparql.setReturnFormat(JSON)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "# execute query and format conversion\n",
    "response3 = sparql.query().convert()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 3. From `.json` to `pandas`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Example 1: Extracting the whole table from `.json` and convert it to `pandas`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests\n",
    "import pandas"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Using the `request` package, query the database  (`get()`)  and convert the answer to .json (`.json()`):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "# database url\n",
    "tggatesconvert = 'http://open-tggates-api.cloud.douglasconnect.com/v2/'\n",
    "\n",
    "# compounds of interest\n",
    "compoundset = {'paracetamol', 'acetominophen', 'methapyrilene', 'phenylbutazone', 'simvastatin', 'valproic acid'}\n",
    "compounds_name = \"|\".join(compoundset) # separate with “|” for database specs \n",
    "\n",
    "# query database\n",
    "response = requests.get(tggatesconvert + 'samples',\n",
    "                        params={'limit': 10000, \n",
    "                                'compoundNameFilter': compounds_name,\n",
    "                                'organismFilter': 'Human', \n",
    "                                'tissueFilter': 'Liver',\n",
    "                                'cellTypeFilter': 'in vitro', \n",
    "                                'repeatTypeFilter': 'Single',\n",
    "                                'timepointHrFilter': '24.0', \n",
    "                                'doseLevelFilter': 'High'\n",
    "                          })\n",
    "# convert to .json\n",
    "samples = response.json() "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Convert from `.json` to `pandas` framework:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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>_id_</th>\n",
       "      <th>cellType</th>\n",
       "      <th>compoundName</th>\n",
       "      <th>controlSamples</th>\n",
       "      <th>doseLevel</th>\n",
       "      <th>organism</th>\n",
       "      <th>repeatType</th>\n",
       "      <th>sampleId</th>\n",
       "      <th>timepointHr</th>\n",
       "      <th>tissue</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>003016020014</td>\n",
       "      <td>in vitro</td>\n",
       "      <td>methapyrilene</td>\n",
       "      <td>[]</td>\n",
       "      <td>High</td>\n",
       "      <td>Human</td>\n",
       "      <td>Single</td>\n",
       "      <td>003016020014</td>\n",
       "      <td>24.0</td>\n",
       "      <td>Liver</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>003016020015</td>\n",
       "      <td>in vitro</td>\n",
       "      <td>methapyrilene</td>\n",
       "      <td>[]</td>\n",
       "      <td>High</td>\n",
       "      <td>Human</td>\n",
       "      <td>Single</td>\n",
       "      <td>003016020015</td>\n",
       "      <td>24.0</td>\n",
       "      <td>Liver</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>003016014002</td>\n",
       "      <td>in vitro</td>\n",
       "      <td>phenylbutazone</td>\n",
       "      <td>[]</td>\n",
       "      <td>High</td>\n",
       "      <td>Human</td>\n",
       "      <td>Single</td>\n",
       "      <td>003016014002</td>\n",
       "      <td>24.0</td>\n",
       "      <td>Liver</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>003016014003</td>\n",
       "      <td>in vitro</td>\n",
       "      <td>phenylbutazone</td>\n",
       "      <td>[]</td>\n",
       "      <td>High</td>\n",
       "      <td>Human</td>\n",
       "      <td>Single</td>\n",
       "      <td>003016014003</td>\n",
       "      <td>24.0</td>\n",
       "      <td>Liver</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>003016079002</td>\n",
       "      <td>in vitro</td>\n",
       "      <td>simvastatin</td>\n",
       "      <td>[]</td>\n",
       "      <td>High</td>\n",
       "      <td>Human</td>\n",
       "      <td>Single</td>\n",
       "      <td>003016079002</td>\n",
       "      <td>24.0</td>\n",
       "      <td>Liver</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>003016079003</td>\n",
       "      <td>in vitro</td>\n",
       "      <td>simvastatin</td>\n",
       "      <td>[]</td>\n",
       "      <td>High</td>\n",
       "      <td>Human</td>\n",
       "      <td>Single</td>\n",
       "      <td>003016079003</td>\n",
       "      <td>24.0</td>\n",
       "      <td>Liver</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>003016038010</td>\n",
       "      <td>in vitro</td>\n",
       "      <td>valproic acid</td>\n",
       "      <td>[]</td>\n",
       "      <td>High</td>\n",
       "      <td>Human</td>\n",
       "      <td>Single</td>\n",
       "      <td>003016038010</td>\n",
       "      <td>24.0</td>\n",
       "      <td>Liver</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>003016038011</td>\n",
       "      <td>in vitro</td>\n",
       "      <td>valproic acid</td>\n",
       "      <td>[]</td>\n",
       "      <td>High</td>\n",
       "      <td>Human</td>\n",
       "      <td>Single</td>\n",
       "      <td>003016038011</td>\n",
       "      <td>24.0</td>\n",
       "      <td>Liver</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           _id_  cellType    compoundName controlSamples doseLevel organism  \\\n",
       "0  003016020014  in vitro   methapyrilene             []      High    Human   \n",
       "1  003016020015  in vitro   methapyrilene             []      High    Human   \n",
       "2  003016014002  in vitro  phenylbutazone             []      High    Human   \n",
       "3  003016014003  in vitro  phenylbutazone             []      High    Human   \n",
       "4  003016079002  in vitro     simvastatin             []      High    Human   \n",
       "5  003016079003  in vitro     simvastatin             []      High    Human   \n",
       "6  003016038010  in vitro   valproic acid             []      High    Human   \n",
       "7  003016038011  in vitro   valproic acid             []      High    Human   \n",
       "\n",
       "  repeatType      sampleId  timepointHr tissue  \n",
       "0     Single  003016020014         24.0  Liver  \n",
       "1     Single  003016020015         24.0  Liver  \n",
       "2     Single  003016014002         24.0  Liver  \n",
       "3     Single  003016014003         24.0  Liver  \n",
       "4     Single  003016079002         24.0  Liver  \n",
       "5     Single  003016079003         24.0  Liver  \n",
       "6     Single  003016038010         24.0  Liver  \n",
       "7     Single  003016038011         24.0  Liver  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# convert to pandas\n",
    "table = pandas.DataFrame(samples['samples'])\n",
    "display(table)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Example 2: Extracting one piece of information (i.e. one column) from `.json` and put it in a `pandas` framework"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests\n",
    "import pandas"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create the variables:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "# database url\n",
    "chemidconvert = 'https://chemidconvert.cloud.douglasconnect.com/v1/'\n",
    "\n",
    "# compounds of interest\n",
    "compoundset = {'paracetamol', 'acetominophen', 'methapyrilene', 'phenylbutazone', 'simvastatin', 'valproic acid'}\n",
    "\n",
    "# create pandas framework\n",
    "pandas.set_option('display.max_colwidth', -1)  # make table as wide as page\n",
    "compounds = pandas.DataFrame(columns=['Compound name', 'Smiles', 'Image'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In a `for` loop, query the database(`.get()`), convert to answer .json (`json()`), and then to `pandas` :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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>Compound name</th>\n",
       "      <th>Smiles</th>\n",
       "      <th>Image</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>valproic acid</td>\n",
       "      <td>CCCC(CCC)C(O)=O</td>\n",
       "      <td>CCCC(CCC)C(O)=O</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>phenylbutazone</td>\n",
       "      <td>CCCCC1C(=O)N(N(C1=O)c2ccccc2)c3ccccc3</td>\n",
       "      <td>CCCCC1C(=O)N(N(C1=O)c2ccccc2)c3ccccc3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>paracetamol</td>\n",
       "      <td>CC(=O)Nc1ccc(O)cc1</td>\n",
       "      <td>CC(=O)Nc1ccc(O)cc1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>simvastatin</td>\n",
       "      <td>CCC(C)(C)C(=O)O[C@H]1C[C@@H](C)C=C2C=C[C@H](C)[C@H](CC[C@@H]3C[C@@H](O)CC(=O)O3)[C@@H]12</td>\n",
       "      <td>CCC(C)(C)C(=O)O[C@H]1C[C@@H](C)C=C2C=C[C@H](C)[C@H](CC[C@@H]3C[C@@H](O)CC(=O)O3)[C@@H]12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>acetominophen</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>methapyrilene</td>\n",
       "      <td>CN(C)CCN(Cc1sccc1)c2ccccn2</td>\n",
       "      <td>CN(C)CCN(Cc1sccc1)c2ccccn2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Compound name  \\\n",
       "0  valproic acid    \n",
       "1  phenylbutazone   \n",
       "2  paracetamol      \n",
       "3  simvastatin      \n",
       "4  acetominophen    \n",
       "5  methapyrilene    \n",
       "\n",
       "                                                                                     Smiles  \\\n",
       "0  CCCC(CCC)C(O)=O                                                                            \n",
       "1  CCCCC1C(=O)N(N(C1=O)c2ccccc2)c3ccccc3                                                      \n",
       "2  CC(=O)Nc1ccc(O)cc1                                                                         \n",
       "3  CCC(C)(C)C(=O)O[C@H]1C[C@@H](C)C=C2C=C[C@H](C)[C@H](CC[C@@H]3C[C@@H](O)CC(=O)O3)[C@@H]12   \n",
       "4  None                                                                                       \n",
       "5  CN(C)CCN(Cc1sccc1)c2ccccn2                                                                 \n",
       "\n",
       "                                                                                      Image  \n",
       "0  CCCC(CCC)C(O)=O                                                                           \n",
       "1  CCCCC1C(=O)N(N(C1=O)c2ccccc2)c3ccccc3                                                     \n",
       "2  CC(=O)Nc1ccc(O)cc1                                                                        \n",
       "3  CCC(C)(C)C(=O)O[C@H]1C[C@@H](C)C=C2C=C[C@H](C)[C@H](CC[C@@H]3C[C@@H](O)CC(=O)O3)[C@@H]12  \n",
       "4  None                                                                                      \n",
       "5  CN(C)CCN(Cc1sccc1)c2ccccn2                                                                "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# query the database and fill out pandas framework \n",
    "for compound in compoundset:\n",
    "    # query\n",
    "    smiles = requests.get(chemidconvert + 'name/to/smiles', \n",
    "                          params={'name': compound}).json()['smiles']\n",
    "    # fill out pandas framework\n",
    "    compounds = compounds.append({'Compound name': compound, 'Smiles': smiles, 'Image': smiles}, ignore_index=True)\n",
    "display (compounds)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}