02-dataframe.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Dask DataFrame\n",
    "\n",
    "## Notebook Objectives\n",
    "* **Download NYC Yellow Taxi Cab Dataset for 2019**.\n",
    "* **Reading and working with tabular data using pandas**, a popular library for data analysis.\n",
    "* **Reading and working with tabular data using Dask DataFrame** - an interface to scale pandas code, and a look at **Dask Dashboards** for real-time visualization of the state of your cluster.\n",
    "* **Scaling Dask computation to the Cloud** using Coiled, a deployment-as-a-service library for scaling Python. (Optional)\n",
    "* **Limitations of Dask DataFrame**.\n",
    "* **References** for further reading."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Download NYC Yellow Taxi Cab Dataset for 2019\n",
    "\n",
    "A typical data science workflow starts with some data that needs to be understood. A typical first step is data cleaning and  exploratory analysis to find interesting details and patterns.\n",
    "\n",
    "In this notebook, we will be working with the [New York City Yellow Taxi Trips Dataset](https://www1.nyc.gov/site/tlc/about/tlc-trip-record-data.page) for 2019."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Reading and working with tabular data using **pandas**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Reading data\n",
    "\n",
    "pandas has a `read_csv` method to import data into your workspace. We use it to read the taxi data for January 2019.\n",
    "\n",
    "`%%time` is a [magic function](https://ipython.readthedocs.io/en/stable/interactive/magics.html) in IPython to compute the execution time of a Python expression.\n",
    "\n",
    "pandas reads data in the form of a 'dataframe' -- a structured format consisting of rows and column, along with some metadata about the values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 3.16 s, sys: 1.66 s, total: 4.82 s\n",
      "Wall time: 53.5 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>VendorID</th>\n",
       "      <th>tpep_pickup_datetime</th>\n",
       "      <th>tpep_dropoff_datetime</th>\n",
       "      <th>passenger_count</th>\n",
       "      <th>trip_distance</th>\n",
       "      <th>RatecodeID</th>\n",
       "      <th>store_and_fwd_flag</th>\n",
       "      <th>PULocationID</th>\n",
       "      <th>DOLocationID</th>\n",
       "      <th>payment_type</th>\n",
       "      <th>fare_amount</th>\n",
       "      <th>extra</th>\n",
       "      <th>mta_tax</th>\n",
       "      <th>tip_amount</th>\n",
       "      <th>tolls_amount</th>\n",
       "      <th>improvement_surcharge</th>\n",
       "      <th>total_amount</th>\n",
       "      <th>congestion_surcharge</th>\n",
       "      <th>airport_fee</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2019-01-01 00:46:40</td>\n",
       "      <td>2019-01-01 00:53:20</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.50</td>\n",
       "      <td>1.0</td>\n",
       "      <td>N</td>\n",
       "      <td>151</td>\n",
       "      <td>239</td>\n",
       "      <td>1</td>\n",
       "      <td>7.00</td>\n",
       "      <td>0.50</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1.65</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.3</td>\n",
       "      <td>9.95</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2019-01-01 00:59:47</td>\n",
       "      <td>2019-01-01 01:18:59</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.60</td>\n",
       "      <td>1.0</td>\n",
       "      <td>N</td>\n",
       "      <td>239</td>\n",
       "      <td>246</td>\n",
       "      <td>1</td>\n",
       "      <td>14.00</td>\n",
       "      <td>0.50</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.3</td>\n",
       "      <td>16.30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>2018-12-21 13:48:30</td>\n",
       "      <td>2018-12-21 13:52:40</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.0</td>\n",
       "      <td>N</td>\n",
       "      <td>236</td>\n",
       "      <td>236</td>\n",
       "      <td>1</td>\n",
       "      <td>4.50</td>\n",
       "      <td>0.50</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.3</td>\n",
       "      <td>5.80</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2</td>\n",
       "      <td>2018-11-28 15:52:25</td>\n",
       "      <td>2018-11-28 15:55:45</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.0</td>\n",
       "      <td>N</td>\n",
       "      <td>193</td>\n",
       "      <td>193</td>\n",
       "      <td>2</td>\n",
       "      <td>3.50</td>\n",
       "      <td>0.50</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.3</td>\n",
       "      <td>7.55</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2</td>\n",
       "      <td>2018-11-28 15:56:57</td>\n",
       "      <td>2018-11-28 15:58:33</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2.0</td>\n",
       "      <td>N</td>\n",
       "      <td>193</td>\n",
       "      <td>193</td>\n",
       "      <td>2</td>\n",
       "      <td>52.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.3</td>\n",
       "      <td>55.55</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7696612</th>\n",
       "      <td>2</td>\n",
       "      <td>2019-01-31 23:37:20</td>\n",
       "      <td>2019-02-01 00:10:43</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10.24</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>142</td>\n",
       "      <td>95</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2.75</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>5.76</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7696613</th>\n",
       "      <td>2</td>\n",
       "      <td>2019-01-31 23:28:00</td>\n",
       "      <td>2019-01-31 23:50:50</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12.43</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>48</td>\n",
       "      <td>213</td>\n",
       "      <td>0</td>\n",
       "      <td>48.80</td>\n",
       "      <td>5.50</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.3</td>\n",
       "      <td>54.60</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7696614</th>\n",
       "      <td>2</td>\n",
       "      <td>2019-01-31 23:11:00</td>\n",
       "      <td>2019-01-31 23:46:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9.14</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>159</td>\n",
       "      <td>246</td>\n",
       "      <td>0</td>\n",
       "      <td>51.05</td>\n",
       "      <td>2.75</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.3</td>\n",
       "      <td>54.60</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7696615</th>\n",
       "      <td>2</td>\n",
       "      <td>2019-01-31 23:03:00</td>\n",
       "      <td>2019-01-31 23:14:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>265</td>\n",
       "      <td>265</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.5</td>\n",
       "      <td>9.82</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7696616</th>\n",
       "      <td>2</td>\n",
       "      <td>2019-01-31 23:41:03</td>\n",
       "      <td>2019-02-01 00:19:16</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12.30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>237</td>\n",
       "      <td>197</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2.75</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>7696617 rows × 19 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         VendorID tpep_pickup_datetime tpep_dropoff_datetime  passenger_count  \\\n",
       "0               1  2019-01-01 00:46:40   2019-01-01 00:53:20              1.0   \n",
       "1               1  2019-01-01 00:59:47   2019-01-01 01:18:59              1.0   \n",
       "2               2  2018-12-21 13:48:30   2018-12-21 13:52:40              3.0   \n",
       "3               2  2018-11-28 15:52:25   2018-11-28 15:55:45              5.0   \n",
       "4               2  2018-11-28 15:56:57   2018-11-28 15:58:33              5.0   \n",
       "...           ...                  ...                   ...              ...   \n",
       "7696612         2  2019-01-31 23:37:20   2019-02-01 00:10:43              NaN   \n",
       "7696613         2  2019-01-31 23:28:00   2019-01-31 23:50:50              NaN   \n",
       "7696614         2  2019-01-31 23:11:00   2019-01-31 23:46:00              NaN   \n",
       "7696615         2  2019-01-31 23:03:00   2019-01-31 23:14:00              NaN   \n",
       "7696616         2  2019-01-31 23:41:03   2019-02-01 00:19:16              NaN   \n",
       "\n",
       "         trip_distance  RatecodeID store_and_fwd_flag  PULocationID  \\\n",
       "0                 1.50         1.0                  N           151   \n",
       "1                 2.60         1.0                  N           239   \n",
       "2                 0.00         1.0                  N           236   \n",
       "3                 0.00         1.0                  N           193   \n",
       "4                 0.00         2.0                  N           193   \n",
       "...                ...         ...                ...           ...   \n",
       "7696612          10.24         NaN               None           142   \n",
       "7696613          12.43         NaN               None            48   \n",
       "7696614           9.14         NaN               None           159   \n",
       "7696615           0.00         NaN               None           265   \n",
       "7696616          12.30         NaN               None           237   \n",
       "\n",
       "         DOLocationID  payment_type  fare_amount  extra  mta_tax  tip_amount  \\\n",
       "0                 239             1         7.00   0.50      0.5        1.65   \n",
       "1                 246             1        14.00   0.50      0.5        1.00   \n",
       "2                 236             1         4.50   0.50      0.5        0.00   \n",
       "3                 193             2         3.50   0.50      0.5        0.00   \n",
       "4                 193             2        52.00   0.00      0.5        0.00   \n",
       "...               ...           ...          ...    ...      ...         ...   \n",
       "7696612            95             0         0.00   2.75      0.0        0.00   \n",
       "7696613           213             0        48.80   5.50      0.0        0.00   \n",
       "7696614           246             0        51.05   2.75      0.5        0.00   \n",
       "7696615           265             0         0.00   0.00      0.5        9.82   \n",
       "7696616           197             0         0.00   2.75      0.0        0.00   \n",
       "\n",
       "         tolls_amount  improvement_surcharge  total_amount  \\\n",
       "0                0.00                    0.3          9.95   \n",
       "1                0.00                    0.3         16.30   \n",
       "2                0.00                    0.3          5.80   \n",
       "3                0.00                    0.3          7.55   \n",
       "4                0.00                    0.3         55.55   \n",
       "...               ...                    ...           ...   \n",
       "7696612          5.76                    0.3          0.00   \n",
       "7696613          0.00                    0.3         54.60   \n",
       "7696614          0.00                    0.3         54.60   \n",
       "7696615          0.00                    0.3          0.00   \n",
       "7696616          0.00                    0.3          0.00   \n",
       "\n",
       "         congestion_surcharge airport_fee  \n",
       "0                         NaN        None  \n",
       "1                         NaN        None  \n",
       "2                         NaN        None  \n",
       "3                         NaN        None  \n",
       "4                         NaN        None  \n",
       "...                       ...         ...  \n",
       "7696612                   NaN        None  \n",
       "7696613                   NaN        None  \n",
       "7696614                   NaN        None  \n",
       "7696615                   NaN        None  \n",
       "7696616                   NaN        None  \n",
       "\n",
       "[7696617 rows x 19 columns]"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%time\n",
    "\n",
    "import pandas as pd\n",
    "\n",
    "df = pd.read_parquet(\"s3://nyc-tlc/trip data/yellow_tripdata_2019-01.parquet\")\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note the time taken, it's ~5 seconds in our case. pandas has read all the data for January and inferred the datatypes for each column. The `.info()` method can be used to gather a concise summary of the dataframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 7696617 entries, 0 to 7696616\n",
      "Data columns (total 19 columns):\n",
      " #   Column                 Dtype         \n",
      "---  ------                 -----         \n",
      " 0   VendorID               int64         \n",
      " 1   tpep_pickup_datetime   datetime64[ns]\n",
      " 2   tpep_dropoff_datetime  datetime64[ns]\n",
      " 3   passenger_count        float64       \n",
      " 4   trip_distance          float64       \n",
      " 5   RatecodeID             float64       \n",
      " 6   store_and_fwd_flag     object        \n",
      " 7   PULocationID           int64         \n",
      " 8   DOLocationID           int64         \n",
      " 9   payment_type           int64         \n",
      " 10  fare_amount            float64       \n",
      " 11  extra                  float64       \n",
      " 12  mta_tax                float64       \n",
      " 13  tip_amount             float64       \n",
      " 14  tolls_amount           float64       \n",
      " 15  improvement_surcharge  float64       \n",
      " 16  total_amount           float64       \n",
      " 17  congestion_surcharge   float64       \n",
      " 18  airport_fee            object        \n",
      "dtypes: datetime64[ns](2), float64(11), int64(4), object(2)\n",
      "memory usage: 1.1+ GB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Working with the data\n",
    "\n",
    "After importing the data, the next step is working on the data to find some useful information.\n",
    "\n",
    "In the following blocks, the mean of the tip amount is calculated as a function of passenger count."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In pandas, you can use `mean()` to calculate mean, and `groupby()` for mapping to a column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 124 ms, sys: 17.1 ms, total: 141 ms\n",
      "Wall time: 138 ms\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "passenger_count\n",
       "0.0    1.786901\n",
       "1.0    1.828352\n",
       "2.0    1.833932\n",
       "3.0    1.795589\n",
       "4.0    1.702710\n",
       "5.0    1.869868\n",
       "6.0    1.856830\n",
       "7.0    6.542632\n",
       "8.0    6.480690\n",
       "9.0    3.116667\n",
       "Name: tip_amount, dtype: float64"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%time\n",
    "\n",
    "df.groupby(\"passenger_count\").tip_amount.mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Limitation in pandas\n",
    "\n",
    "pandas is the most popular library for exploratory data analysis, but it has a limitation. pandas is great at handling small quantities of data, but fails with a `MemoryError` when using larger datasets. This is where Dask comes in."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Optional: Uncomment and run the following code block to read the entire dataset in pandas."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# import glob\n",
    "\n",
    "# df = pd.concat(map(pd.read_csv, glob.glob('data/*.csv')))\n",
    "# df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Reading and working with tabular data using **Dask DataFrame**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Reading data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Dask can be used to scale pandas to larger datasets. Dask's DataFrame API has the same functions as the pandas API because it's a wrapper around pandas. This makes Dask code familiar and easy to use.\n",
    "\n",
    "First, spin up a cluster! "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "    <div style=\"width: 24px; height: 24px; background-color: #e1e1e1; border: 3px solid #9D9D9D; border-radius: 5px; position: absolute;\"> </div>\n",
       "    <div style=\"margin-left: 48px;\">\n",
       "        <h3 style=\"margin-bottom: 0px;\">Client</h3>\n",
       "        <p style=\"color: #9D9D9D; margin-bottom: 0px;\">Client-4e817d36-7a77-11ed-9f63-92492cdc1fe7</p>\n",
       "        <table style=\"width: 100%; text-align: left;\">\n",
       "\n",
       "        <tr>\n",
       "        \n",
       "            <td style=\"text-align: left;\"><strong>Connection method:</strong> Cluster object</td>\n",
       "            <td style=\"text-align: left;\"><strong>Cluster type:</strong> distributed.LocalCluster</td>\n",
       "        \n",
       "        </tr>\n",
       "\n",
       "        \n",
       "            <tr>\n",
       "                <td style=\"text-align: left;\">\n",
       "                    <strong>Dashboard: </strong> <a href=\"http://127.0.0.1:8787/status\" target=\"_blank\">http://127.0.0.1:8787/status</a>\n",
       "                </td>\n",
       "                <td style=\"text-align: left;\"></td>\n",
       "            </tr>\n",
       "        \n",
       "\n",
       "        </table>\n",
       "\n",
       "        \n",
       "            <button style=\"margin-bottom: 12px;\" data-commandlinker-command=\"dask:populate-and-launch-layout\" data-commandlinker-args='{\"url\": \"http://127.0.0.1:8787/status\" }'>\n",
       "                Launch dashboard in JupyterLab\n",
       "            </button>\n",
       "        \n",
       "\n",
       "        \n",
       "            <details>\n",
       "            <summary style=\"margin-bottom: 20px;\"><h3 style=\"display: inline;\">Cluster Info</h3></summary>\n",
       "            <div class=\"jp-RenderedHTMLCommon jp-RenderedHTML jp-mod-trusted jp-OutputArea-output\">\n",
       "    <div style=\"width: 24px; height: 24px; background-color: #e1e1e1; border: 3px solid #9D9D9D; border-radius: 5px; position: absolute;\">\n",
       "    </div>\n",
       "    <div style=\"margin-left: 48px;\">\n",
       "        <h3 style=\"margin-bottom: 0px; margin-top: 0px;\">LocalCluster</h3>\n",
       "        <p style=\"color: #9D9D9D; margin-bottom: 0px;\">bbd8c95a</p>\n",
       "        <table style=\"width: 100%; text-align: left;\">\n",
       "            <tr>\n",
       "                <td style=\"text-align: left;\">\n",
       "                    <strong>Dashboard:</strong> <a href=\"http://127.0.0.1:8787/status\" target=\"_blank\">http://127.0.0.1:8787/status</a>\n",
       "                </td>\n",
       "                <td style=\"text-align: left;\">\n",
       "                    <strong>Workers:</strong> 4\n",
       "                </td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                <td style=\"text-align: left;\">\n",
       "                    <strong>Total threads:</strong> 8\n",
       "                </td>\n",
       "                <td style=\"text-align: left;\">\n",
       "                    <strong>Total memory:</strong> 16.00 GiB\n",
       "                </td>\n",
       "            </tr>\n",
       "            \n",
       "            <tr>\n",
       "    <td style=\"text-align: left;\"><strong>Status:</strong> running</td>\n",
       "    <td style=\"text-align: left;\"><strong>Using processes:</strong> True</td>\n",
       "</tr>\n",
       "\n",
       "            \n",
       "        </table>\n",
       "\n",
       "        <details>\n",
       "            <summary style=\"margin-bottom: 20px;\">\n",
       "                <h3 style=\"display: inline;\">Scheduler Info</h3>\n",
       "            </summary>\n",
       "\n",
       "            <div style=\"\">\n",
       "    <div>\n",
       "        <div style=\"width: 24px; height: 24px; background-color: #FFF7E5; border: 3px solid #FF6132; border-radius: 5px; position: absolute;\"> </div>\n",
       "        <div style=\"margin-left: 48px;\">\n",
       "            <h3 style=\"margin-bottom: 0px;\">Scheduler</h3>\n",
       "            <p style=\"color: #9D9D9D; margin-bottom: 0px;\">Scheduler-2e085ad6-9c17-41bb-9173-d81745b7ff2b</p>\n",
       "            <table style=\"width: 100%; text-align: left;\">\n",
       "                <tr>\n",
       "                    <td style=\"text-align: left;\">\n",
       "                        <strong>Comm:</strong> tcp://127.0.0.1:51662\n",
       "                    </td>\n",
       "                    <td style=\"text-align: left;\">\n",
       "                        <strong>Workers:</strong> 4\n",
       "                    </td>\n",
       "                </tr>\n",
       "                <tr>\n",
       "                    <td style=\"text-align: left;\">\n",
       "                        <strong>Dashboard:</strong> <a href=\"http://127.0.0.1:8787/status\" target=\"_blank\">http://127.0.0.1:8787/status</a>\n",
       "                    </td>\n",
       "                    <td style=\"text-align: left;\">\n",
       "                        <strong>Total threads:</strong> 8\n",
       "                    </td>\n",
       "                </tr>\n",
       "                <tr>\n",
       "                    <td style=\"text-align: left;\">\n",
       "                        <strong>Started:</strong> Just now\n",
       "                    </td>\n",
       "                    <td style=\"text-align: left;\">\n",
       "                        <strong>Total memory:</strong> 16.00 GiB\n",
       "                    </td>\n",
       "                </tr>\n",
       "            </table>\n",
       "        </div>\n",
       "    </div>\n",
       "\n",
       "    <details style=\"margin-left: 48px;\">\n",
       "        <summary style=\"margin-bottom: 20px;\">\n",
       "            <h3 style=\"display: inline;\">Workers</h3>\n",
       "        </summary>\n",
       "\n",
       "        \n",
       "        <div style=\"margin-bottom: 20px;\">\n",
       "            <div style=\"width: 24px; height: 24px; background-color: #DBF5FF; border: 3px solid #4CC9FF; border-radius: 5px; position: absolute;\"> </div>\n",
       "            <div style=\"margin-left: 48px;\">\n",
       "            <details>\n",
       "                <summary>\n",
       "                    <h4 style=\"margin-bottom: 0px; display: inline;\">Worker: 0</h4>\n",
       "                </summary>\n",
       "                <table style=\"width: 100%; text-align: left;\">\n",
       "                    <tr>\n",
       "                        <td style=\"text-align: left;\">\n",
       "                            <strong>Comm: </strong> tcp://127.0.0.1:51680\n",
       "                        </td>\n",
       "                        <td style=\"text-align: left;\">\n",
       "                            <strong>Total threads: </strong> 2\n",
       "                        </td>\n",
       "                    </tr>\n",
       "                    <tr>\n",
       "                        <td style=\"text-align: left;\">\n",
       "                            <strong>Dashboard: </strong> <a href=\"http://127.0.0.1:51681/status\" target=\"_blank\">http://127.0.0.1:51681/status</a>\n",
       "                        </td>\n",
       "                        <td style=\"text-align: left;\">\n",
       "                            <strong>Memory: </strong> 4.00 GiB\n",
       "                        </td>\n",
       "                    </tr>\n",
       "                    <tr>\n",
       "                        <td style=\"text-align: left;\">\n",
       "                            <strong>Nanny: </strong> tcp://127.0.0.1:51666\n",
       "                        </td>\n",
       "                        <td style=\"text-align: left;\"></td>\n",
       "                    </tr>\n",
       "                    <tr>\n",
       "                        <td colspan=\"2\" style=\"text-align: left;\">\n",
       "                            <strong>Local directory: </strong> /var/folders/hf/2s7qjx7j5ndc5220_qxv8y800000gn/T/dask-worker-space/worker-uhglwxcd\n",
       "                        </td>\n",
       "                    </tr>\n",
       "\n",
       "                    \n",
       "\n",
       "                    \n",
       "\n",
       "                </table>\n",
       "            </details>\n",
       "            </div>\n",
       "        </div>\n",
       "        \n",
       "        <div style=\"margin-bottom: 20px;\">\n",
       "            <div style=\"width: 24px; height: 24px; background-color: #DBF5FF; border: 3px solid #4CC9FF; border-radius: 5px; position: absolute;\"> </div>\n",
       "            <div style=\"margin-left: 48px;\">\n",
       "            <details>\n",
       "                <summary>\n",
       "                    <h4 style=\"margin-bottom: 0px; display: inline;\">Worker: 1</h4>\n",
       "                </summary>\n",
       "                <table style=\"width: 100%; text-align: left;\">\n",
       "                    <tr>\n",
       "                        <td style=\"text-align: left;\">\n",
       "                            <strong>Comm: </strong> tcp://127.0.0.1:51679\n",
       "                        </td>\n",
       "                        <td style=\"text-align: left;\">\n",
       "                            <strong>Total threads: </strong> 2\n",
       "                        </td>\n",
       "                    </tr>\n",
       "                    <tr>\n",
       "                        <td style=\"text-align: left;\">\n",
       "                            <strong>Dashboard: </strong> <a href=\"http://127.0.0.1:51684/status\" target=\"_blank\">http://127.0.0.1:51684/status</a>\n",
       "                        </td>\n",
       "                        <td style=\"text-align: left;\">\n",
       "                            <strong>Memory: </strong> 4.00 GiB\n",
       "                        </td>\n",
       "                    </tr>\n",
       "                    <tr>\n",
       "                        <td style=\"text-align: left;\">\n",
       "                            <strong>Nanny: </strong> tcp://127.0.0.1:51665\n",
       "                        </td>\n",
       "                        <td style=\"text-align: left;\"></td>\n",
       "                    </tr>\n",
       "                    <tr>\n",
       "                        <td colspan=\"2\" style=\"text-align: left;\">\n",
       "                            <strong>Local directory: </strong> /var/folders/hf/2s7qjx7j5ndc5220_qxv8y800000gn/T/dask-worker-space/worker-vdo838m4\n",
       "                        </td>\n",
       "                    </tr>\n",
       "\n",
       "                    \n",
       "\n",
       "                    \n",
       "\n",
       "                </table>\n",
       "            </details>\n",
       "            </div>\n",
       "        </div>\n",
       "        \n",
       "        <div style=\"margin-bottom: 20px;\">\n",
       "            <div style=\"width: 24px; height: 24px; background-color: #DBF5FF; border: 3px solid #4CC9FF; border-radius: 5px; position: absolute;\"> </div>\n",
       "            <div style=\"margin-left: 48px;\">\n",
       "            <details>\n",
       "                <summary>\n",
       "                    <h4 style=\"margin-bottom: 0px; display: inline;\">Worker: 2</h4>\n",
       "                </summary>\n",
       "                <table style=\"width: 100%; text-align: left;\">\n",
       "                    <tr>\n",
       "                        <td style=\"text-align: left;\">\n",
       "                            <strong>Comm: </strong> tcp://127.0.0.1:51677\n",
       "                        </td>\n",
       "                        <td style=\"text-align: left;\">\n",
       "                            <strong>Total threads: </strong> 2\n",
       "                        </td>\n",
       "                    </tr>\n",
       "                    <tr>\n",
       "                        <td style=\"text-align: left;\">\n",
       "                            <strong>Dashboard: </strong> <a href=\"http://127.0.0.1:51683/status\" target=\"_blank\">http://127.0.0.1:51683/status</a>\n",
       "                        </td>\n",
       "                        <td style=\"text-align: left;\">\n",
       "                            <strong>Memory: </strong> 4.00 GiB\n",
       "                        </td>\n",
       "                    </tr>\n",
       "                    <tr>\n",
       "                        <td style=\"text-align: left;\">\n",
       "                            <strong>Nanny: </strong> tcp://127.0.0.1:51667\n",
       "                        </td>\n",
       "                        <td style=\"text-align: left;\"></td>\n",
       "                    </tr>\n",
       "                    <tr>\n",
       "                        <td colspan=\"2\" style=\"text-align: left;\">\n",
       "                            <strong>Local directory: </strong> /var/folders/hf/2s7qjx7j5ndc5220_qxv8y800000gn/T/dask-worker-space/worker-_g98czai\n",
       "                        </td>\n",
       "                    </tr>\n",
       "\n",
       "                    \n",
       "\n",
       "                    \n",
       "\n",
       "                </table>\n",
       "            </details>\n",
       "            </div>\n",
       "        </div>\n",
       "        \n",
       "        <div style=\"margin-bottom: 20px;\">\n",
       "            <div style=\"width: 24px; height: 24px; background-color: #DBF5FF; border: 3px solid #4CC9FF; border-radius: 5px; position: absolute;\"> </div>\n",
       "            <div style=\"margin-left: 48px;\">\n",
       "            <details>\n",
       "                <summary>\n",
       "                    <h4 style=\"margin-bottom: 0px; display: inline;\">Worker: 3</h4>\n",
       "                </summary>\n",
       "                <table style=\"width: 100%; text-align: left;\">\n",
       "                    <tr>\n",
       "                        <td style=\"text-align: left;\">\n",
       "                            <strong>Comm: </strong> tcp://127.0.0.1:51678\n",
       "                        </td>\n",
       "                        <td style=\"text-align: left;\">\n",
       "                            <strong>Total threads: </strong> 2\n",
       "                        </td>\n",
       "                    </tr>\n",
       "                    <tr>\n",
       "                        <td style=\"text-align: left;\">\n",
       "                            <strong>Dashboard: </strong> <a href=\"http://127.0.0.1:51682/status\" target=\"_blank\">http://127.0.0.1:51682/status</a>\n",
       "                        </td>\n",
       "                        <td style=\"text-align: left;\">\n",
       "                            <strong>Memory: </strong> 4.00 GiB\n",
       "                        </td>\n",
       "                    </tr>\n",
       "                    <tr>\n",
       "                        <td style=\"text-align: left;\">\n",
       "                            <strong>Nanny: </strong> tcp://127.0.0.1:51668\n",
       "                        </td>\n",
       "                        <td style=\"text-align: left;\"></td>\n",
       "                    </tr>\n",
       "                    <tr>\n",
       "                        <td colspan=\"2\" style=\"text-align: left;\">\n",
       "                            <strong>Local directory: </strong> /var/folders/hf/2s7qjx7j5ndc5220_qxv8y800000gn/T/dask-worker-space/worker-xqi396ff\n",
       "                        </td>\n",
       "                    </tr>\n",
       "\n",
       "                    \n",
       "\n",
       "                    \n",
       "\n",
       "                </table>\n",
       "            </details>\n",
       "            </div>\n",
       "        </div>\n",
       "        \n",
       "\n",
       "    </details>\n",
       "</div>\n",
       "\n",
       "        </details>\n",
       "    </div>\n",
       "</div>\n",
       "            </details>\n",
       "        \n",
       "\n",
       "    </div>\n",
       "</div>"
      ],
      "text/plain": [
       "<Client: 'tcp://127.0.0.1:51662' processes=4 threads=8, memory=16.00 GiB>"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from dask.distributed import Client\n",
    "\n",
    "client = Client(n_workers=4)\n",
    "client"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Open the Dask Dashboard in JupyterLab -- Cluster Map, Task Stream, and Dask workers\n",
    "\n",
    "* **Cluster map** (also called the pew-pew map) visualizes interactions between the scheduler and the workers.\n",
    "* **Task stream** shows tasks performed by each worker in real-time.\n",
    "* **Dask workers** displays CPU and memory being used by each worker."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The same reading operation with Dask, but this time read the complete dataset - data for all the years."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 443 ms, sys: 151 ms, total: 594 ms\n",
      "Wall time: 2.05 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>VendorID</th>\n",
       "      <th>tpep_pickup_datetime</th>\n",
       "      <th>tpep_dropoff_datetime</th>\n",
       "      <th>passenger_count</th>\n",
       "      <th>trip_distance</th>\n",
       "      <th>RatecodeID</th>\n",
       "      <th>store_and_fwd_flag</th>\n",
       "      <th>PULocationID</th>\n",
       "      <th>DOLocationID</th>\n",
       "      <th>payment_type</th>\n",
       "      <th>fare_amount</th>\n",
       "      <th>extra</th>\n",
       "      <th>mta_tax</th>\n",
       "      <th>tip_amount</th>\n",
       "      <th>tolls_amount</th>\n",
       "      <th>improvement_surcharge</th>\n",
       "      <th>total_amount</th>\n",
       "      <th>congestion_surcharge</th>\n",
       "      <th>airport_fee</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>npartitions=12</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>int64</td>\n",
       "      <td>datetime64[ns]</td>\n",
       "      <td>datetime64[ns]</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>object</td>\n",
       "      <td>int64</td>\n",
       "      <td>int64</td>\n",
       "      <td>int64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "<div>Dask Name: read-parquet, 1 graph layer</div>"
      ],
      "text/plain": [
       "Dask DataFrame Structure:\n",
       "               VendorID tpep_pickup_datetime tpep_dropoff_datetime passenger_count trip_distance RatecodeID store_and_fwd_flag PULocationID DOLocationID payment_type fare_amount    extra  mta_tax tip_amount tolls_amount improvement_surcharge total_amount congestion_surcharge airport_fee\n",
       "npartitions=12                                                                                                                                                                                                                                                                                 \n",
       "                  int64       datetime64[ns]        datetime64[ns]         float64       float64    float64             object        int64        int64        int64     float64  float64  float64    float64      float64               float64      float64              float64      object\n",
       "                    ...                  ...                   ...             ...           ...        ...                ...          ...          ...          ...         ...      ...      ...        ...          ...                   ...          ...                  ...         ...\n",
       "...                 ...                  ...                   ...             ...           ...        ...                ...          ...          ...          ...         ...      ...      ...        ...          ...                   ...          ...                  ...         ...\n",
       "                    ...                  ...                   ...             ...           ...        ...                ...          ...          ...          ...         ...      ...      ...        ...          ...                   ...          ...                  ...         ...\n",
       "                    ...                  ...                   ...             ...           ...        ...                ...          ...          ...          ...         ...      ...      ...        ...          ...                   ...          ...                  ...         ...\n",
       "Dask Name: read-parquet, 1 graph layer"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%time\n",
    "\n",
    "import dask.dataframe as dd\n",
    "\n",
    "df = dd.read_parquet(\"s3://nyc-tlc/trip data/yellow_tripdata_2019-*.parquet\")\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "That took ~600 milliseconds because Dask hasn't actually imported all the data. It has created partitions and estimated the datatypes of each column.\n",
    "\n",
    "Let's look at the first few rows, `head()` pandas method can be used for this."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>VendorID</th>\n",
       "      <th>tpep_pickup_datetime</th>\n",
       "      <th>tpep_dropoff_datetime</th>\n",
       "      <th>passenger_count</th>\n",
       "      <th>trip_distance</th>\n",
       "      <th>RatecodeID</th>\n",
       "      <th>store_and_fwd_flag</th>\n",
       "      <th>PULocationID</th>\n",
       "      <th>DOLocationID</th>\n",
       "      <th>payment_type</th>\n",
       "      <th>fare_amount</th>\n",
       "      <th>extra</th>\n",
       "      <th>mta_tax</th>\n",
       "      <th>tip_amount</th>\n",
       "      <th>tolls_amount</th>\n",
       "      <th>improvement_surcharge</th>\n",
       "      <th>total_amount</th>\n",
       "      <th>congestion_surcharge</th>\n",
       "      <th>airport_fee</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2019-01-01 00:46:40</td>\n",
       "      <td>2019-01-01 00:53:20</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.5</td>\n",
       "      <td>1.0</td>\n",
       "      <td>N</td>\n",
       "      <td>151</td>\n",
       "      <td>239</td>\n",
       "      <td>1</td>\n",
       "      <td>7.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1.65</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>9.95</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2019-01-01 00:59:47</td>\n",
       "      <td>2019-01-01 01:18:59</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.6</td>\n",
       "      <td>1.0</td>\n",
       "      <td>N</td>\n",
       "      <td>239</td>\n",
       "      <td>246</td>\n",
       "      <td>1</td>\n",
       "      <td>14.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>16.30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>2018-12-21 13:48:30</td>\n",
       "      <td>2018-12-21 13:52:40</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>N</td>\n",
       "      <td>236</td>\n",
       "      <td>236</td>\n",
       "      <td>1</td>\n",
       "      <td>4.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>5.80</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2</td>\n",
       "      <td>2018-11-28 15:52:25</td>\n",
       "      <td>2018-11-28 15:55:45</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>N</td>\n",
       "      <td>193</td>\n",
       "      <td>193</td>\n",
       "      <td>2</td>\n",
       "      <td>3.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>7.55</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2</td>\n",
       "      <td>2018-11-28 15:56:57</td>\n",
       "      <td>2018-11-28 15:58:33</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>N</td>\n",
       "      <td>193</td>\n",
       "      <td>193</td>\n",
       "      <td>2</td>\n",
       "      <td>52.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>55.55</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   VendorID tpep_pickup_datetime tpep_dropoff_datetime  passenger_count  \\\n",
       "0         1  2019-01-01 00:46:40   2019-01-01 00:53:20              1.0   \n",
       "1         1  2019-01-01 00:59:47   2019-01-01 01:18:59              1.0   \n",
       "2         2  2018-12-21 13:48:30   2018-12-21 13:52:40              3.0   \n",
       "3         2  2018-11-28 15:52:25   2018-11-28 15:55:45              5.0   \n",
       "4         2  2018-11-28 15:56:57   2018-11-28 15:58:33              5.0   \n",
       "\n",
       "   trip_distance  RatecodeID store_and_fwd_flag  PULocationID  DOLocationID  \\\n",
       "0            1.5         1.0                  N           151           239   \n",
       "1            2.6         1.0                  N           239           246   \n",
       "2            0.0         1.0                  N           236           236   \n",
       "3            0.0         1.0                  N           193           193   \n",
       "4            0.0         2.0                  N           193           193   \n",
       "\n",
       "   payment_type  fare_amount  extra  mta_tax  tip_amount  tolls_amount  \\\n",
       "0             1          7.0    0.5      0.5        1.65           0.0   \n",
       "1             1         14.0    0.5      0.5        1.00           0.0   \n",
       "2             1          4.5    0.5      0.5        0.00           0.0   \n",
       "3             2          3.5    0.5      0.5        0.00           0.0   \n",
       "4             2         52.0    0.0      0.5        0.00           0.0   \n",
       "\n",
       "   improvement_surcharge  total_amount  congestion_surcharge airport_fee  \n",
       "0                    0.3          9.95                   NaN        None  \n",
       "1                    0.3         16.30                   NaN        None  \n",
       "2                    0.3          5.80                   NaN        None  \n",
       "3                    0.3          7.55                   NaN        None  \n",
       "4                    0.3         55.55                   NaN        None  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To look at the last few rows, use the `tail()` pandas method."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>VendorID</th>\n",
       "      <th>tpep_pickup_datetime</th>\n",
       "      <th>tpep_dropoff_datetime</th>\n",
       "      <th>passenger_count</th>\n",
       "      <th>trip_distance</th>\n",
       "      <th>RatecodeID</th>\n",
       "      <th>store_and_fwd_flag</th>\n",
       "      <th>PULocationID</th>\n",
       "      <th>DOLocationID</th>\n",
       "      <th>payment_type</th>\n",
       "      <th>fare_amount</th>\n",
       "      <th>extra</th>\n",
       "      <th>mta_tax</th>\n",
       "      <th>tip_amount</th>\n",
       "      <th>tolls_amount</th>\n",
       "      <th>improvement_surcharge</th>\n",
       "      <th>total_amount</th>\n",
       "      <th>congestion_surcharge</th>\n",
       "      <th>airport_fee</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>6896312</th>\n",
       "      <td>2</td>\n",
       "      <td>2019-12-31 23:56:29</td>\n",
       "      <td>2020-01-01 00:11:17</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.82</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>143</td>\n",
       "      <td>141</td>\n",
       "      <td>0</td>\n",
       "      <td>18.95</td>\n",
       "      <td>2.75</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.3</td>\n",
       "      <td>22.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6896313</th>\n",
       "      <td>2</td>\n",
       "      <td>2019-12-31 23:11:53</td>\n",
       "      <td>2019-12-31 23:30:56</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.75</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>148</td>\n",
       "      <td>246</td>\n",
       "      <td>0</td>\n",
       "      <td>22.45</td>\n",
       "      <td>2.75</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.3</td>\n",
       "      <td>25.50</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6896314</th>\n",
       "      <td>2</td>\n",
       "      <td>2019-12-31 23:57:21</td>\n",
       "      <td>2020-01-01 00:23:34</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.46</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>197</td>\n",
       "      <td>205</td>\n",
       "      <td>0</td>\n",
       "      <td>34.86</td>\n",
       "      <td>2.75</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.3</td>\n",
       "      <td>37.91</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6896315</th>\n",
       "      <td>2</td>\n",
       "      <td>2019-12-31 23:37:29</td>\n",
       "      <td>2020-01-01 00:28:21</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.66</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>90</td>\n",
       "      <td>74</td>\n",
       "      <td>0</td>\n",
       "      <td>36.45</td>\n",
       "      <td>2.75</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.3</td>\n",
       "      <td>39.50</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6896316</th>\n",
       "      <td>2</td>\n",
       "      <td>2019-12-31 23:09:00</td>\n",
       "      <td>2019-12-31 23:54:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-15.50</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>142</td>\n",
       "      <td>149</td>\n",
       "      <td>0</td>\n",
       "      <td>53.03</td>\n",
       "      <td>2.75</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.12</td>\n",
       "      <td>0.3</td>\n",
       "      <td>62.70</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         VendorID tpep_pickup_datetime tpep_dropoff_datetime  passenger_count  \\\n",
       "6896312         2  2019-12-31 23:56:29   2020-01-01 00:11:17              NaN   \n",
       "6896313         2  2019-12-31 23:11:53   2019-12-31 23:30:56              NaN   \n",
       "6896314         2  2019-12-31 23:57:21   2020-01-01 00:23:34              NaN   \n",
       "6896315         2  2019-12-31 23:37:29   2020-01-01 00:28:21              NaN   \n",
       "6896316         2  2019-12-31 23:09:00   2019-12-31 23:54:00              NaN   \n",
       "\n",
       "         trip_distance  RatecodeID store_and_fwd_flag  PULocationID  \\\n",
       "6896312           2.82         NaN               None           143   \n",
       "6896313           3.75         NaN               None           148   \n",
       "6896314           6.46         NaN               None           197   \n",
       "6896315           5.66         NaN               None            90   \n",
       "6896316         -15.50         NaN               None           142   \n",
       "\n",
       "         DOLocationID  payment_type  fare_amount  extra  mta_tax  tip_amount  \\\n",
       "6896312           141             0        18.95   2.75      0.0         0.0   \n",
       "6896313           246             0        22.45   2.75      0.0         0.0   \n",
       "6896314           205             0        34.86   2.75      0.0         0.0   \n",
       "6896315            74             0        36.45   2.75      0.0         0.0   \n",
       "6896316           149             0        53.03   2.75      0.5         0.0   \n",
       "\n",
       "         tolls_amount  improvement_surcharge  total_amount  \\\n",
       "6896312          0.00                    0.3         22.00   \n",
       "6896313          0.00                    0.3         25.50   \n",
       "6896314          0.00                    0.3         37.91   \n",
       "6896315          0.00                    0.3         39.50   \n",
       "6896316          6.12                    0.3         62.70   \n",
       "\n",
       "         congestion_surcharge airport_fee  \n",
       "6896312                   NaN        None  \n",
       "6896313                   NaN        None  \n",
       "6896314                   NaN        None  \n",
       "6896315                   NaN        None  \n",
       "6896316                   NaN        None  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is different from pandas. pandas reads the complete dataset before inferring the datatypes and null-value information, which wouldn't be ideal for a larger-than-memory dataset.\n",
    "\n",
    "Dask estimates the datatypes with a small sample of data to stay efficient, so a good practice is to specify datatypes during the function call.\n",
    "\n",
    "*Note that Dask also provides a helpful error message to diagnose this issue.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = dd.read_parquet(\n",
    "    \"s3://nyc-tlc/trip data/yellow_tripdata_2019-*.parquet\",\n",
    "    dtype={'RatecodeID': 'float64',\n",
    "           'VendorID': 'float64',\n",
    "           'passenger_count': 'float64',\n",
    "           'payment_type': 'float64'}\n",
    ")\n",
    "# repartition the dataset to a more optimal size for faster computations\n",
    "df = df.repartition(partition_size=\"100MB\").persist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>VendorID</th>\n",
       "      <th>tpep_pickup_datetime</th>\n",
       "      <th>tpep_dropoff_datetime</th>\n",
       "      <th>passenger_count</th>\n",
       "      <th>trip_distance</th>\n",
       "      <th>RatecodeID</th>\n",
       "      <th>store_and_fwd_flag</th>\n",
       "      <th>PULocationID</th>\n",
       "      <th>DOLocationID</th>\n",
       "      <th>payment_type</th>\n",
       "      <th>fare_amount</th>\n",
       "      <th>extra</th>\n",
       "      <th>mta_tax</th>\n",
       "      <th>tip_amount</th>\n",
       "      <th>tolls_amount</th>\n",
       "      <th>improvement_surcharge</th>\n",
       "      <th>total_amount</th>\n",
       "      <th>congestion_surcharge</th>\n",
       "      <th>airport_fee</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2019-01-01 00:46:40</td>\n",
       "      <td>2019-01-01 00:53:20</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.5</td>\n",
       "      <td>1.0</td>\n",
       "      <td>N</td>\n",
       "      <td>151</td>\n",
       "      <td>239</td>\n",
       "      <td>1</td>\n",
       "      <td>7.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1.65</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>9.95</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2019-01-01 00:59:47</td>\n",
       "      <td>2019-01-01 01:18:59</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.6</td>\n",
       "      <td>1.0</td>\n",
       "      <td>N</td>\n",
       "      <td>239</td>\n",
       "      <td>246</td>\n",
       "      <td>1</td>\n",
       "      <td>14.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>16.30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>2018-12-21 13:48:30</td>\n",
       "      <td>2018-12-21 13:52:40</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>N</td>\n",
       "      <td>236</td>\n",
       "      <td>236</td>\n",
       "      <td>1</td>\n",
       "      <td>4.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>5.80</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2</td>\n",
       "      <td>2018-11-28 15:52:25</td>\n",
       "      <td>2018-11-28 15:55:45</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>N</td>\n",
       "      <td>193</td>\n",
       "      <td>193</td>\n",
       "      <td>2</td>\n",
       "      <td>3.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>7.55</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2</td>\n",
       "      <td>2018-11-28 15:56:57</td>\n",
       "      <td>2018-11-28 15:58:33</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>N</td>\n",
       "      <td>193</td>\n",
       "      <td>193</td>\n",
       "      <td>2</td>\n",
       "      <td>52.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>55.55</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   VendorID tpep_pickup_datetime tpep_dropoff_datetime  passenger_count  \\\n",
       "0         1  2019-01-01 00:46:40   2019-01-01 00:53:20              1.0   \n",
       "1         1  2019-01-01 00:59:47   2019-01-01 01:18:59              1.0   \n",
       "2         2  2018-12-21 13:48:30   2018-12-21 13:52:40              3.0   \n",
       "3         2  2018-11-28 15:52:25   2018-11-28 15:55:45              5.0   \n",
       "4         2  2018-11-28 15:56:57   2018-11-28 15:58:33              5.0   \n",
       "\n",
       "   trip_distance  RatecodeID store_and_fwd_flag  PULocationID  DOLocationID  \\\n",
       "0            1.5         1.0                  N           151           239   \n",
       "1            2.6         1.0                  N           239           246   \n",
       "2            0.0         1.0                  N           236           236   \n",
       "3            0.0         1.0                  N           193           193   \n",
       "4            0.0         2.0                  N           193           193   \n",
       "\n",
       "   payment_type  fare_amount  extra  mta_tax  tip_amount  tolls_amount  \\\n",
       "0             1          7.0    0.5      0.5        1.65           0.0   \n",
       "1             1         14.0    0.5      0.5        1.00           0.0   \n",
       "2             1          4.5    0.5      0.5        0.00           0.0   \n",
       "3             2          3.5    0.5      0.5        0.00           0.0   \n",
       "4             2         52.0    0.0      0.5        0.00           0.0   \n",
       "\n",
       "   improvement_surcharge  total_amount  congestion_surcharge airport_fee  \n",
       "0                    0.3          9.95                   NaN        None  \n",
       "1                    0.3         16.30                   NaN        None  \n",
       "2                    0.3          5.80                   NaN        None  \n",
       "3                    0.3          7.55                   NaN        None  \n",
       "4                    0.3         55.55                   NaN        None  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>VendorID</th>\n",
       "      <th>tpep_pickup_datetime</th>\n",
       "      <th>tpep_dropoff_datetime</th>\n",
       "      <th>passenger_count</th>\n",
       "      <th>trip_distance</th>\n",
       "      <th>RatecodeID</th>\n",
       "      <th>store_and_fwd_flag</th>\n",
       "      <th>PULocationID</th>\n",
       "      <th>DOLocationID</th>\n",
       "      <th>payment_type</th>\n",
       "      <th>fare_amount</th>\n",
       "      <th>extra</th>\n",
       "      <th>mta_tax</th>\n",
       "      <th>tip_amount</th>\n",
       "      <th>tolls_amount</th>\n",
       "      <th>improvement_surcharge</th>\n",
       "      <th>total_amount</th>\n",
       "      <th>congestion_surcharge</th>\n",
       "      <th>airport_fee</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>6896312</th>\n",
       "      <td>2</td>\n",
       "      <td>2019-12-31 23:56:29</td>\n",
       "      <td>2020-01-01 00:11:17</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.82</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>143</td>\n",
       "      <td>141</td>\n",
       "      <td>0</td>\n",
       "      <td>18.95</td>\n",
       "      <td>2.75</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.3</td>\n",
       "      <td>22.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6896313</th>\n",
       "      <td>2</td>\n",
       "      <td>2019-12-31 23:11:53</td>\n",
       "      <td>2019-12-31 23:30:56</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.75</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>148</td>\n",
       "      <td>246</td>\n",
       "      <td>0</td>\n",
       "      <td>22.45</td>\n",
       "      <td>2.75</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.3</td>\n",
       "      <td>25.50</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6896314</th>\n",
       "      <td>2</td>\n",
       "      <td>2019-12-31 23:57:21</td>\n",
       "      <td>2020-01-01 00:23:34</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.46</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>197</td>\n",
       "      <td>205</td>\n",
       "      <td>0</td>\n",
       "      <td>34.86</td>\n",
       "      <td>2.75</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.3</td>\n",
       "      <td>37.91</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6896315</th>\n",
       "      <td>2</td>\n",
       "      <td>2019-12-31 23:37:29</td>\n",
       "      <td>2020-01-01 00:28:21</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.66</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>90</td>\n",
       "      <td>74</td>\n",
       "      <td>0</td>\n",
       "      <td>36.45</td>\n",
       "      <td>2.75</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.3</td>\n",
       "      <td>39.50</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6896316</th>\n",
       "      <td>2</td>\n",
       "      <td>2019-12-31 23:09:00</td>\n",
       "      <td>2019-12-31 23:54:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-15.50</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>142</td>\n",
       "      <td>149</td>\n",
       "      <td>0</td>\n",
       "      <td>53.03</td>\n",
       "      <td>2.75</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.12</td>\n",
       "      <td>0.3</td>\n",
       "      <td>62.70</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         VendorID tpep_pickup_datetime tpep_dropoff_datetime  passenger_count  \\\n",
       "6896312         2  2019-12-31 23:56:29   2020-01-01 00:11:17              NaN   \n",
       "6896313         2  2019-12-31 23:11:53   2019-12-31 23:30:56              NaN   \n",
       "6896314         2  2019-12-31 23:57:21   2020-01-01 00:23:34              NaN   \n",
       "6896315         2  2019-12-31 23:37:29   2020-01-01 00:28:21              NaN   \n",
       "6896316         2  2019-12-31 23:09:00   2019-12-31 23:54:00              NaN   \n",
       "\n",
       "         trip_distance  RatecodeID store_and_fwd_flag  PULocationID  \\\n",
       "6896312           2.82         NaN               None           143   \n",
       "6896313           3.75         NaN               None           148   \n",
       "6896314           6.46         NaN               None           197   \n",
       "6896315           5.66         NaN               None            90   \n",
       "6896316         -15.50         NaN               None           142   \n",
       "\n",
       "         DOLocationID  payment_type  fare_amount  extra  mta_tax  tip_amount  \\\n",
       "6896312           141             0        18.95   2.75      0.0         0.0   \n",
       "6896313           246             0        22.45   2.75      0.0         0.0   \n",
       "6896314           205             0        34.86   2.75      0.0         0.0   \n",
       "6896315            74             0        36.45   2.75      0.0         0.0   \n",
       "6896316           149             0        53.03   2.75      0.5         0.0   \n",
       "\n",
       "         tolls_amount  improvement_surcharge  total_amount  \\\n",
       "6896312          0.00                    0.3         22.00   \n",
       "6896313          0.00                    0.3         25.50   \n",
       "6896314          0.00                    0.3         37.91   \n",
       "6896315          0.00                    0.3         39.50   \n",
       "6896316          6.12                    0.3         62.70   \n",
       "\n",
       "         congestion_surcharge airport_fee  \n",
       "6896312                   NaN        None  \n",
       "6896313                   NaN        None  \n",
       "6896314                   NaN        None  \n",
       "6896315                   NaN        None  \n",
       "6896316                   NaN        None  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This now works!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Working with the data\n",
    "\n",
    "The same computation (to calculate mean for the tip amount as a function of passenger count) is now performed on the entire dataset using Dask DataFrame.\n",
    "\n",
    "*Note that Dask code is similar to pandas code.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 5.54 ms, sys: 1.69 ms, total: 7.24 ms\n",
      "Wall time: 6.61 ms\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "Dask Series Structure:\n",
       "npartitions=1\n",
       "    float64\n",
       "        ...\n",
       "Name: tip_amount, dtype: float64\n",
       "Dask Name: truediv, 6 graph layers"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%time\n",
    "\n",
    "mean_tip_amount = df.groupby(\"passenger_count\").tip_amount.mean()\n",
    "mean_tip_amount"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Dask DataFrame is backed by the Delayed API we saw in the previous notebook, so the evaluations here are also lazy.\n",
    "\n",
    "You can use `compute()` to get the output."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time\n",
    "\n",
    "mean_tip_amount.compute()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Dask deletes intermediate results, like the full pandas dataframe for each file. This lets us handle datasets that are larger than memory, but also means that repeated computations will have to load all of the data in each time.\n",
    "\n",
    "You can use `persist()` to store intermediate results for future use:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "mean_tip_persist = mean_tip_amount.persist()\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Checkpoint"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Question:** Compute the standard deviation for tip_amount as a function of passenger_count for the entire dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#your answer here"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "jupyter": {
     "source_hidden": true
    }
   },
   "outputs": [],
   "source": [
    "# Solution 1\n",
    "\n",
    "std_tip = df.groupby(\"passenger_count\").tip_amount.std().compute()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Sharing intermediate outputs"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Sometimes individual computations may related to each other, and can benefit from sharing intermediate results. For example, computing minimum and maximum values."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In pandas (and therefore in Dask DataFrame), you can use `min()` and `max()` to compute minimum and maximum respectively."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "max_tip_amount = df.tip_amount.max()\n",
    "min_tip_amount = df.tip_amount.min()\n",
    "median_tip_amount = df.tip_amount.median()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Without Sharing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 1min 16s, sys: 2.13 s, total: 1min 18s\n",
      "Wall time: 1min 33s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "max_tip = max_tip_amount.compute()\n",
    "min_tip = min_tip_amount.compute()\n",
    "median_tip = median_tip_amount.compute()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### With Sharing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "import dask"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 47.6 s, sys: 1.21 s, total: 48.8 s\n",
      "Wall time: 51.8 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "max_tip, min_tip = dask.compute(max_tip_amount, min_tip_amount)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice the shared computation is significantly faster!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Checkpoint"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Question:** Compute the mean and standard deviation for total amount by sharing intermediate results."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#your answer here"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "jupyter": {
     "source_hidden": true
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Solution 2\n",
    "\n",
    "import dask\n",
    "\n",
    "mean_total = df.total_amount.mean()\n",
    "std_total = df.total_amount.mean()\n",
    "\n",
    "dask.compute(mean_total, std_total)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "client.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Scaling to the Cloud (Optional)\n",
    "\n",
    "We can now scale our Dask workflow to the cloud. There are many different ways to do this, but here we'll use [Coiled](https://www.coiled.io/). Coiled allows us to stay in this same notebook and makes the process much easier (see the [Coiled documentation](https://docs.coiled.io/user_guide/index.html)).\n",
    "\n",
    "1. Sign in to [cloud.coiled.io](https://cloud.coiled.io/)\n",
    "2. In your terminal (or command prompt in Windows) run `coiled login`\n",
    "4. Set up Coiled with your cloud provider account by running `coiled setup wizard`\n",
    "\n",
    "*Coiled is free to start!*\n",
    "\n",
    "That's it! Now in the same notebook, let's connect to our Coiled cluster."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import coiled\n",
    "\n",
    "cluster = coiled.Cluster(\n",
    "    name=\"talkpython\",\n",
    "    n_workers=10,\n",
    "    # uncomment if you're running on binder\n",
    "    # scheduler_port=443\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "from dask.distributed import Client\n",
    "\n",
    "client = Client(cluster)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "import dask.dataframe as dd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = dd.read_parquet(\n",
    "    \"s3://nyc-tlc/trip data/yellow_tripdata_2019-*.parquet\"\n",
    ")\n",
    "df = df.repartition(partition_size=\"100MB\").persist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "passenger_count\n",
       "0.0    2.122789\n",
       "1.0    2.206793\n",
       "2.0    2.214356\n",
       "3.0    2.137791\n",
       "4.0    2.023801\n",
       "5.0    2.235441\n",
       "6.0    2.221106\n",
       "7.0    6.675962\n",
       "8.0    7.111625\n",
       "9.0    7.377822\n",
       "Name: tip_amount, dtype: float64"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby(\"passenger_count\").tip_amount.mean().compute()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Close the cluster\n",
    "# Will close automatically after 20 minutes of inactivity\n",
    "cluster.close()\n",
    "\n",
    "# Close the client\n",
    "client.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Limitations of Dask DataFrame\n",
    "\n",
    "Dask DataFrame API does not implement the complete pandas interface because some pandas operations are not suited for a parallel and distributed environment.\n",
    "\n",
    "### Data Shuffling\n",
    "\n",
    "Dask DataFrames consist of multiple pandas dataframes, each of which has it's index starting from zero. Some operations like indexing (`set_index`, `reset_index`) may need the data to be sorted, which requires a lot of time-consuming shuffling of data. These operations are slower in Dask. Hence, presorting the index and making logical partitions are good practices.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## References\n",
    "\n",
    "* [Dask DataFrame documentation](https://docs.dask.org/en/latest/dataframe.html)\n",
    "* [Dask DataFrame API](https://docs.dask.org/en/latest/dataframe-api.html)\n",
    "* [Dask DataFrame examples](https://examples.dask.org/dataframe.html)\n",
    "* [Dask Tutorial - DataFrames](https://github.com/pavithraes/dask-tutorial/blob/master/04_dataframe.ipynb)"
   ]
  },
  {
   "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.9.15"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}