Generic Expression APIs¶

These expressions are available on scalars and columns of any element type.

`Value` ¶

Bases: Expr

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

Functions¶

`as_table()` ¶

Promote the expression to a table.

Returns:

Type	Description
`Table`	A table expression

Examples:

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

Sort an expression ascending.

`between(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()` ¶

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
r0 := UnboundTable: t
  string_col string
SimpleCase(...)

`cases(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(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(*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(...)

`collect(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())
<class '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()` ¶

Sort an expression descending.

`fillna(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(*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(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()` ¶

Compute an integer hash value.

The hashing function used is backend-dependent.

Returns:

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

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

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(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'])
r0 := UnboundTable: t
  string_col string
Contains(string_col): Contains(...)

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)
r0 := UnboundTable: t
  string_col string
r1 := UnboundTable: t2
  other_string_col string
Contains(string_col, other_string_col): Contains(...)

`isnull()` ¶

Return whether this expression is NULL.

Examples:

>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.examples.penguins.fetch().limit(5)
>>> t.bill_depth_mm
┏━━━━━━━━━━━━━━━┓
┃ bill_depth_mm ┃
┡━━━━━━━━━━━━━━━┩
│ float64       │
├───────────────┤
│          18.7 │
│          17.4 │
│          18.0 │
│           nan │
│          19.3 │
└───────────────┘
>>> t.bill_depth_mm.isnull()
┏━━━━━━━━━━━━━━━━━━━━━━━┓
┃ IsNull(bill_depth_mm) ┃
┡━━━━━━━━━━━━━━━━━━━━━━━┩
│ boolean               │
├───────────────────────┤
│ False                 │
│ False                 │
│ False                 │
│ True                  │
│ False                 │
└───────────────────────┘

`least(*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)` ¶

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")
r0 := UnboundTable: t
  a int64
b: r0.a

`notin(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])
┏━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ NotContains(bill_depth_mm) ┃
┡━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩
│ boolean                    │
├────────────────────────────┤
│ False                      │
│ True                       │
│ True                       │
│ NULL                       │
│ True                       │
└────────────────────────────┘

`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 │
│           nan │
│          19.3 │
└───────────────┘
>>> t.bill_depth_mm.notnull()
┏━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ NotNull(bill_depth_mm) ┃
┡━━━━━━━━━━━━━━━━━━━━━━━━┩
│ boolean                │
├────────────────────────┤
│ True                   │
│ True                   │
│ True                   │
│ False                  │
│ True                   │
└────────────────────────┘

`nullif(null_if_expr)` ¶

Set values to null if they equal the values null_if_expr.

Commonly use 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(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(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(**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  ...  body_mass_g     sex  year
0  Adelie  Torgersen            39.1  ...       3750.0    male  2007
1  Adelie  Torgersen            39.5  ...       3800.0  female  2007
2  Adelie  Torgersen            40.3  ...       3250.0  female  2007
3  Adelie  Torgersen             NaN  ...          NaN    None  2007
4  Adelie  Torgersen            36.7  ...       3450.0  female  2007
[5 rows x 8 columns]

`type()` ¶

Return the [DataType] of this expression.

`typeof()` ¶

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

Bases: Value, _FixedTextJupyterMixin

Functions¶

`approx_median(where=None)` ¶

Return an approximate of the median of self.

The result may or may not be exact

Whether the result is an approximation depends on the backend.

Do not depend on the results being exact

Parameters:

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

Returns:

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

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.

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

Return the relative rank of the values in the column.

`rank()` ¶

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`
`--------`
`ir.IntegerColumn`
`ir.IntegerColumn`
`ir.IntegerColumn`
`ir.IntegerColumn`
`ir.IntegerColumn`
`ir.IntegerColumn`
`ir.IntegerColumn`
`ir.IntegerColumn`
`ir.IntegerColumn`
`ir.IntegerColumn`
`ir.IntegerColumn`
`ir.IntegerColumn`
`ir.IntegerColumn`
`ir.IntegerColumn`
`ir.IntegerColumn`
`ir.IntegerColumn`

`topk(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()` ¶

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

Bases: Value

Functions¶

`as_table()` ¶

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

Last update: August 5, 2022

Generic Expression APIs¶

Value ¶

Functions¶

as_table() ¶

asc() ¶

between(lower, upper) ¶

case() ¶

cases(case_result_pairs, default=None) ¶

cast(target_type) ¶

coalesce(*args) ¶

collect(where=None) ¶

desc() ¶

fillna(fill_value) ¶

greatest(*args) ¶

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

hash() ¶

identical_to(other) ¶

isin(values) ¶

isnull() ¶

least(*args) ¶

name(name) ¶

notin(values) ¶

notnull() ¶

nullif(null_if_expr) ¶

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

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

to_pandas(**kwargs) ¶

type() ¶

typeof() ¶

Column ¶

Functions¶

approx_median(where=None) ¶

approx_nunique(where=None) ¶

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

argmax(key, where=None) ¶

argmin(key, where=None) ¶

count(where=None) ¶

cume_dist() ¶

cummax() ¶

cummin() ¶

dense_rank() ¶

first(where=None) ¶

lag(offset=None, default=None) ¶

last(where=None) ¶

lead(offset=None, default=None) ¶

max(where=None) ¶

min(where=None) ¶

mode(where=None) ¶

nth(n) ¶

ntile(buckets) ¶

nunique(where=None) ¶

percent_rank() ¶

rank() ¶

topk(k, by=None) ¶

value_counts() ¶

Scalar ¶

Functions¶

as_table() ¶

`Value` ¶

`as_table()` ¶

`asc()` ¶

`between(lower, upper)` ¶

`case()` ¶

`cases(case_result_pairs, default=None)` ¶

`cast(target_type)` ¶

`coalesce(*args)` ¶

`collect(where=None)` ¶

`desc()` ¶

`fillna(fill_value)` ¶

`greatest(*args)` ¶

`group_concat(sep=',', where=None)` ¶

`hash()` ¶

`identical_to(other)` ¶

`isin(values)` ¶

`isnull()` ¶

`least(*args)` ¶

`name(name)` ¶

`notin(values)` ¶

`notnull()` ¶

`nullif(null_if_expr)` ¶

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

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

`to_pandas(**kwargs)` ¶

`type()` ¶

`typeof()` ¶

`Column` ¶

`approx_median(where=None)` ¶

`approx_nunique(where=None)` ¶

`arbitrary(where=None, how='first')` ¶

`argmax(key, where=None)` ¶

`argmin(key, where=None)` ¶

`count(where=None)` ¶

`cume_dist()` ¶

`cummax()` ¶

`cummin()` ¶

`dense_rank()` ¶

`first(where=None)` ¶

`lag(offset=None, default=None)` ¶

`last(where=None)` ¶

`lead(offset=None, default=None)` ¶

`max(where=None)` ¶

`min(where=None)` ¶

`mode(where=None)` ¶

`nth(n)` ¶

`ntile(buckets)` ¶

`nunique(where=None)` ¶

`percent_rank()` ¶

`rank()` ¶

`topk(k, by=None)` ¶

`value_counts()` ¶

`Scalar` ¶

`as_table()` ¶