Top-level APIs¶

These methods and objects are available directly in the ibis module.

`and_(*predicates)` ¶

Combine multiple predicates using &.

Parameters:

Name	Type	Description	Default
`predicates`	`ir.BooleanValue`	Boolean value expressions	`()`

Returns:

Type	Description
`BooleanValue`	A new predicate that evaluates to True if all composing predicates are True. If no predicates were provided, returns True.

`array(values, type=None)` ¶

Create an array expression.

If the input expressions are all column expressions, then the output will be an ArrayColumn. The input columns will be concatenated row-wise to produce each array in the output array column. Each array will have length n, where n is the number of input columns. All input columns should be of the same datatype.

If the input expressions are Python literals, then the output will be a single ArrayScalar of length n, where n is the number of input values. This is equivalent to

values = [1, 2, 3]
ibis.literal(values)

Parameters:

Name	Type	Description	Default
`values`	`Iterable[V]`	An iterable of Ibis expressions or a list of Python literals	required
`type`	`str \| dt.DataType \| None`	An instance of `ibis.expr.datatypes.DataType` or a string indicating the ibis type of `value`.	`None`

Returns:

Type	Description
`ArrayValue`	An array column (if the inputs are column expressions), or an array scalar (if the inputs are Python literals)

Examples:

Create an array column from column expressions

>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.memtable({'a': [1, 2, 3], 'b': [4, 5, 6]})
>>> ibis.array([t.a, t.b])
┏━━━━━━━━━━━━━━━━━━━━━━┓
┃ ArrayColumn()        ┃
┡━━━━━━━━━━━━━━━━━━━━━━┩
│ array<int64>         │
├──────────────────────┤
│ [1, 4]               │
│ [2, 5]               │
│ [3, 6]               │
└──────────────────────┘

Create an array scalar from Python literals

>>> ibis.array([1.0, 2.0, 3.0])
[1.0, 2.0, 3.0]

Mixing scalar and column expressions is allowed

>>> ibis.array([t.a, 42])
┏━━━━━━━━━━━━━━━━━━━━━━┓
┃ ArrayColumn()        ┃
┡━━━━━━━━━━━━━━━━━━━━━━┩
│ array<int64>         │
├──────────────────────┤
│ [1, 42]              │
│ [2, 42]              │
│ [3, 42]              │
└──────────────────────┘

`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

`case()` ¶

Begin constructing a case expression.

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

Examples:

>>> import ibis
>>> cond1 = ibis.literal(1) == 1
>>> cond2 = ibis.literal(2) == 1
>>> expr = ibis.case().when(cond1, 3).when(cond2, 4).end()
>>> expr
SearchedCase(...)

Returns:

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

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

`cumulative_window(group_by=None, order_by=None)` ¶

Create a cumulative window for use with window functions.

All window frames / ranges are inclusive.

Parameters:

Name	Type	Description	Default
`group_by`		Grouping key	`None`
`order_by`		Ordering key	`None`

Returns:

Type	Description
`Window`	A window frame

`date(value, *args)` ¶

Return a date literal if value is coercible to a date.

Parameters:

Name	Type	Description	Default
`value`		Date string, datetime object or numeric value	required
`args`		Month and day if `value` is a year	`()`

Returns:

Type	Description
`DateScalar`	A date expression

`deferred = Deferred()` `module-attribute` ¶

Deferred expression object.

Use this object to refer to a previous table expression in a chain of expressions.

_ may conflict with other idioms in Python

See ibis-project/ibis#4704 for details.

Use from ibis import deferred as <NAME> to assign a different name to the deferred object builder.

Examples:

>>> from ibis import _
>>> t = ibis.table(dict(key="int", value="float"), name="t")
>>> expr = t.group_by(key=_.key - 1).agg(total=_.value.sum())
>>> expr.schema()
ibis.Schema {
  key    int64
  total  float64
}

`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

`difference(table, *rest, distinct=True)` ¶

Compute the set difference of multiple table expressions.

The input tables must have identical schemas.

Parameters:

Name	Type	Description	Default
`table`	`ir.Table`	A table expression	required
`*rest`	`ir.Table`	Additional table expressions	`()`
`distinct`	`bool`	Only diff distinct rows not occurring in the calling table	`True`

Returns:

Type	Description
`Table`	The rows present in `self` that are not present in `tables`.

Examples:

>>> import ibis
>>> ibis.options.interactive = True
>>> t1 = ibis.memtable({"a": [1, 2]})
>>> t1
┏━━━━━━━┓
┃ a     ┃
┡━━━━━━━┩
│ int64 │
├───────┤
│     1 │
│     2 │
└───────┘
>>> t2 = ibis.memtable({"a": [2, 3]})
>>> t2
┏━━━━━━━┓
┃ a     ┃
┡━━━━━━━┩
│ int64 │
├───────┤
│     2 │
│     3 │
└───────┘
>>> ibis.difference(t1, t2)
┏━━━━━━━┓
┃ a     ┃
┡━━━━━━━┩
│ int64 │
├───────┤
│     1 │
└───────┘

`dtype = dt.dtype` `module-attribute` ¶

`get_backend(expr=None)` ¶

Get the current Ibis backend to use for a given expression.

expr An expression to get the backend from. If not passed, the default backend is returned.

Returns:

Type	Description
`BaseBackend`	The Ibis backend.

`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

`ifelse = _deferred(ir.BooleanValue.ifelse)` `module-attribute` ¶

Construct a ternary conditional expression.

Parameters:

Name	Type	Description	Default
`true_expr`	`ir.Value`	Expression to return if `self` evaluates to `True`	required
`false_expr`	`ir.Value`	Expression to return if `self` evaluates to `False` or `NULL`	required

Returns:

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

Examples:

>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.memtable({"is_person": [True, False, True, None]})
>>> ibis.ifelse(t.is_person, "yes", "no")
┏━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ Where(is_person, 'yes', 'no') ┃
┡━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩
│ string                        │
├───────────────────────────────┤
│ yes                           │
│ no                            │
│ yes                           │
│ no                            │
└───────────────────────────────┘

`intersect(table, *rest, distinct=True)` ¶

Compute the set intersection of multiple table expressions.

The input tables must have identical schemas.

Parameters:

Name	Type	Description	Default
`table`	`ir.Table`	A table expression	required
`*rest`	`ir.Table`	Additional table expressions	`()`
`distinct`	`bool`	Only return distinct rows	`True`

Returns:

Type	Description
`Table`	A new table containing the intersection of all input tables.

Examples:

>>> import ibis
>>> ibis.options.interactive = True
>>> t1 = ibis.memtable({"a": [1, 2]})
>>> t1
┏━━━━━━━┓
┃ a     ┃
┡━━━━━━━┩
│ int64 │
├───────┤
│     1 │
│     2 │
└───────┘
>>> t2 = ibis.memtable({"a": [2, 3]})
>>> t2
┏━━━━━━━┓
┃ a     ┃
┡━━━━━━━┩
│ int64 │
├───────┤
│     2 │
│     3 │
└───────┘
>>> ibis.intersect(t1, t2)
┏━━━━━━━┓
┃ a     ┃
┡━━━━━━━┩
│ int64 │
├───────┤
│     2 │
└───────┘

`interval(value=None, unit='s', *, years=None, quarters=None, months=None, weeks=None, days=None, hours=None, minutes=None, seconds=None, milliseconds=None, microseconds=None, nanoseconds=None)` ¶

Return an interval literal expression.

Parameters:

Name	Type	Description	Default
`value`	`int \| datetime.timedelta \| None`	Interval value.	`None`
`unit`	`str`	Unit of `value`	`'s'`
`years`	`int \| None`	Number of years	`None`
`quarters`	`int \| None`	Number of quarters	`None`
`months`	`int \| None`	Number of months	`None`
`weeks`	`int \| None`	Number of weeks	`None`
`days`	`int \| None`	Number of days	`None`
`hours`	`int \| None`	Number of hours	`None`
`minutes`	`int \| None`	Number of minutes	`None`
`seconds`	`int \| None`	Number of seconds	`None`
`milliseconds`	`int \| None`	Number of milliseconds	`None`
`microseconds`	`int \| None`	Number of microseconds	`None`
`nanoseconds`	`int \| None`	Number of nanoseconds	`None`

Returns:

Type	Description
`IntervalScalar`	An interval expression

`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

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

ibis.array
ibis.struct
ibis.map

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')
Traceback (most recent call last):
  ...
TypeError: Value 'foobar' cannot be safely coerced to int64

`map(keys, values=None)` ¶

Create a map container object.

If the keys and values are Python literals, then the output will be a MapScalar. If the keys and values are expressions (ArrayColumn), then the the output will be a MapColumn.

Parameters:

Name	Type	Description	Default
`keys`	`Iterable[Any] \| Mapping[Any, Any] \| ArrayColumn`	Keys of the map or `Mapping`. If `keys` is a `Mapping`, `values` must be `None`.	required
`values`	`Iterable[Any] \| ArrayColumn \| None`	Values of the map or `None`. If `None`, the `keys` argument must be a `Mapping`.	`None`

Returns:

Type	Description
`MapValue`	An expression representing either a map column or literal (associative array with key/value pairs of fixed types)

Examples:

Create a map literal from a dict with the type inferred

>>> import ibis
>>> ibis.options.interactive = True
>>> ibis.map(dict(a=1, b=2))
{'a': 1, 'b': 2}

Create a new map column from columns with keys and values

>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.memtable({'keys': [['a', 'b'], ['b']], 'values': [[1, 2], [3]]})
>>> t
┏━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━┓
┃ keys                 ┃ values               ┃
┡━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━┩
│ array<string>        │ array<int64>         │
├──────────────────────┼──────────────────────┤
│ ['a', 'b']           │ [1, 2]               │
│ ['b']                │ [3]                  │
└──────────────────────┴──────────────────────┘
>>> ibis.map(t.keys, t.values)
┏━━━━━━━━━━━━━━━━━━━━━━┓
┃ Map(keys, values)    ┃
┡━━━━━━━━━━━━━━━━━━━━━━┩
│ map<string, int64>   │
├──────────────────────┤
│ {'a': 1, 'b': 2}     │
│ {'b': 3}             │
└──────────────────────┘

`memtable(data, *, columns=None, schema=None, name=None)` ¶

Construct an ibis table expression from in-memory data.

Parameters:

Name	Type	Description	Default
`data`		Any data accepted by the `pandas.DataFrame` constructor or a `pyarrow.Table`. Examples of acceptable objects are a `pandas.DataFrame`, a `pyarrow.Table`, a list of dicts of non-ibis Python objects, etc. `ibis` objects, like `MapValue`, will result in an error. Do not depend on the underlying storage type (e.g., pyarrow.Table), it's subject to change across non-major releases.	required
`columns`	`Iterable[str] \| None`	Optional `Iterable` of `str` column names.	`None`
`schema`	`SupportsSchema \| None`	Optional `Schema`. The functions use `data` to infer a schema if not passed.	`None`
`name`	`str \| None`	Optional name of the table.	`None`

Returns:

Type	Description
`Table`	A table expression backed by in-memory data.

Examples:

>>> import ibis
>>> t = ibis.memtable([{"a": 1}, {"a": 2}])
>>> t
InMemoryTable
  data:
    PandasDataFrameProxy:
         a
      0  1
      1  2

>>> t = ibis.memtable([{"a": 1, "b": "foo"}, {"a": 2, "b": "baz"}])
>>> t
InMemoryTable
  data:
    PandasDataFrameProxy:
         a    b
      0  1  foo
      1  2  baz

Create a table literal without column names embedded in the data and pass columns

>>> t = ibis.memtable([(1, "foo"), (2, "baz")], columns=["a", "b"])
>>> t
InMemoryTable
  data:
    PandasDataFrameProxy:
         a    b
      0  1  foo
      1  2  baz

Create a table literal without column names embedded in the data. Ibis generates column names if none are provided.

>>> t = ibis.memtable([(1, "foo"), (2, "baz")])
>>> t
InMemoryTable
  data:
    PandasDataFrameProxy:
         col0 col1
      0     1  foo
      1     2  baz

`negate()` ¶

Negate a numeric expression.

Returns:

Type	Description
`NumericValue`	A numeric value expression

Examples:

>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.memtable({"values": [-1, 0, 1]})
>>> t.values.negate()
┏━━━━━━━━━━━━━━━━┓
┃ Negate(values) ┃
┡━━━━━━━━━━━━━━━━┩
│ int64          │
├────────────────┤
│              1 │
│              0 │
│             -1 │
└────────────────┘

`NA = null()` `module-attribute` ¶

The NULL scalar.

Examples:

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

`now()` ¶

Return an expression that will compute the current timestamp.

Returns:

Type	Description
`TimestampScalar`	An expression representing the current timestamp.

`null()` ¶

Create a NULL/NA scalar.

`or_(*predicates)` ¶

Combine multiple predicates using |.

Parameters:

Name	Type	Description	Default
`predicates`	`ir.BooleanValue`	Boolean value expressions	`()`

Returns:

Type	Description
`BooleanValue`	A new predicate that evaluates to True if any composing predicates are True. If no predicates were provided, returns False.

`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()
r0 := UnboundTable: t
  timestamp_col timestamp
  value         float64
r1 := Selection[r0]
  predicates:
    r0.timestamp_col >= $(date)
    r0.timestamp_col <= $(date)
Sum(value): Sum(r1.value)

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

`random()` ¶

Return a random floating point number in the range [0.0, 1.0).

Similar to random.random in the Python standard library.

Returns:

Type	Description
`FloatingScalar`	Random float value expression

`range_window(preceding=None, following=None, group_by=None, order_by=None)` ¶

Create a range-based window clause for use with window functions.

This RANGE window clause aggregates rows based upon differences in the value of the order-by expression.

All window frames / ranges are inclusive.

Parameters:

Name	Description	Default
`preceding`	Number of preceding rows in the window	`None`
`following`	Number of following rows in the window	`None`
`group_by`	Grouping key	`None`
`order_by`	Ordering key	`None`

Returns:

Type	Description
`Window`	A window frame

`read_csv(sources, table_name=None, **kwargs)` ¶

Lazily load a CSV or set of CSVs.

This function delegates to the read_csv method on the current default backend (DuckDB or ibis.config.default_backend).

Parameters:

Name	Type	Description	Default
`sources`	`str \| Path \| Sequence[str \| Path]`	A filesystem path or URL or list of same. Supports CSV and TSV files.	required
`table_name`	`str \| None`	A name to refer to the table. If not provided, a name will be generated.	`None`
`kwargs`	`Any`	Backend-specific keyword arguments for the file type. For the DuckDB backend used by default, please refer to: CSV/TSV: https://duckdb.org/docs/data/csv#parameters.	`{}`

Returns:

Type	Description
`ir.Table`	Table expression representing a file

Examples:

>>> import ibis
>>> t = ibis.read_csv("path/to/data.csv", table_name="my_csv_table")

`read_delta(source, table_name=None, **kwargs)` ¶

Lazily load a Delta Lake table.

Parameters:

Name	Type	Description	Default
`source`	`str \| Path`	A filesystem path or URL.	required
`table_name`	`str \| None`	A name to refer to the table. If not provided, a name will be generated.	`None`
`kwargs`	`Any`	Backend-specific keyword arguments for the file type.	`{}`

Returns:

Type	Description
`ir.Table`	Table expression representing a file

Examples:

>>> import ibis
>>> t = ibis.read_delta("path/to/delta", table_name="my_table")

`read_json(sources, table_name=None, **kwargs)` ¶

Lazily load newline-delimited JSON data.

This function delegates to the read_json method on the current default backend (DuckDB or ibis.config.default_backend).

Parameters:

Name	Type	Description	Default
`sources`	`str \| Path \| Sequence[str \| Path]`	A filesystem path or URL or list of same.	required
`table_name`	`str \| None`	A name to refer to the table. If not provided, a name will be generated.	`None`
`kwargs`	`Any`	Backend-specific keyword arguments for the file type. See https://duckdb.org/docs/extensions/json.html for details.	`{}`

Returns:

Type	Description
`ir.Table`	Table expression representing a file

Examples:

>>> import ibis
>>> ibis.options.interactive = True
>>> lines = '''
... {"a": 1, "b": "d"}
... {"a": 2, "b": null}
... {"a": null, "b": "f"}
... '''
>>> with open("/tmp/lines.json", mode="w") as f:
...     _ = f.write(lines)
>>> t = ibis.read_json("/tmp/lines.json")
>>> t
┏━━━━━━━┳━━━━━━━━┓
┃ a     ┃ b      ┃
┡━━━━━━━╇━━━━━━━━┩
│ int64 │ string │
├───────┼────────┤
│     1 │ d      │
│     2 │ NULL   │
│  NULL │ f      │
└───────┴────────┘

`read_parquet(sources, table_name=None, **kwargs)` ¶

Lazily load a parquet file or set of parquet files.

This function delegates to the read_parquet method on the current default backend (DuckDB or ibis.config.default_backend).

Parameters:

Name	Type	Description	Default
`sources`	`str \| Path \| Sequence[str \| Path]`	A filesystem path or URL or list of same.	required
`table_name`	`str \| None`	A name to refer to the table. If not provided, a name will be generated.	`None`
`kwargs`	`Any`	Backend-specific keyword arguments for the file type. For the DuckDB backend used by default, please refer to: Parquet: https://duckdb.org/docs/data/parquet	`{}`

Returns:

Type	Description
`ir.Table`	Table expression representing a file

Examples:

>>> import ibis
>>> t = ibis.read_parquet("path/to/data.parquet", table_name="my_parquet_table")

`row_number()` ¶

Return an analytic function expression for the current row number.

Returns:

Type	Description
`IntegerColumn`	A column expression enumerating rows

`schema(pairs=None, names=None, types=None)` ¶

Validate and return a Schema object.

Parameters:

Name	Type	Description	Default
`pairs`	`SupportsSchema \| None`	List or dictionary of name, type pairs. Mutually exclusive with `names` and `types` arguments.	`None`
`names`	`Iterable[str] \| None`	Field names. Mutually exclusive with `pairs`.	`None`
`types`	`Iterable[str \| dt.DataType] \| None`	Field types. Mutually exclusive with `pairs`.	`None`

Returns:

Type	Description
`Schema`	An ibis schema

Examples:

>>> from ibis import schema, Schema
>>> sc = schema([('foo', 'string'),
...              ('bar', 'int64'),
...              ('baz', 'boolean')])
>>> sc = schema(names=['foo', 'bar', 'baz'],
...             types=['string', 'int64', 'boolean'])
>>> sc = schema(dict(foo="string"))
>>> sc = schema(Schema(dict(foo="string")))  # no-op

`set_backend(backend)` ¶

Set the default Ibis backend.

Parameters:

Name	Type	Description	Default
`backend`	`str \| BaseBackend`	May be a backend name or URL, or an existing backend instance.	required

Examples:

You can pass the backend as a name:

>>> import ibis
>>> ibis.set_backend("polars")

Or as a URI

>>> ibis.set_backend("postgres://user:password@hostname:5432")

Or as an existing backend instance

>>> ibis.set_backend(ibis.duckdb.connect())

`struct(value, type=None)` ¶

Create a struct expression.

If the input expressions are all column expressions, then the output will be a StructColumn.

If the input expressions are Python literals, then the output will be a StructScalar.

Parameters:

Name	Type	Description	Default
`value`	`Iterable[tuple[str, V]] \| Mapping[str, V]`	The underlying data for literal struct value or a pairs of field names and column expressions.	required
`type`	`str \| dt.DataType \| None`	An instance of `ibis.expr.datatypes.DataType` or a string indicating the ibis type of `value`. This is only used if all of the input values are literals.	`None`

Returns:

Type	Description
`StructValue`	An expression representing a literal or column struct (compound type with fields of fixed types)

Examples:

Create a struct literal from a dict with the type inferred

>>> import ibis
>>> t = ibis.struct(dict(a=1, b='foo'))

Create a struct literal from a dict with a specified type

>>> t = ibis.struct(dict(a=1, b='foo'), type='struct<a: float, b: string>')

Specify a specific type for the struct literal

>>> t = ibis.struct(dict(a=1, b=40), type='struct<a: float, b: int32>')

Create a struct array from multiple arrays

>>> ibis.options.interactive = True
>>> t = ibis.memtable({'a': [1, 2, 3], 'b': ['foo', 'bar', 'baz']})
>>> ibis.struct([('a', t.a), ('b', t.b)])
┏━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ StructColumn()              ┃
┡━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩
│ struct<a: int64, b: string> │
├─────────────────────────────┤
│ {'a': 1, 'b': 'foo'}        │
│ {'a': 2, 'b': 'bar'}        │
│ {'a': 3, 'b': 'baz'}        │
└─────────────────────────────┘

Create a struct array from columns and literals

>>> ibis.struct([('a', t.a), ('b', 'foo')])
┏━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ StructColumn()              ┃
┡━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩
│ struct<a: int64, b: string> │
├─────────────────────────────┤
│ {'a': 1, 'b': 'foo'}        │
│ {'a': 2, 'b': 'foo'}        │
│ {'a': 3, 'b': 'foo'}        │
└─────────────────────────────┘

`table(schema=None, name=None)` ¶

Create a table literal or an abstract table without data.

Parameters:

Name	Type	Description	Default
`schema`	`SupportsSchema \| None`	A schema for the table	`None`
`name`	`str \| None`	Name for the table. One is generated if this value is `None`.	`None`

Returns:

Type	Description
`Table`	A table expression

Examples:

Create a table with no data backing it

>>> import ibis
>>> ibis.options.interactive
False
>>> t = ibis.table(schema=dict(a="int", b="string"), name="t")
>>> t
UnboundTable: t
  a int64
  b string

`time(value, *args)` ¶

Return a time literal if value is coercible to a time.

Parameters:

Name	Type	Description	Default
`value`		Time string	required
`args`		Minutes, seconds if `value` is an hour	`()`

Returns:

Type	Description
`TimeScalar`	A time expression

Examples:

>>> import ibis
>>> ibis.options.interactive = True
>>> ibis.time("00:00:00")
datetime.time(0, 0)
>>> ibis.time(12, 15, 30)
datetime.time(12, 15, 30)

`timestamp(value, *args, timezone=None)` ¶

Return a timestamp literal if value is coercible to a timestamp.

Parameters:

Name	Type	Description	Default
`value`		Timestamp string, datetime object or numeric value	required
`args`		Additional arguments if `value` is numeric	`()`
`timezone`	`str \| None`	Timezone name	`None`

Returns:

Type	Description
`TimestampScalar`	A timestamp expression

Examples:

>>> import ibis
>>> ibis.options.interactive = True
>>> ibis.timestamp("2021-01-01 00:00:00")
Timestamp('2021-01-01 00:00:00')

`trailing_range_window(preceding, order_by, group_by=None)` ¶

Create a trailing range window for use with window functions.

Parameters:

Name	Description	Default
`preceding`	A value expression	required
`order_by`	Ordering key	required
`group_by`	Grouping key	`None`

Returns:

Type	Description
`Window`	A window frame

`trailing_window(preceding, group_by=None, order_by=None)` ¶

Create a trailing window for use with window functions.

Parameters:

Name	Description	Default
`preceding`	The number of preceding rows	required
`group_by`	Grouping key	`None`
`order_by`	Ordering key	`None`

Returns:

Type	Description
`Window`	A window frame

`union(table, *rest, distinct=False)` ¶

Compute the set union of multiple table expressions.

The input tables must have identical schemas.

Parameters:

Name	Type	Description	Default
`table`	`ir.Table`	A table expression	required
`*rest`	`ir.Table`	Additional table expressions	`()`
`distinct`	`bool`	Only return distinct rows	`False`

Returns:

Type	Description
`Table`	A new table containing the union of all input tables.

Examples:

>>> import ibis
>>> ibis.options.interactive = True
>>> t1 = ibis.memtable({"a": [1, 2]})
>>> t1
┏━━━━━━━┓
┃ a     ┃
┡━━━━━━━┩
│ int64 │
├───────┤
│     1 │
│     2 │
└───────┘
>>> t2 = ibis.memtable({"a": [2, 3]})
>>> t2
┏━━━━━━━┓
┃ a     ┃
┡━━━━━━━┩
│ int64 │
├───────┤
│     2 │
│     3 │
└───────┘
>>> ibis.union(t1, t2)  # union all by default
┏━━━━━━━┓
┃ a     ┃
┡━━━━━━━┩
│ int64 │
├───────┤
│     1 │
│     2 │
│     2 │
│     3 │
└───────┘
>>> ibis.union(t1, t2, distinct=True).order_by("a")
┏━━━━━━━┓
┃ a     ┃
┡━━━━━━━┩
│ int64 │
├───────┤
│     1 │
│     2 │
│     3 │
└───────┘

`where = _deferred(ir.BooleanValue.ifelse)` `module-attribute` ¶

Construct a ternary conditional expression.

Parameters:

Name	Type	Description	Default
`true_expr`	`ir.Value`	Expression to return if `self` evaluates to `True`	required
`false_expr`	`ir.Value`	Expression to return if `self` evaluates to `False` or `NULL`	required

Returns:

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

Examples:

>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.memtable({"is_person": [True, False, True, None]})
>>> ibis.where(t.is_person, "yes", "no")
┏━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ Where(is_person, 'yes', 'no') ┃
┡━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩
│ string                        │
├───────────────────────────────┤
│ yes                           │
│ no                            │
│ yes                           │
│ no                            │
└───────────────────────────────┘

`window(preceding=None, following=None, order_by=None, group_by=None, *, rows=None, range=None, between=None)` ¶

Create a window clause for use with window functions.

The ROWS window clause includes peer rows based on differences in row number whereas RANGE includes rows based on the differences in row value of a single order_by expression.

All window frame bounds are inclusive.

Parameters:

Name	Description	Default
`preceding`	Number of preceding rows in the window	`None`
`following`	Number of following rows in the window	`None`
`group_by`	Grouping key	`None`
`order_by`	Ordering key	`None`
`rows`	Whether to use the `ROWS` window clause	`None`
`range`	Whether to use the `RANGE` window clause	`None`
`between`	Automatically infer the window kind based on the boundaries	`None`

Returns:

Type	Description
`Window`	A window frame

Last update: August 1, 2023

Top-level APIs¶

and_(*predicates) ¶

array(values, type=None) ¶

asc(expr) ¶

case() ¶

coalesce(*args) ¶

cumulative_window(group_by=None, order_by=None) ¶

date(value, *args) ¶

deferred = Deferred() module-attribute ¶

desc(expr) ¶

difference(table, *rest, distinct=True) ¶

dtype = dt.dtype module-attribute ¶

get_backend(expr=None) ¶

greatest(*args) ¶

ifelse = _deferred(ir.BooleanValue.ifelse) module-attribute ¶

intersect(table, *rest, distinct=True) ¶

interval(value=None, unit='s', *, years=None, quarters=None, months=None, weeks=None, days=None, hours=None, minutes=None, seconds=None, milliseconds=None, microseconds=None, nanoseconds=None) ¶

least(*args) ¶

literal(value, type=None) ¶

map(keys, values=None) ¶

memtable(data, *, columns=None, schema=None, name=None) ¶

negate() ¶

NA = null() module-attribute ¶

now() ¶

null() ¶

or_(*predicates) ¶

param(type) ¶

show_sql(expr, dialect=None, file=None) ¶

to_sql(expr, dialect=None, **kwargs) ¶

random() ¶

range_window(preceding=None, following=None, group_by=None, order_by=None) ¶

read_csv(sources, table_name=None, **kwargs) ¶

read_delta(source, table_name=None, **kwargs) ¶

read_json(sources, table_name=None, **kwargs) ¶

read_parquet(sources, table_name=None, **kwargs) ¶

row_number() ¶

schema(pairs=None, names=None, types=None) ¶

set_backend(backend) ¶

struct(value, type=None) ¶

table(schema=None, name=None) ¶

time(value, *args) ¶

timestamp(value, *args, timezone=None) ¶

trailing_range_window(preceding, order_by, group_by=None) ¶

trailing_window(preceding, group_by=None, order_by=None) ¶

union(table, *rest, distinct=False) ¶

where = _deferred(ir.BooleanValue.ifelse) module-attribute ¶

window(preceding=None, following=None, order_by=None, group_by=None, *, rows=None, range=None, between=None) ¶

`and_(*predicates)` ¶

`array(values, type=None)` ¶

`asc(expr)` ¶

`case()` ¶

`coalesce(*args)` ¶

`cumulative_window(group_by=None, order_by=None)` ¶

`date(value, *args)` ¶

`deferred = Deferred()` `module-attribute` ¶

`desc(expr)` ¶

`difference(table, *rest, distinct=True)` ¶

`dtype = dt.dtype` `module-attribute` ¶

`get_backend(expr=None)` ¶

`greatest(*args)` ¶

`ifelse = _deferred(ir.BooleanValue.ifelse)` `module-attribute` ¶

`intersect(table, *rest, distinct=True)` ¶

`interval(value=None, unit='s', *, years=None, quarters=None, months=None, weeks=None, days=None, hours=None, minutes=None, seconds=None, milliseconds=None, microseconds=None, nanoseconds=None)` ¶

`least(*args)` ¶

`literal(value, type=None)` ¶

`map(keys, values=None)` ¶

`memtable(data, *, columns=None, schema=None, name=None)` ¶

`negate()` ¶

`NA = null()` `module-attribute` ¶

`now()` ¶

`null()` ¶

`or_(*predicates)` ¶

`param(type)` ¶

`show_sql(expr, dialect=None, file=None)` ¶

`to_sql(expr, dialect=None, **kwargs)` ¶

`random()` ¶

`range_window(preceding=None, following=None, group_by=None, order_by=None)` ¶

`read_csv(sources, table_name=None, **kwargs)` ¶

`read_delta(source, table_name=None, **kwargs)` ¶

`read_json(sources, table_name=None, **kwargs)` ¶

`read_parquet(sources, table_name=None, **kwargs)` ¶

`row_number()` ¶

`schema(pairs=None, names=None, types=None)` ¶

`set_backend(backend)` ¶

`struct(value, type=None)` ¶

`table(schema=None, name=None)` ¶

`time(value, *args)` ¶

`timestamp(value, *args, timezone=None)` ¶

`trailing_range_window(preceding, order_by, group_by=None)` ¶

`trailing_window(preceding, group_by=None, order_by=None)` ¶

`union(table, *rest, distinct=False)` ¶

`where = _deferred(ir.BooleanValue.ifelse)` `module-attribute` ¶

`window(preceding=None, following=None, order_by=None, group_by=None, *, rows=None, range=None, between=None)` ¶