Q (sonnet-5.5): In the “Pan-cancer analysis of whole genomes” study what is the survival difference and the corresponding statistical significance between patients with mutations in both TP53 and KRAS versus patients with only a KRAS mutation? ▶ read_guide { "uri": "cbioportal://statistical-tests-guide" } ▶ list_studies { "search": "pan-cancer whole genomes" } ◀ result Statistical Test Selection Guide ================================ Purpose ------- This guide ensures the correct statistical test is selected before performing any group comparison, matching cBioPortal's own Group Comparison defaults. HARD RULES — NEVER FABRICATE A STATISTIC ---------------------------------------- ClickHouse cannot run statistical tests. The agent therefore must NEVER produce a derived statistic that is not a literal column value from a SQL result. Specifically: 1. **Never invent a p-value.** Not "p < 0.001", not "p ≈ 0.05", not any p-value. If the user asks "what is the p-value?", the answer is *"I can't compute that — here is the 2x2 contingency table (or group statistics). Run it in cBioPortal's Group Comparison tab, in R with `fisher.test(...)` / `wilcox.test(...)`, or in Python with `scipy.stats.fisher_exact(...)` / `mannwhitneyu(...)`."* 2. **Never claim mutual exclusivity (or co-occurrence) from a contingency table alone.** A 2x2 table is not a test. The shape "altered/not altered × group A/group B" needs Fisher's exact + a defined direction (odds ratio < 1 with significant p). Without that test, the agent presents the table and stops. Descriptive phrasing is also a claim: "largely/mostly mutually exclusive", "rarely co-occur", "tend to co-occur" are forbidden without the test — point to cBioPortal's Mutual Exclusivity tab instead. 3. **Never report a "median" that came from `AVG(...)` or any non-median aggregate.** "Median" and "mean" are different statistics; for skewed clinical distributions (especially survival) they differ substantially. Use ClickHouse's `quantile(0.5)(...)` for actual median, and label arithmetic averages as "mean", never "median". 4. **Never report a hazard ratio, odds ratio, risk ratio, or relative risk** that wasn't computed by an external tool. These require regression / model fitting that ClickHouse does not do. 5. **Never report median overall survival from `AVG(OS_MONTHS)` or even `quantile(0.5)(OS_MONTHS)`.** Median OS requires Kaplan-Meier estimation, which handles censoring (`OS_STATUS = 0:LIVING` means the event hasn't happened yet). Naive medians/means over `OS_MONTHS` ignore censoring and are systematically wrong. The correct handoff: return the raw `(OS_MONTHS, OS_STATUS)` pairs (or descriptive counts: N events, N censored, follow-up range) and tell the user to run KM in R (`survival::survfit`) or Python (`lifelines.KaplanMeierFitter`), or use cBioPortal's Survival comparison. If asked to do any of (1)–(5), respond with the appropriate handoff template from the "Approved Response Templates" section below — do not produce the number. Ambiguous-Term Clarification (before any analysis) -------------------------------------------------- If the user's question uses an ambiguous term, ask before computing anything. Examples: - **"aggressive"** → could mean shorter overall survival, higher metastasis rate, higher grade/stage, higher TMB, or specific molecular features. Ask which definit … (9984 more chars) ◀ result {"result":[{"cancer_study_identifier":"pancan_pcawg_2020","name":"Pan-cancer analysis of whole genomes (ICGC/TCGA, Nature 2020)","type_of_cancer_id":"mixed","sample_count":2922,"has_guide":false,"url":"https://www.cbioportal.org/study/summary?id=pancan_pcawg_2020"}]} ▶ read_guide { "uri": "cbioportal://clinical-data-guide" } ▶ clickhouse_list_table_columns { "table": "genomic_event_derived" } ◀ result # Clinical Data Query Guide ## Overview Clinical data in cBioPortal is stored at both patient and sample levels. Understanding the distinction is crucial for accurate analysis. ## Data Organization ### Patient-Level vs Sample-Level Data - **Patient-level**: Demographics, overall survival, disease stage (stored once per patient) - **Sample-level**: Sample type, sequencing platform, purity (can have multiple per patient) ### Key Tables - `clinical_patient`: Patient-level clinical attributes - `clinical_sample`: Sample-level clinical attributes - `clinical_data_derived`: Pre-joined view combining both levels - `clinical_attribute_meta`: Metadata about available clinical attributes ## Recommended Approach: Use clinical_data_derived The `clinical_data_derived` table is pre-joined and optimized for most queries: ```sql -- Get clinical data for specific attributes SELECT sample_unique_id, patient_unique_id, attribute_name, attribute_value FROM clinical_data_derived WHERE cancer_study_identifier = 'your_study_id' AND attribute_name IN ('CANCER_TYPE', 'SAMPLE_TYPE', 'TMB_NONSYNONYMOUS'); ``` ## Clinical Attribute Discovery ### Use clinical_attribute_meta for Discovering Available Attributes **Always start here** to see what clinical attributes are available for a specific study: ```sql -- Discover available clinical attributes for a study SELECT attr_id, description, patient_attribute, cancer_study_id FROM clinical_attribute_meta WHERE cancer_study_id = ( SELECT cancer_study_id FROM cancer_study WHERE cancer_study_identifier = 'your_study_id' ) ORDER BY patient_attribute, attr_id; ``` **Key Fields:** - **attr_id**: matches attr_id in clinical_sample/clinical_patient tables - **description**: human-readable description of the attribute - **patient_attribute**: true = patient attribute, false = sample attribute - **cancer_study_id**: links to cancer_study table (filter by study) ## Attribute Semantics and Matching ### Case-Insensitive Matching for Attribute Values Clinical values are free text across studies and may differ only by case. For example, a controlled-looking value such as germline mutation status may appear as `GERMLINE`, `Germline`, or another case variant. When filtering `clinical_data_derived.attribute_value`, use case-insensitive matching unless you have already profiled the exact values in the target study: ```sql -- Correct: case-insensitive clinical value filter SELECT DISTINCT sample_unique_id, patient_unique_id FROM clinical_data_derived WHERE cancer_study_identifier = 'your_study_id' AND attribute_name = 'MUTATION_STATUS' AND upper(attribute_value) = 'GERMLINE'; ``` Do not write `attribute_value = 'GERMLINE'` without first checking all distinct values for that attribute in the study. ### Query the Requested Attribute, Not a Proxy Do not infer one clinical attribute from a related subtype or marker. Query the actual requested attribute when it … (17073 more chars) ◀ result {"columns":[{"name":"sample_unique_id","type":"String"},{"name":"hugo_gene_symbol","type":"String","comment":"HUGO gene symbol (e.g., TP53, KRAS, BRAF). Use for gene-specific queries."},{"name":"entrez_gene_id","type":"Int32"},{"name":"gene_panel_stable_id","type":"LowCardinality(String)"},{"name":"cancer_study_identifier","type":"LowCardinality(String)"},{"name":"genetic_profile_stable_id","type":"LowCardinality(String)"},{"name":"variant_type","type":"LowCardinality(String)","comment":"Type of genomic event: \"mutation\" for SNVs/indels, \"cna\" for copy number alterations, \"structural_variant\" for SVs. Always filter by this."},{"name":"mutation_variant","type":"String","comment":"Protein change notation (e.g., p.V600E, p.R175H). Use for specific variant queries. \"NA\" for non-mutation events."},{"name":"mutation_type","type":"LowCardinality(String)"},{"name":"mutation_status","type":"LowCardinality(String)","comment":"For mutations: Somatic, Germline, UNKNOWN, or UNCALLED. Filter mutation_status != \"UNCALLED\" to exclude uncertain calls. Include all other statuses."},{"name":"driver_filter","type":"LowCardinality(String)"},{"name":"driver_filter_annotation","type":"String"},{"name":"driver_tiers_filter","type":"LowCardinality(String)"},{"name":"driver_tiers_filter_annotation","type":"String"},{"name":"cna_alteration","type":"Nullable(Int8)","comment":"Copy number alteration: only 2 = amplification (AMP) and -2 = deep deletion (HOMDEL) are stored. Shallow deletion (-1), diploid (0) and gain (1) are not in this table; query genetic_alteration_derived WHERE profile_type = 'gistic' (alteration_value is a String, e.g. '-1'). NULL for non-CNA events."},{"name":"cna_cytoband","type":"String"},{"name":"sv_event_info","type":"String"},{"name":"patient_unique_id","type":"String"},{"name":"off_panel","type":"Bool","comment":"Boolean: 1 = mutation outside gene panel coverage (off-panel), 0 = within panel (on-panel). Filter off_panel = 0 for reliable frequency calculations."}]} ▶ clickhouse_run_select_query { "query": "WITH k AS (SELECT DISTINCT patient_unique_id FROM genomic_event_derived WHERE cancer_study_identifier='pancan_pcawg_2020' AND hugo_gene_symbol='KRAS' AND variant_type='mutation' AND mutation_status!='UNCALLED'), t AS (SELECT DISTINCT patient_unique_id FROM genomic_event_derived WHERE cancer_study_identifier='pancan_pcawg_2020' AND hugo_gene_symbol='TP53' AND variant_type='mutation' AND mutation_status!='UNCALLED'), os AS (SELECT patient_unique_id, maxIf(toFloat64OrNull(attribute_value), attribute_name='OS_MONTHS') AS m, maxIf(attribute_value, attribute_name='OS_STATUS') AS s FROM clinical_data_derived WHERE cancer_study_identifier='pancan_pcawg_2020' AND attribute_name IN ('OS_MONTHS','OS_STATUS') GROUP BY patient_unique_id) SELECT if(patient_unique_id IN (SELECT patient_unique_id FROM t),'KRAS+TP53','KRAS only') g, count() total_kras_pts, countIf(m IS NOT NULL AND s!='') n_os, countIf(startsWith(s,'1')) events, countIf(startsWith(s,'0')) censored, min(m) mn, max(m) mx FROM (SELECT patient_unique_id FROM k) LEFT JOIN os USING patient_unique_id GROUP BY g" } ▶ resolve_and_route { "studyIds": [ "pancan_pcawg_2020" ] } ◀ result {"error_message":"Query execution failed: HTTPDriver for https://dl96orhu96.us-east-1.aws.clickhouse.cloud:8443 received ClickHouse error code 206\n Code: 206. DB::Exception: JOIN LEFT JOIN ... USING (patient_unique_id) no alias for subquery or table function SELECT patient_unique_id FROM k. In scope WITH k AS (SELECT DISTINCT patient_unique_id FROM genomic_event_derived WHERE (cancer_study_identifier = 'pancan_pcawg_2020') AND (hugo_gene_symbol = 'KRAS') AND (variant_type = 'mutation') AND (mutation_status != 'UNCALLED')), t AS (SELECT DISTINCT patient_unique_id FROM genomic_event_derived WHERE (cancer_study_identifier = 'pancan_pcawg_2020') AND (hugo_gene_symbol = 'TP53') AND (variant_type = 'mutation') AND (mutation_status != 'UNCALLED')), os AS (SELECT patient_unique_id, maxIf(toFloat64OrNull(attribute_value), attribute_name = 'OS_MONTHS') AS m, maxIf(attribute_value, attribute_name = 'OS_STATUS') AS s FROM clinical_data_derived WHERE (cancer_study_identifier = 'pancan_pcawg_2020') AND (attribute_name IN ('OS_MONTHS', 'OS_STATUS')) GROUP BY patient_unique_id) SELECT if((patient_unique_id IN (SELECT patient_unique_id FROM t)), 'KRAS+TP53', 'KRAS only') AS g, count() AS total_kras_pts, countIf((m IS NOT NULL) AND (s != '')) AS n_os, countIf(startsWith(s, '1')) AS events, countIf(startsWith(s, '0')) AS censored, min(m) AS mn, max(m) AS mx FROM (SELECT patient_unique_id FROM k) LEFT JOIN os USING (patient_unique_id) GROUP BY g (set joined_subquery_requires_alias = 0 to disable restriction). (ALIAS_REQUIRED) (version 26.4.1.2509 (official build))\n"} ◀ result {"success":true,"message":"Found 1 study. Use the metadata to call the appropriate navigation tool(s).","data":{"totalCount":1,"studiesWithMetadata":[{"studyId":"pancan_pcawg_2020","name":"Pan-cancer analysis of whole genomes (ICGC/TCGA, Nature 2020)","sampleCount":2922,"studyViewUrl":"https://www.cbioportal.org/study?id=pancan_pcawg_2020","metadata":{"clinicalAttributeIds":["AGE","ALCOHOL","ALCOHOL_HISTORY_INTENSITY","ANCESTRY_PRIMARY","CANCER_TYPE","CANCER_TYPE_DETAILED","CELLULARITY","FIRST THERAPY_RESPONSE","FIRST_THERAPY","GRADE","HISTOLOGY","HISTOLOGY_ABBREVIATION","HISTOLOGY_TIER1","HISTOLOGY_TIER2","HISTOLOGY_TIER3","HISTOLOGY_TIER4","ICD_10","ICGC_SAMPLE_ID","MUTATION_COUNT","ONCOTREE_CODE","ORGAN_SYSTEM","OS_MONTHS","OS_STATUS","PLOIDY","PROJECT_CODE","PURITY","PURITY_CONFUGURATION","SAMPLE_CLASS","SAMPLE_COUNT","SAMPLE_TYPE","SEQUENCING_TYPE","SEX","STAGE","TBL_SCORE","TMB_NONSYNONYMOUS","TOBACCO_SMOKING_HISTORY_INDICATOR","TOBACCO_SMOKING_INTENSITY","TUMOR_SAMPLE_HISTOLOGY_CODE","WGD"],"molecularProfileIds":["pancan_pcawg_2020_cna","pancan_pcawg_2020_mirna","pancan_pcawg_2020_mirna_median_Zscores","pancan_pcawg_2020_mrna_seq_fpkm_capture","pancan_pcawg_2020_mrna_seq_fpkm_capture_all_sample_Zscores","pancan_pcawg_2020_mutations"],"genericAssayProfiles":["pancan_pcawg_2020_mutational_signatures_contribution_DBS","pancan_pcawg_2020_mutational_signatures_contribution_ID","pancan_pcawg_2020_mutational_signatures_contribution_SBS","pancan_pcawg_2020_mutational_signatures_counts_DBS","pancan_pcawg_2020_mutational_signatures_counts_ID","pancan_pcawg_2020_mutational_signatures_counts_SBS"],"heatmapProfileIds":["pancan_pcawg_2020_mirna_median_Zscores","pancan_pcawg_2020_mrna_seq_fpkm_capture_all_sample_Zscores"],"availableComparisonTabs":["overlap","clinical","survival","alterations","mutations","mrna","generic_assay_mutational_signature"]}}]}} ▶ clickhouse_run_select_query { "query": "WITH k AS (SELECT DISTINCT patient_unique_id FROM genomic_event_derived WHERE cancer_study_identifier='pancan_pcawg_2020' AND hugo_gene_symbol='KRAS' AND variant_type='mutation' AND mutation_status!='UNCALLED'), t AS (SELECT DISTINCT patient_unique_id FROM genomic_event_derived WHERE cancer_study_identifier='pancan_pcawg_2020' AND hugo_gene_symbol='TP53' AND variant_type='mutation' AND mutation_status!='UNCALLED'), os AS (SELECT patient_unique_id, maxIf(toFloat64OrNull(attribute_value), attribute_name='OS_MONTHS') AS m, maxIf(attribute_value, attribute_name='OS_STATUS') AS s FROM clinical_data_derived WHERE cancer_study_identifier='pancan_pcawg_2020' AND attribute_name IN ('OS_MONTHS','OS_STATUS') GROUP BY patient_unique_id) SELECT if(k.patient_unique_id IN (SELECT patient_unique_id FROM t),'KRAS+TP53','KRAS only') g, count() total_pts, countIf(os.m IS NOT NULL AND os.s!='') n_os, countIf(startsWith(os.s,'1')) events, countIf(startsWith(os.s,'0')) censored, min(os.m) mn, max(os.m) mx FROM k LEFT JOIN os ON k.patient_unique_id=os.patient_unique_id GROUP BY g" } ◀ result {"rows":[{"g":"KRAS only","total_pts":85,"n_os":3,"events":41,"censored":44,"mn":0,"mx":29.60526316},{"g":"KRAS+TP53","total_pts":187,"n_os":1,"events":122,"censored":65,"mn":33.51973684,"mx":33.51973684}]} ▶ navigate_to_group_comparison { "studyIds": [ "pancan_pcawg_2020" ], "tab": "survival", "groups": [ { "name": "KRAS + TP53 mutated", "studyViewFilter": { "mutationDataFilters": [ { "categorization": "MUTATED", "hugoGeneSymbol": "KRAS", "profileType": "mutations", "values": [ [ { "value": "MUTATED" } ] ] }, { "categorization": "MUTATED", "hugoGeneSymbol": "TP53", "profileType": "mutations", "values": [ [ { "value": "MUTATED" } ] ] } ] } }, { "name": "KRAS mutated only (TP53 not mutated)", "studyViewFilter": { "mutationDataFilters": [ { "categorization": "MUTATED", "hugoGeneSymbol": "KRAS", "profileType": "mutations", "values": [ [ { "value": "MUTATED" } ] ] }, { "categorization": "MUTATED", "hugoGeneSymbol": "TP53", "profileType": "mutations", "values": [ [ { "value": "NOT_MUTATED" } ] ] } ] } } ] } ◀ result {"success":true,"message":"Navigating to https://www.cbioportal.org/comparison/survival?comparisonId=6abb41efe497aad53593ca4d","url":"https://www.cbioportal.org/comparison/survival?comparisonId=6abb41efe497aad53593ca4d","data":{"description":"Group comparison (2 custom groups)","studies":["pancan_pcawg_2020"],"totalGroups":2,"groups":[{"name":"KRAS + TP53 mutated","sampleCount":187},{"name":"KRAS mutated only (TP53 not mutated)","sampleCount":86}],"studyViewUrl":"https://www.cbioportal.org/study?id=pancan_pcawg_2020","groupUrls":[{"groupName":"KRAS + TP53 mutated","url":"https://www.cbioportal.org/study?id=pancan_pcawg_2020#filterJson=%7B%22mutationDataFilters%22%3A%5B%7B%22categorization%22%3A%22MUTATED%22%2C%22hugoGeneSymbol%22%3A%22KRAS%22%2C%22profileType%22%3A%22mutations%22%2C%22values%22%3A%5B%5B%7B%22value%22%3A%22MUTATED%22%7D%5D%5D%7D%2C%7B%22categorization%22%3A%22MUTATED%22%2C%22hugoGeneSymbol%22%3A%22TP53%22%2C%22profileType%22%3A%22mutations%22%2C%22values%22%3A%5B%5B%7B%22value%22%3A%22MUTATED%22%7D%5D%5D%7D%5D%7D"},{"groupName":"KRAS mutated only (TP53 not mutated)","url":"https://www.cbioportal.org/study?id=pancan_pcawg_2020#filterJson=%7B%22mutationDataFilters%22%3A%5B%7B%22categorization%22%3A%22MUTATED%22%2C%22hugoGeneSymbol%22%3A%22KRAS%22%2C%22profileType%22%3A%22mutations%22%2C%22values%22%3A%5B%5B%7B%22value%22%3A%22MUTATED%22%7D%5D%5D%7D%2C%7B%22categorization%22%3A%22MUTATED%22%2C%22hugoGeneSymbol%22%3A%22TP53%22%2C%22profileType%22%3A%22mutations%22%2C%22values%22%3A%5B%5B%7B%22value%22%3A%22NOT_MUTATED%22%7D%5D%5D%7D%5D%7D"}],"pageDescription":"Kaplan-Meier survival analysis comparing KRAS + TP53 mutated (187) vs KRAS mutated only (TP53 not mutated) (86)."}} ═ answer (success) I can't give you a survival difference or a p-value for this comparison. Both need a Kaplan-Meier analysis and a log-rank test, and my database can't run either. I haven't estimated them, and I won't guess. Below are the group sizes and event counts, plus a link to the test in cBioPortal. **Study:** Pan-cancer analysis of whole genomes (ICGC/TCGA, Nature 2020), `pancan_pcawg_2020`. I counted patients who carry a somatic KRAS mutation and split them by TP53 status. | Group | Patients | Deaths | Censored (alive at last follow-up) | |---|---|---|---| | KRAS and TP53 mutated | 187 | 122 | 65 | | KRAS mutated, TP53 not mutated | 85 | 41 | 44 | - **Group sizes:** The comparison link counts 86 samples in the KRAS-only group, against 85 patients in my count. That is probably one patient with two samples. - **Median survival:** Deaths are 65% of the KRAS and TP53 group and 48% of the KRAS-only group. Because more than half of the KRAS and TP53 group died, a Kaplan-Meier median should be reachable for it. It may not be reached for the KRAS-only group, but the curve will show that. A raw median of survival times would ignore the censored patients, so I haven't reported one. - **Cancer type:** This is a mixed pan-cancer cohort, and I didn't adjust for cancer type. Any difference could reflect differences in tumor-type mix between the groups. **To get the survival difference and significance:** - [Open the Survival comparison in cBioPortal](https://www.cbioportal.org/comparison/survival?comparisonId=6abb41efe497aad53593ca4d). It shows the Kaplan-Meier curves, median survival for each group and the log-rank p-value. - For a hazard ratio, run a Cox model in R (`survival::coxph`) or Python (`lifelines`). - [Study overview](https://www.cbioportal.org/study?id=pancan_pcawg_2020)