<jupyter_start><jupyter_text>Accessing Gecco data via public SRA subject and sample dataThis notebook explores using GA4GH DRS to access data stored in the cloud for a controlled access dbGaP project - [phs001554](https://www.ncbi.nlm.nih.gov/projects/gap/cgi-bin/study.cgi?study_id=phs001554.v1.p1) "Detection of Colorectal Cancer Susceptibility Loci Using Genome-Wide Sequencing".A query using the  data on public subject and sample attributes held in SRA BigQuery. Gen3 DRS ids are accessed in BigQuery uploaded from a Gen3 manifest file. >>The sequence files used in this notebook are  under controlled access. You will not be able to access those files unless you have been granted access through dbGaP to the phs001554 study. If you have that access you will also have access to the non-scrambled subject and sample data. If that is the case, we would be pleased to hear of your interest, and the possibility of collaborating to ensure the GA4GH tools described here enable your studies. Please see [form to register interest](https://docs.google.com/forms/d/e/1FAIpQLSfmmc3VKd6ANdzaVMyelT3c9gIWuoS4ZwT0vsqD-o2ZRxJf7A/viewform)<jupyter_code>from fasp.search import BigQuerySearchClient
from fasp.loc import crdcDRSClient
from fasp.workflow import sbcgcWESClient
from fasp.runner import FASPRunner

faspRunner = FASPRunner(program='GECCO_Gen3_on_SB')
runNote = 'GECCO files retrieved from Gen3 DRS and computed on SB'

searchClient = BigQuerySearchClient()
query = '''select sra.acc,  drs_id
from `dnastack-main.cshcodeathon.gecco_gen3_drs_index` i
join `nih-sra-datastore.sra.metadata` sra on sra.acc = i.acc
CROSS JOIN UNNEST(sra.attributes) AS atts
where atts.k = 'subject_is_affected_sam' and atts.v = 'Yes'
and sra.consent = 'DS-CC-NPU-GSO'
and file_type = 'cram' limit 3'''
df = searchClient.runQuery(query, returnType='dataframe')
df
drsClient = crdcDRSClient('/Users/forei/.keys/crdc_credentials.json', 's3')

settings = faspRunner.settings
wesClient = sbcgcWESClient(settings['SevenBridgesProject'])

faspRunner.configure(searchClient, drsClient, wesClient)

run_ids = faspRunner.runQuery(query, runNote)
import pandas as pd
runs_df = pd.DataFrame(run_ids)
runs_df
merged_df = pd.merge(
    df,
    runs_df,
    how="inner",
    left_on='acc',
    right_on='subject'
)
merged_df.drop('subject', axis=1, inplace=True)
merged_df.drop('drs_id', axis=1, inplace=True)
merged_df.to_csv('~/mywork.csv')
merged_df
faspRunner.getFASPicon()<jupyter_output><empty_output><jupyter_text>Update the dataframe with the status of the runs<jupyter_code>for index, row in merged_df.iterrows():
    status = wesClient.getTaskStatus(row['run_id'])
    merged_df.at[index, "status"] = status
merged_df<jupyter_output><empty_output><jupyter_text>Once the runs have completed the results can be retrieved.<jupyter_code>statsRequired = ['raw total sequences',
                 'sequences',
                 'reads mapped',
                 'insert size average',
                 'insert size standard deviation']
statsList = []
for index, row in merged_df.iterrows():
    stats =  wesClient.getSAMToolsResults(row['run_id'], statsRequired)
    stats['acc'] = row['acc']
    statsList.append(stats)

stats_df =  pd.DataFrame(statsList)
stats_df
final_df = pd.merge(
    merged_df,
    stats_df,
    how="inner",
    left_on='acc',
    right_on='acc'
)
final_df.drop('status', axis=1, inplace=True)
final_df.drop('run_id', axis=1, inplace=True)

final_df<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09643062751871043, 'fraction_numerical': 0.012089810017271158, 'mean_word_length': 5.075174825174825, 'pattern_counts': {'":': 0, '<': 13, '<?xml version=': 0, '>': 15, 'https://': 2, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '3', 'dir': 'jupyter-structured-clean-dedup', 'id': '117259', 'license': 'Apache-2.0', 'n_tokens_mistral': 1213, 'n_tokens_neox': 1164, 'n_words': 320, 'path': 'notebooks/GECCO_Gen3_Public_SSD.ipynb', 'repo_name': 'jb-adams/fasp-scripts', 'size': '3474'}