README.ipynb

{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "# data_algebra\n",
    "\n",
    "[data_algebra](https://github.com/WinVector/data_algebra) is a piped data wrangling system\n",
    "based on Codd's relational algebra and experience working with data manipulation languages at scale.\n",
    "The primary purpose of the package is to support an easy to\n",
    "compose and maintain grammar of data processing steps that in turn can be used to generate\n",
    "database specific SQL. The package also implements the same transforms for Pandas DataFrames.\n",
    "\n",
    "The package is [available on PyPi](https://pypi.org/project/data-algebra/), and can be installed with `pip install data_algebra`.\n",
    "\n",
    "A good introduction can be found [here](https://github.com/WinVector/data_algebra/blob/main/Examples/Introduction/data_algebra_Introduction.ipynb), and many worked examples are [here](https://github.com/WinVector/data_algebra/tree/main/Examples). A catalog of expression methods is found [here](https://github.com/WinVector/data_algebra/blob/main/Examples/Methods/op_catalog.csv). The pydoc documentation is [here](https://winvector.github.io/data_algebra/). And the [README](https://github.com/WinVector/data_algebra/blob/main/README.md) is a good place to check for news or updates.\n",
    "\n",
    "Currently, the system is primarily adapted and testing for Pandas, Polars, Google BigQuery, PostgreSQL, SQLite, and Spark. Porting and extension is designed to be easy.\n",
    "\n",
    "[This](https://github.com/WinVector/data_algebra) is to be the [`Python`](https://www.python.org) equivalent of the [`R`](https://www.r-project.org) packages [`rquery`](https://github.com/WinVector/rquery/), [`rqdatatable`](https://github.com/WinVector/rqdatatable), and [`cdata`](https://CRAN.R-project.org/package=cdata).  This package supplies piped Codd-transform style notation that can perform data engineering in [`Pandas`](https://pandas.pydata.org) or (still in development) [`Polars`](https://www.pola.rs) and generate [`SQL`](https://en.wikipedia.org/wiki/SQL) queries from the same specification.\n",
    "\n",
    "# Installing\n",
    "\n",
    "Install `data_algebra` with `pip install data_algebra`\n",
    "\n",
    "# Announcement\n",
    "\n",
    "This article introduces the [`data_algebra`](https://github.com/WinVector/data_algebra) project: a data processing tool family available in `R` and `Python`.  These tools are designed to transform data either in-memory or on remote databases.  For an example (with video) of using `data_algebra` to re-arrange data layout please see [here](https://github.com/WinVector/data_algebra/blob/master/Examples/cdata/ranking_pivot_example.md). The key question is: what operators (or major steps) are supported by the data algebra, and what methods (operations on columns) are supported. The operators are documented [here](https://github.com/WinVector/data_algebra/blob/main/Examples/Introduction/data_algebra_Introduction.ipynb), and which methods can be used in which contexts is linsted [here](https://github.com/WinVector/data_algebra/blob/main/Examples/Methods/op_catalog.csv). Also, please check the [README](https://github.com/WinVector/data_algebra/blob/main/README.md) for news.\n",
    "\n",
    "In particular, we will discuss the `Python` implementation (also called `data_algebra`) and its relation to the mature `R` implementations (`rquery` and `rqdatatable`).\n",
    "\n",
    "## Introduction\n",
    "\n",
    "The project intent is to realize a method chained data processing language based on [Codd's relational operators](https://en.wikipedia.org/wiki/Relational_model) that is easy to maintain, has helpful tooling, and has very similar realizations (or dialects) for:\n",
    "\n",
    "  * [`SQL`](https://en.wikipedia.org/wiki/SQL) databases accessed from [`Python`](https://www.python.org), useful working at scale with `PostgreSQL` or Apache `Spark` (`Spark` example [here](https://github.com/WinVector/data_algebra/blob/master/Examples/Spark/pyspark_example.ipynb)).\n",
    "  * [`Pandas`](https://pandas.pydata.org) `DataFrame` objects in `Python`.\n",
    "  * `SQL` databases access from [`R`](https://www.r-project.org) (implementation is [here](https://github.com/WinVector/rquery), and is mature and ready for production use).\n",
    "  \n",
    "The intent is the notation should look idiomatic in each language.  Working in `Python` should feel like working in `Python`, and working in `R` should feel like working in `R`.  The data semantics, however, are designed to be close to the `SQL` realizations (given the close connection of `SQL` to the relational algebra; in particular row numbering starts at `1` and row and column order is not preserved except at row-order steps or select-columns steps respectively). The intent is: it should be very easy to use the system in either `Python` or `R` (a boon to multi-language data science projects) and it is easy to port either code or experience from one system to another (a boon for porting projects, or for data scientists working with more than one code base or computer language).\n",
    "\n",
    "Related work includes:\n",
    "\n",
    "  * [Codd's relational algebra](https://en.wikipedia.org/wiki/Relational_algebra)\n",
    "  * [`SQL`](https://en.wikipedia.org/wiki/SQL)\n",
    "  * [`data.table`](https://CRAN.R-project.org/package=data.table)\n",
    "  * [`datalog`](https://en.wikipedia.org/wiki/Datalog)\n",
    "  * [`dfply`](https://github.com/kieferk/dfply)\n",
    "  * [`dplython`](https://github.com/dodger487/dplython)\n",
    "  * [`LINQ`](https://en.wikipedia.org/wiki/Language_Integrated_Query)\n",
    "  * [`Apache Calcite`](https://calcite.apache.org)\n",
    "  * [`dplyr`](https://CRAN.R-project.org/package=dplyr)\n",
    "  * [`dtplyr`](https://CRAN.R-project.org/package=dtplyr)\n",
    "  * [`table.express`](https://github.com/asardaes/table.express)\n",
    "  * [`Pandas`](https://pandas.pydata.org)\n",
    "  * [`pandas-ply`](https://github.com/coursera/pandas-ply)\n",
    "  * [`Polars`](https://www.pola.rs)\n",
    "  * [`PRQL`](https://github.com/PRQL/prql/)\n",
    "  * [`SQLAlchemy`](https://www.sqlalchemy.org)\n",
    "  * [`rquery`](https://github.com/WinVector/rquery/)\n",
    "  * [`cdata`](https://github.com/WinVector/cdata/)\n",
    "  * [`siuba`](https://github.com/machow/siuba)\n",
    "  * [`Substrait`](https://substrait.io/)\n",
    "  * [`tidypolars`](https://github.com/markfairbanks/tidypolars)\n",
    "  * [`Preql`](https://github.com/erezsh/Preql)\n",
    "  \n",
    "The `data_algebra` principles include:\n",
    "\n",
    "  * Writing data transforms as a pipeline or method-chain of many simple transform steps.\n",
    "  * Treating data transform pipelines or directed acyclic graphs (DAGs) as themselves being sharable data.\n",
    "  * Being able to use the same transform specification many places (in memory, on databases, in `R`, in `Python`).\n",
    "\n",
    "The `data_algebra` supplies two primary services:\n",
    "\n",
    "  * Building composite data processing pipelines (which we demonstrate in this note).\n",
    "  * Building record transforms (which we demonstrate [here](https://github.com/WinVector/data_algebra/blob/master/Examples/cdata/cdata_general_example.ipynb)).\n",
    "\n",
    "## Example\n",
    "\n",
    "Let's start with a pipeline example in `Python` (for a record transform example, please see [here](https://github.com/WinVector/data_algebra/blob/master/Examples/cdata/cdata_general_example.ipynb)).\n",
    "\n",
    "For our example we will assume we have a data set of how many points different subjects score in a psychological survey.  The goal is transform the data so that we see what fraction of the subjects answers are in each category (subject to an exponential transform, as often used in [logistic regression](https://en.wikipedia.org/wiki/Logistic_regression)).  We then treat the per-subject renormalized data as a probability or diagnosis.\n",
    "\n",
    "The exact meaning of such a scoring method are not the topic of this note.  It is a notional example to show a non-trivial data transformation need.  In particular: having to normalize per-subject (divide some set of scores per-subject by a per-subject total) is a classic pain point in data-processing.  In classic `SQL` this can only be done by joining against a summary table, or in more modern `SQL` with a \"[window function](https://en.wikipedia.org/wiki/SQL_window_function).\" We want to show by working in small enough steps this can be done simply.\n",
    "\n",
    "### Set up\n",
    "\n",
    "Let's start our `Python` example.  First we import the packages we are going to use, and set a few options.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'1.6.5'"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import polars as pl\n",
    "import data_algebra as da\n",
    "import data_algebra.BigQuery\n",
    "\n",
    "\n",
    "da.__version__"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "Now let's type in our example data.  Notice this is an in-memory `Polars` `Data.Frame`.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe td {\n",
       "        white-space: pre;\n",
       "    }\n",
       "\n",
       "    .dataframe td {\n",
       "        padding-top: 0;\n",
       "    }\n",
       "\n",
       "    .dataframe td {\n",
       "        padding-bottom: 0;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "<small>shape: (4, 5)</small>\n",
       "<thead>\n",
       "<tr>\n",
       "<th>\n",
       "subjectID\n",
       "</th>\n",
       "<th>\n",
       "surveyCategory\n",
       "</th>\n",
       "<th>\n",
       "assessmentTotal\n",
       "</th>\n",
       "<th>\n",
       "irrelevantCol1\n",
       "</th>\n",
       "<th>\n",
       "irrelevantCol2\n",
       "</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>\n",
       "i64\n",
       "</td>\n",
       "<td>\n",
       "str\n",
       "</td>\n",
       "<td>\n",
       "f64\n",
       "</td>\n",
       "<td>\n",
       "str\n",
       "</td>\n",
       "<td>\n",
       "str\n",
       "</td>\n",
       "</tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "<tr>\n",
       "<td>\n",
       "1\n",
       "</td>\n",
       "<td>\n",
       "&quot;withdrawal beh...\n",
       "</td>\n",
       "<td>\n",
       "5.0\n",
       "</td>\n",
       "<td>\n",
       "&quot;irrel1&quot;\n",
       "</td>\n",
       "<td>\n",
       "&quot;irrel2&quot;\n",
       "</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>\n",
       "1\n",
       "</td>\n",
       "<td>\n",
       "&quot;positive re-fr...\n",
       "</td>\n",
       "<td>\n",
       "2.0\n",
       "</td>\n",
       "<td>\n",
       "&quot;irrel1&quot;\n",
       "</td>\n",
       "<td>\n",
       "&quot;irrel2&quot;\n",
       "</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>\n",
       "2\n",
       "</td>\n",
       "<td>\n",
       "&quot;withdrawal beh...\n",
       "</td>\n",
       "<td>\n",
       "3.0\n",
       "</td>\n",
       "<td>\n",
       "&quot;irrel1&quot;\n",
       "</td>\n",
       "<td>\n",
       "&quot;irrel2&quot;\n",
       "</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>\n",
       "2\n",
       "</td>\n",
       "<td>\n",
       "&quot;positive re-fr...\n",
       "</td>\n",
       "<td>\n",
       "4.0\n",
       "</td>\n",
       "<td>\n",
       "&quot;irrel1&quot;\n",
       "</td>\n",
       "<td>\n",
       "&quot;irrel2&quot;\n",
       "</td>\n",
       "</tr>\n",
       "</tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "shape: (4, 5)\n",
       "┌───────────┬─────────────────────┬─────────────────┬────────────────┬────────────────┐\n",
       "│ subjectID ┆ surveyCategory      ┆ assessmentTotal ┆ irrelevantCol1 ┆ irrelevantCol2 │\n",
       "│ ---       ┆ ---                 ┆ ---             ┆ ---            ┆ ---            │\n",
       "│ i64       ┆ str                 ┆ f64             ┆ str            ┆ str            │\n",
       "╞═══════════╪═════════════════════╪═════════════════╪════════════════╪════════════════╡\n",
       "│ 1         ┆ withdrawal behavior ┆ 5.0             ┆ irrel1         ┆ irrel2         │\n",
       "│ 1         ┆ positive re-framing ┆ 2.0             ┆ irrel1         ┆ irrel2         │\n",
       "│ 2         ┆ withdrawal behavior ┆ 3.0             ┆ irrel1         ┆ irrel2         │\n",
       "│ 2         ┆ positive re-framing ┆ 4.0             ┆ irrel1         ┆ irrel2         │\n",
       "└───────────┴─────────────────────┴─────────────────┴────────────────┴────────────────┘"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d_local = pl.DataFrame({\n",
    "    'subjectID':[1, 1, 2, 2],\n",
    "    'surveyCategory': [ \"withdrawal behavior\", \"positive re-framing\", \"withdrawal behavior\", \"positive re-framing\"],\n",
    "    'assessmentTotal': [5., 2., 3., 4.],\n",
    "    'irrelevantCol1': ['irrel1']*4,\n",
    "    'irrelevantCol2': ['irrel2']*4,\n",
    "})\n",
    "\n",
    "d_local"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "\n",
    "Let's also copy this data to a database.  Normally big data is already in the system one wants to work with, so the copying over is just to simulate the data already being there.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "BigQuery_DBHandle(db_model=BigQueryModel, conn=<google.cloud.bigquery.client.Client object at 0x7fd67052be80>)\n"
     ]
    }
   ],
   "source": [
    "db_handle = data_algebra.BigQuery.example_handle()\n",
    "\n",
    "print(db_handle)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>subjectID</th>\n",
       "      <th>surveyCategory</th>\n",
       "      <th>assessmentTotal</th>\n",
       "      <th>irrelevantCol1</th>\n",
       "      <th>irrelevantCol2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>withdrawal behavior</td>\n",
       "      <td>5.0</td>\n",
       "      <td>irrel1</td>\n",
       "      <td>irrel2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>positive re-framing</td>\n",
       "      <td>2.0</td>\n",
       "      <td>irrel1</td>\n",
       "      <td>irrel2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>withdrawal behavior</td>\n",
       "      <td>3.0</td>\n",
       "      <td>irrel1</td>\n",
       "      <td>irrel2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2</td>\n",
       "      <td>positive re-framing</td>\n",
       "      <td>4.0</td>\n",
       "      <td>irrel1</td>\n",
       "      <td>irrel2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   subjectID       surveyCategory  assessmentTotal irrelevantCol1  \\\n",
       "0          1  withdrawal behavior              5.0         irrel1   \n",
       "1          1  positive re-framing              2.0         irrel1   \n",
       "2          2  withdrawal behavior              3.0         irrel1   \n",
       "3          2  positive re-framing              4.0         irrel1   \n",
       "\n",
       "  irrelevantCol2  \n",
       "0         irrel2  \n",
       "1         irrel2  \n",
       "2         irrel2  \n",
       "3         irrel2  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "remote_table_description = db_handle.insert_table(\n",
    "    d_local, \n",
    "    table_name='d', \n",
    "    allow_overwrite=True)\n",
    "\n",
    "remote_table_description.head\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "\n",
    "Normally one does not read data back from a database, but instead materializes results in the database with `SQL` commands such as `CREATE TABLE tablename AS SELECT ...`. Also note: case in columns is a bit of nightmare. It is often best to lower-case them all.\n",
    "\n",
    "### Back to the `data_algebra`\n",
    "\n",
    "Now we continue our example by importing the `data_algebra` components we need.\n",
    "\n",
    "Now we use the `data_algebra` to define our processing pipeline: `ops`.  We are writing this pipeline using a [method chaining](https://en.wikipedia.org/wiki/Method_chaining) notation.  This notation will look *very* much like a [pipe](https://en.wikipedia.org/wiki/Pipeline_(Unix)) to `R`/[`magrittr`](https://CRAN.R-project.org/package=magrittr) users.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "scale = 0.237\n",
    "\n",
    "ops = (\n",
    "    da.descr(d=d_local)\n",
    "        .extend({'probability': f'(assessmentTotal * {scale}).exp()'})\n",
    "        .extend({'total': 'probability.sum()'},\n",
    "                partition_by='subjectID')\n",
    "        .extend({'probability': 'probability / total'})\n",
    "        .extend({'row_number': '(1).cumsum()'},\n",
    "                partition_by=['subjectID'],\n",
    "                order_by=['probability'], \n",
    "                reverse=['probability'])\n",
    "        .select_rows('row_number == 1')\n",
    "        .select_columns(['subjectID', 'surveyCategory', 'probability'])\n",
    "        .rename_columns({'diagnosis': 'surveyCategory'})\n",
    "    )\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "We are deliberately writing a longer pipeline of simple steps, so we can use the same pipeline locally with Pandas or Polars, and (potentially) great scale with `PostgreSQL` or Apache `Spark`.  A more concise variation of this pipeline can be found in the R example [here](https://github.com/WinVector/rquery).\n",
    "\n",
    "The intent is: the user can build up very sophisticated processing pipelines using a small number of primitive steps.  The pipelines tend to be long, but can still be very efficient- as they are well suited for use with `Polars`, and with `SQL` query optimizers.  Most of the heavy lifting is performed by the  very powerful \"window functions\" (triggered by use of `partition_by` and `order_by`) available on the `extend()` step.  Multiple statements can be combined into extend steps, but only when they have the same window-structure, and don't create and use the same value name in the same statement (except for replacement, which is shown in this example).  Many conditions are checked and enforced during pipeline construction, making debugging very easy.\n",
    "\n",
    "For a more Pythonic way of writing the same pipeline we can show how the code would have been formatted by [`black`](https://github.com/psf/black).\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(\n",
      "    TableDescription(\n",
      "        table_name=\"d\",\n",
      "        column_names=[\n",
      "            \"subjectID\",\n",
      "            \"surveyCategory\",\n",
      "            \"assessmentTotal\",\n",
      "            \"irrelevantCol1\",\n",
      "            \"irrelevantCol2\",\n",
      "        ],\n",
      "    )\n",
      "    .extend({\"probability\": \"(assessmentTotal * 0.237).exp()\"})\n",
      "    .extend({\"total\": \"probability.sum()\"}, partition_by=[\"subjectID\"])\n",
      "    .extend({\"probability\": \"probability / total\"})\n",
      "    .extend(\n",
      "        {\"row_number\": \"(1).cumsum()\"},\n",
      "        partition_by=[\"subjectID\"],\n",
      "        order_by=[\"probability\"],\n",
      "        reverse=[\"probability\"],\n",
      "    )\n",
      "    .select_rows(\"row_number == 1\")\n",
      "    .select_columns([\"subjectID\", \"surveyCategory\", \"probability\"])\n",
      "    .rename_columns({\"diagnosis\": \"surveyCategory\"})\n",
      ")\n",
      "\n"
     ]
    }
   ],
   "source": [
    "py_source = ops.to_python(pretty=True)\n",
    "\n",
    "print(py_source)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "In either case, the pipeline is read as a sequence of operations (top to bottom, and left to right).  What it is saying is:\n",
    "\n",
    "  * We start with a table named \"d\" that is known to have columns \"subjectID\", \"surveyCategory\", \"assessmentTotal\", \"irrelevantCol1\", and \"irrelevantCol2\".\n",
    "  * We produce a new table by transforming this table through a sequence of \"extend\" operations which add new columns.\n",
    "  \n",
    "    * The first `extend` computes `probability = exp(scale*assessmentTotal)`, this is similar to the inverse-link step of a logistic regression. We assume when writing this pipeline we were given this math as a requirement.\n",
    "    * The next few `extend` steps total the `probability` per-subject (this is controlled by the `partition_by` argument) and then rank the normalized probabilities per-subject (grouping again specified by the `partition_by` argument, and order controlled by the `order_by` clause).\n",
    "    \n",
    "  * We then select the per-subject top-ranked rows by the `select_rows` step.\n",
    "  \n",
    "  * And finally we clean up the results for presentation with the `select_columns`, `rename_columns`, and `order_rows` steps.  The names of these methods are intended to evoke what they do.\n",
    "  \n",
    "The point is: each step is deliberately so trivial one can reason about it. However the many steps in sequence do quite a lot.\n",
    "\n",
    "### `SQL`\n",
    "\n",
    "Once we have the `ops` object we can do quite a lot with it. We have already exhibited the pretty-printing of the pipeline. Next we demonstrate translating the operator pipeline into `SQL`.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "-- data_algebra SQL https://github.com/WinVector/data_algebra\n",
      "--  dialect: BigQueryModel 1.6.5\n",
      "--       string quote: \"\n",
      "--   identifier quote: `\n",
      "WITH\n",
      " `table_reference_0` AS (\n",
      "  SELECT\n",
      "   `subjectID` ,\n",
      "   `surveyCategory` ,\n",
      "   `assessmentTotal`\n",
      "  FROM\n",
      "   `data-algebra-test.test_1.d`\n",
      " ) ,\n",
      " `extend_1` AS (\n",
      "  SELECT  -- .extend({ 'probability': '(assessmentTotal * 0.237).exp()'})\n",
      "   `subjectID` ,\n",
      "   `surveyCategory` ,\n",
      "   EXP(`assessmentTotal` * 0.237) AS `probability`\n",
      "  FROM\n",
      "   `table_reference_0`\n",
      " ) ,\n",
      " `extend_2` AS (\n",
      "  SELECT  -- .extend({ 'total': 'probability.sum()'}, partition_by=['subjectID'])\n",
      "   `subjectID` ,\n",
      "   `surveyCategory` ,\n",
      "   `probability` ,\n",
      "   SUM(`probability`) OVER ( PARTITION BY `subjectID`  )  AS `total`\n",
      "  FROM\n",
      "   `extend_1`\n",
      " ) ,\n",
      " `extend_3` AS (\n",
      "  SELECT  -- .extend({ 'probability': 'probability / total'})\n",
      "   `subjectID` ,\n",
      "   `surveyCategory` ,\n",
      "   `probability` / `total` AS `probability`\n",
      "  FROM\n",
      "   `extend_2`\n",
      " ) ,\n",
      " `extend_4` AS (\n",
      "  SELECT  -- .extend({ 'row_number': '(1).cumsum()'}, partition_by=['subjectID'], order_by=['probability'], reverse=['probability'])\n",
      "   `subjectID` ,\n",
      "   `surveyCategory` ,\n",
      "   `probability` ,\n",
      "   SUM(1) OVER ( PARTITION BY `subjectID` ORDER BY `probability` DESC  )  AS `row_number`\n",
      "  FROM\n",
      "   `extend_3`\n",
      " ) ,\n",
      " `select_rows_5` AS (\n",
      "  SELECT  -- .select_rows('row_number == 1')\n",
      "   `subjectID` ,\n",
      "   `surveyCategory` ,\n",
      "   `probability`\n",
      "  FROM\n",
      "   `extend_4`\n",
      "  WHERE\n",
      "   `row_number` = 1\n",
      " )\n",
      "SELECT  -- .rename_columns({'diagnosis': 'surveyCategory'})\n",
      " `surveyCategory` AS `diagnosis` ,\n",
      " `subjectID` ,\n",
      " `probability`\n",
      "FROM\n",
      " `select_rows_5`\n",
      "\n"
     ]
    }
   ],
   "source": [
    "sql = db_handle.to_sql(ops)\n",
    "\n",
    "print(sql)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "\n",
    "Older `SQL` (with use of with or common table expressions) can be hard to read, as `SQL` expresses composition by inner-nesting (inside `SELECT` statements happen first).  The operator pipeline expresses composition by sequencing or method-chaining, which can be a lot more legible.  In this example we use the SQL-99 common table expression (`WITH`) notation to manage the composition in a more legible manner. A huge advantage of the `SQL` is: we can send it to the database for execution, as we do now.\n",
    "\n",
    "Also notice the generated `SQL` has applied query narrowing: columns not used in the outer queries are removed from the inner queries. The \"irrelevant\" columns are not carried into the calculation as they would be with a `SELECT *`.  This early optimization comes in quite handy.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>diagnosis</th>\n",
       "      <th>subjectID</th>\n",
       "      <th>probability</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>withdrawal behavior</td>\n",
       "      <td>1</td>\n",
       "      <td>0.670622</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>positive re-framing</td>\n",
       "      <td>2</td>\n",
       "      <td>0.558974</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             diagnosis  subjectID  probability\n",
       "0  withdrawal behavior          1     0.670622\n",
       "1  positive re-framing          2     0.558974"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ops >> db_handle"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "\n",
    "What comes back is: one row per subject, with the highest per-subject diagnosis and the estimated probability. Again, the math of this is outside the scope of this note (think of that as something coming from a specification)- the ability to write such a pipeline is our actual topic. We are also working in a db-oriented view, that row and column order is irrelevant unless specified.\n",
    "\n",
    "The hope is that the `data_algebra` pipeline is easier to read, write, and maintain than the `SQL` query. If we wanted to change the calculation we would just add a stage to the `data_algebra` pipeline and then regenerate the `SQL` query.\n",
    "\n",
    "### `Polars`\n",
    "\n",
    "An advantage of the pipeline is it can also be directly used on `Pandas` or `Polars` `DataFrame`s. Let's see how that is achieved.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe td {\n",
       "        white-space: pre;\n",
       "    }\n",
       "\n",
       "    .dataframe td {\n",
       "        padding-top: 0;\n",
       "    }\n",
       "\n",
       "    .dataframe td {\n",
       "        padding-bottom: 0;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "<small>shape: (2, 3)</small>\n",
       "<thead>\n",
       "<tr>\n",
       "<th>\n",
       "subjectID\n",
       "</th>\n",
       "<th>\n",
       "diagnosis\n",
       "</th>\n",
       "<th>\n",
       "probability\n",
       "</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>\n",
       "i64\n",
       "</td>\n",
       "<td>\n",
       "str\n",
       "</td>\n",
       "<td>\n",
       "f64\n",
       "</td>\n",
       "</tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "<tr>\n",
       "<td>\n",
       "1\n",
       "</td>\n",
       "<td>\n",
       "&quot;withdrawal beh...\n",
       "</td>\n",
       "<td>\n",
       "0.670622\n",
       "</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>\n",
       "2\n",
       "</td>\n",
       "<td>\n",
       "&quot;positive re-fr...\n",
       "</td>\n",
       "<td>\n",
       "0.558974\n",
       "</td>\n",
       "</tr>\n",
       "</tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "shape: (2, 3)\n",
       "┌───────────┬─────────────────────┬─────────────┐\n",
       "│ subjectID ┆ diagnosis           ┆ probability │\n",
       "│ ---       ┆ ---                 ┆ ---         │\n",
       "│ i64       ┆ str                 ┆ f64         │\n",
       "╞═══════════╪═════════════════════╪═════════════╡\n",
       "│ 1         ┆ withdrawal behavior ┆ 0.670622    │\n",
       "│ 2         ┆ positive re-framing ┆ 0.558974    │\n",
       "└───────────┴─────────────────────┴─────────────┘"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ops.eval({'d': d_local})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "\n",
    "There is also a shorthand notation for single table source pipelines:\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe td {\n",
       "        white-space: pre;\n",
       "    }\n",
       "\n",
       "    .dataframe td {\n",
       "        padding-top: 0;\n",
       "    }\n",
       "\n",
       "    .dataframe td {\n",
       "        padding-bottom: 0;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "<small>shape: (2, 3)</small>\n",
       "<thead>\n",
       "<tr>\n",
       "<th>\n",
       "subjectID\n",
       "</th>\n",
       "<th>\n",
       "diagnosis\n",
       "</th>\n",
       "<th>\n",
       "probability\n",
       "</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>\n",
       "i64\n",
       "</td>\n",
       "<td>\n",
       "str\n",
       "</td>\n",
       "<td>\n",
       "f64\n",
       "</td>\n",
       "</tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "<tr>\n",
       "<td>\n",
       "1\n",
       "</td>\n",
       "<td>\n",
       "&quot;withdrawal beh...\n",
       "</td>\n",
       "<td>\n",
       "0.670622\n",
       "</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>\n",
       "2\n",
       "</td>\n",
       "<td>\n",
       "&quot;positive re-fr...\n",
       "</td>\n",
       "<td>\n",
       "0.558974\n",
       "</td>\n",
       "</tr>\n",
       "</tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "shape: (2, 3)\n",
       "┌───────────┬─────────────────────┬─────────────┐\n",
       "│ subjectID ┆ diagnosis           ┆ probability │\n",
       "│ ---       ┆ ---                 ┆ ---         │\n",
       "│ i64       ┆ str                 ┆ f64         │\n",
       "╞═══════════╪═════════════════════╪═════════════╡\n",
       "│ 1         ┆ withdrawal behavior ┆ 0.670622    │\n",
       "│ 2         ┆ positive re-framing ┆ 0.558974    │\n",
       "└───────────┴─────────────────────┴─────────────┘"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ops.transform(d_local)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "`eval` takes a dictionary of `DataFrame`s (names matching names specified in the pipeline) and returns the result of applying the pipeline to this data.  Currently our `Pandas` and `Polars` implementation only allows very simple window functions.  This is why we didn't write `probability = probability/sum(probability)`, but instead broken the calculation into multiple steps by introducing the `total` column (the `SQL` realization does in fact support more complex window functions).  This is a small issue with the grammar: but our feeling encourage simple steps is in fact a good thing (improves debuggability), and in `SQL` the query optimizers likely optimize the different query styles into very similar realizations anyway.\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Pandas\n",
    "\n",
    "The exact same pipeline can be applied directly to Pandas data frames."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>subjectID</th>\n",
       "      <th>diagnosis</th>\n",
       "      <th>probability</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>withdrawal behavior</td>\n",
       "      <td>0.670622</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>positive re-framing</td>\n",
       "      <td>0.558974</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   subjectID            diagnosis  probability\n",
       "0          1  withdrawal behavior     0.670622\n",
       "1          2  positive re-framing     0.558974"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ops.transform(d_local.to_pandas())"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Piped notation\n",
    "\n",
    "Python doesn't *need* piped notation, as method chaining is available in the language.  However, we have added a \"pipe data into the operator\" notation."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe td {\n",
       "        white-space: pre;\n",
       "    }\n",
       "\n",
       "    .dataframe td {\n",
       "        padding-top: 0;\n",
       "    }\n",
       "\n",
       "    .dataframe td {\n",
       "        padding-bottom: 0;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "<small>shape: (2, 3)</small>\n",
       "<thead>\n",
       "<tr>\n",
       "<th>\n",
       "subjectID\n",
       "</th>\n",
       "<th>\n",
       "diagnosis\n",
       "</th>\n",
       "<th>\n",
       "probability\n",
       "</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>\n",
       "i64\n",
       "</td>\n",
       "<td>\n",
       "str\n",
       "</td>\n",
       "<td>\n",
       "f64\n",
       "</td>\n",
       "</tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "<tr>\n",
       "<td>\n",
       "1\n",
       "</td>\n",
       "<td>\n",
       "&quot;withdrawal beh...\n",
       "</td>\n",
       "<td>\n",
       "0.670622\n",
       "</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>\n",
       "2\n",
       "</td>\n",
       "<td>\n",
       "&quot;positive re-fr...\n",
       "</td>\n",
       "<td>\n",
       "0.558974\n",
       "</td>\n",
       "</tr>\n",
       "</tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "shape: (2, 3)\n",
       "┌───────────┬─────────────────────┬─────────────┐\n",
       "│ subjectID ┆ diagnosis           ┆ probability │\n",
       "│ ---       ┆ ---                 ┆ ---         │\n",
       "│ i64       ┆ str                 ┆ f64         │\n",
       "╞═══════════╪═════════════════════╪═════════════╡\n",
       "│ 1         ┆ withdrawal behavior ┆ 0.670622    │\n",
       "│ 2         ┆ positive re-framing ┆ 0.558974    │\n",
       "└───────────┴─────────────────────┴─────────────┘"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d_local >> ops"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "\n",
    "## Export/Import\n",
    "\n",
    "Because our operator pipeline is a `Python` object with no references to external objects (such as the database connection), it can be saved through standard methods such as \"[pickling](https://docs.python.org/3/library/pickle.html).\""
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "## Some Advantages of `data_algebra`\n",
    "\n",
    "A `data_algebra` operator pipeline carries around usable knowledge of the data transform.\n",
    "\n",
    "For example:\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'d': {'assessmentTotal', 'subjectID', 'surveyCategory'}}"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# report all source table columns used by the query\n",
    "ops.columns_used()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "('subjectID', 'diagnosis', 'probability')"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# what columns does this operation produce?\n",
    "ops.column_names"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Conclusion\n",
    "\n",
    "The `data_algebra` is part of a powerful cross-language and mutli-implementaiton family data manipulation tools. These tools can greatly reduce the development and maintenance cost of data science projects, while improving the documentation of project intent.\n",
    "\n",
    "[Win Vector LLC](http://www.win-vector.com/) is looking for sponsors and partners to further the package.  In particular if your group is using both `R` and `Python` in big-data projects (where `SQL` is a need, including [`Apache Spark`](https://spark.apache.org)), or are porting a project from one of these languages to another- please get in touch.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# be neat\n",
    "db_handle.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note: `mysql` is not fully supported, as it doesn't name quoted common table expression columns in an obvious way. Current primary databases are PostgreSQL, Google Big Query, SparkSQL, and SQLite."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.9.13 ('data_algebra_dev_env')",
   "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"
  },
  "vscode": {
   "interpreter": {
    "hash": "78f2e370b2228f20769c50722e4fc1c000bb9a3ed5301a20de3a1aec2b74b543"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}