Q (haiku): In the TCGA PanCancer Atlas ovarian cancer study, what is the correlation between EGFR mRNA expression and EGFR protein (RPPA) levels?

▶ read_guide
{
 "uri": "cbioportal://gene-expression-guide"
}

▶ resolve_and_route
{
 "studyKeywords": [
  "TCGA",
  "ovarian",
  "PanCancer"
 ]
}

◀ result
# Gene Expression Analysis Guide

This guide covers continuous-value genomic data: gene **expression**, **copy number** values, **methylation**, and related profile types. Mutation/CNA/SV *frequency* analysis lives in `cbioportal://mutation-frequency-guide`.

## Where this data lives

Continuous per-sample-per-gene values are stored in `genetic_alteration_derived`:

| Column | Description |
|---|---|
| `sample_unique_id` | `<study>_<sample_stable_id>` |
| `cancer_study_identifier` | study scope |
| `hugo_gene_symbol` | gene |
| `profile_type` | which assay/normalization (see below) |
| `alteration_value` | the actual value — stored as Nullable(String); cast with `toFloat64OrNull` |

`alteration_value` is a string because the same column hosts many different value scales. The `''` and `'NA'` sentinels mean "missing"; always filter them out and use `toFloat64OrNull(alteration_value) IS NOT NULL` for downstream math.

## Discovering profile types for a study

Different studies expose different profile types depending on what assays were run and how the data was normalized. Always check what a specific study supports before picking one:

```sql
SELECT DISTINCT profile_type
FROM genetic_alteration_derived
WHERE cancer_study_identifier = 'brca_metabric'
ORDER BY profile_type;
```

Common values across the public portal:

| Family | Profile types |
|---|---|
| mRNA expression | `rna_seq_v2_mrna`, `rna_seq_v2_mrna_median_Zscores`, `rna_seq_v2_mrna_median_all_sample_Zscores` (TCGA PanCancer Atlas), `mrna`, `mrna_median_Zscores`, `mrna_seq_v2_rsem`, `mrna_seq_v2_rsem_Zscores`, `mrna_seq_cpm`, `mrna_seq_fpkm`, `mrna_U133`, `mrna_outliers` |
| Copy number (continuous) | `cna`, `linear_CNA`, `log2CNA`, `cna_consensus`, `cna_rae`, `gistic` |
| Methylation | `methylation_hm27`, `methylation_hm450`, `methylation_epic`, `methylation_promoters_rrbs` |
| miRNA | `mirna`, `mirna_median_Zscores` |
| Protein | `protein_quantification`, `protein_level`, `RPPA` |

**Z-score vs raw choice.** When the user asks "is X correlated with Y", either works for Spearman (rank-based) — Pearson would care. Default to the non-Z-score variant if both exist, and call out which one in the response.

## Canonical recipe — Spearman correlation between two genes

```sql
SELECT *
FROM gene_pair_coexpression(
    study        = 'brca_metabric',
    gene_a       = 'TP53',
    gene_b       = 'MYC',
    profile_type = 'mrna'
);
```

Returns one row: `(gene_a, gene_b, profile_type, spearman_correlation, num_samples)`.

- `spearman_correlation` in [−1, 1]; `NULL` when fewer than 3 valid paired samples.
- Mirrors cbioportal-backend's `ClickhouseCoExpressionMapper.getCoExpressions`, simplified to a pair lookup (the backend computes one ref gene vs ALL other genes for the coexpression page; here the agent asks about a specific pair).

### Verified examples

| Study | gene_a | gene_b | profile_type | spearman | n |
|---|---|---|---|---|---|
| `brca_metabric` | TP53 | MYC | `mrna` | 0.118 | 1980 |
|  … (4931 more chars)

◀ result
{"success":true,"message":"Found 1 study. Use the metadata to call the appropriate navigation tool(s).","data":{"totalCount":1,"studiesWithMetadata":[{"studyId":"ov_tcga_pan_can_atlas_2018","name":"Ovarian Serous Cystadenocarcinoma (TCGA, PanCancer Atlas)","sampleCount":585,"studyViewUrl":"https://www.cbioportal.org/study?id=ov_tcga_pan_can_atlas_2018","metadata":{"clinicalAttributeIds":["AGE","AJCC_PATHOLOGIC_TUMOR_STAGE","AJCC_STAGING_EDITION","ANEUPLOIDY_SCORE","BUFFA_HYPOXIA_SCORE","CANCER_TYPE","CANCER_TYPE_ACRONYM","CANCER_TYPE_DETAILED","DAYS_LAST_FOLLOWUP","DAYS_TO_BIRTH","DAYS_TO_INITIAL_PATHOLOGIC_DIAGNOSIS","DFS_MONTHS","DFS_STATUS","DSS_MONTHS","DSS_STATUS","ETHNICITY","FORM_COMPLETION_DATE","FRACTION_GENOME_ALTERED","GENETIC_ANCESTRY_LABEL","GRADE","HISTORY_NEOADJUVANT_TRTYN","ICD_10","ICD_O_3_HISTOLOGY","ICD_O_3_SITE","INFORMED_CONSENT_VERIFIED","IN_PANCANPATHWAYS_FREEZE","MSI_SCORE_MANTIS","MSI_SENSOR_SCORE","MUTATION_COUNT","NEW_TUMOR_EVENT_AFTER_INITIAL_TREATMENT","ONCOTREE_CODE","OS_MONTHS","OS_STATUS","OTHER_PATIENT_ID","PATH_M_STAGE","PATH_N_STAGE","PATH_T_STAGE","PERSON_NEOPLASM_CANCER_STATUS","PFS_MONTHS","PFS_STATUS","PRIMARY_LYMPH_NODE_PRESENTATION_ASSESSMENT","PRIOR_DX","RACE","RADIATION_THERAPY","RAGNUM_HYPOXIA_SCORE","SAMPLE_COUNT","SAMPLE_TYPE","SEX","SOMATIC_STATUS","SUBTYPE","TBL_SCORE","TISSUE_PROSPECTIVE_COLLECTION_INDICATOR","TISSUE_RETROSPECTIVE_COLLECTION_INDICATOR","TISSUE_SOURCE_SITE","TISSUE_SOURCE_SITE_CODE","TMB_NONSYNONYMOUS","TUMOR_TISSUE_SITE","TUMOR_TYPE","WEIGHT","WINTER_HYPOXIA_SCORE"],"molecularProfileIds":["ov_tcga_pan_can_atlas_2018_gistic","ov_tcga_pan_can_atlas_2018_log2CNA","ov_tcga_pan_can_atlas_2018_mutations","ov_tcga_pan_can_atlas_2018_protein_quantification","ov_tcga_pan_can_atlas_2018_protein_quantification_zscores","ov_tcga_pan_can_atlas_2018_rna_seq_v2_mrna","ov_tcga_pan_can_atlas_2018_rna_seq_v2_mrna_median_Zscores","ov_tcga_pan_can_atlas_2018_rna_seq_v2_mrna_median_all_sample_Zscores","ov_tcga_pan_can_atlas_2018_rppa","ov_tcga_pan_can_atlas_2018_rppa_Zscores","ov_tcga_pan_can_atlas_2018_structural_variants"],"genericAssayProfiles":["ov_tcga_pan_can_atlas_2018_armlevel_cna","ov_tcga_pan_can_atlas_2018_genetic_ancestry","ov_tcga_pan_can_atlas_2018_methylation_hm27_hm450_merge","ov_tcga_pan_can_atlas_2018_methylation_hm450","ov_tcga_pan_can_atlas_2018_phosphoprotein_quantification"],"heatmapProfileIds":["ov_tcga_pan_can_atlas_2018_protein_quantification_zscores","ov_tcga_pan_can_atlas_2018_rna_seq_v2_mrna_median_Zscores","ov_tcga_pan_can_atlas_2018_rna_seq_v2_mrna_median_all_sample_Zscores","ov_tcga_pan_can_atlas_2018_rppa_Zscores"],"availableComparisonTabs":["overlap","clinical","survival","alterations","mutations","mrna","protein","generic_assay_armlevel_cna","generic_assay_genetic_ancestry","generic_assay_methylation","generic_assay_phosphosite_quantification"]}}]}}

▶ clickhouse_run_select_query
{
 "query": "
SELECT *
FROM gene_pair_coexpression(
    study        = 'ov_tcga_pan_can_atlas_2018',
    gene_a       = 'EGFR',
    gene_b       = 'EGFR',
    profile_type_a = 'rna_seq_v2_mrna',
    profile_type_b = 'rppa'
)
",
 "max_rows": 10
}

◀ result
{"error_message":"Query execution failed: HTTPDriver for https://dl96orhu96.us-east-1.aws.clickhouse.cloud:8443 received ClickHouse error code 456\n Code: 456. DB::Exception: Substitution `profile_type` is not set. (UNKNOWN_QUERY_PARAMETER) (version 26.4.1.2359 (official build))\n"}

▶ clickhouse_run_select_query
{
 "query": "
WITH mrna_data AS (
    SELECT sample_unique_id, toFloat64OrNull(alteration_value) AS mrna_value
    FROM genetic_alteration_derived
    WHERE cancer_study_identifier = 'ov_tcga_pan_can_atlas_2018'
      AND hugo_gene_symbol = 'EGFR'
      AND profile_type = 'rna_seq_v2_mrna'
      AND alteration_value NOT IN ('', 'NA')
      AND toFloat64OrNull(alteration_value) IS NOT NULL
),
protein_data AS (
    SELECT sample_unique_id, toFloat64OrNull(alteration_value) AS protein_value
    FROM genetic_alteration_derived
    WHERE cancer_study_identifier = 'ov_tcga_pan_can_atlas_2018'
      AND hugo_gene_symbol = 'EGFR'
      AND profile_type = 'rppa'
      AND alteration_value NOT IN ('', 'NA')
      AND toFloat64OrNull(alteration_value) IS NOT NULL
)
SELECT 
    'EGFR mRNA (rna_seq_v2_mrna) vs EGFR Protein (RPPA)' AS comparison,
    round(spearmanCorr(m.mrna_value, p.protein_value), 3) AS spearman_correlation,
    count() AS num_samples
FROM mrna_data m
INNER JOIN protein_data p USING (sample_unique_id)
",
 "max_rows": 10
}

◀ result
{"error_message":"Query execution failed: HTTPDriver for https://dl96orhu96.us-east-1.aws.clickhouse.cloud:8443 received ClickHouse error code 46\n Code: 46. DB::Exception: Function with name `spearmanCorr` does not exist. In scope WITH mrna_data AS (SELECT sample_unique_id, toFloat64OrNull(alteration_value) AS mrna_value FROM genetic_alteration_derived WHERE (cancer_study_identifier = 'ov_tcga_pan_can_atlas_2018') AND (hugo_gene_symbol = 'EGFR') AND (profile_type = 'rna_seq_v2_mrna') AND (alteration_value NOT IN ('', 'NA')) AND (toFloat64OrNull(alteration_value) IS NOT NULL)), protein_data AS (SELECT sample_unique_id, toFloat64OrNull(alteration_value) AS protein_value FROM genetic_alteration_derived WHERE (cancer_study_identifier = 'ov_tcga_pan_can_atlas_2018') AND (hugo_gene_symbol = 'EGFR') AND (profile_type = 'rppa') AND (alteration_value NOT IN ('', 'NA')) AND (toFloat64OrNull(alteration_value) IS NOT NULL)) SELECT 'EGFR mRNA (rna_seq_v2_mrna) vs EGFR Protein (RPPA)' AS comparison, round(spearmanCorr(m.mrna_value, p.protein_value), 3) AS spearman_correlation, count() AS num_samples FROM mrna_data AS m INNER JOIN protein_data AS p USING (sample_unique_id). (UNKNOWN_FUNCTION) (version 26.4.1.2359 (official build))\n"}

▶ clickhouse_run_select_query
{
 "query": "
WITH mrna_data AS (
    SELECT sample_unique_id, toFloat64OrNull(alteration_value) AS mrna_value
    FROM genetic_alteration_derived
    WHERE cancer_study_identifier = 'ov_tcga_pan_can_atlas_2018'
      AND hugo_gene_symbol = 'EGFR'
      AND profile_type = 'rna_seq_v2_mrna'
      AND alteration_value NOT IN ('', 'NA')
      AND toFloat64OrNull(alteration_value) IS NOT NULL
),
protein_data AS (
    SELECT sample_unique_id, toFloat64OrNull(alteration_value) AS protein_value
    FROM genetic_alteration_derived
    WHERE cancer_study_identifier = 'ov_tcga_pan_can_atlas_2018'
      AND hugo_gene_symbol = 'EGFR'
      AND profile_type = 'rppa'
      AND alteration_value NOT IN ('', 'NA')
      AND toFloat64OrNull(alteration_value) IS NOT NULL
)
SELECT 
    'EGFR mRNA (rna_seq_v2_mrna) vs EGFR Protein (RPPA)' AS comparison,
    round(rankCorr(m.mrna_value, p.protein_value), 3) AS spearman_correlation,
    count() AS num_samples
FROM mrna_data m
INNER JOIN protein_data p USING (sample_unique_id)
",
 "max_rows": 10
}

◀ result
{"rows":[{"comparison":"EGFR mRNA (rna_seq_v2_mrna) vs EGFR Protein (RPPA)","spearman_correlation":0.451,"num_samples":236}]}

▶ navigate_to_results_view
{
 "studyIds": [
  "ov_tcga_pan_can_atlas_2018"
 ],
 "genes": [
  "EGFR"
 ],
 "tab": "plots",
 "plotsVertSelection": {
  "selectedGeneOption": "EGFR",
  "dataType": "protein_level"
 },
 "plotsHorzSelection": {
  "selectedGeneOption": "EGFR",
  "dataType": "mRNA_expression"
 },
 "zScoreThreshold": 2,
 "rppaScoreThreshold": 0
}

◀ result
{"success":true,"message":"Navigating to https://www.cbioportal.org/results/plots?cancer_study_list=ov_tcga_pan_can_atlas_2018&gene_list=EGFR&case_set_id=ov_tcga_pan_can_atlas_2018_all&Z_SCORE_THRESHOLD=2&RPPA_SCORE_THRESHOLD=0&plots_horz_selection=%7B%22selectedGeneOption%22%3A%221956%22%2C%22dataType%22%3A%22mRNA_expression%22%7D&plots_vert_selection=%7B%22selectedGeneOption%22%3A%221956%22%2C%22dataType%22%3A%22protein_level%22%7D","url":"https://www.cbioportal.org/results/plots?cancer_study_list=ov_tcga_pan_can_atlas_2018&gene_list=EGFR&case_set_id=ov_tcga_pan_can_atlas_2018_all&Z_SCORE_THRESHOLD=2&RPPA_SCORE_THRESHOLD=0&plots_horz_selection=%7B%22selectedGeneOption%22%3A%221956%22%2C%22dataType%22%3A%22mRNA_expression%22%7D&plots_vert_selection=%7B%22selectedGeneOption%22%3A%221956%22%2C%22dataType%22%3A%22protein_level%22%7D","data":{"studyIds":["ov_tcga_pan_can_atlas_2018"],"studies":[{"studyId":"ov_tcga_pan_can_atlas_2018","name":"Ovarian Serous Cystadenocarcinoma (TCGA, PanCancer Atlas)","sampleCount":585}],"genes":["EGFR"],"caseSetId":"ov_tcga_pan_can_atlas_2018_all","pageDescription":"Scatter/box plots: EGFR protein_level (y-axis) by EGFR mrna_expression (x-axis)."}}

═ answer (success)
## Results

**EGFR mRNA–Protein Correlation in TCGA PanCancer Atlas Ovarian Cancer:**

| Correlation Measure | Value |
|---|---|
| **Spearman correlation** | **0.451** |
| **Sample pairs** | **236 tumors** |

The **moderate positive correlation (r = 0.451)** between EGFR mRNA expression and EGFR protein (RPPA) levels indicates that mRNA and protein levels are substantially but not tightly coupled in ovarian cancer. This moderate strength is typical for mRNA–protein relationships — post-transcriptional regulation (protein stability, translation efficiency, phosphorylation) introduces variance that mRNA alone doesn't capture.

**Visualization:**
[View EGFR mRNA vs Protein correlation plot](https://www.cbioportal.org/results/plots?cancer_study_list=ov_tcga_pan_can_atlas_2018&gene_list=EGFR&case_set_id=ov_tcga_pan_can_atlas_2018_all&Z_SCORE_THRESHOLD=2&RPPA_SCORE_THRESHOLD=0&plots_horz_selection=%7B%22selectedGeneOption%22%3A%221956%22%2C%22dataType%22%3A%22mRNA_expression%22%7D&plots_vert_selection=%7B%22selectedGeneOption%22%3A%221956%22%2C%22dataType%22%3A%22protein_level%22%7D)

This link opens the Plots tab configured to show EGFR protein (RPPA, y-axis) vs EGFR mRNA (x-axis) as a scatter plot across all 585 ovarian cancer samples. The 236 samples with measurements of both assays define the correlation.