01 - Getting Started.ipynb

{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "# Getting started\n",
        "\n",
        "## Installation\n",
        "\n",
        "See the [README](https://github.com/ibis-project/ibis-tutorial#setup) for\n",
        "up-to-date installation instructions!\n",
        "\n",
        "## Intro\n",
        "\n",
        "We can begin by importing Ibis and firing up a connection to DuckDB! (DuckDB is\n",
        "fast and runs locally, so it's handy for lots of use-cases, including\n",
        "tutorials).\n",
        "\n",
        "Here we connect to an existing DuckDB database containing a dataset on penguins."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "import ibis\n",
        "\n",
        "con = ibis.duckdb.connect(\"data/penguins/palmer_penguins.ddb\", read_only=True)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Now we have a connection, we can start by looking around.  Are there any tables\n",
        "in this database (one would hope)?\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "con.list_tables()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Yep, there's a table called `penguins`. Let's take a look at it."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "penguins = con.table(\"penguins\")"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "By default, you'll get a `repr` showing the schema of the table, the column\n",
        "names and the dtype of each column."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "penguins"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "If we run `head` to peek at the data, you'll notice that we don't actually see\n",
        "data (yet), what's going on?"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "penguins.head()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Ibis has a deferred execution model.  It builds up expressions based on what you\n",
        "ask it to do, and then executes those expressions on request.\n",
        "\n",
        "In this case, our query isn't too involved, we want to see the first few rows of\n",
        "the `penguins` table.\n",
        "\n",
        "We can do that by asking for the results of this query as a `pandas.DataFrame`:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "penguins.head().to_pandas()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Or a `pyarrow.Table`:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "penguins.head().to_pyarrow()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Or a `polars.DataFrame`:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "penguins.head().to_polars()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "We'll get into more detail about what Ibis is doing a bit later on.  For now,\n",
        "the important point is that Ibis is deferred.\n",
        "\n",
        "## Interactive Mode\n",
        "\n",
        "Remember when we said Ibis is deferred? Sometimes you want eager execution so\n",
        "you can explore a dataset.  For most of this tutorial, we'll turn on\n",
        "`interactive` mode, where Ibis will eagerly execute as much of the query as it\n",
        "needs to in order to show you the first 10 rows of the result."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "ibis.options.interactive = True"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "In interactive mode, we use `rich` to render the output inline:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "penguins.head()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Tables and Columns\n",
        "\n",
        "`penguins` is a table!  A table is a collection of one or more columns, each\n",
        "with a specific datatype."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "penguins.head()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "type(penguins)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "We can look at a single column of that table using the column name as an\n",
        "attribute:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "penguins.species"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "What kind of column is `species`? It's a `StringColumn`!"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "type(penguins.species)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Ibis \"verbs\", or, stuff you can do to a table\n",
        "\n",
        "The rest of this notebook covers some of the general methods you can use to\n",
        "alter the output of a particular table.\n",
        "\n",
        "We'll cover, in order, `filter`, `select`, `drop`, `mutate`, `order_by`,\n",
        "`aggregate`, `group_by`, and `join`. Time to dive in!\n",
        "\n",
        "## Filter\n",
        "\n",
        "A filter allows you to view a subset of the rows in a table, based on some condition.\n",
        "\n",
        "For instance, we might want to only view penguin data for the Adelie species:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "penguins.filter(penguins.species == \"Adelie\")"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "You can also combine multiple filters, across multiple columns.\n",
        "\n",
        "We can subset the data down to Adelie penguins residing on the island of Dream:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "expr = penguins.filter(\n",
        "    (penguins.island == \"Dream\") & (penguins.species == \"Adelie\")\n",
        ")\n",
        "\n",
        "expr"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Above we combined two filters using `&`, you can also pass them in as\n",
        "individual arguments:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "expr = penguins.filter(\n",
        "        penguins.island == \"Dream\",\n",
        "        penguins.species == \"Adelie\",\n",
        ")\n",
        "expr"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "ibis.to_sql(expr)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Select\n",
        "\n",
        "Filter filters, Select selects (there's a pattern here). If you only want a\n",
        "subset of the columns in the original table, you can select those columns\n",
        "explicitly.\n",
        "\n",
        "You can refer to the columns using strings:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "penguins.select(\"species\", \"island\", \"year\")"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Or you can use explicit references to the `Column` objects:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "penguins.select(penguins.species, penguins.island, penguins.year)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Or you can mix and match:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "penguins.select(\"species\", \"island\", penguins.year)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Drop\n",
        "\n",
        "Drop is nearly the same as Select, but rather than explicitly choosing the\n",
        "columns to display, we explicitly choose the columns to _not_ display.\n",
        "\n",
        "And as with `select`, you can specify the columns as strings:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "penguins.drop(\"sex\", \"year\")"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Or you can use explicit references to the `Column` objects:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "penguins.drop(penguins.sex, penguins.year)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Or you can mix and match:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "penguins.drop(\"sex\", penguins.year)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Mutate\n",
        "\n",
        "Everything we've seen so far has been subtractive -- removing rows or columns.\n",
        "What about _adding_ columns?\n",
        "\n",
        "That's what `mutate` is for! You can create a new column -- either as a\n",
        "function of other existing columns (for example, converting units):"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "penguins.mutate(bill_length_cm=penguins.bill_length_mm / 10)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Or you can create a new column and populate it with some literal value:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "penguins.mutate(my_favorite_number=ibis.literal(41))"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## On immutability\n",
        "\n",
        "We've filtered, selected, dropped, and mutated this `penguins` table quite a bit."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "penguins"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "And yet, notice that none of our changes persist -- the base table for our\n",
        "query isn't altered. The query (or expression) is a recipe of things to do with\n",
        "the base table (`penguins`).\n",
        "\n",
        "If you want to keep an expression around, you can assign it to a variable:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "expr = penguins.select(\"species\", \"island\")\n",
        "expr"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "**Note**: Every time you execute an expression (via interactive mode, or\n",
        "  `to_pandas`, or similar), the entire expression gets executed, starting from\n",
        "  the base table.  DuckDB is very fast and this dataset is very small, so the\n",
        "  delay is unnoticeable, but for very large datasets, it might become more\n",
        "  pronounced.  There is functionality to `cache` intermediate results that isn't\n",
        "  covered in this tutorial, but you can read more about it in [the\n",
        "  docs](https://ibis-project.org/reference/expression-tables.html#ibis.expr.types.relations.Table.cache).\n",
        "\n",
        "## Method chaining\n",
        "\n",
        "You can build up complicated queries by chaining together Ibis methods. The\n",
        "output of many Ibis methods is a table (just like `penguins`!) and we can\n",
        "continue calling table methods until we're satisfied.  Or until we end up with\n",
        "something that _isn't_ a table.  More on that later."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "penguins.select(\"species\", \"island\", \"year\", \"bill_length_mm\").drop(\"year\")"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Not the most staggeringly complicated query, but we'll see more soon.\n",
        "\n",
        "### Exercise 1\n",
        "\n",
        "Your PI is a cranky American biologist who thinks the metric system is for\n",
        "suckers (oh no).\n",
        "\n",
        "They demand that we convert all of the existing measurements (`mm` and `g`) to\n",
        "inches and lbs, respectively (I am so sorry). The PI is extra cranky this\n",
        "morning, so we had better make sure that ONLY the silly units are visible in\n",
        "the output.\n",
        "\n",
        "Some ~helpful~ cursed conversion factors:\n",
        "\n",
        "|  |  |\n",
        "| -- | -- |\n",
        "| mm -> in | divide by 25.4 |\n",
        "| g -> lb | divide by 453.6 |\n",
        "\n",
        "Two ways you might accomplish this:\n",
        "- Chaining `.mutate` to create new columns and `.drop` to drop the old metric columns\n",
        "- Using a single `.select` to create the new columns as well as select the relevant older columns\n",
        "\n",
        "Try both ways below! How do they compare?"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "# Convert the metric units to imperial, and drop the metric columns.\n",
        "# Try this using a `.mutate` and `.drop` call.\n",
        "penguins_imperial_mutate_drop = penguins"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "# Convert the metric units to imperial, and drop the metric columns.\n",
        "# Try this using a single `.select` call.\n",
        "penguins_imperial_select = penguins"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "#### Solutions"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "%load solutions/nb01_ex01_mutate_drop.py"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "%load solutions/nb01_ex01_select.py"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "### Does it matter which method you choose?\n",
        "\n",
        "In this case, no.  Sometimes, there might be a small difference in the generated\n",
        "SQL but they will be semantically equivalent.."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "ibis.to_sql(penguins_imperial_mutate_drop)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "ibis.to_sql(penguins_imperial_select)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "In practice, small differences in the generated SQL don't make a difference.\n",
        "Any modern SQL execution engine will optimize variations to the same set of\n",
        "operations and there will be no measurable performance difference.\n",
        "\n",
        "## Order By\n",
        "\n",
        "Want to order your data by a given column or columns?  Use `order_by`!\n",
        "\n",
        "The default ordering direction is ascending:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "penguins.order_by(penguins.flipper_length_mm)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "We can ask Ibis to sort in descending order, too."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "penguins.order_by(penguins.flipper_length_mm.desc())"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Let's select out a subset of the columns to keep this a bit tidier."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "penguins.order_by(penguins.flipper_length_mm.desc()).select(\n",
        "    \"species\", \"island\", \"flipper_length_mm\"\n",
        ")"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "You can also call `ibis.desc` on the column name to set the order direction:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "(\n",
        "    penguins\n",
        "    .order_by(ibis.desc(\"flipper_length_mm\"))\n",
        "    .select(\"species\", \"island\", \"flipper_length_mm\")\n",
        ")"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Aggregate\n",
        "\n",
        "Ibis has several aggregate functions available to help summarize data.  All the\n",
        "old favorites are there: `mean`, `max`, `min`, `count`, `sum`...\n",
        "\n",
        "You can aggregate a column by calling the method on that column:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "penguins.flipper_length_mm.mean()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Or you can compute multiple aggregates using the `aggregate` method (also\n",
        "available as `agg` for faster typing):"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "penguins.agg(\n",
        "    [\n",
        "        penguins.flipper_length_mm.mean(),\n",
        "        penguins.bill_depth_mm.max()\n",
        "    ]\n",
        ")"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "But aggregates really shine when paired with a `group_by`!\n",
        "\n",
        "## Group By\n",
        "\n",
        "`group_by` creates groupings of rows that have the same value for one or more columns.\n",
        "\n",
        "But it doesn't do much on its own -- you can pair it with aggregate to get a result."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "penguins.group_by(\"species\").agg()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Without any `aggregate` function specified, we get the distinct values of the\n",
        "grouped-column.\n",
        "\n",
        "We can add a second column to the `group_by` to get the distinct pairs across\n",
        "both columns:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "penguins.group_by([\"species\", \"island\"]).agg()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Now, if we add an aggregation function to that, we start to really open things up."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "penguins.group_by([\"species\", \"island\"]).agg(penguins.bill_length_mm.mean())"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "By adding that `mean` to the `aggregate`, we now have a concise way to calculate\n",
        "aggregates over each of the distinct groups in the `group_by`. And we can\n",
        "calculate as many aggregates as we need."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "penguins.group_by([\"species\", \"island\"]).agg(\n",
        "    [penguins.bill_length_mm.mean(), penguins.flipper_length_mm.max()]\n",
        ")"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "If we need more specific groups, we can add to the `group_by`."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "penguins.group_by([\"species\", \"island\", \"sex\"]).agg(\n",
        "        [penguins.bill_length_mm.mean(), penguins.flipper_length_mm.max()]\n",
        "    )"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Exercises\n",
        "\n",
        "Time to use what we've learned to answer some penguin questions.\n",
        "\n",
        "### Exercise 2\n",
        "\n",
        "What was the largest female penguin (by body mass) on each island in the year 2008?\n",
        "\n",
        "\n",
        "#### Solution\n",
        "\n",
        "Note that there are several ways these queries could be written - it's fine if\n",
        "your solution doesn't look like ours, what's important is that the results are\n",
        "the same."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "%load solutions/nb01_ex02.py"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "### Exercise 3\n",
        "\n",
        "What was the largest male penguin (by body mass) on each island for each year of\n",
        "data collection?\n",
        "\n",
        "\n",
        "#### Solution"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "%load solutions/nb01_ex03.py"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Join\n",
        "\n",
        "So far all the methods we've introduced work on a single Table. Some queries require\n",
        "information contained in *multiple* tables. This is where `join` comes in!\n",
        "\n",
        "Joins describe how to combine multiple tables into a single table by\n",
        "correlating rows found in the \"left\" and \"right\" tables in the join.\n",
        "\n",
        "There are many different kinds of join (left, right, inner, outer, semi, anti,\n",
        "...). The simplest is the \"inner join\" on a single column. Barring a treatise\n",
        "on joins, you may think of an inner join as a way to add extra columns from a\n",
        "`right` table to the `left` table via a lookup column. In ibis this join is\n",
        "invoked like:\n",
        "\n",
        "```python\n",
        "left.join(right, (\"key_column_name_in_left\", \"key_column_name_in_right\"))\n",
        "\n",
        "# OR if the column names are the same in both\n",
        "left.join(right, \"key_column_name\")\n",
        "```\n",
        "\n",
        "### Exercise 4\n",
        "\n",
        "Add a new column to the `penguins` table with the mean mass of penguins found\n",
        "on that island."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "mean_mass_per_island = (\n",
        "    penguins\n",
        "    .group_by(\"island\")\n",
        "    .agg(island_mean_mass=penguins.body_mass_g.mean())\n",
        ")\n",
        "\n",
        "penguins.join(...)  # what goes here?"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "#### Solution"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "%load solutions/nb01_ex04.py"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Ibis's magical `_` (\"Deferred\") API\n",
        "\n",
        "Ibis's query API is optimized to be able to build up complex queries through\n",
        "method chaining. For example, in the above query we chain a `group_by`, `agg`\n",
        "and `join` call together into a single larger query.\n",
        "\n",
        "Sometimes we want to be able to refer to the output of one method in a later\n",
        "method call. One way to make calls like this more concise is to make use of\n",
        "ibis's `_` API. This is a \"magical\" object that constructs \"deferred\n",
        "expressions\". Everywhere you see `_` in the expression you should read \"the\n",
        "current table (`self`) in the method being called\".\n",
        "\n",
        "For example, both of these calls do the same thing:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "from ibis import _"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "# Explicitly referencing `penguins.bill_length_mm` and `penguins.bill_depth_mm`\n",
        "t1 = penguins.mutate(\n",
        "    length_depth_ratio=penguins.bill_length_mm / penguins.bill_depth_mm\n",
        ")\n",
        "\n",
        "t1"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "# Using deferred expressions (the `_` object)\n",
        "t2 = penguins.mutate(\n",
        "    length_depth_ratio=_.bill_length_mm / _.bill_depth_mm\n",
        ")\n",
        "\n",
        "t2"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "We can even check that they're the same by asserting that the expressions\n",
        "backing each `ibis.Table` are identical (remember, `ibis` uses a lazy\n",
        "expression API behind the scenes):"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "assert t1.equals(t2)  # assert the expressions backing t1 and t2 are equivalent"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## A brief digression on the SQL Ibis generates\n",
        "\n",
        "Maybe you've heard that SQL has a standard?  This is true, and also misleading.\n",
        "The SQL standard is more of a suggestion, and there are myriad SQL _dialects_.\n",
        "\n",
        "Ibis compiles expressions into the appropriate SQL dialect for the backend you\n",
        "are using. In this case, we started with a DuckDB table, so we get DuckDB SQL:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "ibis.to_sql(expr)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "But if you want to use a _different_ dialect, you can pass the dialect name:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "ibis.to_sql(expr, dialect=\"postgres\")"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Explore Ibis's API\n",
        "\n",
        "Ibis has _many_ methods on its `Table` and various `Column` types, we've only\n",
        "covered a few so far. Besides our [API\n",
        "docs](https://ibis-project.org/reference/expression-tables), another good way of\n",
        "exploring the API is through `<TAB>` completion in your notebook or terminal\n",
        "session. Doing\n",
        "\n",
        "```python\n",
        "# Explore the Table API\n",
        "penguins.<TAB>\n",
        "```\n",
        "\n",
        "or \n",
        "\n",
        "```python\n",
        "# Explore the Column API\n",
        "penguins.species.<TAB>\n",
        "# or for a different column type\n",
        "penguins.body_mass_g.<TAB>\n",
        "```\n",
        "\n",
        "can help you learn what other methods Ibis has to offer.\n",
        "\n",
        "\n",
        "### Exercise 5\n",
        "\n",
        "Find and read the docstring on `ibis.Column.cast`. Then try to cast\n",
        "`penguins.year` to `\"float64\"`, `\"string\"`, `\"date\"`, and `\"int8\"`. happens for\n",
        "each type and why?"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "# Your code here ..."
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "### Exercise 6\n",
        "\n",
        "Find one interesting method on a Table or Column type that we didn't talk about\n",
        "here. Read through the docstring and example, and try calling it on the\n",
        "`penguins` dataset. If you're in need of a suggestion, you might try looking at\n",
        "`Table.rename`, `Table.fillna`, `Table.dropna`, or `Table.describe`, but we\n",
        "suggest exploring around through tab-completion yourself too."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "# Your code here ..."
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "### References\n",
        "\n",
        "1: Horst AM, Hill AP, Gorman KB (2020). palmerpenguins: Palmer Archipelago\n",
        "(Antarctica) penguin data. R package version 0.1.0.\n",
        "https://allisonhorst.github.io/palmerpenguins/. doi: 10.5281/zenodo.3960218."
      ]
    }
  ],
  "metadata": {
    "kernelspec": {
      "name": "python3",
      "language": "python",
      "display_name": "Python 3 (ipykernel)"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 4
}