pydata-tutorial-simpsons-CLEAN.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# D’oh! Unevenly spaced time series analysis of The Simpsons in Pandas\n",
    "#### [PyData Seattle 2017 tutorial](https://pydata.org/seattle2017/schedule/presentation/104/)\n",
    "#### [Joe McCarthy](http://interrelativity.com/joe/), Data Scientist, [Indeed](https://www.indeed.com)\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Motivations"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Unevenly spaced opportunities to do unevenly spaced time series data analysis\n",
    "\n",
    "<p><a href=\"http://pandas.pydata.org/\"><img src=\"http://pandas.pydata.org/_static/pandas_logo.png\" style=\"margin: 0px 0px 10px 20px; width: 150px; float: right;\" title=\"pandas\" alt=\"pandas_logo.png\" /></a>I occasionally need to analyze gaps between events of interest, but not so often that I remember how to do it. I end up searching through my notebooks to remind myself which Pandas methods to use, and how to use them. I wanted to create a protypical example notebook that I can more easily reference .. and wanted to share it with others, in case they might also find it useful.</p>\n",
    "\n",
    "### Data.world\n",
    "\n",
    "<p><a href=\"https://data.world\"><img src=\"https://d2ogkq1rg66kh0.cloudfront.net/site-resources/images/fb-image-share.7b15f964.jpg\" style=\"margin: 0px 12px 5px 20px; width: 125px; float: right;\" title=\"data.world\" alt=\"data_world_logo.png\" /></a>The last time I hunted down a notebook containing unevenly spaced time series analysis was around the time I first heard about the [data.world](https://data.world) platform for creating, sharing and collaborating on data sets .. I think of it as a GitHub for data.</p>\n",
    "\n",
    "<p><a href=\"https://www.dataquest.io\"><img src=\"http://data-science-hack.com/wp-content/uploads/2016/04/dataquest-io-1-1.png\" style=\"margin: 0px 0px 5px 20px; width: 125px; float: right;\" title=\"dataquest.io\" alt=\"dataquest-io-logo.png\" /></a>I was further inspired about using data.world after reading a great tutorial created by Josh Devlin at [Dataquest.io](https://www.dataquest.io) on [Turbocharge Your Data Acquisition using the data.world Python Library](https://www.dataquest.io/blog/datadotworld-python-tutorial/). This is also where I discovered the dataset about The Simpsons episodes, which I think is more entertaining and generally accessible than the datasets I typically work with at Indeed (although those are interesting, too).</p>\n",
    "\n",
    "### PyData redemption\n",
    "\n",
    "I gave a tutorial on [Python for Data Science](https://pydata.org/seattle2015/schedule/presentation/8/) at PyData Seattle 2015, wherein I tried to squeeze a 4-hour tutorial into a 2-hour slot. It didn't work out so well, though I did make the [Jupyter Notebook from the tutorial](http://nbviewer.jupyter.org/github/gumption/Python_for_Data_Science/blob/master/Python_for_Data_Science_all.ipynb) publicly available, so attendees could go back over some of the things we rushed through or didn't even get to. \n",
    "\n",
    "For PyData Seattle 2017, I decided to propose a topic that I estimated would take more like an hour, so that we can take a somewhat more leisurely approach .. and, with any luck, we'll be able to get to lunch before the other morning tutorials let out. \n",
    "\n",
    "And I will be making this notebook publicly available as well."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Using Python and Pandas in a Jupyter Notebook"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Among the prerequisites for this tutorial are to have Python, Pandas and Jupyter Notebook installed on your computer. In case anyone has a computer that does not yet meet these requirements, I recommend downloading and installing [Anaconda](https://www.continuum.io/downloads), an open data science platform for Python, which includes Pandas, Jupyter Notebook and a variety of other useful open source Python libraries. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Python 2 vs. Python 3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are 2 major versions of Python in widespread use: [Python 2](https://docs.python.org/2/) and [Python 3](https://docs.python.org/3/). Python 3 has some features that are not backward compatible with Python 2, and some Python 2 libraries have not been updated to work with Python 3. I have been using Python 2, primarily because I use some of those Python 2[-only] libraries, but an increasing proportion of them are migrating to Python 3, and I anticipate shifting to Python 3 in the near future.\n",
    "\n",
    "For more on the topic, I recommend a very well documented IPython Notebook, which includes numerous helpful examples and links, by [Sebastian Raschka](http://sebastianraschka.com/), [Key differences between Python 2.7.x and Python 3.x](http://nbviewer.ipython.org/github/rasbt/python_reference/blob/master/tutorials/key_differences_between_python_2_and_3.ipynb), the [Cheat Sheet: Writing Python 2-3 compatible code](http://python-future.org/compatible_idioms.html) by Ed Schofield ... or [googling Python 2 vs 3](https://www.google.com/q=python%202%20vs%203).\n",
    "\n",
    "In order to make this notebook compatible with Python 2 *or* Python 3, we will import the [`print_function`]((https://docs.python.org/3/library/functions.html#print)) from the  [**`__future__`**](https://docs.python.org/2/library/__future__.html) module (in Python 2, `print` is a [statement](https://docs.python.org/2/reference/simple_stmts.html#print) not a function). We will also import the `division` module from the `future`, as I find [the use of `/` for \"true division\"](https://www.python.org/dev/peps/pep-0238/) - and the use of `//` for \"floor division\" - to be more aligned with my intuition.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from __future__ import print_function, division"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We will also import - using the customary aliases - commonly used libraries in the python scientific stack, and specify that any plots will appear inline within the cell of this notebook (vs. popping up a separate window)."
   ]
  },
  {
   "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",
    "\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Using pandas\n",
    "\n",
    "This tutorial is designed for people who are already familiar with the `pandas` data analysis library.\n",
    "\n",
    "A thorough review of `pandas` concepts is beyond the scope of this session, but for participants who are not sufficiently familiar with `pandas`, I highly recommend the pandas [tutorial (\"10 Minutes to pandas\")](https://pandas.pydata.org/pandas-docs/stable/10min.html) and [Intro to Data Structures](https://pandas.pydata.org/pandas-docs/stable/dsintro.html).\n",
    "\n",
    "Here are a few basic concepts:\n",
    "\n",
    "* A [`DataFrame`](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.html) is a tabular (2-dimensional) data structure with 2 axes: rows (`axis=0`) and columns (`axis=1`)\n",
    "* The columns in a `DataFrame` are one-dimensional arrays called [`Series`](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.html)\n",
    "* Elements of a `DataFrame` can be accessed using labels, positions or boolean vectors\n",
    "  * The labels for a `Series` or the rows of a `DataFrame` are accessible via their `index` attribute\n",
    "  * The labels for each column in a `DataFrame` are accessible via its `columns` attribute\n",
    "  * Individual labels can be specified using brackets or - in most cases - dotted notation\n",
    "  * Positions can be specified using the [slice operators](https://docs.python.org/2/tutorial/introduction.html) for python sequences\n",
    "  \n",
    "Examples of these concepts will be highlighted when we first encounter them.\n",
    "\n",
    "Other important `pandas` concepts and operations used throughout this tutorial include [grouping (split-apply-combine)](http://pandas.pydata.org/pandas-docs/stable/groupby.html) and [merging](http://pandas.pydata.org/pandas-docs/stable/merging.html)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Using the data.world Python Library"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this section, I will borrow heavily - with permission - from Josh Devlin's tutorial, [Turbocharge Your Data Acquisition using the data.world Python library](https://www.dataquest.io/blog/datadotworld-python-tutorial/).\n",
    "\n",
    "There are additional elements incorporated from the [Python SDK documentation](https://data.world/nrippner/explore-the-data-world-python-sdk/workspace/file?filename=ddw_SDK.ipynb) the [Data Wrangling tutorial](https://data.world/nrippner/python-data-wrangling-tutorial/workspace/file?filename=datadotworld_wrangling_tutorial.ipynb) at data.world."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Installing the data.world library"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The data.world Python SDK can be installed via the command line using the [`conda install` utility](https://conda.io/docs/using/pkgs.html#install-a-package-from-anaconda-org):\n",
    "\n",
    "`conda install -c conda-forge datadotworld-py`\n",
    "\n",
    "Alternately, you can use `pip` to install the SDK:\n",
    "\n",
    "`pip install git+git://github.com/datadotworld/data.world-py.git`\n",
    "\n",
    "Next, you need to retrieve and store your data.world API token.\n",
    "\n",
    "* From your data.world account, go to [Settings > Advanced](https://data.world/settings/advanced) and get your API token (the image below is from Josh's tutorial):\n",
    "\n",
    "<a href=\"https://data.world/settings/advanced\"> <img src=\"https://www.dataquest.io/blog/images/data.world_tutorial/api_token.png\" style=\"width: 600px;\" title=\"data.world Advanced Settings\" alt=\"data_world_api_token.png\" /></a>\n",
    "\n",
    "* If you installed the data.world Python SDK in a virtualenv or Conda env, activate that environment. \n",
    "* Run `dw configure` on the command line, which will prompt you for your token:\n",
    "```\n",
    "~ (datadotworld) $ dw configure\n",
    "API token (obtained at: https://data.world/settings/advanced): _\n",
    "```\n",
    "When you enter your token, a `~/.dw/` directory will created in your home directory and your token will be stored in your `~/.dw/config` file.\n",
    "\n",
    "Finally, if you used `pip install` above, you will need to run `pip install datadotworld[PANDAS]` on the command line."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### The Simpsons"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For the benefit of anyone who is not familiar with The Simpsons, here is some context from the [Wikipedia page](https://en.wikipedia.org/wiki/The_Simpsons) for the television show:\n",
    "\n",
    "<blockquote>\n",
    "<p><a href=\"https://en.wikipedia.org/wiki/The_Simpsons\"><img src=\"https://upload.wikimedia.org/wikipedia/en/thumb/0/0d/Simpsons_FamilyPicture.png/220px-Simpsons_FamilyPicture.png\" style=\"margin: 0px 0px 10px 20px; width: 150px; float: right;\" title=\"The Simpsons\" alt=\"Simpsons_FamilyPicture.png\" /></a>The Simpsons is an American animated sitcom created by Matt Groening for the Fox Broadcasting Company. The series is a satirical depiction of working-class life epitomized by the Simpson family, which consists of Homer, Marge, Bart, Lisa, and Maggie. The show is set in the fictional town of Springfield and parodies American culture, society, television, and the human condition.</p>\n",
    "\n",
    "<p>...</p>\n",
    "\n",
    "<p>Since its debut on December 17, 1989, 618 episodes of The Simpsons have been broadcast. Its 28th season began on September 25, 2016. It is the longest-running American sitcom and the longest-running American animated program, and, in 2009, it surpassed Gunsmoke as the longest-running American scripted primetime television series. The Simpsons Movie, a feature-length film, was released in theaters worldwide on July 27, 2007, and grossed over $527 million. On November 4, 2016, the series was renewed for a twenty-ninth and thirtieth season of 22 episodes each, extending the show to 2019.</p>\n",
    "</blockquote>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### The Simpsons data set at data.world"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Josh provides a brief history of this data set in [his tutorial](https://www.dataquest.io/blog/datadotworld-python-tutorial/):\n",
    "\n",
    "> For this tutorial, we’ll be working with a data set of information on the TV show, [The Simpsons](https://en.wikipedia.org/wiki/The_Simpsons). The dataset was scraped by Tod Schenider for his post [The Simpsons by the Data](http://toddwschneider.com/posts/the-simpsons-by-the-data/), for which he made the scraper [available on GitHub](https://github.com/toddwschneider/flim-springfield). [Kaggle user William Cukierski used the scraper to upload the data set](https://www.kaggle.com/wcukierski/the-simpsons-by-the-data), which was then [rehosted on data.world](https://data.world/data-society/the-simpsons-by-the-data).\n",
    "\n",
    "[The data set page on data.world](https://data.world/data-society/the-simpsons-by-the-data) shows four CSV files in the data set:\n",
    "\n",
    "* `simpsons_characters.csv`: Every character appearing in The Simpsons.\n",
    "* `simpsons_episodes.csv`: Every episode of the The Simpsons.\n",
    "* `simpsons_locations.csv`: Every location appearing in The Simpsons.\n",
    "* `simpsons_script_lines.csv`: Most lines from most scripts of the Simpsons.\n",
    "\n",
    "In _this_ tutorial, we'll only be using the data from `simpsons_episodes.csv` and `simpsons_script_lines.csv`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Using data.world’s Python library to explore the data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Continuing with [Josh's tutorial](https://www.dataquest.io/blog/datadotworld-python-tutorial/):\n",
    "\n",
    "> First, let’s import the `datadotworld` library:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import datadotworld as dw"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> We’re going to use the `load_dataset()` function to take a look at the data. When we use `load_dataset()` for the first time, it:\n",
    "> \n",
    "> * Downloads the data set from data.world and caches it in our `~/.dw/` directory\n",
    "> * Returns a `LocalDataset` object representing the data set\n",
    "> \n",
    "> Caching the data set locally is a really neat feature - it allows for quicker subsequent loading, lets you work on the data offline, ensures that your source data is the same each time you run your code, and in the future will support data set versioning. After the first time you call `load_dataset()` for a given dataset, it will load the dataset from the cached version. You can pass `True` to the optional `force_update` parameter if you wish to force a reload from the remote version and overwrite the changes.\n",
    "> \n",
    "> `load_dataset()` has one required parameter, `dataset_key` which you can extract from the URL of the data set on data.world. As an example, our simpsons data set has the URL https://data.world/data-society/the-simpsons-by-the-data, which makes its ID `data-society/the-simpsons-by-the-data`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "lds = dw.load_dataset('data-society/the-simpsons-by-the-data')  # , force_update=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "After loading the dataset, we can verify that it has been locally cached."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "! ls -l ~/.dw/cache/data-society/the-simpsons-by-the-data/latest/data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Getting to know our data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> To take a closer look at our `LocalDataset` object , we can use the `LocalDataset.describe()` method, which returns a JSON object."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# We use pprint as it makes our output easier to read \n",
    "import pprint as pp\n",
    "\n",
    "pp.pprint(lds.describe())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The JSON object has additional information since Josh created his tutorial, which has been inserted below:\n",
    "\n",
    "> Our JSON object has 6 key/value pairs at the top level: \n",
    "* `description`\n",
    "* `homepage` \n",
    "* `keywords` \n",
    "* `name` \n",
    "* `resouces` \n",
    "* `title`\n",
    "\n",
    "> `resources` is a list that contains information about each file in our data.world data set:\n",
    "* `name` \n",
    "* `format`\n",
    "* `path`\n",
    "* `mediatype`\n",
    "* `bytes`\n",
    "\n",
    "> Along with the `LocalDataset.describe()` function, there are three key attributes of our LocalDataset object which we can use to access the data itself:  \n",
    "* `LocalDataset.dataframes`\n",
    "* `LocalDataset.tables`\n",
    "* `LocalDataset.raw_data`\n",
    "\n",
    "> Each of these attributes work the same way, but return the data in a different format."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in [lds.dataframes, lds.tables, lds.raw_data]:\n",
    "    print(i, '\\n')  # pprint does not workon lazy-loaded dicts"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> `LocalDataset.dataframes` returns a dictionary of pandas DataFrame objects, where as `LocalDataset.tables` and `LocalDataset.raw_data` returns the data in dictionaries of Python lists and bytes format respectively. Lists can be useful if we don’t want to use pandas, and bytes is great if we have binary data like images or database files.\n",
    ">\n",
    "> Because of the power of the pandas library, let’s use `LocalDataset.dataframes` to explore and have some fun with our data!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exploratory analysis of The Simpsons data using `pandas`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "data.world provides a number of ways of working with data, including their own version of SQL (dwSQL), SPARQL and an R SDK (see their [Intro to data.world Dataset tutorial](https://data.world/jonloyens/an-intro-to-dataworld-dataset)), but in this tutorial we will focus only on using the Python SDK and using the `pandas DataFrames` in their datasets.\n",
    "\n",
    "We will create a `DataFrame` variable, `df`, to work with data from the `simpsons_script_lines` CSV file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = lds.dataframes['simpsons_script_lines']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The warnings show that we have some columns with mixed types in this `DataFrame`.\n",
    "\n",
    "```\n",
    "/Users/joem/anaconda/lib/python2.7/site-packages/datadotworld/models/dataset.py:192: UserWarning: Unable to set data frame dtypes automatically using simpsons_script_lines schema. Data types may need to be adjusted manually. Error: Integer column has NA values in column 7\n",
    "  'Error: {}'.format(resource_name, e))\n",
    "/Users/joem/anaconda/lib/python2.7/site-packages/datadotworld/util.py:136: DtypeWarning: Columns (4,5,6) have mixed types. Specify dtype option on import or set low_memory=False.\n",
    "  return self._loader_func()\n",
    "```\n",
    "\n",
    "We can use the [`pandas.DataFrame.info()`](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.info.html) function to get more information on the contents of these columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "9 columns have type `object`, which is usually an indicator that the column contains a mix of data types. \n",
    "\n",
    "Sometimes this happens when columns include null (`NaN`) values, so let's show the number of null values along with the number of data types, and include the number of unique values in each column. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i, column_label in enumerate(df.columns):\n",
    "    print('{:2d}: {:20} {:6d} unique values, {:6d} null values, {:2d} data type(s)'.format(\n",
    "        i,\n",
    "        column_label + ':', \n",
    "        df[column_label].nunique(),\n",
    "        df[column_label].isnull().sum(),\n",
    "        len(df[column_label].apply(lambda x: type(x)).value_counts())))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also see what kinds of values are stored in the columns that have mixed data types."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i, column_label in enumerate(df.columns):\n",
    "    if len(df[column_label].apply(lambda x: type(x)).value_counts()) > 1:\n",
    "        print(i, column_label)\n",
    "        print(df[column_label].apply(lambda x: type(x)).value_counts())\n",
    "        print()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data Preparation & Cleaning"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are a number of columns which have more than one data type, e.g., `str` and `float`. There are some other data quality issues in the `DataFrame` created from `simpsons_script_lines.csv`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Another [notebook](pydata-tutorial-simpsons-data-cleaning.ipynb) in the repository contains a more detailed data analysis and cleaning of the `DataFrame`.\n",
    "\n",
    "For now, we'll just import a module with some utility functions and run a function to do the cleaning.\n",
    "\n",
    "If there is time - and interest - toward the end of the tutorial, we can review that notebook."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pydata_simpsons\n",
    "\n",
    "df = pydata_simpsons.clean_simpsons_script_lines(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We will also use data from the `simpsons_episodes.csv` file to map episodes to seasons, so we will create a separate `DataFrame` for working with the episodes data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_episodes = lds.dataframes['simpsons_episodes']\n",
    "len(df_episodes)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_episodes.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_episodes.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The only field we need from the episodes data is `season`, and we'll need the `id` field to map to the `episode_id` field in the `simpsons_script_lines` data, so we'll [merge](http://pandas.pydata.org/pandas-docs/stable/merging.html) those fields from `df_episodes` with `df` (using [`pandas.DataFrame.merge()`](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.merge.html)), and then drop the extraneous `id` column (`axis=1`) from `df_episodes` (using [`pandas.DataFrame.drop()`](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.drop.html))."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df = df.merge(\n",
    "    df_episodes[['id', 'season']],\n",
    "    left_on='episode_id',\n",
    "    right_on='id',\n",
    "    how='left',\n",
    "    suffixes=['', '_y']).drop('id_y', axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The new `season` field will be in the rightmost column of the `DataFrame`, but I would like to see it appear right after the `id` field, so will re-order the columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df = df[[u'id', u'season', u'episode_id', u'number', u'raw_text', u'timestamp_in_ms',\n",
    "       u'speaking_line', u'character_id', u'location_id',\n",
    "       u'raw_character_text', u'raw_location_text', u'spoken_words',\n",
    "       u'normalized_text', u'word_count']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Time Series Analysis"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "From [Wikipedia](https://en.wikipedia.org/wiki/Time_series):\n",
    "\n",
    "> A **time series** is a series of data points indexed (or listed or graphed) in time order. Most commonly, a time series is a sequence taken at successive equally spaced points in time. Thus it is a sequence of discrete-time data. Examples of time series are heights of ocean tides, counts of sunspots, and the daily closing value of the Dow Jones Industrial Average.\n",
    ">\n",
    "> **Time series *analysis*** comprises methods for analyzing time series data in order to extract meaningful statistics and other characteristics of the data."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Time Series data from The Simpsons"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In [The Simpsons dataset on data.world](https://data.world/data-society/the-simpsons-by-the-data), we will use two `DataFrames`, each loaded from its corresponding CSV file.\n",
    "\n",
    "Each data point (row) in the DataFrame we loaded from **`simpsons_script_lines.csv`** represents a distinct script line, indexed by an **`id`** field that indicates the order in which the line occured.\n",
    "\n",
    "Other fields in this `DataFrame` include\n",
    "* **`episode_id`**: an identifier for each episode\n",
    "* **`number`**: an identifier for the position of each line within an episode\n",
    "* **`raw_text`**: the raw text extracted from ???\n",
    "* **`timestamp_in_ms`**: the number of milliseconds since the start of the season when the line occurred\n",
    "* **`speaking_line`**: a boolean indicator of whether the line was a speaking line\n",
    "* **`character_id`**: a unique identifier for each character\n",
    "* **`location_id`**: a unique identifier for each location\n",
    "* **`raw_character_text`**: the full text (name) of the character\n",
    "* **`raw_location_text`**: the raw text (name) of the location\n",
    "* **`spoken_words`**: the text spoken in the line (if any)\n",
    "* **`normalized_text`**: a lowercase version of the `spoken_words`, with most punctuation removed\n",
    "* **`word_count`**: the number of space-delimited tokens in `normalized_text`\n",
    "\n",
    "Each data point in the `DataFrame` loaded from **`simpsons_episodes.csv`** represents a distinct episode, which is also indexed by an **`id`** field that indicates the order in which the episode occured. This `id` field maps to the `episode_id` in `simpsons_script_lines.csv`. The only other field from this `DataFrame` we will use is **`season`**, which is a number indicating the season in which the episode originally aired."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that although there are 600 `id` values in `simpsons_episodes.csv`, there are only 564 `episode_id` values in `simpsons_script_lines.csv`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_episodes.id.nunique(), df.episode_id.nunique(), len(df_episodes[~df_episodes.id.isin(df.episode_id)])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_episodes[~df_episodes.id.isin(df.episode_id)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exercise Set A"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that we have our base datasets, we can do some time series analyses on the data. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Exercise A1: What are the minimum, maximum and mean number of script lines per episode?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You may find it helpful to use the following `pandas` functions:\n",
    "* [`pandas.DataFrame.groupby()`](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.groupby.html)\n",
    "* [`pandas.Series.nunique()`](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.nunique.html)\n",
    "* [`pandas.Series.min()`](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.min.html), [`pandas.Series.max()`](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.max.html), [`pandas.Series.mean()`](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.mean.html) .. and/or [`pandas.core.groupby.DataFrameGroupBy.agg()`](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.core.groupby.DataFrameGroupBy.agg.html)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# your code here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Exercise A2: Show (plot) the distribution of the number of script lines per episode."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You may find it helpful to use the [`pandas.DataFrame.hist()`](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.hist.html) function."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# your code here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Exercise A3: What are the minimum, maximum and mean number of episodes per season?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# your code here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Exercise A4: Show (plot) the distribution of the number of episodes per season."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# your code here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Exercise A5: What are the minimum, maximun and mean number of characters appearing per episode?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# your code here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Exercise A6: Show (plot) the distribution of the number of characters per episode."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# your code here"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exercise Set B"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Some of the interesting questions we might ask about The Simpsons are focused on the characters.\n",
    "\n",
    "In order to simpify analysis of The Simpsons characters, it will be useful to construct a new `DataFrame` containing data associated with each character (vs. data associated with each episode or script line, although we will use data from both sources in constructing the new `DataFrame`).\n",
    "\n",
    "The following exercises will focus primarily on using some standard `pandas` functions, and set the stage for doing some additional time series analysis."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Exercise B1: Determine the number of lines associated with each character"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "_(We will do this together)_\n",
    "\n",
    "First, it is important to note that we have two fields in the `DataFrame` extracted from `simpsons_script_lilnes.csv` that are associated with characters:\n",
    "* `character_id`: a unique identifier for each character\n",
    "* `raw_character_text`: the full text (name) associated in the script with each appearance of a character\n",
    "\n",
    "And it is further important to note that there are more distinct `raw_character_text` values than there are distinct `character_id` values. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.raw_character_text.nunique(), df.character_id.nunique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we group the script lines by `character_id` (using [`pandas.DataFrame.groupby()`](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.groupby.html)) and then gather the distinct `raw_character_text` values associated with each `character_id` (using [`pandas.Series.unique()`](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.unique.html)), the first few rows indicate why there are more `raw_character_text` values than `character_id` values.\n",
    "\n",
    "A given character can have different personae within the context of a show (e.g., \"Marge Simpson\" and \"Young Marge\")."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.groupby('character_id').raw_character_text.unique().head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we group by both `character_id` and `raw_character_text`, and then find the number of rows in each group (using [`pandas.core.groupby.GroupBy.size()`](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.core.groupby.GroupBy.size.html)), and then convert the resulting [`MultiIndex`](https://pandas.pydata.org/pandas-docs/stable/advanced.html) object back into a `DataFrame` (using [`pandas.DataFrame.reset_index()`](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.reset_index.html)), we can create a new DataFrame with a row for each `raw_character_text` value, wherein each row has \n",
    "* `character_id`: a non-unique value which may be shared across other `raw_character_text` rows\n",
    "* `raw_character_text`: a unique full text (name) for the character\n",
    "* a column indicating the number of script lines associated with that `raw_character_text` (labeled `0`)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.groupby(['character_id', 'raw_character_text']).size().reset_index().head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Alternatively, we could use [`pandas.Series.nunique()`](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.nunique.html) to count the number of unique `id` values within each group."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.groupby(['character_id', 'raw_character_text'])['id'].nunique().reset_index().head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can rename the column showing the number of script lines (using [`pandas.DataFrame.rename()`](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.rename.html)), and assign the resulting `DataFrame` to a new variable, `df_characters`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df_characters = df.groupby(['character_id', 'raw_character_text'])['id'].nunique().reset_index().rename(\n",
    "    columns={'id': 'num_lines'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_characters.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can sort the characters based on the number of lines they have (using [`pandas.DataFrame.sort_values()`](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.sort_values.html)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_characters.sort_values('num_lines', ascending=False).head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can exclude the null character by restricting the rows to those for which `character_id>0` or those for which `raw_character_text!=''`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_characters[df_characters.character_id>0].sort_values('num_lines', ascending=False).head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Exercise B2: Determine the number of episodes in which each character appears."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "More specifically: add a new column to `df_characters`, named `num_episodes`, containing a count of the number of `episode_id` values associated with each `raw_character_text` value.\n",
    "\n",
    "It may be useful to break this down into two steps:\n",
    "* Create a new `DataFrame` containing the number of `episode_id` values associated with each `raw_character_text` value (hint: you might use  [`pandas.Series.nunique()`](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.nunique.html) again)\n",
    "* [Merge](http://pandas.pydata.org/pandas-docs/stable/merging.html) that `DataFrame` with the existing `df_characters` (using [`pandas.DataFrame.merge()`](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.merge.html))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# your code here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Exercise B3: Determine the first and last episodes in which each character appears"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "More specifically, add the following columns to `df_characters`:\n",
    "* `first_episode_id`: the first (smallest) `episode_id` in which the associated `raw_character_text` value appears\n",
    "* `last_episode_id`: the last (largest) `episode_id` in which the associated `raw_character_text` value appears\n",
    "\n",
    "You can either follow the pattern in the previous exercise - twice - or combine the two into a single grouping (using the [`pandas.core.groupby.DataFrameGroupBy.agg()`](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.core.groupby.DataFrameGroupBy.agg.html)) before merging."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# your code here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Exercise B4: Determine the number of seasons, the first season and the last season in which a character appears"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "More specifically, add the following columns to `df_characters`:\n",
    "* `num_seasons`: the number of distinct `seasons` in which the associated `raw_character_text` appears\n",
    "* `first_season`: the first `season` in which the associated `raw_character_text` value appears\n",
    "* `last_episode_id`: the last `season` in which the associated `raw_character_text` value appears\n",
    "\n",
    "You should be able to modify the code you wrote for Exercises 2 and 3 above to address this task."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# your code here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Your `DataFrame` should look similar to the one below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df_characters = pydata_simpsons.create_simpsons_characters_dataframe(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_characters.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exercise Set C"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that we have `df_characters` constructed, we can do some additional analysis (more easily)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Exercise C1: What are the minimum, maximum and mean number of script lines per character?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# your code here"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Unevenly spaced time series analysis"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "As noted earlier (from the Wikipedia article on Time series):\n",
    "\n",
    "> Most commonly, a time series is a sequence taken at successive equally spaced points in time\n",
    "\n",
    "Unevenly spaced time series analysis involves data points that are spaced over a sequence of unequal time intervals. The sizes of the intervals themselves are often a focus of such analysis.\n",
    "\n",
    "Examples of such analyses I've done at Indeed include\n",
    "* identifying \"repeat business\" from Indeed Hire clients, based on different definitions of \"repeat\", by analyzing the intervals between the dates on which new job requisitions from the same client were created\n",
    "* determining how much time Indeed Hire recruiters spend reviewing candidate resumes and cover letters, by analyzing the intervals between candidate profile dispositions\n",
    "\n",
    "Within the context of this tutorial, we'll restrict our focus to unevenly spaced time series analysis of The Simpsons."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Identify scene changes (an unevenly spaced time series analysis)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "One potential indicator of scene changes is changes of location.\n",
    "\n",
    "One way to identify changes of location is to create a new column with the location id of the previous script line and then look for rows in which the previous location id is different from the [current] location id."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It is important to remember that there is noise in the data, including script lines for which no location information is available. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "(len(df[df.location_id==0]), \n",
    " len(df[df.raw_location_text=='']), \n",
    " len(df[(df.location_id==0) & (df.raw_location_text=='')]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df[df.location_id==0].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As with character data, some `location_id` values are associated with more than one `raw_location_text` value."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "(df.location_id.nunique(), \n",
    " df.raw_location_text.nunique(), \n",
    " df.raw_location_text.str.lower().nunique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[df.location_id>0].raw_location_text.value_counts().head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[df.location_id>0].raw_location_text.value_counts().tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In the context of this tutorial, we will simply forge ahead, noting that the results below are, at best, approximate."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exercise Set D"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Exercise D1: Add a column, `prev_location_id`, to contain the previous  `location_id` for each script line within a given season"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The [`pandas.DataFrame.shift()`](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.shift.html) function can be used to return the rows of a `DataFrame` shifted by some offset (the default offset is 1 row). Positive offsets can be interpreted as shifting \"down\", which allows us to easily access previous rows."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.location_id.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.location_id.shift().head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also use negative offset values to shift \"up\" for next rows."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.location_id.shift(-1).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We'll want to first group by episode."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.groupby(['episode_id']).location_id.shift().head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And to keep things consistent with the `location_id` field, we can substitute zero for null (`NaN`) values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.groupby('episode_id').location_id.shift().fillna(0).astype('int64').head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df.loc[:, 'prev_location_id'] = df.groupby('episode_id').location_id.shift().fillna(0).astype('int64')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[['location_id', 'prev_location_id']].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Exercise D2: How many scene changes are there across all episodes?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we conveniently ignore the fact that we have rows with missing locations (where `location_id==0`), we can compute this by simply counting the number of rows where the `location_id` and `prev_location_id` differ."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "len(df[df.location_id != df.prev_location_id])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Most script lines take place within the same scene (location)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "len(df[df.location_id == df.prev_location_id])"
   ]
  }
 ],
 "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.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}