Part 2 - Grouping, Plotting, & Merging.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Grouping\n",
    "\n",
    "The pandas `groupby` function allows us to group our data on the values in a column or column to look at summary measures for records sharing the same values.\n",
    "\n",
    "For example, let's load the speed camera dataset again and ask which camera locations or days of the week have produced the most violations."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "df = pd.read_csv(\"data/Speed_Camera_Violations.csv\")\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that the data is loaded, let's find the 10 locations with the most total violations recorded.\n",
    "\n",
    "To do this, we need to group by the ADDRESS column, then examine the VIOLATIONS column of the resulting grouped dataframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# first let's group by address and look at descriptive statistics for the first 10 records\n",
    "df.groupby([\"ADDRESS\"])[\"VIOLATIONS\"].describe().head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The above records aren't sorted in any meaningful way, but the first thing to note is that the Index is no longer just an integer, it is now the Address. This is because the `groupby` method returns a special object with a new index made up of the \n",
    "values of the column being grouped on.\n",
    "\n",
    "We can still use the `loc` indexer with this new grouped object to, for example, find the count for a given address:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# `count` returns the number of rows for this address, not the total violation count.\n",
    "# IE this tells us the number of observation (in case of our example data, Speed_Camera_Violations,\n",
    "# this corresponds to the number of different days with at least one violation).\n",
    "df.groupby([\"ADDRESS\"])[\"VIOLATIONS\"].count().loc[\"19 W CHICAGO AVE\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# to get the total violation count, we want the `sum` method:\n",
    "df.groupby([\"ADDRESS\"])[\"VIOLATIONS\"].sum().loc[\"19 W CHICAGO AVE\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Now let's get the top 10 camera locations by total violation count:\n",
    "df.groupby([\"ADDRESS\"])[\"VIOLATIONS\"].sum().sort_values(ascending=False).head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It's possible that some locations just have more observations than others, so a more meaningful measure is probably the mean violation count per observation. To get this we just need to use the `mean` function rather than `sum`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.groupby([\"ADDRESS\"])[\"VIOLATIONS\"].mean().sort_values(ascending=False).head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "How about days of the week? *When* are people most likely to be caught speeding?\n",
    "\n",
    "The simplest way to do this is to create a new weekday column and group on that."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# datetime series have a special `dt` property that exposes the date/time-specific functionality.\n",
    "# In this case, dayofweek is a 0-based index where 0 = Monday, 6 = Sunday.\n",
    "df[\"VIOLATION DATE\"] = pd.to_datetime(df[\"VIOLATION DATE\"], format=\"%m/%d/%Y\")\n",
    "df[\"VIOLATION DATE\"].dt.dayofweek.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"DAY OF WEEK\"] = df[\"VIOLATION DATE\"].dt.dayofweek\n",
    "df.groupby([\"DAY OF WEEK\"])[\"VIOLATIONS\"].mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Plotting\n",
    "\n",
    "It's not easy to understand at a glance the distribution of speeding violations by day of the week above, so let's produce a simple plot to visualize and help understand it.\n",
    "\n",
    "Pandas has some basic plotting functions, but I prefer how it interacts with a different visualization package called Seaborn\n",
    "\n",
    "If you do not have seaborn, you can use pip to install it pip install seaborn"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import seaborn as sns #The cannonical way to import seaborn"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# The beauty of pandas with seaborn is how cleanly they interact with eachother\n",
    "\n",
    "sns.lineplot(x = 'DAY OF WEEK', y = 'VIOLATIONS', data = df)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What if time of year is a factor here? Seaborn has a wonderful feature called hue, which allows for a quick comparison of different types of data in one graph"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#first lets create a month column\n",
    "df['MONTH'] = df['VIOLATION DATE'].dt.month\n",
    "\n",
    "#then lets recreate that same plot but with the months separated out\n",
    "sns.lineplot(x = 'DAY OF WEEK', y = 'VIOLATIONS', hue = 'MONTH', data = df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#thats a little chaotic, lets try flipping the hue with the x axis\n",
    "\n",
    "sns.lineplot(x = 'MONTH', y = 'VIOLATIONS', hue = 'DAY OF WEEK', data = df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can continue to make this graph prettier, but I will save that for the data visualization course, this is enough to see 2 interesting trents.  First is that violations are much more likely to occur on saturday and sunday, regardless of the time of year.  The second is that that violations are much more likely to occur during the summer months."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Combining DataFrames\n",
    "\n",
    "Often you will need to combine data from multiple data sets together. There are three types of combinations in pandas: concatenations and merges (aka joins).\n",
    "\n",
    "**Concatenating** means taking multiple DataFrame objects and appending their rows together to make a new DataFrame. In general you will do this when your datasets contain the same columns and you are combining observations of the same type together into one dataset that contains all the rows from all the datasets.\n",
    "\n",
    "**Merging** is joining DataFrames together SQL-style by using common values. This is useful when you have multiple datasets with common keys and you want to combine them into one dataset that contains columns from all the datasets being merged."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Concatenation example\n",
    "df1 = pd.DataFrame({'Site': [1, 2, 3],\n",
    "                    'Observed Value': [8.1, 5.5, 6.9]})\n",
    "\n",
    "df2 = pd.DataFrame({'Site': [7, 8, 9],\n",
    "                    'Observed Value': [10.5, 11.5, 12.0]})\n",
    "\n",
    "print(\"df1: \")\n",
    "print(df1)\n",
    "print()\n",
    "print(\"df2: \")\n",
    "print(df2)\n",
    "print()\n",
    "print(\"concatenated along rows: \")\n",
    "print(pd.concat([df1, df2]))\n",
    "print()\n",
    "print(\"concatenated along columns: \")\n",
    "print(pd.concat([df1, df2], axis = 1))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Merge example\n",
    "df1 = pd.DataFrame({'Site': [3, 1, 2],\n",
    "                    'Observed Value': [8.1, 5.5, 6.9]})\n",
    "\n",
    "df2 = pd.DataFrame({'Site': [1, 2, 3, 4],\n",
    "                    'Temperature': [27.1, 18.2, 29.8, 30.4]})\n",
    "\n",
    "print(\"df1: \")\n",
    "print(df1)\n",
    "print()\n",
    "print(\"df2: \")\n",
    "print(df2)\n",
    "print()\n",
    "print(\"merged: \")\n",
    "print(pd.merge(df1, df2))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(\"df1: \")\n",
    "print(df1)\n",
    "print()\n",
    "print(\"df2: \")\n",
    "print(df2)\n",
    "print()\n",
    "print(\"merged: \")\n",
    "print(pd.merge(df1, df2, how = 'outer'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Mapping and Applying\n",
    "\n",
    "As we have already seen, there are some basic ways to create a new column based on existing columns.  But what if we have a more complicated function? For that, pandas provides the `map` and the `apply` functions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Mapping is commonly used to apply a dictionary of values to a column. \n",
    "#for instance lets take our lakes dataframe from before\n",
    "\n",
    "\n",
    "lakes_data = [['erie', 64, 19],\n",
    "            ['huron', 229, 59],\n",
    "            ['michigan', 281, 85],\n",
    "            ['ontario', 244, 86],\n",
    "            ['superior', 406, 149]]\n",
    "\n",
    "lakes = pd.DataFrame(lakes_data, columns = ['lake', 'Max Depth (m)', 'Avg Depth (m)'])\n",
    "lakes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#lets say I want to shorten the lake names\n",
    "\n",
    "short_lake_dict = {'erie': 'ER',\n",
    "                  'huron': 'HUR',\n",
    "                  'michigan': 'MIC',\n",
    "                  'ontario': 'ONT',\n",
    "                  'superior': 'SUP'}\n",
    "\n",
    "lakes.lake = lakes.lake.map(short_lake_dict)\n",
    "lakes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "But what if you want to do something more complicated than a dictionary? that is where the `apply` function comes into play."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#first lets create a fake function to apply to our dataframe\n",
    "#lets say on some lakes we care about the average depth, while for others we care about max depth \n",
    "def lake_specific_stats(lake, m, a):\n",
    "    if lake in ['ER', 'MIC']:\n",
    "        return m\n",
    "    else:\n",
    "        return a\n",
    "    \n",
    "#we will want to make a new column that captures the data (average or max depth) that we care about\n",
    "\n",
    "lakes['max/ave'] = lakes.apply(lambda x: lake_specific_stats(x.lake, x['Max Depth (m)'], x['Avg Depth (m)']), axis = 1)\n",
    "lakes\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}