intro-to-pandas-3-Pandas-On-MovieLens.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Using pandas on the MovieLens dataset"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To show pandas in a more \"applied\" sense, let's use it to answer some questions about the [MovieLens](http://www.grouplens.org/datasets/movielens/) dataset. Recall that we've already read our data into DataFrames and merged it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "from IPython.display import display\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# pass in column names for each CSV\n",
    "u_cols = ['user_id', 'age', 'sex', 'occupation', 'zip_code']\n",
    "users = pd.read_csv('data/ml-100k/u.user', sep='|', names=u_cols)\n",
    "\n",
    "r_cols = ['user_id', 'movie_id', 'rating', 'unix_timestamp']\n",
    "ratings = pd.read_csv('data/ml-100k/u.data', sep='\\t', names=r_cols)\n",
    "\n",
    "# the movies file contains columns indicating the movie's genres\n",
    "# let's only load the first five columns of the file with usecols\n",
    "m_cols = ['movie_id', 'title', 'release_date', 'video_release_date', 'imdb_url']\n",
    "movies = pd.read_csv('data/ml-100k/u.item', sep='|', names=m_cols, usecols=range(5))\n",
    "\n",
    "# create one merged DataFrame\n",
    "movie_ratings = pd.merge(movies, ratings)\n",
    "lens = pd.merge(movie_ratings, users)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**What are the 25 most rated movies?**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "most_rated = lens.groupby('title').size().sort_values(ascending=False)[:25]\n",
    "most_rated"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There's a lot going on in the code above, but it's very idomatic. We're splitting the DataFrame into groups by movie title and applying the `size` method to get the count of records in each group. Then we order our results in descending order and limit the output to the top 25 using Python's slicing syntax.\n",
    "\n",
    "In SQL, this would be equivalent to:\n",
    "\n",
    "    SELECT title, count(1)\n",
    "    FROM lens\n",
    "    GROUP BY title\n",
    "    ORDER BY 2 DESC\n",
    "    LIMIT 25;\n",
    "\n",
    "Alternatively, pandas has a nifty `value_counts` method - yes, this is simpler - the goal above was to show a basic `groupby` example."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "lens.title.value_counts()[:25]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Which movies are most highly rated?**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "movie_stats = lens.groupby('title').agg({'rating': [np.size, np.mean]})\n",
    "movie_stats.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can use the `agg` method to pass a dictionary specifying the columns to aggregate (as keys) and a list of functions we'd like to apply.\n",
    "\n",
    "Let's sort the resulting DataFrame so that we can see which movies have the highest average score."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# sort by rating average\n",
    "movie_stats.sort_values(by=[('rating', 'mean')], ascending=False).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Because our columns are now a [MultiIndex](http://pandas.pydata.org/pandas-docs/stable/indexing.html#hierarchical-indexing-multiindex), we need to pass in a tuple specifying how to sort.\n",
    "\n",
    "The above movies are rated so rarely that we can't count them as quality films. Let's only look at movies that have been rated at least 100 times."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "atleast_100 = movie_stats['rating']['size'] >= 100\n",
    "movie_stats[atleast_100].sort_values(by=[('rating', 'mean')], ascending=False)[:15]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Those results look realistic.  Notice that we used boolean indexing to filter our `movie_stats` frame.\n",
    "\n",
    "We broke this question down into many parts, so here's the Python needed to get the 15 movies with the highest average rating, requiring that they had at least 100 ratings:\n",
    "\n",
    "```python\n",
    "    movie_stats = lens.groupby('title').agg({'rating': [np.size, np.mean]})\n",
    "    atleast_100 = movie_stats['rating'].size >= 100\n",
    "    movie_stats[atleast_100].sort([('rating', 'mean')], ascending=False)[:15]\n",
    "```\n",
    "\n",
    "The SQL equivalent would be:\n",
    "\n",
    "    SELECT title, COUNT(1) size, AVG(rating) mean\n",
    "    FROM lens\n",
    "    GROUP BY title\n",
    "    HAVING COUNT(1) >= 100\n",
    "    ORDER BY 3 DESC\n",
    "    LIMIT 15;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Limiting our population going forward**\n",
    "\n",
    "Going forward, let's only look at the 50 most rated movies. Let's make a Series of movies that meet this threshold so we can use it for filtering later."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "most_50 = lens.groupby('movie_id').size().sort_values(ascending=False)[:50]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The SQL to match this would be:\n",
    "\n",
    "    CREATE TABLE most_50 AS (\n",
    "        SELECT movie_id, COUNT(1)\n",
    "        FROM lens\n",
    "        GROUP BY movie_id\n",
    "        ORDER BY 2 DESC\n",
    "        LIMIT 50\n",
    "    );\n",
    "    \n",
    "This table would then allow us to use EXISTS, IN, or JOIN whenever we wanted to filter our results. Here's an example using EXISTS:\n",
    "\n",
    "    SELECT *\n",
    "    FROM lens\n",
    "    WHERE EXISTS (SELECT 1 FROM most_50 WHERE lens.movie_id = most_50.movie_id);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Which movies are most controversial amongst different ages?**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's look at how these movies are viewed across different age groups. First, let's look at how age is distributed amongst our users."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "users.age.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "users.age.hist(bins=30)\n",
    "plt.title(\"Distribution of users' ages\")\n",
    "plt.ylabel('count of users')\n",
    "plt.xlabel('age');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "pandas' integration with [matplotlib](http://matplotlib.org/index.html) makes basic graphing of Series/DataFrames trivial.  In this case, just call `hist` on the column to produce a histogram.  We can also use [matplotlib.pyplot](http://matplotlib.org/users/pyplot_tutorial.html) to customize our graph a bit (always label your axes)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Binning our users**\n",
    "\n",
    "I don't think it'd be very useful to compare individual ages - let's bin our users into age groups using `pandas.cut`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "labels = ['0-9', '10-19', '20-29', '30-39', '40-49', '50-59', '60-69', '70-79']\n",
    "lens['age_group'] = pd.cut(lens.age, range(0, 81, 10), right=False, labels=labels)\n",
    "lens[['age', 'age_group']].drop_duplicates()[:10]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`pandas.cut` allows you to bin numeric data. In the above lines, we first created labels to name our bins, then split our users into eight bins of ten years (0-9, 10-19, 20-29, etc.). Our use of `right=False` told the function that we wanted the bins to be *exclusive* of the max age in the bin (e.g. a 30 year old user gets the 30s label).\n",
    "\n",
    "Now we can now compare ratings across age groups."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "lens.groupby('age_group').agg({'rating': [np.size, np.mean]})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Young users seem a bit more critical than other age groups. Let's look at how the 50 most rated movies are viewed across each age group. We can use the `most_50` Series we created earlier for filtering."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "lens.set_index('movie_id', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "by_age = lens.ix[most_50.index].groupby(['title', 'age_group'])\n",
    "by_age.rating.mean().head(15)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice that both the title and age group are indexes here, with the average rating value being a Series. This is going to produce a really long list of values.\n",
    "\n",
    "Wouldn't it be nice to see the data as a table? Each title as a row, each age group as a column, and the average rating in each cell.\n",
    "\n",
    "Behold! The magic of `unstack`!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "by_age.rating.mean().unstack(1).fillna(0)[10:20]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`unstack`, well, unstacks the specified level of a [MultiIndex](http://pandas.pydata.org/pandas-docs/stable/indexing.html#hierarchical-indexing-multiindex) (by default, `groupby` turns the grouped field into an index - since we grouped by two fields, it became a MultiIndex). We unstacked the second index (remember that Python uses 0-based indexes), and then filled in NULL values with 0.\n",
    "\n",
    "If we had used:\n",
    "```python\n",
    "    by_age.rating.mean().unstack(0).fillna(0)\n",
    "```\n",
    "We would have had our age groups as rows and movie titles as columns."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Which movies do men and women most disagree on?**\n",
    "\n",
    "*Wes McKinney basically went through the exact same question in his book. It's a good, yet simple example of pivot_table. McKinney's book is the go-to text for pandas: [book here](http://www.amazon.com/gp/product/1449319793/ref=as_li_tl?ie=UTF8&camp=1789&creative=390957&creativeASIN=1449319793&linkCode=as2&tag=gjreda-20&linkId=MCGW4C4NOBRVV5OC).*\n",
    "\n",
    "Think about how you'd have to do this in SQL for a second. You'd have to use a combination of IF/CASE statements with aggregate functions in order to pivot your dataset. Your query would look something like this:\n",
    "\n",
    "    SELECT title, AVG(IF(sex = 'F', rating, NULL)), AVG(IF(sex = 'M', rating, NULL))\n",
    "    FROM lens\n",
    "    GROUP BY title;\n",
    "    \n",
    "Imagine how annoying it'd be if you had to do this on more than two columns.\n",
    "    \n",
    "DataFrame's have a *pivot_table* method that makes these kinds of operations much easier (and less verbose)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "lens.reset_index('movie_id', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "pivoted = lens.pivot_table(index=['movie_id', 'title'],\n",
    "                           columns=['sex'],\n",
    "                           values='rating',\n",
    "                           fill_value=0)\n",
    "pivoted.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "pivoted['diff'] = pivoted.M - pivoted.F\n",
    "pivoted.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "pivoted.reset_index('movie_id', inplace=True)\n",
    "pivoted.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "disagreements = pivoted[pivoted.movie_id.isin(most_50.index)]['diff']\n",
    "disagreements.sort_values().plot(kind='barh', figsize=[9, 15])\n",
    "plt.title('Male vs. Female Avg. Ratings\\n(Difference > 0 = Favored by Men)')\n",
    "plt.ylabel('Title')\n",
    "plt.xlabel('Average Rating Difference')\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Of course men like Terminator more than women. Independence Day though? Really?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Additional Resources:\n",
    "\n",
    "* [pandas documentation](http://pandas.pydata.org/pandas-docs/stable/)\n",
    "* [Introduction to pandas](http://nbviewer.ipython.org/urls/gist.github.com/fonnesbeck/5850375/raw/c18cfcd9580d382cb6d14e4708aab33a0916ff3e/1.+Introduction+to+Pandas.ipynb) by [Chris Fonnesbeck](https://twitter.com/fonnesbeck)\n",
    "* [pandas videos from PyCon](http://pyvideo.org/search?models=videos.video&q=pandas)\n",
    "* [pandas and Python top 10](http://manishamde.github.io/blog/2013/03/07/pandas-and-python-top-10/)\n",
    "* [pandasql](http://blog.yhathq.com/posts/pandasql-sql-for-pandas-dataframes.html)\n",
    "* [Practical pandas by Tom Augspurger (one of the pandas developers)](http://tomaugspurger.github.io/categories/pandas.html)\n",
    "    * [Video](https://www.youtube.com/watch?v=otCriSKVV_8) from Tom's pandas tutorial at PyData Seattle 2015"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}