SQL_in_Pandas.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "44bb3a56-132d-4c25-b19e-32c23e5de574",
   "metadata": {},
   "source": [
    "# SQL Queries in Pandas Dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "e5a82fb8-079c-47a6-98a9-a557218089dc",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Collecting package metadata (current_repodata.json): done\n",
      "Solving environment: done\n",
      "\n",
      "## Package Plan ##\n",
      "\n",
      "  environment location: /Users/zoumanakeita/opt/anaconda3\n",
      "\n",
      "  added / updated specs:\n",
      "    - pandasql\n",
      "\n",
      "\n",
      "The following packages will be downloaded:\n",
      "\n",
      "    package                    |            build\n",
      "    ---------------------------|-----------------\n",
      "    pandasql-0.7.3             |   py37hecd8cb5_1         319 KB\n",
      "    ------------------------------------------------------------\n",
      "                                           Total:         319 KB\n",
      "\n",
      "The following NEW packages will be INSTALLED:\n",
      "\n",
      "  pandasql           pkgs/main/osx-64::pandasql-0.7.3-py37hecd8cb5_1\n",
      "\n",
      "\n",
      "\n",
      "Downloading and Extracting Packages\n",
      "pandasql-0.7.3       | 319 KB    | ##################################### | 100% \n",
      "Preparing transaction: done\n",
      "Verifying transaction: done\n",
      "Executing transaction: done\n"
     ]
    }
   ],
   "source": [
    "# Doing this process you will get stuck for ever\n",
    "#!conda install pandasql\n",
    "\n",
    "\n",
    "# You can solve it by using the --yes parameter. \n",
    "\n",
    "!conda install --yes pandasql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "de6b07b0-b3a7-4e48-ad3d-136286800da8",
   "metadata": {},
   "outputs": [],
   "source": [
    "from pandasql import sqldf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "fddf9445-f55c-421b-9542-93e6296e4867",
   "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>Students</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Age</th>\n",
       "      <th>Email</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Sira</td>\n",
       "      <td>Female</td>\n",
       "      <td>18</td>\n",
       "      <td>sira@info.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Ibrahim</td>\n",
       "      <td>Male</td>\n",
       "      <td>27</td>\n",
       "      <td>ib@info.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Moussa</td>\n",
       "      <td>Male</td>\n",
       "      <td>19</td>\n",
       "      <td>mouss@info.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Mamadou</td>\n",
       "      <td>Male</td>\n",
       "      <td>22</td>\n",
       "      <td>mam@info.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Nabintou</td>\n",
       "      <td>Female</td>\n",
       "      <td>21</td>\n",
       "      <td>nab@info.com</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Students  Gender  Age           Email\n",
       "0      Sira  Female   18   sira@info.com\n",
       "1   Ibrahim    Male   27     ib@info.com\n",
       "2    Moussa    Male   19  mouss@info.com\n",
       "3   Mamadou    Male   22    mam@info.com\n",
       "4  Nabintou  Female   21    nab@info.com"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# Create Student Data Frame\n",
    "students= {\n",
    "    'Students':[\"Sira\",\"Ibrahim\",\"Moussa\",\"Mamadou\",\"Nabintou\"],\n",
    "    'Gender':['Female','Male','Male', \"Male\", \"Female\"],\n",
    "    'Age':[18, 27, 19, 22, 21],\n",
    "    'Email': [\"sira@info.com\", \"ib@info.com\", \"mouss@info.com\", \n",
    "             \"mam@info.com\", \"nab@info.com\"]\n",
    "          }\n",
    "students_df = pd.DataFrame(students)\n",
    "\n",
    "students_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "a946a286-edec-4a7a-baff-8717a7f68b51",
   "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>Teacher</th>\n",
       "      <th>Email</th>\n",
       "      <th>Degree</th>\n",
       "      <th>Department</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Ibrahim</td>\n",
       "      <td>ib@info.com</td>\n",
       "      <td>M.S in Data Science</td>\n",
       "      <td>Business</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Nabintou</td>\n",
       "      <td>nab@info.com</td>\n",
       "      <td>B.S in Statistics</td>\n",
       "      <td>Statistics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Mamadou</td>\n",
       "      <td>mam@info.com</td>\n",
       "      <td>B. Comp Sc</td>\n",
       "      <td>Comp Sc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Fatim</td>\n",
       "      <td>fat@info.com</td>\n",
       "      <td>M.S. Architecture</td>\n",
       "      <td>Engineering</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Aziz</td>\n",
       "      <td>aziz@info.com</td>\n",
       "      <td>B.S in Accounting</td>\n",
       "      <td>Business</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Teacher          Email               Degree   Department\n",
       "0   Ibrahim    ib@info.com  M.S in Data Science     Business\n",
       "1  Nabintou   nab@info.com    B.S in Statistics   Statistics\n",
       "2   Mamadou   mam@info.com           B. Comp Sc      Comp Sc\n",
       "3     Fatim   fat@info.com    M.S. Architecture  Engineering\n",
       "4      Aziz  aziz@info.com    B.S in Accounting     Business"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create Teaching Assistant Data Frame\n",
    "teaching_assistant= {\n",
    "    'Teacher':[\"Ibrahim\",\"Nabintou\",\"Mamadou\",\"Fatim\",\"Aziz\"],\n",
    "    'Email':['ib@info.com','nab@info.com','mam@info.com', \n",
    "             \"fat@info.com\", \"aziz@info.com\"],\n",
    "    'Degree':[\"M.S in Data Science\", \"B.S in Statistics\", \n",
    "              \"B. Comp Sc\", \"M.S. Architecture\", \"B.S in Accounting\"],\n",
    "    'Department': [\"Business\", \"Statistics\", \"Comp Sc\", \n",
    "             \"Engineering\", \"Business\"]\n",
    "          }\n",
    "teaching_assistant_df = pd.DataFrame(teaching_assistant)\n",
    "\n",
    "teaching_assistant_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e79e34dc-a4f2-43ff-8c17-32ba8e504ba7",
   "metadata": {},
   "source": [
    "## 1. Data Selection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "ff745804-adad-4328-983c-78476fb24a5e",
   "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>Students</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Age</th>\n",
       "      <th>Email</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Sira</td>\n",
       "      <td>Female</td>\n",
       "      <td>18</td>\n",
       "      <td>sira@info.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Ibrahim</td>\n",
       "      <td>Male</td>\n",
       "      <td>27</td>\n",
       "      <td>ib@info.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Moussa</td>\n",
       "      <td>Male</td>\n",
       "      <td>19</td>\n",
       "      <td>mouss@info.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Mamadou</td>\n",
       "      <td>Male</td>\n",
       "      <td>22</td>\n",
       "      <td>mam@info.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Nabintou</td>\n",
       "      <td>Female</td>\n",
       "      <td>21</td>\n",
       "      <td>nab@info.com</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Students  Gender  Age           Email\n",
       "0      Sira  Female   18   sira@info.com\n",
       "1   Ibrahim    Male   27     ib@info.com\n",
       "2    Moussa    Male   19  mouss@info.com\n",
       "3   Mamadou    Male   22    mam@info.com\n",
       "4  Nabintou  Female   21    nab@info.com"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# We can get all the columns data as follow\n",
    "all_students = sqldf(\"SELECT * FROM students_df\")\n",
    "all_students"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "78b60f88-6513-4294-9ea6-5c720ff76113",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "------------------------------\n",
      "Students    object\n",
      "Gender      object\n",
      "Age          int64\n",
      "Email       object\n",
      "dtype: object\n"
     ]
    }
   ],
   "source": [
    "print(type(all_students))\n",
    "print(\"---\"*10)\n",
    "print(all_students.dtypes)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "8052de28-a746-4433-81f6-0d73ac4512f2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pandas.core.frame.DataFrame"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(all_cols)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "89b3c06a-3261-4868-ba54-d327152c0516",
   "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>Students</th>\n",
       "      <th>Email</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Sira</td>\n",
       "      <td>sira@info.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Ibrahim</td>\n",
       "      <td>ib@info.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Moussa</td>\n",
       "      <td>mouss@info.com</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Students           Email\n",
       "0     Sira   sira@info.com\n",
       "1  Ibrahim     ib@info.com\n",
       "2   Moussa  mouss@info.com"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Select the Student Name, and Email\n",
    "\n",
    "query = \"\"\" SELECT Students, Email \n",
    "            FROM students_df \n",
    "            LIMIT 3\n",
    "        \"\"\"\n",
    "name_email = sqldf(query)\n",
    "name_email"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "92c8a53a-3fb1-4c9e-9f50-b2283d0d378d",
   "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>Students</th>\n",
       "      <th>Email</th>\n",
       "      <th>Gender</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Sira</td>\n",
       "      <td>sira@info.com</td>\n",
       "      <td>Female</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Ibrahim</td>\n",
       "      <td>ib@info.com</td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Moussa</td>\n",
       "      <td>mouss@info.com</td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Mamadou</td>\n",
       "      <td>mam@info.com</td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Nabintou</td>\n",
       "      <td>nab@info.com</td>\n",
       "      <td>Female</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Students           Email  Gender\n",
       "0      Sira   sira@info.com  Female\n",
       "1   Ibrahim     ib@info.com    Male\n",
       "2    Moussa  mouss@info.com    Male\n",
       "3   Mamadou    mam@info.com    Male\n",
       "4  Nabintou    nab@info.com  Female"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Select Student Name, Email and Gender\n",
    "selection = sqldf(\"SELECT Students, Email, Gender FROM students_df\")\n",
    "selection"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "02d912e7-37b7-48c7-baa2-c2c663d79e5e",
   "metadata": {},
   "source": [
    "## Data Filtering"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "58c6ff1c-3075-4309-84a2-9dbcd1ba77c2",
   "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>Students</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Age</th>\n",
       "      <th>Email</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Sira</td>\n",
       "      <td>Female</td>\n",
       "      <td>18</td>\n",
       "      <td>sira@info.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Nabintou</td>\n",
       "      <td>Female</td>\n",
       "      <td>21</td>\n",
       "      <td>nab@info.com</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Students  Gender  Age          Email\n",
       "0      Sira  Female   18  sira@info.com\n",
       "1  Nabintou  Female   21   nab@info.com"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Define the query\n",
    "query = \"\"\"SELECT * \n",
    "           FROM students_df \n",
    "           WHERE Gender = 'Female'\n",
    "        \"\"\"\n",
    "\n",
    "# Execute the query\n",
    "female_student = sqldf(query)\n",
    "female_student"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "e30d1546-c552-4905-818b-5bd24022a01b",
   "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>Teacher</th>\n",
       "      <th>Email</th>\n",
       "      <th>Degree</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Ibrahim</td>\n",
       "      <td>ib@info.com</td>\n",
       "      <td>M.S in Data Science</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Fatim</td>\n",
       "      <td>fat@info.com</td>\n",
       "      <td>M.S. Architecture</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Teacher         Email               Degree\n",
       "0  Ibrahim   ib@info.com  M.S in Data Science\n",
       "1    Fatim  fat@info.com    M.S. Architecture"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Select Name, Gender & Email of all the Masters Students\n",
    "query = \"\"\" SELECT Teacher, Email, Degree \n",
    "            FROM teaching_assistant_df \n",
    "            WHERE Degree LIKE 'M.S%'\n",
    "            \"\"\"\n",
    "ms_students = sqldf(query)\n",
    "ms_students"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "f8878148-684f-412f-809f-95335407352a",
   "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>Teacher</th>\n",
       "      <th>Email</th>\n",
       "      <th>Degree</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Ibrahim</td>\n",
       "      <td>ib@info.com</td>\n",
       "      <td>M.S in Data Science</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Fatim</td>\n",
       "      <td>fat@info.com</td>\n",
       "      <td>M.S. Architecture</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Teacher         Email               Degree\n",
       "0  Ibrahim   ib@info.com  M.S in Data Science\n",
       "3    Fatim  fat@info.com    M.S. Architecture"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Pandas Equivalent \n",
    "cols_to_get = ['Teacher', 'Email', 'Degree']\n",
    "teaching_assistant_df[teaching_assistant_df.Degree.str.startswith('M.S')][cols_to_get]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f76c3866-1c8f-4ab0-be1f-fc19daa3b76a",
   "metadata": {},
   "source": [
    "## Data Aggregation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "07ab0976-0c61-4730-b98b-feb2b0dce5a3",
   "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>AVG(Age)</th>\n",
       "      <th>Gender</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>19.500000</td>\n",
       "      <td>Female</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>22.666667</td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    AVG(Age)  Gender\n",
       "0  19.500000  Female\n",
       "1  22.666667    Male"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get the average age Gender Wise\n",
    "query = \"\"\" SELECT AVG(Age), Gender \n",
    "            FROM students_df \n",
    "            GROUP BY Gender\n",
    "        \"\"\"\n",
    "avg_age = sqldf(query)\n",
    "avg_age"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "03a51f8b-8cb7-47b6-9db1-58c9789e08b2",
   "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>Department</th>\n",
       "      <th>Depart_Count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Business</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Statistics</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Engineering</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Comp Sc</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Department  Depart_Count\n",
       "0     Business             2\n",
       "1   Statistics             1\n",
       "2  Engineering             1\n",
       "3      Comp Sc             1"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Count the number of occurence of each department\n",
    "\n",
    "query = \"\"\" SELECT Department, COUNT(*) as Depart_Count\n",
    "            FROM teaching_assistant_df \n",
    "            GROUP BY Department\n",
    "            ORDER BY Depart_Count DESC;\n",
    "        \"\"\"\n",
    "\n",
    "depart_count = sqldf(query)\n",
    "depart_count"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0564fca9-e424-4d2d-87d1-4b1425140ab3",
   "metadata": {},
   "source": [
    "## Joins"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "15511d23-0f32-4870-95e3-d7e130def787",
   "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>Students</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Email</th>\n",
       "      <th>Age</th>\n",
       "      <th>Department</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Ibrahim</td>\n",
       "      <td>Male</td>\n",
       "      <td>ib@info.com</td>\n",
       "      <td>27</td>\n",
       "      <td>Business</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Mamadou</td>\n",
       "      <td>Male</td>\n",
       "      <td>mam@info.com</td>\n",
       "      <td>22</td>\n",
       "      <td>Comp Sc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Nabintou</td>\n",
       "      <td>Female</td>\n",
       "      <td>nab@info.com</td>\n",
       "      <td>21</td>\n",
       "      <td>Statistics</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Students  Gender         Email  Age  Department\n",
       "0   Ibrahim    Male   ib@info.com   27    Business\n",
       "1   Mamadou    Male  mam@info.com   22     Comp Sc\n",
       "2  Nabintou  Female  nab@info.com   21  Statistics"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Select Students who are Teacher assistants along with their departments\n",
    "\n",
    "query = \"\"\" SELECT st.Students, st.Gender, st.Email, st.Age, tat.Department\n",
    "            FROM students_df st INNER JOIN teaching_assistant_df tat \n",
    "            ON st.Email = tat.Email;\n",
    "            \"\"\"\n",
    "\n",
    "result = sqldf(query)\n",
    "result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2ae008dd-acfe-48f3-b92c-1be2495e3a63",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}