Scalars and columns of any element type.
Value
Value(self, arg)
Base class for a data generating expression having a known type.
Methods
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.
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.
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 self
.
typeof
Return the string name of the datatype of self.
asc
asc()
Sort an expression ascending.
between
between(lower, upper)
Check if this expression is between lower
and upper
, inclusive.
Parameters
lower
Value
Lower bound, inclusive
required
upper
Value
Upper bound, inclusive
required
Returns
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()
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
SimpleCaseBuilder
A case builder
Examples
>>> import ibis
>>> ibis.options.interactive = True
>>> x = ibis.examples.penguins.fetch().head(5 )["sex" ]
>>> x
┏━━━━━━━━┓
┃ sex ┃
┡━━━━━━━━┩
│ string │
├────────┤
│ male │
│ female │
│ female │
│ NULL │
│ female │
└────────┘
>>> x.case().when("male" , "M" ).when("female" , "F" ).else_("U" ).end()
┏━━━━━━━━━━━━━━━━━━━━━━┓
┃ SimpleCase(sex, 'U') ┃
┡━━━━━━━━━━━━━━━━━━━━━━┩
│ string │
├──────────────────────┤
│ M │
│ F │
│ F │
│ U │
│ F │
└──────────────────────┘
Cases not given result in the ELSE case
>>> x.case().when("male" , "M" ).else_("OTHER" ).end()
┏━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ SimpleCase(sex, 'OTHER') ┃
┡━━━━━━━━━━━━━━━━━━━━━━━━━━┩
│ string │
├──────────────────────────┤
│ M │
│ OTHER │
│ OTHER │
│ OTHER │
│ OTHER │
└──────────────────────────┘
If you don’t supply an ELSE, then NULL is used
>>> x.case().when("male" , "M" ).end()
┏━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ SimpleCase(sex, Cast(None, string)) ┃
┡━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩
│ string │
├─────────────────────────────────────┤
│ M │
│ NULL │
│ NULL │
│ NULL │
│ NULL │
└─────────────────────────────────────┘
cases
cases(case_result_pairs, default=None)
Create a case expression in one shot.
Parameters
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
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(target_type)
Cast expression to indicated data type.
Similar to pandas.Series.astype
.
Parameters
target_type
Any
Type to cast to. Anything accepted by ibis.dtype()
required
Examples
>>> import ibis
>>> ibis.options.interactive = True
>>> x = ibis.examples.penguins.fetch()["bill_depth_mm" ]
>>> x
┏━━━━━━━━━━━━━━━┓
┃ bill_depth_mm ┃
┡━━━━━━━━━━━━━━━┩
│ float64 │
├───────────────┤
│ 18.7 │
│ 17.4 │
│ 18.0 │
│ NULL │
│ 19.3 │
│ 20.6 │
│ 17.8 │
│ 19.6 │
│ 18.1 │
│ 20.2 │
│ … │
└───────────────┘
python’s built-in types can be used
┏━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ Cast(bill_depth_mm, int64) ┃
┡━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩
│ int64 │
├────────────────────────────┤
│ 19 │
│ 17 │
│ 18 │
│ NULL │
│ 19 │
│ 21 │
│ 18 │
│ 20 │
│ 18 │
│ 20 │
│ … │
└────────────────────────────┘
or string names
┏━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ Cast(bill_depth_mm, uint16) ┃
┡━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩
│ uint16 │
├─────────────────────────────┤
│ 19 │
│ 17 │
│ 18 │
│ NULL │
│ 19 │
│ 21 │
│ 18 │
│ 20 │
│ 18 │
│ 20 │
│ … │
└─────────────────────────────┘
If you make an illegal cast, you won’t know until the backend actually executes it. Consider .try_cast()
.
>>> ibis.literal("a string" ).cast("int64" )
ConversionException: Conversion Error: Could not convert string 'a string' to INT64
coalesce
coalesce(*args)
Return the first non-null value from args
.
Parameters
args
Value
Arguments from which to choose the first non-null value
()
Returns
Value
Coalesced expression
Examples
>>> import ibis
>>> ibis.coalesce(None , 4 , 5 ).name("x" )
collect
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
where
ir
.BooleanValue
| None
Filter to apply before aggregation
None
Returns
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 │
└────────┴───────┘
>>> type (t.value.collect())
ibis.expr.types.arrays.ArrayScalar
Collect elements per group
>>> t.group_by("key" ).agg(v= lambda t: t.value.collect()).order_by("key" )
┏━━━━━━━━┳━━━━━━━━━━━━━━━━┓
┃ 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 )).order_by("key" )
┏━━━━━━━━┳━━━━━━━━━━━━━━┓
┃ key ┃ v ┃
┡━━━━━━━━╇━━━━━━━━━━━━━━┩
│ string │ array<int64> │
├────────┼──────────────┤
│ a │ [ 2 , 3 ] │
│ b │ [ 4 , 5 ] │
└────────┴──────────────┘
desc
desc()
Sort an expression descending.
fillna
fillna(fill_value)
Replace any null values with the indicated fill value.
Parameters
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
Value
self
filled with fill_value
where it is NA
group_concat
group_concat(sep=',', where=None)
Concatenate values using the indicated separator to produce a string.
Parameters
sep
str
Separator will be used to join strings
','
where
ir
.BooleanValue
| None
Filter expression
None
Returns
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 │
│ NULL │ NULL │
│ 36.7 │ 19.3 │
└────────────────┴───────────────┘
>>> t.bill_length_mm.group_concat()
>>> t.bill_length_mm.group_concat(sep= ": " )
>>> t.bill_length_mm.group_concat(sep= ": " , where= t.bill_depth_mm > 18 )
hash
hash()
Compute an integer hash value.
Returns
IntegerValue
The hash value of self
Examples
>>> import ibis
>>> ibis.options.interactive = True
>>> ibis.literal("hello" ).hash ()
identical_to
identical_to(other)
Return whether this expression is identical to other.
Corresponds to IS NOT DISTINCT FROM
in SQL.
Parameters
other
Value
Expression to compare to
required
Returns
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)
isin
isin(values)
Check whether this expression’s values are in values
.
NULL
values are propagated in the output. See examples for details.
Returns
BooleanValue
Expression indicating membership
Examples
>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.memtable({"a" : [1 , 2 , 3 ], "b" : [2 , 3 , 4 ]})
>>> t
┏━━━━━━━┳━━━━━━━┓
┃ a ┃ b ┃
┡━━━━━━━╇━━━━━━━┩
│ int64 │ int64 │
├───────┼───────┤
│ 1 │ 2 │
│ 2 │ 3 │
│ 3 │ 4 │
└───────┴───────┘
Check against a literal sequence of values
┏━━━━━━━━━━━━━┓
┃ InValues(a) ┃
┡━━━━━━━━━━━━━┩
│ boolean │
├─────────────┤
│ True │
│ True │
│ False │
└─────────────┘
Check against a derived expression
┏━━━━━━━━━━━━━━━┓
┃ InSubquery(a) ┃
┡━━━━━━━━━━━━━━━┩
│ boolean │
├───────────────┤
│ False │
│ False │
│ True │
└───────────────┘
Check against a column from a different table
>>> t2 = ibis.memtable({"x" : [99 , 2 , 99 ]})
>>> t.a.isin(t2.x)
┏━━━━━━━━━━━━━━━┓
┃ InSubquery(a) ┃
┡━━━━━━━━━━━━━━━┩
│ boolean │
├───────────────┤
│ False │
│ True │
│ False │
└───────────────┘
NULL
behavior
>>> t = ibis.memtable({"x" : [1 , 2 ]})
>>> t.x.isin([1 , None ])
┏━━━━━━━━━━━━━┓
┃ InValues(x) ┃
┡━━━━━━━━━━━━━┩
│ boolean │
├─────────────┤
│ True │
│ NULL │
└─────────────┘
>>> t = ibis.memtable({"x" : [1 , None , 2 ]})
>>> t.x.isin([1 ])
┏━━━━━━━━━━━━━┓
┃ InValues(x) ┃
┡━━━━━━━━━━━━━┩
│ boolean │
├─────────────┤
│ True │
│ NULL │
│ False │
└─────────────┘
┏━━━━━━━━━━━━━┓
┃ InValues(x) ┃
┡━━━━━━━━━━━━━┩
│ boolean │
├─────────────┤
│ False │
│ NULL │
│ False │
└─────────────┘
isnull
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 │
│ NULL │
│ 19.3 │
└───────────────┘
>>> t.bill_depth_mm.isnull()
┏━━━━━━━━━━━━━━━━━━━━━━━┓
┃ IsNull(bill_depth_mm) ┃
┡━━━━━━━━━━━━━━━━━━━━━━━┩
│ boolean │
├───────────────────────┤
│ False │
│ False │
│ False │
│ True │
│ False │
└───────────────────────┘
name
name(name)
Rename an expression to name
.
Parameters
name
The new name of the expression
required
Returns
Value
self
with name name
Examples
>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.memtable({"a" : [1 , 2 ]}, name= "t" )
>>> t.a
┏━━━━━━━┓
┃ a ┃
┡━━━━━━━┩
│ int64 │
├───────┤
│ 1 │
│ 2 │
└───────┘
┏━━━━━━━┓
┃ b ┃
┡━━━━━━━┩
│ int64 │
├───────┤
│ 1 │
│ 2 │
└───────┘
notin
notin(values)
Check whether this expression’s values are not in values
.
Opposite of Value.isin()
.
Parameters
values
Value | Sequence [Value ]
Values or expression to check for lack of membership
required
Returns
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 │
│ NULL │
│ 19.3 │
└───────────────┘
>>> t.bill_depth_mm.notin([18.7 , 18.1 ])
┏━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ Not(InValues(bill_depth_mm)) ┃
┡━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩
│ boolean │
├──────────────────────────────┤
│ False │
│ True │
│ True │
│ NULL │
│ True │
└──────────────────────────────┘
notnull
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 │
│ NULL │
│ 19.3 │
└───────────────┘
>>> t.bill_depth_mm.notnull()
┏━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ NotNull(bill_depth_mm) ┃
┡━━━━━━━━━━━━━━━━━━━━━━━━┩
│ boolean │
├────────────────────────┤
│ True │
│ True │
│ True │
│ False │
│ True │
└────────────────────────┘
nullif
nullif(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.
Equivalent to (self == null_if_expr).ifelse(ibis.null(), self)
.
Parameters
null_if_expr
Value
Expression indicating what values should be NULL
required
Examples
>>> import ibis
>>> ibis.options.interactive = True
>>> vals = ibis.examples.penguins.fetch().head(5 ).sex
>>> vals
┏━━━━━━━━┓
┃ sex ┃
┡━━━━━━━━┩
│ string │
├────────┤
│ male │
│ female │
│ female │
│ NULL │
│ female │
└────────┘
┏━━━━━━━━━━━━━━━━━━━━━┓
┃ NullIf(sex, 'male') ┃
┡━━━━━━━━━━━━━━━━━━━━━┩
│ string │
├─────────────────────┤
│ NULL │
│ female │
│ female │
│ NULL │
│ female │
└─────────────────────┘
over
over(window=None, *, rows=None, range=None, group_by=None, order_by=None)
Construct a window expression.
Parameters
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
Value
A window function expression
substitute
substitute(value, replacement=None, else_=None)
Replace values given in values
with replacement
.
This is similar to the pandas replace
method.
Parameters
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
Examples
>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.examples.penguins.fetch()
>>> t.island.value_counts().order_by("island" )
┏━━━━━━━━━━━┳━━━━━━━━━━━━━━┓
┃ island ┃ island_count ┃
┡━━━━━━━━━━━╇━━━━━━━━━━━━━━┩
│ string │ int64 │
├───────────┼──────────────┤
│ Biscoe │ 168 │
│ Dream │ 124 │
│ Torgersen │ 52 │
└───────────┴──────────────┘
>>> t.island.substitute({"Torgersen" : "torg" , "Biscoe" : "bisc" }).name(
... "island"
... ).value_counts().order_by("island" )
┏━━━━━━━━┳━━━━━━━━━━━━━━┓
┃ island ┃ island_count ┃
┡━━━━━━━━╇━━━━━━━━━━━━━━┩
│ string │ int64 │
├────────┼──────────────┤
│ Dream │ 124 │
│ bisc │ 168 │
│ torg │ 52 │
└────────┴──────────────┘
to_pandas
to_pandas(**kwargs)
Convert a column expression to a pandas Series or scalar object.
Parameters
kwargs
Same as keyword arguments to execute
{}
Examples
>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.examples.penguins.fetch().limit(5 )
>>> t.to_pandas()
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(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
target_type
Any
Type to try cast to. Anything accepted by ibis.dtype()
required
Examples
>>> import ibis
>>> from ibis import _
>>> ibis.options.interactive = True
>>> t = ibis.memtable({"numbers" : [1 , 2 , 3 , 4 ], "strings" : ["1.0" , "2" , "hello" , "world" ]})
>>> t
┏━━━━━━━━━┳━━━━━━━━━┓
┃ numbers ┃ strings ┃
┡━━━━━━━━━╇━━━━━━━━━┩
│ int64 │ string │
├─────────┼─────────┤
│ 1 │ 1.0 │
│ 2 │ 2 │
│ 3 │ hello │
│ 4 │ world │
└─────────┴─────────┘
>>> t = t.mutate(numbers_to_strings= _.numbers.try_cast("string" ))
>>> t = t.mutate(strings_to_numbers= _.strings.try_cast("int" ))
>>> t
┏━━━━━━━━━┳━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━┓
┃ numbers ┃ strings ┃ numbers_to_strings ┃ strings_to_numbers ┃
┡━━━━━━━━━╇━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━┩
│ int64 │ string │ string │ int64 │
├─────────┼─────────┼────────────────────┼────────────────────┤
│ 1 │ 1.0 │ 1 │ 1 │
│ 2 │ 2 │ 2 │ 2 │
│ 3 │ hello │ 3 │ NULL │
│ 4 │ world │ 4 │ NULL │
└─────────┴─────────┴────────────────────┴────────────────────┘
type
type()
Return the DataType of self
.
typeof
typeof()
Return the string name of the datatype of self.
The values of the returned strings are necessarily backend dependent. e.g. duckdb may say “DOUBLE”, while sqlite may say “real”.
Returns
StringValue
A string indicating the type of the value
Examples
>>> import ibis
>>> ibis.options.interactive = True
>>> vals = ibis.examples.penguins.fetch().head(5 ).bill_length_mm
>>> vals
┏━━━━━━━━━━━━━━━━┓
┃ bill_length_mm ┃
┡━━━━━━━━━━━━━━━━┩
│ float64 │
├────────────────┤
│ 39.1 │
│ 39.5 │
│ 40.3 │
│ NULL │
│ 36.7 │
└────────────────┘
┏━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ TypeOf(bill_length_mm) ┃
┡━━━━━━━━━━━━━━━━━━━━━━━━┩
│ string │
├────────────────────────┤
│ DOUBLE │
│ DOUBLE │
│ DOUBLE │
│ DOUBLE │
│ DOUBLE │
└────────────────────────┘
Different backends have different names for their native types
>>> ibis.duckdb.connect ().execute(ibis.literal(5.4 ).typeof())
>>> ibis.sqlite.connect ().execute(ibis.literal(5.4 ).typeof())
Column
Column(self, arg)
Methods
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
.
as_scalar
Inform ibis that the expression should be treated as a scalar.
as_table
Promote the expression to a Table .
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.
median
Return the median of the column.
min
Return the minimum of a column.
mode
Return the mode of a column.
nth
Return the n
th value (0-indexed) over a window.
ntile
Return the integer number of a partitioning of the column values.
nunique
Compute the number of distinct rows in an expression.
percent_rank
Return the relative rank of the values in the column.
preview
Print a subset as a single-column Rich Table.
quantile
Return value at the given quantile.
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_nunique
approx_nunique(where=None)
Return the approximate number of distinct elements in self
.
Whether the result is an approximation depends on the backend.
Parameters
where
ir
.BooleanValue
| None
Filter in values when where
is True
None
Returns
Scalar
An approximate count of the distinct elements of self
Examples
>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.examples.penguins.fetch()
>>> t.body_mass_g.approx_nunique()
>>> t.body_mass_g.approx_nunique(where= t.species == "Adelie" )
arbitrary
arbitrary(where=None, how=None)
Select an arbitrary value in a column.
Returns an arbitrary (nondeterministic, backend-specific) value from the column. The value will be non-NULL, except if the column is empty or all values are NULL.
Parameters
where
ir
.BooleanValue
| None
A filter expression
None
how
Any
DEPRECATED
None
argmax
argmax(key, where=None)
Return the value of self
that maximizes key
.
Parameters
key
ir
.Value
Key to use for max
computation.
required
where
ir
.BooleanValue
| None
Keep values when where
is True
None
Returns
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)
>>> t.species.argmax(t.body_mass_g, where= t.island == "Dream" )
argmin
argmin(key, where=None)
Return the value of self
that minimizes key
.
Parameters
key
ir
.Value
Key to use for min
computation.
required
where
ir
.BooleanValue
| None
Keep values when where
is True
None
Returns
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)
>>> t.species.argmin(t.body_mass_g, where= t.island == "Biscoe" )
as_scalar
as_scalar()
Inform ibis that the expression should be treated as a scalar.
Creates a scalar subquery from the column expression. Since ibis cannot be sure that the column expression contains only one value, the column expression is wrapped in a scalar subquery and treated as a scalar.
Note that the execution of the scalar subquery will fail if the column expression contains more than one value.
Examples
>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.examples.penguins.fetch()
>>> heavy_gentoo = t.filter (t.species == "Gentoo" , t.body_mass_g > 6200 )
>>> from_that_island = t.filter (t.island == heavy_gentoo.island.as_scalar())
>>> from_that_island.species.value_counts().order_by("species" )
┏━━━━━━━━━┳━━━━━━━━━━━━━━━┓
┃ species ┃ species_count ┃
┡━━━━━━━━━╇━━━━━━━━━━━━━━━┩
│ string │ int64 │
├─────────┼───────────────┤
│ Adelie │ 44 │
│ Gentoo │ 124 │
└─────────┴───────────────┘
as_table
as_table()
Promote the expression to a Table .
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)
count
count(where=None)
Compute the number of rows in an expression.
Parameters
where
ir
.BooleanValue
| None
Filter expression
None
Returns
IntegerScalar
Number of elements in an expression
cume_dist
cume_dist()
Return the cumulative distribution over a window.
cummax
cummax(where=None, group_by=None, order_by=None)
Return the cumulative max over a window.
cummin
cummin(where=None, group_by=None, order_by=None)
Return the cumulative min over a window.
dense_rank
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()
.
Examples
>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.memtable({"values" : [1 , 2 , 1 , 2 , 3 , 2 ]})
>>> t.mutate(rank= t.values.dense_rank())
┏━━━━━━━━┳━━━━━━━┓
┃ values ┃ rank ┃
┡━━━━━━━━╇━━━━━━━┩
│ int64 │ int64 │
├────────┼───────┤
│ 1 │ 0 │
│ 1 │ 0 │
│ 2 │ 1 │
│ 2 │ 1 │
│ 2 │ 1 │
│ 3 │ 2 │
└────────┴───────┘
first
first(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(where= t.chars != "a" )
lag
lag(offset=None, default=None)
Return the row located at offset
rows before the current row.
Parameters
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(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(where= t.chars != "d" )
lead
lead(offset=None, default=None)
Return the row located at offset
rows after the current row.
Parameters
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(where=None)
Return the maximum of a column.
Parameters
where
ir
.BooleanValue
| None
Filter in values when where
is True
None
Returns
Scalar
The maximum value in self
Examples
>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.examples.penguins.fetch()
>>> t.body_mass_g.max ()
>>> t.body_mass_g.max (where= t.species == "Chinstrap" )
min
min(where=None)
Return the minimum of a column.
Parameters
where
ir
.BooleanValue
| None
Filter in values when where
is True
None
Returns
Scalar
The minimum value in self
Examples
>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.examples.penguins.fetch()
>>> t.body_mass_g.min ()
>>> t.body_mass_g.min (where= t.species == "Adelie" )
mode
mode(where=None)
Return the mode of a column.
Parameters
where
ir
.BooleanValue
| None
Filter in values when where
is True
None
Examples
>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.examples.penguins.fetch()
>>> t.body_mass_g.mode()
>>> t.body_mass_g.mode(where= (t.species == "Gentoo" ) & (t.sex == "male" ))
nth
nth(n)
Return the n
th 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
n
int | ir
.IntegerValue
Desired rank value
required
Returns
Column
The nth value over a window
ntile
ntile(buckets)
Return the integer number of a partitioning of the column values.
Parameters
buckets
int | ir
.IntegerValue
Number of buckets to partition into
required
nunique
nunique(where=None)
Compute the number of distinct rows in an expression.
Parameters
where
ir
.BooleanValue
| None
Filter expression
None
Returns
IntegerScalar
Number of distinct elements in an expression
Examples
>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.examples.penguins.fetch()
>>> t.body_mass_g.nunique()
>>> t.body_mass_g.nunique(where= t.species == "Adelie" )
percent_rank
percent_rank()
Return the relative rank of the values in the column.
preview
preview(max_rows=None, max_length=None, max_string=None, max_depth=None, console_width=None)
Print a subset as a single-column Rich Table.
This is an explicit version of what you get when you inspect this object in interactive mode, except with this version you can pass formatting options. The options are the same as those exposed in ibis.options.interactive
.
Parameters
max_rows
int | None
Maximum number of rows to display
None
max_length
int | None
Maximum length for pretty-printed arrays and maps.
None
max_string
int | None
Maximum length for pretty-printed strings.
None
max_depth
int | None
Maximum depth for nested data types.
None
console_width
int | float | None
Width of the console in characters. If not specified, the width will be inferred from the console.
None
Examples
>>> import ibis
>>> t = ibis.examples.penguins.fetch()
>>> t.island.preview(max_rows= 3 , max_string= 5 )
┏━━━━━━━━┓
┃ island ┃
┡━━━━━━━━┩
│ stri… │
├────────┤
│ Torg… │
│ Torg… │
│ Torg… │
│ … │
└────────┘
quantile
quantile(quantile, where=None)
Return value at the given quantile.
The output of this method is a continuous quantile if the input is numeric, otherwise the output is a discrete quantile.
Parameters
quantile
float | ir
.NumericValue
| Sequence [ir
.NumericValue
| float ]
0 <= quantile <= 1
, or an array of such values indicating the quantile or quantiles to compute
required
where
ir
.BooleanValue
| None
Boolean filter for input values
None
Examples
>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.examples.penguins.fetch()
Compute the 99th percentile of bill_depth
>>> t.bill_depth_mm.quantile(0.99 )
>>> t.group_by(t.species).agg(p99_bill_depth= t.bill_depth_mm.quantile(0.99 )).order_by(
... ibis.desc("p99_bill_depth" )
... )
┏━━━━━━━━━━━┳━━━━━━━━━━━━━━━━┓
┃ species ┃ p99_bill_depth ┃
┡━━━━━━━━━━━╇━━━━━━━━━━━━━━━━┩
│ string │ float64 │
├───────────┼────────────────┤
│ Adelie │ 21.200 │
│ Chinstrap │ 20.733 │
│ Gentoo │ 17.256 │
└───────────┴────────────────┘
In addition to numeric types, any orderable non-numeric types such as strings and dates work with quantile
.
Let’s compute the 99th percentile of the species
column
>>> t.group_by(t.island).agg(p99_species= t.species.quantile(0.99 )).order_by(
... ibis.desc("p99_species" )
... )
┏━━━━━━━━━━━┳━━━━━━━━━━━━━┓
┃ island ┃ p99_species ┃
┡━━━━━━━━━━━╇━━━━━━━━━━━━━┩
│ string │ string │
├───────────┼─────────────┤
│ Biscoe │ Gentoo │
│ Dream │ Chinstrap │
│ Torgersen │ Adelie │
└───────────┴─────────────┘
rank
rank()
Compute position of first element within each equal-value group in sorted order.
Equivalent to SQL’s RANK()
window function.
Examples
>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.memtable({"values" : [1 , 2 , 1 , 2 , 3 , 2 ]})
>>> t.mutate(rank= t.values.rank())
┏━━━━━━━━┳━━━━━━━┓
┃ values ┃ rank ┃
┡━━━━━━━━╇━━━━━━━┩
│ int64 │ int64 │
├────────┼───────┤
│ 1 │ 0 │
│ 1 │ 0 │
│ 2 │ 2 │
│ 2 │ 2 │
│ 2 │ 2 │
│ 3 │ 5 │
└────────┴───────┘
topk
topk(k, by=None)
Return a “top k” expression.
Parameters
k
int
Return this number of rows
required
by
ir
.Value
| None
An expression. Defaults to count
.
None
value_counts
value_counts()
Compute a frequency table.
Returns
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().order_by("chars" )
┏━━━━━━━━┳━━━━━━━━━━━━━┓
┃ chars ┃ chars_count ┃
┡━━━━━━━━╇━━━━━━━━━━━━━┩
│ string │ int64 │
├────────┼─────────────┤
│ a │ 2 │
│ b │ 1 │
│ c │ 1 │
│ d │ 3 │
└────────┴─────────────┘
Scalar
Scalar(self, arg)
Methods
as_scalar
Inform ibis that the expression should be treated as a scalar.
as_table
Promote the scalar expression to a table.
as_scalar
as_scalar()
Inform ibis that the expression should be treated as a scalar.
If the expression is a literal, it will be returned as is. If it depends on a table, it will be turned to a scalar subquery.
Returns
Scalar
A scalar subquery or a literal
Examples
>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.examples.penguins.fetch()
>>> max_gentoo_weight = t.filter (t.species == "Gentoo" ).body_mass_g.max ()
>>> light_penguins = t.filter (t.body_mass_g < max_gentoo_weight / 2 )
>>> light_penguins.species.value_counts().order_by("species" )
┏━━━━━━━━━━━┳━━━━━━━━━━━━━━━┓
┃ species ┃ species_count ┃
┡━━━━━━━━━━━╇━━━━━━━━━━━━━━━┩
│ string │ int64 │
├───────────┼───────────────┤
│ Adelie │ 15 │
│ Chinstrap │ 2 │
└───────────┴───────────────┘
as_table
as_table()
Promote the scalar expression to a table.
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)
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)
literal
ibis.literal(value, type=None)
Create a scalar expression from a Python value.
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
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
Scalar
An expression representing a literal value
Examples
Construct an integer literal
>>> import ibis
>>> x = ibis.literal(42 )
>>> x.type ()
Construct a float64
literal from an int
>>> y = ibis.literal(42 , type = "double" )
>>> y.type ()
Ibis checks for invalid types
>>> ibis.literal("foobar" , type = "int64" )
TypeError: Unable to normalize 'foobar' to Int64(nullable=True)
param
ibis.param(type)
Create a deferred parameter of a given type.
Parameters
type
dt
.DataType
The type of the unbound parameter, e.g., double, int64, date, etc.
required
Returns
Scalar
A scalar expression backend by a parameter
Examples
>>> from datetime import date
>>> import ibis
>>> start = ibis.param("date" )
>>> t = ibis.memtable(
... {
... "date_col" : [date(2013 , 1 , 1 ), date(2013 , 1 , 2 ), date(2013 , 1 , 3 )],
... "value" : [1.0 , 2.0 , 3.0 ],
... },
... )
>>> expr = t.filter (t.date_col >= start).value.sum ()
>>> expr.execute(params= {start: date(2013 , 1 , 1 )})
>>> expr.execute(params= {start: date(2013 , 1 , 2 )})
>>> expr.execute(params= {start: date(2013 , 1 , 3 )})
NA
ibis.expr.api.NA
The NULL scalar.
Examples
>>> import ibis
>>> my_null = ibis.NA
>>> my_null.isnull()
null
ibis.null()
Create a NULL/NA scalar.
coalesce
ibis.coalesce(*args)
Return the first non-null value from args
.
Parameters
args
Any
Arguments from which to choose the first non-null value
()
Returns
Value
Coalesced expression
Examples
>>> import ibis
>>> ibis.options.interactive = True
>>> ibis.coalesce(None , 4 , 5 )
least
ibis.least(*args)
Compute the smallest value among the supplied arguments.
Parameters
args
Any
Arguments to choose from
()
Returns
Value
Minimum of the passed arguments
Examples
>>> import ibis
>>> ibis.options.interactive = True
>>> ibis.least(None , 4 , 5 )
greatest
ibis.greatest(*args)
Compute the largest value among the supplied arguments.
Parameters
args
Any
Arguments to choose from
()
Returns
Value
Maximum of the passed arguments
Examples
>>> import ibis
>>> ibis.options.interactive = True
>>> ibis.greatest(None , 4 , 5 )
asc
ibis.asc(expr)
Create a ascending sort key from asc
or column name.
Parameters
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
ir
.ValueExpr
An expression
desc
ibis.desc(expr)
Create a descending sort key from expr
or column name.
Parameters
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
ir
.ValueExpr
An expression
ifelse
ibis.ifelse(condition, true_expr, false_expr)
Construct a ternary conditional expression.
Parameters
condition
Any
A boolean expression
required
true_expr
Any
Expression to return if condition
evaluates to True
required
false_expr
Any
Expression to return if condition
evaluates to False
or NULL
required
Returns
ir
.Value
The value of true_expr
if condition
is True
else false_expr
Examples
>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.memtable({"condition" : [True , False , True , None ]})
>>> ibis.ifelse(t.condition, "yes" , "no" )
┏━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ IfElse(condition, 'yes', 'no') ┃
┡━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩
│ string │
├────────────────────────────────┤
│ yes │
│ no │
│ yes │
│ no │
└────────────────────────────────┘
case
ibis.case()
Begin constructing a case expression.
Use the .when
method on the resulting object followed by .end
to create a complete case expression.
Returns
SearchedCaseBuilder
A builder object to use for constructing a case expression.
Examples
>>> import ibis
>>> from ibis import _
>>> ibis.options.interactive = True
>>> t = ibis.memtable(
... {
... "left" : [1 , 2 , 3 , 4 ],
... "symbol" : ["+" , "-" , "*" , "/" ],
... "right" : [5 , 6 , 7 , 8 ],
... }
... )
>>> t.mutate(
... result= (
... ibis.case()
... .when(_.symbol == "+" , _.left + _.right)
... .when(_.symbol == "-" , _.left - _.right)
... .when(_.symbol == "*" , _.left * _.right)
... .when(_.symbol == "/" , _.left / _.right)
... .end()
... )
... )
┏━━━━━━━┳━━━━━━━━┳━━━━━━━┳━━━━━━━━━┓
┃ left ┃ symbol ┃ right ┃ result ┃
┡━━━━━━━╇━━━━━━━━╇━━━━━━━╇━━━━━━━━━┩
│ int64 │ string │ int64 │ float64 │
├───────┼────────┼───────┼─────────┤
│ 1 │ + │ 5 │ 6.0 │
│ 2 │ - │ 6 │ -4.0 │
│ 3 │ * │ 7 │ 21.0 │
│ 4 │ / │ 8 │ 0.5 │
└───────┴────────┴───────┴─────────┘
to_sql
ibis.to_sql(expr, dialect=None, pretty=True, **kwargs)
Return the formatted SQL string for an expression.
Parameters
expr
ir
.Expr
Ibis expression.
required
dialect
str | None
SQL dialect to use for compilation.
None
pretty
bool
Whether to use pretty formatting.
True
kwargs
Scalar parameters
{}
Back to top