Generic expressions

Scalars and columns of any element type.

Value

Value(self, arg)

Base class for a data generating expression having a known type.

Methods

Name	Description
asc	Sort an expression in ascending order.
between	Check if this expression is between `lower` and `upper`, inclusive.
case	DEPRECATED: use `value.cases()` or `ibis.cases()` instead.
cases	Create a multi-branch if-else expression.
cast	Cast expression to indicated data type.
coalesce	Return the first non-null value from `args`.
collect	Aggregate this expression’s elements into an array.
desc	Sort an expression in descending order.
fill_null	Replace `NULL`s with the given value. Does NOT affect `NaN` and `inf` values.
fillna	DEPRECATED: use `fill_null` instead, which acts exactly the same.
group_concat	Concatenate values using the indicated separator to produce a string.
hash	Compute an integer hash value.
identical_to	Return whether this expression is identical to other.
isin	Check whether this expression’s values are in `values`.
isnull	Whether this expression is `NULL`. Does NOT detect `NaN` and `inf` values.
name	Rename an expression to `name`.
notin	Check whether this expression’s values are not in `values`.
notnull	Return whether this expression is not NULL.
nullif	Set values to null if they equal the values `null_if_expr`.
over	Construct a window expression.
substitute	Replace values given in `values` with `replacement`.
to_pandas	Convert a table expression to a pandas DataFrame.
try_cast	Try cast expression to indicated data type.
type	Return the DataType of `self`.
typeof	Return the string name of the datatype of self.

asc

asc(nulls_first=False)

Sort an expression in ascending order.

Parameters

Name	Type	Description	Default
nulls_first	bool	Whether to sort `NULL` values first	`False`

Returns

Name	Type	Description
	Value	Sorted expression

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.memtable({"a": [1, 2, 3, None]})
>>> t.order_by(t.a.asc())

┏━━━━━━━━━┓
┃ a       ┃
┡━━━━━━━━━┩
│ float64 │
├─────────┤
│     1.0 │
│     2.0 │
│     3.0 │
│    NULL │
└─────────┘

>>> t.order_by(t.a.asc(nulls_first=True))

┏━━━━━━━━━┓
┃ a       ┃
┡━━━━━━━━━┩
│ float64 │
├─────────┤
│    NULL │
│     1.0 │
│     2.0 │
│     3.0 │
└─────────┘

between

between(lower, upper)

Check if this expression is between lower and upper, inclusive.

Parameters

Name	Type	Description	Default
lower	Value	Lower bound, inclusive	required
upper	Value	Upper bound, inclusive	required

Returns

Name	Type	Description
	BooleanValue	Expression indicating membership in the provided range

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.examples.penguins.fetch().limit(5)
>>> t.bill_length_mm.between(35, 38)

┏━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ Between(bill_length_mm, 35, 38) ┃
┡━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩
│ boolean                         │
├─────────────────────────────────┤
│ False                           │
│ False                           │
│ False                           │
│ NULL                            │
│ True                            │
└─────────────────────────────────┘

case

case()

DEPRECATED: use value.cases() or ibis.cases() instead.

cases

cases(branch, /, *branches, else_=None)

Create a multi-branch if-else expression.

Equivalent to a SQL CASE statement.

Parameters

Name	Type	Description	Default
branch	tuple[Value, Value]	First (`condition`, `result`) pair. Required.	required
branches	tuple[Value, Value]	Additional (`condition`, `result`) pairs. We look through the test values in order and return the result corresponding to the first test value that matches `self`. If none match, we return `else_`.	`()`
else_	Value \| None	Value to return if none of the case conditions evaluate to `True`. Defaults to `NULL`.	`None`

Returns

Name	Type	Description
	Value	A value expression

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.memtable(
...     {
...         "left": [5, 6, 7, 8, 9, 10],
...         "symbol": ["+", "-", "*", "/", "bogus", None],
...         "right": [1, 2, 3, 4, 5, 6],
...     }
... )

Note that we never hit the None case, because x = NULL is always NULL, which is not truthy. If you want to replace NULLs, you should use .fill_null(some_value) prior to cases().

>>> t.mutate(
...     result=(
...         t.symbol.cases(
...             ("+", t.left + t.right),
...             ("-", t.left - t.right),
...             ("*", t.left * t.right),
...             ("/", t.left / t.right),
...             (None, -999),
...         )
...     )
... )

┏━━━━━━━┳━━━━━━━━┳━━━━━━━┳━━━━━━━━━┓
┃ left  ┃ symbol ┃ right ┃ result  ┃
┡━━━━━━━╇━━━━━━━━╇━━━━━━━╇━━━━━━━━━┩
│ int64 │ string │ int64 │ float64 │
├───────┼────────┼───────┼─────────┤
│     5 │ +      │     1 │     6.0 │
│     6 │ -      │     2 │     4.0 │
│     7 │ *      │     3 │    21.0 │
│     8 │ /      │     4 │     2.0 │
│     9 │ bogus  │     5 │    NULL │
│    10 │ NULL   │     6 │    NULL │
└───────┴────────┴───────┴─────────┘

cast

cast(target_type, /)

Cast expression to indicated data type.

Similar to pandas.Series.astype.

Parameters

Name	Type	Description	Default
target_type	Any	Type to cast to. Anything accepted by `ibis.dtype()`	required

Returns

Name	Type	Description
	Value	Casted expression

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> x = ibis.examples.penguins.fetch()["bill_depth_mm"]
>>> x

┏━━━━━━━━━━━━━━━┓
┃ bill_depth_mm ┃
┡━━━━━━━━━━━━━━━┩
│ float64       │
├───────────────┤
│          18.7 │
│          17.4 │
│          18.0 │
│          NULL │
│          19.3 │
│          20.6 │
│          17.8 │
│          19.6 │
│          18.1 │
│          20.2 │
│             … │
└───────────────┘

python’s built-in types can be used

>>> x.cast(int)

┏━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ Cast(bill_depth_mm, int64) ┃
┡━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩
│ int64                      │
├────────────────────────────┤
│                         19 │
│                         17 │
│                         18 │
│                       NULL │
│                         19 │
│                         21 │
│                         18 │
│                         20 │
│                         18 │
│                         20 │
│                          … │
└────────────────────────────┘

or string names

>>> x.cast("uint16")

┏━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ Cast(bill_depth_mm, uint16) ┃
┡━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩
│ uint16                      │
├─────────────────────────────┤
│                          19 │
│                          17 │
│                          18 │
│                        NULL │
│                          19 │
│                          21 │
│                          18 │
│                          20 │
│                          18 │
│                          20 │
│                           … │
└─────────────────────────────┘

If you make an illegal cast, you won’t know until the backend actually executes it. Consider .try_cast().

>>> ibis.literal("a string").cast("int64")

---------------------------------------------------------------------------
ConversionException                       Traceback (most recent call last)
File /nix/store/2z5myi8rsjx9n2z3vyip1apb69ncy480-ibis-3.12/lib/python3.12/site-packages/IPython/core/formatters.py:770, in PlainTextFormatter.__call__(self, obj)
    763 stream = StringIO()
    764 printer = pretty.RepresentationPrinter(stream, self.verbose,
    765     self.max_width, self.newline,
    766     max_seq_length=self.max_seq_length,
    767     singleton_pprinters=self.singleton_printers,
    768     type_pprinters=self.type_printers,
    769     deferred_pprinters=self.deferred_printers)
--> 770 printer.pretty(obj)
    771 printer.flush()
    772 return stream.getvalue()

File /nix/store/2z5myi8rsjx9n2z3vyip1apb69ncy480-ibis-3.12/lib/python3.12/site-packages/IPython/lib/pretty.py:411, in RepresentationPrinter.pretty(self, obj)
    400                         return meth(obj, self, cycle)
    401                 if (
    402                     cls is not object
    403                     # check if cls defines __repr__
   (...)    409                     and callable(_safe_getattr(cls, "__repr__", None))
    410                 ):
--> 411                     return _repr_pprint(obj, self, cycle)
    413     return _default_pprint(obj, self, cycle)
    414 finally:

File /nix/store/2z5myi8rsjx9n2z3vyip1apb69ncy480-ibis-3.12/lib/python3.12/site-packages/IPython/lib/pretty.py:786, in _repr_pprint(obj, p, cycle)
    784 """A pprint that just redirects to the normal repr function."""
    785 # Find newlines and replace them with p.break_()
--> 786 output = repr(obj)
    787 lines = output.splitlines()
    788 with p.group():

File ~/work/ibis/ibis/ibis/expr/types/core.py:83, in Expr.__repr__(self)
     81 def __repr__(self) -> str:
     82     if ibis.options.interactive:
---> 83         return _capture_rich_renderable(self)
     84     else:
     85         return self._noninteractive_repr()

File ~/work/ibis/ibis/ibis/expr/types/core.py:63, in _capture_rich_renderable(renderable)
     61 console = Console(force_terminal=False)
     62 with console.capture() as capture:
---> 63     console.print(renderable)
     64 return capture.get().rstrip()

File /nix/store/2z5myi8rsjx9n2z3vyip1apb69ncy480-ibis-3.12/lib/python3.12/site-packages/rich/console.py:1719, in Console.print(self, sep, end, style, justify, overflow, no_wrap, emoji, markup, highlight, width, height, crop, soft_wrap, new_line_start, *objects)
   1717 if style is None:
   1718     for renderable in renderables:
-> 1719         extend(render(renderable, render_options))
   1720 else:
   1721     for renderable in renderables:

File /nix/store/2z5myi8rsjx9n2z3vyip1apb69ncy480-ibis-3.12/lib/python3.12/site-packages/rich/console.py:1320, in Console.render(self, renderable, options)
   1318 renderable = rich_cast(renderable)
   1319 if hasattr(renderable, "__rich_console__") and not isclass(renderable):
-> 1320     render_iterable = renderable.__rich_console__(self, _options)
   1321 elif isinstance(renderable, str):
   1322     text_renderable = self.render_str(
   1323         renderable, highlight=_options.highlight, markup=_options.markup
   1324     )

File ~/work/ibis/ibis/ibis/expr/types/core.py:106, in Expr.__rich_console__(self, console, options)
    103 if opts.interactive:
    104     from ibis.expr.types.pretty import to_rich
--> 106     rich_object = to_rich(self, console_width=console_width)
    107 else:
    108     rich_object = Text(self._noninteractive_repr())

File ~/work/ibis/ibis/ibis/expr/types/pretty.py:275, in to_rich(expr, max_rows, max_columns, max_length, max_string, max_depth, console_width)
    272 from ibis.expr.types import Scalar
    274 if isinstance(expr, Scalar):
--> 275     return _to_rich_scalar(
    276         expr, max_length=max_length, max_string=max_string, max_depth=max_depth
    277     )
    278 else:
    279     return _to_rich_table(
    280         expr,
    281         max_rows=max_rows,
   (...)    286         console_width=console_width,
    287     )

File ~/work/ibis/ibis/ibis/expr/types/pretty.py:297, in _to_rich_scalar(expr, max_length, max_string, max_depth)
    290 def _to_rich_scalar(
    291     expr: Scalar,
    292     *,
   (...)    295     max_depth: int | None = None,
    296 ) -> Pretty:
--> 297     value = expr.to_pyarrow().as_py()
    299     if value is None:
    300         formatted_value = Text.styled("NULL", style="dim")

File ~/work/ibis/ibis/ibis/expr/types/core.py:579, in Expr.to_pyarrow(self, params, limit, **kwargs)
    551 @experimental
    552 def to_pyarrow(
    553     self,
   (...)    557     **kwargs: Any,
    558 ) -> pa.Table:
    559     """Execute expression and return results in as a pyarrow table.
    560 
    561     This method is eager and will execute the associated expression
   (...)    577         A pyarrow table holding the results of the executed expression.
    578     """
--> 579     return self._find_backend(use_default=True).to_pyarrow(
    580         self, params=params, limit=limit, **kwargs
    581     )

File ~/work/ibis/ibis/ibis/backends/duckdb/__init__.py:1425, in Backend.to_pyarrow(self, expr, params, limit, **kwargs)
   1412 def to_pyarrow(
   1413     self,
   1414     expr: ir.Expr,
   (...)   1419     **kwargs: Any,
   1420 ) -> pa.Table:
   1421     from ibis.backends.duckdb.converter import DuckDBPyArrowData
   1423     table = self._to_duckdb_relation(
   1424         expr, params=params, limit=limit, **kwargs
-> 1425     ).arrow()
   1426     return expr.__pyarrow_result__(table, data_mapper=DuckDBPyArrowData)

ConversionException: Conversion Error: Could not convert string 'a string' to INT64

LINE 1: SELECT CAST('a string' AS BIGINT) AS "Cast('a string', int64)"
               ^

coalesce

coalesce(*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	`()`

Returns

Name	Type	Description
	Value	Coalesced expression

Examples

>>> import ibis
>>> ibis.coalesce(None, 4, 5).name("x")

┌───┐
│ 4 │
└───┘

collect

collect(where=None, order_by=None, include_null=False, distinct=False)

Aggregate this expression’s elements into an array.

This function is called array_agg, list_agg, or list in other systems.

Parameters

Name	Type	Description	Default
where	`ir`.`BooleanValue` \| None	An optional filter expression. If provided, only rows where `where` is `True` will be included in the aggregate.	`None`
order_by	Any	An ordering key (or keys) to use to order the rows before aggregating. If not provided, the order of the items in the result is undefined and backend specific.	`None`
include_null	bool	Whether to include null values when performing this aggregation. Set to `True` to include nulls in the result.	`False`
distinct	bool	Whether to collect only distinct elements.	`False`

Returns

Name	Type	Description
	`ArrayScalar`	An array of all the collected elements.

Examples

Basic collect usage

>>> import ibis
>>> from ibis import _
>>> ibis.options.interactive = True
>>> t = ibis.memtable({"key": list("aaabb"), "value": [1, 1, 2, 3, 5]})
>>> t

┏━━━━━━━━┳━━━━━━━┓
┃ key    ┃ value ┃
┡━━━━━━━━╇━━━━━━━┩
│ string │ int64 │
├────────┼───────┤
│ a      │     1 │
│ a      │     1 │
│ a      │     2 │
│ b      │     3 │
│ b      │     5 │
└────────┴───────┘

Collect all elements into an array scalar:

>>> t.value.collect().to_pandas()

[1, 1, 2, 3, 5]

Collect only unique elements:

>>> t.value.collect(distinct=True).to_pandas()

[1, 3, 2, 5]

Collect elements in a specified order:

>>> t.value.collect(order_by=_.value.desc()).to_pandas()

[5, 3, 2, 1, 1]

Collect elements per group, filtering out values <= 1:

>>> t.group_by("key").agg(v=t.value.collect(where=_.value > 1)).order_by("key")

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

desc

desc(nulls_first=False)

Sort an expression in descending order.

Parameters

Name	Type	Description	Default
nulls_first	bool	Whether to sort `NULL` values first.	`False`

Returns

Name	Type	Description
	Value	Sorted expression

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.memtable({"a": [1, 2, 3, None]})
>>> t.order_by(t.a.desc())

┏━━━━━━━━━┓
┃ a       ┃
┡━━━━━━━━━┩
│ float64 │
├─────────┤
│     3.0 │
│     2.0 │
│     1.0 │
│    NULL │
└─────────┘

>>> t.order_by(t.a.desc(nulls_first=True))

┏━━━━━━━━━┓
┃ a       ┃
┡━━━━━━━━━┩
│ float64 │
├─────────┤
│    NULL │
│     3.0 │
│     2.0 │
│     1.0 │
└─────────┘

fill_null

fill_null(fill_value, /)

Replace NULLs with the given value. Does NOT affect NaN and inf values.

This only replaces genuine NULL values, it does NOT affect NaN and inf values for floating point types.

Parameters

Name	Type	Description	Default
fill_value	Scalar	Value with which to replace `NULL` values in `self`	required

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.memtable({"f": [None, "-inf", "3.0", "inf", "nan"]})
>>> t = t.mutate(f=ibis._.f.cast(float))
>>> t = t.mutate(filled=t.f.fill_null(99))
>>> t

┏━━━━━━━━━┳━━━━━━━━━┓
┃ f       ┃ filled  ┃
┡━━━━━━━━━╇━━━━━━━━━┩
│ float64 │ float64 │
├─────────┼─────────┤
│    NULL │    99.0 │
│    -inf │    -inf │
│     3.0 │     3.0 │
│     inf │     inf │
│     nan │     nan │
└─────────┴─────────┘

If you want to fill all NaN and inf values as well, use something like the following:

>>> t.mutate(filled2=ibis.or_(t.f.isnull(), t.f.isnan(), t.f.isinf()).ifelse(99, t.f))

┏━━━━━━━━━┳━━━━━━━━━┳━━━━━━━━━┓
┃ f       ┃ filled  ┃ filled2 ┃
┡━━━━━━━━━╇━━━━━━━━━╇━━━━━━━━━┩
│ float64 │ float64 │ float64 │
├─────────┼─────────┼─────────┤
│    NULL │    99.0 │    99.0 │
│    -inf │    -inf │    99.0 │
│     3.0 │     3.0 │     3.0 │
│     inf │     inf │    99.0 │
│     nan │     nan │    99.0 │
└─────────┴─────────┴─────────┘

Returns

Name	Type	Description
	Value	`self` filled with `fill_value` where it is `NULL`

fillna

fillna(fill_value, /)

DEPRECATED: use fill_null instead, which acts exactly the same.

group_concat

group_concat(sep=',', where=None, order_by=None)

Concatenate values using the indicated separator to produce a string.

Parameters

Name	Type	Description	Default
sep	str	The separator to use to join strings.	`','`
where	`ir`.`BooleanValue` \| None	An optional filter expression. If provided, only rows where `where` is `True` will be included in the aggregate.	`None`
order_by	Any	An ordering key (or keys) to use to order the rows before aggregating. If not provided, the order of the items in the result is undefined and backend specific.	`None`

Returns

Name	Type	Description
	`StringScalar`	Concatenated string expression

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.examples.penguins.fetch().limit(5)
>>> t[["bill_length_mm", "bill_depth_mm"]]

┏━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━┓
┃ bill_length_mm ┃ bill_depth_mm ┃
┡━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━┩
│ float64        │ float64       │
├────────────────┼───────────────┤
│           39.1 │          18.7 │
│           39.5 │          17.4 │
│           40.3 │          18.0 │
│           NULL │          NULL │
│           36.7 │          19.3 │
└────────────────┴───────────────┘

>>> t.bill_length_mm.group_concat()

┌─────────────────────┐
│ 39.1,39.5,40.3,36.7 │
└─────────────────────┘

>>> t.bill_length_mm.group_concat(sep=": ")

┌────────────────────────┐
│ 39.1: 39.5: 40.3: 36.7 │
└────────────────────────┘

>>> t.bill_length_mm.group_concat(sep=": ", where=t.bill_depth_mm > 18)

┌────────────┐
│ 39.1: 36.7 │
└────────────┘

hash

hash()

Compute an integer hash value.

The hashing function used is backend-dependent.

Returns

Name	Type	Description
	IntegerValue	The hash value of `self`

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> ibis.literal("hello").hash()

┌──────────────────────┐
│ -4155090522938856779 │
└──────────────────────┘

identical_to

identical_to(other, /)

Return whether this expression is identical to other.

Corresponds to IS NOT DISTINCT FROM in SQL.

Parameters

Name	Type	Description	Default
other	Value	Expression to compare to	required

Returns

Name	Type	Description
	BooleanValue	Whether this expression is not distinct from `other`

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> one = ibis.literal(1)
>>> two = ibis.literal(2)
>>> two.identical_to(one + one)

┌──────┐
│ True │
└──────┘

isin

isin(values, /)

Check whether this expression’s values are in values.

NULL values are propagated in the output. See examples for details.

Parameters

Name	Type	Description	Default
values	`ir`.`ArrayValue` \| `ir`.`Column` \| Iterable[Value]	Values or expression to check for membership	required

Returns

Name	Type	Description
	BooleanValue	Expression indicating membership

Examples

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

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

Check against a literal sequence of values

>>> t.a.isin([1, 2])

┏━━━━━━━━━━━━━━━━━━━━━┓
┃ InValues(a, (1, 2)) ┃
┡━━━━━━━━━━━━━━━━━━━━━┩
│ boolean             │
├─────────────────────┤
│ True                │
│ True                │
│ False               │
└─────────────────────┘

Check against a derived expression

>>> t.a.isin(t.b + 1)

┏━━━━━━━━━━━━━━━┓
┃ InSubquery(a) ┃
┡━━━━━━━━━━━━━━━┩
│ boolean       │
├───────────────┤
│ False         │
│ False         │
│ True          │
└───────────────┘

Check against a column from a different table

>>> t2 = ibis.memtable({"x": [99, 2, 99]})
>>> t.a.isin(t2.x)

┏━━━━━━━━━━━━━━━┓
┃ InSubquery(a) ┃
┡━━━━━━━━━━━━━━━┩
│ boolean       │
├───────────────┤
│ False         │
│ True          │
│ False         │
└───────────────┘

NULL behavior

>>> t = ibis.memtable({"x": [1, 2]})
>>> t.x.isin([1, None])

┏━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ InValues(x, (1, None)) ┃
┡━━━━━━━━━━━━━━━━━━━━━━━━┩
│ boolean                │
├────────────────────────┤
│ True                   │
│ NULL                   │
└────────────────────────┘

>>> t = ibis.memtable({"x": [1, None, 2]})
>>> t.x.isin([1])

┏━━━━━━━━━━━━━━━━━━━┓
┃ InValues(x, (1,)) ┃
┡━━━━━━━━━━━━━━━━━━━┩
│ boolean           │
├───────────────────┤
│ True              │
│ NULL              │
│ False             │
└───────────────────┘

>>> t.x.isin([3])

┏━━━━━━━━━━━━━━━━━━━┓
┃ InValues(x, (3,)) ┃
┡━━━━━━━━━━━━━━━━━━━┩
│ boolean           │
├───────────────────┤
│ False             │
│ NULL              │
│ False             │
└───────────────────┘

isnull

isnull()

Whether this expression is NULL. Does NOT detect NaN and inf values.

For FloatingValue types, use FloatingValue.isnan() and FloatingValue.isinf() to detect NaN and inf values.

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.memtable({"f": [None, "-inf", "3.0", "inf", "nan"]})
>>> t = t.mutate(f=ibis._.f.cast(float))
>>> t.mutate(
...     isnull=t.f.isnull(),
...     isnan=t.f.isnan(),
...     isinf=t.f.isinf(),
... )

┏━━━━━━━━━┳━━━━━━━━━┳━━━━━━━━━┳━━━━━━━━━┓
┃ f       ┃ isnull  ┃ isnan   ┃ isinf   ┃
┡━━━━━━━━━╇━━━━━━━━━╇━━━━━━━━━╇━━━━━━━━━┩
│ float64 │ boolean │ boolean │ boolean │
├─────────┼─────────┼─────────┼─────────┤
│    NULL │ True    │ NULL    │ NULL    │
│    -inf │ False   │ False   │ True    │
│     3.0 │ False   │ False   │ False   │
│     inf │ False   │ False   │ True    │
│     nan │ False   │ True    │ False   │
└─────────┴─────────┴─────────┴─────────┘

name

name(name, /)

Rename an expression to name.

Parameters

Name	Type	Description	Default
name	str	The new name of the expression	required

Returns

Name	Type	Description
	Value	`self` with name `name`

Examples

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

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

>>> t.a.name("b")

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

notin

notin(values, /)

Check whether this expression’s values are not in values.

Opposite of Value.isin().

Parameters

Name	Type	Description	Default
values	`ir`.`ArrayValue` \| `ir`.`Column` \| Iterable[Value]	Values or expression to check for lack of membership	required

Returns

Name	Type	Description
	BooleanValue	Whether `self`’s values are not contained in `values`

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.examples.penguins.fetch().limit(5)
>>> t.bill_depth_mm

┏━━━━━━━━━━━━━━━┓
┃ bill_depth_mm ┃
┡━━━━━━━━━━━━━━━┩
│ float64       │
├───────────────┤
│          18.7 │
│          17.4 │
│          18.0 │
│          NULL │
│          19.3 │
└───────────────┘

>>> t.bill_depth_mm.notin([18.7, 18.1])

┏━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ Not(InValues(bill_depth_mm, (18.7, 18.1))) ┃
┡━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩
│ boolean                                    │
├────────────────────────────────────────────┤
│ False                                      │
│ True                                       │
│ True                                       │
│ NULL                                       │
│ True                                       │
└────────────────────────────────────────────┘

notnull

notnull()

Return whether this expression is not NULL.

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.examples.penguins.fetch().limit(5)
>>> t.bill_depth_mm

┏━━━━━━━━━━━━━━━┓
┃ bill_depth_mm ┃
┡━━━━━━━━━━━━━━━┩
│ float64       │
├───────────────┤
│          18.7 │
│          17.4 │
│          18.0 │
│          NULL │
│          19.3 │
└───────────────┘

>>> t.bill_depth_mm.notnull()

┏━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ NotNull(bill_depth_mm) ┃
┡━━━━━━━━━━━━━━━━━━━━━━━━┩
│ boolean                │
├────────────────────────┤
│ True                   │
│ True                   │
│ True                   │
│ False                  │
│ True                   │
└────────────────────────┘

nullif

nullif(null_if_expr, /)

Set values to null if they equal the values null_if_expr.

Commonly used to avoid divide-by-zero problems by replacing zero with NULL in the divisor.

Equivalent to (self == null_if_expr).ifelse(ibis.null(), self).

Parameters

Name	Type	Description	Default
null_if_expr	Value	Expression indicating what values should be NULL	required

Returns

Name	Type	Description
	Value	Value expression

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> vals = ibis.examples.penguins.fetch().head(5).sex
>>> vals

┏━━━━━━━━┓
┃ sex    ┃
┡━━━━━━━━┩
│ string │
├────────┤
│ male   │
│ female │
│ female │
│ NULL   │
│ female │
└────────┘

>>> vals.nullif("male")

┏━━━━━━━━━━━━━━━━━━━━━┓
┃ NullIf(sex, 'male') ┃
┡━━━━━━━━━━━━━━━━━━━━━┩
│ string              │
├─────────────────────┤
│ NULL                │
│ female              │
│ female              │
│ NULL                │
│ female              │
└─────────────────────┘

over

over(window=None, *, rows=None, range=None, group_by=None, order_by=None)

Construct a window expression.

Parameters

Name	Description	Default
window	Window specification	`None`
rows	Whether to use the `ROWS` window clause	`None`
range	Whether to use the `RANGE` window clause	`None`
group_by	Grouping key	`None`
order_by	Ordering key	`None`

Returns

Name	Type	Description
	Value	A window function expression

substitute

substitute(value, replacement=None, else_=None)

Replace values given in values with replacement.

This is similar to the pandas replace method.

Parameters

Name	Type	Description	Default
value	Value \| dict	Expression or dict.	required
replacement	Value \| None	If an expression is passed to value, this must be passed.	`None`
else_	Value \| None	If an original value does not match `value`, then `else_` is used. The default of `None` means leave the original value unchanged.	`None`

Returns

Name	Type	Description
	Value	Replaced values
	[`Value.cases()`](./expression-generic.qmd#ibis.expr.types.generic.Value.case)
	[`ibis.cases()`](./expression-generic.qmd#ibis.cases)

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.examples.penguins.fetch()
>>> t.island.value_counts().order_by("island")

┏━━━━━━━━━━━┳━━━━━━━━━━━━━━┓
┃ island    ┃ island_count ┃
┡━━━━━━━━━━━╇━━━━━━━━━━━━━━┩
│ string    │ int64        │
├───────────┼──────────────┤
│ Biscoe    │          168 │
│ Dream     │          124 │
│ Torgersen │           52 │
└───────────┴──────────────┘

>>> t.island.substitute({"Torgersen": "torg", "Biscoe": "bisc"}).name(
...     "island"
... ).value_counts().order_by("island")

┏━━━━━━━━┳━━━━━━━━━━━━━━┓
┃ island ┃ island_count ┃
┡━━━━━━━━╇━━━━━━━━━━━━━━┩
│ string │ int64        │
├────────┼──────────────┤
│ Dream  │          124 │
│ bisc   │          168 │
│ torg   │           52 │
└────────┴──────────────┘

to_pandas

to_pandas(params=None, limit=None, **kwargs)

Convert a table expression to a pandas DataFrame.

Parameters

Name	Type	Description	Default
params	Mapping[`ir`.`Scalar`, Any] \| None	Mapping of scalar parameter expressions to value.	`None`
limit	int \| str \| None	An integer to effect a specific row limit. A value of `None` means no limit. The default is in `ibis/config.py`.	`None`
kwargs	Any	Keyword arguments	`{}`

Returns

Name	Type	Description
	`DataFrame`	The result of executing the expression as a pandas DataFrame

try_cast

try_cast(target_type, /)

Try cast expression to indicated data type.

If the cast fails for a row, the value is returned as null or NaN depending on target_type and backend behavior.

Parameters

Name	Type	Description	Default
target_type	Any	Type to try cast to. Anything accepted by `ibis.dtype()`	required

Returns

Name	Type	Description
	Value	Casted expression

Examples

>>> import ibis
>>> from ibis import _
>>> ibis.options.interactive = True
>>> t = ibis.memtable({"numbers": [1, 2, 3, 4], "strings": ["1.0", "2", "hello", "world"]})
>>> t

┏━━━━━━━━━┳━━━━━━━━━┓
┃ numbers ┃ strings ┃
┡━━━━━━━━━╇━━━━━━━━━┩
│ int64   │ string  │
├─────────┼─────────┤
│       1 │ 1.0     │
│       2 │ 2       │
│       3 │ hello   │
│       4 │ world   │
└─────────┴─────────┘

>>> t = t.mutate(numbers_to_strings=_.numbers.try_cast("string"))
>>> t = t.mutate(strings_to_numbers=_.strings.try_cast("int"))
>>> t

┏━━━━━━━━━┳━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━┓
┃ numbers ┃ strings ┃ numbers_to_strings ┃ strings_to_numbers ┃
┡━━━━━━━━━╇━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━┩
│ int64   │ string  │ string             │ int64              │
├─────────┼─────────┼────────────────────┼────────────────────┤
│       1 │ 1.0     │ 1                  │                  1 │
│       2 │ 2       │ 2                  │                  2 │
│       3 │ hello   │ 3                  │               NULL │
│       4 │ world   │ 4                  │               NULL │
└─────────┴─────────┴────────────────────┴────────────────────┘

type

type()

Return the DataType of self.

Examples

>>> from datetime import datetime
>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.memtable(
...     {
...         "int_col": [1],
...         "timestamp_col": [
...             datetime(2024, 11, 2, 10, 5, 2),
...         ],
...         "string_col": ["a"],
...     }
... )

>>> t.int_col.type()

Int64(nullable=True)

>>> t.timestamp_col.type()

Timestamp(timezone=None, scale=None, nullable=True)

>>> t.string_col.type()

String(length=None, nullable=True)

typeof

typeof()

Return the string name of the datatype of self.

The values of the returned strings are necessarily backend dependent. e.g. duckdb may say “DOUBLE”, while sqlite may say “real”.

Returns

Name	Type	Description
	StringValue	A string indicating the type of the value

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> vals = ibis.examples.penguins.fetch().head(5).bill_length_mm
>>> vals

┏━━━━━━━━━━━━━━━━┓
┃ bill_length_mm ┃
┡━━━━━━━━━━━━━━━━┩
│ float64        │
├────────────────┤
│           39.1 │
│           39.5 │
│           40.3 │
│           NULL │
│           36.7 │
└────────────────┘

>>> vals.typeof()

┏━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ TypeOf(bill_length_mm) ┃
┡━━━━━━━━━━━━━━━━━━━━━━━━┩
│ string                 │
├────────────────────────┤
│ DOUBLE                 │
│ DOUBLE                 │
│ DOUBLE                 │
│ DOUBLE                 │
│ DOUBLE                 │
└────────────────────────┘

Different backends have different names for their native types

>>> ibis.duckdb.connect().execute(ibis.literal(5.4).typeof())

'DECIMAL(2,1)'

>>> ibis.sqlite.connect().execute(ibis.literal(5.4).typeof())

'real'

Column

Column(self, arg)

Methods

Name	Description
approx_median	Return an approximate of the median of `self`.
approx_nunique	Return the approximate number of distinct elements in `self`.
arbitrary	Select an arbitrary value in a column.
argmax	Return the value of `self` that maximizes `key`.
argmin	Return the value of `self` that minimizes `key`.
as_scalar	Inform ibis that the expression should be treated as a scalar.
as_table	Promote the expression to a Table.
count	Compute the number of rows in an expression.
cume_dist	Return the cumulative distribution over a window.
cummax	Return the cumulative max over a window.
cummin	Return the cumulative min over a window.
dense_rank	Position of first element within each group of equal values.
first	Return the first value of a column.
lag	Return the row located at `offset` rows before the current row.
last	Return the last value of a column.
lead	Return the row located at `offset` rows after the current row.
max	Return the maximum of a column.
median	Return the median of the column.
min	Return the minimum of a column.
mode	Return the mode of a column.
nth	Return the `n`th value (0-indexed) over a window.
ntile	Return the integer number of a partitioning of the column values.
nunique	Compute the number of distinct rows in an expression.
percent_rank	Return the relative rank of the values in the column.
preview	Print a subset as a single-column Rich Table.
quantile	Return value at the given quantile.
rank	Compute position of first element within each equal-value group in sorted order.
to_list	Convert a column expression to a list.
topk	Computes a Table of the top values by a metric (defaults to count).
value_counts	Compute a frequency table.

approx_median

approx_median(where=None)

Return an approximate of the median of self.

The result may or may not be exact

Whether the result is an approximation depends on the backend.

Do not depend on the results being exact

Parameters

Name	Type	Description	Default
where	`ir`.`BooleanValue` \| None	Filter in values when `where` is `True`	`None`

Returns

Name	Type	Description
	Scalar	An approximation of the median of `self`

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.examples.penguins.fetch()
>>> t.body_mass_g.approx_median()

┌────────┐
│ 4030.0 │
└────────┘

>>> t.body_mass_g.approx_median(where=t.species == "Chinstrap")

┌────────┐
│ 3700.0 │
└────────┘

approx_nunique

approx_nunique(where=None)

Return the approximate number of distinct elements in self.

The result may or may not be exact

Whether the result is an approximation depends on the backend.

Do not depend on the results being exact

Parameters

Name	Type	Description	Default
where	`ir`.`BooleanValue` \| None	Filter in values when `where` is `True`	`None`

Returns

Name	Type	Description
	Scalar	An approximate count of the distinct elements of `self`

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.examples.penguins.fetch()
>>> t.body_mass_g.approx_nunique()

┌────┐
│ 92 │
└────┘

>>> t.body_mass_g.approx_nunique(where=t.species == "Adelie")

┌────┐
│ 61 │
└────┘

arbitrary

arbitrary(where=None)

Select an arbitrary value in a column.

Returns an arbitrary (nondeterministic, backend-specific) value from the column. The value will be non-NULL, except if the column is empty or all values are NULL.

Parameters

Name	Type	Description	Default
where	`ir`.`BooleanValue` \| None	A filter expression	`None`

Returns

Name	Type	Description
	Scalar	An expression

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.memtable({"a": [1, 2, 2], "b": list("aaa"), "c": [4.0, 4.1, 4.2]})
>>> t

┏━━━━━━━┳━━━━━━━━┳━━━━━━━━━┓
┃ a     ┃ b      ┃ c       ┃
┡━━━━━━━╇━━━━━━━━╇━━━━━━━━━┩
│ int64 │ string │ float64 │
├───────┼────────┼─────────┤
│     1 │ a      │     4.0 │
│     2 │ a      │     4.1 │
│     2 │ a      │     4.2 │
└───────┴────────┴─────────┘

>>> t.group_by("a").agg(arb=t.b.arbitrary(), c=t.c.sum()).order_by("a")

┏━━━━━━━┳━━━━━━━━┳━━━━━━━━━┓
┃ a     ┃ arb    ┃ c       ┃
┡━━━━━━━╇━━━━━━━━╇━━━━━━━━━┩
│ int64 │ string │ float64 │
├───────┼────────┼─────────┤
│     1 │ a      │     4.0 │
│     2 │ a      │     8.3 │
└───────┴────────┴─────────┘

argmax

argmax(key, *, where=None)

Return the value of self that maximizes key.

If more than one value maximizes key, the returned value is backend specific. The result may be NULL.

Parameters

Name	Type	Description	Default
key	`ir`.`Value`	Key to use for `max` computation.	required
where	`ir`.`BooleanValue` \| None	Keep values when `where` is `True`	`None`

Returns

Name	Type	Description
	Scalar	The value of `self` that maximizes `key`

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.examples.penguins.fetch()
>>> t.species.argmax(t.body_mass_g)

┌────────┐
│ Gentoo │
└────────┘

>>> t.species.argmax(t.body_mass_g, where=t.island == "Dream")

┌───────────┐
│ Chinstrap │
└───────────┘

argmin

argmin(key, *, where=None)

Return the value of self that minimizes key.

If more than one value minimizes key, the returned value is backend specific. The result may be NULL.

Parameters

Name	Type	Description	Default
key	`ir`.`Value`	Key to use for `min` computation.	required
where	`ir`.`BooleanValue` \| None	Keep values when `where` is `True`	`None`

Returns

Name	Type	Description
	Scalar	The value of `self` that minimizes `key`

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.examples.penguins.fetch()
>>> t.species.argmin(t.body_mass_g)

┌───────────┐
│ Chinstrap │
└───────────┘

>>> t.species.argmin(t.body_mass_g, where=t.island == "Biscoe")

┌────────┐
│ Adelie │
└────────┘

as_scalar

as_scalar()

Inform ibis that the expression should be treated as a scalar.

Creates a scalar subquery from the column expression. Since ibis cannot be sure that the column expression contains only one value, the column expression is wrapped in a scalar subquery and treated as a scalar.

Note that the execution of the scalar subquery will fail if the column expression contains more than one value.

Returns

Name	Type	Description
	Scalar	A scalar subquery

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.examples.penguins.fetch()
>>> heavy_gentoo = t.filter(t.species == "Gentoo", t.body_mass_g > 6200)
>>> from_that_island = t.filter(t.island == heavy_gentoo.island.as_scalar())
>>> from_that_island.species.value_counts().order_by("species")

┏━━━━━━━━━┳━━━━━━━━━━━━━━━┓
┃ species ┃ species_count ┃
┡━━━━━━━━━╇━━━━━━━━━━━━━━━┩
│ string  │ int64         │
├─────────┼───────────────┤
│ Adelie  │            44 │
│ Gentoo  │           124 │
└─────────┴───────────────┘

as_table

as_table()

Promote the expression to a Table.

Returns

Name	Type	Description
	`Table`	A table expression

Examples

>>> import ibis
>>> t = ibis.table(dict(a="str"), name="t")
>>> expr = t.a.length().name("len").as_table()
>>> expected = t.select(len=t.a.length())
>>> expr.equals(expected)

True

count

count(where=None)

Compute the number of rows in an expression.

Parameters

Name	Type	Description	Default
where	`ir`.`BooleanValue` \| None	Filter expression	`None`

Returns

Name	Type	Description
	`IntegerScalar`	Number of elements in an expression

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.memtable(
...     {
...         "id": [1, 2, 3, 4, 5, 6],
...         "color": ["Red", "Green", "Blue", "Blue", "Red", "Blue"],
...     }
... )
>>> t.count()

┌───┐
│ 6 │
└───┘

>>> t.count(where=t.color == "Blue")

┌───┐
│ 3 │
└───┘

cume_dist

cume_dist()

Return the cumulative distribution over a window.

Examples

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

┏━━━━━━━━┳━━━━━━━━━━━┓
┃ values ┃ cume_dist ┃
┡━━━━━━━━╇━━━━━━━━━━━┩
│ int64  │ float64   │
├────────┼───────────┤
│      1 │  0.333333 │
│      1 │  0.333333 │
│      2 │  0.833333 │
│      2 │  0.833333 │
│      2 │  0.833333 │
│      3 │  1.000000 │
└────────┴───────────┘

cummax

cummax(where=None, group_by=None, order_by=None)

Return the cumulative max over a window.

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.memtable(
...     {
...         "id": [1, 2, 3, 4, 5, 6],
...         "grouper": ["a", "a", "a", "b", "b", "c"],
...         "values": [3, 2, 1, 2, 3, 2],
...     }
... )
>>> t.mutate(cummax=t.values.cummax())

┏━━━━━━━┳━━━━━━━━━┳━━━━━━━━┳━━━━━━━━┓
┃ id    ┃ grouper ┃ values ┃ cummax ┃
┡━━━━━━━╇━━━━━━━━━╇━━━━━━━━╇━━━━━━━━┩
│ int64 │ string  │ int64  │ int64  │
├───────┼─────────┼────────┼────────┤
│     1 │ a       │      3 │      3 │
│     2 │ a       │      2 │      3 │
│     3 │ a       │      1 │      3 │
│     4 │ b       │      2 │      3 │
│     5 │ b       │      3 │      3 │
│     6 │ c       │      2 │      3 │
└───────┴─────────┴────────┴────────┘

>>> t.mutate(cummax=t.values.cummax(where=t.grouper != "c", group_by=t.grouper)).order_by(
...     t.id
... )

┏━━━━━━━┳━━━━━━━━━┳━━━━━━━━┳━━━━━━━━┓
┃ id    ┃ grouper ┃ values ┃ cummax ┃
┡━━━━━━━╇━━━━━━━━━╇━━━━━━━━╇━━━━━━━━┩
│ int64 │ string  │ int64  │ int64  │
├───────┼─────────┼────────┼────────┤
│     1 │ a       │      3 │      3 │
│     2 │ a       │      2 │      3 │
│     3 │ a       │      1 │      3 │
│     4 │ b       │      2 │      2 │
│     5 │ b       │      3 │      3 │
│     6 │ c       │      2 │   NULL │
└───────┴─────────┴────────┴────────┘

cummin

cummin(where=None, group_by=None, order_by=None)

Return the cumulative min over a window.

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.memtable(
...     {
...         "id": [1, 2, 3, 4, 5, 6],
...         "grouper": ["a", "a", "a", "b", "b", "c"],
...         "values": [3, 2, 1, 2, 3, 2],
...     }
... )
>>> t.mutate(cummin=t.values.cummin())

┏━━━━━━━┳━━━━━━━━━┳━━━━━━━━┳━━━━━━━━┓
┃ id    ┃ grouper ┃ values ┃ cummin ┃
┡━━━━━━━╇━━━━━━━━━╇━━━━━━━━╇━━━━━━━━┩
│ int64 │ string  │ int64  │ int64  │
├───────┼─────────┼────────┼────────┤
│     1 │ a       │      3 │      3 │
│     2 │ a       │      2 │      2 │
│     3 │ a       │      1 │      1 │
│     4 │ b       │      2 │      1 │
│     5 │ b       │      3 │      1 │
│     6 │ c       │      2 │      1 │
└───────┴─────────┴────────┴────────┘

>>> t.mutate(cummin=t.values.cummin(where=t.grouper != "c", group_by=t.grouper)).order_by(
...     t.id
... )

┏━━━━━━━┳━━━━━━━━━┳━━━━━━━━┳━━━━━━━━┓
┃ id    ┃ grouper ┃ values ┃ cummin ┃
┡━━━━━━━╇━━━━━━━━━╇━━━━━━━━╇━━━━━━━━┩
│ int64 │ string  │ int64  │ int64  │
├───────┼─────────┼────────┼────────┤
│     1 │ a       │      3 │      3 │
│     2 │ a       │      2 │      2 │
│     3 │ a       │      1 │      1 │
│     4 │ b       │      2 │      2 │
│     5 │ b       │      3 │      2 │
│     6 │ c       │      2 │   NULL │
└───────┴─────────┴────────┴────────┘

dense_rank

dense_rank()

Position of first element within each group of equal values.

Values are returned in sorted order and duplicate values are ignored.

Equivalent to SQL’s DENSE_RANK().

Returns

Name	Type	Description
	IntegerColumn	The rank

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.memtable({"values": [1, 2, 1, 2, 3, 2]})
>>> t.mutate(rank=t.values.dense_rank())

┏━━━━━━━━┳━━━━━━━┓
┃ values ┃ rank  ┃
┡━━━━━━━━╇━━━━━━━┩
│ int64  │ int64 │
├────────┼───────┤
│      1 │     0 │
│      1 │     0 │
│      2 │     1 │
│      2 │     1 │
│      2 │     1 │
│      3 │     2 │
└────────┴───────┘

first

first(where=None, order_by=None, include_null=False)

Return the first value of a column.

Parameters

Name	Type	Description	Default
where	`ir`.`BooleanValue` \| None	An optional filter expression. If provided, only rows where `where` is `True` will be included in the aggregate.	`None`
order_by	Any	An ordering key (or keys) to use to order the rows before aggregating. If not provided, the meaning of `first` is undefined and will be backend specific.	`None`
include_null	bool	Whether to include null values when performing this aggregation. Set to `True` to include nulls in the result.	`False`

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.memtable({"chars": ["a", "b", "c", "d"]})
>>> t

┏━━━━━━━━┓
┃ chars  ┃
┡━━━━━━━━┩
│ string │
├────────┤
│ a      │
│ b      │
│ c      │
│ d      │
└────────┘

>>> t.chars.first()

┌───┐
│ a │
└───┘

>>> t.chars.first(where=t.chars != "a")

┌───┐
│ b │
└───┘

lag

lag(offset=None, default=None)

Return the row located at offset rows before the current row.

Parameters

Name	Type	Description	Default
offset	int \| `ir`.`IntegerValue` \| None	Index of row to select	`None`
default	Value \| None	Value used if no row exists at `offset`	`None`

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.memtable(
...     {"year": [2007, 2008, 2009, 2010], "total": [1899.6, 1928.2, 2037.9, 1955.2]}
... )
>>> t.mutate(total_lead=t.total.lag())

┏━━━━━━━┳━━━━━━━━━┳━━━━━━━━━━━━┓
┃ year  ┃ total   ┃ total_lead ┃
┡━━━━━━━╇━━━━━━━━━╇━━━━━━━━━━━━┩
│ int64 │ float64 │ float64    │
├───────┼─────────┼────────────┤
│  2007 │  1899.6 │       NULL │
│  2008 │  1928.2 │     1899.6 │
│  2009 │  2037.9 │     1928.2 │
│  2010 │  1955.2 │     2037.9 │
└───────┴─────────┴────────────┘

>>> t.mutate(total_lead=t.total.lag(2, 0))

┏━━━━━━━┳━━━━━━━━━┳━━━━━━━━━━━━┓
┃ year  ┃ total   ┃ total_lead ┃
┡━━━━━━━╇━━━━━━━━━╇━━━━━━━━━━━━┩
│ int64 │ float64 │ float64    │
├───────┼─────────┼────────────┤
│  2007 │  1899.6 │        0.0 │
│  2008 │  1928.2 │        0.0 │
│  2009 │  2037.9 │     1899.6 │
│  2010 │  1955.2 │     1928.2 │
└───────┴─────────┴────────────┘

last

last(where=None, order_by=None, include_null=False)

Return the last value of a column.

Parameters

Name	Type	Description	Default
where	`ir`.`BooleanValue` \| None	An optional filter expression. If provided, only rows where `where` is `True` will be included in the aggregate.	`None`
order_by	Any	An ordering key (or keys) to use to order the rows before aggregating. If not provided, the meaning of `last` is undefined and will be backend specific.	`None`
include_null	bool	Whether to include null values when performing this aggregation. Set to `True` to include nulls in the result.	`False`

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.memtable({"chars": ["a", "b", "c", "d"]})
>>> t

┏━━━━━━━━┓
┃ chars  ┃
┡━━━━━━━━┩
│ string │
├────────┤
│ a      │
│ b      │
│ c      │
│ d      │
└────────┘

>>> t.chars.last()

┌───┐
│ d │
└───┘

>>> t.chars.last(where=t.chars != "d")

┌───┐
│ c │
└───┘

lead

lead(offset=None, default=None)

Return the row located at offset rows after the current row.

Parameters

Name	Type	Description	Default
offset	int \| `ir`.`IntegerValue` \| None	Index of row to select	`None`
default	Value \| None	Value used if no row exists at `offset`	`None`

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.memtable(
...     {"year": [2007, 2008, 2009, 2010], "total": [1899.6, 1928.2, 2037.9, 1955.2]}
... )
>>> t.mutate(total_lead=t.total.lead())

┏━━━━━━━┳━━━━━━━━━┳━━━━━━━━━━━━┓
┃ year  ┃ total   ┃ total_lead ┃
┡━━━━━━━╇━━━━━━━━━╇━━━━━━━━━━━━┩
│ int64 │ float64 │ float64    │
├───────┼─────────┼────────────┤
│  2007 │  1899.6 │     1928.2 │
│  2008 │  1928.2 │     2037.9 │
│  2009 │  2037.9 │     1955.2 │
│  2010 │  1955.2 │       NULL │
└───────┴─────────┴────────────┘

>>> t.mutate(total_lead=t.total.lead(2, 0))

┏━━━━━━━┳━━━━━━━━━┳━━━━━━━━━━━━┓
┃ year  ┃ total   ┃ total_lead ┃
┡━━━━━━━╇━━━━━━━━━╇━━━━━━━━━━━━┩
│ int64 │ float64 │ float64    │
├───────┼─────────┼────────────┤
│  2007 │  1899.6 │     2037.9 │
│  2008 │  1928.2 │     1955.2 │
│  2009 │  2037.9 │        0.0 │
│  2010 │  1955.2 │        0.0 │
└───────┴─────────┴────────────┘

max

max(where=None)

Return the maximum of a column.

Parameters

Name	Type	Description	Default
where	`ir`.`BooleanValue` \| None	Filter in values when `where` is `True`	`None`

Returns

Name	Type	Description
	Scalar	The maximum value in `self`

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.examples.penguins.fetch()
>>> t.body_mass_g.max()

┌──────┐
│ 6300 │
└──────┘

>>> t.body_mass_g.max(where=t.species == "Chinstrap")

┌──────┐
│ 4800 │
└──────┘

median

median(where=None)

Return the median of the column.

Parameters

Name	Type	Description	Default
where	`ir`.`BooleanValue` \| None	Optional boolean expression. If given, only the values where `where` evaluates to true will be considered for the median.	`None`

Returns

Name	Type	Description
	Scalar	Median of the column

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.examples.penguins.fetch()

Compute the median of bill_depth_mm

>>> t.bill_depth_mm.median()

┌──────┐
│ 17.3 │
└──────┘

>>> t.group_by(t.species).agg(median_bill_depth=t.bill_depth_mm.median()).order_by(
...     ibis.desc("median_bill_depth")
... )

┏━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━┓
┃ species   ┃ median_bill_depth ┃
┡━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━┩
│ string    │ float64           │
├───────────┼───────────────────┤
│ Chinstrap │             18.45 │
│ Adelie    │             18.40 │
│ Gentoo    │             15.00 │
└───────────┴───────────────────┘

In addition to numeric types, any orderable non-numeric types such as strings and dates work with median.

>>> t.group_by(t.island).agg(median_species=t.species.median()).order_by(
...     ibis.desc("median_species")
... )

┏━━━━━━━━━━━┳━━━━━━━━━━━━━━━━┓
┃ island    ┃ median_species ┃
┡━━━━━━━━━━━╇━━━━━━━━━━━━━━━━┩
│ string    │ string         │
├───────────┼────────────────┤
│ Biscoe    │ Gentoo         │
│ Dream     │ Chinstrap      │
│ Torgersen │ Adelie         │
└───────────┴────────────────┘

min

min(where=None)

Return the minimum of a column.

Parameters

Name	Type	Description	Default
where	`ir`.`BooleanValue` \| None	Filter in values when `where` is `True`	`None`

Returns

Name	Type	Description
	Scalar	The minimum value in `self`

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.examples.penguins.fetch()
>>> t.body_mass_g.min()

┌──────┐
│ 2700 │
└──────┘

>>> t.body_mass_g.min(where=t.species == "Adelie")

┌──────┐
│ 2850 │
└──────┘

mode

mode(where=None)

Return the mode of a column.

Parameters

Name	Type	Description	Default
where	`ir`.`BooleanValue` \| None	Filter in values when `where` is `True`	`None`

Returns

Name	Type	Description
	Scalar	The mode of `self`

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.examples.penguins.fetch()
>>> t.body_mass_g.mode()

┌──────┐
│ 3800 │
└──────┘

>>> t.body_mass_g.mode(where=(t.species == "Gentoo") & (t.sex == "male"))

┌──────┐
│ 5550 │
└──────┘

nth

nth(n, /)

Return the nth value (0-indexed) over a window.

.nth(0) is equivalent to .first(). Negative will result in NULL. If the value of n is greater than the number of rows in the window, NULL will be returned.

Parameters

Name	Type	Description	Default
n	int \| `ir`.`IntegerValue`	Desired rank value	required

Returns

Name	Type	Description
	Column	The nth value over a window

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.memtable({"values": [1, 2, 3, 4, 5, 6]})
>>> t.mutate(nth=t.values.nth(2))

┏━━━━━━━━┳━━━━━━━┓
┃ values ┃ nth   ┃
┡━━━━━━━━╇━━━━━━━┩
│ int64  │ int64 │
├────────┼───────┤
│      1 │     3 │
│      2 │     3 │
│      3 │     3 │
│      4 │     3 │
│      5 │     3 │
│      6 │     3 │
└────────┴───────┘

>>> t.mutate(nth=t.values.nth(7))

┏━━━━━━━━┳━━━━━━━┓
┃ values ┃ nth   ┃
┡━━━━━━━━╇━━━━━━━┩
│ int64  │ int64 │
├────────┼───────┤
│      1 │  NULL │
│      2 │  NULL │
│      3 │  NULL │
│      4 │  NULL │
│      5 │  NULL │
│      6 │  NULL │
└────────┴───────┘

ntile

ntile(buckets, /)

Return the integer number of a partitioning of the column values.

Parameters

Name	Type	Description	Default
buckets	int \| `ir`.`IntegerValue`	Number of buckets to partition into	required

Examples

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

┏━━━━━━━━┳━━━━━━━┓
┃ values ┃ ntile ┃
┡━━━━━━━━╇━━━━━━━┩
│ int64  │ int64 │
├────────┼───────┤
│      1 │     0 │
│      1 │     0 │
│      2 │     1 │
│      2 │     1 │
│      2 │     2 │
│      3 │     2 │
└────────┴───────┘

nunique

nunique(where=None)

Compute the number of distinct rows in an expression.

Parameters

Name	Type	Description	Default
where	`ir`.`BooleanValue` \| None	Filter expression	`None`

Returns

Name	Type	Description
	`IntegerScalar`	Number of distinct elements in an expression

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.examples.penguins.fetch()
>>> t.body_mass_g.nunique()

┌────┐
│ 94 │
└────┘

>>> t.body_mass_g.nunique(where=t.species == "Adelie")

┌────┐
│ 55 │
└────┘

percent_rank

percent_rank()

Return the relative rank of the values in the column.

Examples

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

┏━━━━━━━━┳━━━━━━━━━━━━━━┓
┃ values ┃ percent_rank ┃
┡━━━━━━━━╇━━━━━━━━━━━━━━┩
│ int64  │ float64      │
├────────┼──────────────┤
│      1 │          0.0 │
│      1 │          0.0 │
│      2 │          0.4 │
│      2 │          0.4 │
│      2 │          0.4 │
│      3 │          1.0 │
└────────┴──────────────┘

preview

preview(
    max_rows=None,
    max_length=None,
    max_string=None,
    max_depth=None,
    console_width=None,
)

Print a subset as a single-column Rich Table.

This is an explicit version of what you get when you inspect this object in interactive mode, except with this version you can pass formatting options. The options are the same as those exposed in ibis.options.interactive.

Parameters

Name	Type	Description	Default
max_rows	int \| None	Maximum number of rows to display	`None`
max_length	int \| None	Maximum length for pretty-printed arrays and maps.	`None`
max_string	int \| None	Maximum length for pretty-printed strings.	`None`
max_depth	int \| None	Maximum depth for nested data types.	`None`
console_width	int \| float \| None	Width of the console in characters. If not specified, the width will be inferred from the console.	`None`

Examples

>>> import ibis
>>> t = ibis.examples.penguins.fetch()
>>> t.island.preview(max_rows=3, max_string=5)

┏━━━━━━━━┓
┃ island ┃
┡━━━━━━━━┩
│ stri…  │
├────────┤
│ Torg…  │
│ Torg…  │
│ Torg…  │
│ …      │
└────────┘

quantile

quantile(quantile, *, where=None)

Return value at the given quantile.

The output of this method is a continuous quantile if the input is numeric, otherwise the output is a discrete quantile.

Parameters

Name	Type	Description	Default
quantile	float \| `ir`.`NumericValue` \| Sequence[`ir`.`NumericValue` \| float]	`0 <= quantile <= 1`, or an array of such values indicating the quantile or quantiles to compute	required
where	`ir`.`BooleanValue` \| None	Boolean filter for input values	`None`

Returns

Name	Type	Description
	Scalar	Quantile of the input

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.examples.penguins.fetch()

Compute the 99th percentile of bill_depth

>>> t.bill_depth_mm.quantile(0.99)

┌──────┐
│ 21.1 │
└──────┘

>>> t.group_by(t.species).agg(p99_bill_depth=t.bill_depth_mm.quantile(0.99)).order_by(
...     ibis.desc("p99_bill_depth")
... )

┏━━━━━━━━━━━┳━━━━━━━━━━━━━━━━┓
┃ species   ┃ p99_bill_depth ┃
┡━━━━━━━━━━━╇━━━━━━━━━━━━━━━━┩
│ string    │ float64        │
├───────────┼────────────────┤
│ Adelie    │         21.200 │
│ Chinstrap │         20.733 │
│ Gentoo    │         17.256 │
└───────────┴────────────────┘

In addition to numeric types, any orderable non-numeric types such as strings and dates work with quantile.

Let’s compute the 99th percentile of the species column

>>> t.group_by(t.island).agg(p99_species=t.species.quantile(0.99)).order_by(
...     ibis.desc("p99_species")
... )

┏━━━━━━━━━━━┳━━━━━━━━━━━━━┓
┃ island    ┃ p99_species ┃
┡━━━━━━━━━━━╇━━━━━━━━━━━━━┩
│ string    │ string      │
├───────────┼─────────────┤
│ Biscoe    │ Gentoo      │
│ Dream     │ Chinstrap   │
│ Torgersen │ Adelie      │
└───────────┴─────────────┘

rank

rank()

Compute position of first element within each equal-value group in sorted order.

Equivalent to SQL’s RANK() window function.

Returns

Name	Type	Description
	`Int64Column`	The min rank

Examples

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

┏━━━━━━━━┳━━━━━━━┓
┃ values ┃ rank  ┃
┡━━━━━━━━╇━━━━━━━┩
│ int64  │ int64 │
├────────┼───────┤
│      1 │     0 │
│      1 │     0 │
│      2 │     2 │
│      2 │     2 │
│      2 │     2 │
│      3 │     5 │
└────────┴───────┘

to_list

to_list(**kwargs)

Convert a column expression to a list.

Parameters

Name	Type	Description	Default
kwargs		Same as keyword arguments to `to_pyarrow`	`{}`

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.examples.penguins.fetch()
>>> t.bill_length_mm.to_list(limit=5)

[39.1, 39.5, 40.3, None, 36.7]

Added in version 10.0.0

topk

topk(k=None, by=None, *, name=None)

Computes a Table of the top values by a metric (defaults to count).

Changed in version 9.5.0

Added name parameter.

Parameters

Name	Type	Description	Default
k	int \| None	The number of rows to return. If `None`, all values are returned in descending order.	`None`
by	`ir`.`Value` \| None	The metric to compute “top” by. Defaults to `count`.	`None`
name	str \| None	The name to use for the metric column. If not provided, a suitable name will be generated.	`None`

Returns

Name	Type	Description
	`Table`	The top `k` values.

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.examples.diamonds.fetch()

Compute the top 3 diamond colors by frequency:

>>> t.color.topk(3)

┏━━━━━━━━┳━━━━━━━━━━━━━┓
┃ color  ┃ color_count ┃
┡━━━━━━━━╇━━━━━━━━━━━━━┩
│ string │ int64       │
├────────┼─────────────┤
│ G      │       11292 │
│ E      │        9797 │
│ F      │        9542 │
└────────┴─────────────┘

Compute the top 3 diamond colors by mean price:

>>> t.color.topk(3, by=t.price.mean())

┏━━━━━━━━┳━━━━━━━━━━━━━┓
┃ color  ┃ Mean(price) ┃
┡━━━━━━━━╇━━━━━━━━━━━━━┩
│ string │ float64     │
├────────┼─────────────┤
│ J      │ 5323.818020 │
│ I      │ 5091.874954 │
│ H      │ 4486.669196 │
└────────┴─────────────┘

Rank all the colors by max carat:

>>> t.color.topk(k=5, by=t.carat.max(), name="max_carat")

┏━━━━━━━━┳━━━━━━━━━━━┓
┃ color  ┃ max_carat ┃
┡━━━━━━━━╇━━━━━━━━━━━┩
│ string │ float64   │
├────────┼───────────┤
│ J      │      5.01 │
│ H      │      4.13 │
│ I      │      4.01 │
│ D      │      3.40 │
│ E      │      3.05 │
└────────┴───────────┘

value_counts

value_counts(name=None)

Compute a frequency table.

Parameters

Name	Type	Description	Default
name	str \| None	The name to use for the frequency column. If not provided, a suitable name will be generated.	`None`

Returns

Name	Type	Description
	`Table`	The frequency table.

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.memtable({"chars": ["a", "a", "b", "c", "c", "c", "d", "d", "d", "d"]})

Compute the count of each unique value in “chars”, ordered by “chars”:

>>> t.chars.value_counts().order_by("chars")

┏━━━━━━━━┳━━━━━━━━━━━━━┓
┃ chars  ┃ chars_count ┃
┡━━━━━━━━╇━━━━━━━━━━━━━┩
│ string │ int64       │
├────────┼─────────────┤
│ a      │           2 │
│ b      │           1 │
│ c      │           3 │
│ d      │           4 │
└────────┴─────────────┘

Compute the count of each unique value in “chars” as a column named “freq”, ordered by “freq”:

>>> t.chars.value_counts(name="freq").order_by("freq")

┏━━━━━━━━┳━━━━━━━┓
┃ chars  ┃ freq  ┃
┡━━━━━━━━╇━━━━━━━┩
│ string │ int64 │
├────────┼───────┤
│ b      │     1 │
│ a      │     2 │
│ c      │     3 │
│ d      │     4 │
└────────┴───────┘

Added in version 9.5.0

Deferred

Deferred(self, obj, repr=None)

The user facing wrapper object providing syntactic sugar for deferreds.

Provides a natural-like syntax for constructing deferred expressions by overloading all of the available dunder methods including the equality operator.

Its sole purpose is to provide a nicer syntax for constructing deferred expressions, thus it gets unwrapped to the underlying deferred expression when used by the rest of the library.

Parameters

Name	Type	Description	Default
obj		The deferred object to provide syntax sugar for.	required
repr		An optional fixed string to use when repr-ing the deferred expression, instead of the default. This is useful for complex deferred expressions where the arguments don’t necessarily make sense to be user facing in the repr.	`None`

Scalar

Scalar(self, arg)

Methods

Name	Description
as_scalar	Inform ibis that the expression should be treated as a scalar.
as_table	Promote the scalar expression to a table.

as_scalar

as_scalar()

Inform ibis that the expression should be treated as a scalar.

If the expression is a literal, it will be returned as is. If it depends on a table, it will be turned to a scalar subquery.

Returns

Name	Type	Description
	Scalar	A scalar subquery or a literal

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.examples.penguins.fetch()
>>> max_gentoo_weight = t.filter(t.species == "Gentoo").body_mass_g.max()
>>> light_penguins = t.filter(t.body_mass_g < max_gentoo_weight / 2)
>>> light_penguins.species.value_counts().order_by("species")

┏━━━━━━━━━━━┳━━━━━━━━━━━━━━━┓
┃ species   ┃ species_count ┃
┡━━━━━━━━━━━╇━━━━━━━━━━━━━━━┩
│ string    │ int64         │
├───────────┼───────────────┤
│ Adelie    │            15 │
│ Chinstrap │             2 │
└───────────┴───────────────┘

as_table

as_table()

Promote the scalar expression to a table.

Returns

Name	Type	Description
	`Table`	A table expression

Examples

Promote an aggregation to a table

>>> import ibis
>>> import ibis.expr.types as ir
>>> t = ibis.table(dict(a="str"), name="t")
>>> expr = t.a.length().sum().name("len").as_table()
>>> isinstance(expr, ir.Table)

True

Promote a literal value to a table

>>> import ibis.expr.types as ir
>>> lit = ibis.literal(1).name("a").as_table()
>>> isinstance(lit, ir.Table)

True

deferred

deferred

Deferred expression object.

Use this object to refer to a previous table expression in a chain of expressions.

_ may conflict with other idioms in Python

See https://github.com/ibis-project/ibis/issues/4704 for details.

Use from ibis import deferred as <NAME> to assign a different name to the deferred object builder.

Another option is to use ibis._ directly.

Examples

>>> from ibis import _
>>> t = ibis.table(dict(key="int", value="float"), name="t")
>>> expr = t.group_by(key=_.key - 1).agg(total=_.value.sum())
>>> expr.schema()

ibis.Schema {
  key    int64
  total  float64
}

literal

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

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

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

---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
File ~/work/ibis/ibis/ibis/expr/datatypes/value.py:268, in normalize(typ, value)
    267 try:
--> 268     value = int(value)
    269 except ValueError:

ValueError: invalid literal for int() with base 10: 'foobar'

During handling of the above exception, another exception occurred:

TypeError                                 Traceback (most recent call last)
Cell In[359], line 1
----> 1 ibis.literal("foobar", type="int64")  

File ~/work/ibis/ibis/ibis/common/deferred.py:614, in deferrable.<locals>.wrapper.<locals>.inner(*args, **kwargs)
    612     builder = Call(func, *args, **kwargs)
    613     return Deferred(builder, repr=repr)
--> 614 return func(*args, **kwargs)

File ~/work/ibis/ibis/ibis/expr/types/generic.py:3035, in literal(value, type)
   3030         raise TypeError(
   3031             f"Ibis literal {value!r} cannot be safely coerced to datatype {type}"
   3032         )
   3034 dtype = dt.infer(value) if type is None else dt.dtype(type)
-> 3035 return ops.Literal(value, dtype=dtype).to_expr()

File ~/work/ibis/ibis/ibis/common/bases.py:72, in AbstractMeta.__call__(cls, *args, **kwargs)
     52 def __call__(cls, *args, **kwargs):
     53     """Create a new instance of the class.
     54 
     55     The subclass may override the `__create__` classmethod to change the
   (...)     70 
     71     """
---> 72     return cls.__create__(*args, **kwargs)

File ~/work/ibis/ibis/ibis/common/grounds.py:120, in Annotable.__create__(cls, *args, **kwargs)
    116 @classmethod
    117 def __create__(cls, *args: Any, **kwargs: Any) -> Self:
    118     # construct the instance by passing only validated keyword arguments
    119     kwargs = cls.__signature__.validate(cls, args, kwargs)
--> 120     return super().__create__(**kwargs)

File ~/work/ibis/ibis/ibis/expr/operations/generic.py:147, in Literal.__init__(self, value, dtype)
    145 def __init__(self, value, dtype):
    146     # normalize ensures that the value is a valid value for the given dtype
--> 147     value = dt.normalize(dtype, value)
    148     super().__init__(value=value, dtype=dtype)

File ~/work/ibis/ibis/ibis/expr/datatypes/value.py:270, in normalize(typ, value)
    268     value = int(value)
    269 except ValueError:
--> 270     raise TypeError(f"Unable to normalize {value!r} to {dtype!r}")
    271 if value not in dtype.bounds:
    272     raise TypeError(
    273         f"Value {value} is out of bounds for type {dtype!r} "
    274         f"(bounds: {dtype.bounds})"
    275     )

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

Literals can also be used in a deferred context.

Here’s an example of constructing a table of a column’s type repeated for every row:

>>> from ibis import _, selectors as s
>>> ibis.options.interactive = True
>>> t = ibis.examples.penguins.fetch()
>>> t.select(s.across(s.all(), ibis.literal(_.type(), type=str).name(_.get_name()))).head(1)

┏━━━━━━━━━┳━━━━━━━━┳━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━┳━━━━━━━━┳━━━━━━━━┓
┃ species ┃ island ┃ bill_length_mm ┃ bill_depth_mm ┃ flipper_length_mm ┃ body_mass_g ┃ sex    ┃ year   ┃
┡━━━━━━━━━╇━━━━━━━━╇━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━╇━━━━━━━━╇━━━━━━━━┩
│ string  │ string │ string         │ string        │ string            │ string      │ string │ string │
├─────────┼────────┼────────────────┼───────────────┼───────────────────┼─────────────┼────────┼────────┤
│ string  │ string │ float64        │ float64       │ int64             │ int64       │ string │ int64  │
└─────────┴────────┴────────────────┴───────────────┴───────────────────┴─────────────┴────────┴────────┘

param

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

Name	Type	Description
	`Scalar`	A scalar expression backend by a parameter

Examples

>>> from datetime import date
>>> import ibis
>>> start = ibis.param("date")
>>> t = ibis.memtable(
...     {
...         "date_col": [date(2013, 1, 1), date(2013, 1, 2), date(2013, 1, 3)],
...         "value": [1.0, 2.0, 3.0],
...     },
... )
>>> expr = t.filter(t.date_col >= start).value.sum()
>>> expr.execute(params={start: date(2013, 1, 1)})

6.0

>>> expr.execute(params={start: date(2013, 1, 2)})

5.0

>>> expr.execute(params={start: date(2013, 1, 3)})

3.0

null

ibis.null(type=None, /)

Create a NULL scalar.

NULLs with an unspecified type are castable and comparable to values, but lack datatype-specific methods:

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> ibis.null().upper()  # quartodoc: +EXPECTED_FAILURE

---------------------------------------------------------------------------
AttributeError                            Traceback (most recent call last)
Cell In[374], line 3
      1 import ibis
      2 ibis.options.interactive = True
----> 3 ibis.null().upper()  # quartodoc: +EXPECTED_FAILURE

AttributeError: 'NullScalar' object has no attribute 'upper'

>>> ibis.null(str).upper()

┌──────┐
│ NULL │
└──────┘

>>> ibis.null(str).upper().isnull()

┌──────┐
│ True │
└──────┘

range

ibis.range(start, stop, step)

Generate a range of values.

Integer ranges are supported, as well as timestamp ranges.

Note

start is inclusive and stop is exclusive, just like Python’s builtin range.

When step equals 0, however, this function will return an empty array.

Python’s range will raise an exception when step is zero.

Parameters

Name	Description	Default
start	Lower bound of the range, inclusive.	required
stop	Upper bound of the range, exclusive.	required
step	Step value. Optional, defaults to 1.	required

Returns

Name	Type	Description
	`ArrayValue`	An array of values

Examples

>>> import ibis
>>> ibis.options.interactive = True

Range using only a stop argument

>>> ibis.range(5)

┌────────────────┐
│ [0, 1, ... +3] │
└────────────────┘

Simple range using start and stop

>>> ibis.range(1, 5)

┌────────────────┐
│ [1, 2, ... +2] │
└────────────────┘

Generate an empty range

>>> ibis.range(0)

┌────┐
│ [] │
└────┘

Negative step values are supported

>>> ibis.range(10, 4, -2)

┌─────────────────┐
│ [10, 8, ... +1] │
└─────────────────┘

ibis.range behaves the same as Python’s range …

>>> ibis.range(0, 7, -1)

┌────┐
│ [] │
└────┘

… except when the step is zero, in which case ibis.range returns an empty array

>>> ibis.range(0, 5, 0)

┌────┐
│ [] │
└────┘

Because the resulting expression is array, you can unnest the values

>>> ibis.range(5).unnest().name("numbers")

┏━━━━━━━━━┓
┃ numbers ┃
┡━━━━━━━━━┩
│ int8    │
├─────────┤
│       0 │
│       1 │
│       2 │
│       3 │
│       4 │
└─────────┘

Timestamp ranges are also supported

>>> expr = ibis.range("2002-01-01", "2002-02-01", ibis.interval(days=2)).name("ts")
>>> expr

┌─────────────────────────────────────────────────────────────────────────────────────┐
│ [datetime.datetime(2002, 1, 1, 0, 0), datetime.datetime(2002, 1, 3, 0, 0), ... +14] │
└─────────────────────────────────────────────────────────────────────────────────────┘

>>> expr.unnest()

┏━━━━━━━━━━━━━━━━━━━━━┓
┃ ts                  ┃
┡━━━━━━━━━━━━━━━━━━━━━┩
│ timestamp           │
├─────────────────────┤
│ 2002-01-01 00:00:00 │
│ 2002-01-03 00:00:00 │
│ 2002-01-05 00:00:00 │
│ 2002-01-07 00:00:00 │
│ 2002-01-09 00:00:00 │
│ 2002-01-11 00:00:00 │
│ 2002-01-13 00:00:00 │
│ 2002-01-15 00:00:00 │
│ 2002-01-17 00:00:00 │
│ 2002-01-19 00:00:00 │
│ …                   │
└─────────────────────┘

coalesce

ibis.coalesce(arg, /, *args)

Return the first non-null value from args.

Parameters

Name	Type	Description	Default
arg	Any	First argument from which to choose the first non-null value	required
args	Any	Arguments from which to choose the first non-null value	`()`

Returns

Name	Type	Description
	`Value`	Coalesced expression

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> ibis.coalesce(None, 4, 5)

┌───┐
│ 4 │
└───┘

least

ibis.least(arg, /, *args)

Compute the smallest value among the supplied arguments.

Parameters

Name	Type	Description	Default
arg	Any	First argument	required
args	Any	Remaining arguments	`()`

Returns

Name	Type	Description
	`Value`	Minimum of the passed arguments

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> ibis.least(None, 4, 5)

┌───┐
│ 4 │
└───┘

greatest

ibis.greatest(arg, /, *args)

Compute the largest value among the supplied arguments.

Parameters

Name	Type	Description	Default
arg	Any	First argument	required
args	Any	Remaining arguments	`()`

Returns

Name	Type	Description
	`Value`	Maximum of the passed arguments

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> ibis.greatest(None, 4, 5)

┌───┐
│ 5 │
└───┘

ifelse

ibis.ifelse(condition, true_expr, false_expr, /)

Construct a ternary conditional expression.

Parameters

Name	Type	Description	Default
condition	Any	A boolean expression	required
true_expr	Any	Expression to return if `condition` evaluates to `True`	required
false_expr	Any	Expression to return if `condition` evaluates to `False` or `NULL`	required

Returns

Name	Type	Description
Value	`ir`.`Value`	The value of `true_expr` if `condition` is `True` else `false_expr`

Examples

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

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

cases

ibis.cases(branch, *branches, else_=None)

Create a multi-branch if-else expression.

Equivalent to a SQL CASE statement.

Added in version 10.0.0

Parameters

Name	Type	Description	Default
branch	tuple[Any, Any]	First (`condition`, `result`) pair. Required.	required
branches	tuple[Any, Any]	Additional (`condition`, `result`) pairs. We look through the test values in order and return the result corresponding to the first test value that matches `self`. If none match, we return `else_`.	`()`
else_	Any \| None	Value to return if none of the case conditions evaluate to `True`. Defaults to `NULL`.	`None`

Returns

Name	Type	Description
	`Value`	A value expression

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> v = ibis.memtable({"values": [1, 2, 1, 2, 3, 2, 4]}).values
>>> ibis.cases((v == 1, "a"), (v > 2, "b"), else_="unk").name("cases")

┏━━━━━━━━┓
┃ cases  ┃
┡━━━━━━━━┩
│ string │
├────────┤
│ a      │
│ unk    │
│ a      │
│ unk    │
│ b      │
│ unk    │
│ b      │
└────────┘

>>> ibis.cases(
...     (v % 2 == 0, "divisible by 2"),
...     (v % 3 == 0, "divisible by 3"),
...     (v % 4 == 0, "shadowed by the 2 case"),
... ).name("cases")

┏━━━━━━━━━━━━━━━━┓
┃ cases          ┃
┡━━━━━━━━━━━━━━━━┩
│ string         │
├────────────────┤
│ NULL           │
│ divisible by 2 │
│ NULL           │
│ divisible by 2 │
│ divisible by 3 │
│ divisible by 2 │
│ divisible by 2 │
└────────────────┘

asc

ibis.asc(expr, /, *, nulls_first=False)

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
nulls_first	bool	Bool to indicate whether to put NULL values first or not.	`False`

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

Name	Type	Description
	`ir`.`ValueExpr`	An expression

desc

ibis.desc(expr, /, *, nulls_first=False)

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
nulls_first	bool	Bool to indicate whether to put NULL values first or not.	`False`

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

Name	Type	Description
	`ir`.`ValueExpr`	An expression

to_sql

ibis.to_sql(expr, dialect=None, pretty=True, **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`
pretty	bool	Whether to use pretty formatting.	`True`
kwargs		Scalar parameters	`{}`

Returns

Name	Type	Description
	str	Formatted SQL string

Examples

>>> import ibis
>>> t = ibis.table({"a": "int", "b": "int"}, name="t")
>>> expr = t.mutate(c=t.a + t.b)
>>> ibis.to_sql(expr)

SELECT
  "t0"."a",
  "t0"."b",
  "t0"."a" + "t0"."b" AS "c"
FROM "t" AS "t0"

You can also specify the SQL dialect to use for compilation:

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

SELECT
  `t0`.`a`,
  `t0`.`b`,
  `t0`.`a` + `t0`.`b` AS `c`
FROM `t` AS `t0`

Value

Methods

asc

Parameters

Returns

See Also

Examples

between

Parameters

Returns

Examples

case

cases

Parameters

Returns

See Also

Examples

cast

Parameters

Returns

See Also

Examples

coalesce

Parameters

Returns

See Also

Examples

collect

Parameters

Returns

Examples

desc

Parameters

Returns

See Also

Examples

fill_null

Parameters

See Also

Examples

Returns

fillna

group_concat

Parameters

Returns

Examples

hash

Returns

Examples

identical_to

Parameters

Returns

Examples

isin

Parameters

Returns

See Also

Examples

isnull

See Also

Examples

name

Parameters

Returns

Examples

notin

Parameters

Returns

Examples

notnull

Examples

nullif

Parameters

Returns

Examples

over

Parameters

Returns

substitute

Parameters