{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Ibis+Vega+Altair using an interactive slider\n",
    "\n",
    "We will try to reproduce [this](https://altair-viz.github.io/gallery/us_population_over_time.html)\n",
    "example from the Altair gallery, but with lazily fetching data as the user interacts with the slider.\n",
    "To keep ourselves honest, we'll be putting the data in a SQLite database.\n",
    "\n",
    "First, let's show the original example, without any modifications:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import altair as alt\n",
    "from vega_datasets import data\n",
    "\n",
    "source = data.population.url\n",
    "\n",
    "pink_blue = alt.Scale(domain=('Male', 'Female'),\n",
    "                      range=[\"steelblue\", \"salmon\"])\n",
    "\n",
    "slider = alt.binding_range(min=1900, max=2000, step=10)\n",
    "select_year = alt.selection_single(name=\"year\", fields=['year'],\n",
    "                                   bind=slider, init={'year': 2000})\n",
    "\n",
    "alt.Chart(source).mark_bar().encode(\n",
    "    x=alt.X('sex:N', title=None),\n",
    "    y=alt.Y('people:Q', scale=alt.Scale(domain=(0, 12000000))),\n",
    "    color=alt.Color('sex:N', scale=pink_blue),\n",
    "    column='age:O'\n",
    ").properties(\n",
    "    width=20\n",
    ").add_selection(\n",
    "    select_year\n",
    ").transform_calculate(\n",
    "    \"sex\", alt.expr.if_(alt.datum.sex == 1, \"Male\", \"Female\")\n",
    ").transform_filter(\n",
    "    select_year\n",
    ").configure_facet(\n",
    "    spacing=8\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Loading the data into a database\n",
    "\n",
    "We begin our lazy-fetching example by downloading the data and putting it into a SQLite database:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlalchemy\n",
    "dbfile = 'population.db'\n",
    "engine = sqlalchemy.create_engine(f'sqlite:///{dbfile}')\n",
    "\n",
    "import pandas as pd\n",
    "df = pd.read_json(data.population.url)\n",
    "df.to_sql('pop', engine, if_exists='replace')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now, let's create an ibis connection to this database and verify that the data is there:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import ibis\n",
    "\n",
    "import warnings\n",
    "try:\n",
    "    # ibis version >= 1.4\n",
    "    from ibis.backends import sqlite as ibis_sqlite\n",
    "except ImportError as msg:\n",
    "    # ibis version < 1.4\n",
    "    warnings.warn(str(msg))\n",
    "    from ibis import sqlite as ibis_sqlite\n",
    "\n",
    "connection = ibis_sqlite.connect(dbfile)\n",
    "connection.list_tables()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can use inspect the data using this ibis connection:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pop = connection.table('pop')\n",
    "pop.head().execute()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Making an interactive plot\n",
    "\n",
    "We are now ready to make an interactive plot using this database connection.\n",
    "We can reuse the same objects for `pink_blue`, `slider`, and `select_year`, as they are independent of the data source.\n",
    "The `Chart` specifiation is completely identical, except that instead of the pandas dataframe,\n",
    "we supply it with the Ibis sqlite connection:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# import ibis_vega_transform\n",
    "\n",
    "# alt.Chart(pop).mark_bar().encode(\n",
    "#     x=alt.X('sex:N', title=None),\n",
    "#     y=alt.Y('people:Q', scale=alt.Scale(domain=(0, 12000000))),\n",
    "#     color=alt.Color('sex:N', scale=pink_blue),\n",
    "#     column='age:O'\n",
    "# ).properties(\n",
    "#     width=20\n",
    "# ).add_selection(\n",
    "#     select_year\n",
    "# ).transform_calculate(\n",
    "#     \"sex\", alt.expr.if_(alt.datum.sex == 1, \"Male\", \"Female\")\n",
    "# ).transform_filter(\n",
    "#     select_year\n",
    "# ).configure_facet(\n",
    "#     spacing=8\n",
    "# )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}