coffee_analysis_exercise.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Introduction to data analytics with pandas"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Quentin Caudron\n",
    "\n",
    "#### PyData Seattle, July 2017"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "source": [
    "You can ignore this next cell, it's only for presentations !"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [],
   "source": [
    "%%HTML\n",
    "<style>\n",
    "\n",
    ".rendered_html {\n",
    "  font-size: 0.7em;\n",
    "}\n",
    "\n",
    ".CodeMirror-scroll {\n",
    "    font-size: 1.2em;\n",
    "}\n",
    "\n",
    ".rendered_html table, .rendered_html th, .rendered_html tr, .rendered_html td, .rendered_html h2, .rendered_html h4 {\n",
    "     font-size: 100%;\n",
    "}\n",
    "\n",
    "</style>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Systems check\n",
    "\n",
    "Do you have a working Python installation, with the `pandas` package ?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "source": [
    "**Note :** This cell should run without raising a traceback. Assuming it runs, you can also try printing the value of `pd.__version__` to see what version of `pandas` you have installed."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## A little about me\n",
    "\n",
    "- Lapsed computational physicist\n",
    "- PhD computational neuroscience, postdoc statistical epidemiology\n",
    "- Data Scientist at CBRE - `www.cbredev.com`\n",
    "- ATOM in Seattle"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## A little about the hero of this story\n",
    "\n",
    "<center><img src=\"images/coffee_machine.jpg\" width=\"400px\" /></center>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "source": [
    "We'll be analysing a real-world dataset together. It's about my favourite thing in the world : **coffee**. This dataset was collected at the Mathematics Institute at the University of Warwick. It's a time-series dataset, describing the **total number of coffees made by our espresso machine** by a certain date."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## A little about this workshop\n",
    "\n",
    "We'll be running through an analysis of this dataset as a way to expose you to the `pandas` API. The aim is to develop a little familiarity with how to work with `pandas`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "Slides are available at https://github.com/QCaudron/pydata_pandas. One notebook contains solutions; **beware of spoilers**.\n",
    "\n",
    "The notebooks contain **notes** about what we're doing that I'll skip during this workshop, but try to explain on the way."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "The `pandas` API is **enormous**. The [documentation](http://pandas.pydata.org/pandas-docs/stable/) is excellent, don't hesitate to look things up."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Key questions\n",
    "\n",
    "1. Who are the main contributors to this dataset, and when are contributions generally made ?\n",
    "2. What are the department's weekday coffee habits ?\n",
    "3. How much coffee are people drinking ?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Let's begin"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "source": [
    "**Note :** The second line here tells `matplotlib` to plot directly under the cell where any plotting code is called. `pandas` uses `matplotlib` to generate graphs, and without this, the graphs would appear outside the Jupyter notebook when you called `plt.show()` - but we just want them to appear without having to do this.\n",
    "\n",
    "http://ipython.readthedocs.io/en/stable/interactive/plotting.html#id1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Importing the data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's import the coffee data from CSV."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Read data from data/coffees.csv\n",
    "data = ..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "source": [
    "**Note :** `pandas` can read from many data formats : CSV, JSON, Excel, HDF5, SQL, and more.\n",
    "\n",
    "http://pandas.pydata.org/pandas-docs/version/0.20/io.html"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "#### What does this data look like ?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "#### Let's just look at the first few rows."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# .head()\n",
    "..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "We have an index, and three columns : `timestamp`, `coffees`, and `contributor`.\n",
    "\n",
    "Uh-oh. Why is there a string of text, `testing`, in our coffee numbers ? What's going on in the `coffees` column in the row after that ?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "source": [
    "**Note :** `df.head(n=10)` would show the first ten rows. The default is `n=5`.\n",
    "\n",
    "https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.head.html"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "#### Let's look at that string in the third row."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# .loc or .iloc\n",
    "..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "Definitely a string. We'll note this as something to fix after we finish looking around."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "source": [
    "**Note :** `.loc` uses a label-based lookup, which means that the value you pass into the square brackets must be in the index. Another method, `.iloc`, is integer-location-based, so `.iloc[2]` would return the third row. In this case, they're the same, but had we changed our index, as we'll see later, things would work differently.\n",
    "\n",
    "Indexing a dataframe with `[]` directly returns a `pd.Series` or `pd.DataFrame` by searching over *columns*, not rows. Indexing a `pd.Series` with `[]` is like indexing a dataframe with `.iloc`.\n",
    "\n",
    "https://pandas.pydata.org/pandas-docs/stable/indexing.html"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "#### We should also take a look at that NaN. In fact, let's look at the first five values in `coffees`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# [] indexing on a series\n",
    "..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true,
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "source": [
    "**Note :** here, we're indexing a *series* ( a `pd.Series` object ). From a `pd.DataFrame` ( here, `data` ), when you access a single column ( `data.coffees` or `data[\"coffees\"]` ), the object returned is a `pd.Series`. From that, indexing directly with `[]` works in an integer-location-based manner, and like with numpy arrays, you can take slices ( `[:5]` ).\n",
    "\n",
    "https://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.html"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "#### How long is the dataset ?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [],
   "source": [
    "print(\"Dataset length :\")\n",
    "\n",
    "# len()\n",
    "print(...)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "#### What else can we find out ?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# .describe()\n",
    "..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "Looks like we also have some missing data - we have 671 rows, but the `coffees` column only has 658 entries."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "source": [
    "**Note :** `.describe()` returns different things based on what's in the dataframe, as we'll see later. For numerical columns, it will return things like the mean, standard deviation, and percentiles. For object columns ( strings or datetimes ), it will return the most frequent entry and the first and last items. For all columns, `.describe()` will return the count of objects in that column ( not counting NaNs ) and the unique number of entries. You can determine what's returned using `.describe()`'s keyword arguments.\n",
    "\n",
    "https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.describe.html"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "#### Let's look at the dataframe where `coffees` is null."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# .isnull() and boolean indexing with []\n",
    "..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "source": [
    "**Note :** `.isnull()` returns a boolean array ( an array of `True`s and `False`s ), that you can then use to index the dataframe directly. Here, our boolean array tells us which entries in the `coffees` column are null, and we use that to index against the full dataframe - so we get back every column in the dataframe, but only those rows where `coffees` is null.\n",
    "\n",
    "https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.isnull.html"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "#### What type of Python objects are the columns ?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# .dtypes\n",
    "..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "The `contributor` column makes sense as `object`, because we expect strings there; but surely the `timestamp` should be a timestamp-type, and `coffees` should be numerical ?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "#### Let's inspect what's in the `timestamp` column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# print the first element of the series with [] indexing\n",
    "print(...)\n",
    "\n",
    "# print its type()\n",
    "print(...)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "It looks like the `timestamp` field was read from CSV as a string. That makes sense - CSV files are very basic. We'll have `pandas` interpret these strings as datetimes for us automatically."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "source": [
    "**Note :** here's an example of using direct `[]` indexing on a `pd.Series`. We're accessing the first entry, just to see what type of object we have there."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "#### On our first pass, what problems did we find ?\n",
    "\n",
    "- The `timestamp` column contains strings; these need to be datetimes\n",
    "- The `coffees` column contains some null values and at least one string"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Cleaning the data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "#### The `coffees` column should only contain numerical data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# cast the coffees column using pd.to_numeric, and coerce errors\n",
    "data.coffees = ...\n",
    "\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    " #### The `coffees` column contains NaNs."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Use .dropna() using a subset, and pass inplace\n",
    "...\n",
    "\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "#### The `coffees` column is of type `float`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Cast to int using .astype()\n",
    "data.coffees = ...\n",
    "\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "#### Let's have pandas parse the `timestamp` strings to datetime objects. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# pd.to_datetime()\n",
    "data.timestamp = ...\n",
    "\n",
    "# Confirm dtypes\n",
    "data.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "#### So where do we stand ?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# .describe(), passing the include kwarg to see all information\n",
    "..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "# What do the first few rows look like ?\n",
    "..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "source": [
    "**Note :** `.describe(include=\"all\")` is describing all attributes of all columns, but some don't make sense based on the column's `dtype`. For example, the contributor column has no `first` and `last` attributes, because those describe the first and last entries in an ordered series. That makes sense for the timestamp - those have an intuitive definition of sorting - but not so much for strings ( alphabetical order doesn't really matter when they're arbitrary strings ). Similary, the timestamp column has no mean or other numerical traits. What does it mean to calculate the mean timestamp ?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## The time-series at a glance"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "#### Let's begin by visualising the coffee counts."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# .plot() on the coffees series\n",
    "..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "`pandas` is plotting the coffees against the index, which is just a series of integers."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "source": [
    "**Note :** `.plot()` on a `pd.Series` will plot the data against the index. On a `pd.DataFrame`, the `.plot()` method allows plotting of one column against another.\n",
    "\n",
    "By default, `.plot()` renders a line graph, but you can specify which type of plot you'd like - bar, line, histogram, area, scatter, etc..\n",
    "\n",
    "https://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.plot.html\n",
    "\n",
    "https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.plot.html"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "#### Let's use the dataframe's `plot()` method rather than that of the series."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# .plot() on the dataframe, setting x to \n",
    "# the timestamp, with dot-dash style\n",
    "..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "We have some very uneven spacing in places. We might start by cutting off the last few points of this time-series, which is missing a lot of data."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "#### We'll inspect the last few points of this time-series."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# .tail() with ten rows\n",
    "..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "After mid-March, things start getting spaced rather erratically."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "#### Let's cut off the tail of the time-series, anything after 2013-03-01."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Use conditional indexing against the timestamp\n",
    "data = ...\n",
    "\n",
    "data.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "source": [
    "**Note :** this is another example of boolean indexing. `data.timestamp < \"2013-03-01\"` is a boolean array, and can be passed into the dataframe immediately in `[]`, much like with a `np.ndarray`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "#### One final look."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Once again, plot the data against the timestamp\n",
    "..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## 1. Contributions to the time-series"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "#### Who are our main contributors ?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# .value_counts()\n",
    "..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "source": [
    "**Note :** `.value_counts()` counts the unique values in a series. It's similar to doing a `.groupby()` followed by a `.count()`, as we'll see soon.\n",
    "\n",
    "https://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.value_counts.html"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "#### Let's plot this."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# .plot() a bar chart from the value counts\n",
    "..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "#### On which weekdays were contributions made ?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a series of the weekdays\n",
    "# for each entry using .dt.weekday\n",
    "weekdays = ...\n",
    "\n",
    "# .assign() it to our dataframe\n",
    "data = ...\n",
    "\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "#### Can we replace these integers with actual weekdays ?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "weekday_names = [\"Monday\", \"Tuesday\", \"Wednesday\", \"Thursday\", \"Friday\", \"Saturday\", \"Sunday\"]\n",
    "weekday_dict = {key: weekday_names[key] for key in range(7)}\n",
    "\n",
    "# Use .apply() to apply a custom function to the weekdays column\n",
    "data.weekdays = ...\n",
    "\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "#### Let's group by these weekdays."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# .groupby() the weekdays and then .count() rows in each group\n",
    "weekday_counts = ...\n",
    "\n",
    "# We can reorder this dataframe by our weekday_names list\n",
    "weekday_counts = ...\n",
    "\n",
    "weekday_counts"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "source": [
    "**Note :** this first line could be replaced by `weekday_counts = data.coffees.value_counts()`, with the only difference being that that would return a series to us, and here, we got back a dataframe."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "#### We can now visualise these weekday counts."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# .plot() a bar chart of data in weekday_counts\n",
    "..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## 2. Weekday trends"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "#### First, we'll set our timestamps to the dataframe's index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Set the dataframe's .index property\n",
    "data.index = ...\n",
    "\n",
    "# Let's drop the timestamp column, as we no longer need it\n",
    "data.drop([\"timestamp\"], axis=1, inplace=True)\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "#### Let's add some rows at midnight on every day."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# pd.date_range, with daily frequency, and normalisation\n",
    "midnights = ...\n",
    "\n",
    "midnights"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "source": [
    "**Note :** `pd.date_range` creates a fixed-frequency DatetimeIndex. `normalize=True` ensures these datetimes are at midnight, and not at whatever time the starting point is.\n",
    "\n",
    "https://pandas.pydata.org/pandas-docs/stable/generated/pandas.date_range.html"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "#### Let's take the union of this index and our dataset's index."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Take the union of the existing and new indices\n",
    "new_index = ...\n",
    "\n",
    "new_index"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "source": [
    "**Note :** the union of these indices is just a new index where entries from both indices are present. It's sorted by time."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "#### Now we can reindex our dataframe with this new index."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# .reindex() the dataframe\n",
    "upsampled_data = ...\n",
    "\n",
    "upsampled_data.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "source": [
    "**Note :** `.reindex()` keeps any values that conform to the new index, and inserts `NaN`s where we have no values.\n",
    "\n",
    "https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.reindex.html"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "#### We can fill in these `NaN`s using interpolation."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# .interpolate the upsampled_data using the time method\n",
    "upsampled_data = ...\n",
    "\n",
    "upsampled_data.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "#### We're now ready to resample the time-series at a daily frequency."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# .resample() followed by .asfreq()\n",
    "daily_data = ...\n",
    "\n",
    "# Drop the contributor column, we no longer need it\n",
    "daily_data = ...\n",
    "\n",
    "# Generate a column of weekday names\n",
    "daily_data[\"weekdays\"] = daily_data.index.weekday_name  # ?!\n",
    "daily_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true,
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "# Let's plot the data once more, to see how we're doing\n",
    "..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "#### Let's begin by figuring out how many coffees are made on any given day."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [],
   "source": [
    "# Use .diff() on the coffees column; follow up with .shift()\n",
    "coffees_made = ...\n",
    "\n",
    "# Add this as a column to the dataframe\n",
    "daily_data[\"coffees_made_today\"] = ...\n",
    "\n",
    "daily_data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "source": [
    "**Note :** we use `.shift()` here because if we look at the `.diff()` between a Monday and a Tuesday, those coffees are attributed to the Tuesday. However, what we want to say is \"this many coffees were made at some point on the Monday\", so we shift the entire series up one."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "#### Now we can group this by weekday."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# .groupby weekdays, take the mean, and\n",
    "# grab the coffees_made_today column\n",
    "coffees_by_day = ...\n",
    "\n",
    "coffees_by_day"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "#### Let's order this series and then plot it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Sort coffees_by_day by our list of weekend names\n",
    "coffees_by_day = ...\n",
    "\n",
    "# Plot a bar chart\n",
    "..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "Wednesdays was seminar day..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## 3. Coffee per person"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "#### We can now pull in data on how many people were in the department."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Bring in data/department_members.csv;\n",
    "# have the first column be the index, and parse the dates\n",
    "people = ...\n",
    "\n",
    "people.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "#### Let's join the datasets."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Use an outer join, then interpolate over \n",
    "# missing values using nearest values\n",
    "daily_data = ...\n",
    "\n",
    "daily_data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "source": [
    "**Note :** by default, inner joins are performed. That is, if a row from one of the datasets has an index that isn't in the other dataset, that row is dropped. You can specify whether you want outer, left, or right joins, as well plenty of other useful options. The `pandas` API for joining or merging datasets is very developed.\n",
    "\n",
    "https://pandas.pydata.org/pandas-docs/stable/merging.html"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "#### Let's create a column for the number of coffees consumed per person."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# New column is the ratio of coffees made on a\n",
    "# given day to number of members in the department\n",
    "daily_data[\"coffees_per_person\"] = ...\n",
    "\n",
    "# Let's drop those remaining NaNs while we're at it\n",
    "...\n",
    "\n",
    "daily_data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "#### We can now plot this column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Plot the coffees_per_person column\n",
    "..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "#### Those are strange plateaus. We'll pull in another dataset, telling us when the machine was broken."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# pd.read_csv(); try using data/coffee_status.csv\n",
    "# parse_dates as kwarg; also pass index_col\n",
    "machine_status = ...\n",
    "\n",
    "machine_status.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "source": [
    "**Note :** the `parse_dates` keyword argument takes several values. By passing in a list of strings, we're telling `pandas` to attempt to parse the dates in columns with those names.\n",
    "\n",
    "https://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_csv.html"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "#### What values are in the `status` column ?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# .value_counts()\n",
    "..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "#### A quick trick to plot this as a time-series..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Make a pd.Series from the status series where things are OK\n",
    "numerical_status = ...\n",
    "\n",
    "numerical_status.plot()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "source": [
    "**Note :** the first line here creates a boolean `pd.Series`, holding the value `True` when `machine_status.status` is `\"OK\"`, and `False` otherwise. Because it's a `pd.Series`, its index stays the same as that of `machine_status`, which was a `DatetimeIndex`. Then, we can plot the boolean series ( `True` appearing as `1`, and `False` appearing as `0` ), and just quickly scan to see that there are long areas where the coffee machine was operations, with short bouts ( thankfully ! ) of the machine being broken."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "#### Let's join the datasets on the date field !"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [],
   "source": [
    "# .join() daily_data with machine_status\n",
    "daily_data = ...\n",
    "\n",
    "daily_data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "#### We'll bring in this numerical representation of status column into our dataframe too."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Column depicting when the status was \"OK\"\n",
    "# Cast the series to ints before as you create a new column in the dataframe\n",
    "daily_data[\"numerical_status\"] = ...\n",
    "\n",
    "daily_data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "#### Let's plot both the coffees per person and the numerical status."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Plot both columns on the same graph, using default args\n",
    "..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "#### We see a strong weekday-weekend effect. Resampling weekly will fix that."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Resample weekly, taking the mean\n",
    "# of each day to get a weekly value\n",
    "weekly_data = ...\n",
    "\n",
    "# Plot the coffees per person and the machine's status\n",
    "..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## What have we achieved ?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "#### Cleaning\n",
    "\n",
    "- Cast columns to the correct dtypes\n",
    "- Dropped rows with no data\n",
    "- Truncated the time-series when things got sparse"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "#### Exploring the contributions\n",
    "\n",
    "- Discovered who contributed to the dataset and how much\n",
    "- Established how contributions varied by day of the week"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "#### Exploring the time-series\n",
    "\n",
    "- Resampled the uneven time-series to regular daily intervals\n",
    "- Interpolated over missing data using our time index\n",
    "- Discovered on what days of the week coffee was especially popular\n",
    "- Joined the dataset with others to enrich the information available\n",
    "- Derived new columns from existing ones\n",
    "- Smoothed the time-series by weekly downsampling"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "#### Insights\n",
    "\n",
    "- A small number of people contributed most of the data\n",
    "- Contributions were lacking at the weekends, and Mondays had the most contributions\n",
    "- Seminar Wednesdays is a strong driver of coffee consumption\n",
    "- Periods of low coffee drinking correlated strongly with periods where the machine was broken\n",
    "- A significant dip in consumption occurred in the summer months"
   ]
  }
 ],
 "metadata": {
  "celltoolbar": "Slideshow",
  "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.0"
  },
  "livereveal": {
   "height": 720,
   "scroll": 1,
   "start_slideshow_at": "selected",
   "theme": "serif",
   "transition": "slide",
   "width": 1280
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}