Data Manipulation with Python.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<center>\n",
    "  <h1>Digital Tools and Methods for the Humanities and Social Sciences</h1>\n",
    "  <img src=\"https://github.com/simonwiles/colab_workshops/raw/master/cidr-logo.no-text.240x140.png\" alt=\"Center for Interdisciplinary Digital Research @ Stanford\"/>\n",
    "</center>\n",
    "\n",
    "# Data Manipulation with Python\n",
    "\n",
    "### Instructors\n",
    "- Scott Bailey (CIDR), <em>scottbailey@stanford.edu</em>\n",
    "- Simon Wiles (CIDR), <em>simon.wiles@stanford.edu</em>\n",
    "\n",
    "### Goal\n",
    "By the end of this workshop, we hope you'll be able to load in data into a Pandas `DataFrame`, perform basic cleaning and analysis, and create visualizations of some relevant aspects of a dataset.  For most of this workshop we will work with a dataset prepared from the [IMDb Datasets](https://www.imdb.com/interfaces/) and the [OMDb API](https://www.omdbapi.com/).\n",
    "\n",
    "### Topics\n",
    "- Pandas Series and DataFrame\n",
    "- Loading data in, null and missing data\n",
    "- Describing data\n",
    "- Column manipulation\n",
    "- String manipulation\n",
    "- Split-Apply-Combine\n",
    "- Plotting:\n",
    "  - Basic charts (line, bar, pie)\n",
    "  - Histograms\n",
    "  - Scatter plots\n",
    "  - Boxplots, violinplots\n",
    "  \n",
    "<mark>TODO: the above should probably be reworked</mark>\n",
    "\n",
    "### Jupyter Notebooks and Google Colaboratory\n",
    "\n",
    "Jupyter notebooks are a way to write and run Python code in an interactive way. They're quickly becoming a standard way of putting together data, code, and written explanations or visualizations into a single document and sharing that. There are a lot of ways that you can run Jupyter notebooks, including just locally on your computer, but we've decided to use Google's Colaboratory notebook platform for this workshop.\n",
    "\n",
    "Using the Google Colaboratory platform allows us to focus on learning and writing Python in the workshop rather than on setting up Python, which can sometimes take a bit of extra work depending on platforms, operating systems, and other installed applications. If you'd like to install a Python distribution locally, though, we have some instructions (with gifs!) on installing Python through the Anaconda distribution, which will also help you handle virtual environments: https://github.com/sul-cidr/Workshops/wiki/Installing-and-Configuring-Anaconda-and-Jupyter-Notebooks\n",
    "\n",
    "If you run into problems, or would like to look into other ways of installing Python or handling virtual environments, feel free to send us an email (contact-cidr@stanford.edu) or visit us during our [consulting hours](https://library.stanford.edu/research/cidr/consulting).\n",
    "\n",
    "### Environment\n",
    "If you would prefer to use Anaconda or your own local installation of python or Jupyter Notebooks, for this workshop you will need an environment with the following packages installed and available:\n",
    "- `pandas`\n",
    "- `matplotlib`\n",
    "- `sqlalchemy`\n",
    "- `seaborn` (available in the `conda-forge` channel if you're on Anaconda)\n",
    "\n",
    "Please note that we will likely not have time during the workshop to support you with problems related to a local environment, and we do recommend using the Colaboratory notebooks if you are at all unsure.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. What is Pandas?\n",
    "\n",
    "Pandas is a high-level data manipulation tool first created in 2008 by Wes McKinney.  The name is derived from the term “panel data,” an econometrics term for data sets that include observations over multiple time periods for the same individuals.<sup>[[wikipedia](https://en.wikipedia.org/wiki/Pandas_(software))]</sup>\n",
    "\n",
    "From Jake Vanderplas’ book [**Python Data Science Handbook**](http://shop.oreilly.com/product/0636920034919.do) (from which some code excerpts are used in this workshop):\n",
    "\n",
    "> Pandas is a newer package built on top of NumPy, and provides an efficient implementation of a `DataFrame`. `DataFrame`s are essentially multidimensional arrays with attached row and column labels, and often with heterogeneous types and/or missing data. As well as offering a convenient storage interface for labeled data, Pandas implements a number of powerful data operations familiar to users of both database frameworks and spreadsheet programs."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# The two lines below configure how our outputs are shown in this notebook\n",
    "#  environment.  They need not concern us now.\n",
    "pd.set_option('display.max_rows', 20)\n",
    "pd.DataFrame._repr_html_ = \\\n",
    "    lambda self: ('<style>table.dataframe td {white-space: nowrap}</style>' +\n",
    "                  self.to_html(max_rows=10, show_dimensions=True, notebook=True))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.1. What does Pandas *do*?\n",
    "\n",
    "<mark>TODO: wip</mark>\n",
    "* Reading and writing data from persistent storage\n",
    "* Cleaning, filtering, and otherwise preparing data\n",
    "* Calculating statistics and analyzing data\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "... but perhaps we should let Pandas introduce itself:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.2. Where can I get more help with Pandas?\n",
    "\n",
    "The [Pandas website](https://pandas.pydata.org/) and [online documentation](http://pandas.pydata.org/pandas-docs/stable/) are useful resources, and of course the indispensible [Stack Overflow has a \"pandas\" tag](https://stackoverflow.com/questions/tagged/pandas).  There is also a (much younger, much smaller) [sister site dedicated to Data Science questions that has a \"pandas\" tag](https://datascience.stackexchange.com/questions/tagged/pandas) too."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.isnull?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Introduction to `DataFrame`  s and `Series`\n",
    "\n",
    "The main data structure that Pandas implements is the `DataFrame`, and a `DataFrame` is composed of one or more `Series` and, optionaly, an `Index`.  \n",
    "\n",
    "A `DataFrame` is a two-dimensional array with flexible row indices and flexible column names. It can be thought of as a generalization of a two-dimensional NumPy array, or a specialization of a dictionary in which each column name maps to a `Series` of column data.\n",
    "\n",
    "A `Series` is a one-dimensional array of indexed data. It can be thought of as a specialized dictionary or a generalized NumPy array.\n",
    "\n",
    "A `DataFrame` is made up of `Series` in a similar way in which a table is made up of columns. The only restriction is that each column must be of the same data type.  Many of the operations that can be performed on a `DataFrame` can also be performed on an individual `Series`.\n",
    "\n",
    "\n",
    "<img src=\"https://github.com/simonwiles/colab_workshops/raw/master/dataframes.png\" />"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Creating `DataFrame`s and loading data\n",
    "\n",
    "There are a great many ways to create a Pandas `DataFrame` -- we can build one ourselves in lower-level Python datatypes, of course, but Pandas also provides methods to load data in from common storage and serialization formats."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a title=\"PerryPlanet [Public domain], via Wikimedia Commons\" href=\"https://commons.wikimedia.org/wiki/File:Bayarea_map.svg\" style=\"float:right\"><img width=\"256\" alt=\"Bayarea map\" src=\"https://upload.wikimedia.org/wikipedia/commons/thumb/7/78/Bayarea_map.svg/512px-Bayarea_map.svg.png\"></a>\n",
    "### 3.1. Introduction to `DataFrame`s\n",
    "\n",
    "The simplest way to generate a `DataFrame` is to create it directly from a `dict` of `list`s:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = {\n",
    "    \"county\": [\"Alameda\", \"Contra Costa\", \"Marin\", \"Napa\", \"San Francisco\", \"San Mateo\", \"Santa Clara\", \"Solano\", \"Sonoma\"],\n",
    "    \"county seat\": [\"Oakland\", \"Martinez\", \"San Rafael\", \"Napa\", \"San Francisco\", \"Redwood City\", \"San Jose\", \"Fairfield\", \"Santa Rosa\"],\n",
    "    \"population\": [1494876, 1037817, 250666, 135377, 870887, 711622, 1762754, 411620, 478551],\n",
    "    \"area\": [2130, 2080, 2140, 2040, 600.59, 1930, 3380, 2350, 4580]\n",
    "}\n",
    "bay_area_counties = pd.DataFrame(data)\n",
    "bay_area_counties[['county', 'county seat']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Pandas has automatically created an `Index` on this `DataFrame` ([0..8]), but we can also specify our own `Index` when we instantiate the frame ourselves:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "bay_area_counties = pd.DataFrame(data, index=[\"Ala\", \"Con\", \"Mar\", \"Nan\", \"SF\", \"SM\", \"SC\", \"Sol\", \"Son\"])\n",
    "bay_area_counties"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This allows us to `loc`ate a specific reference using the key in the `Index`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "bay_area_counties.loc['Ala']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also set an `Index` at any time after the `DataFrame` has been created, either by adding a new index:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "bay_area_counties = pd.DataFrame(data)\n",
    "bay_area_counties.index = [\"Ala\", \"Con\", \"Mar\", \"Nan\", \"SF\", \"SM\", \"SC\", \"Sol\", \"Son\"]\n",
    "bay_area_counties"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "or by choosing one of the existing columns to become the index: <mark color=\"#fcf8e3\">(note the use of `inplace=True`)</mark>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "bay_area_counties = pd.DataFrame(data)\n",
    "bay_area_counties.set_index('county', inplace=True)\n",
    "bay_area_counties"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "bay_area_counties.loc['Santa Clara']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.2. Reading data from persistent storage\n",
    "\n",
    "However, most of the time we're more likely to be reading data in from an external source of some kind, and Pandas has us well covered here.\n",
    "\n",
    "First, let's grab some data into our Colaboratory Notebook environment so that we can work with it locally:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "!mkdir -p workshop_data\n",
    "!wget https://raw.githubusercontent.com/simonwiles/colab_workshops/master/sample_data/imdb_top_1000.csv -O workshop_data/imdb_top_1000.csv"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 3.2.1. CSV files\n",
    "Reading in data from CSV files is as simple as:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_frame = pd.read_csv('workshop_data/imdb_top_1000.csv')\n",
    "data_frame"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice again that Pandas has created a default `Index` for this `DataFrame` -- we probably want the `imdbID` column to be the `Index`, and we can set that after import, as above, or we can specify it when loading the CSV initially:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_frame = pd.read_csv('workshop_data/imdb_top_1000.csv', index_col='imdbID')\n",
    "data_frame.head(4) # the `head` method defaults to five if called without an argument\n",
    "                   # a `tail` method is also available with the same semantics"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 3.2.2. Reading data from JSON Files\n",
    "\n",
    "JSON files can be loaded in a similarly straightforward way.\n",
    "\n",
    "There are two things to note here:\n",
    "\n",
    "1. the nature of JSON as a file format is such that the `Index` is explicit, and Pandas will set it correctly for us initially.\n",
    "2. We're loading the data directly over HTTP(S) here -- Pandas `read_...` methods can accept a local file path or a URL, and Pandas will take care of fetching the data for you."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.read_json('https://raw.githubusercontent.com/simonwiles/colab_workshops/master/sample_data/imdb_top_1000.json')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 3.2.3. Reading data via a SQL query\n",
    "\n",
    "We can also load data from relational databases or other datastores that export a SQL-compatible interface.  For this example we'll download a simple SQLite database file to operate on, but Pandas’ `read_sql*` methods can accept a `connection` object that is predicated on a remote database server if required."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "!pip install sqlalchemy\n",
    "!mkdir -p workshop_data\n",
    "!wget https://raw.githubusercontent.com/simonwiles/colab_workshops/master/sample_data/imdb_top_1000.sqlite -O workshop_data/imdb_top_1000.sqlite"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine\n",
    "engine = create_engine(\"sqlite:///workshop_data/imdb_top_1000.sqlite\", echo=False)\n",
    "pd.read_sql_query(\"SELECT * FROM imdb_top_1000;\", con=engine, index_col='imdbID')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 3.2.3. Other input formats\n",
    "\n",
    "Pandas also has methods that allow it to read data directly from other formats, including those used by Microsoft Excel, Stata, SAS, and Google Big Query.  More details are available from the [Pandas documentation](https://pandas.pydata.org/pandas-docs/stable/user_guide/io.html)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.3. Writing `DataFrames` back out to persistent storage"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Pandas makes writing data to persistent storage formats similarly convenient.  Methods are available to write to most of the formats Pandas can read, including all those demonstrated above."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_frame.to_csv('imdb_data_2.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. Working with `DataFrame`s\n",
    "\n",
    "Let's begin by loading our dataset of the top 1,000 ranked films on imDB.\n",
    "\n",
    "<mark>In the same way that it's common to `import pandas as pd`, it's common to use `df` as an identifier for generic `DataFrame`s, especially in tutorials and demos.  For anything other than interactive sessions or throw-away scripts, I strongly recommend using good descriptive identigiers for your `DataFrame`s!</mark>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv(\n",
    "    'https://raw.githubusercontent.com/simonwiles/colab_workshops/master/sample_data/imdb_top_1000.csv', \n",
    "    index_col='imdbID')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4.1. Exploring `DataFrame`s\n",
    "\n",
    "Pandas provides a host of ways to explore our data, which is especially useful when we're getting to know a new dataset or source."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.at['tt0111161', 'Title']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.iat[0, 0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.Rated.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.Rated.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.imdbVotes.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4.2. Indexing and slicing\n",
    "Accessing columns can be done using the dot notation, `df.column_name`, or the dictionary notation, `df['column_name']`.  This returns a `Series` object."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.Title"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "type(df.Title)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`DataFrame`s can be sliced to return a subset of the available columns -- this returns a new `DataFrame` object."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[['Title', 'Director']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "type(df[['Title', 'Director']])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`Series` object and other subsets of `DataFrame`s preserve the indices of the `DataFrame` from which they are derived, which makes further operations such as merging or columns manipulation possible."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`DataFrame`s are designed to operate at the column level, not at the row level. However, a subset of rows can be visualized easily using a slice like in any Python list."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[10:15]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can chain these operations together, as long as we remember what we are returning in each link of the chain."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[10:15].Production"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.Production[10:15]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[['Production']][10:15]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# .reset_index() returns a copy of the DataFrame with the default Index of simple ordinals.\n",
    "df[[\"Title\", \"Plot\"]].reset_index().iloc[2:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[[\"Title\", \"Plot\"]].loc['tt7286456':'tt5813916']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<mark>Notice how in the `.iloc` example, record #5 is omitted, in usual python slice fashion, but in the `.loc` example, the observation with `imdbID == tt5813916` is *included* in the result.</mark>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 4.2.1 Copying and sorting `DataFrame`s\n",
    "\n",
    "`DataFrame`s provide the `.sort_values()` method to allow sorting on multiple columns.  Typically we want to sort our data temporarily at time of output -- either as we’re displaying or saving it:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.sort_values(['Year'], ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "However, sometime we actually want to change the way in which the observations are ordered in a `DataFrame` in a way which persists, and we can do this using the `inplace=True` argument to the `.sort_values()` method.\n",
    "\n",
    "To demonstrate this, we're going to reset the index on the `DataFrame` so that observations are given a simple ordinal index.  Since we don't want to change our `DataFrame` for the following examples, we'll make a `.copy()` to operate on."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_copy = df.copy()\n",
    "df_copy.reset_index(inplace=True)\n",
    "df_copy.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_copy.sort_values(['Year'], ascending=False, inplace=True)\n",
    "df_copy.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It is also possible to sort by multiple columns at once:\n",
    "\n",
    "(Notice how the `ascending` kwarg takes a list that applies to the list of columns to sort on in corresponding order.)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_copy.sort_values(['Year', 'Title'], ascending=[False, True]).head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice too that since we didn't use `inplace=True` in the previous example, our `DataFrame` remains sorted as before:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_copy.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 4.2.2 Activity\n",
    "\n",
    "Given the `DataFrame` defined above, write an expression to extract a `DataFrame` with the columns `Title`, `Year`, `imdbRating`, and `imdbVotes`. Show the first 5 rows in order of highest rating and then most votes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Write your code here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4.3. Expressions and assignment\n",
    "<mark>TODO: intro.</mark>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Operations performed on a column, or `Series`, are broadcast to each of the elements."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# convert the running times to hours\n",
    "df.Runtime / 60"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Simple string concatenation can be performed in the same manner:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.Title + '(' + df.Rated + '), directed by: ' + df.Director"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "as are boolean operations:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.Year < 2000"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "By itself this is not terribly useful, but expressions of this kind are very powerful when passed to a `DataFrame` to select content:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[df.Production == 'Paramount Pictures']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[df.Year < 2000]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Any expression that evaluates to a `Series` of boolean values (`True` or `False`) and shares the same `Index` as the source `DataFrame` can be used.  Complex conditions can be assembled using bitwise logical operators `&`, `|`, and `~` to create simple but powerful filters."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# returns a `Series`\n",
    "df[(df.Year < 2000) & (df.imdbRating > 8)].Title"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# returns a `DataFrame`\n",
    "df.loc[(df.Year < 2000) & (df.imdbRating > 8), ['Title', 'Year', 'imdbRating']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `.str` property gives access to string variables in a broadcast fashion, such that they can be manipulated:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.Actors.str.split(', ')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can make use of `.str` in expressions:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# select records with Oscar nominations or wins\n",
    "df[df.Awards.str.contains('Oscar') == True]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[df.Director.apply(lambda a: len(a.split(', ')) > 3)][['Title', 'Director']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 4.3.1. Assignment to `DataFrame`s\n",
    "\n",
    "It is also possible to assign values directly to columns or cells in a data frame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_copy = df.copy()\n",
    "# create a new column based on an existing one\n",
    "df_copy['Runtime (hrs)'] = df_copy.Runtime / 60\n",
    "\n",
    "# edit a single value\n",
    "df_copy.at['tt0111161', 'Title'] = 'Rita Hayworth and Shawshank Redemption'\n",
    "\n",
    "df_copy[['Title', 'Runtime', 'Runtime (hrs)']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 4.3.2. Activity\n",
    "\n",
    "Returning to our “Bay Area Counties” data from earlier, write an expression to calculate the population density of each county, and assign it to a new column on the `DataFrame`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = {\n",
    "    \"county\": [\"Alameda\", \"Contra Costa\", \"Marin\", \"Napa\", \"San Francisco\", \"San Mateo\", \"Santa Clara\", \"Solano\", \"Sonoma\"],\n",
    "    \"county seat\": [\"Oakland\", \"Martinez\", \"San Rafael\", \"Napa\", \"San Francisco\", \"Redwood City\", \"San Jose\", \"Fairfield\", \"Santa Rosa\"],\n",
    "    \"population\": [1494876, 1037817, 250666, 135377, 870887, 711622, 1762754, 411620, 478551],\n",
    "    \"area\": [2130, 2080, 2140, 2040, 600.59, 1930, 3380, 2350, 4580]  # data is in km²\n",
    "}\n",
    "bay_area_counties = pd.DataFrame(data)\n",
    "bay_area_counties"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Write your code here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4.4. Cleaning and manipulating `DataFrame`s"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 4.4.1. Converting data types\n",
    "\n",
    "<mark>More here!</mark>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Released'].astype('datetime64')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.to_datetime(df['Released'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Released'].apply(pd.to_datetime)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['imdbVotes'].dropna()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 4.4.2 `.apply()`\n",
    "\n",
    "The fundamental way of manipulating the contents of `DataFrame`s is by using the `apply()` method.  `apply()` takes a function as an argument, and `apply`s the function to each element in the container it’s called on."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def count_genres_naive(text):\n",
    "    genres = text.split(\", \")\n",
    "    return len(genres)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.Genre"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.Genre.apply(count_genres_naive)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For the purposes of this workshop, the version of the dataset we are currently working with has had some data deliberately removed, and unfortunately our `count_genres_naive` function does not know how to handle missing data.\n",
    "\n",
    "Pandas uses the `NaN` datatype from the underlying `numpy` package to represent missing data, and this datatype is based on the primitive `float` type, which is why the `Attribute` error reads as it does."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[pd.isna(df.Genre)][['Title', 'Genre']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We could handle this problem in a number of ways:\n",
    "1. we could drop the observations with missing values immediately (and temporarily) before we apply our count function;\n",
    "2. we could modify our `count_genres_naive` function to handle the missing values appropriately;\n",
    "3. we could fix the data manually by assigning values where they are missing; or\n",
    "4. we could clean the dataset when we initially import it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.Genre.dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.Genre.dropna().apply(count_genres_naive)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "One possible problem with this approach is that we don't get a count for the observations with missing values.  This means that if we want to enrich our original `DataFrame` with a new column containing the genre count, our count column will also be populated with `NaN`s:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Genre Count'] = df.Genre.dropna().apply(count_genres_naive)\n",
    "df[pd.isna(df.Genre)][['Genre', 'Genre Count']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this case, we might conclude that since the data is missing, the appropriate value for “genre count” is `0`, and we can write a less naïve operator function that handles this for us:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def count_genres(text):\n",
    "    if pd.isna(text):\n",
    "        return 0\n",
    "    genres = text.split(\", \")\n",
    "    return len(genres)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Genre Count'] = df.Genre.apply(count_genres)\n",
    "df[pd.isna(df.Genre)][['Genre', 'Genre Count']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For the sake of our workshop, however, the dataset has been modified so that `Genre` has been removed where it should be the single value “Comedy”.  Since we know, therefore, what the value should be for all observations where `Genre` is missing, we have the option to simply update the `DataFrame` directly in the following way:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc[pd.isna(df.Genre), 'Genre'] = 'Comedy'\n",
    "df[pd.isna(df.Genre)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We might be done at this point, but it might also be a good idea to check if there are other missing values in our dataset:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.isnull().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 4.4.3 Manipulating / cleaning on import\n",
    "\n",
    "With this in mind, then, and especially when creating re-usable scripts for repetitive tasks or processes we want to iterate over, a common ‘best practice’ is to handle many data transformations when the `DataFrame` is first populated."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "column_types = {\n",
    "    # 'imdbVotes': 'Int64',\n",
    "    'Year': int\n",
    "}\n",
    "fill_nans = {\n",
    "    'Genre': 'Comedy',\n",
    "    'imdbVotes': 0,\n",
    "    'Rated': '',\n",
    "    'Awards': '',\n",
    "    'Metascore': '',\n",
    "    'BoxOffice': '',\n",
    "    'Production': '',\n",
    "    'RottenTomatoes': ''\n",
    "}\n",
    "date_columns = ['Released']\n",
    "df = pd.read_csv(\n",
    "    'https://raw.githubusercontent.com/simonwiles/colab_workshops/master/sample_data/imdb_top_1000.csv',\n",
    "    parse_dates=date_columns,\n",
    "    index_col='imdbID',\n",
    "    dtype=column_types).fillna(value=fill_nans)\n",
    "df.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.isnull().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 4.4.4 Activity\n",
    "\n",
    "Add a new column `language_count` to the `DataFrame` and show movies with more than 1 language."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Write your code here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4.5. Aggregating data\n",
    "\n",
    "But what about the most-featured Director in the top 1000 list?  Or the average rating for movies classified as “Comedy”?  For these kinds of operations we need to compute across aggregations of the dataset.  In Pandas this may be accomplished by means of `.groupby()` operation followed by an `.aggregate()` function. Aggregates can be considered a form of `.apply()` that operates on a collection of observations at once (in these cases, on the collections created by `.groupby()`.\n",
    "\n",
    "<img src=\"https://github.com/simonwiles/colab_workshops/raw/master/aggregation.png\">\n",
    "\n",
    "Those who are familiar with the `R` programming language, may recognize this as the “Split-Apply-Combine” approach:[<sup>*</sup>](#fn)\n",
    "- *Split* a dataset into relevant subsets;\n",
    "- *Apply* a function to each subset;\n",
    "- *Combine* all the pieces back together.\n",
    "\n",
    "<hr>\n",
    "<span id=\"fn\">*</span>  A classic paper on the Split-Apply-Combine methodology is available here: [“The Split-Apply-Combine Strategy for Data Analysis”](https://www.jstatsoft.org/article/view/v040i01/v40i01.pdf)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.groupby('Director')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.groupby('Director')[['Title']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.groupby('Director')[['Title']].aggregate(lambda values: len(values))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can then sort on the aggregated values to show the highest (and lowest) ranking Directors among our top 1,000 movies:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.groupby('Director')[['Title']].aggregate(len).sort_values(['Title'], ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we want to aggregate across the combination of, e.g., Director and Production credit, the `.group_by()` method will accept a list of columns:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "(df.groupby(['Director', 'Production'])[['Title']]\n",
    "   .aggregate(len)\n",
    "   .sort_values(['Title'], ascending=False)\n",
    "   .head(10))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.pivot_table(\n",
    "    index=['Director', 'Production'],\n",
    "    values=['Title'],\n",
    "    aggfunc=len\n",
    ").sort_values('Title', ascending=False).head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4.5.1. Activity\n",
    "\n",
    "Show the top five years for movies categorized as \"Comedy\".\n",
    "\n",
    "(Hint: don't try to parse-out the different values in the “Genre” column -- just test for the presence of the word “Comedy”, e.g. `df.Genre.str.contains('Comedy')`.)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Write your code here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5. Visualization\n",
    "\n",
    "Pandas also provides some utilities to create basic plots just by calling `plot()` on a `Series` or `DataFrame`. But first we need to tell Jupyter that we are going to plot some charts using the plotting library `matplotlib`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# enables inline plotting in Jupyter using matplotlib\n",
    "%matplotlib inline\n",
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.groupby('Year')[['Year']].aggregate(len).plot()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Each time you call `plot()` an `AxesSubplot` object is returned, and these are automatically rendered by the Jupyter notebook environment. `AxesSubplot` objects are objects of the underlying `matplotlib` library for plotting in Python, and as such, lots of different options can be passed to control the output."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ax = df.groupby('Year')[['Year']].aggregate(len).plot(\n",
    "    kind=\"bar\",\n",
    "    figsize=(15, 5),\n",
    "    title=\"# Movies per Year\",\n",
    "    legend=None\n",
    ")\n",
    "ax.set_ylabel(\"# Movies\")\n",
    "ax.set_xlabel(\"Year of Release\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "plt.style.available"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "with plt.style.context('ggplot'):\n",
    "    df.groupby('Year')[['Year']].aggregate(len).plot()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "with plt.xkcd():\n",
    "    df.groupby('Year')[['Year']].aggregate(len).plot()\n",
    "plt.rcdefaults()  # this is needed as the XKCD style is a special case"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import seaborn as sns\n",
    "df.groupby('Year')[['Year']].aggregate(len).plot()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fig, ax = plt.subplots(1, figsize=(15, 5))\n",
    "df['imdbVotes'].hist(ax=ax, bins=15, density=True, color='lightseagreen')\n",
    "df['imdbVotes'].plot(ax=ax, kind='kde', xlim=(0, 2200000), style='r--')\n",
    "ax.set_title('Histogram of # votes')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fig, ax = plt.subplots(1, figsize=(6, 6))\n",
    "df.boxplot(column='imdbVotes', by='Rated', grid=False, ax=ax, sym='')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fig, ax = plt.subplots(1, figsize=(6, 6))\n",
    "sns.violinplot(y=df['imdbVotes'], grid=False, ax=ax)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fig, ax = plt.subplots(1, figsize=(15, 5))\n",
    "ax.scatter(x=df.Year, y=df.imdbVotes)\n",
    "ax.set_ylabel('# Votes')\n",
    "ax.set_xlabel('Year')\n",
    "ax.set_title('# Votes by Year')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "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.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}