Value

Value(arg)

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

Methods

Name	Description
asc	Sort an expression ascending.
between	Check if this expression is between `lower` and `upper`, inclusive.
case	Create a SimpleCaseBuilder to chain multiple if-else statements.
cases	Create a multi-branch if-else expression.
cast	Cast expression to indicated data type.
coalesce	Return the first non-null value from `args`.
collect	Aggregate this expression’s elements into an array.
desc	Sort an expression descending.
fill_null	Replace any null values with the indicated fill value.
fillna	Deprecated - use `fill_null` instead.
group_concat	Concatenate values using the indicated separator to produce a string.
hash	Compute an integer hash value.
identical_to	Return whether this expression is identical to other.
isin	Check whether this expression’s values are in `values`.
isnull	Return whether this expression is NULL.
name	Rename an expression to `name`.
notin	Check whether this expression’s values are not in `values`.
notnull	Return whether this expression is not NULL.
nullif	Set values to null if they equal the values `null_if_expr`.
over	Construct a window expression.
substitute	Replace values given in `values` with `replacement`.
to_pandas	Convert a column expression to a pandas Series or scalar object.
try_cast	Try cast expression to indicated data type.
type	Return the DataType of `self`.
typeof	Return the string name of the datatype of self.

asc

asc(nulls_first=False)

Sort an expression ascending.

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)

case

case()

Create a SimpleCaseBuilder to chain multiple if-else statements.

Add new search expressions with the .when() method. These must be comparable with this column expression. Conclude by calling .end().

Returns

Name	Type	Description
	SimpleCaseBuilder	A case builder

Examples

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

>>> x.case().when("male", "M").when("female", "F").else_("U").end()

Cases not given result in the ELSE case

>>> x.case().when("male", "M").else_("OTHER").end()

If you don’t supply an ELSE, then NULL is used

>>> x.case().when("male", "M").end()

cases

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

Create a multi-branch if-else expression.

Equivalent to a SQL CASE statement.

Parameters

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

Returns

Name	Type	Description
	Value	A value expression

Examples

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

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

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

cast

cast(target_type)

Cast expression to indicated data type.

Similar to pandas.Series.astype.

Parameters

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

Returns

Name	Type	Description
	Value	Casted expression

Examples

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

python’s built-in types can be used

>>> x.cast(int)

or string names

>>> x.cast("int8")

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

coalesce

coalesce(*args)

Return the first non-null value from args.

Parameters

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

Returns

Name	Type	Description
	Value	Coalesced expression

Examples

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

collect

collect(where=None, order_by=None, include_null=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`

Returns

Name	Type	Description
	ArrayScalar	Collected array

Examples

Basic collect usage

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

>>> t.value.collect()

>>> type(t.value.collect())

Collect elements per group

>>> t.group_by("key").agg(v=lambda t: t.value.collect()).order_by("key")

Collect elements per group using a filter

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

desc

desc(nulls_first=False)

Sort an expression descending.

fill_null

fill_null(fill_value)

Replace any null values with the indicated fill value.

Parameters

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

Examples

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

>>> t.sex.fill_null("unrecorded").name("sex")

Returns

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

fillna

fillna(fill_value)

Deprecated - use fill_null instead.

group_concat

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

Concatenate values using the indicated separator to produce a string.

Parameters

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

Returns

Name	Type	Description
	StringScalar	Concatenated string expression

Examples

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

>>> t.bill_length_mm.group_concat()

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

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

hash

hash()

Compute an integer hash value.

The hashing function used is backend-dependent.

Returns

Name	Type	Description
	IntegerValue	The hash value of `self`

Examples

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

identical_to

identical_to(other)

Return whether this expression is identical to other.

Corresponds to IS NOT DISTINCT FROM in SQL.

Parameters

Name	Type	Description	Default
other	Value	Expression to compare to	required

Returns

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

Examples

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

isin

isin(values)

Check whether this expression’s values are in values.

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

Parameters

Name	Type	Description	Default
values	Value \| Sequence[Value]	Values or expression to check for membership	required

Returns

Name	Type	Description
	BooleanValue	Expression indicating membership

Examples

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

Check against a literal sequence of values

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

Check against a derived expression

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

Check against a column from a different table

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

NULL behavior

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

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

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

isnull

isnull()

Return whether this expression is NULL.

Examples

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

>>> t.bill_depth_mm.isnull()

name

name(name)

Rename an expression to name.

Parameters

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

Returns

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

Examples

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

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

notin

notin(values)

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

Opposite of Value.isin().

Parameters

Name	Type	Description	Default
values	Value \| Sequence[Value]	Values or expression to check for lack of membership	required

Returns

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

Examples

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

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

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

>>> t.bill_depth_mm.notnull()

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

>>> vals.nullif("male")

over

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

Construct a window expression.

Parameters

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

Returns

Name	Type	Description
	Value	A window function expression

substitute

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

Replace values given in values with replacement.

This is similar to the pandas replace method.

Parameters

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

Returns

Name	Type	Description
	Value	Replaced values

Examples

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

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

to_pandas

to_pandas(**kwargs)

Convert a column expression to a pandas Series or scalar object.

Parameters

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

Examples

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

try_cast

try_cast(target_type)

Try cast expression to indicated data type.

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

Parameters

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

Returns

Name	Type	Description
	Value	Casted expression

Examples

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

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

type

type()

Return the DataType of self.

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

>>> vals.typeof()

Different backends have different names for their native types

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

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

Methods

asc

between

Parameters

Returns

Examples

case

Returns

See Also

Examples

cases

Parameters

Returns

See Also

Examples

cast

Parameters

Returns

See Also

Examples

coalesce

Parameters

Returns

See Also

Examples

collect

Parameters

Returns

Examples

desc

fill_null

Parameters

See Also

Examples

Returns

fillna

group_concat

Parameters

Returns

Examples

hash

Returns

Examples

identical_to

Parameters

Returns

Examples

isin

Parameters

Returns

See Also

Examples

isnull

Examples

name

Parameters

Returns

Examples

notin

Parameters

Returns

Examples

notnull

Examples

nullif

Parameters

Returns

Examples

over

Parameters

Returns

substitute

Parameters

Returns

Examples

to_pandas

Parameters

Examples

try_cast

Parameters