notebooks/advanced_topics/clinical_data_intro.ipynb

{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "colab_type": "text",
        "id": "view-in-github"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/ImagingDataCommons/IDC-Tutorials/blob/master/notebooks/advanced_topics/clinical_data_intro.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "m_TcNSmCPUVC"
      },
      "source": [
        "# IDC clinical data exploration\n",
        "\n",
        "The goal of this notebook is to introduce the users of NCI Imaging Data Commons (IDC) to the organization of clinical data BigQuery tables that accompany some of the IDC imaging data. For a quick visual summary of the clinical data available in IDC, please check out [this DataStudio dashboard](https://datastudio.google.com/u/0/reporting/04cf5976-4ea0-4fee-a749-8bfd162f2e87/page/p_s7mk6eybqc).\n",
        "\n",
        "[NCI Imaging Data Commons (IDC)](https://imaging.datacommons.cancer.gov) is a cloud-based repository of publicly available cancer imaging data co-located with the analysis and exploration tools and resources. IDC is a node within the broader [NCI Cancer Research Data Commons (CRDC)](https://datacommons.cancer.gov/) infrastructure that provides secure access to a large, comprehensive, and expanding collection of cancer research data.\n",
        "\n",
        "If you are not familiar with IDC, we recommend you first take a look at the [Getting started](https://github.com/ImagingDataCommons/IDC-Tutorials/blob/master/notebooks/getting_started/) notebooks that are intended to serve as the introduction into working with IDC programmatically.\n",
        "\n",
        "If you have any questions about this tutorial, please post your questions on the [IDC user forum](https://discourse.canceridc.dev/) (preferred) or email IDC support at support@canceridc.dev!\n",
        "\n",
        "Authored by Andrey Fedorov and George White\n",
        "\n",
        "Prepared: July 2022\n",
        "\n",
        "Updated: May 2023"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "40SRMJpLRbx3"
      },
      "source": [
        "# Prerequisites\n",
        "\n",
        "In order to be able to run the cells in this notebook, you must complete the prerequisites to set up your Google Cloud Platform account, as shown in this tutorial: https://github.com/ImagingDataCommons/IDC-Examples/blob/master/notebooks/getting_started/part1_prerequisites.ipynb.\n",
        "\n",
        "Once you completed the prerequisites, insert your Google Cloud Platform project ID in the cell below in place of `REPLACE_ME_WITH_YOUR_PROJECT_ID`."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "cellView": "form",
        "id": "RJ-UxuUeQsuy"
      },
      "outputs": [],
      "source": [
        "#@title Enter your Project ID\n",
        "# initialize this variable with your Google Cloud Project ID!\n",
        "my_ProjectID = \"\" #@param {type:\"string\"}\n",
        "\n",
        "import os\n",
        "os.environ[\"GCP_PROJECT_ID\"] = my_ProjectID\n",
        "\n",
        "!gcloud config set project $GCP_PROJECT_ID"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "UAQruFrsTbHQ"
      },
      "source": [
        "In the following cell you will be asked to authorize Google Collaboratory to act on your behalf. In order to proceed with the following cells you must allow this."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 2,
      "metadata": {
        "id": "o8WdiIiBQwav"
      },
      "outputs": [],
      "source": [
        "# you will need to authenticate with your Google ID to do anything meaningful with IDC\n",
        "from google.colab import auth\n",
        "auth.authenticate_user()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "P7BDaMjXykCZ"
      },
      "source": [
        "# Clinical data - background\n",
        "\n",
        "By clinical data we refer to the broad spectrum of image-related data that may accompany images. Such data may include demographics of the patients, observations related to their clinical history (therapies, diagnoses, findings), lab tests, surgeries.\n",
        "\n",
        "Clinical data is often critical in understanding imaging data, and is essential for the development and validation of imaging biomarkers. However, such data is most often stored in spreadsheets that follow conventions specific to the site that collected the data, may not be accompanied by the dictionary defining the terms used in describing clinical data, and is rarely harmonized. As an example, you can consider examing the clinical data that accompanies the [ACRIN 6698 collection](https://wiki.cancerimagingarchive.net/pages/viewpage.action?pageId=50135447) curated by The Cancer Imaging Archive (TCIA). File named \"Full Ancillary Patient Information file.xlsx\" linked from the collection page contains two sheets, one of which is the dictionary, and the othe one contains per-patient data with the columns defined by the dictionary.\n",
        "\n",
        "Not only the terms used in the clinical data accompanying individual collection are not harmonized, but the format of the spreadsheets is also collection-specific. In order to search and navigate clinical data, one has to parse those collection specific tables, and there is no interface to support searching across collections.\n",
        "\n",
        "With the release v11 of IDC, we make the attempt to lower the barriers for accessing clinical data accompanying IDC imaging collections. We parse collection-specific tables, and organize the underlying data into BigQuery tables that can be accessed using standard SQL queries. You can also see the summary of clinical data available for IDC collections in [this dashboard](https://datastudio.google.com/u/0/reporting/04cf5976-4ea0-4fee-a749-8bfd162f2e87/page/p_s7mk6eybqc). \n",
        "\n",
        "At the completion of this tutorial you will learn how IDC clinical data is organized, and how to write queries to interrogate this data."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "29H1Kp5dTYBb"
      },
      "source": [
        "# Organization of clinical data in IDC\n",
        "\n",
        "Clinical data accompanying imaging collections is organized into collection-specific tables in the `idc_current_clinical` BigQuery dataset (in order to locate this dataset, you will need to navigate to the [BigQuery console](https://console.cloud.google.com/bigquery), and locate `idc_current_clinical` under the `bigquery-public-data` project, which you should have added in the prerequisites mentioned earlier. \n",
        "\n",
        "When a certain imaging collection is accompanied by a single clinical sheet, the BigQuery table will be named `<collection_id>_clinical`. For the collection that have multiple clinical sheets (saved as either separate files, or separate Excel sheets), the tables will be named `<collection_id>_<sheet_id>`. "
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "j-_rqQZuJoyi"
      },
      "source": [
        "## Inventory of clinical data tables: `table_metadata`"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "TLhEHQkNWkac"
      },
      "outputs": [],
      "source": [
        "# python API is the most flexible way to query IDC BigQuery metadata tables\n",
        "from google.cloud import bigquery\n",
        "bq_client = bigquery.Client(my_ProjectID)\n",
        "\n",
        "selection_query = \"\"\"\n",
        "SELECT *\n",
        "FROM bigquery-public-data.idc_current_clinical.table_metadata\n",
        "ORDER BY collection_id\n",
        "\"\"\"\n",
        "\n",
        "selection_result = bq_client.query(selection_query)\n",
        "table_metadata_df = selection_result.result().to_dataframe()\n",
        "\n",
        "table_metadata_df"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "B9Vgq-gtI9G4"
      },
      "source": [
        "The per-row attributes stored in this table aim to describe the content of the specific clinical table, its versioning and provenance (what specific file/sheet was used to populate the content of the table).\n",
        "\n",
        "Column `table_name` contains the fully resolved pointer to the BigQuery table with the parsed clinical data."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "rblrQXIVIc4o"
      },
      "source": [
        "## Inventory of clinical data \"dictionary terms\": `column_metadata`\n",
        "\n",
        "The purpose of the clinical data \"dictionary terms\" is to to facilitate exploration of and access to the clinical data. Although the content of the clinical data sheets is for the most part not harmonized, they follow the same general structure:\n",
        "\n",
        "1. files/sheets that contain per-patient rows of clinical data attributes, defined by column headers\n",
        "2. (in some cases) file/sheet defining the meaning of the individual columns and (in some cases, and when applicable) the dictionary defining the enumerated values for the specific\n",
        "\n",
        "The IDC inventory of the clinical data \"dictionary terms\" contains the complete list of column names, across all clinical sheets, and per-column attributes to facilitate exploration. Let's look at the content to better understand what this means."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "4bERb4Qxk9EK"
      },
      "outputs": [],
      "source": [
        "selection_query = \"\"\"\n",
        "SELECT *\n",
        "FROM bigquery-public-data.idc_current_clinical.column_metadata\n",
        "ORDER BY collection_id\n",
        "\"\"\"\n",
        "\n",
        "selection_result = bq_client.query(selection_query)\n",
        "column_metadata_df = selection_result.result().to_dataframe()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "l6PMXWDHlQjf"
      },
      "outputs": [],
      "source": [
        "column_metadata_df.columns"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "uqC59zgzLUYi"
      },
      "source": [
        "The only column that is initialized consistently across those collection-specific tables is `dicom_patient_id`, which corresponds to the `PatientID` column in the `dicom_all` table. `dicom_patient_id` is the only column that is curated by IDC, since the original clinical data tables do not follow the consistent convention while referring to the patient identification.\n",
        "\n",
        "Note that some imaging collections do not have any clinical data at all, while others may have multiple tables containing various types of clinical data.\n",
        "\n",
        "`column_metadata` should be used as a \"key\" for identifying information of interest in clinical metadata. It contains information about the columns across all of the collections for which we have clinical data."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "_Tb_7AWmlyGa"
      },
      "source": [
        "The most important columns in this table are the following:\n",
        "* `column`: name of the column in a collection-specific clinical metadata table; note that for some of the collections this name may not contain any human readable information, and may instead be a coded value\n",
        "* `column_label`: for some collections it may contain a description for a column name that otherwise may not be human readable. For other collections it is the original name of the column in the source Excel file\n",
        "* `collection_id` and `table_name` identify the collection the specific column corresponds to, and the name of the clinical data table where this column is present.\n",
        "\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "fzbLawNtdl0p"
      },
      "source": [
        "Here's an example of the columns that correspond to the clinical metadata in one of the ACRIN collections, where `column` is not particularly helpful, but `column_label` provides human readable information to allow interpretation of the column."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "nGsS6SvPdrWn"
      },
      "outputs": [],
      "source": [
        "column_metadata_df[column_metadata_df[\"collection_id\"] == \"acrin_nsclc_fdg_pet\"][:5]"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "m0H78L-VdxHn"
      },
      "source": [
        "For some columns, the values come from a defined set. In the example above, we can, for example, examine the values assigned to encode patient race."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "9NCP15tRd2gU"
      },
      "outputs": [],
      "source": [
        "race_values = column_metadata_df[(column_metadata_df[\"collection_id\"] == \"acrin_nsclc_fdg_pet\") & (column_metadata_df[\"column_label\"] == \"RACE\")][\"values\"]\n",
        "\n",
        "race_values.tolist()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "yaoJvf2nd6wL"
      },
      "source": [
        "On the other hand, if we look at the metadata available for the `c4kc_kits` collection, `column_label` and `column` are identical. \n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "AYMJ8kjTd9eb"
      },
      "outputs": [],
      "source": [
        "column_metadata_df[column_metadata_df[\"collection_id\"] == \"c4kc_kits\"][:3]"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "voowbwXCeFBF"
      },
      "source": [
        "**As a general rule of thumb**:\n",
        "* when selecting specific columns from clinical tables, use `column` values\n",
        "* when searching for concepts of interest in `column_metadata`, use `column_label`"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "gWgLxoraN3mY"
      },
      "source": [
        "# Exploring IDC clinical data\n",
        "\n",
        "In the following sections of the notebook we go over some use cases to demonstrate various options for navigating IDC clinical data. \n",
        "\n",
        "As always, if you have a use case that is not addressed here, if you have suggestions or are confused - please start a discussion thread on the [IDC User forum](https://discourse.canceridc.dev/)!"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "03rwe7OfvsnV"
      },
      "source": [
        "## Collection-focused exploration\n",
        "\n",
        "If you used clinical data stored in TCIA, perhaps you started with a specific collection of your interest, downloaded clinical data files for that collection, parsed them into your code. Let's go over those steps the IDC way!\n",
        "\n",
        "First, let's see which of the collections in IDC have clinical data in BigQuery. To do that we will reuse the pandas dataframe with the results of the query we executed earlier.\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "TIZq1GbQoTIC"
      },
      "outputs": [],
      "source": [
        "column_metadata_df[\"collection_id\"].unique().tolist()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "_KoEJ_Wvwa79"
      },
      "source": [
        "If you are interested in what clinical data is available for the specific collection, you can select only the rows corresponding to that collection in the `column_metadata` table. Here we select a subset of columns to improve readability of the dataframe.\n",
        "\n",
        "Note that for some collections, clinical data sheets are accompanied by dictionaries, which formalize the values encountered. Examples of such collections are [ISPY1](https://wiki.cancerimagingarchive.net/display/Public/ISPY1) or ACRIN trials.\n",
        "\n",
        "For many other collections there are no such dictionaries available. In those situations, the values you will see in the `values` columns have been derived by examining the distinct values encountered in the clinical data sheets.\n",
        "\n",
        "In the following we look at the clinical data columns (\"dictionary terms\") for the [ACRIN 6698 collection](https://wiki.cancerimagingarchive.net/pages/viewpage.action?pageId=50135447) mentioned earlier."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "zrtRWr_SwmIJ"
      },
      "outputs": [],
      "source": [
        "acrin6698_clinical_df = column_metadata_df[column_metadata_df[\"collection_id\"] == \"acrin_6698\"] \n",
        "\n",
        "acrin6698_clinical_df"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Dm5cAn73SQJW"
      },
      "source": [
        "Here's how you can select just the specific columns in the table - this way it is easier to examine the data."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "4mY1gbR7ysxx"
      },
      "outputs": [],
      "source": [
        "acrin6698_clinical_df[[\"collection_id\", \"table_name\", \"column\", \"column_label\", \"data_type\", \"values\"]]"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "CXy4mOdj428I"
      },
      "source": [
        "`table_name` gives us the location of the BigQuery table that contains the column described in the `column_metadata` row.\n",
        "\n",
        "Given the information available in the per-collection clinical data, we can proceed with selecting a subset of patients that meet the criteria of your interest. As an example, the following query will select all of the distinct combination of patient ID and tumor grade, as defined by the `sbrgrade` column contents.\n",
        "\n",
        "Note that in this case we use `%%bigquery` magic to execute the query - this is done just to demonstrate an alternative ways to work with SQL. You can switch between this approach and BigQuery Python API based on your preference! \n",
        "\n",
        "When you use `%%bigquery` magic, the result of the query will be saved into a pandas dataframe you specify as the last argument to the call (`acrin_6698_tumors`, in the example below). If you skip that argument, the result will be displayed in the cell, but you cannot reuse it later."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "BGlJeRjJ6poR"
      },
      "outputs": [],
      "source": [
        "%%bigquery acrin_6698_tumors --project=$my_ProjectID\n",
        "\n",
        "SELECT \n",
        "  DISTINCT(dicom_patient_id), sbrgrade\n",
        "FROM \n",
        "  bigquery-public-data.idc_current_clinical.acrin_6698_clinical\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "ZsdIVcNq9QQc"
      },
      "outputs": [],
      "source": [
        "acrin_6698_tumors"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "EE5N8y7RUw6a"
      },
      "source": [
        "We can next use `dicom_patient_id` to link clinical data with the imaging studies available for the given patient. The query becomes a bit more complex, since we need to join data across two tables."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "gpxUao0JU8GJ"
      },
      "outputs": [],
      "source": [
        "%%bigquery acrin_6698_viewable --project=$my_ProjectID \n",
        "\n",
        "SELECT\n",
        "  ANY_VALUE(PatientID) AS PatientID,\n",
        "  STRING_AGG(DISTINCT(acrin_6698_clinical.sbrgrade)) as tumor_grade,\n",
        "  STRING_AGG(DISTINCT(Modality)) AS modalities,\n",
        "  COUNT(DISTINCT(SeriesInstanceUID)) AS num_series,\n",
        "  ANY_VALUE(CONCAT(\"https://viewer.imaging.datacommons.cancer.gov/viewer/\", StudyInstanceUID)) AS viewer_url\n",
        "FROM\n",
        "  bigquery-public-data.idc_current.dicom_all AS dicom_all\n",
        "JOIN\n",
        "  bigquery-public-data.idc_current_clinical.acrin_6698_clinical AS acrin_6698_clinical\n",
        "ON\n",
        "  dicom_all.PatientID = acrin_6698_clinical.dicom_patient_id\n",
        "GROUP BY\n",
        "  StudyInstanceUID\n",
        "ORDER BY\n",
        "  PatientID"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "tqb7ONv5a_6_"
      },
      "outputs": [],
      "source": [
        "acrin_6698_viewable"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "X7OrCFT3aQhT"
      },
      "source": [
        "Remember that Google Cloud [BigQuery console](https://console.cloud.google.com/bigquery) is very handy in interactive exploration of tables and debugging of the queries! Until you become familiar with BigQuery and SQL, it is not recommended that you debug queries from the notebook cells. \n",
        "\n",
        "As an exercise, try to modify the query above to include count of distinct values of `SOPInstanceUID` in the query result (that number will correspond to the number of files you would be downloading for a given study)."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "-38R6Z_3veHG"
      },
      "source": [
        "## Discovery mode\n",
        "\n",
        "Sometime you may want to find whether specific clinical attribute is available for the imaging data you can find in IDC. \n",
        "\n",
        "We can start by looking at the distinct values of `column_label` (which in the general case will be either more descriptive, or identical to `column`).\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "tt3htrbxr6KC"
      },
      "outputs": [],
      "source": [
        "import pandas as pd\n",
        "\n",
        "pd.DataFrame({\"column_label\":column_metadata_df[\"column_label\"].unique()})"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "7iZpB1Foi1hd"
      },
      "source": [
        "Let's say we want to know which of the cases have information related to therapy. We can search column metadata for the presence of word \"therapy\" (since the terms in clinical data are not harmonized, we need to account for the variability in capitalization)."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "qCTFR-QpjJSw"
      },
      "outputs": [],
      "source": [
        "column_metadata_df[column_metadata_df[\"column_label\"].str.contains(\"[tT]herapy\", na=False)][[\"column_label\", \"collection_id\", \"values\"]]"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "V23hIu1jo-We"
      },
      "source": [
        "We observe that there are several collections that contain column named \"Chemotherapy\". Let's filter these values further, in order to identify subjects that underwent chemotherapy."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "uACJ3N_GFHFG"
      },
      "outputs": [],
      "source": [
        "column_metadata_df[column_metadata_df[\"column_label\"].str.contains(\"[Cc]hemotherapy\", na=False)][[ \"collection_id\", \"table_name\", \"column\", \"column_label\",\"values\"]]"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "kke1DZgwGHuA"
      },
      "source": [
        "From the table above we can observe that:\n",
        "\n",
        "1. There are several collections that have clinical metadata related to chemotherapy regimen of the subject; \n",
        "\n",
        "2. There are no value sets assigned for the column `induction_chemotherapy` in the table `hnscc_clinical`. Value sets may be missing if the dictionary is not provided, or if there are a large number (>20) distinct values of the column in the table. In such cases one can check the distinct values of the column by querying the table directly.  \n",
        "\n",
        "Looking at the value sets for the collections/columns that have those, we can observe that subjects that had any chemotherapy could be selected as follows for the respective collections (non-exhaustive list):\n",
        "* `head_neck_radiomics_hn1`: subjects that have value other than `none` in table `head_neck_radiomics_hn1_clinical` column `chemotherapy_given`\n",
        "* `hnscc`: subjects that have value other than `No` in table `hnscc_clinical` column `chemotherapy_regime`\n",
        "* `hnscc_3dct_rt`: subjects that have value other than `None` in table `hnscc_3dct_rt_demographics` column `chemotherapy_medication`\n",
        "* `nsclc_radiogenomics`: subjects that have value `Yes` in table `nsclc_radiogenomics_clinical` column `chemotherapy`\n",
        "* `hcc_tace_seg` column `chemotherapy` in the `hcc_tace_seg_clinical` table\n",
        "\n",
        "Let's focus on the clinical data related to chemotherapy for the collection `hcc_tace_seg`."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "EhrEq84tezyL"
      },
      "outputs": [],
      "source": [
        "chemotherapy_subset = column_metadata_df[column_metadata_df[\"column_label\"].str.contains(\"[Cc]hemotherapy\", na=False)][[ \"collection_id\", \"table_name\", \"column\", \"column_label\",\"values\"]]\n",
        "\n",
        "chemotherapy_subset = chemotherapy_subset[chemotherapy_subset[\"collection_id\"] == \"hcc_tace_seg\"]\n",
        "\n",
        "chemotherapy_subset"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "NGu6dVggelsQ"
      },
      "source": [
        "Before we select subjects that meet the criteria defined above, let's confirm  the values encountered in table `hcc_tace_seg_clinical` column `chemotherapy` match the value set in the `column_metadata` table. Here are the values of the dictionary we observe from the `column_metadata` inventory."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "pcJeHJrifjIG"
      },
      "outputs": [],
      "source": [
        "chemotherapy_subset[\"values\"].tolist()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "GEKd7J4Dfa46"
      },
      "source": [
        "The query below will select the distinct values encountered in the `chemotherapy` column of the `hcc_tace_seg_clinical` table, which we confirm matches those in the dictionary."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "sUdb9uOoJg7p"
      },
      "outputs": [],
      "source": [
        "selection_query = \"\"\"\n",
        "SELECT DISTINCT(chemotherapy)\n",
        "FROM bigquery-public-data.idc_current_clinical.hcc_tace_seg_clinical\n",
        "\"\"\"\n",
        "\n",
        "selection_result = bq_client.query(selection_query)\n",
        "hcc_tace_seg_clinical_df = selection_result.result().to_dataframe()\n",
        "hcc_tace_seg_clinical_df"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "QQRPfWs7GsEg"
      },
      "source": [
        "Next we can examine the data to see what therapy individual patients had."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "i0W1qTE1G_X4"
      },
      "outputs": [],
      "source": [
        "selection_query = \"\"\"\n",
        "SELECT DISTINCT(dicom_patient_id), chemotherapy\n",
        "FROM bigquery-public-data.idc_current_clinical.hcc_tace_seg_clinical\n",
        "WHERE chemotherapy <> \"null\"\n",
        "\"\"\"\n",
        "\n",
        "selection_result = bq_client.query(selection_query)\n",
        "hcc_tace_seg_clinical_df = selection_result.result().to_dataframe()\n",
        "hcc_tace_seg_clinical_df"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "NjdEJe1rHvQC"
      },
      "source": [
        "`dicom_patient_id` is the key to connect clinical data and imaging data. Let's see what imaging studies we have for patient `HCC_101` that was treated with \tdoxorubicin LC beads. Along the way we can also generate viewer URLs to conveniently examine the images."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "gVpDfdv0H9Jb"
      },
      "outputs": [],
      "source": [
        "%%bigquery --project=$my_ProjectID\n",
        "\n",
        "SELECT \n",
        "  StudyInstanceUID, \n",
        "  STRING_AGG(DISTINCT(Modality)) AS modalities, \n",
        "  STRING_AGG(DISTINCT(collection_id)) AS collection_id, \n",
        "  STRING_AGG(DISTINCT(Access)) AS access,\n",
        "  COUNT(DISTINCT(SeriesInstanceUID)) AS num_series,\n",
        "  ANY_VALUE(CONCAT(\"https://viewer.imaging.datacommons.cancer.gov/viewer/\", StudyInstanceUID)) as viewer_url\n",
        "FROM bigquery-public-data.idc_current.dicom_all\n",
        "WHERE PatientID = \"HCC_103\"\n",
        "GROUP BY StudyInstanceUID"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "XaPx7APdr-MV"
      },
      "source": [
        "## Patient-focused exploration\n",
        "\n",
        "Sometime you may want to know whether specific patient has any clinical data available. One way to do this is to locate the collection that patient belongs to, and then check whether any of the clinical data tables (if any) that are available for that collection have that patient identifier.\n",
        "\n",
        "Alternatively, we can build a complete list of patients that have clinical data by performing a union on all of the `dicom_patient_id` columns across all of the clinical data tables, which is what we do in the next cell.\n",
        "\n",
        "In this query we use the ability of BigQuery to [query multiple tables using a wildcard table](https://cloud.google.com/bigquery/docs/querying-wildcard-tables). Note that here we refer to the specific version of the data, since `idc_current_clinical` dataset contains views, which cannot be queried through prefix."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "d8k3apaS-gfs"
      },
      "outputs": [],
      "source": [
        "import re\n",
        "\n",
        "selection_query = \"\"\"\n",
        "SELECT\n",
        "  DISTINCT(dicom_patient_id)\n",
        "FROM\n",
        "  `bigquery-public-data.idc_v14_clinical.*`\n",
        "WHERE\n",
        "  _TABLE_SUFFIX NOT IN (\"table_metadata\",\n",
        "    \"column_metadata\" )\n",
        "\"\"\"\n",
        "\n",
        "selection_result = bq_client.query(selection_query)\n",
        "patients_df = selection_result.result().to_dataframe()\n",
        "\n",
        "patients_df\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "rixPlivvj9de"
      },
      "source": [
        "# Want to learn more?\n",
        "\n",
        "* check out other notebooks: https://github.com/ImagingDataCommons/IDC-Examples/tree/master/notebooks\n",
        "* join our community forum to ask any questions about IDC: https://discourse.canceridc.dev/\n",
        "* ask your questions during live discussions with IDC developers at the IDC weekly office hours - join us on Google Meet at https://meet.google.com/xyt-vody-tvb every Tuesday 16:30 – 17:30 (New York) and Wednesday 10:30-11:30 (New York)\n",
        "* browse IDC portal: https://imaging.datacommons.cancer.gov/explore/\n",
        "* read IDC paper: http://dx.doi.org/10.1158/0008-5472.CAN-21-0950\n",
        "* watch a recent presentation about IDC: https://youtu.be/P9ateg9ZUEs"
      ]
    }
  ],
  "metadata": {
    "colab": {
      "include_colab_link": true,
      "provenance": [],
      "toc_visible": true
    },
    "kernelspec": {
      "display_name": "Python 3",
      "name": "python3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}