Pandas.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "AO82jjZ-rFvb"
   },
   "source": [
    "## Using `pandas`\n",
    "\n",
    "`pandas` is an important python package that simplifies the analysis of data, particularly time series and large databases that need to be organized.  Here, we introduce `pandas` to easily read a text file containing weather data from Champaign, Illinois Willard Airport.  It is a file containing the hourly observations from 1972-2018 containing dewpoint temperatures > 75 degrees F.\n",
    "\n",
    "The data file is in a format called \"comma separated values\" or \"csv\".  This is simply where the data in the file is \"delimited\" - or separated - with a comma to indicate a separation between data entries. The file also contains a header, footer (which we may or may not want to read, but does contain \"metadata\" - or data about the data - about where it came from and other information about the data), and also a line immediately above the data labeling each column in the csv file.  Then, there are lines containing the data in csv format.  These, along with the header line containing the labels for the columns, is what we want to read with `pandas`.  We'll skip the rest.\n",
    "\n",
    "To learn more about using `pandas` to read data, see https://www.datacamp.com/community/tutorials/pandas-read-csv"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "eZgDEf_vahFT"
   },
   "source": [
    "## Getting started\n",
    "\n",
    "We'll always import `pandas` using the following convention."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "Z3Vh-RHeeo2R"
   },
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "ehdAt-xFavFX"
   },
   "source": [
    "## Getting a file\n",
    "\n",
    "We'll use package `gdown` to download a file to your session that is on my Google Drive.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "id": "4hp_QgLXlSzS"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Collecting gdown\n",
      "  Downloading gdown-3.12.2.tar.gz (8.2 kB)\n",
      "  Installing build dependencies ... \u001b[?25ldone\n",
      "\u001b[?25h  Getting requirements to build wheel ... \u001b[?25ldone\n",
      "\u001b[?25h    Preparing wheel metadata ... \u001b[?25ldone\n",
      "\u001b[?25hRequirement already satisfied: requests[socks] in /home/snesbitt/anaconda3/lib/python3.7/site-packages (from gdown) (2.24.0)\n",
      "Requirement already satisfied: six in /home/snesbitt/anaconda3/lib/python3.7/site-packages (from gdown) (1.15.0)\n",
      "Requirement already satisfied: tqdm in /home/snesbitt/anaconda3/lib/python3.7/site-packages (from gdown) (4.49.0)\n",
      "Requirement already satisfied: filelock in /home/snesbitt/anaconda3/lib/python3.7/site-packages (from gdown) (3.0.12)\n",
      "Requirement already satisfied: idna<3,>=2.5 in /home/snesbitt/anaconda3/lib/python3.7/site-packages (from requests[socks]->gdown) (2.10)\n",
      "Requirement already satisfied: chardet<4,>=3.0.2 in /home/snesbitt/anaconda3/lib/python3.7/site-packages (from requests[socks]->gdown) (3.0.4)\n",
      "Requirement already satisfied: urllib3!=1.25.0,!=1.25.1,<1.26,>=1.21.1 in /home/snesbitt/anaconda3/lib/python3.7/site-packages (from requests[socks]->gdown) (1.25.10)\n",
      "Requirement already satisfied: certifi>=2017.4.17 in /home/snesbitt/anaconda3/lib/python3.7/site-packages (from requests[socks]->gdown) (2020.6.20)\n",
      "Requirement already satisfied: PySocks!=1.5.7,>=1.5.6; extra == \"socks\" in /home/snesbitt/anaconda3/lib/python3.7/site-packages (from requests[socks]->gdown) (1.7.1)\n",
      "Building wheels for collected packages: gdown\n",
      "  Building wheel for gdown (PEP 517) ... \u001b[?25ldone\n",
      "\u001b[?25h  Created wheel for gdown: filename=gdown-3.12.2-py3-none-any.whl size=9681 sha256=ecf21c2f78952200c224b1df7138ec1211483a15c92acad608c4adcee36ff143\n",
      "  Stored in directory: /home/snesbitt/.cache/pip/wheels/ba/e0/7e/726e872a53f7358b4b96a9975b04e98113b005cd8609a63abc\n",
      "Successfully built gdown\n",
      "Installing collected packages: gdown\n",
      "Successfully installed gdown-3.12.2\n",
      "Downloading...\n",
      "From: https://drive.google.com/uc?id=1zZ-WnsQIbxNE-HWXamW3MbiH0CH3_AXG\n",
      "To: /mnt/c/Users/snesbitt/Documents/GitHub/MSCAR-2020/KCMI_dewpoints_greaterthan_75F.csv\n",
      "100%|██████████████████████████████████████| 53.0k/53.0k [00:00<00:00, 2.63MB/s]\n"
     ]
    }
   ],
   "source": [
    "!gdown https://drive.google.com/uc?id=1zZ-WnsQIbxNE-HWXamW3MbiH0CH3_AXG"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "257cRk7KbUyq"
   },
   "source": [
    "We can see that the file was downloaded to `/content/KCMI_dewpoints_greaterthan_75F.csv`.  Let's make sure it was there by running the `ls` command in the shell."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "id": "nBow3JbfbeP0"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " Introduction.pdf\n",
      " KCMI_dewpoints_greaterthan_75F.csv\n",
      " LICENSE\n",
      "'Machine Learning.ipynb'\n",
      " NumPy.ipynb\n",
      " Pandas.ipynb\n",
      "'Python Variables, Data Types, Indexing.ipynb'\n",
      " README.md\n",
      " binder\n",
      " index.ipynb\n",
      "'netCDF, xarray, and cartopy.ipynb'\n"
     ]
    }
   ],
   "source": [
    "#ls gives us a file listing\n",
    "!ls"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "BuxZT5q6bu4j"
   },
   "source": [
    "Now, let's take a look at the file.  There are several linux commands for quickly perusing a file.  Try some of these, putting the correct filename in the command:\n",
    "\n",
    "!cat *filename*\n",
    "\n",
    "!head *filename*\n",
    "\n",
    "!tail *filename*\n",
    "\n",
    "!head -40 *filename*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "6NHCcAizi38e"
   },
   "outputs": [],
   "source": [
    "!head -40 KCMI_dewpoints_greaterthan_75F.csv"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "qklui-2jcjRg"
   },
   "source": [
    "## Reading a `csv` file in `pandas`\n",
    "\n",
    "OK, now that we have a file, let's read it in.  \n",
    "\n",
    "`pandas` has powerful interfaces for reading many types of files, including `csv` files.  It's called `read_csv`.  It's accessed within the `pandas` package - if you followed the convection loading `pandas` with `pd` with `pd.read_csv`.\n",
    "\n",
    "Now, it's just a matter of telling the `read_csv` command a bit about the file, since it needs to know to not try to read the header and footer as data.  Otherwise it will not know how to read that data.\n",
    "\n",
    "For example, if we try to run the reader without any information about the header and footer, you won't get very far!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "yvCcSViJdnSE"
   },
   "outputs": [],
   "source": [
    "data = pd.read_csv('KCMI_dewpoints_greaterthan_75F.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "Ls0ThFf9d6xU"
   },
   "source": [
    "However, if we use the `head` and `tail` commands from above, we can count how many header and footer lines to skip.  I count 9 and 4, respectively. Note that the first csv-formatted line describing the data is ok - if we include that in the data it will be used as a label for the data.  We enter those into the `read_csv` command using the keyword arguments `header` and `skipfooter`.  Now try reading the data - it will be stored in a pandas DataFrame called 'data'."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "up7Obi7xeqeN"
   },
   "outputs": [],
   "source": [
    "data = pd.read_csv('KCMI_dewpoints_greaterthan_75F.csv',header=9,skipfooter=4)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "CBIk6LHnYA6y"
   },
   "outputs": [],
   "source": [
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "KgUBE047ehMQ"
   },
   "source": [
    "Note that we got a 'Warning' message back from the command interpreter.  Warning messages in python are usually ok.  Here the warning is just telling us that the 'python' read engine was used instead of the 'c' engine.  This is just a performance issue - if you were reading a file that is larger, this might make a difference.\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "leIFnBSfCgLZ"
   },
   "source": [
    "Now do get all the bells and whistles - `pandas` can help you decipher the dates and times also."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "rEzOXqLZB_D0"
   },
   "outputs": [],
   "source": [
    "data = pd.read_csv('KCMI_dewpoints_greaterthan_75F.csv',header=9,skipfooter=4,parse_dates=[['Date', 'Time']])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "C7acS_KDB-HO"
   },
   "source": [
    "Examining the `data` DataFrame can be done just by putting it into a cell.\n",
    "\n",
    "Note what is displayed on the screen - there are column headers, as well as a row index, and the data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "-jHoX3SOhZG9"
   },
   "outputs": [],
   "source": [
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "4bY2rk6YgZoA"
   },
   "source": [
    "If you want to grab a column, you can enclose the column name in a string enclosed in brackets [' ']."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "S9NkGfnievXX"
   },
   "outputs": [],
   "source": [
    "data['Dew Point Temperature (F)']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "llju4KJVg2QK"
   },
   "source": [
    "Want to grab a particular row?  Add an index value.  Try different rows!  Note that python indexing begins at 0!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "wAV7AvzRfnMt"
   },
   "outputs": [],
   "source": [
    "data['Dew Point Temperature (F)'][0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "mOrM8fW4hpG5"
   },
   "source": [
    "Let's say you wanted to select the first 10 values from the table.  You can select ranges using a colon between values, i.e. [a:b].  Give it a try!  Be sure to count to see whether there is 10 there!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "IZZKRhEKiaDY"
   },
   "outputs": [],
   "source": [
    "data['Dew Point Temperature (F)'][0:10]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "S_Dpcvb6ihrW"
   },
   "source": [
    "If you want to access the values in an array without formatting, you can use the `.values` method to access the underlying NumPy array."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "JNXWTmkDhOtg"
   },
   "outputs": [],
   "source": [
    "data['Dew Point Temperature (F)'].values[0:10]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "Psg_RQf6hBWB"
   },
   "source": [
    "Want to calculate some simple statistics of the column?  There are many methods available in `pandas`, but here is the one to calculate the mean of the column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "toWLCq99kehs"
   },
   "outputs": [],
   "source": [
    "data['Dew Point Temperature (F)'].mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "Uqk53BPfhVCv"
   },
   "source": [
    "Try the `describe()` method."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "hgumCge2hNuW"
   },
   "outputs": [],
   "source": [
    "data['Dew Point Temperature (F)'].describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "uCGaz1cfhcOu"
   },
   "source": [
    "You can even easily create a histogram!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "3YZkFWYDjvcZ"
   },
   "outputs": [],
   "source": [
    "data['Dew Point Temperature (F)'].hist()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "g3NqLoJnCuWn"
   },
   "source": [
    "For full date/time index functionality, make Date_Time the index."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "a4D9WqVuCt6H"
   },
   "outputs": [],
   "source": [
    "data.set_index(pd.DatetimeIndex(data['Date_Time']))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "A60ZQRokGxeT"
   },
   "source": [
    "How to subset on a range of dates:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "KVpJkluJDAUc"
   },
   "outputs": [],
   "source": [
    "data[(data['Date_Time']>'2018-07-14 10:00:00') & (data['Date_Time']<'2019-07-14 10:00:00')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "6E1wp4THDFuo"
   },
   "outputs": [],
   "source": [
    "data['Date_Time'].values"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "CIRdEpJgIEQ3"
   },
   "source": [
    "# Aggregation and Grouping"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "-eBST5iEIEQ4"
   },
   "source": [
    "An essential piece of analysis of large data is efficient summarization: computing aggregations like ``sum()``, ``mean()``, ``median()``, ``min()``, and ``max()``, in which a single number gives insight into the nature of a potentially large dataset.\n",
    "In this section, we'll explore aggregations in Pandas, from simple operations akin to what we've seen on NumPy arrays, to more sophisticated operations based on the concept of a ``groupby``."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "EegMw78mIEQ5"
   },
   "source": [
    "For convenience, we'll use a ``display`` magic function:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "PBeaNrA0IEQ7"
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "class display(object):\n",
    "    \"\"\"Display HTML representation of multiple objects\"\"\"\n",
    "    template = \"\"\"<div style=\"float: left; padding: 10px;\">\n",
    "    <p style='font-family:\"Courier New\", Courier, monospace'>{0}</p>{1}\n",
    "    </div>\"\"\"\n",
    "    def __init__(self, *args):\n",
    "        self.args = args\n",
    "        \n",
    "    def _repr_html_(self):\n",
    "        return '\\n'.join(self.template.format(a, eval(a)._repr_html_())\n",
    "                         for a in self.args)\n",
    "    \n",
    "    def __repr__(self):\n",
    "        return '\\n\\n'.join(a + '\\n' + repr(eval(a))\n",
    "                           for a in self.args)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "GEnofitQIERs"
   },
   "source": [
    "## Simple Aggregations in `pandas`\n",
    "\n",
    "The following table summarizes some other built-in Pandas aggregations:\n",
    "\n",
    "| Aggregation              | Description                     |\n",
    "|--------------------------|---------------------------------|\n",
    "| ``count()``              | Total number of items           |\n",
    "| ``first()``, ``last()``  | First and last item             |\n",
    "| ``mean()``, ``median()`` | Mean and median                 |\n",
    "| ``min()``, ``max()``     | Minimum and maximum             |\n",
    "| ``std()``, ``var()``     | Standard deviation and variance |\n",
    "| ``mad()``                | Mean absolute deviation         |\n",
    "| ``prod()``               | Product of all items            |\n",
    "| ``sum()``                | Sum of all items                |\n",
    "\n",
    "These are all methods of ``DataFrame`` and ``Series`` objects."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "HEJ68JqEIERt"
   },
   "source": [
    "To go deeper into the data, however, simple aggregates are often not enough.\n",
    "The next level of data summarization is the ``groupby`` operation, which allows you to quickly and efficiently compute aggregates on subsets of data."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "VpTYawIsIERu"
   },
   "source": [
    "## GroupBy: Split, Apply, Combine\n",
    "\n",
    "Simple aggregations can give you a flavor of your dataset, but often we would prefer to aggregate conditionally on some label or index: this is implemented in the so-called ``groupby`` operation.\n",
    "The name \"group by\" comes from a command in the SQL database language, but it is perhaps more illuminative to think of it in the terms first coined by Hadley Wickham of Rstats fame: *split, apply, combine*."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "iCJrw-VUIERv"
   },
   "source": [
    "### Split, apply, combine\n",
    "\n",
    "A canonical example of this split-apply-combine operation, where the \"apply\" is a summation aggregation, is illustrated in this figure:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "Mf6HkZ3LIERw"
   },
   "source": [
    "![](https://github.com/jakevdp/PythonDataScienceHandbook/blob/master/notebooks/figures/03.08-split-apply-combine.png?raw=1)\n",
    "[figure source in Appendix](06.00-Figure-Code.ipynb#Split-Apply-Combine)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "wAgf61FQIERx"
   },
   "source": [
    "This makes clear what the ``groupby`` accomplishes:\n",
    "\n",
    "- The *split* step involves breaking up and grouping a ``DataFrame`` depending on the value of the specified key.\n",
    "- The *apply* step involves computing some function, usually an aggregate, transformation, or filtering, within the individual groups.\n",
    "- The *combine* step merges the results of these operations into an output array.\n",
    "\n",
    "While this could certainly be done manually using some combination of the masking, aggregation, and merging commands covered earlier, an important realization is that *the intermediate splits do not need to be explicitly instantiated*. Rather, the ``GroupBy`` can (often) do this in a single pass over the data, updating the sum, mean, count, min, or other aggregate for each group along the way.\n",
    "The power of the ``GroupBy`` is that it abstracts away these steps: the user need not think about *how* the computation is done under the hood, but rather thinks about the *operation as a whole*.\n",
    "\n",
    "As a concrete example, let's take a look at using Pandas for the computation shown in this diagram.\n",
    "We'll start by creating the input ``DataFrame``:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "RLf2gIeIIERy"
   },
   "outputs": [],
   "source": [
    "df = pd.DataFrame({'key': ['A', 'B', 'C', 'A', 'B', 'C'],\n",
    "                   'data': range(6)}, columns=['key', 'data'])\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "WuAKAs-EIER1"
   },
   "source": [
    "The most basic split-apply-combine operation can be computed with the ``groupby()`` method of ``DataFrame``s, passing the name of the desired key column:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "-SS01TwaIER2"
   },
   "outputs": [],
   "source": [
    "df.groupby('key')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "9YjtfGqVIER5"
   },
   "source": [
    "Notice that what is returned is not a set of ``DataFrame``s, but a ``DataFrameGroupBy`` object.\n",
    "This object is where the magic is: you can think of it as a special view of the ``DataFrame``, which is poised to dig into the groups but does no actual computation until the aggregation is applied.\n",
    "This \"lazy evaluation\" approach means that common aggregates can be implemented very efficiently in a way that is almost transparent to the user.\n",
    "\n",
    "To produce a result, we can apply an aggregate to this ``DataFrameGroupBy`` object, which will perform the appropriate apply/combine steps to produce the desired result:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "5FzoswNoIER6"
   },
   "outputs": [],
   "source": [
    "df.groupby('key').quantile(.90)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "5TucIbyNIER9"
   },
   "source": [
    "The ``sum()`` method is just one possibility here; you can apply virtually any common Pandas or NumPy aggregation function, as well as virtually any valid ``DataFrame`` operation, as we will see in the following discussion."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "HID7uVFuKD8o"
   },
   "source": [
    "## Example: Visualizing Seattle Bicycle Counts\n",
    "\n",
    "As a more involved example of working with some time series data, let's take a look at bicycle counts on Seattle's [Fremont Bridge](http://www.openstreetmap.org/#map=17/47.64813/-122.34965).\n",
    "This data comes from an automated bicycle counter, installed in late 2012, which has inductive sensors on the east and west sidewalks of the bridge.\n",
    "The hourly bicycle counts can be downloaded from http://data.seattle.gov/; here is the [direct link to the dataset](https://data.seattle.gov/Transportation/Fremont-Bridge-Hourly-Bicycle-Counts-by-Month-Octo/65db-xm6k).\n",
    "\n",
    "As of summer 2016, the CSV can be downloaded as follows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "P65R67CBKD8p"
   },
   "outputs": [],
   "source": [
    "!curl -o FremontBridge.csv https://data.seattle.gov/api/views/65db-xm6k/rows.csv?accessType=DOWNLOAD"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "49y6MZowKD8r"
   },
   "source": [
    "Once this dataset is downloaded, we can use Pandas to read the CSV output into a ``DataFrame``.\n",
    "We will specify that we want the Date as an index, and we want these dates to be automatically parsed:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "MccoGwVWKD8s"
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "data = pd.read_csv('FremontBridge.csv', index_col='Date', parse_dates=True)\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "3l4WHO0DKD8t"
   },
   "source": [
    "For convenience, we'll further process this dataset by shortening the column names and adding a \"Total\" column:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "E58i-os2KD8u"
   },
   "outputs": [],
   "source": [
    "data.columns = ['Total', 'West', 'East']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "4u63uTdqKD8w"
   },
   "source": [
    "Now let's take a look at the summary statistics for this data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "W9mAZWUUKD8x"
   },
   "outputs": [],
   "source": [
    "data.dropna().describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "a5NaKwuqKD8z"
   },
   "source": [
    "### Visualizing the data\n",
    "\n",
    "We can gain some insight into the dataset by visualizing it.\n",
    "Let's start by plotting the raw data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "1e1c8RK6KD8z"
   },
   "outputs": [],
   "source": [
    "%pylab inline\n",
    "import seaborn; seaborn.set()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "8TPOHuupKD81"
   },
   "outputs": [],
   "source": [
    "data.plot()\n",
    "plt.ylabel('Hourly Bicycle Count');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "GYp2nhg7KD83"
   },
   "source": [
    "The ~25,000 hourly samples are far too dense for us to make much sense of.\n",
    "We can gain more insight by resampling the data to a coarser grid.\n",
    "Let's resample by week:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "fM-86MWCKD83"
   },
   "outputs": [],
   "source": [
    "weekly = data.resample('W').sum()\n",
    "weekly.plot(style=[':', '--', '-'])\n",
    "plt.ylabel('Weekly bicycle count');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "5moS1bqhKD85"
   },
   "source": [
    "This shows us some interesting seasonal trends: as you might expect, people bicycle more in the summer than in the winter, and even within a particular season the bicycle use varies from week to week (likely dependent on weather; see [In Depth: Linear Regression](05.06-Linear-Regression.ipynb) where we explore this further).\n",
    "\n",
    "Another way that comes in handy for aggregating the data is to use a rolling mean, utilizing the ``pd.rolling_mean()`` function.\n",
    "Here we'll do a 30 day rolling mean of our data, making sure to center the window:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "R56T8VEOKD85"
   },
   "outputs": [],
   "source": [
    "daily = data.resample('D').sum()\n",
    "daily.rolling(30, center=True).sum().plot(style=[':', '--', '-'])\n",
    "plt.ylabel('mean hourly count');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "iiYcOBv7KD87"
   },
   "source": [
    "The jaggedness of the result is due to the hard cutoff of the window.\n",
    "We can get a smoother version of a rolling mean using a window function–for example, a Gaussian window.\n",
    "The following code specifies both the width of the window (we chose 50 days) and the width of the Gaussian within the window (we chose 10 days):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "oXMVwAQwKD87"
   },
   "outputs": [],
   "source": [
    "daily.rolling(50, center=True,\n",
    "              win_type='gaussian').sum(std=10).plot(style=[':', '--', '-']);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "KiFULJXPKD8-"
   },
   "source": [
    "### Digging into the data\n",
    "\n",
    "While these smoothed data views are useful to get an idea of the general trend in the data, they hide much of the interesting structure.\n",
    "For example, we might want to look at the average traffic as a function of the time of day.\n",
    "We can do this using the GroupBy functionality discussed in [Aggregation and Grouping](03.08-Aggregation-and-Grouping.ipynb):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "1It31u8PEtlx"
   },
   "outputs": [],
   "source": [
    "data.index.dayofyear"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "IymdrwIDKD8_"
   },
   "outputs": [],
   "source": [
    "by_time = data.groupby(data.index.time).mean()\n",
    "hourly_ticks = 4 * 60 * 60 * np.arange(6)\n",
    "by_time.plot(xticks=hourly_ticks, style=[':', '--', '-']);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "OlfcB8QlKD9A"
   },
   "source": [
    "The hourly traffic is a strongly bimodal distribution, with peaks around 8:00 in the morning and 5:00 in the evening.\n",
    "This is likely evidence of a strong component of commuter traffic crossing the bridge.\n",
    "This is further evidenced by the differences between the western sidewalk (generally used going toward downtown Seattle), which peaks more strongly in the morning, and the eastern sidewalk (generally used going away from downtown Seattle), which peaks more strongly in the evening.\n",
    "\n",
    "We also might be curious about how things change based on the day of the week. Again, we can do this with a simple groupby:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "E400ucTPKD9B"
   },
   "outputs": [],
   "source": [
    "by_weekday = data.groupby(data.index.dayofweek).mean()\n",
    "by_weekday.index = ['Mon', 'Tues', 'Wed', 'Thurs', 'Fri', 'Sat', 'Sun']\n",
    "by_weekday.plot(style=[':', '--', '-']);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "Q1v_J0mvKD9C"
   },
   "source": [
    "This shows a strong distinction between weekday and weekend totals, with around twice as many average riders crossing the bridge on Monday through Friday than on Saturday and Sunday.\n",
    "\n",
    "With this in mind, let's do a compound GroupBy and look at the hourly trend on weekdays versus weekends.\n",
    "We'll start by grouping by both a flag marking the weekend, and the time of day:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "slAfGBg9KD9D"
   },
   "outputs": [],
   "source": [
    "weekend = np.where(data.index.weekday < 5, 'Weekday', 'Weekend')\n",
    "by_time = data.groupby([weekend, data.index.time]).mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "uilY69GrKD9F"
   },
   "source": [
    "Now we'll use some of the Matplotlib tools described in [Multiple Subplots](04.08-Multiple-Subplots.ipynb) to plot two panels side by side:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "LkfyId3yKD9G"
   },
   "outputs": [],
   "source": [
    "import matplotlib.pyplot as plt\n",
    "fig, ax = plt.subplots(1, 2, figsize=(14, 5))\n",
    "by_time.loc['Weekday'].plot(ax=ax[0], title='Weekdays',\n",
    "                           xticks=hourly_ticks, style=[':', '--', '-'])\n",
    "by_time.loc['Weekend'].plot(ax=ax[1], title='Weekends',\n",
    "                           xticks=hourly_ticks, style=[':', '--', '-']);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "EsP-NhDwKD9I"
   },
   "source": [
    "The result is very interesting: we see a bimodal commute pattern during the work week, and a unimodal recreational pattern during the weekends.\n",
    "It would be interesting to dig through this data in more detail, and examine the effect of weather, temperature, time of year, and other factors on people's commuting patterns."
   ]
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "colab": {
   "collapsed_sections": [],
   "name": "Pandas.ipynb",
   "provenance": [
    {
     "file_id": "1-0FoYcUiWssGhzjC9ZHgr-Gt7xfEbMBQ",
     "timestamp": 1601071199919
    },
    {
     "file_id": "https://github.com/jakevdp/PythonDataScienceHandbook/blob/master/notebooks/03.08-Aggregation-and-Grouping.ipynb",
     "timestamp": 1581439432778
    }
   ]
  },
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}