{
“cells”: [
{

“cell_type”: “markdown”, “metadata”: {}, “source”: [

“# More Value Expressions”

]

}, {

“cell_type”: “markdown”, “metadata”: {}, “source”: [

“## Setup”

]

}, {

“cell_type”: “code”, “execution_count”: null, “metadata”: {}, “outputs”: [], “source”: [

“import ibisn”, “import osn”, “hdfs_port = os.environ.get(‘IBIS_WEBHDFS_PORT’, 50070)n”, “hdfs = ibis.impala.hdfs_connect(host=’impala’, port=hdfs_port)n”, “con = ibis.impala.connect(host=’impala’, database=’ibis_testing’,n”, ” hdfs_client=hdfs)n”, “ibis.options.interactive = True”

]

}, {

“cell_type”: “markdown”, “metadata”: {}, “source”: [

“## Type castingn”, “n”, “The Ibis type system is pretty basic and will get better (and more documented over time). It maps directly onto the current Impala type systemn”, “n”, “- int8n”, “- int16n”, “- int32n”, “- int64n”, “- booleann”, “- floatn”, “- doublen”, “- stringn”, “- timestampn”, “- decimal($precision, $scale)n”, “n”, “These type names can be used to cast from one type to another”

]

}, {

“cell_type”: “code”, “execution_count”: null, “metadata”: {}, “outputs”: [], “source”: [

“table = con.table(‘functional_alltypes’)n”, “table.string_col.cast(‘double’).sum()”

]

}, {

“cell_type”: “code”, “execution_count”: null, “metadata”: {}, “outputs”: [], “source”: [

“table.string_col.cast(‘decimal(12,2)’).sum()”

]

}, {

“cell_type”: “markdown”, “metadata”: {}, “source”: [

“## Case / if-then-else expressionsn”, “n”, “n”, “We support a number of variants of the SQL-equivalent CASE expression, and will add more API functions over time to meet different use cases and enhance the expressiveness of any branching-based value logic.”

]

}, {

“cell_type”: “code”, “execution_count”: null, “metadata”: {}, “outputs”: [], “source”: [

“expr = (table.string_coln”, ” .case()n”, ” .when(‘4’, ‘fee’)n”, ” .when(‘7’, ‘fi’)n”, ” .when(‘1’, ‘fo’)n”, ” .when(‘0’, ‘fum’)n”, ” .else_(table.string_col)n”, ” .end()n”, ” .name(‘new_strings’))n”, “n”, “expr.value_counts()”

]

}, {

“cell_type”: “markdown”, “metadata”: {}, “source”: [

“If the else_ default condition is not provided, any values not matching one of the conditions will be NULL.”

]

}, {

“cell_type”: “code”, “execution_count”: null, “metadata”: {}, “outputs”: [], “source”: [

“expr = (table.string_coln”, ” .case()n”, ” .when(‘4’, ‘fee’)n”, ” .when(‘7’, ‘fi’)n”, ” .end()n”, ” .name(‘with_nulls’))n”, “n”, “expr.value_counts()”

]

}, {

“cell_type”: “markdown”, “metadata”: {}, “source”: [

“To test for an arbitrary series of boolean conditions, use the case API method and pass any boolean expressions potentially involving columns of the table:”

]

}, {

“cell_type”: “code”, “execution_count”: null, “metadata”: {}, “outputs”: [], “source”: [

“expr = (ibis.case()n”, ” .when(table.int_col > 5, table.bigint_col * 2)n”, ” .when(table.int_col > 2, table.bigint_col)n”, ” .else_(table.int_col)n”, ” .end())n”, “n”, “table[‘id’, ‘int_col’, ‘bigint_col’, expr.name(‘case_result’)].limit(20)”

]

}, {

“cell_type”: “markdown”, “metadata”: {}, “source”: [

“Simple ternary-cases (like the Python X if COND else Y) can be written using the ifelse function:”

]

}, {

“cell_type”: “code”, “execution_count”: null, “metadata”: {}, “outputs”: [], “source”: [

“expr = ((table.int_col > 5)n”, ” .ifelse(table.bigint_col / 2, table.bigint_col * 2)n”, ” .name(‘ifelse_result’))n”, “n”, “table[‘int_col’, ‘bigint_col’, expr].limit(10)”

]

}, {

“cell_type”: “markdown”, “metadata”: {}, “source”: [

“## Set membershipn”, “n”, “n”, “The isin and notin functions are like their pandas counterparts. These can take:n”, “n”, “- A list of value expressions, either literal values or other column expressionsn”, “- An array/column expression of some kind”

]

}, {

“cell_type”: “code”, “execution_count”: null, “metadata”: {}, “outputs”: [], “source”: [

“bool_clause = table.string_col.notin([‘1’, ‘4’, ‘7’])n”, “table[bool_clause].string_col.value_counts()”

]

}, {

“cell_type”: “markdown”, “metadata”: {}, “source”: [

“You can also check for membership in an array. Here is an example of filtering based on the top 3 (ignoring ties) most frequently-occurring values in the string_col column of alltypes:”

]

}, {

“cell_type”: “code”, “execution_count”: null, “metadata”: {}, “outputs”: [], “source”: [

“top_strings = table.string_col.value_counts().limit(3).string_coln”, “top_filter = table.string_col.isin(top_strings)n”, “expr = table[top_filter]n”, “n”, “expr.count()”

]

}, {

“cell_type”: “markdown”, “metadata”: {}, “source”: [

“This is a common enough operation that we provide a special analytical filter function topk:”

]

}, {

“cell_type”: “code”, “execution_count”: null, “metadata”: {}, “outputs”: [], “source”: [

“table[table.string_col.topk(3)].count()”

]

}, {

“cell_type”: “markdown”, “metadata”: {}, “source”: [

“Cool, huh? More on topk later.”

]

}, {

“cell_type”: “markdown”, “metadata”: {}, “source”: [

“## Null-nessn”, “n”, “Like their pandas equivalents, the isnull and notnull functions return True values if the values are null, or non-null, respectively. For example:”

]

}, {

“cell_type”: “code”, “execution_count”: null, “metadata”: {}, “outputs”: [], “source”: [

“expr = (table.string_coln”, ” .case()n”, ” .when(‘4’, ‘fee’)n”, ” .when(‘7’, ‘fi’)n”, ” .when(‘1’, ‘fo’)n”, ” .end()n”, ” .name(‘new_strings’))n”, “n”, “expr.isnull().value_counts()”

]

}, {

“cell_type”: “markdown”, “metadata”: {}, “source”: [

“Functions like isnull can be combined with case expressions or functions like ifelse to replace null values with some other value. ifelse here will use the first value supplied for any True value and the second value for any False value. Either value can be a scalar or array. “

]

}, {

“cell_type”: “code”, “execution_count”: null, “metadata”: {}, “outputs”: [], “source”: [

“expr2 = expr.isnull().ifelse(‘was null’, expr).name(‘strings’)n”, “expr2.value_counts()”

]

}, {

“cell_type”: “markdown”, “metadata”: {}, “source”: [

“## Distinct-based operationsn”, “n”, “n”, “Ibis supports using distinct to remove duplicate rows or values on tables or arrays. For example:”

]

}, {

“cell_type”: “code”, “execution_count”: null, “metadata”: {}, “outputs”: [], “source”: [

“table[‘int_col’, ‘bigint_col’].distinct()”

]

}, {

“cell_type”: “code”, “execution_count”: null, “metadata”: {}, “outputs”: [], “source”: [

“table.string_col.distinct()”

]

}, {

“cell_type”: “markdown”, “metadata”: {}, “source”: [

“This can be combined with count to form a reduction metric:”

]

}, {

“cell_type”: “code”, “execution_count”: null, “metadata”: {}, “outputs”: [], “source”: [

“metric = (table.bigint_coln”, ” .distinct().count()n”, ” .name(‘unique_bigints’))”

]

}, {

“cell_type”: “markdown”, “metadata”: {}, “source”: [

“This is common enough to have a shortcut nunique:”

]

}, {

“cell_type”: “code”, “execution_count”: null, “metadata”: {}, “outputs”: [], “source”: [

“table.string_col.nunique()”

]

}, {

“cell_type”: “markdown”, “metadata”: {}, “source”: [

“## String operationsn”, “n”, “n”, “What’s supported is pretty basic right now. We intend to support the full gamut of regular expression munging with a nice API, though in some cases some work will be required on Impala’s backend to support everything. “

]

}, {

“cell_type”: “code”, “execution_count”: null, “metadata”: {}, “outputs”: [], “source”: [

“nation = con.table(‘tpch_nation’)n”, “nation.limit(5)”

]

}, {

“cell_type”: “markdown”, “metadata”: {}, “source”: [

“At the moment, basic substring operations (substr, with conveniences left and right) and Python-like APIs such as lower and upper (for case normalization) are supported. So you could count first letter occurrences in a string column like so:”

]

}, {

“cell_type”: “code”, “execution_count”: null, “metadata”: {}, “outputs”: [], “source”: [

“expr = nation.n_name.lower().left(1).name(‘first_letter’)n”, “expr.value_counts().sort_by((‘count’, False))”

]

}, {

“cell_type”: “markdown”, “metadata”: {}, “source”: [

“For fuzzy and regex filtering/searching, you can use one of the followingn”, “n”, “- like, works as the SQL LIKE keywordn”, “- rlike, like re.search or SQL RLIKEn”, “- contains, like x in str_value in Python”

]

}, {

“cell_type”: “code”, “execution_count”: null, “metadata”: {}, “outputs”: [], “source”: [

“nation[nation.n_name.like(‘%GE%’)]”

]

}, {

“cell_type”: “code”, “execution_count”: null, “metadata”: {}, “outputs”: [], “source”: [

“nation[nation.n_name.lower().rlike(‘.*ge.*’)]”

]

}, {

“cell_type”: “code”, “execution_count”: null, “metadata”: {}, “outputs”: [], “source”: [

“nation[nation.n_name.lower().contains(‘ge’)]”

]

}, {

“cell_type”: “markdown”, “metadata”: {}, “source”: [

“## Timestamp operationsn”, “n”, “n”, “Date and time functionality is relatively limited at present compared with pandas, but we’ll get there. The main things we have right now aren”, “n”, “- Field access (year, month, day, …)n”, “- Timedeltasn”, “- Comparisons with fixed timestamps”

]

}, {

“cell_type”: “code”, “execution_count”: null, “metadata”: {}, “outputs”: [], “source”: [

“table = con.table(‘functional_alltypes’)n”, “n”, “table[table.timestamp_col, table.timestamp_col.minute().name(‘minute’)].limit(10)”

]

}, {

“cell_type”: “markdown”, “metadata”: {}, “source”: [

“Somewhat more comprehensively”

]

}, {

“cell_type”: “code”, “execution_count”: null, “metadata”: {}, “outputs”: [], “source”: [

“def get_field(f):n”, ” return getattr(table.timestamp_col, f)().name(f)n”, “n”, “fields = [‘year’, ‘month’, ‘day’, ‘hour’, ‘minute’, ‘second’, ‘millisecond’]n”, “projection = [table.timestamp_col] + [get_field(x) for x in fields]n”, “table[projection].limit(10)”

]

}, {

“cell_type”: “markdown”, “metadata”: {}, “source”: [

“For timestamp arithmetic and comparisons, check out functions in the top level ibis namespace. This include things like day and second, but also the ibis.timestamp function:”

]

}, {

“cell_type”: “code”, “execution_count”: null, “metadata”: {}, “outputs”: [], “source”: [

“table[table.timestamp_col.min(), table.timestamp_col.max(), table.count().name(‘nrows’)]”

]

}, {

“cell_type”: “code”, “execution_count”: null, “metadata”: {}, “outputs”: [], “source”: [

“table[table.timestamp_col < ‘2010-01-01’].count()”

]

}, {

“cell_type”: “code”, “execution_count”: null, “metadata”: {}, “outputs”: [], “source”: [

“table[table.timestamp_col < n”, ” (ibis.timestamp(‘2010-01-01’) + ibis.interval(months=3))].count()”

]

}, {

“cell_type”: “code”, “execution_count”: null, “metadata”: {}, “outputs”: [], “source”: [

“expr = (table.timestamp_col + ibis.interval(days=1) + ibis.interval(hours=4)).name(‘offset’)n”, “table[table.timestamp_col, expr, ibis.now().name(‘current_time’)].limit(10)”

]

}

], “metadata”: {

“kernelspec”: {

“display_name”: “Python 3”, “language”: “python”, “name”: “python3”

}, “language_info”: {

“codemirror_mode”: {

“name”: “ipython”, “version”: 3

}, “file_extension”: “.py”, “mimetype”: “text/x-python”, “name”: “python”, “nbconvert_exporter”: “python”, “pygments_lexer”: “ipython3”, “version”: “3.6.7”

}

}, “nbformat”: 4, “nbformat_minor”: 1

}