05-pandas-aggregates.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Aggregations and Queries in pandas\n",
    "\n",
    "In [notebook 4](04-pandas-basics.ipynb) we saw that pandas provides some useful structures, namely `Series` and `DataFrames`, and we perfomed some basic manipulations of these structures. \n",
    "\n",
    "Both `Series` and `DataFrames` have a resemblance to a classic spreadsheet or database. In this notebook we will see how pandas supports aggregations and transformations which are traditionally associated with relational database and SQL.\n",
    "\n",
    "We begin by defining a pandas DataFrame that is very similar to the DataFrame used in the previous notebook:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "data = {'film':['Little Women', 'Cats', 'Jumanji', 'Star Wars', 'Yesterday'], \n",
    "       'mean views':[908, 102, 3604, 1145, 795],\n",
    "       'date': [np.datetime64('2019-12-25'), np.datetime64('2019-12-20'), \n",
    "                np.datetime64('2019-12-04'), np.datetime64('2019-12-18'), np.datetime64('2019-06-28')],\n",
    "        'month':['Dec', 'Dec', 'Dec', 'Dec', 'Jun'],\n",
    "       'run time':[135, 110, 123, 142, 116],\n",
    "       'theaters':[1093,987,4038,4874, 3860],\n",
    "       'rating':[4,3,3,4,4]\n",
    "    }\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "movie_df = pd.DataFrame(data) \n",
    "\n",
    "movie_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It is common to want to group such data by one column of the data frame.\n",
    "\n",
    "The pandas `.groupby()` function allows us to create a grouped object, which is organised based on the columns passed in the argument of the function. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "grouped = movie_df.groupby('rating')\n",
    "grouped"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can see that `grouped` is a specific pandas object and although we can't easily view such objects we are able to apply functions to those groups. Let's try taking the mean across groups:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "grouped.mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The data now has a `rating` key. The `.mean()` acts on all numeric columns of the DataFrame and returns the mean value across all entries which share the same rating. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also pass a python list of column names to the `.groupby()` function. The DataFrame will be grouped by all of the list entries, in the order that they are given:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "grouped2 = movie_df.groupby(['rating', 'month'])\n",
    "grouped2.mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can see that all films with rating `3` were released in December, and as such only one row corresponds to rating `3`. However, films with rating 4 were released in both December and June, hence there are two rows for this rating. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Querying by values\n",
    "\n",
    "We often want the subset of a dataset that has a particular value.  Pandas makes that easy!  Let's see how to find the movies that were released in December:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "movie_df[movie_df[\"month\"] == \"Dec\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Write an expression to find all of the films with fewer than 1,000 mean views:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# Write an expression to select every row in movie_df with fewer than 1000 mean views"
   ]
  },
  {
   "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.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}