Generic expressions

Scalars and columns of any element type.

Value

Value()

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

Methods

Name	Description
as_table	Promote the expression to a Table.
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 case expression in one shot.
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.
fillna	Replace any null values with the indicated fill value.
greatest	Compute the largest value among the supplied arguments.
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.
least	Compute the smallest value among the supplied arguments.
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 this expression.
typeof	Return the data type of the expression.

as_table

as_table(self)

Promote the expression to a Table.

Returns

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

Sort an expression ascending.

between

between(self, 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

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

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

Type	Description
`SimpleCaseBuilder`	A case builder

Examples

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

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

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

┏━━━━━━━━━━━━━━━━━━━━━━┓
┃ SimpleCase(sex, 'U') ┃
┡━━━━━━━━━━━━━━━━━━━━━━┩
│ string               │
├──────────────────────┤
│ M                    │
│ F                    │
│ F                    │
│ U                    │
│ F                    │
└──────────────────────┘

Cases not given result in the ELSE case

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

┏━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ SimpleCase(sex, 'OTHER') ┃
┡━━━━━━━━━━━━━━━━━━━━━━━━━━┩
│ string                   │
├──────────────────────────┤
│ M                        │
│ OTHER                    │
│ OTHER                    │
│ OTHER                    │
│ OTHER                    │
└──────────────────────────┘

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

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

┏━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ SimpleCase(sex, Cast(None, string)) ┃
┡━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩
│ string                              │
├─────────────────────────────────────┤
│ M                                   │
│ NULL                                │
│ NULL                                │
│ NULL                                │
│ NULL                                │
└─────────────────────────────────────┘

cases

cases(self, case_result_pairs, default=None)

Create a case expression in one shot.

Parameters

Name	Type	Description	Default
`case_result_pairs`	Iterable[tuple[`ir`.`BooleanValue`, Value]]	Conditional-result pairs	required
`default`	Value \| None	Value to return if none of the case conditions are true	`None`

Returns

Type	Description
Value	Value expression

Examples

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

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

>>> number_letter_map = ((1, "a"), (2, "b"), (3, "c"))
>>> t.values.cases(number_letter_map, default="unk").name("replace")

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

cast

cast(self, 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

Type	Description
Value	Casted expression

Examples

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

┏━━━━━━━━━━━━━━━┓
┃ bill_depth_mm ┃
┡━━━━━━━━━━━━━━━┩
│ float64       │
├───────────────┤
│          18.7 │
│          17.4 │
│          18.0 │
│           nan │
│          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: Conversion Error: Could not convert string 'a string' to INT64

coalesce

coalesce(self, *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

Type	Description
Value	Coalesced expression

Examples

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

collect

collect(self, where=None)

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	Filter to apply before aggregation	`None`

Returns

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

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

>>> t.value.collect()

[1, 2, 3, 4, 5]

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

ibis.expr.types.arrays.ArrayScalar

Collect elements per group

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

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

Collect elements per group using a filter

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

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

desc

desc(self)

Sort an expression descending.

fillna

fillna(self, fill_value)

Replace any null values with the indicated fill value.

Parameters

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

Examples

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

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

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

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

Returns

Type	Description
Value	`self` filled with `fill_value` where it is `NA`

greatest

greatest(self, *args)

Compute the largest value among the supplied arguments.

Parameters

Name	Type	Description	Default
`args`	`ir`.`Value`	Arguments to choose from	`()`

Returns

Type	Description
Value	Maximum of the passed arguments

group_concat

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

Concatenate values using the indicated separator to produce a string.

Parameters

Name	Type	Description	Default
`sep`	str	Separator will be used to join strings	`','`
`where`	`ir`.`BooleanValue` \| None	Filter expression	`None`

Returns

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 │
│            nan │           nan │
│           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(self)

Compute an integer hash value.

The hashing function used is backend-dependent.

Returns

Type	Description
`IntegerValue`	The hash value of `self`

identical_to

identical_to(self, 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

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

Examples

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

True

isin

isin(self, values)

Check whether this expression’s values are in values.

Parameters

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

Returns

Type	Description
`BooleanValue`	Expression indicating membership

Examples

Check whether a column’s values are contained in a sequence

>>> import ibis
>>> table = ibis.table(dict(string_col="string"), name="t")
>>> table.string_col.isin(["foo", "bar", "baz"])

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

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

Check whether a column’s values are contained in another table’s column

>>> table2 = ibis.table(dict(other_string_col="string"), name="t2")
>>> table.string_col.isin(table2.other_string_col)

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

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

isnull

isnull(self)

Return whether this expression is NULL.

Examples

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

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

>>> t.bill_depth_mm.isnull()

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

least

least(self, *args)

Compute the smallest value among the supplied arguments.

Parameters

Name	Type	Description	Default
`args`	`ir`.`Value`	Arguments to choose from	`()`

Returns

Type	Description
Value	Minimum of the passed arguments

name

name(self, name)

Rename an expression to name.

Parameters

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

Returns

Type	Description
Value	`self` with name `name`

Examples

>>> import ibis
>>> t = ibis.table(dict(a="int64"), name="t")
>>> t.a.name("b")

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

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

notin

notin(self, values)

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

Parameters

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

Returns

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

Examples

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

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

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

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

notnull

notnull(self)

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

>>> t.bill_depth_mm.notnull()

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

nullif

nullif(self, 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.

Parameters

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

Returns

Type	Description
Value	Value expression

over

over(self, 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

Type	Description
Value	A window function expression

substitute

substitute(self, 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

Type	Description
Value	Replaced values

Examples

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

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

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

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

to_pandas

to_pandas(self, **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()

	species	island	bill_length_mm	bill_depth_mm	flipper_length_mm	body_mass_g	sex	year
0	Adelie	Torgersen	39.1	18.7	181.0	3750.0	male	2007
1	Adelie	Torgersen	39.5	17.4	186.0	3800.0	female	2007
2	Adelie	Torgersen	40.3	18.0	195.0	3250.0	female	2007
3	Adelie	Torgersen	NaN	NaN	NaN	NaN	None	2007
4	Adelie	Torgersen	36.7	19.3	193.0	3450.0	female	2007

try_cast

try_cast(self, 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

Type	Description
Value	Casted expression

Examples

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

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

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

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

type

type(self)

Return the [DataType] of this expression.

typeof

typeof(self)

Return the data type of the expression.

The values of the returned strings are necessarily backend dependent.

Returns

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

Column

Column()

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`.
count	Compute the number of rows in an expression.
cume_dist	Return the cumulative distribution over a window.
cummax	Return the cumulative max over a window.
cummin	Return the cumulative min over a window.
dense_rank	Position of first element within each group of equal values.
first	Return the first value of a column.
lag	Return the row located at `offset` rows before the current row.
last	Return the last value of a column.
lead	Return the row located at `offset` rows after the current row.
max	Return the maximum of a column.
min	Return the minimum of a column.
mode	Return the mode of a column.
nth	Return the `n`th value (0-indexed) over a window.
ntile	Return the integer number of a partitioning of the column values.
nunique	Compute the number of distinct rows in an expression.
percent_rank	Return the relative rank of the values in the column.
rank	Compute position of first element within each equal-value group in sorted order.
topk	Return a “top k” expression.
value_counts	Compute a frequency table.

approx_median

approx_median(self, where=None)

Return an approximate of the median of self.

The result may or may not be exact

Whether the result is an approximation depends on the backend.

Do not depend on the results being exact

Parameters

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

Returns

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

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

approx_nunique

approx_nunique(self, where=None)

Return the approximate number of distinct elements in self.

The result may or may not be exact

Whether the result is an approximation depends on the backend.

Do not depend on the results being exact

Parameters

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

Returns

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

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

arbitrary

arbitrary(self, where=None, how='first')

Select an arbitrary value in a column.

Parameters

Name	Type	Description	Default
`where`	`ir`.`BooleanValue` \| None	A filter expression	`None`
`how`	Literal[‘first’, ‘last’, ‘heavy’]	The method to use for selecting the element. * `"first"`: Select the first non-`NULL` element * `"last"`: Select the last non-`NULL` element * `"heavy"`: Select a frequently occurring value using the heavy hitters algorithm. `"heavy"` is only supported by Clickhouse backend.	`'first'`

Returns

Type	Description
Scalar	An expression

argmax

argmax(self, key, where=None)

Return the value of self that maximizes key.

Parameters

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

Returns

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(self, key, where=None)

Return the value of self that minimizes key.

Parameters

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

Returns

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'

count

count(self, where=None)

Compute the number of rows in an expression.

Parameters

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

Returns

Type	Description
`IntegerScalar`	Number of elements in an expression

cume_dist

cume_dist(self)

Return the cumulative distribution over a window.

cummax

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

Return the cumulative max over a window.

cummin

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

Return the cumulative min over a window.

dense_rank

dense_rank(self)

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

Type	Description
`IntegerColumn`	The rank

Examples

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

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

first

first(self, where=None)

Return the first value of a column.

Examples

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

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

>>> t.chars.first()

'a'

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

'b'

lag

lag(self, 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`

last

last(self, where=None)

Return the last value of a column.

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(self, 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`

max

max(self, 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

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

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

min

min(self, 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

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

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

mode

mode(self, 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

Type	Description
Scalar	The mode of `self`

Examples

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

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

nth

nth(self, 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

Type	Description
Column	The nth value over a window

ntile

ntile(self, 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

nunique

nunique(self, where=None)

Compute the number of distinct rows in an expression.

Parameters

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

Returns

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

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

percent_rank

percent_rank(self)

Return the relative rank of the values in the column.

rank

rank(self)

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

Equivalent to SQL’s RANK() window function.

Returns

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

topk

topk(self, k, by=None)

Return a “top k” expression.

Parameters

Name	Type	Description	Default
`k`	int	Return this number of rows	required
`by`	`ir`.`Value` \| None	An expression. Defaults to `count`.	`None`

Returns

Type	Description
`TableExpr`	A top-k expression

value_counts

value_counts(self)

Compute a frequency table.

Returns

Type	Description
`Table`	Frequency table expression

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.memtable({"chars": char} for char in "aabcddd")
>>> t

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

>>> t.chars.value_counts()

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

Scalar

Scalar()

Methods

Name	Description
as_table	Promote the scalar expression to a table.

as_table

as_table(self)

Promote the scalar expression to a table.

Returns

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

literal

ibis.literal(value, type=None)

Create a scalar expression from a Python value.

Use specific functions for arrays, structs and maps

Ibis supports literal construction of arrays using the following functions:

Constructing these types using literal will be deprecated in a future release.

Parameters

Name	Type	Description	Default
`value`	Any	A Python value	required
`type`	`dt`.`DataType` \| str \| None	An instance of `DataType` or a string indicating the ibis type of `value`. This parameter can be used in cases where ibis’s type inference isn’t sufficient for discovering the type of `value`.	`None`

Returns

Type	Description
Scalar	An expression representing a literal value

Examples

Construct an integer literal

>>> import ibis
>>> x = ibis.literal(42)
>>> x.type()

Int8(nullable=True)

Construct a float64 literal from an int

>>> y = ibis.literal(42, type="double")
>>> y.type()

Float64(nullable=True)

Ibis checks for invalid types

>>> ibis.literal("foobar", type="int64")

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

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

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

Examples

>>> import ibis
>>> start = ibis.param("date")
>>> end = ibis.param("date")
>>> schema = dict(timestamp_col="timestamp", value="double")
>>> t = ibis.table(schema, name="t")
>>> predicates = [t.timestamp_col >= start, t.timestamp_col <= end]
>>> t.filter(predicates).value.sum()

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

NA

ibis.expr.api.NA

The NULL scalar.

Examples

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

True

null

ibis.null()

Create a NULL/NA scalar.

coalesce

ibis.coalesce(*args)

Return the first non-null value from args.

Parameters

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

Returns

Type	Description
`Value`	Coalesced expression

Examples

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

least

ibis.least(*args)

Compute the smallest value among the supplied arguments.

Parameters

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

Returns

Type	Description
`Value`	Minimum of the passed arguments

Examples

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

greatest

ibis.greatest(*args)

Compute the largest value among the supplied arguments.

Parameters

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

Returns

Type	Description
`Value`	Maximum of the passed arguments

Examples

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

asc

ibis.asc(expr)

Create a ascending sort key from asc or column name.

Parameters

Name	Type	Description	Default
`expr`	`ir`.`Column` \| str	The expression or column name to use for sorting	required

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.examples.penguins.fetch()
>>> t[["species", "year"]].order_by(ibis.asc("year")).head()

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

Returns

Type	Description
`ir`.`ValueExpr`	An expression

desc

ibis.desc(expr)

Create a descending sort key from expr or column name.

Parameters

Name	Type	Description	Default
`expr`	`ir`.`Column` \| str	The expression or column name to use for sorting	required

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.examples.penguins.fetch()
>>> t[["species", "year"]].order_by(ibis.desc("year")).head()

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

Returns

Type	Description
`ir`.`ValueExpr`	An expression

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

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

Examples

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

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

case

ibis.case()

Begin constructing a case expression.

Use the .when method on the resulting object followed by .end to create a complete case expression.

Returns

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

Examples

>>> import ibis
>>> from ibis import _
>>> ibis.options.interactive = True
>>> t = ibis.memtable(
...     {
...         "left": [1, 2, 3, 4],
...         "symbol": ["+", "-", "*", "/"],
...         "right": [5, 6, 7, 8],
...     }
... )
>>> t.mutate(
...     result=(
...         ibis.case()
...         .when(_.symbol == "+", _.left + _.right)
...         .when(_.symbol == "-", _.left - _.right)
...         .when(_.symbol == "*", _.left * _.right)
...         .when(_.symbol == "/", _.left / _.right)
...         .end()
...     )
... )

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

show_sql

ibis.show_sql(expr, dialect=None, file=None)

Pretty-print the compiled SQL string of an expression.

If a dialect cannot be inferred and one was not passed, duckdb will be used as the dialect

Parameters

Name	Type	Description	Default
`expr`	`ir`.`Expr`	Ibis expression whose SQL will be printed	required
`dialect`	str \| None	String dialect. This is typically not required, but can be useful if ibis cannot infer the backend dialect.	`None`
`file`	IO[str] \| None	File to write output to	`None`

Examples

>>> import ibis
>>> from ibis import _
>>> t = ibis.table(dict(a="int"), name="t")
>>> expr = t.select(c=_.a * 2)
>>> ibis.show_sql(expr)  # duckdb dialect by default

SELECT
  t0.a * CAST(2 AS TINYINT) AS c
FROM t AS t0

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

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

to_sql

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

Return the formatted SQL string for an expression.

Parameters

Name	Type	Description	Default
`expr`	`ir`.`Expr`	Ibis expression.	required
`dialect`	str \| None	SQL dialect to use for compilation.	`None`
`kwargs`		Scalar parameters	`{}`

Returns

Type	Description
str	Formatted SQL string

Value

Methods

as_table

Returns

Examples

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

fillna

Parameters

See Also

Examples

Returns

greatest

Parameters

Returns

group_concat

Parameters

Returns

Examples

hash

Returns

identical_to

Parameters

Returns

Examples

isin

Parameters

Returns

Examples

isnull

Examples

least

Parameters

Returns

name

Parameters

Returns

Examples

notin

Parameters

Returns

Examples

notnull

Examples

nullif

Parameters

Returns

over

Parameters

Returns

substitute

Parameters

Returns