Skip to content

Top-level APIs

These methods and objects are available directly in the ibis module.

NA

NA is the null scalar.

and_(*predicates)

Combine multiple predicates using &.

Parameters:

Name Type Description Default
predicates ir.BooleanValue

Boolean value expressions

()

Returns:

Type Description
ir.BooleanValue

A new predicate that evaluates to True if all composing predicates are True. If no predicates were provided, returns True.

array(values, type=None)

Create an array expression.

If the input expressions are all column expressions, then the output will be an ArrayColumn. The input columns will be concatenated row-wise to produce each array in the output array column. Each array will have length n, where n is the number of input columns. All input columns should be of the same datatype.

If the input expressions are Python literals, then the output will be a single ArrayScalar of length n, where n is the number of input values. This is equivalent to

values = [1, 2, 3]
ibis.literal(values)

Parameters:

Name Type Description Default
values Iterable[V]

An iterable of Ibis expressions or a list of Python literals

required
type str | dt.DataType | None

An instance of ibis.expr.datatypes.DataType or a string indicating the ibis type of value.

None

Examples:

Create an array column from column expressions

>>> import ibis
>>> t = ibis.table([('a', 'int64'), ('b', 'int64')], name='t')
>>> result = ibis.array([t.a, t.b])

Create an array scalar from Python literals

>>> import ibis
>>> result = ibis.array([1.0, 2.0, 3.0])

Returns:

Type Description
ArrayValue

An array column (if the inputs are column expressions), or an array scalar (if the inputs are Python literals)

asc(expr)

Create a ascending sort key from asc or column name.

Parameters:

Name Type Description Default
expr ir.Column | str

The expression or column name to use for sorting

required

Examples:

>>> import ibis
>>> t = ibis.table(dict(g='string'), name='t')
>>> t.group_by('g').size('count').sort_by(ibis.asc('count'))
r0 := UnboundTable: t
  g string
r1 := Aggregation[r0]
  metrics:
    count: Count(t)
  by:
    g: r0.g
Selection[r1]
  sort_keys:
    asc|r1.count

Returns:

Type Description
ir.SortExpr | ops.DeferredSortKey

A sort expression or deferred sort key

case()

Begin constructing a case expression.

Examples:

>>> import ibis
>>> cond1 = ibis.literal(1) == 1
>>> cond2 = ibis.literal(2) == 1
>>> (ibis.case()
...  .when(cond1, 3)
...  .when(cond2, 4).end())
>>> SearchedCase(cases=[ValueList(values=[1 == 1, 2 == 1])], results=[ValueList(values=[3, 4])], default=Cast(None, to=int8))

Returns:

Type Description
bl.SearchedCaseBuilder

A builder object to use for constructing a case expression.

coalesce(self, *args)

Return the first non-null value from args.

Parameters:

Name Type Description Default
args Value

Arguments from which to choose the first non-null value

()

Examples:

>>> import ibis
>>> ibis.coalesce(None, 4, 5)
Coalesce([ValueList(values=[None, 4, 5])])

Returns:

Type Description
Value

Coalesced expression

cumulative_window(group_by=None, order_by=None)

Create a cumulative window for use with window functions.

All window frames / ranges are inclusive.

Parameters:

Name Type Description Default
group_by None

Grouping key

None
order_by None

Ordering key

None

Returns:

Type Description
Window

A window frame

date(value)

Return a date literal if value is coercible to a date.

Parameters:

Name Type Description Default
value None

Date string

required

Returns:

Type Description
DateValue

A date expression

desc(expr)

Create a descending sort key from expr or column name.

Parameters:

Name Type Description Default
expr ir.Column | str

The expression or column name to use for sorting

required

Examples:

>>> import ibis
>>> t = ibis.table(dict(g='string'), name='t')
>>> t.group_by('g').size('count').sort_by(ibis.desc('count'))
r0 := UnboundTable: t
  g string
r1 := Aggregation[r0]
  metrics:
    count: Count(t)
  by:
    g: r0.g
Selection[r1]
  sort_keys:
    desc|r1.count

Returns:

Type Description
ir.SortExpr | ops.DeferredSortKey

A sort expression or deferred sort key

difference(self, *tables, *, distinct=True, **kwargs)

Compute the set difference of multiple table expressions.

The input tables must have identical schemas.

Parameters:

Name Type Description Default
*tables Table

One or more table expressions

()
distinct bool

Only diff distinct rows not occurring in the calling table

True

Returns:

Type Description
Table

The rows present in self that are not present in tables.

greatest(self, *args)

Compute the largest value among the supplied arguments.

Parameters:

Name Type Description Default
args ir.Value

Arguments to choose from

()

Returns:

Type Description
ir.Value

Maximum of the passed arguments

ifelse(self, true_expr, false_expr)

Construct a ternary conditional expression.

Parameters:

Name Type Description Default
true_expr ir.Value

Expression to return if self evaluates to True

required
false_expr ir.Value

Expression to return if self evaluates to False

required

Examples:

>>> import ibis
>>> t = ibis.table([("is_person", "boolean")], name="t")
>>> expr = t.is_person.ifelse("yes", "no")
>>> print(ibis.impala.compile(expr))
SELECT CASE WHEN `is_person` THEN 'yes' ELSE 'no' END AS `tmp`
FROM t

Returns:

Type Description
ir.Value

The value of true_expr if arg is True else false_expr

intersect(self, *tables, *, distinct=True, **kwargs)

Compute the set intersection of multiple table expressions.

The input tables must have identical schemas.

Parameters:

Name Type Description Default
*tables Table

One or more table expressions

()
distinct bool

Only return distinct rows

True

Returns:

Type Description
Table

A new table containing the intersection of all input tables.

interval(value=None, unit='s', years=None, quarters=None, months=None, weeks=None, days=None, hours=None, minutes=None, seconds=None, milliseconds=None, microseconds=None, nanoseconds=None)

Return an interval literal expression.

Parameters:

Name Type Description Default
value int | datetime.timedelta | None

Interval value. If passed, must be combined with unit.

None
unit str

Unit of value

's'
years int | None

Number of years

None
quarters int | None

Number of quarters

None
months int | None

Number of months

None
weeks int | None

Number of weeks

None
days int | None

Number of days

None
hours int | None

Number of hours

None
minutes int | None

Number of minutes

None
seconds int | None

Number of seconds

None
milliseconds int | None

Number of milliseconds

None
microseconds int | None

Number of microseconds

None
nanoseconds int | None

Number of nanoseconds

None

Returns:

Type Description
ir.IntervalScalar

An interval expression

least(self, *args)

Compute the smallest value among the supplied arguments.

Parameters:

Name Type Description Default
args ir.Value

Arguments to choose from

()

Returns:

Type Description
ir.Value

Minimum of the passed arguments

literal(value, type=None)

Create a scalar expression from a Python value.

Use specific functions for arrays, structs and maps

Ibis supports literal construction of arrays using the following functions:

  1. ibis.array
  2. ibis.struct
  3. ibis.map

Constructing these types using literal will be deprecated in a future release.

Parameters:

Name Type Description Default
value Any

A Python value

required
type dt.DataType | str | None

An instance of DataType or a string indicating the ibis type of value. This parameter can be used in cases where ibis's type inference isn't sufficient for discovering the type of value.

None

Examples:

Construct an integer literal

>>> import ibis
>>> x = ibis.literal(42)
>>> x.type()
Int8(nullable=True)

Construct a float64 literal from an int

>>> y = ibis.literal(42, type='double')
>>> y.type()
Float64(nullable=True)

Ibis checks for invalid types

>>> ibis.literal('foobar', type='int64')
Traceback (most recent call last):
  ...
TypeError: Value 'foobar' cannot be safely coerced to int64

Returns:

Type Description
Scalar

An expression representing a literal value

map(value, type=None)

Create a map literal from a dict or other mapping.

Parameters:

Name Type Description Default
value Iterable[tuple[K, V]] | Mapping[K, V]

the literal map value

required
type str | dt.DataType | None

An instance of ibis.expr.datatypes.DataType or a string indicating the ibis type of value.

None

Examples:

Create a map literal from a dict with the type inferred

>>> import ibis
>>> t = ibis.map(dict(a=1, b=2))

Create a map literal from a dict with the specified type

>>> import ibis
>>> t = ibis.map(dict(a=1, b=2), type='map<string, double>')

Returns:

Type Description
MapValue

An expression representing a literal map (associative array with key/value pairs of fixed types)

negate(self)

Negate a numeric expression.

Returns:

Type Description
NumericValue

A numeric value expression

now()

Return an expression that will compute the current timestamp.

Returns:

Type Description
ir.TimestampScalar

A "now" expression

null()

Create a NULL/NA scalar

or_(*predicates)

Combine multiple predicates using |.

Parameters:

Name Type Description Default
predicates ir.BooleanValue

Boolean value expressions

()

Returns:

Type Description
ir.BooleanValue

A new predicate that evaluates to True if any composing predicates are True. If no predicates were provided, returns False.

param(type)

Create a deferred parameter of a given type.

Parameters:

Name Type Description Default
type dt.DataType

The type of the unbound parameter, e.g., double, int64, date, etc.

required

Examples:

>>> import ibis
>>> start = ibis.param('date')
>>> end = ibis.param('date')
>>> schema = dict(timestamp_col='timestamp', value='double')
>>> t = ibis.table(schema, name='t')
>>> predicates = [t.timestamp_col >= start, t.timestamp_col <= end]
>>> t.filter(predicates).value.sum()
r0 := UnboundTable: t
  timestamp_col timestamp
  value         float64
r1 := Selection[r0]
  predicates:
    r0.timestamp_col >= $(date)
    r0.timestamp_col <= $(date)
sum: Sum(r1.value)

Returns:

Type Description
ir.Scalar

A scalar expression backend by a parameter

show_sql(expr, dialect=None, file=None)

Pretty-print the compiled SQL string of an expression.

If a dialect cannot be inferred and one was not passed, duckdb will be used as the dialect

Parameters:

Name Type Description Default
expr ir.Expr

Ibis expression whose SQL will be printed

required
dialect str | None

String dialect. This is typically not required, but can be useful if ibis cannot infer the backend dialect.

None
file IO[str] | None

File to write output to

None

Examples:

>>> import ibis
>>> from ibis import _
>>> t = ibis.table(dict(a="int"), name="t")
>>> expr = t.select(c=_.a * 2)
>>> ibis.show_sql(expr)  # duckdb dialect by default
SELECT
  t0.a * CAST(2 AS SMALLINT) AS c
FROM t AS t0
>>> ibis.show_sql(expr, dialect="mysql")
SELECT
  t0.a * 2 AS c
FROM t AS t0

to_sql(expr, dialect=None)

Return the formatted SQL string for an expression.

Parameters:

Name Type Description Default
expr ir.Expr

Ibis expression.

required
dialect str | None

SQL dialect to use for compilation.

None

Returns:

Type Description
str

Formatted SQL string

random()

Return a random floating point number in the range [0.0, 1.0).

Similar to random.random in the Python standard library.

Returns:

Type Description
FloatingScalar

Random float value expression

range_window(preceding=None, following=None, group_by=None, order_by=None)

Create a range-based window clause for use with window functions.

This RANGE window clause aggregates rows based upon differences in the value of the order-by expression.

All window frames / ranges are inclusive.

Parameters:

Name Type Description Default
preceding None

Number of preceding rows in the window

None
following None

Number of following rows in the window

None
group_by None

Grouping key

None
order_by None

Ordering key

None

Returns:

Type Description
Window

A window frame

row_number()

Return an analytic function expression for the current row number.

Returns:

Type Description
ir.IntegerColumn

A column expression enumerating rows

schema(pairs=None, names=None, types=None)

Validate and return a Schema object.

Parameters:

Name Type Description Default
pairs SupportsSchema | None

List or dictionary of name, type pairs. Mutually exclusive with names and types arguments.

None
names Iterable[str] | None

Field names. Mutually exclusive with pairs.

None
types Iterable[str | dt.DataType] | None

Field types. Mutually exclusive with pairs.

None

Examples:

>>> from ibis import schema, Schema
>>> sc = schema([('foo', 'string'),
...              ('bar', 'int64'),
...              ('baz', 'boolean')])
>>> sc = schema(names=['foo', 'bar', 'baz'],
...             types=['string', 'int64', 'boolean'])
>>> sc = schema(dict(foo="string"))
>>> sc = schema(Schema(['foo'], ['string']))  # no-op

Returns:

Type Description
sch.Schema

An ibis schema

struct(value, type=None)

Create a struct literal from a dict or other mapping.

Parameters:

Name Type Description Default
value Iterable[tuple[str, V]] | Mapping[str, V]

The underlying data for literal struct value

required
type str | dt.DataType | None

An instance of ibis.expr.datatypes.DataType or a string indicating the ibis type of value.

None

Examples:

Create a struct literal from a dict with the type inferred

>>> import ibis
>>> t = ibis.struct(dict(a=1, b='foo'))

Create a struct literal from a dict with a specified type

>>> t = ibis.struct(dict(a=1, b='foo'), type='struct<a: float, b: string>')

Returns:

Type Description
StructValue

An expression representing a literal struct (compound type with fields of fixed types)

table(schema=None, name=None)

Create a table literal or an abstract table without data.

Parameters:

Name Type Description Default
schema SupportsSchema | None

A schema for the table

None
name str | None

Name for the table. One is generated if this value is None.

None

Examples:

Create a table with no data backing it

>>> t = ibis.table(schema=dict(a="int", b="string"))
>>> t
UnboundTable: unbound_table_0
  a int64
  b string

Returns:

Type Description
ir.Table

A table expression

time(value)

timestamp(value, *args, *, timezone=None)

Construct a timestamp literal if value is coercible to a timestamp.

Parameters:

Name Type Description Default
value None

The value to use for constructing the timestamp

required
timezone str | None

The timezone of the timestamp

None

Returns:

Type Description
ir.TimestampScalar

A timestamp expression

trailing_range_window(preceding, order_by, group_by=None)

Create a trailing range window for use with window functions.

Parameters:

Name Type Description Default
preceding None

A value expression

required
order_by None

Ordering key

required
group_by None

Grouping key

None

Returns:

Type Description
Window

A window frame

trailing_window(preceding, group_by=None, order_by=None)

Create a trailing window for use with aggregate window functions.

Parameters:

Name Type Description Default
preceding None

The number of preceding rows

required
group_by None

Grouping key

None
order_by None

Ordering key

None

Returns:

Type Description
Window

A window frame

union(self, *tables, *, distinct=False, **kwargs)

Compute the set union of multiple table expressions.

The input tables must have identical schemas.

Parameters:

Name Type Description Default
*tables Table

One or more table expressions

()
distinct bool

Only return distinct rows

False

Returns:

Type Description
Table

A new table containing the union of all input tables.

where(self, true_expr, false_expr)

Construct a ternary conditional expression.

Parameters:

Name Type Description Default
true_expr ir.Value

Expression to return if self evaluates to True

required
false_expr ir.Value

Expression to return if self evaluates to False

required

Examples:

>>> import ibis
>>> t = ibis.table([("is_person", "boolean")], name="t")
>>> expr = t.is_person.ifelse("yes", "no")
>>> print(ibis.impala.compile(expr))
SELECT CASE WHEN `is_person` THEN 'yes' ELSE 'no' END AS `tmp`
FROM t

Returns:

Type Description
ir.Value

The value of true_expr if arg is True else false_expr

window(preceding=None, following=None, group_by=None, order_by=None)

Create a window clause for use with window functions.

The ROWS window clause includes peer rows based on differences in row number whereas RANGE includes rows based on the differences in row value of a single order_by expression.

All window frame bounds are inclusive.

Parameters:

Name Type Description Default
preceding None

Number of preceding rows in the window

None
following None

Number of following rows in the window

None
group_by None

Grouping key

None
order_by None

Ordering key

None

Returns:

Type Description
Window

A window frame


Last update: September 13, 2022