<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 Sample Notebook for exploring gnomAD in BigQuery
This notebook contains sample queries to explore the gnomAD dataset which is hosted through the Google Cloud Public Datasets Program.
Setup and Authentication
If 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.
Step1:  User Authentication
Before 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.
Step2:  Set Google Cloud Project
To run queries in BigQuery, you need to specify the Google Cloud project that will be used. The queries below report the number of bytes billed by each query. The first 1 TB of query data processed in a project per month is free. For more details, see the BigQuery Pricing page.
To find your Project ID, go to the Project Settings page in the Google Cloud Console. You can select the project you want using the drop-down menu at the top of the page.
Step4:  gnomAD Queries Type1
Step6:  After you found the [X, Y] range for your gene of interst you can run Type1 queries efficiently. Here are a couple of examples
Step8:  Query 1.1b - Variant Type (Python)
You can also find the number of INDELs and SNVs in the region of interest by doing the aggregation and count in Python using the dataframe.
Step10:  Instead of aggregating the results in BigQuery to count the number of each variant type, we could return all rows and process them here. The following query adds a few more columns to the previous query. 
Query 1.2 - Allele Count by Sex
A query to retrieve all variants in the region of interest along with AN and AC values split by sex.
AN
Step11:  We can then perform further analysis on the dataframe such as filtering out variants with a low allele count (AC).
Step12:  Or we could filter to find variants that were most common in females that were not found in any male samples.
Step14:  Instead of splitting AN and AC values by sex we can analyze ancestry.
Query 1.3 - Allele Count by Ancestry
A query to retrieve all variants in the region of interest along with AN and AC values for the following ancestries
Step15:  An example here would be to report the most common variant for each ancestry that was not present in any of the others.
Step17:  Query 1.4 - gnomAD Columns
gnomAD tables have many more columns, you can find the full list of columns along with their description using the following query.
Step18:  Using column_info dataframe you can find other available values for the ancestry slice
Step19:  Note that the corresponding values for AC and AF (Alternate allele frequency) exist under the alternate_bases column.
Step21:  Please refer to gnomAD release announcements (v2.1 and v3.0) for more details about demographics and annotation slices.
The next query showcases how to use AN and AC values.
Query 1.5 - Burden of Mutation
Given a region of interest, compute the burden of mutation for the gene along with other summary statistics.
Step22:  The other column to use is alternate_bases.vep which contains the VEP annotaions for each variant.
Step24:  The next query showcases how to use some of the vep annotation values.
Query 1.6 - VEP Annotations
Given a region of interest, examine vep annotations to pull out missense variants.
Step25:  gnomAD Queries Type2
Step27:  Query 2.1 - Find alleles that occur at least in 90% of samples
Find all variants on the selected chromosome that were observed in at least 90% of samples. In other words, this query finds variants where allele frequency is very high for non-REF alleles.
Step28:  We can condense the result and only list gene symbols and the number of variants found in the previous query
Step30:  Query 2.2 - Top variants by ancenstry difference
Find top 1,000 variants on the selected chromosome that show the most significant differences between male samples of African-American ancestry versus Finnish ancestry
Step32:  Query 2.3 - Find genes with high number of INDELs
Find top 1000 genes with the highest number of INDELs on the selected chromosome.
Step34:  Query 2.4 - Find distribution of SNVs across a chromosome
Find the distribution of SNVs across the selected chromosome. In order to be able to plot the result we group base pairs into buckets of size 10,000.
<ASSISTANT_TASK:>
Python Code:
# Import libraries
import numpy as np
import os
# Imports for using and authenticating BigQuery
from google.colab import auth
Explanation: Sample Notebook for exploring gnomAD in BigQuery
This notebook contains sample queries to explore the gnomAD dataset which is hosted through the Google Cloud Public Datasets Program.
Setup and Authentication
If 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.
End of explanation
auth.authenticate_user()
Explanation: User Authentication
Before 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.
End of explanation
# Replace project_id with your Google Cloud Project ID. 
os.environ["GOOGLE_CLOUD_PROJECT"]='project-id'
Explanation: Set Google Cloud Project
To run queries in BigQuery, you need to specify the Google Cloud project that will be used. The queries below report the number of bytes billed by each query. The first 1 TB of query data processed in a project per month is free. For more details, see the BigQuery Pricing page.
To find your Project ID, go to the Project Settings page in the Google Cloud Console. You can select the project you want using the drop-down menu at the top of the page.
End of explanation
import ipywidgets as widgets
print("Variables for Region (Type 1) Queries")
gnomad_version_widget_region = widgets.Dropdown(
    options=['v2_1_1_exomes', 'v2_1_1_genomes', 'v3_genomes'],
    value='v3_genomes',
    description='gnomAD version:',
    disabled=False,
    style={'description_width': 'initial'}
)
display(gnomad_version_widget_region)
chromosome_widget_region = widgets.Dropdown(
    options=['chr1', 'chr2', 'chr3', 'chr4', 'chr5', 'chr6', 'chr7', 'chr8',
             'chr9', 'chr10', 'chr11', 'chr12', 'chr13', 'chr14', 'chr15',
             'chr16', 'chr17', 'chr18', 'chr19', 'chr20', 'chr21', 'chr22',
             'chrX', 'chrY'],
    value='chr17',
    description='Chromosome:',
    disabled=False,
    style={'description_width': 'initial'}
)
display(chromosome_widget_region)
gene_symbol_widget_region= widgets.Text(
    value='BRCA1',
    placeholder='gene_symbol',
    description='Gene Symbol:',
    disabled=False,
    style={'description_width': 'initial'}
)
display(gene_symbol_widget_region)
# Set the variables for the rest of the Type 1 queries based on the values above.
gnomad_version_region=gnomad_version_widget_region.value
chromosome_region=chromosome_widget_region.value
gene_symbol_region=gene_symbol_widget_region.value
print('Running Region (Type 1) queries on gnomAD version: {}, chromosome: {}, gene symbol: {}'.format(
    gnomad_version_region,
    chromosome_region,
    gene_symbol_region
))
if gnomad_version_region.startswith('v3'):
  # Variant type (snv, indel, multi-snv, multi-indel, or mixed) is stored under difference columns in V2 and V3
  variant_type_col = 'variant_type'
  extra_columns = ''
else:
  variant_type_col = 'alternate_bases. allele_type'
  # These vep columns only exist in V2
  extra_columns = 'vep.STRAND AS STRAND, vep.Protein_position AS Protein_pos,'
from google.cloud import bigquery
client = bigquery.Client()
def run_query(query):
    query_job = client.query(query)
    result = query_job.to_dataframe(progress_bar_type='tqdm_notebook')
    gb_processed = (query_job.total_bytes_billed / 1024 ** 3)
    print('This query processed {} GB of data which is {}% of your 1 TB monthly free quota.'.format(gb_processed, round(gb_processed / 1024 * 100, 4)))
    return result
query_template = 
SELECT MIN(start_position) AS X, MAX(end_position) AS Y
FROM `bigquery-public-data.gnomAD.{GNOMAD_VER}__{CHROM}` AS main_table
WHERE EXISTS
  (SELECT 1 FROM UNNEST(main_table.alternate_bases) AS alternate_bases
   WHERE EXISTS (SELECT 1 from alternate_bases.vep WHERE SYMBOL = '{GENE}'))
query = query_template.format(GNOMAD_VER=gnomad_version_region,
                              CHROM=chromosome_region,
                              GENE=gene_symbol_region)
limits = run_query(query)
print(limits)
x = limits.at[0, 'X']
y = limits.at[0, 'Y']
Explanation: gnomAD Queries Type1: Explore a particular genomic region
This category include queries that extract information from a region of the genome, for example a gene. Because gnomAD BigQuery tables utilize integer range partitioning they are optimized for this type of query.
The main requirement to use this feature is to limit queries to a particular region by adding these conditions to the WHERE clause:
WHERE start_position &gt;= X AND start_position &lt;= Y
Where [X, Y] is the region of interest.
You can find values of X and Y by refering to an external databses. For example the following table sumarizes the start and end positions for 4 genes on chromosome 17 extracted from an external resource:
| Gene  | X     | Y     | Source    |
|:-:    |-  |-  |-  |
| BRCA1     | 43044295  | 43125364  | link   |
| COL1A1    | 50184096  | 50201649  | link  |
| TP53  | 31094927  | 31377677  | link    |
| NF1   | 56593699  | 56595611  | link     |
Alternatively you could use the following query that extract the same infomration directly from gnomAD tables.
In the following example we are using BRCA1 on chr17 as an example. You can enter your gene of interest and chromosome to modify all the following queries. If your query returns NaN this might be because you specified the wrong chromosome, which will query the wrong table.
Also you can choose which version of the gnomAD dataset you'd like to use for all the queries:
 * v2_1_1_exomes
 * v2_1_1_genomes
 * v3_genomes
End of explanation
# NOTE: For v2_1_1 the "variant_type" column must be replaced with "alternate_bases.allele_type AS variant_type"
query_template = 
SELECT COUNT(1) AS num, variant_type
FROM (
SELECT DISTINCT 
       start_position,
       reference_bases,
       alternate_bases.alt,
       {VAR_TYPE_COL} AS variant_type,
FROM `bigquery-public-data.gnomAD.{GNOMAD_VER}__{CHROM}` AS main_table,
     main_table.alternate_bases AS alternate_bases
WHERE start_position >= {X} AND start_position <= {Y}
)
GROUP BY 2
ORDER BY 1 DESC
query = query_template.format(GNOMAD_VER=gnomad_version_region,
                              CHROM=chromosome_region,
                              VAR_TYPE_COL=variant_type_col, X=x, Y=y)
summary = run_query(query)
summary.head()
Explanation: After you found the [X, Y] range for your gene of interst you can run Type1 queries efficiently. Here are a couple of examples:
Query 1.1a - Variant Type (BigQuery)
Find the number of INDELs and SNVs in the region of interest using BigQuery
End of explanation
# NOTE: For v2_1_1 the "variant_type" column must be replaced with "alternate_bases.allele_type AS variant_type"
query_template = 
SELECT DISTINCT 
       start_position,
       reference_bases,
       alternate_bases.alt,
       {VAR_TYPE_COL} AS variant_type,
FROM `bigquery-public-data.gnomAD.{GNOMAD_VER}__{CHROM}` AS main_table,
     main_table.alternate_bases AS alternate_bases
WHERE start_position >= {X} AND start_position <= {Y}
ORDER BY 1,2
query = query_template.format(GNOMAD_VER=gnomad_version_region,
                              CHROM=chromosome_region,
                              VAR_TYPE_COL=variant_type_col, X=x, Y=y)
summary_dataframe = run_query(query)
# Count the number of each variant type in Python instead of in BigQuery
print('Number of variants by type:')
for v in summary_dataframe.variant_type.unique():
  print('{}: {}'.format(v,
                        np.count_nonzero(summary_dataframe['variant_type'] == v)))
Explanation: Query 1.1b - Variant Type (Python)
You can also find the number of INDELs and SNVs in the region of interest by doing the aggregation and count in Python using the dataframe.
End of explanation
# NOTE: For v2_1_1 the "variant_type" column must be replaced with "alternate_bases.allele_type AS variant_type"
query_template = 
SELECT reference_name AS CHROM, 
       start_position AS POS,
       names AS ID,
       reference_bases AS REF,
       alternate_bases.alt AS ALT,
       AN,
       AN_male,
       AN_female,
       alternate_bases.AC AS AC,
       alternate_bases.AC_male AS AC_male,
       alternate_bases.AC_female AS AC_female,
       alternate_bases.nhomalt AS nhomalt,
       alternate_bases.nhomalt_male AS nhomalt_male,
       alternate_bases.nhomalt_female AS nhomalt_female,
FROM `bigquery-public-data.gnomAD.{GNOMAD_VER}__{CHROM}` AS main_table,
     main_table.alternate_bases AS alternate_bases
WHERE start_position >= {X} AND start_position <= {Y}
ORDER BY 1,2
query = query_template.format(GNOMAD_VER=gnomad_version_region,
                              CHROM=chromosome_region, X=x, Y=y)
stats_sex = run_query(query)
stats_sex.head()
Explanation: Instead of aggregating the results in BigQuery to count the number of each variant type, we could return all rows and process them here. The following query adds a few more columns to the previous query. 
Query 1.2 - Allele Count by Sex
A query to retrieve all variants in the region of interest along with AN and AC values split by sex.
AN: Total number of alleles in samples
AC: Alternate allele count for samples
nhomalt: The number of individuals that are called homozygous for the alternate allele.
End of explanation
stats_sex_filtered_ac=stats_sex.loc[stats_sex['AC'] > 10]
stats_sex_filtered_ac.head()
Explanation: We can then perform further analysis on the dataframe such as filtering out variants with a low allele count (AC).
End of explanation
stats_sex_no_male=stats_sex.loc[stats_sex['AC_male'] == 0].sort_values(by=('AC_female'),
                                                                       ascending = False)
stats_sex_no_male.head(10)
Explanation: Or we could filter to find variants that were most common in females that were not found in any male samples.
End of explanation
# NOTE: For v2_1_1 the "variant_type" column must be replaced with "alternate_bases.allele_type AS variant_type"
query_template = 
SELECT reference_name AS CHROM, 
       start_position AS POS,
       names AS ID,
       reference_bases AS REF,
       alternate_bases.alt AS ALT,
       AN_afr,
       AN_amr,
       AN_eas,
       AN_nfe,
       alternate_bases.AC_afr AS AC_afr,
       alternate_bases.AC_amr AS AC_amr,
       alternate_bases.AC_eas AS AC_eas,
       alternate_bases.AC_nfe AS AC_nfe,
FROM `bigquery-public-data.gnomAD.{GNOMAD_VER}__{CHROM}` AS main_table,
     main_table.alternate_bases AS alternate_bases
WHERE start_position >= {X} AND start_position <= {Y}
ORDER BY 1,2
query = query_template.format(GNOMAD_VER=gnomad_version_region,
                              CHROM=chromosome_region, X=x, Y=y)
stats_ancestry = run_query(query)
stats_ancestry.head()
Explanation: Instead of splitting AN and AC values by sex we can analyze ancestry.
Query 1.3 - Allele Count by Ancestry
A query to retrieve all variants in the region of interest along with AN and AC values for the following ancestries:
* afr: African-American/African ancestry
* amr: Latino ancestry
* eas: East Asian ancestry
* nfe: Non-Finnish European ancestry
End of explanation
stats_ancestry_amr=stats_ancestry.loc[
                                      (stats_ancestry['AC_amr'] > 0) &
                                      (stats_ancestry['AC_afr'] == 0) &
                                      (stats_ancestry['AC_eas'] == 0) &
                                      (stats_ancestry['AC_nfe'] == 0)].sort_values(by=('AC_amr'),
                                                                                   ascending = False)
stats_ancestry_amr.head(10)
Explanation: An example here would be to report the most common variant for each ancestry that was not present in any of the others.
End of explanation
query_template = 
SELECT column_name, field_path, description
FROM `bigquery-public-data`.gnomAD.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS
WHERE table_name = "{GNOMAD_VER}__{CHROM}"
      AND column_name IN (
          SELECT COLUMN_NAME
          FROM `bigquery-public-data`.gnomAD.INFORMATION_SCHEMA.COLUMNS
          WHERE table_name = "{GNOMAD_VER}__{CHROM}")
query = query_template.format(GNOMAD_VER=gnomad_version_region,
                              CHROM=chromosome_region)
column_info = run_query(query)
print('There are {} columns in `bigquery-public-data.gnomAD.{}__{}` table'.format(len(column_info.index),
                                                                                  gnomad_version_region,
                                                                                  chromosome_region))
column_info.head(7)
Explanation: Query 1.4 - gnomAD Columns
gnomAD tables have many more columns, you can find the full list of columns along with their description using the following query.
End of explanation
AN_columns = column_info[column_info['column_name'].str.startswith('AN')] # Retain only rows that column_name starts with "AN"
AN_columns = AN_columns[['column_name', 'description']] # Drop extra column (field_path)
AN_columns = AN_columns.sort_values(by=['column_name']) # Sort by column_name
AN_columns.head(11)
Explanation: Using column_info dataframe you can find other available values for the ancestry slice:
End of explanation
AC_columns = column_info[column_info['field_path'].str.startswith('alternate_bases.AC')] # Retain only rows that field_path starts with "alternate_bases.AC"
AC_columns = AC_columns[['field_path', 'description']] # Drop extra column (column_name)
AC_columns = AC_columns.sort_values(by=['field_path']) # Sort by field_path
AC_columns.head(11)
Explanation: Note that the corresponding values for AC and AF (Alternate allele frequency) exist under the alternate_bases column.
End of explanation
query_template = 
WITH summary_stats AS (
SELECT
  COUNT(1) AS num_variants,
  SUM(ARRAY_LENGTH(alternate_bases)) AS num_alts,  # This data appears to be bi-allelic.
  SUM((SELECT alt.AC FROM UNNEST(alternate_bases) AS alt)) AS sum_AC,
  APPROX_QUANTILES((SELECT alt.AC FROM UNNEST(alternate_bases) AS alt), 10) AS quantiles_AC,
  SUM(AN) AS sum_AN,
  APPROX_QUANTILES(AN, 10) AS quantiles_AN,
  -- Also include some information from Variant Effect Predictor (VEP).
  STRING_AGG(DISTINCT (SELECT annot.symbol FROM UNNEST(alternate_bases) AS alt,
                                                UNNEST(vep) AS annot LIMIT 1), ', ') AS genes
FROM `bigquery-public-data.gnomAD.{GNOMAD_VER}__{CHROM}` AS main_table
WHERE start_position >= {X} AND start_position <= {Y})
---
--- The resulting quantiles and burden_of_mutation score give a very rough idea of the mutation
--- rate within these particular regions of the genome. This query could be further refined to
--- compute over smaller windows within the regions of interest and/or over different groupings
--- of AC and AN by population.
---
SELECT
  ROUND(({Y} - {X}) / num_variants, 3) AS burden_of_mutation,
  *,
FROM summary_stats
query = query_template.format(GNOMAD_VER=gnomad_version_region,
                              CHROM=chromosome_region, X=x, Y=y)
burden_of_mu = run_query(query)
burden_of_mu.head()
Explanation: Please refer to gnomAD release announcements (v2.1 and v3.0) for more details about demographics and annotation slices.
The next query showcases how to use AN and AC values.
Query 1.5 - Burden of Mutation
Given a region of interest, compute the burden of mutation for the gene along with other summary statistics.
End of explanation
vep_columns = column_info[column_info['field_path'].str.startswith('alternate_bases.vep')] # Retain only rows that field_path starts with "alternate_bases.vep"
vep_columns = vep_columns[['field_path', 'description']] # Drop extra column (column_name)
vep_columns.head(22)
Explanation: The other column to use is alternate_bases.vep which contains the VEP annotaions for each variant.
End of explanation
query_template = 
SELECT reference_name AS CHROM, 
       start_position AS POS,
       names AS ID,
       reference_bases AS REF,
       alternate_bases.alt AS ALT,
       vep.Consequence AS Consequence,
       vep.IMPACT AS Impact,
       vep.SYMBOL AS Symbol,
       vep.Gene AS Gene,
       vep.EXON AS EXON,
       vep.INTRON AS INTRON,
       {EXTRA_COLS}
FROM `bigquery-public-data.gnomAD.{GNOMAD_VER}__{CHROM}` AS main_table,
     main_table.alternate_bases AS alternate_bases,
     alternate_bases.vep AS vep
WHERE start_position >= {X} AND start_position <= {Y} AND
      REGEXP_CONTAINS(vep.Consequence, r"missense_variant")
ORDER BY start_position, reference_bases
query = query_template.format(GNOMAD_VER=gnomad_version_region,
                              CHROM=chromosome_region,
                              EXTRA_COLS=extra_columns, X=x, Y=y)
neg_variants = run_query(query)
neg_variants.head()
Explanation: The next query showcases how to use some of the vep annotation values.
Query 1.6 - VEP Annotations
Given a region of interest, examine vep annotations to pull out missense variants.
End of explanation
import ipywidgets as widgets
print("Variables for Chromosome (Type 2) queries")
gnomad_version_widget_chr = widgets.Dropdown(
    options=['v2_1_1_exomes', 'v2_1_1_genomes', 'v3_genomes'],
    value='v2_1_1_exomes',
    description='gnomAD version:',
    disabled=False,
    style={'description_width': 'initial'}
)
display(gnomad_version_widget_chr)
chromosome_widget_chr = widgets.Dropdown(
    options=['chr1', 'chr2', 'chr3', 'chr4', 'chr5', 'chr6', 'chr7', 'chr8',
             'chr9', 'chr10', 'chr11', 'chr12', 'chr13', 'chr14', 'chr15',
             'chr16', 'chr17', 'chr18', 'chr19', 'chr20', 'chr21', 'chr22',
             'chrX', 'chrY'],
    value='chr17',
    description='Chromosome:',
    disabled=False,
    style={'description_width': 'initial'}
)
display(chromosome_widget_chr)
# Set the variables for the rest of the Chromosome (Type 2) queries based on the values above.
gnomad_version_chr=gnomad_version_widget_chr.value
chromosome_chr=chromosome_widget_chr.value
print('Running chromosome (Type 2) queries on gnomAD version: {}, chromosome: {}'.format(
    gnomad_version_chr,
    chromosome_chr
))
if gnomad_version_chr.startswith('v3'):
  # Variant type (snv, indel, multi-snv, multi-indel, or mixed) is stored under difference columns in V2 and V3
  variant_type_col = 'variant_type'
  extra_columns = ''
else:
  variant_type_col = 'alternate_bases. allele_type'
  # These vep columns only exist in V2
  extra_columns = 'vep.STRAND AS STRAND, vep.Protein_position AS Protein_pos,'
Explanation: gnomAD Queries Type2: Explore an entire chromosome
This section queries across an entire chromosome.
End of explanation
query_template = 
SELECT reference_name AS CHROM, 
       start_position AS POS,
       names AS ID,
       reference_bases AS REF,
       alternate_bases.alt AS ALT,
       vep.SYMBOL AS Symbol,
       vep.Gene AS Gene,
       AN,
       alternate_bases.AC AS AC,
       alternate_bases.AF AS AF,
       vep.EXON AS EXON,
       vep.INTRON AS INTRON,
       {EXTRA_COLS}
FROM `bigquery-public-data.gnomAD.{GNOMAD_VER}__{CHROM}` AS main_table,
     main_table.alternate_bases AS alternate_bases,
     alternate_bases.vep AS vep
WHERE AN > 0 AND AF > 0.9 
ORDER BY AN DESC
query = query_template.format(GNOMAD_VER=gnomad_version_chr,
                              CHROM=chromosome_chr,
                              EXTRA_COLS=extra_columns)
high_af = run_query(query)
high_af.head()
Explanation: Query 2.1 - Find alleles that occur at least in 90% of samples
Find all variants on the selected chromosome that were observed in at least 90% of samples. In other words, this query finds variants where allele frequency is very high for non-REF alleles.
End of explanation
high_af.groupby('Symbol').count()[['POS']].sort_values(by=['POS'],
                                                               ascending=False).head(10)
Explanation: We can condense the result and only list gene symbols and the number of variants found in the previous query:
End of explanation
query_template = 
SELECT reference_name AS CHROM, 
       start_position AS POS,
       names AS ID,
       reference_bases AS REF,
       alternate_bases.alt AS ALT,
       vep.SYMBOL AS Symbol,
       vep.Gene AS Gene,
       AN,
       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,
       vep.EXON AS EXON,
       vep.INTRON AS INTRON,
       {EXTRA_COLS} 
FROM `bigquery-public-data.gnomAD.{GNOMAD_VER}__{CHROM}` 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
query = query_template.format(GNOMAD_VER=gnomad_version_chr,
                              CHROM=chromosome_chr,
                              EXTRA_COLS=extra_columns)                              
stats_chr_ancestry = run_query(query)
stats_chr_ancestry.head()
Explanation: Query 2.2 - Top variants by ancenstry difference
Find top 1,000 variants on the selected chromosome that show the most significant differences between male samples of African-American ancestry versus Finnish ancestry
End of explanation
query_template = 
SELECT Symbol, count(1) AS num_indels
FROM
    (
    SELECT DISTINCT 
        start_position AS str_pos,
        alternate_bases.alt AS alt,
        vep.SYMBOL AS Symbol,
        {VAR_TYPE_COL} AS variant_type,
    FROM `bigquery-public-data.gnomAD.{GNOMAD_VER}__{CHROM}` AS main_table,
        main_table.alternate_bases AS alternate_bases,
        alternate_bases.vep AS vep
    WHERE vep.SYMBOL IS NOT NULL AND variant_type IN ('ins', 'del', 'indel')
    )
GROUP BY 1
ORDER BY 2 DESC
LIMIT 1000
query = query_template.format(GNOMAD_VER=gnomad_version_chr,
                              CHROM=chromosome_chr,
                              VAR_TYPE_COL=variant_type_col)
indel_stats = run_query(query)
indel_stats.head(10)
Explanation: Query 2.3 - Find genes with high number of INDELs
Find top 1000 genes with the highest number of INDELs on the selected chromosome.
End of explanation
bucket_size = 10000
query_template = 
SELECT CAST(FLOOR(DIV(start_position, {BUCKET})) AS INT64) AS start_pos_bucket ,
       count(1) AS num_snv
FROM 
    (
    SELECT DISTINCT 
          start_position,
          alternate_bases.alt AS alt,
          {VAR_TYPE_COL} AS variant_type,
    FROM `bigquery-public-data.gnomAD.{GNOMAD_VER}__{CHROM}` AS main_table,
        main_table.alternate_bases AS alternate_bases
    WHERE variant_type = 'snv'
    )
GROUP BY 1
ORDER BY 1
query = query_template.format(GNOMAD_VER=gnomad_version_chr,
                              CHROM=chromosome_chr,
                              VAR_TYPE_COL=variant_type_col,
                              BUCKET=bucket_size)
snv_dist = run_query(query)
snv_dist.head()
import matplotlib.pyplot as plt
plt.figure(dpi=150)
plt.bar(snv_dist.start_pos_bucket, snv_dist.num_snv)
plt.xlabel("Bucket number of start_pos")
plt.ylabel("No of SNVs in each bucket")
plt.title("Distribution of SNVs on {} for buckets of {} base pairs".format(chromosome_chr, bucket_size))
plt.show()
Explanation: Query 2.4 - Find distribution of SNVs across a chromosome
Find the distribution of SNVs across the selected chromosome. In order to be able to plot the result we group base pairs into buckets of size 10,000.
End of explanation
<END_TASK>