Q (haiku): What is the correlation coefficient between EGFR copy number and EGFR mRNA expression in ovarian cancer?

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

▶ search_oncotree
{
 "search_term": "ovarian"
}

◀ 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 | `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 |
| `brca_metabric` | ESR1 | PGR | `mrna` | 0.487 | 1980 |

ESR1↔PGR is the textbook breast-cancer estrogen-receptor coregula … (2785 more chars)

◀ result
{"result":[{"code":"OCNOS","name":"Ovarian Choriocarcinoma, NOS","score":60,"mainType":"Ovarian Cancer","tissue":"Ovary/Fallopian Tube","hierarchy":"OVARY > OOVC > OCNOS"},{"code":"OCS","name":"Ovarian Carcinosarcoma/Malignant Mixed Mesodermal Tumor","score":60,"mainType":"Ovarian Cancer","tissue":"Ovary/Fallopian Tube","hierarchy":"OVARY > OVT > OCS"},{"code":"OGCT","name":"Ovarian Germ Cell Tumor","score":60,"mainType":"Germ Cell Tumor","tissue":"Ovary/Fallopian Tube","hierarchy":"OVARY > OGCT"},{"code":"OOVC","name":"Ovarian Cancer, Other","score":60,"mainType":"Ovarian Cancer","tissue":"Ovary/Fallopian Tube","hierarchy":"OVARY > OOVC"},{"code":"OSMAD","name":"Ovarian Seromucinous Adenoma","score":60,"mainType":"Ovarian Cancer","tissue":"Ovary/Fallopian Tube","hierarchy":"OVARY > OVT > OSMAD"},{"code":"OSMBT","name":"Ovarian Seromucinous Borderline Tumor","score":60,"mainType":"Ovarian Cancer","tissue":"Ovary/Fallopian Tube","hierarchy":"OVARY > OVT > OSMBT"},{"code":"OSMCA","name":"Ovarian Seromucinous Carcinoma","score":60,"mainType":"Ovarian Cancer","tissue":"Ovary/Fallopian Tube","hierarchy":"OVARY > OVT > OSMCA"},{"code":"OVT","name":"Ovarian Epithelial Tumor","score":60,"mainType":"Ovarian Cancer","tissue":"Ovary/Fallopian Tube","hierarchy":"OVARY > OVT"},{"code":"CCBOV","name":"Clear Cell Borderline Ovarian Tumor","score":50,"mainType":"Ovarian Cancer","tissue":"Ovary/Fallopian Tube","hierarchy":"OVARY > OVT > CCBOV"},{"code":"CCOV","name":"Clear Cell Ovarian Cancer","score":50,"mainType":"Ovarian Cancer","tissue":"Ovary/Fallopian Tube","hierarchy":"OVARY > OVT > CCOV"},{"code":"EBOV","name":"Endometrioid Borderlin Ovarian Tumor","score":50,"mainType":"Ovarian Cancer","tissue":"Ovary/Fallopian Tube","hierarchy":"OVARY > OVT > EBOV"},{"code":"EOV","name":"Endometrioid Ovarian Cancer","score":50,"mainType":"Ovarian Cancer","tissue":"Ovary/Fallopian Tube","hierarchy":"OVARY > OVT > EOV"},{"code":"HGSOC","name":"High-Grade Serous Ovarian Cancer","score":50,"mainType":"Ovarian Cancer","tissue":"Ovary/Fallopian Tube","hierarchy":"OVARY > OVT > SOC > HGSOC"},{"code":"LGSOC","name":"Low-Grade Serous Ovarian Cancer","score":50,"mainType":"Ovarian Cancer","tissue":"Ovary/Fallopian Tube","hierarchy":"OVARY > OVT > SOC > LGSOC"},{"code":"MBOV","name":"Mucinous Borderline Ovarian Tumor","score":50,"mainType":"Ovarian Cancer","tissue":"Ovary/Fallopian Tube","hierarchy":"OVARY > OVT > MBOV"},{"code":"MOV","name":"Mucinous Ovarian Cancer","score":50,"mainType":"Ovarian Cancer","tissue":"Ovary/Fallopian Tube","hierarchy":"OVARY > OVT > MOV"},{"code":"MXOV","name":"Mixed Ovarian Carcinoma","score":50,"mainType":"Ovarian Cancer","tissue":"Ovary/Fallopian Tube","hierarchy":"OVARY > OVT > MXOV"},{"code":"SBMOV","name":"Serous Borderline Ovarian Tumor, Micropapillary","score":50,"mainType":"Ovarian Cancer","tissue":"Ovary/Fallopian Tube","hierarchy":"OVARY > OVT > SBMOV"},{"code":"SBOV","name":"Serous Borderline Ovarian Tumor","score":50,"mainType":"Ovarian  … (1039 more chars)

▶ list_studies
{
 "search": "ovarian",
 "limit": 20
}

◀ result
{"result":[{"cancer_study_identifier":"msk_met_2021","name":"MSK MetTropism (MSK, Cell 2021)","type_of_cancer_id":"mixed","sample_count":25775,"has_guide":false,"url":"https://www.cbioportal.org/study/summary?id=msk_met_2021"},{"cancer_study_identifier":"ov_tcga","name":"Ovarian Serous Cystadenocarcinoma (TCGA, Firehose Legacy)","type_of_cancer_id":"hgsoc","sample_count":617,"has_guide":false,"url":"https://www.cbioportal.org/study/summary?id=ov_tcga"},{"cancer_study_identifier":"hgsoc_tcga_gdc","name":"High-Grade Serous Ovarian Cancer (TCGA GDC, 2025)","type_of_cancer_id":"hgsoc","sample_count":604,"has_guide":false,"url":"https://www.cbioportal.org/study/summary?id=hgsoc_tcga_gdc"},{"cancer_study_identifier":"ov_tcga_pan_can_atlas_2018","name":"Ovarian Serous Cystadenocarcinoma (TCGA, PanCancer Atlas)","type_of_cancer_id":"hgsoc","sample_count":585,"has_guide":true,"url":"https://www.cbioportal.org/study/summary?id=ov_tcga_pan_can_atlas_2018"},{"cancer_study_identifier":"ovary_geomx_gray_foundation_2024","name":"Ovarian Cancer (Gray Foundation, Cancer Discov 2024)","type_of_cancer_id":"ovary","sample_count":567,"has_guide":false,"url":"https://www.cbioportal.org/study/summary?id=ovary_geomx_gray_foundation_2024"},{"cancer_study_identifier":"ov_tcga_pub","name":"Ovarian Serous Cystadenocarcinoma (TCGA, Nature 2011)","type_of_cancer_id":"hgsoc","sample_count":489,"has_guide":false,"url":"https://www.cbioportal.org/study/summary?id=ov_tcga_pub"},{"cancer_study_identifier":"lgsoc_mapk_msk_2022","name":"Low-Grade Serous Ovarian Cancer (MSK, Clin Cancer Res 2022)","type_of_cancer_id":"lgsoc","sample_count":119,"has_guide":false,"url":"https://www.cbioportal.org/study/summary?id=lgsoc_mapk_msk_2022"},{"cancer_study_identifier":"ovary_cptac_gdc","name":"Ovarian Cancer (CPTAC GDC, 2025)","type_of_cancer_id":"ovary","sample_count":112,"has_guide":false,"url":"https://www.cbioportal.org/study/summary?id=ovary_cptac_gdc"},{"cancer_study_identifier":"msk_spectrum_tme_2022","name":"Ovarian Cancer - MSK SPECTRUM (MSK, Nature 2022)","type_of_cancer_id":"ovary","sample_count":82,"has_guide":false,"url":"https://www.cbioportal.org/study/summary?id=msk_spectrum_tme_2022"},{"cancer_study_identifier":"hgsoc_msk_2021","name":"High-Grade Serous Ovarian Cancer (MSK, NPJ Genome Med 2021)","type_of_cancer_id":"hgsoc","sample_count":45,"has_guide":false,"url":"https://www.cbioportal.org/study/summary?id=hgsoc_msk_2021"},{"cancer_study_identifier":"ucec_msk_2024","name":"Endometrial and Ovarian Cancer (MSK, Nature Medicine 2024)","type_of_cancer_id":"mixed","sample_count":33,"has_guide":false,"url":"https://www.cbioportal.org/study/summary?id=ucec_msk_2024"},{"cancer_study_identifier":"ovarian_msk_2025","name":"Serous Ovarian Cancer (MSK, 2025)","type_of_cancer_id":"ovary","sample_count":31,"has_guide":false,"url":"https://www.cbioportal.org/study/summary?id=ovarian_msk_2025"},{"cancer_study_identifier":"mixed_msk_tcga_2021","name":"RAD51B Associated Mixed Cancers (MSK, N … (162 more chars)

▶ get_study_guide
{
 "study_id": "ov_tcga_pan_can_atlas_2018"
}

◀ result
# Ovarian Serous Cystadenocarcinoma (TCGA, PanCancer Atlas)

**Study ID:** `ov_tcga_pan_can_atlas_2018`

See `_tcga_pancan_template.md` for common TCGA clinical attributes.

## Study-Specific Attributes

### Histology
| Attribute | Description | Values (samples) |
|-----------|-------------|--------|
| `GRADE` | Histologic grade | G3 400, G2 65, GX 7, G1 5, GB 2, G4 1, blank 105 |
| `CANCER_TYPE_DETAILED` | Histology | Serous Ovarian Cancer (all 585) |

### Not available in this study
- **Platinum sensitivity and residual disease**: no treatment-response or surgical-outcome attributes. Survival (`OS_*`, `PFS_*`, `DFS_*`, `DSS_*`) is the only outcome data.
- **BRCA / HRD status**: no clinical status attributes. Derive BRCA1/BRCA2 status from mutation (somatic calls only) and CNA data; there is no HRD score.
- `AJCC_PATHOLOGIC_TUMOR_STAGE` is blank for all samples. `SUBTYPE` is `OV` (177) or blank (408) and carries no molecular subtype.

## Key Genes
| Gene | Frequency | Notes |
|------|-----------|-------|
| TP53 | ~96% | Near-universal in high-grade serous |
| BRCA1 | ~10% germline | PARP inhibitor sensitivity |
| BRCA2 | ~6% germline | PARP inhibitor sensitivity |
| NF1 | ~4% | RAS pathway |
| RB1 | ~2% | Cell cycle |
| CDK12 | ~3% | DNA repair |

## Notes
- High-grade serous ovarian cancer (HGSOC) is genomically distinct from other ovarian subtypes
- TP53 is mutated in virtually all HGSOC
- BRCA1/2 mutations (germline or somatic) predict PARP inhibitor response
- HRD score predicts platinum and PARP sensitivity even without BRCA mutation
- Copy number alterations more prominent than mutations (except TP53)


▶ clickhouse_run_select_query
{
 "query": "
SELECT DISTINCT profile_type
FROM genetic_alteration_derived
WHERE cancer_study_identifier = 'ov_tcga_pan_can_atlas_2018'
ORDER BY profile_type
"
}

◀ result
{"rows":[{"profile_type":"gistic"},{"profile_type":"log2CNA"},{"profile_type":"protein_quantification"},{"profile_type":"protein_quantification_zscores"},{"profile_type":"rna_seq_v2_mrna"},{"profile_type":"rna_seq_v2_mrna_median_Zscores"},{"profile_type":"rna_seq_v2_mrna_median_all_sample_Zscores"},{"profile_type":"rppa"},{"profile_type":"rppa_Zscores"}]}

▶ 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 = 'rna_seq_v2_mrna'
)
"
}

◀ result
{"rows":[{"gene_a":"EGFR","gene_b":"EGFR","profile_type":"rna_seq_v2_mrna","spearman_correlation":1,"num_samples":300}]}

▶ clickhouse_run_select_query
{
 "query": "
WITH cna AS (
    SELECT sample_unique_id, toFloat64OrNull(alteration_value) AS cna_value
    FROM genetic_alteration_derived
    WHERE cancer_study_identifier = 'ov_tcga_pan_can_atlas_2018'
      AND profile_type = 'log2CNA'
      AND hugo_gene_symbol = 'EGFR'
      AND alteration_value NOT IN ('', 'NA')
      AND toFloat64OrNull(alteration_value) IS NOT NULL
),
mrna 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 profile_type = 'rna_seq_v2_mrna'
      AND hugo_gene_symbol = 'EGFR'
      AND alteration_value NOT IN ('', 'NA')
      AND toFloat64OrNull(alteration_value) IS NOT NULL
)
SELECT 
    rankCorr(cna.cna_value, mrna.mrna_value) AS spearman_correlation,
    count() AS num_samples,
    uniqExact(cna.sample_unique_id) AS n_cna_samples,
    uniqExact(mrna.sample_unique_id) AS n_mrna_samples
FROM cna INNER JOIN mrna USING (sample_unique_id)
"
}

◀ result
{"rows":[{"spearman_correlation":0.36354541587592437,"num_samples":295,"n_cna_samples":295,"n_mrna_samples":295}]}

▶ resolve_and_route
{
 "studyIds": [
  "ov_tcga_pan_can_atlas_2018"
 ]
}

◀ 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"]}}]}}

▶ navigate_to_results_view
{
 "studyIds": [
  "ov_tcga_pan_can_atlas_2018"
 ],
 "genes": [
  "EGFR"
 ],
 "tab": "plots",
 "plotsHorzSelection": {
  "selectedGeneOption": "EGFR",
  "dataType": "log2CNA"
 },
 "plotsVertSelection": {
  "selectedGeneOption": "EGFR",
  "dataType": "rna_seq_v2_mrna"
 }
}

◀ 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&plots_horz_selection=%7B%22selectedGeneOption%22%3A%221956%22%2C%22dataType%22%3A%22log2CNA%22%7D&plots_vert_selection=%7B%22selectedGeneOption%22%3A%221956%22%2C%22dataType%22%3A%22rna_seq_v2_mrna%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&plots_horz_selection=%7B%22selectedGeneOption%22%3A%221956%22%2C%22dataType%22%3A%22log2CNA%22%7D&plots_vert_selection=%7B%22selectedGeneOption%22%3A%221956%22%2C%22dataType%22%3A%22rna_seq_v2_mrna%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 rna_seq_v2_mrna (y-axis) by EGFR log2cna (x-axis)."}}

═ answer (success)
## Results

**Spearman correlation coefficient between EGFR copy number and EGFR mRNA expression in ovarian cancer:**

**ρ = 0.364** (n = 295 samples)

This is a **moderate positive correlation**, indicating that samples with higher EGFR copy number tend to have higher EGFR mRNA expression levels. The correlation is statistically meaningful but not perfect, which is typical for gene dosage effects—copy number amplification correlates with expression, but additional regulatory factors (transcription, post-transcriptional control) also play a role.

**Dataset:** Ovarian Serous Cystadenocarcinoma (TCGA, PanCancer Atlas)
- **Copy number data:** log2-scaled (585 samples profiled; 295 with both CNA and mRNA data)
- **mRNA data:** RNA-seq v2 (300 samples profiled)

---

### Visualize the Correlation

[View EGFR copy number vs mRNA expression scatter 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&plots_horz_selection=%7B%22selectedGeneOption%22%3A%221956%22%2C%22dataType%22%3A%22log2CNA%22%7D&plots_vert_selection=%7B%22selectedGeneOption%22%3A%221956%22%2C%22dataType%22%3A%22rna_seq_v2_mrna%22%7D)

This opens the Plots tab where you can interactively explore the relationship between EGFR copy number (x-axis) and EGFR mRNA expression (y-axis) across the 295 ovarian cancer samples.