<commit_before>***********************
Query of the Month Club
***********************

Welcome to the 'Query of the Month Club' where we'll be creating a collection
of new and interesting queries to demonstrate the powerful combination of
BigData from the TCGA and BigQuery from Google.

Please let us know if you'd like to be featured on the "query-club"!
email: dgibbs (at) systemsbiology (dot) org


------------------

August, 2017
###########

This month we have been working on a graphical front-end for BigQuery using
`R Shiny <https://shiny.rstudio.com/>`_ .

Using the R programming language, Shiny is an easy way to produce interactive
visualizations that can be hosted on the web, making public sharing possible.

Shiny sites need to have a shiny server, and one easy way to have your sites
hosted is by using the shinyapps.io service, which is provided by the same
company that produces the RStudio (has a builtin Shiny server for dev work).

In the past, we've shown how queries can be programmatically built up; here
we're going to provide some user interfaces to variables that are inserted into
the BigQuery (like gene names).

The query is going to look at patient survival, and how survival rates change
with gene mutations. Therefore we'll be using two tables and a small set of
variables:

+ isb-cgc:TCGA_bioclin_v0.Clinical for survival data
    - **days_to_death**: If the patient has died, and this information is available, then
      this field will indicate the number of days, relative to "time zero" (typically
      the day of diagnosis), until death (in the future, ie a positive value).
    - **vital_status**: This field is filled in for all but 4 cases and is correct as of
      the last available followup for that individual.  7534 cases were known to still
      be "Alive", while 3622 were "Dead", and 4 were of unknown vital status.
+ isb-cgc:TCGA_hg38_data_v0.Somatic_Mutation for mutation status
    - **Variant_Classification**: eg Missense_Mutation, Silent, 3'UTR, Intron, etc (18 different values occur in this table)
    - **Variant_Type**: one of 3 possible values: SNP, DEL, INS
    - **IMPACT**: one of 4 values: LOW, MODERATE, HIGH, or MODIFIER

What we want the query to do, is in taking a cohort, collect patients into two
groups, those that have a greater than LOW IMPACT SNP in a particular gene, and
those that do not have any SNP in that gene. Then we can compare the
time_to_death between the two groups to assess whether the SNP has some
potential effect. Many patients are still alive, and for those, the days_to_death
is modified to be the end of the study (or the max number of days possible.)

Let's take a look at an example query, then we'll see how to build it up in the
code.

.. code-block:: sql

  -- using Standard SQL --
  --
  -- First we build our table of survival times
  --
  WITH clin_table AS (
  SELECT
    case_barcode,
    days_to_death,
    vital_status
  FROM
    `isb-cgc.TCGA_bioclin_v0.Clinical`
  WHERE
    project_short_name = 'TCGA-GBM' ),
  --
  -- Then we can build our table of mutation status.
  -- We do that by using an If statement with a sub-query.
  --
  mut_table AS (
  SELECT
    case_barcode,
    IF ( case_barcode IN (
      SELECT
        case_barcode
      FROM
        `isb-cgc.TCGA_hg38_data_v0.Somatic_Mutation`
      WHERE
        SYMBOL = 'IDH1'
        AND Variant_Classification <> 'Silent'
        AND Variant_Type = 'SNP'
        AND IMPACT <> 'LOW'), 'Mutant', 'WT') AS mutation_status
  FROM
    `isb-cgc.TCGA_hg38_data_v0.Somatic_Mutation` )
  --
  -- Finally, we can join those tables.
  --
  SELECT
    mut_table.case_barcode,
    days_to_death,
    vital_status,
    mutation_status
  FROM
    clin_table
  JOIN
    mut_table
  ON
    clin_table.case_barcode = mut_table.case_barcode
  GROUP BY
    mut_table.case_barcode,
    days_to_death,
    vital_status,
    mutation_status


------------------

July, 2017
###########

Way back in December we started talking about the new
`NCI-GDC Data Portal <https://gdc-portal.nci.nih.gov/>`_
which includes both hg19 and hg38 alignments. At that time, those were part of
isb-cgc:hg19_data_previews and isb-cgc:hg38_data_previews. Now, at this point they've matured into three data sets:

- isb-cgc:TCGA_bioclin_v0
- isb-cgc:TCGA_hg19_data_v0
- isb-cgc:TCGA_hg38_data_v0

And, as you'll discover, there's been some changes to the "standard" column
names that we used previously. This was done to better align with the GDC, and
make things more universal across data sources.

For one, barcode column names (and most column names) have become all lower case
and underscore_separated. So 'AliquotBarcode' has become 'aliquot_barcode'. Same with
SampleBarcode (sample_barcode). However, ParticipantBarcode has become case_barcode.
Also 'Study' is now refered to as 'project_short_name'. So if you're having
trouble getting an 'old' query to work, make sure the column names haven't changed,
and check whether it's in Legacy SQL or Standard SQL.
<commit_msg>dev work 1600<commit_after>***********************
Query of the Month Club
***********************

Welcome to the 'Query of the Month Club' where we'll be creating a collection
of new and interesting queries to demonstrate the powerful combination of
BigData from the TCGA and BigQuery from Google.

Please let us know if you'd like to be featured on the "query-club"!
email: dgibbs (at) systemsbiology (dot) org


------------------

August, 2017
###########

This month we have been working on a graphical front-end for BigQuery using
`R Shiny <https://shiny.rstudio.com/>`_ .

You can find it `here <https://isb-cgc.shinyapps.io/ShinyKaplanMeier/>`_.

Using the R programming language, Shiny is an easy way to produce interactive
visualizations that can be hosted on the web, making public sharing possible.

Shiny sites need to have a shiny server, and one easy way to have your sites
hosted is by using the shinyapps.io service, which is provided by the same
company that produces the RStudio (has a builtin Shiny server for dev work).

In the past, we've shown how queries can be programmatically built up; here
we're going to provide some user interfaces to variables that are inserted into
the BigQuery (like gene names).

The query is going to look at patient survival, and how survival rates change
with gene mutations. Therefore we'll be using two tables and a small set of
variables:

+ isb-cgc:TCGA_bioclin_v0.Clinical for survival data
    - **days_to_death**: If the patient has died, and this information is available, then
      this field will indicate the number of days, relative to "time zero" (typically
      the day of diagnosis), until death (in the future, ie a positive value).
    - **vital_status**: This field is filled in for all but 4 cases and is correct as of
      the last available followup for that individual.  7534 cases were known to still
      be "Alive", while 3622 were "Dead", and 4 were of unknown vital status.
+ isb-cgc:TCGA_hg38_data_v0.Somatic_Mutation for mutation status
    - **Variant_Classification**: eg Missense_Mutation, Silent, 3'UTR, Intron, etc (18 different values occur in this table)
    - **Variant_Type**: one of 3 possible values: SNP, DEL, INS
    - **IMPACT**: one of 4 values: LOW, MODERATE, HIGH, or MODIFIER

What we want the query to do, is in taking a cohort, collect patients into two
groups, those that have a greater than LOW IMPACT SNP in a particular gene, and
those that do not have any SNP in that gene. Then we can compare the
time_to_death between the two groups to assess whether the SNP has some
potential effect. Many patients are still alive, and for those, the days_to_death
is modified to be the end of the study (or the max number of days possible.)

Let's take a look at an example query, then we'll see how to build it up in the
code.

.. code-block:: sql

  -- using Standard SQL --
  --
  -- First we build our table of survival times
  --
  WITH clin_table AS (
  SELECT
    case_barcode,
    days_to_death,
    vital_status
  FROM
    `isb-cgc.TCGA_bioclin_v0.Clinical`
  WHERE
    project_short_name = 'TCGA-GBM' ),
  --
  -- Then we can build our table of mutation status.
  -- We do that by using an If statement with a sub-query.
  --
  mut_table AS (
  SELECT
    case_barcode,
    IF ( case_barcode IN (
      SELECT
        case_barcode
      FROM
        `isb-cgc.TCGA_hg38_data_v0.Somatic_Mutation`
      WHERE
        SYMBOL = 'IDH1'
        AND Variant_Classification <> 'Silent'
        AND Variant_Type = 'SNP'
        AND IMPACT <> 'LOW'), 'Mutant', 'WT') AS mutation_status
  FROM
    `isb-cgc.TCGA_hg38_data_v0.Somatic_Mutation` )
  --
  -- Finally, we can join those tables.
  --
  SELECT
    mut_table.case_barcode,
    days_to_death,
    vital_status,
    mutation_status
  FROM
    clin_table
  JOIN
    mut_table
  ON
    clin_table.case_barcode = mut_table.case_barcode
  GROUP BY
    mut_table.case_barcode,
    days_to_death,
    vital_status,
    mutation_status


**The Shiny App**


Now we'll move on to the description of the app. When you create a new Shiny
project in RStudio, two main files are created: 'ui.R' and 'server.R'.  Additionally,
I created one more called 'global.R'.  'ui.R' contains the code needed to build
the html interface. 'server.R' contains the code that responds to the interface, and
'global.R' contains the functions that build the query, call BigQuery, and
plot the results.

Starting with the interface found in 'ui.R', the
`googleAuthR <https://github.com/MarkEdmondson1234/googleAuthR>`_ package was
used to perform authorization. To do this, first a button is added to the
interface using googleAuthUI("loginButton").


..code-block:: R
  
  ui <- fluidPage(
  # and some header information
  sidebarPanel(
    googleAuthUI("loginButton"),


The project ID was gathered using the textInput widget, this is done because
even after logging in, we still need to tell BigQuery what project we'd like
to bill. Cohorts are selected using the selectInput widget, which is like a
drop down menu of TCGA studies. And lastly, we have a textInput widget to
specify the gene symbol. At the bottom of the interface is an actionButton called
submit that kicks off the work.


..code-block:: R

  #
  textInput("projectid", "Project ID", value = "isb-cgc-xy-abcd", placeholder = "isb-cgc-xy-abcd"),
  selectInput("cohortid", label = "Cohort",
                  choices = list(
                    "TCGA-ACC"="TCGA-ACC",
                    "TCGA-BLCA"="TCGA-BLCA",
                    "TCGA-BRCA"="TCGA-BRCA",
                    "TCGA-CESC"="TCGA-CESC",
                    "TCGA-CHOL"="TCGA-CHOL",
                    ## etc ##
              ),selected = "TCGA-GBM") ,
  textInput("varname",  "Gene Symbol", value = "IDH1", placeholder = "IDH1"),
  actionButton(inputId="submit",label = "Submit")


In the 'server.R' file, there's one main function called 'server'. Inside that
function, we get our accessToken by calling the googleAuth module, linked to
the 'loginButton', and we have a function linked (using eventReactive) to the submit button called
outputPlot.




------------------

July, 2017
###########

Way back in December we started talking about the new
`NCI-GDC Data Portal <https://gdc-portal.nci.nih.gov/>`_
which includes both hg19 and hg38 alignments. At that time, those were part of
isb-cgc:hg19_data_previews and isb-cgc:hg38_data_previews. Now, at this point they've matured into three data sets:

- isb-cgc:TCGA_bioclin_v0
- isb-cgc:TCGA_hg19_data_v0
- isb-cgc:TCGA_hg38_data_v0

And, as you'll discover, there's been some changes to the "standard" column
names that we used previously. This was done to better align with the GDC, and
make things more universal across data sources.

For one, barcode column names (and most column names) have become all lower case
and underscore_separated. So 'AliquotBarcode' has become 'aliquot_barcode'. Same with
SampleBarcode (sample_barcode). However, ParticipantBarcode has become case_barcode.
Also 'Study' is now refered to as 'project_short_name'. So if you're having
trouble getting an 'old' query to work, make sure the column names haven't changed,
and check whether it's in Legacy SQL or Standard SQL.

{'fraction_non_alphanumeric': 0.08458646616541353, 'fraction_numerical': 0.008989865969271003, 'mean_word_length': 3.4611738971928547, 'pattern_counts': {'":': 0, '<': 14, '<?xml version=': 0, '>': 13, 'https://': 6, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '7584615', 'n_tokens_mistral': 3843, 'n_tokens_neox': 3608, 'n_words': 1609}