Section 04 Joining Tables/20 MySQL Self Join.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "4fe9c00a-a43f-4482-85db-bacd97ddd960",
   "metadata": {
    "tags": [],
    "toc-hr-collapsed": true
   },
   "source": [
    "## **[MySQL Self Join](https://www.mysqltutorial.org/mysql-self-join/)**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a0fa3032-8ccc-4b4e-a404-d4263aa2be98",
   "metadata": {},
   "source": [
    "Joins a table to itself using the inner join or left join."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "abf5727d-e208-4167-809e-611751189226",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import re\n",
    "import matplotlib.pyplot as plt\n",
    "import matplotlib.image as mpimg\n",
    "from IPython.display import Image, SVG\n",
    "\n",
    "from sqlalchemy_utils import database_exists, create_database\n",
    "from sqlalchemy import create_engine, inspect, MetaData, text\n",
    "from sqlalchemy_schemadisplay import create_schema_graph\n",
    "import pymysql\n",
    "\n",
    "pd.set_option(\n",
    "    'display.max_columns', None,\n",
    "    'expand_frame_repr', True,\n",
    "    'display.max_colwidth', None,\n",
    "    'display.max_rows', 10,\n",
    ")\n",
    "\n",
    "pd.set_option('display.width', 65)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "b87d2402-376d-4bcf-84df-295fc4d22692",
   "metadata": {},
   "outputs": [],
   "source": [
    "# connect to the classicmodels database\n",
    "connect_args={'ssl':{'fake_flag_to_enable_tls': True}}\n",
    "\n",
    "engine = create_engine('mysql+pymysql://namlq:abc123@localhost/classicmodels',\n",
    "                       connect_args=connect_args, echo=False\n",
    "                         )\n",
    "inspector = inspect(engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e1ea7b7c-ea2b-4cfa-adb6-a258a81a072f",
   "metadata": {
    "tags": []
   },
   "source": [
    "### MySQL self join examples"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bbc986dd-d8b9-41ea-b915-6d3cc140278a",
   "metadata": {
    "tags": []
   },
   "source": [
    "#### Inspecting left table and right table in pandas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "c90f8ee0-9c2c-417f-b5e2-d48eda9f2eff",
   "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>employeeNumber</th>\n",
       "      <th>lastName</th>\n",
       "      <th>firstName</th>\n",
       "      <th>jobTitle</th>\n",
       "      <th>reportsTo</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1002</td>\n",
       "      <td>Murphy</td>\n",
       "      <td>Diane</td>\n",
       "      <td>President</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1056</td>\n",
       "      <td>Patterson</td>\n",
       "      <td>Mary</td>\n",
       "      <td>VP Sales</td>\n",
       "      <td>1002.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1076</td>\n",
       "      <td>Firrelli</td>\n",
       "      <td>Jeff</td>\n",
       "      <td>VP Marketing</td>\n",
       "      <td>1002.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1088</td>\n",
       "      <td>Patterson</td>\n",
       "      <td>William</td>\n",
       "      <td>Sales Manager (APAC)</td>\n",
       "      <td>1056.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1102</td>\n",
       "      <td>Bondur</td>\n",
       "      <td>Gerard</td>\n",
       "      <td>Sale Manager (EMEA)</td>\n",
       "      <td>1056.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   employeeNumber   lastName firstName              jobTitle  \\\n",
       "0            1002     Murphy     Diane             President   \n",
       "1            1056  Patterson      Mary              VP Sales   \n",
       "2            1076   Firrelli      Jeff          VP Marketing   \n",
       "3            1088  Patterson   William  Sales Manager (APAC)   \n",
       "4            1102     Bondur    Gerard   Sale Manager (EMEA)   \n",
       "\n",
       "   reportsTo  \n",
       "0        NaN  \n",
       "1     1002.0  \n",
       "2     1002.0  \n",
       "3     1056.0  \n",
       "4     1056.0  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "employees = (\n",
    "    pd.read_sql_table('employees', engine)\n",
    "    [['employeeNumber', 'lastName', 'firstName',\n",
    "      'jobTitle', 'reportsTo']]\n",
    ")\n",
    "\n",
    "employees.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "5be9bde6-cf79-4262-bf6d-6c91c4b9d81e",
   "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>directStaff</th>\n",
       "      <th>staffTitle</th>\n",
       "      <th>left_reportsTo</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Murphy Diane</td>\n",
       "      <td>President</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Patterson Mary</td>\n",
       "      <td>VP Sales</td>\n",
       "      <td>1002.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Firrelli Jeff</td>\n",
       "      <td>VP Marketing</td>\n",
       "      <td>1002.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Patterson William</td>\n",
       "      <td>Sales Manager (APAC)</td>\n",
       "      <td>1056.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Bondur Gerard</td>\n",
       "      <td>Sale Manager (EMEA)</td>\n",
       "      <td>1056.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         directStaff            staffTitle  left_reportsTo\n",
       "0       Murphy Diane             President             NaN\n",
       "1     Patterson Mary              VP Sales          1002.0\n",
       "2      Firrelli Jeff          VP Marketing          1002.0\n",
       "3  Patterson William  Sales Manager (APAC)          1056.0\n",
       "4      Bondur Gerard   Sale Manager (EMEA)          1056.0"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# the left table\n",
    "left_table = (\n",
    "    employees\n",
    "    .assign(directStaff = employees.lastName + ' ' + employees.firstName)\n",
    "    .rename(columns={\n",
    "        'jobTitle': 'staffTitle', 'reportsTo': 'left_reportsTo'})\n",
    "    [['directStaff', 'staffTitle', 'left_reportsTo']]\n",
    ")\n",
    "\n",
    "left_table.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "ed85d811-25ba-4d8c-9167-f61c6370d950",
   "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>right_employeeNumber</th>\n",
       "      <th>manager</th>\n",
       "      <th>managerTitle</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1002</td>\n",
       "      <td>Murphy Diane</td>\n",
       "      <td>President</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1056</td>\n",
       "      <td>Patterson Mary</td>\n",
       "      <td>VP Sales</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1076</td>\n",
       "      <td>Firrelli Jeff</td>\n",
       "      <td>VP Marketing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1088</td>\n",
       "      <td>Patterson William</td>\n",
       "      <td>Sales Manager (APAC)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1102</td>\n",
       "      <td>Bondur Gerard</td>\n",
       "      <td>Sale Manager (EMEA)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   right_employeeNumber            manager  \\\n",
       "0                  1002       Murphy Diane   \n",
       "1                  1056     Patterson Mary   \n",
       "2                  1076      Firrelli Jeff   \n",
       "3                  1088  Patterson William   \n",
       "4                  1102      Bondur Gerard   \n",
       "\n",
       "           managerTitle  \n",
       "0             President  \n",
       "1              VP Sales  \n",
       "2          VP Marketing  \n",
       "3  Sales Manager (APAC)  \n",
       "4   Sale Manager (EMEA)  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# the right table\n",
    "right_table = (\n",
    "    employees\n",
    "    .assign(manager = employees.lastName + ' ' + employees.firstName)\n",
    "    .rename(columns={\n",
    "        'jobTitle': 'managerTitle', 'employeeNumber': 'right_employeeNumber'})\n",
    "    [['right_employeeNumber', 'manager', 'managerTitle']]\n",
    ")\n",
    "right_table.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ec9f1139-5836-4f25-8d2f-b1efb80b689a",
   "metadata": {
    "tags": []
   },
   "source": [
    "#### 1) MySQL self join using INNER JOIN clause"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9f8dd339-8cea-4824-9b0e-c136a54c7742",
   "metadata": {},
   "source": [
    "- Self Join to get the whole organization structure: step by step in pandas codes:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "bd26dc05-53a9-4c3a-ba0a-6882a461e30a",
   "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>directStaff</th>\n",
       "      <th>staffTitle</th>\n",
       "      <th>left_reportsTo</th>\n",
       "      <th>right_employeeNumber</th>\n",
       "      <th>manager</th>\n",
       "      <th>managerTitle</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Patterson Mary</td>\n",
       "      <td>VP Sales</td>\n",
       "      <td>1002</td>\n",
       "      <td>1002</td>\n",
       "      <td>Murphy Diane</td>\n",
       "      <td>President</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Firrelli Jeff</td>\n",
       "      <td>VP Marketing</td>\n",
       "      <td>1002</td>\n",
       "      <td>1002</td>\n",
       "      <td>Murphy Diane</td>\n",
       "      <td>President</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Patterson William</td>\n",
       "      <td>Sales Manager (APAC)</td>\n",
       "      <td>1056</td>\n",
       "      <td>1056</td>\n",
       "      <td>Patterson Mary</td>\n",
       "      <td>VP Sales</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Bondur Gerard</td>\n",
       "      <td>Sale Manager (EMEA)</td>\n",
       "      <td>1056</td>\n",
       "      <td>1056</td>\n",
       "      <td>Patterson Mary</td>\n",
       "      <td>VP Sales</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Bow Anthony</td>\n",
       "      <td>Sales Manager (NA)</td>\n",
       "      <td>1056</td>\n",
       "      <td>1056</td>\n",
       "      <td>Patterson Mary</td>\n",
       "      <td>VP Sales</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         directStaff            staffTitle  left_reportsTo  \\\n",
       "0     Patterson Mary              VP Sales            1002   \n",
       "1      Firrelli Jeff          VP Marketing            1002   \n",
       "2  Patterson William  Sales Manager (APAC)            1056   \n",
       "3      Bondur Gerard   Sale Manager (EMEA)            1056   \n",
       "4        Bow Anthony    Sales Manager (NA)            1056   \n",
       "\n",
       "   right_employeeNumber         manager managerTitle  \n",
       "0                  1002    Murphy Diane    President  \n",
       "1                  1002    Murphy Diane    President  \n",
       "2                  1056  Patterson Mary     VP Sales  \n",
       "3                  1056  Patterson Mary     VP Sales  \n",
       "4                  1056  Patterson Mary     VP Sales  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# merge the two tables\n",
    "(pd.merge(\n",
    "    left=left_table, right=right_table, left_on='left_reportsTo',\n",
    "    right_on='right_employeeNumber', how='inner')\n",
    " .assign(left_reportsTo = lambda df: df.left_reportsTo.astype('Int64'))\n",
    " .head()\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a43f86ff-5af8-4cf3-9bf3-7caf5c32ef21",
   "metadata": {},
   "source": [
    "MySQL codes:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "a48f5a7d-edbf-47a8-a336-4c83e4c3a410",
   "metadata": {},
   "outputs": [],
   "source": [
    "# .5 ms ± 310 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)\n",
    "# %%timeit\n",
    "string = '''\n",
    "SELECT \n",
    "    CONCAT(r.lastName, ', ', r.firstName) AS manager,\n",
    "    r.jobTitle AS managerTitle,\n",
    "    CONCAT(l.lastName, ', ', l.firstName) AS 'directStaff',\n",
    "    l.jobTitle As staffTitle\n",
    "FROM employees l\n",
    "INNER JOIN employees r ON r.employeeNumber = l.reportsTo\n",
    "ORDER BY manager, directStaff\n",
    ";'''\n",
    "\n",
    "df1 = pd.read_sql(string, engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "80789abc-7879-474a-b167-de874451302d",
   "metadata": {},
   "source": [
    "Equivalent pandas codes:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "8b3233c0-2fb8-4382-a07d-2948483d8a99",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 37.4 ms ± 1.63 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)\n",
    "# %%timeit\n",
    "employees = pd.read_sql_table('employees', engine)\n",
    "df2 = (\n",
    "    pd.merge(left=employees, right=employees,\n",
    "             left_on='reportsTo', right_on='employeeNumber', \n",
    "             how='inner', suffixes=['_left', '_right'])\n",
    "    .assign(directStaff = lambda df: df['lastName_left'] + ', ' + df['firstName_left'],\n",
    "            manager = lambda df: df['lastName_right'] + ', ' + df['firstName_right'])\n",
    "    .rename(columns={'jobTitle_left': 'staffTitle',\n",
    "                     'jobTitle_right': 'managerTitle'})\n",
    "    [['manager', 'managerTitle', 'directStaff', 'staffTitle']]\n",
    "    .sort_values(by=['manager', 'directStaff'],\n",
    "                 key=lambda col: col.str.upper(),\n",
    "                 ignore_index=True)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "46cfb1a1-10dd-4168-8121-2a9bc269b0fc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.equals(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "a1c3f369-e6f1-4a81-84f8-6f04cb4bc35e",
   "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>manager</th>\n",
       "      <th>managerTitle</th>\n",
       "      <th>directStaff</th>\n",
       "      <th>staffTitle</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bondur, Gerard</td>\n",
       "      <td>Sale Manager (EMEA)</td>\n",
       "      <td>Bondur, Loui</td>\n",
       "      <td>Sales Rep</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Bondur, Gerard</td>\n",
       "      <td>Sale Manager (EMEA)</td>\n",
       "      <td>Bott, Larry</td>\n",
       "      <td>Sales Rep</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Bondur, Gerard</td>\n",
       "      <td>Sale Manager (EMEA)</td>\n",
       "      <td>Castillo, Pamela</td>\n",
       "      <td>Sales Rep</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Bondur, Gerard</td>\n",
       "      <td>Sale Manager (EMEA)</td>\n",
       "      <td>Gerard, Martin</td>\n",
       "      <td>Sales Rep</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Bondur, Gerard</td>\n",
       "      <td>Sale Manager (EMEA)</td>\n",
       "      <td>Hernandez, Gerard</td>\n",
       "      <td>Sales Rep</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          manager         managerTitle        directStaff  \\\n",
       "0  Bondur, Gerard  Sale Manager (EMEA)       Bondur, Loui   \n",
       "1  Bondur, Gerard  Sale Manager (EMEA)        Bott, Larry   \n",
       "2  Bondur, Gerard  Sale Manager (EMEA)   Castillo, Pamela   \n",
       "3  Bondur, Gerard  Sale Manager (EMEA)     Gerard, Martin   \n",
       "4  Bondur, Gerard  Sale Manager (EMEA)  Hernandez, Gerard   \n",
       "\n",
       "  staffTitle  \n",
       "0  Sales Rep  \n",
       "1  Sales Rep  \n",
       "2  Sales Rep  \n",
       "3  Sales Rep  \n",
       "4  Sales Rep  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bb0f636b-868a-439d-9d4c-fb8faaac5ec9",
   "metadata": {
    "tags": []
   },
   "source": [
    "#### 2) MySQL self join using LEFT JOIN clause"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9d6a2661-4959-4b02-9aad-02f8bc189f70",
   "metadata": {},
   "source": [
    "Use the `LEFT JOIN` clause instead of `INNER JOIN` to include the President:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "2f21ba73-8136-4446-9681-2bf1abfd3838",
   "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>directStaff</th>\n",
       "      <th>staffTitle</th>\n",
       "      <th>left_reportsTo</th>\n",
       "      <th>right_employeeNumber</th>\n",
       "      <th>manager</th>\n",
       "      <th>managerTitle</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Murphy Diane</td>\n",
       "      <td>President</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Patterson Mary</td>\n",
       "      <td>VP Sales</td>\n",
       "      <td>1002</td>\n",
       "      <td>1002.0</td>\n",
       "      <td>Murphy Diane</td>\n",
       "      <td>President</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Firrelli Jeff</td>\n",
       "      <td>VP Marketing</td>\n",
       "      <td>1002</td>\n",
       "      <td>1002.0</td>\n",
       "      <td>Murphy Diane</td>\n",
       "      <td>President</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Patterson William</td>\n",
       "      <td>Sales Manager (APAC)</td>\n",
       "      <td>1056</td>\n",
       "      <td>1056.0</td>\n",
       "      <td>Patterson Mary</td>\n",
       "      <td>VP Sales</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Bondur Gerard</td>\n",
       "      <td>Sale Manager (EMEA)</td>\n",
       "      <td>1056</td>\n",
       "      <td>1056.0</td>\n",
       "      <td>Patterson Mary</td>\n",
       "      <td>VP Sales</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         directStaff            staffTitle  left_reportsTo  \\\n",
       "0       Murphy Diane             President            <NA>   \n",
       "1     Patterson Mary              VP Sales            1002   \n",
       "2      Firrelli Jeff          VP Marketing            1002   \n",
       "3  Patterson William  Sales Manager (APAC)            1056   \n",
       "4      Bondur Gerard   Sale Manager (EMEA)            1056   \n",
       "\n",
       "   right_employeeNumber         manager managerTitle  \n",
       "0                   NaN             NaN          NaN  \n",
       "1                1002.0    Murphy Diane    President  \n",
       "2                1002.0    Murphy Diane    President  \n",
       "3                1056.0  Patterson Mary     VP Sales  \n",
       "4                1056.0  Patterson Mary     VP Sales  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# merge the two tables\n",
    "(pd.merge(\n",
    "    left=left_table, right=right_table, left_on='left_reportsTo',\n",
    "    right_on='right_employeeNumber', how='left')\n",
    " .assign(left_reportsTo = lambda df: df.left_reportsTo.astype('Int64'))\n",
    " .head()\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "461c7fd6-ae3f-42c4-a5e1-02f0dd5cac2f",
   "metadata": {},
   "source": [
    "MySQL codes:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "6e009b44-dab6-4677-95bf-1add6af011b1",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 2.18 ms ± 161 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)\n",
    "# %%timeit\n",
    "string = '''\n",
    "SELECT \n",
    "    IFNULL(CONCAT(r.lastName, ', ', r.firstName), 'Administrative Council') AS manager,\n",
    "    IFNULL(r.jobTitle, 'Administrative Council') AS managerTitle,\n",
    "    CONCAT(l.lastName, ', ', l.firstName) AS 'directStaff',\n",
    "    l.jobTitle As staffTitle\n",
    "FROM employees l\n",
    "LEFT JOIN employees r ON r.employeeNumber = l.reportsTo\n",
    "ORDER BY manager, directStaff\n",
    ";'''\n",
    "\n",
    "df1 = pd.read_sql(string, engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "53600e46-9b4d-4930-82a2-2a91c281ae54",
   "metadata": {},
   "source": [
    "pandas codes:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "6af2b6d9-621d-4c31-8a64-e398ad40943d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 39.7 ms ± 2.42 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)\n",
    "# %%timeit\n",
    "employees = pd.read_sql_table('employees', engine)\n",
    "\n",
    "df2 = (\n",
    "    pd.merge(\n",
    "        left=employees, right=employees, left_on='reportsTo', \n",
    "        right_on='employeeNumber', how='left', suffixes=['_left', '_right'])\n",
    "    .assign(manager = lambda df: df['lastName_right'] + ', ' + df['firstName_right'],\n",
    "            directStaff = lambda df: df['lastName_left'] + ', ' + df['firstName_left'])\n",
    "    .rename(columns={'jobTitle_left': 'staffTitle', 'jobTitle_right': 'managerTitle'})\n",
    "    [['manager', 'managerTitle', 'directStaff', 'staffTitle']]\n",
    "    .fillna('Administrative Council')\n",
    "    .sort_values(by=['manager', 'directStaff'], ignore_index=True)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "ba37890e-2bd3-4c45-9af8-4bcd62723c6c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.equals(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "8ce988f3-1612-45f7-9d41-b7bd3075d477",
   "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>manager</th>\n",
       "      <th>managerTitle</th>\n",
       "      <th>directStaff</th>\n",
       "      <th>staffTitle</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Administrative Council</td>\n",
       "      <td>Administrative Council</td>\n",
       "      <td>Murphy, Diane</td>\n",
       "      <td>President</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Bondur, Gerard</td>\n",
       "      <td>Sale Manager (EMEA)</td>\n",
       "      <td>Bondur, Loui</td>\n",
       "      <td>Sales Rep</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Bondur, Gerard</td>\n",
       "      <td>Sale Manager (EMEA)</td>\n",
       "      <td>Bott, Larry</td>\n",
       "      <td>Sales Rep</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Bondur, Gerard</td>\n",
       "      <td>Sale Manager (EMEA)</td>\n",
       "      <td>Castillo, Pamela</td>\n",
       "      <td>Sales Rep</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Bondur, Gerard</td>\n",
       "      <td>Sale Manager (EMEA)</td>\n",
       "      <td>Gerard, Martin</td>\n",
       "      <td>Sales Rep</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  manager            managerTitle  \\\n",
       "0  Administrative Council  Administrative Council   \n",
       "1          Bondur, Gerard     Sale Manager (EMEA)   \n",
       "2          Bondur, Gerard     Sale Manager (EMEA)   \n",
       "3          Bondur, Gerard     Sale Manager (EMEA)   \n",
       "4          Bondur, Gerard     Sale Manager (EMEA)   \n",
       "\n",
       "        directStaff staffTitle  \n",
       "0     Murphy, Diane  President  \n",
       "1      Bondur, Loui  Sales Rep  \n",
       "2       Bott, Larry  Sales Rep  \n",
       "3  Castillo, Pamela  Sales Rep  \n",
       "4    Gerard, Martin  Sales Rep  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "348bca32-234a-4b8c-b977-679a8825316d",
   "metadata": {
    "tags": []
   },
   "source": [
    "#### 3) Using MySQL self join to compare successive rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "43ec1484-9e5b-4e93-83d6-1c392a212184",
   "metadata": {},
   "outputs": [],
   "source": [
    "customers = pd.read_sql_table('customers', engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "cf83e660-e752-456a-a510-b48940ae4d98",
   "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>customerName</th>\n",
       "      <th>city</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Warburg Exchange</td>\n",
       "      <td>Aachen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Diecast Classics Inc.</td>\n",
       "      <td>Allentown</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Schuyler Imports</td>\n",
       "      <td>Amsterdam</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>GiftsForHim.com</td>\n",
       "      <td>Auckland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Down Under Souveniers, Inc</td>\n",
       "      <td>Auckland</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 customerName        city\n",
       "0            Warburg Exchange      Aachen\n",
       "1       Diecast Classics Inc.   Allentown\n",
       "2            Schuyler Imports   Amsterdam\n",
       "3             GiftsForHim.com    Auckland\n",
       "4  Down Under Souveniers, Inc  Auckland  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(customers\n",
    " [['customerName', 'city']]\n",
    " .sort_values('city', ignore_index=True)\n",
    " .head()\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "63f4511c-26b2-4229-8c7b-e15cadf47cf4",
   "metadata": {},
   "outputs": [],
   "source": [
    "string = '''\n",
    "SELECT\n",
    "    l.city,\n",
    "    l.customerName AS customerName_left,\n",
    "    r.customerName AS customerName_right\n",
    "FROM customers l\n",
    "INNER JOIN customers r\n",
    "    ON l.city = r.city \n",
    "    AND l.customerName > r.customerName\n",
    "ORDER BY\n",
    "    city, customerName_left, customerName_right\n",
    ";'''\n",
    "\n",
    "df1 = pd.read_sql(string, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "e7f49042-1f16-4c90-ae5b-1c2d670e8acc",
   "metadata": {},
   "outputs": [],
   "source": [
    "customers = (\n",
    "    pd.read_sql_table('customers', engine)\n",
    "    .assign(city = lambda df: df.city.str.strip())\n",
    ")\n",
    "\n",
    "df2 = (\n",
    "    pd.merge(left=customers, right=customers, on='city',\n",
    "             how='inner', suffixes=['_left', '_right'])\n",
    "    .query('customerName_left > customerName_right')\n",
    "    [['city', 'customerName_left', 'customerName_right']]\n",
    "    .sort_values(['city', 'customerName_left', 'customerName_right'],\n",
    "                 ignore_index=True, key = lambda col: col.str.upper())\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "fc89c695-2423-4a48-b686-3b00ba668e5c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(df1\n",
    " .assign(city = df1.city.str.strip())\n",
    " .equals(df2)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "d78097d6-f173-4cfc-aa9f-43a35b1eadee",
   "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>city</th>\n",
       "      <th>customerName_left</th>\n",
       "      <th>customerName_right</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Auckland</td>\n",
       "      <td>GiftsForHim.com</td>\n",
       "      <td>Down Under Souveniers, Inc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Auckland</td>\n",
       "      <td>Kelly's Gift Shop</td>\n",
       "      <td>Down Under Souveniers, Inc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Auckland</td>\n",
       "      <td>Kelly's Gift Shop</td>\n",
       "      <td>GiftsForHim.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Boston</td>\n",
       "      <td>Gifts4AllAges.com</td>\n",
       "      <td>Diecast Collectables</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Brickhaven</td>\n",
       "      <td>Collectables For Less Inc.</td>\n",
       "      <td>Auto-Moto Classics Inc.</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         city           customerName_left  \\\n",
       "0    Auckland             GiftsForHim.com   \n",
       "1  Auckland             Kelly's Gift Shop   \n",
       "2  Auckland             Kelly's Gift Shop   \n",
       "3      Boston           Gifts4AllAges.com   \n",
       "4  Brickhaven  Collectables For Less Inc.   \n",
       "\n",
       "           customerName_right  \n",
       "0  Down Under Souveniers, Inc  \n",
       "1  Down Under Souveniers, Inc  \n",
       "2             GiftsForHim.com  \n",
       "3        Diecast Collectables  \n",
       "4     Auto-Moto Classics Inc.  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.head()"
   ]
  }
 ],
 "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.9.15"
  },
  "toc-autonumbering": true,
  "toc-showmarkdowntxt": false,
  "toc-showtags": false
 },
 "nbformat": 4,
 "nbformat_minor": 5
}