04-Dask/02.02-MovieLens-and-Dask.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "7e9f4107-83a1-4cf6-8340-9f9454b57eae",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "source": [
    "# Distributed Data Analysis with Dask  \n",
    "*__[Dask](https://www.dask.org/)__ with the __MovieLens__ dataset* \n",
    "\n",
    "**Part 2: Playing with the Movies data**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ffbfea8e-4c06-4664-95bf-c1a756e0d5f3",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "source": [
    "### <font color='green'>__Support for Google Colab__  </font>  \n",
    "    \n",
    "open this notebook in Colab using the following button:  \n",
    "  \n",
    "<a href=\"https://colab.research.google.com/github/shauryashaurya/learn-data-munging/blob/main/04-Dask/02.02-MovieLens-and-Dask.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>  \n",
    "\n",
    "**Note**: The Dask Dashboard is not available on Google Colab unless you register with tunnelling systems like Saturn Cloud or NGrok - these are both good approaches - for folks running this on colab I have not built support for bit for the workshop. Your contributions / PRs would be very welcome.\n",
    "  \n",
    "<font color='green'>uncomment and execute the cell below to setup and run this notebook on Google Colab.</font>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8e1de5f9-c3d2-4c7a-bf58-67a956247b5e",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "source": [
    "### Graphviz or Cytoscape (ipycytoscape)\n",
    "\n",
    "For some Dask exercises, we may want to visualize the task graph. \n",
    "To do so we'll need: [GraphViz](https://graphviz.org/) or [Cytoscape](https://cytoscape.org/download.html) and [ipycytoscape](https://ipycytoscape.readthedocs.io/en/latest/installing.html)\n",
    "\n",
    "These come with ready installers. Install, restart Jupyter if needed. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "2ccf9819-828f-4902-aa51-2a584b9cd52e",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# # SETUP FOR COLAB: select all the lines below and uncomment (CTRL+/ on windows)\n",
    "\n",
    "# # grab dask - in most cases it should already be available in colab\n",
    "# ! python -m pip install --quiet --upgrade --no-cache-dir \"dask[complete]\"\n",
    "# # Let's download and unzip the MovieLens 25M Dataset as well.\n",
    "# ! mkdir ./../data\n",
    "# ! wget -q https://files.grouplens.org/datasets/movielens/ml-25m.zip\n",
    "# ! unzip ./ml-25m.zip -d ./../data/\n",
    "\n",
    "# ! echo \"DONE\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e1f91f34-2408-4229-9061-13ea1da8a94a",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "source": [
    "**Citation**:  \n",
    "*F. Maxwell Harper and Joseph A. Konstan.* 2015.  \n",
    "The MovieLens Datasets: History and Context.  \n",
    "ACM Transactions on Interactive Intelligent Systems (TiiS) 5, 4: 19:1–19:19. <https://doi.org/10.1145/2827872>  "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "febcb8ad-e1a9-4495-8156-866cb2725f35",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "source": [
    "# Setup Dask, Pandas and Numpy"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ceb70cbe-695d-491d-ab3f-e8159248ea1c",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "source": [
    "## Setup the Dask Cluster"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f6eb3755-0b46-4cd2-a620-902ce0053e15",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "source": [
    "### Installation\n",
    "\n",
    "A local install is as simple as ```pip install \"dask[complete]\"```  \n",
    "  \n",
    "Unlike Spark, Dask is incredibly easy to setup - checkout [Dask Installation Docs](https://docs.dask.org/en/stable/install.html)  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "9b2f5d95-49dd-4d8d-8087-510e65fa8f45",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "numpy version:  1.26.2\n",
      "pandas version:  2.1.3\n"
     ]
    }
   ],
   "source": [
    "# Step 1: numpy and pandas\n",
    "\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "print(\"numpy version: \", np.__version__)\n",
    "print(\"pandas version: \", pd.__version__)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "56626fb8-a0d5-4a87-a80c-a603e3cc23a4",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "dask version:  2023.12.0\n"
     ]
    }
   ],
   "source": [
    "# Step 2: import dask and related\n",
    "import dask\n",
    "import dask.array as da\n",
    "import dask.bag as db\n",
    "import dask.dataframe as dd\n",
    "from dask.distributed import Client, LocalCluster\n",
    "\n",
    "print(\"dask version: \", dask.__version__)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "c52f3952-2237-487b-9999-3ee4f6b991e9",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Step 3: Create a Dask Cluster and a Client\n",
    "try:\n",
    "    cluster = LocalCluster()\n",
    "    # alternative, when you want to specify the dashboard address/port\n",
    "    # cluster = LocalCluster(dashboard_address = 'localhost:8786')\n",
    "    if cluster.shutdown_on_close == False:\n",
    "        cluster.shutdown_on_close = True\n",
    "except Exception:\n",
    "    pass\n",
    "#\n",
    "try:\n",
    "    client = Client(cluster)\n",
    "except Exception:\n",
    "    pass"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "2711459d-cd93-4aba-a01d-7dca834e68fd",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# see cluster information\n",
    "# cluster"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "861d1421-50aa-4f63-a8ac-d823b09b6522",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# see client information\n",
    "# client"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f7d6e612-ab92-4524-afae-2a0287734ffe",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "source": [
    "# Problem Set 1  - ```movies.csv```\n",
    "\n",
    "1. How many unique movies exist in the ```movies.csv``` dataset?  \n",
    "\n",
    "1. Prepare a yearwise list of movies - Extract the year of release in movies.csv into a new column year_of_release   "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "97dfd994-9edb-4149-925d-db722ab8b245",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "source": [
    "# Locate the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "4eb99b71-ca35-49de-8c86-e8f697e3dd07",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "datalocation = \"../data/ml-25m/\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "e4af7a8f-b196-4d0f-90b0-9648651cc33e",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# specify file names\n",
    "file_path_movies = datalocation + \"movies.csv\"\n",
    "file_path_links = datalocation + \"links.csv\"\n",
    "file_path_ratings = datalocation + \"ratings.csv\"\n",
    "file_path_tags = datalocation + \"tags.csv\"\n",
    "file_path_genome_tags = datalocation + \"genome-tags.csv\"\n",
    "file_path_genome_scores = datalocation + \"genome-scores.csv\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "396b95d1-219a-457b-b261-0c996f524430",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "source": [
    "# Load the dataset(s)\n",
    "\n",
    "From the ```README.txt``` file in the small MovieLens dataset:\n",
    "The dataset files are written as [**comma-separated values**](http://en.wikipedia.org/wiki/Comma-separated_values) files with a **single header row**. Columns that contain commas (`,`) are **escaped using double-quotes (`\"`)**. These files are encoded as **UTF-8**. If accented characters in movie titles or tag values (e.g. Misérables, Les (1995)) display incorrectly, make sure that any program reading the data, such as a text editor, terminal, or script, is configured for UTF-8.\n",
    "\n",
    "So, we specify:\n",
    "* Separator - ```,```\n",
    "* Escape Character - ```\"```\n",
    "* Encoding - ```UTF-8```\n",
    "\n",
    "Often this is called the **dialect** of the CSV file.\n",
    "These dialects vary often, so need our attention."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "2f37cdce-7b27-4e36-b9db-d37506181382",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# dask dataframes parallelize pandas dataframes\n",
    "# so many of the idioms are similar\n",
    "csv_separator = \",\"\n",
    "csv_escapechar = '\"'\n",
    "csv_quotechar = csv_escapechar\n",
    "csv_encoding = \"utf-8\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "54634dd5-8c1d-4688-88fa-bd39afa3cfa8",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "source": [
    "## Movies"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "824043b3-bfc5-487b-bc0c-835481f38979",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "source": [
    "Let's specify the [-  ```dtypes```  ](https://pandas.pydata.org/docs/user_guide/basics.html#dtypes) of each of the columns in the movies file. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "fc22406a-4c50-4485-afa4-d25417477807",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# schema, inferred from the README.txt file\n",
    "movies_schema = {\"movieId\": \"Int32\", \"title\": \"string\", \"genres\": \"string\"}"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "84778cdf-d82b-4c24-b46b-6769378b14ce",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "source": [
    "Two of the columns are [strings of text](https://pandas.pydata.org/docs/user_guide/text.html#working-with-text-data). Pandas may treat those as ```object```, but we wanted to use the [```pandas.StringDType```](https://pandas.pydata.org/docs/reference/api/pandas.StringDtype.html#pandas-stringdtype) here."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "f586d8bb-19ae-47e9-8c09-b6cf113ba4b4",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# we are using dd - dask.dataframe\n",
    "movies = dd.read_csv(\n",
    "    file_path_movies,\n",
    "    dtype=movies_schema,\n",
    "    sep=csv_separator,\n",
    "    quotechar=csv_quotechar,\n",
    "    encoding=csv_encoding,\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "521d5f6a-1be8-496b-9d75-ebd120a3475f",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "movieId              Int32\n",
       "title      string[pyarrow]\n",
       "genres     string[pyarrow]\n",
       "dtype: object"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# data types of each column\n",
    "movies.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "06241496-47c5-44bb-8ea9-78b8e9ff3ee6",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "source": [
    "# Solutions to Problem Set 1"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0cdd3ac2-3634-49e9-a3a2-46b572343d02",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "source": [
    "## How many unique movies?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "24751b59-67b2-4499-b017-5c9633e7a82d",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# lazy\n",
    "number_of_unique_movies_task = movies[\"title\"].nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "aa440251-7043-4991-96bb-17bd1d52be4c",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# you know you want to...\n",
    "# number_of_unique_movies_task.visualize()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "8bdd6b3f-5b55-48ed-aaf4-52b6ea410c3e",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "62325\n"
     ]
    }
   ],
   "source": [
    "# use compute() on scalar to get final value\n",
    "number_of_unique_movies = number_of_unique_movies_task.compute()\n",
    "print(number_of_unique_movies)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a45e8c61-3c6a-4d63-9039-5a784a810140",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "source": [
    "#### What if we just needed to count the number of rows in the dataset?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "8cad8309-cc63-40c9-903f-7c00ceee34e4",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "62423"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# shape property helps\n",
    "# compute number of rows\n",
    "# needs to be collected across all workers\n",
    "movies.shape[0].compute()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "c331d6fa-b3eb-4735-9e28-53a4d960730f",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# compute number of columns\n",
    "# consistent across all workers\n",
    "movies.shape[1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "a9d375f1-0998-47e7-9f63-a47bcfd65d04",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "number_of_rows_in_movies = movies.shape[0].compute()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "a7e27dad-1e52-4dcc-abd4-cc5ef845250c",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "62423"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "number_of_rows_in_movies"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "6172f081-cc38-4b2f-adaf-df89f97e189c",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "98\n"
     ]
    }
   ],
   "source": [
    "# seems like some movie titles\n",
    "print(number_of_rows_in_movies - number_of_unique_movies)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "40196303-acaa-4fb5-9ba1-9e508649e096",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "source": [
    "#### Of course we need to find these..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "9da59bb5-439c-4a0e-8f70-108e718d44ed",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "movie_title_counts = (\n",
    "    movies.groupby(by=\"title\").count().sort_values(by=\"movieId\", ascending=False)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "85f5a475-f3b3-427e-9448-b82c2a0e0a62",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# as expected, this one's kinda straightforward\n",
    "# movie_title_counts.visualize()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "83c73e23-5b30-4070-b5c0-c6058dce671f",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "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>movieId</th>\n",
       "      <th>genres</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>title</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>The Plague (2006)</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Seven Years Bad Luck (1921)</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Another World (2014)</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Interrogation (2016)</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sing (2016)</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                             movieId  genres\n",
       "title                                       \n",
       "The Plague (2006)                  2       2\n",
       "Seven Years Bad Luck (1921)        2       2\n",
       "Another World (2014)               2       2\n",
       "Interrogation (2016)               2       2\n",
       "Sing (2016)                        2       2"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movie_title_counts.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "d4927ec1-7639-4c0b-9b38-9d7fdcccd309",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "repeating_titles = movie_title_counts[movie_title_counts[\"movieId\"] > 1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "c64bd08f-c4fd-49b6-9cf0-49a37a11a1e2",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "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>movieId</th>\n",
       "      <th>genres</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>title</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>The Plague (2006)</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Seven Years Bad Luck (1921)</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Another World (2014)</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Interrogation (2016)</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sing (2016)</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                             movieId  genres\n",
       "title                                       \n",
       "The Plague (2006)                  2       2\n",
       "Seven Years Bad Luck (1921)        2       2\n",
       "Another World (2014)               2       2\n",
       "Interrogation (2016)               2       2\n",
       "Sing (2016)                        2       2"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "repeating_titles.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "1fa9663a-ccc2-4d63-85a5-e65126d9fec9",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "98"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "repeating_titles.shape[0].compute()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4dfb882f-9710-4ea0-9669-394813bb3807",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "source": [
    "## Year-wise list of movies \n",
    "- Extract the year of release in movies.csv into a new column year_of_release"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "dc7acb2d-197c-470f-a467-85d4e04a032d",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "No of groups in the regex:  3\n"
     ]
    }
   ],
   "source": [
    "import re\n",
    "\n",
    "# regex:\n",
    "# 1st capture group: match a single (\n",
    "# 2nd capture group: match exactly 4 digits\n",
    "# 3rd capture group: match a single )\n",
    "# at the end of the string\n",
    "year_regex_pattern = \"([(])([0-9]{4})([)]$)\"\n",
    "# alternative: use \\d{4} instead of [0-9]\n",
    "print(\"No of groups in the regex: \", re.compile(year_regex_pattern).groups)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "9ab6912d-5604-441a-a15d-e18bc04a7742",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# just like pandas except lazy\n",
    "movies[\"year\"] = dd.to_numeric(\n",
    "    movies[\"title\"].str.extract(year_regex_pattern, flags=re.X, expand=False)[1]\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "2e15468d-0010-4f8e-b808-511495f9cdfa",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# dont'ask me to do it...\n",
    "# movies.visualize()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "f067eaa5-43b1-41d4-946d-36cafa887c8b",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "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>movieId</th>\n",
       "      <th>title</th>\n",
       "      <th>genres</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Toy Story (1995)</td>\n",
       "      <td>Adventure|Animation|Children|Comedy|Fantasy</td>\n",
       "      <td>1995</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Jumanji (1995)</td>\n",
       "      <td>Adventure|Children|Fantasy</td>\n",
       "      <td>1995</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Grumpier Old Men (1995)</td>\n",
       "      <td>Comedy|Romance</td>\n",
       "      <td>1995</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Waiting to Exhale (1995)</td>\n",
       "      <td>Comedy|Drama|Romance</td>\n",
       "      <td>1995</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Father of the Bride Part II (1995)</td>\n",
       "      <td>Comedy</td>\n",
       "      <td>1995</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>Heat (1995)</td>\n",
       "      <td>Action|Crime|Thriller</td>\n",
       "      <td>1995</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>Sabrina (1995)</td>\n",
       "      <td>Comedy|Romance</td>\n",
       "      <td>1995</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>Tom and Huck (1995)</td>\n",
       "      <td>Adventure|Children</td>\n",
       "      <td>1995</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>Sudden Death (1995)</td>\n",
       "      <td>Action</td>\n",
       "      <td>1995</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10</td>\n",
       "      <td>GoldenEye (1995)</td>\n",
       "      <td>Action|Adventure|Thriller</td>\n",
       "      <td>1995</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   movieId                               title  \\\n",
       "0        1                    Toy Story (1995)   \n",
       "1        2                      Jumanji (1995)   \n",
       "2        3             Grumpier Old Men (1995)   \n",
       "3        4            Waiting to Exhale (1995)   \n",
       "4        5  Father of the Bride Part II (1995)   \n",
       "5        6                         Heat (1995)   \n",
       "6        7                      Sabrina (1995)   \n",
       "7        8                 Tom and Huck (1995)   \n",
       "8        9                 Sudden Death (1995)   \n",
       "9       10                    GoldenEye (1995)   \n",
       "\n",
       "                                        genres  year  \n",
       "0  Adventure|Animation|Children|Comedy|Fantasy  1995  \n",
       "1                   Adventure|Children|Fantasy  1995  \n",
       "2                               Comedy|Romance  1995  \n",
       "3                         Comedy|Drama|Romance  1995  \n",
       "4                                       Comedy  1995  \n",
       "5                        Action|Crime|Thriller  1995  \n",
       "6                               Comedy|Romance  1995  \n",
       "7                           Adventure|Children  1995  \n",
       "8                                       Action  1995  \n",
       "9                    Action|Adventure|Thriller  1995  "
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movies[movies[\"year\"] == 1995].head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "c5a94969-338e-432e-b640-0c80ca7840f8",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "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>movieId</th>\n",
       "      <th>title</th>\n",
       "      <th>genres</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>867</th>\n",
       "      <td>887</td>\n",
       "      <td>Talk of Angels (1998)</td>\n",
       "      <td>Drama</td>\n",
       "      <td>1998</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1541</th>\n",
       "      <td>1598</td>\n",
       "      <td>Desperate Measures (1998)</td>\n",
       "      <td>Crime|Drama|Thriller</td>\n",
       "      <td>1998</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1593</th>\n",
       "      <td>1655</td>\n",
       "      <td>Phantoms (1998)</td>\n",
       "      <td>Drama|Horror|Thriller</td>\n",
       "      <td>1998</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1617</th>\n",
       "      <td>1679</td>\n",
       "      <td>Chairman of the Board (1998)</td>\n",
       "      <td>Comedy</td>\n",
       "      <td>1998</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1618</th>\n",
       "      <td>1680</td>\n",
       "      <td>Sliding Doors (1998)</td>\n",
       "      <td>Drama|Romance</td>\n",
       "      <td>1998</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1620</th>\n",
       "      <td>1682</td>\n",
       "      <td>Truman Show, The (1998)</td>\n",
       "      <td>Comedy|Drama|Sci-Fi</td>\n",
       "      <td>1998</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1661</th>\n",
       "      <td>1727</td>\n",
       "      <td>Horse Whisperer, The (1998)</td>\n",
       "      <td>Drama|Romance</td>\n",
       "      <td>1998</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1666</th>\n",
       "      <td>1732</td>\n",
       "      <td>Big Lebowski, The (1998)</td>\n",
       "      <td>Comedy|Crime</td>\n",
       "      <td>1998</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1669</th>\n",
       "      <td>1735</td>\n",
       "      <td>Great Expectations (1998)</td>\n",
       "      <td>Drama|Romance</td>\n",
       "      <td>1998</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1670</th>\n",
       "      <td>1738</td>\n",
       "      <td>Vermin (1998)</td>\n",
       "      <td>Comedy</td>\n",
       "      <td>1998</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      movieId                         title                 genres  year\n",
       "867       887         Talk of Angels (1998)                  Drama  1998\n",
       "1541     1598     Desperate Measures (1998)   Crime|Drama|Thriller  1998\n",
       "1593     1655               Phantoms (1998)  Drama|Horror|Thriller  1998\n",
       "1617     1679  Chairman of the Board (1998)                 Comedy  1998\n",
       "1618     1680          Sliding Doors (1998)          Drama|Romance  1998\n",
       "1620     1682       Truman Show, The (1998)    Comedy|Drama|Sci-Fi  1998\n",
       "1661     1727   Horse Whisperer, The (1998)          Drama|Romance  1998\n",
       "1666     1732      Big Lebowski, The (1998)           Comedy|Crime  1998\n",
       "1669     1735     Great Expectations (1998)          Drama|Romance  1998\n",
       "1670     1738                 Vermin (1998)                 Comedy  1998"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movies[movies[\"year\"] == 1998].head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "ae34a6d2-ffdb-4cd8-8d05-64396062eeb7",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "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>movieId</th>\n",
       "      <th>title</th>\n",
       "      <th>genres</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>12536</th>\n",
       "      <td>60684</td>\n",
       "      <td>Watchmen (2009)</td>\n",
       "      <td>Action|Drama|Mystery|Sci-Fi|Thriller|IMAX</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12695</th>\n",
       "      <td>62265</td>\n",
       "      <td>Accidental Husband, The (2009)</td>\n",
       "      <td>Comedy|Romance</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12756</th>\n",
       "      <td>63072</td>\n",
       "      <td>Road, The (2009)</td>\n",
       "      <td>Adventure|Drama|Thriller</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12970</th>\n",
       "      <td>65585</td>\n",
       "      <td>Bride Wars (2009)</td>\n",
       "      <td>Comedy|Romance</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12973</th>\n",
       "      <td>65601</td>\n",
       "      <td>My Bloody Valentine 3-D (2009)</td>\n",
       "      <td>Horror|Thriller</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12988</th>\n",
       "      <td>65682</td>\n",
       "      <td>Underworld: Rise of the Lycans (2009)</td>\n",
       "      <td>Action|Fantasy|Horror|Thriller</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13004</th>\n",
       "      <td>65802</td>\n",
       "      <td>Paul Blart: Mall Cop (2009)</td>\n",
       "      <td>Action|Comedy|Crime</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13005</th>\n",
       "      <td>65810</td>\n",
       "      <td>Notorious (2009)</td>\n",
       "      <td>Drama|Musical</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13006</th>\n",
       "      <td>65813</td>\n",
       "      <td>Unborn, The (2009)</td>\n",
       "      <td>Horror|Mystery|Thriller</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13012</th>\n",
       "      <td>65882</td>\n",
       "      <td>Uninvited, The (2009)</td>\n",
       "      <td>Drama|Horror|Mystery|Thriller</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       movieId                                  title  \\\n",
       "12536    60684                        Watchmen (2009)   \n",
       "12695    62265         Accidental Husband, The (2009)   \n",
       "12756    63072                       Road, The (2009)   \n",
       "12970    65585                      Bride Wars (2009)   \n",
       "12973    65601         My Bloody Valentine 3-D (2009)   \n",
       "12988    65682  Underworld: Rise of the Lycans (2009)   \n",
       "13004    65802            Paul Blart: Mall Cop (2009)   \n",
       "13005    65810                       Notorious (2009)   \n",
       "13006    65813                     Unborn, The (2009)   \n",
       "13012    65882                  Uninvited, The (2009)   \n",
       "\n",
       "                                          genres  year  \n",
       "12536  Action|Drama|Mystery|Sci-Fi|Thriller|IMAX  2009  \n",
       "12695                             Comedy|Romance  2009  \n",
       "12756                   Adventure|Drama|Thriller  2009  \n",
       "12970                             Comedy|Romance  2009  \n",
       "12973                            Horror|Thriller  2009  \n",
       "12988             Action|Fantasy|Horror|Thriller  2009  \n",
       "13004                        Action|Comedy|Crime  2009  \n",
       "13005                              Drama|Musical  2009  \n",
       "13006                    Horror|Mystery|Thriller  2009  \n",
       "13012              Drama|Horror|Mystery|Thriller  2009  "
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movies[movies[\"year\"] == 2009].head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8cb379a2-0cf7-43a3-84ea-ffaf694455d8",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "source": [
    "#### How many movies each year?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "48df2f66-c0f1-4b43-a731-aa9265b3e4a1",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# using movies - knowing that 98 rows are duplicate\n",
    "# todo - build a 'unique' only dataframe and use it for further analysis\n",
    "\n",
    "movies_each_year = (\n",
    "    movies.groupby(by=\"year\").count().sort_values(by=\"movieId\", ascending=False)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "311ecfd1-e785-46b3-a1a2-eec8b63fbb7d",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "135"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movies_each_year.shape[0].compute()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "6ddeda98-1874-4ddb-9605-6d75adc26798",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "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>movieId</th>\n",
       "      <th>title</th>\n",
       "      <th>genres</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>year</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2015</th>\n",
       "      <td>2513</td>\n",
       "      <td>2513</td>\n",
       "      <td>2513</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016</th>\n",
       "      <td>2488</td>\n",
       "      <td>2488</td>\n",
       "      <td>2488</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014</th>\n",
       "      <td>2403</td>\n",
       "      <td>2403</td>\n",
       "      <td>2403</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017</th>\n",
       "      <td>2374</td>\n",
       "      <td>2374</td>\n",
       "      <td>2374</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013</th>\n",
       "      <td>2164</td>\n",
       "      <td>2164</td>\n",
       "      <td>2164</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1878</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1887</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1883</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1880</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1874</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>135 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      movieId  title  genres\n",
       "year                        \n",
       "2015     2513   2513    2513\n",
       "2016     2488   2488    2488\n",
       "2014     2403   2403    2403\n",
       "2017     2374   2374    2374\n",
       "2013     2164   2164    2164\n",
       "...       ...    ...     ...\n",
       "1878        1      1       1\n",
       "1887        1      1       1\n",
       "1883        1      1       1\n",
       "1880        1      1       1\n",
       "1874        1      1       1\n",
       "\n",
       "[135 rows x 3 columns]"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movies_each_year.head(135)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "551e8977-5875-4428-8b42-79a19af9d0f7",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "source": [
    "# Bonus Round   \n",
    "## Find out how many times the word 'The' appears across all the titles in the movies dataset.  "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d60b554d-674f-4368-a2d7-61e4491aec81",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "source": [
    "Hint: this is just like the UDFs we discovered in Spark"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "20e2d2e0-cea9-4277-9d86-c20e95ab9b1c",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0                      Toy Story (1995)\n",
       "1                        Jumanji (1995)\n",
       "2               Grumpier Old Men (1995)\n",
       "3              Waiting to Exhale (1995)\n",
       "4    Father of the Bride Part II (1995)\n",
       "Name: title, dtype: string"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movies['title'].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "d22e573e-3a38-49e9-8a56-cee6c6058469",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# custom function\n",
    "def count_the(title):\n",
    "\t# split title by space, case insensitive\n",
    "\twords_in_title = str(title).lower().split(' ')\n",
    "\t# return the count of the word 'the'\n",
    "\treturn words_in_title.count('the')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "bcf8a4cd-320c-4758-a6c0-669e8225923f",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "count_of_the = movies['title'].map(count_the).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "aa95f444-164c-4cba-a7f6-0fcdc8720eec",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# what!?!\n",
    "# count_of_the.visualize()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "bcf1215f-84eb-4ba4-8fdb-6bc7c920f72c",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "19583"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "count_of_the.compute()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dab03e75-c0c5-4c88-bb65-6152de06d926",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "source": [
    "# Wrap Up the cluster"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e3e94e31-72f8-4d57-854e-98c4487087c0",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# wrap up like this\n",
    "client.retire_workers()\n",
    "# QQ - do we really need cluster.close() here?\n",
    "# cluster.close()\n",
    "client.shutdown()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6fb9e732-7360-4500-a27d-d00d9b9f6302",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "source": [
    "# Insights"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7279aa60-997e-48d8-ba69-46e283b7f662",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "source": [
    "* Things are very much Pandas like\n",
    "* but *lazy* - use ```compute()```, it helps.\n",
    "* ```.shape[0].compute()``` to get all the rows - because rows are distributed across workers\n",
    "* ```.shape[1]``` to get all columns - these are consistent, so don't need compute"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e9cd9648-5a13-446f-a775-76616ace03c1",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "source": [
    "# Next"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f30f9979-d5c1-4fa6-875d-76e2c017ec76",
   "metadata": {},
   "source": [
    "* Let's play with the MovieLens dataset some more."
   ]
  }
 ],
 "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.11.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}