Generic expressions

Scalars and columns of any element type.

Value

expr.types.generic.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 required
upper Value Upper bound 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
>>> t = ibis.table([("string_col", "string")], name="t")
>>> expr = t.string_col
>>> case_expr = (
...     expr.case()
...     .when("a", "an a")
...     .when("b", "a b")
...     .else_("null or (not a and not b)")
...     .end()
... )
>>> case_expr
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`.

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.

Parameters

Name Type Description Default
target_type dt.DataType Type to cast to required

Returns

Type Description
Value Casted expression

Examples

>>> import ibis
>>> ibis.options.interactive = False
>>> t = ibis.table(dict(a="int64"), name="t")
>>> t.a.cast("float")
r0 := UnboundTable: t
  a int64

Cast(a, float64): Cast(r0.a, to=float64)

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")
x: Coalesce([None, 4, 5])

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 ┃
┡━━━━━━━━╇━━━━━━━┩
│ stringint64 │
├────────┼───────┤
│ 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              ┃
┡━━━━━━━━╇━━━━━━━━━━━━━━━━┩
│ stringarray<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            ┃
┡━━━━━━━━╇━━━━━━━━━━━━━━┩
│ stringarray<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 ┃
┡━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━┩
│ float64float64       │
├────────────────┼───────────────┤
│           39.118.7 │
│           39.517.4 │
│           40.318.0 │
│            nannan │
│           36.719.3 │
└────────────────┴───────────────┘
>>> t.bill_length_mm.group_concat()

'39.1,39.5,40.3,36.7'
>>> t.bill_length_mm.group_concat(sep=": ")

'39.1: 39.5: 40.3: 36.7'
>>> t.bill_length_mm.group_concat(sep=": ", where=t.bill_depth_mm > 18)

'39.1: 36.7'

hash

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

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 ┃
┡━━━━━━━━━━━╇━━━━━━━━━━━━━━┩
│ stringint64        │
├───────────┼──────────────┤
│ Torgersen52 │
│ Biscoe   168 │
│ Dream    124 │
└───────────┴──────────────┘
>>> t.island.substitute({"Torgersen": "torg", "Biscoe": "bisc"}).value_counts()
┏━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ SimpleCase(island, island)  SimpleCase(island, island)_count ┃
┡━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩
│ stringint64                            │
├────────────────────────────┼──────────────────────────────────┤
│ 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 dt.DataType Type to try cast to 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 ┃
┡━━━━━━━━━╇━━━━━━━━━┩
│ int64string  │
├─────────┼─────────┤
│       11.0     │
│       22       │
│       3hello   │
│       4world   │
└─────────┴─────────┘
>>> t = t.mutate(numbers_to_strings=_.numbers.try_cast("string"))
>>> t = t.mutate(strings_to_numbers=_.strings.try_cast("int"))
>>> t
┏━━━━━━━━━┳━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━┓
┃ numbers  strings  numbers_to_strings  strings_to_numbers ┃
┡━━━━━━━━━╇━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━┩
│ int64stringstringint64              │
├─────────┼─────────┼────────────────────┼────────────────────┤
│       11.0    1                 1 │
│       22      2                 2 │
│       3hello  3                 NULL │
│       4world  4                 NULL │
└─────────┴─────────┴────────────────────┴────────────────────┘

type

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

expr.types.generic.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 nth value (0-indexed) over a window.
ntile Return the integer number of a partitioning of the column values.
nunique Compute the number of distinct rows in an expression.
percent_rank Return the relative rank of the values in the column.
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()

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

3700

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

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

55

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)

Return the cumulative max over a window.

cummin

cummin(self)

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

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

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

4800

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

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

2850

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

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

5550

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

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

55

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

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 ┃
┡━━━━━━━━╇━━━━━━━━━━━━━┩
│ stringint64       │
├────────┼─────────────┤
│ a     2 │
│ b     1 │
│ c     1 │
│ d     3 │
└────────┴─────────────┘

Scalar

expr.types.generic.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
Back to top