birds of feathers

birds.py

import ibis

conn = ibis.connect("duckdb://") # bigguery://, snowfalke://, etc

# load penguin data
penguins = conn.read_csv(
    "https://raw.githubusercontent.com/mwaskom/seaborn-data/master/penguins.csv"
)

# query: find female penguins heavier than their species average

query = (
    penguins
    .mutate(
        species_avg_weight=penguins.body_mass_g.mean().over(
          ibis.window(group_by=penguins.species)
        )
    )
    .filter(lambda t: (t.sex.lower() == "female") & (t.body_mass_g > t.species_avg_weight))
    .select("species", "island", "body_mass_g", "species_avg_weight")
    .order_by(ibis.desc("body_mass_g"))
)

# see results executed in db
print(query.execute())
                        
# debug the SQL generated
print(ibis.to_sql(query))