02-Pandas/02.02-Data-Wrangling-with-MovieLens-and-Pandas.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "7e9f4107-83a1-4cf6-8340-9f9454b57eae",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "source": [
    "# Data Wrangling in Python  \n",
    "*__[Pandas](https://pandas.pydata.org/)__ with the __MovieLens__ dataset*  \n",
    "\n",
    "**Part 2: Playing with the Movies and Ratings data**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f21bef00-086e-44fd-b516-01016a08432d",
   "metadata": {},
   "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/02-Pandas/02.02-Data-Wrangling-with-MovieLens-and-Pandas.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>  \n",
    "\n",
    "  \n",
    "<font color='green'>uncomment and execute the cell below to setup and run this notebook on Google Colab.</font>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "cbba6f93-2b10-45bf-9975-a41d30ffd219",
   "metadata": {},
   "outputs": [],
   "source": [
    "# # SETUP FOR COLAB: select all the lines below and uncomment (CTRL+/ on windows)\n",
    "# # Let's download and unzip the Small MovieLens Dataset\n",
    "# ! mkdir ./../data\n",
    "# ! wget -q https://files.grouplens.org/datasets/movielens/ml-latest-small.zip\n",
    "# ! unzip ./ml-latest-small.zip -d ./../data/"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "868129c9-cedd-4513-997b-53f9427b6f1a",
   "metadata": {},
   "source": [
    "### Get the _Small_ MovieLens Dataset"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "67afc9a9-31b8-483d-89c1-699867c8700b",
   "metadata": {},
   "source": [
    "We'll use the [small MovieLens dataset](https://grouplens.org/datasets/movielens/#:~:text=Small%3A%20100%2C000%20ratings%20and%203%2C600%20tag%20applications) here.\n",
    "\n",
    "Download it and unzip to the data folder under the name `ml-latest-small`.\n",
    "\n",
    "This dataset expands to about 3.2 MB on your local disk. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cb164ece-2d5e-4451-b455-24fe2add5bee",
   "metadata": {},
   "source": [
    "# Locate the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "4a045b14-f578-47ad-9972-a3f5aaba0911",
   "metadata": {},
   "outputs": [],
   "source": [
    "datalocation = \"./../data/ml-latest-small/\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "0428c276-5ef7-4bde-addf-3bb79337783d",
   "metadata": {},
   "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\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3b518bef-b08d-40ff-b63b-7395f06de060",
   "metadata": {},
   "source": [
    "# Setup Pandas and Numpy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "53c0617c-fe63-4ac9-8131-dc20a9220e2b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "numpy version:  1.26.2\n",
      "pandas version:  2.1.4\n"
     ]
    }
   ],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "print(\"numpy version: \", np.__version__)\n",
    "print(\"pandas version: \", pd.__version__)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "96574358-90c0-4eed-8f4e-1dd5692bd60c",
   "metadata": {},
   "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",
    "We saw in the last notebook that what the README file really meant was that the **Quote Character** is ```\"```, so additionally:  \n",
    "* Quote Character - ```\"```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "51505e23-1133-4302-beaf-1690963ec807",
   "metadata": {},
   "outputs": [],
   "source": [
    "csv_separator = \",\"\n",
    "csv_escapechar = '\"'\n",
    "csv_encoding = \"utf-8\"\n",
    "csv_quotechar = csv_escapechar"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "503f86f0-c81b-4f2c-80ab-090afbe049d2",
   "metadata": {},
   "source": [
    "## Movies"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2583f6a3-72c2-4414-9c12-b56ac69fb00a",
   "metadata": {},
   "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": 6,
   "id": "c9a1e502-96a1-4ae1-91e9-cba5e695e7a1",
   "metadata": {},
   "outputs": [],
   "source": [
    "# schema, inferred from the README.txt file\n",
    "movies_schema = {\"movieId\": \"Int32\", \"title\": \"string\", \"genres\": \"string\"}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "5c90ad76-3592-4ed7-8c9f-fc67b94648d2",
   "metadata": {},
   "outputs": [],
   "source": [
    "movies = pd.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": 8,
   "id": "f1b032d0-cd24-4aa7-8d98-422ba8d4900e",
   "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>movieId</th>\n",
       "      <th>title</th>\n",
       "      <th>genres</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Jumanji (1995)</td>\n",
       "      <td>Adventure|Children|Fantasy</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",
       "    </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",
       "    </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",
       "    </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",
       "\n",
       "                                        genres  \n",
       "0  Adventure|Animation|Children|Comedy|Fantasy  \n",
       "1                   Adventure|Children|Fantasy  \n",
       "2                               Comedy|Romance  \n",
       "3                         Comedy|Drama|Romance  \n",
       "4                                       Comedy  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# show the first 15 lines\n",
    "movies.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "521d5f6a-1be8-496b-9d75-ebd120a3475f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "movieId             Int32\n",
       "title      string[python]\n",
       "genres     string[python]\n",
       "dtype: object"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# data types of each column\n",
    "movies.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "25e7b872-1db9-49c9-9288-d327cae2fdf1",
   "metadata": {},
   "source": [
    "## Ratings"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "342bbe4a-c904-4313-8b31-252dc87cda76",
   "metadata": {},
   "source": [
    "Reading through the ```README``` file:  \n",
    "Ratings are made on a 5-star scale, with half-star increments (0.5 stars - 5.0 stars).  \n",
    "Timestamps represent seconds since midnight Coordinated Universal Time (UTC) of January 1, 1970.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "672561a7-1b7f-4168-aaec-85245faec9b0",
   "metadata": {},
   "outputs": [],
   "source": [
    "# schema, inferred from the README.txt file\n",
    "# read timestamps as integers then convert to dates later.\n",
    "ratings_schema = {\n",
    "    \"userId\": \"Int32\",\n",
    "    \"movieId\": \"Int32\",\n",
    "    \"rating\": \"Float32\",\n",
    "    \"timestamp\": \"Int64\",\n",
    "}\n",
    "#"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "5d102b11-3aca-43ef-977a-21c67ca7f610",
   "metadata": {},
   "outputs": [],
   "source": [
    "ratings = pd.read_csv(\n",
    "    file_path_ratings,\n",
    "    dtype=ratings_schema,\n",
    "    sep=csv_separator,\n",
    "    quotechar=csv_quotechar,\n",
    "    encoding=csv_encoding,\n",
    ")\n",
    "\n",
    "# now let's add a datetime column that we derive from the raw timestamp\n",
    "ratings[\"datetime\"] = pd.to_datetime(ratings[\"timestamp\"], unit=\"s\", utc=True)\n",
    "ratings[\"date\"] = pd.to_datetime(ratings[\"datetime\"].dt.date)\n",
    "ratings[\"day\"] = ratings[\"date\"].dt.day\n",
    "ratings[\"month\"] = ratings[\"date\"].dt.month\n",
    "ratings[\"year\"] = ratings[\"date\"].dt.year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "5f36be99-fb8b-48d4-897a-d1076092022c",
   "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>userId</th>\n",
       "      <th>movieId</th>\n",
       "      <th>rating</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>datetime</th>\n",
       "      <th>date</th>\n",
       "      <th>day</th>\n",
       "      <th>month</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4.0</td>\n",
       "      <td>964982703</td>\n",
       "      <td>2000-07-30 18:45:03+00:00</td>\n",
       "      <td>2000-07-30</td>\n",
       "      <td>30</td>\n",
       "      <td>7</td>\n",
       "      <td>2000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>4.0</td>\n",
       "      <td>964981247</td>\n",
       "      <td>2000-07-30 18:20:47+00:00</td>\n",
       "      <td>2000-07-30</td>\n",
       "      <td>30</td>\n",
       "      <td>7</td>\n",
       "      <td>2000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>4.0</td>\n",
       "      <td>964982224</td>\n",
       "      <td>2000-07-30 18:37:04+00:00</td>\n",
       "      <td>2000-07-30</td>\n",
       "      <td>30</td>\n",
       "      <td>7</td>\n",
       "      <td>2000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>47</td>\n",
       "      <td>5.0</td>\n",
       "      <td>964983815</td>\n",
       "      <td>2000-07-30 19:03:35+00:00</td>\n",
       "      <td>2000-07-30</td>\n",
       "      <td>30</td>\n",
       "      <td>7</td>\n",
       "      <td>2000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>50</td>\n",
       "      <td>5.0</td>\n",
       "      <td>964982931</td>\n",
       "      <td>2000-07-30 18:48:51+00:00</td>\n",
       "      <td>2000-07-30</td>\n",
       "      <td>30</td>\n",
       "      <td>7</td>\n",
       "      <td>2000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   userId  movieId  rating  timestamp                  datetime       date  \\\n",
       "0       1        1     4.0  964982703 2000-07-30 18:45:03+00:00 2000-07-30   \n",
       "1       1        3     4.0  964981247 2000-07-30 18:20:47+00:00 2000-07-30   \n",
       "2       1        6     4.0  964982224 2000-07-30 18:37:04+00:00 2000-07-30   \n",
       "3       1       47     5.0  964983815 2000-07-30 19:03:35+00:00 2000-07-30   \n",
       "4       1       50     5.0  964982931 2000-07-30 18:48:51+00:00 2000-07-30   \n",
       "\n",
       "   day  month  year  \n",
       "0   30      7  2000  \n",
       "1   30      7  2000  \n",
       "2   30      7  2000  \n",
       "3   30      7  2000  \n",
       "4   30      7  2000  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ratings.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "5fda8643-8179-4e79-9c56-663a66830e9c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# now let's add a datetime column that we derive from the raw timestamp\n",
    "ratings[\"datetime\"] = pd.to_datetime(ratings[\"timestamp\"], unit=\"s\", utc=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "3c1a2a42-ed8b-4a45-a90c-a8e97d5b7cbd",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "userId                    Int32\n",
       "movieId                   Int32\n",
       "rating                  Float32\n",
       "timestamp                 Int64\n",
       "datetime     datetime64[s, UTC]\n",
       "date             datetime64[ns]\n",
       "day                       int32\n",
       "month                     int32\n",
       "year                      int32\n",
       "dtype: object"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ratings.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a7b57798-6cab-47b1-8cad-da71d7735d41",
   "metadata": {},
   "source": [
    "let's [extract the dates](https://pandas.pydata.org/docs/reference/api/pandas.Series.dt.date.html#pandas-series-dt-date) into a new column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "b634931a-1b8f-48d2-a399-b932dcba0cd7",
   "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>userId</th>\n",
       "      <th>movieId</th>\n",
       "      <th>rating</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>datetime</th>\n",
       "      <th>date</th>\n",
       "      <th>day</th>\n",
       "      <th>month</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4.0</td>\n",
       "      <td>964982703</td>\n",
       "      <td>2000-07-30 18:45:03+00:00</td>\n",
       "      <td>2000-07-30</td>\n",
       "      <td>30</td>\n",
       "      <td>7</td>\n",
       "      <td>2000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>4.0</td>\n",
       "      <td>964981247</td>\n",
       "      <td>2000-07-30 18:20:47+00:00</td>\n",
       "      <td>2000-07-30</td>\n",
       "      <td>30</td>\n",
       "      <td>7</td>\n",
       "      <td>2000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>4.0</td>\n",
       "      <td>964982224</td>\n",
       "      <td>2000-07-30 18:37:04+00:00</td>\n",
       "      <td>2000-07-30</td>\n",
       "      <td>30</td>\n",
       "      <td>7</td>\n",
       "      <td>2000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>47</td>\n",
       "      <td>5.0</td>\n",
       "      <td>964983815</td>\n",
       "      <td>2000-07-30 19:03:35+00:00</td>\n",
       "      <td>2000-07-30</td>\n",
       "      <td>30</td>\n",
       "      <td>7</td>\n",
       "      <td>2000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>50</td>\n",
       "      <td>5.0</td>\n",
       "      <td>964982931</td>\n",
       "      <td>2000-07-30 18:48:51+00:00</td>\n",
       "      <td>2000-07-30</td>\n",
       "      <td>30</td>\n",
       "      <td>7</td>\n",
       "      <td>2000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   userId  movieId  rating  timestamp                  datetime       date  \\\n",
       "0       1        1     4.0  964982703 2000-07-30 18:45:03+00:00 2000-07-30   \n",
       "1       1        3     4.0  964981247 2000-07-30 18:20:47+00:00 2000-07-30   \n",
       "2       1        6     4.0  964982224 2000-07-30 18:37:04+00:00 2000-07-30   \n",
       "3       1       47     5.0  964983815 2000-07-30 19:03:35+00:00 2000-07-30   \n",
       "4       1       50     5.0  964982931 2000-07-30 18:48:51+00:00 2000-07-30   \n",
       "\n",
       "   day  month  year  \n",
       "0   30      7  2000  \n",
       "1   30      7  2000  \n",
       "2   30      7  2000  \n",
       "3   30      7  2000  \n",
       "4   30      7  2000  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ratings.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4f5fe549-ec90-49dd-bfc8-a36e134d45fb",
   "metadata": {},
   "source": [
    "# Problem Set 1\n",
    "\n",
    "1. That comma and quotechar thing... find out how many titles in movies data set have commas in them?\n",
    "1. Can we extract the year of release from the movie title and put it in a separate column?\n",
    "1. How many movies in the data set from each year? How many from each decade?\n",
    "1. Can we calculate an average rating for each movie?\n",
    "1. How many times was each movie rated? Is there a wide margin between the number of ratings one movie has recieved vs another? \n",
    "1. Is there a way I can query a movieID and get it's title and average rating back? "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8f820bac-1643-4735-b8e9-3caba4651bf0",
   "metadata": {},
   "source": [
    "## Solutions to Problem Set 1"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4560bafb-5d9c-4068-b245-4da590a1bc6f",
   "metadata": {},
   "source": [
    "### How many titles in movies data set have commas in them?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "9d58ea87-2585-470e-8280-9335c90a1807",
   "metadata": {},
   "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",
       "5                            Heat (1995)\n",
       "6                         Sabrina (1995)\n",
       "7                    Tom and Huck (1995)\n",
       "8                    Sudden Death (1995)\n",
       "9                       GoldenEye (1995)\n",
       "10        American President, The (1995)\n",
       "11    Dracula: Dead and Loving It (1995)\n",
       "12                          Balto (1995)\n",
       "13                          Nixon (1995)\n",
       "14               Cutthroat Island (1995)\n",
       "Name: title, dtype: string"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 1\n",
    "# Series of all the titles\n",
    "movies['title'].head(15)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "3a4132e8-fe4a-48f4-9b20-f2e278a9a8d1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0     False\n",
       "1     False\n",
       "2     False\n",
       "3     False\n",
       "4     False\n",
       "5     False\n",
       "6     False\n",
       "7     False\n",
       "8     False\n",
       "9     False\n",
       "10     True\n",
       "11    False\n",
       "12    False\n",
       "13    False\n",
       "14    False\n",
       "Name: title, dtype: boolean"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 2\n",
    "# Test if a title has a comma or not\n",
    "movies['title'].str.contains(',').head(15)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "405e4e77-b11b-461d-93b0-e2e9c94254a4",
   "metadata": {},
   "source": [
    "See how #10 matches?  \n",
    "That's a clue to how we isolate such titles.  \n",
    "\n",
    "We can build a [filter](https://pandas.pydata.org/docs/user_guide/indexing.html#indexing-and-selecting-data) using: ```new_df = df[condition]```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "4e9fcd3f-84b4-4c61-ae27-ea0eda492fc4",
   "metadata": {},
   "outputs": [],
   "source": [
    "movies_with_commas = movies[movies['title'].str.contains(',') == True]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "2a20f885-d3fb-4cf5-a3bd-b19faea41ff7",
   "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>movieId</th>\n",
       "      <th>title</th>\n",
       "      <th>genres</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>11</td>\n",
       "      <td>American President, The (1995)</td>\n",
       "      <td>Comedy|Drama|Romance</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>29</td>\n",
       "      <td>City of Lost Children, The (Cité des enfants p...</td>\n",
       "      <td>Adventure|Drama|Fantasy|Mystery|Sci-Fi</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>40</td>\n",
       "      <td>Cry, the Beloved Country (1995)</td>\n",
       "      <td>Drama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>50</td>\n",
       "      <td>Usual Suspects, The (1995)</td>\n",
       "      <td>Crime|Mystery|Thriller</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>54</td>\n",
       "      <td>Big Green, The (1995)</td>\n",
       "      <td>Children|Comedy</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    movieId                                              title  \\\n",
       "10       11                     American President, The (1995)   \n",
       "28       29  City of Lost Children, The (Cité des enfants p...   \n",
       "36       40                    Cry, the Beloved Country (1995)   \n",
       "46       50                         Usual Suspects, The (1995)   \n",
       "49       54                              Big Green, The (1995)   \n",
       "\n",
       "                                    genres  \n",
       "10                    Comedy|Drama|Romance  \n",
       "28  Adventure|Drama|Fantasy|Mystery|Sci-Fi  \n",
       "36                                   Drama  \n",
       "46                  Crime|Mystery|Thriller  \n",
       "49                         Children|Comedy  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movies_with_commas.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "01997a13-98a2-4dcf-accf-26e474b1104b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "movieId    9742\n",
       "title      9742\n",
       "genres     9742\n",
       "dtype: int64"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# total movies\n",
    "movies.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "1f1c95cd-e85d-4ed1-8848-c7f2889fa3c8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "movieId    2079\n",
       "title      2079\n",
       "genres     2079\n",
       "dtype: int64"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# number of movies with commas in their titles\n",
    "movies_with_commas.count()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0b8b61d3-4de1-40ef-b5cd-987479f14d9f",
   "metadata": {},
   "source": [
    "### extract the year of release from the movie title to a separate column"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "02819b1f-1382-4236-93ce-b24803337625",
   "metadata": {},
   "source": [
    "We'll use regex to match here.\n",
    "Something like [regex101](https://regex101.com/r/pWPPbM/1) is really helpful in building the expression."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "e891a184-f6c7-43ad-b620-4bf736f5d31d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "American President, The (1995)\n",
      "City of Lost Children, The (Cité des enfants perdus, La) (1995)\n"
     ]
    }
   ],
   "source": [
    "# select a complex-ish titles for building regex\n",
    "print(movies_with_commas.loc[10]['title'])\n",
    "print(movies_with_commas.loc[28]['title'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "4abd5381-2cdf-42e2-bc16-3772500f4028",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "No of groups in the regex:  3\n"
     ]
    }
   ],
   "source": [
    "import re\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": "markdown",
   "id": "f2391e6e-d3bd-4a8d-b267-355546def041",
   "metadata": {},
   "source": [
    "We can use [Pandas Series' ```str.extract()```](https://pandas.pydata.org/docs/reference/api/pandas.Series.str.extract.html#pandas-series-str-extract) method here."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "6e228205-1033-486c-b13c-d39aa014da8e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<re.Match object; span=(24, 30), match='(1995)'>\n",
      "<re.Match object; span=(57, 63), match='(1995)'>\n"
     ]
    }
   ],
   "source": [
    "# see if the regex works\n",
    "print(re.search(year_regex_pattern, movies_with_commas.loc[10]['title']))\n",
    "print(re.search(year_regex_pattern, movies_with_commas.loc[28]['title']))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "633827e5-3224-48e1-b0a0-0d315f2d85e4",
   "metadata": {},
   "source": [
    "```str.extract()``` will return 3 columns, one for each capture group."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "9f143cbe-2ead-43d9-bd71-9ac52680005b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# we are interested in the second capture group\n",
    "movies['year'] = movies['title'].str.extract(year_regex_pattern, flags = re.X, expand=False)[1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "7633a200-9f8f-4954-8c56-bbac9ab2e50a",
   "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>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",
       "  </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",
       "\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  "
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movies.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "02557b01-6b9b-4e6d-9122-71175004cb27",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1995\n",
      "1995\n"
     ]
    }
   ],
   "source": [
    "# check\n",
    "print(movies.loc[10]['year'])\n",
    "print(movies.loc[28]['year'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "46b3b0c6-f153-4fa5-9350-31e2676a427c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "movieId             Int32\n",
       "title      string[python]\n",
       "genres     string[python]\n",
       "year       string[python]\n",
       "dtype: object"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# wait... let's look at dtypes\n",
    "movies.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "a5476d5f-772d-4b16-8c0e-d73e133af972",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "movieId             Int32\n",
       "title      string[python]\n",
       "genres     string[python]\n",
       "year                Int16\n",
       "dtype: object"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# year needs to be int\n",
    "movies['year'] = pd.to_numeric(movies['year'],downcast='integer')\n",
    "movies.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "46a7b2f8-fe04-497b-9a23-c94f1ffc4038",
   "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>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",
       "  </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",
       "\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  "
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movies.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4fd30626-e083-4db0-8a8d-88c4b686c6fa",
   "metadata": {},
   "source": [
    "### How many movies in the data set from each year?  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "c14281af-49ca-403b-b02e-956651e84cb5",
   "metadata": {},
   "outputs": [],
   "source": [
    "movies_by_year_groupby = movies.groupby(by = 'year')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "c903352c-7e17-4d6b-89a5-de3082a7ba46",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['_DataFrameGroupBy__examples_dataframe_doc',\n",
       " '__annotations__',\n",
       " '__class__',\n",
       " '__class_getitem__',\n",
       " '__delattr__',\n",
       " '__dict__',\n",
       " '__dir__',\n",
       " '__doc__',\n",
       " '__eq__',\n",
       " '__format__',\n",
       " '__ge__',\n",
       " '__getattr__',\n",
       " '__getattribute__',\n",
       " '__getitem__',\n",
       " '__getstate__',\n",
       " '__gt__',\n",
       " '__hash__',\n",
       " '__init__',\n",
       " '__init_subclass__',\n",
       " '__iter__',\n",
       " '__le__',\n",
       " '__len__',\n",
       " '__lt__',\n",
       " '__module__',\n",
       " '__ne__',\n",
       " '__new__',\n",
       " '__orig_bases__',\n",
       " '__parameters__',\n",
       " '__reduce__',\n",
       " '__reduce_ex__',\n",
       " '__repr__',\n",
       " '__setattr__',\n",
       " '__sizeof__',\n",
       " '__slots__',\n",
       " '__str__',\n",
       " '__subclasshook__',\n",
       " '__weakref__',\n",
       " '_accessors',\n",
       " '_agg_examples_doc',\n",
       " '_agg_general',\n",
       " '_agg_py_fallback',\n",
       " '_aggregate_frame',\n",
       " '_aggregate_with_numba',\n",
       " '_apply_filter',\n",
       " '_apply_to_column_groupbys',\n",
       " '_ascending_count',\n",
       " '_choose_path',\n",
       " '_concat_objects',\n",
       " '_constructor',\n",
       " '_cumcount_array',\n",
       " '_cython_agg_general',\n",
       " '_cython_transform',\n",
       " '_define_paths',\n",
       " '_deprecate_axis',\n",
       " '_descending_count',\n",
       " '_dir_additions',\n",
       " '_dir_deletions',\n",
       " '_fill',\n",
       " '_get_data_to_aggregate',\n",
       " '_get_index',\n",
       " '_get_indices',\n",
       " '_gotitem',\n",
       " '_hidden_attrs',\n",
       " '_infer_selection',\n",
       " '_insert_inaxis_grouper',\n",
       " '_internal_names',\n",
       " '_internal_names_set',\n",
       " '_is_protocol',\n",
       " '_make_mask_from_int',\n",
       " '_make_mask_from_list',\n",
       " '_make_mask_from_positional_indexer',\n",
       " '_make_mask_from_slice',\n",
       " '_make_mask_from_tuple',\n",
       " '_mask_selected_obj',\n",
       " '_maybe_transpose_result',\n",
       " '_nth',\n",
       " '_numba_agg_general',\n",
       " '_numba_prep',\n",
       " '_obj_1d_constructor',\n",
       " '_obj_with_exclusions',\n",
       " '_op_via_apply',\n",
       " '_positional_selector',\n",
       " '_python_agg_general',\n",
       " '_python_apply_general',\n",
       " '_reindex_output',\n",
       " '_reset_cache',\n",
       " '_selected_obj',\n",
       " '_selection',\n",
       " '_selection_list',\n",
       " '_set_result_index_ordered',\n",
       " '_transform',\n",
       " '_transform_general',\n",
       " '_transform_with_numba',\n",
       " '_value_counts',\n",
       " '_wrap_agged_manager',\n",
       " '_wrap_aggregated_output',\n",
       " '_wrap_applied_output',\n",
       " '_wrap_applied_output_series',\n",
       " '_wrap_transform_fast_result',\n",
       " 'agg',\n",
       " 'aggregate',\n",
       " 'all',\n",
       " 'any',\n",
       " 'apply',\n",
       " 'bfill',\n",
       " 'boxplot',\n",
       " 'corr',\n",
       " 'corrwith',\n",
       " 'count',\n",
       " 'cov',\n",
       " 'cumcount',\n",
       " 'cummax',\n",
       " 'cummin',\n",
       " 'cumprod',\n",
       " 'cumsum',\n",
       " 'describe',\n",
       " 'diff',\n",
       " 'dtypes',\n",
       " 'ewm',\n",
       " 'expanding',\n",
       " 'ffill',\n",
       " 'fillna',\n",
       " 'filter',\n",
       " 'first',\n",
       " 'genres',\n",
       " 'get_group',\n",
       " 'groups',\n",
       " 'head',\n",
       " 'hist',\n",
       " 'idxmax',\n",
       " 'idxmin',\n",
       " 'indices',\n",
       " 'last',\n",
       " 'max',\n",
       " 'mean',\n",
       " 'median',\n",
       " 'min',\n",
       " 'movieId',\n",
       " 'ndim',\n",
       " 'ngroup',\n",
       " 'ngroups',\n",
       " 'nth',\n",
       " 'nunique',\n",
       " 'ohlc',\n",
       " 'pct_change',\n",
       " 'pipe',\n",
       " 'plot',\n",
       " 'prod',\n",
       " 'quantile',\n",
       " 'rank',\n",
       " 'resample',\n",
       " 'rolling',\n",
       " 'sample',\n",
       " 'sem',\n",
       " 'shift',\n",
       " 'size',\n",
       " 'skew',\n",
       " 'std',\n",
       " 'sum',\n",
       " 'tail',\n",
       " 'take',\n",
       " 'title',\n",
       " 'transform',\n",
       " 'value_counts',\n",
       " 'var',\n",
       " 'year']"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dir(movies_by_year_groupby)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "0a8e51b1-c8c5-4475-9b19-e47b6c8c0547",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "data type of movies_by_year_groupby.groups is:  <class 'pandas.io.formats.printing.PrettyDict'>\n"
     ]
    }
   ],
   "source": [
    "# groupby.groups is a dict with all unique values of 'year' as keys\n",
    "print('data type of movies_by_year_groupby.groups is: ', type(movies_by_year_groupby.groups))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "77710837-8ba3-45fd-8ad7-c12a521da82e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "dict_keys([1902, 1903, 1908, 1915, 1916, 1917, 1919, 1920, 1921, 1922, 1923, 1924, 1925, 1926, 1927, 1928, 1929, 1930, 1931, 1932, 1933, 1934, 1935, 1936, 1937, 1938, 1939, 1940, 1941, 1942, 1943, 1944, 1945, 1946, 1947, 1948, 1949, 1950, 1951, 1952, 1953, 1954, 1955, 1956, 1957, 1958, 1959, 1960, 1961, 1962, 1963, 1964, 1965, 1966, 1967, 1968, 1969, 1970, 1971, 1972, 1973, 1974, 1975, 1976, 1977, 1978, 1979, 1980, 1981, 1982, 1983, 1984, 1985, 1986, 1987, 1988, 1989, 1990, 1991, 1992, 1993, 1994, 1995, 1996, 1997, 1998, 1999, 2000, 2001, 2002, 2003, 2004, 2005, 2006, 2007, 2008, 2009, 2010, 2011, 2012, 2013, 2014, 2015, 2016, 2017, 2018])\n"
     ]
    }
   ],
   "source": [
    "print(movies_by_year_groupby.groups.keys())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "5ba81028-814e-408c-9005-5d6918b62f9a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "number of unique years in movies data:  106\n"
     ]
    }
   ],
   "source": [
    "print('number of unique years in movies data: ',len(movies_by_year_groupby.groups.keys()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "036adbb7-4729-4765-bd4e-f5dd0dfc3757",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "number of unique years in movies data:  106\n"
     ]
    }
   ],
   "source": [
    "# compare with the series.nunique() method\n",
    "print('number of unique years in movies data: ',movies['year'].nunique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "499d5955-5f5d-40c8-9730-ca01f02f91ae",
   "metadata": {},
   "outputs": [],
   "source": [
    "count_movies_by_year = movies_by_year_groupby.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "16720f28-0f4c-4673-85ac-9ce185914739",
   "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>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>2018</th>\n",
       "      <td>41</td>\n",
       "      <td>41</td>\n",
       "      <td>41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017</th>\n",
       "      <td>147</td>\n",
       "      <td>147</td>\n",
       "      <td>147</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016</th>\n",
       "      <td>218</td>\n",
       "      <td>218</td>\n",
       "      <td>218</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015</th>\n",
       "      <td>274</td>\n",
       "      <td>274</td>\n",
       "      <td>274</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014</th>\n",
       "      <td>277</td>\n",
       "      <td>277</td>\n",
       "      <td>277</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      movieId  title  genres\n",
       "year                        \n",
       "2018       41     41      41\n",
       "2017      147    147     147\n",
       "2016      218    218     218\n",
       "2015      274    274     274\n",
       "2014      277    277     277"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# show latest years first\n",
    "count_movies_by_year.sort_values(by='year', ascending=False).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "83de6385-3d0e-4afb-9785-fdcb0630ea47",
   "metadata": {},
   "source": [
    "### How many from each decade?"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b3833e9e-cc94-4492-9495-a81acecf7a8d",
   "metadata": {},
   "source": [
    "#### [```pandas.DataFrame.apply()```](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.apply.html#pandas-dataframe-apply)\n",
    "Apply a function to all the elements.  \n",
    "This gets really slow really fast.  \n",
    "Exercise Caution.  "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "77de0534-1351-4e5a-b3de-da9a131a7d78",
   "metadata": {},
   "source": [
    "We'll define a function that returns a decade for a given year.  \n",
    "Then ```apply()``` it to the ```year``` column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "592b6ba9-598a-4da1-bc58-6498d7213fac",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1920\n",
      "1970\n",
      "2000\n",
      "2010\n"
     ]
    }
   ],
   "source": [
    "# define a trivial function to return a decade\n",
    "def get_decade(year):\n",
    "\t# in case year is missing\n",
    "\tif pd.isna(year):\n",
    "\t\treturn 0\n",
    "\treturn int(year // 10 * 10)\n",
    "# \n",
    "print(get_decade(1924))\n",
    "print(get_decade(1972))\n",
    "print(get_decade(2001))\n",
    "print(get_decade(2018))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "e7843c10-5038-45c1-bb76-7c54096e7526",
   "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>movieId</th>\n",
       "      <th>title</th>\n",
       "      <th>genres</th>\n",
       "      <th>year</th>\n",
       "      <th>decade</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",
       "      <td>1990</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",
       "      <td>1990</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",
       "      <td>1990</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",
       "      <td>1990</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",
       "      <td>1990</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",
       "\n",
       "                                        genres  year  decade  \n",
       "0  Adventure|Animation|Children|Comedy|Fantasy  1995    1990  \n",
       "1                   Adventure|Children|Fantasy  1995    1990  \n",
       "2                               Comedy|Romance  1995    1990  \n",
       "3                         Comedy|Drama|Romance  1995    1990  \n",
       "4                                       Comedy  1995    1990  "
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# add a decade column to movies\n",
    "movies['decade'] = movies['year'].apply(get_decade)\n",
    "movies.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "5de7fa7b-2ba6-42cd-95f9-c24b49a95cb8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "movieId             Int32\n",
       "title      string[python]\n",
       "genres     string[python]\n",
       "year                Int16\n",
       "decade              int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movies.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "5548e0fb-7746-4ce6-a0a3-29b8e07a826b",
   "metadata": {},
   "outputs": [],
   "source": [
    "movies_by_decade_groupby = movies.groupby(by = 'decade')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "0ee24e54-dacc-4730-9d06-da5c5479f511",
   "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>movieId</th>\n",
       "      <th>title</th>\n",
       "      <th>genres</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>decade</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>24</td>\n",
       "      <td>24</td>\n",
       "      <td>24</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1900</th>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1910</th>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1920</th>\n",
       "      <td>37</td>\n",
       "      <td>37</td>\n",
       "      <td>37</td>\n",
       "      <td>37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1930</th>\n",
       "      <td>136</td>\n",
       "      <td>136</td>\n",
       "      <td>136</td>\n",
       "      <td>136</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1940</th>\n",
       "      <td>197</td>\n",
       "      <td>197</td>\n",
       "      <td>197</td>\n",
       "      <td>197</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1950</th>\n",
       "      <td>279</td>\n",
       "      <td>279</td>\n",
       "      <td>279</td>\n",
       "      <td>279</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1960</th>\n",
       "      <td>401</td>\n",
       "      <td>401</td>\n",
       "      <td>401</td>\n",
       "      <td>401</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1970</th>\n",
       "      <td>499</td>\n",
       "      <td>499</td>\n",
       "      <td>499</td>\n",
       "      <td>499</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1980</th>\n",
       "      <td>1177</td>\n",
       "      <td>1177</td>\n",
       "      <td>1177</td>\n",
       "      <td>1177</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1990</th>\n",
       "      <td>2209</td>\n",
       "      <td>2209</td>\n",
       "      <td>2209</td>\n",
       "      <td>2209</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2000</th>\n",
       "      <td>2846</td>\n",
       "      <td>2846</td>\n",
       "      <td>2846</td>\n",
       "      <td>2846</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010</th>\n",
       "      <td>1927</td>\n",
       "      <td>1927</td>\n",
       "      <td>1927</td>\n",
       "      <td>1927</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        movieId  title  genres  year\n",
       "decade                              \n",
       "0            24     24      24     0\n",
       "1900          3      3       3     3\n",
       "1910          7      7       7     7\n",
       "1920         37     37      37    37\n",
       "1930        136    136     136   136\n",
       "1940        197    197     197   197\n",
       "1950        279    279     279   279\n",
       "1960        401    401     401   401\n",
       "1970        499    499     499   499\n",
       "1980       1177   1177    1177  1177\n",
       "1990       2209   2209    2209  2209\n",
       "2000       2846   2846    2846  2846\n",
       "2010       1927   1927    1927  1927"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movies_by_decade_groupby.count()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "05bf5209-74f3-48c4-88e5-731fb1d868dc",
   "metadata": {},
   "source": [
    "### Average rating for each movie"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "fd4dcb82-a219-40ce-a9fd-079ca2979e67",
   "metadata": {},
   "outputs": [],
   "source": [
    "# select 2 columns from the ratings dataframe - supply the columns as a list\n",
    "ratings_movieId_groupby = ratings[['movieId', 'rating']].groupby(by = 'movieId')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "636e4397-5b22-4bb1-a202-c9ece0165c6a",
   "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>rating</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>movieId</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3.92093</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3.431818</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3.259615</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2.357143</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>3.071429</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>193581</th>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>193583</th>\n",
       "      <td>3.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>193585</th>\n",
       "      <td>3.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>193587</th>\n",
       "      <td>3.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>193609</th>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>9724 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           rating\n",
       "movieId          \n",
       "1         3.92093\n",
       "2        3.431818\n",
       "3        3.259615\n",
       "4        2.357143\n",
       "5        3.071429\n",
       "...           ...\n",
       "193581        4.0\n",
       "193583        3.5\n",
       "193585        3.5\n",
       "193587        3.5\n",
       "193609        4.0\n",
       "\n",
       "[9724 rows x 1 columns]"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ratings_movieId_groupby.mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b9c2de50-5809-4889-b083-a4d2efcd663d",
   "metadata": {},
   "source": [
    "### How many times was each movie rated?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "9932dd88-88d6-420f-b457-6af0d833bc4d",
   "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>rating</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>movieId</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>356</th>\n",
       "      <td>329</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>318</th>\n",
       "      <td>317</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>296</th>\n",
       "      <td>307</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>593</th>\n",
       "      <td>279</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2571</th>\n",
       "      <td>278</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>260</th>\n",
       "      <td>251</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>480</th>\n",
       "      <td>238</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110</th>\n",
       "      <td>237</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>589</th>\n",
       "      <td>224</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>527</th>\n",
       "      <td>220</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         rating\n",
       "movieId        \n",
       "356         329\n",
       "318         317\n",
       "296         307\n",
       "593         279\n",
       "2571        278\n",
       "260         251\n",
       "480         238\n",
       "110         237\n",
       "589         224\n",
       "527         220"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rating_counts = ratings_movieId_groupby.count()\n",
    "rating_counts.sort_values(by = 'rating', ascending=False).head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dd2e6054-e1b2-4df5-8fb7-4c624261f719",
   "metadata": {},
   "source": [
    "### Is there a wide margin between the number of ratings one movie has recieved vs another?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "d10e858d-097e-4817-8377-a2484eeb8553",
   "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>rating</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>9724.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>10.369807</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>22.401005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>329.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          rating\n",
       "count     9724.0\n",
       "mean   10.369807\n",
       "std    22.401005\n",
       "min          1.0\n",
       "25%          1.0\n",
       "50%          3.0\n",
       "75%          9.0\n",
       "max        329.0"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rating_counts.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "94c78aca-87aa-4129-b23a-a9b4bb2bd621",
   "metadata": {},
   "source": [
    "seems like 75% of the movies have about 9 ratings or less."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "d0269613-ddeb-4e6b-94c2-d2bbc59ee540",
   "metadata": {},
   "outputs": [],
   "source": [
    "# all the movies with over 9 ratings\n",
    "movies_with_more_than_9_ratings = rating_counts[rating_counts['rating']>9]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "b6533235-3b1e-487d-96dc-f81842aede6f",
   "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>rating</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>2269.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>35.749669</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>35.986989</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>14.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>22.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>43.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>329.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          rating\n",
       "count     2269.0\n",
       "mean   35.749669\n",
       "std    35.986989\n",
       "min         10.0\n",
       "25%         14.0\n",
       "50%         22.0\n",
       "75%         43.0\n",
       "max        329.0"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movies_with_more_than_9_ratings.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "76d54fb8-fc10-463a-83c6-e42002a42ffa",
   "metadata": {},
   "outputs": [],
   "source": [
    "# all the movies with 9 ratings or less\n",
    "movies_with_9_or_fewer_ratings = rating_counts[rating_counts['rating']<=9]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "92e73bfa-d957-4dec-a36e-1b15117c2724",
   "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>rating</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>7455.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>2.645205</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>2.181174</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         rating\n",
       "count    7455.0\n",
       "mean   2.645205\n",
       "std    2.181174\n",
       "min         1.0\n",
       "25%         1.0\n",
       "50%         2.0\n",
       "75%         4.0\n",
       "max         9.0"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movies_with_9_or_fewer_ratings.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "85d75119-0c29-4183-a71c-fba27201bbd9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.7666598107774578"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# num 9 or less / total\n",
    "7455.0/9724.0"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ec8989af-dcac-48e9-8d72-1049a2592254",
   "metadata": {},
   "source": [
    "### BTW - what's that movie that has the most ratings?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "9c6ba155-4627-4f8e-917d-e3be31f7b530",
   "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>movieId</th>\n",
       "      <th>title</th>\n",
       "      <th>genres</th>\n",
       "      <th>year</th>\n",
       "      <th>decade</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>314</th>\n",
       "      <td>356</td>\n",
       "      <td>Forrest Gump (1994)</td>\n",
       "      <td>Comedy|Drama|Romance|War</td>\n",
       "      <td>1994</td>\n",
       "      <td>1990</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     movieId                title                    genres  year  decade\n",
       "314      356  Forrest Gump (1994)  Comedy|Drama|Romance|War  1994    1990"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movies[movies['movieId'] == 356]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "71026301-5919-48a8-8089-152dae3405bc",
   "metadata": {},
   "source": [
    "### Query a movieID and get it's title and average rating back"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "17b48b55-c83d-4a73-8985-9e5134cfda18",
   "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>rating</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>movieId</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3.92093</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3.431818</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3.259615</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2.357143</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>3.071429</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           rating\n",
       "movieId          \n",
       "1         3.92093\n",
       "2        3.431818\n",
       "3        3.259615\n",
       "4        2.357143\n",
       "5        3.071429"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# create the average rating dataframe\n",
    "avg_ratings = ratings_movieId_groupby.mean()\n",
    "avg_ratings.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "b2a2cb50-8c35-490b-8fd2-c507c5779209",
   "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>average_rating</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>movieId</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3.92093</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3.431818</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3.259615</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2.357143</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>3.071429</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         average_rating\n",
       "movieId                \n",
       "1               3.92093\n",
       "2              3.431818\n",
       "3              3.259615\n",
       "4              2.357143\n",
       "5              3.071429"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# let's rename the rating column for clarity\n",
    "avg_ratings.rename(columns={'rating':'average_rating'}, inplace=True)\n",
    "avg_ratings.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e29270ba-dbfc-4172-8789-0686e29366b9",
   "metadata": {},
   "source": [
    "#### [```pandas.DataFrame.merge```](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.merge.html)\n",
    "\n",
    "```merge``` is how pandas does database style joins.  \n",
    "\n",
    "Joins are a way to match data between two tables, allowing us to  combine columns from one or more tables into a new table. \n",
    "\n",
    "[Read up more!](https://pandas.pydata.org/docs/user_guide/merging.html#database-style-dataframe-or-named-series-joining-merging)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "23d71779-9084-4737-bd03-bf7235d954fa",
   "metadata": {},
   "source": [
    "N.B. [```pandas.DataFrame.join()```](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.join.html#pandas-dataframe-join) also exists,  uses ```pandas.merge``` internally."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "da0183a5-6c89-47b0-b34f-cedf9c9fcd55",
   "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>movieId</th>\n",
       "      <th>title</th>\n",
       "      <th>genres</th>\n",
       "      <th>year</th>\n",
       "      <th>decade</th>\n",
       "      <th>average_rating</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",
       "      <td>1990</td>\n",
       "      <td>3.92093</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",
       "      <td>1990</td>\n",
       "      <td>3.431818</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",
       "      <td>1990</td>\n",
       "      <td>3.259615</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",
       "      <td>1990</td>\n",
       "      <td>2.357143</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",
       "      <td>1990</td>\n",
       "      <td>3.071429</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",
       "\n",
       "                                        genres  year  decade  average_rating  \n",
       "0  Adventure|Animation|Children|Comedy|Fantasy  1995    1990         3.92093  \n",
       "1                   Adventure|Children|Fantasy  1995    1990        3.431818  \n",
       "2                               Comedy|Romance  1995    1990        3.259615  \n",
       "3                         Comedy|Drama|Romance  1995    1990        2.357143  \n",
       "4                                       Comedy  1995    1990        3.071429  "
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# merge (join) with the movies dataframe\n",
    "movies = movies.merge(avg_ratings, on='movieId', how='left')\n",
    "movies.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "48a523d2-324c-4c03-afb1-ce1ea87045ba",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_movie_title_and_avg_rating(movieId):\n",
    "\tif movieId:\n",
    "\t\tmovie = movies[movies['movieId'] == movieId]\n",
    "\t\t# .values array to extract the raw value from a field \n",
    "\t\tmovie_title = movie['title'].values[0]\n",
    "\t\tmovie_avg_rating = movie['average_rating'].values[0]\n",
    "\t\t# pay attention to double quotes and single quotes when constructing the string\n",
    "\t\treturn f\"Movie Title: {movie_title} - has an average rating of: {movie_avg_rating}\"\n",
    "\treturn 'Incorrect movieId'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "28abbf2b-bdf1-4171-a846-57237d67fe4a",
   "metadata": {},
   "source": [
    "Let's test this on the movies that got lots of ratings..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "e2fc27f4-49fc-4ada-ab93-de121a95f128",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Movie Title: Forrest Gump (1994) - has an average rating of: 4.164133548736572'"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_movie_title_and_avg_rating(356)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "ffb00390-0830-47bb-89fc-86e62a6c890d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Movie Title: Shawshank Redemption, The (1994) - has an average rating of: 4.429022312164307'"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_movie_title_and_avg_rating(318)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "d3545509-2054-4ea9-843b-4b8be229fc83",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Movie Title: Pulp Fiction (1994) - has an average rating of: 4.197068214416504'"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_movie_title_and_avg_rating(296)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "3b1e0b31-2a28-4f37-9240-2cc2a0ea1c2c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Movie Title: Silence of the Lambs, The (1991) - has an average rating of: 4.161290168762207'"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_movie_title_and_avg_rating(593)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "5bd0acda-a086-479a-a2f8-d498b9344c66",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Movie Title: Matrix, The (1999) - has an average rating of: 4.192446231842041'"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_movie_title_and_avg_rating(2571)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8fefb34b-8f97-4566-9b51-b5f08863f249",
   "metadata": {},
   "source": [
    "...and those movies we used to test commas."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "3ec2dcb6-635d-4c4b-a9ea-3e2cdff668f7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Movie Title: American President, The (1995) - has an average rating of: 3.671428680419922'"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_movie_title_and_avg_rating(11)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "8a182b9a-2a4e-4d60-90cb-e923c49040d2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Movie Title: City of Lost Children, The (Cité des enfants perdus, La) (1995) - has an average rating of: 4.013157844543457'"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_movie_title_and_avg_rating(29)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6fb9e732-7360-4500-a27d-d00d9b9f6302",
   "metadata": {},
   "source": [
    "# Insights"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7279aa60-997e-48d8-ba69-46e283b7f662",
   "metadata": {},
   "source": [
    "1. For CSV data pay attention to the dialect\n",
    "2. EscapeChar vs QuoteChar in Pandas\n",
    "3. Opinion: Safe approach for timestamps - import as Integers/Numeric and convert using ```pd.to_datetime```\n",
    "4. ```pandas.Series.dt.date```\n",
    "5. ```pandas.DataFrame.apply```\n",
    "6. ```pandas.DataFrame.merge```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e9cd9648-5a13-446f-a775-76616ace03c1",
   "metadata": {},
   "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
}