DataSummaries.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "8a16a9fe",
   "metadata": {},
   "source": [
    "# Summarize Search Results"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4f90b531",
   "metadata": {},
   "source": [
    "The CDA provides a custom python tool for searching CDA data. [`Q`](usage/#q) (short for Query) offers several ways to search and filter data, and several input modes:\n",
    "\n",
    "---\n",
    "- **<a href=\"../../QuickStart/usage/#q\">Q.()</a>** builds a query that can be used by `run()` or `count()`\n",
    "- **<a href=\"../../QuickStart/usage/#qrun\">Q.run()</a>** returns data for the specified search \n",
    "- **<a href=\"../../QuickStart/usage/#qcount\">Q.count()</a>** returns summary information (counts) data that fit the specified search\n",
    "- **<a href=\"../../QuickStart/usage/#columns\">columns()</a>** returns entity field names\n",
    "- **<a href=\"../../QuickStart/usage/#unique_terms\">unique_terms()</a>** returns entity field contents\n",
    "\n",
    "---\n",
    "                                                                    \n",
    "Before we do any work, we needs to import these functions cdapython.\n",
    "We're also telling cdapython to report it's version so we can be sure we're using the one we mean to:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "5245b7b0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<pre style=\"white-space:pre;overflow-x:auto;line-height:normal;font-family:Menlo,'DejaVu Sans Mono',consolas,'Courier New',monospace\"><span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">2022.8</span>.<span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">29</span>\n",
       "</pre>\n"
      ],
      "text/plain": [
       "\u001b[1;36m2022.8\u001b[0m.\u001b[1;36m29\u001b[0m\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "from cdapython import Q, columns, unique_terms, query\n",
    "print(Q.get_version())\n",
    "Q.set_host_url('http://localhost:8080')\n",
    "Q.set_default_project_dataset(\"gdc-bq-sample.dev\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d538da92",
   "metadata": {},
   "source": [
    "<div class=\"cdanote\" style=\"background-color:#b3e5d5;color:black;padding:20px;\">\n",
    "    \n",
    "CDA data comes from three sources:\n",
    "<ul>\n",
    "<li><b>The <a href=\"https://proteomic.datacommons.cancer.gov/pdc/\"> Proteomic Data Commons</a> (PDC)</b></li>\n",
    "<li><b>The <a href=\"https://gdc.cancer.gov/\">Genomic Data Commons</a> (GDC)</b></li>\n",
    "<li><b>The <a href=\"https://datacommons.cancer.gov/repository/imaging-data-commons\">Imaging Data Commons</a> (IDC)</b></li>\n",
    "</ul> \n",
    "    \n",
    "The CDA makes this data searchable in four main endpoints:\n",
    "\n",
    "<ul>\n",
    "<li><b>subject:</b> A patient entity captures the study-independent metadata for research subjects. Human research subjects are usually not traceable to a particular person to protect the subjects privacy.</li>\n",
    "<li><b>researchsubject:</b> A research subject is the entity of interest in a specific research study or project, typically a human being or an animal, but can also be a device, group of humans or animals, or a tissue sample. Human research subjects are usually not traceable to a particular person to protect the subjects privacy. This entity plays the role of the case_id in existing data. An individual who participates in 3 studies will have 3 researchsubject IDs</li>\n",
    "<li><b>specimen:</b> Any material taken as a sample from a biological entity (living or dead), or from a physical object or the environment. Specimens are usually collected as an example of their kind, often for use in some investigation.</li>\n",
    "<li><b>file:</b> A unit of data about subjects, researchsubjects, specimens, or their associated information</li>\n",
    "</ul>\n",
    "    \n",
    "And two endpoints that offer deeper information about data in the researchsubject endpoint:\n",
    "<ul>\n",
    "<li><b>diagnosis:</b> A collection of characteristics that describe an abnormal condition of the body as assessed at a point in time. May be used to capture information about neoplastic and non-neoplastic conditions.</li>\n",
    "<li><b>treatment:</b> Represent medication administration or other treatment types.</li>\n",
    "</ul>\n",
    "Any metadata field can be searched from any endpoint, the only difference between search types is what type of data is returned by default. This means that you can think of the CDA as a really, really enormous spreadsheet full of data. To search this enormous spreadsheet, you'd want select columns, and then filter rows.\n",
    "</div>\n",
    "\n",
    "\n",
    "If you are looking to build a cohort of distinct individuals who meet some criteria, search by `subject`. If you want to build a cohort, but are particularly interested in studies rather than the participates per se, search by `researchsubject`. If you are looking for biosamples that can be ordered or a specific format of information (for e.g. histological slides) start with `specimen`. If you are primarily looking for files you can reuse for your own analysis, start with `file`.\n",
    "\n",
    "In CDA search, these concepts can also be chained together, so you can look specifically for specimen subjects, or researchsubject diagnoses. In the four 'main' tables, all of the rows will have one or more files associated with them that can be directly found by chaining, as in specimen files. Diagnosis and treatment do not have files directly associated with them and so can only be used to find files in conjunction with the other searches.\n",
    "\n",
    "In all cases, any search can use any metadata field, the only difference between search types is what type of data you return by default. \n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f76026a3",
   "metadata": {},
   "source": [
    "## Getting simple summary data\n",
    "\n",
    "Let's try a broad search of the CDA to see what information exists about cancers that were first diagnosed in the brain. To run this simple search, we would first construct a query in `Q` and save it to a variable `myquery`. This is the same query we ran in the <a href=\"../BasicSearch\">Basic Search</a> notebook:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "c92a98ba",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "\n",
    "\n",
    "\n",
    "myquery = Q('primary_diagnosis_site = \"brain\"')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6e855573",
   "metadata": {
    "tags": [
     "Help!"
    ]
   },
   "source": [
    "\n",
    "<div class=\"cdawarn\" style=\"background-color:#f9cfbf;color:black;padding:20px;\">\n",
    "<h3>Where did those terms come from?</h3>\n",
    "    \n",
    "If you aren't sure how we knew what terms to put in our search, please refer back to the <a href=\"../SearchTerms\">What search terms are available?</a> notebook. \n",
    "</div>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4e492876",
   "metadata": {},
   "source": [
    "### Overall summary\n",
    "\n",
    "You can get a quick summary of how many unique specimens, treatments, diagnoses, researchsubjects and subjects meet your search criteria by chaining a `count` command into the basic `run` call. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "c8e3599f",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<pre style=\"white-space:pre;overflow-x:auto;line-height:normal;font-family:Menlo,'DejaVu Sans Mono',consolas,'Courier New',monospace\">Getting results from database\n",
       "\n",
       "</pre>\n"
      ],
      "text/plain": [
       "Getting results from database\n",
       "\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<pre style=\"white-space:pre;overflow-x:auto;line-height:normal;font-family:Menlo,'DejaVu Sans Mono',consolas,'Courier New',monospace\">Waiting for results\n",
       "</pre>\n"
      ],
      "text/plain": [
       "Waiting for results\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<pre style=\"white-space:pre;overflow-x:auto;line-height:normal;font-family:Menlo,'DejaVu Sans Mono',consolas,'Courier New',monospace\">\n",
       "                        Total execution time: <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">0</span>\n",
       "                        min <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">15.935</span> sec <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">15935</span> ms\n",
       "                        \n",
       "</pre>\n"
      ],
      "text/plain": [
       "\n",
       "                        Total execution time: \u001b[1;36m0\u001b[0m\n",
       "                        min \u001b[1;36m15.935\u001b[0m sec \u001b[1;36m15935\u001b[0m ms\n",
       "                        \n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<pre style=\"white-space:pre;overflow-x:auto;line-height:normal;font-family:Menlo,'DejaVu Sans Mono',consolas,'Courier New',monospace\">specimen_count : <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">39201</span>\n",
       "</pre>\n"
      ],
      "text/plain": [
       "specimen_count : \u001b[1;36m39201\u001b[0m\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<pre style=\"white-space:pre;overflow-x:auto;line-height:normal;font-family:Menlo,'DejaVu Sans Mono',consolas,'Courier New',monospace\">treatment_count : <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">2386</span>\n",
       "</pre>\n"
      ],
      "text/plain": [
       "treatment_count : \u001b[1;36m2386\u001b[0m\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<pre style=\"white-space:pre;overflow-x:auto;line-height:normal;font-family:Menlo,'DejaVu Sans Mono',consolas,'Courier New',monospace\">diagnosis_count : <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">1756</span>\n",
       "</pre>\n"
      ],
      "text/plain": [
       "diagnosis_count : \u001b[1;36m1756\u001b[0m\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<pre style=\"white-space:pre;overflow-x:auto;line-height:normal;font-family:Menlo,'DejaVu Sans Mono',consolas,'Courier New',monospace\">mutation_count : <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">904</span>\n",
       "</pre>\n"
      ],
      "text/plain": [
       "mutation_count : \u001b[1;36m904\u001b[0m\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<pre style=\"white-space:pre;overflow-x:auto;line-height:normal;font-family:Menlo,'DejaVu Sans Mono',consolas,'Courier New',monospace\">researchsubject_count : <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">3716</span>\n",
       "</pre>\n"
      ],
      "text/plain": [
       "researchsubject_count : \u001b[1;36m3716\u001b[0m\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<pre style=\"white-space:pre;overflow-x:auto;line-height:normal;font-family:Menlo,'DejaVu Sans Mono',consolas,'Courier New',monospace\">subject_count : <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">2384</span>\n",
       "</pre>\n"
      ],
      "text/plain": [
       "subject_count : \u001b[1;36m2384\u001b[0m\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": []
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": []
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "myquery.count.run()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d483e944",
   "metadata": {},
   "source": [
    "These numbers are how many total rows of data will come back when querying the various endpoints.\n",
    "\n",
    "\n",
    "\n",
    "### subject summary\n",
    "\n",
    "We can also add `count`to the other run calls we did in the <a href=\"../BasicSearch\">Basic Search</a> notebook to get more detailed summaries:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "6d9137aa",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<pre style=\"white-space:pre;overflow-x:auto;line-height:normal;font-family:Menlo,'DejaVu Sans Mono',consolas,'Courier New',monospace\">Getting results from database\n",
       "\n",
       "</pre>\n"
      ],
      "text/plain": [
       "Getting results from database\n",
       "\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<pre style=\"white-space:pre;overflow-x:auto;line-height:normal;font-family:Menlo,'DejaVu Sans Mono',consolas,'Courier New',monospace\">Waiting for results\n",
       "</pre>\n"
      ],
      "text/plain": [
       "Waiting for results\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<pre style=\"white-space:pre;overflow-x:auto;line-height:normal;font-family:Menlo,'DejaVu Sans Mono',consolas,'Courier New',monospace\">\n",
       "                        Total execution time: <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">0</span>\n",
       "                        min <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">33.746</span> sec <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">33746</span> ms\n",
       "                        \n",
       "</pre>\n"
      ],
      "text/plain": [
       "\n",
       "                        Total execution time: \u001b[1;36m0\u001b[0m\n",
       "                        min \u001b[1;36m33.746\u001b[0m sec \u001b[1;36m33746\u001b[0m ms\n",
       "                        \n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<pre style=\"white-space:pre;overflow-x:auto;line-height:normal;font-family:Menlo,'DejaVu Sans Mono',consolas,'Courier New',monospace\">    total : <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">2384</span>    \n",
       "</pre>\n"
      ],
      "text/plain": [
       "    total : \u001b[1;36m2384\u001b[0m    \n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<pre style=\"white-space:pre;overflow-x:auto;line-height:normal;font-family:Menlo,'DejaVu Sans Mono',consolas,'Courier New',monospace\">  files : <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">4099497</span>   \n",
       "</pre>\n"
      ],
      "text/plain": [
       "  files : \u001b[1;36m4099497\u001b[0m   \n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<style type=\"text/css\">\n",
       "#T_c836e_ th {\n",
       "  background-color: #000066;\n",
       "  color: white;\n",
       "  text-align: left;\n",
       "}\n",
       "#T_c836e_ td {\n",
       "  text-align: left;\n",
       "  border-bottom: 1px solid black;\n",
       "}\n",
       "</style>\n",
       "<table id=\"T_c836e_\" style='display:inline'>\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th class=\"col_heading level0 col0\" >subject_identifier_system</th>\n",
       "      <th class=\"col_heading level0 col1\" >count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td id=\"T_c836e_row0_col0\" class=\"data row0 col0\" >IDC</td>\n",
       "      <td id=\"T_c836e_row0_col1\" class=\"data row0 col1\" >1955</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td id=\"T_c836e_row1_col0\" class=\"data row1 col0\" >PDC</td>\n",
       "      <td id=\"T_c836e_row1_col1\" class=\"data row1 col1\" >309</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td id=\"T_c836e_row2_col0\" class=\"data row2 col0\" >GDC</td>\n",
       "      <td id=\"T_c836e_row2_col1\" class=\"data row2 col1\" >1454</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<style type=\"text/css\">\n",
       "#T_c5ee0_ th {\n",
       "  background-color: #000066;\n",
       "  color: white;\n",
       "  text-align: left;\n",
       "}\n",
       "#T_c5ee0_ td {\n",
       "  text-align: left;\n",
       "  border-bottom: 1px solid black;\n",
       "}\n",
       "</style>\n",
       "<table id=\"T_c5ee0_\" style='display:inline'>\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th class=\"col_heading level0 col0\" >sex</th>\n",
       "      <th class=\"col_heading level0 col1\" >count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td id=\"T_c5ee0_row0_col0\" class=\"data row0 col0\" >None</td>\n",
       "      <td id=\"T_c5ee0_row0_col1\" class=\"data row0 col1\" >748</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td id=\"T_c5ee0_row1_col0\" class=\"data row1 col0\" >female</td>\n",
       "      <td id=\"T_c5ee0_row1_col1\" class=\"data row1 col1\" >653</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td id=\"T_c5ee0_row2_col0\" class=\"data row2 col0\" >male</td>\n",
       "      <td id=\"T_c5ee0_row2_col1\" class=\"data row2 col1\" >980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td id=\"T_c5ee0_row3_col0\" class=\"data row3 col0\" >not reported</td>\n",
       "      <td id=\"T_c5ee0_row3_col1\" class=\"data row3 col1\" >3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<style type=\"text/css\">\n",
       "#T_aa980_ th {\n",
       "  background-color: #000066;\n",
       "  color: white;\n",
       "  text-align: left;\n",
       "}\n",
       "#T_aa980_ td {\n",
       "  text-align: left;\n",
       "  border-bottom: 1px solid black;\n",
       "}\n",
       "</style>\n",
       "<table id=\"T_aa980_\" style='display:inline'>\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th class=\"col_heading level0 col0\" >race</th>\n",
       "      <th class=\"col_heading level0 col1\" >count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td id=\"T_aa980_row0_col0\" class=\"data row0 col0\" >None</td>\n",
       "      <td id=\"T_aa980_row0_col1\" class=\"data row0 col1\" >748</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td id=\"T_aa980_row1_col0\" class=\"data row1 col0\" >white</td>\n",
       "      <td id=\"T_aa980_row1_col1\" class=\"data row1 col1\" >1311</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td id=\"T_aa980_row2_col0\" class=\"data row2 col0\" >black or african american</td>\n",
       "      <td id=\"T_aa980_row2_col1\" class=\"data row2 col1\" >96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td id=\"T_aa980_row3_col0\" class=\"data row3 col0\" >not reported</td>\n",
       "      <td id=\"T_aa980_row3_col1\" class=\"data row3 col1\" >136</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td id=\"T_aa980_row4_col0\" class=\"data row4 col0\" >asian</td>\n",
       "      <td id=\"T_aa980_row4_col1\" class=\"data row4 col1\" >33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td id=\"T_aa980_row5_col0\" class=\"data row5 col0\" >not allowed to collect</td>\n",
       "      <td id=\"T_aa980_row5_col1\" class=\"data row5 col1\" >25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td id=\"T_aa980_row6_col0\" class=\"data row6 col0\" >american indian or alaska native</td>\n",
       "      <td id=\"T_aa980_row6_col1\" class=\"data row6 col1\" >4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td id=\"T_aa980_row7_col0\" class=\"data row7 col0\" >Unknown</td>\n",
       "      <td id=\"T_aa980_row7_col1\" class=\"data row7 col1\" >21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td id=\"T_aa980_row8_col0\" class=\"data row8 col0\" >other</td>\n",
       "      <td id=\"T_aa980_row8_col1\" class=\"data row8 col1\" >9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td id=\"T_aa980_row9_col0\" class=\"data row9 col0\" >native hawaiian or other pacific islander</td>\n",
       "      <td id=\"T_aa980_row9_col1\" class=\"data row9 col1\" >1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<style type=\"text/css\">\n",
       "#T_b6e66_ th {\n",
       "  background-color: #000066;\n",
       "  color: white;\n",
       "  text-align: left;\n",
       "}\n",
       "#T_b6e66_ td {\n",
       "  text-align: left;\n",
       "  border-bottom: 1px solid black;\n",
       "}\n",
       "</style>\n",
       "<table id=\"T_b6e66_\" style='display:inline'>\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th class=\"col_heading level0 col0\" >ethnicity</th>\n",
       "      <th class=\"col_heading level0 col1\" >count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td id=\"T_b6e66_row0_col0\" class=\"data row0 col0\" >None</td>\n",
       "      <td id=\"T_b6e66_row0_col1\" class=\"data row0 col1\" >748</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td id=\"T_b6e66_row1_col0\" class=\"data row1 col0\" >not hispanic or latino</td>\n",
       "      <td id=\"T_b6e66_row1_col1\" class=\"data row1 col1\" >1285</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td id=\"T_b6e66_row2_col0\" class=\"data row2 col0\" >not reported</td>\n",
       "      <td id=\"T_b6e66_row2_col1\" class=\"data row2 col1\" >219</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td id=\"T_b6e66_row3_col0\" class=\"data row3 col0\" >hispanic or latino</td>\n",
       "      <td id=\"T_b6e66_row3_col1\" class=\"data row3 col1\" >85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td id=\"T_b6e66_row4_col0\" class=\"data row4 col0\" >Unknown</td>\n",
       "      <td id=\"T_b6e66_row4_col1\" class=\"data row4 col1\" >22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td id=\"T_b6e66_row5_col0\" class=\"data row5 col0\" >not allowed to collect</td>\n",
       "      <td id=\"T_b6e66_row5_col1\" class=\"data row5 col1\" >25</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<style type=\"text/css\">\n",
       "#T_343b0_ th {\n",
       "  background-color: #000066;\n",
       "  color: white;\n",
       "  text-align: left;\n",
       "}\n",
       "#T_343b0_ td {\n",
       "  text-align: left;\n",
       "  border-bottom: 1px solid black;\n",
       "}\n",
       "</style>\n",
       "<table id=\"T_343b0_\" style='display:inline'>\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th class=\"col_heading level0 col0\" >cause_of_death</th>\n",
       "      <th class=\"col_heading level0 col1\" >count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td id=\"T_343b0_row0_col0\" class=\"data row0 col0\" >None</td>\n",
       "      <td id=\"T_343b0_row0_col1\" class=\"data row0 col1\" >2098</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td id=\"T_343b0_row1_col0\" class=\"data row1 col0\" >Not Reported</td>\n",
       "      <td id=\"T_343b0_row1_col1\" class=\"data row1 col1\" >200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td id=\"T_343b0_row2_col0\" class=\"data row2 col0\" >Cancer Related</td>\n",
       "      <td id=\"T_343b0_row2_col1\" class=\"data row2 col1\" >63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td id=\"T_343b0_row3_col0\" class=\"data row3 col0\" >Not Cancer Related</td>\n",
       "      <td id=\"T_343b0_row3_col1\" class=\"data row3 col1\" >9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td id=\"T_343b0_row4_col0\" class=\"data row4 col0\" >Infection</td>\n",
       "      <td id=\"T_343b0_row4_col1\" class=\"data row4 col1\" >3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td id=\"T_343b0_row5_col0\" class=\"data row5 col0\" >Unknown</td>\n",
       "      <td id=\"T_343b0_row5_col1\" class=\"data row5 col1\" >9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td id=\"T_343b0_row6_col0\" class=\"data row6 col0\" >Surgical Complications</td>\n",
       "      <td id=\"T_343b0_row6_col1\" class=\"data row6 col1\" >2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<pre style=\"white-space:pre;overflow-x:auto;line-height:normal;font-family:Menlo,'DejaVu Sans Mono',consolas,'Courier New',monospace\"><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">with</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> flattened_result </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">as</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> (</span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">SELECT</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> results.</span><span style=\"color: #f92672; text-decoration-color: #f92672; background-color: #272822\">*</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">EXCEPT</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\">(rn) </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">FROM</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> (</span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">SELECT</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> ROW_NUMBER() OVER (PARTITION </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">BY</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> Subject.id, </span><span style=\"background-color: #272822\">    </span>\n",
       "<span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\">_subject_identifier.</span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">system</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\">, _subject_associated_project, _subject_Files) </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">as</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> rn, Subject.id </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">AS</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> subject_id, </span><span style=\"background-color: #272822\">         </span>\n",
       "<span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\">_subject_identifier.</span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">system</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">AS</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> subject_identifier_system, _subject_identifier.value </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">AS</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> subject_identifier_value, </span><span style=\"background-color: #272822\">   </span>\n",
       "<span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\">Subject.species </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">AS</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> species, Subject.sex </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">AS</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> sex, Subject.race </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">AS</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> race, Subject.ethnicity </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">AS</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> ethnicity, </span><span style=\"background-color: #272822\">             </span>\n",
       "<span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\">Subject.days_to_birth </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">AS</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> days_to_birth, _subject_associated_project, Subject.vital_status </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">AS</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> vital_status, </span><span style=\"background-color: #272822\">        </span>\n",
       "<span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\">Subject.days_to_death </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">AS</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> days_to_death, Subject.cause_of_death </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">AS</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> cause_of_death, _subject_Files </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">FROM</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> </span><span style=\"background-color: #272822\">             </span>\n",
       "<span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\">gdc</span><span style=\"color: #f92672; text-decoration-color: #f92672; background-color: #272822\">-</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\">bq</span><span style=\"color: #f92672; text-decoration-color: #f92672; background-color: #272822\">-</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\">sample.dev.all_Subjects_v3_0_final </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">AS</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> Subject </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">LEFT</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">JOIN</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">UNNEST</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\">(Subject.ResearchSubject) </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">AS</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> _ResearchSubject </span>\n",
       "<span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">LEFT</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">JOIN</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">UNNEST</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\">(Subject.identifier) </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">AS</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> _subject_identifier </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">LEFT</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">JOIN</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">UNNEST</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\">(Subject.subject_associated_project) </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">AS</span>\n",
       "<span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\">_subject_associated_project </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">LEFT</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">JOIN</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">UNNEST</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\">(Subject.Files) </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">AS</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> _subject_Files </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">WHERE</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> </span><span style=\"background-color: #272822\">                               </span>\n",
       "<span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\">(IFNULL(</span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">UPPER</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\">(_ResearchSubject.primary_diagnosis_site), </span><span style=\"color: #e6db74; text-decoration-color: #e6db74; background-color: #272822\">''</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\">) </span><span style=\"color: #f92672; text-decoration-color: #f92672; background-color: #272822\">=</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">UPPER</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\">(</span><span style=\"color: #e6db74; text-decoration-color: #e6db74; background-color: #272822\">'brain'</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\">))) </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">as</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> results </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">WHERE</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> rn </span><span style=\"color: #f92672; text-decoration-color: #f92672; background-color: #272822\">=</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> </span><span style=\"color: #ae81ff; text-decoration-color: #ae81ff; background-color: #272822\">1</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\">) </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">select</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> </span><span style=\"background-color: #272822\">    </span>\n",
       "<span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\">(</span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">SELECT</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">COUNT</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\">(</span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">DISTINCT</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> subject_id) </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">from</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> flattened_result) </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">as</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> total, (</span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">SELECT</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">COUNT</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\">(</span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">DISTINCT</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> _subject_Files) </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">from</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> </span><span style=\"background-color: #272822\">   </span>\n",
       "<span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\">flattened_result) </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">as</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> files, (</span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">select</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> ARRAY(</span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">select</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">as</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> STRUCT subject_identifier_system, </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">count</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\">(</span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">distinct</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> subject_id) </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">as</span>\n",
       "<span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">count</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">from</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> flattened_result </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">group</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">by</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> subject_identifier_system)) </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">as</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> subject_identifier_system, (</span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">select</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> ARRAY(</span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">select</span>\n",
       "<span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">as</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> STRUCT sex, </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">count</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\">(</span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">distinct</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> subject_id) </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">as</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">count</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">from</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> flattened_result </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">group</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">by</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> sex)) </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">as</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> sex, (</span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">select</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> </span><span style=\"background-color: #272822\">           </span>\n",
       "<span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\">ARRAY(</span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">select</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">as</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> STRUCT race, </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">count</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\">(</span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">distinct</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> subject_id) </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">as</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">count</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">from</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> flattened_result </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">group</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">by</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> race)) </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">as</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> race, </span><span style=\"background-color: #272822\">   </span>\n",
       "<span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\">(</span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">select</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> ARRAY(</span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">select</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">as</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> STRUCT ethnicity, </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">count</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\">(</span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">distinct</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> subject_id) </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">as</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">count</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">from</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> flattened_result </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">group</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">by</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> </span><span style=\"background-color: #272822\">      </span>\n",
       "<span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\">ethnicity)) </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">as</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> ethnicity, (</span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">select</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> ARRAY(</span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">select</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">as</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> STRUCT cause_of_death, </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">count</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\">(</span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">distinct</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> subject_id) </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">as</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">count</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">from</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> </span><span style=\"background-color: #272822\"> </span>\n",
       "<span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\">flattened_result </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">group</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">by</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> cause_of_death)) </span><span style=\"color: #66d9ef; text-decoration-color: #66d9ef; background-color: #272822\">as</span><span style=\"color: #f8f8f2; text-decoration-color: #f8f8f2; background-color: #272822\"> cause_of_death</span><span style=\"background-color: #272822\">                                                       </span>\n",
       "</pre>\n"
      ],
      "text/plain": [
       "\u001b[38;2;102;217;239;48;2;39;40;34mwith\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34mflattened_result\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mas\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m(\u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mSELECT\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34mresults\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m.\u001b[0m\u001b[38;2;249;38;114;48;2;39;40;34m*\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mEXCEPT\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m(\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34mrn\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m)\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mFROM\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m(\u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mSELECT\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34mROW_NUMBER\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m(\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m)\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34mOVER\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m(\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34mPARTITION\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mBY\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34mSubject\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m.\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34mid\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m,\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[48;2;39;40;34m    \u001b[0m\n",
       "\u001b[38;2;248;248;242;48;2;39;40;34m_subject_identifier\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m.\u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34msystem\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m,\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m_subject_associated_project\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m,\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m_subject_Files\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m)\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mas\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34mrn\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m,\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34mSubject\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m.\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34mid\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mAS\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34msubject_id\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m,\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[48;2;39;40;34m         \u001b[0m\n",
       "\u001b[38;2;248;248;242;48;2;39;40;34m_subject_identifier\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m.\u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34msystem\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mAS\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34msubject_identifier_system\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m,\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m_subject_identifier\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m.\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34mvalue\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mAS\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34msubject_identifier_value\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m,\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[48;2;39;40;34m   \u001b[0m\n",
       "\u001b[38;2;248;248;242;48;2;39;40;34mSubject\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m.\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34mspecies\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mAS\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34mspecies\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m,\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34mSubject\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m.\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34msex\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mAS\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34msex\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m,\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34mSubject\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m.\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34mrace\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mAS\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34mrace\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m,\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34mSubject\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m.\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34methnicity\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mAS\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34methnicity\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m,\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[48;2;39;40;34m             \u001b[0m\n",
       "\u001b[38;2;248;248;242;48;2;39;40;34mSubject\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m.\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34mdays_to_birth\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mAS\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34mdays_to_birth\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m,\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m_subject_associated_project\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m,\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34mSubject\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m.\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34mvital_status\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mAS\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34mvital_status\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m,\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[48;2;39;40;34m        \u001b[0m\n",
       "\u001b[38;2;248;248;242;48;2;39;40;34mSubject\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m.\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34mdays_to_death\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mAS\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34mdays_to_death\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m,\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34mSubject\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m.\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34mcause_of_death\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mAS\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34mcause_of_death\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m,\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m_subject_Files\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mFROM\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[48;2;39;40;34m             \u001b[0m\n",
       "\u001b[38;2;248;248;242;48;2;39;40;34mgdc\u001b[0m\u001b[38;2;249;38;114;48;2;39;40;34m-\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34mbq\u001b[0m\u001b[38;2;249;38;114;48;2;39;40;34m-\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34msample\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m.\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34mdev\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m.\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34mall_Subjects_v3_0_final\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mAS\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34mSubject\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mLEFT\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mJOIN\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mUNNEST\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m(\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34mSubject\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m.\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34mResearchSubject\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m)\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mAS\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m_ResearchSubject\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\n",
       "\u001b[38;2;102;217;239;48;2;39;40;34mLEFT\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mJOIN\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mUNNEST\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m(\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34mSubject\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m.\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34midentifier\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m)\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mAS\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m_subject_identifier\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mLEFT\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mJOIN\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mUNNEST\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m(\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34mSubject\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m.\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34msubject_associated_project\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m)\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mAS\u001b[0m\n",
       "\u001b[38;2;248;248;242;48;2;39;40;34m_subject_associated_project\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mLEFT\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mJOIN\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mUNNEST\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m(\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34mSubject\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m.\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34mFiles\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m)\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mAS\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m_subject_Files\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mWHERE\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[48;2;39;40;34m                               \u001b[0m\n",
       "\u001b[38;2;248;248;242;48;2;39;40;34m(\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34mIFNULL\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m(\u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mUPPER\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m(\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m_ResearchSubject\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m.\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34mprimary_diagnosis_site\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m)\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m,\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;230;219;116;48;2;39;40;34m''\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m)\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;249;38;114;48;2;39;40;34m=\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mUPPER\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m(\u001b[0m\u001b[38;2;230;219;116;48;2;39;40;34m'brain'\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m)\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m)\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m)\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mas\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34mresults\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mWHERE\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34mrn\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;249;38;114;48;2;39;40;34m=\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;174;129;255;48;2;39;40;34m1\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m)\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mselect\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[48;2;39;40;34m    \u001b[0m\n",
       "\u001b[38;2;248;248;242;48;2;39;40;34m(\u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mSELECT\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mCOUNT\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m(\u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mDISTINCT\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34msubject_id\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m)\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mfrom\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34mflattened_result\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m)\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mas\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34mtotal\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m,\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m(\u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mSELECT\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mCOUNT\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m(\u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mDISTINCT\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m_subject_Files\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m)\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mfrom\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[48;2;39;40;34m   \u001b[0m\n",
       "\u001b[38;2;248;248;242;48;2;39;40;34mflattened_result\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m)\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mas\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34mfiles\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m,\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m(\u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mselect\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34mARRAY\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m(\u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mselect\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mas\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34mSTRUCT\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34msubject_identifier_system\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m,\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mcount\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m(\u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mdistinct\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34msubject_id\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m)\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mas\u001b[0m\n",
       "\u001b[38;2;102;217;239;48;2;39;40;34mcount\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mfrom\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34mflattened_result\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mgroup\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mby\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34msubject_identifier_system\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m)\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m)\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mas\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34msubject_identifier_system\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m,\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m(\u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mselect\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34mARRAY\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m(\u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mselect\u001b[0m\n",
       "\u001b[38;2;102;217;239;48;2;39;40;34mas\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34mSTRUCT\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34msex\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m,\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mcount\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m(\u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mdistinct\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34msubject_id\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m)\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mas\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mcount\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mfrom\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34mflattened_result\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mgroup\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mby\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34msex\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m)\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m)\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mas\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34msex\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m,\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m(\u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mselect\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[48;2;39;40;34m           \u001b[0m\n",
       "\u001b[38;2;248;248;242;48;2;39;40;34mARRAY\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m(\u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mselect\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mas\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34mSTRUCT\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34mrace\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m,\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mcount\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m(\u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mdistinct\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34msubject_id\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m)\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mas\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mcount\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mfrom\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34mflattened_result\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mgroup\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mby\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34mrace\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m)\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m)\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mas\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34mrace\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m,\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[48;2;39;40;34m   \u001b[0m\n",
       "\u001b[38;2;248;248;242;48;2;39;40;34m(\u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mselect\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34mARRAY\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m(\u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mselect\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mas\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34mSTRUCT\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34methnicity\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m,\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mcount\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m(\u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mdistinct\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34msubject_id\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m)\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mas\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mcount\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mfrom\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34mflattened_result\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mgroup\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mby\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[48;2;39;40;34m      \u001b[0m\n",
       "\u001b[38;2;248;248;242;48;2;39;40;34methnicity\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m)\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m)\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mas\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34methnicity\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m,\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m(\u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mselect\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34mARRAY\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m(\u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mselect\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mas\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34mSTRUCT\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34mcause_of_death\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m,\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mcount\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m(\u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mdistinct\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34msubject_id\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m)\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mas\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mcount\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mfrom\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[48;2;39;40;34m \u001b[0m\n",
       "\u001b[38;2;248;248;242;48;2;39;40;34mflattened_result\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mgroup\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mby\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34mcause_of_death\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m)\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m)\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;102;217;239;48;2;39;40;34mas\u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34m \u001b[0m\u001b[38;2;248;248;242;48;2;39;40;34mcause_of_death\u001b[0m\u001b[48;2;39;40;34m                                                       \u001b[0m\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": []
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "myquery.subject.count.run(show_sql=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dff2da08",
   "metadata": {},
   "source": [
    "Since we save the output as a variable, we need to look at the variable to see the results:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "993479db",
   "metadata": {},
   "outputs": [],
   "source": [
    "subjectresults"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e7e6d522",
   "metadata": {},
   "source": [
    "By default, the results are displayed as a table for easy previewing of the data. Since we queried the `subject` endpoint, our default results tell us `subject` level information, that is, information about unique individuals: their sex, race, age, species, etc. Using counts gives us back a nice pivot table type summary of the countable fields for Subjects. Note that above the table it also tells you the total subject count, as well as how many files are associated with those subjects."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "05e52f3f",
   "metadata": {},
   "source": [
    "\n",
    "---\n",
    "\n",
    "<div class=\"cdadefine\" style=\"background-color:#add9e5;color:black;padding:20px;\">\n",
    "\n",
    "<h3>Subject Field Definitions</h3>\n",
    "\n",
    "<i>A patient entity captures the study-independent metadata for research subjects. Human research subjects are usually not traceable to a particular person to protect the subjects privacy.</i>\n",
    "    \n",
    "    \n",
    "    \n",
    "<style type=\"text/css\">\n",
    ".tg  {border-collapse:collapse;border-spacing:0;margin:0px auto;}\n",
    ".tg td{border-color:black;border-style:solid;border-width:1px;font-family:noto sans, sans-serif;font-size:14px;\n",
    "  overflow:hidden;padding:10px 5px;word-break:normal;}\n",
    ".tg th{border-color:black;border-style:solid;border-width:1px;font-family:noto sans, sans-serif;font-size:14px;\n",
    "  font-weight:normal;overflow:hidden;padding:10px 5px;word-break:normal;}\n",
    ".tg .tg-7zrl{text-align:left;vertical-align:bottom}\n",
    ".tg .tg-0lax{text-align:left;vertical-align:top}\n",
    "</style>\n",
    "<table class=\"tg\">\n",
    "<tbody>\n",
    "  <tr>\n",
    "    <td class=\"tg-0lax\"> id (`total`)</td>\n",
    "    <td class=\"tg-0lax\"> The overall number of subjects returned.</td>\n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td class=\"tg-0lax\"> files</td>\n",
    "    <td class=\"tg-0lax\"> The number of files that match this search.</td>\n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td class=\"tg-7zrl\">identifier.value(`system`)</td>\n",
    "    <td class=\"tg-0lax\"> The identifier for the data provider.</td>\n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td class=\"tg-7zrl\">species</td>\n",
    "    <td class=\"tg-0lax\"> The taxonomic group (e.g. species) of the subject.</td>\n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td class=\"tg-7zrl\">sex</td>\n",
    "    <td class=\"tg-0lax\">  The biologic character or quality that distinguishes male and female from one another as expressed by analysis of the person's gonadal, morphologic (internal and external), chromosomal, and hormonal characteristics. </td>\n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td class=\"tg-7zrl\">race</td>\n",
    "    <td class=\"tg-0lax\"> An arbitrary classification of a taxonomic group that is a division of a species.</td>\n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td class=\"tg-7zrl\">ethnicity</td>\n",
    "    <td class=\"tg-0lax\"> An individual's self-described social and cultural grouping.</td>\n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td class=\"tg-0lax\"> cause_of_death</td>\n",
    "    <td class=\"tg-0lax\"> The cause of death, if known</td>\n",
    "  </tr>\n",
    "</tbody>\n",
    "</table>\n",
    "\n",
    "</div>\n",
    "    \n",
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5356bf41",
   "metadata": {},
   "source": [
    "This gives you a quick way to assess whether the full search results will have the data fields you require. But if you want to get the underlying data for your own downstream applications, you can also get the raw numbers by calling the zeroth value of the variable:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "869dfd5d",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "ename": "TypeError",
     "evalue": "'NoneType' object is not subscriptable",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mTypeError\u001b[0m                                 Traceback (most recent call last)",
      "Input \u001b[0;32mIn [6]\u001b[0m, in \u001b[0;36m<cell line: 1>\u001b[0;34m()\u001b[0m\n\u001b[0;32m----> 1\u001b[0m \u001b[43msubjectresults\u001b[49m\u001b[43m[\u001b[49m\u001b[38;5;241;43m0\u001b[39;49m\u001b[43m]\u001b[49m\n",
      "\u001b[0;31mTypeError\u001b[0m: 'NoneType' object is not subscriptable"
     ]
    }
   ],
   "source": [
    "subjectresults[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "73745aa6",
   "metadata": {},
   "source": [
    "### researchsubject\n",
    "\n",
    "If we're interested in what researchsubjects meet our criteria, we can also run our query against the researchsubject endpoint. Lets run it without saving to a variable this time to make it a bit quicker:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "a2d8c874",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<pre style=\"white-space:pre;overflow-x:auto;line-height:normal;font-family:Menlo,'DejaVu Sans Mono',consolas,'Courier New',monospace\">Getting results from database\n",
       "\n",
       "</pre>\n"
      ],
      "text/plain": [
       "Getting results from database\n",
       "\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<pre style=\"white-space:pre;overflow-x:auto;line-height:normal;font-family:Menlo,'DejaVu Sans Mono',consolas,'Courier New',monospace\">Waiting for results\n",
       "</pre>\n"
      ],
      "text/plain": [
       "Waiting for results\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<pre style=\"white-space:pre;overflow-x:auto;line-height:normal;font-family:Menlo,'DejaVu Sans Mono',consolas,'Courier New',monospace\">\n",
       "                Http Status: <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">500</span>\n",
       "                Error Message: Unrecognized name: _researchsubject_identifier at <span style=\"font-weight: bold\">[</span><span style=\"color: #00ff00; text-decoration-color: #00ff00; font-weight: bold\">1:121</span><span style=\"font-weight: bold\">]</span>\n",
       "                \n",
       "</pre>\n"
      ],
      "text/plain": [
       "\n",
       "                Http Status: \u001b[1;36m500\u001b[0m\n",
       "                Error Message: Unrecognized name: _researchsubject_identifier at \u001b[1m[\u001b[0m\u001b[1;92m1:121\u001b[0m\u001b[1m]\u001b[0m\n",
       "                \n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<pre style=\"white-space:pre;overflow-x:auto;line-height:normal;font-family:Menlo,'DejaVu Sans Mono',consolas,'Courier New',monospace\">\n",
       "                        Total execution time: <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">0</span>\n",
       "                        min <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">1.08</span> sec <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">1080</span> ms\n",
       "                        \n",
       "</pre>\n"
      ],
      "text/plain": [
       "\n",
       "                        Total execution time: \u001b[1;36m0\u001b[0m\n",
       "                        min \u001b[1;36m1.08\u001b[0m sec \u001b[1;36m1080\u001b[0m ms\n",
       "                        \n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "myquery.researchsubject.count.run()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4564beee",
   "metadata": {},
   "source": [
    "\n",
    "\n",
    "\n",
    "---\n",
    "\n",
    "<div class=\"cdadefine\" style=\"background-color:#add9e5;color:black;padding:20px;\">\n",
    "\n",
    "<h3>ResearchSubject Field Definitions</h3>\n",
    "\n",
    "<i>A research subject is the entity of interest in a specific research study or project, typically a human being or an animal, but can also be a device, group of humans or animals, or a tissue sample. Human research subjects are usually not traceable to a particular person to protect the subjects privacy. This entity plays the role of the case_id in existing data. An individual who participates in 3 studies will have 3 researchsubject IDs</i>\n",
    "    \n",
    "<style type=\"text/css\">\n",
    ".tg  {border-collapse:collapse;border-spacing:0;margin:0px auto;}\n",
    ".tg td{border-color:black;border-style:solid;border-width:1px;font-family:noto sans, sans-serif;font-size:14px;\n",
    "  overflow:hidden;padding:10px 5px;word-break:normal;}\n",
    ".tg th{border-color:black;border-style:solid;border-width:1px;font-family:noto sans, sans-serif;font-size:14px;\n",
    "  font-weight:normal;overflow:hidden;padding:10px 5px;word-break:normal;}\n",
    ".tg .tg-7zrl{text-align:left;vertical-align:bottom}\n",
    ".tg .tg-0lax{text-align:left;vertical-align:top}\n",
    "</style>\n",
    "<table class=\"tg\">\n",
    "<tbody>\n",
    "  <tr>\n",
    "    <td class=\"tg-0lax\"> id (`total`)</td>\n",
    "    <td class=\"tg-0lax\"> The overall number of researchsubjects returned.</td>\n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td class=\"tg-0lax\"> files</td>\n",
    "    <td class=\"tg-0lax\"> The number of files that match this search.</td>\n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td class=\"tg-7zrl\">identifier.value(`system`)</td>\n",
    "    <td class=\"tg-0lax\"> The identifier for the data provider.</td>\n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td class=\"tg-7zrl\">primary_diagnosis_condition</td>\n",
    "    <td class=\"tg-0lax\"> The text term used to describe the type of malignant disease.</td>\n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td class=\"tg-7zrl\">primary_diagnosis_site</td>\n",
    "    <td class=\"tg-0lax\"> The text term used to describe the primary site of disease.</td>\n",
    "  </tr>\n",
    "</tbody>\n",
    "</table>\n",
    "</div>\n",
    "    \n",
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8a67cf09",
   "metadata": {},
   "source": [
    "### diagnosis\n",
    "\n",
    "The diagnosis endpoint is an extension of the researchsubject endpoint, and returns information about researchsubjects that have a diagnosis that meets our search criteria. :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "7770d68c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<pre style=\"white-space:pre;overflow-x:auto;line-height:normal;font-family:Menlo,'DejaVu Sans Mono',consolas,'Courier New',monospace\">Getting results from database\n",
       "\n",
       "</pre>\n"
      ],
      "text/plain": [
       "Getting results from database\n",
       "\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<pre style=\"white-space:pre;overflow-x:auto;line-height:normal;font-family:Menlo,'DejaVu Sans Mono',consolas,'Courier New',monospace\">Waiting for results\n",
       "</pre>\n"
      ],
      "text/plain": [
       "Waiting for results\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<pre style=\"white-space:pre;overflow-x:auto;line-height:normal;font-family:Menlo,'DejaVu Sans Mono',consolas,'Courier New',monospace\">\n",
       "                Http Status: <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">500</span>\n",
       "                Error Message: Unrecognized name: _diagnosis_identifier at <span style=\"font-weight: bold\">[</span><span style=\"color: #00ff00; text-decoration-color: #00ff00; font-weight: bold\">1:115</span><span style=\"font-weight: bold\">]</span>\n",
       "                \n",
       "</pre>\n"
      ],
      "text/plain": [
       "\n",
       "                Http Status: \u001b[1;36m500\u001b[0m\n",
       "                Error Message: Unrecognized name: _diagnosis_identifier at \u001b[1m[\u001b[0m\u001b[1;92m1:115\u001b[0m\u001b[1m]\u001b[0m\n",
       "                \n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<pre style=\"white-space:pre;overflow-x:auto;line-height:normal;font-family:Menlo,'DejaVu Sans Mono',consolas,'Courier New',monospace\">\n",
       "                        Total execution time: <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">0</span>\n",
       "                        min <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">0.942</span> sec <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">942</span> ms\n",
       "                        \n",
       "</pre>\n"
      ],
      "text/plain": [
       "\n",
       "                        Total execution time: \u001b[1;36m0\u001b[0m\n",
       "                        min \u001b[1;36m0.942\u001b[0m sec \u001b[1;36m942\u001b[0m ms\n",
       "                        \n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "myquery.diagnosis.count.run()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eb73357c",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "<div class=\"cdadefine\" style=\"background-color:#add9e5;color:black;padding:20px;\">\n",
    "\n",
    "<h3>Diagnosis Field Definitions</h3>\n",
    "\n",
    "<i>A collection of characteristics that describe an abnormal condition of the body as assessed at a point in time. May be used to capture information about neoplastic and non-neoplastic conditions.</i>\n",
    "\n",
    "<style type=\"text/css\">\n",
    ".tg  {border-collapse:collapse;border-spacing:0;margin:0px auto;}\n",
    ".tg td{border-color:black;border-style:solid;border-width:1px;font-family:noto sans, sans-serif;font-size:14px;\n",
    "  overflow:hidden;padding:10px 5px;word-break:normal;}\n",
    ".tg th{border-color:black;border-style:solid;border-width:1px;font-family:noto sans, sans-serif;font-size:14px;\n",
    "  font-weight:normal;overflow:hidden;padding:10px 5px;word-break:normal;}\n",
    ".tg .tg-za14{border-color:inherit;text-align:left;vertical-align:bottom}\n",
    ".tg .tg-0pky{border-color:inherit;text-align:left;vertical-align:top}\n",
    ".tg .tg-7zrl{text-align:left;vertical-align:bottom}\n",
    ".tg .tg-0lax{text-align:left;vertical-align:top}\n",
    "</style>\n",
    "<table class=\"tg\">\n",
    "<tbody>\n",
    "  <tr>\n",
    "    <td class=\"tg-0lax\"> id (`total`)</td>\n",
    "    <td class=\"tg-0lax\"> The overall number of diagnoses returned.</td>\n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td class=\"tg-7zrl\">identifier.value(`system`)</td>\n",
    "    <td class=\"tg-0lax\"> The identifier for the data provider.</td>\n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td class=\"tg-za14\">primary_diagnosis</td>\n",
    "    <td class=\"tg-0pky\"> The diagnosis instance that qualified a subject for inclusion on a ResearchProject.</td>\n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td class=\"tg-za14\">stage</td>\n",
    "    <td class=\"tg-0pky\"> The extent of a cancer in the body.</td>\n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td class=\"tg-7zrl\">grade</td>\n",
    "    <td class=\"tg-0lax\"> The degree of abnormality of cancer cells.</td>\n",
    "  </tr>\n",
    "</tbody>\n",
    "</table>\n",
    "\n",
    "\n",
    "</div>\n",
    "    \n",
    "---\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "97442718",
   "metadata": {},
   "source": [
    "### treatment\n",
    "\n",
    "The treatment endpoint is an extension of diagnosis and returns information about treatments undertaken on research subjects that have a given diagnosis that meets our search criteria:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "be1ac64f",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<pre style=\"white-space:pre;overflow-x:auto;line-height:normal;font-family:Menlo,'DejaVu Sans Mono',consolas,'Courier New',monospace\">Getting results from database\n",
       "\n",
       "</pre>\n"
      ],
      "text/plain": [
       "Getting results from database\n",
       "\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<pre style=\"white-space:pre;overflow-x:auto;line-height:normal;font-family:Menlo,'DejaVu Sans Mono',consolas,'Courier New',monospace\">Waiting for results\n",
       "</pre>\n"
      ],
      "text/plain": [
       "Waiting for results\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<pre style=\"white-space:pre;overflow-x:auto;line-height:normal;font-family:Menlo,'DejaVu Sans Mono',consolas,'Courier New',monospace\">\n",
       "                Http Status: <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">500</span>\n",
       "                Error Message: Unrecognized name: _treatment_identifier at <span style=\"font-weight: bold\">[</span><span style=\"color: #00ff00; text-decoration-color: #00ff00; font-weight: bold\">1:115</span><span style=\"font-weight: bold\">]</span>\n",
       "                \n",
       "</pre>\n"
      ],
      "text/plain": [
       "\n",
       "                Http Status: \u001b[1;36m500\u001b[0m\n",
       "                Error Message: Unrecognized name: _treatment_identifier at \u001b[1m[\u001b[0m\u001b[1;92m1:115\u001b[0m\u001b[1m]\u001b[0m\n",
       "                \n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<pre style=\"white-space:pre;overflow-x:auto;line-height:normal;font-family:Menlo,'DejaVu Sans Mono',consolas,'Courier New',monospace\">\n",
       "                        Total execution time: <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">0</span>\n",
       "                        min <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">0.732</span> sec <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">732</span> ms\n",
       "                        \n",
       "</pre>\n"
      ],
      "text/plain": [
       "\n",
       "                        Total execution time: \u001b[1;36m0\u001b[0m\n",
       "                        min \u001b[1;36m0.732\u001b[0m sec \u001b[1;36m732\u001b[0m ms\n",
       "                        \n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "myquery.treatment.count.run()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ec5dda7f",
   "metadata": {},
   "source": [
    "\n",
    "---\n",
    "\n",
    "<div class=\"cdadefine\" style=\"background-color:#add9e5;color:black;padding:20px;\">\n",
    "\n",
    "<h3>Treatment Field Definitions</h3>\n",
    "\n",
    "<i><i> Medication administration or other treatment types. A single research subject may have multiple treatments for a single diagnosis, and/or different diagnoses, and different treatments, across different studies</i></i>\n",
    "    \n",
    "<style type=\"text/css\">\n",
    ".tg  {border-collapse:collapse;border-spacing:0;margin:0px auto;}\n",
    ".tg td{border-color:black;border-style:solid;border-width:1px;font-family:noto sans, sans-serif;font-size:14px;\n",
    "  overflow:hidden;padding:10px 5px;word-break:normal;}\n",
    ".tg th{border-color:black;border-style:solid;border-width:1px;font-family:noto sans, sans-serif;font-size:14px;\n",
    "  font-weight:normal;overflow:hidden;padding:10px 5px;word-break:normal;}\n",
    ".tg .tg-za14{border-color:inherit;text-align:left;vertical-align:bottom}\n",
    ".tg .tg-0pky{border-color:inherit;text-align:left;vertical-align:top}\n",
    ".tg .tg-7zrl{text-align:left;vertical-align:bottom}\n",
    ".tg .tg-0lax{text-align:left;vertical-align:top}\n",
    "</style>\n",
    "<table class=\"tg\">\n",
    "<tbody>\n",
    "  <tr>\n",
    "    <td class=\"tg-0lax\"> id (`total`)</td>\n",
    "    <td class=\"tg-0lax\"> The overall number of treatments returned.</td>\n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td class=\"tg-7zrl\">identifier.value(`system`)</td>\n",
    "    <td class=\"tg-0lax\"> The identifier for the data provider.</td>\n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td class=\"tg-za14\">treatment_type</td>\n",
    "    <td class=\"tg-0pky\"> The treatment type including medication/therapeutics or other procedures.</td>\n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td class=\"tg-7zrl\">treatment_effect</td>\n",
    "    <td class=\"tg-0lax\">The effect of a treatment on the diagnosis or tumor. </td>\n",
    "  </tr>\n",
    "</tbody>\n",
    "</table>\n",
    " \n",
    "\n",
    "</div>\n",
    "    \n",
    "---\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9c00bec8",
   "metadata": {},
   "source": [
    "### specimens\n",
    "\n",
    "We can use this same query to see what specimens are available for brain tissue at the CDA:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "51960eed",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<pre style=\"white-space:pre;overflow-x:auto;line-height:normal;font-family:Menlo,'DejaVu Sans Mono',consolas,'Courier New',monospace\">Getting results from database\n",
       "\n",
       "</pre>\n"
      ],
      "text/plain": [
       "Getting results from database\n",
       "\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<pre style=\"white-space:pre;overflow-x:auto;line-height:normal;font-family:Menlo,'DejaVu Sans Mono',consolas,'Courier New',monospace\">Waiting for results\n",
       "</pre>\n"
      ],
      "text/plain": [
       "Waiting for results\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<pre style=\"white-space:pre;overflow-x:auto;line-height:normal;font-family:Menlo,'DejaVu Sans Mono',consolas,'Courier New',monospace\">\n",
       "                Http Status: <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">500</span>\n",
       "                Error Message: Unrecognized name: _specimen_identifier at <span style=\"font-weight: bold\">[</span><span style=\"color: #00ff00; text-decoration-color: #00ff00; font-weight: bold\">1:114</span><span style=\"font-weight: bold\">]</span>\n",
       "                \n",
       "</pre>\n"
      ],
      "text/plain": [
       "\n",
       "                Http Status: \u001b[1;36m500\u001b[0m\n",
       "                Error Message: Unrecognized name: _specimen_identifier at \u001b[1m[\u001b[0m\u001b[1;92m1:114\u001b[0m\u001b[1m]\u001b[0m\n",
       "                \n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<pre style=\"white-space:pre;overflow-x:auto;line-height:normal;font-family:Menlo,'DejaVu Sans Mono',consolas,'Courier New',monospace\">\n",
       "                        Total execution time: <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">0</span>\n",
       "                        min <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">0.766</span> sec <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">766</span> ms\n",
       "                        \n",
       "</pre>\n"
      ],
      "text/plain": [
       "\n",
       "                        Total execution time: \u001b[1;36m0\u001b[0m\n",
       "                        min \u001b[1;36m0.766\u001b[0m sec \u001b[1;36m766\u001b[0m ms\n",
       "                        \n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "myquery.specimen.count.run()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7f254547",
   "metadata": {},
   "source": [
    "Nearly 40,000 specimens with over 50,000 files meet our search criteria! We would typically expect this number to be much larger than our number of subjects or research_subjects. First because studies will often take more than one sample per subject, and second because any given specimen might be aliquoted out to be used in multiple tests. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b3ed75e5",
   "metadata": {},
   "source": [
    "<div class=\"cdadefine\" style=\"background-color:#add9e5;color:black;padding:20px;\">\n",
    "\n",
    "<h3>Specimen Field Definitions</h3>\n",
    "\n",
    "<i><i>Any material taken as a sample from a biological entity (living or dead), or from a physical object or the environment. Specimens are usually collected as an example of their kind, often for use in some investigation.</i>\n",
    " A given specimen will have only a single subject ID and a single research subject ID</i>\n",
    "    \n",
    "    \n",
    "<style type=\"text/css\">\n",
    ".tg  {border-collapse:collapse;border-spacing:0;margin:0px auto;}\n",
    ".tg td{border-color:black;border-style:solid;border-width:1px;font-family:noto sans, sans-serif;font-size:14px;\n",
    "  overflow:hidden;padding:10px 5px;word-break:normal;}\n",
    ".tg th{border-color:black;border-style:solid;border-width:1px;font-family:noto sans, sans-serif;font-size:14px;\n",
    "  font-weight:normal;overflow:hidden;padding:10px 5px;word-break:normal;}\n",
    ".tg .tg-za14{border-color:inherit;text-align:left;vertical-align:bottom}\n",
    ".tg .tg-0pky{border-color:inherit;text-align:left;vertical-align:top}\n",
    ".tg .tg-7zrl{text-align:left;vertical-align:bottom}\n",
    ".tg .tg-0lax{text-align:left;vertical-align:top}\n",
    "</style>\n",
    "    \n",
    "<table class=\"tg\">\n",
    "<tbody>\n",
    "  <tr>\n",
    "    <td class=\"tg-0lax\"> id (`total`)</td>\n",
    "    <td class=\"tg-0lax\"> The overall number of specimens returned.</td>\n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td class=\"tg-0lax\"> files</td>\n",
    "    <td class=\"tg-0lax\"> The number of files that match this search.</td>\n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td class=\"tg-7zrl\">identifier.value(`system`)</td>\n",
    "    <td class=\"tg-0lax\"> The identifier for the data provider.</td>\n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td class=\"tg-za14\">primary_disease_type</td>\n",
    "    <td class=\"tg-0pky\"> The text term used to describe the type of malignant disease.</td>\n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td class=\"tg-7zrl\">source_material_type</td>\n",
    "    <td class=\"tg-0lax\"> The general kind of material from which the specimen was derived.</td>\n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td class=\"tg-7zrl\">specimen_type</td>\n",
    "    <td class=\"tg-0lax\"> The high-level type of the specimen, based on its how it has been derived from the original extracted sample. One of: analyte, aliquot, portion, sample, or slide.</td>\n",
    "  </tr>\n",
    "</tbody>\n",
    "</table>\n",
    "</div>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5ccb0f1c",
   "metadata": {},
   "source": [
    "### file\n",
    "\n",
    "The file endpoint returns all files that match our query:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "d6e0639a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<pre style=\"white-space:pre;overflow-x:auto;line-height:normal;font-family:Menlo,'DejaVu Sans Mono',consolas,'Courier New',monospace\">Getting results from database\n",
       "\n",
       "</pre>\n"
      ],
      "text/plain": [
       "Getting results from database\n",
       "\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<pre style=\"white-space:pre;overflow-x:auto;line-height:normal;font-family:Menlo,'DejaVu Sans Mono',consolas,'Courier New',monospace\">Waiting for results\n",
       "</pre>\n"
      ],
      "text/plain": [
       "Waiting for results\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<pre style=\"white-space:pre;overflow-x:auto;line-height:normal;font-family:Menlo,'DejaVu Sans Mono',consolas,'Courier New',monospace\">\n",
       "                Http Status: <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">500</span>\n",
       "                Error Message: <span style=\"color: #800080; text-decoration-color: #800080; font-style: italic\">None</span>\n",
       "                \n",
       "</pre>\n"
      ],
      "text/plain": [
       "\n",
       "                Http Status: \u001b[1;36m500\u001b[0m\n",
       "                Error Message: \u001b[3;35mNone\u001b[0m\n",
       "                \n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<pre style=\"white-space:pre;overflow-x:auto;line-height:normal;font-family:Menlo,'DejaVu Sans Mono',consolas,'Courier New',monospace\">\n",
       "                        Total execution time: <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">0</span>\n",
       "                        min <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">0.104</span> sec <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">104</span> ms\n",
       "                        \n",
       "</pre>\n"
      ],
      "text/plain": [
       "\n",
       "                        Total execution time: \u001b[1;36m0\u001b[0m\n",
       "                        min \u001b[1;36m0.104\u001b[0m sec \u001b[1;36m104\u001b[0m ms\n",
       "                        \n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "myquery.file.count.run()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e34044c9",
   "metadata": {},
   "source": [
    "There are a huge number of files (4099497) that match our search. Likely we would want to additionally filter the results by file format or data type to get only files we can use. See all the ways you can filter and refine searches  with more search terms in the <a href=\"../Operators\">Operators</a> notebook.\n",
    "\n",
    "\n",
    "<div class=\"cdadefine\" style=\"background-color:#add9e5;color:black;padding:20px;\">\n",
    "\n",
    "<h3>File Field Definitions</h3>\n",
    "\n",
    "<i>A file is an information-bearing electronic object that contains a physical embodiment of some information using a particular character encoding.</i>\n",
    "\n",
    "    \n",
    "<style type=\"text/css\">\n",
    ".tg  {border-collapse:collapse;border-spacing:0;}\n",
    ".tg td{border-color:black;border-style:solid;border-width:1px;font-family:noto sans, sans-serif;font-size:14px;\n",
    "  overflow:hidden;padding:10px 5px;word-break:normal;}\n",
    ".tg th{border-color:black;border-style:solid;border-width:1px;font-family:noto sans, sans-serif;font-size:14px;\n",
    "  font-weight:normal;overflow:hidden;padding:10px 5px;word-break:normal;}\n",
    ".tg .tg-7zrl{text-align:left;vertical-align:bottom}\n",
    "</style>\n",
    "<table class=\"tg\">\n",
    "<tbody>\n",
    "  <tr>\n",
    "    <td class=\"tg-0lax\"> id (`total`)</td>\n",
    "    <td class=\"tg-0lax\"> The overall number of files returned.</td>\n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td class=\"tg-7zrl\">identifier.value(`system`)</td>\n",
    "    <td class=\"tg-0lax\"> The identifier for the data provider.</td>\n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td class=\"tg-7zrl\">data_catagory</td>\n",
    "    <td class=\"tg-7zrl\">Broad categorization of the contents of the data file.</td>\n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td class=\"tg-7zrl\">data_type</td>\n",
    "    <td class=\"tg-7zrl\">Specific content type of the data file.</td>\n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td class=\"tg-7zrl\">file_format</td>\n",
    "    <td class=\"tg-7zrl\">Format of the data files.</td>\n",
    "  </tr>\n",
    "</tbody>\n",
    "</table>\n",
    "\n",
    "</div>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a899b6ca",
   "metadata": {},
   "source": [
    "### mutation\n",
    "\n",
    "The mutation endpoint returns all mutations that match our query:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "900f8b52",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<pre style=\"white-space:pre;overflow-x:auto;line-height:normal;font-family:Menlo,'DejaVu Sans Mono',consolas,'Courier New',monospace\">Getting results from database\n",
       "\n",
       "</pre>\n"
      ],
      "text/plain": [
       "Getting results from database\n",
       "\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<pre style=\"white-space:pre;overflow-x:auto;line-height:normal;font-family:Menlo,'DejaVu Sans Mono',consolas,'Courier New',monospace\">Waiting for results\n",
       "</pre>\n"
      ],
      "text/plain": [
       "Waiting for results\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<pre style=\"white-space:pre;overflow-x:auto;line-height:normal;font-family:Menlo,'DejaVu Sans Mono',consolas,'Courier New',monospace\">\n",
       "                        Total execution time: <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">0</span>\n",
       "                        min <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">8.545</span> sec <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">8545</span> ms\n",
       "                        \n",
       "</pre>\n"
      ],
      "text/plain": [
       "\n",
       "                        Total execution time: \u001b[1;36m0\u001b[0m\n",
       "                        min \u001b[1;36m8.545\u001b[0m sec \u001b[1;36m8545\u001b[0m ms\n",
       "                        \n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<pre style=\"white-space:pre;overflow-x:auto;line-height:normal;font-family:Menlo,'DejaVu Sans Mono',consolas,'Courier New',monospace\">    total : <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">904</span>     \n",
       "</pre>\n"
      ],
      "text/plain": [
       "    total : \u001b[1;36m904\u001b[0m     \n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "ename": "KeyError",
     "evalue": "'ncbi_build'",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mKeyError\u001b[0m                                  Traceback (most recent call last)",
      "\u001b[0;32m~/Documents/python/working/cda-python/venv/lib/python3.7/site-packages/IPython/core/formatters.py\u001b[0m in \u001b[0;36m__call__\u001b[0;34m(self, obj)\u001b[0m\n\u001b[1;32m    700\u001b[0m                 \u001b[0mtype_pprinters\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mtype_printers\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    701\u001b[0m                 deferred_pprinters=self.deferred_printers)\n\u001b[0;32m--> 702\u001b[0;31m             \u001b[0mprinter\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mpretty\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mobj\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    703\u001b[0m             \u001b[0mprinter\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mflush\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    704\u001b[0m             \u001b[0;32mreturn\u001b[0m \u001b[0mstream\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mgetvalue\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/Documents/python/working/cda-python/venv/lib/python3.7/site-packages/IPython/lib/pretty.py\u001b[0m in \u001b[0;36mpretty\u001b[0;34m(self, obj)\u001b[0m\n\u001b[1;32m    392\u001b[0m                         \u001b[0;32mif\u001b[0m \u001b[0mcls\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mobject\u001b[0m\u001b[0;31m \u001b[0m\u001b[0;31m\\\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    393\u001b[0m                                 \u001b[0;32mand\u001b[0m \u001b[0mcallable\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mcls\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m__dict__\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'__repr__'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 394\u001b[0;31m                             \u001b[0;32mreturn\u001b[0m \u001b[0m_repr_pprint\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mobj\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcycle\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    395\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    396\u001b[0m             \u001b[0;32mreturn\u001b[0m \u001b[0m_default_pprint\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mobj\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcycle\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/Documents/python/working/cda-python/venv/lib/python3.7/site-packages/IPython/lib/pretty.py\u001b[0m in \u001b[0;36m_repr_pprint\u001b[0;34m(obj, p, cycle)\u001b[0m\n\u001b[1;32m    698\u001b[0m     \u001b[0;34m\"\"\"A pprint that just redirects to the normal repr function.\"\"\"\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    699\u001b[0m     \u001b[0;31m# Find newlines and replace them with p.break_()\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 700\u001b[0;31m     \u001b[0moutput\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mrepr\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mobj\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    701\u001b[0m     \u001b[0mlines\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0moutput\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msplitlines\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    702\u001b[0m     \u001b[0;32mwith\u001b[0m \u001b[0mp\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mgroup\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/Documents/python/working/cda-python/cdapython/results/result.py\u001b[0m in \u001b[0;36m__repr__\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m     87\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     88\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0m__repr__\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;34m->\u001b[0m \u001b[0mstr\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 89\u001b[0;31m         \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_repr_value\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mshow_value\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mshow_sql\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     90\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     91\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0m__str__\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;34m->\u001b[0m \u001b[0mstr\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/Documents/python/working/cda-python/cdapython/results/count_result.py\u001b[0m in \u001b[0;36m_repr_value\u001b[0;34m(self, show_value)\u001b[0m\n\u001b[1;32m     43\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     44\u001b[0m                 \u001b[0;32mfor\u001b[0m \u001b[0mitem\u001b[0m \u001b[0;32min\u001b[0m \u001b[0mvalue\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 45\u001b[0;31m                     \u001b[0;32mif\u001b[0m \u001b[0mitem\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0mkey\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     46\u001b[0m                         \u001b[0mitem\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0mkey\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;34m\"null\"\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     47\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mKeyError\u001b[0m: 'ncbi_build'"
     ]
    }
   ],
   "source": [
    "myquery.mutation.count.run()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6a4fd46e",
   "metadata": {},
   "source": [
    "## Files from a single endpoint (endpoint chaining)\n",
    "\n",
    "If you want all file formats and data types, but only from a specific endpoint, you can also filter the file results by chaining endpoints together. This will return all the files that match our search AND that are specifically from specimens:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "2cfb2582",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<pre style=\"white-space:pre;overflow-x:auto;line-height:normal;font-family:Menlo,'DejaVu Sans Mono',consolas,'Courier New',monospace\">Getting results from database\n",
       "\n",
       "</pre>\n"
      ],
      "text/plain": [
       "Getting results from database\n",
       "\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<pre style=\"white-space:pre;overflow-x:auto;line-height:normal;font-family:Menlo,'DejaVu Sans Mono',consolas,'Courier New',monospace\">Waiting for results\n",
       "</pre>\n"
      ],
      "text/plain": [
       "Waiting for results\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<pre style=\"white-space:pre;overflow-x:auto;line-height:normal;font-family:Menlo,'DejaVu Sans Mono',consolas,'Courier New',monospace\">\n",
       "                Http Status: <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">500</span>\n",
       "                Error Message: <span style=\"color: #800080; text-decoration-color: #800080; font-style: italic\">None</span>\n",
       "                \n",
       "</pre>\n"
      ],
      "text/plain": [
       "\n",
       "                Http Status: \u001b[1;36m500\u001b[0m\n",
       "                Error Message: \u001b[3;35mNone\u001b[0m\n",
       "                \n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<pre style=\"white-space:pre;overflow-x:auto;line-height:normal;font-family:Menlo,'DejaVu Sans Mono',consolas,'Courier New',monospace\">\n",
       "                        Total execution time: <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">0</span>\n",
       "                        min <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">0.117</span> sec <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">117</span> ms\n",
       "                        \n",
       "</pre>\n"
      ],
      "text/plain": [
       "\n",
       "                        Total execution time: \u001b[1;36m0\u001b[0m\n",
       "                        min \u001b[1;36m0.117\u001b[0m sec \u001b[1;36m117\u001b[0m ms\n",
       "                        \n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "myquery.specimen.file.count.run()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6994e312",
   "metadata": {},
   "source": [
    "Learn more about chaining endpoints in the [Chaining endpoints](\"../AdvancedSearch-Chaining\") notebook."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.16"
  },
  "metadata": {
   "interpreter": {
    "hash": "5c867d1980d5b66d2bfc8a5903dcee074b4b68f3917a4f27f8a310cab24e9f1b"
   }
  },
  "vscode": {
   "interpreter": {
    "hash": "4578c8680ee810f847df558484335f5ffb0f004d38a87276387030f59580c508"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}