Lec16.daskdataframes.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Dask DataFrames\n",
    "\n",
    "Similar to the design of arrays, dask dataframes are paritioned pandas dataframes. Computations run pandas operators on the chunks and aggregate results from the chunked operations.\n",
    "\n",
    "<img src=\"https://upload.wikimedia.org/wikipedia/commons/8/81/Dask-dataframe.svg\" width=\"256\" title=\"    https://upload.wikimedia.org/wikipedia/commons/8/81/Dask-dataframe.svg\" />\n",
    "\n",
    "For the most part, dask has tried to implement all of pandas, but there are some inefficient operations that it does not support.\n",
    "\n",
    "Let's load up a dataframe and see what we get.  This is the NYC flight data used in the dask tutorial."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><strong>Dask DataFrame Structure:</strong></div>\n",
       "<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>DayOfWeek</th>\n",
       "      <th>DepTime</th>\n",
       "      <th>CRSDepTime</th>\n",
       "      <th>ArrTime</th>\n",
       "      <th>CRSArrTime</th>\n",
       "      <th>UniqueCarrier</th>\n",
       "      <th>FlightNum</th>\n",
       "      <th>TailNum</th>\n",
       "      <th>ActualElapsedTime</th>\n",
       "      <th>CRSElapsedTime</th>\n",
       "      <th>AirTime</th>\n",
       "      <th>ArrDelay</th>\n",
       "      <th>DepDelay</th>\n",
       "      <th>Origin</th>\n",
       "      <th>Dest</th>\n",
       "      <th>Distance</th>\n",
       "      <th>TaxiIn</th>\n",
       "      <th>TaxiOut</th>\n",
       "      <th>Cancelled</th>\n",
       "      <th>Diverted</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>npartitions=10</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <td>datetime64[ns]</td>\n",
       "      <td>int64</td>\n",
       "      <td>float64</td>\n",
       "      <td>int64</td>\n",
       "      <td>float64</td>\n",
       "      <td>int64</td>\n",
       "      <td>object</td>\n",
       "      <td>int64</td>\n",
       "      <td>object</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>object</td>\n",
       "      <td>object</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>bool</td>\n",
       "      <td>int64</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",
       "      <td>...</td>\n",
       "      <td>...</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",
       "      <td>...</td>\n",
       "      <td>...</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",
       "      <td>...</td>\n",
       "      <td>...</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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "<div>Dask Name: read-csv, 10 tasks</div>"
      ],
      "text/plain": [
       "Dask DataFrame Structure:\n",
       "                          Date DayOfWeek  DepTime CRSDepTime  ArrTime CRSArrTime UniqueCarrier FlightNum TailNum ActualElapsedTime CRSElapsedTime  AirTime ArrDelay DepDelay  Origin    Dest Distance   TaxiIn  TaxiOut Cancelled Diverted\n",
       "npartitions=10                                                                                                                                                                                                                            \n",
       "                datetime64[ns]     int64  float64      int64  float64      int64        object     int64  object           float64        float64  float64  float64  float64  object  object  float64  float64  float64      bool    int64\n",
       "                           ...       ...      ...        ...      ...        ...           ...       ...     ...               ...            ...      ...      ...      ...     ...     ...      ...      ...      ...       ...      ...\n",
       "...                        ...       ...      ...        ...      ...        ...           ...       ...     ...               ...            ...      ...      ...      ...     ...     ...      ...      ...      ...       ...      ...\n",
       "                           ...       ...      ...        ...      ...        ...           ...       ...     ...               ...            ...      ...      ...      ...     ...     ...      ...      ...      ...       ...      ...\n",
       "                           ...       ...      ...        ...      ...        ...           ...       ...     ...               ...            ...      ...      ...      ...     ...     ...      ...      ...      ...       ...      ...\n",
       "Dask Name: read-csv, 10 tasks"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import dask.dataframe as dd\n",
    "df = dd.read_csv('./data/nycflight/*.csv',\n",
    "                 parse_dates={'Date': [0, 1, 2]},\n",
    "                 dtype={'TailNum': str,\n",
    "                        'CRSElapsedTime': float,\n",
    "                        'Cancelled': bool})\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "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>DayOfWeek</th>\n",
       "      <th>DepTime</th>\n",
       "      <th>CRSDepTime</th>\n",
       "      <th>ArrTime</th>\n",
       "      <th>CRSArrTime</th>\n",
       "      <th>UniqueCarrier</th>\n",
       "      <th>FlightNum</th>\n",
       "      <th>TailNum</th>\n",
       "      <th>ActualElapsedTime</th>\n",
       "      <th>...</th>\n",
       "      <th>AirTime</th>\n",
       "      <th>ArrDelay</th>\n",
       "      <th>DepDelay</th>\n",
       "      <th>Origin</th>\n",
       "      <th>Dest</th>\n",
       "      <th>Distance</th>\n",
       "      <th>TaxiIn</th>\n",
       "      <th>TaxiOut</th>\n",
       "      <th>Cancelled</th>\n",
       "      <th>Diverted</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>269176</th>\n",
       "      <td>1999-12-27</td>\n",
       "      <td>1</td>\n",
       "      <td>1645.0</td>\n",
       "      <td>1645</td>\n",
       "      <td>1830.0</td>\n",
       "      <td>1901</td>\n",
       "      <td>UA</td>\n",
       "      <td>1753</td>\n",
       "      <td>N516UA</td>\n",
       "      <td>225.0</td>\n",
       "      <td>...</td>\n",
       "      <td>205.0</td>\n",
       "      <td>-31.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>LGA</td>\n",
       "      <td>DEN</td>\n",
       "      <td>1619.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>False</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>269177</th>\n",
       "      <td>1999-12-28</td>\n",
       "      <td>2</td>\n",
       "      <td>1726.0</td>\n",
       "      <td>1645</td>\n",
       "      <td>1928.0</td>\n",
       "      <td>1901</td>\n",
       "      <td>UA</td>\n",
       "      <td>1753</td>\n",
       "      <td>N504UA</td>\n",
       "      <td>242.0</td>\n",
       "      <td>...</td>\n",
       "      <td>214.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>41.0</td>\n",
       "      <td>LGA</td>\n",
       "      <td>DEN</td>\n",
       "      <td>1619.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>23.0</td>\n",
       "      <td>False</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>269178</th>\n",
       "      <td>1999-12-29</td>\n",
       "      <td>3</td>\n",
       "      <td>1646.0</td>\n",
       "      <td>1645</td>\n",
       "      <td>1846.0</td>\n",
       "      <td>1901</td>\n",
       "      <td>UA</td>\n",
       "      <td>1753</td>\n",
       "      <td>N592UA</td>\n",
       "      <td>240.0</td>\n",
       "      <td>...</td>\n",
       "      <td>220.0</td>\n",
       "      <td>-15.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>LGA</td>\n",
       "      <td>DEN</td>\n",
       "      <td>1619.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>False</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>269179</th>\n",
       "      <td>1999-12-30</td>\n",
       "      <td>4</td>\n",
       "      <td>1651.0</td>\n",
       "      <td>1645</td>\n",
       "      <td>1908.0</td>\n",
       "      <td>1901</td>\n",
       "      <td>UA</td>\n",
       "      <td>1753</td>\n",
       "      <td>N575UA</td>\n",
       "      <td>257.0</td>\n",
       "      <td>...</td>\n",
       "      <td>233.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>LGA</td>\n",
       "      <td>DEN</td>\n",
       "      <td>1619.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>False</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>269180</th>\n",
       "      <td>1999-12-31</td>\n",
       "      <td>5</td>\n",
       "      <td>1642.0</td>\n",
       "      <td>1645</td>\n",
       "      <td>1851.0</td>\n",
       "      <td>1901</td>\n",
       "      <td>UA</td>\n",
       "      <td>1753</td>\n",
       "      <td>N539UA</td>\n",
       "      <td>249.0</td>\n",
       "      <td>...</td>\n",
       "      <td>232.0</td>\n",
       "      <td>-10.0</td>\n",
       "      <td>-3.0</td>\n",
       "      <td>LGA</td>\n",
       "      <td>DEN</td>\n",
       "      <td>1619.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>False</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             Date  DayOfWeek  DepTime  CRSDepTime  ArrTime  CRSArrTime  \\\n",
       "269176 1999-12-27          1   1645.0        1645   1830.0        1901   \n",
       "269177 1999-12-28          2   1726.0        1645   1928.0        1901   \n",
       "269178 1999-12-29          3   1646.0        1645   1846.0        1901   \n",
       "269179 1999-12-30          4   1651.0        1645   1908.0        1901   \n",
       "269180 1999-12-31          5   1642.0        1645   1851.0        1901   \n",
       "\n",
       "       UniqueCarrier  FlightNum TailNum  ActualElapsedTime  ...  AirTime  \\\n",
       "269176            UA       1753  N516UA              225.0  ...    205.0   \n",
       "269177            UA       1753  N504UA              242.0  ...    214.0   \n",
       "269178            UA       1753  N592UA              240.0  ...    220.0   \n",
       "269179            UA       1753  N575UA              257.0  ...    233.0   \n",
       "269180            UA       1753  N539UA              249.0  ...    232.0   \n",
       "\n",
       "        ArrDelay  DepDelay  Origin Dest Distance  TaxiIn  TaxiOut  Cancelled  \\\n",
       "269176     -31.0       0.0     LGA  DEN   1619.0     7.0     13.0      False   \n",
       "269177      27.0      41.0     LGA  DEN   1619.0     5.0     23.0      False   \n",
       "269178     -15.0       1.0     LGA  DEN   1619.0     5.0     15.0      False   \n",
       "269179       7.0       6.0     LGA  DEN   1619.0     5.0     19.0      False   \n",
       "269180     -10.0      -3.0     LGA  DEN   1619.0     6.0     11.0      False   \n",
       "\n",
       "        Diverted  \n",
       "269176         0  \n",
       "269177         0  \n",
       "269178         0  \n",
       "269179         0  \n",
       "269180         0  \n",
       "\n",
       "[5 rows x 21 columns]"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "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>DayOfWeek</th>\n",
       "      <th>DepTime</th>\n",
       "      <th>CRSDepTime</th>\n",
       "      <th>ArrTime</th>\n",
       "      <th>CRSArrTime</th>\n",
       "      <th>UniqueCarrier</th>\n",
       "      <th>FlightNum</th>\n",
       "      <th>TailNum</th>\n",
       "      <th>ActualElapsedTime</th>\n",
       "      <th>...</th>\n",
       "      <th>AirTime</th>\n",
       "      <th>ArrDelay</th>\n",
       "      <th>DepDelay</th>\n",
       "      <th>Origin</th>\n",
       "      <th>Dest</th>\n",
       "      <th>Distance</th>\n",
       "      <th>TaxiIn</th>\n",
       "      <th>TaxiOut</th>\n",
       "      <th>Cancelled</th>\n",
       "      <th>Diverted</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1990-01-01</td>\n",
       "      <td>1</td>\n",
       "      <td>1621.0</td>\n",
       "      <td>1540</td>\n",
       "      <td>1747.0</td>\n",
       "      <td>1701</td>\n",
       "      <td>US</td>\n",
       "      <td>33</td>\n",
       "      <td>NaN</td>\n",
       "      <td>86.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>46.0</td>\n",
       "      <td>41.0</td>\n",
       "      <td>EWR</td>\n",
       "      <td>PIT</td>\n",
       "      <td>319.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1990-01-02</td>\n",
       "      <td>2</td>\n",
       "      <td>1547.0</td>\n",
       "      <td>1540</td>\n",
       "      <td>1700.0</td>\n",
       "      <td>1701</td>\n",
       "      <td>US</td>\n",
       "      <td>33</td>\n",
       "      <td>NaN</td>\n",
       "      <td>73.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>EWR</td>\n",
       "      <td>PIT</td>\n",
       "      <td>319.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1990-01-03</td>\n",
       "      <td>3</td>\n",
       "      <td>1546.0</td>\n",
       "      <td>1540</td>\n",
       "      <td>1710.0</td>\n",
       "      <td>1701</td>\n",
       "      <td>US</td>\n",
       "      <td>33</td>\n",
       "      <td>NaN</td>\n",
       "      <td>84.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>EWR</td>\n",
       "      <td>PIT</td>\n",
       "      <td>319.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1990-01-04</td>\n",
       "      <td>4</td>\n",
       "      <td>1542.0</td>\n",
       "      <td>1540</td>\n",
       "      <td>1710.0</td>\n",
       "      <td>1701</td>\n",
       "      <td>US</td>\n",
       "      <td>33</td>\n",
       "      <td>NaN</td>\n",
       "      <td>88.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>EWR</td>\n",
       "      <td>PIT</td>\n",
       "      <td>319.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1990-01-05</td>\n",
       "      <td>5</td>\n",
       "      <td>1549.0</td>\n",
       "      <td>1540</td>\n",
       "      <td>1706.0</td>\n",
       "      <td>1701</td>\n",
       "      <td>US</td>\n",
       "      <td>33</td>\n",
       "      <td>NaN</td>\n",
       "      <td>77.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>EWR</td>\n",
       "      <td>PIT</td>\n",
       "      <td>319.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        Date  DayOfWeek  DepTime  CRSDepTime  ArrTime  CRSArrTime  \\\n",
       "0 1990-01-01          1   1621.0        1540   1747.0        1701   \n",
       "1 1990-01-02          2   1547.0        1540   1700.0        1701   \n",
       "2 1990-01-03          3   1546.0        1540   1710.0        1701   \n",
       "3 1990-01-04          4   1542.0        1540   1710.0        1701   \n",
       "4 1990-01-05          5   1549.0        1540   1706.0        1701   \n",
       "\n",
       "  UniqueCarrier  FlightNum TailNum  ActualElapsedTime  ...  AirTime  ArrDelay  \\\n",
       "0            US         33     NaN               86.0  ...      NaN      46.0   \n",
       "1            US         33     NaN               73.0  ...      NaN      -1.0   \n",
       "2            US         33     NaN               84.0  ...      NaN       9.0   \n",
       "3            US         33     NaN               88.0  ...      NaN       9.0   \n",
       "4            US         33     NaN               77.0  ...      NaN       5.0   \n",
       "\n",
       "   DepDelay  Origin Dest Distance  TaxiIn  TaxiOut  Cancelled  Diverted  \n",
       "0      41.0     EWR  PIT    319.0     NaN      NaN      False         0  \n",
       "1       7.0     EWR  PIT    319.0     NaN      NaN      False         0  \n",
       "2       6.0     EWR  PIT    319.0     NaN      NaN      False         0  \n",
       "3       2.0     EWR  PIT    319.0     NaN      NaN      False         0  \n",
       "4       9.0     EWR  PIT    319.0     NaN      NaN      False         0  \n",
       "\n",
       "[5 rows x 21 columns]"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The dataframe has 10 chunks (npartitions) that correspond to the ten files that were read.  Dataframes have two key properties:\n",
    "  * they are tabular (two-dimensional) data\n",
    "  * each column has a datatype defined by a _schema_\n",
    "  \n",
    "The programmer is encouraged to think of this as \"spreadsheet or SQL table\".  It is reasonable to call the data __structured__.\n",
    "  * this is in contrast to semi-structured data which has tags and hierarchy, but does not enforce types. Examples are XML and JSON.\n",
    "  * in database parlance, this is flat data model.\n",
    "\n",
    "### Data Slicing and Aggregation\n",
    "\n",
    "The most basic operations in a database and in dask is to <code>select</code> rows and <code>project</code> columns.\n",
    "\n",
    "#### Selecting rows\n",
    "\n",
    "Find all flights flown by a specific plane, identified by <code>TailNum</code>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = df[df.TailNum=='N516UA'].compute()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Projecting Columns\n",
    "\n",
    "Build a dataframe that describes the plane ('TailNum') and route ('FlightNum')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>FlightNum</th>\n",
       "      <th>TailNum</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>33</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>33</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>33</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>33</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>33</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>269176</th>\n",
       "      <td>1753</td>\n",
       "      <td>N516UA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>269177</th>\n",
       "      <td>1753</td>\n",
       "      <td>N504UA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>269178</th>\n",
       "      <td>1753</td>\n",
       "      <td>N592UA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>269179</th>\n",
       "      <td>1753</td>\n",
       "      <td>N575UA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>269180</th>\n",
       "      <td>1753</td>\n",
       "      <td>N539UA</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2611892 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        FlightNum TailNum\n",
       "0              33     NaN\n",
       "1              33     NaN\n",
       "2              33     NaN\n",
       "3              33     NaN\n",
       "4              33     NaN\n",
       "...           ...     ...\n",
       "269176       1753  N516UA\n",
       "269177       1753  N504UA\n",
       "269178       1753  N592UA\n",
       "269179       1753  N575UA\n",
       "269180       1753  N539UA\n",
       "\n",
       "[2611892 rows x 2 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#routes = df[df.TailNum=='N516UA'][['FlightNum','TailNum']].compute()\n",
    "routes = df[['FlightNum','TailNum']].compute()\n",
    "\n",
    "routes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Aggregating Data\n",
    "\n",
    "A common data science inquiry is to query an aggregate (mean, min, max, sum, etc.) in a group.  This is done with a <code>groupby</code> query. The pattern is to construct a grouping and then aggregate over the grouping.\n",
    "\n",
    "__Query:__ How many different flights were flown by each plane?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "TailNum\n",
       "'144DA        1\n",
       "112          10\n",
       "A367NW       39\n",
       "EI-BWD      638\n",
       "EI-CAL       91\n",
       "          ...  \n",
       "NEIDLA      125\n",
       "NN7324       74\n",
       "NOZ1AA       27\n",
       "NXXXXX        4\n",
       "UNKNOW    33663\n",
       "Name: FlightNum, Length: 3712, dtype: int64"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#routes.groupby('TailNum').FlightNum.count()\n",
    "routes.groupby('TailNum')['FlightNum'].count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "I have intentionaly mixed syntax. Dataframes in both R and Python use two form of syntax interchangeably.\n",
    "  * <code>dataframe.columnName</code>\n",
    "  * <code>dataframe['columnName']\n",
    "\n",
    "Many functions only accept the bracketed indexing of columns.\n",
    "    \n",
    "__Query:__ How many times was each flight flown?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "FlightNum\n",
       "1       3215\n",
       "2          0\n",
       "3       3744\n",
       "4          0\n",
       "5       3218\n",
       "        ... \n",
       "9607       1\n",
       "9608       1\n",
       "9777       2\n",
       "9851       0\n",
       "9899       2\n",
       "Name: TailNum, Length: 2405, dtype: int64"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "routes.groupby('FlightNum')['TailNum'].count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__Query:__ What were the most routes flown by a single plane?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "33663"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "routes.groupby('TailNum')['FlightNum'].count().max()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__Query:__ What is the maximum number of planes to fly a single route?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "6343"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "routes.groupby('FlightNum')['TailNum'].count().max()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "But, these aggregates are not really the questions we want answered. More natural questions are awkward.\n",
    "  * What plane flew the most routes?\n",
    "  * What route was flown by the most planes?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "305"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "routes.groupby('FlightNum').TailNum.count().idxmax()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'UNKNOW'"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "routes.groupby('TailNum').FlightNum.count().idxmax()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This reveals problems with the data.  So, let's look for an actual plane."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'N413DA'"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "routes[routes.TailNum != 'UNKNOW'].groupby('TailNum').FlightNum.count().idxmax()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "and ask for how many flights it has flown"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4844"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "routes[routes.TailNum != 'UNKNOW'].groupby('TailNum').FlightNum.count().max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pandas.core.frame.DataFrame"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(routes)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "and verify that this is the right answer."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4844"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "routecount = routes.groupby('TailNum').FlightNum.count()\n",
    "routecount.get('N413DA')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pandas.core.series.Series"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(routecount)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We have uncovered what I think is the most annoying part of dask and pandas dataframes. Aggregate functions return series, which are not dataframes. They have different methods.  I would have preferred to have written:\n",
    "\n",
    "<code>routecount[routecount.TailNum=='N413DA'].compute()</code>\n",
    "\n",
    "But, that's dataframe syntax not series syntax.  Aggregates assume that the output is small and return pandas series."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pandas.core.series.Series"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(routecount)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Indexes\n",
    "\n",
    "All dataframes have a __default index__.  In this case, the index was generated when we loaded the data and is the row number in the pandas dataframe. Surprisingly, the index is not unique.  The same index value appears in each pandas dataframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Dask Index Structure:\n",
      "npartitions=10\n",
      "    int64\n",
      "      ...\n",
      "    ...  \n",
      "      ...\n",
      "      ...\n",
      "dtype: int64\n",
      "Dask Name: read-csv, 20 tasks \n",
      "\n",
      "Number of rows in the database\n",
      " 2611892\n",
      "Number of unique values in the index\n",
      " 271539\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Date</th>\n",
       "      <th>DayOfWeek</th>\n",
       "      <th>DepTime</th>\n",
       "      <th>CRSDepTime</th>\n",
       "      <th>ArrTime</th>\n",
       "      <th>CRSArrTime</th>\n",
       "      <th>UniqueCarrier</th>\n",
       "      <th>FlightNum</th>\n",
       "      <th>TailNum</th>\n",
       "      <th>ActualElapsedTime</th>\n",
       "      <th>...</th>\n",
       "      <th>AirTime</th>\n",
       "      <th>ArrDelay</th>\n",
       "      <th>DepDelay</th>\n",
       "      <th>Origin</th>\n",
       "      <th>Dest</th>\n",
       "      <th>Distance</th>\n",
       "      <th>TaxiIn</th>\n",
       "      <th>TaxiOut</th>\n",
       "      <th>Cancelled</th>\n",
       "      <th>Diverted</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>22000</th>\n",
       "      <td>1990-01-17</td>\n",
       "      <td>3</td>\n",
       "      <td>1849.0</td>\n",
       "      <td>1850</td>\n",
       "      <td>1946.0</td>\n",
       "      <td>1945</td>\n",
       "      <td>CO</td>\n",
       "      <td>828</td>\n",
       "      <td>NaN</td>\n",
       "      <td>57.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>EWR</td>\n",
       "      <td>BDL</td>\n",
       "      <td>116.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22000</th>\n",
       "      <td>1991-02-28</td>\n",
       "      <td>4</td>\n",
       "      <td>2042.0</td>\n",
       "      <td>2040</td>\n",
       "      <td>2154.0</td>\n",
       "      <td>2156</td>\n",
       "      <td>US</td>\n",
       "      <td>458</td>\n",
       "      <td>NaN</td>\n",
       "      <td>72.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>LGA</td>\n",
       "      <td>BUF</td>\n",
       "      <td>292.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22000</th>\n",
       "      <td>1992-02-23</td>\n",
       "      <td>7</td>\n",
       "      <td>1302.0</td>\n",
       "      <td>1305</td>\n",
       "      <td>1419.0</td>\n",
       "      <td>1416</td>\n",
       "      <td>US</td>\n",
       "      <td>132</td>\n",
       "      <td>NaN</td>\n",
       "      <td>77.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>-3.0</td>\n",
       "      <td>EWR</td>\n",
       "      <td>BUF</td>\n",
       "      <td>282.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22000</th>\n",
       "      <td>1993-02-12</td>\n",
       "      <td>5</td>\n",
       "      <td>2258.0</td>\n",
       "      <td>2029</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2210</td>\n",
       "      <td>US</td>\n",
       "      <td>117</td>\n",
       "      <td>NaN</td>\n",
       "      <td>67.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>115.0</td>\n",
       "      <td>149.0</td>\n",
       "      <td>LGA</td>\n",
       "      <td>CLE</td>\n",
       "      <td>418.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22000</th>\n",
       "      <td>1994-02-25</td>\n",
       "      <td>5</td>\n",
       "      <td>712.0</td>\n",
       "      <td>700</td>\n",
       "      <td>806.0</td>\n",
       "      <td>753</td>\n",
       "      <td>US</td>\n",
       "      <td>419</td>\n",
       "      <td>NaN</td>\n",
       "      <td>54.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>13.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>LGA</td>\n",
       "      <td>PHL</td>\n",
       "      <td>96.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22000</th>\n",
       "      <td>1995-01-31</td>\n",
       "      <td>2</td>\n",
       "      <td>1825.0</td>\n",
       "      <td>1800</td>\n",
       "      <td>1943.0</td>\n",
       "      <td>1920</td>\n",
       "      <td>CO</td>\n",
       "      <td>323</td>\n",
       "      <td>N83872</td>\n",
       "      <td>78.0</td>\n",
       "      <td>...</td>\n",
       "      <td>37.0</td>\n",
       "      <td>23.0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>EWR</td>\n",
       "      <td>DCA</td>\n",
       "      <td>199.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>38.0</td>\n",
       "      <td>False</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22000</th>\n",
       "      <td>1996-02-07</td>\n",
       "      <td>3</td>\n",
       "      <td>1216.0</td>\n",
       "      <td>1215</td>\n",
       "      <td>1343.0</td>\n",
       "      <td>1338</td>\n",
       "      <td>US</td>\n",
       "      <td>1419</td>\n",
       "      <td>N437US</td>\n",
       "      <td>87.0</td>\n",
       "      <td>...</td>\n",
       "      <td>65.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>LGA</td>\n",
       "      <td>PIT</td>\n",
       "      <td>335.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>False</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22000</th>\n",
       "      <td>1997-01-31</td>\n",
       "      <td>5</td>\n",
       "      <td>747.0</td>\n",
       "      <td>745</td>\n",
       "      <td>1026.0</td>\n",
       "      <td>1003</td>\n",
       "      <td>CO</td>\n",
       "      <td>133</td>\n",
       "      <td>N17321</td>\n",
       "      <td>279.0</td>\n",
       "      <td>...</td>\n",
       "      <td>239.0</td>\n",
       "      <td>23.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>EWR</td>\n",
       "      <td>DEN</td>\n",
       "      <td>1605.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>33.0</td>\n",
       "      <td>False</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22000</th>\n",
       "      <td>1998-02-19</td>\n",
       "      <td>4</td>\n",
       "      <td>2031.0</td>\n",
       "      <td>2025</td>\n",
       "      <td>2146.0</td>\n",
       "      <td>2151</td>\n",
       "      <td>CO</td>\n",
       "      <td>1978</td>\n",
       "      <td>N14245</td>\n",
       "      <td>75.0</td>\n",
       "      <td>...</td>\n",
       "      <td>44.0</td>\n",
       "      <td>-5.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>EWR</td>\n",
       "      <td>PWM</td>\n",
       "      <td>284.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>False</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22000</th>\n",
       "      <td>1999-02-17</td>\n",
       "      <td>3</td>\n",
       "      <td>1511.0</td>\n",
       "      <td>1450</td>\n",
       "      <td>1830.0</td>\n",
       "      <td>1820</td>\n",
       "      <td>HP</td>\n",
       "      <td>2035</td>\n",
       "      <td>N645AW</td>\n",
       "      <td>319.0</td>\n",
       "      <td>...</td>\n",
       "      <td>290.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>EWR</td>\n",
       "      <td>PHX</td>\n",
       "      <td>2133.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>False</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            Date  DayOfWeek  DepTime  CRSDepTime  ArrTime  CRSArrTime  \\\n",
       "22000 1990-01-17          3   1849.0        1850   1946.0        1945   \n",
       "22000 1991-02-28          4   2042.0        2040   2154.0        2156   \n",
       "22000 1992-02-23          7   1302.0        1305   1419.0        1416   \n",
       "22000 1993-02-12          5   2258.0        2029      5.0        2210   \n",
       "22000 1994-02-25          5    712.0         700    806.0         753   \n",
       "22000 1995-01-31          2   1825.0        1800   1943.0        1920   \n",
       "22000 1996-02-07          3   1216.0        1215   1343.0        1338   \n",
       "22000 1997-01-31          5    747.0         745   1026.0        1003   \n",
       "22000 1998-02-19          4   2031.0        2025   2146.0        2151   \n",
       "22000 1999-02-17          3   1511.0        1450   1830.0        1820   \n",
       "\n",
       "      UniqueCarrier  FlightNum TailNum  ActualElapsedTime  ...  AirTime  \\\n",
       "22000            CO        828     NaN               57.0  ...      NaN   \n",
       "22000            US        458     NaN               72.0  ...      NaN   \n",
       "22000            US        132     NaN               77.0  ...      NaN   \n",
       "22000            US        117     NaN               67.0  ...      NaN   \n",
       "22000            US        419     NaN               54.0  ...      NaN   \n",
       "22000            CO        323  N83872               78.0  ...     37.0   \n",
       "22000            US       1419  N437US               87.0  ...     65.0   \n",
       "22000            CO        133  N17321              279.0  ...    239.0   \n",
       "22000            CO       1978  N14245               75.0  ...     44.0   \n",
       "22000            HP       2035  N645AW              319.0  ...    290.0   \n",
       "\n",
       "       ArrDelay  DepDelay  Origin Dest Distance  TaxiIn  TaxiOut  Cancelled  \\\n",
       "22000       1.0      -1.0     EWR  BDL    116.0     NaN      NaN      False   \n",
       "22000      -2.0       2.0     LGA  BUF    292.0     NaN      NaN      False   \n",
       "22000       3.0      -3.0     EWR  BUF    282.0     NaN      NaN      False   \n",
       "22000     115.0     149.0     LGA  CLE    418.0     NaN      NaN      False   \n",
       "22000      13.0      12.0     LGA  PHL     96.0     NaN      NaN      False   \n",
       "22000      23.0      25.0     EWR  DCA    199.0     3.0     38.0      False   \n",
       "22000       5.0       1.0     LGA  PIT    335.0     9.0     13.0      False   \n",
       "22000      23.0       2.0     EWR  DEN   1605.0     7.0     33.0      False   \n",
       "22000      -5.0       6.0     EWR  PWM    284.0     3.0     28.0      False   \n",
       "22000      10.0      21.0     EWR  PHX   2133.0     4.0     25.0      False   \n",
       "\n",
       "       Diverted  \n",
       "22000         0  \n",
       "22000         0  \n",
       "22000         0  \n",
       "22000         0  \n",
       "22000         0  \n",
       "22000         0  \n",
       "22000         0  \n",
       "22000         0  \n",
       "22000         0  \n",
       "22000         0  \n",
       "\n",
       "[10 rows x 21 columns]"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(df.index, \"\\n\")\n",
    "print(\"Number of rows in the database\\n\", len(df))\n",
    "maxindex = df.index.nunique().compute()\n",
    "print(\"Number of unique values in the index\\n\", maxindex)\n",
    "\n",
    "# find all entries with index value 22000\n",
    "df.loc[22000].compute()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "RangeIndex(start=0, stop=5, step=1)"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.index.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The term index is used to mean many things in computer science:\n",
    "* the offset of an element in a data structure, i.e. the array index\n",
    "* a data structure used for fast access in a data store\n",
    "\n",
    "When I heard index in dask, I expected it to be the latter. It is not. By default, it is only the row number in the local pandas data frame.\n",
    "\n",
    "\n",
    "My concept of an index comes from relational databases: \"A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and storage space to maintain the index data structure.\"\n",
    "\n",
    "Real indexes come in many forms, but are most commonly:\n",
    "  * hash tables -- organize the data by the hash value of a key field for constant time $O(1)$ lookup by key.\n",
    "  * B+-trees/sorted -- sort the data in a tree to lookup a key in $O(\\log n)$ time and be able to scan sequential keys.\n",
    "\n",
    "A blog from TimesTen gives a reasonable schematic.\n",
    "\n",
    "<img src=\"https://media.licdn.com/dms/image/C5612AQHf5yFVqdFiUA/article-inline_image-shrink_1000_1488/0/1520212409838?e=1672876800&v=beta&t=84a4_OoiJpVjatT92ds8rL6ERWTWJCjEk5q1onLsvgQ\" width=\"768\" title=\"https://media.licdn.com/dms/image/C5612AQHf5yFVqdFiUA/article-inline_image-shrink_1000_1488/0/1520212409838?e=1672876800&v=beta&t=84a4_OoiJpVjatT92ds8rL6ERWTWJCjEk5q1onLsvgQ\" />\n",
    "\n",
    "This is not really a topic for this class. Although, indexing is one of my favorite parts of computer science.\n",
    "\n",
    "A simulacrum of indexing can be accomplished in dask by calling <code>set_index</code>, which often results in a global shuffle of the data.  It can be very expensive. After an index is set, all of the data are sorted (across all partitions). This makes it much more efficient to conduct some database like operations, `join()`, `groupby()`, etc.\n",
    "\n",
    "The data that we have is already sorted by date, so setting the index to that value does not take a long time. \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Dask Index Structure:\n",
       "npartitions=10\n",
       "    int64\n",
       "      ...\n",
       "    ...  \n",
       "      ...\n",
       "      ...\n",
       "dtype: int64\n",
       "Dask Name: read-csv, 20 tasks"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 7.62 s, sys: 1.01 s, total: 8.63 s\n",
      "Wall time: 3.51 s\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div><strong>Dask DataFrame Structure:</strong></div>\n",
       "<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>DayOfWeek</th>\n",
       "      <th>DepTime</th>\n",
       "      <th>CRSDepTime</th>\n",
       "      <th>ArrTime</th>\n",
       "      <th>CRSArrTime</th>\n",
       "      <th>UniqueCarrier</th>\n",
       "      <th>FlightNum</th>\n",
       "      <th>TailNum</th>\n",
       "      <th>ActualElapsedTime</th>\n",
       "      <th>CRSElapsedTime</th>\n",
       "      <th>AirTime</th>\n",
       "      <th>ArrDelay</th>\n",
       "      <th>DepDelay</th>\n",
       "      <th>Origin</th>\n",
       "      <th>Dest</th>\n",
       "      <th>Distance</th>\n",
       "      <th>TaxiIn</th>\n",
       "      <th>TaxiOut</th>\n",
       "      <th>Cancelled</th>\n",
       "      <th>Diverted</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>npartitions=10</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1990-01-01</th>\n",
       "      <td>int64</td>\n",
       "      <td>float64</td>\n",
       "      <td>int64</td>\n",
       "      <td>float64</td>\n",
       "      <td>int64</td>\n",
       "      <td>object</td>\n",
       "      <td>int64</td>\n",
       "      <td>object</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>object</td>\n",
       "      <td>object</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>bool</td>\n",
       "      <td>int64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1991-01-01</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",
       "      <td>...</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",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1999-01-01</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",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1999-12-31</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",
       "      <td>...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "<div>Dask Name: sort_index, 30 tasks</div>"
      ],
      "text/plain": [
       "Dask DataFrame Structure:\n",
       "               DayOfWeek  DepTime CRSDepTime  ArrTime CRSArrTime UniqueCarrier FlightNum TailNum ActualElapsedTime CRSElapsedTime  AirTime ArrDelay DepDelay  Origin    Dest Distance   TaxiIn  TaxiOut Cancelled Diverted\n",
       "npartitions=10                                                                                                                                                                                                            \n",
       "1990-01-01         int64  float64      int64  float64      int64        object     int64  object           float64        float64  float64  float64  float64  object  object  float64  float64  float64      bool    int64\n",
       "1991-01-01           ...      ...        ...      ...        ...           ...       ...     ...               ...            ...      ...      ...      ...     ...     ...      ...      ...      ...       ...      ...\n",
       "...                  ...      ...        ...      ...        ...           ...       ...     ...               ...            ...      ...      ...      ...     ...     ...      ...      ...      ...       ...      ...\n",
       "1999-01-01           ...      ...        ...      ...        ...           ...       ...     ...               ...            ...      ...      ...      ...     ...     ...      ...      ...      ...       ...      ...\n",
       "1999-12-31           ...      ...        ...      ...        ...           ...       ...     ...               ...            ...      ...      ...      ...     ...     ...      ...      ...      ...       ...      ...\n",
       "Dask Name: sort_index, 30 tasks"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%time df.set_index('Date')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><strong>Dask DataFrame Structure:</strong></div>\n",
       "<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>DayOfWeek</th>\n",
       "      <th>DepTime</th>\n",
       "      <th>CRSDepTime</th>\n",
       "      <th>ArrTime</th>\n",
       "      <th>CRSArrTime</th>\n",
       "      <th>UniqueCarrier</th>\n",
       "      <th>FlightNum</th>\n",
       "      <th>TailNum</th>\n",
       "      <th>ActualElapsedTime</th>\n",
       "      <th>CRSElapsedTime</th>\n",
       "      <th>AirTime</th>\n",
       "      <th>ArrDelay</th>\n",
       "      <th>DepDelay</th>\n",
       "      <th>Origin</th>\n",
       "      <th>Dest</th>\n",
       "      <th>Distance</th>\n",
       "      <th>TaxiIn</th>\n",
       "      <th>TaxiOut</th>\n",
       "      <th>Cancelled</th>\n",
       "      <th>Diverted</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>npartitions=10</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <td>datetime64[ns]</td>\n",
       "      <td>int64</td>\n",
       "      <td>float64</td>\n",
       "      <td>int64</td>\n",
       "      <td>float64</td>\n",
       "      <td>int64</td>\n",
       "      <td>object</td>\n",
       "      <td>int64</td>\n",
       "      <td>object</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>object</td>\n",
       "      <td>object</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>bool</td>\n",
       "      <td>int64</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",
       "      <td>...</td>\n",
       "      <td>...</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",
       "      <td>...</td>\n",
       "      <td>...</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",
       "      <td>...</td>\n",
       "      <td>...</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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "<div>Dask Name: read-csv, 10 tasks</div>"
      ],
      "text/plain": [
       "Dask DataFrame Structure:\n",
       "                          Date DayOfWeek  DepTime CRSDepTime  ArrTime CRSArrTime UniqueCarrier FlightNum TailNum ActualElapsedTime CRSElapsedTime  AirTime ArrDelay DepDelay  Origin    Dest Distance   TaxiIn  TaxiOut Cancelled Diverted\n",
       "npartitions=10                                                                                                                                                                                                                            \n",
       "                datetime64[ns]     int64  float64      int64  float64      int64        object     int64  object           float64        float64  float64  float64  float64  object  object  float64  float64  float64      bool    int64\n",
       "                           ...       ...      ...        ...      ...        ...           ...       ...     ...               ...            ...      ...      ...      ...     ...     ...      ...      ...      ...       ...      ...\n",
       "...                        ...       ...      ...        ...      ...        ...           ...       ...     ...               ...            ...      ...      ...      ...     ...     ...      ...      ...      ...       ...      ...\n",
       "                           ...       ...      ...        ...      ...        ...           ...       ...     ...               ...            ...      ...      ...      ...     ...     ...      ...      ...      ...       ...      ...\n",
       "                           ...       ...      ...        ...      ...        ...           ...       ...     ...               ...            ...      ...      ...      ...     ...     ...      ...      ...      ...       ...      ...\n",
       "Dask Name: read-csv, 10 tasks"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Dask has implemented some what I would expect out of indexing:\n",
    "1. dask can be told that there is existing structure in the data\n",
    "2. dask reorganizes data to optimize queries\n",
    "\n",
    "Dask does not support some features that I think it should\n",
    "1. hash indexing\n",
    "2. data readers infer the existence of an index"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Why is this a simulacrum of an index, and not a real database structure. Because it only sorts the data, if does not build an additional data structure for fast access. The improved performance only comes from the sortedness of the data, e.g.:\n",
    "* find an element using the index field can use binary search, rather than a scan\n",
    "* joining data on the index field can be done using a \"two-finger join\" in a linear scan of two tables without sorting (data are presorted)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## RB this doesn't work the way I want. I will try to fix and present later.\n",
    "\n",
    "\n",
    "### Why do indexes matter?\n",
    "\n",
    "Understanding the structure of the can lead to massive performance differences.\n",
    "We can create an example in which seemingly identical queries perform \n",
    "differently because of an implicit index structure.\n",
    "\n",
    "We create 100 files each with 100000 entries with two fields 'A' and 'B'.  'A' contains an integer that identifies the file number (0-99). 'A' is the same in each file. 'B' contains a sequence of numbers 0-1000000 in each file. These data:\n",
    "  * are too big to fit in memory\n",
    "  * have one field that will be identical in each pandas dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "ename": "IndexError",
     "evalue": "list index out of range",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mIndexError\u001b[0m                                Traceback (most recent call last)",
      "Input \u001b[0;32mIn [23]\u001b[0m, in \u001b[0;36m<cell line: 1>\u001b[0;34m()\u001b[0m\n\u001b[0;32m----> 1\u001b[0m df \u001b[38;5;241m=\u001b[39m \u001b[43mdd\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mread_csv\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43m/tmp/csv*.csv\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[43m)\u001b[49m\n\u001b[1;32m      2\u001b[0m df\u001b[38;5;241m.\u001b[39mhead\n\u001b[1;32m      3\u001b[0m df\u001b[38;5;241m.\u001b[39mcompute()\n",
      "File \u001b[0;32m~/opt/anaconda3/envs/pp/lib/python3.10/site-packages/dask/dataframe/io/csv.py:742\u001b[0m, in \u001b[0;36mmake_reader.<locals>.read\u001b[0;34m(urlpath, blocksize, lineterminator, compression, sample, sample_rows, enforce, assume_missing, storage_options, include_path_column, **kwargs)\u001b[0m\n\u001b[1;32m    729\u001b[0m \u001b[38;5;28;01mdef\u001b[39;00m \u001b[38;5;21mread\u001b[39m(\n\u001b[1;32m    730\u001b[0m     urlpath,\n\u001b[1;32m    731\u001b[0m     blocksize\u001b[38;5;241m=\u001b[39m\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mdefault\u001b[39m\u001b[38;5;124m\"\u001b[39m,\n\u001b[0;32m   (...)\u001b[0m\n\u001b[1;32m    740\u001b[0m     \u001b[38;5;241m*\u001b[39m\u001b[38;5;241m*\u001b[39mkwargs,\n\u001b[1;32m    741\u001b[0m ):\n\u001b[0;32m--> 742\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[43mread_pandas\u001b[49m\u001b[43m(\u001b[49m\n\u001b[1;32m    743\u001b[0m \u001b[43m        \u001b[49m\u001b[43mreader\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    744\u001b[0m \u001b[43m        \u001b[49m\u001b[43murlpath\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    745\u001b[0m \u001b[43m        \u001b[49m\u001b[43mblocksize\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mblocksize\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    746\u001b[0m \u001b[43m        \u001b[49m\u001b[43mlineterminator\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mlineterminator\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    747\u001b[0m \u001b[43m        \u001b[49m\u001b[43mcompression\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mcompression\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    748\u001b[0m \u001b[43m        \u001b[49m\u001b[43msample\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43msample\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    749\u001b[0m \u001b[43m        \u001b[49m\u001b[43msample_rows\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43msample_rows\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    750\u001b[0m \u001b[43m        \u001b[49m\u001b[43menforce\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43menforce\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    751\u001b[0m \u001b[43m        \u001b[49m\u001b[43massume_missing\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43massume_missing\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    752\u001b[0m \u001b[43m        \u001b[49m\u001b[43mstorage_options\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mstorage_options\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    753\u001b[0m \u001b[43m        \u001b[49m\u001b[43minclude_path_column\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43minclude_path_column\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    754\u001b[0m \u001b[43m        \u001b[49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[43mkwargs\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    755\u001b[0m \u001b[43m    \u001b[49m\u001b[43m)\u001b[49m\n",
      "File \u001b[0;32m~/opt/anaconda3/envs/pp/lib/python3.10/site-packages/dask/dataframe/io/csv.py:521\u001b[0m, in \u001b[0;36mread_pandas\u001b[0;34m(reader, urlpath, blocksize, lineterminator, compression, sample, sample_rows, enforce, assume_missing, storage_options, include_path_column, **kwargs)\u001b[0m\n\u001b[1;32m    516\u001b[0m     paths \u001b[38;5;241m=\u001b[39m get_fs_token_paths(urlpath, mode\u001b[38;5;241m=\u001b[39m\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mrb\u001b[39m\u001b[38;5;124m\"\u001b[39m, storage_options\u001b[38;5;241m=\u001b[39mstorage_options)[\n\u001b[1;32m    517\u001b[0m         \u001b[38;5;241m2\u001b[39m\n\u001b[1;32m    518\u001b[0m     ]\n\u001b[1;32m    520\u001b[0m     \u001b[38;5;66;03m# Infer compression from first path\u001b[39;00m\n\u001b[0;32m--> 521\u001b[0m     compression \u001b[38;5;241m=\u001b[39m infer_compression(\u001b[43mpaths\u001b[49m\u001b[43m[\u001b[49m\u001b[38;5;241;43m0\u001b[39;49m\u001b[43m]\u001b[49m)\n\u001b[1;32m    523\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m blocksize \u001b[38;5;241m==\u001b[39m \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mdefault\u001b[39m\u001b[38;5;124m\"\u001b[39m:\n\u001b[1;32m    524\u001b[0m     blocksize \u001b[38;5;241m=\u001b[39m AUTO_BLOCKSIZE\n",
      "\u001b[0;31mIndexError\u001b[0m: list index out of range"
     ]
    }
   ],
   "source": [
    "df = dd.read_csv('/tmp/csv*.csv')\n",
    "df.head\n",
    "df.compute()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now let's compare how long it takes to sum all the elements grouped by each key for both columns.  In both cases, we touch all of the data.  But, the sums in when we <code>groupby</code> 'A' can all be evaluated in one chunk.  When we <code>groupby</code> 'B', partial sums at each value must be aggregated across all chunks."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 20.9 s, sys: 2.78 s, total: 23.7 s\n",
      "Wall time: 6.76 s\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>B</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>A</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>124999750000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>124999750000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>124999750000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>124999750000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>124999750000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>124999750000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>124999750000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>124999750000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>124999750000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>124999750000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>100 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               B\n",
       "A               \n",
       "0   124999750000\n",
       "1   124999750000\n",
       "10  124999750000\n",
       "11  124999750000\n",
       "12  124999750000\n",
       "..           ...\n",
       "95  124999750000\n",
       "96  124999750000\n",
       "97  124999750000\n",
       "98  124999750000\n",
       "99  124999750000\n",
       "\n",
       "[100 rows x 1 columns]"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%time df.groupby('A').sum().compute()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 7.99 s, sys: 1.49 s, total: 9.48 s\n",
      "Wall time: 1.66 s\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4950</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4950</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4950</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4950</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4950</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>499995</th>\n",
       "      <td>4950</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>499996</th>\n",
       "      <td>4950</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>499997</th>\n",
       "      <td>4950</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>499998</th>\n",
       "      <td>4950</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>499999</th>\n",
       "      <td>4950</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>500000 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           A\n",
       "B           \n",
       "0       4950\n",
       "1       4950\n",
       "2       4950\n",
       "3       4950\n",
       "4       4950\n",
       "...      ...\n",
       "499995  4950\n",
       "499996  4950\n",
       "499997  4950\n",
       "499998  4950\n",
       "499999  4950\n",
       "\n",
       "[500000 rows x 1 columns]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# RB for this to run you must create data at bottom of file.\n",
    "df = dd.read_csv('/tmp/csv*.csv')\n",
    "%time df.groupby('B').sum().compute()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A couple of observations:\n",
    "- Dask is parallelizing the computation across multiple nodes.\n",
    "  - notice the difference between wall time and CPU times.\n",
    "- this pattern is much less efficient\n",
    "  - it has interference between different parallel chunks\n",
    "  - sending the data from node to node\n",
    "  \n",
    "Now setting the index and compare:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 4.87 s, sys: 1.65 s, total: 6.52 s\n",
      "Wall time: 1.22 s\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>B</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>A</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>124999750000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>124999750000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>124999750000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>124999750000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>124999750000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>124999750000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>124999750000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>124999750000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>124999750000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>124999750000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>100 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               B\n",
       "A               \n",
       "0   124999750000\n",
       "1   124999750000\n",
       "10  124999750000\n",
       "11  124999750000\n",
       "12  124999750000\n",
       "..           ...\n",
       "95  124999750000\n",
       "96  124999750000\n",
       "97  124999750000\n",
       "98  124999750000\n",
       "99  124999750000\n",
       "\n",
       "[100 rows x 1 columns]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = dd.read_csv('/tmp/csv*.csv')\n",
    "df.set_index('A')\n",
    "%time df.groupby('A').sum().compute()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 8.04 s, sys: 1.77 s, total: 9.81 s\n",
      "Wall time: 1.68 s\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4950</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4950</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4950</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4950</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4950</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>499995</th>\n",
       "      <td>4950</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>499996</th>\n",
       "      <td>4950</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>499997</th>\n",
       "      <td>4950</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>499998</th>\n",
       "      <td>4950</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>499999</th>\n",
       "      <td>4950</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>500000 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           A\n",
       "B           \n",
       "0       4950\n",
       "1       4950\n",
       "2       4950\n",
       "3       4950\n",
       "4       4950\n",
       "...      ...\n",
       "499995  4950\n",
       "499996  4950\n",
       "499997  4950\n",
       "499998  4950\n",
       "499999  4950\n",
       "\n",
       "[500000 rows x 1 columns]"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = dd.read_csv('/tmp/csv*.csv')\n",
    "df.set_index('B')\n",
    "%time df.groupby('B').sum().compute()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So, it turns out the the index ends up being not that helpful. It doesn't localize any computation in this specific example, i.e. both versions end up using the same amount of data.\n",
    "\n",
    "### This doesn't work the way I want.  Warning.\n",
    "\n",
    "I tried to get the index to accelerate computation. But, I can't get it to work and I can't get Dask to reshuffle the data. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = dd.read_csv('./data/nycflight/*.csv',\n",
    "                 parse_dates={'Date': [0, 1, 2]},\n",
    "                 dtype={'TailNum': str,\n",
    "                        'CRSElapsedTime': float,\n",
    "                        'Cancelled': bool})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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>DayOfWeek</th>\n",
       "      <th>DepTime</th>\n",
       "      <th>CRSDepTime</th>\n",
       "      <th>ArrTime</th>\n",
       "      <th>CRSArrTime</th>\n",
       "      <th>UniqueCarrier</th>\n",
       "      <th>FlightNum</th>\n",
       "      <th>TailNum</th>\n",
       "      <th>ActualElapsedTime</th>\n",
       "      <th>...</th>\n",
       "      <th>AirTime</th>\n",
       "      <th>ArrDelay</th>\n",
       "      <th>DepDelay</th>\n",
       "      <th>Origin</th>\n",
       "      <th>Dest</th>\n",
       "      <th>Distance</th>\n",
       "      <th>TaxiIn</th>\n",
       "      <th>TaxiOut</th>\n",
       "      <th>Cancelled</th>\n",
       "      <th>Diverted</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>84646</th>\n",
       "      <td>1994-05-21</td>\n",
       "      <td>6</td>\n",
       "      <td>1009.0</td>\n",
       "      <td>1000</td>\n",
       "      <td>1240.0</td>\n",
       "      <td>1240</td>\n",
       "      <td>US</td>\n",
       "      <td>259</td>\n",
       "      <td>NaN</td>\n",
       "      <td>151.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>LGA</td>\n",
       "      <td>MCO</td>\n",
       "      <td>950.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84667</th>\n",
       "      <td>1994-05-21</td>\n",
       "      <td>6</td>\n",
       "      <td>1002.0</td>\n",
       "      <td>1000</td>\n",
       "      <td>1117.0</td>\n",
       "      <td>1125</td>\n",
       "      <td>US</td>\n",
       "      <td>261</td>\n",
       "      <td>NaN</td>\n",
       "      <td>75.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-8.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>LGA</td>\n",
       "      <td>PIT</td>\n",
       "      <td>335.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84693</th>\n",
       "      <td>1994-05-21</td>\n",
       "      <td>6</td>\n",
       "      <td>908.0</td>\n",
       "      <td>900</td>\n",
       "      <td>1211.0</td>\n",
       "      <td>1204</td>\n",
       "      <td>US</td>\n",
       "      <td>263</td>\n",
       "      <td>NaN</td>\n",
       "      <td>183.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>LGA</td>\n",
       "      <td>MIA</td>\n",
       "      <td>1097.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84724</th>\n",
       "      <td>1994-05-21</td>\n",
       "      <td>6</td>\n",
       "      <td>1400.0</td>\n",
       "      <td>1400</td>\n",
       "      <td>1455.0</td>\n",
       "      <td>1453</td>\n",
       "      <td>US</td>\n",
       "      <td>268</td>\n",
       "      <td>NaN</td>\n",
       "      <td>55.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>LGA</td>\n",
       "      <td>BWI</td>\n",
       "      <td>185.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84751</th>\n",
       "      <td>1994-05-21</td>\n",
       "      <td>6</td>\n",
       "      <td>921.0</td>\n",
       "      <td>920</td>\n",
       "      <td>1049.0</td>\n",
       "      <td>1104</td>\n",
       "      <td>US</td>\n",
       "      <td>273</td>\n",
       "      <td>NaN</td>\n",
       "      <td>88.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-15.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>LGA</td>\n",
       "      <td>CMH</td>\n",
       "      <td>478.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</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",
       "      <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>106922</th>\n",
       "      <td>1994-05-21</td>\n",
       "      <td>6</td>\n",
       "      <td>1201.0</td>\n",
       "      <td>1200</td>\n",
       "      <td>1309.0</td>\n",
       "      <td>1318</td>\n",
       "      <td>CO</td>\n",
       "      <td>1691</td>\n",
       "      <td>NaN</td>\n",
       "      <td>68.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-9.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>EWR</td>\n",
       "      <td>ORF</td>\n",
       "      <td>284.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106952</th>\n",
       "      <td>1994-05-21</td>\n",
       "      <td>6</td>\n",
       "      <td>1426.0</td>\n",
       "      <td>1430</td>\n",
       "      <td>1518.0</td>\n",
       "      <td>1530</td>\n",
       "      <td>CO</td>\n",
       "      <td>1692</td>\n",
       "      <td>NaN</td>\n",
       "      <td>52.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-12.0</td>\n",
       "      <td>-4.0</td>\n",
       "      <td>EWR</td>\n",
       "      <td>PVD</td>\n",
       "      <td>160.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>107009</th>\n",
       "      <td>1994-05-21</td>\n",
       "      <td>6</td>\n",
       "      <td>1900.0</td>\n",
       "      <td>1850</td>\n",
       "      <td>1958.0</td>\n",
       "      <td>2010</td>\n",
       "      <td>CO</td>\n",
       "      <td>1697</td>\n",
       "      <td>NaN</td>\n",
       "      <td>58.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-12.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>EWR</td>\n",
       "      <td>ORF</td>\n",
       "      <td>284.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>107063</th>\n",
       "      <td>1994-05-21</td>\n",
       "      <td>6</td>\n",
       "      <td>1257.0</td>\n",
       "      <td>1300</td>\n",
       "      <td>1350.0</td>\n",
       "      <td>1400</td>\n",
       "      <td>CO</td>\n",
       "      <td>1701</td>\n",
       "      <td>NaN</td>\n",
       "      <td>53.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-10.0</td>\n",
       "      <td>-3.0</td>\n",
       "      <td>EWR</td>\n",
       "      <td>BWI</td>\n",
       "      <td>169.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>107090</th>\n",
       "      <td>1994-05-21</td>\n",
       "      <td>6</td>\n",
       "      <td>1404.0</td>\n",
       "      <td>1410</td>\n",
       "      <td>1608.0</td>\n",
       "      <td>1635</td>\n",
       "      <td>CO</td>\n",
       "      <td>1719</td>\n",
       "      <td>NaN</td>\n",
       "      <td>124.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-27.0</td>\n",
       "      <td>-6.0</td>\n",
       "      <td>EWR</td>\n",
       "      <td>JAX</td>\n",
       "      <td>821.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>641 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             Date  DayOfWeek  DepTime  CRSDepTime  ArrTime  CRSArrTime  \\\n",
       "84646  1994-05-21          6   1009.0        1000   1240.0        1240   \n",
       "84667  1994-05-21          6   1002.0        1000   1117.0        1125   \n",
       "84693  1994-05-21          6    908.0         900   1211.0        1204   \n",
       "84724  1994-05-21          6   1400.0        1400   1455.0        1453   \n",
       "84751  1994-05-21          6    921.0         920   1049.0        1104   \n",
       "...           ...        ...      ...         ...      ...         ...   \n",
       "106922 1994-05-21          6   1201.0        1200   1309.0        1318   \n",
       "106952 1994-05-21          6   1426.0        1430   1518.0        1530   \n",
       "107009 1994-05-21          6   1900.0        1850   1958.0        2010   \n",
       "107063 1994-05-21          6   1257.0        1300   1350.0        1400   \n",
       "107090 1994-05-21          6   1404.0        1410   1608.0        1635   \n",
       "\n",
       "       UniqueCarrier  FlightNum TailNum  ActualElapsedTime  ...  AirTime  \\\n",
       "84646             US        259     NaN              151.0  ...      NaN   \n",
       "84667             US        261     NaN               75.0  ...      NaN   \n",
       "84693             US        263     NaN              183.0  ...      NaN   \n",
       "84724             US        268     NaN               55.0  ...      NaN   \n",
       "84751             US        273     NaN               88.0  ...      NaN   \n",
       "...              ...        ...     ...                ...  ...      ...   \n",
       "106922            CO       1691     NaN               68.0  ...      NaN   \n",
       "106952            CO       1692     NaN               52.0  ...      NaN   \n",
       "107009            CO       1697     NaN               58.0  ...      NaN   \n",
       "107063            CO       1701     NaN               53.0  ...      NaN   \n",
       "107090            CO       1719     NaN              124.0  ...      NaN   \n",
       "\n",
       "        ArrDelay  DepDelay  Origin Dest Distance  TaxiIn  TaxiOut  Cancelled  \\\n",
       "84646        0.0       9.0     LGA  MCO    950.0     NaN      NaN      False   \n",
       "84667       -8.0       2.0     LGA  PIT    335.0     NaN      NaN      False   \n",
       "84693        7.0       8.0     LGA  MIA   1097.0     NaN      NaN      False   \n",
       "84724        2.0       0.0     LGA  BWI    185.0     NaN      NaN      False   \n",
       "84751      -15.0       1.0     LGA  CMH    478.0     NaN      NaN      False   \n",
       "...          ...       ...     ...  ...      ...     ...      ...        ...   \n",
       "106922      -9.0       1.0     EWR  ORF    284.0     NaN      NaN      False   \n",
       "106952     -12.0      -4.0     EWR  PVD    160.0     NaN      NaN      False   \n",
       "107009     -12.0      10.0     EWR  ORF    284.0     NaN      NaN      False   \n",
       "107063     -10.0      -3.0     EWR  BWI    169.0     NaN      NaN      False   \n",
       "107090     -27.0      -6.0     EWR  JAX    821.0     NaN      NaN      False   \n",
       "\n",
       "        Diverted  \n",
       "84646          0  \n",
       "84667          0  \n",
       "84693          0  \n",
       "84724          0  \n",
       "84751          0  \n",
       "...          ...  \n",
       "106922         0  \n",
       "106952         0  \n",
       "107009         0  \n",
       "107063         0  \n",
       "107090         0  \n",
       "\n",
       "[641 rows x 21 columns]"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dayflights = df[df.Date=='1994-05-21'].compute()\n",
    "dayflights"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 3.83 s, sys: 640 ms, total: 4.47 s\n",
      "Wall time: 1.74 s\n"
     ]
    }
   ],
   "source": [
    "df = dd.read_csv('./data/nycflight/*.csv',\n",
    "                 parse_dates={'Date': [0, 1, 2]},\n",
    "                 dtype={'TailNum': str,\n",
    "                        'CRSElapsedTime': float,\n",
    "                        'Cancelled': bool})\n",
    "%time dayflights = df[df.Date=='1994-05-21'].compute()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 6.33 s, sys: 584 ms, total: 6.91 s\n",
      "Wall time: 3.73 s\n"
     ]
    }
   ],
   "source": [
    "df = dd.read_csv('./data/nycflight/*.csv',\n",
    "                 parse_dates={'Date': [0, 1, 2]},\n",
    "                 dtype={'TailNum': str,\n",
    "                        'CRSElapsedTime': float,\n",
    "                        'Cancelled': bool})\n",
    "df.set_index('Date')\n",
    "%time dayflights = df.loc['1994-05-21'].compute()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This are big performance differences that come from both:\n",
    "  * the organization of the data\n",
    "  * dask knowing about the organization of the data\n",
    "\n",
    "### Parting Thoughts\n",
    "\n",
    "Limitations and comments:\n",
    "  * dask does not have a general sort capability\n",
    "    * but, can be accomplished with set_index\n",
    "    * shuffle is inefficient, use a different engine\n",
    "  * dask does not support row indexing by phycial offset\n",
    "    * this is not an important feature"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Generate data\n",
    "\n",
    "Uncomment and run this once to make data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "import csv\n",
    "for i in range(100):\n",
    "    with open(f'/tmp/csv{i}.csv', 'w', newline='') as csvfile:\n",
    "        csvw = csv.writer(csvfile, delimiter=',', quotechar='\"', quoting=csv.QUOTE_MINIMAL)\n",
    "        csvw.writerow(['A','B'])\n",
    "        for j in range(500000):\n",
    "            csvw.writerow([i,j])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}