Summarize the subject metadata available for bam files¶

I'm a developer, and I have written a new mutation calling pipeline. I've tested it on my own small dataset, but now I'm looking for a larger set of bam files that I can run through it.

First, decide what column to search. I'm looking for columns that are part of the file table:

In [2]:

columns(table="file")

columns(table="file")

Out[2]:

ⓘtable	column	data_type	nullable	description
file	file_id	text	False	A unique identifier for this file minted by CDA. May change release-to-release. Contains no semantically reliable content with one exception: in the case of a DICOM series from IDC, this field will contain the crdc_series_uuid assigned by IDC to that DICOM series. Note that this crdc_series_uuid may change from one IDC release version to the next, according to IDC's data processing and identification rules.
file	access	text	True	Denotes controlled vs open access.
file	anatomic_site	text	False	One or more anatomic sites described by this file.
file	category	text	True	General category of file data. In the case of DICOM series from IDC, this will be decoded from the DICOM Modality code assigned to the series.
file	drs_uri	text	True	A resolvable DRS URI minted for this file and maintained by DCF; prefix is "drs://dg.4dfc:". In the case of a DICOM series from IDC, this will be a comma-separated list of all DICOM-instance-level DRS URIs belonging to that series.
file	file_description	text	True	Optional text describing this file.
file	file_name	text	True	File name or label (may optionally include local path prefix; this will not generally be a URI). In the case of DICOM series from IDC, this will be null.
file	file_type	text	True	File data type (like "CT image" or "miRNA expression quantification"). In the case of a DICOM series from IDC, this will be a decoded "SOPClassName" string corresponding to the "SOPClassUID" code assigned to DICOM instances belonging to the series.
file	format	text	True	File format (like JPEG, PDF or CSV).
file	size	bigint	True	File size in bytes. In the case of a DICOM series from IDC, the value in this field will be the sum of the sizes of all DICOM instances belonging to the series.
(1 more row not shown)

file_format is what I'm looking for. Now I want to see whether bam is a valid value in it:

In [3]:

column_values("format")

column_values("format")

Out[3]:

ⓘformat	value_count
3192	1116726
3244	371745
3240	328785
3242	253018
3174	231761
3243	206391
3173	177713
3210	168169
3196	107789
3183	86376
(62 more rows not shown)

The value in the database is BAM, so I'm going to use that. According to the column values, there's around 200 thousand files, but I'm interested in comparing variants for individuals, so I want a count of how many subjects have bam files:

In [4]:

get_subject_data( match_all=["format = bam"])

get_subject_data( match_all=["format = bam"])

Out[4]:

ⓘsubject_id	cause_of_death	ethnicity	race	species	year_of_birth	year_of_death	data_source	format
DCCPS.11837	<NA>	Non-Hispanic	White	<NA>	<NA>	<NA>	[GC]	[BAM]
TCGA.TCGA-CV-A45R	<NA>	Hispanic or Latino	White	human	<NA>	<NA>	[GC, GDC, IDC]	[BAM]
TCGA.TCGA-23-1123	<NA>	Non-Hispanic	White	human	1939	2000	[GDC, IDC, PDC]	[BAM]
TCGA.TCGA-HS-A5N8	<NA>	<NA>	<NA>	human	<NA>	<NA>	[GDC, IDC]	[BAM]
Kids First.PT_GE1FKN67	<NA>	Hispanic or Latino	White	human	<NA>	<NA>	[GC]	[BAM]
TARGET.TARGET-50-PAJMZI	<NA>	Non-Hispanic	Black or African American	human	<NA>	<NA>	[GDC]	[BAM]
HTAN.HTA3_80014	<NA>	Non-Hispanic	White	<NA>	<NA>	<NA>	[GC]	[BAM]
MMRF.MMRF_2789	<NA>	<NA>	<NA>	human	<NA>	<NA>	[GDC]	[BAM]
TCGA.TCGA-BF-AAP0	<NA>	Non-Hispanic	White	human	<NA>	<NA>	[GDC, IDC]	[BAM]
REBC.REBC-AF74	<NA>	<NA>	<NA>	human	<NA>	<NA>	[GDC]	[BAM]
(39,616 more rows not shown)

I can also get a summary of the subject (or any other table) information for these files so I can decide what to filter next:

In [5]:

summarize_subjects( match_all=["format = bam"])

summarize_subjects( match_all=["format = bam"])

╔═══════════════════════════════╗
║ number_of_matching_subjects   ║
╠═══════════════════════════════╣
║ 39626                         ║
╚═══════════════════════════════╝
╔════════════════════════════════════════════════╗
║ number_of_files_related_to_matching_subjects   ║
╠════════════════════════════════════════════════╣
║ 231761                                         ║
╚════════════════════════════════════════════════╝
╔════════════╦══════════════════════╗
║   subjects ║          data_source ║
╠════════════╬══════════════════════╣
║      13453 ║             GDC only ║
║       9831 ║            IDC + GDC ║
║       8455 ║              GC only ║
║       4633 ║       IDC + GC + GDC ║
║       1117 ║ IDC + GC + GDC + PDC ║
║        899 ║      IDC + GDC + PDC ║
║        516 ║            GDC + PDC ║
║        435 ║            ICDC only ║
║        117 ║             IDC + GC ║
║         80 ║           IDC + ICDC ║
║         45 ║             GC + GDC ║
║         45 ║       GC + GDC + PDC ║
╚════════════╩══════════════════════╝
╔════════════════╦═══════════╗
║   count_result ║   species ║
╠════════════════╬═══════════╣
║          34593 ║     human ║
║           4518 ║      <NA> ║
║            515 ║       dog ║
╚════════════════╩═══════════╝
╔════════════════╦════════════════════╗
║   count_result ║          ethnicity ║
╠════════════════╬════════════════════╣
║          23411 ║       Non-Hispanic ║
║          12943 ║               <NA> ║
║           3272 ║ Hispanic or Latino ║
╚════════════════╩════════════════════╝
╔════════════════╦══════════════════════════════════════════╗
║   count_result ║                                     race ║
╠════════════════╬══════════════════════════════════════════╣
║          24910 ║                                    White ║
║           9666 ║                                     <NA> ║
║           3174 ║                Black or African American ║
║           1656 ║                                    Asian ║
║            126 ║         American Indian or Alaska Native ║
║             93 ║ Native Hawaiian or Other Pacific Isla... ║
║              1 ║                       More than one race ║
╚════════════════╩══════════════════════════════════════════╝
╔════════════════╦══════════════════════════╗
║   count_result ║           cause_of_death ║
╠════════════════╬══════════════════════════╣
║          38127 ║                     <NA> ║
║           1241 ║     Cancer-Related Death ║
║            198 ║ Non-Cancer Related Death ║
║             24 ║                Infection ║
║             15 ║  Cardiovascular Disorder ║
║             14 ║    Surgical Complication ║
║              7 ║                 Toxicity ║
╚════════════════╩══════════════════════════╝
╔════════════════╦══════════╗
║   count_result ║   format ║
╠════════════════╬══════════╣
║         231761 ║      BAM ║
╚════════════════╩══════════╝
╔════════════════╦═════════════════╗
║                ║ year_of_birth   ║
╠════════════════╬═════════════════╣
║           mean ║ 1973            ║
║            min ║ 1908            ║
║ lower quartile ║ 1950            ║
║         median ║ 1965            ║
║ upper quartile ║ 2003            ║
║            max ║ 2022            ║
╚════════════════╩═════════════════╝
╔════════════════╦═════════════════╗
║                ║ year_of_death   ║
╠════════════════╬═════════════════╣
║           mean ║ 2015            ║
║            min ║ 1992            ║
║ lower quartile ║ 2013            ║
║         median ║ 2018            ║
║ upper quartile ║ 2019            ║
║            max ║ 2023            ║
╚════════════════╩═════════════════╝

For instance, I might look for all of the subjects who have both a tumor sample and a normal control associated with them, because I'm interested in de novo cancer mutations. This means that I want to search the same column twice, once for subjects who have normal tissue and once for subjects that have tumor tissue, and only keep the intersection of those results:

In [6]:

normal = get_subject_data( match_all=["tumor_vs_normal = normal", "format = bam"])
tumor = get_subject_data( match_all=["tumor_vs_normal = tumor", "format = bam"])

both = intersect_subject_results(normal, tumor)
both

normal = get_subject_data( match_all=["tumor_vs_normal = normal", "format = bam"]) tumor = get_subject_data( match_all=["tumor_vs_normal = tumor", "format = bam"]) both = intersect_subject_results(normal, tumor) both

Out[6]:

ⓘsubject_id	cause_of_death	ethnicity	race	species	year_of_birth	year_of_death	data_source	format	tumor_vs_normal
ALCHEMIST.ALCH-ABBM	<NA>	<NA>	<NA>	human	<NA>	<NA>	[GDC]	[BAM]	[normal, tumor]
ALCHEMIST.ALCH-ABBR	<NA>	<NA>	<NA>	human	<NA>	<NA>	[GDC]	[BAM]	[normal, tumor]
ALCHEMIST.ALCH-ABC2	<NA>	<NA>	<NA>	human	<NA>	<NA>	[GDC]	[BAM]	[normal, tumor]
ALCHEMIST.ALCH-ABD2	<NA>	<NA>	<NA>	human	<NA>	<NA>	[GDC]	[BAM]	[normal, tumor]
ALCHEMIST.ALCH-ABD5	<NA>	<NA>	<NA>	human	<NA>	<NA>	[GDC]	[BAM]	[normal, tumor]
ALCHEMIST.ALCH-ABE2	<NA>	<NA>	<NA>	human	<NA>	<NA>	[GDC]	[BAM]	[normal, tumor]
ALCHEMIST.ALCH-ABE5	<NA>	<NA>	<NA>	human	<NA>	<NA>	[GDC]	[BAM]	[normal, tumor]
ALCHEMIST.ALCH-ABEF	<NA>	<NA>	<NA>	human	<NA>	<NA>	[GDC]	[BAM]	[normal, tumor]
ALCHEMIST.ALCH-ABEI	<NA>	<NA>	<NA>	human	<NA>	<NA>	[GDC]	[BAM]	[normal, tumor]
ALCHEMIST.ALCH-ABEP	<NA>	<NA>	<NA>	human	<NA>	<NA>	[GDC]	[BAM]	[normal, tumor]
(27,317 more rows not shown)

It looks like there are about 22 thousand bams that contain comparisons between normal and tumor tissue. Now I could save that output to a file to browse through

In [7]:

both.to_csv('bams_tumor_normal.csv')

both.to_csv('bams_tumor_normal.csv')

Or I could keep filtering by other fields. For instance, I might want just one anatomicial location:

In [8]:

normal_lung = get_subject_data( match_all=["tumor_vs_normal = normal", "format = bam", "anatomic_site = lung"])
tumor_lung = get_subject_data( match_all=["tumor_vs_normal = tumor", "format = bam", "anatomic_site = lung"])
both_lung = intersect_subject_results(normal_lung, tumor_lung)
both_lung

normal_lung = get_subject_data( match_all=["tumor_vs_normal = normal", "format = bam", "anatomic_site = lung"]) tumor_lung = get_subject_data( match_all=["tumor_vs_normal = tumor", "format = bam", "anatomic_site = lung"]) both_lung = intersect_subject_results(normal_lung, tumor_lung) both_lung

Out[8]:

ⓘsubject_id	cause_of_death	ethnicity	race	species	year_of_birth	year_of_death	data_source	format	tumor_vs_normal	anatomic_site
CMCP.OSA01-E8	<NA>	<NA>	<NA>	dog	<NA>	<NA>	[ICDC]	[BAM]	[normal, tumor]	[lung]
CPTAC.C3L-00001	<NA>	Non-Hispanic	White	human	1954	<NA>	[GC, GDC, IDC, PDC]	[BAM]	[normal, tumor]	[lung]
CPTAC.C3L-00009	<NA>	Non-Hispanic	White	human	1939	2017	[GC, GDC, IDC, PDC]	[BAM]	[normal, tumor]	[lung]
CPTAC.C3L-00080	<NA>	Non-Hispanic	White	human	1958	<NA>	[GC, GDC, IDC, PDC]	[BAM]	[normal, tumor]	[lung]
CPTAC.C3L-00081	Cancer-Related Death	Non-Hispanic	White	human	1954	2017	[GC, GDC, IDC, PDC]	[BAM]	[normal, tumor]	[lung]
CPTAC.C3L-00083	<NA>	Non-Hispanic	White	human	1944	<NA>	[GC, GDC, IDC, PDC]	[BAM]	[normal, tumor]	[lung]
CPTAC.C3L-00093	Cancer-Related Death	Non-Hispanic	White	human	1949	2021	[GC, GDC, IDC, PDC]	[BAM]	[normal, tumor]	[lung]
CPTAC.C3L-00094	Cancer-Related Death	Non-Hispanic	White	human	1946	2018	[GC, GDC, IDC, PDC]	[BAM]	[normal, tumor]	[lung]
CPTAC.C3L-00095	<NA>	Non-Hispanic	White	human	1953	<NA>	[GC, GDC, IDC, PDC]	[BAM]	[normal, tumor]	[lung]
CPTAC.C3L-00140	<NA>	Non-Hispanic	White	human	1945	<NA>	[GC, GDC, IDC, PDC]	[BAM]	[normal, tumor]	[lung]
(305 more rows not shown)