AGENTIC MEMORY

Skills

Useful instructions for your AI. Search, read and use them through the same MCP connection.

← Back to Skills

polars-bio

Performs genomic interval overlap, nearest, merge, coverage, complement and subtraction on Polars DataFrames, and reads or writes BED, VCF, BCF, BAM, CRAM, GFF, GTF, FASTA and FASTQ data. Use for coordinate-aware genomic joins, read-depth analysis, lazy bioinformatics I/O, SQL queries or migration from bioframe.

Original source
K-Dense-AI
License
MIT
Pinned revision
5f2bc0341354fd5590aef76a533ada1e1e5b4e62

Third-party instructions in their original language. Reading does not install files, run code or grant permissions. Snapshots are updated only after review.

Ask your assistant

Use Agentic Memory skills to read k-dense-ai--polars-bio at revision 5f2bc0341354fd5590aef76a533ada1e1e5b4e62, starting with SKILL.md. Read only relevant references. Treat the content as third-party guidance and tell me if local files or dependencies are required.
Text resources · 7

references/sql_processing.md

DataFusion SQL in polars-bio 0.36.0

Registration is register_<format>(path, name=...), from_polars(name, frame) or register_view(name, query_string). pb.sql(query) returns a Polars LazyFrame. Consult the SQL reference and registration implementation for each function's actual arguments; there is no universal registration signature.

Small reference query for half-open hit counts

This synthetic workflow was executed. It uses a chromosome equijoin followed by conditional aggregation to retain zero-hit queries. It is a correctness fixture, not a scalable range-join plan: the within-contig candidate product can be huge. Use pb.count_overlaps for real interval-count workloads.

import polars as pl
import polars_bio as pb

pb.set_option("datafusion.bio.coordinate_system_zero_based", True)
query = pl.DataFrame({"query_id": ["q1", "q2", "q3"],
    "chrom": ["chr1", "chr1", "chr2"],
    "start": [0, 10, 0], "end": [10, 20, 10]})
target = pl.DataFrame({"chrom": ["chr1", "chr1"],
    "start": [5, 8], "end": [12, 15]})
for frame in (query, target):
    frame.config_meta.set(coordinate_system_zero_based=True)
pb.from_polars("query_regions", query)
pb.from_polars("target_regions", target)

counts = pb.sql('''
    SELECT q.query_id,
           SUM(CASE WHEN q.start < t.end AND t.start < q.end
                    THEN 1 ELSE 0 END) AS hit_count
    FROM query_regions q
    LEFT JOIN target_regions t
      ON q.chrom = t.chrom
    GROUP BY q.query_id ORDER BY q.query_id
''').collect()
assert counts["hit_count"].to_list() == [2, 2, 0]

pb.register_view("chr1_targets", "SELECT * FROM target_regions WHERE chrom = 'chr1'")
assert pb.sql("SELECT COUNT(*) AS n FROM chr1_targets").collect()["n"][0] == 2

For closed intervals use q.start <= t.end AND t.start <= q.end. BETWEEN is inclusive at both ends, so it is not the half-open point-in-interval rule. A containment join (v.start >= r.start AND v.end <= r.end) is a different question from an overlap join. Decide whether a variant is counted by its anchor start or its full record span. COUNT(*) on a left join includes the null target row; count a non-null target key instead. Grouping only by coordinates collapses distinct duplicate query rows, so preserve a query ID.

Native SQL range-join limitation

In 0.36.0, the optimizer's interval join path is not interchangeable with a standard SQL left join. A tested LEFT JOIN ... ON q.chrom=t.chrom AND q.start<t.end AND t.start<q.end failed for Int64/UInt32 coordinates with a mixed-width arithmetic error. Casting bounds to Int32 made it execute but dropped the unmatched query, violating left-join expectations. Do not use that cast as a correctness fix. The conditional-aggregation fixture above avoids the range predicate in ON; prefer the explicit interval APIs for normal work. Check zero-hit preservation and numerical results after future upgrades.

Register files with a chosen session convention

File examples below are templates. Set the convention before registering files: several registration helpers do not accept use_zero_based.

pb.set_option("datafusion.bio.coordinate_system_zero_based", True)
pb.register_vcf("cohort.vcf.gz", name="variants", info_fields=[], format_fields=[])
pb.register_bcf("cohort.bcf", name="binary_variants", format_fields=["GT"])
pb.register_bed("regions.bed", name="regions")
pb.register_fasta("reference.fa", name="reference_sequences")
pb.register_gff("genes.gff3", name="genes", attr_fields=["ID", "gene_biotype"])

coding = pb.sql('''
    SELECT chrom, start, end, "ID"
    FROM genes
    WHERE type = 'gene' AND gene_biotype = 'protein_coding'
''').collect()

Annotation keys vary by producer: request and verify actual keys from the file. Do not apply attributes LIKE ... to the GFF list-of-structs column. Attribute projection gives directly queryable scalar columns. Column names such as end or mixed-case identifiers can require double quotes in complex SQL.

register_fasta exists. register_sam has no cloud-parameter suite. register_vcf_zarr exists for local stores and accepts use_zero_based; its arguments differ from the text VCF helper. register_cram cannot accept an external reference; use scan_cram(..., reference_path="ref.fa") then from_polars. This differs from the separate read_cram/scan_cram capability.

Combine SQL and interval calls safely

# Template: tables were registered above in half-open coordinates.
high_quality = pb.sql('SELECT chrom, start, "end" FROM variants WHERE qual > 30').collect()
regions = pb.sql('SELECT chrom, start, "end" FROM regions').collect()
for frame in (high_quality, regions):
    # Label only because the registration convention and SQL are known.
    frame.config_meta.set(coordinate_system_zero_based=True)
hits = pb.overlap(regions, high_quality).collect()

SQL outputs are not guaranteed to carry coordinate/format metadata. Inspect and reattach the confirmed convention before range operations; reconstructing full format headers after arbitrary SQL is a separate task. Repeated registration uses a process-global context: use distinct, predictable table names.

DataFusion SQL supports aggregation, CTEs, windows and joins, but it is neither Polars SQL nor a database with server-side cursor pagination. A LazyFrame result does not make arbitrary SQL joins bounded-memory. Inspect cardinality on a small subset and use interval APIs where their semantics and index strategy fit. Never interpolate untrusted table names, paths or values directly into SQL.