Faster Pandas_1.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "2a29f7e4-4cfb-4a2e-a8bf-60189e2c0ea8",
   "metadata": {},
   "source": [
    "# LinkedIn Learning\n",
    "# Faster Pandas"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5ec28c25-7a38-47fb-ab16-f373bedee604",
   "metadata": {},
   "source": [
    "## 1. Overview"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "00eaee5d-4edc-41d6-abf7-faeca959b628",
   "metadata": {},
   "source": [
    "### A. Measuring Performance "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "c2f73f0e-a1e6-46a5-a3bc-0b7ff7327ebf",
   "metadata": {},
   "outputs": [],
   "source": [
    "def find_outliers(data):\n",
    "    # Find Outliers in data, return indices of outliers\n",
    "    out = data[(data - data.mean()).abs() > 2 * data.std()]\n",
    "    return out.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "e52aeebe-c908-4576-be44-fa2bbe6f88c9",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "9981c72c-2744-47cb-800b-d258fd0dc39a",
   "metadata": {},
   "outputs": [],
   "source": [
    "data = pd.Series(np.random.randint(50,60,10_000))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "f1b185a5-f915-40d4-a08c-70fa8e455ad5",
   "metadata": {},
   "outputs": [],
   "source": [
    "data[7] = 3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "cf8ec8b2-e963-4045-acbb-3716f6127782",
   "metadata": {},
   "outputs": [],
   "source": [
    "data[1003] = 100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "2fd5c79e-6b9c-4f28-b3c6-7f5be48d3a0b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index([7, 1003], dtype='int64')"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "find_outliers(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "8104ece2-7223-4472-8e04-545380af627d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "458 μs ± 39.7 μs per loop (mean ± std. dev. of 7 runs, 1,000 loops each)\n"
     ]
    }
   ],
   "source": [
    "%timeit find_outliers(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "7037e744-dbd4-475b-aff9-c97e6658bc48",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "\u001b[1;31mDocstring:\u001b[0m\n",
       "Time execution of a Python statement or expression\n",
       "\n",
       "Usage, in line mode:\n",
       "  %timeit [-n<N> -r<R> [-t|-c] -q -p<P> -o] statement\n",
       "or in cell mode:\n",
       "  %%timeit [-n<N> -r<R> [-t|-c] -q -p<P> -o] setup_code\n",
       "  code\n",
       "  code...\n",
       "\n",
       "Time execution of a Python statement or expression using the timeit\n",
       "module.  This function can be used both as a line and cell magic:\n",
       "\n",
       "- In line mode you can time a single-line statement (though multiple\n",
       "  ones can be chained with using semicolons).\n",
       "\n",
       "- In cell mode, the statement in the first line is used as setup code\n",
       "  (executed but not timed) and the body of the cell is timed.  The cell\n",
       "  body has access to any variables created in the setup code.\n",
       "\n",
       "Options:\n",
       "-n<N>: execute the given statement <N> times in a loop. If <N> is not\n",
       "provided, <N> is determined so as to get sufficient accuracy.\n",
       "\n",
       "-r<R>: number of repeats <R>, each consisting of <N> loops, and take the\n",
       "average result.\n",
       "Default: 7\n",
       "\n",
       "-t: use time.time to measure the time, which is the default on Unix.\n",
       "This function measures wall time.\n",
       "\n",
       "-c: use time.clock to measure the time, which is the default on\n",
       "Windows and measures wall time. On Unix, resource.getrusage is used\n",
       "instead and returns the CPU user time.\n",
       "\n",
       "-p<P>: use a precision of <P> digits to display the timing result.\n",
       "Default: 3\n",
       "\n",
       "-q: Quiet, do not print result.\n",
       "\n",
       "-o: return a TimeitResult that can be stored in a variable to inspect\n",
       "    the result in more details.\n",
       "\n",
       ".. versionchanged:: 7.3\n",
       "    User variables are no longer expanded,\n",
       "    the magic line is always left unmodified.\n",
       "\n",
       "Examples\n",
       "--------\n",
       "::\n",
       "\n",
       "  In [1]: %timeit pass\n",
       "  8.26 ns ± 0.12 ns per loop (mean ± std. dev. of 7 runs, 100000000 loops each)\n",
       "\n",
       "  In [2]: u = None\n",
       "\n",
       "  In [3]: %timeit u is None\n",
       "  29.9 ns ± 0.643 ns per loop (mean ± std. dev. of 7 runs, 10000000 loops each)\n",
       "\n",
       "  In [4]: %timeit -r 4 u == None\n",
       "\n",
       "  In [5]: import time\n",
       "\n",
       "  In [6]: %timeit -n1 time.sleep(2)\n",
       "\n",
       "The times reported by %timeit will be slightly higher than those\n",
       "reported by the timeit.py script when variables are accessed. This is\n",
       "due to the fact that %timeit executes the statement in the namespace\n",
       "of the shell, compared with timeit.py, which uses a single setup\n",
       "statement to import function or create variables. Generally, the bias\n",
       "does not matter as long as results from timeit.py are not mixed with\n",
       "those from %timeit.\n",
       "\u001b[1;31mFile:\u001b[0m      c:\\users\\advait\\anaconda3\\lib\\site-packages\\ipython\\core\\magics\\execution.py"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%timeit?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "19b8990a-9cec-4551-a255-0f262692db99",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Collecting pytest-benchmark\n",
      "  Downloading pytest_benchmark-5.1.0-py3-none-any.whl.metadata (25 kB)\n",
      "Collecting pytest>=8.1 (from pytest-benchmark)\n",
      "  Downloading pytest-8.3.4-py3-none-any.whl.metadata (7.5 kB)\n",
      "Requirement already satisfied: py-cpuinfo in c:\\users\\advait\\anaconda3\\lib\\site-packages (from pytest-benchmark) (9.0.0)\n",
      "Requirement already satisfied: colorama in c:\\users\\advait\\anaconda3\\lib\\site-packages (from pytest>=8.1->pytest-benchmark) (0.4.6)\n",
      "Requirement already satisfied: iniconfig in c:\\users\\advait\\anaconda3\\lib\\site-packages (from pytest>=8.1->pytest-benchmark) (1.1.1)\n",
      "Requirement already satisfied: packaging in c:\\users\\advait\\anaconda3\\lib\\site-packages (from pytest>=8.1->pytest-benchmark) (23.2)\n",
      "Collecting pluggy<2,>=1.5 (from pytest>=8.1->pytest-benchmark)\n",
      "  Downloading pluggy-1.5.0-py3-none-any.whl.metadata (4.8 kB)\n",
      "Downloading pytest_benchmark-5.1.0-py3-none-any.whl (44 kB)\n",
      "Downloading pytest-8.3.4-py3-none-any.whl (343 kB)\n",
      "Downloading pluggy-1.5.0-py3-none-any.whl (20 kB)\n",
      "Installing collected packages: pluggy, pytest, pytest-benchmark\n",
      "  Attempting uninstall: pluggy\n",
      "    Found existing installation: pluggy 1.0.0\n",
      "    Uninstalling pluggy-1.0.0:\n",
      "      Successfully uninstalled pluggy-1.0.0\n",
      "  Attempting uninstall: pytest\n",
      "    Found existing installation: pytest 7.4.4\n",
      "    Uninstalling pytest-7.4.4:\n",
      "      Successfully uninstalled pytest-7.4.4\n",
      "Successfully installed pluggy-1.5.0 pytest-8.3.4 pytest-benchmark-5.1.0\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\n",
      "[notice] A new release of pip is available: 24.3.1 -> 25.0\n",
      "[notice] To update, run: python.exe -m pip install --upgrade pip\n"
     ]
    }
   ],
   "source": [
    "!pip install pytest-benchmark"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f7752dea-014c-4516-86e6-e95f0d787cbb",
   "metadata": {},
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "3895d9f5-c2e1-422f-8dee-cb854690c6fe",
   "metadata": {},
   "outputs": [],
   "source": [
    "def gen_data(size, num_outliers):\n",
    "    # Generate data in with size element containing num_outliers outliers/.\n",
    "    # returns the data and outliers\n",
    "    regular = np.random.randint(50, 60, size - num_outliers)\n",
    "    low = np.random.randint(1, 10, num_outliers//2)\n",
    "    high = np.random.randint(100,110, num_outliers - len(low))\n",
    "    data = np.concatenate([regular, low, high])\n",
    "    np.random.shuffle(data)\n",
    "    return pd.Series(data), pd.Series(np.concatenate([low, high]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "e2766db4-c256-4b5c-aa07-acc276410dae",
   "metadata": {},
   "outputs": [],
   "source": [
    "def test_bench_outlier(benchmark):\n",
    "    size = 10_000 # Usual size of data\n",
    "    num_outliers = 5 # Usual number of outiers\n",
    "    data, expected = gen_data(size, num_outliers)\n",
    "    indices = benchmark(OUTLIERS, data)\n",
    "    outliers = data.loc[indices]\n",
    "    assert set(expected) == set(outliers), 'bad result'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "b7cf2b45-9a0c-4fed-8fd8-fa799ab90162",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\u001b[1m============================= test session starts =============================\u001b[0m\n",
      "platform win32 -- Python 3.12.4, pytest-8.3.4, pluggy-1.5.0\n",
      "benchmark: 5.1.0 (defaults: timer=time.perf_counter disable_gc=False min_rounds=5 min_time=0.000005 max_time=1.0 calibration_precision=10 warmup=False warmup_iterations=100000)\n",
      "rootdir: C:\\Users\\Advait\\Desktop\\LinkedIn Learning HSBC\\Faster Pandas\n",
      "plugins: anyio-4.2.0, benchmark-5.1.0\n",
      "collected 1 item\n",
      "\n",
      "test_outliers.py \u001b[32m.\u001b[0m\u001b[32m                                                       [100%]\u001b[0m\n",
      "\n",
      "\n",
      "\u001b[33m---------------------------------------------------- benchmark: 1 tests ----------------------------------------------------\u001b[0m\n",
      "Name (time in us)           Min         Max      Mean   StdDev    Median     IQR  Outliers  OPS (Kops/s)  Rounds  Iterations\n",
      "\u001b[33m----------------------------------------------------------------------------------------------------------------------------\u001b[0m\n",
      "test_bench_outlier   \u001b[1m  259.4000\u001b[0m\u001b[1m  1,083.9000\u001b[0m\u001b[1m  283.1203\u001b[0m\u001b[1m  52.6713\u001b[0m\u001b[1m  270.6500\u001b[0m\u001b[1m  6.2000\u001b[0m    61;161\u001b[1m        3.5321\u001b[0m    1160           1\n",
      "\u001b[33m----------------------------------------------------------------------------------------------------------------------------\u001b[0m\n",
      "\n",
      "Legend:\n",
      "  Outliers: 1 Standard Deviation from Mean; 1.5 IQR (InterQuartile Range) from 1st Quartile and 3rd Quartile.\n",
      "  OPS: Operations Per Second, computed as 1 / Mean\n",
      "\u001b[32m============================== \u001b[32m\u001b[1m1 passed\u001b[0m\u001b[32m in 2.28s\u001b[0m\u001b[32m ==============================\u001b[0m\n"
     ]
    }
   ],
   "source": [
    "!python -m pytest"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "58f3dc73-ad2c-40cb-8b46-13e9e98730fd",
   "metadata": {},
   "source": [
    "### B. Profiling"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "41e9868d-743e-4675-a97c-eaabbd541c57",
   "metadata": {},
   "outputs": [],
   "source": [
    "import re \n",
    "\n",
    "def stem(word):\n",
    "    # Return stem of word\n",
    "    # stem('working') --> work, stem('works') --> work\n",
    "    return re.sub(r'(sling)$', '', word)\n",
    "\n",
    "def tokenize(text):\n",
    "    # Split text to words, ignoring stop words\n",
    "    tokens = []\n",
    "    for tok in re.findall('[a-zA-Z]+', text):\n",
    "        tok = tok.lower()\n",
    "        tok = stem(tok)\n",
    "        if tok not in stop_words:\n",
    "            tokens.append(tok)\n",
    "    return tokens\n",
    "\n",
    "stop_words = {\n",
    "    'a', 'able', 'about', 'across', 'after', 'all', 'almost', 'also', 'am',\n",
    "    'among', 'an', 'and', 'any', 'are', 'as', 'at', 'be', 'because', 'been',\n",
    "    'but', 'by', 'can', 'cannot', 'could', 'dear', 'did', 'do', 'does',\n",
    "    'either', 'else', 'ever', 'every', 'for', 'from', 'get', 'got', 'had',\n",
    "    'has', 'have', 'he', 'her', 'hers', 'him', 'his', 'how', 'however', 'i',\n",
    "    'if', 'in', 'into', 'is', 'it', 'its', 'just', 'least', 'let', 'like',\n",
    "    'likely', 'may', 'me', 'might', 'most', 'must', 'my', 'neither', 'no',\n",
    "    'nor', 'not', 'of', 'off', 'often', 'on', 'only', 'or', 'other', 'our',\n",
    "    'own', 'rather', 'said', 'say', 'says', 'she', 'should', 'since', 'so',\n",
    "    'some', 'than', 'that', 'the', 'their', 'them', 'then', 'there', 'these',\n",
    "    'they', 'this', 'tis', 'to', 'too', 'twas', 'us', 'wants', 'was', 'we',\n",
    "    'were', 'what', 'when', 'where', 'which', 'while', 'who', 'whom', 'why',\n",
    "    'will', 'with', 'would', 'yet', 'you', 'your',\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "f1a23abf-e1b4-4c93-a21b-2a1cb7d8938a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['encourage',\n",
       " 'develop',\n",
       " 'three',\n",
       " 'great',\n",
       " 'virtues',\n",
       " 'programmer',\n",
       " 'laziness',\n",
       " 'impatience',\n",
       " 'hubris']"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%run nlp.py\n",
    "\n",
    "s = 'We will encourage you to develop the three great virtues of a programmer: laziness, impatience, and hubris'\n",
    "\n",
    "tokenize(s)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "72ccdd8f-2c2d-4baa-8f7c-5186eafc6415",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " "
     ]
    },
    {
     "data": {
      "text/plain": [
       "         203 function calls (201 primitive calls) in 0.002 seconds\n",
       "\n",
       "   Ordered by: internal time\n",
       "\n",
       "   ncalls  tottime  percall  cumtime  percall filename:lineno(function)\n",
       "        1    0.001    0.001    0.001    0.001 {method 'disable' of '_lsprof.Profiler' objects}\n",
       "        1    0.000    0.000    0.000    0.000 {built-in method builtins.exec}\n",
       "        1    0.000    0.000    0.002    0.002 {built-in method select.select}\n",
       "        1    0.000    0.000    0.002    0.002 windows_events.py:761(_poll)\n",
       "        1    0.000    0.000    0.000    0.000 attrsettr.py:65(_get_attr_opt)\n",
       "        1    0.000    0.000    0.000    0.000 nlp.py:8(tokenize)\n",
       "        1    0.000    0.000    0.000    0.000 attrsettr.py:42(__getattr__)\n",
       "    37/35    0.000    0.000    0.000    0.000 {built-in method builtins.isinstance}\n",
       "        1    0.000    0.000    0.002    0.002 base_events.py:1910(_run_once)\n",
       "       18    0.000    0.000    0.000    0.000 __init__.py:280(_compile)\n",
       "       17    0.000    0.000    0.000    0.000 __init__.py:179(sub)\n",
       "        1    0.000    0.000    0.000    0.000 {method 'findall' of 're.Pattern' objects}\n",
       "       17    0.000    0.000    0.000    0.000 {method 'sub' of 're.Pattern' objects}\n",
       "        5    0.000    0.000    0.000    0.000 enum.py:726(__call__)\n",
       "        1    0.000    0.000    0.000    0.000 iostream.py:156(_handle_event)\n",
       "        3    0.000    0.000    0.000    0.000 typing.py:392(inner)\n",
       "       17    0.000    0.000    0.000    0.000 nlp.py:3(stem)\n",
       "        2    0.000    0.000    0.000    0.000 enum.py:1562(__and__)\n",
       "        2    0.000    0.000    0.000    0.000 typing.py:1276(__hash__)\n",
       "        1    0.000    0.000    0.000    0.000 __init__.py:209(findall)\n",
       "        1    0.000    0.000    0.000    0.000 zmqstream.py:687(_rebuild_io_state)\n",
       "        9    0.000    0.000    0.000    0.000 enum.py:1544(_get_value)\n",
       "        1    0.000    0.000    0.000    0.000 queue.py:97(empty)\n",
       "        5    0.000    0.000    0.000    0.000 enum.py:1129(__new__)\n",
       "       17    0.000    0.000    0.000    0.000 {method 'lower' of 'str' objects}\n",
       "        1    0.000    0.000    0.000    0.000 zmqstream.py:580(_run_callback)\n",
       "        1    0.000    0.000    0.000    0.000 zmqstream.py:710(_update_handler)\n",
       "        1    0.000    0.000    0.000    0.000 <string>:1(<module>)\n",
       "        1    0.000    0.000    0.000    0.000 <frozen importlib._bootstrap>:1390(_handle_fromlist)\n",
       "        1    0.000    0.000    0.000    0.000 {method '_acquire_restore' of '_thread.RLock' objects}\n",
       "        1    0.000    0.000    0.000    0.000 threading.py:302(__exit__)\n",
       "        1    0.000    0.000    0.000    0.000 typing.py:1212(__instancecheck__)\n",
       "        1    0.000    0.000    0.000    0.000 typing.py:1483(__subclasscheck__)\n",
       "        1    0.000    0.000    0.000    0.000 {built-in method _abc._abc_subclasscheck}\n",
       "        9    0.000    0.000    0.000    0.000 {method 'append' of 'list' objects}\n",
       "        1    0.000    0.000    0.000    0.000 base_events.py:734(time)\n",
       "        1    0.000    0.000    0.000    0.000 enum.py:1551(__or__)\n",
       "        1    0.000    0.000    0.000    0.000 <frozen abc>:121(__subclasscheck__)\n",
       "        1    0.000    0.000    0.000    0.000 {built-in method builtins.issubclass}\n",
       "        1    0.000    0.000    0.002    0.002 windows_events.py:443(select)\n",
       "        1    0.000    0.000    0.000    0.000 {built-in method builtins.max}\n",
       "        1    0.000    0.000    0.000    0.000 {built-in method builtins.min}\n",
       "        1    0.000    0.000    0.000    0.000 zmqstream.py:566(sending)\n",
       "        1    0.000    0.000    0.000    0.000 {built-in method math.ceil}\n",
       "        1    0.000    0.000    0.000    0.000 {built-in method time.monotonic}\n",
       "        1    0.000    0.000    0.000    0.000 {method 'upper' of 'str' objects}\n",
       "        1    0.000    0.000    0.000    0.000 {built-in method builtins.getattr}\n",
       "        3    0.000    0.000    0.000    0.000 {built-in method builtins.len}\n",
       "        1    0.000    0.000    0.000    0.000 queue.py:209(_qsize)\n",
       "        2    0.000    0.000    0.000    0.000 {built-in method builtins.hash}\n",
       "        1    0.000    0.000    0.000    0.000 {built-in method builtins.hasattr}\n",
       "        1    0.000    0.000    0.000    0.000 {method '__exit__' of '_thread.lock' objects}\n",
       "        1    0.000    0.000    0.000    0.000 {method '__exit__' of '_thread.RLock' objects}\n",
       "        1    0.000    0.000    0.000    0.000 zmqstream.py:562(receiving)\n",
       "        1    0.000    0.000    0.000    0.000 typing.py:2173(cast)"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%prun tokenize(s)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "5629cf6f-4178-4879-9d8e-b050519df3b1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " "
     ]
    },
    {
     "data": {
      "text/plain": [
       "         1160748 function calls (1160727 primitive calls) in 0.780 seconds\n",
       "\n",
       "   Ordered by: internal time\n",
       "\n",
       "   ncalls  tottime  percall  cumtime  percall filename:lineno(function)\n",
       "    10000    0.168    0.000    0.760    0.000 nlp.py:8(tokenize)\n",
       "   170000    0.114    0.000    0.367    0.000 __init__.py:179(sub)\n",
       "   180000    0.108    0.000    0.169    0.000 __init__.py:280(_compile)\n",
       "   170000    0.095    0.000    0.095    0.000 {method 'sub' of 're.Pattern' objects}\n",
       "   170000    0.072    0.000    0.439    0.000 nlp.py:3(stem)\n",
       "    10000    0.064    0.000    0.064    0.000 {method 'findall' of 're.Pattern' objects}\n",
       "180145/180141    0.060    0.000    0.060    0.000 {built-in method builtins.isinstance}\n",
       "   170000    0.048    0.000    0.048    0.000 {method 'lower' of 'str' objects}\n",
       "    90005    0.024    0.000    0.024    0.000 {method 'append' of 'list' objects}\n",
       "    10000    0.008    0.000    0.082    0.000 __init__.py:209(findall)\n",
       "      6/3    0.007    0.001    0.235    0.078 {method 'run' of '_contextvars.Context' objects}\n",
       "        1    0.006    0.006    0.529    0.529 <string>:1(<module>)\n",
       "        2    0.001    0.001    0.197    0.098 socket.py:698(send_multipart)\n",
       "        1    0.001    0.001    0.088    0.088 history.py:55(only_when_enabled)\n",
       "       14    0.001    0.000    0.001    0.000 socket.py:621(send)\n",
       "        2    0.000    0.000    0.019    0.010 windows_events.py:482(recv)\n",
       "        1    0.000    0.000    0.000    0.000 {method 'disable' of '_lsprof.Profiler' objects}\n",
       "        5    0.000    0.000    0.000    0.000 attrsettr.py:65(_get_attr_opt)\n",
       "       15    0.000    0.000    0.000    0.000 enum.py:1551(__or__)\n",
       "        1    0.000    0.000    0.011    0.011 futures.py:394(_call_set_state)\n",
       "        2    0.000    0.000    0.208    0.104 iostream.py:156(_handle_event)\n",
       "        2    0.000    0.000    0.287    0.144 iostream.py:275(<lambda>)\n",
       "       66    0.000    0.000    0.000    0.000 enum.py:1544(_get_value)\n",
       "      6/3    0.000    0.000    0.235    0.078 events.py:86(_run)\n",
       "        5    0.000    0.000    0.000    0.000 attrsettr.py:42(__getattr__)\n",
       "       29    0.000    0.000    0.000    0.000 enum.py:726(__call__)\n",
       "        2    0.000    0.000    0.000    0.000 traitlets.py:3474(validate)\n",
       "        1    0.000    0.000    0.000    0.000 history.py:845(writeout_cache)\n",
       "      2/1    0.000    0.000    0.000    0.000 {method 'send' of '_socket.socket' objects}\n",
       "      3/2    0.000    0.000    0.019    0.010 proactor_events.py:792(_loop_self_reading)\n",
       "        3    0.000    0.000    0.246    0.082 base_events.py:1910(_run_once)\n",
       "        2    0.000    0.000    0.000    0.000 socket.py:777(recv_multipart)\n",
       "       29    0.000    0.000    0.000    0.000 enum.py:1129(__new__)\n",
       "        7    0.000    0.000    0.000    0.000 enum.py:1562(__and__)\n",
       "        1    0.000    0.000    0.000    0.000 inspect.py:3123(_bind)\n",
       "        2    0.000    0.000    0.000    0.000 {method 'WSARecv' of '_overlapped.Overlapped' objects}\n",
       "        2    0.000    0.000    0.000    0.000 {method '__exit__' of 'sqlite3.Connection' objects}\n",
       "        2    0.000    0.000    0.000    0.000 threading.py:323(wait)\n",
       "        2    0.000    0.000    0.287    0.144 iostream.py:277(_really_send)\n",
       "        5    0.000    0.000    0.000    0.000 base_events.py:814(_call_soon)\n",
       "        1    0.000    0.000    0.000    0.000 queues.py:225(get)\n",
       "        2    0.000    0.000    0.208    0.104 zmqstream.py:580(_run_callback)\n",
       "        3    0.000    0.000    0.000    0.000 zmqstream.py:687(_rebuild_io_state)\n",
       "        1    0.000    0.000    0.000    0.000 {method 'execute' of 'sqlite3.Connection' objects}\n",
       "      2/1    0.000    0.000    0.000    0.000 proactor_events.py:820(_write_to_self)\n",
       "        2    0.000    0.000    0.000    0.000 windows_events.py:714(_register)\n",
       "        3    0.000    0.000    0.000    0.000 zmqstream.py:710(_update_handler)\n",
       "        2    0.000    0.000    0.000    0.000 windows_events.py:55(__init__)\n",
       "        2    0.000    0.000    0.209    0.104 zmqstream.py:607(_handle_events)\n",
       "      5/1    0.000    0.000    0.000    0.000 {method 'acquire' of '_thread.lock' objects}\n",
       "        6    0.000    0.000    0.000    0.000 typing.py:392(inner)\n",
       "        3    0.000    0.000    0.000    0.000 queue.py:97(empty)\n",
       "        1    0.000    0.000    0.000    0.000 traitlets.py:1527(_notify_observers)\n",
       "      2/1    0.000    0.000    0.000    0.000 base_events.py:838(call_soon_threadsafe)\n",
       "        1    0.000    0.000    0.529    0.529 {built-in method builtins.exec}\n",
       "      3/2    0.000    0.000    0.000    0.000 {built-in method _overlapped.GetQueuedCompletionStatus}\n",
       "        2    0.000    0.000    0.000    0.000 {built-in method _thread.allocate_lock}\n",
       "      2/1    0.000    0.000    0.000    0.000 windows_events.py:761(_poll)\n",
       "        5    0.000    0.000    0.000    0.000 threading.py:299(__enter__)\n",
       "        6    0.000    0.000    0.000    0.000 traitlets.py:676(__get__)\n",
       "        5    0.000    0.000    0.000    0.000 <frozen importlib._bootstrap>:1390(_handle_fromlist)\n",
       "        1    0.000    0.000    0.011    0.011 kernelbase.py:294(poll_control_queue)\n",
       "        2    0.000    0.000    0.000    0.000 traitlets.py:3631(set)\n",
       "        5    0.000    0.000    0.000    0.000 events.py:36(__init__)\n",
       "        1    0.000    0.000    0.000    0.000 asyncio.py:225(add_callback)\n",
       "        1    0.000    0.000    0.000    0.000 decorator.py:199(fix)\n",
       "       10    0.000    0.000    0.000    0.000 {built-in method builtins.hasattr}\n",
       "        5    0.000    0.000    0.000    0.000 {built-in method builtins.getattr}\n",
       "        1    0.000    0.000    0.000    0.000 asyncio.py:539(_start_select)\n",
       "        2    0.000    0.000    0.000    0.000 threading.py:394(notify)\n",
       "        2    0.000    0.000    0.000    0.000 typing.py:1483(__subclasscheck__)\n",
       "        3    0.000    0.000    0.000    0.000 _weakrefset.py:75(__contains__)\n",
       "        2    0.000    0.000    0.000    0.000 traitlets.py:727(_cross_validate)\n",
       "        2    0.000    0.000    0.000    0.000 traitlets.py:689(set)\n",
       "        6    0.000    0.000    0.000    0.000 traitlets.py:629(get)\n",
       "        2    0.000    0.000    0.000    0.000 traitlets.py:718(_validate)\n",
       "        4    0.000    0.000    0.000    0.000 typing.py:1276(__hash__)\n",
       "        2    0.000    0.000    0.208    0.104 zmqstream.py:648(_handle_recv)\n",
       "        1    0.000    0.000    0.000    0.000 queues.py:209(put_nowait)\n",
       "        5    0.000    0.000    0.000    0.000 threading.py:302(__exit__)\n",
       "        3    0.000    0.000    0.000    0.000 {method 'set_result' of '_asyncio.Future' objects}\n",
       "        1    0.000    0.000    0.088    0.088 decorator.py:229(fun)\n",
       "        1    0.000    0.000    0.000    0.000 queues.py:186(put)\n",
       "        2    0.000    0.000    0.000    0.000 typing.py:1212(__instancecheck__)\n",
       "        1    0.000    0.000    0.000    0.000 inspect.py:2939(apply_defaults)\n",
       "        3    0.000    0.000    0.000    0.000 queue.py:209(_qsize)\n",
       "        2    0.000    0.000    0.000    0.000 traitlets.py:3624(validate_elements)\n",
       "        1    0.000    0.000    0.000    0.000 inspect.py:2886(args)\n",
       "       16    0.000    0.000    0.000    0.000 {built-in method builtins.len}\n",
       "        9    0.000    0.000    0.000    0.000 {method 'popleft' of 'collections.deque' objects}\n",
       "        1    0.000    0.000    0.000    0.000 inspect.py:3262(bind)\n",
       "        1    0.000    0.000    0.000    0.000 traitlets.py:1512(_notify_trait)\n",
       "        2    0.000    0.000    0.000    0.000 queues.py:322(_consume_expired)\n",
       "        1    0.000    0.000    0.208    0.208 asyncio.py:612(_handle_select)\n",
       "        3    0.000    0.000    0.000    0.000 base_events.py:785(call_soon)\n",
       "        1    0.000    0.000    0.000    0.000 {method 'getresult' of '_overlapped.Overlapped' objects}\n",
       "        1    0.000    0.000    0.011    0.011 _base.py:337(_invoke_callbacks)\n",
       "        2    0.000    0.000    0.000    0.000 {built-in method _abc._abc_subclasscheck}\n",
       "        4    0.000    0.000    0.000    0.000 base_events.py:734(time)\n",
       "        9    0.000    0.000    0.000    0.000 {method 'append' of 'collections.deque' objects}\n",
       "        1    0.000    0.000    0.000    0.000 queues.py:256(get_nowait)\n",
       "        2    0.000    0.000    0.000    0.000 traitlets.py:708(__set__)\n",
       "        2    0.000    0.000    0.000    0.000 iostream.py:212(_is_master_process)\n",
       "        3    0.000    0.000    0.000    0.000 {method '__enter__' of '_thread.RLock' objects}\n",
       "        1    0.000    0.000    0.011    0.011 _base.py:537(set_result)\n",
       "        1    0.000    0.000    0.000    0.000 windows_events.py:92(set_result)\n",
       "        2    0.000    0.000    0.000    0.000 traitlets.py:2304(validate)\n",
       "        2    0.000    0.000    0.000    0.000 iostream.py:215(_check_mp_mode)\n",
       "        2    0.000    0.000    0.000    0.000 {built-in method builtins.issubclass}\n",
       "        1    0.000    0.000    0.000    0.000 concurrent.py:182(future_set_result_unless_cancelled)\n",
       "        6    0.000    0.000    0.000    0.000 {built-in method builtins.next}\n",
       "        2    0.000    0.000    0.000    0.000 <frozen abc>:121(__subclasscheck__)\n",
       "        2    0.000    0.000    0.000    0.000 windows_events.py:704(_register_with_iocp)\n",
       "        1    0.000    0.000    0.208    0.208 asyncio.py:621(_handle_event)\n",
       "        1    0.000    0.000    0.000    0.000 {method '_acquire_restore' of '_thread.RLock' objects}\n",
       "        3    0.000    0.000    0.000    0.000 {method 'clear' of 'list' objects}\n",
       "        1    0.000    0.000    0.000    0.000 ioloop.py:742(_run_callback)\n",
       "        3    0.000    0.000    0.000    0.000 zmqstream.py:566(sending)\n",
       "        2    0.000    0.000    0.000    0.000 {method 'release' of '_thread.lock' objects}\n",
       "        1    0.000    0.000    0.000    0.000 history.py:839(_writeout_output_cache)\n",
       "       24    0.000    0.000    0.000    0.000 typing.py:2173(cast)\n",
       "        1    0.000    0.000    0.000    0.000 traitlets.py:1523(notify_change)\n",
       "        1    0.000    0.000    0.000    0.000 queues.py:173(qsize)\n",
       "        7    0.000    0.000    0.000    0.000 {method '__exit__' of '_thread.lock' objects}\n",
       "        1    0.000    0.000    0.000    0.000 inspect.py:2909(kwargs)\n",
       "      2/1    0.000    0.000    0.000    0.000 windows_events.py:443(select)\n",
       "        5    0.000    0.000    0.000    0.000 {method 'upper' of 'str' objects}\n",
       "        1    0.000    0.000    0.000    0.000 threading.py:308(_release_save)\n",
       "        1    0.000    0.000    0.000    0.000 threading.py:424(notify_all)\n",
       "        1    0.000    0.000    0.000    0.000 windows_events.py:459(finish_socket_func)\n",
       "        1    0.000    0.000    0.000    0.000 threading.py:627(clear)\n",
       "        3    0.000    0.000    0.000    0.000 {built-in method _contextvars.copy_context}\n",
       "        4    0.000    0.000    0.000    0.000 {built-in method time.monotonic}\n",
       "        1    0.000    0.000    0.000    0.000 queues.py:317(__put_internal)\n",
       "        2    0.000    0.000    0.000    0.000 {method 'add_done_callback' of '_asyncio.Future' objects}\n",
       "        1    0.000    0.000    0.000    0.000 {method '_release_save' of '_thread.RLock' objects}\n",
       "        1    0.000    0.000    0.000    0.000 history.py:833(_writeout_input_cache)\n",
       "        1    0.000    0.000    0.000    0.000 queues.py:312(_put)\n",
       "        3    0.000    0.000    0.000    0.000 {built-in method math.ceil}\n",
       "        5    0.000    0.000    0.000    0.000 base_events.py:539(_check_closed)\n",
       "        1    0.000    0.000    0.208    0.208 asyncio.py:200(_handle_events)\n",
       "        4    0.000    0.000    0.000    0.000 {method 'get' of 'dict' objects}\n",
       "        5    0.000    0.000    0.000    0.000 zmqstream.py:562(receiving)\n",
       "        2    0.000    0.000    0.000    0.000 {built-in method nt.getpid}\n",
       "        2    0.000    0.000    0.000    0.000 {method 'result' of '_asyncio.Future' objects}\n",
       "        1    0.000    0.000    0.000    0.000 {method 'values' of 'mappingproxy' objects}\n",
       "        3    0.000    0.000    0.000    0.000 {method 'items' of 'mappingproxy' objects}\n",
       "        2    0.000    0.000    0.000    0.000 traitlets.py:3486(validate_elements)\n",
       "        9    0.000    0.000    0.000    0.000 base_events.py:2005(get_debug)\n",
       "        1    0.000    0.000    0.000    0.000 {built-in method builtins.max}\n",
       "        3    0.000    0.000    0.000    0.000 {method '__exit__' of '_thread.RLock' objects}\n",
       "        1    0.000    0.000    0.000    0.000 {method 'pop' of 'dict' objects}\n",
       "        1    0.000    0.000    0.000    0.000 zmqstream.py:718(<lambda>)\n",
       "        2    0.000    0.000    0.000    0.000 {method '__enter__' of '_thread.lock' objects}\n",
       "       10    0.000    0.000    0.000    0.000 inspect.py:2798(kind)\n",
       "        4    0.000    0.000    0.000    0.000 {built-in method builtins.hash}\n",
       "        2    0.000    0.000    0.000    0.000 {built-in method builtins.iter}\n",
       "        1    0.000    0.000    0.000    0.000 {built-in method _asyncio.get_running_loop}\n",
       "        2    0.000    0.000    0.000    0.000 {method 'done' of '_asyncio.Future' objects}\n",
       "        3    0.000    0.000    0.000    0.000 {method '_is_owned' of '_thread.RLock' objects}\n",
       "        2    0.000    0.000    0.000    0.000 {method 'fileno' of '_socket.socket' objects}\n",
       "        1    0.000    0.000    0.000    0.000 threading.py:311(_acquire_restore)\n",
       "        1    0.000    0.000    0.000    0.000 queues.py:59(_set_timeout)\n",
       "        2    0.000    0.000    0.000    0.000 windows_events.py:429(_check_closed)\n",
       "        2    0.000    0.000    0.000    0.000 iostream.py:254(closed)\n",
       "        1    0.000    0.000    0.000    0.000 {method 'remove' of 'collections.deque' objects}\n",
       "        2    0.000    0.000    0.000    0.000 {method 'extend' of 'list' objects}\n",
       "        2    0.000    0.000    0.000    0.000 {method 'keys' of 'dict' objects}\n",
       "        1    0.000    0.000    0.000    0.000 {built-in method builtins.min}\n",
       "        1    0.000    0.000    0.000    0.000 queues.py:177(empty)\n",
       "        1    0.000    0.000    0.000    0.000 threading.py:314(_is_owned)\n",
       "        1    0.000    0.000    0.000    0.000 inspect.py:2878(__init__)\n",
       "        3    0.000    0.000    0.000    0.000 proactor_events.py:883(_process_events)\n",
       "        2    0.000    0.000    0.000    0.000 {method 'cancelled' of '_asyncio.Future' objects}\n",
       "        4    0.000    0.000    0.000    0.000 inspect.py:2786(name)\n",
       "        4    0.000    0.000    0.000    0.000 inspect.py:3079(parameters)\n",
       "        1    0.000    0.000    0.000    0.000 queues.py:309(_get)\n",
       "        1    0.000    0.000    0.000    0.000 base_events.py:720(is_closed)\n",
       "        1    0.000    0.000    0.000    0.000 locks.py:224(clear)"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%prun \n",
    "\n",
    "for _ in range(10_000):\n",
    "    tokenize(s)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ad224f2e-97d1-40cc-b1d4-1b789eecfa62",
   "metadata": {},
   "source": [
    "### C. Challege 1 --> Identify Bottleneck "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "51a8328c-11ad-4363-9506-3bcf63a594f1",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "\n",
    "def second(values):\n",
    "    \"\"\"Return second highest value\n",
    "\n",
    "    >>> second([1, 7, 9, 3, 5])\n",
    "    7\n",
    "    \"\"\"\n",
    "    top, second = -1, -1\n",
    "    for value in values:\n",
    "        if value > top:\n",
    "            top, second = value, top\n",
    "        elif value > second:\n",
    "            second = value\n",
    "    return second\n",
    "\n",
    "\n",
    "def median_diff(csv_file):\n",
    "    df = pd.read_csv(csv_file)\n",
    "    top1 = df.groupby('id')['price'].max()\n",
    "    top2 = df.groupby('id')['price'].apply(second)\n",
    "    diffs = top1 - top2\n",
    "    return diffs.median()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "a23a45a8-67f3-4e93-aecb-754c6edcd16d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.013655841283122139"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "median_diff('bids.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "abd8e01d-c59d-4256-b6c9-d47bedb0d0f6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " "
     ]
    },
    {
     "data": {
      "text/plain": [
       "         483288 function calls (483238 primitive calls) in 0.899 seconds\n",
       "\n",
       "   Ordered by: cumulative time\n",
       "\n",
       "   ncalls  tottime  percall  cumtime  percall filename:lineno(function)\n",
       "      2/1    0.000    0.000    0.898    0.898 {built-in method builtins.exec}\n",
       "        1    0.001    0.001    0.898    0.898 <string>:1(<module>)\n",
       "        1    0.002    0.002    0.618    0.618 4240894245.py:19(median_diff)\n",
       "        1    0.000    0.000    0.582    0.582 generic.py:224(apply)\n",
       "        1    0.000    0.000    0.582    0.582 groupby.py:1780(apply)\n",
       "        1    0.001    0.001    0.582    0.582 groupby.py:1850(_python_apply_general)\n",
       "        1    0.036    0.036    0.580    0.580 ops.py:897(apply_groupwise)\n",
       "    10000    0.013    0.000    0.319    0.000 ops.py:1149(__iter__)\n",
       "    10000    0.026    0.000    0.297    0.000 ops.py:1171(_chop)\n",
       "        1    0.000    0.000    0.279    0.279 readers.py:868(read_csv)\n",
       "        1    0.002    0.002    0.183    0.183 readers.py:583(_read)\n",
       "        1    0.000    0.000    0.180    0.180 readers.py:1907(read)\n",
       "        1    0.165    0.165    0.173    0.173 c_parser_wrapper.py:222(read)\n",
       "    10000    0.073    0.000    0.129    0.000 4240894245.py:4(second)\n",
       "    10000    0.057    0.000    0.114    0.000 managers.py:1974(get_slice)\n",
       "        1    0.004    0.004    0.096    0.096 decorator.py:229(fun)\n",
       "        1    0.000    0.000    0.092    0.092 history.py:55(only_when_enabled)\n",
       "        1    0.000    0.000    0.092    0.092 history.py:845(writeout_cache)\n",
       "        1    0.000    0.000    0.089    0.089 history.py:833(_writeout_input_cache)\n",
       "        2    0.088    0.044    0.089    0.045 {method '__exit__' of 'sqlite3.Connection' objects}\n",
       "    10003    0.014    0.000    0.083    0.000 series.py:664(_constructor_from_mgr)\n",
       "    10007    0.047    0.000    0.065    0.000 generic.py:6236(__finalize__)\n",
       "    10002    0.024    0.000    0.056    0.000 base.py:836(__iter__)\n",
       "        2    0.000    0.000    0.055    0.027 ops.py:743(group_info)\n",
       "        2    0.015    0.008    0.055    0.027 ops.py:758(_get_compressed_codes)\n",
       "    10000    0.015    0.000    0.049    0.000 base.py:5425(_getitem_slice)\n",
       "    10006    0.014    0.000    0.044    0.000 series.py:784(name)\n",
       "    10005    0.008    0.000    0.044    0.000 generic.py:339(_from_mgr)\n",
       "        1    0.000    0.000    0.044    0.044 ops.py:622(_get_splitter)\n",
       "        2    0.000    0.000    0.039    0.020 grouper.py:689(codes)\n",
       "        2    0.000    0.000    0.039    0.020 grouper.py:777(_codes_and_uniques)\n",
       "        2    0.005    0.003    0.039    0.020 algorithms.py:610(factorize)\n",
       "    10008    0.023    0.000    0.034    0.000 base.py:649(_simple_new)\n",
       "    20019    0.033    0.000    0.033    0.000 generic.py:6301(__setattr__)\n",
       "        1    0.000    0.000    0.033    0.033 groupby.py:3269(max)\n",
       "        1    0.000    0.000    0.033    0.033 groupby.py:1896(_agg_general)\n",
       "        1    0.000    0.000    0.033    0.033 groupby.py:1957(_cython_agg_general)\n",
       "        1    0.000    0.000    0.032    0.032 base.py:365(grouped_reduce)\n",
       "        1    0.000    0.000    0.032    0.032 groupby.py:1971(array_func)\n",
       "        1    0.000    0.000    0.032    0.032 ops.py:812(_cython_operation)\n",
       "    10011    0.027    0.000    0.032    0.000 generic.py:278(__init__)\n",
       "    10008    0.009    0.000    0.031    0.000 common.py:1571(validate_all_hashable)\n",
       "    30010    0.013    0.000    0.030    0.000 series.py:831(_values)\n",
       "        2    0.028    0.014    0.028    0.014 algorithms.py:548(factorize_array)\n",
       "        1    0.001    0.001    0.024    0.024 ops.py:735(has_dropped_na)\n",
       "10019/10018    0.009    0.000    0.022    0.000 {built-in method builtins.all}\n",
       "    30010    0.015    0.000    0.017    0.000 managers.py:2004(internal_values)\n",
       "    20016    0.007    0.000    0.013    0.000 common.py:1590(<genexpr>)\n",
       "        1    0.000    0.000    0.013    0.013 ops.py:946(_sorted_ids)\n",
       "        1    0.000    0.000    0.008    0.008 ops.py:1162(_sorted_data)\n",
       "        1    0.000    0.000    0.008    0.008 generic.py:4027(take)\n",
       "        1    0.000    0.000    0.008    0.008 managers.py:869(take)\n",
       "40604/40601    0.008    0.000    0.008    0.000 {built-in method builtins.isinstance}\n",
       "        1    0.000    0.000    0.008    0.008 ops.py:523(cython_operation)\n",
       "        1    0.000    0.000    0.008    0.008 ops.py:310(_cython_op_ndim_compat)\n",
       "        1    0.008    0.008    0.008    0.008 ops.py:354(_call_cython_op)\n",
       "        3    0.000    0.000    0.007    0.002 take.py:59(take_nd)\n",
       "        3    0.007    0.002    0.007    0.002 take.py:120(_take_nd_ndarray)\n",
       "        1    0.000    0.000    0.007    0.007 ops.py:939(_sort_idx)\n",
       "        1    0.007    0.007    0.007    0.007 sorting.py:644(get_group_index_sorter)\n",
       "    20016    0.007    0.000    0.007    0.000 {built-in method __new__ of type object at 0x00007FF8A3DB7A20}\n",
       "        1    0.000    0.000    0.007    0.007 frame.py:694(__init__)\n",
       "        1    0.000    0.000    0.007    0.007 construction.py:423(dict_to_mgr)\n",
       "    10028    0.004    0.000    0.007    0.000 inference.py:334(is_hashable)\n",
       "        1    0.001    0.001    0.006    0.006 c_parser_wrapper.py:355(_concatenate_chunks)\n",
       "        2    0.000    0.000    0.006    0.003 algorithms.py:1452(safe_sort)\n",
       "        1    0.000    0.000    0.006    0.006 construction.py:96(arrays_to_mgr)\n",
       "        1    0.000    0.000    0.006    0.006 managers.py:2121(create_block_manager_from_column_arrays)\n",
       "        1    0.000    0.000    0.006    0.006 managers.py:2190(_form_blocks)\n",
       "    10011    0.006    0.000    0.006    0.000 flags.py:51(__init__)\n",
       "        3    0.006    0.002    0.006    0.002 {method 'take' of 'numpy.ndarray' objects}\n",
       "        2    0.005    0.003    0.005    0.003 managers.py:2246(_stack_arrays)\n",
       "    10000    0.005    0.000    0.005    0.000 series.py:1063(axes)\n",
       "    10007    0.005    0.000    0.005    0.000 flags.py:87(allows_duplicate_labels)\n",
       "        3    0.005    0.002    0.005    0.002 concat.py:52(concat_compat)\n",
       "    10010    0.005    0.000    0.005    0.000 base.py:831(_reset_identity)\n",
       "    10009    0.003    0.000    0.003    0.000 managers.py:1837(__init__)\n",
       "    10016    0.003    0.000    0.003    0.000 {method 'append' of 'list' objects}\n",
       "        1    0.003    0.003    0.003    0.003 utils.py:239(maybe_convert_indices)\n",
       "    10157    0.003    0.000    0.003    0.000 {built-in method builtins.getattr}\n",
       "       70    0.000    0.000    0.003    0.000 <frozen codecs>:319(decode)\n",
       "       70    0.003    0.000    0.003    0.000 {built-in method _codecs.utf_8_decode}\n",
       "    20014    0.003    0.000    0.003    0.000 generic.py:405(flags)\n",
       "        1    0.000    0.000    0.003    0.003 managers.py:623(reindex_indexer)\n",
       "        1    0.000    0.000    0.003    0.003 managers.py:708(_slice_take_blocks_ax0)\n",
       "        1    0.000    0.000    0.002    0.002 blocks.py:1287(take_nd)\n",
       "        1    0.000    0.000    0.002    0.002 readers.py:1575(__init__)\n",
       "        1    0.000    0.000    0.002    0.002 readers.py:1848(_make_engine)\n",
       "        1    0.000    0.000    0.002    0.002 range.py:1148(take)\n",
       "    10038    0.002    0.000    0.002    0.000 {built-in method builtins.hash}\n",
       "10205/10164    0.002    0.000    0.002    0.000 {built-in method builtins.len}\n",
       "    10000    0.002    0.000    0.002    0.000 managers.py:1989(index)\n",
       "    10007    0.002    0.000    0.002    0.000 managers.py:1940(_block)\n",
       "      5/4    0.000    0.000    0.002    0.000 series.py:389(__init__)\n",
       "    10007    0.002    0.000    0.002    0.000 flags.py:55(allows_duplicate_labels)\n",
       "        1    0.002    0.002    0.002    0.002 {method 'execute' of 'sqlite3.Connection' objects}\n",
       "    10016    0.002    0.000    0.002    0.000 series.py:734(name)\n",
       "        5    0.002    0.000    0.002    0.000 {method 'astype' of 'numpy.ndarray' objects}\n",
       "    10007    0.002    0.000    0.002    0.000 generic.py:363(attrs)\n",
       "    10004    0.001    0.000    0.001    0.000 managers.py:1828(ndim)\n",
       "       12    0.000    0.000    0.001    0.000 construction.py:517(sanitize_array)\n",
       "        1    0.000    0.000    0.001    0.001 readers.py:1990(__exit__)\n",
       "        1    0.000    0.000    0.001    0.001 readers.py:1622(close)\n",
       "        8    0.001    0.000    0.001    0.000 {method 'reduce' of 'numpy.ufunc' objects}\n",
       "        1    0.001    0.001    0.001    0.001 c_parser_wrapper.py:194(close)\n",
       "        2    0.000    0.000    0.001    0.000 frame.py:9041(groupby)\n",
       "        4    0.000    0.000    0.001    0.000 groupby.py:1296(__init__)\n",
       "        1    0.000    0.000    0.001    0.001 generic.py:378(_wrap_applied_output)\n",
       "        5    0.000    0.000    0.001    0.000 base.py:475(__new__)\n",
       "        2    0.000    0.000    0.001    0.000 grouper.py:846(get_grouper)\n",
       "        4    0.000    0.000    0.001    0.000 cast.py:124(maybe_convert_platform)\n",
       "        4    0.001    0.000    0.001    0.000 {method 'argsort' of 'numpy.ndarray' objects}\n",
       "        1    0.001    0.001    0.001    0.001 c_parser_wrapper.py:60(__init__)\n",
       "        1    0.000    0.000    0.001    0.001 series.py:607(_init_dict)\n",
       "       15    0.000    0.000    0.000    0.000 base.py:7593(ensure_index)\n",
       "        1    0.000    0.000    0.000    0.000 common.py:664(get_handle)\n",
       "        6    0.000    0.000    0.000    0.000 frame.py:4062(__getitem__)\n",
       "        2    0.000    0.000    0.000    0.000 grouper.py:744(_group_index)\n",
       "        2    0.000    0.000    0.000    0.000 base.py:674(_with_infer)\n",
       "        1    0.000    0.000    0.000    0.000 common.py:62(new_method)\n",
       "        1    0.000    0.000    0.000    0.000 arraylike.py:192(__sub__)\n",
       "        1    0.000    0.000    0.000    0.000 series.py:6133(_arith_method)\n",
       "        1    0.000    0.000    0.000    0.000 {method 'max' of 'numpy.ndarray' objects}\n",
       "        1    0.000    0.000    0.000    0.000 _methods.py:39(_amax)\n",
       "        2    0.000    0.000    0.000    0.000 generic.py:1939(__getitem__)\n",
       "        1    0.000    0.000    0.000    0.000 {method 'min' of 'numpy.ndarray' objects}\n",
       "        1    0.000    0.000    0.000    0.000 _methods.py:43(_amin)\n",
       "        6    0.000    0.000    0.000    0.000 frame.py:4626(_get_item_cache)\n",
       "        2    0.000    0.000    0.000    0.000 base.py:232(__getitem__)\n",
       "        5    0.000    0.000    0.000    0.000 cast.py:1580(construct_1d_object_array_from_listlike)\n",
       "        4    0.000    0.000    0.000    0.000 {method 'any' of 'numpy.ndarray' objects}\n",
       "        1    0.000    0.000    0.000    0.000 base.py:1371(_arith_method)\n",
       "        2    0.000    0.000    0.000    0.000 frame.py:3983(_ixs)\n",
       "        1    0.000    0.000    0.000    0.000 {built-in method _io.open}\n",
       "        5    0.000    0.000    0.000    0.000 _methods.py:55(_any)\n",
       "        2    0.000    0.000    0.000    0.000 algorithms.py:262(_get_hashtable_algo)\n",
       "       18    0.000    0.000    0.000    0.000 {built-in method numpy.empty}\n",
       "      101    0.000    0.000    0.000    0.000 generic.py:42(_instancecheck)\n",
       "        2    0.000    0.000    0.000    0.000 traitlets.py:708(__set__)\n",
       "        2    0.000    0.000    0.000    0.000 events.py:86(_run)\n",
       "        1    0.000    0.000    0.000    0.000 base_events.py:1910(_run_once)\n",
       "        2    0.000    0.000    0.000    0.000 traitlets.py:3631(set)\n",
       "        1    0.000    0.000    0.000    0.000 series.py:5773(isna)\n",
       "        1    0.000    0.000    0.000    0.000 generic.py:8693(isna)\n",
       "        2    0.000    0.000    0.000    0.000 series.py:6418(_reduce)\n",
       "        2    0.000    0.000    0.000    0.000 {method 'run' of '_contextvars.Context' objects}\n",
       "        1    0.000    0.000    0.000    0.000 series.py:5136(reindex)\n",
       "        2    0.000    0.000    0.000    0.000 ops.py:782(result_index)\n",
       "        1    0.000    0.000    0.000    0.000 generic.py:5343(reindex)\n",
       "        5    0.000    0.000    0.000    0.000 managers.py:1863(from_array)\n",
       "        1    0.000    0.000    0.000    0.000 missing.py:101(isna)\n",
       "        1    0.000    0.000    0.000    0.000 series.py:6551(median)\n",
       "        1    0.000    0.000    0.000    0.000 missing.py:184(_isna)\n",
       "        1    0.000    0.000    0.000    0.000 generic.py:12424(median)\n",
       "        1    0.000    0.000    0.000    0.000 generic.py:12362(_stat_function)\n",
       "        2    0.000    0.000    0.000    0.000 base.py:5552(equals)\n",
       "        4    0.000    0.000    0.000    0.000 generic.py:2077(__contains__)\n",
       "        2    0.000    0.000    0.000    0.000 _decorators.py:325(wrapper)\n",
       "        2    0.000    0.000    0.000    0.000 traitlets.py:689(set)\n",
       "       10    0.000    0.000    0.000    0.000 base.py:5323(__contains__)\n",
       "        1    0.000    0.000    0.000    0.000 nanops.py:111(f)\n",
       "        1    0.000    0.000    0.000    0.000 common.py:131(close)\n",
       "        1    0.000    0.000    0.000    0.000 iostream.py:117(_run_event_pipe_gc)\n",
       "        1    0.000    0.000    0.000    0.000 series.py:6201(_construct_result)\n",
       "        2    0.000    0.000    0.000    0.000 base.py:1922(rename)\n",
       "        1    0.000    0.000    0.000    0.000 {method 'close' of '_io.TextIOWrapper' objects}\n",
       "        1    0.000    0.000    0.000    0.000 ops.py:771(ngroups)\n",
       "        2    0.000    0.000    0.000    0.000 base.py:1809(set_names)\n",
       "      101    0.000    0.000    0.000    0.000 generic.py:37(_check)\n",
       "        1    0.000    0.000    0.000    0.000 common.py:304(_get_filepath_or_buffer)\n",
       "        2    0.000    0.000    0.000    0.000 frame.py:4608(_box_col_values)\n",
       "        2    0.000    0.000    0.000    0.000 algorithms.py:280(_check_object_for_strings)\n",
       "        2    0.000    0.000    0.000    0.000 algorithms.py:106(_ensure_data)\n",
       "        1    0.000    0.000    0.000    0.000 {method 'disable' of '_lsprof.Profiler' objects}\n",
       "        1    0.000    0.000    0.000    0.000 {built-in method bottleneck.reduce.nanmedian}\n",
       "        2    0.000    0.000    0.000    0.000 generic.py:1953(_gotitem)\n",
       "        1    0.000    0.000    0.000    0.000 series.py:6137(_align_for_op)\n",
       "        6    0.000    0.000    0.000    0.000 config.py:145(_get_option)\n",
       "        1    0.000    0.000    0.000    0.000 decorator.py:199(fix)\n",
       "        2    0.000    0.000    0.000    0.000 missing.py:466(array_equivalent)\n",
       "        4    0.000    0.000    0.000    0.000 algorithms.py:184(_reconstruct_data)\n",
       "       70    0.000    0.000    0.000    0.000 <frozen codecs>:331(getstate)\n",
       "        2    0.000    0.000    0.000    0.000 managers.py:1012(iget)\n",
       "        1    0.000    0.000    0.000    0.000 construction.py:596(_homogenize)\n",
       "        2    0.000    0.000    0.000    0.000 config.py:153(_set_option)\n",
       "        1    0.000    0.000    0.000    0.000 generic.py:6662(copy)\n",
       "        2    0.000    0.000    0.000    0.000 generic.py:5598(<genexpr>)\n",
       "        1    0.000    0.000    0.000    0.000 config.py:479(__enter__)\n",
       "        2    0.000    0.000    0.000    0.000 _dtype.py:346(_name_get)\n",
       "        5    0.000    0.000    0.000    0.000 blocks.py:2716(new_block)\n",
       "        7    0.000    0.000    0.000    0.000 numeric.py:274(full)\n",
       "        2    0.000    0.000    0.000    0.000 traitlets.py:718(_validate)\n",
       "        3    0.000    0.000    0.000    0.000 generic.py:511(_validate_dtype)\n",
       "        1    0.000    0.000    0.000    0.000 base.py:5651(identical)\n",
       "        3    0.000    0.000    0.000    0.000 take.py:564(_take_preprocess_indexer_and_fill_value)\n",
       "        3    0.000    0.000    0.000    0.000 common.py:1596(pandas_dtype)\n",
       "        8    0.000    0.000    0.000    0.000 config.py:127(_get_single_key)\n",
       "        1    0.000    0.000    0.000    0.000 iostream.py:126(_event_pipe_gc)\n",
       "        2    0.000    0.000    0.000    0.000 _asarray.py:27(require)\n",
       "        1    0.000    0.000    0.000    0.000 inspect.py:3262(bind)\n",
       "        6    0.000    0.000    0.000    0.000 generic.py:807(_set_axis)\n",
       "        3    0.000    0.000    0.000    0.000 cast.py:551(maybe_promote)\n",
       "        1    0.000    0.000    0.000    0.000 base.py:842(_engine)\n",
       "        2    0.000    0.000    0.000    0.000 frame.py:678(_constructor_sliced_from_mgr)\n",
       "        8    0.000    0.000    0.000    0.000 {built-in method builtins.any}\n",
       "        1    0.000    0.000    0.000    0.000 numeric.py:2378(array_equal)\n",
       "        2    0.000    0.000    0.000    0.000 tasks.py:653(sleep)\n",
       "        2    0.000    0.000    0.000    0.000 grouper.py:527(__init__)\n",
       "        3    0.000    0.000    0.000    0.000 cast.py:1157(maybe_infer_to_datetimelike)\n",
       "        1    0.000    0.000    0.000    0.000 generic.py:155(_wrap_agged_manager)\n",
       "        7    0.000    0.000    0.000    0.000 blocks.py:2674(get_block_type)\n",
       "        1    0.000    0.000    0.000    0.000 array_ops.py:240(arithmetic_op)\n",
       "        1    0.000    0.000    0.000    0.000 traitlets.py:1512(_notify_trait)\n",
       "        5    0.000    0.000    0.000    0.000 inference.py:273(is_dict_like)\n",
       "        1    0.000    0.000    0.000    0.000 inspect.py:3123(_bind)\n",
       "        5    0.000    0.000    0.000    0.000 base.py:609(_dtype_to_subclass)\n",
       "        9    0.000    0.000    0.000    0.000 common.py:1434(_is_dtype_type)\n",
       "       11    0.000    0.000    0.000    0.000 base.py:7688(maybe_extract_name)\n",
       "        2    0.000    0.000    0.000    0.000 {built-in method numpy.arange}\n",
       "        1    0.000    0.000    0.000    0.000 cast.py:259(maybe_downcast_to_dtype)\n",
       "        1    0.000    0.000    0.000    0.000 managers.py:1782(_consolidate_inplace)\n",
       "        1    0.000    0.000    0.000    0.000 series.py:6459(any)\n",
       "        1    0.000    0.000    0.000    0.000 config.py:485(__exit__)\n",
       "        1    0.000    0.000    0.000    0.000 base_parser.py:191(_validate_parse_dates_presence)\n",
       "        1    0.000    0.000    0.000    0.000 groupby.py:1578(_wrap_aggregated_output)\n",
       "        1    0.000    0.000    0.000    0.000 managers.py:1764(is_consolidated)\n",
       "        2    0.000    0.000    0.000    0.000 generic.py:1826(_check_label_or_level_ambiguity)\n",
       "        1    0.000    0.000    0.000    0.000 base_parser.py:115(__init__)\n",
       "        1    0.000    0.000    0.000    0.000 traitlets.py:1523(notify_change)\n",
       "        3    0.000    0.000    0.000    0.000 base.py:82(shape)\n",
       "        1    0.000    0.000    0.000    0.000 array_ops.py:189(_na_arithmetic_op)\n",
       "        1    0.000    0.000    0.000    0.000 range.py:137(__new__)\n",
       "       17    0.000    0.000    0.000    0.000 <frozen abc>:117(__instancecheck__)\n",
       "        2    0.000    0.000    0.000    0.000 managers.py:180(blknos)\n",
       "        1    0.000    0.000    0.000    0.000 managers.py:1772(_consolidate_check)\n",
       "        5    0.000    0.000    0.000    0.000 common.py:137(is_object_dtype)\n",
       "       90    0.000    0.000    0.000    0.000 {method 'get' of 'dict' objects}\n",
       "        1    0.000    0.000    0.000    0.000 managers.py:557(copy)\n",
       "        1    0.000    0.000    0.000    0.000 traitlets.py:1527(_notify_observers)\n",
       "        2    0.000    0.000    0.000    0.000 _dtype.py:330(_name_includes_bit_suffix)\n",
       "        1    0.000    0.000    0.000    0.000 readers.py:1685(_clean_options)\n",
       "        1    0.000    0.000    0.000    0.000 readers.py:1627(_get_options_with_defaults)\n",
       "        2    0.000    0.000    0.000    0.000 traitlets.py:3474(validate)\n",
       "        2    0.000    0.000    0.000    0.000 common.py:1081(is_numeric_dtype)\n",
       "        1    0.000    0.000    0.000    0.000 expressions.py:226(evaluate)\n",
       "        2    0.000    0.000    0.000    0.000 common.py:1204(is_potential_multi_index)\n",
       "        6    0.000    0.000    0.000    0.000 managers.py:236(set_axis)\n",
       "        8    0.000    0.000    0.000    0.000 config.py:635(_get_root)\n",
       "        6    0.000    0.000    0.000    0.000 traitlets.py:676(__get__)\n",
       "        1    0.000    0.000    0.000    0.000 expressions.py:95(_evaluate_numexpr)\n",
       "        1    0.000    0.000    0.000    0.000 futures.py:311(_set_result_unless_cancelled)\n",
       "        5    0.000    0.000    0.000    0.000 blocks.py:2645(maybe_coerce_values)\n",
       "        2    0.000    0.000    0.000    0.000 common.py:1165(_is_binary_mode)\n",
       "        1    0.000    0.000    0.000    0.000 managers.py:317(apply)\n",
       "        1    0.000    0.000    0.000    0.000 missing.py:664(maybe_fill)\n",
       "        1    0.000    0.000    0.000    0.000 common.py:160(is_url)\n",
       "       35    0.000    0.000    0.000    0.000 {built-in method builtins.hasattr}\n",
       "       12    0.000    0.000    0.000    0.000 construction.py:696(_sanitize_ndim)\n",
       "        1    0.000    0.000    0.000    0.000 nanops.py:482(nanany)\n",
       "        2    0.000    0.000    0.000    0.000 grouper.py:1084(_convert_grouper)\n",
       "        1    0.000    0.000    0.000    0.000 common.py:550(infer_compression)\n",
       "        2    0.000    0.000    0.000    0.000 base.py:773(_view)\n",
       "       17    0.000    0.000    0.000    0.000 {built-in method _abc._abc_instancecheck}\n",
       "        2    0.000    0.000    0.000    0.000 numerictypes.py:357(issubdtype)\n",
       "        2    0.000    0.000    0.000    0.000 common.py:248(stringify_path)\n",
       "        2    0.000    0.000    0.000    0.000 base.py:1768(_set_names)\n",
       "        1    0.000    0.000    0.000    0.000 missing.py:261(_isna_array)\n",
       "       47    0.000    0.000    0.000    0.000 {built-in method builtins.issubclass}\n",
       "        6    0.000    0.000    0.000    0.000 indexing.py:2765(check_dict_or_set_indexers)\n",
       "       25    0.000    0.000    0.000    0.000 {built-in method numpy.asarray}\n",
       "        1    0.000    0.000    0.000    0.000 parse.py:374(urlparse)\n",
       "        1    0.000    0.000    0.000    0.000 {method 'all' of 'numpy.ndarray' objects}\n",
       "        6    0.000    0.000    0.000    0.000 base.py:86(_validate_set_axis)\n",
       "       30    0.000    0.000    0.000    0.000 base.py:909(__len__)\n",
       "        2    0.000    0.000    0.000    0.000 range.py:201(_simple_new)\n",
       "       15    0.000    0.000    0.000    0.000 construction.py:416(extract_array)\n",
       "       10    0.000    0.000    0.000    0.000 common.py:1331(is_ea_or_datetimelike_dtype)\n",
       "        3    0.000    0.000    0.000    0.000 generic.py:586(_get_axis)\n",
       "        1    0.000    0.000    0.000    0.000 {method 'set_result' of '_asyncio.Future' objects}\n",
       "        1    0.000    0.000    0.000    0.000 windows_events.py:443(select)\n",
       "        1    0.000    0.000    0.000    0.000 nanops.py:174(_has_infs)\n",
       "        1    0.000    0.000    0.000    0.000 expressions.py:67(_evaluate_standard)\n",
       "        2    0.000    0.000    0.000    0.000 common.py:633(is_integer_dtype)\n",
       "        1    0.000    0.000    0.000    0.000 base_events.py:743(call_later)\n",
       "        1    0.000    0.000    0.000    0.000 _methods.py:61(_all)\n",
       "        1    0.000    0.000    0.000    0.000 c_parser_wrapper.py:201(_set_noconvert_columns)\n",
       "        2    0.000    0.000    0.000    0.000 common.py:1198(is_bool_dtype)\n",
       "        1    0.000    0.000    0.000    0.000 api.py:386(default_index)\n",
       "        1    0.000    0.000    0.000    0.000 {method 'fill' of 'numpy.ndarray' objects}\n",
       "        1    0.000    0.000    0.000    0.000 windows_events.py:761(_poll)\n",
       "        1    0.000    0.000    0.000    0.000 {built-in method _operator.sub}\n",
       "        3    0.000    0.000    0.000    0.000 common.py:188(_expand_user)\n",
       "       12    0.000    0.000    0.000    0.000 construction.py:735(_sanitize_str_dtypes)\n",
       "        1    0.000    0.000    0.000    0.000 missing.py:564(_array_equivalent_object)\n",
       "        1    0.000    0.000    0.000    0.000 missing.py:305(_isna_string_dtype)\n",
       "        2    0.000    0.000    0.000    0.000 algorithms.py:217(_ensure_arraylike)\n",
       "        7    0.000    0.000    0.000    0.000 common.py:568(require_length_match)\n",
       "        1    0.000    0.000    0.000    0.000 generic.py:160(_get_data_to_aggregate)\n",
       "        2    0.000    0.000    0.000    0.000 _ufunc_config.py:33(seterr)\n",
       "        8    0.000    0.000    0.000    0.000 config.py:676(_translate_key)\n",
       "        1    0.000    0.000    0.000    0.000 ops.py:1191(_get_splitter)\n",
       "        1    0.000    0.000    0.000    0.000 base.py:5170(_get_engine_target)\n",
       "       11    0.000    0.000    0.000    0.000 construction.py:481(ensure_wrapped_if_datetimelike)\n",
       "        7    0.000    0.000    0.000    0.000 base.py:84(<genexpr>)\n",
       "        2    0.000    0.000    0.000    0.000 grouper.py:643(name)\n",
       "        2    0.000    0.000    0.000    0.000 managers.py:1850(from_blocks)\n",
       "        2    0.000    0.000    0.000    0.000 traitlets.py:727(_cross_validate)\n",
       "        5    0.000    0.000    0.000    0.000 generic.py:6284(__getattr__)\n",
       "        3    0.000    0.000    0.000    0.000 threading.py:1220(is_alive)\n",
       "        4    0.000    0.000    0.000    0.000 base.py:5373(__getitem__)\n",
       "        1    0.000    0.000    0.000    0.000 _ufunc_config.py:430(__enter__)\n",
       "        2    0.000    0.000    0.000    0.000 {built-in method numpy.array}\n",
       "        1    0.000    0.000    0.000    0.000 warnings.py:168(simplefilter)\n",
       "        2    0.000    0.000    0.000    0.000 series.py:1471(_set_as_cached)\n",
       "        6    0.000    0.000    0.000    0.000 traitlets.py:629(get)\n",
       "        4    0.000    0.000    0.000    0.000 common.py:131(<lambda>)\n",
       "        1    0.000    0.000    0.000    0.000 base_events.py:785(call_soon)\n",
       "       12    0.000    0.000    0.000    0.000 __init__.py:34(using_copy_on_write)\n",
       "        9    0.000    0.000    0.000    0.000 inference.py:300(<genexpr>)\n",
       "        1    0.000    0.000    0.000    0.000 base_events.py:767(call_at)\n",
       "        4    0.000    0.000    0.000    0.000 numerictypes.py:283(issubclass_)\n",
       "       26    0.000    0.000    0.000    0.000 {method 'pop' of 'dict' objects}\n",
       "        9    0.000    0.000    0.000    0.000 config.py:649(_get_deprecated_option)\n",
       "        2    0.000    0.000    0.000    0.000 groupby.py:1357(__getattr__)\n",
       "        1    0.000    0.000    0.000    0.000 base_parser.py:601(_set_noconvert_dtype_columns)\n",
       "        1    0.000    0.000    0.000    0.000 base_parser.py:362(_make_index)\n",
       "        1    0.000    0.000    0.000    0.000 warnings.py:184(_add_filter)\n",
       "        5    0.000    0.000    0.000    0.000 base.py:591(_ensure_array)\n",
       "        2    0.000    0.000    0.000    0.000 traitlets.py:3624(validate_elements)\n",
       "        2    0.000    0.000    0.000    0.000 common.py:301(maybe_iterable_to_list)\n",
       "        3    0.000    0.000    0.000    0.000 <frozen ntpath>:350(expanduser)\n",
       "       10    0.000    0.000    0.000    0.000 range.py:999(__len__)\n",
       "        1    0.000    0.000    0.000    0.000 config.py:471(__init__)\n",
       "        2    0.000    0.000    0.000    0.000 construction.py:769(_try_cast)\n",
       "        1    0.000    0.000    0.000    0.000 common.py:1233(dedup_names)\n",
       "        4    0.000    0.000    0.000    0.000 groupby.py:1340(<genexpr>)\n",
       "        1    0.000    0.000    0.000    0.000 threading.py:311(_acquire_restore)\n",
       "        1    0.000    0.000    0.000    0.000 events.py:155(cancel)\n",
       "        1    0.000    0.000    0.000    0.000 warnings.py:484(__enter__)\n",
       "        1    0.000    0.000    0.000    0.000 {built-in method builtins.locals}\n",
       "        4    0.000    0.000    0.000    0.000 base_events.py:734(time)\n",
       "        1    0.000    0.000    0.000    0.000 blocks.py:292(make_block_same_class)\n",
       "        1    0.000    0.000    0.000    0.000 base_events.py:814(_call_soon)\n",
       "        1    0.000    0.000    0.000    0.000 inspect.py:2939(apply_defaults)\n",
       "        1    0.000    0.000    0.000    0.000 {method 'any' of 'numpy.generic' objects}\n",
       "        1    0.000    0.000    0.000    0.000 {built-in method numpy.zeros}\n",
       "        8    0.000    0.000    0.000    0.000 {method 'split' of 'str' objects}\n",
       "        2    0.000    0.000    0.000    0.000 blocks.py:219(_can_consolidate)\n",
       "        1    0.000    0.000    0.000    0.000 common.py:81(get_op_result_name)\n",
       "        3    0.000    0.000    0.000    0.000 threading.py:302(__exit__)\n",
       "       12    0.000    0.000    0.000    0.000 generic.py:572(_get_axis_number)\n",
       "        1    0.000    0.000    0.000    0.000 base_parser.py:350(_maybe_make_multi_index_columns)\n",
       "        1    0.000    0.000    0.000    0.000 history.py:839(_writeout_output_cache)\n",
       "        1    0.000    0.000    0.000    0.000 blocks.py:253(fill_value)\n",
       "        1    0.000    0.000    0.000    0.000 ops.py:268(_get_out_dtype)\n",
       "        4    0.000    0.000    0.000    0.000 generic.py:667(_info_axis)\n",
       "        1    0.000    0.000    0.000    0.000 common.py:292(is_fsspec_url)\n",
       "        2    0.000    0.000    0.000    0.000 {method 'format' of 'str' objects}\n",
       "        1    0.000    0.000    0.000    0.000 config.py:688(_warn_if_deprecated)\n",
       "        1    0.000    0.000    0.000    0.000 threading.py:627(clear)\n",
       "        2    0.000    0.000    0.000    0.000 events.py:36(__init__)\n",
       "        4    0.000    0.000    0.000    0.000 grouper.py:947(<genexpr>)\n",
       "        2    0.000    0.000    0.000    0.000 {built-in method builtins.sorted}\n",
       "        6    0.000    0.000    0.000    0.000 common.py:372(apply_if_callable)\n",
       "        2    0.000    0.000    0.000    0.000 base.py:1765(_get_names)\n",
       "       48    0.000    0.000    0.000    0.000 typing.py:2173(cast)\n",
       "        1    0.000    0.000    0.000    0.000 readers.py:2133(_refine_defaults_read)\n",
       "        1    0.000    0.000    0.000    0.000 ops.py:1129(__init__)\n",
       "        1    0.000    0.000    0.000    0.000 cast.py:331(maybe_downcast_numeric)\n",
       "        7    0.000    0.000    0.000    0.000 multiarray.py:1080(copyto)\n",
       "        3    0.000    0.000    0.000    0.000 take.py:325(_get_take_nd_function)\n",
       "        1    0.000    0.000    0.000    0.000 ops.py:639(group_keys_seq)\n",
       "        1    0.000    0.000    0.000    0.000 base_parser.py:871(_do_date_conversions)\n",
       "        1    0.000    0.000    0.000    0.000 managers.py:2320(_preprocess_slice_or_indexer)\n",
       "        1    0.000    0.000    0.000    0.000 events.py:111(__init__)\n",
       "       12    0.000    0.000    0.000    0.000 construction.py:758(_maybe_repeat)\n",
       "        1    0.000    0.000    0.000    0.000 inspect.py:2886(args)\n",
       "        2    0.000    0.000    0.000    0.000 base.py:3777(get_loc)\n",
       "        4    0.000    0.000    0.000    0.000 grouper.py:949(<genexpr>)\n",
       "        1    0.000    0.000    0.000    0.000 ops.py:249(_get_output_shape)\n",
       "        1    0.000    0.000    0.000    0.000 <string>:1(<lambda>)\n",
       "        1    0.000    0.000    0.000    0.000 _ufunc_config.py:435(__exit__)\n",
       "        2    0.000    0.000    0.000    0.000 grouper.py:983(is_in_axis)\n",
       "        1    0.000    0.000    0.000    0.000 base.py:214(_obj_with_exclusions)\n",
       "        4    0.000    0.000    0.000    0.000 common.py:126(_classes_and_not_datetimelike)\n",
       "        2    0.000    0.000    0.000    0.000 traitlets.py:2304(validate)\n",
       "        2    0.000    0.000    0.000    0.000 inference.py:105(is_file_like)\n",
       "        1    0.000    0.000    0.000    0.000 {method 'join' of 'str' objects}\n",
       "        1    0.000    0.000    0.000    0.000 {built-in method _thread.allocate_lock}\n",
       "        1    0.000    0.000    0.000    0.000 readers.py:1674(_check_file_or_buffer)\n",
       "        1    0.000    0.000    0.000    0.000 ops.py:512(_validate_axis)\n",
       "        8    0.000    0.000    0.000    0.000 config.py:617(_select_options)\n",
       "        4    0.000    0.000    0.000    0.000 common.py:1270(is_1d_only_ea_dtype)\n",
       "        1    0.000    0.000    0.000    0.000 base.py:549(find)\n",
       "        1    0.000    0.000    0.000    0.000 blocks.py:790(copy)\n",
       "        2    0.000    0.000    0.000    0.000 threading.py:299(__enter__)\n",
       "        3    0.000    0.000    0.000    0.000 threading.py:1153(_wait_for_tstate_lock)\n",
       "        1    0.000    0.000    0.000    0.000 array_ops.py:507(maybe_prepare_scalar_for_op)\n",
       "        2    0.000    0.000    0.000    0.000 blocks.py:214(is_extension)\n",
       "        3    0.000    0.000    0.000    0.000 managers.py:2177(_grouping_func)\n",
       "        2    0.000    0.000    0.000    0.000 grouper.py:638(_passed_categorical)\n",
       "        5    0.000    0.000    0.000    0.000 common.py:123(<lambda>)\n",
       "        1    0.000    0.000    0.000    0.000 {built-in method _overlapped.GetQueuedCompletionStatus}\n",
       "        2    0.000    0.000    0.000    0.000 _ufunc_config.py:132(geterr)\n",
       "        2    0.000    0.000    0.000    0.000 ops.py:578(__init__)\n",
       "        2    0.000    0.000    0.000    0.000 ops.py:696(names)\n",
       "        2    0.000    0.000    0.000    0.000 groupby.py:5565(_reindex_output)\n",
       "        1    0.000    0.000    0.000    0.000 base_parser.py:279(_extract_multi_indexer_columns)\n",
       "        1    0.000    0.000    0.000    0.000 base_events.py:447(create_future)\n",
       "        1    0.000    0.000    0.000    0.000 series.py:914(__len__)\n",
       "        5    0.000    0.000    0.000    0.000 construction.py:688(_sanitize_non_ordered)\n",
       "        1    0.000    0.000    0.000    0.000 base.py:456(_engine_type)\n",
       "       16    0.000    0.000    0.000    0.000 ops.py:592(groupings)\n",
       "       10    0.000    0.000    0.000    0.000 {built-in method builtins.callable}\n",
       "        7    0.000    0.000    0.000    0.000 {built-in method builtins.next}\n",
       "        1    0.000    0.000    0.000    0.000 copy.py:61(copy)\n",
       "        6    0.000    0.000    0.000    0.000 {method 'items' of 'dict' objects}\n",
       "        1    0.000    0.000    0.000    0.000 common.py:207(validate_header_arg)\n",
       "        1    0.000    0.000    0.000    0.000 dispatch.py:17(should_extension_dispatch)\n",
       "        2    0.000    0.000    0.000    0.000 {method 'clear' of 'list' objects}\n",
       "        2    0.000    0.000    0.000    0.000 common.py:1040(needs_i8_conversion)\n",
       "        6    0.000    0.000    0.000    0.000 __init__.py:42(warn_copy_on_write)\n",
       "        2    0.000    0.000    0.000    0.000 range.py:553(equals)\n",
       "        4    0.000    0.000    0.000    0.000 grouper.py:948(<genexpr>)\n",
       "        2    0.000    0.000    0.000    0.000 grouper.py:999(is_in_obj)\n",
       "        1    0.000    0.000    0.000    0.000 base_parser.py:1269(_process_date_conversion)\n",
       "        1    0.000    0.000    0.000    0.000 missing.py:673(na_value_for_dtype)\n",
       "        1    0.000    0.000    0.000    0.000 common.py:103(_maybe_match_name)\n",
       "        2    0.000    0.000    0.000    0.000 common.py:1229(<genexpr>)\n",
       "        5    0.000    0.000    0.000    0.000 common.py:121(classes)\n",
       "        1    0.000    0.000    0.000    0.000 <frozen codecs>:309(__init__)\n",
       "        1    0.000    0.000    0.000    0.000 common.py:188(all_none)\n",
       "        1    0.000    0.000    0.000    0.000 blocks.py:2795(extend_blocks)\n",
       "        2    0.000    0.000    0.000    0.000 base.py:5676(<genexpr>)\n",
       "        2    0.000    0.000    0.000    0.000 blocks.py:1253(iget)\n",
       "        2    0.000    0.000    0.000    0.000 grouper.py:1080(_is_label_like)\n",
       "        9    0.000    0.000    0.000    0.000 {method 'endswith' of 'str' objects}\n",
       "        1    0.000    0.000    0.000    0.000 enum.py:202(__get__)\n",
       "        2    0.000    0.000    0.000    0.000 common.py:91(ensure_python_int)\n",
       "        1    0.000    0.000    0.000    0.000 base.py:540(<genexpr>)\n",
       "        1    0.000    0.000    0.000    0.000 base.py:332(array)\n",
       "        1    0.000    0.000    0.000    0.000 nanops.py:154(_bn_ok_dtype)\n",
       "        3    0.000    0.000    0.000    0.000 {method 'copy' of 'dict' objects}\n",
       "        1    0.000    0.000    0.000    0.000 {method 'remove' of 'list' objects}\n",
       "        1    0.000    0.000    0.000    0.000 inspect.py:2909(kwargs)\n",
       "        1    0.000    0.000    0.000    0.000 parse.py:119(_coerce_args)\n",
       "        1    0.000    0.000    0.000    0.000 ops.py:692(levels)\n",
       "        2    0.000    0.000    0.000    0.000 base.py:791(is_)\n",
       "        1    0.000    0.000    0.000    0.000 {method '_acquire_restore' of '_thread.RLock' objects}\n",
       "        5    0.000    0.000    0.000    0.000 base.py:1671(name)\n",
       "        2    0.000    0.000    0.000    0.000 config.py:665(_get_registered_option)\n",
       "        2    0.000    0.000    0.000    0.000 frame.py:1030(axes)\n",
       "        3    0.000    0.000    0.000    0.000 blocks.py:718(dtype)\n",
       "        1    0.000    0.000    0.000    0.000 threading.py:308(_release_save)\n",
       "        1    0.000    0.000    0.000    0.000 common.py:1117(_maybe_memory_map)\n",
       "        2    0.000    0.000    0.000    0.000 common.py:1399(_get_dtype)\n",
       "        4    0.000    0.000    0.000    0.000 common.py:152(cast_scalar_indexer)\n",
       "        1    0.000    0.000    0.000    0.000 common.py:515(get_compression_method)\n",
       "        2    0.000    0.000    0.000    0.000 {built-in method numpy.seterrobj}\n",
       "        1    0.000    0.000    0.000    0.000 {method 'match' of 're.Pattern' objects}\n",
       "      6/3    0.000    0.000    0.000    0.000 {method 'acquire' of '_thread.lock' objects}\n",
       "        5    0.000    0.000    0.000    0.000 {method '__exit__' of '_thread.lock' objects}\n",
       "        2    0.000    0.000    0.000    0.000 traitlets.py:3486(validate_elements)\n",
       "        5    0.000    0.000    0.000    0.000 base.py:974(dtype)\n",
       "        3    0.000    0.000    0.000    0.000 {method 'append' of 'collections.deque' objects}\n",
       "        9    0.000    0.000    0.000    0.000 {method 'lower' of 'str' objects}\n",
       "        3    0.000    0.000    0.000    0.000 {built-in method builtins.iter}\n",
       "        1    0.000    0.000    0.000    0.000 base.py:74(__len__)\n",
       "        1    0.000    0.000    0.000    0.000 ops.py:284(_get_result_dtype)\n",
       "        2    0.000    0.000    0.000    0.000 {method 'upper' of 'str' objects}\n",
       "        3    0.000    0.000    0.000    0.000 {method 'startswith' of 'str' objects}\n",
       "        1    0.000    0.000    0.000    0.000 function.py:413(validate_func)\n",
       "        1    0.000    0.000    0.000    0.000 c_parser_wrapper.py:392(ensure_dtype_objs)\n",
       "        2    0.000    0.000    0.000    0.000 generic.py:696(ndim)\n",
       "        2    0.000    0.000    0.000    0.000 {method 'keys' of 'dict' objects}\n",
       "        1    0.000    0.000    0.000    0.000 warnings.py:505(__exit__)\n",
       "        1    0.000    0.000    0.000    0.000 generic.py:592(_get_block_manager_axis)\n",
       "       14    0.000    0.000    0.000    0.000 base.py:5144(_values)\n",
       "        1    0.000    0.000    0.000    0.000 ops.py:1113(_is_indexed_like)\n",
       "        1    0.000    0.000    0.000    0.000 {built-in method _codecs.lookup}\n",
       "        1    0.000    0.000    0.000    0.000 common.py:633(is_builtin_func)\n",
       "        3    0.000    0.000    0.000    0.000 {method 'pop' of 'set' objects}\n",
       "        1    0.000    0.000    0.000    0.000 ops.py:212(_get_cython_vals)\n",
       "        1    0.000    0.000    0.000    0.000 base_parser.py:1123(_make_date_converter)\n",
       "        1    0.000    0.000    0.000    0.000 base_parser.py:1426(_validate_parse_dates_arg)\n",
       "        4    0.000    0.000    0.000    0.000 {built-in method time.monotonic}\n",
       "        1    0.000    0.000    0.000    0.000 {method 'values' of 'mappingproxy' objects}\n",
       "        2    0.000    0.000    0.000    0.000 {method 'add' of 'set' objects}\n",
       "        2    0.000    0.000    0.000    0.000 _dtype.py:24(_kind_name)\n",
       "        1    0.000    0.000    0.000    0.000 construction.py:196(mgr_to_mgr)\n",
       "        2    0.000    0.000    0.000    0.000 config.py:886(inner)\n",
       "        3    0.000    0.000    0.000    0.000 __init__.py:55(using_pyarrow_string_dtype)\n",
       "        1    0.000    0.000    0.000    0.000 enum.py:1292(value)\n",
       "        3    0.000    0.000    0.000    0.000 function.py:64(__call__)\n",
       "        1    0.000    0.000    0.000    0.000 threading.py:314(_is_owned)\n",
       "        2    0.000    0.000    0.000    0.000 _validators.py:226(validate_bool_kwarg)\n",
       "        1    0.000    0.000    0.000    0.000 {method 'update' of 'dict' objects}\n",
       "        1    0.000    0.000    0.000    0.000 managers.py:291(arrays)\n",
       "        3    0.000    0.000    0.000    0.000 {method 'items' of 'mappingproxy' objects}\n",
       "        1    0.000    0.000    0.000    0.000 {built-in method builtins.max}\n",
       "        2    0.000    0.000    0.000    0.000 grouper.py:722(_result_index)\n",
       "        2    0.000    0.000    0.000    0.000 {built-in method math.ceil}\n",
       "        1    0.000    0.000    0.000    0.000 events.py:72(cancel)\n",
       "        1    0.000    0.000    0.000    0.000 groupby.py:966(_selected_obj)\n",
       "        7    0.000    0.000    0.000    0.000 series.py:1480(_clear_item_cache)\n",
       "        1    0.000    0.000    0.000    0.000 base_parser.py:893(_check_data_length)\n",
       "        2    0.000    0.000    0.000    0.000 managers.py:196(blklocs)\n",
       "        1    0.000    0.000    0.000    0.000 base_parser.py:255(_has_complex_date_col)\n",
       "       10    0.000    0.000    0.000    0.000 inspect.py:2798(kind)\n",
       "        1    0.000    0.000    0.000    0.000 readers.py:2056(_clean_na_values)\n",
       "        4    0.000    0.000    0.000    0.000 base.py:363(ndim)\n",
       "        1    0.000    0.000    0.000    0.000 {built-in method _heapq.heappop}\n",
       "        2    0.000    0.000    0.000    0.000 {method '__enter__' of '_thread.lock' objects}\n",
       "        2    0.000    0.000    0.000    0.000 {method 'extend' of 'list' objects}\n",
       "        1    0.000    0.000    0.000    0.000 warnings.py:458(__init__)\n",
       "        1    0.000    0.000    0.000    0.000 numba_.py:18(maybe_use_numba)\n",
       "        2    0.000    0.000    0.000    0.000 {method 'popleft' of 'collections.deque' objects}\n",
       "        1    0.000    0.000    0.000    0.000 missing.py:131(dispatch_fill_zeros)\n",
       "        1    0.000    0.000    0.000    0.000 base.py:2313(is_unique)\n",
       "        3    0.000    0.000    0.000    0.000 base_parser.py:1447(is_index_col)\n",
       "        4    0.000    0.000    0.000    0.000 {built-in method numpy.geterrobj}\n",
       "        3    0.000    0.000    0.000    0.000 multiarray.py:153(concatenate)\n",
       "        1    0.000    0.000    0.000    0.000 <string>:2(__init__)\n",
       "        2    0.000    0.000    0.000    0.000 grouper.py:661(_ilevel)\n",
       "        2    0.000    0.000    0.000    0.000 {method 'values' of 'dict' objects}\n",
       "        2    0.000    0.000    0.000    0.000 base_events.py:539(_check_closed)\n",
       "        1    0.000    0.000    0.000    0.000 readers.py:2365(_validate_skipfooter)\n",
       "        3    0.000    0.000    0.000    0.000 {built-in method _warnings._filters_mutated}\n",
       "        3    0.000    0.000    0.000    0.000 threading.py:601(is_set)\n",
       "        4    0.000    0.000    0.000    0.000 base_events.py:2005(get_debug)\n",
       "        1    0.000    0.000    0.000    0.000 ops.py:125(__init__)\n",
       "        1    0.000    0.000    0.000    0.000 {built-in method _codecs.lookup_error}\n",
       "        3    0.000    0.000    0.000    0.000 {built-in method nt.fspath}\n",
       "        1    0.000    0.000    0.000    0.000 {built-in method sys.getfilesystemencoding}\n",
       "        1    0.000    0.000    0.000    0.000 <frozen codecs>:260(__init__)\n",
       "        1    0.000    0.000    0.000    0.000 {method 'cancelled' of '_asyncio.Future' objects}\n",
       "        4    0.000    0.000    0.000    0.000 inspect.py:3079(parameters)\n",
       "        1    0.000    0.000    0.000    0.000 _validators.py:450(check_dtype_backend)\n",
       "        1    0.000    0.000    0.000    0.000 readers.py:2267(_extract_dialect)\n",
       "        1    0.000    0.000    0.000    0.000 base_parser.py:986(_validate_usecols_arg)\n",
       "        1    0.000    0.000    0.000    0.000 {built-in method _contextvars.copy_context}\n",
       "        1    0.000    0.000    0.000    0.000 {method 'encode' of 'str' objects}\n",
       "        1    0.000    0.000    0.000    0.000 groupby.py:1567(_maybe_transpose_result)\n",
       "        1    0.000    0.000    0.000    0.000 {built-in method _heapq.heappush}\n",
       "        1    0.000    0.000    0.000    0.000 {method '__exit__' of '_thread.RLock' objects}\n",
       "        1    0.000    0.000    0.000    0.000 {method 'insert' of 'list' objects}\n",
       "        1    0.000    0.000    0.000    0.000 {built-in method builtins.min}\n",
       "        2    0.000    0.000    0.000    0.000 proactor_events.py:883(_process_events)\n",
       "        1    0.000    0.000    0.000    0.000 common.py:529(is_string_or_object_np_dtype)\n",
       "        1    0.000    0.000    0.000    0.000 expressions.py:76(_can_use_numexpr)\n",
       "        1    0.000    0.000    0.000    0.000 range.py:315(step)\n",
       "        2    0.000    0.000    0.000    0.000 base.py:6672(_maybe_cast_indexer)\n",
       "        3    0.000    0.000    0.000    0.000 series.py:660(_constructor)\n",
       "        1    0.000    0.000    0.000    0.000 {built-in method _asyncio.get_running_loop}\n",
       "        4    0.000    0.000    0.000    0.000 inspect.py:2786(name)\n",
       "        1    0.000    0.000    0.000    0.000 inspect.py:2878(__init__)\n",
       "        1    0.000    0.000    0.000    0.000 multiarray.py:85(empty_like)\n",
       "        1    0.000    0.000    0.000    0.000 base.py:1176(_maybe_disallow_fill)\n",
       "        1    0.000    0.000    0.000    0.000 range.py:376(dtype)\n",
       "        1    0.000    0.000    0.000    0.000 managers.py:913(__init__)\n",
       "        1    0.000    0.000    0.000    0.000 array_ops.py:594(_bool_arith_check)\n",
       "        1    0.000    0.000    0.000    0.000 range.py:231(_constructor)\n",
       "        1    0.000    0.000    0.000    0.000 {method 'release' of '_thread.lock' objects}\n",
       "        1    0.000    0.000    0.000    0.000 {method 'reverse' of 'list' objects}\n",
       "        1    0.000    0.000    0.000    0.000 {built-in method builtins.ord}\n",
       "        1    0.000    0.000    0.000    0.000 base_events.py:1905(_timer_handle_cancelled)\n",
       "        1    0.000    0.000    0.000    0.000 common.py:192(<genexpr>)\n",
       "        1    0.000    0.000    0.000    0.000 missing.py:1073(clean_reindex_fill_method)\n",
       "        1    0.000    0.000    0.000    0.000 range.py:216(_validate_dtype)\n",
       "        2    0.000    0.000    0.000    0.000 readers.py:527(validate_integer)\n",
       "        1    0.000    0.000    0.000    0.000 _ufunc_config.py:426(__init__)\n",
       "        1    0.000    0.000    0.000    0.000 range.py:280(start)\n",
       "        1    0.000    0.000    0.000    0.000 copy.py:102(_copy_immutable)\n",
       "        1    0.000    0.000    0.000    0.000 parse.py:108(_noop)\n",
       "        1    0.000    0.000    0.000    0.000 numeric.py:2374(_array_equal_dispatcher)\n",
       "        1    0.000    0.000    0.000    0.000 managers.py:246(items)\n",
       "        1    0.000    0.000    0.000    0.000 readers.py:559(_validate_names)\n",
       "        1    0.000    0.000    0.000    0.000 readers.py:1987(__enter__)"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%prun -s cumulative median_diff('bids.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d62ac35b-27e6-4b82-85bb-69cdd9f37ca1",
   "metadata": {},
   "source": [
    "## 2. Vectorization"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3829ae9f-0774-4a28-b012-7afe88b564cd",
   "metadata": {},
   "source": [
    "### A. What is Vectorization?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "942fffd7-41e1-408e-931e-07e3667efdce",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "s = pd.Series(range(10_000))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "06c0ee3c-28a2-4986-9ebe-b5e4256bbd51",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1.52 ms ± 119 μs per loop (mean ± std. dev. of 7 runs, 1,000 loops each)\n"
     ]
    }
   ],
   "source": [
    "%%timeit\n",
    "total = 0\n",
    "for val in s:\n",
    "    total += val"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "bc439f4c-00d9-4a0e-8443-d0b1f92ddb02",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "16 μs ± 1.98 μs per loop (mean ± std. dev. of 7 runs, 100,000 loops each)\n"
     ]
    }
   ],
   "source": [
    "%timeit s.sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "id": "28c5b97e-91c3-487b-9452-c3c989945e21",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "95.0"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "1520 / 16"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5919f189-b168-4ccb-927f-79b95721084a",
   "metadata": {},
   "source": [
    "### B. Boolean Indexing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "id": "3d8fa781-ec3c-46f7-964c-b59d08d7bfe8",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('cart.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "id": "c1acf1e1-5607-4810-a848-4e725e88dc1e",
   "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>Customer</th>\n",
       "      <th>Item</th>\n",
       "      <th>Amount</th>\n",
       "      <th>Item Price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Rick</td>\n",
       "      <td>Wine</td>\n",
       "      <td>20</td>\n",
       "      <td>103.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Morty</td>\n",
       "      <td>Almond Milk</td>\n",
       "      <td>1</td>\n",
       "      <td>10.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Summer</td>\n",
       "      <td>Ice Cream</td>\n",
       "      <td>1</td>\n",
       "      <td>8.32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Beth</td>\n",
       "      <td>Comb</td>\n",
       "      <td>1</td>\n",
       "      <td>7.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Jerry</td>\n",
       "      <td>Tequila</td>\n",
       "      <td>2</td>\n",
       "      <td>20.34</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Customer         Item  Amount  Item Price\n",
       "0     Rick         Wine      20      103.20\n",
       "1    Morty  Almond Milk       1       10.04\n",
       "2   Summer    Ice Cream       1        8.32\n",
       "3     Beth         Comb       1        7.30\n",
       "4    Jerry      Tequila       2       20.34"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "id": "800fbddd-fc7c-414f-9be4-6c21ce92c19c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0     True\n",
       "1     True\n",
       "2    False\n",
       "3    False\n",
       "4     True\n",
       "Name: Item Price, dtype: bool"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Item Price'] > 10"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "id": "263344b0-7697-4b46-9f43-f6589585294d",
   "metadata": {},
   "outputs": [],
   "source": [
    "mask = df['Item Price'] > 10"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "id": "35571c45-8e02-4f43-b67c-e4f69ce43dc8",
   "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>Customer</th>\n",
       "      <th>Item</th>\n",
       "      <th>Amount</th>\n",
       "      <th>Item Price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Rick</td>\n",
       "      <td>Wine</td>\n",
       "      <td>20</td>\n",
       "      <td>103.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Morty</td>\n",
       "      <td>Almond Milk</td>\n",
       "      <td>1</td>\n",
       "      <td>10.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Jerry</td>\n",
       "      <td>Tequila</td>\n",
       "      <td>2</td>\n",
       "      <td>20.34</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Customer         Item  Amount  Item Price\n",
       "0     Rick         Wine      20      103.20\n",
       "1    Morty  Almond Milk       1       10.04\n",
       "4    Jerry      Tequila       2       20.34"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[mask]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "id": "a46d0ca8-416b-490c-828c-f552942f6c70",
   "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>Customer</th>\n",
       "      <th>Item</th>\n",
       "      <th>Amount</th>\n",
       "      <th>Item Price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Rick</td>\n",
       "      <td>Wine</td>\n",
       "      <td>20</td>\n",
       "      <td>103.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Morty</td>\n",
       "      <td>Almond Milk</td>\n",
       "      <td>1</td>\n",
       "      <td>10.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Jerry</td>\n",
       "      <td>Tequila</td>\n",
       "      <td>2</td>\n",
       "      <td>20.34</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Customer         Item  Amount  Item Price\n",
       "0     Rick         Wine      20      103.20\n",
       "1    Morty  Almond Milk       1       10.04\n",
       "4    Jerry      Tequila       2       20.34"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df['Item Price'] > 10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "id": "c7840f6d-0868-4b7a-8ac7-e79d77c59459",
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "id": "f35059f2-4dbf-4343-b053-285a8a5428ee",
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = sqlite3.connect('logs_1.db', detect_types = sqlite3.PARSE_DECLTYPES)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "id": "c76cf02c-2d46-4876-8915-82ca2c420225",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_sql('SELECT * FROM logs', conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "id": "72243224-6d8e-4787-9478-5031f7da89ba",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "10000"
      ]
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "id": "68c76db6-4793-4b10-975a-b28b93bc40d3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "595 ms ± 60.9 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)\n"
     ]
    }
   ],
   "source": [
    "%%timeit\n",
    "total = 0\n",
    "for _,row in df.iterrows():\n",
    "    if row['status_code'] >= 400:\n",
    "        total += 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "id": "c55fd3ac-66fa-411e-a583-0684fe80d148",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "386 μs ± 63 μs per loop (mean ± std. dev. of 7 runs, 1,000 loops each)\n"
     ]
    }
   ],
   "source": [
    "%timeit len(df[df['status_code'] >= 400])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "id": "a49a7b9d-8231-422d-989d-1a897ce0e7fc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "15414.507772020725"
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "5_950_000/386"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "45397bb8-aa77-4589-9902-f80fbdaccde3",
   "metadata": {},
   "source": [
    "### B. Understanding ufuncs (Universal Functions)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "id": "fc701cf7-d6c7-4f07-9378-d03dfb49633c",
   "metadata": {},
   "outputs": [],
   "source": [
    "s = pd.Series(range(10_000))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "id": "0e45f84e-967c-498a-9bce-d47b5c6f94d9",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1.39 ms ± 117 μs per loop (mean ± std. dev. of 7 runs, 1,000 loops each)\n"
     ]
    }
   ],
   "source": [
    "%timeit max(s)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "id": "d56ea6bf-8479-4738-801c-c821377afd0f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "24.1 μs ± 4.13 μs per loop (mean ± std. dev. of 7 runs, 10,000 loops each)\n"
     ]
    }
   ],
   "source": [
    "%timeit s.max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "id": "a4b327a7-6423-4c65-bb29-012bcb2bc951",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "57.676348547717836"
      ]
     },
     "execution_count": 89,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "1390 / 24.1"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b69e2578-3f89-44d9-acc9-a77c0572c3e3",
   "metadata": {},
   "source": [
    "### C. Challenge 2 --> Selecting and Manipulating Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "id": "2f243ca2-53a0-46a4-8ade-f9786f060451",
   "metadata": {},
   "outputs": [],
   "source": [
    "\"\"\"Find last time we had error in logs\"\"\"\n",
    "\n",
    "import sqlite3\n",
    "from contextlib import closing\n",
    "\n",
    "import pandas as pd\n",
    "\n",
    "\n",
    "def last_error_time(df):\n",
    "    \"\"\"Find last time there's an error in df\"\"\"\n",
    "    last_time = None\n",
    "    for _, row in df.iterrows():\n",
    "        if row['status_code'] < 400:\n",
    "            continue\n",
    "        if not last_time or row['time'] > last_time:\n",
    "            last_time = row['time']\n",
    "    return last_time\n",
    "\n",
    "\n",
    "def load_df(db_file):\n",
    "    \"\"\"Load DataFrame from database\"\"\"\n",
    "    conn = sqlite3.connect(db_file, detect_types=sqlite3.PARSE_DECLTYPES)\n",
    "    with closing(conn):\n",
    "        return pd.read_sql('SELECT * FROM logs', conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "id": "b5ee54e8-cc87-4a5f-870a-2c09785ec998",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = load_df('logs_2.db')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "id": "60742b65-c354-4116-9f29-87b6e90a75c9",
   "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>time</th>\n",
       "      <th>origin</th>\n",
       "      <th>method</th>\n",
       "      <th>path</th>\n",
       "      <th>status_code</th>\n",
       "      <th>size</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1995-08-01 00:00:01</td>\n",
       "      <td>in24.inetnebr.com</td>\n",
       "      <td>GET</td>\n",
       "      <td>/shuttle/missions/sts-68/news/sts-68-mcc-05.txt</td>\n",
       "      <td>200</td>\n",
       "      <td>1839</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1995-08-01 00:00:07</td>\n",
       "      <td>uplherc.upl.com</td>\n",
       "      <td>GET</td>\n",
       "      <td>/</td>\n",
       "      <td>304</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1995-08-01 00:00:08</td>\n",
       "      <td>uplherc.upl.com</td>\n",
       "      <td>GET</td>\n",
       "      <td>/images/ksclogo-medium.gif</td>\n",
       "      <td>304</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1995-08-01 00:00:08</td>\n",
       "      <td>uplherc.upl.com</td>\n",
       "      <td>GET</td>\n",
       "      <td>/images/MOSAIC-logosmall.gif</td>\n",
       "      <td>304</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1995-08-01 00:00:08</td>\n",
       "      <td>uplherc.upl.com</td>\n",
       "      <td>GET</td>\n",
       "      <td>/images/USA-logosmall.gif</td>\n",
       "      <td>304</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9995</th>\n",
       "      <td>1995-08-01 07:54:40</td>\n",
       "      <td>ppp-14.flashnet.it</td>\n",
       "      <td>GET</td>\n",
       "      <td>/images/USA-logosmall.gif</td>\n",
       "      <td>200</td>\n",
       "      <td>234</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9996</th>\n",
       "      <td>1995-08-01 07:54:41</td>\n",
       "      <td>204.238.216.51</td>\n",
       "      <td>GET</td>\n",
       "      <td>/images/ksclogo-medium.gif</td>\n",
       "      <td>304</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9997</th>\n",
       "      <td>1995-08-01 07:54:42</td>\n",
       "      <td>204.238.216.51</td>\n",
       "      <td>GET</td>\n",
       "      <td>/images/USA-logosmall.gif</td>\n",
       "      <td>304</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9998</th>\n",
       "      <td>1995-08-01 07:54:42</td>\n",
       "      <td>204.238.216.51</td>\n",
       "      <td>GET</td>\n",
       "      <td>/images/MOSAIC-logosmall.gif</td>\n",
       "      <td>304</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9999</th>\n",
       "      <td>1995-08-01 07:54:42</td>\n",
       "      <td>204.238.216.51</td>\n",
       "      <td>GET</td>\n",
       "      <td>/images/NASA-logosmall.gif</td>\n",
       "      <td>304</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10000 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                    time              origin method  \\\n",
       "0    1995-08-01 00:00:01   in24.inetnebr.com    GET   \n",
       "1    1995-08-01 00:00:07     uplherc.upl.com    GET   \n",
       "2    1995-08-01 00:00:08     uplherc.upl.com    GET   \n",
       "3    1995-08-01 00:00:08     uplherc.upl.com    GET   \n",
       "4    1995-08-01 00:00:08     uplherc.upl.com    GET   \n",
       "...                  ...                 ...    ...   \n",
       "9995 1995-08-01 07:54:40  ppp-14.flashnet.it    GET   \n",
       "9996 1995-08-01 07:54:41      204.238.216.51    GET   \n",
       "9997 1995-08-01 07:54:42      204.238.216.51    GET   \n",
       "9998 1995-08-01 07:54:42      204.238.216.51    GET   \n",
       "9999 1995-08-01 07:54:42      204.238.216.51    GET   \n",
       "\n",
       "                                                 path  status_code  size  \n",
       "0     /shuttle/missions/sts-68/news/sts-68-mcc-05.txt          200  1839  \n",
       "1                                                   /          304     0  \n",
       "2                          /images/ksclogo-medium.gif          304     0  \n",
       "3                        /images/MOSAIC-logosmall.gif          304     0  \n",
       "4                           /images/USA-logosmall.gif          304     0  \n",
       "...                                               ...          ...   ...  \n",
       "9995                        /images/USA-logosmall.gif          200   234  \n",
       "9996                       /images/ksclogo-medium.gif          304     0  \n",
       "9997                        /images/USA-logosmall.gif          304     0  \n",
       "9998                     /images/MOSAIC-logosmall.gif          304     0  \n",
       "9999                       /images/NASA-logosmall.gif          304     0  \n",
       "\n",
       "[10000 rows x 6 columns]"
      ]
     },
     "execution_count": 93,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "id": "655160a2-9933-4011-9a61-eae5d91ae5d8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "577 ms ± 28.6 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)\n"
     ]
    }
   ],
   "source": [
    "%timeit last_error_time(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "id": "ed5ab88d-93b1-448f-b824-276ef60a441d",
   "metadata": {},
   "outputs": [],
   "source": [
    "\"\"\"Find last time we had error in logs\"\"\"\n",
    "\n",
    "import sqlite3\n",
    "from contextlib import closing\n",
    "\n",
    "import pandas as pd\n",
    "\n",
    "\n",
    "def last_error_time(df):\n",
    "    \"\"\"Find last time there's an error in df\"\"\"\n",
    "    return df[df['status_code'] >= 400]['time'].max()\n",
    "\n",
    "\n",
    "def load_df(db_file):\n",
    "    \"\"\"Load DataFrame from database\"\"\"\n",
    "    conn = sqlite3.connect(db_file, detect_types=sqlite3.PARSE_DECLTYPES)\n",
    "    with closing(conn):\n",
    "        return pd.read_sql('SELECT * FROM logs', conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "id": "0f54e224-dff2-4b13-b293-e82a922fdf5e",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_2 = load_df('logs_2.db')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "id": "5e311845-fe9a-4bab-807e-33978d2c38cf",
   "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>time</th>\n",
       "      <th>origin</th>\n",
       "      <th>method</th>\n",
       "      <th>path</th>\n",
       "      <th>status_code</th>\n",
       "      <th>size</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1995-08-01 00:00:01</td>\n",
       "      <td>in24.inetnebr.com</td>\n",
       "      <td>GET</td>\n",
       "      <td>/shuttle/missions/sts-68/news/sts-68-mcc-05.txt</td>\n",
       "      <td>200</td>\n",
       "      <td>1839</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1995-08-01 00:00:07</td>\n",
       "      <td>uplherc.upl.com</td>\n",
       "      <td>GET</td>\n",
       "      <td>/</td>\n",
       "      <td>304</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1995-08-01 00:00:08</td>\n",
       "      <td>uplherc.upl.com</td>\n",
       "      <td>GET</td>\n",
       "      <td>/images/ksclogo-medium.gif</td>\n",
       "      <td>304</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1995-08-01 00:00:08</td>\n",
       "      <td>uplherc.upl.com</td>\n",
       "      <td>GET</td>\n",
       "      <td>/images/MOSAIC-logosmall.gif</td>\n",
       "      <td>304</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1995-08-01 00:00:08</td>\n",
       "      <td>uplherc.upl.com</td>\n",
       "      <td>GET</td>\n",
       "      <td>/images/USA-logosmall.gif</td>\n",
       "      <td>304</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9995</th>\n",
       "      <td>1995-08-01 07:54:40</td>\n",
       "      <td>ppp-14.flashnet.it</td>\n",
       "      <td>GET</td>\n",
       "      <td>/images/USA-logosmall.gif</td>\n",
       "      <td>200</td>\n",
       "      <td>234</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9996</th>\n",
       "      <td>1995-08-01 07:54:41</td>\n",
       "      <td>204.238.216.51</td>\n",
       "      <td>GET</td>\n",
       "      <td>/images/ksclogo-medium.gif</td>\n",
       "      <td>304</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9997</th>\n",
       "      <td>1995-08-01 07:54:42</td>\n",
       "      <td>204.238.216.51</td>\n",
       "      <td>GET</td>\n",
       "      <td>/images/USA-logosmall.gif</td>\n",
       "      <td>304</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9998</th>\n",
       "      <td>1995-08-01 07:54:42</td>\n",
       "      <td>204.238.216.51</td>\n",
       "      <td>GET</td>\n",
       "      <td>/images/MOSAIC-logosmall.gif</td>\n",
       "      <td>304</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9999</th>\n",
       "      <td>1995-08-01 07:54:42</td>\n",
       "      <td>204.238.216.51</td>\n",
       "      <td>GET</td>\n",
       "      <td>/images/NASA-logosmall.gif</td>\n",
       "      <td>304</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10000 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                    time              origin method  \\\n",
       "0    1995-08-01 00:00:01   in24.inetnebr.com    GET   \n",
       "1    1995-08-01 00:00:07     uplherc.upl.com    GET   \n",
       "2    1995-08-01 00:00:08     uplherc.upl.com    GET   \n",
       "3    1995-08-01 00:00:08     uplherc.upl.com    GET   \n",
       "4    1995-08-01 00:00:08     uplherc.upl.com    GET   \n",
       "...                  ...                 ...    ...   \n",
       "9995 1995-08-01 07:54:40  ppp-14.flashnet.it    GET   \n",
       "9996 1995-08-01 07:54:41      204.238.216.51    GET   \n",
       "9997 1995-08-01 07:54:42      204.238.216.51    GET   \n",
       "9998 1995-08-01 07:54:42      204.238.216.51    GET   \n",
       "9999 1995-08-01 07:54:42      204.238.216.51    GET   \n",
       "\n",
       "                                                 path  status_code  size  \n",
       "0     /shuttle/missions/sts-68/news/sts-68-mcc-05.txt          200  1839  \n",
       "1                                                   /          304     0  \n",
       "2                          /images/ksclogo-medium.gif          304     0  \n",
       "3                        /images/MOSAIC-logosmall.gif          304     0  \n",
       "4                           /images/USA-logosmall.gif          304     0  \n",
       "...                                               ...          ...   ...  \n",
       "9995                        /images/USA-logosmall.gif          200   234  \n",
       "9996                       /images/ksclogo-medium.gif          304     0  \n",
       "9997                        /images/USA-logosmall.gif          304     0  \n",
       "9998                     /images/MOSAIC-logosmall.gif          304     0  \n",
       "9999                       /images/NASA-logosmall.gif          304     0  \n",
       "\n",
       "[10000 rows x 6 columns]"
      ]
     },
     "execution_count": 97,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "id": "196e8d16-085a-4937-a24b-0276d8e0d871",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "537 μs ± 32.6 μs per loop (mean ± std. dev. of 7 runs, 1,000 loops each)\n"
     ]
    }
   ],
   "source": [
    "%timeit last_error_time(df_2)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dcd83ef1-d3a7-4c7e-8e77-4560ba04bbde",
   "metadata": {},
   "source": [
    "### 3. Common Mistakes"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e17f746b-9634-4963-bbdd-b44cefe6bba8",
   "metadata": {},
   "source": [
    "### A. The Limitations of Appending"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "id": "eae0f7b7-87d2-43ef-bcf6-d4b313668253",
   "metadata": {},
   "outputs": [],
   "source": [
    "\"\"\"Parse log lines\"\"\"\n",
    "\n",
    "from datetime import datetime\n",
    "\n",
    "\n",
    "def parse_time(ts):\n",
    "    # [02/Jul/1995:16:30:08 -0400]\n",
    "    time = datetime.strptime(ts, '[%d/%b/%Y:%H:%M:%S %z]')\n",
    "    return time.replace(tzinfo=None)  # Remove time zone\n",
    "\n",
    "\n",
    "def parse_line(line):\n",
    "    fields = line.split()\n",
    "    size = 0 if fields[-1] == '-' else int(fields[-1])\n",
    "    return {\n",
    "        'origin': fields[0],\n",
    "        'time': parse_time(fields[3] + ' ' + fields[4]),\n",
    "        'method': fields[5][1:],  # Remove leading \"\n",
    "        'path': fields[6],\n",
    "        'status_code': int(fields[-2]),\n",
    "        'size': size,\n",
    "    }"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "id": "6c8699d1-a00e-4dd6-9d0c-996c2391c538",
   "metadata": {},
   "outputs": [],
   "source": [
    "import lzma"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "id": "90ff999a-5726-4969-b829-5ef23fec1aa2",
   "metadata": {},
   "outputs": [],
   "source": [
    "with lzma.open('log.txt.xz', 'rt') as fp:\n",
    "    lines = fp.readlines()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "id": "fe0f159d-2ccb-49ac-a84c-9580394cf91e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1000"
      ]
     },
     "execution_count": 104,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(lines)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "id": "d4662484-b201-4f24-b416-330b2aa85a4b",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "id": "d2f5f75e-e704-4430-a274-321b4fcfdfc9",
   "metadata": {},
   "outputs": [],
   "source": [
    "for line in lines:\n",
    "    df = pd.concat([df, pd.DataFrame([parse_line(line)])], ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "id": "ca7f7296-39ec-456d-aaf0-9dd8f637af93",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "963 ms ± 89.9 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)\n"
     ]
    }
   ],
   "source": [
    "%%timeit\n",
    "df = pd.DataFrame()\n",
    "for line in lines:\n",
    "    df = pd.concat([df, pd.DataFrame([parse_line(line)])], ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "id": "281ca9c7-2cb5-439a-9a93-132171729d98",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame.from_records(parse_line(line) for line in lines)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "id": "acc33dd8-bce9-4d1c-9507-978c03b9289e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "26.9 ms ± 2.63 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)\n"
     ]
    }
   ],
   "source": [
    "%%timeit\n",
    "df = pd.DataFrame.from_records(parse_line(line) for line in lines)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "id": "9839e959-e7c6-4dbd-a8e3-5bbcad428571",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "35.79925650557621"
      ]
     },
     "execution_count": 112,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "963 / 26.9"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "48861e69-c8bc-4048-8229-f7b00b9dea22",
   "metadata": {},
   "source": [
    "### B. The limitations of object type"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "id": "cfb3b60f-07e0-4055-b166-b6739949180b",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('logs_1.csv.xz')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "id": "da59bfd1-d362-468a-9a75-6ed80e83fd4e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "origin         object\n",
       "date           object\n",
       "time           object\n",
       "method         object\n",
       "path           object\n",
       "status_code     int64\n",
       "size            int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 114,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "id": "e91675fe-7357-4757-bf11-5bf8cb5d03b9",
   "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>date</th>\n",
       "      <th>time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1995-07-01</td>\n",
       "      <td>00:00:01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1995-07-01</td>\n",
       "      <td>00:00:06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1995-07-01</td>\n",
       "      <td>00:00:09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1995-07-01</td>\n",
       "      <td>00:00:11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1995-07-01</td>\n",
       "      <td>00:00:11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         date      time\n",
       "0  1995-07-01  00:00:01\n",
       "1  1995-07-01  00:00:06\n",
       "2  1995-07-01  00:00:09\n",
       "3  1995-07-01  00:00:11\n",
       "4  1995-07-01  00:00:11"
      ]
     },
     "execution_count": 115,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[['date', 'time']].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "id": "a4c80293-bf3a-488b-a7a5-940a3a36736d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "6533"
      ]
     },
     "execution_count": 116,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['time'].nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "id": "169a0eed-9574-4bd2-8db5-2c34b8f7e61c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1.44 ms ± 99.3 μs per loop (mean ± std. dev. of 7 runs, 1,000 loops each)\n"
     ]
    }
   ],
   "source": [
    "%timeit df['time'].nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "id": "1db01e4d-445f-42da-96f9-f904bf2f58d9",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Advait\\AppData\\Local\\Temp\\ipykernel_10552\\2818078471.py:1: UserWarning: Could not infer format, so each element will be parsed individually, falling back to `dateutil`. To ensure parsing is consistent and as-expected, please specify a format.\n",
      "  df['time_ts'] = pd.to_datetime(df['time'])\n"
     ]
    }
   ],
   "source": [
    "df['time_ts'] = pd.to_datetime(df['time'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "id": "71e65804-dc82-40f5-914c-bf4c4987667a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "343 μs ± 42.7 μs per loop (mean ± std. dev. of 7 runs, 1,000 loops each)\n"
     ]
    }
   ],
   "source": [
    "%timeit df['time_ts'].nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "id": "5495200e-ac5e-4100-bd36-73459f59de46",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4.198250728862973"
      ]
     },
     "execution_count": 121,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "1440 / 343"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "66ca4903-6888-4c67-addc-40b60de72aa5",
   "metadata": {},
   "source": [
    "### C. The limitations of Row Iterations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "id": "3099d749-abe5-4375-b4a7-fecccde77446",
   "metadata": {},
   "outputs": [],
   "source": [
    "size = 50_000\n",
    "\n",
    "df = pd.DataFrame({\n",
    "    'a' : np.random.randint(1, 1000, size),\n",
    "    'b' : np.random.randint(1, 1000, size),\n",
    "    'c' : np.random.randint(1, 1000, size)\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "id": "9a3477d7-2b5d-4eaf-aacb-7370f76b2a02",
   "metadata": {},
   "outputs": [],
   "source": [
    "total = 0\n",
    "\n",
    "for _, row in df.iterrows():\n",
    "    total += row.max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "id": "76f5e720-8356-41b8-bcb7-f22bdebd0751",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "37475182"
      ]
     },
     "execution_count": 124,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "id": "e4dfb309-943d-4c9b-aee4-eded42bc47a9",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: total: 2.64 s\n",
      "Wall time: 2.77 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "total = 0\n",
    "\n",
    "for _, row in df.iterrows():\n",
    "    total += row.max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "id": "3804df0a-0d2f-4d52-b435-222494c2cf95",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "37475182"
      ]
     },
     "execution_count": 127,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.apply(np.max, axis=1).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "id": "f8be310c-f072-4155-bdd8-fa5aa7bf3a02",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: total: 1.2 s\n",
      "Wall time: 1.25 s\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "37475182"
      ]
     },
     "execution_count": 131,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%time \n",
    "df.apply(np.max, axis=1).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "id": "8e992e54-ed77-455b-b82b-10a158f6d514",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "37475182"
      ]
     },
     "execution_count": 129,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.apply(np.max, axis=1, raw = True).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "id": "5d628cc9-5cf2-4da1-b0ca-af46cacec6f0",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: total: 375 ms\n",
      "Wall time: 398 ms\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "37475182"
      ]
     },
     "execution_count": 130,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%time df.apply(np.max, axis=1, raw = True).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "id": "b05da47d-0ed3-4769-a740-ebcd3d27c4c3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "6.633165829145729"
      ]
     },
     "execution_count": 132,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "2640 / 398"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "951e6c95-8bbe-4c03-9bcf-cc5c4115772e",
   "metadata": {},
   "source": [
    "### D. Understanding the isin function"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "id": "eda9f3ac-d3b0-40a2-a032-6cdeea46edce",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import sqlite3\n",
    "\n",
    "conn = sqlite3.connect('logs_3.db', detect_types = sqlite3.PARSE_DECLTYPES)\n",
    "\n",
    "df = pd.read_sql('SELECT * FROM logs', conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 134,
   "id": "4bf6dd71-e796-4616-9034-fedf845e3168",
   "metadata": {},
   "outputs": [],
   "source": [
    "import lzma"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 135,
   "id": "3382a632-7e7f-4ac8-93e4-37369d6435f7",
   "metadata": {},
   "outputs": [],
   "source": [
    "with lzma.open('ips.txt.xz', 'rt') as fp:\n",
    "    ips = [line.strip() for line in fp]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 136,
   "id": "16cb3242-7fd5-4184-944f-42040e400d15",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "100000"
      ]
     },
     "execution_count": 136,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(ips)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 137,
   "id": "4c59f887-99f9-497a-96dc-f03a12e900cd",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "27.1 ms ± 1.81 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)\n"
     ]
    }
   ],
   "source": [
    "%timeit df['origin'].isin(ips)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 138,
   "id": "2057d83c-1e14-40a2-bc93-2153b842228f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "list"
      ]
     },
     "execution_count": 138,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(ips)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 139,
   "id": "4ae1bb08-cd7b-45c9-a72f-9d1a6d1bff3a",
   "metadata": {},
   "outputs": [],
   "source": [
    "ip = df['origin'][3923]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "id": "99f23a90-ccba-41ac-bedd-5e8b4f87f27f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1.86 ms ± 24.8 μs per loop (mean ± std. dev. of 7 runs, 1,000 loops each)\n"
     ]
    }
   ],
   "source": [
    "%timeit ip in ips"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 141,
   "id": "771eb49c-06b9-44f9-8728-ebf8d2a600cc",
   "metadata": {},
   "outputs": [],
   "source": [
    "ips_set = set(ips)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 142,
   "id": "ea3212c2-6f20-44e8-b5c3-dffecffe15a1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "43.1 ns ± 2.76 ns per loop (mean ± std. dev. of 7 runs, 10,000,000 loops each)\n"
     ]
    }
   ],
   "source": [
    "%timeit ip in ips_set"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 143,
   "id": "f9810b0b-c3b0-4e08-927f-6f843fd87129",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "43155.45243619489"
      ]
     },
     "execution_count": 143,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "1860000 / 43.1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 144,
   "id": "7ee6e0c9-a808-47dc-b977-1821b6e4f18c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "51.2 ms ± 1.78 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)\n"
     ]
    }
   ],
   "source": [
    "%timeit df['origin'].isin(ips_set)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 146,
   "id": "0f8ef8c9-91c8-4b27-92be-464300c1ef6e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2.52 ms ± 356 μs per loop (mean ± std. dev. of 7 runs, 100 loops each)\n"
     ]
    }
   ],
   "source": [
    "%timeit df['origin'].apply(lambda v : v in ips_set)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 147,
   "id": "b690ea6a-159b-41bf-9d28-07e35d07f997",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False"
      ]
     },
     "execution_count": 147,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ip in ips_set"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 148,
   "id": "35532020-4594-4c49-b260-a9bfc5e2d49f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False"
      ]
     },
     "execution_count": 148,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ips_set.__contains__(ip)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 150,
   "id": "dd2425ae-832f-4847-a57c-b95842fa8269",
   "metadata": {},
   "outputs": [],
   "source": [
    "is_bad = ips_set.__contains__"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 151,
   "id": "06d00169-40ce-4ca5-a598-bde9961f173c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1.93 ms ± 240 μs per loop (mean ± std. dev. of 7 runs, 100 loops each)\n"
     ]
    }
   ],
   "source": [
    "%timeit df['origin'].apply(is_bad)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 152,
   "id": "2d1b4aa2-1dde-40aa-bf24-58c9f46bcfd2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "14.041450777202074"
      ]
     },
     "execution_count": 152,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "27.1 / 1.93"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "00b0ca68-9d18-49e8-aa7e-711ad82da049",
   "metadata": {},
   "source": [
    "### E. Parsing time once"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "e069cc50-2d54-40ef-afa2-b1a2aa1b32e5",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "02ec4efc-e679-4aef-ad89-0da9445deaf2",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('logs_2.csv.xz')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "f7279ba0-fd8d-4d43-9a68-edcf20166df0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "50000"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "2e690c88-91ca-4f8e-a978-8a6144f3c12d",
   "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>origin</th>\n",
       "      <th>time</th>\n",
       "      <th>method</th>\n",
       "      <th>path</th>\n",
       "      <th>status_code</th>\n",
       "      <th>size</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>26626</th>\n",
       "      <td>kip-1-sn-364.dartmouth.edu</td>\n",
       "      <td>1995-08-18T12:57:22</td>\n",
       "      <td>GET</td>\n",
       "      <td>/icons/image.xbm</td>\n",
       "      <td>200</td>\n",
       "      <td>509</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23425</th>\n",
       "      <td>ix-tam1-09.ix.netcom.com</td>\n",
       "      <td>1995-08-16T18:23:33</td>\n",
       "      <td>GET</td>\n",
       "      <td>/shuttle/technology/images/srb_mod_compare_6-s...</td>\n",
       "      <td>200</td>\n",
       "      <td>28219</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27094</th>\n",
       "      <td>akiko.bms.com</td>\n",
       "      <td>1995-08-18T16:21:23</td>\n",
       "      <td>GET</td>\n",
       "      <td>/images/ksclogosmall.gif</td>\n",
       "      <td>200</td>\n",
       "      <td>3635</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2830</th>\n",
       "      <td>129.126.75.123</td>\n",
       "      <td>1995-08-04T09:08:27</td>\n",
       "      <td>GET</td>\n",
       "      <td>/icons/menu.xbm</td>\n",
       "      <td>200</td>\n",
       "      <td>527</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10571</th>\n",
       "      <td>n1028246.ksc.nasa.gov</td>\n",
       "      <td>1995-08-09T10:37:01</td>\n",
       "      <td>GET</td>\n",
       "      <td>/images/ksclogo-medium.gif</td>\n",
       "      <td>200</td>\n",
       "      <td>5866</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           origin                 time method  \\\n",
       "26626  kip-1-sn-364.dartmouth.edu  1995-08-18T12:57:22    GET   \n",
       "23425    ix-tam1-09.ix.netcom.com  1995-08-16T18:23:33    GET   \n",
       "27094               akiko.bms.com  1995-08-18T16:21:23    GET   \n",
       "2830               129.126.75.123  1995-08-04T09:08:27    GET   \n",
       "10571       n1028246.ksc.nasa.gov  1995-08-09T10:37:01    GET   \n",
       "\n",
       "                                                    path  status_code   size  \n",
       "26626                                   /icons/image.xbm          200    509  \n",
       "23425  /shuttle/technology/images/srb_mod_compare_6-s...          200  28219  \n",
       "27094                           /images/ksclogosmall.gif          200   3635  \n",
       "2830                                     /icons/menu.xbm          200    527  \n",
       "10571                         /images/ksclogo-medium.gif          200   5866  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "8f6f2132-1177-4dfa-94a8-ca7f95a5623d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'1995-08-01T00:49:20'"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['time'][48]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "6ac7bb6f-d5c4-4ec9-b68c-5dd2d74c81f7",
   "metadata": {},
   "outputs": [],
   "source": [
    "def is_morning(ts):\n",
    "    t = pd.to_datetime(ts)\n",
    "    return t.hour >= 6 and t.hour < 12"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "7a5aa04b-f48a-428c-b3a6-c65be9d4e012",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "13004"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df[df['time'].apply(is_morning)])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "341572b9-e60a-4d0f-905d-6bb90b41ed63",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "32 s ± 2.6 s per loop (mean ± std. dev. of 7 runs, 1 loop each)\n"
     ]
    }
   ],
   "source": [
    "%timeit len(df[df['time'].apply(is_morning)])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "0d28d715-820e-4578-bd22-45321bce63c7",
   "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>origin</th>\n",
       "      <th>time</th>\n",
       "      <th>method</th>\n",
       "      <th>path</th>\n",
       "      <th>status_code</th>\n",
       "      <th>size</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>48681</th>\n",
       "      <td>silver.sms.fi</td>\n",
       "      <td>1995-08-31 13:02:14</td>\n",
       "      <td>GET</td>\n",
       "      <td>/shuttle/countdown/images/countclock.gif</td>\n",
       "      <td>304</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26269</th>\n",
       "      <td>163.205.12.123</td>\n",
       "      <td>1995-08-18 10:11:00</td>\n",
       "      <td>GET</td>\n",
       "      <td>/images/USA-logosmall.gif</td>\n",
       "      <td>200</td>\n",
       "      <td>234</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6276</th>\n",
       "      <td>dal04-14.ppp.iadfw.net</td>\n",
       "      <td>1995-08-07 00:57:58</td>\n",
       "      <td>GET</td>\n",
       "      <td>/images/NASA-logosmall.gif</td>\n",
       "      <td>200</td>\n",
       "      <td>786</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33628</th>\n",
       "      <td>198.83.140.55</td>\n",
       "      <td>1995-08-23 07:37:27</td>\n",
       "      <td>GET</td>\n",
       "      <td>/software/winvn/bluemarb.gif</td>\n",
       "      <td>304</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46003</th>\n",
       "      <td>163.206.89.4</td>\n",
       "      <td>1995-08-30 14:40:28</td>\n",
       "      <td>GET</td>\n",
       "      <td>/images/MOSAIC-logosmall.gif</td>\n",
       "      <td>200</td>\n",
       "      <td>363</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       origin                time method  \\\n",
       "48681           silver.sms.fi 1995-08-31 13:02:14    GET   \n",
       "26269          163.205.12.123 1995-08-18 10:11:00    GET   \n",
       "6276   dal04-14.ppp.iadfw.net 1995-08-07 00:57:58    GET   \n",
       "33628           198.83.140.55 1995-08-23 07:37:27    GET   \n",
       "46003            163.206.89.4 1995-08-30 14:40:28    GET   \n",
       "\n",
       "                                           path  status_code  size  \n",
       "48681  /shuttle/countdown/images/countclock.gif          304     0  \n",
       "26269                 /images/USA-logosmall.gif          200   234  \n",
       "6276                 /images/NASA-logosmall.gif          200   786  \n",
       "33628              /software/winvn/bluemarb.gif          304     0  \n",
       "46003              /images/MOSAIC-logosmall.gif          200   363  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_1 = pd.read_csv('logs_2.csv.xz', parse_dates = ['time'])\n",
    "len(df_1)\n",
    "df_1.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "550402ba-bef7-411a-b143-19330fe645e0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "13004"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df_1[(df_1['time'].dt.hour >= 6) & (df_1['time'].dt.hour < 12)])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "ba8da02f-9a54-47c8-b159-0b92fb68f385",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "7.4 ms ± 466 μs per loop (mean ± std. dev. of 7 runs, 100 loops each)\n"
     ]
    }
   ],
   "source": [
    "%timeit len(df_1[(df_1['time'].dt.hour >= 6) & (df_1['time'].dt.hour < 12)])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "ade47fa2-0393-465d-a12e-c11c0c72b4e1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4324.324324324324"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "32000 / 7.4"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1c1b243e-f143-478b-9116-6067330ac43b",
   "metadata": {},
   "source": [
    "### F. Challenge - 3 --> Querying a DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "e3d988cb-cc4e-4cfc-b77f-9f1f7db26d27",
   "metadata": {},
   "outputs": [],
   "source": [
    "\"\"\"Find how many rides in 2016 were in the afternoon of weekend or holiday.\n",
    "\n",
    "- Afternoon: Between noon to 6pm\n",
    "- Weekend: Saturday or Sunday\n",
    "- Holiday: See holidays_2016 below\n",
    "\"\"\"\n",
    "\n",
    "import pandas as pd\n",
    "\n",
    "# 2016 public holidays\n",
    "holidays_2016 = [\n",
    "    '2016-01-01',  # new year\n",
    "    '2016-01-18',  # MLK\n",
    "    '2016-05-30',  # memorial\n",
    "    '2016-07-04',  # independence\n",
    "    '2016-09-05',  # labor\n",
    "    '2016-11-11',  # veterans\n",
    "    '2016-11-24',  # thanksgiving\n",
    "    '2016-12-26',  # christmas\n",
    "]\n",
    "\n",
    "\n",
    "def load_df(file_name):\n",
    "    \"\"\"Load data from CSV to DataFrame\"\"\"\n",
    "    return pd.read_csv(file_name)\n",
    "\n",
    "\n",
    "def is_2016(s):\n",
    "    ts = pd.to_datetime(s)\n",
    "\n",
    "    return ts.year == 2016\n",
    "\n",
    "\n",
    "def is_weekend(s):\n",
    "    \"\"\"Check that s in a weekend day\"\"\"\n",
    "    ts = pd.to_datetime(s)\n",
    "\n",
    "    return ts.day_name() == 'Saturday' or ts.day_name() == 'Sunday'\n",
    "\n",
    "\n",
    "def is_holiday(s):\n",
    "    \"\"\"Check that s (e.g. '10/26/2014') is a holiday\"\"\"\n",
    "    ts = pd.to_datetime(s)\n",
    "\n",
    "    day = ts.strftime('%Y-%m-%d')  # holidays_2016 format\n",
    "    return day in holidays_2016\n",
    "\n",
    "\n",
    "def is_afternoon(s):\n",
    "    \"\"\"Check that s (e.g. '13:12:00' is in the afternoon\"\"\"\n",
    "    ts = pd.to_datetime(s)\n",
    "\n",
    "    return ts.hour >= 12 and ts.hour < 18\n",
    "\n",
    "\n",
    "def vacation_rides(df):\n",
    "    \"\"\"Return only rows that are in holiday afternoon\"\"\"\n",
    "    result = pd.DataFrame()  # Initialize an empty DataFrame\n",
    "    \n",
    "    for _, row in df.iterrows():\n",
    "        date, time = row['Checkout Date'], row['Checkout Time']\n",
    "        \n",
    "        if not is_2016(date):\n",
    "            continue\n",
    "\n",
    "        if (is_holiday(date) or is_weekend(date)) and is_afternoon(time):\n",
    "            row_df = row.to_frame().T  # Convert row (Series) to DataFrame\n",
    "            result = pd.concat([result, row_df], ignore_index=True)  # Append row\n",
    "    \n",
    "    return result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "8f5d7b0d-9965-4607-aa88-796200463f9e",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = load_df('austin-bikes.csv.xz')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "e7ad43f8-af85-4382-aa0e-3e7af3910479",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "421"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(vacation_rides(df))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "eeec25f8-3adf-46e3-926b-424aba1dd1e4",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: total: 8.97 s\n",
      "Wall time: 9.19 s\n"
     ]
    }
   ],
   "source": [
    "%time _ = vacation_rides(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "572b44c7-42ec-4d01-9970-7275a38031ca",
   "metadata": {},
   "outputs": [],
   "source": [
    "\"\"\"Find how many rides in 2016 were in the afternoon of weekend or holiday.\n",
    "\n",
    "- Afternoon: Between noon to 6pm\n",
    "- Weekend: Friday or Saturday\n",
    "- Holiday: See holidays_2016 below\n",
    "\"\"\"\n",
    "\n",
    "from calendar import SATURDAY, SUNDAY\n",
    "\n",
    "import pandas as pd\n",
    "\n",
    "# 2016 public holidays\n",
    "holidays_2016 = pd.to_datetime([\n",
    "    '2016-01-01',  # new year\n",
    "    '2016-01-18',  # MLK\n",
    "    '2016-05-30',  # memorial\n",
    "    '2016-07-04',  # independence\n",
    "    '2016-09-05',  # labor\n",
    "    '2016-11-11',  # veterans\n",
    "    '2016-11-24',  # thanksgiving\n",
    "    '2016-12-26',  # christmas\n",
    "])\n",
    "\n",
    "\n",
    "def load_df(file_name):\n",
    "    \"\"\"Load data from CSV to DataFrame\"\"\"\n",
    "    return pd.read_csv(\n",
    "        file_name,\n",
    "        parse_dates={'time': ['Checkout Date', 'Checkout Time']},\n",
    "    )\n",
    "\n",
    "\n",
    "def vacation_rides(df):\n",
    "    \"\"\"Return only rows that are in holiday afternoon\"\"\"\n",
    "    mask_2016 = df['time'].dt.year == 2016\n",
    "\n",
    "    holiday_mask = (\n",
    "        (df['time'].dt.floor('d').isin(holidays_2016)) |\n",
    "        (df['time'].dt.weekday.isin([SATURDAY, SUNDAY]))\n",
    "    )\n",
    "\n",
    "    afternoon_mask = (df['time'].dt.hour >= 12) & (df['time'].dt.hour < 18)\n",
    "\n",
    "    return df[mask_2016 & holiday_mask & afternoon_mask]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "038d1640-31f7-4e1d-a68a-24be5dda81c2",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Advait\\AppData\\Local\\Temp\\ipykernel_10004\\2478150591.py:27: FutureWarning: Support for nested sequences for 'parse_dates' in pd.read_csv is deprecated. Combine the desired columns with pd.to_datetime after parsing instead.\n",
      "  return pd.read_csv(\n"
     ]
    }
   ],
   "source": [
    "df = load_df('austin-bikes.csv.xz')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "c910e702-20b3-4d35-90ba-1c5a38b137e3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "421"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(vacation_rides(df))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "408c9fca-3ecc-4b8f-ad21-0020f5926d27",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "3.91 ms ± 679 μs per loop (mean ± std. dev. of 7 runs, 100 loops each)\n"
     ]
    }
   ],
   "source": [
    "%timeit vacation_rides(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "bb8b6775-0565-4988-9ce1-65f0d8878c22",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2301.7902813299233"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "9000 / 3.91"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d5716826-bcb7-4c7a-8a20-a8cd2422f429",
   "metadata": {},
   "source": [
    "## 4. Pandas Performance "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "509c5e19-3cbf-41e8-9178-eb9954f8b2d2",
   "metadata": {},
   "source": [
    "### A. Using Built In functions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "6b3b13e2-f034-48fc-9391-f82a3a61865d",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('austin-bikes.csv.xz', low_memory = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "4ab0d61d-b27c-4c71-ba54-6f3d31763b91",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "11553"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "max(df['Trip Duration Minutes'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "df60fdbe-acd0-4ba7-8674-e6269bb7dc5b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "8.022916666666667"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Out[40] / (24*60)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "e8562fa4-95ee-4ebf-a685-3a96b75ee96f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "11553"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Trip Duration Minutes'].max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "123907a0-6ef8-4910-b300-cffc5c322bf5",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1.13 ms ± 62.4 μs per loop (mean ± std. dev. of 7 runs, 1,000 loops each)\n"
     ]
    }
   ],
   "source": [
    "%timeit max(df['Trip Duration Minutes'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "f979ebd8-9cc0-4b43-89f2-a18dd170009e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "26.4 μs ± 4.75 μs per loop (mean ± std. dev. of 7 runs, 10,000 loops each)\n"
     ]
    }
   ],
   "source": [
    "%timeit df['Trip Duration Minutes'].max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "b7361c60-7afc-4917-a8b0-d4f54475ffc1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "428.03030303030306"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "11300 / 26.4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "d0940add-707c-4b34-ab10-c5e88fd67400",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "9.93 μs ± 1.22 μs per loop (mean ± std. dev. of 7 runs, 100,000 loops each)\n"
     ]
    }
   ],
   "source": [
    "%timeit df['Trip Duration Minutes'].values.max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "463acffd-6ea6-40dd-a01d-b769d09948d9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1137.9657603222558"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "11300 / 9.93"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "49f92cdf-65f1-4034-8ddd-775780882e10",
   "metadata": {},
   "source": [
    "### B. Understanding eval and query "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "7073b864-c2d6-497d-9fc8-571e0ac62c29",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: numexpr in c:\\users\\advait\\anaconda3\\lib\\site-packages (2.8.7)\n",
      "Requirement already satisfied: numpy>=1.13.3 in c:\\users\\advait\\anaconda3\\lib\\site-packages (from numexpr) (1.26.4)\n"
     ]
    }
   ],
   "source": [
    "!pip install numexpr"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "2d3a4d88-8a68-4817-a323-c464f6ff0492",
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "conn = sqlite3.connect('logs_4db.db', detect_types = sqlite3.PARSE_DECLTYPES)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "27ce8d36-6a7a-4dc5-81a8-4f1e8d04c846",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_sql('SELECT * FROM logs', conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "4f35ee18-e2b8-4138-bfcc-5523de6c696d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "10000"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "02106fd3-6ec3-4580-8ccc-da2b576254d6",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.concat([df] * 1000)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "8ef423d1-59cc-4d38-93cd-90af3604ad8e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "10000000"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "4866b97f-a901-4e0b-adf3-90d54212e2e4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'10,000,000'"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "f'{len(df) :,}'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "e033f6a9-5e1b-4e35-b7a7-cb6c9ad8f324",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "867 ms ± 57.7 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)\n"
     ]
    }
   ],
   "source": [
    "%timeit df[(df['method'] == 'GET') & (df['status_code'] >= 400)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "8acfd5ef-20e7-4294-a2b9-1dee9537008d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "498 ms ± 21 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)\n"
     ]
    }
   ],
   "source": [
    "%timeit df.query('method == \"GET\" & status_code >= 400')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "6cf6d68f-1ab6-454a-9d5a-1d819f1a5c65",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.5743944636678201"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "498 / 867"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "52e08296-0135-4c2d-a286-7d861aceb085",
   "metadata": {},
   "source": [
    "### C. Understanding join function"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "9c113999-d492-451d-bf12-f6f4559e8ebf",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('taxi.csv.xz')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "3a099555-374d-4f96-ace8-a2ffd4dc37ad",
   "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>VendorID</th>\n",
       "      <th>tpep_pickup_datetime</th>\n",
       "      <th>tpep_dropoff_datetime</th>\n",
       "      <th>passenger_count</th>\n",
       "      <th>trip_distance</th>\n",
       "      <th>RatecodeID</th>\n",
       "      <th>store_and_fwd_flag</th>\n",
       "      <th>PULocationID</th>\n",
       "      <th>DOLocationID</th>\n",
       "      <th>payment_type</th>\n",
       "      <th>fare_amount</th>\n",
       "      <th>extra</th>\n",
       "      <th>mta_tax</th>\n",
       "      <th>tip_amount</th>\n",
       "      <th>tolls_amount</th>\n",
       "      <th>improvement_surcharge</th>\n",
       "      <th>total_amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>10788</th>\n",
       "      <td>1</td>\n",
       "      <td>2018-05-01 03:43:36</td>\n",
       "      <td>2018-05-01 03:51:08</td>\n",
       "      <td>1</td>\n",
       "      <td>3.40</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>141</td>\n",
       "      <td>7</td>\n",
       "      <td>2</td>\n",
       "      <td>11.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>12.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>120329</th>\n",
       "      <td>2</td>\n",
       "      <td>2018-05-01 14:00:03</td>\n",
       "      <td>2018-05-01 14:15:18</td>\n",
       "      <td>1</td>\n",
       "      <td>1.68</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>125</td>\n",
       "      <td>164</td>\n",
       "      <td>1</td>\n",
       "      <td>11.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1.20</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>13.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>406064</th>\n",
       "      <td>1</td>\n",
       "      <td>2018-05-02 11:58:22</td>\n",
       "      <td>2018-05-02 12:09:04</td>\n",
       "      <td>1</td>\n",
       "      <td>1.10</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>186</td>\n",
       "      <td>161</td>\n",
       "      <td>1</td>\n",
       "      <td>8.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>2.20</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>11.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>332614</th>\n",
       "      <td>2</td>\n",
       "      <td>2018-05-02 06:32:46</td>\n",
       "      <td>2018-05-02 06:40:22</td>\n",
       "      <td>1</td>\n",
       "      <td>1.33</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>100</td>\n",
       "      <td>161</td>\n",
       "      <td>2</td>\n",
       "      <td>7.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>7.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>376129</th>\n",
       "      <td>2</td>\n",
       "      <td>2018-05-02 09:47:41</td>\n",
       "      <td>2018-05-02 09:55:50</td>\n",
       "      <td>1</td>\n",
       "      <td>0.94</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>100</td>\n",
       "      <td>163</td>\n",
       "      <td>1</td>\n",
       "      <td>7.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1.56</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>9.36</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        VendorID tpep_pickup_datetime tpep_dropoff_datetime  passenger_count  \\\n",
       "10788          1  2018-05-01 03:43:36   2018-05-01 03:51:08                1   \n",
       "120329         2  2018-05-01 14:00:03   2018-05-01 14:15:18                1   \n",
       "406064         1  2018-05-02 11:58:22   2018-05-02 12:09:04                1   \n",
       "332614         2  2018-05-02 06:32:46   2018-05-02 06:40:22                1   \n",
       "376129         2  2018-05-02 09:47:41   2018-05-02 09:55:50                1   \n",
       "\n",
       "        trip_distance  RatecodeID store_and_fwd_flag  PULocationID  \\\n",
       "10788            3.40           1                  N           141   \n",
       "120329           1.68           1                  N           125   \n",
       "406064           1.10           1                  N           186   \n",
       "332614           1.33           1                  N           100   \n",
       "376129           0.94           1                  N           100   \n",
       "\n",
       "        DOLocationID  payment_type  fare_amount  extra  mta_tax  tip_amount  \\\n",
       "10788              7             2         11.0    0.5      0.5        0.00   \n",
       "120329           164             1         11.0    0.0      0.5        1.20   \n",
       "406064           161             1          8.0    0.0      0.5        2.20   \n",
       "332614           161             2          7.0    0.0      0.5        0.00   \n",
       "376129           163             1          7.0    0.0      0.5        1.56   \n",
       "\n",
       "        tolls_amount  improvement_surcharge  total_amount  \n",
       "10788            0.0                    0.3         12.30  \n",
       "120329           0.0                    0.3         13.00  \n",
       "406064           0.0                    0.3         11.00  \n",
       "332614           0.0                    0.3          7.80  \n",
       "376129           0.0                    0.3          9.36  "
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "e65e150a-80dd-4430-b7e9-1f89fa681916",
   "metadata": {},
   "outputs": [],
   "source": [
    "ndf = pd.DataFrame([\n",
    "    (1, 'Creative'),\n",
    "    (2, 'Verifone'),\n",
    "    (3, 'BigApple')\n",
    "], columns = ['id', 'Vendor'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "id": "fca9656a-22ec-4e05-9433-c58fe9d04d84",
   "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>id</th>\n",
       "      <th>Vendor</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Creative</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Verifone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>BigApple</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id    Vendor\n",
       "0   1  Creative\n",
       "1   2  Verifone\n",
       "2   3  BigApple"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ndf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "id": "c923de94-7dde-48fa-8af4-b304429fd0dd",
   "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>VendorID</th>\n",
       "      <th>tpep_pickup_datetime</th>\n",
       "      <th>tpep_dropoff_datetime</th>\n",
       "      <th>passenger_count</th>\n",
       "      <th>trip_distance</th>\n",
       "      <th>RatecodeID</th>\n",
       "      <th>store_and_fwd_flag</th>\n",
       "      <th>PULocationID</th>\n",
       "      <th>DOLocationID</th>\n",
       "      <th>payment_type</th>\n",
       "      <th>fare_amount</th>\n",
       "      <th>extra</th>\n",
       "      <th>mta_tax</th>\n",
       "      <th>tip_amount</th>\n",
       "      <th>tolls_amount</th>\n",
       "      <th>improvement_surcharge</th>\n",
       "      <th>total_amount</th>\n",
       "      <th>id</th>\n",
       "      <th>Vendor</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2018-05-01 00:13:56</td>\n",
       "      <td>2018-05-01 00:22:46</td>\n",
       "      <td>1</td>\n",
       "      <td>1.60</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>230</td>\n",
       "      <td>50</td>\n",
       "      <td>1</td>\n",
       "      <td>8.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1.85</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.3</td>\n",
       "      <td>11.15</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Creative</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2018-05-01 00:23:26</td>\n",
       "      <td>2018-05-01 00:29:56</td>\n",
       "      <td>1</td>\n",
       "      <td>1.70</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>263</td>\n",
       "      <td>239</td>\n",
       "      <td>1</td>\n",
       "      <td>7.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>2.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.3</td>\n",
       "      <td>10.80</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Creative</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>2018-05-01 00:36:23</td>\n",
       "      <td>2018-05-01 00:48:26</td>\n",
       "      <td>2</td>\n",
       "      <td>2.60</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>239</td>\n",
       "      <td>152</td>\n",
       "      <td>1</td>\n",
       "      <td>12.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.3</td>\n",
       "      <td>14.30</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Creative</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>2018-05-01 00:26:12</td>\n",
       "      <td>2018-05-01 00:27:05</td>\n",
       "      <td>1</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>145</td>\n",
       "      <td>145</td>\n",
       "      <td>1</td>\n",
       "      <td>2.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>9.63</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.3</td>\n",
       "      <td>13.43</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Creative</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>2018-05-01 00:29:51</td>\n",
       "      <td>2018-05-01 00:30:02</td>\n",
       "      <td>1</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>145</td>\n",
       "      <td>145</td>\n",
       "      <td>2</td>\n",
       "      <td>2.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.3</td>\n",
       "      <td>3.80</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Creative</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>499994</th>\n",
       "      <td>2</td>\n",
       "      <td>2018-05-02 17:27:40</td>\n",
       "      <td>2018-05-02 17:33:37</td>\n",
       "      <td>1</td>\n",
       "      <td>0.65</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>233</td>\n",
       "      <td>229</td>\n",
       "      <td>1</td>\n",
       "      <td>5.5</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1.46</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.3</td>\n",
       "      <td>8.76</td>\n",
       "      <td>2.0</td>\n",
       "      <td>Verifone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>499995</th>\n",
       "      <td>2</td>\n",
       "      <td>2018-05-02 17:36:29</td>\n",
       "      <td>2018-05-02 17:48:23</td>\n",
       "      <td>1</td>\n",
       "      <td>2.00</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>229</td>\n",
       "      <td>224</td>\n",
       "      <td>1</td>\n",
       "      <td>9.5</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>2.26</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.3</td>\n",
       "      <td>13.56</td>\n",
       "      <td>2.0</td>\n",
       "      <td>Verifone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>499996</th>\n",
       "      <td>2</td>\n",
       "      <td>2018-05-02 17:56:43</td>\n",
       "      <td>2018-05-02 18:07:26</td>\n",
       "      <td>1</td>\n",
       "      <td>1.62</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>137</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>9.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.3</td>\n",
       "      <td>10.80</td>\n",
       "      <td>2.0</td>\n",
       "      <td>Verifone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>499997</th>\n",
       "      <td>2</td>\n",
       "      <td>2018-05-02 17:33:05</td>\n",
       "      <td>2018-05-02 18:38:26</td>\n",
       "      <td>1</td>\n",
       "      <td>18.70</td>\n",
       "      <td>2</td>\n",
       "      <td>N</td>\n",
       "      <td>132</td>\n",
       "      <td>50</td>\n",
       "      <td>1</td>\n",
       "      <td>52.0</td>\n",
       "      <td>4.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>12.61</td>\n",
       "      <td>5.76</td>\n",
       "      <td>0.3</td>\n",
       "      <td>75.67</td>\n",
       "      <td>2.0</td>\n",
       "      <td>Verifone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>499998</th>\n",
       "      <td>1</td>\n",
       "      <td>2018-05-02 17:19:21</td>\n",
       "      <td>2018-05-02 17:33:25</td>\n",
       "      <td>1</td>\n",
       "      <td>1.70</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>236</td>\n",
       "      <td>162</td>\n",
       "      <td>2</td>\n",
       "      <td>10.5</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.3</td>\n",
       "      <td>12.30</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Creative</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>499999 rows × 19 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        VendorID tpep_pickup_datetime tpep_dropoff_datetime  passenger_count  \\\n",
       "0              1  2018-05-01 00:13:56   2018-05-01 00:22:46                1   \n",
       "1              1  2018-05-01 00:23:26   2018-05-01 00:29:56                1   \n",
       "2              1  2018-05-01 00:36:23   2018-05-01 00:48:26                2   \n",
       "3              1  2018-05-01 00:26:12   2018-05-01 00:27:05                1   \n",
       "4              1  2018-05-01 00:29:51   2018-05-01 00:30:02                1   \n",
       "...          ...                  ...                   ...              ...   \n",
       "499994         2  2018-05-02 17:27:40   2018-05-02 17:33:37                1   \n",
       "499995         2  2018-05-02 17:36:29   2018-05-02 17:48:23                1   \n",
       "499996         2  2018-05-02 17:56:43   2018-05-02 18:07:26                1   \n",
       "499997         2  2018-05-02 17:33:05   2018-05-02 18:38:26                1   \n",
       "499998         1  2018-05-02 17:19:21   2018-05-02 17:33:25                1   \n",
       "\n",
       "        trip_distance  RatecodeID store_and_fwd_flag  PULocationID  \\\n",
       "0                1.60           1                  N           230   \n",
       "1                1.70           1                  N           263   \n",
       "2                2.60           1                  N           239   \n",
       "3                0.00           1                  N           145   \n",
       "4                0.00           1                  N           145   \n",
       "...               ...         ...                ...           ...   \n",
       "499994           0.65           1                  N           233   \n",
       "499995           2.00           1                  N           229   \n",
       "499996           1.62           1                  N           137   \n",
       "499997          18.70           2                  N           132   \n",
       "499998           1.70           1                  N           236   \n",
       "\n",
       "        DOLocationID  payment_type  fare_amount  extra  mta_tax  tip_amount  \\\n",
       "0                 50             1          8.0    0.5      0.5        1.85   \n",
       "1                239             1          7.5    0.5      0.5        2.00   \n",
       "2                152             1         12.0    0.5      0.5        1.00   \n",
       "3                145             1          2.5    0.5      0.5        9.63   \n",
       "4                145             2          2.5    0.5      0.5        0.00   \n",
       "...              ...           ...          ...    ...      ...         ...   \n",
       "499994           229             1          5.5    1.0      0.5        1.46   \n",
       "499995           224             1          9.5    1.0      0.5        2.26   \n",
       "499996             4             2          9.0    1.0      0.5        0.00   \n",
       "499997            50             1         52.0    4.5      0.5       12.61   \n",
       "499998           162             2         10.5    1.0      0.5        0.00   \n",
       "\n",
       "        tolls_amount  improvement_surcharge  total_amount   id    Vendor  \n",
       "0               0.00                    0.3         11.15  1.0  Creative  \n",
       "1               0.00                    0.3         10.80  1.0  Creative  \n",
       "2               0.00                    0.3         14.30  1.0  Creative  \n",
       "3               0.00                    0.3         13.43  1.0  Creative  \n",
       "4               0.00                    0.3          3.80  1.0  Creative  \n",
       "...              ...                    ...           ...  ...       ...  \n",
       "499994          0.00                    0.3          8.76  2.0  Verifone  \n",
       "499995          0.00                    0.3         13.56  2.0  Verifone  \n",
       "499996          0.00                    0.3         10.80  2.0  Verifone  \n",
       "499997          5.76                    0.3         75.67  2.0  Verifone  \n",
       "499998          0.00                    0.3         12.30  1.0  Creative  \n",
       "\n",
       "[499999 rows x 19 columns]"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(df, ndf, left_on  = 'VendorID', right_on = 'id', how = 'left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "id": "9ba9bdd1-c2d1-4bcf-8fb5-694ca9157ef5",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "94.1 ms ± 3.79 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)\n"
     ]
    }
   ],
   "source": [
    "%timeit pd.merge(df, ndf, left_on  = 'VendorID', right_on = 'id', how = 'left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "id": "68243d74-ad45-4471-9dbb-f5b28c5e4d6d",
   "metadata": {},
   "outputs": [],
   "source": [
    "ndfi = ndf.set_index('id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "id": "c82e1a25-2822-4948-80e2-806ff81f14a3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "78.8 ms ± 3.33 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)\n"
     ]
    }
   ],
   "source": [
    "%timeit pd.merge(df, ndfi, left_on  = 'VendorID', right_index = True, how = 'left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "id": "ebaca91e-50e0-40c7-bce8-ee4db8d1712f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1.1941624365482233"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "94.1 / 78.8"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8b8e90f8-2065-4c2b-b403-d7d9b2cf9d9a",
   "metadata": {},
   "source": [
    "### Challenge 4 --> Join and Query"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "id": "e756ae0f-1569-4f83-b181-fbeb8ae8e129",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "16.0\n"
     ]
    }
   ],
   "source": [
    "\"\"\"What is the median trip duration in 2017, only in active kiosks?\n",
    "\n",
    "- Trip data in austin-bikes.csv.xz\n",
    "- Kiosk status data in austin-kiosk.csv\n",
    "\"\"\"\n",
    "\n",
    "import pandas as pd\n",
    "\n",
    "bike_df = pd.read_csv('austin-bikes.csv.xz', low_memory=False)\n",
    "\n",
    "# Set index to 'Kiosk ID' for faster merge\n",
    "kiosk_df = pd.read_csv('austin-kiosk.csv', index_col='Kiosk ID')\n",
    "df = pd.merge(\n",
    "    bike_df, kiosk_df, left_on='Checkout Kiosk ID', right_index=True)\n",
    "\n",
    "# Use query for selecting data\n",
    "active_2017 = df.query(\n",
    "    '`Kiosk Status` == \"active\" & Year == 2017 & `Trip Duration Minutes` > 0')\n",
    "\n",
    "# Use built-in median\n",
    "print(active_2017['Trip Duration Minutes'].median())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fc169f04-cdd9-4708-9a54-7c0f34c0db63",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}