tutorial/06-ComplexFiltering.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Complex Filtering\n",
    "\n",
    "The filtering examples we've shown to this point have been pretty simple, either comparisons between columns or fixed values, or set filter functions like `isin` and `notin`. \n",
    "\n",
    "Ibis supports a number of richer analytical filters that can involve one or more of:\n",
    "\n",
    "- Aggregates computed from the same or other tables\n",
    "- Conditional aggregates (in SQL-speak these are similar to \"correlated subqueries\")\n",
    "- \"Existence\" set filters (equivalent to the SQL `EXISTS` and `NOT EXISTS` keywords)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Setup"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from tutorial_utils import setup\n",
    "\n",
    "setup()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import ibis\n",
    "\n",
    "ibis.options.interactive = True\n",
    "\n",
    "connection = ibis.sqlite.connect(\"geography.db\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Using scalar aggregates in filters"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "countries = connection.table(\"countries\")\n",
    "countries.limit(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We could always compute some aggregate value from the table and use that in another expression, or we can use a data-derived aggregate in the filter. Take the average of a column. For example the average of countries size:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "countries.area_km2.mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can use this expression as a substitute for a scalar value in a filter, and the execution engine will combine everything into a single query rather than having to access the database multiple times. For example, we want to filter European countries larger than the average country size in the world. See how most countries in Europe are smaller than the world average:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cond = countries.area_km2 > countries.area_km2.mean()\n",
    "expr = countries[(countries.continent == \"EU\") & cond]\n",
    "expr"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Conditional aggregates\n",
    "\n",
    "\n",
    "Suppose that we wish to filter using an aggregate computed conditional on some other expressions holding true.\n",
    "\n",
    "For example, we want to filter European countries larger than the average country size, but this time of the average in Africa. African countries have an smaller size compared to the world average, and France gets into the list:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "conditional_avg = countries[countries.continent == \"AF\"].area_km2.mean()\n",
    "countries[(countries.continent == \"EU\") & (countries.area_km2 > conditional_avg)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## \"Existence\" filters\n",
    "\n",
    "\n",
    "Some filtering involves checking for the existence of a particular value in a column of another table, or amount the results of some value expression. This is common in many-to-many relationships, and can be performed in numerous different ways, but it's nice to be able to express it with a single concise statement and let Ibis compute it optimally.\n",
    "\n",
    "An example could be finding all countries that had **any** year with a higher GDP than 3 trillion US dollars:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "gdp = connection.table(\"gdp\")\n",
    "gdp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cond = ((gdp.country_code == countries.iso_alpha3) & (gdp.value > 3e12)).any()\n",
    "\n",
    "countries[cond][\"name\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note how this is different than a join between `countries` and `gdp`, which would return one row per year. The method `.any()` is equivalent to filtering with a subquery."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Filtering in aggregations\n",
    "\n",
    "\n",
    "Suppose that you want to compute an aggregation with a subset of the data for _only one_ of the metrics / aggregates in question, and the complete data set with the other aggregates. Most aggregation functions are thus equipped with a `where` argument. Let me show it to you in action:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "arctic = countries.name.isin(\n",
    "    [\n",
    "        \"United States\",\n",
    "        \"Canada\",\n",
    "        \"Finland\",\n",
    "        \"Greenland\",\n",
    "        \"Iceland\",\n",
    "        \"Norway\",\n",
    "        \"Russia\",\n",
    "        \"Sweden\",\n",
    "    ]\n",
    ")\n",
    "\n",
    "metrics = [\n",
    "    countries.count().name(\"# countries\"),\n",
    "    countries.population.sum().name(\"total population\"),\n",
    "    countries.population.sum(where=arctic).name(\"population arctic countries\"),\n",
    "]\n",
    "\n",
    "(countries.group_by(countries.continent).aggregate(metrics))"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.10.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}