"
]
},
"metadata": {},
"output_type": "display_data"
},
{
"data": {
"text/html": [
"2022.10.24\n",
"\n"
],
"text/plain": [
"\u001b[1;36m2022.10\u001b[0m.\u001b[1;36m24\u001b[0m\n"
]
},
"metadata": {},
"output_type": "display_data"
}
],
"source": [
"from cdapython import Q, columns, unique_terms, query\n",
"import numpy as np\n",
"import pandas as pd\n",
"from itables import init_notebook_mode, show\n",
"init_notebook_mode(all_interactive=True)\n",
"import itables.options as opt\n",
"opt.maxBytes=0\n",
"opt.scrollX=\"200px\"\n",
"opt.scrollCollapse=True\n",
"opt.paging=True\n",
"opt.maxColumns=0\n",
"print(Q.get_version())"
]
},
{
"cell_type": "markdown",
"id": "dc806cf9",
"metadata": {},
"source": [
"The CDA indexes tens of thousands of subjects, researchsubjects, speciments, and their diagnosis data. CDA also indexes more than 45 million files. To keep search results from being overwhelming, CDA limits search results to the first 100 records by default:"
]
},
{
"cell_type": "code",
"execution_count": 3,
"id": "f20a48d1",
"metadata": {
"scrolled": true
},
"outputs": [
{
"data": {
"text/html": [
"Getting results from database\n",
"\n",
"\n"
],
"text/plain": [
"Getting results from database\n",
"\n"
]
},
"metadata": {},
"output_type": "display_data"
},
{
"data": {
"text/html": [
"\n",
" Total execution time: 0\n",
" min 3.656 sec 3656 ms\n",
" \n",
"\n"
],
"text/plain": [
"\n",
" Total execution time: \u001b[1;36m0\u001b[0m\n",
" min \u001b[1;36m3.656\u001b[0m sec \u001b[1;36m3656\u001b[0m ms\n",
" \n"
]
},
"metadata": {},
"output_type": "display_data"
},
{
"data": {
"text/plain": [
"\n",
" \n",
" Offset: 0\n",
" Count: 100\n",
" Total Row Count: 3015\n",
" More pages: True\n",
" "
]
},
"execution_count": 3,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"myquery = Q('primary_diagnosis_site = \"brain\"')\n",
"brainresults = myquery.subject.run()\n",
"brainresults"
]
},
{
"cell_type": "markdown",
"id": "72582e92",
"metadata": {},
"source": [
"\n",
"---\n",
"\n",
"- **Offset:** This is how many rows of information we've told the query to skip in the data, here we didn't tell it to skip anything, so the offset is zero\n",
"- **Count:** This is how many rows the current page of our results table has. To keep searches fast, we default to pages with 100 rows.\n",
"- **Total Row Count:** This is how many rows are in the full results table\n",
"- **More pages:** This is always a True or False. False means that our current page has all the available results. True means that we will see only the first 100 results in this table, and will need to page through for more.\n",
"\n",
"---"
]
},
{
"cell_type": "markdown",
"id": "19ae2e2a",
"metadata": {},
"source": [
"This preview behaviour is great for search, but not when you are ready to download the information. The pagination feature is how you retrieve all the results for your final query and not just the first 100. \n",
"\n",
"You can have your full results output to a dataframe or a list.\n",
"\n",
"## Results to a dataframe\n",
"\n",
"The easiest way to get results to a dataframe is to use the `auto_paginator()`, which directly turns your results into a full dataframe:\n"
]
},
{
"cell_type": "code",
"execution_count": 4,
"id": "f8504d5f",
"metadata": {},
"outputs": [
{
"data": {
"application/vnd.jupyter.widget-view+json": {
"model_id": "ae6180858e9e4e798994f2fc991101f3",
"version_major": 2,
"version_minor": 0
},
"text/plain": [
"Output()"
]
},
"metadata": {},
"output_type": "display_data"
},
{
"data": {
"text/html": [
"\n"
],
"text/plain": []
},
"metadata": {},
"output_type": "display_data"
},
{
"data": {
"text/html": [
"\n",
"\n"
],
"text/plain": [
"\n"
]
},
"metadata": {},
"output_type": "display_data"
},
{
"data": {
"text/html": [
"\n",
"\n"
],
"text/plain": [
"\n"
]
},
"metadata": {},
"output_type": "display_data"
}
],
"source": [
"paginated_brain_results = brainresults.auto_paginator(to_df=True)"
]
},
{
"cell_type": "code",
"execution_count": 5,
"id": "d9b79f36",
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
" \n",
" | \n",
" subject_id | \n",
" subject_identifier | \n",
" species | \n",
" sex | \n",
" race | \n",
" ethnicity | \n",
" days_to_birth | \n",
" subject_associated_project | \n",
" vital_status | \n",
" days_to_death | \n",
" cause_of_death | \n",
"
\n",
" | Loading... (need help?) |
\n",
"\n",
"\n"
],
"text/plain": [
" subject_id subject_identifier \\\n",
"0 TCGA-14-1454 [{'system': 'GDC', 'value': 'TCGA-14-1454'}, {... \n",
"1 TCGA-16-1060 [{'system': 'GDC', 'value': 'TCGA-16-1060'}, {... \n",
"2 TCGA-DU-5870 [{'system': 'GDC', 'value': 'TCGA-DU-5870'}, {... \n",
"3 TCGA-DU-6397 [{'system': 'GDC', 'value': 'TCGA-DU-6397'}, {... \n",
"4 TCGA-DU-7009 [{'system': 'GDC', 'value': 'TCGA-DU-7009'}, {... \n",
".. ... ... \n",
"95 C881049 [{'system': 'PDC', 'value': 'C881049'}] \n",
"96 GENIE-GRCC-159a16b7 [{'system': 'GDC', 'value': 'GENIE-GRCC-159a16... \n",
"97 GENIE-MSK-P-0005515 [{'system': 'GDC', 'value': 'GENIE-MSK-P-00055... \n",
"98 QIN-BRAIN-DSC-01-33 [{'system': 'IDC', 'value': 'QIN-BRAIN-DSC-01-... \n",
"99 QIN-GBM-TR-13 [{'system': 'IDC', 'value': 'QIN-GBM-TR-13'}] \n",
"\n",
" species sex race ethnicity \\\n",
"0 homo sapiens female white not reported \n",
"1 homo sapiens female white not hispanic or latino \n",
"2 homo sapiens female white not hispanic or latino \n",
"3 homo sapiens male white not hispanic or latino \n",
"4 homo sapiens female white not hispanic or latino \n",
".. ... ... ... ... \n",
"95 homo sapiens male white not hispanic or latino \n",
"96 homo sapiens female not allowed to collect not allowed to collect \n",
"97 homo sapiens male white not hispanic or latino \n",
"98 homo sapiens None None None \n",
"99 homo sapiens None None None \n",
"\n",
" days_to_birth subject_associated_project \\\n",
"0 NaN [TCGA-GBM, tcga_gbm] \n",
"1 NaN [TCGA-GBM, tcga_gbm] \n",
"2 NaN [tcga_lgg, TCGA-LGG] \n",
"3 NaN [tcga_lgg, TCGA-LGG] \n",
"4 NaN [tcga_lgg, TCGA-LGG] \n",
".. ... ... \n",
"95 NaN [Proteogenomic Analysis of Pediatric Brain Can... \n",
"96 NaN [GENIE-GRCC] \n",
"97 NaN [GENIE-MSK] \n",
"98 NaN [qin_brain_dsc_mri] \n",
"99 NaN [qin_gbm_treatment_response] \n",
"\n",
" vital_status days_to_death cause_of_death \n",
"0 Dead None None \n",
"1 Dead None None \n",
"2 Alive None None \n",
"3 Dead None None \n",
"4 Dead None None \n",
".. ... ... ... \n",
"95 Alive NaN Not Reported \n",
"96 Not Reported NaN None \n",
"97 Not Reported NaN None \n",
"98 None NaN None \n",
"99 None NaN None \n",
"\n",
"[3015 rows x 11 columns]"
]
},
"execution_count": 5,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"paginated_brain_results # view the dataframe"
]
},
{
"cell_type": "markdown",
"id": "b94a8169",
"metadata": {},
"source": [
"If you'd like more control over your output, you can use the `paginator`. It will also get all the data, but requires you to write the looping code.\n",
"\n",
"In the simplest case, you create an empty dataframe for the data to land in, then use the paginator in a loop to get all the results:"
]
},
{
"cell_type": "code",
"execution_count": 6,
"id": "04600790",
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"Getting results from database\n",
"\n",
"\n"
],
"text/plain": [
"Getting results from database\n",
"\n"
]
},
"metadata": {},
"output_type": "display_data"
},
{
"data": {
"text/html": [
"\n",
" Total execution time: 0\n",
" min 3.652 sec 3652 ms\n",
" \n",
"\n"
],
"text/plain": [
"\n",
" Total execution time: \u001b[1;36m0\u001b[0m\n",
" min \u001b[1;36m3.652\u001b[0m sec \u001b[1;36m3652\u001b[0m ms\n",
" \n"
]
},
"metadata": {},
"output_type": "display_data"
},
{
"data": {
"application/vnd.jupyter.widget-view+json": {
"model_id": "b7031288a9724cbdaa88172a235af31d",
"version_major": 2,
"version_minor": 0
},
"text/plain": [
"Output()"
]
},
"metadata": {},
"output_type": "display_data"
},
{
"data": {
"text/html": [
"\n"
],
"text/plain": []
},
"metadata": {},
"output_type": "display_data"
},
{
"data": {
"text/html": [
"\n",
"\n"
],
"text/plain": [
"\n"
]
},
"metadata": {},
"output_type": "display_data"
},
{
"data": {
"text/html": [
"\n",
"\n"
],
"text/plain": [
"\n"
]
},
"metadata": {},
"output_type": "display_data"
}
],
"source": [
"mydf = pd.DataFrame()\n",
"for i in myquery.subject.run().paginator(to_df=True):\n",
" mydf = pd.concat([mydf, i])"
]
},
{
"cell_type": "code",
"execution_count": 7,
"id": "0f26fb01",
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
" \n",
" | \n",
" subject_id | \n",
" subject_identifier | \n",
" species | \n",
" sex | \n",
" race | \n",
" ethnicity | \n",
" days_to_birth | \n",
" subject_associated_project | \n",
" vital_status | \n",
" days_to_death | \n",
" cause_of_death | \n",
"
\n",
" | Loading... (need help?) |
\n",
"\n",
"\n"
],
"text/plain": [
" subject_id subject_identifier \\\n",
"0 C177366 [{'system': 'PDC', 'value': 'C177366'}] \n",
"1 C3N-03003 [{'system': 'IDC', 'value': 'C3N-03003'}] \n",
"2 C48216 [{'system': 'PDC', 'value': 'C48216'}] \n",
"3 C794457 [{'system': 'PDC', 'value': 'C794457'}] \n",
"4 C82041 [{'system': 'PDC', 'value': 'C82041'}] \n",
".. ... ... \n",
"10 UPENN-GBM-00230 [{'system': 'IDC', 'value': 'UPENN-GBM-00230'}] \n",
"11 UPENN-GBM-00307 [{'system': 'IDC', 'value': 'UPENN-GBM-00307'}] \n",
"12 UPENN-GBM-00419 [{'system': 'IDC', 'value': 'UPENN-GBM-00419'}] \n",
"13 UPENN-GBM-00562 [{'system': 'IDC', 'value': 'UPENN-GBM-00562'}] \n",
"14 W16 [{'system': 'IDC', 'value': 'W16'}] \n",
"\n",
" species sex race \\\n",
"0 homo sapiens male american indian or alaska native \n",
"1 homo sapiens None None \n",
"2 homo sapiens female white \n",
"3 homo sapiens male white \n",
"4 homo sapiens male not reported \n",
".. ... ... ... \n",
"10 homo sapiens None None \n",
"11 homo sapiens None None \n",
"12 homo sapiens None None \n",
"13 homo sapiens None None \n",
"14 homo sapiens None None \n",
"\n",
" ethnicity days_to_birth \\\n",
"0 not hispanic or latino NaN \n",
"1 None NaN \n",
"2 not hispanic or latino NaN \n",
"3 not hispanic or latino NaN \n",
"4 not hispanic or latino NaN \n",
".. ... ... \n",
"10 None NaN \n",
"11 None NaN \n",
"12 None NaN \n",
"13 None NaN \n",
"14 None NaN \n",
"\n",
" subject_associated_project vital_status \\\n",
"0 [Proteogenomic Analysis of Pediatric Brain Can... Dead \n",
"1 [cptac_gbm] None \n",
"2 [Proteogenomic Analysis of Pediatric Brain Can... Alive \n",
"3 [Proteogenomic Analysis of Pediatric Brain Can... Alive \n",
"4 [Proteogenomic Analysis of Pediatric Brain Can... Alive \n",
".. ... ... \n",
"10 [upenn_gbm] None \n",
"11 [upenn_gbm] None \n",
"12 [upenn_gbm] None \n",
"13 [upenn_gbm] None \n",
"14 [ivygap] None \n",
"\n",
" days_to_death cause_of_death \n",
"0 NaN Not Reported \n",
"1 NaN None \n",
"2 NaN Not Reported \n",
"3 NaN Not Reported \n",
"4 NaN Not Reported \n",
".. ... ... \n",
"10 None None \n",
"11 None None \n",
"12 None None \n",
"13 None None \n",
"14 None None \n",
"\n",
"[3015 rows x 11 columns]"
]
},
"execution_count": 7,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"mydf # view the dataframe"
]
},
{
"cell_type": "markdown",
"id": "a7cf4651",
"metadata": {},
"source": [
"## Results to a list\n",
"\n",
"If you want results in a list, you'll need to use the paginator. Saving to a list works similarly to the dataframe call. The differences are:\n",
"\n",
"- initiate a list not a DataFrame\n",
"- change `to_list=True`\n",
"- change the concat index to `extend()`"
]
},
{
"cell_type": "code",
"execution_count": 8,
"id": "0ee1d319",
"metadata": {
"scrolled": true
},
"outputs": [
{
"data": {
"text/html": [
"Getting results from database\n",
"\n",
"\n"
],
"text/plain": [
"Getting results from database\n",
"\n"
]
},
"metadata": {},
"output_type": "display_data"
},
{
"data": {
"text/html": [
"\n",
" Total execution time: 0\n",
" min 3.654 sec 3654 ms\n",
" \n",
"\n"
],
"text/plain": [
"\n",
" Total execution time: \u001b[1;36m0\u001b[0m\n",
" min \u001b[1;36m3.654\u001b[0m sec \u001b[1;36m3654\u001b[0m ms\n",
" \n"
]
},
"metadata": {},
"output_type": "display_data"
},
{
"data": {
"application/vnd.jupyter.widget-view+json": {
"model_id": "3417648591d94192a39567f530530107",
"version_major": 2,
"version_minor": 0
},
"text/plain": [
"Output()"
]
},
"metadata": {},
"output_type": "display_data"
},
{
"data": {
"text/html": [
"\n"
],
"text/plain": []
},
"metadata": {},
"output_type": "display_data"
},
{
"data": {
"text/html": [
"\n",
"\n"
],
"text/plain": [
"\n"
]
},
"metadata": {},
"output_type": "display_data"
},
{
"data": {
"text/html": [
"\n",
"\n"
],
"text/plain": [
"\n"
]
},
"metadata": {},
"output_type": "display_data"
}
],
"source": [
"mylist = []\n",
"for i in myquery.subject.run().paginator(to_list=True):\n",
" mylist.extend(i)"
]
},
{
"cell_type": "markdown",
"id": "13980da4",
"metadata": {},
"source": [
"This gives back the correct number of results:"
]
},
{
"cell_type": "code",
"execution_count": 9,
"id": "15d373af",
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"3015"
]
},
"execution_count": 9,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"len(mylist)"
]
},
{
"cell_type": "markdown",
"id": "1f65ca21",
"metadata": {},
"source": [
"And we can preview the first result to see that it has the same values:"
]
},
{
"cell_type": "code",
"execution_count": 10,
"id": "f2bb66b9",
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"[{'subject_id': 'C177366',\n",
" 'subject_identifier': [{'system': 'PDC', 'value': 'C177366'}],\n",
" 'species': 'homo sapiens',\n",
" 'sex': 'male',\n",
" 'race': 'american indian or alaska native',\n",
" 'ethnicity': 'not hispanic or latino',\n",
" 'days_to_birth': None,\n",
" 'subject_associated_project': ['Proteogenomic Analysis of Pediatric Brain Cancer Tumors Pilot Study'],\n",
" 'vital_status': 'Dead',\n",
" 'days_to_death': None,\n",
" 'cause_of_death': 'Not Reported'}]"
]
},
"execution_count": 10,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"mylist[0:1]"
]
}
],
"metadata": {
"kernelspec": {
"display_name": "Python 3 (ipykernel)",
"language": "python",
"name": "python3"
},
"language_info": {
"codemirror_mode": {
"name": "ipython",
"version": 3
},
"file_extension": ".py",
"mimetype": "text/x-python",
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython3",
"version": "3.10.6"
}
},
"nbformat": 4,
"nbformat_minor": 5
}