ibis-data

Ibis Data Interface

Ibis provides a database-agnostic Python DataFrame API. Write queries once in Python; Ibis translates them to optimized SQL for the connected backend (DuckDB, PostgreSQL, SQLite, etc.).

Why Ibis

• Portability: Develop with DuckDB, deploy against PostgreSQL -- change only the connection
• Lazy evaluation: Operations build an expression tree; nothing executes until .execute()
• Full SQL power: Window functions, CTEs, joins, aggregations -- all through Python
• No ORM: You get SQL performance without SQL strings

Connecting

import ibis

# DuckDB (default for local/parquet work)
con = ibis.duckdb.connect()
con = ibis.duckdb.connect("my.duckdb")

# PostgreSQL
con = ibis.postgres.connect(host="localhost", database="mydb", user="user", password="pass")

# SQLite
con = ibis.sqlite.connect("my.sqlite")

# Read files directly
table = con.read_parquet("data.parquet")
table = con.read_csv("data.csv")

Core Operations

# Explore
table.schema()          # column names and types
table.head(10)          # preview rows
table.describe()        # summary statistics
table.count().execute() # row count

# Select and filter
selected = table.select("id", "amount", "date")
filtered = table.filter((table.amount > 100) & (table.date >= "2024-01-01"))
sorted_data = table.order_by(table.amount.desc())

# Transform
enriched = table.mutate(
    revenue=table.quantity * table.unit_price,
    year=table.date.year(),
    size=ibis.case()
        .when(table.amount < 100, "small")
        .when(table.amount < 1000, "medium")
        .else_("large")
        .end()
)

# Aggregate
summary = (
    table.group_by("category")
    .aggregate(
        total=table.amount.sum(),
        avg=table.amount.mean(),
        count=table.count()
    )
)

# Join
joined = (
    orders
    .join(customers, orders.customer_id == customers.id, how="left")
    .select(orders.order_id, orders.amount, customers.name)
)

# Window functions
ranked = table.mutate(
    rank=table.amount.rank().over(
        ibis.window(group_by="category", order_by=table.amount.desc())
    )
)

# Execute and export
df = summary.execute()              # -> pandas DataFrame
con.to_parquet(summary, "out.parquet")
df.to_csv("out.csv", index=False)

API Reference

API	What it covers
Table expressions	select, filter, mutate, group_by, agg, join, order_by
Selectors	Choose columns by name, type, or regex
Generic expressions	.cast(), .isnull(), .fillna(), case(), .ifelse()
Numeric expressions	sum(), mean(), std(), rounding, logarithms
String expressions	Slicing, regex, case conversion, stripping
Temporal expressions	.year(), .month(), interval arithmetic, formatting
Collection expressions	Array/map operations, unnesting
JSON expressions	Path-based extraction from JSON columns

Best Practices

• Filter early: Reduce data volume before aggregations
• Stay lazy: Chain operations before calling .execute()
• Use selectors: Apply operations to multiple columns programmatically
• Handle nulls: Check with .isnull() and handle with .fillna() explicitly
• Check SQL: Use ibis.to_sql(expr) to inspect generated queries

Installation

uv add "ibis-framework[duckdb]"        # DuckDB backend
uv add "ibis-framework[postgres]"      # PostgreSQL backend