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 ascending.
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 descending.
fill_null Replace any null values with the indicated fill value.
fillna DEPRECATED: use fill_null instead.
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 Return whether this expression is NULL.
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 an expression to a pandas or scalar object.
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 ascending.

Parameters

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

Returns

Name Type Description
Value Sorted expression

See Also

ibis.asc()

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

See Also

Value.substitute() ibis.cases()

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  ┃
┡━━━━━━━╇━━━━━━━━╇━━━━━━━╇━━━━━━━━━┩
│ int64stringint64float64 │
├───────┼────────┼───────┼─────────┤
│     5+     16.0 │
│     6-     24.0 │
│     7*     321.0 │
│     8/     42.0 │
│     9bogus 5NULL │
│    10NULL6NULL │
└───────┴────────┴───────┴─────────┘

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

See Also

Value.try_cast() ibis.dtype()

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/001ahppwh6y4w5nzj0kv9vr1bcad6r16-ibis-3.11/lib/python3.11/site-packages/IPython/core/formatters.py:711, in PlainTextFormatter.__call__(self, obj)
    704 stream = StringIO()
    705 printer = pretty.RepresentationPrinter(stream, self.verbose,
    706     self.max_width, self.newline,
    707     max_seq_length=self.max_seq_length,
    708     singleton_pprinters=self.singleton_printers,
    709     type_pprinters=self.type_printers,
    710     deferred_pprinters=self.deferred_printers)
--> 711 printer.pretty(obj)
    712 printer.flush()
    713 return stream.getvalue()

File /nix/store/001ahppwh6y4w5nzj0kv9vr1bcad6r16-ibis-3.11/lib/python3.11/site-packages/IPython/lib/pretty.py:419, in RepresentationPrinter.pretty(self, obj)
    408                         return meth(obj, self, cycle)
    409                 if (
    410                     cls is not object
    411                     # check if cls defines __repr__
   (...)
    417                     and callable(_safe_getattr(cls, "__repr__", None))
    418                 ):
--> 419                     return _repr_pprint(obj, self, cycle)
    421     return _default_pprint(obj, self, cycle)
    422 finally:

File /nix/store/001ahppwh6y4w5nzj0kv9vr1bcad6r16-ibis-3.11/lib/python3.11/site-packages/IPython/lib/pretty.py:794, in _repr_pprint(obj, p, cycle)
    792 """A pprint that just redirects to the normal repr function."""
    793 # Find newlines and replace them with p.break_()
--> 794 output = repr(obj)
    795 lines = output.splitlines()
    796 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/001ahppwh6y4w5nzj0kv9vr1bcad6r16-ibis-3.11/lib/python3.11/site-packages/rich/console.py:1705, in Console.print(self, sep, end, style, justify, overflow, no_wrap, emoji, markup, highlight, width, height, crop, soft_wrap, new_line_start, *objects)
   1703 if style is None:
   1704     for renderable in renderables:
-> 1705         extend(render(renderable, render_options))
   1706 else:
   1707     for renderable in renderables:

File /nix/store/001ahppwh6y4w5nzj0kv9vr1bcad6r16-ibis-3.11/lib/python3.11/site-packages/rich/console.py:1306, in Console.render(self, renderable, options)
   1304 renderable = rich_cast(renderable)
   1305 if hasattr(renderable, "__rich_console__") and not isclass(renderable):
-> 1306     render_iterable = renderable.__rich_console__(self, _options)
   1307 elif isinstance(renderable, str):
   1308     text_renderable = self.render_str(
   1309         renderable, highlight=_options.highlight, markup=_options.markup
   1310     )

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:299, 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 = format_values(
    298         expr.type(),
--> 299         [expr.to_pyarrow().as_py()],
    300         max_length=max_length or ibis.options.repr.interactive.max_length,
    301         max_string=max_string or ibis.options.repr.interactive.max_string,
    302         max_depth=max_depth or ibis.options.repr.interactive.max_depth,
    303     )[0]
    304     return Panel(value, expand=False, box=box.SQUARE)

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

File ~/work/ibis/ibis/ibis/backends/duckdb/__init__.py:1400, in Backend.to_pyarrow(self, expr, params, limit, **_)
   1392 def to_pyarrow(
   1393     self,
   1394     expr: ir.Expr,
   (...)
   1398     **_: Any,
   1399 ) -> pa.Table:
-> 1400     table = self._to_duckdb_relation(expr, params=params, limit=limit).arrow()
   1401     return expr.__pyarrow_result__(table)

ConversionException: Conversion Error: Could not convert string 'a string' to INT64
LINE 1: SELECT CAST('a string' AS BIGINT) AS "Cast('a ...
               ^

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

See Also

ibis.coalesce() Value.fill_null()

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 ┃
┡━━━━━━━━╇━━━━━━━┩
│ stringint64 │
├────────┼───────┤
│ 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            ┃
┡━━━━━━━━╇━━━━━━━━━━━━━━┩
│ stringarray<int64> │
├────────┼──────────────┤
│ a     [2]          │
│ b     [3, 5]       │
└────────┴──────────────┘

desc

desc(nulls_first=False)

Sort an expression descending.

Parameters

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

Returns

Name Type Description
Value Sorted expression

See Also

ibis.desc()

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 any null values with the indicated fill value.

Parameters

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

See Also

Value.coalesce() ibis.coalesce()

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.examples.penguins.fetch().limit(5)
>>> t.sex
┏━━━━━━━━┓
┃ sex    ┃
┡━━━━━━━━┩
│ string │
├────────┤
│ male   │
│ female │
│ female │
│ NULL   │
│ female │
└────────┘
>>> t.sex.fill_null("unrecorded").name("sex")
┏━━━━━━━━━━━━┓
┃ sex        ┃
┡━━━━━━━━━━━━┩
│ string     │
├────────────┤
│ male       │
│ female     │
│ female     │
│ unrecorded │
│ female     │
└────────────┘

Returns

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

fillna

fillna(fill_value)

DEPRECATED: use fill_null instead.

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 ┃
┡━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━┩
│ float64float64       │
├────────────────┼───────────────┤
│           39.118.7 │
│           39.517.4 │
│           40.318.0 │
│           NULLNULL │
│           36.719.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 Value | Sequence[Value] Values or expression to check for membership required

Returns

Name Type Description
BooleanValue Expression indicating membership

See Also

Value.notin()

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.memtable({"a": [1, 2, 3], "b": [2, 3, 4]})
>>> t
┏━━━━━━━┳━━━━━━━┓
┃ a      b     ┃
┡━━━━━━━╇━━━━━━━┩
│ int64int64 │
├───────┼───────┤
│     12 │
│     23 │
│     34 │
└───────┴───────┘

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

Return whether this expression is 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.isnull()
┏━━━━━━━━━━━━━━━━━━━━━━━┓
┃ IsNull(bill_depth_mm) ┃
┡━━━━━━━━━━━━━━━━━━━━━━━┩
│ boolean               │
├───────────────────────┤
│ False                 │
│ False                 │
│ False                 │
│ True                  │
│ False                 │
└───────────────────────┘

name

name(name)

Rename an expression to name.

Parameters

Name Type Description Default
name 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 Value | Sequence[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 Type 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 ┃
┡━━━━━━━━━━━╇━━━━━━━━━━━━━━┩
│ stringint64        │
├───────────┼──────────────┤
│ Biscoe   168 │
│ Dream    124 │
│ Torgersen52 │
└───────────┴──────────────┘
>>> t.island.substitute({"Torgersen": "torg", "Biscoe": "bisc"}).name(
...     "island"
... ).value_counts().order_by("island")
┏━━━━━━━━┳━━━━━━━━━━━━━━┓
┃ island  island_count ┃
┡━━━━━━━━╇━━━━━━━━━━━━━━┩
│ stringint64        │
├────────┼──────────────┤
│ Dream 124 │
│ bisc  168 │
│ torg  52 │
└────────┴──────────────┘

to_pandas

to_pandas(**kwargs)

Convert an expression to a pandas or scalar object.

Parameters

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

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.examples.penguins.fetch()
>>> t.to_pandas(limit=5)
species island bill_length_mm bill_depth_mm flipper_length_mm body_mass_g sex year
0 Adelie Torgersen 39.1 18.7 181.0 3750.0 male 2007
1 Adelie Torgersen 39.5 17.4 186.0 3800.0 female 2007
2 Adelie Torgersen 40.3 18.0 195.0 3250.0 female 2007
3 Adelie Torgersen NaN NaN NaN NaN None 2007
4 Adelie Torgersen 36.7 19.3 193.0 3450.0 female 2007

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

See Also

Value.cast() ibis.dtype()

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 ┃
┡━━━━━━━━━╇━━━━━━━━━┩
│ int64string  │
├─────────┼─────────┤
│       11.0     │
│       22       │
│       3hello   │
│       4world   │
└─────────┴─────────┘
>>> 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 ┃
┡━━━━━━━━━╇━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━┩
│ int64stringstringint64              │
├─────────┼─────────┼────────────────────┼────────────────────┤
│       11.0    1                 1 │
│       22      2                 2 │
│       3hello  3                 NULL │
│       4world  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(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 nth 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 Return a “top k” expression.
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, how=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
how Any DEPRECATED 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       ┃
┡━━━━━━━╇━━━━━━━━╇━━━━━━━━━┩
│ int64stringfloat64 │
├───────┼────────┼─────────┤
│     1a     4.0 │
│     2a     4.1 │
│     2a     4.2 │
└───────┴────────┴─────────┘
>>> t.group_by("a").agg(arb=t.b.arbitrary(), c=t.c.sum()).order_by("a")
┏━━━━━━━┳━━━━━━━━┳━━━━━━━━━┓
┃ a      arb     c       ┃
┡━━━━━━━╇━━━━━━━━╇━━━━━━━━━┩
│ int64stringfloat64 │
├───────┼────────┼─────────┤
│     1a     4.0 │
│     2a     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 ┃
┡━━━━━━━━━╇━━━━━━━━━━━━━━━┩
│ stringint64         │
├─────────┼───────────────┤
│ 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 ┃
┡━━━━━━━━╇━━━━━━━━━━━┩
│ int64float64   │
├────────┼───────────┤
│      10.333333 │
│      10.333333 │
│      20.833333 │
│      20.833333 │
│      20.833333 │
│      31.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 ┃
┡━━━━━━━╇━━━━━━━━━╇━━━━━━━━╇━━━━━━━━┩
│ int64stringint64int64  │
├───────┼─────────┼────────┼────────┤
│     1a      33 │
│     2a      23 │
│     3a      13 │
│     4b      23 │
│     5b      33 │
│     6c      23 │
└───────┴─────────┴────────┴────────┘
>>> t.mutate(cummax=t.values.cummax(where=t.grouper != "c", group_by=t.grouper)).order_by(
...     t.id
... )
┏━━━━━━━┳━━━━━━━━━┳━━━━━━━━┳━━━━━━━━┓
┃ id     grouper  values  cummax ┃
┡━━━━━━━╇━━━━━━━━━╇━━━━━━━━╇━━━━━━━━┩
│ int64stringint64int64  │
├───────┼─────────┼────────┼────────┤
│     1a      33 │
│     2a      23 │
│     3a      13 │
│     4b      22 │
│     5b      33 │
│     6c      2NULL │
└───────┴─────────┴────────┴────────┘

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 ┃
┡━━━━━━━╇━━━━━━━━━╇━━━━━━━━╇━━━━━━━━┩
│ int64stringint64int64  │
├───────┼─────────┼────────┼────────┤
│     1a      33 │
│     2a      22 │
│     3a      11 │
│     4b      21 │
│     5b      31 │
│     6c      21 │
└───────┴─────────┴────────┴────────┘
>>> t.mutate(cummin=t.values.cummin(where=t.grouper != "c", group_by=t.grouper)).order_by(
...     t.id
... )
┏━━━━━━━┳━━━━━━━━━┳━━━━━━━━┳━━━━━━━━┓
┃ id     grouper  values  cummin ┃
┡━━━━━━━╇━━━━━━━━━╇━━━━━━━━╇━━━━━━━━┩
│ int64stringint64int64  │
├───────┼─────────┼────────┼────────┤
│     1a      33 │
│     2a      22 │
│     3a      11 │
│     4b      22 │
│     5b      32 │
│     6c      2NULL │
└───────┴─────────┴────────┴────────┘

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  ┃
┡━━━━━━━━╇━━━━━━━┩
│ int64int64 │
├────────┼───────┤
│      10 │
│      10 │
│      21 │
│      21 │
│      21 │
│      32 │
└────────┴───────┘

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 ┃
┡━━━━━━━╇━━━━━━━━━╇━━━━━━━━━━━━┩
│ int64float64float64    │
├───────┼─────────┼────────────┤
│  20071899.6NULL │
│  20081928.21899.6 │
│  20092037.91928.2 │
│  20101955.22037.9 │
└───────┴─────────┴────────────┘
>>> t.mutate(total_lead=t.total.lag(2, 0))
┏━━━━━━━┳━━━━━━━━━┳━━━━━━━━━━━━┓
┃ year   total    total_lead ┃
┡━━━━━━━╇━━━━━━━━━╇━━━━━━━━━━━━┩
│ int64float64float64    │
├───────┼─────────┼────────────┤
│  20071899.60.0 │
│  20081928.20.0 │
│  20092037.91899.6 │
│  20101955.21928.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 ┃
┡━━━━━━━╇━━━━━━━━━╇━━━━━━━━━━━━┩
│ int64float64float64    │
├───────┼─────────┼────────────┤
│  20071899.61928.2 │
│  20081928.22037.9 │
│  20092037.91955.2 │
│  20101955.2NULL │
└───────┴─────────┴────────────┘
>>> t.mutate(total_lead=t.total.lead(2, 0))
┏━━━━━━━┳━━━━━━━━━┳━━━━━━━━━━━━┓
┃ year   total    total_lead ┃
┡━━━━━━━╇━━━━━━━━━╇━━━━━━━━━━━━┩
│ int64float64float64    │
├───────┼─────────┼────────────┤
│  20071899.62037.9 │
│  20081928.21955.2 │
│  20092037.90.0 │
│  20101955.20.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 ┃
┡━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━┩
│ stringfloat64           │
├───────────┼───────────────────┤
│ Chinstrap18.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 ┃
┡━━━━━━━━━━━╇━━━━━━━━━━━━━━━━┩
│ stringstring         │
├───────────┼────────────────┤
│ Biscoe   Gentoo         │
│ Dream    Chinstrap      │
│ TorgersenAdelie         │
└───────────┴────────────────┘

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   ┃
┡━━━━━━━━╇━━━━━━━┩
│ int64int64 │
├────────┼───────┤
│      13 │
│      23 │
│      33 │
│      43 │
│      53 │
│      63 │
└────────┴───────┘
>>> t.mutate(nth=t.values.nth(7))
┏━━━━━━━━┳━━━━━━━┓
┃ values  nth   ┃
┡━━━━━━━━╇━━━━━━━┩
│ int64int64 │
├────────┼───────┤
│      1NULL │
│      2NULL │
│      3NULL │
│      4NULL │
│      5NULL │
│      6NULL │
└────────┴───────┘

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 ┃
┡━━━━━━━━╇━━━━━━━┩
│ int64int64 │
├────────┼───────┤
│      10 │
│      10 │
│      21 │
│      21 │
│      22 │
│      32 │
└────────┴───────┘

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 ┃
┡━━━━━━━━╇━━━━━━━━━━━━━━┩
│ int64float64      │
├────────┼──────────────┤
│      10.0 │
│      10.0 │
│      20.4 │
│      20.4 │
│      20.4 │
│      31.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 ┃
┡━━━━━━━━━━━╇━━━━━━━━━━━━━━━━┩
│ stringfloat64        │
├───────────┼────────────────┤
│ Adelie   21.200 │
│ Chinstrap20.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 ┃
┡━━━━━━━━━━━╇━━━━━━━━━━━━━┩
│ stringstring      │
├───────────┼─────────────┤
│ Biscoe   Gentoo      │
│ Dream    Chinstrap   │
│ TorgersenAdelie      │
└───────────┴─────────────┘

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  ┃
┡━━━━━━━━╇━━━━━━━┩
│ int64int64 │
├────────┼───────┤
│      10 │
│      10 │
│      22 │
│      22 │
│      22 │
│      35 │
└────────┴───────┘

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]

topk

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

Return a “top k” expression.

Computes a Table containing the top k values by a certain metric (defaults to count).

Parameters

Name Type Description Default
k int The number of rows to return. required
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. A suitable name will be automatically generated if not provided. 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   CountStar(diamonds) ┃
┡━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━┩
│ stringint64               │
├────────┼─────────────────────┤
│ 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) ┃
┡━━━━━━━━╇━━━━━━━━━━━━━┩
│ stringfloat64     │
├────────┼─────────────┤
│ J     5323.818020 │
│ I     5091.874954 │
│ H     4486.669196 │
└────────┴─────────────┘

Compute the top 2 diamond colors by max carat:

>>> t.color.topk(2, by=t.carat.max(), name="max_carat")
┏━━━━━━━━┳━━━━━━━━━━━┓
┃ color   max_carat ┃
┡━━━━━━━━╇━━━━━━━━━━━┩
│ stringfloat64   │
├────────┼───────────┤
│ J     5.01 │
│ H     4.13 │
└────────┴───────────┘

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. A suitable name will be automatically generated if not provided. 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 ┃
┡━━━━━━━━╇━━━━━━━━━━━━━┩
│ stringint64       │
├────────┼─────────────┤
│ 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  ┃
┡━━━━━━━━╇━━━━━━━┩
│ stringint64 │
├────────┼───────┤
│ b     1 │
│ a     2 │
│ c     3 │
│ d     4 │
└────────┴───────┘

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 ┃
┡━━━━━━━━━━━╇━━━━━━━━━━━━━━━┩
│ stringint64         │
├───────────┼───────────────┤
│ Adelie   15 │
│ Chinstrap2 │
└───────────┴───────────────┘

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:

  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

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[365], line 1
----> 1 ibis.literal("foobar", type="int64")  

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

File ~/work/ibis/ibis/ibis/expr/types/generic.py:2937, in literal(value, type)
   2932         raise TypeError(
   2933             f"Ibis literal {value!r} cannot be safely coerced to datatype {type}"
   2934         )
   2936 dtype = dt.infer(value) if type is None else dt.dtype(type)
-> 2937 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   ┃
┡━━━━━━━━━╇━━━━━━━━╇━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━╇━━━━━━━━╇━━━━━━━━┩
│ stringstringstringstringstringstringstringstring │
├─────────┼────────┼────────────────┼───────────────┼───────────────────┼─────────────┼────────┼────────┤
│ string stringfloat64       float64      int64            int64      stringint64  │
└─────────┴────────┴────────────────┴───────────────┴───────────────────┴─────────────┴────────┴────────┘

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[380], 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()

┌──────┐
│ None │
└──────┘
>>> 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 Type 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(*args)

Return the first non-null value from args.

Parameters

Name Type Description Default
args Any Arguments from which to choose the first non-null value ()

Returns

Name Type Description
Value Coalesced expression

See Also

Value.coalesce() Value.fill_null()

Examples

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

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

least

ibis.least(*args)

Compute the smallest value among the supplied arguments.

Parameters

Name Type Description Default
args Any Arguments to choose from ()

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

Compute the largest value among the supplied arguments.

Parameters

Name Type Description Default
args Any Arguments to choose from ()

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

See Also

BooleanValue.ifelse()

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.

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

See Also

Value.cases() Value.substitute()

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

See Also

Value.asc()

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.examples.penguins.fetch()
>>> t[["species", "year"]].order_by(ibis.asc("year")).head()
┏━━━━━━━━━┳━━━━━━━┓
┃ species  year  ┃
┡━━━━━━━━━╇━━━━━━━┩
│ stringint64 │
├─────────┼───────┤
│ 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

See Also

Value.desc()

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.examples.penguins.fetch()
>>> t[["species", "year"]].order_by(ibis.desc("year")).head()
┏━━━━━━━━━┳━━━━━━━┓
┃ species  year  ┃
┡━━━━━━━━━╇━━━━━━━┩
│ stringint64 │
├─────────┼───────┤
│ 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`

See Also

Table.compile()

Back to top