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
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

Returns:

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

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])

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').order_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.ValueExpr	An expression

case()

Begin constructing a case expression.

Use the .when method on the resulting object followed by .end to create a complete case.

Examples:

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

Returns:

Type	Description
SearchedCaseBuilder	A builder object to use for constructing a case expression.

coalesce = _deferred(ir.Value.coalesce) module-attribute

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		Grouping key	None
order_by		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		Date string	required

Returns:

Type	Description
DateScalar	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').order_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.ValueExpr	An expression

difference = ir.Table.difference module-attribute

greatest = _deferred(ir.Value.greatest) module-attribute

ifelse = _deferred(ir.BooleanValue.ifelse) module-attribute

intersect = ir.Table.intersect module-attribute

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
IntervalScalar	An interval expression

least = _deferred(ir.Value.least) module-attribute

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

Returns:

Type	Description
Scalar	An expression representing a literal value

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

map(keys, values)

Create a map literal from a dict or other mapping.

Parameters:

Name	Type	Description	Default
keys		Keys of the map	required
values		Values of the map	required

Returns:

Type	Description
MapValue	An expression representing either a map column or literal (associative array with key/value pairs of fixed types)

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>')

negate = ir.NumericValue.negate module-attribute

now()

Return an expression that will compute the current timestamp.

Returns:

Type	Description
TimestampScalar	An expression representing the current timestamp.

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
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

Returns:

Type	Description
Scalar	A scalar expression backend by a parameter

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)

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		Number of preceding rows in the window	None
following		Number of following rows in the window	None
group_by		Grouping key	None
order_by		Ordering key	None

Returns:

Type	Description
Window	A window frame

read_csv(sources, **kwargs)

Lazily load a CSV or set of CSVs.

Parameters:

Name	Type	Description	Default
sources	str | Path	A filesystem path or URL or list of same. Supports CSV and TSV files.	required
kwargs	Any	DuckDB-specific keyword arguments for the file type. CSV/TSV: https://duckdb.org/docs/data/csv#parameters.	{}

Returns:

Type	Description
ir.Table	Table expression representing a file

Examples:

>>> batting = ibis.read_csv("ci/ibis-testing-data/batting.csv")

read_parquet(sources, **kwargs)

Lazily load a parquet file or set of parquet files.

Parameters:

Name	Type	Description	Default
sources	str | Path	A filesystem path or URL or list of same.	required
kwargs	Any	DuckDB-specific keyword arguments for the file type. Parquet: https://duckdb.org/docs/data/parquet	{}

Returns:

Type	Description
ir.Table	Table expression representing a file

Examples:

>>> batting = ibis.read_parquet("ci/ibis-testing-data/parquet/batting/batting.parquet")

row_number()

Return an analytic function expression for the current row number.

Returns:

Type	Description
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
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

Returns:

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

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>')

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

Returns:

Type	Description
Table	A table expression

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

time(value)

timestamp(value, *args, timezone=None)

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

Parameters:

Name	Type	Description	Default
value		The value to use for constructing the timestamp	required
args		Additional arguments used when constructing a timestamp	()
timezone	str | None	The timezone of the timestamp	None

Returns:

Type	Description
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		A value expression	required
order_by		Ordering key	required
group_by		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		The number of preceding rows	required
group_by		Grouping key	None
order_by		Ordering key	None

Returns:

Type	Description
Window	A window frame

union = ir.Table.union module-attribute

where = _deferred(ir.BooleanValue.ifelse) module-attribute

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		Number of preceding rows in the window	None
following		Number of following rows in the window	None
group_by		Grouping key	None
order_by		Ordering key	None

Returns:

Type	Description
Window	A window frame

---

Last update: December 9, 2022