notebooks/getting_started/part3_exploring_cohorts.ipynb

{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "<a href=\"https://colab.research.google.com/github/ImagingDataCommons/IDC-Tutorials/blob/master/notebooks/getting_started/part3_exploring_cohorts.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "KmXfYFZtja2F"
      },
      "source": [
        "# Getting started with IDC - Part 3: Advanced searching using Google BigQuery\n",
        "\n",
        "---\n",
        "\n",
        "## Summary\n",
        "\n",
        "This notebook is part of [the \"Getting started with IDC\" notebook series](https://github.com/ImagingDataCommons/IDC-Examples/blob/master/notebooks/getting_started) introducing NCI Imaging Data Commons to the users who want to interact with IDC programmatically.\n",
        "\n",
        "In [Part 2](https://github.com/fedorov/IDC-Tutorials/blob/master/notebooks/getting_started/part2_searching_basics.ipynb) you learned about the basics of working with IDC using `idc-index` package and the metadata it provides for subsetting IDC data.\n",
        "\n",
        "In this notebook you can learn how to gain full access to the metadata accompanying IDC data, which includes all of the DICOM attributes. Ability to access full metadata makes it possible to define very powerful search criteria to, for example, select images that contain segmentation of a specific organ, or filter by the value of a particular radiomics feature.\n",
        "\n",
        "Due to the size of the complete IDC metadata, we need special tools that can efficiently perform queries agains this amount of metadata.\n",
        "\n",
        "[Google BigQuery](https://cloud.google.com/bigquery) is a serverless cloud-based analytic platform that provides SQL interface to query and analyze large amounts of tabular data.\n",
        "\n",
        "---\n",
        "Initial version: Nov 2022\n",
        "\n",
        "Updated: May 2024"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "07dGiFgK12BQ"
      },
      "source": [
        "## What is IDC?\n",
        "\n",
        "[NCI Imaging Data Commons (IDC)](https://datacommons.cancer.gov/repository/imaging-data-commons) 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) infrastructure that provides secure access to a large, comprehensive, and expanding collection of cancer research data."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "8kHfbIFLIei-"
      },
      "source": [
        "## Prerequisites\n",
        "\n",
        "In order to proceed with this tutorial you must have a Google account, and first you must complete the BigQuery prerequisites that are available in the \"Getting started with BigQuery\" section of the [Part 1](https://github.com/ImagingDataCommons/IDC-Examples/blob/master/notebooks/getting_started/part1_prerequisites.ipynb) of the \"Getting started\" tutorial series.\n",
        "\n",
        "Assuming you completed the above, you should have Google Cloud ProjectID that you should insert in the following cell. This project ID will be used for all supsequent operations that utilize Google Cloud. Note that the following cell will prompt you to log in with your Google account and authorize Google Collaboratory to access certain services."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "M3uMIzDYIirn"
      },
      "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",
        "from google.colab import auth\n",
        "auth.authenticate_user()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "lURCyrUu28se"
      },
      "source": [
        "## Why do I need to search?\n",
        "\n",
        "Think of IDC as a library. Image files are books, and we have ~65 TB of those. When you go to a library, you want to check out just the books that you want to read. In order to find a book in a large library you need a catalog.\n",
        "\n",
        "Just as in the library, IDC maintains a catalog that indexes a variety of metadata fields describing the files we curate. That metadata catalog is accessible in a large database table that you should be using to search and subset the images. Each row in that table corresponds to a file, and includes the location of the file alongside the metadata attributes describing that file.\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "CN7gv_hj3BWB"
      },
      "source": [
        "## How do I search?\n",
        "\n",
        "When you search, or _query_ IDC catalog, you specify what criteria should the metadata describing the selected files satisfy.\n",
        "\n",
        "Queries can be as simple as\n",
        "\n",
        "* \"_everything in collection X_\",\n",
        "\n",
        "or as complex as\n",
        "\n",
        "* \"_files corresponding to CT images of female patients that are accompanied by annotations of lung tumors that are larger than 1500 mm^3 in volume_\".\n",
        "\n",
        "Although it would be very nice to just state what you need in free form, in practice queries need to be written in a formal way.\n",
        "\n",
        "IDC organizes all of the metadata into large tables, where each row corresponds to one image file (as of IDC data release v18, we index >45 millions of files) and each column represents a metadata attribute present in one or more files in IDC (currently, we index hundreds of such attributes).\n",
        "\n",
        "IDC metadata tables are maintained in [GCP BigQuery](https://cloud.google.com/bigquery),  with only a tiny subset of the attributes indexed in the catalog available via the [IDC Portal exploration page](https://imaging.datacommons.cancer.gov/explore/). IDC metadata can be queried using Standard Query Language (SQL), and does not require learning any IDC-specific API.\n",
        "\n",
        "In the following steps of the tutorial we will use just a few of the attributes (SQL table columns) to get started. You will be able to use the same principles and SQL queries to extend your search criteria to include any of the other attributes indexed by IDC."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "XCgWllXk3eDs"
      },
      "source": [
        "## First query and BigQuery workspace\n",
        "\n",
        "To get started, let's build the queries that replicate some of the information about IDC data shown in the IDC Portal.\n",
        "\n",
        "As the very first query, let's get the list of all the image collections available in IDC. Here is that query:\n",
        "\n",
        "```sql\n",
        "SELECT\n",
        "  DISTINCT(collection_id)\n",
        "FROM\n",
        "  bigquery-public-data.idc_current.dicom_all\n",
        "```\n",
        "\n",
        "To run this query interactively, copy the query above to the clipboard, paste it into the query tab in the [BigQuery SQL workspace](https://console.cloud.google.com/bigquery), and hit the \"Run\" button. Within few moments you should be able to see the list of collections in IDC in the \"Query results\" section of the interface.\n",
        "\n",
        "![bq_run](https://www.dropbox.com/s/6ah98n6e9ik18if/bq_run.png?raw=1)\n",
        "\n",
        "Let's look into how this query works:\n",
        "* `SELECT` defines the list of columns that should be returned by the query,\n",
        "* `DISTINCT` indicates that we want to see the distinct values encountered in the selected column,\n",
        "* `FROM` defines which table should be queried.\n",
        "\n",
        "In this query we work with the [`dicom_all` table](https://console.cloud.google.com/bigquery?p=bigquery-public-data&d=idc_current&t=dicom_all&page=table), which contains the DICOM metadata extracted from IDC images along with collection-level metadata that does not originate from DICOM."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "k8F25l0W3j6m"
      },
      "source": [
        "## Organization of IDC metadata in BigQuery tables\n",
        "\n",
        "Let's take a moment to look into the table used in the `FROM` clause of our query: [`bigquery-public-data.idc_current.dicom_all`](https://console.cloud.google.com/bigquery?p=bigquery-public-data&d=idc_current&t=dicom_all&page=table).\n",
        "\n",
        "This name is like an address that allows to locate the specific table in BigQuery. This \"address\" consists of three components: <project_id>.<dataset_id>.<table_id>\n",
        "\n",
        "1. `bigquery-public-data` is the ID of a public GCP _project_ that is maintained by Google Public Datasets Program. IDC-curated BigQuery tables with the metadata about IDC images are included in this project.\n",
        "2. `idc_current`is a _dataset_ within the `bigquery-public-data` project. Think of BigQuery datasets as containers that are used to organize and control access to the tables within the project.\n",
        "3. `dicom_all` is one of the tables within the `idc_current` dataset. As you spend more time learning about IDC, you will hopefully leverage other tables available in that dataset.\n",
        "\n",
        "If you now look back at the [BigQuery console](https://console.cloud.google.com/bigquery) and expand the list of datasets under the `bigquery-public-data` project, you will see that in addition to the `idc_current` dataset there are also datasets `idc_v14`, `idc_v13`, etc all the way to `idc_v1`. Those datasets correspond to the IDC data release versions, with `idc_current` being an alias for the latest (at the moment of writing this, v14 is the latest release) version of IDC data.\n",
        "\n",
        "We will not spend time discussing how IDC versioning works, but it is important to know that\n",
        "\n",
        "1. IDC data is versioned;\n",
        "2. queries against the `idc_current` dataset are equivalent to the queries against the latest version (currently, `idc_v14`) of IDC data;\n",
        "3. if you want the results of the queries to be persistent, write those against `idc_v*` datasets instead of `idc_current`."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "qFQXjWtO3nia"
      },
      "source": [
        "## Same query using Python SDK\n",
        "\n",
        "BigQuery SQL workspace is a very convenient tool for exploring schemas of the tables, experimenting with writing and debugging queries, profiling their execution. But you can also run those queries programmatically, which is very convenient if you want to direct the result the query into a pandas dataframe, or just perform your searches programmatically.\n",
        "\n",
        "BigQuery API support is implemented in a variety of languages, with the python bindings available in the `google-cloud-bigquery` package. Conveniently, this package is pre-installed in Colab!\n",
        "\n",
        "**HINT**: SQL query syntax is not sensitive to indentation or capitalization - although those are quite helpful to make the query more readable!"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "8L-vdz973qNE"
      },
      "outputs": [],
      "source": [
        "from google.cloud import bigquery\n",
        "\n",
        "# BigQuery client is initialized with the ID of the project\n",
        "# we specified in the beginning of the notebook!\n",
        "bq_client = bigquery.Client(my_ProjectID)\n",
        "\n",
        "selection_query = \"\"\"\n",
        "SELECT\n",
        "  DISTINCT(collection_id)\n",
        "FROM\n",
        "  bigquery-public-data.idc_current.dicom_all\n",
        "\"\"\"\n",
        "\n",
        "selection_result = bq_client.query(selection_query)\n",
        "selection_df = selection_result.result().to_dataframe()\n",
        "\n",
        "selection_df"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "AtXB7F_P3v5h"
      },
      "source": [
        "## Exploring other IDC portal attributes via SQL\n",
        "\n",
        "Next we will explore few other attributes that are available in the [IDC portal](https://imaging.datacommons.cancer.gov/) (with the few exceptions, the mapping is pretty intuitive):\n",
        "\n",
        "![portal_filters](https://www.dropbox.com/s/qt3dhzara1ap7s3/portal_filters.png?raw=1)\n",
        "\n",
        "* \"Primary site location\" maps to the `collection_tumorLocation` column\n",
        "* \"Body Part Examined\" -> `BodyPartExamined`\n",
        "* \"Modality\" -> `Modality`\n",
        "\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "I_9G4E3431As"
      },
      "source": [
        "### Exercise 1: Write the query to list all values for `collection_tumorLocation`\n",
        "\n",
        "In the cell below, we pre-filled the code by copying the previous cell. All you need to do is write the query!\n",
        "\n",
        "As always, you can use the the Editor tab in the [BigQuery SQL workspace](https://console.cloud.google.com/bigquery) to experiment and debug the query if you prefer!"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "2vhLBDKd3441"
      },
      "outputs": [],
      "source": [
        "from google.cloud import bigquery\n",
        "\n",
        "# BigQuery client is initialized with the ID of the project\n",
        "# we specified in the beginning of the notebook!\n",
        "bq_client = bigquery.Client(my_ProjectID)\n",
        "\n",
        "# Execution of this cell will fail unless you wrote the query below!\n",
        "selection_query = \"\"\"\n",
        "REPLACE THIS TEXT WITH YOUR QUERY!\n",
        "\"\"\"\n",
        "\n",
        "selection_result = bq_client.query(selection_query)\n",
        "selection_df = selection_result.result().to_dataframe()\n",
        "\n",
        "selection_df"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "2vxUIqRE3-LV"
      },
      "source": [
        "## Combining multiple attributes in a single query\n",
        "\n",
        "With the understanding of the attributes that are available in IDC, you can use SQL to build queries that summarize those attributes to better understand the data.\n",
        "\n",
        "In the following, we use the earlier discussed attributes to list distinct values encountered for tumor locations, body parts examined and modalities for the individual collections. We will also use the attribute that captures the species covered by the collection."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "tm7eE_od4Ahn"
      },
      "outputs": [],
      "source": [
        "from google.cloud import bigquery\n",
        "\n",
        "# BigQuery client is initialized with the ID of the project\n",
        "# we specified in the beginning of the notebook!\n",
        "bq_client = bigquery.Client(my_ProjectID)\n",
        "\n",
        "selection_query = \"\"\"\n",
        "SELECT\n",
        "  collection_id,\n",
        "  STRING_AGG(DISTINCT(Modality)) as modalities,\n",
        "  STRING_AGG(DISTINCT(collection_tumorLocation)) as tumor_locations,\n",
        "  STRING_AGG(DISTINCT(collection_species)) as species\n",
        "FROM\n",
        "  bigquery-public-data.idc_current.dicom_all\n",
        "GROUP BY\n",
        "  collection_id\n",
        "\"\"\"\n",
        "\n",
        "selection_result = bq_client.query(selection_query)\n",
        "selection_df = selection_result.result().to_dataframe()\n",
        "\n",
        "selection_df"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Z7D1aq6R4Dca"
      },
      "source": [
        "In the query above, we use the familiar operators `SELECT` and `FROM`, but also couple of new ones:\n",
        "\n",
        "* `GROUP BY` in the end of the query indicates that we want to get a single row per the distinct value of the `collection_id`\n",
        "* `STRING_AGG` and `DISTINCT` indicate how the values of the selected columns should be aggregated while combining into single row per `collection_id`: we take all the distinct values per individual `collection_id`, and the concatenate them into a single string\n",
        "\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "BtEb8C7o4Gw0"
      },
      "source": [
        "### Exercise 2: Use multiple criteria to select image collections of interest\n",
        "\n",
        "In the previous query we learned how to get collection-level list of the modalities and tumor locations. In this exercise you task is to write a query that identifies all of the collections that include MR modality and \"Lung\" as a tumor location.\n",
        "\n",
        "As always, you can use the the Editor tab in the [BigQuery SQL workspace](https://console.cloud.google.com/bigquery) to experiment and debug the query if you prefer!"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "ZBiQPuRI4H0p"
      },
      "outputs": [],
      "source": [
        "from google.cloud import bigquery\n",
        "\n",
        "# BigQuery client is initialized with the ID of the project\n",
        "# we specified in the beginning of the notebook!\n",
        "bq_client = bigquery.Client(my_ProjectID)\n",
        "\n",
        "# Execution of this cell will fail unless you wrote the query below!\n",
        "selection_query = \"\"\"\n",
        "SELECT\n",
        "  DISTINCT(collection_id)\n",
        "FROM\n",
        "  bigquery-public-data.idc_current.dicom_all\n",
        "WHERE\n",
        "  # write the selection criteria under this line!\n",
        "  # Use AND operator to combine the filter values for the\n",
        "  # Modality and collection_tumorLocation to select collections that\n",
        "  # include MR images for Lung cancer locations\n",
        "  # Note that SQL uses single = for comparison, and strings should\n",
        "  # be enclosed in \"\"\n",
        "\"\"\"\n",
        "\n",
        "selection_result = bq_client.query(selection_query)\n",
        "selection_df = selection_result.result().to_dataframe()\n",
        "\n",
        "selection_df"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "YEqcnryk4NCb"
      },
      "source": [
        "## DICOM data model: Patients, studies, series and instances\n",
        "\n",
        "Up to now we searched the data at the granularity of the collections. In practice, we often want to know how many patients meet our search criteria, or what are the specific images that we need to download.\n",
        "\n",
        "IDC is using DICOM for data representation, and in the DICOM data model, patients (identified by `PatientID`) undergo imaging exams (or _studies_, in DICOM nomenclature).\n",
        "\n",
        "Each patient will have one or more studies, with each study identified uniquely by the attribute `StudyInstanceUID`. During each of the imaging studies one or more imaging _series_ will be collected. As an example, a Computed Tomography (CT) imaging study may include a volume sweep before and after administration of the contrast agent. Imaging series are uniqiely identified by `SeriesInstanceUID`.\n",
        "\n",
        "Finally, each imaging series contains one or more _instances_, where each instance corresponds to a file. Most often, one instance corresponds to a single slice from a cross-sectional image. Individual instances are identified by unique `SOPInstanceUID` values.\n",
        "\n",
        "Let's go over some examples of how those concepts can help us navigate data in IDC. Here is how we would count all the patients that have MR (Magnetic Resonance) images:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "YkqtA6li4TGK"
      },
      "outputs": [],
      "source": [
        "from google.cloud import bigquery\n",
        "\n",
        "# BigQuery client is initialized with the ID of the project\n",
        "# we specified in the beginning of the notebook!\n",
        "bq_client = bigquery.Client(my_ProjectID)\n",
        "\n",
        "selection_query = \"\"\"\n",
        "SELECT\n",
        "  COUNT(DISTINCT(PatientID)) as patient_cnt\n",
        "FROM\n",
        "  bigquery-public-data.idc_current.dicom_all\n",
        "WHERE\n",
        "  Modality = \"MR\"\n",
        "\"\"\"\n",
        "\n",
        "selection_result = bq_client.query(selection_query)\n",
        "selection_df = selection_result.result().to_dataframe()\n",
        "\n",
        "selection_df"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "cDTMoRZi4WKG"
      },
      "source": [
        "### Exercise 3: Learn more about the size of the collections in IDC\n",
        "\n",
        "In this exercise your task is to expand on the query we wrote earlier to calculate additional characteristics of the collections by counting the number of distinct values of `PatientID`, `StudyInstanceUID`, `SeriesInstanceUID` and `SOPInstanceUID` in each of the collections.\n",
        "\n",
        "As always, you can use the the Editor tab in the [BigQuery SQL workspace](https://console.cloud.google.com/bigquery) to experiment and debug the query if you prefer!"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "HSrocANc4Y2V"
      },
      "outputs": [],
      "source": [
        "from google.cloud import bigquery\n",
        "\n",
        "# BigQuery client is initialized with the ID of the project\n",
        "# we specified in the beginning of the notebook!\n",
        "bq_client = bigquery.Client(my_ProjectID)\n",
        "\n",
        "# Execution of this cell will fail unless you wrote the query below!\n",
        "selection_query = \"\"\"\n",
        "SELECT\n",
        "  collection_id,\n",
        "  STRING_AGG(DISTINCT(Modality)) as modalities,\n",
        "  STRING_AGG(DISTINCT(collection_tumorLocation)) as tumor_locations,\n",
        "  STRING_AGG(DISTINCT(collection_species)) as species\n",
        "  # count number of distinct patients\n",
        "  # count number of distinct studies\n",
        "  # count number of distinct series\n",
        "  # count number of distinct instances\n",
        "FROM\n",
        "  bigquery-public-data.idc_current.dicom_all\n",
        "GROUP BY\n",
        "  collection_id\n",
        "\"\"\"\n",
        "\n",
        "selection_result = bq_client.query(selection_query)\n",
        "selection_df = selection_result.result().to_dataframe()\n",
        "\n",
        "selection_df"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "gneXoacN4c1o"
      },
      "source": [
        "# Selecting series of specific type\n",
        "\n",
        "In many cases, image analysis is done at the granularity of the individual DICOM series. In some cases DICOM series corresponds to a single instance (e.g., for X-ray modalities), but in most cases imaging modalities are cross-sectional, containing multiple slices, with each slice stored in a separate instance (file), which can be reconstructed into a 3D volume.\n",
        "\n",
        "From the examples and queries above, you should have developed some understanding about the modalities and few other collection-level characteristics for the data included in IDC. As an example, we know that IDC data contains MR images of Liver.\n",
        "\n",
        "In the following query we select the UID of a sample MR series from the images covering Liver cancer."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "jym-kbFb4cgL"
      },
      "outputs": [],
      "source": [
        "from google.cloud import bigquery\n",
        "\n",
        "# BigQuery client is initialized with the ID of the project\n",
        "# we specified in the beginning of the notebook!\n",
        "bq_client = bigquery.Client(my_ProjectID)\n",
        "\n",
        "selection_query = \"\"\"\n",
        "SELECT\n",
        "  SeriesInstanceUID,\n",
        "  collection_id\n",
        "FROM\n",
        "  bigquery-public-data.idc_current.dicom_all\n",
        "WHERE\n",
        "  Modality = \"MR\" AND collection_tumorLocation = \"Liver\"\n",
        "\n",
        "# note the use of this new operator that makes the query\n",
        "# return just the first one of the matching rows\n",
        "LIMIT\n",
        "  1\n",
        "\"\"\"\n",
        "\n",
        "selection_result = bq_client.query(selection_query)\n",
        "selection_df = selection_result.result().to_dataframe()\n",
        "\n",
        "selection_df"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "fhS4fwVv4h-W"
      },
      "source": [
        "The result of this query is the _unique identifier_ for a DICOM series that meets the selection criteria."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "sKsJN8IY4lbV"
      },
      "source": [
        "## Selecting studies that contain series of the specific type\n",
        "\n",
        "Another common use case for searching the data is identifying images that are accompanied by annotations of findings in those images. A significant portion of images in IDC has segementations of the structures of interest, which were created manually or using automated segmentation tools. Without going into details, such segmentations are stored in DICOM Segmentation (SEG modality) or Radiotherapy Structure Set (RTSS modality) objects.\n",
        "\n",
        "Segmentations for a given image series within a study will be stored in a separate series. We can select studies that contain both images and segmentations using the query below. To help better summarize the result, we will list all of the modalities encountered in the given study."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "lxcOYLFv4oBI"
      },
      "outputs": [],
      "source": [
        "from google.cloud import bigquery\n",
        "\n",
        "# BigQuery client is initialized with the ID of the project\n",
        "# we specified in the beginning of the notebook!\n",
        "bq_client = bigquery.Client(my_ProjectID)\n",
        "\n",
        "selection_query = \"\"\"\n",
        "WITH\n",
        "  temp_result AS (\n",
        "  SELECT\n",
        "    StudyInstanceUID,\n",
        "    STRING_AGG(DISTINCT(collection_id)) AS collection_id,\n",
        "    STRING_AGG(DISTINCT(collection_tumorLocation)) AS collection_tumorLocation,\n",
        "    ARRAY_AGG(DISTINCT(Modality)) AS modalities\n",
        "  FROM\n",
        "    bigquery-public-data.idc_current.dicom_all\n",
        "  GROUP BY\n",
        "    StudyInstanceUID)\n",
        "SELECT\n",
        "  StudyInstanceUID,\n",
        "  ARRAY_TO_STRING(modalities,\",\") AS modalities,\n",
        "  collection_id\n",
        "FROM\n",
        "  temp_result\n",
        "WHERE\n",
        "  \"CT\" IN UNNEST(modalities)\n",
        "  AND \"SEG\" IN UNNEST(modalities)\n",
        "  AND collection_tumorLocation = \"Lung\"\n",
        "LIMIT\n",
        "  1\n",
        "\"\"\"\n",
        "\n",
        "selection_result = bq_client.query(selection_query)\n",
        "selection_df = selection_result.result().to_dataframe()\n",
        "\n",
        "selection_df"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "9tHlTpAm4qty"
      },
      "source": [
        "This query introduces a couple of more advanced concepts:\n",
        "* we use `WITH` operator to define an intermediate query that writes the result into `temp_result` table, which is then queried\n",
        "* we capture all of the distinct values of `Modality` into an _array_ `modalities`, since we want to check for presence of both MR and SEG modalities in the study.\n",
        "\n",
        "We will work with those advanced features more in the subsequent tutorials. For now, you can experiment with this query by adjusting the parameters to select studies that contain combinations of certain modalities (for example, are there imaging studies of liver cancer that contain either MR or CT images and segmentations?). This is probably the most challenging exercise from this tutorial - [BigQuery SQL workspace](https://console.cloud.google.com/bigquery) should be handy to work on it!"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "yv5wMVl75CaX"
      },
      "source": [
        "## Summary\n",
        "\n",
        "In this part of the \"Getting started\" series we learned the very basics of using Google BigQuery for searching IDC meatadata.\n",
        "\n",
        "As you experienced, similar to `idc-index`, you can perform those search operations from Pyton using the same SQL syntax.\n",
        "\n",
        "We hope you enjoyed this tutorial! If something didn't work as expected, if you have any feedback or suggestions of what should be added to this tutorial, please contact IDC support by sending email to support@canceridc.dev or posting your question on [IDC User forum](https://discourse.canceridc.dev)."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "NnuWgtK_vnWC"
      },
      "source": [
        "## Acknowledgments\n",
        "\n",
        "Imaging Data Commons has been funded in whole or in part with Federal funds from the National Cancer Institute, National Institutes of Health, under Task Order No. HHSN26110071 under Contract No. HHSN261201500003l.\n",
        "\n",
        "If you use IDC in your research, please cite the following publication:\n",
        "\n",
        "> Fedorov, A., Longabaugh, W. J. R., Pot, D., Clunie, D. A., Pieper, S. D., Gibbs, D. L., Bridge, C., Herrmann, M. D., Homeyer, A., Lewis, R., Aerts, H. J. W., Krishnaswamy, D., Thiriveedhi, V. K., Ciausu, C., Schacherer, D. P., Bontempi, D., Pihl, T., Wagner, U., Farahani, K., Kim, E. & Kikinis, R. _National Cancer Institute Imaging Data Commons: Toward Transparency, Reproducibility, and Scalability in Imaging Artificial Intelligence_. RadioGraphics (2023). [https://doi.org/10.1148/rg.230180](https://doi.org/10.1148/rg.230180)"
      ]
    }
  ],
  "metadata": {
    "colab": {
      "provenance": [],
      "toc_visible": true
    },
    "gpuClass": "standard",
    "kernelspec": {
      "display_name": "Python 3",
      "name": "python3"
    },
    "language_info": {
      "name": "python"
    },
    "orig_nbformat": 4
  },
  "nbformat": 4,
  "nbformat_minor": 0
}