<jupyter_start><jupyter_text>Sample Notebook for exploring gnomAD in BigQueryThis notebook contains sample queries to explore the gnomAD dataset which is hosted through the Google Cloud Public Datasets Program.  Setup and AuthenticationIf you just want to look at sample results, you can scroll down to see the output of the existing queries without having to run anything. If you would like to re-run the queries or make changes, you will need to authenticate as your user and set the Google Cloud project in which to run the analysis.<jupyter_code># Import libraries
import os

# Imports for using and authenticating BigQuery
from google.colab import auth
%load_ext google.cloud.bigquery<jupyter_output><empty_output><jupyter_text>User AuthenticationBefore running any queries using BigQuery, you need to first authenticate yourself by running the following cell. If you are running it for the first time, it will ask you to follow a link to log in using your Google identity account, and accept the data access requests to your profile. Once this is done, it will generate a string of verification code, which you should paste back to the cell below and press enter. This should be a Google account which you can login to and which has access to run BigQuery jobs in the Google Cloud project specified in the next step.<jupyter_code>auth.authenticate_user()<jupyter_output><empty_output><jupyter_text>Set Google Cloud ProjectTo run queries in BigQuery, you need to specify the Google Cloud project that will be used. The first 1 TB of query data processed per month is free. For more details, see the [BigQuery Pricing](https://cloud.google.com/bigquery/pricing) page.To find your Project ID, go to the [Project Settings page](https://console.cloud.google.com/iam-admin/settings) in the Google [Cloud Console](https://console.cloud.google.com/). You can select the project you want using the drop-down menu at the top of the page.<jupyter_code># Replace project_id with your Google Cloud Project ID. 
os.environ["GOOGLE_CLOUD_PROJECT"]='project_id'<jupyter_output><empty_output><jupyter_text>gnomAD Queries Query1Find 10,000 SNV on chr17 that are more common in women than men, min sample size set to 30.<jupyter_code>%%bigquery result1
SELECT DISTINCT 
       start_position AS str_pos,
       reference_bases AS ref,
       alternate_bases.alt AS alt,
       alternate_bases.allele_type AS type,
       vep.SYMBOL AS gene,
       vep.feature_type AS f_type,
       alternate_bases.AC AS AC,
       alternate_bases.AC_female AS AC_f,
       alternate_bases.AC_male AS AC_m,
       ROUND(alternate_bases.AC_female / alternate_bases.AC, 3) AS f_ratio
FROM `bigquery-public-data.gnomAD.v2_1_1_exomes__chr17` AS main_table,
     main_table.alternate_bases AS alternate_bases,
     alternate_bases.vep AS vep
WHERE alternate_bases.AC > 30 AND vep.SYMBOL IS NOT NULL
ORDER BY f_ratio DESC
LIMIT 10000

result1.head()<jupyter_output><empty_output><jupyter_text>We can condensed the result and only list gene symbols and the number of variants found in the query1.<jupyter_code>result1.groupby('gene').count()[['str_pos']].sort_values(by=['str_pos'], ascending=False).head()<jupyter_output><empty_output><jupyter_text>Query2Find top 1,000 SNV on chr17 that show the most significant differences between male samples of African-American ancestry versus Finnish ancestry<jupyter_code>%%bigquery result2
SELECT DISTINCT 
       start_position AS str_pos,
       reference_bases AS ref,
       alternate_bases.alt AS alt,
       alternate_bases.allele_type AS type,
       vep.SYMBOL AS gene,
       vep.feature_type AS f_type,
       alternate_bases.AC_male AS AC_m,
       alternate_bases.AC_fin_male AS AC_fin_m,
       alternate_bases.AC_afr_male AS AC_afr_m,
       ROUND(ABS(alternate_bases.AC_fin_male - alternate_bases.AC_afr_male) / alternate_bases.AC_male, 3) AS fin_afr_diff
FROM `bigquery-public-data.gnomAD.v2_1_1_exomes__chr17` AS main_table,
     main_table.alternate_bases AS alternate_bases,
     alternate_bases.vep AS vep
WHERE vep.SYMBOL IS NOT NULL AND
      alternate_bases.AC_male > 20 AND alternate_bases.AC_fin_male > 0 AND alternate_bases.AC_afr_male > 0
order by fin_afr_diff DESC
LIMIT 1000
result2.head()<jupyter_output><empty_output><jupyter_text>Query3Find top 1000 genes with the highest number of SNV on chr17<jupyter_code>%%bigquery result3
SELECT gene, count(1) AS num_snv
FROM
(
SELECT DISTINCT 
       start_position AS str_pos,
       alternate_bases.alt AS alt,
       vep.SYMBOL AS gene,
FROM `bigquery-public-data.gnomAD.v2_1_1_exomes__chr17` AS main_table,
     main_table.alternate_bases AS alternate_bases,
     alternate_bases.vep AS vep
WHERE vep.SYMBOL IS NOT NULL AND alternate_bases.allele_type = 'snv'
)
GROUP BY 1
ORDER BY 2 DESC
LIMIT 1000
result3.head()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.05944490472245236, 'fraction_numerical': 0.014913007456503728, 'mean_word_length': 4.728351126927639, 'pattern_counts': {'":': 0, '<': 29, '<?xml version=': 0, '>': 33, 'https://': 3, 'lorem ipsum': 0, 'www.': 0, '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': '7', 'dir': 'jupyter-structured-clean-dedup', 'id': '48625', 'license': 'Apache-2.0', 'n_tokens_mistral': 1551, 'n_tokens_neox': 1494, 'n_words': 528, 'path': 'gnomAD/gnomAD.ipynb', 'repo_name': 'moschetti/variant-processing-in-BigQuery', 'size': '4828'}