Generic expressions

Scalars and columns of any element type.

Value

Value(arg)

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

Methods

Name	Description
as_scalar	Tell ibis to treat the expression as a scalar.
as_table	Convert a Scalar, Column, or Table to a Table.
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.
compile	Compile expr to a SQL string (for SQL backends) or a LazyFrame (for the polars backend).
desc	Sort an expression in descending order.
equals	Return whether this expression is structurally equivalent to other.
execute	Execute an expression against its backend if one exists.
fill_null	Replace NULLs with the given value. Does NOT affect NaN and inf values.
fillna	DEPRECATED: use fill_null instead, which acts exactly the same.
get_backend	Get the current Ibis backend of the expression.
get_name	Return the name of this expression.
group_concat	Concatenate values using the indicated separator to produce a string.
hash	Compute an integer hash value.
identical_to	Like ==, but always returns non-NULL, even when comparing NULLs.
isin	Check whether this expression is 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 is 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.
pipe	Compose f with self.
substitute	Replace values given in values with replacement.
to_csv	Write the results of executing the given expression to a CSV file.
to_delta	Write the results of executing the given expression to a Delta Lake table.
to_json	Write the results of expr to a json file of [{column -> value}, …] objects.
to_pandas	Convert a table expression to a pandas DataFrame.
to_pandas_batches	Execute expression and return an iterator of pandas DataFrames.
to_parquet	Write the results of executing the given expression to a parquet file.
to_parquet_dir	Write the results of executing the given expression to a parquet file in a directory.
to_polars	Execute expression and return results as a polars dataframe.
to_pyarrow	Execute expression to a pyarrow object.
to_pyarrow_batches	Execute expression and return a RecordBatchReader.
to_sql	Compile to a formatted SQL string.
to_torch	Execute an expression and return results as a dictionary of torch tensors.
to_xlsx	Write a table to an Excel file.
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.
unbind	Return an expression built on UnboundTable instead of backend-specific objects.
visualize	Visualize an expression as a GraphViz graph in the browser.

as_scalar

as_scalar()

Tell ibis to treat the expression as a scalar.

Ibis cannot know until execution time whether a Column or Table expression contains only one row or many rows,

This method is a way to explicitly tell ibis to trust you that this expression will only contain one row at execution time. This allows you to use this expression with other tables.

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

Convert a Scalar, Column, or Table to a Table.

• Calling this on a Table is a no-op.
• Calling this on a Column will return a single-column table.
• Calling this on a Scalar will return a single-row, single-column table.

Returns

Name	Type	Description
	Table	A table expression

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

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  ┃
┡━━━━━━━╇━━━━━━━━╇━━━━━━━╇━━━━━━━━━┩
│ 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

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/1w0931lgp02pk2yqql58b10fnfmvcf9x-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/1w0931lgp02pk2yqql58b10fnfmvcf9x-ibis-3.12/lib/python3.12/site-packages/IPython/lib/pretty.py:420, in RepresentationPrinter.pretty(self, obj)
    409                         return meth(obj, self, cycle)
    410                 if (
    411                     cls is not object
    412                     # check if cls defines __repr__
   (...)    418                     and callable(_safe_getattr(cls, "__repr__", None))
    419                 ):
--> 420                     return _repr_pprint(obj, self, cycle)
    422     return _default_pprint(obj, self, cycle)
    423 finally:

File /nix/store/1w0931lgp02pk2yqql58b10fnfmvcf9x-ibis-3.12/lib/python3.12/site-packages/IPython/lib/pretty.py:795, in _repr_pprint(obj, p, cycle)
    793 """A pprint that just redirects to the normal repr function."""
    794 # Find newlines and replace them with p.break_()
--> 795 output = repr(obj)
    796 lines = output.splitlines()
    797 with p.group():

File ~/work/ibis/ibis/ibis/expr/types/core.py:54, in Expr.__repr__(self)
     52 def __repr__(self) -> str:
     53     if ibis.options.interactive:
---> 54         return capture_rich_renderable(self)
     55     else:
     56         return self._noninteractive_repr()

File ~/work/ibis/ibis/ibis/expr/types/rich.py:48, in capture_rich_renderable(renderable)
     46 console = Console(force_terminal=False)
     47 with _with_rich_display_disabled(), console.capture() as capture:
---> 48     console.print(renderable)
     49 return capture.get().rstrip()

File /nix/store/1w0931lgp02pk2yqql58b10fnfmvcf9x-ibis-3.12/lib/python3.12/site-packages/rich/console.py:1724, in Console.print(self, sep, end, style, justify, overflow, no_wrap, emoji, markup, highlight, width, height, crop, soft_wrap, new_line_start, *objects)
   1722 if style is None:
   1723     for renderable in renderables:
-> 1724         extend(render(renderable, render_options))
   1725 else:
   1726     render_style = self.get_style(style)

File /nix/store/1w0931lgp02pk2yqql58b10fnfmvcf9x-ibis-3.12/lib/python3.12/site-packages/rich/console.py:1325, in Console.render(self, renderable, options)
   1323 renderable = rich_cast(renderable)
   1324 if hasattr(renderable, "__rich_console__") and not isclass(renderable):
-> 1325     render_iterable = renderable.__rich_console__(self, _options)
   1326 elif isinstance(renderable, str):
   1327     text_renderable = self.render_str(
   1328         renderable, highlight=_options.highlight, markup=_options.markup
   1329     )

File ~/work/ibis/ibis/ibis/expr/types/core.py:75, in Expr.__rich_console__(self, console, options)
     73 try:
     74     if opts.interactive:
---> 75         rich_object = to_rich(self, console_width=console_width)
     76     else:
     77         rich_object = Text(self._noninteractive_repr())

File ~/work/ibis/ibis/ibis/expr/types/rich.py:66, in to_rich(expr, max_rows, max_columns, max_length, max_string, max_depth, console_width)
     63 from ibis.expr.types._rich import to_rich_scalar, to_rich_table
     65 if isinstance(expr, ir.Scalar):
---> 66     return to_rich_scalar(
     67         expr, max_length=max_length, max_string=max_string, max_depth=max_depth
     68     )
     69 else:
     70     return to_rich_table(
     71         expr,
     72         max_rows=max_rows,
   (...)     77         console_width=console_width,
     78     )

File ~/work/ibis/ibis/ibis/expr/types/_rich.py:270, in to_rich_scalar(expr, max_length, max_string, max_depth)
    263 def to_rich_scalar(
    264     expr: Scalar,
    265     *,
   (...)    268     max_depth: int | None = None,
    269 ) -> Panel:
--> 270     value = expr.to_pyarrow().as_py()
    272     if value is None:
    273         formatted_value = Text.styled("NULL", style="dim")

File ~/work/ibis/ibis/ibis/expr/types/generic.py:1452, in Scalar.to_pyarrow(self, params, limit, **kwargs)
   1444 @experimental
   1445 def to_pyarrow(
   1446     self,
   (...)   1450     **kwargs: Any,
   1451 ) -> pa.Scalar:
-> 1452     return super().to_pyarrow(params=params, limit=limit, **kwargs)

File ~/work/ibis/ibis/ibis/expr/types/core.py:604, in Expr.to_pyarrow(self, params, limit, **kwargs)
    574 @experimental
    575 def to_pyarrow(
    576     self,
   (...)    580     **kwargs: Any,
    581 ) -> pa.Table | pa.Array | pa.Scalar:
    582     """Execute expression to a pyarrow object.
    583 
    584     This method is eager and will execute the associated expression
   (...)    602         If the passed expression is a Scalar, a pyarrow scalar is returned.
    603     """
--> 604     return self._find_backend(use_default=True).to_pyarrow(
    605         self, params=params, limit=limit, **kwargs
    606     )

File ~/work/ibis/ibis/ibis/backends/duckdb/__init__.py:1400, in Backend.to_pyarrow(self, expr, params, limit, **kwargs)
   1387 def to_pyarrow(
   1388     self,
   1389     expr: ir.Expr,
   (...)   1394     **kwargs: Any,
   1395 ) -> pa.Table:
   1396     from ibis.backends.duckdb.converter import DuckDBPyArrowData
   1398     table = self._to_duckdb_relation(
   1399         expr, params=params, limit=limit, **kwargs
-> 1400     ).to_arrow_table()
   1401     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

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 ┃
┡━━━━━━━━╇━━━━━━━┩
│ 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, 5, 3, 2]

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]       │
└────────┴──────────────┘

compile

compile(limit=None, params=None, pretty=False)

Compile expr to a SQL string (for SQL backends) or a LazyFrame (for the polars backend).

Parameters

Name	Type	Description	Default
limit	int | None	An integer to effect a specific row limit. A value of None means “no limit”. The default is in ibis/config.py.	None
params	Mapping[ir.Value, Any] | None	Mapping of scalar parameter expressions to value	None
pretty	bool	In case of SQL backends, return a pretty formatted SQL query.	False

Returns

Name	Type	Description
	str | pl.LazyFrame	A SQL string or a LazyFrame object, depending on the backend of self.

Examples

>>> import ibis
>>> d = {"a": [1, 2, 3], "b": [4, 5, 6]}
>>> con = ibis.duckdb.connect()
>>> t = con.create_table("t", d)
>>> expr = t.mutate(c=t.a + t.b)
>>> expr.compile()

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

If you want to see the pretty formatted SQL query, set pretty to True.

>>> expr.compile(pretty=True)

'SELECT\n  "t0"."a",\n  "t0"."b",\n  "t0"."a" + "t0"."b" AS "c"\nFROM "memory"."main"."t" AS "t0"'

If the expression does not have a backend, an error will be raised.

>>> t = ibis.memtable(d)
>>> expr = t.mutate(c=t.a + t.b)
>>> expr.compile()  # quartodoc: +EXPECTED_FAILURE

---------------------------------------------------------------------------
IbisError                                 Traceback (most recent call last)
Cell In[56], line 3
      1 t = ibis.memtable(d)
      2 expr = t.mutate(c=t.a + t.b)
----> 3 expr.compile()  # quartodoc: +EXPECTED_FAILURE

File ~/work/ibis/ibis/ibis/expr/types/core.py:531, in Expr.compile(self, limit, params, pretty)
    478 def compile(
    479     self,
    480     *,
   (...)    483     pretty: bool = False,
    484 ) -> str | pl.LazyFrame:
    485     r"""Compile `expr` to a SQL string (for SQL backends) or a LazyFrame (for the polars backend).
    486 
    487     Parameters
   (...)    529     [`Table.to_sql()`](./expression-tables.qmd#ibis.expr.types.relations.Table.to_sql)
    530     """
--> 531     return self._find_backend().compile(
    532         self, limit=limit, params=params, pretty=pretty
    533     )

File ~/work/ibis/ibis/ibis/expr/types/core.py:337, in Expr._find_backend(self, use_default)
    335     default = _default_backend() if use_default else None
    336     if default is None:
--> 337         raise IbisError(
    338             "Expression depends on no backends, and found no default"
    339         )
    340     return default
    342 if len(backends) > 1:

IbisError: Expression depends on no backends, and found no default

See Also

Value.compile() Table.compile() Value.to_sql() Table.to_sql()

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

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 │
└─────────┘

equals

equals(other, /)

Return whether this expression is structurally equivalent to other.

If you want to produce an equality expression, use == syntax.

Parameters

Name	Type	Description	Default
other		Another expression	required

Examples

>>> import ibis
>>> t1 = ibis.table(dict(a="int"), name="t")
>>> t2 = ibis.table(dict(a="int"), name="t")
>>> t1.equals(t2)

True

>>> v = ibis.table(dict(a="string"), name="v")
>>> t1.equals(v)

False

execute

execute(limit='default', params=None, **kwargs)

Execute an expression against its backend if one exists.

Parameters

Name	Type	Description	Default
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.	'default'
params	Mapping[ir.Value, Any] | None	Mapping of scalar parameter expressions to value	None
kwargs	Any	Keyword arguments	{}

Examples

>>> import ibis
>>> t = ibis.examples.penguins.fetch()
>>> t.execute()

	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
...	...	...	...	...	...	...	...	...
339	Chinstrap	Dream	55.8	19.8	207.0	4000.0	male	2009
340	Chinstrap	Dream	43.5	18.1	202.0	3400.0	female	2009
341	Chinstrap	Dream	49.6	18.2	193.0	3775.0	male	2009
342	Chinstrap	Dream	50.8	19.0	210.0	4100.0	male	2009
343	Chinstrap	Dream	50.2	18.7	198.0	3775.0	female	2009

344 rows × 8 columns

Scalar parameters can be supplied dynamically during execution.

>>> species = ibis.param("string")
>>> expr = t.filter(t.species == species).order_by(t.bill_length_mm)
>>> expr.execute(limit=3, params={species: "Gentoo"})

	species	island	bill_length_mm	bill_depth_mm	flipper_length_mm	body_mass_g	sex	year
0	Gentoo	Biscoe	40.9	13.7	214	4650	female	2007
1	Gentoo	Biscoe	41.7	14.7	210	4700	female	2009
2	Gentoo	Biscoe	42.0	13.5	210	4150	female	2007

See Also

Table.to_pandas() Value.to_pandas()

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

See Also

Value.coalesce() ibis.coalesce() Value.isnull() FloatingValue.isnan() FloatingValue.isinf()

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.

get_backend

get_backend()

Get the current Ibis backend of the expression.

Returns

Name	Type	Description
	BaseBackend	The Ibis backend.

Examples

>>> import ibis
>>> con = ibis.duckdb.connect()
>>> t = con.create_table("t", {"id": [1, 2, 3]})
>>> t.get_backend()

<ibis.backends.duckdb.Backend at 0x7fff680c7260>

See Also

ibis.get_backend()

get_name

get_name()

Return the name of this expression.

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.

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

┌─────────────────────┐
│ 8628213525773890682 │
└─────────────────────┘

identical_to

identical_to(other, /)

Like ==, but always returns non-NULL, even when comparing NULLs.

With regular equality, NULL == <anything> is always NULL, even for NULL == NULL.

In contrast, identical_to treats NULL as a singleton value, so that

• NULL.identical_to(NULL) is True.
• NULL.identical_to(<non-null>) is False.

This 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
>>> t = ibis.memtable(
...     [
...         (1, 1),
...         (1, 2),
...         (1, None),
...         (None, None),
...     ],
...     schema={"a": "int64", "b": "int64"},
... )
>>> t.mutate(
...     eq=_.a == _.b,
...     neq=_.a != _.b,
...     identical=_.a.identical_to(_.b),
...     not_identical=~_.a.identical_to(_.b),
... )

┏━━━━━━━┳━━━━━━━┳━━━━━━━━━┳━━━━━━━━━┳━━━━━━━━━━━┳━━━━━━━━━━━━━━━┓
┃ a     ┃ b     ┃ eq      ┃ neq     ┃ identical ┃ not_identical ┃
┡━━━━━━━╇━━━━━━━╇━━━━━━━━━╇━━━━━━━━━╇━━━━━━━━━━━╇━━━━━━━━━━━━━━━┩
│ int64 │ int64 │ boolean │ boolean │ boolean   │ boolean       │
├───────┼───────┼─────────┼─────────┼───────────┼───────────────┤
│     1 │     1 │ True    │ False   │ True      │ False         │
│     1 │     2 │ False   │ True    │ False     │ True          │
│     1 │  NULL │ NULL    │ NULL    │ False     │ True          │
│  NULL │  NULL │ NULL    │ NULL    │ True      │ False         │
└───────┴───────┴─────────┴─────────┴───────────┴───────────────┘

isin

isin(values, /)

Check whether this expression is in values.

NULL values in the input are propagated in the output. If the values argument contains any NULL values, then ibis follows the SQL behavior of returning NULL (not False) when self is not present. See examples below 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	True if self is contained in values, False otherwise.

See Also

Value.notin()

Examples

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

Checking for values in literals:

>>> t.mutate(
...     a_in_12=t.a.isin([1, 2]),
...     a_in_12None=t.a.isin([1, 2, None]),
... )

┏━━━━━━━┳━━━━━━━┳━━━━━━━━━┳━━━━━━━━━━━━━┓
┃ a     ┃ b     ┃ a_in_12 ┃ a_in_12None ┃
┡━━━━━━━╇━━━━━━━╇━━━━━━━━━╇━━━━━━━━━━━━━┩
│ int64 │ int64 │ boolean │ boolean     │
├───────┼───────┼─────────┼─────────────┤
│     1 │     1 │ True    │ True        │
│     2 │     2 │ True    │ True        │
│     3 │     9 │ False   │ NULL        │
│  NULL │  NULL │ NULL    │ NULL        │
└───────┴───────┴─────────┴─────────────┘

Checking for values in columns of the same table:

>>> t.mutate(
...     a_in_b=t.a.isin(t.b),
...     a_in_b_no_null=t.a.isin(t.b.fill_null(0)),
...     a_in_b_plus_1=t.a.isin(t.b + 1),
... )

┏━━━━━━━┳━━━━━━━┳━━━━━━━━━┳━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━┓
┃ a     ┃ b     ┃ a_in_b  ┃ a_in_b_no_null ┃ a_in_b_plus_1 ┃
┡━━━━━━━╇━━━━━━━╇━━━━━━━━━╇━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━┩
│ int64 │ int64 │ boolean │ boolean        │ boolean       │
├───────┼───────┼─────────┼────────────────┼───────────────┤
│     1 │     1 │ True    │ True           │ NULL          │
│     2 │     2 │ True    │ True           │ True          │
│     3 │     9 │ NULL    │ False          │ True          │
│  NULL │  NULL │ NULL    │ NULL           │ NULL          │
└───────┴───────┴─────────┴────────────────┴───────────────┘

Checking for values in a column from a different table:

>>> t2 = ibis.memtable({"x": [1, 2, 99], "y": [1, 2, None]})
>>> t.mutate(
...     a_in_x=t.a.isin(t2.x),
...     a_in_y=t.a.isin(t2.y),
...     a_in_y_plus_1=t.a.isin(t2.y + 1),
... )

┏━━━━━━━┳━━━━━━━┳━━━━━━━━━┳━━━━━━━━━┳━━━━━━━━━━━━━━━┓
┃ a     ┃ b     ┃ a_in_x  ┃ a_in_y  ┃ a_in_y_plus_1 ┃
┡━━━━━━━╇━━━━━━━╇━━━━━━━━━╇━━━━━━━━━╇━━━━━━━━━━━━━━━┩
│ int64 │ int64 │ boolean │ boolean │ boolean       │
├───────┼───────┼─────────┼─────────┼───────────────┤
│     1 │     1 │ True    │ True    │ NULL          │
│     2 │     2 │ True    │ True    │ True          │
│     3 │     9 │ False   │ NULL    │ True          │
│  NULL │  NULL │ NULL    │ NULL    │ NULL          │
└───────┴───────┴─────────┴─────────┴───────────────┘

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.

See Also

Value.fill_null() FloatingValue.isnan() FloatingValue.isinf()

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]})
>>> t.a

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

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

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

notin

notin(values, /)

Check whether this expression is not in values.

Opposite of Value.isin().

NULL values in the input are propagated in the output. If the values argument contains any NULL values, then ibis follows the SQL behavior of returning NULL (not False) when self is present. See examples below for details.

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	True if self is not in values, False otherwise.

Examples

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

Checking for values in literals:

>>> t.mutate(
...     a_notin_12=t.a.notin([1, 2]),
...     a_notin_12None=t.a.notin([1, 2, None]),
... )

┏━━━━━━━┳━━━━━━━┳━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━┓
┃ a     ┃ b     ┃ a_notin_12 ┃ a_notin_12None ┃
┡━━━━━━━╇━━━━━━━╇━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━┩
│ int64 │ int64 │ boolean    │ boolean        │
├───────┼───────┼────────────┼────────────────┤
│     1 │     1 │ False      │ False          │
│     2 │     2 │ False      │ False          │
│     3 │     9 │ True       │ NULL           │
│  NULL │  NULL │ NULL       │ NULL           │
└───────┴───────┴────────────┴────────────────┘

Checking for values in columns of the same table:

>>> t.mutate(
...     a_notin_b=t.a.notin(t.b),
...     a_notin_b_no_null=t.a.notin(t.b.fill_null(0)),
...     a_notin_b_plus_1=t.a.notin(t.b + 1),
... )

┏━━━━━━━┳━━━━━━━┳━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━┓
┃ a     ┃ b     ┃ a_notin_b ┃ a_notin_b_no_null ┃ a_notin_b_plus_1 ┃
┡━━━━━━━╇━━━━━━━╇━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━┩
│ int64 │ int64 │ boolean   │ boolean           │ boolean          │
├───────┼───────┼───────────┼───────────────────┼──────────────────┤
│     1 │     1 │ False     │ False             │ NULL             │
│     2 │     2 │ False     │ False             │ False            │
│     3 │     9 │ NULL      │ True              │ False            │
│  NULL │  NULL │ NULL      │ NULL              │ NULL             │
└───────┴───────┴───────────┴───────────────────┴──────────────────┘

Checking for values in a column from a different table:

>>> t2 = ibis.memtable({"x": [1, 2, 99], "y": [1, 2, None]})
>>> t.mutate(
...     a_notin_x=t.a.notin(t2.x),
...     a_notin_y=t.a.notin(t2.y),
...     a_notin_y_plus_1=t.a.notin(t2.y + 1),
... )

┏━━━━━━━┳━━━━━━━┳━━━━━━━━━━━┳━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━┓
┃ a     ┃ b     ┃ a_notin_x ┃ a_notin_y ┃ a_notin_y_plus_1 ┃
┡━━━━━━━╇━━━━━━━╇━━━━━━━━━━━╇━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━┩
│ int64 │ int64 │ boolean   │ boolean   │ boolean          │
├───────┼───────┼───────────┼───────────┼──────────────────┤
│     1 │     1 │ False     │ False     │ NULL             │
│     2 │     2 │ False     │ False     │ False            │
│     3 │     9 │ True      │ NULL      │ False            │
│  NULL │  NULL │ NULL      │ NULL      │ NULL             │
└───────┴───────┴───────────┴───────────┴──────────────────┘

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

pipe

pipe(f, /, *args, **kwargs)

Compose f with self.

Parameters

Name	Type	Description	Default
f		If the expression needs to be passed as anything other than the first argument to the function, pass a tuple with the argument name. For example, (f, ‘data’) if the function f expects a ‘data’ keyword	required
args	Any	Positional arguments to f	()
kwargs	Any	Keyword arguments to f	{}

Examples

>>> import ibis
>>> t = ibis.memtable(
...     {
...         "a": [5, 10, 15],
...         "b": ["a", "b", "c"],
...     }
... )
>>> f = lambda a: (a + 1).name("a")
>>> g = lambda a: (a * 2).name("a")
>>> result1 = t.a.pipe(f).pipe(g)
>>> result1

┏━━━━━━━┓
┃ a     ┃
┡━━━━━━━┩
│ int64 │
├───────┤
│    12 │
│    22 │
│    32 │
└───────┘

>>> result2 = g(f(t.a))  # equivalent to the above
>>> result1.equals(result2)

True

Returns

Name	Type	Description
	Expr	Result type of passed function

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

See Also

Value.cases() 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_csv

to_csv(path, /, *, params=None, **kwargs)

Write the results of executing the given expression to a CSV file.

This method is eager and will execute the associated expression immediately.

See https://arrow.apache.org/docs/python/generated/pyarrow.csv.CSVWriter.html for details.

Parameters

Name	Type	Description	Default
path	str | Path	The data target. A string or Path where the CSV file will be written.	required
params	Mapping[ir.Scalar, Any] | None	Mapping of scalar parameter expressions to value.	None
**kwargs	Any	Additional keyword arguments passed to pyarrow.csv.CSVWriter	{}

to_delta

to_delta(path, /, *, params=None, **kwargs)

Write the results of executing the given expression to a Delta Lake table.

This method is eager and will execute the associated expression immediately.

Parameters

Name	Type	Description	Default
path	str | Path	The data target. A string or Path to the Delta Lake table directory.	required
params	Mapping[ir.Scalar, Any] | None	Mapping of scalar parameter expressions to value.	None
**kwargs	Any	Additional keyword arguments passed to deltalake.writer.write_deltalake method	{}

to_json

to_json(path, /, **kwargs)

Write the results of expr to a json file of [{column -> value}, …] objects.

This method is eager and will execute the associated expression immediately.

Parameters

Name	Type	Description	Default
path	str | Path	The data target. A string or Path where the JSON file will be written.	required
kwargs	Any	Additional, backend-specific keyword arguments.	{}

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

to_pandas_batches

to_pandas_batches(limit=None, params=None, chunk_size=1000000, **kwargs)

Execute expression and return an iterator of pandas DataFrames.

This method is eager and will execute the associated expression immediately.

Parameters

Name	Type	Description	Default
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
params	Mapping[ir.Value, Any] | None	Mapping of scalar parameter expressions to value.	None
chunk_size	int	Maximum number of rows in each returned DataFrame.	1000000
kwargs	Any	Keyword arguments	{}

Returns

Name	Type	Description
	Iterator[pd.DataFrame]

to_parquet

to_parquet(path, /, *, params=None, **kwargs)

Write the results of executing the given expression to a parquet file.

This method is eager and will execute the associated expression immediately.

See https://arrow.apache.org/docs/python/generated/pyarrow.parquet.ParquetWriter.html for details.

Parameters

Name	Type	Description	Default
path	str | Path	A string or Path where the Parquet file will be written.	required
params	Mapping[ir.Scalar, Any] | None	Mapping of scalar parameter expressions to value.	None
**kwargs	Any	Additional keyword arguments passed to pyarrow.parquet.ParquetWriter	{}

Examples

Write out an expression to a single parquet file.

>>> import ibis
>>> import tempfile
>>> penguins = ibis.examples.penguins.fetch()
>>> penguins.to_parquet(tempfile.mktemp())

Partition on a single column.

>>> penguins.to_parquet(tempfile.mkdtemp(), partition_by="year")

Partition on multiple columns.

>>> penguins.to_parquet(tempfile.mkdtemp(), partition_by=("year", "island"))

NoteHive-partitioned output is currently only supported when using DuckDB

to_parquet_dir

to_parquet_dir(directory, /, *, params=None, **kwargs)

Write the results of executing the given expression to a parquet file in a directory.

This method is eager and will execute the associated expression immediately.

See https://arrow.apache.org/docs/python/generated/pyarrow.dataset.write_dataset.html for details.

Parameters

Name	Type	Description	Default
directory	str | Path	The data target. A string or Path to the directory where the parquet file will be written.	required
params	Mapping[ir.Scalar, Any] | None	Mapping of scalar parameter expressions to value.	None
**kwargs	Any	Additional keyword arguments passed to pyarrow.dataset.write_dataset	{}

to_polars

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

Execute expression and return results as a polars dataframe.

This method is eager and will execute the associated expression immediately.

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	A polars dataframe holding the results of the executed expression.

to_pyarrow

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

Execute expression to a pyarrow object.

This method is eager and will execute the associated expression immediately.

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
	result	If the passed expression is a Table, a pyarrow table is returned. If the passed expression is a Column, a pyarrow array is returned. If the passed expression is a Scalar, a pyarrow scalar is returned.

to_pyarrow_batches

to_pyarrow_batches(limit=None, params=None, chunk_size=1000000, **kwargs)

Execute expression and return a RecordBatchReader.

This method is eager and will execute the associated expression immediately.

Parameters

Name	Type	Description	Default
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
params	Mapping[ir.Value, Any] | None	Mapping of scalar parameter expressions to value.	None
chunk_size	int	Maximum number of rows in each returned record batch.	1000000
kwargs	Any	Keyword arguments	{}

Returns

Name	Type	Description
	results	RecordBatchReader

to_sql

to_sql(dialect=None, pretty=True, **kwargs)

Compile to a formatted SQL string.

Parameters

Name	Type	Description	Default
dialect	str | None	SQL dialect to use for compilation. Uses the dialect bound to self if not specified, or the default dialect if no dialect is bound.	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)
>>> expr.to_sql()

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:

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

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

See Also

Value.to_sql() Table.to_sql() ibis.to_sql() Value.compile() Table.compile()

to_torch

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

Execute an expression and return results as a dictionary of torch tensors.

Parameters

Name	Type	Description	Default
params	Mapping[ir.Scalar, Any] | None	Parameters to substitute into the expression.	None
limit	int | str | None	An integer to effect a specific row limit. A value of None means no limit.	None
kwargs	Any	Keyword arguments passed into the backend’s to_torch implementation.	{}

Returns

Name	Type	Description
	dict[str, torch.Tensor]	A dictionary of torch tensors, keyed by column name.

to_xlsx

to_xlsx(path, /, *, sheet='Sheet1', header=False, params=None, **kwargs)

Write a table to an Excel file.

Parameters

Name	Type	Description	Default
path	str | Path	Excel output path.	required
sheet	str	The name of the sheet to write to, eg ‘Sheet3’.	'Sheet1'
header	bool	Whether to include the column names as the first row.	False
params	Mapping[ir.Scalar, Any] | None	Additional Ibis expression parameters to pass to the backend’s write function.	None
kwargs	Any	Additional arguments passed to the backend’s write function.	{}

Notes

Requires DuckDB >= 1.2.0.

See Also

DuckDB’s excel extension docs for writing

Examples

>>> import os
>>> import ibis
>>> con = ibis.duckdb.connect()
>>> t = con.create_table(
...     "t",
...     ibis.memtable({"a": [1, 2, 3], "b": ["a", "b", "c"]}),
...     temp=True,
... )
>>> t.to_xlsx("/tmp/test.xlsx")
>>> os.path.exists("/tmp/test.xlsx")

True

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 ┃
┡━━━━━━━━━╇━━━━━━━━━┩
│ 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'

unbind

unbind()

Return an expression built on UnboundTable instead of backend-specific objects.

Examples

>>> import ibis
>>> import pandas as pd
>>> duckdb_con = ibis.duckdb.connect()
>>> polars_con = ibis.polars.connect()
>>> for backend in (duckdb_con, polars_con):
...     t = backend.create_table("t", pd.DataFrame({"a": [1, 2, 3]}))
>>> bound_table = duckdb_con.table("t")
>>> bound_table.get_backend().name

'duckdb'

>>> unbound_table = bound_table.unbind()
>>> polars_con.execute(unbound_table)

	a
0	1
1	2
2	3

visualize

visualize(
    format='svg',
    *,
    label_edges=False,
    verbose=False,
    node_attr=None,
    node_attr_getter=None,
    edge_attr=None,
    edge_attr_getter=None,
)

Visualize an expression as a GraphViz graph in the browser.

Parameters

Name	Type	Description	Default
format	str	Image output format. These are specified by the graphviz Python library.	'svg'
label_edges	bool	Show operation input names as edge labels	False
verbose	bool	Print the graphviz DOT code to stderr if True	False
node_attr	Mapping[str, str] | None	Mapping of (attribute, value) pairs set for all nodes. Options are specified by the graphviz Python library.	None
node_attr_getter	NodeAttributeGetter | None	Callback taking a node and returning a mapping of (attribute, value) pairs for that node. Options are specified by the graphviz Python library.	None
edge_attr	Mapping[str, str] | None	Mapping of (attribute, value) pairs set for all edges. Options are specified by the graphviz Python library.	None
edge_attr_getter	EdgeAttributeGetter | None	Callback taking two adjacent nodes and returning a mapping of (attribute, value) pairs for the edge between those nodes. Options are specified by the graphviz Python library.	None

Examples

Open the visualization of an expression in default browser:

>>> import ibis
>>> import ibis.expr.operations as ops
>>> left = ibis.table(dict(a="int64", b="string"), name="left")
>>> right = ibis.table(dict(b="string", c="int64", d="string"), name="right")
>>> expr = left.inner_join(right, "b").select(left.a, b=right.c, c=right.d)
>>> expr.visualize(
...     format="svg",
...     label_edges=True,
...     node_attr={"fontname": "Roboto Mono", "fontsize": "10"},
...     node_attr_getter=lambda node: isinstance(node, ops.Field) and {"shape": "oval"},
...     edge_attr={"fontsize": "8"},
...     edge_attr_getter=lambda u, v: isinstance(u, ops.Field) and {"color": "red"},
... )  # quartodoc: +SKIP

Raises

Name	Type	Description
	ImportError	If graphviz is not installed.

Column

Column(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 to treat this Column as a scalar.
as_table	Promote the expression to a Table.
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.
compile	Compile expr to a SQL string (for SQL backends) or a LazyFrame (for the polars backend).
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.
desc	Sort an expression in descending order.
equals	Return whether this expression is structurally equivalent to other.
execute	Execute an expression against its backend if one exists.
fill_null	Replace NULLs with the given value. Does NOT affect NaN and inf values.
fillna	DEPRECATED: use fill_null instead, which acts exactly the same.
first	Return the first value of a column.
get_backend	Get the current Ibis backend of the expression.
get_name	Return the name of this expression.
group_concat	Concatenate values using the indicated separator to produce a string.
hash	Compute an integer hash value.
identical_to	Like ==, but always returns non-NULL, even when comparing NULLs.
isin	Check whether this expression is in values.
isnull	Whether this expression is NULL. Does NOT detect NaN and inf values.
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.
name	Rename an expression to name.
notin	Check whether this expression is not in values.
notnull	Return whether this expression is not NULL.
nth	Return the nth value (0-indexed) over a window.
ntile	Return the integer number of a partitioning of the column values.
nullif	Set values to null if they equal the values null_if_expr.
nunique	Compute the number of distinct rows in an expression.
over	Construct a window expression.
percent_rank	Return the relative rank of the values in the column.
pipe	Compose f with self.
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.
substitute	Replace values given in values with replacement.
to_csv	Write the results of executing the given expression to a CSV file.
to_delta	Write the results of executing the given expression to a Delta Lake table.
to_json	Write the results of expr to a json file of [{column -> value}, …] objects.
to_list	Convert a column expression to a list.
to_pandas	Convert a table expression to a pandas DataFrame.
to_pandas_batches	Execute expression and return an iterator of pandas DataFrames.
to_parquet	Write the results of executing the given expression to a parquet file.
to_parquet_dir	Write the results of executing the given expression to a parquet file in a directory.
to_polars	Execute expression and return results as a polars dataframe.
to_pyarrow_batches	Execute expression and return a RecordBatchReader.
to_sql	Compile to a formatted SQL string.
to_torch	Execute an expression and return results as a dictionary of torch tensors.
to_xlsx	Write a table to an Excel file.
topk	Computes a Table of the top values by a metric (defaults to count).
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.
unbind	Return an expression built on UnboundTable instead of backend-specific objects.
value_counts	Compute a frequency table.
visualize	Visualize an expression as a GraphViz graph in the browser.

approx_median

approx_median(where=None)

Return an approximate of the median of self.

NoteThe result may or may not be exact

Whether the result is an approximation depends on the backend.

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

NoteThe result may or may not be exact

Whether the result is an approximation depends on the backend.

WarningDo 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 to treat this Column as a scalar.

Ibis cannot know until execution time whether a column expression contains only one value or many values.

This method is a way to explicitly tell ibis to trust you that this column expression will only contain one value at execution time. This allows you to use this column expression with other tables.

Note that execution will fail if the column DOES contain 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)

We know from domain knowledge that there is only one Gentoo penguin with body mass greater than 6200g, so heavy_gentoo is a table with exactly one row.

If we try to use t.island == heavy_gentoo.island directly in a filter, we will get an error because we are trying to compare columns from two tables to each other, which we don’t know know to align without a specific join:

>>> t.filter(t.island == heavy_gentoo.island)  

---------------------------------------------------------------------------
BinderException                           Traceback (most recent call last)
File /nix/store/1w0931lgp02pk2yqql58b10fnfmvcf9x-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/1w0931lgp02pk2yqql58b10fnfmvcf9x-ibis-3.12/lib/python3.12/site-packages/IPython/lib/pretty.py:420, in RepresentationPrinter.pretty(self, obj)
    409                         return meth(obj, self, cycle)
    410                 if (
    411                     cls is not object
    412                     # check if cls defines __repr__
   (...)    418                     and callable(_safe_getattr(cls, "__repr__", None))
    419                 ):
--> 420                     return _repr_pprint(obj, self, cycle)
    422     return _default_pprint(obj, self, cycle)
    423 finally:

File /nix/store/1w0931lgp02pk2yqql58b10fnfmvcf9x-ibis-3.12/lib/python3.12/site-packages/IPython/lib/pretty.py:795, in _repr_pprint(obj, p, cycle)
    793 """A pprint that just redirects to the normal repr function."""
    794 # Find newlines and replace them with p.break_()
--> 795 output = repr(obj)
    796 lines = output.splitlines()
    797 with p.group():

File ~/work/ibis/ibis/ibis/expr/types/core.py:54, in Expr.__repr__(self)
     52 def __repr__(self) -> str:
     53     if ibis.options.interactive:
---> 54         return capture_rich_renderable(self)
     55     else:
     56         return self._noninteractive_repr()

File ~/work/ibis/ibis/ibis/expr/types/rich.py:48, in capture_rich_renderable(renderable)
     46 console = Console(force_terminal=False)
     47 with _with_rich_display_disabled(), console.capture() as capture:
---> 48     console.print(renderable)
     49 return capture.get().rstrip()

File /nix/store/1w0931lgp02pk2yqql58b10fnfmvcf9x-ibis-3.12/lib/python3.12/site-packages/rich/console.py:1724, in Console.print(self, sep, end, style, justify, overflow, no_wrap, emoji, markup, highlight, width, height, crop, soft_wrap, new_line_start, *objects)
   1722 if style is None:
   1723     for renderable in renderables:
-> 1724         extend(render(renderable, render_options))
   1725 else:
   1726     render_style = self.get_style(style)

File /nix/store/1w0931lgp02pk2yqql58b10fnfmvcf9x-ibis-3.12/lib/python3.12/site-packages/rich/console.py:1325, in Console.render(self, renderable, options)
   1323 renderable = rich_cast(renderable)
   1324 if hasattr(renderable, "__rich_console__") and not isclass(renderable):
-> 1325     render_iterable = renderable.__rich_console__(self, _options)
   1326 elif isinstance(renderable, str):
   1327     text_renderable = self.render_str(
   1328         renderable, highlight=_options.highlight, markup=_options.markup
   1329     )

File ~/work/ibis/ibis/ibis/expr/types/core.py:75, in Expr.__rich_console__(self, console, options)
     73 try:
     74     if opts.interactive:
---> 75         rich_object = to_rich(self, console_width=console_width)
     76     else:
     77         rich_object = Text(self._noninteractive_repr())

File ~/work/ibis/ibis/ibis/expr/types/rich.py:70, in to_rich(expr, max_rows, max_columns, max_length, max_string, max_depth, console_width)
     66     return to_rich_scalar(
     67         expr, max_length=max_length, max_string=max_string, max_depth=max_depth
     68     )
     69 else:
---> 70     return to_rich_table(
     71         expr,
     72         max_rows=max_rows,
     73         max_columns=max_columns,
     74         max_length=max_length,
     75         max_string=max_string,
     76         max_depth=max_depth,
     77         console_width=console_width,
     78     )

File ~/work/ibis/ibis/ibis/expr/types/_rich.py:331, in to_rich_table(tablish, max_rows, max_columns, max_length, max_string, max_depth, console_width)
    328     if orig_ncols > len(computed_cols):
    329         table = table.select(*computed_cols)
--> 331 result = table.limit(max_rows + 1).to_pyarrow()
    332 # Now format the columns in order, stopping if the console width would
    333 # be exceeded.
    334 col_info = []

File ~/work/ibis/ibis/ibis/expr/types/relations.py:628, in Table.to_pyarrow(self, params, limit, **kwargs)
    620 @experimental
    621 def to_pyarrow(
    622     self,
   (...)    626     **kwargs: Any,
    627 ) -> pa.Table:
--> 628     return super().to_pyarrow(params=params, limit=limit, **kwargs)

File ~/work/ibis/ibis/ibis/expr/types/core.py:604, in Expr.to_pyarrow(self, params, limit, **kwargs)
    574 @experimental
    575 def to_pyarrow(
    576     self,
   (...)    580     **kwargs: Any,
    581 ) -> pa.Table | pa.Array | pa.Scalar:
    582     """Execute expression to a pyarrow object.
    583 
    584     This method is eager and will execute the associated expression
   (...)    602         If the passed expression is a Scalar, a pyarrow scalar is returned.
    603     """
--> 604     return self._find_backend(use_default=True).to_pyarrow(
    605         self, params=params, limit=limit, **kwargs
    606     )

File ~/work/ibis/ibis/ibis/backends/duckdb/__init__.py:1398, in Backend.to_pyarrow(self, expr, params, limit, **kwargs)
   1387 def to_pyarrow(
   1388     self,
   1389     expr: ir.Expr,
   (...)   1394     **kwargs: Any,
   1395 ) -> pa.Table:
   1396     from ibis.backends.duckdb.converter import DuckDBPyArrowData
-> 1398     table = self._to_duckdb_relation(
   1399         expr, params=params, limit=limit, **kwargs
   1400     ).to_arrow_table()
   1401     return expr.__pyarrow_result__(table, data_mapper=DuckDBPyArrowData)

File ~/work/ibis/ibis/ibis/backends/duckdb/__init__.py:1342, in Backend._to_duckdb_relation(self, expr, params, limit, **kwargs)
   1340 if table_expr.schema().geospatial:
   1341     self._load_extensions(["spatial"])
-> 1342 return self.con.sql(sql)

BinderException: Binder Error: Referenced table "t1" not found!
Candidate tables: "t0"

Instead, we should use as_scalar() to tell ibis that we know heavy_gentoo.island contains exactly one value:

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

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

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  ┃
┡━━━━━━━╇━━━━━━━━╇━━━━━━━╇━━━━━━━━━┩
│ 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

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/1w0931lgp02pk2yqql58b10fnfmvcf9x-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/1w0931lgp02pk2yqql58b10fnfmvcf9x-ibis-3.12/lib/python3.12/site-packages/IPython/lib/pretty.py:420, in RepresentationPrinter.pretty(self, obj)
    409                         return meth(obj, self, cycle)
    410                 if (
    411                     cls is not object
    412                     # check if cls defines __repr__
   (...)    418                     and callable(_safe_getattr(cls, "__repr__", None))
    419                 ):
--> 420                     return _repr_pprint(obj, self, cycle)
    422     return _default_pprint(obj, self, cycle)
    423 finally:

File /nix/store/1w0931lgp02pk2yqql58b10fnfmvcf9x-ibis-3.12/lib/python3.12/site-packages/IPython/lib/pretty.py:795, in _repr_pprint(obj, p, cycle)
    793 """A pprint that just redirects to the normal repr function."""
    794 # Find newlines and replace them with p.break_()
--> 795 output = repr(obj)
    796 lines = output.splitlines()
    797 with p.group():

File ~/work/ibis/ibis/ibis/expr/types/core.py:54, in Expr.__repr__(self)
     52 def __repr__(self) -> str:
     53     if ibis.options.interactive:
---> 54         return capture_rich_renderable(self)
     55     else:
     56         return self._noninteractive_repr()

File ~/work/ibis/ibis/ibis/expr/types/rich.py:48, in capture_rich_renderable(renderable)
     46 console = Console(force_terminal=False)
     47 with _with_rich_display_disabled(), console.capture() as capture:
---> 48     console.print(renderable)
     49 return capture.get().rstrip()

File /nix/store/1w0931lgp02pk2yqql58b10fnfmvcf9x-ibis-3.12/lib/python3.12/site-packages/rich/console.py:1724, in Console.print(self, sep, end, style, justify, overflow, no_wrap, emoji, markup, highlight, width, height, crop, soft_wrap, new_line_start, *objects)
   1722 if style is None:
   1723     for renderable in renderables:
-> 1724         extend(render(renderable, render_options))
   1725 else:
   1726     render_style = self.get_style(style)

File /nix/store/1w0931lgp02pk2yqql58b10fnfmvcf9x-ibis-3.12/lib/python3.12/site-packages/rich/console.py:1325, in Console.render(self, renderable, options)
   1323 renderable = rich_cast(renderable)
   1324 if hasattr(renderable, "__rich_console__") and not isclass(renderable):
-> 1325     render_iterable = renderable.__rich_console__(self, _options)
   1326 elif isinstance(renderable, str):
   1327     text_renderable = self.render_str(
   1328         renderable, highlight=_options.highlight, markup=_options.markup
   1329     )

File ~/work/ibis/ibis/ibis/expr/types/core.py:75, in Expr.__rich_console__(self, console, options)
     73 try:
     74     if opts.interactive:
---> 75         rich_object = to_rich(self, console_width=console_width)
     76     else:
     77         rich_object = Text(self._noninteractive_repr())

File ~/work/ibis/ibis/ibis/expr/types/rich.py:66, in to_rich(expr, max_rows, max_columns, max_length, max_string, max_depth, console_width)
     63 from ibis.expr.types._rich import to_rich_scalar, to_rich_table
     65 if isinstance(expr, ir.Scalar):
---> 66     return to_rich_scalar(
     67         expr, max_length=max_length, max_string=max_string, max_depth=max_depth
     68     )
     69 else:
     70     return to_rich_table(
     71         expr,
     72         max_rows=max_rows,
   (...)     77         console_width=console_width,
     78     )

File ~/work/ibis/ibis/ibis/expr/types/_rich.py:270, in to_rich_scalar(expr, max_length, max_string, max_depth)
    263 def to_rich_scalar(
    264     expr: Scalar,
    265     *,
   (...)    268     max_depth: int | None = None,
    269 ) -> Panel:
--> 270     value = expr.to_pyarrow().as_py()
    272     if value is None:
    273         formatted_value = Text.styled("NULL", style="dim")

File ~/work/ibis/ibis/ibis/expr/types/generic.py:1452, in Scalar.to_pyarrow(self, params, limit, **kwargs)
   1444 @experimental
   1445 def to_pyarrow(
   1446     self,
   (...)   1450     **kwargs: Any,
   1451 ) -> pa.Scalar:
-> 1452     return super().to_pyarrow(params=params, limit=limit, **kwargs)

File ~/work/ibis/ibis/ibis/expr/types/core.py:604, in Expr.to_pyarrow(self, params, limit, **kwargs)
    574 @experimental
    575 def to_pyarrow(
    576     self,
   (...)    580     **kwargs: Any,
    581 ) -> pa.Table | pa.Array | pa.Scalar:
    582     """Execute expression to a pyarrow object.
    583 
    584     This method is eager and will execute the associated expression
   (...)    602         If the passed expression is a Scalar, a pyarrow scalar is returned.
    603     """
--> 604     return self._find_backend(use_default=True).to_pyarrow(
    605         self, params=params, limit=limit, **kwargs
    606     )

File ~/work/ibis/ibis/ibis/backends/duckdb/__init__.py:1400, in Backend.to_pyarrow(self, expr, params, limit, **kwargs)
   1387 def to_pyarrow(
   1388     self,
   1389     expr: ir.Expr,
   (...)   1394     **kwargs: Any,
   1395 ) -> pa.Table:
   1396     from ibis.backends.duckdb.converter import DuckDBPyArrowData
   1398     table = self._to_duckdb_relation(
   1399         expr, params=params, limit=limit, **kwargs
-> 1400     ).to_arrow_table()
   1401     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

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 ┃
┡━━━━━━━━╇━━━━━━━┩
│ 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, 5, 3, 2]

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]       │
└────────┴──────────────┘

compile

compile(limit=None, params=None, pretty=False)

Compile expr to a SQL string (for SQL backends) or a LazyFrame (for the polars backend).

Parameters

Name	Type	Description	Default
limit	int | None	An integer to effect a specific row limit. A value of None means “no limit”. The default is in ibis/config.py.	None
params	Mapping[ir.Value, Any] | None	Mapping of scalar parameter expressions to value	None
pretty	bool	In case of SQL backends, return a pretty formatted SQL query.	False

Returns

Name	Type	Description
	str | pl.LazyFrame	A SQL string or a LazyFrame object, depending on the backend of self.

Examples

>>> import ibis
>>> d = {"a": [1, 2, 3], "b": [4, 5, 6]}
>>> con = ibis.duckdb.connect()
>>> t = con.create_table("t", d)
>>> expr = t.mutate(c=t.a + t.b)
>>> expr.compile()

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

If you want to see the pretty formatted SQL query, set pretty to True.

>>> expr.compile(pretty=True)

'SELECT\n  "t0"."a",\n  "t0"."b",\n  "t0"."a" + "t0"."b" AS "c"\nFROM "memory"."main"."t" AS "t0"'

If the expression does not have a backend, an error will be raised.

>>> t = ibis.memtable(d)
>>> expr = t.mutate(c=t.a + t.b)
>>> expr.compile()  # quartodoc: +EXPECTED_FAILURE

---------------------------------------------------------------------------
IbisError                                 Traceback (most recent call last)
Cell In[308], line 3
      1 t = ibis.memtable(d)
      2 expr = t.mutate(c=t.a + t.b)
----> 3 expr.compile()  # quartodoc: +EXPECTED_FAILURE

File ~/work/ibis/ibis/ibis/expr/types/core.py:531, in Expr.compile(self, limit, params, pretty)
    478 def compile(
    479     self,
    480     *,
   (...)    483     pretty: bool = False,
    484 ) -> str | pl.LazyFrame:
    485     r"""Compile `expr` to a SQL string (for SQL backends) or a LazyFrame (for the polars backend).
    486 
    487     Parameters
   (...)    529     [`Table.to_sql()`](./expression-tables.qmd#ibis.expr.types.relations.Table.to_sql)
    530     """
--> 531     return self._find_backend().compile(
    532         self, limit=limit, params=params, pretty=pretty
    533     )

File ~/work/ibis/ibis/ibis/expr/types/core.py:337, in Expr._find_backend(self, use_default)
    335     default = _default_backend() if use_default else None
    336     if default is None:
--> 337         raise IbisError(
    338             "Expression depends on no backends, and found no default"
    339         )
    340     return default
    342 if len(backends) > 1:

IbisError: Expression depends on no backends, and found no default

See Also

Value.compile() Table.compile() Value.to_sql() Table.to_sql()

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 │
└────────┴───────┘

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

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 │
└─────────┘

equals

equals(other, /)

Return whether this expression is structurally equivalent to other.

If you want to produce an equality expression, use == syntax.

Parameters

Name	Type	Description	Default
other		Another expression	required

Examples

>>> import ibis
>>> t1 = ibis.table(dict(a="int"), name="t")
>>> t2 = ibis.table(dict(a="int"), name="t")
>>> t1.equals(t2)

True

>>> v = ibis.table(dict(a="string"), name="v")
>>> t1.equals(v)

False

execute

execute(limit='default', params=None, **kwargs)

Execute an expression against its backend if one exists.

Parameters

Name	Type	Description	Default
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.	'default'
params	Mapping[ir.Value, Any] | None	Mapping of scalar parameter expressions to value	None
kwargs	Any	Keyword arguments	{}

Examples

>>> import ibis
>>> t = ibis.examples.penguins.fetch()
>>> t.execute()

	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
...	...	...	...	...	...	...	...	...
339	Chinstrap	Dream	55.8	19.8	207.0	4000.0	male	2009
340	Chinstrap	Dream	43.5	18.1	202.0	3400.0	female	2009
341	Chinstrap	Dream	49.6	18.2	193.0	3775.0	male	2009
342	Chinstrap	Dream	50.8	19.0	210.0	4100.0	male	2009
343	Chinstrap	Dream	50.2	18.7	198.0	3775.0	female	2009

344 rows × 8 columns

Scalar parameters can be supplied dynamically during execution.

>>> species = ibis.param("string")
>>> expr = t.filter(t.species == species).order_by(t.bill_length_mm)
>>> expr.execute(limit=3, params={species: "Gentoo"})

	species	island	bill_length_mm	bill_depth_mm	flipper_length_mm	body_mass_g	sex	year
0	Gentoo	Biscoe	40.9	13.7	214	4650	female	2007
1	Gentoo	Biscoe	41.7	14.7	210	4700	female	2009
2	Gentoo	Biscoe	42.0	13.5	210	4150	female	2007

See Also

Table.to_pandas() Value.to_pandas()

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

See Also

Value.coalesce() ibis.coalesce() Value.isnull() FloatingValue.isnan() FloatingValue.isinf()

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.

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 │
└───┘

get_backend

get_backend()

Get the current Ibis backend of the expression.

Returns

Name	Type	Description
	BaseBackend	The Ibis backend.

Examples

>>> import ibis
>>> con = ibis.duckdb.connect()
>>> t = con.create_table("t", {"id": [1, 2, 3]})
>>> t.get_backend()

<ibis.backends.duckdb.Backend at 0x7fff107cf200>

See Also

ibis.get_backend()

get_name

get_name()

Return the name of this expression.

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.

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

┌─────────────────────┐
│ 8628213525773890682 │
└─────────────────────┘

identical_to

identical_to(other, /)

Like ==, but always returns non-NULL, even when comparing NULLs.

With regular equality, NULL == <anything> is always NULL, even for NULL == NULL.

In contrast, identical_to treats NULL as a singleton value, so that

• NULL.identical_to(NULL) is True.
• NULL.identical_to(<non-null>) is False.

This 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
>>> t = ibis.memtable(
...     [
...         (1, 1),
...         (1, 2),
...         (1, None),
...         (None, None),
...     ],
...     schema={"a": "int64", "b": "int64"},
... )
>>> t.mutate(
...     eq=_.a == _.b,
...     neq=_.a != _.b,
...     identical=_.a.identical_to(_.b),
...     not_identical=~_.a.identical_to(_.b),
... )

┏━━━━━━━┳━━━━━━━┳━━━━━━━━━┳━━━━━━━━━┳━━━━━━━━━━━┳━━━━━━━━━━━━━━━┓
┃ a     ┃ b     ┃ eq      ┃ neq     ┃ identical ┃ not_identical ┃
┡━━━━━━━╇━━━━━━━╇━━━━━━━━━╇━━━━━━━━━╇━━━━━━━━━━━╇━━━━━━━━━━━━━━━┩
│ int64 │ int64 │ boolean │ boolean │ boolean   │ boolean       │
├───────┼───────┼─────────┼─────────┼───────────┼───────────────┤
│     1 │     1 │ True    │ False   │ True      │ False         │
│     1 │     2 │ False   │ True    │ False     │ True          │
│     1 │  NULL │ NULL    │ NULL    │ False     │ True          │
│  NULL │  NULL │ NULL    │ NULL    │ True      │ False         │
└───────┴───────┴─────────┴─────────┴───────────┴───────────────┘

isin

isin(values, /)

Check whether this expression is in values.

NULL values in the input are propagated in the output. If the values argument contains any NULL values, then ibis follows the SQL behavior of returning NULL (not False) when self is not present. See examples below 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	True if self is contained in values, False otherwise.

See Also

Value.notin()

Examples

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

Checking for values in literals:

>>> t.mutate(
...     a_in_12=t.a.isin([1, 2]),
...     a_in_12None=t.a.isin([1, 2, None]),
... )

┏━━━━━━━┳━━━━━━━┳━━━━━━━━━┳━━━━━━━━━━━━━┓
┃ a     ┃ b     ┃ a_in_12 ┃ a_in_12None ┃
┡━━━━━━━╇━━━━━━━╇━━━━━━━━━╇━━━━━━━━━━━━━┩
│ int64 │ int64 │ boolean │ boolean     │
├───────┼───────┼─────────┼─────────────┤
│     1 │     1 │ True    │ True        │
│     2 │     2 │ True    │ True        │
│     3 │     9 │ False   │ NULL        │
│  NULL │  NULL │ NULL    │ NULL        │
└───────┴───────┴─────────┴─────────────┘

Checking for values in columns of the same table:

>>> t.mutate(
...     a_in_b=t.a.isin(t.b),
...     a_in_b_no_null=t.a.isin(t.b.fill_null(0)),
...     a_in_b_plus_1=t.a.isin(t.b + 1),
... )

┏━━━━━━━┳━━━━━━━┳━━━━━━━━━┳━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━┓
┃ a     ┃ b     ┃ a_in_b  ┃ a_in_b_no_null ┃ a_in_b_plus_1 ┃
┡━━━━━━━╇━━━━━━━╇━━━━━━━━━╇━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━┩
│ int64 │ int64 │ boolean │ boolean        │ boolean       │
├───────┼───────┼─────────┼────────────────┼───────────────┤
│     1 │     1 │ True    │ True           │ NULL          │
│     2 │     2 │ True    │ True           │ True          │
│     3 │     9 │ NULL    │ False          │ True          │
│  NULL │  NULL │ NULL    │ NULL           │ NULL          │
└───────┴───────┴─────────┴────────────────┴───────────────┘

Checking for values in a column from a different table:

>>> t2 = ibis.memtable({"x": [1, 2, 99], "y": [1, 2, None]})
>>> t.mutate(
...     a_in_x=t.a.isin(t2.x),
...     a_in_y=t.a.isin(t2.y),
...     a_in_y_plus_1=t.a.isin(t2.y + 1),
... )

┏━━━━━━━┳━━━━━━━┳━━━━━━━━━┳━━━━━━━━━┳━━━━━━━━━━━━━━━┓
┃ a     ┃ b     ┃ a_in_x  ┃ a_in_y  ┃ a_in_y_plus_1 ┃
┡━━━━━━━╇━━━━━━━╇━━━━━━━━━╇━━━━━━━━━╇━━━━━━━━━━━━━━━┩
│ int64 │ int64 │ boolean │ boolean │ boolean       │
├───────┼───────┼─────────┼─────────┼───────────────┤
│     1 │     1 │ True    │ True    │ NULL          │
│     2 │     2 │ True    │ True    │ True          │
│     3 │     9 │ False   │ NULL    │ True          │
│  NULL │  NULL │ NULL    │ NULL    │ NULL          │
└───────┴───────┴─────────┴─────────┴───────────────┘

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.

See Also

Value.fill_null() FloatingValue.isnan() FloatingValue.isinf()

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   │
└─────────┴─────────┴─────────┴─────────┘

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 │
└──────┘

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]})
>>> t.a

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

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

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

notin

notin(values, /)

Check whether this expression is not in values.

Opposite of Value.isin().

NULL values in the input are propagated in the output. If the values argument contains any NULL values, then ibis follows the SQL behavior of returning NULL (not False) when self is present. See examples below for details.

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	True if self is not in values, False otherwise.

Examples

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

Checking for values in literals:

>>> t.mutate(
...     a_notin_12=t.a.notin([1, 2]),
...     a_notin_12None=t.a.notin([1, 2, None]),
... )

┏━━━━━━━┳━━━━━━━┳━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━┓
┃ a     ┃ b     ┃ a_notin_12 ┃ a_notin_12None ┃
┡━━━━━━━╇━━━━━━━╇━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━┩
│ int64 │ int64 │ boolean    │ boolean        │
├───────┼───────┼────────────┼────────────────┤
│     1 │     1 │ False      │ False          │
│     2 │     2 │ False      │ False          │
│     3 │     9 │ True       │ NULL           │
│  NULL │  NULL │ NULL       │ NULL           │
└───────┴───────┴────────────┴────────────────┘

Checking for values in columns of the same table:

>>> t.mutate(
...     a_notin_b=t.a.notin(t.b),
...     a_notin_b_no_null=t.a.notin(t.b.fill_null(0)),
...     a_notin_b_plus_1=t.a.notin(t.b + 1),
... )

┏━━━━━━━┳━━━━━━━┳━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━┓
┃ a     ┃ b     ┃ a_notin_b ┃ a_notin_b_no_null ┃ a_notin_b_plus_1 ┃
┡━━━━━━━╇━━━━━━━╇━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━┩
│ int64 │ int64 │ boolean   │ boolean           │ boolean          │
├───────┼───────┼───────────┼───────────────────┼──────────────────┤
│     1 │     1 │ False     │ False             │ NULL             │
│     2 │     2 │ False     │ False             │ False            │
│     3 │     9 │ NULL      │ True              │ False            │
│  NULL │  NULL │ NULL      │ NULL              │ NULL             │
└───────┴───────┴───────────┴───────────────────┴──────────────────┘

Checking for values in a column from a different table:

>>> t2 = ibis.memtable({"x": [1, 2, 99], "y": [1, 2, None]})
>>> t.mutate(
...     a_notin_x=t.a.notin(t2.x),
...     a_notin_y=t.a.notin(t2.y),
...     a_notin_y_plus_1=t.a.notin(t2.y + 1),
... )

┏━━━━━━━┳━━━━━━━┳━━━━━━━━━━━┳━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━┓
┃ a     ┃ b     ┃ a_notin_x ┃ a_notin_y ┃ a_notin_y_plus_1 ┃
┡━━━━━━━╇━━━━━━━╇━━━━━━━━━━━╇━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━┩
│ int64 │ int64 │ boolean   │ boolean   │ boolean          │
├───────┼───────┼───────────┼───────────┼──────────────────┤
│     1 │     1 │ False     │ False     │ NULL             │
│     2 │     2 │ False     │ False     │ False            │
│     3 │     9 │ True      │ NULL      │ False            │
│  NULL │  NULL │ NULL      │ NULL      │ NULL             │
└───────┴───────┴───────────┴───────────┴──────────────────┘

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                   │
└────────────────────────┘

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 │
└────────┴───────┘

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              │
└─────────────────────┘

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 │
└────┘

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

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 │
└────────┴──────────────┘

pipe

pipe(f, /, *args, **kwargs)

Compose f with self.

Parameters

Name	Type	Description	Default
f		If the expression needs to be passed as anything other than the first argument to the function, pass a tuple with the argument name. For example, (f, ‘data’) if the function f expects a ‘data’ keyword	required
args	Any	Positional arguments to f	()
kwargs	Any	Keyword arguments to f	{}

Examples

>>> import ibis
>>> t = ibis.memtable(
...     {
...         "a": [5, 10, 15],
...         "b": ["a", "b", "c"],
...     }
... )
>>> f = lambda a: (a + 1).name("a")
>>> g = lambda a: (a * 2).name("a")
>>> result1 = t.a.pipe(f).pipe(g)
>>> result1

┏━━━━━━━┓
┃ a     ┃
┡━━━━━━━┩
│ int64 │
├───────┤
│    12 │
│    22 │
│    32 │
└───────┘

>>> result2 = g(f(t.a))  # equivalent to the above
>>> result1.equals(result2)

True

Returns

Name	Type	Description
	Expr	Result type of passed function

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 │
└────────┴───────┘

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

See Also

Value.cases() 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_csv

to_csv(path, /, *, params=None, **kwargs)

Write the results of executing the given expression to a CSV file.

This method is eager and will execute the associated expression immediately.

See https://arrow.apache.org/docs/python/generated/pyarrow.csv.CSVWriter.html for details.

Parameters

Name	Type	Description	Default
path	str | Path	The data target. A string or Path where the CSV file will be written.	required
params	Mapping[ir.Scalar, Any] | None	Mapping of scalar parameter expressions to value.	None
**kwargs	Any	Additional keyword arguments passed to pyarrow.csv.CSVWriter	{}

to_delta

to_delta(path, /, *, params=None, **kwargs)

Write the results of executing the given expression to a Delta Lake table.

This method is eager and will execute the associated expression immediately.

Parameters

Name	Type	Description	Default
path	str | Path	The data target. A string or Path to the Delta Lake table directory.	required
params	Mapping[ir.Scalar, Any] | None	Mapping of scalar parameter expressions to value.	None
**kwargs	Any	Additional keyword arguments passed to deltalake.writer.write_deltalake method	{}

to_json

to_json(path, /, **kwargs)

Write the results of expr to a json file of [{column -> value}, …] objects.

This method is eager and will execute the associated expression immediately.

Parameters

Name	Type	Description	Default
path	str | Path	The data target. A string or Path where the JSON file will be written.	required
kwargs	Any	Additional, backend-specific keyword arguments.	{}

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]

NoteAdded in version 10.0.0

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

to_pandas_batches

to_pandas_batches(limit=None, params=None, chunk_size=1000000, **kwargs)

Execute expression and return an iterator of pandas DataFrames.

This method is eager and will execute the associated expression immediately.

Parameters

Name	Type	Description	Default
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
params	Mapping[ir.Value, Any] | None	Mapping of scalar parameter expressions to value.	None
chunk_size	int	Maximum number of rows in each returned DataFrame.	1000000
kwargs	Any	Keyword arguments	{}

Returns

Name	Type	Description
	Iterator[pd.DataFrame]

to_parquet

to_parquet(path, /, *, params=None, **kwargs)

Write the results of executing the given expression to a parquet file.

This method is eager and will execute the associated expression immediately.

See https://arrow.apache.org/docs/python/generated/pyarrow.parquet.ParquetWriter.html for details.

Parameters

Name	Type	Description	Default
path	str | Path	A string or Path where the Parquet file will be written.	required
params	Mapping[ir.Scalar, Any] | None	Mapping of scalar parameter expressions to value.	None
**kwargs	Any	Additional keyword arguments passed to pyarrow.parquet.ParquetWriter	{}

Examples

Write out an expression to a single parquet file.

>>> import ibis
>>> import tempfile
>>> penguins = ibis.examples.penguins.fetch()
>>> penguins.to_parquet(tempfile.mktemp())

Partition on a single column.

>>> penguins.to_parquet(tempfile.mkdtemp(), partition_by="year")

Partition on multiple columns.

>>> penguins.to_parquet(tempfile.mkdtemp(), partition_by=("year", "island"))

NoteHive-partitioned output is currently only supported when using DuckDB

to_parquet_dir

to_parquet_dir(directory, /, *, params=None, **kwargs)

Write the results of executing the given expression to a parquet file in a directory.

This method is eager and will execute the associated expression immediately.

See https://arrow.apache.org/docs/python/generated/pyarrow.dataset.write_dataset.html for details.

Parameters

Name	Type	Description	Default
directory	str | Path	The data target. A string or Path to the directory where the parquet file will be written.	required
params	Mapping[ir.Scalar, Any] | None	Mapping of scalar parameter expressions to value.	None
**kwargs	Any	Additional keyword arguments passed to pyarrow.dataset.write_dataset	{}

to_polars

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

Execute expression and return results as a polars dataframe.

This method is eager and will execute the associated expression immediately.

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	A polars dataframe holding the results of the executed expression.

to_pyarrow_batches

to_pyarrow_batches(limit=None, params=None, chunk_size=1000000, **kwargs)

Execute expression and return a RecordBatchReader.

This method is eager and will execute the associated expression immediately.

Parameters

Name	Type	Description	Default
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
params	Mapping[ir.Value, Any] | None	Mapping of scalar parameter expressions to value.	None
chunk_size	int	Maximum number of rows in each returned record batch.	1000000
kwargs	Any	Keyword arguments	{}

Returns

Name	Type	Description
	results	RecordBatchReader

to_sql

to_sql(dialect=None, pretty=True, **kwargs)

Compile to a formatted SQL string.

Parameters

Name	Type	Description	Default
dialect	str | None	SQL dialect to use for compilation. Uses the dialect bound to self if not specified, or the default dialect if no dialect is bound.	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)
>>> expr.to_sql()

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:

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

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

See Also

Value.to_sql() Table.to_sql() ibis.to_sql() Value.compile() Table.compile()

to_torch

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

Execute an expression and return results as a dictionary of torch tensors.

Parameters

Name	Type	Description	Default
params	Mapping[ir.Scalar, Any] | None	Parameters to substitute into the expression.	None
limit	int | str | None	An integer to effect a specific row limit. A value of None means no limit.	None
kwargs	Any	Keyword arguments passed into the backend’s to_torch implementation.	{}

Returns

Name	Type	Description
	dict[str, torch.Tensor]	A dictionary of torch tensors, keyed by column name.

to_xlsx

to_xlsx(path, /, *, sheet='Sheet1', header=False, params=None, **kwargs)

Write a table to an Excel file.

Parameters

Name	Type	Description	Default
path	str | Path	Excel output path.	required
sheet	str	The name of the sheet to write to, eg ‘Sheet3’.	'Sheet1'
header	bool	Whether to include the column names as the first row.	False
params	Mapping[ir.Scalar, Any] | None	Additional Ibis expression parameters to pass to the backend’s write function.	None
kwargs	Any	Additional arguments passed to the backend’s write function.	{}

Notes

Requires DuckDB >= 1.2.0.

See Also

DuckDB’s excel extension docs for writing

Examples

>>> import os
>>> import ibis
>>> con = ibis.duckdb.connect()
>>> t = con.create_table(
...     "t",
...     ibis.memtable({"a": [1, 2, 3], "b": ["a", "b", "c"]}),
...     temp=True,
... )
>>> t.to_xlsx("/tmp/test.xlsx")
>>> os.path.exists("/tmp/test.xlsx")

True

topk

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

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

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

See Also

Column.value_counts Table.topk Table.value_counts

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 │
└────────┴───────────┘

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 ┃
┡━━━━━━━━━╇━━━━━━━━━┩
│ 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'

unbind

unbind()

Return an expression built on UnboundTable instead of backend-specific objects.

Examples

>>> import ibis
>>> import pandas as pd
>>> duckdb_con = ibis.duckdb.connect()
>>> polars_con = ibis.polars.connect()
>>> for backend in (duckdb_con, polars_con):
...     t = backend.create_table("t", pd.DataFrame({"a": [1, 2, 3]}))
>>> bound_table = duckdb_con.table("t")
>>> bound_table.get_backend().name

'duckdb'

>>> unbound_table = bound_table.unbind()
>>> polars_con.execute(unbound_table)

	a
0	1
1	2
2	3

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.

See Also

Column.topk Table.value_counts Table.topk

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 │
└────────┴───────┘

NoteAdded in version 9.5.0

visualize

visualize(
    format='svg',
    *,
    label_edges=False,
    verbose=False,
    node_attr=None,
    node_attr_getter=None,
    edge_attr=None,
    edge_attr_getter=None,
)

Visualize an expression as a GraphViz graph in the browser.

Parameters

Name	Type	Description	Default
format	str	Image output format. These are specified by the graphviz Python library.	'svg'
label_edges	bool	Show operation input names as edge labels	False
verbose	bool	Print the graphviz DOT code to stderr if True	False
node_attr	Mapping[str, str] | None	Mapping of (attribute, value) pairs set for all nodes. Options are specified by the graphviz Python library.	None
node_attr_getter	NodeAttributeGetter | None	Callback taking a node and returning a mapping of (attribute, value) pairs for that node. Options are specified by the graphviz Python library.	None
edge_attr	Mapping[str, str] | None	Mapping of (attribute, value) pairs set for all edges. Options are specified by the graphviz Python library.	None
edge_attr_getter	EdgeAttributeGetter | None	Callback taking two adjacent nodes and returning a mapping of (attribute, value) pairs for the edge between those nodes. Options are specified by the graphviz Python library.	None

Examples

Open the visualization of an expression in default browser:

>>> import ibis
>>> import ibis.expr.operations as ops
>>> left = ibis.table(dict(a="int64", b="string"), name="left")
>>> right = ibis.table(dict(b="string", c="int64", d="string"), name="right")
>>> expr = left.inner_join(right, "b").select(left.a, b=right.c, c=right.d)
>>> expr.visualize(
...     format="svg",
...     label_edges=True,
...     node_attr={"fontname": "Roboto Mono", "fontsize": "10"},
...     node_attr_getter=lambda node: isinstance(node, ops.Field) and {"shape": "oval"},
...     edge_attr={"fontsize": "8"},
...     edge_attr_getter=lambda u, v: isinstance(u, ops.Field) and {"color": "red"},
... )  # quartodoc: +SKIP

Raises

Name	Type	Description
	ImportError	If graphviz is not installed.

Deferred

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

Methods

Name	Description
as_scalar	Tell ibis to treat this expression as a single scalar value.
as_table	Promote the expression to a Table.

as_scalar

as_scalar()

Tell ibis to treat this expression as a single scalar value.

If the expression is a literal, it will be returned as is. If it depends on a table, eg an aggregation of a column, 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 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.

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

TipUse 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:273, in normalize(typ, value)
    272 try:
--> 273     value = int(value)
    274 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[629], line 1
----> 1 ibis.literal("foobar", type="int64")  

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

File ~/work/ibis/ibis/ibis/expr/types/generic.py:3131, in literal(value, type)
   3126         raise TypeError(
   3127             f"Ibis literal {value!r} cannot be safely coerced to datatype {type}"
   3128         )
   3130 dtype = dt.infer(value) if type is None else dt.dtype(type)
-> 3131 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:275, in normalize(typ, value)
    273     value = int(value)
    274 except ValueError:
--> 275     raise TypeError(f"Unable to normalize {value!r} to {dtype!r}")
    276 if value not in dtype.bounds:
    277     raise TypeError(
    278         f"Value {value} is out of bounds for type {dtype!r} "
    279         f"(bounds: {dtype.bounds})"
    280     )

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

See Also

Value.coalesce() Value.fill_null()

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

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.

NoteAdded 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

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	V | str | Deferred	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  ┃
┡━━━━━━━━━╇━━━━━━━┩
│ 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	V | str | Deferred	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  ┃
┡━━━━━━━━━╇━━━━━━━┩
│ string  │ int64 │
├─────────┼───────┤
│ Adelie  │  2009 │
│ Adelie  │  2009 │
│ Adelie  │  2009 │
│ Adelie  │  2009 │
│ Adelie  │  2009 │
└─────────┴───────┘

Returns

Name	Type	Description
	ir.ValueExpr	An expression

to_sql

ibis.expr.sql.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()