Skip to content

Table Expressions

Table expressions form the basis for most Ibis expressions.

relations

Classes

Table (Expr)

Methods
aggregate(self, metrics=None, by=None, having=None, **kwargs)

Aggregate a table with a given set of reductions grouping by by.

Parameters

metrics Aggregate expressions by Grouping expressions having Post-aggregation filters kwargs Named aggregate expressions

Returns

Table An aggregate table expression

alias(self, alias)

Create a table expression with a specific name alias.

This method is useful for exposing an ibis expression to the underlying backend for use in the Table.sql method.

.alias will create a temporary view

.alias creates a temporary view in the database.

This side effect will be removed in a future version of ibis and is not part of the public API.

Parameters

alias Name of the child expression

Returns

Table An table expression

Examples

con = ibis.duckdb.connect("ci/ibis-testing-data/ibis_testing.ddb") t = con.table("functional_alltypes") expr = t.alias("my_t").sql("SELECT sum(double_col) FROM my_t") expr r0 := AlchemyTable: functional_alltypes index int64 ⋮ month int32 r1 := View[r0]: my_t schema: index int64 ⋮ month int32 SQLStringView[r1]: _ibis_view_0 query: 'SELECT sum(double_col) FROM my_t' schema: sum(double_col) float64

asof_join(left, right, predicates=(), by=(), tolerance=None, *, suffixes=('_x', '_y'))

Perform an "as-of" join between left and right.

Similar to a left join except that the match is done on nearest key rather than equal keys.

Optionally, match keys with by before joining with predicates.

Parameters

left Table expression right Table expression predicates Join expressions by column to group by before joining tolerance Amount of time to look behind when joining suffixes Left and right suffixes that will be used to rename overlapping columns.

Returns

Table Table expression

count(self)

Compute the number of rows in the table.

Returns

IntegerScalar Number of rows in the table

cross_join(left, right, *rest, *, suffixes=('_x', '_y'))

Compute the cross join of a sequence of tables.

Parameters

left Left table right Right table rest Additional tables to cross join suffixes Left and right suffixes that will be used to rename overlapping columns.

Returns

Table Cross join of left, right and rest

Examples

import ibis schemas = [(name, 'int64') for name in 'abcde'] a, b, c, d, e = [ ... ibis.table([(name, type)], name=name) for name, type in schemas ... ] joined1 = ibis.cross_join(a, b, c, d, e) joined1 r0 := UnboundTable[e] e int64 r1 := UnboundTable[d] d int64 r2 := UnboundTable[c] c int64 r3 := UnboundTable[b] b int64 r4 := UnboundTable[a] a int64 r5 := CrossJoin[r3, r2] r6 := CrossJoin[r5, r1] r7 := CrossJoin[r6, r0] CrossJoin[r4, r7]

difference(self, right)

Compute the set difference of two table expressions.

The input tables must have identical schemas.

Parameters

right Table expression

Returns

Table The rows present in left that are not present in right.

distinct(self)

Compute the set of unique rows in the table.

drop(self, fields)

Remove fields from a table.

Parameters

fields Fields to drop

Returns

Table Expression without fields

dropna(self, subset=None, how='any')

Remove rows with null values from the table.

Parameters

subset Columns names to consider when dropping nulls. By default all columns are considered. how Determine whether a row is removed if there is at least one null value in the row ('any'), or if all row values are null ('all'). Options are 'any' or 'all'. Default is 'any'.

Examples

import ibis t = ibis.table([('a', 'int64'), ('b', 'string')]) t = t.dropna() # Drop all rows where any values are null t = t.dropna(how='all') # Only drop rows where all values are null t = t.dropna(subset=['a'], how='all') # Only drop rows where all values in column 'a' are null # noqa: E501

Returns

Table Table expression

fillna(self, replacements)

Fill null values in a table expression.

Parameters

replacements Value with which to fill the nulls. If passed as a mapping, the keys are column names that map to their replacement value. If passed as a scalar, all columns are filled with that value.

Notes

There is potential lack of type stability with the fillna API. For example, different library versions may impact whether or not a given backend promotes integer replacement values to floats.

Examples

import ibis import ibis.expr.datatypes as dt t = ibis.table([('a', 'int64'), ('b', 'string')]) t = t.fillna(0.0) # Replace nulls in all columns with 0.0 t.fillna({c: 0.0 for c, t in t.schema().items() if t == dt.float64}) r0 := UnboundTable[unbound_table_...] a int64 b string r1 := FillNa[r0] replacements: 0.0 FillNa[r1] replacements: frozendict({})

Returns

Table Table expression

filter(self, predicates)

Select rows from table based on predicates.

Parameters

predicates Boolean value expressions used to select rows in table.

Returns

Table Filtered table expression

get_column(self, name)

Get a reference to a single column from the table

Returns

Column A column named name.

get_columns(self, iterable)

Get multiple columns from the table

Examples

import ibis table = ibis.table( ... [ ... ('a', 'int64'), ... ('b', 'string'), ... ('c', 'timestamp'), ... ('d', 'float'), ... ], ... name='t' ... ) a, b, c = table.get_columns(['a', 'b', 'c'])

Returns

list[ir.Column] List of column expressions

group_by(self, by=None, **additional_grouping_expressions)

Create a grouped table expression.

Parameters

by Grouping expressions additional_grouping_expressions Named grouping expressions

Examples

import ibis pairs = [('a', 'int32'), ('b', 'timestamp'), ('c', 'double')] t = ibis.table(pairs) b1, b2 = t.a, t.b result = t.group_by([b1, b2]).aggregate(sum_of_c=t.c.sum())

Returns

GroupedTable A grouped table expression

groupby(self, by=None, **additional_grouping_expressions)

Create a grouped table expression.

Parameters

by Grouping expressions additional_grouping_expressions Named grouping expressions

Examples

import ibis pairs = [('a', 'int32'), ('b', 'timestamp'), ('c', 'double')] t = ibis.table(pairs) b1, b2 = t.a, t.b result = t.group_by([b1, b2]).aggregate(sum_of_c=t.c.sum())

Returns

GroupedTable A grouped table expression

head(self, n=5)

Select the first n rows of a table.

The result set is not deterministic without a sort.

Parameters

n Number of rows to include, defaults to 5

Returns

Table table limited to n rows

info(self, buf=None)

Show column names, types and null counts.

Parameters

buf A writable buffer, defaults to stdout

intersect(self, right)

Compute the set intersection of two table expressions.

The input tables must have identical schemas.

Parameters

right Table expression

Returns

Table The rows common amongst left and right.

join(left, right, predicates=(), how='inner', *, suffixes=('_x', '_y'))

Perform a join between two tables.

Parameters

left Left table to join right Right table to join predicates Boolean or column names to join on how Join method suffixes Left and right suffixes that will be used to rename overlapping columns.

limit(self, n, offset=0)

Select the first n rows at beginning of table starting at offset.

Parameters

n Number of rows to include offset Number of rows to skip first

Returns

Table The first n rows of table starting at offset

materialize(self)

DEPRECATED: materialize is deprecated as of v3.0; remove the .materialize() call, it has no effect

mutate(self, exprs=None, **mutations)

Add columns to a table expression.

Parameters

exprs List of named expressions to add as columns mutations Named expressions using keyword arguments

Returns

Table Table expression with additional columns

Examples

Using keywords arguments to name the new columns

import ibis table = ibis.table( ... [('foo', 'double'), ('bar', 'double')], ... name='t' ... ) expr = table.mutate(qux=table.foo + table.bar, baz=5) expr r0 := UnboundTable[t] foo float64 bar float64 Selection[r0] selections: r0 baz: 5 qux: r0.foo + r0.bar

Use the name method to name the new columns.

new_columns = [ibis.literal(5).name('baz',), ... (table.foo + table.bar).name('qux')] expr2 = table.mutate(new_columns) expr.equals(expr2) True

prevent_rewrite(self, client=None)

Prevent optimization from happening below this expression.

DEPRECATED: prevent_rewrite is deprecated as of v4.0;

Only valid on SQL-string generating backends.

Parameters

client A client to use to create the SQLQueryResult operation. This can be useful if you're compiling an expression that derives from an UnboundTable operation.

Returns

Table An opaque SQL query

projection(self, exprs)

Compute a new table expression using exprs.

Passing an aggregate function to this method will broadcast the aggregate's value over the number of rows in the table and automatically constructs a window function expression. See the examples section for more details.

Parameters

exprs Column expression, string, or list of column expressions and strings.

Returns

Table Table expression

Examples

Simple projection

import ibis fields = [('a', 'int64'), ('b', 'double')] t = ibis.table(fields, name='t') proj = t.projection([t.a, (t.b + 1).name('b_plus_1')]) proj r0 := UnboundTable[t] a int64 b float64 Selection[r0] selections: a: r0.a b_plus_1: r0.b + 1 proj2 = t[t.a, (t.b + 1).name('b_plus_1')] proj.equals(proj2) True

Aggregate projection

agg_proj = t[t.a.sum().name('sum_a'), t.b.mean().name('mean_b')] agg_proj r0 := UnboundTable[t] a int64 b float64 Selection[r0] selections: sum_a: Window(Sum(r0.a), window=Window(how='rows')) mean_b: Window(Mean(r0.b), window=Window(how='rows'))

Note the Window objects here.

Their existence means that the result of the aggregation will be broadcast across the number of rows in the input column. The purpose of this expression rewrite is to make it easy to write column/scalar-aggregate operations like

t[(t.a - t.a.mean()).name('demeaned_a')] r0 := UnboundTable[t] a int64 b float64 Selection[r0] selections: demeaned_a: r0.a - Window(Mean(r0.a), window=Window(how='rows'))

relabel(self, substitutions)

Change table column names, otherwise leaving table unaltered.

Parameters

substitutions Name mapping

Returns

Table A relabeled table expression

rowid(self)

A numbering expression representing the row number of the results.

It can be 0 or 1 indexed depending on the backend. Check the backend documentation for specifics.

Notes

This function is different from the window function row_number (even if they are conceptually the same), and different from rowid in backends where it represents the physical location (e.g. Oracle or PostgreSQL's ctid).

Returns

IntegerColumn An integer column

Examples

my_table[my_table.rowid(), my_table.name].execute() # doctest: +SKIP 1|Ibis 2|pandas 3|Dask

schema(self)

Get the schema for this table (if one is known)

Returns

Schema The table's schema.

select(self, exprs)

Compute a new table expression using exprs.

Passing an aggregate function to this method will broadcast the aggregate's value over the number of rows in the table and automatically constructs a window function expression. See the examples section for more details.

Parameters

exprs Column expression, string, or list of column expressions and strings.

Returns

Table Table expression

Examples

Simple projection

import ibis fields = [('a', 'int64'), ('b', 'double')] t = ibis.table(fields, name='t') proj = t.projection([t.a, (t.b + 1).name('b_plus_1')]) proj r0 := UnboundTable[t] a int64 b float64 Selection[r0] selections: a: r0.a b_plus_1: r0.b + 1 proj2 = t[t.a, (t.b + 1).name('b_plus_1')] proj.equals(proj2) True

Aggregate projection

agg_proj = t[t.a.sum().name('sum_a'), t.b.mean().name('mean_b')] agg_proj r0 := UnboundTable[t] a int64 b float64 Selection[r0] selections: sum_a: Window(Sum(r0.a), window=Window(how='rows')) mean_b: Window(Mean(r0.b), window=Window(how='rows'))

Note the Window objects here.

Their existence means that the result of the aggregation will be broadcast across the number of rows in the input column. The purpose of this expression rewrite is to make it easy to write column/scalar-aggregate operations like

t[(t.a - t.a.mean()).name('demeaned_a')] r0 := UnboundTable[t] a int64 b float64 Selection[r0] selections: demeaned_a: r0.a - Window(Mean(r0.a), window=Window(how='rows'))

set_column(self, name, expr)

Replace an existing column with a new expression.

Parameters

name Column name to replace expr New data for column

Returns

Table Table expression with new columns

sort_by(self, sort_exprs)

Sort table by sort_exprs

Parameters

sort_exprs Sort specifications

Examples

import ibis t = ibis.table([('a', 'int64'), ('b', 'string')]) ab_sorted = t.sort_by([('a', True), ('b', False)])

Returns

Table Sorted table

sql(self, query)

Run a SQL query against a table expression.

The SQL string is backend specific

query must be valid SQL for the execution backend the expression will run against

See Table.alias for details on using named table expressions in a SQL string.

Parameters

query Query string

Returns

Table An opaque table expression

Examples

con = ibis.duckdb.connect("ci/ibis-testing-data/ibis_testing.ddb") t = con.table("functional_alltypes") expr = t.sql("SELECT sum(double_col) FROM functional_alltypes") expr r0 := AlchemyTable: functional_alltypes index int64 ⋮ month int32 SQLStringView[r0]: _ibis_view_1 query: 'SELECT sum(double_col) FROM functional_alltypes' schema: sum(double_col) float64

to_array(self)

View a single column table as an array.

Returns

Value A single column view of a table

union(self, right, distinct=False)

Compute the set union of two table expressions.

The input tables must have identical schemas.

Parameters

right Table expression distinct Only union distinct rows not occurring in the calling table (this can be very expensive, be careful)

Returns

Table Union of table and right

view(self)

Create a new table expression distinct from the current one.

Use this API for any self-referencing operations like a self-join.

Returns

Table Table expression

TableExpr (Expr)

Methods
aggregate(self, metrics=None, by=None, having=None, **kwargs)

Aggregate a table with a given set of reductions grouping by by.

Parameters

metrics Aggregate expressions by Grouping expressions having Post-aggregation filters kwargs Named aggregate expressions

Returns

Table An aggregate table expression

alias(self, alias)

Create a table expression with a specific name alias.

This method is useful for exposing an ibis expression to the underlying backend for use in the Table.sql method.

.alias will create a temporary view

.alias creates a temporary view in the database.

This side effect will be removed in a future version of ibis and is not part of the public API.

Parameters

alias Name of the child expression

Returns

Table An table expression

Examples

con = ibis.duckdb.connect("ci/ibis-testing-data/ibis_testing.ddb") t = con.table("functional_alltypes") expr = t.alias("my_t").sql("SELECT sum(double_col) FROM my_t") expr r0 := AlchemyTable: functional_alltypes index int64 ⋮ month int32 r1 := View[r0]: my_t schema: index int64 ⋮ month int32 SQLStringView[r1]: _ibis_view_0 query: 'SELECT sum(double_col) FROM my_t' schema: sum(double_col) float64

asof_join(left, right, predicates=(), by=(), tolerance=None, *, suffixes=('_x', '_y'))

Perform an "as-of" join between left and right.

Similar to a left join except that the match is done on nearest key rather than equal keys.

Optionally, match keys with by before joining with predicates.

Parameters

left Table expression right Table expression predicates Join expressions by column to group by before joining tolerance Amount of time to look behind when joining suffixes Left and right suffixes that will be used to rename overlapping columns.

Returns

Table Table expression

count(self)

Compute the number of rows in the table.

Returns

IntegerScalar Number of rows in the table

cross_join(left, right, *rest, *, suffixes=('_x', '_y'))

Compute the cross join of a sequence of tables.

Parameters

left Left table right Right table rest Additional tables to cross join suffixes Left and right suffixes that will be used to rename overlapping columns.

Returns

Table Cross join of left, right and rest

Examples

import ibis schemas = [(name, 'int64') for name in 'abcde'] a, b, c, d, e = [ ... ibis.table([(name, type)], name=name) for name, type in schemas ... ] joined1 = ibis.cross_join(a, b, c, d, e) joined1 r0 := UnboundTable[e] e int64 r1 := UnboundTable[d] d int64 r2 := UnboundTable[c] c int64 r3 := UnboundTable[b] b int64 r4 := UnboundTable[a] a int64 r5 := CrossJoin[r3, r2] r6 := CrossJoin[r5, r1] r7 := CrossJoin[r6, r0] CrossJoin[r4, r7]

difference(self, right)

Compute the set difference of two table expressions.

The input tables must have identical schemas.

Parameters

right Table expression

Returns

Table The rows present in left that are not present in right.

distinct(self)

Compute the set of unique rows in the table.

drop(self, fields)

Remove fields from a table.

Parameters

fields Fields to drop

Returns

Table Expression without fields

dropna(self, subset=None, how='any')

Remove rows with null values from the table.

Parameters

subset Columns names to consider when dropping nulls. By default all columns are considered. how Determine whether a row is removed if there is at least one null value in the row ('any'), or if all row values are null ('all'). Options are 'any' or 'all'. Default is 'any'.

Examples

import ibis t = ibis.table([('a', 'int64'), ('b', 'string')]) t = t.dropna() # Drop all rows where any values are null t = t.dropna(how='all') # Only drop rows where all values are null t = t.dropna(subset=['a'], how='all') # Only drop rows where all values in column 'a' are null # noqa: E501

Returns

Table Table expression

fillna(self, replacements)

Fill null values in a table expression.

Parameters

replacements Value with which to fill the nulls. If passed as a mapping, the keys are column names that map to their replacement value. If passed as a scalar, all columns are filled with that value.

Notes

There is potential lack of type stability with the fillna API. For example, different library versions may impact whether or not a given backend promotes integer replacement values to floats.

Examples

import ibis import ibis.expr.datatypes as dt t = ibis.table([('a', 'int64'), ('b', 'string')]) t = t.fillna(0.0) # Replace nulls in all columns with 0.0 t.fillna({c: 0.0 for c, t in t.schema().items() if t == dt.float64}) r0 := UnboundTable[unbound_table_...] a int64 b string r1 := FillNa[r0] replacements: 0.0 FillNa[r1] replacements: frozendict({})

Returns

Table Table expression

filter(self, predicates)

Select rows from table based on predicates.

Parameters

predicates Boolean value expressions used to select rows in table.

Returns

Table Filtered table expression

get_column(self, name)

Get a reference to a single column from the table

Returns

Column A column named name.

get_columns(self, iterable)

Get multiple columns from the table

Examples

import ibis table = ibis.table( ... [ ... ('a', 'int64'), ... ('b', 'string'), ... ('c', 'timestamp'), ... ('d', 'float'), ... ], ... name='t' ... ) a, b, c = table.get_columns(['a', 'b', 'c'])

Returns

list[ir.Column] List of column expressions

group_by(self, by=None, **additional_grouping_expressions)

Create a grouped table expression.

Parameters

by Grouping expressions additional_grouping_expressions Named grouping expressions

Examples

import ibis pairs = [('a', 'int32'), ('b', 'timestamp'), ('c', 'double')] t = ibis.table(pairs) b1, b2 = t.a, t.b result = t.group_by([b1, b2]).aggregate(sum_of_c=t.c.sum())

Returns

GroupedTable A grouped table expression

groupby(self, by=None, **additional_grouping_expressions)

Create a grouped table expression.

Parameters

by Grouping expressions additional_grouping_expressions Named grouping expressions

Examples

import ibis pairs = [('a', 'int32'), ('b', 'timestamp'), ('c', 'double')] t = ibis.table(pairs) b1, b2 = t.a, t.b result = t.group_by([b1, b2]).aggregate(sum_of_c=t.c.sum())

Returns

GroupedTable A grouped table expression

head(self, n=5)

Select the first n rows of a table.

The result set is not deterministic without a sort.

Parameters

n Number of rows to include, defaults to 5

Returns

Table table limited to n rows

info(self, buf=None)

Show column names, types and null counts.

Parameters

buf A writable buffer, defaults to stdout

intersect(self, right)

Compute the set intersection of two table expressions.

The input tables must have identical schemas.

Parameters

right Table expression

Returns

Table The rows common amongst left and right.

join(left, right, predicates=(), how='inner', *, suffixes=('_x', '_y'))

Perform a join between two tables.

Parameters

left Left table to join right Right table to join predicates Boolean or column names to join on how Join method suffixes Left and right suffixes that will be used to rename overlapping columns.

limit(self, n, offset=0)

Select the first n rows at beginning of table starting at offset.

Parameters

n Number of rows to include offset Number of rows to skip first

Returns

Table The first n rows of table starting at offset

materialize(self)

DEPRECATED: materialize is deprecated as of v3.0; remove the .materialize() call, it has no effect

mutate(self, exprs=None, **mutations)

Add columns to a table expression.

Parameters

exprs List of named expressions to add as columns mutations Named expressions using keyword arguments

Returns

Table Table expression with additional columns

Examples

Using keywords arguments to name the new columns

import ibis table = ibis.table( ... [('foo', 'double'), ('bar', 'double')], ... name='t' ... ) expr = table.mutate(qux=table.foo + table.bar, baz=5) expr r0 := UnboundTable[t] foo float64 bar float64 Selection[r0] selections: r0 baz: 5 qux: r0.foo + r0.bar

Use the name method to name the new columns.

new_columns = [ibis.literal(5).name('baz',), ... (table.foo + table.bar).name('qux')] expr2 = table.mutate(new_columns) expr.equals(expr2) True

prevent_rewrite(self, client=None)

Prevent optimization from happening below this expression.

DEPRECATED: prevent_rewrite is deprecated as of v4.0;

Only valid on SQL-string generating backends.

Parameters

client A client to use to create the SQLQueryResult operation. This can be useful if you're compiling an expression that derives from an UnboundTable operation.

Returns

Table An opaque SQL query

projection(self, exprs)

Compute a new table expression using exprs.

Passing an aggregate function to this method will broadcast the aggregate's value over the number of rows in the table and automatically constructs a window function expression. See the examples section for more details.

Parameters

exprs Column expression, string, or list of column expressions and strings.

Returns

Table Table expression

Examples

Simple projection

import ibis fields = [('a', 'int64'), ('b', 'double')] t = ibis.table(fields, name='t') proj = t.projection([t.a, (t.b + 1).name('b_plus_1')]) proj r0 := UnboundTable[t] a int64 b float64 Selection[r0] selections: a: r0.a b_plus_1: r0.b + 1 proj2 = t[t.a, (t.b + 1).name('b_plus_1')] proj.equals(proj2) True

Aggregate projection

agg_proj = t[t.a.sum().name('sum_a'), t.b.mean().name('mean_b')] agg_proj r0 := UnboundTable[t] a int64 b float64 Selection[r0] selections: sum_a: Window(Sum(r0.a), window=Window(how='rows')) mean_b: Window(Mean(r0.b), window=Window(how='rows'))

Note the Window objects here.

Their existence means that the result of the aggregation will be broadcast across the number of rows in the input column. The purpose of this expression rewrite is to make it easy to write column/scalar-aggregate operations like

t[(t.a - t.a.mean()).name('demeaned_a')] r0 := UnboundTable[t] a int64 b float64 Selection[r0] selections: demeaned_a: r0.a - Window(Mean(r0.a), window=Window(how='rows'))

relabel(self, substitutions)

Change table column names, otherwise leaving table unaltered.

Parameters

substitutions Name mapping

Returns

Table A relabeled table expression

rowid(self)

A numbering expression representing the row number of the results.

It can be 0 or 1 indexed depending on the backend. Check the backend documentation for specifics.

Notes

This function is different from the window function row_number (even if they are conceptually the same), and different from rowid in backends where it represents the physical location (e.g. Oracle or PostgreSQL's ctid).

Returns

IntegerColumn An integer column

Examples

my_table[my_table.rowid(), my_table.name].execute() # doctest: +SKIP 1|Ibis 2|pandas 3|Dask

schema(self)

Get the schema for this table (if one is known)

Returns

Schema The table's schema.

select(self, exprs)

Compute a new table expression using exprs.

Passing an aggregate function to this method will broadcast the aggregate's value over the number of rows in the table and automatically constructs a window function expression. See the examples section for more details.

Parameters

exprs Column expression, string, or list of column expressions and strings.

Returns

Table Table expression

Examples

Simple projection

import ibis fields = [('a', 'int64'), ('b', 'double')] t = ibis.table(fields, name='t') proj = t.projection([t.a, (t.b + 1).name('b_plus_1')]) proj r0 := UnboundTable[t] a int64 b float64 Selection[r0] selections: a: r0.a b_plus_1: r0.b + 1 proj2 = t[t.a, (t.b + 1).name('b_plus_1')] proj.equals(proj2) True

Aggregate projection

agg_proj = t[t.a.sum().name('sum_a'), t.b.mean().name('mean_b')] agg_proj r0 := UnboundTable[t] a int64 b float64 Selection[r0] selections: sum_a: Window(Sum(r0.a), window=Window(how='rows')) mean_b: Window(Mean(r0.b), window=Window(how='rows'))

Note the Window objects here.

Their existence means that the result of the aggregation will be broadcast across the number of rows in the input column. The purpose of this expression rewrite is to make it easy to write column/scalar-aggregate operations like

t[(t.a - t.a.mean()).name('demeaned_a')] r0 := UnboundTable[t] a int64 b float64 Selection[r0] selections: demeaned_a: r0.a - Window(Mean(r0.a), window=Window(how='rows'))

set_column(self, name, expr)

Replace an existing column with a new expression.

Parameters

name Column name to replace expr New data for column

Returns

Table Table expression with new columns

sort_by(self, sort_exprs)

Sort table by sort_exprs

Parameters

sort_exprs Sort specifications

Examples

import ibis t = ibis.table([('a', 'int64'), ('b', 'string')]) ab_sorted = t.sort_by([('a', True), ('b', False)])

Returns

Table Sorted table

sql(self, query)

Run a SQL query against a table expression.

The SQL string is backend specific

query must be valid SQL for the execution backend the expression will run against

See Table.alias for details on using named table expressions in a SQL string.

Parameters

query Query string

Returns

Table An opaque table expression

Examples

con = ibis.duckdb.connect("ci/ibis-testing-data/ibis_testing.ddb") t = con.table("functional_alltypes") expr = t.sql("SELECT sum(double_col) FROM functional_alltypes") expr r0 := AlchemyTable: functional_alltypes index int64 ⋮ month int32 SQLStringView[r0]: _ibis_view_1 query: 'SELECT sum(double_col) FROM functional_alltypes' schema: sum(double_col) float64

to_array(self)

View a single column table as an array.

Returns

Value A single column view of a table

union(self, right, distinct=False)

Compute the set union of two table expressions.

The input tables must have identical schemas.

Parameters

right Table expression distinct Only union distinct rows not occurring in the calling table (this can be very expensive, be careful)

Returns

Table Union of table and right

view(self)

Create a new table expression distinct from the current one.

Use this API for any self-referencing operations like a self-join.

Returns

Table Table expression

Functions

find_base_table(expr)

DEPRECATED: find_base_table is deprecated as of v4.0; Use ibis.expr.analysis.find_first_base_table() instead

groupby

User API for grouping operations.

Classes

GroupedTable

An intermediate table expression to hold grouping information.

Methods
count(self, metric_name='count')

Computing the number of rows per group.

Parameters

metric_name Name to use for the row count metric

Returns

Table The aggregated table

having(self, expr)

Add a post-aggregation result filter expr.

Parameters

expr An expression that filters based on an aggregate value.

Returns

GroupedTable A grouped table expression

mutate(self, exprs=None, **kwds)

Return a table projection with window functions applied.

Any arguments can be functions.

Parameters

exprs List of expressions kwds Expressions

Examples

import ibis t = ibis.table([ ... ('foo', 'string'), ... ('bar', 'string'), ... ('baz', 'double'), ... ], name='t') t UnboundTable[t] foo string bar string baz float64 expr = (t.group_by('foo') ... .order_by(ibis.desc('bar')) ... .mutate(qux=lambda x: x.baz.lag(), ... qux2=t.baz.lead())) print(expr) r0 := UnboundTable[t] foo string bar string baz float64 Selection[r0] selections: r0 qux: Window(Lag(r0.baz), window=Window(group_by=[r0.foo], order_by=[desc|r0.bar], how='rows')) qux2: Window(Lead(r0.baz), window=Window(group_by=[r0.foo], order_by=[desc|r0.bar], how='rows'))

Returns

Table A table expression with window functions applied

order_by(self, expr)

Sort a grouped table expression by expr.

Notes

This API call is ignored in aggregations.

Parameters

expr Expressions to order the results by

Returns

GroupedTable A sorted grouped GroupedTable

over(self, window)

Add a window frame clause to be applied to child analytic expressions.

Parameters

window Window to add to child analytic expressions

Returns

GroupedTable A new grouped table expression

projection(self, exprs)

Project new columns out of the grouped table.

See Also

ibis.expr.groupby.GroupedTable.mutate

size(self, metric_name='count')

Computing the number of rows per group.

Parameters

metric_name Name to use for the row count metric

Returns

Table The aggregated table


Last update: February 3, 2022