Lectures/L-Datacleaning-reproducibility/03-Datatables_Pandas.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-02-20T09:19:55.258939Z",
     "start_time": "2021-02-20T09:19:55.238022Z"
    }
   },
   "source": [
    "### Datatables \n",
    "Official page: https://datatable.readthedocs.io/en/latest/start/quick-start.html\n",
    "\n",
    "Introduction/tutorial: https://towardsdatascience.com/an-overview-of-pythons-datatable-package-5d3a97394ee9\n",
    "\n",
    "\n",
    "\n",
    "A different way of treating frames (like pandas dataframes)\n",
    "\n",
    "Also there is a comparison between SQL langauges and `datatables`: https://datatable.readthedocs.io/en/latest/manual/comparison_with_sql.html\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Collecting datatable\n",
      "  Downloading datatable-1.0.0-cp38-cp38-manylinux_2_12_x86_64.whl (96.6 MB)\n",
      "\u001b[K     |████████████████████████████████| 96.6 MB 8.5 kB/s  eta 0:00:01     |█████▎                          | 15.9 MB 34.2 MB/s eta 0:00:03     |████████████████████            | 60.4 MB 29.8 MB/s eta 0:00:02     |████████████████████████████    | 84.5 MB 35.0 MB/s eta 0:00:01     |████████████████████████████▊   | 86.7 MB 35.0 MB/s eta 0:00:01\n",
      "\u001b[?25hInstalling collected packages: datatable\n",
      "Successfully installed datatable-1.0.0\n"
     ]
    }
   ],
   "source": [
    "!pip install datatable --user"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*    Can automatically **detect separators**, headers, column types, quoting rules, etc.\n",
    "*    Can **read** data **from multiple sources** including file, URL, shell, raw text, archives and glob.\n",
    "*    Provides **multi-threaded** file reading for maximum speed\n",
    "*    Includes a **progress indicator** when reading large files\n",
    "*    Can read both RFC4180-compliant and non-compliant files."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-02-20T09:41:52.556430Z",
     "start_time": "2021-02-20T09:41:51.830524Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style type='text/css'>\n",
       ".datatable table.frame { margin-bottom: 0; }\n",
       ".datatable table.frame thead { border-bottom: none; }\n",
       ".datatable table.frame tr.coltypes td {  color: #FFFFFF;  line-height: 6px;  padding: 0 0.5em;}\n",
       ".datatable .bool    { background: #DDDD99; }\n",
       ".datatable .object  { background: #565656; }\n",
       ".datatable .int     { background: #5D9E5D; }\n",
       ".datatable .float   { background: #4040CC; }\n",
       ".datatable .str     { background: #CC4040; }\n",
       ".datatable .time    { background: #40CC40; }\n",
       ".datatable .row_index {  background: var(--jp-border-color3);  border-right: 1px solid var(--jp-border-color0);  color: var(--jp-ui-font-color3);  font-size: 9px;}\n",
       ".datatable .frame tbody td { text-align: left; }\n",
       ".datatable .frame tr.coltypes .row_index {  background: var(--jp-border-color0);}\n",
       ".datatable th:nth-child(2) { padding-left: 12px; }\n",
       ".datatable .hellipsis {  color: var(--jp-cell-editor-border-color);}\n",
       ".datatable .vellipsis {  background: var(--jp-layout-color0);  color: var(--jp-cell-editor-border-color);}\n",
       ".datatable .na {  color: var(--jp-cell-editor-border-color);  font-size: 80%;}\n",
       ".datatable .sp {  opacity: 0.25;}\n",
       ".datatable .footer { font-size: 9px; }\n",
       ".datatable .frame_dimensions {  background: var(--jp-border-color3);  border-top: 1px solid var(--jp-border-color0);  color: var(--jp-ui-font-color3);  display: inline-block;  opacity: 0.6;  padding: 1px 10px 1px 5px;}\n",
       "</style>\n"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import datatable as dt\n",
    "import pandas as pd\n",
    "import os\n",
    "\n",
    "datafile = \"/v/courses/dataesp2023.public/Datasets/D-Interactive_Visualization/traffic-violation.csv\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Loading time¶\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-02-20T09:41:55.534605Z",
     "start_time": "2021-02-20T09:41:54.797688Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 7.18 s, sys: 730 ms, total: 7.91 s\n",
      "Wall time: 1.57 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "dtf = dt.fread(datafile)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-02-20T09:42:18.109192Z",
     "start_time": "2021-02-20T09:42:04.537823Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 4.21 s, sys: 1.38 s, total: 5.6 s\n",
      "Wall time: 5.6 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "df = pd.read_csv(datafile)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Data manipulation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-02-20T09:42:26.306098Z",
     "start_time": "2021-02-20T09:42:26.284123Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class='datatable'>\n",
       "  <table class='frame'>\n",
       "  <thead>\n",
       "    <tr class='colnames'><td class='row_index'></td><th>SeqID</th><th>Date Of Stop</th><th>Time Of Stop</th><th>Agency</th></tr>\n",
       "    <tr class='coltypes'><td class='row_index'></td><td class='str' title='str32'>&#x25AA;&#x25AA;&#x25AA;&#x25AA;</td><td class='str' title='str32'>&#x25AA;&#x25AA;&#x25AA;&#x25AA;</td><td class='str' title='str32'>&#x25AA;&#x25AA;&#x25AA;&#x25AA;</td><td class='str' title='str32'>&#x25AA;&#x25AA;&#x25AA;&#x25AA;</td></tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr><td class='row_index'>0</td><td>63f0c83d-9127-4734-8655-84b5c5c542ea</td><td>05/21/2019</td><td>22:52:00</td><td>MCP</td></tr>\n",
       "    <tr><td class='row_index'>1</td><td>8ba4a63a-daab-4144-8fb2-73ca2cc9c818</td><td>05/21/2019</td><td>22:51:00</td><td>MCP</td></tr>\n",
       "    <tr><td class='row_index'>2</td><td>6008542f-ebd4-4a6c-8aca-8db1ec961484</td><td>05/21/2019</td><td>22:48:00</td><td>MCP</td></tr>\n",
       "    <tr><td class='row_index'>3</td><td>41a5c596-a342-4928-8433-85c128fc7990</td><td>05/21/2019</td><td>22:45:00</td><td>MCP</td></tr>\n",
       "    <tr><td class='row_index'>4</td><td>e9924f8b-9a1a-4614-9177-d6f3afc0664c</td><td>05/21/2019</td><td>22:44:00</td><td>MCP</td></tr>\n",
       "    <tr><td class='row_index'>5</td><td>e9924f8b-9a1a-4614-9177-d6f3afc0664c</td><td>05/21/2019</td><td>22:44:00</td><td>MCP</td></tr>\n",
       "    <tr><td class='row_index'>6</td><td>b9cc5ffd-a6e6-487c-8e90-751c233385ab</td><td>05/21/2019</td><td>22:44:00</td><td>MCP</td></tr>\n",
       "    <tr><td class='row_index'>7</td><td>2bf1e1d2-a59a-4460-888d-3637faaa4939</td><td>05/21/2019</td><td>22:44:00</td><td>MCP</td></tr>\n",
       "    <tr><td class='row_index'>8</td><td>829cd2e0-c909-44e0-b80f-0e3b61b1fd51</td><td>05/21/2019</td><td>22:41:00</td><td>MCP</td></tr>\n",
       "  </tbody>\n",
       "  </table>\n",
       "  <div class='footer'>\n",
       "    <div class='frame_dimensions'>9 rows &times; 4 columns</div>\n",
       "  </div>\n",
       "</div>\n"
      ],
      "text/plain": [
       "<Frame#7f1bec771d80 9x4>"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dtf[1:10,:4]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class='datatable'>\n",
       "  <table class='frame'>\n",
       "  <thead>\n",
       "    <tr class='colnames'><td class='row_index'></td><th>Description</th><th>Location</th><th>Latitude</th><th>Longitude</th><th>Accident</th></tr>\n",
       "    <tr class='coltypes'><td class='row_index'></td><td class='str' title='str32'>&#x25AA;&#x25AA;&#x25AA;&#x25AA;</td><td class='str' title='str32'>&#x25AA;&#x25AA;&#x25AA;&#x25AA;</td><td class='float' title='float64'>&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;</td><td class='float' title='float64'>&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;</td><td class='str' title='str32'>&#x25AA;&#x25AA;&#x25AA;&#x25AA;</td></tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr><td class='row_index'>0</td><td>OPER. MOTOR VEH. WITH OPERATOR NOT RESTRAINED BY S&#133;</td><td>12 N. WASHINGTON ST</td><td>39.0848</td><td>&minus;77.1528</td><td>No</td></tr>\n",
       "    <tr><td class='row_index'>1</td><td>FAILURE TO DISPLAY REGISTRATION CARD UPON DEMAND B&#133;</td><td>12 N. WASHINGTON ST</td><td>39.0848</td><td>&minus;77.1528</td><td>No</td></tr>\n",
       "    <tr><td class='row_index'>2</td><td>FAILURE TO ATTACH VEHICLE REGISTRATION PLATES AT F&#133;</td><td>RANDOLPH RD/  HUNTERS LN</td><td>39.0537</td><td>&minus;77.1009</td><td>No</td></tr>\n",
       "    <tr><td class='row_index'>3</td><td>DRIVING VEH. W/O ADEQUATE REAR REG. PLATE ILLUMINA&#133;</td><td>OLD GEORGETOWN RD @ TUCKERMAN LA</td><td>39.0245</td><td>&minus;77.103</td><td>No</td></tr>\n",
       "    <tr><td class='row_index'>4</td><td>EXCEEDING THE POSTED SPEED LIMIT OF 25 MPH</td><td>OLD GEORGETOWN RD / AUBURN AVE</td><td>38.9881</td><td>&minus;77.0999</td><td>No</td></tr>\n",
       "    <tr><td class='row_index'>5</td><td>FAILURE TO ATTACH VEHICLE REGISTRATION PLATES AT F&#133;</td><td>1700 UNIVERSITY BLVD W</td><td>39.0367</td><td>&minus;77.0386</td><td>No</td></tr>\n",
       "    <tr><td class='row_index'>6</td><td>EXCEEDING THE POSTED SPEED LIMIT OF 35 MPH</td><td>1700 UNIVERSITY BLVD W</td><td>39.0367</td><td>&minus;77.0386</td><td>No</td></tr>\n",
       "    <tr><td class='row_index'>7</td><td>DRIVER USING HANDS TO USE HANDHELD TELEPHONE WHILE&#133;</td><td>GEORGIA AVE / SLIGO AVE</td><td>38.9918</td><td>&minus;77.0267</td><td>No</td></tr>\n",
       "    <tr><td class='row_index'>8</td><td>FAILURE OF DR. TO MAKE LANE CH. TO AVAIL. LANE NOT&#133;</td><td>MVA/270</td><td>39.1497</td><td>&minus;77.2155</td><td>No</td></tr>\n",
       "    <tr><td class='row_index'>9</td><td>DRIVING VEH. W/O ADEQUATE REAR REG. PLATE ILLUMINA&#133;</td><td>RANDOLPH RD/ROCKING HORSE RD</td><td>39.0538</td><td>&minus;77.0975</td><td>No</td></tr>\n",
       "  </tbody>\n",
       "  </table>\n",
       "  <div class='footer'>\n",
       "    <div class='frame_dimensions'>10 rows &times; 5 columns</div>\n",
       "  </div>\n",
       "</div>\n"
      ],
      "text/plain": [
       "<Frame#7f1bec771b40 10x5>"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dtf[:,dtf.names[5:10]].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-02-20T09:42:43.610584Z",
     "start_time": "2021-02-20T09:42:43.607605Z"
    }
   },
   "source": [
    "#### Frame proxy f"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-02-20T09:43:55.078372Z",
     "start_time": "2021-02-20T09:43:55.064829Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class='datatable'>\n",
       "  <table class='frame'>\n",
       "  <thead>\n",
       "    <tr class='colnames'><td class='row_index'></td><th>SeqID</th></tr>\n",
       "    <tr class='coltypes'><td class='row_index'></td><td class='str' title='str32'>&#x25AA;&#x25AA;&#x25AA;&#x25AA;</td></tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr><td class='row_index'>0</td><td>63f0c83d-9127-4734-8655-84b5c5c542ea</td></tr>\n",
       "    <tr><td class='row_index'>1</td><td>63f0c83d-9127-4734-8655-84b5c5c542ea</td></tr>\n",
       "    <tr><td class='row_index'>2</td><td>8ba4a63a-daab-4144-8fb2-73ca2cc9c818</td></tr>\n",
       "    <tr><td class='row_index'>3</td><td>6008542f-ebd4-4a6c-8aca-8db1ec961484</td></tr>\n",
       "    <tr><td class='row_index'>4</td><td>41a5c596-a342-4928-8433-85c128fc7990</td></tr>\n",
       "  </tbody>\n",
       "  </table>\n",
       "  <div class='footer'>\n",
       "    <div class='frame_dimensions'>5 rows &times; 1 column</div>\n",
       "  </div>\n",
       "</div>\n"
      ],
      "text/plain": [
       "<Frame#7f1bec771d20 5x1>"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dtf[:5,dt.f.SeqID]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Datatable frames can be converted pandas DataFrame or numpy array"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "numpy_df = dtf.to_numpy()\n",
    "pandas_df = dtf.to_pandas()"
   ]
  }
 ],
 "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.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}