Skip to content

somAgg code book phenotype queries

Details of all the samples included in somAgg are listed in the LabKey table cancer_analysis. You can use this table to link the samples to their phenotypes in other LabKey tables, using either the LabKey desktop application and the APIs.

The sample IDs in the somAgg VCFs are the 'tumour_sample_platekey' (e.g. LP1234567-DNA_A09) - not the participant IDs. In order to link between plate-key and participant ID - use the cancer_analysis table. There is one sample per row in this table. 

LabKey API

Setting up access

Before you get started with using the LabKey APIs, you need to ensure you have it set up correctly, following this document. We recommend running your LabKey API scripts on the HPC, as detailed here, as they can be quite large.

Before you get started with using the LabKey APIs, you need to ensure you have it set up correctly, following this document. We recommend running your LabKey API scripts on the HPC, as detailed here, as they can be quite large.

To query our tables using CloudOS, you will need to set up an interactive session. You will need to use conda to install the required R packages:

conda create -n query-tables r-glue r-tidyverse r-data.table r-dbi r-rpostgres r-irkernel -y

You can now launch an R notebook under the query-tables kernel. More details

To query our tables using CloudOS, you will need to set up an interactive session. You will need to use conda to install the required python packages:

conda create -n python-tables psycopg2 ipykernel sqlalchemy pandas numpy -y
conda activate python-tables

You can now launch a Jupyter notebook under the python-tables kernel.

Import modules/libraries you need

LabKey has an underlying SQL database. To access it you will need to have the labkey module/library loaded. You will also be working with tables and dataframes. Here you can see the modules/libraries you should load in your scripts:

1
2
3
library(tidyverse)
library(Rlabkey)
library(readr)
If you are using R version 3 or above, you should also add the following line: 
1
labkey.setWafEncoding(FALSE)

1
2
3
4
import numpy as np
import functools
import labkey
import pandas as pd


1
2
3
4
5
library(tidyverse)
library(data.table)
library(glue)
library(RPostgres)
library(readr)

1
2
3
4
import numpy as np
import functools
import psycopg2
from sqlalchemy import create_engine, event, text

Helper function to access the LabKey API

We have here a helper function called labkey_to_df that you can use to access the LabKey API in the RE and return data as a dataframe. You need to give it:

  • sql_query: an SQL query to access the LabKey tables
  • database: the version of the database you want to access for example /main-programme/main-programme_v19_2024-10-31
  • maxrows: maximum number of rows you want to return. This parameter defaults to 100000000, but if the output contains exactly the default value, then we suggest increasing this value.

Feel free to include this function in all your scripts and invoke it every time you want to query the data.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
labkey_to_df <- function(sql_query, database, maxrows){

    labkey.setDefaults(baseUrl = "https://labkey-embassy.gel.zone/labkey/")

    labkey.executeSql(folderPath = database,
                      schemaName = "lists",
                      colNameOpt = "rname",
                      sql = sql_query,
                      maxRows = maxrows) %>%
        mutate(across(everything(), as.character))
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
def labkey_to_df(sql_query, database, maxrows):

    ver = labkey.__version__

    if ver < '2.4.0':
    server_context = labkey.utils.create_server_context(
        domain = "labkey-embassy.gel.zone",
        container_path = database,
        context_path = "labkey",
        use_ssl = True
    )

    results =  labkey.query.execute_sql(
        server_context,
        schema_name = "lists",
        sql = sql_query,
        max_rows = maxrows
    )

    if ver >= '2.4.0':
    from labkey.api_wrapper import APIWrapper

    labkey_server = "labkey-embassy.gel.zone"
    project_name = database
    contextPath = "labkey"
    schema = 'lists'
    api = APIWrapper(
        labkey_server,
        project_name,
        contextPath,
        use_ssl=True
        )
    results = api.query.execute_sql(sql=sql_query, schema_name=schema, max_rows = maxrows)

    return(pd.DataFrame(results['rows']))


 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
labkey_to_df <- function(sql_query, database, maxrows){
  DBNAME = "gel_clinical_cb_sql_pro"
  HOST = "clinical-cb-sql-pro.cfe5cdx3wlef.eu-west-2.rds.amazonaws.com"
  PORT = 5432
  PASSWORD = 'anXReTz36Q5r'
  USER = 'jupyter_notebook'

  connection <- DBI::dbConnect(
      RPostgres::Postgres(),
      dbname = DBNAME,
      host = HOST,
      port = PORT,
      password = PASSWORD,
      user = USER,
      options = paste("-c search_path=", database, sep="")
      )

  dbGetQuery(connection, sql_query, n = maxrows)
  }

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
def query_to_df(sql_query, version):
    database = "gel_clinical_cb_sql_pro"
    host = "clinical-cb-sql-pro.cfe5cdx3wlef.eu-west-2.rds.amazonaws.com"
    port = 5432
    password = 'anXReTz36Q5r'
    user = 'jupyter_notebook'
    engine = create_engine(f'''postgresql://{user}:{password}@{host}:{port}/{database}''')

    @event.listens_for(engine, "connect", insert=True)
    def set_search_path(dbapi_connection, connection_record):
        existing_autocommit = dbapi_connection.autocommit
        dbapi_connection.autocommit = True
        cursor = dbapi_connection.cursor()
        cursor.execute(f"SET SESSION search_path={version}")
        cursor.close()
        dbapi_connection.autocommit = existing_autocommit

    with engine.connect() as connection:
        result = connection.execute(text(sql_query))
        return(pd.DataFrame(result))

To run my queries, I'll need to set up my database version:

version <- "/main-programme/main-programme_v19_2024-10-31"

version = "/main-programme/main-programme_v19_2024-10-31"


version <- "source_data_100kv16_covidv4"

version = "source_data_100kv16_covidv4"

Importing the cancer_analysis table

This example show how to fetch the entire cancer_analysis table and save as an object in your session. The table can then be post-filtered.

1
2
whole_agg_sql <- "SELECT * from cancer_analysis"
whole_agg_query <- labkey_to_df(whole_agg_sql, version, 1000000)

1
2
whole_agg_sql = "SELECT * from cancer_analysis"
whole_agg_query = labkey_to_df(whole_agg_sql, version, 1000000)

1
2
whole_agg_sql <- "SELECT * from cancer_analysis"
whole_agg_query <- labkey_to_df(whole_agg_sql, version, 1000000)

1
2
whole_agg_sql = "SELECT * from cancer_analysis"
whole_agg_query = query_to_df(whole_agg_sql, version)

Filtering tables and selecting columns

Tables can be filtered for certain attributes and specified columns selected in labkey using SQL. In this example, we can filter the aggregate_gvcf_sample_stats table for participants in somAgg with colorectal cancer with WGS from fresh-frozen samples. We can then select only the participant ID and platekey columns.

1
2
3
4
5
6
7
disease <- "COLORECTAL"
prep <- "FF"
source_sql <- paste("SELECT participant_id, tumour_sample_platekey
     FROM aggregate_gvcf_sample_stats
     WHERE disease_type = '", disease, "'
     AND preparation_method = '", prep, "'", sep = "")
source_query <- labkey_to_df(source_sql, version, 100000)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
disease = "COLORECTAL"
prep = "FF"

source_sql = (f'''
SELECT participant_id, tumour_sample_platekey
     FROM aggregate_gvcf_sample_stats
     WHERE disease_type = '{disease}'
     AND preparation_method = '{prep}'
    ''')
source_query = labkey_to_df(source_sql, version, 10000)

1
2
3
4
5
6
7
disease <- "COLORECTAL"
prep <- "FF"
source_sql <- paste("SELECT participant_id, tumour_sample_platekey
     FROM aggregate_gvcf_sample_stats
     WHERE disease_type = '", disease, "'
     AND preparation_method = '", prep, "'", sep = "")
source_query <- labkey_to_df(source_sql, version, 100000)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
disease = "COLORECTAL"
prep = "FF"

source_sql = (f'''
SELECT participant_id, tumour_sample_platekey
     FROM aggregate_gvcf_sample_stats
     WHERE disease_type = '{disease}'
     AND preparation_method = '{prep}'
    ''')
source_query = query_to_df(source_sql, version)

Joining multiple tables together to create a cohort

The utility of the LabKey APIs comes when joining multiple tables together. In this example, we perform the query above to find participants in somAgg with colorectal cancer with WGS from fresh-frozen samples. We then inner-join these results together with the cancer_staging_consolidated table to identify the staging of cancer at WGS. We pull pack a table of participant ID and plate-key of participants who satisfy the criteria.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
disease <- "COLORECTAL"
prep <- "FF"
stage_not <- c('?', '6', 'A', 'B', 'U', 'X', '<NA>')

source_phenotype_sql <- paste("SELECT ca.participant_id, ca.tumour_sample_platekey, av.stage_best
     FROM cancer_analysis AS ca
     INNER JOIN av_tumour AS av
     ON ca.participant_id = av.participant_id
     WHERE disease_type = '", disease, "'
     AND preparation_method = '", prep, "'
 AND stage_best NOT IN ('", paste(stage_not, collapse = "', '")"
source_phenotype_query <- labkey_to_df(source_phenotype_sql, version, 100000)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
disease = "COLORECTAL"
prep = "FF"
stage_not <- ['?', '6', 'A', 'B', 'U', 'X', '<NA>']

source_phenotype_sql = (f '''
  SELECT ca.participant_id, ca.tumour_sample_platekey, av.stage_best
     FROM cancer_analysis AS ca
     INNER JOIN av_tumour AS av
     ON ca.participant_id = av.participant_id
     WHERE disease_type = '{disease}'
     AND preparation_method = '{prep}'
     AND stage_best NOT IN {*stage_not}
     ''')
source_phenotype_query = labkey_to_df(source_phenotype_sql, version, 10000)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
disease <- "COLORECTAL"
prep <- "FF"
stage_not <- c('?', '6', 'A', 'B', 'U', 'X', '<NA>')

source_phenotype_sql <- paste("SELECT ca.participant_id, ca.tumour_sample_platekey, av.stage_best
     FROM cancer_analysis AS ca
     INNER JOIN av_tumour AS av
     ON ca.participant_id = av.participant_id
     WHERE disease_type = '", disease, "'
     AND preparation_method = '", prep, "'
 AND stage_best NOT IN ('", paste(stage_not, collapse = "', '")"
source_phenotype_query <- labkey_to_df(source_phenotype_sql, version, 100000)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
disease = "COLORECTAL"
prep = "FF"
stage_not <- ['?', '6', 'A', 'B', 'U', 'X', '<NA>']

source_phenotype_sql = (f '''
  SELECT ca.participant_id, ca.tumour_sample_platekey, av.stage_best
     FROM cancer_analysis AS ca
     INNER JOIN av_tumour AS av
     ON ca.participant_id = av.participant_id
     WHERE disease_type = '{disease}'
     AND preparation_method = '{prep}'
     AND stage_best NOT IN {*stage_not}
     ''')
source_phenotype_query = querßy_to_df(source_phenotype_sql, version)

cancer_staging_consolidated contains other columns with staging information that can be used for sample where stage_best is not available. Also, interval_min can be used to set a maximum time allowed between sample collection and string information collected, which may not coincide.