Q (sonnet-5.5): Are there studies that were not processed using polyA enrichment in order to explore lncRNA-related questions?

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

◀ 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)

▶ clickhouse_run_select_query
{
 "query": "SELECT cancer_study_identifier, name, substring(description,1,300) d FROM cancer_study WHERE positionCaseInsensitive(description,'ribo')>0 OR positionCaseInsensitive(description,'polyA')>0 OR positionCaseInsensitive(description,'poly-A')>0 OR positionCaseInsensitive(description,'total RNA')>0 OR positionCaseInsensitive(description,'lncRNA')>0 OR positionCaseInsensitive(description,'rRNA')>0"
}

◀ result
{"rows":[]}

▶ clickhouse_list_tables
{}

◀ result
{"tables":[{"name":"allele_specific_copy_number"},{"name":"alteration_driver_annotation"},{"name":"cancer_study"},{"name":"cancer_study_query_preferences"},{"name":"cancer_study_tags"},{"name":"cbioportal_sequence_state"},{"name":"clinical_attribute_counts"},{"name":"clinical_attribute_meta"},{"name":"clinical_data_derived"},{"name":"clinical_event"},{"name":"clinical_event_data"},{"name":"clinical_event_data_derived"},{"name":"clinical_event_derived"},{"name":"clinical_patient"},{"name":"clinical_sample"},{"name":"cna_event"},{"name":"cna_panel_gene_coverage"},{"name":"cna_wes_coverage"},{"name":"co_altered_genes_in_study"},{"name":"copy_number_seg"},{"name":"copy_number_seg_file"},{"name":"gene"},{"name":"gene_alias"},{"name":"gene_alteration_frequency_by_cancer_type"},{"name":"gene_cna_distribution_in_study"},{"name":"gene_mutation_frequency_by_cancer_type"},{"name":"gene_mutation_frequency_in_studies"},{"name":"gene_mutation_frequency_in_study"},{"name":"gene_mutation_variants_in_study"},{"name":"gene_pair_coexpression"},{"name":"gene_panel"},{"name":"gene_panel_list"},{"name":"gene_panel_to_gene_derived"},{"name":"generic_assay_data_derived"},{"name":"generic_assay_meta_derived"},{"name":"generic_assay_profile_entity_derived"},{"name":"generic_entity_properties"},{"name":"geneset"},{"name":"geneset_gene"},{"name":"geneset_hierarchy_leaf"},{"name":"geneset_hierarchy_node"},{"name":"genetic_alteration"},{"name":"genetic_alteration_derived"},{"name":"genetic_entity"},{"name":"genetic_profile"},{"name":"genetic_profile_link"},{"name":"genetic_profile_samples"},{"name":"genomic_event_derived"},{"name":"gistic"},{"name":"gistic_to_gene"},{"name":"info"},{"name":"mut_sig"},{"name":"mutation"},{"name":"mutation_count_by_keyword"},{"name":"mutation_derived"},{"name":"mutation_event"},{"name":"mutation_panel_gene_coverage"},{"name":"mutation_wes_coverage"},{"name":"patient"},{"name":"reference_genome"},{"name":"reference_genome_gene"},{"name":"resource_definition"},{"name":"resource_patient"},{"name":"resource_sample"},{"name":"resource_study"},{"name":"sample"},{"name":"sample_cna_event"},{"name":"sample_derived"},{"name":"sample_list"},{"name":"sample_list_list"},{"name":"sample_profile"},{"name":"sample_to_gene_panel_derived"},{"name":"structural_variant"},{"name":"sv_panel_gene_coverage"},{"name":"sv_wes_coverage"},{"name":"top_cna_genes_in_study"},{"name":"top_mutated_genes_in_cohort"},{"name":"top_mutated_genes_in_study"},{"name":"top_sv_genes_in_study"},{"name":"treatment_counts_in_study"},{"name":"treatment_regimens_in_study"},{"name":"type_of_cancer"}]}

▶ clickhouse_list_table_columns
{
 "table": "genetic_profile"
}

◀ result
{"columns":[{"name":"genetic_profile_id","type":"Int64"},{"name":"stable_id","type":"String"},{"name":"cancer_study_id","type":"Int64"},{"name":"genetic_alteration_type","type":"String"},{"name":"generic_assay_type","type":"Nullable(String)"},{"name":"datatype","type":"String"},{"name":"name","type":"String"},{"name":"description","type":"Nullable(String)"},{"name":"show_profile_in_analysis_tab","type":"Int32"},{"name":"pivot_threshold","type":"Nullable(Float64)"},{"name":"sort_order","type":"Nullable(String)"},{"name":"patient_level","type":"Nullable(Int32)"}]}

▶ clickhouse_run_select_query
{
 "query": "SELECT stable_id, name, substring(description,1,400) d FROM genetic_profile WHERE genetic_alteration_type='MRNA_EXPRESSION' AND (positionCaseInsensitive(description,'ribo')>0 OR positionCaseInsensitive(description,'polyA')>0 OR positionCaseInsensitive(description,'poly-A')>0 OR positionCaseInsensitive(description,'poly(A)')>0 OR positionCaseInsensitive(description,'total RNA')>0 OR positionCaseInsensitive(description,'lncRNA')>0 OR positionCaseInsensitive(description,'rRNA')>0 OR positionCaseInsensitive(description,'depletion')>0 OR positionCaseInsensitive(description,'capture')>0 OR positionCaseInsensitive(name,'total')>0)"
}

◀ result
{"rows":[{"stable_id":"prad_eururol_2017_rna_seq_mrna","name":"mRNA expression (FPKM)","d":"mRNA expression from capture (RNA Seq FPKM)."},{"stable_id":"brca_tcga_gdc_mrna_seq_tpm","name":"mRNA Expression (TPM)","d":"mRNA expression from capture (RNA Seq TPM)"},{"stable_id":"brca_tcga_gdc_mrna_seq_tpm_Zscores","name":"mRNA Expression TPM z-scores","d":"mRNA expression from capture (RNA Seq TPM) zscores"},{"stable_id":"brca_tcga_gdc_mrna_seq_fpkm","name":"mRNA Expression (FPKM)","d":"mRNA expression from capture (RNA Seq FPKM)"},{"stable_id":"brca_tcga_gdc_mrna_seq_fpkm_Zscores","name":"mRNA Expression FPKM z-scores","d":"mRNA expression from capture (RNA Seq FPKM) zscores"},{"stable_id":"brca_tcga_gdc_mrna_seq_read_counts_Zscores","name":"mRNA Expression z-scores","d":"mRNA expression from capture read count zscores"},{"stable_id":"cesc_tcga_gdc_mrna_seq_tpm","name":"mRNA Expression (TPM)","d":"mRNA expression from capture (RNA Seq TPM)"},{"stable_id":"cesc_tcga_gdc_mrna_seq_tpm_Zscores","name":"mRNA Expression TPM z-scores","d":"mRNA expression from capture (RNA Seq TPM) zscores"},{"stable_id":"cesc_tcga_gdc_mrna_seq_fpkm","name":"mRNA Expression (FPKM)","d":"mRNA expression from capture (RNA Seq FPKM)"},{"stable_id":"cesc_tcga_gdc_mrna_seq_fpkm_Zscores","name":"mRNA Expression FPKM z-scores","d":"mRNA expression from capture (RNA Seq FPKM) zscores"},{"stable_id":"cesc_tcga_gdc_mrna_seq_read_counts_Zscores","name":"mRNA Expression z-scores","d":"mRNA expression from capture read count zscores"},{"stable_id":"chol_tcga_gdc_mrna_seq_tpm","name":"mRNA Expression (TPM)","d":"mRNA expression from capture (RNA Seq TPM)"},{"stable_id":"chol_tcga_gdc_mrna_seq_tpm_Zscores","name":"mRNA Expression TPM z-scores","d":"mRNA expression from capture (RNA Seq TPM) zscores"},{"stable_id":"chol_tcga_gdc_mrna_seq_fpkm","name":"mRNA Expression (FPKM)","d":"mRNA expression from capture (RNA Seq FPKM)"},{"stable_id":"chol_tcga_gdc_mrna_seq_fpkm_Zscores","name":"mRNA Expression FPKM z-scores","d":"mRNA expression from capture (RNA Seq FPKM) zscores"},{"stable_id":"chol_tcga_gdc_mrna_seq_read_counts_Zscores","name":"mRNA Expression z-scores","d":"mRNA expression from capture read count zscores"},{"stable_id":"dlbclnos_tcga_gdc_mrna_seq_tpm","name":"mRNA Expression (TPM)","d":"mRNA expression from capture (RNA Seq TPM)"},{"stable_id":"dlbclnos_tcga_gdc_mrna_seq_tpm_Zscores","name":"mRNA Expression TPM z-scores","d":"mRNA expression from capture (RNA Seq TPM) zscores"},{"stable_id":"dlbclnos_tcga_gdc_mrna_seq_fpkm","name":"mRNA Expression (FPKM)","d":"mRNA expression from capture (RNA Seq FPKM)"},{"stable_id":"dlbclnos_tcga_gdc_mrna_seq_fpkm_Zscores","name":"mRNA Expression FPKM z-scores","d":"mRNA expression from capture (RNA Seq FPKM) zscores"},{"stable_id":"dlbclnos_tcga_gdc_mrna_seq_read_counts_Zscores","name":"mRNA Expression z-scores","d":"mRNA expression from capture read count zscores"},{"stable_id":"esca_tcga_gdc_mrna_seq_tpm","name":"mRNA Expression (TPM)","d": … (11136 more chars)

▶ clickhouse_run_select_query
{
 "query": "SELECT stable_id, name, substring(description,1,500) d FROM genetic_profile WHERE positionCaseInsensitive(description,'ribo')>0 OR positionCaseInsensitive(description,'polyA')>0 OR positionCaseInsensitive(description,'poly-A')>0 OR positionCaseInsensitive(description,'poly(A)')>0 OR positionCaseInsensitive(description,'total RNA')>0 OR positionCaseInsensitive(description,'lncRNA')>0 OR positionCaseInsensitive(description,'rRNA')>0 OR positionCaseInsensitive(description,'depleti')>0 OR positionCaseInsensitive(description,'noncoding')>0 OR positionCaseInsensitive(description,'non-coding')>0 OR positionCaseInsensitive(name,'lncRNA')>0"
}

◀ result
{"rows":[{"stable_id":"prad_su2c_2019_mrna_seq_fpkm_polya","name":"mRNA expression (FPKM polyA)","d":"mRNA expression from polyA (RNA Seq FPKM)"},{"stable_id":"prad_su2c_2019_mrna_seq_fpkm_polya_Zscores","name":"mRNA expression z-scores relative to diploid samples (FPKM polyA)","d":"mRNA expression from polyA Z-scores (RNA Seq FPKM) compared to the expression distribution of each gene tumors that are diploid for this gene."},{"stable_id":"prad_su2c_2019_mrna_seq_fpkm_polya_all_sample_Zscores","name":"mRNA expression z-scores relative to all samples (log FPKM polyA)","d":"Log-transformed mRNA z-scores compared to expression distribution of all samples (RNA Seq FPKM polyA)."}]}

▶ clickhouse_run_select_query
{
 "query": "SELECT cancer_study_identifier, name FROM cancer_study WHERE positionCaseInsensitive(description,'stranded')>0 OR positionCaseInsensitive(description,'non-coding')>0 OR positionCaseInsensitive(description,'noncoding')>0 OR positionCaseInsensitive(description,'long non')>0 OR positionCaseInsensitive(name,'lncRNA')>0"
}

◀ result
{"rows":[]}

▶ clickhouse_run_select_query
{
 "query": "SELECT stable_id, name, description FROM genetic_profile WHERE stable_id LIKE 'prad_su2c_2019%' AND genetic_alteration_type='MRNA_EXPRESSION'"
}

◀ result
{"rows":[{"stable_id":"prad_su2c_2019_mrna_seq_fpkm_capture","name":"mRNA expression (FPKM capture)","description":"mRNA expression from capture (RNA Seq FPKM)"},{"stable_id":"prad_su2c_2019_mrna_seq_fpkm_capture_Zscores","name":"mRNA expression z-scores relative to diploid samples (FPKM capture)","description":"mRNA expression from capture Z-scores (RNA Seq FPKM) compared to the expression distribution of each gene tumors that are diploid for this gene."},{"stable_id":"prad_su2c_2019_mrna_seq_fpkm_capture_all_sample_Zscores","name":"mRNA expression z-scores relative to all samples (log FPKM capture)","description":"Log-transformed mRNA z-scores compared to expression distribution of all samples (RNA Seq FPKM)."},{"stable_id":"prad_su2c_2019_mrna_seq_fpkm_polya","name":"mRNA expression (FPKM polyA)","description":"mRNA expression from polyA (RNA Seq FPKM)"},{"stable_id":"prad_su2c_2019_mrna_seq_fpkm_polya_Zscores","name":"mRNA expression z-scores relative to diploid samples (FPKM polyA)","description":"mRNA expression from polyA Z-scores (RNA Seq FPKM) compared to the expression distribution of each gene tumors that are diploid for this gene."},{"stable_id":"prad_su2c_2019_mrna_seq_fpkm_polya_all_sample_Zscores","name":"mRNA expression z-scores relative to all samples (log FPKM polyA)","description":"Log-transformed mRNA z-scores compared to expression distribution of all samples (RNA Seq FPKM polyA)."}]}

▶ clickhouse_run_select_query
{
 "query": "SELECT count() c, countIf(hugo_gene_symbol LIKE 'LINC%' OR hugo_gene_symbol IN ('MALAT1','NEAT1','XIST','HOTAIR','H19')) l FROM genetic_alteration_derived WHERE cancer_study_identifier='prad_su2c_2019' AND profile_type LIKE '%polya' AND hugo_gene_symbol IN ('MALAT1','NEAT1','XIST','HOTAIR','H19','PCA3','PCAT1')"
}

◀ result
{"rows":[{"c":0,"l":0}]}

▶ clickhouse_run_select_query
{
 "query": "SELECT c.cancer_study_identifier, c.name, g.stable_id FROM genetic_profile g JOIN cancer_study c ON c.cancer_study_id=g.cancer_study_id WHERE g.stable_id LIKE '%capture' AND g.genetic_alteration_type='MRNA_EXPRESSION' AND g.stable_id NOT LIKE '%tcga_gdc%' ORDER BY 1"
}

◀ result
{"rows":[{"cancer_study_identifier":"pancan_pcawg_2020","c.name":"Pan-cancer analysis of whole genomes (ICGC/TCGA, Nature 2020)","stable_id":"pancan_pcawg_2020_mrna_seq_fpkm_capture"},{"cancer_study_identifier":"prad_su2c_2019","c.name":"Metastatic Prostate Adenocarcinoma (SU2C/PCF Dream Team, PNAS 2019)","stable_id":"prad_su2c_2019_mrna_seq_fpkm_capture"}]}

▶ clickhouse_run_select_query
{
 "query": "SELECT profile_type, count() FROM genetic_alteration_derived WHERE cancer_study_identifier='prad_su2c_2019' AND hugo_gene_symbol IN ('MALAT1','NEAT1','PCA3','PCAT1','H19') AND profile_type LIKE '%fpkm%' GROUP BY profile_type"
}

◀ result
{"rows":[]}

═ answer (success)
I'm wrapping up my search of the study and expression-profile descriptions.

**Short answer:** cBioPortal doesn't have a searchable field for library prep. The one study I found that labels its RNA-seq by prep method is the SU2C/PCF metastatic prostate study, which has both polyA and capture profiles. I couldn't confirm lncRNA coverage in either.

**Where prep is labeled**
- [Metastatic Prostate Adenocarcinoma (SU2C/PCF Dream Team, PNAS 2019)](https://www.cbioportal.org/study/summary?id=prad_su2c_2019) has two sets of RNA-seq profiles:
  - **polyA:** FPKM, plus two z-score versions.
  - **Capture (exome capture RNA-seq):** FPKM, plus two z-score versions. This is not polyA-selected, so it is the candidate for non-polyA questions.
- [Pan-cancer analysis of whole genomes (ICGC/TCGA, Nature 2020)](https://www.cbioportal.org/study/summary?id=pancan_pcawg_2020) has an FPKM profile labeled "capture". I found no polyA-labeled profile in it.

**Where prep is not labeled**
- The TCGA GDC studies (BRCA, LUAD, GBM and others) describe their expression as "from capture (RNA Seq TPM/FPKM)". That wording looks like a generic template, so I wouldn't read it as a real statement about library prep.
- Other studies' descriptions don't mention polyA, ribo-depletion, total RNA or rRNA. Their prep method isn't recorded in cBioPortal, so you'd need the original publication or the data provider.

**lncRNA caveat**
- cBioPortal expression data is indexed by gene symbol. When I queried the SU2C prostate study for well-known lncRNAs (MALAT1, NEAT1, PCA3, PCAT1, H19), I got no rows in the FPKM profiles. I didn't check the polyA and capture profiles separately, and I didn't check other lncRNAs.
- Non-polyA data doesn't guarantee lncRNA coverage, and the portal's gene set may exclude most lncRNAs. Before building an analysis, check whether your lncRNAs of interest are present in the profile.

For a definitive answer on library prep for a specific cohort, check the study's publication. If you tell me the cancer type or a candidate lncRNA, I can check which studies actually contain it.