sparql_aopdb_figure.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "e84f325b",
   "metadata": {},
   "source": [
    "# Use Case: Connecting EPA AOP-DB Data to Extant Databases with RDF and SPARQL <a name=\"top\"></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "87eb7601",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-success\">\n",
    "    The following Python code executes <strong>SPARQL</strong> queries on <strong>AOP-DB</strong> and related databases. The first example queries just AOP-DB. The second example is a <strong>federated</strong> SPARQL query. Federated SPARQL queries are SPARQL queries which retrieve linked RDF data from multiple databases.\n",
    "<br><br>\n",
    "This document will showcase how SPARQL can be used to access AOP-DB RDF data, and link it with extant datasets. For more detailed information on RDF, SPARQL, and other semantic technologies, please visit the <a href=\"https://www.w3.org/standards/semanticweb/#w3c_overview\">official semantic web documentation</a>.\n",
    "</div>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "be555ba2",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-info\">\n",
    "The <strong>SPARQLWrapper</strong> package allows SPARQL query execution in a Python environment. The <strong>pandas</strong> package is used for data manipulation and display. \n",
    "</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "e0583eb8",
   "metadata": {},
   "outputs": [],
   "source": [
    "from SPARQLWrapper import SPARQLWrapper, JSON\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3a948986",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-info\">\n",
    "The functions defined below convert JSON, which is the data format returned by SPARQL queries, into pandas dataframes.\n",
    "</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "4a02d352",
   "metadata": {},
   "outputs": [],
   "source": [
    "# This function takes the JSON data retrieved by SPARQL queries and converts it to a useful format\n",
    "def convertjson(jdata):\n",
    "    jdata.setReturnFormat(JSON)\n",
    "    return jdata.query().convert()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "1f883808",
   "metadata": {},
   "outputs": [],
   "source": [
    "# This function unpacks the queried data into a pandas dataframe\n",
    "def sparql_to_df(results):\n",
    "\n",
    "    head = []\n",
    "    for header in results[\"head\"][\"vars\"]:\n",
    "        head.append(str(header))\n",
    "\n",
    "    dbdict = {}\n",
    "    for i in range(len(head)):\n",
    "        dbdict[head[i]] = []\n",
    "\n",
    "    for result in results[\"results\"][\"bindings\"]:\n",
    "        for item in head:\n",
    "            dbdict[item].append(result[item][\"value\"])\n",
    "\n",
    "    return pd.DataFrame.from_dict(dbdict)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c62c8e74",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-info\">\n",
    "    Now we will use SPARQLWrapper to connect to <a href=\"http://81.169.200.64:8892/sparql/\">AOP-DB's SPARQL endpoint</a>.\n",
    "</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "da0dae56",
   "metadata": {},
   "outputs": [],
   "source": [
    "aopdb = SPARQLWrapper(\"http://81.169.200.64:8892/sparql/\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7db0c054",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-info\">\n",
    "    The SPARQL query in <strong><em>example one</em></strong> -- the query itself is the red text in triple quotes -- retrieves AOP-DB genes and related EPA ToxCast assay information. The variables returned in a SPARQL query are denoted by a leading question mark. In this case, these variables are <strong>?gene</strong> and <strong>?ToxCast_assay</strong>.\n",
    "</div>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b3b70e7d",
   "metadata": {},
   "source": [
    "### Example 1: Simple SPARQL Query of AOP-DB \n",
    "Retrieving Genes and ToxCast Assays"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "acc56a13",
   "metadata": {},
   "outputs": [],
   "source": [
    "aopdb.setQuery(\"\"\"\n",
    "PREFIX mmo: <http://purl.obolibrary.org/obo/MMO_>\n",
    "PREFIX edam: <http://edamontology.org/>\n",
    "\n",
    "SELECT DISTINCT ?gene ?ToxCast_assay {\n",
    "\n",
    "    ?gene_id mmo:0000441 ?assay_id;\n",
    "            edam:data_1027 ?gene.\n",
    "          \n",
    "    ?assay_id dc:title ?ToxCast_assay.\n",
    "    \n",
    "    }LIMIT 50 \n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "0f1859dd",
   "metadata": {},
   "outputs": [],
   "source": [
    "gene_assay_Query = sparql_to_df(convertjson(aopdb))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "043af333",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-info\">\n",
    "   Below is a sample of the National Center for Biotechnology Information (NCBI) gene identification numbers,\n",
    "    and their asociated assays, that the SPARQL query of AOP-DB in example one returned.\n",
    "</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "7e8be53d",
   "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>gene</th>\n",
       "      <th>ToxCast_assay</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>150</td>\n",
       "      <td>NVS_GPCR_hAdra2A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>154</td>\n",
       "      <td>NVS_GPCR_hAdrb2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>1588</td>\n",
       "      <td>TOX21_Aromatase_Inhibition</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>1588</td>\n",
       "      <td>NVS_ADME_hCYP19A1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>1956</td>\n",
       "      <td>NVS_ENZ_hEGFR</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    gene               ToxCast_assay\n",
       "0    150            NVS_GPCR_hAdra2A\n",
       "1    154             NVS_GPCR_hAdrb2\n",
       "31  1588  TOX21_Aromatase_Inhibition\n",
       "30  1588           NVS_ADME_hCYP19A1\n",
       "46  1956               NVS_ENZ_hEGFR"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gene_assay_Query.sort_values('gene').head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c098c07f",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-info\">\n",
    "    Example one is a simple demonstration of how SPARQL queries retrieve information from RDF data. <strong><em>Example two</em></strong>, a federated SPARQL query, will retrieve linked data from four databases- <strong> AOP-DB</strong>, <strong>AOP-Wiki</strong>,<strong> Protein Ontology</strong>, and <strong> WikiPathways</strong>.\n",
    "    <br><br>\n",
    "   This second example begins much like the first, retrieving gene and ToxCast assay information from AOP-DB. However, it then extends into the <a href=\"https://aopwiki.rdf.bigcat-bioinformatics.org/sparql\">AOP-Wiki SPARQL endpoint</a> using the <strong>SERVICE</strong> call. Because AOP-DB and AOP-Wiki have RDF data describing different attributes of the same genes, the query, which has already retrieved gene and ToxCast assay information from AOP-DB, can retrieve from AOP-Wiki those gene's protein and key event information. Then the query makes another SERVICE call to the <a href=\"https://lod.proconsortium.org/yasgui.html\">Protein Ontology SPARQL endpoint</a> to obtain detailed descriptions of each protein. Finally, the query calls the <a href=\"http://sparql.wikipathways.org/sparql\">WikiPathways</a> endpoint and retrieves biological pathways that the genes are part of, and descriptions of those pathways.\n",
    "</div>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0425a075",
   "metadata": {},
   "source": [
    "### Example 2: Federated SPARQL Query of AOP-DB, AOP-Wiki, Protein Ontology, and WikiPathways\n",
    "Retrieving AOP, Key Event, Gene, Protein, and Biological Pathway Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "3c471cbc",
   "metadata": {},
   "outputs": [],
   "source": [
    "aopdb.setQuery(\"\"\"\n",
    "PREFIX pato: <http://purl.obolibrary.org/obo/PATO_>\n",
    "PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>\n",
    "PREFIX aopo: <http://aopkb.org/aop_ontology#>\n",
    "PREFIX edam: <http://edamontology.org/>\n",
    "PREFIX skos: <http://www.w3.org/2004/02/skos/core#>\n",
    "PREFIX obo: <http://purl.obolibrary.org/obo/>\n",
    "PREFIX dc: <http://purl.org/dc/elements/1.1/>\n",
    "PREFIX wp: <http://vocabularies.wikipathways.org/wp#>\n",
    "PREFIX dcterms: <http://purl.org/dc/terms/> \n",
    "\n",
    "\n",
    "SELECT DISTINCT ?aop_id ?ke_id ?key_event ?gene_NCBI ?protein ?description ?wp_pathname ?wp_description  {\n",
    "\n",
    "          ?gene mmo:0000441 ?assay.\n",
    "          ?assay dc:title ?assayId.\n",
    "    \n",
    "    SERVICE <https://aopwiki.rdf.bigcat-bioinformatics.org/sparql> {\n",
    "    \n",
    "          ?gene a edam:data_1027 ;\n",
    "                dc:identifier ?gene_NCBI .\n",
    "                \n",
    "          ?object dc:title ?protein;\n",
    "                  skos:exactMatch ?gene.\n",
    "          \n",
    "          ?ke pato:0001241 ?object; \n",
    "              dc:title ?key_event; \n",
    "              rdfs:label ?ke_id.\n",
    "          \n",
    "          ?aop a aopo:AdverseOutcomePathway ;\n",
    "              rdfs:label ?aop_id;\n",
    "              aopo:has_key_event ?ke.\n",
    "          }\n",
    "\n",
    "    SERVICE <https://sparql.proconsortium.org/virtuoso/sparql> {\n",
    "\n",
    "          ?object rdfs:label ?_PRO_label ;\n",
    "                  obo:IAO_0000115 ?description .\n",
    "       \n",
    "          BIND(STR(?_PRO_label) AS ?PRO_label) .\n",
    "          }\n",
    "        \n",
    "    SERVICE <http://sparql.wikipathways.org/sparql> {\n",
    "    \n",
    "          ?wp_gene wp:bdbEntrezGene ?gene;\n",
    "                  dcterms:isPartOf ?wpPath .\n",
    "                 \n",
    "          ?wpPath dcterms:identifier ?pathway ;\n",
    "                  dcterms:description ?wp_description ;\n",
    "                  dc:title ?wp_pathname .\n",
    "                \n",
    "          BIND(STR(?pathway) AS ?pathway) . \n",
    "          }\n",
    "          \n",
    "    }LIMIT 50 \n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "7fa7f078",
   "metadata": {},
   "outputs": [],
   "source": [
    "fedQuery = sparql_to_df(convertjson(aopdb))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "77153da2",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-info\">\n",
    "   Below is a sample of the adverse outcome pathway, key event, assay, gene, protein, and biological pathway information cobined using the SPARQL query in example two.\n",
    "</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "358a7bdf",
   "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>aop_id</th>\n",
       "      <th>ke_id</th>\n",
       "      <th>key_event</th>\n",
       "      <th>gene_NCBI</th>\n",
       "      <th>protein</th>\n",
       "      <th>description</th>\n",
       "      <th>wp_pathname</th>\n",
       "      <th>wp_description</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AOP 10</td>\n",
       "      <td>KE 667</td>\n",
       "      <td>Binding at picrotoxin site, iGABAR chloride ch...</td>\n",
       "      <td>ncbigene:780973</td>\n",
       "      <td>gamma-aminobutyric acid receptor subunit alpha-1</td>\n",
       "      <td>A GABA(A) receptor protein that is a translati...</td>\n",
       "      <td>Iron uptake and transport</td>\n",
       "      <td>The transport of iron between cells is mediate...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AOP 10</td>\n",
       "      <td>KE 667</td>\n",
       "      <td>Binding at picrotoxin site, iGABAR chloride ch...</td>\n",
       "      <td>ncbigene:780973</td>\n",
       "      <td>gamma-aminobutyric acid receptor subunit alpha-1</td>\n",
       "      <td>A GABA(A) receptor protein that is a translati...</td>\n",
       "      <td>SIDS Susceptibility Pathways</td>\n",
       "      <td>In this model, we provide an integrated view o...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AOP 52</td>\n",
       "      <td>KE 111</td>\n",
       "      <td>Agonism, Estrogen receptor</td>\n",
       "      <td>ncbigene:407238</td>\n",
       "      <td>estrogen receptor</td>\n",
       "      <td>A protein that is a translation product of the...</td>\n",
       "      <td>Integrated Breast Cancer Pathway</td>\n",
       "      <td>This pathway incorporates the most important p...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AOP 67</td>\n",
       "      <td>KE 658</td>\n",
       "      <td>Decreased testosterone by the fetal Leydig cel...</td>\n",
       "      <td>ncbigene:407238</td>\n",
       "      <td>estrogen receptor</td>\n",
       "      <td>A protein that is a translation product of the...</td>\n",
       "      <td>Integrated Breast Cancer Pathway</td>\n",
       "      <td>This pathway incorporates the most important p...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AOP 165</td>\n",
       "      <td>KE 1046</td>\n",
       "      <td>Suppression, Estrogen receptor (ER) activity</td>\n",
       "      <td>ncbigene:407238</td>\n",
       "      <td>estrogen receptor</td>\n",
       "      <td>A protein that is a translation product of the...</td>\n",
       "      <td>Integrated Breast Cancer Pathway</td>\n",
       "      <td>This pathway incorporates the most important p...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    aop_id    ke_id                                          key_event  \\\n",
       "0   AOP 10   KE 667  Binding at picrotoxin site, iGABAR chloride ch...   \n",
       "1   AOP 10   KE 667  Binding at picrotoxin site, iGABAR chloride ch...   \n",
       "2   AOP 52   KE 111                         Agonism, Estrogen receptor   \n",
       "3   AOP 67   KE 658  Decreased testosterone by the fetal Leydig cel...   \n",
       "4  AOP 165  KE 1046       Suppression, Estrogen receptor (ER) activity   \n",
       "\n",
       "         gene_NCBI                                           protein  \\\n",
       "0  ncbigene:780973  gamma-aminobutyric acid receptor subunit alpha-1   \n",
       "1  ncbigene:780973  gamma-aminobutyric acid receptor subunit alpha-1   \n",
       "2  ncbigene:407238                                 estrogen receptor   \n",
       "3  ncbigene:407238                                 estrogen receptor   \n",
       "4  ncbigene:407238                                 estrogen receptor   \n",
       "\n",
       "                                         description  \\\n",
       "0  A GABA(A) receptor protein that is a translati...   \n",
       "1  A GABA(A) receptor protein that is a translati...   \n",
       "2  A protein that is a translation product of the...   \n",
       "3  A protein that is a translation product of the...   \n",
       "4  A protein that is a translation product of the...   \n",
       "\n",
       "                        wp_pathname  \\\n",
       "0         Iron uptake and transport   \n",
       "1      SIDS Susceptibility Pathways   \n",
       "2  Integrated Breast Cancer Pathway   \n",
       "3  Integrated Breast Cancer Pathway   \n",
       "4  Integrated Breast Cancer Pathway   \n",
       "\n",
       "                                      wp_description  \n",
       "0  The transport of iron between cells is mediate...  \n",
       "1  In this model, we provide an integrated view o...  \n",
       "2  This pathway incorporates the most important p...  \n",
       "3  This pathway incorporates the most important p...  \n",
       "4  This pathway incorporates the most important p...  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fedQuery.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0d4794b9",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-success\" role='alert'>\n",
    "In approximately 20 lines of Python and about 35 lines of SPARQL, this query retrieves data on eight variables from four databases. There are many more variables in AOP-DB, AOP-Wiki, Protein Ontology, and WikiPathways that can be combined in a SPARQL query, and there are also many more databases with relevant information that could be included. Having AOP-DB data available as RDF presents a broad range of opportunities for research and data access.\n",
    "</div>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "69e98c11",
   "metadata": {},
   "source": [
    "<hr></hr>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "00e5c0b8",
   "metadata": {},
   "source": [
    "<table style=\"border:2px solid #add8e6;margin-left:0;margin-right:auto;text-align:left\">\n",
    "    <tr>\n",
    "        <td style='text-align:left'>Author:</td><td> </td><td>Wes Slaughter</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <td style='text-align:left'>Date:</td><td> </td><td>5/28/2020</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <td style='text-align:left'>ORISE Fellow</td><td> </td><td>US EPA ORD CPHEA</td>\n",
    "    </tr>\n",
    "    \n",
    "</table>\n",
    "\n",
    "<br>\n",
    "<br>"
   ]
  }
 ],
 "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.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}