vectorization05a.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "bb260129f63ae10c",
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "source": [
    "# introduction to the vectorizing principle\n",
    "\n",
    "## part 5a: generating SQL and dataframe transformation code with one syntax\n",
    "\n",
    "While pandas is the most popular library for describing data transformations in python, there are more which you will also see in following parts. Relational databases are also a means of highly efficient processing of tabular data. They are typically instructed with a declarative language called SQL which allows for highly sophisticated query engines to first plan how to execute the query. From a large search space of possible execution plans they chose the supposedly best one by using computation cost estimators.\n",
    "\n",
    "For having a quick look at the data or for searching bugs in previously executed transformation code, it is highly efficient to write ad-hoc SQL scripts by hand. However, for better software engineering practices such as testing, programmatically creating SQL is the preferred option. In fact, it would be ideal to have one syntax from which both dataframe transformation code and SQL can be generated. This is what we show in this part.\n",
    "\n",
    "The library [pydiverse.transform](https://github.com/pydiverse/pydiverse.transform/) is used here even though it is early stage and still lacks essential features. It is intended as a proof of concept for this idea.\n",
    "\n",
    "Pandas uses the fluent interface technique chaining functional methods with multiple function calls: `df.assign(x=lambda df: df.int_col*2).loc[lambda df: df.x == 4]`\n",
    "\n",
    "pydiverse.transform uses the pipe syntax instead, similar to its precursors [dplyr from R](https://dplyr.tidyverse.org/) and [siuba](https://siuba.org/): `df >> mutate(x=λ.int_col*2) >> filter(λ.x == 4)`\n",
    "\n",
    "pydiverse.transform also supports creating custom verbs with the `@verb` decorator which then can be used within a pipe just like builtin verbs such as `mutate` or `filter`.\n",
    "\n",
    "Detail: In SQL, joining a table does not add any new columns to the select statement. It rather brings a new table within the namespace that can be used for creating select columns. pydiverse.transform supports this feature by dropping columns with an empty `select()` statement which does not remove them from the namespace for further use in `mutate()` statements.\n",
    "\n",
    "#### let's get started:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "d8d7eadc",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-08-16T12:19:36.607327005Z",
     "start_time": "2023-08-16T12:19:36.534852624Z"
    }
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import sqlalchemy as sa\n",
    "\n",
    "from pydiverse.transform import λ, Table, verb\n",
    "from pydiverse.transform.core.dtypes import String\n",
    "from pydiverse.transform.lazy.sql_table import SQLTableImpl\n",
    "from pydiverse.transform.eager.pandas_table import PandasTableImpl\n",
    "\n",
    "from pydiverse.transform.core.alignment import aligned, eval_aligned\n",
    "from pydiverse.transform.core.verbs import alias, arrange, collect, filter, group_by, join, mutate, select, summarise, show_query, ungroup, left_join"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "a19125f5",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-08-16T12:19:36.724297475Z",
     "start_time": "2023-08-16T12:19:36.553213245Z"
    }
   },
   "outputs": [],
   "source": [
    "# Load data...\n",
    "titanic_dataset = pd.read_csv('https://raw.githubusercontent.com/mwaskom/seaborn-data/master/titanic.csv')\n",
    "\n",
    "engine = sa.create_engine('sqlite:///:memory:')\n",
    "titanic_dataset.to_sql('titanic', engine, index = False, if_exists = 'replace')\n",
    "\n",
    "# Create table objects\n",
    "titanic_p = Table(PandasTableImpl('titanic', titanic_dataset))\n",
    "titanic_s = Table(SQLTableImpl(engine, 'titanic'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "55359784",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-08-16T12:19:36.761435916Z",
     "start_time": "2023-08-16T12:19:36.739616092Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "Table <code>titanic</code> using <code>PandasTableImpl</code> backend:</br><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>survived</th>\n",
       "      <th>pclass</th>\n",
       "      <th>sex</th>\n",
       "      <th>age</th>\n",
       "      <th>sibsp</th>\n",
       "      <th>parch</th>\n",
       "      <th>fare</th>\n",
       "      <th>embarked</th>\n",
       "      <th>class</th>\n",
       "      <th>who</th>\n",
       "      <th>adult_male</th>\n",
       "      <th>deck</th>\n",
       "      <th>embark_town</th>\n",
       "      <th>alive</th>\n",
       "      <th>alone</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>male</td>\n",
       "      <td>22.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>7.25</td>\n",
       "      <td>S</td>\n",
       "      <td>Third</td>\n",
       "      <td>man</td>\n",
       "      <td>True</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>Southampton</td>\n",
       "      <td>no</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>female</td>\n",
       "      <td>38.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>71.2833</td>\n",
       "      <td>C</td>\n",
       "      <td>First</td>\n",
       "      <td>woman</td>\n",
       "      <td>False</td>\n",
       "      <td>C</td>\n",
       "      <td>Cherbourg</td>\n",
       "      <td>yes</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>female</td>\n",
       "      <td>26.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>7.925</td>\n",
       "      <td>S</td>\n",
       "      <td>Third</td>\n",
       "      <td>woman</td>\n",
       "      <td>False</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>Southampton</td>\n",
       "      <td>yes</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>female</td>\n",
       "      <td>35.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>53.1</td>\n",
       "      <td>S</td>\n",
       "      <td>First</td>\n",
       "      <td>woman</td>\n",
       "      <td>False</td>\n",
       "      <td>C</td>\n",
       "      <td>Southampton</td>\n",
       "      <td>yes</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>male</td>\n",
       "      <td>35.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>8.05</td>\n",
       "      <td>S</td>\n",
       "      <td>Third</td>\n",
       "      <td>man</td>\n",
       "      <td>True</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>Southampton</td>\n",
       "      <td>no</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>886</th>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>male</td>\n",
       "      <td>27.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>S</td>\n",
       "      <td>Second</td>\n",
       "      <td>man</td>\n",
       "      <td>True</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>Southampton</td>\n",
       "      <td>no</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>887</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>female</td>\n",
       "      <td>19.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>S</td>\n",
       "      <td>First</td>\n",
       "      <td>woman</td>\n",
       "      <td>False</td>\n",
       "      <td>B</td>\n",
       "      <td>Southampton</td>\n",
       "      <td>yes</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>888</th>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>female</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>23.45</td>\n",
       "      <td>S</td>\n",
       "      <td>Third</td>\n",
       "      <td>woman</td>\n",
       "      <td>False</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>Southampton</td>\n",
       "      <td>no</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>889</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>male</td>\n",
       "      <td>26.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>C</td>\n",
       "      <td>First</td>\n",
       "      <td>man</td>\n",
       "      <td>True</td>\n",
       "      <td>C</td>\n",
       "      <td>Cherbourg</td>\n",
       "      <td>yes</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>890</th>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>male</td>\n",
       "      <td>32.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>7.75</td>\n",
       "      <td>Q</td>\n",
       "      <td>Third</td>\n",
       "      <td>man</td>\n",
       "      <td>True</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>Queenstown</td>\n",
       "      <td>no</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>891 rows × 15 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "Table: titanic, backend: PandasTableImpl\n",
       "     survived  pclass     sex   age  sibsp  parch     fare embarked   class  \\\n",
       "0           0       3    male  22.0      1      0     7.25        S   Third   \n",
       "1           1       1  female  38.0      1      0  71.2833        C   First   \n",
       "2           1       3  female  26.0      0      0    7.925        S   Third   \n",
       "3           1       1  female  35.0      1      0     53.1        S   First   \n",
       "4           0       3    male  35.0      0      0     8.05        S   Third   \n",
       "..        ...     ...     ...   ...    ...    ...      ...      ...     ...   \n",
       "886         0       2    male  27.0      0      0     13.0        S  Second   \n",
       "887         1       1  female  19.0      0      0     30.0        S   First   \n",
       "888         0       3  female  <NA>      1      2    23.45        S   Third   \n",
       "889         1       1    male  26.0      0      0     30.0        C   First   \n",
       "890         0       3    male  32.0      0      0     7.75        Q   Third   \n",
       "\n",
       "       who  adult_male  deck  embark_town alive  alone  \n",
       "0      man        True  <NA>  Southampton    no  False  \n",
       "1    woman       False     C    Cherbourg   yes  False  \n",
       "2    woman       False  <NA>  Southampton   yes   True  \n",
       "3    woman       False     C  Southampton   yes  False  \n",
       "4      man        True  <NA>  Southampton    no   True  \n",
       "..     ...         ...   ...          ...   ...    ...  \n",
       "886    man        True  <NA>  Southampton    no   True  \n",
       "887  woman       False     B  Southampton   yes   True  \n",
       "888  woman       False  <NA>  Southampton    no  False  \n",
       "889    man        True     C    Cherbourg   yes   True  \n",
       "890    man        True  <NA>   Queenstown    no   True  \n",
       "\n",
       "[891 rows x 15 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Choose a backend ...\n",
    "# titanic_p -> Pandas\n",
    "# titanic_s -> SQLite\n",
    "titanic = titanic_p\n",
    "\n",
    "# ... and preview it\n",
    "titanic"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "8b3b6e93",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-08-16T12:19:36.826992901Z",
     "start_time": "2023-08-16T12:19:36.765456070Z"
    }
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/martin/micromamba/envs/vectorization/lib/python3.11/site-packages/pydiverse/transform/eager/pandas_table.py:244: FutureWarning: DataFrameGroupBy.grouper is deprecated and will be removed in a future version of pandas.\n",
      "  grouper = self.grouped_df().grouper if self.grouped_by else None\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "Table <code>titanic</code> using <code>PandasTableImpl</code> backend:</br><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>who</th>\n",
       "      <th>survived</th>\n",
       "      <th>avg_fare</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>child</td>\n",
       "      <td>0</td>\n",
       "      <td>32.633703</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>child</td>\n",
       "      <td>1</td>\n",
       "      <td>32.891329</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>man</td>\n",
       "      <td>0</td>\n",
       "      <td>21.490736</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>man</td>\n",
       "      <td>1</td>\n",
       "      <td>42.076422</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>woman</td>\n",
       "      <td>0</td>\n",
       "      <td>20.967174</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>woman</td>\n",
       "      <td>1</td>\n",
       "      <td>54.813801</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Table: titanic, backend: PandasTableImpl\n",
       "     who  survived   avg_fare\n",
       "0  child         0  32.633703\n",
       "1  child         1  32.891329\n",
       "2    man         0  21.490736\n",
       "3    man         1  42.076422\n",
       "4  woman         0  20.967174\n",
       "5  woman         1  54.813801"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Let's look at the average fare people payed that survived vs those that didn't survive\n",
    "who_survived_fare = (titanic\n",
    "    >> group_by(λ.who, λ.survived)\n",
    "    >> summarise(avg_fare = λ.fare.mean())\n",
    ")\n",
    "\n",
    "who_survived_fare"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "0e0a4d6d",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-08-16T12:19:36.827651524Z",
     "start_time": "2023-08-16T12:19:36.808570Z"
    },
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Table: dead, backend: PandasTableImpl\n",
      "     who  survived   avg_fare\n",
      "0  child         0  32.633703\n",
      "2    man         0  21.490736\n",
      "4  woman         0  20.967174\n",
      "\n",
      "Table: alive, backend: PandasTableImpl\n",
      "     who  survived   avg_fare\n",
      "1  child         1  32.891329\n",
      "3    man         1  42.076422\n",
      "5  woman         1  54.813801\n"
     ]
    }
   ],
   "source": [
    "# We now can analyze how much more each category of person would have had to pay to survive.\n",
    "# For that we split our table into two seperate taples representing the two different survival outcomes.\n",
    "dead  = who_survived_fare >> filter(λ.survived == 0) >> alias('dead')\n",
    "alive = who_survived_fare >> filter(λ.survived == 1) >> alias('alive')\n",
    "\n",
    "print(dead)\n",
    "print()\n",
    "print(alive)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "0fbeab4f",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-08-16T12:19:36.924836820Z",
     "start_time": "2023-08-16T12:19:36.852414205Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "Table <code>dead</code> using <code>PandasTableImpl</code> backend:</br><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>who</th>\n",
       "      <th>price_to_survive</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>child</td>\n",
       "      <td>0.257626</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>man</td>\n",
       "      <td>20.585686</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>woman</td>\n",
       "      <td>33.846627</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Table: dead, backend: PandasTableImpl\n",
       "     who  price_to_survive\n",
       "0  child          0.257626\n",
       "2    man         20.585686\n",
       "4  woman         33.846627"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# This is the SQL style of this ...  (with an explicit join)\n",
    "\n",
    "price_to_survive_lazy = (dead\n",
    " >> left_join(alive, dead.who == alive.who, validate='1:?')\n",
    " >> select(λ.who)\n",
    " >> mutate(price_to_survive = alive.avg_fare - dead.avg_fare))\n",
    "\n",
    "price_to_survive_lazy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "6023960f",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-08-16T12:19:36.925672083Z",
     "start_time": "2023-08-16T12:19:36.852931752Z"
    },
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "Table <code>dead</code> using <code>PandasTableImpl</code> backend:</br><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>who</th>\n",
       "      <th>price_to_survive</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>child</td>\n",
       "      <td>0.257626</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>man</td>\n",
       "      <td>20.585686</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>woman</td>\n",
       "      <td>33.846627</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Table: dead, backend: PandasTableImpl\n",
       "     who  price_to_survive\n",
       "0  child          0.257626\n",
       "2    man         20.585686\n",
       "4  woman         33.846627"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# ... but we can also do it in a more dataframe style (assuming aligned row ordering).\n",
    "\n",
    "# Because we know that the 'dead' and the 'alive' table are aligned with each\n",
    "# other (they have the same order and same number of rows), we can perform\n",
    "# row wise operations on their columns without explicitly joining the two tables.\n",
    "\n",
    "# The downside of this approach is that it only works in some limited\n",
    "# cases with the SQL backend.\n",
    "\n",
    "price_to_survive = eval_aligned(\n",
    "    alive.avg_fare - dead.avg_fare\n",
    ")\n",
    "\n",
    "# We can then add this new column back to our dataframe.\n",
    "dead >> select(λ.who) >> mutate(price_to_survive = price_to_survive)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "8c55fb8f",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-08-16T12:19:36.927272716Z",
     "start_time": "2023-08-16T12:19:36.896426547Z"
    }
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/martin/micromamba/envs/vectorization/lib/python3.11/site-packages/pydiverse/transform/eager/pandas_table.py:244: FutureWarning: DataFrameGroupBy.grouper is deprecated and will be removed in a future version of pandas.\n",
      "  grouper = self.grouped_df().grouper if self.grouped_by else None\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "Table <code>titanic</code> using <code>PandasTableImpl</code> backend:</br><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>age_bucket</th>\n",
       "      <th>samples</th>\n",
       "      <th>survival_likelyhood</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10.0</td>\n",
       "      <td>64</td>\n",
       "      <td>0.59375</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>20.0</td>\n",
       "      <td>115</td>\n",
       "      <td>0.382609</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>30.0</td>\n",
       "      <td>230</td>\n",
       "      <td>0.365217</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>40.0</td>\n",
       "      <td>155</td>\n",
       "      <td>0.445161</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>50.0</td>\n",
       "      <td>86</td>\n",
       "      <td>0.383721</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>60.0</td>\n",
       "      <td>42</td>\n",
       "      <td>0.404762</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>70.0</td>\n",
       "      <td>17</td>\n",
       "      <td>0.235294</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>80.0</td>\n",
       "      <td>5</td>\n",
       "      <td>0.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>0</td>\n",
       "      <td>0.293785</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Table: titanic, backend: PandasTableImpl\n",
       "   age_bucket  samples  survival_likelyhood\n",
       "0        10.0       64              0.59375\n",
       "1        20.0      115             0.382609\n",
       "2        30.0      230             0.365217\n",
       "3        40.0      155             0.445161\n",
       "4        50.0       86             0.383721\n",
       "5        60.0       42             0.404762\n",
       "6        70.0       17             0.235294\n",
       "7        80.0        5                  0.2\n",
       "8        <NA>        0             0.293785"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Alternatively we can also look at how likely a person was to survive based on their age.\n",
    "survival_by_age = (titanic\n",
    " >> mutate(age_bucket = round(λ.age + 4.999, -1))\n",
    " >> group_by(λ.age_bucket)\n",
    " >> summarise(samples=λ.age_bucket.count(), survival_likelyhood=λ.survived.mean())\n",
    " >> arrange(λ.age_bucket))\n",
    "\n",
    "# As you can see, children ages 0 to 10 were most and people between 70 and 80 were \n",
    "# least likely to survive.\n",
    "survival_by_age"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "1a75674d",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-08-16T12:19:36.927555116Z",
     "start_time": "2023-08-16T12:19:36.896939386Z"
    }
   },
   "outputs": [],
   "source": [
    "# So far we've been using the pandas as the backend for our calculations.\n",
    "# You can now go back to the third cell of this notebook and replace\n",
    "# `titanic = titanic_p` with `titanic = titanic_s` to switch to the \n",
    "# SQLite backand and then re-run all the cells.\n",
    "\n",
    "# You will see that everything (with the exception of the eager style\n",
    "# aligned evaluation) still works and that it produces exactly the same\n",
    "# output.\n",
    "\n",
    "# Additionally, we now can display the appropriate SQL query that correspond\n",
    "# to some of our calculations."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "55dbb381",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-08-16T12:19:36.928345503Z",
     "start_time": "2023-08-16T12:19:36.897195904Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "No query to show for PandasTableImpl\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "Table <code>dead</code> using <code>PandasTableImpl</code> backend:</br><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>who</th>\n",
       "      <th>price_to_survive</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>child</td>\n",
       "      <td>0.257626</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>man</td>\n",
       "      <td>20.585686</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>woman</td>\n",
       "      <td>33.846627</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Table: dead, backend: PandasTableImpl\n",
       "     who  price_to_survive\n",
       "0  child          0.257626\n",
       "2    man         20.585686\n",
       "4  woman         33.846627"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "price_to_survive_lazy >> show_query()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "324b8680",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-08-16T12:19:37.006336226Z",
     "start_time": "2023-08-16T12:19:36.940484869Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "No query to show for PandasTableImpl\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "Table <code>titanic</code> using <code>PandasTableImpl</code> backend:</br><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>age_bucket</th>\n",
       "      <th>samples</th>\n",
       "      <th>survival_likelyhood</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10.0</td>\n",
       "      <td>64</td>\n",
       "      <td>0.59375</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>20.0</td>\n",
       "      <td>115</td>\n",
       "      <td>0.382609</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>30.0</td>\n",
       "      <td>230</td>\n",
       "      <td>0.365217</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>40.0</td>\n",
       "      <td>155</td>\n",
       "      <td>0.445161</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>50.0</td>\n",
       "      <td>86</td>\n",
       "      <td>0.383721</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>60.0</td>\n",
       "      <td>42</td>\n",
       "      <td>0.404762</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>70.0</td>\n",
       "      <td>17</td>\n",
       "      <td>0.235294</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>80.0</td>\n",
       "      <td>5</td>\n",
       "      <td>0.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>0</td>\n",
       "      <td>0.293785</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Table: titanic, backend: PandasTableImpl\n",
       "   age_bucket  samples  survival_likelyhood\n",
       "0        10.0       64              0.59375\n",
       "1        20.0      115             0.382609\n",
       "2        30.0      230             0.365217\n",
       "3        40.0      155             0.445161\n",
       "4        50.0       86             0.383721\n",
       "5        60.0       42             0.404762\n",
       "6        70.0       17             0.235294\n",
       "7        80.0        5                  0.2\n",
       "8        <NA>        0             0.293785"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "survival_by_age >> show_query()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "76193e6d",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-08-16T12:19:37.007202884Z",
     "start_time": "2023-08-16T12:19:36.940803885Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Original Table\n",
      "--------------\n",
      "Table: str_tbl, backend: PandasTableImpl\n",
      "  col1 col2  some_other_col\n",
      "0    a  qua               1\n",
      "1  b     nt               2\n",
      "2   c   co                3\n",
      "\n",
      "Trimmed Table\n",
      "-------------\n",
      "Table: str_tbl, backend: PandasTableImpl\n",
      "  col1 col2  some_other_col\n",
      "0    a  qua               1\n",
      "1    b   nt               2\n",
      "2    c   co               3\n",
      "\n",
      "Joined Table\n",
      "------------\n",
      "Table: str_tbl, backend: PandasTableImpl\n",
      "  col1     col2\n",
      "0  abc  quantco\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# It's also super easy to define custom verbs.\n",
    "# By adding the @verb decorator to a functions it can be used inside a pipe\n",
    "# just like any of the builtin verbs.\n",
    "\n",
    "# In the following example we want to take a table, trim all leading and\n",
    "# trailing whitespace from all string columns and then concatenate them\n",
    "# along the column axis.\n",
    "\n",
    "@verb\n",
    "def trim_all_str(tbl):\n",
    "    for col in tbl:\n",
    "        if isinstance(col._.dtype, String):\n",
    "            tbl[col] = col.strip()\n",
    "    return tbl\n",
    "\n",
    "@verb\n",
    "def join_all_str(tbl, seperator=''):\n",
    "    return tbl >> summarise(**{\n",
    "        col._.name: col.join(seperator)\n",
    "        for col in tbl if isinstance(col._.dtype, String)\n",
    "    })\n",
    "\n",
    "\n",
    "# Create table and transform using our custom verbs.\n",
    "\n",
    "str_df = pd.DataFrame({\n",
    "    'col1': ['  a', 'b  ', ' c '],\n",
    "    'col2': ['qua', ' nt', 'co '],\n",
    "    \n",
    "    'some_other_col': [1, 2, 3],\n",
    "})\n",
    "\n",
    "str_tbl = Table(PandasTableImpl('str_tbl', str_df))\n",
    "trimmed_tbl = str_tbl >> trim_all_str()\n",
    "joined_tbl = trimmed_tbl >> join_all_str()\n",
    "\n",
    "print(f\"\"\"\n",
    "Original Table\n",
    "--------------\n",
    "{str_tbl}\n",
    "\n",
    "Trimmed Table\n",
    "-------------\n",
    "{trimmed_tbl}\n",
    "\n",
    "Joined Table\n",
    "------------\n",
    "{joined_tbl}\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "98ecc13a9ce8034f",
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "source": [
    "Next: [vectorization05b.ipynb](vectorization05b.ipynb): generating SQL and dataframe transformation code with one syntax - an example pipeline"
   ]
  }
 ],
 "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.11.9"
  },
  "widgets": {
   "application/vnd.jupyter.widget-state+json": {
    "state": {},
    "version_major": 2,
    "version_minor": 0
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}