Top-level APIs

Methods and objects available directly on the ibis module.

and_

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

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
>>> ibis.options.interactive = True
>>> t = ibis.memtable({"a": [1, 2, 3], "b": [4, 5, 6]})
>>> ibis.array([t.a, t.b])

┏━━━━━━━━━━━━━━━┓
┃ ArrayColumn() ┃
┡━━━━━━━━━━━━━━━┩
│ array<int64>  │
├───────────────┤
│ [1, 4]        │
│ [2, 5]        │
│ [3, 6]        │
└───────────────┘

Create an array scalar from Python literals

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

[1.0, 2.0, 3.0]

Mixing scalar and column expressions is allowed

>>> ibis.array([t.a, 42])

┏━━━━━━━━━━━━━━━┓
┃ ArrayColumn() ┃
┡━━━━━━━━━━━━━━━┩
│ array<int64>  │
├───────────────┤
│ [1, 42]       │
│ [2, 42]       │
│ [3, 42]       │
└───────────────┘

asc

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
>>> ibis.options.interactive = True
>>> t = ibis.examples.penguins.fetch()
>>> t[["species", "year"]].order_by(ibis.asc("year")).head()

┏━━━━━━━━━┳━━━━━━━┓
┃ species ┃ year  ┃
┡━━━━━━━━━╇━━━━━━━┩
│ string  │ int64 │
├─────────┼───────┤
│ Adelie  │  2007 │
│ Adelie  │  2007 │
│ Adelie  │  2007 │
│ Adelie  │  2007 │
│ Adelie  │  2007 │
└─────────┴───────┘

Returns

Type	Description
ir.ValueExpr	An expression

case

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()
>>> expr

3

Returns

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

coalesce

expr.api.coalesce

connect

backends.base.connect(resource, **kwargs)

Connect to resource, inferring the backend automatically.

The general pattern for ibis.connect is

ibis.connect("backend://connection-parameters")

With many backends that looks like

ibis.connect("backend://user:password@host:port/database")

See the connection syntax for each backend for details about URL connection requirements.

Parameters

Name	Type	Description	Default
resource	Path | str	A URL or path to the resource to be connected to.	required
kwargs	Any	Backend specific keyword arguments	{}

Examples

Connect to an in-memory DuckDB database:

>>> con = ibis.connect("duckdb://")

Connect to an on-disk SQLite database:

>>> con = ibis.connect("sqlite://relative.db")
>>> con = ibis.connect("sqlite:///absolute/path/to/data.db")

Connect to a PostgreSQL server:

>>> con = ibis.connect(
...     "postgres://user:password@hostname:5432"
... )  # quartodoc: +SKIP

Connect to BigQuery:

>>> con = ibis.connect(
...     "bigquery://my-project/my-dataset"
... )  # quartodoc: +SKIP

cumulative_window

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

date(value, *args)

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

Parameters

Name	Type	Description	Default
value		Date string, datetime object or numeric value	required
args		Month and day if value is a year	()

Returns

Type	Description
DateScalar	A date expression

desc

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
>>> ibis.options.interactive = True
>>> t = ibis.examples.penguins.fetch()
>>> t[["species", "year"]].order_by(ibis.desc("year")).head()

┏━━━━━━━━━┳━━━━━━━┓
┃ species ┃ year  ┃
┡━━━━━━━━━╇━━━━━━━┩
│ string  │ int64 │
├─────────┼───────┤
│ Adelie  │  2009 │
│ Adelie  │  2009 │
│ Adelie  │  2009 │
│ Adelie  │  2009 │
│ Adelie  │  2009 │
└─────────┴───────┘

Returns

Type	Description
ir.ValueExpr	An expression

difference

difference(table, *rest, distinct=True)

Compute the set difference of multiple table expressions.

The input tables must have identical schemas.

Parameters

Name	Type	Description	Default
table	ir.Table	A table expression	required
*rest	ir.Table	Additional 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.

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> t1 = ibis.memtable({"a": [1, 2]})
>>> t1

┏━━━━━━━┓
┃ a     ┃
┡━━━━━━━┩
│ int64 │
├───────┤
│     1 │
│     2 │
└───────┘

>>> t2 = ibis.memtable({"a": [2, 3]})
>>> t2

┏━━━━━━━┓
┃ a     ┃
┡━━━━━━━┩
│ int64 │
├───────┤
│     2 │
│     3 │
└───────┘

>>> ibis.difference(t1, t2)

┏━━━━━━━┓
┃ a     ┃
┡━━━━━━━┩
│ int64 │
├───────┤
│     1 │
└───────┘

dtype

dtype(value, nullable=True)

Create a DataType object.

Parameters

Name	Type	Description	Default
value	Any	The object to coerce to an Ibis DataType. Supported inputs include strings, python type annotations, numpy dtypes, pandas dtypes, and pyarrow types.	required
nullable	bool	Whether the type should be nullable. Defaults to True.	True

Examples

>>> import ibis
>>> ibis.dtype("int32")

Int32(nullable=True)

>>> ibis.dtype("array<float>")

Array(value_type=Float64(nullable=True), nullable=True)

DataType objects may also be created from Python types:

>>> ibis.dtype(int)

Int64(nullable=True)

>>> ibis.dtype(list[float])

Array(value_type=Float64(nullable=True), nullable=True)

Or other type systems, like numpy/pandas/pyarrow types:

>>> import pyarrow as pa
>>> ibis.dtype(pa.int32())

Int32(nullable=True)

get_backend

get_backend(expr=None)

Get the current Ibis backend to use for a given expression.

expr An expression to get the backend from. If not passed, the default backend is returned.

Returns

Type	Description
BaseBackend	The Ibis backend.

greatest

expr.api.greatest

ifelse

expr.api.ifelse

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 or NULL	required

Returns

Type	Description
ir.Value	The value of true_expr if arg is True else false_expr

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.memtable({"is_person": [True, False, True, None]})
>>> ibis.ifelse(t.is_person, "yes", "no")

┏━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ Where(is_person, 'yes', 'no') ┃
┡━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩
│ string                        │
├───────────────────────────────┤
│ yes                           │
│ no                            │
│ yes                           │
│ no                            │
└───────────────────────────────┘

intersect

intersect(table, *rest, distinct=True)

Compute the set intersection of multiple table expressions.

The input tables must have identical schemas.

Parameters

Name	Type	Description	Default
table	ir.Table	A table expression	required
*rest	ir.Table	Additional table expressions	()
distinct	bool	Only return distinct rows	True

Returns

Type	Description
Table	A new table containing the intersection of all input tables.

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> t1 = ibis.memtable({"a": [1, 2]})
>>> t1

┏━━━━━━━┓
┃ a     ┃
┡━━━━━━━┩
│ int64 │
├───────┤
│     1 │
│     2 │
└───────┘

>>> t2 = ibis.memtable({"a": [2, 3]})
>>> t2

┏━━━━━━━┓
┃ a     ┃
┡━━━━━━━┩
│ int64 │
├───────┤
│     2 │
│     3 │
└───────┘

>>> ibis.intersect(t1, t2)

┏━━━━━━━┓
┃ a     ┃
┡━━━━━━━┩
│ int64 │
├───────┤
│     2 │
└───────┘

interval

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

expr.api.least

literal

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

TypeError: Unable to normalize 'foobar' to Int64(nullable=True)

map

map(keys, values=None)

Create a map container object.

If the keys and values are Python literals, then the output will be a MapScalar. If the keys and values are expressions (ArrayColumn), then the the output will be a MapColumn.

Parameters

Name	Type	Description	Default
keys	Iterable[Any] | Mapping[Any, Any] | ArrayColumn	Keys of the map or Mapping. If keys is a Mapping, values must be None.	required
values	Iterable[Any] | ArrayColumn | None	Values of the map or None. If None, the keys argument must be a Mapping.	None

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
>>> ibis.options.interactive = True
>>> ibis.map(dict(a=1, b=2))

{'a': 1, 'b': 2}

Create a new map column from columns with keys and values

>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.memtable({"keys": [["a", "b"], ["b"]], "values": [[1, 2], [3]]})
>>> t

┏━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━┓
┃ keys          ┃ values       ┃
┡━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━┩
│ array<string> │ array<int64> │
├───────────────┼──────────────┤
│ ['a', 'b']    │ [1, 2]       │
│ ['b']         │ [3]          │
└───────────────┴──────────────┘

>>> ibis.map(t.keys, t.values)

┏━━━━━━━━━━━━━━━━━━━━┓
┃ Map(keys, values)  ┃
┡━━━━━━━━━━━━━━━━━━━━┩
│ map<string, int64> │
├────────────────────┤
│ {'a': 1, 'b': 2}   │
│ {'b': 3}           │
└────────────────────┘

memtable

memtable(data, *, columns=None, schema=None, name=None)

Construct an ibis table expression from in-memory data.

Parameters

Name	Type	Description	Default
data		Any data accepted by the pandas.DataFrame constructor or a pyarrow.Table. Examples of acceptable objects are a pandas.DataFrame, a pyarrow.Table, a list of dicts of non-ibis Python objects, etc. ibis objects, like MapValue, will result in an error. Do not depend on the underlying storage type (e.g., pyarrow.Table), it’s subject to change across non-major releases.	required
columns	Iterable[str] | None	Optional typing.Iterable of str column names.	None
schema	SupportsSchema | None	Optional Schema. The functions use data to infer a schema if not passed.	None
name	str | None	Optional name of the table.	None

Returns

Type	Description
Table	A table expression backed by in-memory data.

Examples

>>> import ibis
>>> t = ibis.memtable([{"a": 1}, {"a": 2}])
>>> t

┏━━━━━━━┓
┃ a     ┃
┡━━━━━━━┩
│ int64 │
├───────┤
│     1 │
│     2 │
└───────┘

>>> t = ibis.memtable([{"a": 1, "b": "foo"}, {"a": 2, "b": "baz"}])
>>> t

┏━━━━━━━┳━━━━━━━━┓
┃ a     ┃ b      ┃
┡━━━━━━━╇━━━━━━━━┩
│ int64 │ string │
├───────┼────────┤
│     1 │ foo    │
│     2 │ baz    │
└───────┴────────┘

Create a table literal without column names embedded in the data and pass columns

>>> t = ibis.memtable([(1, "foo"), (2, "baz")], columns=["a", "b"])
>>> t

┏━━━━━━━┳━━━━━━━━┓
┃ a     ┃ b      ┃
┡━━━━━━━╇━━━━━━━━┩
│ int64 │ string │
├───────┼────────┤
│     1 │ foo    │
│     2 │ baz    │
└───────┴────────┘

Create a table literal without column names embedded in the data. Ibis generates column names if none are provided.

>>> t = ibis.memtable([(1, "foo"), (2, "baz")])
>>> t

┏━━━━━━━┳━━━━━━━━┓
┃ col0  ┃ col1   ┃
┡━━━━━━━╇━━━━━━━━┩
│ int64 │ string │
├───────┼────────┤
│     1 │ foo    │
│     2 │ baz    │
└───────┴────────┘

negate

expr.api.negate

NA

expr.api.NA

The NULL scalar.

Examples

>>> import ibis
>>> my_null = ibis.NA
>>> my_null.isnull()

True

now

now()

Return an expression that will compute the current timestamp.

Returns

Type	Description
TimestampScalar	An expression representing the current timestamp.

null

null()

Create a NULL/NA scalar.

or_

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

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

IbisError: Expression contains unbound tables and therefore cannot be executed. Use ibis.<backend>.execute(expr) or assign a backend instance to `ibis.options.default_backend`.

show_sql

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 TINYINT) AS c
FROM t AS t0

>>> ibis.show_sql(expr, dialect="mysql")

SELECT
  t0.a * 2 AS c
FROM t AS t0

to_sql

to_sql(expr, dialect=None, **kwargs)

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
kwargs		Scalar parameters	{}

Returns

Type	Description
str	Formatted SQL string

random

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

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

read_csv(sources, table_name=None, **kwargs)

Lazily load a CSV or set of CSVs.

This function delegates to the read_csv method on the current default backend (DuckDB or ibis.config.default_backend).

Parameters

Name	Type	Description	Default
sources	str | Path | Sequence[str | Path]	A filesystem path or URL or list of same. Supports CSV and TSV files.	required
table_name	str | None	A name to refer to the table. If not provided, a name will be generated.	None
kwargs	Any	Backend-specific keyword arguments for the file type. For the DuckDB backend used by default, please refer to: * CSV/TSV: https://duckdb.org/docs/data/csv#parameters.	{}

Returns

Type	Description
ir.Table	Table expression representing a file

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> lines = '''a,b
... 1,d
... 2,
... ,f
... '''
>>> with open("/tmp/lines.csv", mode="w") as f:
...     _ = f.write(lines)
...
>>> t = ibis.read_csv("/tmp/lines.csv")
>>> t

┏━━━━━━━┳━━━━━━━━┓
┃ a     ┃ b      ┃
┡━━━━━━━╇━━━━━━━━┩
│ int64 │ string │
├───────┼────────┤
│     1 │ d      │
│     2 │ NULL   │
│  NULL │ f      │
└───────┴────────┘

read_delta

read_delta(source, table_name=None, **kwargs)

Lazily load a Delta Lake table.

Parameters

Name	Type	Description	Default
source	str | Path	A filesystem path or URL.	required
table_name	str | None	A name to refer to the table. If not provided, a name will be generated.	None
kwargs	Any	Backend-specific keyword arguments for the file type.	{}

Returns

Type	Description
ir.Table	Table expression representing a file

Examples

>>> import ibis
>>> import pandas as pd
>>> ibis.options.interactive = True
>>> df = pd.DataFrame({"a": [1, 2, 3], "b": list("ghi")})
>>> df

	a	b
0	1	g
1	2	h
2	3	i

>>> import deltalake as dl
>>> dl.write_deltalake("/tmp/data.delta", df, mode="overwrite")
>>> t = ibis.read_delta("/tmp/data.delta")
>>> t

┏━━━━━━━┳━━━━━━━━┓
┃ a     ┃ b      ┃
┡━━━━━━━╇━━━━━━━━┩
│ int64 │ string │
├───────┼────────┤
│     1 │ g      │
│     2 │ h      │
│     3 │ i      │
└───────┴────────┘

read_json

read_json(sources, table_name=None, **kwargs)

Lazily load newline-delimited JSON data.

This API is experimental and subject to change.

This function delegates to the read_json method on the current default backend (DuckDB or ibis.config.default_backend).

Parameters

Name	Type	Description	Default
sources	str | Path | Sequence[str | Path]	A filesystem path or URL or list of same.	required
table_name	str | None	A name to refer to the table. If not provided, a name will be generated.	None
kwargs	Any	Backend-specific keyword arguments for the file type. See https://duckdb.org/docs/extensions/json.html for details.	{}

Returns

Type	Description
ir.Table	Table expression representing a file

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> lines = '''
... {"a": 1, "b": "d"}
... {"a": 2, "b": null}
... {"a": null, "b": "f"}
... '''
>>> with open("/tmp/lines.json", mode="w") as f:
...     _ = f.write(lines)
...
>>> t = ibis.read_json("/tmp/lines.json")
>>> t

┏━━━━━━━┳━━━━━━━━┓
┃ a     ┃ b      ┃
┡━━━━━━━╇━━━━━━━━┩
│ int64 │ string │
├───────┼────────┤
│     1 │ d      │
│     2 │ NULL   │
│  NULL │ f      │
└───────┴────────┘

read_parquet

read_parquet(sources, table_name=None, **kwargs)

Lazily load a parquet file or set of parquet files.

This function delegates to the read_parquet method on the current default backend (DuckDB or ibis.config.default_backend).

Parameters

Name	Type	Description	Default
sources	str | Path | Sequence[str | Path]	A filesystem path or URL or list of same.	required
table_name	str | None	A name to refer to the table. If not provided, a name will be generated.	None
kwargs	Any	Backend-specific keyword arguments for the file type. For the DuckDB backend used by default, please refer to: * Parquet: https://duckdb.org/docs/data/parquet	{}

Returns

Type	Description
ir.Table	Table expression representing a file

Examples

>>> import ibis
>>> import pandas as pd
>>> ibis.options.interactive = True
>>> df = pd.DataFrame({"a": [1, 2, 3], "b": list("ghi")})
>>> df

	a	b
0	1	g
1	2	h
2	3	i

>>> df.to_parquet("/tmp/data.parquet")
>>> t = ibis.read_parquet("/tmp/data.parquet")
>>> t

┏━━━━━━━┳━━━━━━━━┓
┃ a     ┃ b      ┃
┡━━━━━━━╇━━━━━━━━┩
│ int64 │ string │
├───────┼────────┤
│     1 │ g      │
│     2 │ h      │
│     3 │ i      │
└───────┴────────┘

row_number

row_number()

Return an analytic function expression for the current row number.

Returns

Type	Description
IntegerColumn	A column expression enumerating rows

schema

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

Returns

Type	Description
Schema	An ibis schema

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(dict(foo="string")))  # no-op

set_backend

set_backend(backend)

Set the default Ibis backend.

Parameters

Name	Type	Description	Default
backend	str | BaseBackend	May be a backend name or URL, or an existing backend instance.	required

Examples

You can pass the backend as a name:

>>> import ibis
>>> ibis.set_backend("polars")

Or as a URI

>>> ibis.set_backend(
...     "postgres://user:password@hostname:5432"
... )  # quartodoc: +SKIP

Or as an existing backend instance

>>> ibis.set_backend(ibis.duckdb.connect())

struct

struct(value, type=None)

Create a struct expression.

If the input expressions are all column expressions, then the output will be a StructColumn.

If the input expressions are Python literals, then the output will be a StructScalar.

Parameters

Name	Type	Description	Default
value	Iterable[tuple[str, V]] | Mapping[str, V]	The underlying data for literal struct value or a pairs of field names and column expressions.	required
type	str | dt.DataType | None	An instance of ibis.expr.datatypes.DataType or a string indicating the ibis type of value. This is only used if all of the input values are literals.	None

Returns

Type	Description
StructValue	An expression representing a literal or column 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>")

Specify a specific type for the struct literal

>>> t = ibis.struct(dict(a=1, b=40), type="struct<a: float, b: int32>")

Create a struct array from multiple arrays

>>> ibis.options.interactive = True
>>> t = ibis.memtable({"a": [1, 2, 3], "b": ["foo", "bar", "baz"]})
>>> ibis.struct([("a", t.a), ("b", t.b)])

┏━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ StructColumn()              ┃
┡━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩
│ struct<a: int64, b: string> │
├─────────────────────────────┤
│ {'a': 1, 'b': 'foo'}        │
│ {'a': 2, 'b': 'bar'}        │
│ {'a': 3, 'b': 'baz'}        │
└─────────────────────────────┘

Create a struct array from columns and literals

>>> ibis.struct([("a", t.a), ("b", "foo")])

┏━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ StructColumn()              ┃
┡━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩
│ struct<a: int64, b: string> │
├─────────────────────────────┤
│ {'a': 1, 'b': 'foo'}        │
│ {'a': 2, 'b': 'foo'}        │
│ {'a': 3, 'b': 'foo'}        │
└─────────────────────────────┘

table

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

>>> import ibis
>>> ibis.options.interactive

True

>>> t = ibis.table(schema=dict(a="int", b="string"), name="t")
>>> t

IbisError: Expression contains unbound tables and therefore cannot be executed. Use ibis.<backend>.execute(expr) or assign a backend instance to `ibis.options.default_backend`.

IbisError: Expression contains unbound tables and therefore cannot be executed. Use ibis.<backend>.execute(expr) or assign a backend instance to `ibis.options.default_backend`.

time

time(value, *args)

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

Parameters

Name	Type	Description	Default
value		Time string	required
args		Minutes, seconds if value is an hour	()

Returns

Type	Description
TimeScalar	A time expression

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> ibis.time("00:00:00")

datetime.time(0, 0)

>>> ibis.time(12, 15, 30)

datetime.time(12, 15, 30)

timestamp

timestamp(value, *args, timezone=None)

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

Parameters

Name	Type	Description	Default
value		Timestamp string, datetime object or numeric value	required
args		Additional arguments if value is numeric	()
timezone	str | None	Timezone name	None

Returns

Type	Description
TimestampScalar	A timestamp expression

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> ibis.timestamp("2021-01-01 00:00:00")

Timestamp('2021-01-01 00:00:00')

trailing_range_window

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

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

Create a trailing window for use with 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

union(table, *rest, distinct=False)

Compute the set union of multiple table expressions.

The input tables must have identical schemas.

Parameters

Name	Type	Description	Default
table	ir.Table	A table expression	required
*rest	ir.Table	Additional table expressions	()
distinct	bool	Only return distinct rows	False

Returns

Type	Description
Table	A new table containing the union of all input tables.

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> t1 = ibis.memtable({"a": [1, 2]})
>>> t1

┏━━━━━━━┓
┃ a     ┃
┡━━━━━━━┩
│ int64 │
├───────┤
│     1 │
│     2 │
└───────┘

>>> t2 = ibis.memtable({"a": [2, 3]})
>>> t2

┏━━━━━━━┓
┃ a     ┃
┡━━━━━━━┩
│ int64 │
├───────┤
│     2 │
│     3 │
└───────┘

>>> ibis.union(t1, t2)  # union all by default

┏━━━━━━━┓
┃ a     ┃
┡━━━━━━━┩
│ int64 │
├───────┤
│     1 │
│     2 │
│     2 │
│     3 │
└───────┘

>>> ibis.union(t1, t2, distinct=True).order_by("a")

┏━━━━━━━┓
┃ a     ┃
┡━━━━━━━┩
│ int64 │
├───────┤
│     1 │
│     2 │
│     3 │
└───────┘

where

expr.api.where

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 or NULL	required

Returns

Type	Description
ir.Value	The value of true_expr if arg is True else false_expr

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.memtable({"is_person": [True, False, True, None]})
>>> ibis.where(t.is_person, "yes", "no")

┏━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ Where(is_person, 'yes', 'no') ┃
┡━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩
│ string                        │
├───────────────────────────────┤
│ yes                           │
│ no                            │
│ yes                           │
│ no                            │
└───────────────────────────────┘

window

window(preceding=None, following=None, order_by=None, group_by=None, *, rows=None, range=None, between=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
rows		Whether to use the ROWS window clause	None
range		Whether to use the RANGE window clause	None
between		Automatically infer the window kind based on the boundaries	None

Returns

Type	Description
Window	A window frame