1-files-databases.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "import os\n",
    "import random\n",
    "import sqlite3\n",
    "import struct\n",
    "\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Files are just 0s and 1s"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create file 1 - ASCII http://www.asciitable.com/\n",
    "\n",
    "f = open('file1.txt','w')\n",
    "architectstring = 'Your life is the sum of a remainder of an unbalanced equation inherent to the programming of the matrix.\\n'\n",
    "f.write(architectstring)\n",
    "f.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "ASCII text of file 1:\n",
      "Your life is the sum of a remainder of an unbalanced equation inherent to the programming of the matrix.\n",
      "\n",
      "String length of the original string:  105\n"
     ]
    }
   ],
   "source": [
    "# Inspect file 1\n",
    "\n",
    "f = open('file1.txt')\n",
    "contents = f.read()\n",
    "print(\"ASCII text of file 1:\")\n",
    "print(contents)\n",
    "\n",
    "\n",
    "print(\"String length of the original string: \", len(architectstring))\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Q1. How many bytes should the file contain?**\n",
    "\n",
    "**Q2. What should the first 5 bytes of the file be?**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Answers\n",
    "\n",
    "print(os.path.getsize('file1.txt'))\n",
    "\n",
    "f = open('file1.txt', 'rb')\n",
    "struct.unpack('BBBBB', f.read(5))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## There are many simple readable file formats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# json: javascript object notation\n",
    "\n",
    "data = {'name':'amy', 'dob':'1990/01/01', 'mean_abp':['60, 70, 70, 50']}\n",
    "\n",
    "with open('file2.json', 'w') as f:  \n",
    "    json.dump(data, f)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'{\"name\": \"amy\", \"dob\": \"1990/01/01\", \"mean_abp\": [\"60, 70, 70, 50\"]}'"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "f = open('file2.json')\n",
    "f.read()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>recordid</th>\n",
       "      <th>SAPS-I</th>\n",
       "      <th>SOFA</th>\n",
       "      <th>Length_of_stay</th>\n",
       "      <th>Survival</th>\n",
       "      <th>In-hospital_death</th>\n",
       "      <th>Age</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Height</th>\n",
       "      <th>Weight</th>\n",
       "      <th>...</th>\n",
       "      <th>SysABP_last</th>\n",
       "      <th>TroponinI_last</th>\n",
       "      <th>TroponinT_last</th>\n",
       "      <th>WBC_last</th>\n",
       "      <th>Weight_last</th>\n",
       "      <th>pH_last</th>\n",
       "      <th>MechVentStartTime</th>\n",
       "      <th>MechVentDuration</th>\n",
       "      <th>MechVentLast8Hour</th>\n",
       "      <th>UrineOutputSum</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>132539</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>54.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9.4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>132540</td>\n",
       "      <td>16</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>175.3</td>\n",
       "      <td>76.0</td>\n",
       "      <td>...</td>\n",
       "      <td>103.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>13.3</td>\n",
       "      <td>81.6</td>\n",
       "      <td>7.37</td>\n",
       "      <td>71.0</td>\n",
       "      <td>360.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>132541</td>\n",
       "      <td>21</td>\n",
       "      <td>11</td>\n",
       "      <td>19</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>56.7</td>\n",
       "      <td>...</td>\n",
       "      <td>126.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.2</td>\n",
       "      <td>56.7</td>\n",
       "      <td>7.47</td>\n",
       "      <td>617.0</td>\n",
       "      <td>2160.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>14.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>132543</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>575</td>\n",
       "      <td>0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>180.3</td>\n",
       "      <td>84.6</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7.9</td>\n",
       "      <td>84.6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>132545</td>\n",
       "      <td>17</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>918</td>\n",
       "      <td>0</td>\n",
       "      <td>88.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 120 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   recordid  SAPS-I  SOFA  Length_of_stay  Survival  In-hospital_death   Age  \\\n",
       "0    132539       6     1               5        -1                  0  54.0   \n",
       "1    132540      16     8               8        -1                  0  76.0   \n",
       "2    132541      21    11              19        -1                  0  44.0   \n",
       "3    132543       7     1               9       575                  0  68.0   \n",
       "4    132545      17     2               4       918                  0  88.0   \n",
       "\n",
       "   Gender  Height  Weight       ...        SysABP_last  TroponinI_last  \\\n",
       "0     0.0     NaN     NaN       ...                NaN             NaN   \n",
       "1     1.0   175.3    76.0       ...              103.0             NaN   \n",
       "2     0.0     NaN    56.7       ...              126.0             NaN   \n",
       "3     1.0   180.3    84.6       ...                NaN             NaN   \n",
       "4     0.0     NaN     NaN       ...                NaN             NaN   \n",
       "\n",
       "   TroponinT_last  WBC_last  Weight_last  pH_last  MechVentStartTime  \\\n",
       "0             NaN       9.4          NaN      NaN                NaN   \n",
       "1             NaN      13.3         81.6     7.37               71.0   \n",
       "2             NaN       6.2         56.7     7.47              617.0   \n",
       "3             NaN       7.9         84.6      NaN                NaN   \n",
       "4             NaN       4.8          NaN      NaN                NaN   \n",
       "\n",
       "   MechVentDuration  MechVentLast8Hour  UrineOutputSum  \n",
       "0               NaN                NaN             NaN  \n",
       "1             360.0                0.0             5.0  \n",
       "2            2160.0                1.0            14.0  \n",
       "3               NaN                NaN             NaN  \n",
       "4               NaN                NaN             NaN  \n",
       "\n",
       "[5 rows x 120 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# csv: comma separated values\n",
    "\n",
    "df = pd.read_csv('data/PhysionetChallenge2012-set-a.csv')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Relational Databases\n",
    "\n",
    "https://docs.python.org/3/library/sqlite3.html\n",
    "\n",
    "Relational table-like structure"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "# The data\n",
    "\n",
    "# Employees\n",
    "df_employees = pd.DataFrame([\n",
    "    [1001, 'Erwin', '2001-01-01', 'ceo'],\n",
    "    [1002, 'Annie', '2002-02-02', 'accountant'],\n",
    "    [1003, 'Reiner', '2003-03-03', 'marketer'],\n",
    "    [1004, 'Mikasa', '2004-04-04', 'engineer']],\n",
    "    columns=['employee_id', 'name', 'hire_date', 'job_title'])\n",
    "\n",
    "# Performance Reviews\n",
    "df_reviews = pd.DataFrame([\n",
    "    [8, 1002, 1001, '2005-12-01'],\n",
    "    [7, 1003, 1001, '2005-12-01'],\n",
    "    [9, 1004, 1001, '2005-12-01'],\n",
    "    [7, 1002, 1001, '2006-12-01'],\n",
    "    [9, 1003, 1001, '2006-12-01'],\n",
    "    [10, 1004, 1001, '2006-12-01']],\n",
    "    columns=['rating', 'employee_id', 'reviewer_id', 'review_date'])\n",
    "\n",
    "# Bonuses\n",
    "df_bonuses = pd.DataFrame([\n",
    "    [10000, 1002, '2005-12-01'],\n",
    "    [20000, 1004, '2005-12-01'],\n",
    "    [20000, 1003, '2006-12-01'],\n",
    "    [30000, 1004, '2006-12-01']],\n",
    "    columns=['amount', 'employee_id', 'bonus_date'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>employee_id</th>\n",
       "      <th>name</th>\n",
       "      <th>hire_date</th>\n",
       "      <th>job_title</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1001</td>\n",
       "      <td>Erwin</td>\n",
       "      <td>2001-01-01</td>\n",
       "      <td>ceo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1002</td>\n",
       "      <td>Annie</td>\n",
       "      <td>2002-02-02</td>\n",
       "      <td>accountant</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1003</td>\n",
       "      <td>Reiner</td>\n",
       "      <td>2003-03-03</td>\n",
       "      <td>marketer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1004</td>\n",
       "      <td>Mikasa</td>\n",
       "      <td>2004-04-04</td>\n",
       "      <td>engineer</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   employee_id    name   hire_date   job_title\n",
       "0         1001   Erwin  2001-01-01         ceo\n",
       "1         1002   Annie  2002-02-02  accountant\n",
       "2         1003  Reiner  2003-03-03    marketer\n",
       "3         1004  Mikasa  2004-04-04    engineer"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>rating</th>\n",
       "      <th>employee_id</th>\n",
       "      <th>reviewer_id</th>\n",
       "      <th>review_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>8</td>\n",
       "      <td>1002</td>\n",
       "      <td>1001</td>\n",
       "      <td>2005-12-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7</td>\n",
       "      <td>1003</td>\n",
       "      <td>1001</td>\n",
       "      <td>2005-12-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>9</td>\n",
       "      <td>1004</td>\n",
       "      <td>1001</td>\n",
       "      <td>2005-12-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7</td>\n",
       "      <td>1002</td>\n",
       "      <td>1001</td>\n",
       "      <td>2006-12-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>9</td>\n",
       "      <td>1003</td>\n",
       "      <td>1001</td>\n",
       "      <td>2006-12-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>10</td>\n",
       "      <td>1004</td>\n",
       "      <td>1001</td>\n",
       "      <td>2006-12-01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   rating  employee_id  reviewer_id review_date\n",
       "0       8         1002         1001  2005-12-01\n",
       "1       7         1003         1001  2005-12-01\n",
       "2       9         1004         1001  2005-12-01\n",
       "3       7         1002         1001  2006-12-01\n",
       "4       9         1003         1001  2006-12-01\n",
       "5      10         1004         1001  2006-12-01"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>amount</th>\n",
       "      <th>employee_id</th>\n",
       "      <th>bonus_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10000</td>\n",
       "      <td>1002</td>\n",
       "      <td>2005-12-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>20000</td>\n",
       "      <td>1004</td>\n",
       "      <td>2005-12-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>20000</td>\n",
       "      <td>1003</td>\n",
       "      <td>2006-12-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>30000</td>\n",
       "      <td>1004</td>\n",
       "      <td>2006-12-01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   amount  employee_id  bonus_date\n",
       "0   10000         1002  2005-12-01\n",
       "1   20000         1004  2005-12-01\n",
       "2   20000         1003  2006-12-01\n",
       "3   30000         1004  2006-12-01"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(df_employees)\n",
    "display(df_reviews)\n",
    "display(df_bonuses)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "# The connection object represents the database\n",
    "os.remove('staff.db')\n",
    "conn = sqlite3.connect('staff.db')\n",
    "\n",
    "c = conn.cursor()\n",
    "\n",
    "# Create tables\n",
    "c.execute('''CREATE TABLE employees\n",
    "             (employee_id integer primary key, name text, hire_date date, job_title text);''')\n",
    "c.execute('''CREATE TABLE reviews\n",
    "             (rating, employee_id, reviewer_id, review_date);''')\n",
    "c.execute('''CREATE TABLE bonuses\n",
    "             ('amount', 'employee_id', 'bonus_date');''')\n",
    "\n",
    "# Insert data\n",
    "c.executemany(\"INSERT INTO employees VALUES (?, ?, ?, ?)\", df_employees.values.tolist())\n",
    "c.executemany(\"INSERT INTO reviews VALUES (?, ?, ?, ?)\", df_reviews.values.tolist())\n",
    "c.executemany(\"INSERT INTO bonuses VALUES (?, ?, ?)\", df_bonuses.values.tolist())\n",
    "\n",
    "# Save (commit) the changes\n",
    "conn.commit()\n",
    "\n",
    "# We can also close the connection if we are done with it.\n",
    "# Just be sure any changes have been committed or they will be lost.\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = sqlite3.connect('staff.db')\n",
    "c = conn.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(1001, 'Erwin', '2001-01-01', 'ceo'),\n",
       " (1002, 'Annie', '2002-02-02', 'accountant'),\n",
       " (1003, 'Reiner', '2003-03-03', 'marketer')]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Querying data from tables\n",
    "result = [row for row in c.execute(\n",
    "    \"select * from employees where hire_date < date('2003-04-04');\")]\n",
    "result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('Annie', 10000, '2005-12-01'),\n",
       " ('Mikasa', 20000, '2005-12-01'),\n",
       " ('Reiner', 20000, '2006-12-01'),\n",
       " ('Mikasa', 30000, '2006-12-01')]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Joining results from multiple tables\n",
    "result = [row for row in c.execute(\n",
    "    '''select e.name, b.amount, b.bonus_date from employees e\n",
    "       inner join bonuses b\n",
    "       on e.employee_id=b.employee_id;''')]\n",
    "result"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Q1. Construct an SQL query to show all the review ratings and dates of the employee named Annie**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "result = [row for row in c.execute(\n",
    "    '''select <INSERT CODE>\n",
    "       where <INSERT CODE>;''')]\n",
    "result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Q2. Design the relations (tables and columns) to hold the following data for patients:**\n",
    "- Name\n",
    "- Gender\n",
    "- Postal code\n",
    "- DOB\n",
    "- Patient ID\n",
    "- Visit datetime\n",
    "- Visit final bill\n",
    "- Blood Sugar \n",
    "- Creatinine\n",
    "- BNP\n",
    "- Sodium\n",
    "- Potassium\n",
    "\n",
    "Hint: Add more columns/variables you think are appropriate.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Answer 1\n",
    "\n",
    "result = [row for row in c.execute(\n",
    "    '''select rating, review_date from reviews\n",
    "       where employee_id=(select employee_id from employees where name='Annie');''')]\n",
    "result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Answer 2\n",
    "patients = ['name', 'gender', 'postal code', 'dob', 'patient_id']\n",
    "visits = ['visit_datetime', 'final_bill', 'patient_id']\n",
    "labs = ['measurement_datetime', 'lab_item_id', 'lab_value']\n",
    "\n",
    "lab_dict = ['lab_item_id', 'lab_item_name']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Searching"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Q. Write a function to return the index of an item in an unsorted list if present, else -1. How does it scale relative to the size of the list?**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "x = list(range(100))\n",
    "random.shuffle(x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def linear_search(x, item):\n",
    "    \"\"\"\n",
    "    Function to return the index of `item` in the array `x` if present, else None.\n",
    "    \"\"\"\n",
    "    # Write code here\n",
    "    pass\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# test\n",
    "linear_search(x, 20)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Q. Write a function to return the index of an item in a sorted list if present, else -1. How does it scale relative to the size of the list?**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "y = list(range(100))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def binary_search(x, item, l, r):\n",
    "    # Write code here\n",
    "    pass"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Test\n",
    "binary_search(y, 31, 0, len(y)-1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Answer for linear left-right search\n",
    "def linear_search(x, item):\n",
    "    for i in range(len(x)):\n",
    "        if x[i] == item:\n",
    "            return i\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Answer\n",
    "def binary_search(x, item, l, r):\n",
    "    # Base cases\n",
    "    if item < x[l] or item > x[r]:\n",
    "        return -1\n",
    "    # Restricted to one index. Do or die.\n",
    "    if l == r:\n",
    "        if x[l] == item:\n",
    "            return l\n",
    "        else:\n",
    "            return -1\n",
    "\n",
    "    m = int((l + r) / 2)\n",
    "\n",
    "    if x[m] == item:\n",
    "        return m\n",
    "    elif x[m] > item:\n",
    "        return binary_search(x, item, l, m-1)\n",
    "    else:\n",
    "        return binary_search(x, item, m+1, r)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "binary_search(y, 46, 0, len(y) - 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}