📚Tutorial

display(df1)

display(df2)

display(overlapping_intervals)

pb.visualize_intervals(overlapping_intervals)

display(nearest_intervals)

pb.visualize_intervals(nearest_intervals, "nearest pair")

display(count_overlaps)

display(coverage)

display(merge)

gff.limit(3).collect()

gff.limit(3).collect()

pb.read_vcf("data/example.vcf").limit(3).collect()

pb.describe_vcf("data/example.vcf").sort("name")

pb.read_vcf("data/example.vcf", info_fields=["CSQ"]).limit(3).collect()

pb.read_fastq("data/example.fastq.gz").limit(3).collect()

pb.read_bam("data/example.bam").limit(3).collect()

pb.read_bed("data/example.bed.bgz").limit(3).collect()

pb.sql("SHOW TABLES").collect()

pb.sql("DESCRIBE example_gff").collect()

pb.sql("SELECT start, end, type FROM example_gff WHERE end = 14409").collect()

pb.sql("SELECT * FROM v_example_gff").collect()

pb.describe_vcf("gs://gcp-public-data--gnomad/release/4.1/genome_sv/gnomad.v4.1.sv.sites.vcf.gz", compression_type="bgz").filter(pl.col("description").str.contains(r"Latino.* allele frequency"))

pb.register_vcf("gs://gcp-public-data--gnomad/release/4.1/genome_sv/gnomad.v4.1.sv.sites.vcf.gz", "gnomad",
                info_fields=['AF_amr'], compression_type="bgz")
query = """
    SELECT
        chrom,
        start,
        end,
        alt,
        array_element(af_amr,1) AS af_amr
    FROM gnomad
    WHERE
        filter = 'HIGH_NCR'
    AND
        alt = '<DUP>'
"""
pb.sql(f"{query} LIMIT 3").collect()

pb.sql("SELECT chrom, start, end, alt FROM gnomad_sites_chr1 LIMIT 10").collect()

pb.sql("SELECT * FROM v_gnomad_sites_chr1").collect()

df = pl.DataFrame({
    "chrom": ["chr1", "chr1"],
    "start": [11993, 12102],
    "end": [11996, 12200],
    "annotation": ["ann1", "ann2"]
})
pb.from_polars("test_annotation", df)
pb.sql("SELECT * FROM test_annotation").collect()

pb.overlap("v_gnomad_sites_chr1","test_annotation").limit(3).collect()

%%time
pb.overlap("data/ex-rna/*.parquet", "data/chainRn4/*.parquet",  cols1=cols, cols2=cols).collect().count()

%%time
pb.overlap("data/ex-rna/*.parquet", "data/chainRn4/*.parquet",  cols1=cols, cols2=cols).collect().count()

%memit pb.overlap("data/ex-rna/*.parquet", "data/chainRn4/*.parquet",  cols1=cols, cols2=cols, streaming=True).sink_parquet("/tmp/overlap.parquet")

%memit pb.overlap("data/ex-rna/*.parquet", "data/chainRn4/*.parquet",  cols1=cols, cols2=cols).collect().write_parquet("/tmp/overlap.parquet")

assert len(bf_overlap) == len(pb_overlap)

pb_overlap = pb.overlap(
    BIO_PD_DF1,
    BIO_PD_DF2,
    cols1=cols,
    cols2=cols,
    output_type="pandas.DataFrame",
    suffixes=("_1", "_2"),
    use_zero_based=True,
)

# we need to override the compression type as the file is bgzipped not gzipped as the extension suggests
pb.read_vcf(gcs_vcf_path, compression_type="bgz").limit(3).collect()

pb.describe_vcf(gcs_vcf_path, compression_type="bgz").sort("name").limit(5)

pb.read_vcf(aws_s3_vcf_path, chunk_size=8, concurrent_fetches=1).limit(3).collect()

vcf_info_fields = ["SVTYPE", "SVLEN"]
pb.read_vcf(gcs_vcf_path, info_fields=vcf_info_fields, compression_type="bgz").limit(3).collect()

%%time
pb.read_vcf("/tmp/gnomad.v4.1.sv.sites.vcf.gz", thread_num=1, compression_type="bgz").count().collect()

%%time
pb.read_vcf("/tmp/gnomad.v4.1.sv.sites.vcf.gz", thread_num=4, compression_type="bgz").count().collect()

pl.read_csv("/tmp/streaming_run.csv").limit(3)

pb.overlap(vcf_1, vcf_2, streaming=False, read_options1=read_options_1, read_options2=read_options_2).collect().count()

pb.read_vcf(gcs_vcf_path, info_fields=info_fields).limit(3).collect()

pb.sql("SELECT chrom, svtype  FROM gnomad_sv").limit(3).collect()

pb.sql("SELECT * FROM gnomad_sv WHERE SVTYPE = 'DEL' AND SVLEN > 1000").limit(3).collect()

pb.sql("SELECT alt, count(*) as cnt FROM gnomad_sv group by alt").collect_schema()

pb.sql("SELECT alt, count(*) as cnt FROM gnomad_sv group by alt").collect()

pb.sql("SELECT chrom, count(*) as cnt FROM gnomad_sv GROUP BY chrom ORDER BY chrom").collect()

pl.read_csv("/tmp/gnomad_chr1.csv").count()

pb.sql("SELECT replace(chrom,'chr','') AS chrom, start, ac,af  FROM gnomad_exomes WHERE array_element(af,1)>0.01").limit(10).collect()

pb.overlap("gnomad_sv", "gnomad_exomes").collect().count()

! wc -l /tmp/overlap.csv