02a_More_Pandas_Data_Manipulation.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### When real data is actually messier than real life\n",
    "\n",
    "Now that you've become familiar with how to do some basic data wrangling on a single dataset in **Python** using `pandas`, we are going to move on to more complicated data operations and problems that you may encounter.\n",
    "\n",
    "In this lesson we will cover the following:\n",
    "\n",
    "1. Working with text:\n",
    "  * Splitting a column into multiple pieces\n",
    "  * Extracting text\n",
    "  * Converting between data types\n",
    "* Converting categorical data into indicator variables\n",
    "* Binning numerical data into categories\n",
    "* Sorting datasets based on one or more columns\n",
    "* Combining multiple datasets together coherently into a single dataset:\n",
    "  * Joining/merging\n",
    "* Gracefully finding and removing bad/incomplete data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We are going to go back to the Movielens 1M dataset, but we are going to fill in a significant amount of detail that was excluded initially by including data that was stored in separate files.\n",
    "\n",
    "Specifically, we will be filling in/filtering/transforming information about the movies and users found in the `ratings.dat` dataset we worked on by combining that original info with data found in the `movies.dat` and `users.dat` files. These can be found in the same folder as where `ratings.dat` is located.\n",
    "\n",
    "So, lets begin!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As always, you have to import the modules you will/may need.\n",
    "\n",
    "Here are the schemas for the two new files we will work with:\n",
    "\n",
    "1. `movies.dat`:\n",
    "  * Row format: MovieID::Title::Genres\n",
    "    * `MovieID`: The id of the movie, should correspond to values in `MovieID` column found in `ratings.dat`\n",
    "    * `Title`: The title, in plain english, of the movie\n",
    "    * `Genres`: A list of genres the movie is associated with, can be of varying length per row\n",
    "\n",
    "2. `users.dat`:\n",
    "\n",
    "  * Row format: UserID::Gender::Age::Occupation::Zip-code\n",
    "    * `UserID`: The id of the user, should correspond to values found in `UserID` column foind in `ratings.dat`\n",
    "    * `Gender`: Self-reported gender of the user, either `M` or `F`\n",
    "    * `Age`: Self-reported age of the user\n",
    "    * `Occupation`: Self-reported occupation category of the user\n",
    "    * `Zip-code`: Self-reported zip-code of the user\n",
    "\n",
    "Lets load these files in, along with the first file, `ratings.dat`, that we worked with:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/Nicole/anaconda3/lib/python3.6/site-packages/ipykernel_launcher.py:1: ParserWarning: Falling back to the 'python' engine because the 'c' engine does not support regex separators (separators > 1 char and different from '\\s+' are interpreted as regex); you can avoid this warning by specifying engine='python'.\n",
      "  \"\"\"Entry point for launching an IPython kernel.\n",
      "/Users/Nicole/anaconda3/lib/python3.6/site-packages/ipykernel_launcher.py:2: ParserWarning: Falling back to the 'python' engine because the 'c' engine does not support regex separators (separators > 1 char and different from '\\s+' are interpreted as regex); you can avoid this warning by specifying engine='python'.\n",
      "  \n",
      "/Users/Nicole/anaconda3/lib/python3.6/site-packages/ipykernel_launcher.py:3: ParserWarning: Falling back to the 'python' engine because the 'c' engine does not support regex separators (separators > 1 char and different from '\\s+' are interpreted as regex); you can avoid this warning by specifying engine='python'.\n",
      "  This is separate from the ipykernel package so we can avoid doing imports until\n"
     ]
    }
   ],
   "source": [
    "movieData  = pd.read_csv(\"./data/movieData/movies.dat\",sep = \"::\", names = [\"MovieID\",\"Title\",\"Genres\"])\n",
    "userData   = pd.read_csv(\"./data/movieData/users.dat\",sep = \"::\", names = [\"UserID\",\"Gender\",\"Age\",\"Occupation\",\"Zip-code\"])\n",
    "ratingData = pd.read_csv(\"./data/movieData/ratings.dat\",sep = \"::\", names = ['UserID','MovieID','Rating','Timestamp'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's take a look at the first few rows in the `movies.dat` dataset, which we've stored in a variable called `movieData` and then lets make sure that every movie is uniquely represented in this set:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>Animation|Children's|Comedy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Jumanji (1995)</td>\n",
       "      <td>Adventure|Children's|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</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",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>Heat (1995)</td>\n",
       "      <td>Action|Crime|Thriller</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>Sabrina (1995)</td>\n",
       "      <td>Comedy|Romance</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>Tom and Huck (1995)</td>\n",
       "      <td>Adventure|Children's</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>Sudden Death (1995)</td>\n",
       "      <td>Action</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",
       "    </tr>\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>11</th>\n",
       "      <td>12</td>\n",
       "      <td>Dracula: Dead and Loving It (1995)</td>\n",
       "      <td>Comedy|Horror</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>13</td>\n",
       "      <td>Balto (1995)</td>\n",
       "      <td>Animation|Children's</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>14</td>\n",
       "      <td>Nixon (1995)</td>\n",
       "      <td>Drama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>15</td>\n",
       "      <td>Cutthroat Island (1995)</td>\n",
       "      <td>Action|Adventure|Romance</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>16</td>\n",
       "      <td>Casino (1995)</td>\n",
       "      <td>Drama|Thriller</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>17</td>\n",
       "      <td>Sense and Sensibility (1995)</td>\n",
       "      <td>Drama|Romance</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>18</td>\n",
       "      <td>Four Rooms (1995)</td>\n",
       "      <td>Thriller</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>19</td>\n",
       "      <td>Ace Ventura: When Nature Calls (1995)</td>\n",
       "      <td>Comedy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>20</td>\n",
       "      <td>Money Train (1995)</td>\n",
       "      <td>Action</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",
       "10       11         American President, The (1995)   \n",
       "11       12     Dracula: Dead and Loving It (1995)   \n",
       "12       13                           Balto (1995)   \n",
       "13       14                           Nixon (1995)   \n",
       "14       15                Cutthroat Island (1995)   \n",
       "15       16                          Casino (1995)   \n",
       "16       17           Sense and Sensibility (1995)   \n",
       "17       18                      Four Rooms (1995)   \n",
       "18       19  Ace Ventura: When Nature Calls (1995)   \n",
       "19       20                     Money Train (1995)   \n",
       "\n",
       "                          Genres  \n",
       "0    Animation|Children's|Comedy  \n",
       "1   Adventure|Children's|Fantasy  \n",
       "2                 Comedy|Romance  \n",
       "3                   Comedy|Drama  \n",
       "4                         Comedy  \n",
       "5          Action|Crime|Thriller  \n",
       "6                 Comedy|Romance  \n",
       "7           Adventure|Children's  \n",
       "8                         Action  \n",
       "9      Action|Adventure|Thriller  \n",
       "10          Comedy|Drama|Romance  \n",
       "11                 Comedy|Horror  \n",
       "12          Animation|Children's  \n",
       "13                         Drama  \n",
       "14      Action|Adventure|Romance  \n",
       "15                Drama|Thriller  \n",
       "16                 Drama|Romance  \n",
       "17                      Thriller  \n",
       "18                        Comedy  \n",
       "19                        Action  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movieData.head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(3883, 3)\n",
      "(3883,)\n"
     ]
    }
   ],
   "source": [
    "print(movieData.shape)\n",
    "print(movieData.MovieID.unique().shape)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So far, so good! There are as many rows in this dataset as there are unique movies!\n",
    "\n",
    "**However**, we can clearly see that we will need to do some massaging with this dataset in order to get it into a more workable format: \n",
    "1. **Create a separate column for the year in which each movie was released by parsing the `Title` column (in case we want to do an analysis involving years)**\n",
    "* **Parse the `Genres` column to extract every genre separately (this looks like it might be a bit tricky since there are different numbers of genres for each movie, but its actually really easy!)**\n",
    "\n",
    "Lets try to tackle **1.** \n",
    "\n",
    "A reasonable approach here would be:\n",
    "\n",
    "1. Creating a new column called `Year` by extracting the 4th-to-last through next-to-last characters from each Title \n",
    "* Converting this value from a `string` (or an `object`, as **pandas** calls them) into an `int`\n",
    "* Removing the last 6 characters completely from the `Title` column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Before formatting:\n",
      "   MovieID                               Title                        Genres\n",
      "0        1                    Toy Story (1995)   Animation|Children's|Comedy\n",
      "1        2                      Jumanji (1995)  Adventure|Children's|Fantasy\n",
      "2        3             Grumpier Old Men (1995)                Comedy|Romance\n",
      "3        4            Waiting to Exhale (1995)                  Comedy|Drama\n",
      "4        5  Father of the Bride Part II (1995)                        Comedy\n",
      "After formatting: \n",
      "   MovieID                        Title                        Genres  Year\n",
      "0        1                    Toy Story   Animation|Children's|Comedy  1995\n",
      "1        2                      Jumanji  Adventure|Children's|Fantasy  1995\n",
      "2        3             Grumpier Old Men                Comedy|Romance  1995\n",
      "3        4            Waiting to Exhale                  Comedy|Drama  1995\n",
      "4        5  Father of the Bride Part II                        Comedy  1995\n"
     ]
    }
   ],
   "source": [
    "print(\"Before formatting:\")\n",
    "print(movieData.head())\n",
    "movieData[\"Year\"] = movieData.Title.str.slice(-5,-1) #1\n",
    "movieData.Year = movieData.Year.astype(int) #2\n",
    "movieData.Title = movieData.Title.str.slice(0,-7) #3\n",
    "print(\"After formatting: \")\n",
    "print(movieData.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's work through all 3 of the lines that didn't simply `print` to the screen.\n",
    "\n",
    "There's a whole slew of functions that you can use to operate on `string` values that **pandas** provides, and the way to access them is very similar to the way you accessed all the cool time functionality when you called `dt` on a given row in the previous lesson.\n",
    "\n",
    "So, to access `str` functions for a given column, just call `DataFrameName.columnName.str`, which translates in our case to `movieData.Title.str`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The function that we are using here is called `slice`:\n",
    "\n",
    "`movieData[\"Year\"] = movieData.Title.str.slice(-5,-1)`\n",
    "\n",
    "`slice` allows us to extract a \"slice\" of the string out directly, based on the starting and ending index (position) of the values we want \"sliced\" out in relation to the whole string.\n",
    "\n",
    "**Indices for lists, arrays, strings, and collections of values more generally in Python always begin at 0 and end at the length of the string/list/collection-1. Indexing can also proceed from the end of the string/list/collection, etc. backwards, using negative numbers.**\n",
    "\n",
    "So, to extract the 4th-to-last to next-to-last values in the string, we have to use index values of -5 and -1.\n",
    "\n",
    "There are other ways to extract or split/transform strings in **Python** and **pandas** and I encourage you to look over the other functions found in the string module of **Python** and the kinds of `string` operations built into **pandas** itself. [Here's a useful link](http://pandas.pydata.org/pandas-docs/stable/text.html)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The second line shouldn't seem nearly as challenging:\n",
    "\n",
    "`movieData.Year = movieData.Year.astype(int)`\n",
    "\n",
    "We are just taking the column, and forcing the values in the column to behave as `int` types (numbers) and not as `string` types (on which we can't operate numerically)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "With that out of the way, lets tackle the next data massaging task:\n",
    "\n",
    "**Parse the `Genres` column to extract every genre separately (this looks like it might be a bit tricky since there are different numbers of genres for each movie, but its actually not as hard as one would think!)**\n",
    "\n",
    "The approach we are going to take here is fairly common in Data Science pipelines and involves converting categorical variables (like our genres column) into a number-like representation. \n",
    "\n",
    "This is typically called creating **indicator variables** (you'll see why they're called indicator variables shortly).\n",
    "\n",
    "The way this approach works is:\n",
    "\n",
    "1. **Collect all of the distinct values found for the given categorical type (in our case, genre).**\n",
    "* **Create a column per category value, and indicate whether the given value is present or absent for each row in the dataset.**\n",
    "\n",
    "The caveat here is that if your categorical variable has many distinct values that it ranges over (>100), then the transformation to indicator variables can lead to a really sparse, large, and space-inefficient representation of your data (for 100 distinct values, your matrix would have to be at least 99 columns wide).\n",
    "\n",
    "Thankfully, **pandas** can do both steps **1. and 2.** for you in one line of code, as long as the data is stored in the right format (which it is!).\n",
    "\n",
    "Here is how this transformation from categorical column -> indicator variable would be executed in **pandas**:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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</td>\n",
       "      <td>Animation|Children's|Comedy</td>\n",
       "      <td>1995</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Jumanji</td>\n",
       "      <td>Adventure|Children's|Fantasy</td>\n",
       "      <td>1995</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Grumpier Old Men</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</td>\n",
       "      <td>Comedy|Drama</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</td>\n",
       "      <td>Comedy</td>\n",
       "      <td>1995</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   MovieID                        Title                        Genres  Year\n",
       "0        1                    Toy Story   Animation|Children's|Comedy  1995\n",
       "1        2                      Jumanji  Adventure|Children's|Fantasy  1995\n",
       "2        3             Grumpier Old Men                Comedy|Romance  1995\n",
       "3        4            Waiting to Exhale                  Comedy|Drama  1995\n",
       "4        5  Father of the Bride Part II                        Comedy  1995"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movieData.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Action</th>\n",
       "      <th>Adventure</th>\n",
       "      <th>Animation</th>\n",
       "      <th>Children's</th>\n",
       "      <th>Comedy</th>\n",
       "      <th>Crime</th>\n",
       "      <th>Documentary</th>\n",
       "      <th>Drama</th>\n",
       "      <th>Fantasy</th>\n",
       "      <th>Film-Noir</th>\n",
       "      <th>Horror</th>\n",
       "      <th>Musical</th>\n",
       "      <th>Mystery</th>\n",
       "      <th>Romance</th>\n",
       "      <th>Sci-Fi</th>\n",
       "      <th>Thriller</th>\n",
       "      <th>War</th>\n",
       "      <th>Western</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Action  Adventure  Animation  Children's  Comedy  Crime  Documentary  \\\n",
       "0       0          0          1           1       1      0            0   \n",
       "1       0          1          0           1       0      0            0   \n",
       "2       0          0          0           0       1      0            0   \n",
       "3       0          0          0           0       1      0            0   \n",
       "4       0          0          0           0       1      0            0   \n",
       "\n",
       "   Drama  Fantasy  Film-Noir  Horror  Musical  Mystery  Romance  Sci-Fi  \\\n",
       "0      0        0          0       0        0        0        0       0   \n",
       "1      0        1          0       0        0        0        0       0   \n",
       "2      0        0          0       0        0        0        1       0   \n",
       "3      1        0          0       0        0        0        0       0   \n",
       "4      0        0          0       0        0        0        0       0   \n",
       "\n",
       "   Thriller  War  Western  \n",
       "0         0    0        0  \n",
       "1         0    0        0  \n",
       "2         0    0        0  \n",
       "3         0    0        0  \n",
       "4         0    0        0  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "genresDF = movieData.Genres.str.get_dummies(sep = \"|\")\n",
    "genresDF.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Awesome, so that totally converted the `Genres` column into the expanded indicator variable representation in one line of code!\n",
    "\n",
    "Let's go through it:\n",
    "\n",
    "`genresDF = movieData.Genres.str.get_dummies(sep = \"|\")`\n",
    "\n",
    "We used `str.get_dummies()` on the `Genres` column and passed in a value to the `sep` parameter, telling the function that if a row had multiple entries, then the `|` character separated them. \n",
    "\n",
    "If, on the other hand, we only had a single genre per row, we could have left the parameter `sep` out completely and simply called:\n",
    "\n",
    "`genresDF = movieData.Genres.str.get_dummies()`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "But now we have a bit of a problem, all of these indicator variables are stored in a separate `DataFrame` from the original `movieData`. We can fix this by **joining the two datasets together.**\n",
    "\n",
    "Join operations are very common operations in databases and in data processing pipelines more generally. **Join operations take two distinct datasets and combine them based on some common column or set of columns (called join keys) so that all columns from both datasets are combined into a single dataset based on the kind of join you want to perform.**\n",
    "\n",
    "3 basic kinds of joins exist. To formalize the join types, lets say we have two datasets called A and B:\n",
    "\n",
    "1. **One-sided (left or right) join**: This kind of join takes all the join keys from one of the datasets (lets say A), keeps all of them, and attempts to find all the rows in B with keys identical to only those in A. Any keys from A not found in B will have null values for columns in B.\n",
    "* **Inner join**: This join only takes all the keys common to both A and B and combines all of the columns in both A and B together. Rows for which keys are not found in both A and B are thrown out.\n",
    "* **Outer (full outer) join**: This join takes all the keys found in either A or B and combines both datasets on the keys that are common to both. For those keys that arent in both datasets, all columns remain, but have null values for the missing columns. (We will talk about null values soon).\n",
    "\n",
    "I will not go into more detail about each of these kinds of joins now, but if youre interested in learning more about join types [this is a good explanation](http://blog.sweetlabs.com/2013/12/cheat-sheet-using-python-pandas-perform-fast-sql-like-joins/) and [here is the **pandas** documentation](http://pandas.pydata.org/pandas-docs/stable/merging.html)\n",
    "\n",
    "The exact type of join we will perform now actually doesn't matter, because all of them will lead to the same result (Do you know why?).\n",
    "\n",
    "So, on to the join:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>MovieID</th>\n",
       "      <th>Title</th>\n",
       "      <th>Genres</th>\n",
       "      <th>Year</th>\n",
       "      <th>Action</th>\n",
       "      <th>Adventure</th>\n",
       "      <th>Animation</th>\n",
       "      <th>Children's</th>\n",
       "      <th>Comedy</th>\n",
       "      <th>Crime</th>\n",
       "      <th>...</th>\n",
       "      <th>Fantasy</th>\n",
       "      <th>Film-Noir</th>\n",
       "      <th>Horror</th>\n",
       "      <th>Musical</th>\n",
       "      <th>Mystery</th>\n",
       "      <th>Romance</th>\n",
       "      <th>Sci-Fi</th>\n",
       "      <th>Thriller</th>\n",
       "      <th>War</th>\n",
       "      <th>Western</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Toy Story</td>\n",
       "      <td>Animation|Children's|Comedy</td>\n",
       "      <td>1995</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Jumanji</td>\n",
       "      <td>Adventure|Children's|Fantasy</td>\n",
       "      <td>1995</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Grumpier Old Men</td>\n",
       "      <td>Comedy|Romance</td>\n",
       "      <td>1995</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Waiting to Exhale</td>\n",
       "      <td>Comedy|Drama</td>\n",
       "      <td>1995</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Father of the Bride Part II</td>\n",
       "      <td>Comedy</td>\n",
       "      <td>1995</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   MovieID                        Title                        Genres  Year  \\\n",
       "0        1                    Toy Story   Animation|Children's|Comedy  1995   \n",
       "1        2                      Jumanji  Adventure|Children's|Fantasy  1995   \n",
       "2        3             Grumpier Old Men                Comedy|Romance  1995   \n",
       "3        4            Waiting to Exhale                  Comedy|Drama  1995   \n",
       "4        5  Father of the Bride Part II                        Comedy  1995   \n",
       "\n",
       "   Action  Adventure  Animation  Children's  Comedy  Crime   ...     Fantasy  \\\n",
       "0       0          0          1           1       1      0   ...           0   \n",
       "1       0          1          0           1       0      0   ...           1   \n",
       "2       0          0          0           0       1      0   ...           0   \n",
       "3       0          0          0           0       1      0   ...           0   \n",
       "4       0          0          0           0       1      0   ...           0   \n",
       "\n",
       "   Film-Noir  Horror  Musical  Mystery  Romance  Sci-Fi  Thriller  War  \\\n",
       "0          0       0        0        0        0       0         0    0   \n",
       "1          0       0        0        0        0       0         0    0   \n",
       "2          0       0        0        0        1       0         0    0   \n",
       "3          0       0        0        0        0       0         0    0   \n",
       "4          0       0        0        0        0       0         0    0   \n",
       "\n",
       "   Western  \n",
       "0        0  \n",
       "1        0  \n",
       "2        0  \n",
       "3        0  \n",
       "4        0  \n",
       "\n",
       "[5 rows x 22 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movieDataWithGenres = movieData.merge(genresDF,left_index=True,right_index=True)\n",
    "movieDataWithGenres.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To perform a join, you can call **pandas** `merge()` function and pass it a variety of parameters (read the documentation for more info). In our case:\n",
    "\n",
    "`movieData = movieData.merge(genresDF,left_index=True,right_index=True)`\n",
    "\n",
    "We are calling `merge` from `movieData`, so `movieData` functions as our left dataset (table), and `genresDF` functions as our right table. \n",
    "\n",
    "We are using the indices of both tables as join keys (thats what setting the parameters `left_index` and `right_index` to `True` does, as by default they are `False`).\n",
    "\n",
    "If you were to join on something other than the index of both tables (like another column), you would have passed that column name (or names) as a `string` or a `List` of `string` names to the `left_on` and `right_on` parameters.\n",
    "\n",
    "**However, in cases such as this, you don't have to do a join, but can simply concatenate the two dataframes together horizontally (since they are in exactly the same order):**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>MovieID</th>\n",
       "      <th>Title</th>\n",
       "      <th>Genres</th>\n",
       "      <th>Year</th>\n",
       "      <th>Action</th>\n",
       "      <th>Adventure</th>\n",
       "      <th>Animation</th>\n",
       "      <th>Children's</th>\n",
       "      <th>Comedy</th>\n",
       "      <th>Crime</th>\n",
       "      <th>...</th>\n",
       "      <th>Fantasy</th>\n",
       "      <th>Film-Noir</th>\n",
       "      <th>Horror</th>\n",
       "      <th>Musical</th>\n",
       "      <th>Mystery</th>\n",
       "      <th>Romance</th>\n",
       "      <th>Sci-Fi</th>\n",
       "      <th>Thriller</th>\n",
       "      <th>War</th>\n",
       "      <th>Western</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Toy Story</td>\n",
       "      <td>Animation|Children's|Comedy</td>\n",
       "      <td>1995</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Jumanji</td>\n",
       "      <td>Adventure|Children's|Fantasy</td>\n",
       "      <td>1995</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Grumpier Old Men</td>\n",
       "      <td>Comedy|Romance</td>\n",
       "      <td>1995</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Waiting to Exhale</td>\n",
       "      <td>Comedy|Drama</td>\n",
       "      <td>1995</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Father of the Bride Part II</td>\n",
       "      <td>Comedy</td>\n",
       "      <td>1995</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   MovieID                        Title                        Genres  Year  \\\n",
       "0        1                    Toy Story   Animation|Children's|Comedy  1995   \n",
       "1        2                      Jumanji  Adventure|Children's|Fantasy  1995   \n",
       "2        3             Grumpier Old Men                Comedy|Romance  1995   \n",
       "3        4            Waiting to Exhale                  Comedy|Drama  1995   \n",
       "4        5  Father of the Bride Part II                        Comedy  1995   \n",
       "\n",
       "   Action  Adventure  Animation  Children's  Comedy  Crime   ...     Fantasy  \\\n",
       "0       0          0          1           1       1      0   ...           0   \n",
       "1       0          1          0           1       0      0   ...           1   \n",
       "2       0          0          0           0       1      0   ...           0   \n",
       "3       0          0          0           0       1      0   ...           0   \n",
       "4       0          0          0           0       1      0   ...           0   \n",
       "\n",
       "   Film-Noir  Horror  Musical  Mystery  Romance  Sci-Fi  Thriller  War  \\\n",
       "0          0       0        0        0        0       0         0    0   \n",
       "1          0       0        0        0        0       0         0    0   \n",
       "2          0       0        0        0        1       0         0    0   \n",
       "3          0       0        0        0        0       0         0    0   \n",
       "4          0       0        0        0        0       0         0    0   \n",
       "\n",
       "   Western  \n",
       "0        0  \n",
       "1        0  \n",
       "2        0  \n",
       "3        0  \n",
       "4        0  \n",
       "\n",
       "[5 rows x 22 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movieDataWithGenres2 = pd.concat((movieData,genresDF),axis=1)\n",
    "movieDataWithGenres2.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "One last thing to do with this dataset! \n",
    "We still have the `Genres` column in `movieData`, but we've expanded it and don't need it.\n",
    "So, let's delete it from the `DataFrame`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>MovieID</th>\n",
       "      <th>Title</th>\n",
       "      <th>Year</th>\n",
       "      <th>Action</th>\n",
       "      <th>Adventure</th>\n",
       "      <th>Animation</th>\n",
       "      <th>Children's</th>\n",
       "      <th>Comedy</th>\n",
       "      <th>Crime</th>\n",
       "      <th>Documentary</th>\n",
       "      <th>...</th>\n",
       "      <th>Fantasy</th>\n",
       "      <th>Film-Noir</th>\n",
       "      <th>Horror</th>\n",
       "      <th>Musical</th>\n",
       "      <th>Mystery</th>\n",
       "      <th>Romance</th>\n",
       "      <th>Sci-Fi</th>\n",
       "      <th>Thriller</th>\n",
       "      <th>War</th>\n",
       "      <th>Western</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Toy Story</td>\n",
       "      <td>1995</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Jumanji</td>\n",
       "      <td>1995</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Grumpier Old Men</td>\n",
       "      <td>1995</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Waiting to Exhale</td>\n",
       "      <td>1995</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Father of the Bride Part II</td>\n",
       "      <td>1995</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   MovieID                        Title  Year  Action  Adventure  Animation  \\\n",
       "0        1                    Toy Story  1995       0          0          1   \n",
       "1        2                      Jumanji  1995       0          1          0   \n",
       "2        3             Grumpier Old Men  1995       0          0          0   \n",
       "3        4            Waiting to Exhale  1995       0          0          0   \n",
       "4        5  Father of the Bride Part II  1995       0          0          0   \n",
       "\n",
       "   Children's  Comedy  Crime  Documentary   ...     Fantasy  Film-Noir  \\\n",
       "0           1       1      0            0   ...           0          0   \n",
       "1           1       0      0            0   ...           1          0   \n",
       "2           0       1      0            0   ...           0          0   \n",
       "3           0       1      0            0   ...           0          0   \n",
       "4           0       1      0            0   ...           0          0   \n",
       "\n",
       "   Horror  Musical  Mystery  Romance  Sci-Fi  Thriller  War  Western  \n",
       "0       0        0        0        0       0         0    0        0  \n",
       "1       0        0        0        0       0         0    0        0  \n",
       "2       0        0        0        1       0         0    0        0  \n",
       "3       0        0        0        0       0         0    0        0  \n",
       "4       0        0        0        0       0         0    0        0  \n",
       "\n",
       "[5 rows x 21 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "del movieDataWithGenres[\"Genres\"]\n",
    "movieDataWithGenres.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We've just learned about how to go from categorical data into indicator variables, which are effectively a kind of proto-numerical data. \n",
    "\n",
    "Now, lets learn how to transform data from a numerical value into a more-categorical value through binning. This process is also referred to as discretization (forcing some value into one of several distinct bins).\n",
    "\n",
    "As always, we are going to learn how to do this by answering a specific question:\n",
    "\n",
    "**In what decade were the most of the movies found in this dataset made?**\n",
    "\n",
    "So, to attack this problem we would need to:\n",
    "\n",
    "1. Extract all of the unique movies along with the year they were released\n",
    "* Get the earliest year and latest year when a movie was made using `min` and `max` to get the range over which we are going to work\n",
    "* Get the start of the decades that these years would have been a part of\n",
    "* Create a new column that bins each year based on a decade-based binning from the earliest decade found to the last decade found from the previous step.\n",
    "* Group by this new column and get the size of each bin.\n",
    "* Sort the grouped data in descending order and look at the first entry\n",
    "\n",
    "Here is the entire sequence of steps:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Earliest year: 1919 Latest year: 2000\n"
     ]
    }
   ],
   "source": [
    "yearsData = movieData[[\"Title\",\"Year\"]] #1\n",
    "minYear = yearsData.Year.min() #2\n",
    "maxYear = yearsData.Year.max() #3\n",
    "\n",
    "print(\"Earliest year: %d Latest year: %d\"%(minYear,maxYear)) #4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Earliest decade: 1910 End of Final Decade: 2000\n"
     ]
    }
   ],
   "source": [
    "minDecade = np.floor_divide(minYear,10)*10 #5\n",
    "maxDecade = np.floor_divide(maxYear,10)*10 #6\n",
    "\n",
    "print(\"Earliest decade: %d End of Final Decade: %d\"%(minDecade,maxDecade)) #7"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "All decades: [1910 1920 1930 1940 1950 1960 1970 1980 1990 2000]\n"
     ]
    }
   ],
   "source": [
    "allDecades = np.arange(minDecade,maxDecade+10,10) #8\n",
    "\n",
    "print(\"All decades: %s\" %(allDecades)) #9"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Decade with the most movies:\n",
      "Decade\n",
      "(1990, 2000]    2362\n",
      "(1980, 1990]     634\n",
      "(1970, 1980]     272\n",
      "(1960, 1970]     192\n",
      "(1950, 1960]     169\n",
      "(1940, 1950]     121\n",
      "(1930, 1940]      89\n",
      "(1920, 1930]      39\n",
      "(1910, 1920]       5\n",
      "dtype: int64\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/Nicole/anaconda3/lib/python3.6/site-packages/ipykernel_launcher.py:1: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  \"\"\"Entry point for launching an IPython kernel.\n"
     ]
    }
   ],
   "source": [
    "yearsData[\"Decade\"] = pd.cut(yearsData.Year,allDecades) #10\n",
    "yearsData\n",
    "moviesPerDecade = yearsData.groupby(\"Decade\").size() #11\n",
    "moviesPerDecade.sort_values(ascending=False,inplace=True)   #12\n",
    "\n",
    "print(\"Decade with the most movies:\") #13\n",
    "print(moviesPerDecade) #14"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Lets work through all the steps, carefully going over those that you may appear new to you.\n",
    "\n",
    "We are not going to go over **steps 1-3** as they should be familiar to you (extract the columns we are interested in, get min and max of one of those columns)\n",
    "\n",
    "In **step 4**, we print the results of **2-3** out to the screen using some formatting tricks. \n",
    "\n",
    "The `%d` inside of the strings tells python that we are going to be putting some data (an integer) in there later (after the string has been written) and pass those values as a tuple after the string, prefixed with another `%` symbol. \n",
    "\n",
    "So, `%d` means \"put an integer in there\" and the `%(minYear,maxYear)` means \"these are the two values, that you should treat as integers, you should put in, in the order they are written.\"\n",
    "\n",
    "**Steps 5-6** are some trickery we have to use to get the closest decade before the minimum and maximum dates when the movies were released.\n",
    "\n",
    "`np.floor_divide()` is a function that takes 2 arguments (a numerator and a denominator) and returns the first number divided by the second, with any remainder lobbed off. \n",
    "\n",
    "Our two arguments in both cases will be the years we found, and we are going to divide them by 10 and then multiply them by 10, so the remainder is lobbed off and the closest decade is returned!\n",
    "\n",
    "**Step 7** is more printing magic that you should now understand a little bit.\n",
    "\n",
    "**Step 8** creates the range of decades we are interested in. \n",
    "\n",
    "`np.arange` takes 3 arguments (the starting integer, the ending integer, and the spacing between them) and creates the evenly spaced range between the two numbers, but only inclusive of the first, and exclusive of the second. THIS IS WHY WE HAD TO ADD 10 TO THE `maxDecade` VALUE!\n",
    "\n",
    "So:\n",
    "\n",
    "    np.arange(0,10,2) \n",
    "    returns:\n",
    "    [0,2,4,6,8]\n",
    "    and in order to get: \n",
    "    [02,4,6,8,10] \n",
    "    we would need to pass:\n",
    "    np.arange(0,12,2)\n",
    "\n",
    "**Step 9** prints what **Step 8** did. Because `allDecades` is not an `int` but a sequence of `int` values (called an `ndarray`, but don't worry about this), we have to treat it as a `string`, hence the `%s` (which stands for `string`)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Step 10** is the actual **pandas** code doing the work we are interested in. \n",
    "\n",
    "`cut` is a function that takes two parameters:\n",
    "\n",
    "* The first parameter is a sequence of values, the `Year` column in our case\n",
    "* The second parameter can either be a sequence of numbers or a single value, and does something different based on the kind of input you give. If the parameter is a sequence of values, it assigns each value in the first parameter to a bin that is exclusive on the left and inclusive on the right for the sequence you give (I will explain). If the second parameter is a single value, this tells **pandas** that the values in the first parameter should be placed into that number of bins, equally spaced around the range of values found in the first parameter (phew!)\n",
    "\n",
    "Heres an example, lets say you have a list of numbers `dude = [1,20,30,43,100,26]`:\n",
    "\n",
    "`pd.cut(dude,[0,10,100])`\n",
    "\n",
    "will return:\n",
    "\n",
    "`[(0,10],(10,100],(10,100],(10,100],(10,100],(10,100]]`\n",
    "\n",
    "But then this:\n",
    "\n",
    "`pd.cut(dude,3)`\n",
    "\n",
    "will return:\n",
    "\n",
    "`[(0.901, 34], (0.901, 34], (0.901, 34], (34, 67], (67, 100], (0.901, 34]]`\n",
    "\n",
    "So in the first case, you give it the bins, in the second, **pandas** figures out the best way to split the data into the correct number of equally large bins.\n",
    "\n",
    "Make sense?\n",
    "\n",
    "So:\n",
    "\n",
    "`yearsData[\"Decade\"] = pd.cut(yearsData.Year,allDecades)`\n",
    "\n",
    "creates a new column called `Decade` in the `yearsData` dataset and assigns to it the bins created by passing the `Year` column and the range of decades found in `allDecades`. \n",
    "\n",
    "If we had just wanted 9 equally spaced year ranges (instead of the 9 decades our dataset covers), we could have just done:\n",
    "\n",
    "`yearsData[\"Decade\"] = pd.cut(yearsData.Year,9)`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Step 11** should be familiar to you by now. `groupby` on our new column, and get the size of every group and save it in a variable `moviesPerDecade`.\n",
    "\n",
    "**Step 12** introduces sorting. \n",
    "\n",
    "`sort` is a pandas function that can sort your dataset along one or several columns in a variety of ways. \n",
    "\n",
    "The `ascending` parameter is by default `True` and forces the sort to either be from smallest value at the top to largest value at the bottom (`ascending = True`, the default) or largest first, smallest last (`ascending=False` as we have).\n",
    "\n",
    "The `inplace` parameter makes it so that you can either sort the dataset and not have to reassign the sorted dataset to a new variable (`inplace = True`; this is especially useful when your dataset is very large and creating a sorted copy would create way too much memory pressure on your system) or not (`inplace = False`, the default).\n",
    "\n",
    "So:\n",
    "\n",
    "`moviesPerDecade.sort(ascending=False,inplace=True)`\n",
    "\n",
    "Sorts this dataset as is (dont copy it somewhere else) and make sure its in descending order. Because this dataset is a `Series` object, which only contains a single column, we don't have to (and really can't) specify the column along which we want to sort.\n",
    "\n",
    "**Steps 13 and 14** should also be obvious. **Step 13** prints a statement to the screen and **Step 14** simply prints the first row in the dataset (since its in descending order, its also the largest element!)\n",
    "\n",
    "Make sense?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ok, we're done working with `movieData` for now, lets move on to `userData`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "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>Gender</th>\n",
       "      <th>Age</th>\n",
       "      <th>Occupation</th>\n",
       "      <th>Zip-code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>F</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>48067</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>M</td>\n",
       "      <td>56</td>\n",
       "      <td>16</td>\n",
       "      <td>70072</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>M</td>\n",
       "      <td>25</td>\n",
       "      <td>15</td>\n",
       "      <td>55117</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>M</td>\n",
       "      <td>45</td>\n",
       "      <td>7</td>\n",
       "      <td>02460</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>M</td>\n",
       "      <td>25</td>\n",
       "      <td>20</td>\n",
       "      <td>55455</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   UserID Gender  Age  Occupation Zip-code\n",
       "0       1      F    1          10    48067\n",
       "1       2      M   56          16    70072\n",
       "2       3      M   25          15    55117\n",
       "3       4      M   45           7    02460\n",
       "4       5      M   25          20    55455"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "userData.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here is some explanation about the `Age` and `Occupation` fields (before you think there is someone that is claiming to be a 1 year old in the dataset!):\n",
    "\n",
    "- Age is a categorical variable mapped to the following ranges:\n",
    "\n",
    "    *  1:  \"Under 18\"\n",
    "    * 18:  \"18-24\"\n",
    "    * 25:  \"25-34\"\n",
    "    * 35:  \"35-44\"\n",
    "    * 45:  \"45-49\"\n",
    "    * 50:  \"50-55\"\n",
    "    * 56:  \"56+\"\n",
    "\n",
    "- Occupation is a categorical variable mapped as follows:\n",
    "\n",
    "    * 0:  \"other\" or not specified\n",
    "    * 1:  \"academic/educator\"\n",
    "    * 2:  \"artist\"\n",
    "    * 3:  \"clerical/admin\"\n",
    "    * 4:  \"college/grad student\"\n",
    "    * 5:  \"customer service\"\n",
    "    * 6:  \"doctor/health care\"\n",
    "    * 7:  \"executive/managerial\"\n",
    "    * 8:  \"farmer\"\n",
    "    * 9:  \"homemaker\"\n",
    "    * 10: \"K-12 student\"\n",
    "    * 11: \"lawyer\"\n",
    "    * 12: \"programmer\"\n",
    "    * 13: \"retired\"\n",
    "    * 14: \"sales/marketing\"\n",
    "    * 15: \"scientist\"\n",
    "    * 16: \"self-employed\"\n",
    "    * 17: \"technician/engineer\"\n",
    "    * 18: \"tradesman/craftsman\"\n",
    "    * 19: \"unemployed\"\n",
    "    * 20: \"writer\"\n",
    "\n",
    "\n",
    "Ok, so then there are several combinations of `Age` and `Occupation` that we can remove:\n",
    "\n",
    "1. **Make sure that every user mapped to `Age 1` has an `Occupation` `4 or 10`, if not, remove them.**\n",
    "*  **Make sure no one that has an `Occupation` other than `10` is of `Age` either `1 or 18`, otherwise remove them.**\n",
    "* **Make sure all of the zipcodes are in a standard (5 digit) format, but keep them as `string` types (any idea why?)**\n",
    "\n",
    "Now that you're more familiar with **pandas**, you should figure out how to do all 3 of these and either remove or change the offending rows.\n",
    "\n",
    "\n",
    "(**Hint:** Use `np.logical_and` to test for the occurence of multiple conditions for your mask that you are going to have to create! `np.logical_and` takes in 2 arguments, the two conditions you want satisfied.)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Store the new, filtered `DataFrame` in a separate variable called `cleanedUserData` and keep the cleaned zipcodes in the column `Zip-code`.\n",
    "\n",
    "(**Hint:** I suggest you use the slicing logic we used before for the `Title` column to extract the first 5 values in the `Zip-code` column and then convert the column from a `string` to an `int`)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "ename": "SyntaxError",
     "evalue": "invalid syntax (<ipython-input-51-2c0a1c117c1c>, line 9)",
     "output_type": "error",
     "traceback": [
      "\u001b[0;36m  File \u001b[0;32m\"<ipython-input-51-2c0a1c117c1c>\"\u001b[0;36m, line \u001b[0;32m9\u001b[0m\n\u001b[0;31m    print(\"Num rows before:\" len(userData))\u001b[0m\n\u001b[0m                               ^\u001b[0m\n\u001b[0;31mSyntaxError\u001b[0m\u001b[0;31m:\u001b[0m invalid syntax\n"
     ]
    }
   ],
   "source": [
    "#Answering question 1\n",
    "#case where they are young\n",
    "myMask1 = np.logical_and(userData.Age==1,np.logical_or(userData.Occupation==4,userData.Occupation==10))\n",
    "#case where they are not young\n",
    "myMask1part2 = np.logical_and(userData.Age!=1,np.logical_and(userData.Occupation!=4,userData.Occupation!=10))\n",
    "myMask1Better = np.logical_or(myMask1,myMask1part2)\n",
    "\n",
    "userDataMyFilteredTest = userData[myMask1Better]\n",
    "print(\"Num rows before:\" len(userData))\n",
    "print(\"Num rows after:\"len(userDataMyFilteredTest))\n",
    "userDataMyFilteredTest.head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   UserID Gender  Age  Occupation Zip-code\n",
      "0       1      F    1          10    48067\n",
      "1       2      M   56          16    70072\n",
      "2       3      M   25          15    55117\n",
      "3       4      M   45           7    02460\n",
      "4       5      M   25          20    55455\n",
      "   UserID Gender  Age  Occupation Zip-code\n",
      "0       1      F    1          10    48067\n",
      "1       2      M   56          16    70072\n",
      "2       3      M   25          15    55117\n",
      "3       4      M   45           7    02460\n",
      "4       5      M   25          20    55455\n",
      "Before removing garbage: (6040, 5)\n",
      "After removing garbage: (4653, 5)\n"
     ]
    }
   ],
   "source": [
    "#Answering question2 using output of question 1\n",
    "myMask2 = np.logical_or(np.logical_and(userDataMyFilteredTest.Occupation!=10,np.logical_and(userDataMyFilteredTest.Age!=1,userDataMyFilteredTest.Age!=18)),userDataMyFilteredTest.Occupation==10)\n",
    "userDataFinallyFiltered = userDataMyFilteredTest[myMask2]\n",
    "print(userDataFinallyFiltered.head())\n",
    "print(userData.head())\n",
    "#Answer question 3\n",
    "userData[\"Zip-code\"].unique()                     \n",
    "userDataFinallyFiltered = userDataFinallyFiltered[userDataFinallyFiltered[\"Zip-code\"].str.len()>=5]\n",
    "userDataFinallyFiltered[\"Zip-code\"] = userDataFinallyFiltered[\"Zip-code\"].str.slice(0,5)\n",
    "\n",
    "print(\"Before removing garbage:\",userData.shape)\n",
    "print(\"After removing garbage:\",userDataFinallyFiltered.shape)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "Ok, now that all of this data munging on both of these datasets is done, lets **join** them with the original `ratingsData`. \n",
    "\n",
    "To join each of these datasets, you will need to use a distinct set of **join keys:**\n",
    "\n",
    "1. To join the `movieData` dataset to the `ratingData` dataset, you will need to join on the `MovieID` column.\n",
    "2. To join the `cleanedUserData` dataset to the `ratingData` dataset, you will need to join on the `UserID` column.\n",
    "\n",
    "Also, remember that we removed several bad rows from the `userData` to get the `cleanedUserData` so when we perform the default **join** (an *inner join*) with the `ratingData` we will get a smaller dataset out!\n",
    "\n",
    "So, go ahead and perform the joins yourself and store the resulting, final joined dataset in a variable called `filledAndCleanedRatingData`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Cleaned users joined with ratings:\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>UserID</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Age</th>\n",
       "      <th>Occupation</th>\n",
       "      <th>Zip-code</th>\n",
       "      <th>MovieID</th>\n",
       "      <th>Rating</th>\n",
       "      <th>Timestamp</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>F</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>48067</td>\n",
       "      <td>1193</td>\n",
       "      <td>5</td>\n",
       "      <td>978300760</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>F</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>48067</td>\n",
       "      <td>661</td>\n",
       "      <td>3</td>\n",
       "      <td>978302109</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>F</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>48067</td>\n",
       "      <td>914</td>\n",
       "      <td>3</td>\n",
       "      <td>978301968</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>F</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>48067</td>\n",
       "      <td>3408</td>\n",
       "      <td>4</td>\n",
       "      <td>978300275</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>F</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>48067</td>\n",
       "      <td>2355</td>\n",
       "      <td>5</td>\n",
       "      <td>978824291</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   UserID Gender  Age  Occupation Zip-code  MovieID  Rating  Timestamp\n",
       "0       1      F    1          10    48067     1193       5  978300760\n",
       "1       1      F    1          10    48067      661       3  978302109\n",
       "2       1      F    1          10    48067      914       3  978301968\n",
       "3       1      F    1          10    48067     3408       4  978300275\n",
       "4       1      F    1          10    48067     2355       5  978824291"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "filledAndCleanedUserAndRatingData = userDataFinallyFiltered.merge(ratingData,on=\"UserID\")\n",
    "print(\"Cleaned users joined with ratings:\")\n",
    "filledAndCleanedUserAndRatingData.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Cleaned users+ratings joined with movies:\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>UserID</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Age</th>\n",
       "      <th>Occupation</th>\n",
       "      <th>Zip-code</th>\n",
       "      <th>MovieID</th>\n",
       "      <th>Rating</th>\n",
       "      <th>Timestamp</th>\n",
       "      <th>Title</th>\n",
       "      <th>Year</th>\n",
       "      <th>...</th>\n",
       "      <th>Fantasy</th>\n",
       "      <th>Film-Noir</th>\n",
       "      <th>Horror</th>\n",
       "      <th>Musical</th>\n",
       "      <th>Mystery</th>\n",
       "      <th>Romance</th>\n",
       "      <th>Sci-Fi</th>\n",
       "      <th>Thriller</th>\n",
       "      <th>War</th>\n",
       "      <th>Western</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>F</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>48067</td>\n",
       "      <td>1193</td>\n",
       "      <td>5</td>\n",
       "      <td>978300760</td>\n",
       "      <td>One Flew Over the Cuckoo's Nest</td>\n",
       "      <td>1975</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>M</td>\n",
       "      <td>56</td>\n",
       "      <td>16</td>\n",
       "      <td>70072</td>\n",
       "      <td>1193</td>\n",
       "      <td>5</td>\n",
       "      <td>978298413</td>\n",
       "      <td>One Flew Over the Cuckoo's Nest</td>\n",
       "      <td>1975</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>12</td>\n",
       "      <td>M</td>\n",
       "      <td>25</td>\n",
       "      <td>12</td>\n",
       "      <td>32793</td>\n",
       "      <td>1193</td>\n",
       "      <td>4</td>\n",
       "      <td>978220179</td>\n",
       "      <td>One Flew Over the Cuckoo's Nest</td>\n",
       "      <td>1975</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>15</td>\n",
       "      <td>M</td>\n",
       "      <td>25</td>\n",
       "      <td>7</td>\n",
       "      <td>22903</td>\n",
       "      <td>1193</td>\n",
       "      <td>4</td>\n",
       "      <td>978199279</td>\n",
       "      <td>One Flew Over the Cuckoo's Nest</td>\n",
       "      <td>1975</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>17</td>\n",
       "      <td>M</td>\n",
       "      <td>50</td>\n",
       "      <td>1</td>\n",
       "      <td>95350</td>\n",
       "      <td>1193</td>\n",
       "      <td>5</td>\n",
       "      <td>978158471</td>\n",
       "      <td>One Flew Over the Cuckoo's Nest</td>\n",
       "      <td>1975</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 28 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   UserID Gender  Age  Occupation Zip-code  MovieID  Rating  Timestamp  \\\n",
       "0       1      F    1          10    48067     1193       5  978300760   \n",
       "1       2      M   56          16    70072     1193       5  978298413   \n",
       "2      12      M   25          12    32793     1193       4  978220179   \n",
       "3      15      M   25           7    22903     1193       4  978199279   \n",
       "4      17      M   50           1    95350     1193       5  978158471   \n",
       "\n",
       "                             Title  Year   ...     Fantasy  Film-Noir  Horror  \\\n",
       "0  One Flew Over the Cuckoo's Nest  1975   ...           0          0       0   \n",
       "1  One Flew Over the Cuckoo's Nest  1975   ...           0          0       0   \n",
       "2  One Flew Over the Cuckoo's Nest  1975   ...           0          0       0   \n",
       "3  One Flew Over the Cuckoo's Nest  1975   ...           0          0       0   \n",
       "4  One Flew Over the Cuckoo's Nest  1975   ...           0          0       0   \n",
       "\n",
       "   Musical  Mystery  Romance  Sci-Fi  Thriller  War  Western  \n",
       "0        0        0        0       0         0    0        0  \n",
       "1        0        0        0       0         0    0        0  \n",
       "2        0        0        0       0         0    0        0  \n",
       "3        0        0        0       0         0    0        0  \n",
       "4        0        0        0       0         0    0        0  \n",
       "\n",
       "[5 rows x 28 columns]"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(\"Cleaned users+ratings joined with movies:\")\n",
    "filledAndCleanedAllData = filledAndCleanedUserAndRatingData.merge(movieDataWithGenres,on = \"MovieID\")\n",
    "filledAndCleanedAllData.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And now, just to get a bit more practice with munging and transforming data in pandas, answer the following questions:\n",
    "\n",
    "1. What was the most frequently rated movie?\n",
    "  * In january?\n",
    "* What movie was most commonly rated in the mornings (before noon)? \n",
    "  * What was its average rating?\n",
    "  * What about in the evenings? What was its average rating?\n",
    "* Which user saw movies that belonged to the largest collection of distinct genres?\n",
    "* Which movie did the most people disagree on (had the highest standard deviation in ratings)?\n",
    "* Which movie had the worst average rating?\n",
    "  * For only those movies that were rated at least 3 times?\n",
    "* What was the average rating for Comedies?\n",
    "* What was the average rating for movies per decade?\n",
    "* Which decade other than the 90s had the highest average rating?\n",
    "  * Which decade had the largest spread in ratings (including the 90s)?\n",
    "* Create a new column on a per-movie basis called `RatingFrequency` following these guidelines:\n",
    "  * Movies that were rated 3 times or fewer\n",
    "  * Movies rated between 3 and 10 times\n",
    "  * Movies rated more than 10 times (you can use `np.inf` as your last number in the range to get the range \"10 or more\")\n",
    "* What was the average rating for comedies for each `RatingFrequency` type?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "#1\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "#1*\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "#2\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "#2*\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "#3\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "#4 Which movie did the most people disagree on (had the highest standard deviation in ratings)?\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Which movie had the worst average rating?\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "#   * For only those movies that were rated at least 3 times?\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "# * What was the average rating for Comedies?\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "# * What was the average rating for movies per decade?\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "# * Which decade had the highest average rating?\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "#   * Which decade had the largest spread in ratings?\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "# * Create a new column on a per-movie basis called `RatingFrequency` following these guidelines:\n",
    "# * Movies that were rated 3 times or fewer\n",
    "# * Movies rated between 3 and 10 times\n",
    "# * Movies rated more than 10 times (you can use `np.inf` as your last number in the range to get the range \"10 or more\")\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "# * What was the average rating for comedies for each `RatingFrequency` type?\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You should now be very comfortable with these features of **pandas**:\n",
    "\n",
    "* Working with `string` input within columns\n",
    "* Converting categorical data into indicator variables\n",
    "* Converting numerical data into categorical data \n",
    "* Joining multiple tables together"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.6.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}