02_Pandas_and_Numpy.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Manipulate Data with Pandas and Numpy \n",
    "\n",
    "Python has special libraries (a collection of functions and datatypes) that expand the functionality of `Python` to make working with data a lot simpler. \n",
    "\n",
    "1. `Numpy` is a [library](http:https://www.numpy.org/) that works well with matrices and vectors. `NumPy` is a core component of the [Python scientific stack](http:https://www.scipy.org/index.html).\n",
    "\n",
    "2. `Pandas` is a **Python Data Analysis** [Library](https://pandas.pydata.org/) and is a very loose acronym for **P**ython **An**alysis of **Da**taset**s** (or something like that) that provides open source, easy-to-use data structures and analysis tools which we will be using heavily throughout the rest of the semester. \n",
    "\n",
    "In order to use these libraries, you must `import` them into our program. This is how we tell our program to give us all the functionality of the library and use the functions when called. We often use aliases `np` and `pd` so we don't have to type out the entire library name when calling them. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "But before we jump into `numpy` and `pandas` let's review some list functions, "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Lists - an ordered series of data (Integer, Float, Objects, etc.)\n",
    "a = ['Hello','World']\n",
    "print(a)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Lists can be changed via native functions in Python"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Via appending to the end\n",
    "a.append('!')\n",
    "print(a)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Or extending multiple items.\n",
    "a.extend(['I','Am','A','List','.'])\n",
    "print(a)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#You can also remove an item\n",
    "a.remove('World')\n",
    "print(a)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Or put one back in\n",
    "a.insert(1,'Hello')\n",
    "print(a)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Count a number of entries\n",
    "a.count('Hello')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Organize by Value\n",
    "a.sort()\n",
    "print(a)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Switch the order around\n",
    "a.reverse()\n",
    "print(a)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Grab a single item\n",
    "a.pop(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Or clear the entire thing\n",
    "a.clear()\n",
    "print(a)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Practice\n",
    "Create an empty list. Add a set of five numbers to it. Then, iterate through that list and multiply each number by 2. Finally sort the values and print them."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Vectors\n",
    "\n",
    "Vectors are 1-dimensional arrays (lists) of scalars (individual numbers). They are created with `Numpy` using the function `array` and can be created directly or cast from a list:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# directly created array\n",
    "an_array = np.array([1,20,34,12])\n",
    "print(an_array,type(an_array))\n",
    "\n",
    "# array from list\n",
    "my_list = [30,3.5,12]\n",
    "an_array_from_list = np.array(my_list)\n",
    "print(an_array_from_list, type(an_array_from_list))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "An array is different from a list in that all the scalars must be of the same type. `Numpy` will **automatically try to cast your values** into the same type. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "my_list_of_strings_and_ints = [\"hello\",12,\"15.8\"]\n",
    "my_string_array = np.array(my_list_of_strings_and_ints)\n",
    "print(my_string_array, my_string_array.dtype)\n",
    "print(my_string_array[1],type(my_string_array[1])) # 12 is now a string!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can perform math operations on them, like multiplying, dividing, adding, and subtracting from them by another scalar or another array."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "my_array = np.array([11, 44, 2.4])\n",
    "print('Original array: \\t', my_array)\n",
    "\n",
    "mult_array = my_array * 3\n",
    "print('Multiplied by 3: \\t', mult_array)\n",
    "\n",
    "div_array = my_array / 3\n",
    "print(\"Divided by 3: \\t\\t\", div_array)\n",
    "\n",
    "sub_array = my_array - 3\n",
    "print(\"Subtracted 3 from: \\t\", sub_array)\n",
    "\n",
    "add_array = my_array + 3\n",
    "print(\"Added 3 to: \\t\\t\", add_array)\n",
    "\n",
    "#For a range of numbers from 2-4\n",
    "new_array = np.arange(2, 5)\n",
    "array_mult = my_array * new_array\n",
    "print(\"Multiply by new array: \\t\", array_mult)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Numpy arrays also have shape, telling you its structure\n",
    "print('my_array shape: ', my_array.shape)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also check each element for certain properties. It will give us a new array of type `bool`:  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "my_array > 10"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What we can then do is apply this same exact `boolean array` to our `original array` to get a new array that satisfies our condition:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "greater_than_10_array = my_array[my_array>10]\n",
    "print(greater_than_10_array) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finally, vectors can be indexed (sliced) in the same way as lists in `Python`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "my_array[1:-1]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Exercise Time!!!\n",
    "* create a `NumPy array` called `weather` = [20.4,7.3,51,70]\n",
    "* convert from fahrenheit to celsius (°F − 32) × 5/9 = °C and store in a new variable called `celsius`\n",
    "* create a new array `cold` with only those entries in celsius that are < 0\n",
    "* create another array `hot` with only those entries in celsius that are > 0\n",
    "* print out all arrays"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Matrices"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that we've talked about Vectors (1-d arrays), lets talk about Matrices, which are 2-dimensional vectors. They are a bit more complicated and have a lot more properties than vectors, but they are also infinitely more useful.\n",
    "\n",
    "Let's start. Here's a simple 2X2 matrix:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "first_matrix = np.array([[20,12],[30,1]])\n",
    "print(first_matrix)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Matrices with an equal number of rows and columns are called **square**.\n",
    "\n",
    "You can access rows, columns, or individual values in the matrix in a way very similar to how we would access elements or collections of elements in lists:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "first_matrix.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(\"First row of the matrix:\\n\",\n",
    "      first_matrix[0,:],\n",
    "      type(first_matrix[0,:]))\n",
    "\n",
    "print(\"First column of the matrix:\\n\",\n",
    "      first_matrix[:,0],\n",
    "      type(first_matrix[:,0]))\n",
    "\n",
    "print(\"Last element in last row of the matrix:\\n\",\n",
    "      first_matrix[-1,-1],\n",
    "      type(first_matrix[-1,-1]))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Matrices operations are slightly different from vector operations in that they can be performed **element wise with individual values or by combining two matrices**. \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(\"Original matrix:\\n\", first_matrix)\n",
    "\n",
    "scalar_addition_matrix = first_matrix + 5\n",
    "print(\"Adding the value 5 to the matrix:\\n\",scalar_addition_matrix)\n",
    "\n",
    "# you can add/subtract matrices if they are same shape\n",
    "second_matrix = np.array([[1,3],[6,7]])\n",
    "print(\"Are my first and second matrices the same shape?\",\n",
    "      first_matrix.shape == second_matrix.shape)\n",
    "\n",
    "scalar_division_matrix = first_matrix / 5\n",
    "print(\"Dividing the matrix by 5:\\n\",scalar_division_matrix)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Matrix multiplication with another matrix or a properly shaped vector"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Matrices can be multiplied either with other matrices or vectors. Both cases rely on the object you're multiplying with having an appropriate shape (but not necessarily the same shape as the first matrix!).\n",
    "\n",
    "**In order for multiplication of two matrices/vectors AxB to be defined, the number of columns in the first matrix must equal the number of rows in the second operand. The resulting new matrix will have a number of rows equal to the number of rows in the first matrix and the number of columns equal to the second matrix.**\n",
    "\n",
    "To multiply matrices, we use a slightly different notation, calling the `dot` operator on a `NumPy` array:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "third_matrix = np.array([[1,2,4],[1,3,5]])\n",
    "print(\"First matrix:\\n\",first_matrix, first_matrix.shape)\n",
    "print(\"Third matrix:\\n\", third_matrix, third_matrix.shape)\n",
    "print(\"First x Third:\\n\",first_matrix.dot(third_matrix), first_matrix.dot(third_matrix).shape)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Practice\n",
    "Mutiply two matrices of different shapes to return a final matrix of shape:\n",
    "1. 3 by 3\n",
    "2. vector with shape (2,)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Pandas"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So, lets begin by loading in our first dataset, the Movielens 1M dataset.\n",
    "\n",
    "This is a file that contains ~1,000,000 movie ratings taken from a website where users can rate a variety of movies on a 1-5 rating scale. Each rating is comprised of the following information:\n",
    "\n",
    "* A user id, represented by a number (an `int`) \n",
    "* A movie id, also an `int` \n",
    "* A rating, an `int` that should vary from 1-5 (inclusive) \n",
    "* A timestamp, recording when the rating was made in seconds since UNIX epoch time (this is standardly set to 12:00AM January 1, 1970), also an `int`\n",
    "\n",
    "The records in this file are arranged in the following order and format:\n",
    "\n",
    "`UserID::MovieID::Rating::Timestamp`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We will use the `read_csv` function in pandas and try to simply load the dataset **as is** into a variable (actually an object) called `ratingData` and see if that gets it into a usable format (it won't)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ratingData = pd.read_csv(\"./data/movieData/ratings.dat\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you look at the documentation for `read_csv`, you'll see that it is very large and provides for lots of different functionality. \n",
    "\n",
    "As a first pass, we just passed the path to the file in as a ```string``` to the `read_csv` function, without any other arguments. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Lets take a look at the first few rows and see what we get using the `head` function on our newly loaded dataset `ratingData`. \n",
    "\n",
    "The `head` function returns the first 5 rows by default of the DataFrame you call it on. You can change it to be a larger or smaller number by passing in a positive `integer` into `head` as an argument like so: `ratingData.head(20)`.\n",
    "\n",
    "The function `tail` does the exact same thing, except with the last records in the dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ratingData.head(20)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ok, well that looks terrible.\n",
    "Lets diagnose the problems we see: \n",
    "\n",
    "1. Everything is in a single column (so we can't separately look at ratings, user ids,movie ids, or timestamps)!\n",
    "2. The first row is used as the name of the only column (we call this the **header**), which is no good, as the first record shouldn't be the header, but an actual record.\n",
    "3. The timestamp is in a format that doesn't really tell us anything useful about when the ratings occurred.\n",
    "\n",
    "So, lets use some of the additional functionality of `read_csv` to load this dataset in cleanly, and hopefully that will solve problems **1 and 2**."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ratingData = pd.read_csv(\"./data/movieData/ratings.dat\",\n",
    "                         sep = \"::\",names = ['UserID','MovieID','Rating','Timestamp'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "ratingData.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "MUCH BETTER!\n",
    "\n",
    "So, what did we just do?\n",
    "\n",
    "The function `read_csv` has lots of functionality, as I had mentioned (and as you saw when you pulled up its documentation). One of its options is called `sep`, and allows you to provide your own separator for dividing the columns that you have in your dataset. \n",
    "\n",
    "The default separator for `read_csv` is the comma (`,`), since `csv` stands for **c**omma **s**eparated **v**alues. \n",
    "\n",
    "However, since `::` separated the fields in this dataset, we supplied that as an argument (again, as a `string`) to the argument `sep` instead.\n",
    "\n",
    "A separate argument, `names`, allows you to pass in your own list of names, again as `strings`, to `read_csv` to be treated as the column names (or the **header**) of the dataset. Since we knew what the names for the columns should be, we put them in.\n",
    "\n",
    "Now that our dataset looks more reasonable, lets do a couple brief sanity checks and then fix issue **3.**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Lets do a sanity check and make sure that:\n",
    "\n",
    "1. All our data is in the format that we expect (everything is an `int`).\n",
    "2. The size of the data is what we expect.\n",
    "3. The ratings range across the values we expect (1-5 and nothing else).\n",
    "\n",
    "The property `dtypes` is accessible from our `ratingData` object, and tells us the types of the data in all of our columns (which addresses **1.**)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ratingData.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 2. shape of data\n",
    "shape = ratingData.shape\n",
    "rows = shape[0]\n",
    "columns = shape[1]\n",
    "print(shape)\n",
    "print(rows)\n",
    "print(columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# returns all unique values in a column\n",
    "ratingData[\"Rating\"].unique()  \n",
    "# OR\n",
    "print(\"Unique values: \", ratingData.Rating.unique())\n",
    "\n",
    "# return number of unique values in a column\n",
    "print(\"Number of unique values: \", ratingData.Rating.nunique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# counts the number of values for each rating from greatest to least\n",
    "ratingData.Rating.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# basic descriptive statistics of each column\n",
    "ratingData.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# get individual stats\n",
    "print(ratingData.Rating.mean())\n",
    "print(ratingData.Rating.quantile(.25))\n",
    "print(ratingData.Rating.count())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that you have a bit of **pandas** functionality at your disposal, you should be able to give me the answer to the following questions:\n",
    "\n",
    "1. How many users are there in the dataset?\n",
    "2. How many movies are in the dataset?\n",
    "3. What is the median rating in the dataset?\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## WRITE YOUR CODE HERE\n",
    "numUsers = \n",
    "numMovies = \n",
    "medRating = \n",
    "\n",
    "print(\"There are\",numUsers,\"unique users,\",numMovies,\\\n",
    "\" unique movies, with a median rating of\",medRating)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Subsetting the Data Set\n",
    "Often times you will not need all of the data so learning how to pick out what you want is important. \n",
    "\n",
    "1. Select some columns on:\n",
    "    1. Column Name or Index using loc/iloc (also applies to rows)\n",
    "2. Select some rows\n",
    "    1. Boolean mask\n",
    "    2. Column criteria\n",
    "3. Aggregation into groups\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# access multiple columns\n",
    "ratingData[[\"Rating\",\"UserID\"]]\n",
    "##OR\n",
    "multipleColumns = [\"Rating\",\"UserID\"]\n",
    "ratingData[multipleColumns].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# access columns using index\n",
    "ratingData.iloc[:,0:-1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create small slice of dataset\n",
    "small = ratingData.iloc[0:5]\n",
    "small"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sorted_small = small.sort_values(by='MovieID')\n",
    "sorted_small"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# returns the row with the explicit index value\n",
    "print(sorted_small.loc[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# returns the row with the implicit index location\n",
    "print(sorted_small.iloc[0])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Subselection using masks/column conditions:\n",
    "\n",
    "1. You create a condition that can be evaluated to either **true** or **false** for every row in the dataset and store the outcome in a variable (this is traditionally called a **mask**).\n",
    "2. You apply that **mask** onto your `DataFrame` (dataset).\n",
    "\n",
    "Let's try this subselection + application with the ratings in our dataset by only getting all of the really low ratings (lets say low ratings are those that are < 3) in our dataset.\n",
    "\n",
    "We will create a mask that expresses our \"crappy ratings\" condition, and then apply that mask to our dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "crappyRatingMask = ratingData.Rating < 3\n",
    "print(\"Crappy rating mask:\\n\",crappyRatingMask.head())\n",
    "\n",
    "#Contains only rows that were true in the mask\n",
    "crappyRatings = ratingData[crappyRatingMask] \n",
    "crappyRatings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# sanity check of rating values\n",
    "print(crappyRatings.Rating.unique()) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you want to save the crappy mask as a column for future reference:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ratingData['Crappy_Movie'] = ratingData.Rating < 3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ratingData.tail(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# returns same as boolean mask\n",
    "ratingData[ratingData.Crappy_Movie]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(crappyRatings.shape)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Aggregating Data\n",
    "Awesome, now that you know how to do some basic subselection, sorting, and calculations on data, we are going to do something a bit more complicated, and start subdividing our data into groups to be able to answer some more general questions about our dataset.\n",
    "\n",
    "Once you have this functionality down, you will be able to: \n",
    "\n",
    "1. Answer more interesting kinds of questions\n",
    "* Answer the questions above using fewer lines of code \n",
    "\n",
    "Lets say you wanted to know or do the following:\n",
    "\n",
    "1. **In what month did users rate the most movies?**\n",
    "2. **What day of the week had the highest average rating?**\n",
    "\n",
    "Our approach here will be:\n",
    "\n",
    "* Convert timestamp into date time\n",
    "* Use the `groupby` functionality of **pandas** to create subgroups of our ratings based on the `month` the ratings were given.\n",
    "* Apply an aggregating function to these groups to return:\n",
    "  * The `size` of each group (each `month`)\n",
    "  * The `mean` of the `Rating` column within each group"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Grouping can get very complicated, but as a first pass you can think of it as a way to split your dataset into non-overlapping subsets along any axis (along rows or columns, in our case). \n",
    "\n",
    "The values along which you **group** your dataset are traditionally called **keys**, so **each key should be unique to each group, and each group can have at most one key associated with it** .\n",
    "\n",
    "Once you've grouped your dataset, the `GroupBy` object isn't too useful by itself. It becomes useful when you apply a **transformation** to it and get a new dataset back. \n",
    "\n",
    "We typically call this **transformation** an **aggregation**, as we are getting some aggregate value back for each group.\n",
    "\n",
    "The **aggregation** functions we will be using for questions **1. and 2.** are `size` and `mean`.\n",
    "\n",
    "Let's first convert the timestamp column into a recognizable time format for pandas to evaluate."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Lets create a new column by formatting our Timestamp column\n",
    "ratingData['FormattedTimestamp'] = pd.to_datetime(ratingData.Timestamp,unit = 's')\n",
    "del ratingData[\"Timestamp\"]\n",
    "ratingData.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The **dt module** allows you to access specific datetime properties and returns a `Series` object which is how pandas represents a single column. \n",
    "\n",
    "`Series` objects are different from `DataFrame` objects (which we've been working with exclusively thus far) because they can be appended (attached) as new columns to your dataset (which is already a `DataFrame` object) without any problems.\n",
    "\n",
    "So if you want to store the `month` of every row in a new column called `month` then do:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ratingData[\"month\"] = ratingData.FormattedTimestamp.dt.month"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ratingData.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we know the months so we can answer our first question...\n",
    "\n",
    "1. **In what month did users rate the most movies?**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "monthGroups = ratingData.groupby(\"month\")\n",
    "# group by multiple columns: need to pass a list of them [\"year\",\"month\"]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# We get the number of records (rows) in each group in our monthGroups object \n",
    "# using the size function (which is accessible from this object):\n",
    "ratingsPerMonth = monthGroups.size()\n",
    "ratingsPerMonth"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# return month with most ratings\n",
    "monthWithMostRatings = ratingsPerMonth[ratingsPerMonth==ratingsPerMonth.max()]\n",
    "monthWithMostRatings"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now you answer:\n",
    "2. **What day of the week had the highest average rating?**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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": 2
}