notebooks/usage.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "0d45f3bd",
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "source": [
    "# Lector usage\n",
    "\n",
    "This notebook shows usage of Lector's main functionality, reading (parsing) CSV files and inferring correct column data types. \n",
    "\n",
    "For motivation why we need another CSV reader see further below in this notebook."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b13164dd",
   "metadata": {
    "heading_collapsed": true
   },
   "source": [
    "# Setup"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "c7b73a6a",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-12-06T19:50:40.946427Z",
     "start_time": "2022-12-06T19:50:40.483382Z"
    },
    "hidden": true,
    "init_cell": true,
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Exception reporting mode: Minimal\n"
     ]
    }
   ],
   "source": [
    "import io\n",
    "import importlib\n",
    "from pathlib import Path\n",
    "\n",
    "import gdown\n",
    "import humanize\n",
    "import lector\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import pyarrow as pa\n",
    "import pyarrow.csv\n",
    "\n",
    "from lector.log import LOG, pformat, schema_view\n",
    "\n",
    "%xmode Minimal"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "5f56be4d",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-12-06T19:58:56.711768Z",
     "start_time": "2022-12-06T19:58:56.701892Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "# Create some not so clean CSV example files to experiment with\n",
    "\n",
    "example_1 = \"\"\"\n",
    "Some preamble content here\n",
    "This is still \"part of the metadata preamble\"\n",
    "id;genre;metric;count;content;website;tags;vecs;date\n",
    "1234982348728374;a;0.1;1;; http://www.graphext.com;\"[a,b,c]\";\"[1.3, 1.4, 1.67]\";11/10/2022\n",
    ";b;0.12;;\"Natural language text is different from categorical data.\"; https://www.twitter.com;[d];\"[0, 1.9423]\";01/10/2022\n",
    "9007199254740993;a;3.14;3;\"The Project · Gutenberg » EBook « of Die Fürstin.\";http://www.google.com;\"['e', 'f']\";[\"84.234, 12509.99\"];13/10/2021\n",
    "\n",
    "\"\"\".encode(\"ISO-8859-1\")\n",
    "\n",
    "example_2 = \"\"\"\n",
    "id;genre;metric;count;content;website;tags;vecs;date\n",
    "1234982348728374;a;0.1;1;; http://www.graphext.com;\"[a,b,c]\";\"[1.3, 1.4, 1.67]\";11/10/2022\n",
    ";b;0.12;;\"Natural language text is different from categorical data.\"; https://www.twitter.com;[d];\"[0, 1.9423]\";01/10/2022\n",
    "18446744073709551615;a;3.14;3;\"The Project · Gutenberg » EBook « of Die Fürstin.\";http://www.google.com;\"['e', 'f']\";[\"84.234, 12509.99\"];13/10/2021\n",
    "\"\"\".encode(\"ISO-8859-1\")\n",
    "\n",
    "with open(\"example_1.csv\", \"wb\") as f:\n",
    "    f.write(example_1)\n",
    "\n",
    "with open(\"example_2.csv\", \"wb\") as f:\n",
    "    f.write(example_2)\n",
    "\n",
    "fpm = \"example_md.csv\"\n",
    "fpl = \"example_lg.csv\"\n",
    "\n",
    "if not Path(\"./example_md.csv\").exists():\n",
    "    gdown.download(\"https://drive.google.com/uc?id=188lrt2psoru55bTog4mLDWd6DC_MUSQd\", fpm)\n",
    "    gdown.download(\"https://drive.google.com/uc?id=1fEN_Z2SbBesNiWdOf2ceXx2c-SRNHIZR\", fpl)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6b0d847e",
   "metadata": {
    "heading_collapsed": true
   },
   "source": [
    "# High-level API\n",
    "\n",
    "The high-level functional API is the simplest way to use Lector. By default it reads CSVs into a pyarrow Table:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "5d6fb590",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-12-05T17:54:18.574421Z",
     "start_time": "2022-12-05T17:54:18.497932Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "tbl = lector.read_csv(\"example_1.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "afe2d951",
   "metadata": {
    "hidden": true
   },
   "source": [
    "The first thing to notice is that this simply worked, unlike the pandas/arrow examples you'll find below. Let's do the same but with a bit more feedback to see what happened:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "f698cdec",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-12-05T17:54:20.582421Z",
     "start_time": "2022-12-05T17:54:20.514777Z"
    },
    "hidden": true,
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\u001b[38;20m18:54:20 INFO | lector | preambles.detect:66\u001b[0m \n",
      "'Fieldless' matches CSV buffer: detected 3 rows to skip.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<pre style=\"white-space:pre;overflow-x:auto;line-height:normal;font-family:Menlo,'DejaVu Sans Mono',consolas,'Courier New',monospace\"></pre>\n"
      ],
      "text/plain": []
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\u001b[38;20m18:54:20 INFO | lector | abc.analyze:147\u001b[0m \n",
      "                                                                                                                   \n",
      "  ─────────── CSV Format ────────────                                                                              \n",
      "   \u001b[1m{\u001b[0m                                                                                                               \n",
      "       \u001b[32m'encoding'\u001b[0m: \u001b[32m'ISO-8859-1'\u001b[0m,                                                                                   \n",
      "       \u001b[32m'preamble'\u001b[0m: \u001b[1;36m3\u001b[0m,                                                                                              \n",
      "       \u001b[32m'dialect'\u001b[0m: \u001b[1;35mDialect\u001b[0m\u001b[1m(\u001b[0m                                                                                         \n",
      "           \u001b[33mdelimiter\u001b[0m=\u001b[32m';'\u001b[0m,                                                                                          \n",
      "           \u001b[33mquote_char\u001b[0m=\u001b[32m'\"'\u001b[0m,                                                                                         \n",
      "           \u001b[33mescape_char\u001b[0m=\u001b[3;35mNone\u001b[0m,                                                                                       \n",
      "           \u001b[33mdouble_quote\u001b[0m=\u001b[3;92mTrue\u001b[0m,                                                                                      \n",
      "           \u001b[33mskip_initial_space\u001b[0m=\u001b[3;91mFalse\u001b[0m,                                                                               \n",
      "           \u001b[33mline_terminator\u001b[0m=\u001b[32m'\\r\\n'\u001b[0m,                                                                                 \n",
      "           \u001b[33mquoting\u001b[0m=\u001b[1;36m0\u001b[0m                                                                                               \n",
      "       \u001b[1m)\u001b[0m                                                                                                           \n",
      "   \u001b[1m}\u001b[0m                                                                                                               \n",
      "  ───────────────────────────────────                                                                              \n",
      "                                                                                                                   \n",
      "\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<pre style=\"white-space:pre;overflow-x:auto;line-height:normal;font-family:Menlo,'DejaVu Sans Mono',consolas,'Courier New',monospace\"></pre>\n"
      ],
      "text/plain": []
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\u001b[38;20m18:54:20 DEBUG | lector | cast.cast_array:120\u001b[0m \n",
      "Converted column \"id\" with converter\n",
      "\u001b[1;35mNumber\u001b[0m\u001b[1m(\u001b[0m\u001b[33mthreshold\u001b[0m=\u001b[1;36m0\u001b[0m\u001b[1;36m.95\u001b[0m\u001b[1m)\u001b[0m\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<pre style=\"white-space:pre;overflow-x:auto;line-height:normal;font-family:Menlo,'DejaVu Sans Mono',consolas,'Courier New',monospace\"></pre>\n"
      ],
      "text/plain": []
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\u001b[38;20m18:54:20 DEBUG | lector | cast.cast_array:120\u001b[0m \n",
      "Converted column \"genre\" with converter\n",
      "\u001b[1;35mCategory\u001b[0m\u001b[1m(\u001b[0m\u001b[33mthreshold\u001b[0m=\u001b[1;36m0\u001b[0m\u001b[1;36m.0\u001b[0m, \u001b[33mmax_cardinality\u001b[0m=\u001b[3;35mNone\u001b[0m\u001b[1m)\u001b[0m\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<pre style=\"white-space:pre;overflow-x:auto;line-height:normal;font-family:Menlo,'DejaVu Sans Mono',consolas,'Courier New',monospace\"></pre>\n"
      ],
      "text/plain": []
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\u001b[38;20m18:54:20 DEBUG | lector | cast.cast_array:120\u001b[0m \n",
      "Converted column \"metric\" with converter\n",
      "\u001b[1;35mNumber\u001b[0m\u001b[1m(\u001b[0m\u001b[33mthreshold\u001b[0m=\u001b[1;36m0\u001b[0m\u001b[1;36m.95\u001b[0m\u001b[1m)\u001b[0m\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<pre style=\"white-space:pre;overflow-x:auto;line-height:normal;font-family:Menlo,'DejaVu Sans Mono',consolas,'Courier New',monospace\"></pre>\n"
      ],
      "text/plain": []
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\u001b[38;20m18:54:20 DEBUG | lector | cast.cast_array:120\u001b[0m \n",
      "Converted column \"count\" with converter\n",
      "\u001b[1;35mNumber\u001b[0m\u001b[1m(\u001b[0m\u001b[33mthreshold\u001b[0m=\u001b[1;36m0\u001b[0m\u001b[1;36m.95\u001b[0m\u001b[1m)\u001b[0m\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<pre style=\"white-space:pre;overflow-x:auto;line-height:normal;font-family:Menlo,'DejaVu Sans Mono',consolas,'Courier New',monospace\"></pre>\n"
      ],
      "text/plain": []
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\u001b[38;20m18:54:20 DEBUG | lector | cast.cast_array:120\u001b[0m \n",
      "Converted column \"content\" with converter\n",
      "\u001b[1;35mText\u001b[0m\u001b[1m(\u001b[0m\u001b[33mthreshold\u001b[0m=\u001b[1;36m0\u001b[0m\u001b[1;36m.8\u001b[0m\u001b[1m)\u001b[0m\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<pre style=\"white-space:pre;overflow-x:auto;line-height:normal;font-family:Menlo,'DejaVu Sans Mono',consolas,'Courier New',monospace\"></pre>\n"
      ],
      "text/plain": []
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\u001b[38;20m18:54:20 DEBUG | lector | cast.cast_array:120\u001b[0m \n",
      "Converted column \"website\" with converter\n",
      "\u001b[1;35mUrl\u001b[0m\u001b[1m(\u001b[0m\u001b[33mthreshold\u001b[0m=\u001b[1;36m0\u001b[0m\u001b[1;36m.8\u001b[0m\u001b[1m)\u001b[0m\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<pre style=\"white-space:pre;overflow-x:auto;line-height:normal;font-family:Menlo,'DejaVu Sans Mono',consolas,'Courier New',monospace\"></pre>\n"
      ],
      "text/plain": []
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\u001b[38;20m18:54:20 DEBUG | lector | cast.cast_array:120\u001b[0m \n",
      "Converted column \"tags\" with converter\n",
      "\u001b[1;35mList\u001b[0m\u001b[1m(\u001b[0m\u001b[33mthreshold\u001b[0m=\u001b[1;36m0\u001b[0m\u001b[1;36m.95\u001b[0m, \u001b[33mthreshold_urls\u001b[0m=\u001b[1;36m0\u001b[0m\u001b[1;36m.8\u001b[0m\u001b[1m)\u001b[0m\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<pre style=\"white-space:pre;overflow-x:auto;line-height:normal;font-family:Menlo,'DejaVu Sans Mono',consolas,'Courier New',monospace\"></pre>\n"
      ],
      "text/plain": []
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\u001b[38;20m18:54:20 DEBUG | lector | cast.cast_array:120\u001b[0m \n",
      "Converted column \"vecs\" with converter\n",
      "\u001b[1;35mList\u001b[0m\u001b[1m(\u001b[0m\u001b[33mthreshold\u001b[0m=\u001b[1;36m0\u001b[0m\u001b[1;36m.95\u001b[0m, \u001b[33mthreshold_urls\u001b[0m=\u001b[1;36m0\u001b[0m\u001b[1;36m.8\u001b[0m\u001b[1m)\u001b[0m\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<pre style=\"white-space:pre;overflow-x:auto;line-height:normal;font-family:Menlo,'DejaVu Sans Mono',consolas,'Courier New',monospace\"></pre>\n"
      ],
      "text/plain": []
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\u001b[38;20m18:54:20 DEBUG | lector | cast.cast_array:120\u001b[0m \n",
      "Converted column \"date\" with converter\n",
      "\u001b[1;35mTimestamp\u001b[0m\u001b[1m(\u001b[0m\u001b[33mthreshold\u001b[0m=\u001b[1;36m0\u001b[0m\u001b[1;36m.95\u001b[0m\u001b[1m)\u001b[0m\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<pre style=\"white-space:pre;overflow-x:auto;line-height:normal;font-family:Menlo,'DejaVu Sans Mono',consolas,'Courier New',monospace\"></pre>\n"
      ],
      "text/plain": []
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\u001b[38;20m18:54:20 INFO | lector | cast.cast_table:83\u001b[0m \n",
      "                                                                                                                   \n",
      " \u001b[3mChanged types                      \u001b[0m                                                                               \n",
      "  ─────────────────────────────────                                                                                \n",
      "  \u001b[1m \u001b[0m\u001b[1mColumn \u001b[0m\u001b[1m \u001b[0m \u001b[1m \u001b[0m\u001b[1mBefore\u001b[0m\u001b[1m \u001b[0m \u001b[1m \u001b[0m\u001b[1mAfter       \u001b[0m\u001b[1m \u001b[0m                                                                                \n",
      "  ─────────────────────────────────                                                                                \n",
      "  \u001b[38;5;204m \u001b[0m\u001b[38;5;204mid     \u001b[0m\u001b[38;5;204m \u001b[0m \u001b[38;5;214m \u001b[0m\u001b[38;5;214mstring\u001b[0m\u001b[38;5;214m \u001b[0m \u001b[38;5;184m \u001b[0m\u001b[38;5;184mint64       \u001b[0m\u001b[38;5;184m \u001b[0m                                                                                \n",
      "  \u001b[38;5;204m \u001b[0m\u001b[38;5;204mgenre  \u001b[0m\u001b[38;5;204m \u001b[0m \u001b[38;5;214m \u001b[0m\u001b[38;5;214mstring\u001b[0m\u001b[38;5;214m \u001b[0m \u001b[38;5;184m \u001b[0m\u001b[38;5;184mdict<string>\u001b[0m\u001b[38;5;184m \u001b[0m                                                                                \n",
      "  \u001b[38;5;204m \u001b[0m\u001b[38;5;204mmetric \u001b[0m\u001b[38;5;204m \u001b[0m \u001b[38;5;214m \u001b[0m\u001b[38;5;214mstring\u001b[0m\u001b[38;5;214m \u001b[0m \u001b[38;5;184m \u001b[0m\u001b[38;5;184mdouble      \u001b[0m\u001b[38;5;184m \u001b[0m                                                                                \n",
      "  \u001b[38;5;204m \u001b[0m\u001b[38;5;204mcount  \u001b[0m\u001b[38;5;204m \u001b[0m \u001b[38;5;214m \u001b[0m\u001b[38;5;214mstring\u001b[0m\u001b[38;5;214m \u001b[0m \u001b[38;5;184m \u001b[0m\u001b[38;5;184muint8       \u001b[0m\u001b[38;5;184m \u001b[0m                                                                                \n",
      "  \u001b[38;5;204m \u001b[0m\u001b[38;5;204mwebsite\u001b[0m\u001b[38;5;204m \u001b[0m \u001b[38;5;214m \u001b[0m\u001b[38;5;214mstring\u001b[0m\u001b[38;5;214m \u001b[0m \u001b[38;5;184m \u001b[0m\u001b[38;5;184mdict<string>\u001b[0m\u001b[38;5;184m \u001b[0m                                                                                \n",
      "  \u001b[38;5;204m \u001b[0m\u001b[38;5;204mtags   \u001b[0m\u001b[38;5;204m \u001b[0m \u001b[38;5;214m \u001b[0m\u001b[38;5;214mstring\u001b[0m\u001b[38;5;214m \u001b[0m \u001b[38;5;184m \u001b[0m\u001b[38;5;184mlist<string>\u001b[0m\u001b[38;5;184m \u001b[0m                                                                                \n",
      "  \u001b[38;5;204m \u001b[0m\u001b[38;5;204mvecs   \u001b[0m\u001b[38;5;204m \u001b[0m \u001b[38;5;214m \u001b[0m\u001b[38;5;214mstring\u001b[0m\u001b[38;5;214m \u001b[0m \u001b[38;5;184m \u001b[0m\u001b[38;5;184mlist<double>\u001b[0m\u001b[38;5;184m \u001b[0m                                                                                \n",
      "  \u001b[38;5;204m \u001b[0m\u001b[38;5;204mdate   \u001b[0m\u001b[38;5;204m \u001b[0m \u001b[38;5;214m \u001b[0m\u001b[38;5;214mstring\u001b[0m\u001b[38;5;214m \u001b[0m \u001b[38;5;184m \u001b[0m\u001b[38;5;184mtimestamp\u001b[0m\u001b[38;5;184m   \u001b[0m\u001b[38;5;184m \u001b[0m                                                                                \n",
      "  ─────────────────────────────────                                                                                \n",
      "                                                                                                                   \n",
      "\n"
     ]
    }
   ],
   "source": [
    "lector.LOG.setLevel(\"DEBUG\")\n",
    "tbl = lector.read_csv(\"example_1.csv\", log=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f887dfb1",
   "metadata": {
    "hidden": true
   },
   "source": [
    "There is a lot to unpack in this log.\n",
    "\n",
    "Firstly, Lector has inferred and constructed a CSV `Format`, which contains all the necessary information for a CSV parser to parse the file (including the encoding, lines to skip, the separator etc.). Lector has then used this format to instruct `pyarrow.csv.read_csv` to read the file _without_ inferring any column types, simply leaving all columns with the original strings as found in the file (because as shown below, arrow, like pandas, may otherwise import erroneous data, silently and non-recoverable).\n",
    "\n",
    "Lector has then applied its `Autocast` strategy to infer and convert each column to the most appropriate data type. The log shows which converter has been found as most appropriate for each column, and with which configuration the conversion has been applied.\n",
    "\n",
    "A table summarizes the final type for each column. Note that not only did Lector infer types for _all_ columns (including lists), and did so correctly (including the non-ISO date format), but also automatically identified the smallest possible numeric types. None of this is possible with pandas or arrow.\n",
    "\n",
    "Inspecting the arrow table's schema, we can see this in more detail:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "517ef648",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-12-05T17:55:26.348446Z",
     "start_time": "2022-12-05T17:55:26.329716Z"
    },
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<pre style=\"white-space:pre;overflow-x:auto;line-height:normal;font-family:Menlo,'DejaVu Sans Mono',consolas,'Courier New',monospace\"></pre>\n"
      ],
      "text/plain": []
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\u001b[38;20m18:55:26 INFO | lector | 1494666864.<module>:1\u001b[0m \n",
      "                                                                                                                   \n",
      " \u001b[3mSchema                                                                 \u001b[0m                                           \n",
      "  ─────────────────────────────────────────────────────────────────────                                            \n",
      "  \u001b[1m \u001b[0m\u001b[1mColumn \u001b[0m\u001b[1m \u001b[0m \u001b[1m \u001b[0m\u001b[1mType        \u001b[0m\u001b[1m \u001b[0m \u001b[1m \u001b[0m\u001b[1mMeta                                      \u001b[0m\u001b[1m \u001b[0m                                            \n",
      "  ─────────────────────────────────────────────────────────────────────                                            \n",
      "  \u001b[38;5;204m \u001b[0m\u001b[38;5;204mid     \u001b[0m\u001b[38;5;204m \u001b[0m \u001b[38;5;184m \u001b[0m\u001b[38;5;184mint64       \u001b[0m\u001b[38;5;184m \u001b[0m  \u001b[1m{\u001b[0m\u001b[32m'semantic'\u001b[0m: \u001b[32m'number\u001b[0m\u001b[32m[\u001b[0m\u001b[32mInt64\u001b[0m\u001b[32m]\u001b[0m\u001b[32m'\u001b[0m\u001b[1m}\u001b[0m                                                          \n",
      "  \u001b[38;5;204m \u001b[0m\u001b[38;5;204mgenre  \u001b[0m\u001b[38;5;204m \u001b[0m \u001b[38;5;184m \u001b[0m\u001b[38;5;184mdict<string>\u001b[0m\u001b[38;5;184m \u001b[0m  \u001b[1m{\u001b[0m\u001b[32m'semantic'\u001b[0m: \u001b[32m'category'\u001b[0m\u001b[1m}\u001b[0m                                                               \n",
      "  \u001b[38;5;204m \u001b[0m\u001b[38;5;204mmetric \u001b[0m\u001b[38;5;204m \u001b[0m \u001b[38;5;184m \u001b[0m\u001b[38;5;184mdouble      \u001b[0m\u001b[38;5;184m \u001b[0m  \u001b[1m{\u001b[0m\u001b[32m'semantic'\u001b[0m: \u001b[32m'number\u001b[0m\u001b[32m[\u001b[0m\u001b[32mdouble\u001b[0m\u001b[32m]\u001b[0m\u001b[32m'\u001b[0m\u001b[1m}\u001b[0m                                                         \n",
      "  \u001b[38;5;204m \u001b[0m\u001b[38;5;204mcount  \u001b[0m\u001b[38;5;204m \u001b[0m \u001b[38;5;184m \u001b[0m\u001b[38;5;184muint8       \u001b[0m\u001b[38;5;184m \u001b[0m  \u001b[1m{\u001b[0m\u001b[32m'semantic'\u001b[0m: \u001b[32m'number\u001b[0m\u001b[32m[\u001b[0m\u001b[32mUInt8\u001b[0m\u001b[32m]\u001b[0m\u001b[32m'\u001b[0m\u001b[1m}\u001b[0m                                                          \n",
      "  \u001b[38;5;204m \u001b[0m\u001b[38;5;204mcontent\u001b[0m\u001b[38;5;204m \u001b[0m \u001b[38;5;184m \u001b[0m\u001b[38;5;184mstring      \u001b[0m\u001b[38;5;184m \u001b[0m  \u001b[1m{\u001b[0m\u001b[32m'semantic'\u001b[0m: \u001b[32m'text'\u001b[0m\u001b[1m}\u001b[0m                                                                   \n",
      "  \u001b[38;5;204m \u001b[0m\u001b[38;5;204mwebsite\u001b[0m\u001b[38;5;204m \u001b[0m \u001b[38;5;184m \u001b[0m\u001b[38;5;184mdict<string>\u001b[0m\u001b[38;5;184m \u001b[0m  \u001b[1m{\u001b[0m\u001b[32m'semantic'\u001b[0m: \u001b[32m'url'\u001b[0m\u001b[1m}\u001b[0m                                                                    \n",
      "  \u001b[38;5;204m \u001b[0m\u001b[38;5;204mtags   \u001b[0m\u001b[38;5;204m \u001b[0m \u001b[38;5;184m \u001b[0m\u001b[38;5;184mlist<string>\u001b[0m\u001b[38;5;184m \u001b[0m  \u001b[1m{\u001b[0m\u001b[32m'semantic'\u001b[0m: \u001b[32m'list\u001b[0m\u001b[32m[\u001b[0m\u001b[32mcategory\u001b[0m\u001b[32m]\u001b[0m\u001b[32m'\u001b[0m\u001b[1m}\u001b[0m                                                         \n",
      "  \u001b[38;5;204m \u001b[0m\u001b[38;5;204mvecs   \u001b[0m\u001b[38;5;204m \u001b[0m \u001b[38;5;184m \u001b[0m\u001b[38;5;184mlist<double>\u001b[0m\u001b[38;5;184m \u001b[0m  \u001b[1m{\u001b[0m\u001b[32m'semantic'\u001b[0m: \u001b[32m'list\u001b[0m\u001b[32m[\u001b[0m\u001b[32mnumber\u001b[0m\u001b[32m[\u001b[0m\u001b[32mfloat64\u001b[0m\u001b[32m]\u001b[0m\u001b[32m]\u001b[0m\u001b[32m'\u001b[0m\u001b[1m}\u001b[0m                                                  \n",
      "  \u001b[38;5;204m \u001b[0m\u001b[38;5;204mdate   \u001b[0m\u001b[38;5;204m \u001b[0m \u001b[38;5;184m \u001b[0m\u001b[38;5;184mtimestamp\u001b[0m\u001b[38;5;184m   \u001b[0m\u001b[38;5;184m \u001b[0m  \u001b[1m{\u001b[0m\u001b[32m'semantic'\u001b[0m: \u001b[32m'date'\u001b[0m, \u001b[32m'format'\u001b[0m: \u001b[32m'%d/%m/%Y'\u001b[0m\u001b[1m}\u001b[0m                                             \n",
      "  ─────────────────────────────────────────────────────────────────────                                            \n",
      "                                                                                                                   \n",
      "\n"
     ]
    }
   ],
   "source": [
    "LOG.info(pformat(schema_view(tbl.schema, title=\"Schema\")))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bb9716c8",
   "metadata": {
    "hidden": true
   },
   "source": [
    " Note e.g. that Lector distinguishes between two types of \"stringy\" data types: `text` and `category`. The former is stored using arrow's efficient `string` type. In the metadata we indicate via the `semantic` field how the data is to be interpreted. In this case, this is \"text\", meaning natural language text that can be processed e.g. with NLP models. When a column's strings don't seem to be text-like, and when its cardinality is appropriate, it is inferred as categorical instead (e.g. the genre column). Also note that the website column was inferred as categorical, but that Lector has in fact recognized it as containing URLs, and so indicates this in its semantic type field.\n",
    "\n",
    "Finally, neither Arrow nor pandas infer list types automatically, nor timestamps (correctly) without knowing the format beforehand.\n",
    "\n",
    "If you want the parsed result as a pandas DataFrame, simply pass the `to_pandas` option:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "6bf9a6c1",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-12-05T17:55:30.984540Z",
     "start_time": "2022-12-05T17:55:30.934343Z"
    },
    "hidden": true
   },
   "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>id</th>\n",
       "      <th>genre</th>\n",
       "      <th>metric</th>\n",
       "      <th>count</th>\n",
       "      <th>content</th>\n",
       "      <th>website</th>\n",
       "      <th>tags</th>\n",
       "      <th>vecs</th>\n",
       "      <th>date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1234982348728374</td>\n",
       "      <td>a</td>\n",
       "      <td>0.10</td>\n",
       "      <td>1</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>http://www.graphext.com</td>\n",
       "      <td>[a, b, c]</td>\n",
       "      <td>[1.3, 1.4, 1.67]</td>\n",
       "      <td>2022-10-11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>b</td>\n",
       "      <td>0.12</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>Natural language text is different from catego...</td>\n",
       "      <td>https://www.twitter.com</td>\n",
       "      <td>[d]</td>\n",
       "      <td>[0.0, 1.9423]</td>\n",
       "      <td>2022-10-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>9007199254740993</td>\n",
       "      <td>a</td>\n",
       "      <td>3.14</td>\n",
       "      <td>3</td>\n",
       "      <td>The Project · Gutenberg » EBook « of Die Fürstin.</td>\n",
       "      <td>http://www.google.com</td>\n",
       "      <td>[e, f]</td>\n",
       "      <td>[84.234, 12509.99]</td>\n",
       "      <td>2021-10-13</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 id genre  metric  count  \\\n",
       "0  1234982348728374     a    0.10      1   \n",
       "1              <NA>     b    0.12   <NA>   \n",
       "2  9007199254740993     a    3.14      3   \n",
       "\n",
       "                                             content                  website  \\\n",
       "0                                               <NA>  http://www.graphext.com   \n",
       "1  Natural language text is different from catego...  https://www.twitter.com   \n",
       "2  The Project · Gutenberg » EBook « of Die Fürstin.    http://www.google.com   \n",
       "\n",
       "        tags                vecs       date  \n",
       "0  [a, b, c]    [1.3, 1.4, 1.67] 2022-10-11  \n",
       "1        [d]       [0.0, 1.9423] 2022-10-01  \n",
       "2     [e, f]  [84.234, 12509.99] 2021-10-13  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = lector.read_csv(\"example_1.csv\", to_pandas=True)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "05859020",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Note that this doesn't use pyarrow's default conversion, which is not \"correct\" in as much as it converts all numeric columns containing nulls to float instead of using the appropriate nullable pandas dtype (nor does it let you configure manually _how_ to convert columns containing nulls). Lector, in contrast, maintains the most appropriate dtypes automatically:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "be444eeb",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-12-05T17:55:49.769907Z",
     "start_time": "2022-12-05T17:55:49.761251Z"
    },
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "id                  Int64\n",
       "genre            category\n",
       "metric            float64\n",
       "count               UInt8\n",
       "content            string\n",
       "website          category\n",
       "tags               object\n",
       "vecs               object\n",
       "date       datetime64[ns]\n",
       "dtype: object"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e5f4c1b7",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-10-04T11:12:57.887091Z",
     "start_time": "2022-10-04T11:12:57.882364Z"
    },
    "heading_collapsed": true
   },
   "source": [
    "# Customization\n",
    "\n",
    "The full signature of the single-function high-level API looks like this:\n",
    "\n",
    "``` python\n",
    "def read_csv(\n",
    "    fp: FileLike,\n",
    "    encoding: str | EncodingDetector | None = None,\n",
    "    dialect: dict | DialectDetector | None = None,\n",
    "    preamble: int | PreambleRegistry | None = None,\n",
    "    types: dict | Inference = Inference.Auto,\n",
    "    strategy: CastStrategy | None = None,\n",
    "    to_pandas: bool = False,\n",
    "): \n",
    "    ...\n",
    "```\n",
    "\n",
    "I.e. for each feature of the CSV that can and by default is inferred (encoding, dialect, preamble and data types), you can either specify known values, or a an object that knows how to generate them.\n",
    "\n",
    "The following, for example, side-steps inference of encoding and preamble, as well as some column types during inference:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "93fcf59b",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-12-05T17:56:00.396244Z",
     "start_time": "2022-12-05T17:56:00.376108Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "tbl = lector.read_csv(\n",
    "    \"example_1.csv\",\n",
    "    encoding=\"ISO-8859-1\",\n",
    "    preamble=3,\n",
    "    types={\"id\": \"uint64\"},\n",
    "    log=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ce61b109",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Note that specifying concrete types for individual columns will currently fall back to arrow's default type inference for the remaining columns.\n",
    "\n",
    "To fully customize how to infer data types you can use the `strategy` argument. This let's you decide:\n",
    "\n",
    "- which _columns_ to infer data types for (remaining will be left as `string`)\n",
    "- which _data types_ Lector is allowed to infer\n",
    "- exactly _how_ each data type is inferred and how it casts the data\n",
    "\n",
    "For example:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "2ad400ab",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-12-05T17:56:06.111224Z",
     "start_time": "2022-12-05T17:56:06.083277Z"
    },
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<pre style=\"white-space:pre;overflow-x:auto;line-height:normal;font-family:Menlo,'DejaVu Sans Mono',consolas,'Courier New',monospace\"></pre>\n"
      ],
      "text/plain": []
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\u001b[33;1m18:56:06 WARNING | lector | cast.cast_array:124\u001b[0m \n",
      "Got no matching converter for string column 'tags'. Will try fallback \u001b[1;35mCategory\u001b[0m\u001b[1m(\u001b[0m\u001b[33mmax_cardinality\u001b[0m=\u001b[1;36m1\u001b[0m\u001b[1;36m.0\u001b[0m\u001b[1m)\u001b[0m.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "pyarrow.Table\n",
       "id: int64\n",
       "genre: string\n",
       "metric: double\n",
       "count: string\n",
       "content: string\n",
       "website: string\n",
       "tags: dictionary<values=string, indices=int32, ordered=0>\n",
       "vecs: string\n",
       "date: string\n",
       "----\n",
       "id: [[1234982348728374,null,9007199254740993]]\n",
       "genre: [[\"a\",\"b\",\"a\"]]\n",
       "metric: [[0.1,0.12,3.14]]\n",
       "count: [[\"1\",null,\"3\"]]\n",
       "content: [[null,\"Natural language text is different from categorical data.\",\"The Project · Gutenberg » EBook « of Die Fürstin.\"]]\n",
       "website: [[\" http://www.graphext.com\",\" https://www.twitter.com\",\"http://www.google.com\"]]\n",
       "tags: [  -- dictionary:\n",
       "[\"[a,b,c]\",\"[d]\",\"['e', 'f']\"]  -- indices:\n",
       "[0,1,2]]\n",
       "vecs: [[\"[1.3, 1.4, 1.67]\",\"[0, 1.9423]\",\"[\"84.234, 12509.99\"]\"]]\n",
       "date: [[\"11/10/2022\",\"01/10/2022\",\"13/10/2021\"]]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from lector.types import Autocast, Category, Timestamp, List, Number\n",
    "\n",
    "strategy = Autocast(\n",
    "    columns=[\"id\", \"metric\", \"tags\"],\n",
    "    converters=[\n",
    "        Number(threshold=0.85),\n",
    "        Timestamp(threshold=0.85),\n",
    "        # List(threshold=0.95),\n",
    "    ],\n",
    "    fallback=Category(max_cardinality=1.0)\n",
    ")\n",
    "\n",
    "tbl = lector.read_csv(\"example_1.csv\", strategy=strategy)\n",
    "tbl"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ac541340",
   "metadata": {
    "hidden": true
   },
   "source": [
    "The above will \n",
    "\n",
    "- only infer data types for the 3 specified columns (\"id\", \"metric\", \"tags\")\n",
    "- only infer numeric and timestamp types, leaving the rest as unadulterated string types \n",
    "- try the specified fallback type (Category) for columns that could not be cast to any other type. Since the tags columns could not be cast to number or timestamp, it tries the specified fallback type (Category). But using Category's default parameters will not pass the type's validity tests and so the column is not cast. In this case, this happened because Category specifies a maximum cardinality of 0.1 (10%), but all rows here are unique. Setting `fallback=Category(max_cardinality=1.0)` e.g. would successfully apply the type in this case.\n",
    "\n",
    "Different data types may have additional parameters determining how and when they cast data. But all have a `threshold` parameter in common. The threshold indicates the proportion of non-null values in a column that have to be convertible to the given data type for the converter to apply. E.g. the converter `Number(threshold=0.85)` will only cast input data to a numeric data type if at least 85% of the original non-null values can be cast without error.\n",
    "\n",
    "The default `Autocast` strategy used above uses this behaviour to try all allowed converters in specified order until one passes this validity test (and if none passes will either use the fallback converter or leave the column as `string`).\n",
    "\n",
    "See the documentation for more details and try the above with different settings."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a599854e",
   "metadata": {
    "heading_collapsed": true,
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# But why another CSV reader?\n",
    "\n",
    "Below we illustrate why pandas (and arrow) CSV reading is less than optimal. Specifically that...\n",
    "\n",
    "### Pandas isn't very good at reading CSVs\n",
    "\n",
    "- Doesn't know how without a lot of hand-holding\n",
    "- Can be wrong, but you won't know it\n",
    "- Doesn't infer the types pandas itself supports\n",
    "- ...and yet is also slow\n",
    "- ...and uses a lot of memory\n",
    "\n",
    "\n",
    "### Arrow is somewhat better\n",
    "\n",
    "- Fast\n",
    "- Memory-efficient\n",
    "- ...but also doesn't infer many of it's supported types\n",
    "- ...and makes similar errors to pandas\n",
    "\n",
    "In addition, neither pandas nor arrow allow (real) customization of type inference. You can basically use it or leave it. But you cannot configure e.g. the inference of only certain types, or how a specific type is inferred or not.\n",
    "\n",
    "Let's see this in action, or \"how I wasted a morning trying to read a client's CSV file\"...\n",
    "\n",
    "Let's try simply reading the first example file with pandas:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e3dc595a",
   "metadata": {
    "hidden": true
   },
   "source": [
    "## Pandas doesn't know how to read CSVs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "01411260",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-12-06T19:50:44.743300Z",
     "start_time": "2022-12-06T19:50:44.734809Z"
    },
    "hidden": true,
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "ename": "UnicodeDecodeError",
     "evalue": "'utf-8' codec can't decode byte 0xb7 in position 380: invalid start byte",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31mUnicodeDecodeError\u001b[0m\u001b[0;31m:\u001b[0m 'utf-8' codec can't decode byte 0xb7 in position 380: invalid start byte\n"
     ]
    }
   ],
   "source": [
    "fp = \"example_1.csv\"\n",
    "df = pd.read_csv(fp)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9f91acaa",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Hm, seems this isn't utf-8. We'll try common alternatives until we find one that seems to match"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "ab1be486",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-12-06T19:50:45.634349Z",
     "start_time": "2022-12-06T19:50:45.629367Z"
    },
    "hidden": true,
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "ename": "ParserError",
     "evalue": "Error tokenizing data. C error: Expected 1 fields in line 5, saw 5\n",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31mParserError\u001b[0m\u001b[0;31m:\u001b[0m Error tokenizing data. C error: Expected 1 fields in line 5, saw 5\n\n"
     ]
    }
   ],
   "source": [
    "df = pd.read_csv(fp, encoding=\"ISO-8859-1\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1934912c",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Still doesn't work. We have to open the file in a text editor, inspect it manually, and hopefully identify the reason. In this case, we have 3 lines of initial metadata in the file, let's ignore those."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "a530e543",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-12-06T19:50:47.270951Z",
     "start_time": "2022-12-06T19:50:47.243066Z"
    },
    "hidden": true,
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "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></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>id;genre;metric;count;content;website;tags;vecs;date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1234982348728374;a;0.1;1;; http://www.graphext.com;\"[a</th>\n",
       "      <th>b</th>\n",
       "      <th>c]\";\"[1.3</th>\n",
       "      <th>1.4</th>\n",
       "      <td>1.67]\";11/10/2022</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>;b;0.12;;\"Natural language text is different from categorical data.\"; https://www.twitter.com;[d];\"[0</th>\n",
       "      <th>1.9423]\";01/10/2022</th>\n",
       "      <th>NaN</th>\n",
       "      <th>NaN</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9007199254740993;a;3.14;3;\"The Project · Gutenberg » EBook « of Die Fürstin.\";http://www.google.com;\"['e'</th>\n",
       "      <th>'f']\";[\"84.234</th>\n",
       "      <th>12509.99\"];13/10/2021</th>\n",
       "      <th>NaN</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                                                   id;genre;metric;count;content;website;tags;vecs;date\n",
       "1234982348728374;a;0.1;1;; http://www.graphext.... b                    c]\";\"[1.3              1.4                                  1.67]\";11/10/2022  \n",
       ";b;0.12;;\"Natural language text is different fr...  1.9423]\";01/10/2022 NaN                    NaN                                                NaN  \n",
       "9007199254740993;a;3.14;3;\"The Project · Gutenb...  'f']\";[\"84.234       12509.99\"];13/10/2021 NaN                                                NaN  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_csv(fp, encoding=\"ISO-8859-1\", skiprows=3)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c22ad8e4",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Still looks wrong. Pandas hasn't detected the separator as being \";\", so let's specify that also manually:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "8f142dd6",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-12-06T19:50:49.231888Z",
     "start_time": "2022-12-06T19:50:49.211552Z"
    },
    "hidden": true,
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "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>id</th>\n",
       "      <th>genre</th>\n",
       "      <th>metric</th>\n",
       "      <th>count</th>\n",
       "      <th>content</th>\n",
       "      <th>website</th>\n",
       "      <th>tags</th>\n",
       "      <th>vecs</th>\n",
       "      <th>date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.234982e+15</td>\n",
       "      <td>a</td>\n",
       "      <td>0.10</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>http://www.graphext.com</td>\n",
       "      <td>[a,b,c]</td>\n",
       "      <td>[1.3, 1.4, 1.67]</td>\n",
       "      <td>11/10/2022</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>b</td>\n",
       "      <td>0.12</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Natural language text is different from catego...</td>\n",
       "      <td>https://www.twitter.com</td>\n",
       "      <td>[d]</td>\n",
       "      <td>[0, 1.9423]</td>\n",
       "      <td>01/10/2022</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>9.007199e+15</td>\n",
       "      <td>a</td>\n",
       "      <td>3.14</td>\n",
       "      <td>3.0</td>\n",
       "      <td>The Project · Gutenberg » EBook « of Die Fürstin.</td>\n",
       "      <td>http://www.google.com</td>\n",
       "      <td>['e', 'f']</td>\n",
       "      <td>[\"84.234, 12509.99\"]</td>\n",
       "      <td>13/10/2021</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             id genre  metric  count  \\\n",
       "0  1.234982e+15     a    0.10    1.0   \n",
       "1           NaN     b    0.12    NaN   \n",
       "2  9.007199e+15     a    3.14    3.0   \n",
       "\n",
       "                                             content  \\\n",
       "0                                                NaN   \n",
       "1  Natural language text is different from catego...   \n",
       "2  The Project · Gutenberg » EBook « of Die Fürstin.   \n",
       "\n",
       "                    website        tags                  vecs        date  \n",
       "0   http://www.graphext.com     [a,b,c]      [1.3, 1.4, 1.67]  11/10/2022  \n",
       "1   https://www.twitter.com         [d]           [0, 1.9423]  01/10/2022  \n",
       "2     http://www.google.com  ['e', 'f']  [\"84.234, 12509.99\"]  13/10/2021  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv(fp, encoding=\"ISO-8859-1\", skiprows=3, sep=\";\")\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2d8d02b0",
   "metadata": {
    "hidden": true
   },
   "source": [
    "This sort of \"worked\", but the imported data is still wrong:\n",
    "\n",
    "- The id column has floats instead of ints (with wrong values)\n",
    "- The URLs are not clean, they have initial spaces\n",
    "- Pandas doesn't know about lists, of course, so these remain plain strings\n",
    "- Dates haven't been automatically inferred\n",
    "\n",
    "It's easier to see these limitations without pandas Dataframe formatting:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "55477232",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-12-06T19:50:51.802281Z",
     "start_time": "2022-12-06T19:50:51.795582Z"
    },
    "hidden": true,
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "id: [1234982348728374.0, nan, 9007199254740992.0]\n",
      "website: [' http://www.graphext.com', ' https://www.twitter.com', 'http://www.google.com']\n",
      "tags: ['[a,b,c]', '[d]', \"['e', 'f']\"]\n",
      "vecs: ['[1.3, 1.4, 1.67]', '[0, 1.9423]', '[\"84.234, 12509.99\"]']\n",
      "date: ['11/10/2022', '01/10/2022', '13/10/2021']\n"
     ]
    }
   ],
   "source": [
    "for col in (\"id\", \"website\", \"tags\", \"vecs\", \"date\"):\n",
    "    print(f\"{col}: {df[col].tolist()}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5941ef34",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Let's try helping pandas along by giving it the exact data types we want:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "aa60577a",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-12-06T19:50:54.516662Z",
     "start_time": "2022-12-06T19:50:54.487341Z"
    },
    "hidden": true,
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/b2/mplwbs6j15976g80v9bw7c4r0000gn/T/ipykernel_13074/4166074380.py:12: UserWarning: Parsing dates in DD/MM/YYYY format when dayfirst=False (the default) was specified. This may lead to inconsistently parsed dates! Specify a format to ensure consistent parsing.\n",
      "  df = pd.read_csv(\n"
     ]
    },
    {
     "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>id</th>\n",
       "      <th>genre</th>\n",
       "      <th>metric</th>\n",
       "      <th>count</th>\n",
       "      <th>content</th>\n",
       "      <th>website</th>\n",
       "      <th>tags</th>\n",
       "      <th>vecs</th>\n",
       "      <th>date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1234982348728374</td>\n",
       "      <td>a</td>\n",
       "      <td>0.10</td>\n",
       "      <td>1</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>http://www.graphext.com</td>\n",
       "      <td>[a,b,c]</td>\n",
       "      <td>[1.3, 1.4, 1.67]</td>\n",
       "      <td>2022-11-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>b</td>\n",
       "      <td>0.12</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>Natural language text is different from catego...</td>\n",
       "      <td>https://www.twitter.com</td>\n",
       "      <td>[d]</td>\n",
       "      <td>[0, 1.9423]</td>\n",
       "      <td>2022-01-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>9007199254740992</td>\n",
       "      <td>a</td>\n",
       "      <td>3.14</td>\n",
       "      <td>3</td>\n",
       "      <td>The Project · Gutenberg » EBook « of Die Fürstin.</td>\n",
       "      <td>http://www.google.com</td>\n",
       "      <td>['e', 'f']</td>\n",
       "      <td>[\"84.234, 12509.99\"]</td>\n",
       "      <td>2021-10-13</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 id genre  metric  count  \\\n",
       "0  1234982348728374     a    0.10      1   \n",
       "1              <NA>     b    0.12   <NA>   \n",
       "2  9007199254740992     a    3.14      3   \n",
       "\n",
       "                                             content  \\\n",
       "0                                               <NA>   \n",
       "1  Natural language text is different from catego...   \n",
       "2  The Project · Gutenberg » EBook « of Die Fürstin.   \n",
       "\n",
       "                    website        tags                  vecs       date  \n",
       "0   http://www.graphext.com     [a,b,c]      [1.3, 1.4, 1.67] 2022-11-10  \n",
       "1   https://www.twitter.com         [d]           [0, 1.9423] 2022-01-10  \n",
       "2     http://www.google.com  ['e', 'f']  [\"84.234, 12509.99\"] 2021-10-13  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "id                 UInt64\n",
       "genre            category\n",
       "metric            float64\n",
       "count               UInt8\n",
       "content            string\n",
       "website          category\n",
       "tags               object\n",
       "vecs               object\n",
       "date       datetime64[ns]\n",
       "dtype: object"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dtypes = {\n",
    "    \"id\": \"UInt64\",\n",
    "    \"genre\": \"category\",\n",
    "    \"metric\": \"float\",\n",
    "    \"count\": \"UInt8\", \n",
    "    \"content\": \"string\",\n",
    "    \"website\": \"category\",\n",
    "    \"tags\": \"object\",\n",
    "    \"vecs\": \"object\"\n",
    "}\n",
    "\n",
    "df = pd.read_csv(\n",
    "    fp,\n",
    "    encoding=\"ISO-8859-1\",\n",
    "    skiprows=3,\n",
    "    sep=\";\",\n",
    "    dtype=dtypes,\n",
    "    parse_dates=[\"date\"],\n",
    "    infer_datetime_format=True\n",
    ")\n",
    "\n",
    "display(df)\n",
    "df.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "03990e3e",
   "metadata": {
    "hidden": true,
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "Ok, where to begin. Even though we specified the correct data types:\n",
    "\n",
    "- The `id` column was indeed imported as integer, but not without internally passing through a float type apparently, which doesn't have enough precision to represent large integers, and so the value is plain wrong (the original, correct value is `9007199254740993`). If this `id` is the identifier of a row in a database, e.g., we just got majorly screwed. What's more, we've been screwed silently. If you use this table to join with another on the `id` column, for example, your whole analysis may be wrong without you realizing much later on (if ever)\n",
    "- The date column was converted to a date dtype, but also unfortunately wrong. Note that the original strings in the CSV are '11/10/2022', '01/10/2022' and '13/10/2021' and thus the only consistent date format here is `day/month/year` (the only sane format of course). Yet pandas has used mixed formats and wrongly inferred most of them. It even warns us about inconsistent formats, but isn't clever enough to infer the correct format itself.\n",
    "- The `website` URLs aren't clean still, but that was to be expected\n",
    "- The list-like columns are also still and unsurprisingly `object` dtypes containing missing values and strings instead of lists"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "95c600ee",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-09-14T13:26:39.255385Z",
     "start_time": "2022-09-14T13:26:39.250746Z"
    },
    "hidden": true,
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Pandas is slow and memory-hungry"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "710d47a8",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Let's check pandas on a medium large CSV file with 1,468,825 rows and 5 columns:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "85014bc6",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-12-06T19:51:09.487933Z",
     "start_time": "2022-12-06T19:51:09.051567Z"
    },
    "hidden": true,
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "departamento      object\n",
       "provincia         object\n",
       "distrito          object\n",
       "fec_vacunacion    object\n",
       "cantidad           int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv(fpm, sep=\";\")\n",
    "df.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d3ffa325",
   "metadata": {
    "hidden": true
   },
   "source": [
    "pandas has only recognized the correct dtype for a single column, namely the int dtype for the `cantidad` column. Arrow behaves the same way, as does lector if you tell it to use Arrow's native type inference. Let's compare their performance:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "45982e61",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-12-06T19:51:27.640565Z",
     "start_time": "2022-12-06T19:51:14.019426Z"
    },
    "hidden": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "412 ms ± 3.62 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)\n",
      "33 ms ± 226 µs per loop (mean ± std. dev. of 7 runs, 10 loops each)\n",
      "59.5 ms ± 765 µs per loop (mean ± std. dev. of 7 runs, 10 loops each)\n",
      "345 ms ± 2.68 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)\n"
     ]
    }
   ],
   "source": [
    "%timeit pd.read_csv(fpm, sep=\";\")\n",
    "%timeit pa.csv.read_csv(fpm, parse_options=pa.csv.ParseOptions(delimiter=\";\"))\n",
    "%timeit lector.read_csv(fpm, types=lector.Inference.Native)\n",
    "%timeit lector.read_csv(fpm)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "100b77fa",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Note that\n",
    "\n",
    "- pandas is  at least 10x slower than arrow reading this file\n",
    "- letting lector infer the CSV format adds a 30ms overhead. \n",
    "\n",
    "Crucially, letting lector infer the CSV format and _all_ column types (including dates, downcasting ints etc.), is still faster than pandas while producing a more useful result:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "506c0521",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-12-06T19:51:50.130184Z",
     "start_time": "2022-12-06T19:51:49.737537Z"
    },
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "departamento            category\n",
       "provincia               category\n",
       "distrito                category\n",
       "fec_vacunacion    datetime64[ns]\n",
       "cantidad                  uint16\n",
       "dtype: object"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lector.read_csv(fpm, to_pandas=True).dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c705c4b4",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Let't try an even bigger file, this time 707MB on disk, with 900,013 rows and 69 columns:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "d113293f",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-12-06T19:52:38.549841Z",
     "start_time": "2022-12-06T19:52:09.790885Z"
    },
    "hidden": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "10.1 s ± 0 ns per loop (mean ± std. dev. of 1 run, 1 loop each)\n",
      "466 ms ± 26.7 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)\n",
      "607 ms ± 17.8 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)\n",
      "5.01 s ± 15.6 ms per loop (mean ± std. dev. of 2 runs, 1 loop each)\n"
     ]
    }
   ],
   "source": [
    "%timeit -n 1 -r 1 pd.read_csv(fpl, low_memory=False)\n",
    "%timeit pa.csv.read_csv(fpl, parse_options=pa.csv.ParseOptions(invalid_row_handler=lambda r: \"skip\"))\n",
    "%timeit lector.read_csv(fpl, types=lector.Inference.Native)\n",
    "%timeit -n 1 -r 2 lector.read_csv(fpl)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "145c641d",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Again, in terms of pure performance, pandas is 20x slower here than arrow, while lector with arrow's native type inference adds a small overhead only. But this difference is even more impressive if we look at what each output generates.\n",
    "\n",
    "Pandas, even though being rather slow, hasn't done anything at all really:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "bc693aaf",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-12-06T19:53:14.206777Z",
     "start_time": "2022-12-06T19:53:01.595610Z"
    },
    "hidden": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "object     53\n",
      "float64    16\n",
      "dtype: int64\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "'3.0 GB'"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv(fpl, low_memory=False)\n",
    "print(df.dtypes.value_counts())\n",
    "humanize.naturalsize(df.memory_usage(index=True, deep=True).sum())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "97bfb1c7",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Pandas has produced 53 object columns, which could contain anything at all really, and 16 float columns. Also note that the Dataframe occupies 3GB of memory (for a CSV of 707MB on disk)!\n",
    "\n",
    "Arrow, in contrast, while being 20x faster, recognizes at least 6 columns as containing dates (though we were admittedly lucky here because these are stored in the only format arrow understands). Also, its table only uses 780MB of memory, less than a third of pandas, which means we can work with datasets at least 3x bigger on the same machine if we stick to arrow:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "c3dc10cf",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-12-06T19:54:09.224581Z",
     "start_time": "2022-12-06T19:54:08.083173Z"
    },
    "hidden": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "object                 47\n",
      "float64                16\n",
      "datetime64[ns, UTC]     6\n",
      "dtype: int64\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "'780.5 MB'"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tbl = pa.csv.read_csv(fpl, parse_options=pa.csv.ParseOptions(invalid_row_handler=lambda r: \"skip\"))\n",
    "print(tbl.to_pandas().dtypes.value_counts())\n",
    "humanize.naturalsize(tbl.get_total_buffer_size())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b2200279",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Finally, using lector with _smart_ type inference, we're still 2x faster than pandas, while producing a better, more useful, result than both pandas and arrow (we also occupy even less memory by inferring better types):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "709a01d8",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-12-06T19:54:40.890628Z",
     "start_time": "2022-12-06T19:54:34.686161Z"
    },
    "hidden": true,
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "category               45\n",
      "UInt32                  8\n",
      "datetime64[ns, UTC]     6\n",
      "float64                 3\n",
      "string                  3\n",
      "Int32                   1\n",
      "UInt16                  1\n",
      "UInt8                   1\n",
      "object                  1\n",
      "dtype: Int64\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "'316.0 MB'"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from lector.utils import to_pandas\n",
    "\n",
    "tbl = lector.read_csv(fpl)\n",
    "df = to_pandas(tbl)\n",
    "print(df.dtypes.astype(\"string\").value_counts())\n",
    "humanize.naturalsize(tbl.get_total_buffer_size())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6c11ebbc",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Finally, although arrow with default type inference (or equivalently, lector with arrow's native type inference) may often seem to be enough, it in fact commits the same int vs. float conversion error as pandas, and doesn't infer all of its own data types. Using the CSV file `example_2` defined at the top of this notebook, e.g.:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "cf30e0f6",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-12-06T20:02:33.466984Z",
     "start_time": "2022-12-06T20:02:33.446099Z"
    },
    "hidden": true,
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [],
   "source": [
    "tbl = pa.csv.read_csv(\n",
    "    \"example_1.csv\",\n",
    "    read_options=pa.csv.ReadOptions(encoding=\"ISO-8859-1\", skip_rows=3),\n",
    "    parse_options=pa.csv.ParseOptions(delimiter=\";\"),\n",
    "    convert_options=pa.csv.ConvertOptions(strings_can_be_null=True)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "ae4825de",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-12-06T19:59:03.585656Z",
     "start_time": "2022-12-06T19:59:03.578635Z"
    },
    "hidden": true,
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "pyarrow.Table\n",
      "id: int64\n",
      "genre: string\n",
      "metric: double\n",
      "count: int64\n",
      "content: string\n",
      "website: string\n",
      "tags: string\n",
      "vecs: string\n",
      "date: string\n",
      "----\n",
      "id: [[1234982348728374,null,9007199254740993]]\n",
      "genre: [[\"a\",\"b\",\"a\"]]\n",
      "metric: [[0.1,0.12,3.14]]\n",
      "count: [[1,null,3]]\n",
      "content: [[null,\"Natural language text is different from categorical data.\",\"The Project · Gutenberg » EBook « of Die Fürstin.\"]]\n",
      "website: [[\" http://www.graphext.com\",\" https://www.twitter.com\",\"http://www.google.com\"]]\n",
      "tags: [[\"[a,b,c]\",\"[d]\",\"['e', 'f']\"]]\n",
      "vecs: [[\"[1.3, 1.4, 1.67]\",\"[0, 1.9423]\",\"[\"84.234, 12509.99\"]\"]]\n",
      "date: [[\"11/10/2022\",\"01/10/2022\",\"13/10/2021\"]] \n",
      "\n",
      "[1234982348728374, None, 9007199254740993]\n",
      "['[a,b,c]', '[d]', \"['e', 'f']\"]\n",
      "['[1.3, 1.4, 1.67]', '[0, 1.9423]', '[\"84.234, 12509.99\"]']\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "False"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(tbl, \"\\n\")\n",
    "print(tbl.column(\"id\").to_pylist())\n",
    "print(tbl.column(\"tags\").to_pylist())\n",
    "print(tbl.column(\"vecs\").to_pylist())\n",
    "\n",
    "int(tbl.column(\"id\")[2].as_py()) == 18446744073709551615"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c8809b19",
   "metadata": {
    "hidden": true,
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "We see that:\n",
    "\n",
    "- the `id` column has been parsed into wrong values. Its third value should be `18446744073709551615`, not `1234982348728374`. Again, this silent error could create mejor headaches if used in joins e.g., which is to say, this is potentially much worse than a simple \"rounding\" type of error\n",
    "- lists are not recognized (though arrow supports them)\n",
    "- non ISO-formatted dates are also not recognized\n",
    "\n",
    "Lector, on the other hand, imports this data correctly and conveniently, and without a lot of hand-holding:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "cd5f945e",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-12-06T20:09:40.009501Z",
     "start_time": "2022-12-06T20:09:39.969536Z"
    },
    "hidden": true
   },
   "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>id</th>\n",
       "      <th>genre</th>\n",
       "      <th>metric</th>\n",
       "      <th>count</th>\n",
       "      <th>content</th>\n",
       "      <th>website</th>\n",
       "      <th>tags</th>\n",
       "      <th>vecs</th>\n",
       "      <th>date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1234982348728374</td>\n",
       "      <td>a</td>\n",
       "      <td>0.10</td>\n",
       "      <td>1</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>http://www.graphext.com</td>\n",
       "      <td>[a, b, c]</td>\n",
       "      <td>[1.3, 1.4, 1.67]</td>\n",
       "      <td>2022-10-11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>b</td>\n",
       "      <td>0.12</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>Natural language text is different from catego...</td>\n",
       "      <td>https://www.twitter.com</td>\n",
       "      <td>[d]</td>\n",
       "      <td>[0.0, 1.9423]</td>\n",
       "      <td>2022-10-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>18446744073709551615</td>\n",
       "      <td>a</td>\n",
       "      <td>3.14</td>\n",
       "      <td>3</td>\n",
       "      <td>The Project · Gutenberg » EBook « of Die Fürstin.</td>\n",
       "      <td>http://www.google.com</td>\n",
       "      <td>[e, f]</td>\n",
       "      <td>[84.234, 12509.99]</td>\n",
       "      <td>2021-10-13</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     id genre  metric  count  \\\n",
       "0      1234982348728374     a    0.10      1   \n",
       "1                  <NA>     b    0.12   <NA>   \n",
       "2  18446744073709551615     a    3.14      3   \n",
       "\n",
       "                                             content                  website  \\\n",
       "0                                               <NA>  http://www.graphext.com   \n",
       "1  Natural language text is different from catego...  https://www.twitter.com   \n",
       "2  The Project · Gutenberg » EBook « of Die Fürstin.    http://www.google.com   \n",
       "\n",
       "        tags                vecs       date  \n",
       "0  [a, b, c]    [1.3, 1.4, 1.67] 2022-10-11  \n",
       "1        [d]       [0.0, 1.9423] 2022-10-01  \n",
       "2     [e, f]  [84.234, 12509.99] 2021-10-13  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "id                 UInt64\n",
      "genre            category\n",
      "metric            float64\n",
      "count               UInt8\n",
      "content            string\n",
      "website          category\n",
      "tags               object\n",
      "vecs               object\n",
      "date       datetime64[ns]\n",
      "dtype: object\n",
      "\n",
      "Type of items in tags column: <class 'numpy.ndarray'>\n"
     ]
    }
   ],
   "source": [
    "df = lector.read_csv(\"example_2.csv\", to_pandas=True)\n",
    "\n",
    "display(df)\n",
    "print(df.dtypes)\n",
    "print(\"\\nType of items in tags column:\", type(df.tags.iloc[0]))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3d89ae6d",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-09-15T10:11:57.436326Z",
     "start_time": "2022-09-15T10:11:57.431504Z"
    },
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Summary\n",
    "\n",
    "Lector tries to offer a CSV reader that just works. Specifically, one that\n",
    "\n",
    "1. does not require human inspection and inference of CSV parsing parameters\n",
    "2. does not introduce value errors (no erroneous int to float coercion e.g.)\n",
    "3. automatically infers a wide range of data types (more than pandas and arrow)\n",
    "\n",
    "In the __best case__, lector is almost as fast as arrow, but also infers CSV formats (no more guessing of parser parameters).\n",
    "\n",
    "In the __worst case__, lector is still faster than pandas, while\n",
    "\n",
    "  - parsing data _correctly_\n",
    "  - inferring \"all\" the types\n",
    "  - using less memory (when keeping result in arrow)\n",
    "  - being configurable\n",
    "  - being hackable\n",
    "  \n",
    "By \"hackable\" we mean that lector is a small library will easily extensible or even replaceable parts. Its behaviour can easily be changed to fit anyone'e needs, something which cannot be said about pandas (messy codebase) or arrow (C++)."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cb3d4902",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Contribute\n",
    "\n",
    "- Repo: https://github.com/graphext/lector\n",
    "- Docs: https://lector.readthedocs.io/en/latest/"
   ]
  }
 ],
 "metadata": {
  "hide_input": false,
  "kernelspec": {
   "display_name": "Python 3.10.8 ('lector')",
   "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 | packaged by conda-forge | (main, Nov 22 2022, 08:25:29) [Clang 14.0.6 ]"
  },
  "vscode": {
   "interpreter": {
    "hash": "3cef7981a17677deec7b1ce0b2fc9f85b06d2d2b5a0d525a22c34880065b88a4"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}