5-More Pandas Introduction.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# More Introduction to Pandas\n",
    "\n",
    "# Table of Contents\n",
    "<a id=\"Table_of_Contents\"> </a>\n",
    "\n",
    "1. [Pandas](#pandas)<br>\n",
    "   1.1 [Grouping and Aggregating Data](#Grouping_and_Aggregation_with_Pandas)<br>\n",
    "    1.2 [Pivot Tables](#Pivot_Tables)<br>\n",
    "    1.3 [Combining Datasets](#Combining_Datasets)<br>\n",
    "    \n",
    "<b>Import the pandas library as pd<b>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Grouping and Aggregating Data\n",
    "<a id=\"Grouping_and_Aggregation_with_Pandas\"> </a>\n",
    "\n",
    "In this section, we will look at how to use the `groupby` Pandas method to aggregate data that resides in a Pandas DataFrame object. Essentially, the `groupby` method allows us to define a subset of the columns in a DataFrame to aggregate on. We can then use defined and user-specifed functions to compute aggregate statistics for the aggregated data. The following table, modified from that found at https://jakevdp.github.io/PythonDataScienceHandbook/03.08-aggregation-and-grouping.html, describes some of the defined aggregations.\n",
    "\n",
    "<table>\n",
    "<thead><tr>\n",
    "<th>Aggregation</th>\n",
    "<th>Description</th>\n",
    "</tr>\n",
    "</thead>\n",
    "<tbody>\n",
    "<tr>\n",
    "<td><code>count()</code></td>\n",
    "<td>Total number of items</td>\n",
    "</tr>\n",
    "<tr>\n",
    "<td><code>first()</code>, <code>last()</code></td>\n",
    "<td>First and last item</td>\n",
    "</tr>\n",
    "<tr>\n",
    "<td><code>mean()</code>, <code>median()</code></td>\n",
    "<td>Mean and median</td>\n",
    "</tr>\n",
    "<tr>\n",
    "<td><code>min()</code>, <code>max()</code></td>\n",
    "<td>Minimum and maximum</td>\n",
    "</tr>\n",
    "<tr>\n",
    "<td><code>std()</code>, <code>var()</code></td>\n",
    "<td>Standard deviation and variance</td>\n",
    "</tr>\n",
    "<tr>\n",
    "<td><code>mad()</code></td>\n",
    "<td>Mean absolute deviation</td>\n",
    "</tr>\n",
    "<tr>\n",
    "<td><code>prod()</code></td>\n",
    "<td>Product of all items</td>\n",
    "</tr>\n",
    "<tr>\n",
    "<td><code>sum()</code></td>\n",
    "<td>Sum of all items</td>\n",
    "</tr>\n",
    "<tr>\n",
    "<td><code>unique()</code></td>\n",
    "<td>Unique values</td>\n",
    "</tr>\n",
    "<tr>\n",
    "<td><code>nunique()</code></td>\n",
    "<td>Number of unique values</td>\n",
    "</tr>\n",
    "</tbody>\n",
    "</table>\n",
    "\n",
    "[Back to Table of Contents](#Table_of_Contents)<br>\n",
    "\n",
    "<b>Create the same sales_data DataFrame we created last class based on the 4 variables below.<b>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stores_list = ['W1', 'W1', 'W1', 'W1', 'W1', 'W1', 'W1', 'W1', 'W1', 'W1', \n",
    "               'W1', 'W1', 'W1', 'W1', 'W1', 'W1', 'W1', 'W1', 'W1', 'W1', \n",
    "               'E1', 'E1', 'E1', 'E1', 'E1', 'E1', 'E1', 'E1', 'E1', 'E1', \n",
    "               'E1', 'E1', 'E1', 'E1', 'E1', 'E1', 'E1', 'E1', 'E1', 'E1', \n",
    "               'E2', 'E2', 'E2', 'E2', 'E2', 'E2', 'E2', 'E2', 'E2', 'E2', \n",
    "               'E2', 'E2', 'E2', 'E2', 'E2', 'E2', 'E2', 'E2', 'E2', 'E2']\n",
    "\n",
    "dates_list = ['12/31/2007', '12/31/2008', '12/31/2009', '12/31/2010', '12/31/2011', '12/31/2012', \n",
    "              '12/31/2013', '12/31/2014', '12/31/2015', '12/31/2016', '12/31/2007', '12/31/2008', \n",
    "              '12/31/2009', '12/31/2010', '12/31/2011', '12/31/2012', '12/31/2013', '12/31/2014', \n",
    "              '12/31/2015', '12/31/2016', '12/31/2007', '12/31/2008', '12/31/2009', '12/31/2010', \n",
    "              '12/31/2011', '12/31/2012', '12/31/2013', '12/31/2014', '12/31/2015', '12/31/2016', \n",
    "              '12/31/2007', '12/31/2008', '12/31/2009', '12/31/2010', '12/31/2011', '12/31/2012', \n",
    "              '12/31/2013', '12/31/2014', '12/31/2015', '12/31/2016', '12/31/2007', '12/31/2008', \n",
    "              '12/31/2009', '12/31/2010', '12/31/2011', '12/31/2012', '12/31/2013', '12/31/2014', \n",
    "              '12/31/2015', '12/31/2016', '12/31/2007', '12/31/2008', '12/31/2009', '12/31/2010', \n",
    "              '12/31/2011', '12/31/2012', '12/31/2013', '12/31/2014', '12/31/2015', '12/31/2016']\n",
    "\n",
    "product_list = ['A', 'A', 'A', 'A', 'A', 'A', 'A', 'A', 'A', 'A', \n",
    "                'B', 'B', 'B', 'B', 'B', 'B', 'B', 'B', 'B', 'B', \n",
    "                'A', 'A', 'A', 'A', 'A', 'A', 'A', 'A', 'A', 'A', \n",
    "                'B', 'B', 'B', 'B', 'B', 'B', 'B', 'B', 'B', 'B', \n",
    "                'A', 'A', 'A', 'A', 'A', 'A', 'A', 'A', 'A', 'A', \n",
    "                'B', 'B', 'B', 'B', 'B', 'B', 'B', 'B', 'B', 'B']\n",
    "\n",
    "sales_list = [1957, 1968, 1983, 2001, 1894, 1850, 2045, 1860, 1784, 1856, \n",
    "              1891, 1822, 1846, 1903, 1924, 1897, 1890, 1858, 1871, 1880, \n",
    "              1858, 1909, 1977, 1717, 1751, 1797, 1804, 1845, 1895, 1713, \n",
    "              1904, 1812, 1733, 1868, 1872, 1909, 2034, 1856, 1813, 1806, \n",
    "              1862, 1800, 1840, 1882, 1819, 1854, 1716, 1845, 1877, 1879, \n",
    "              1696, 1783, 1799, 1852, 1793, 1877, 1687, 1824, 1839, 1889]\n",
    "\n",
    "\n",
    "#Insert new code here! \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>Create a way to show the summary statistics of the sales_data variable<b>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>Create a way to show information on the sales_data variable<b>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>Sort the sales_data variable from least to greatest by the Sales<b>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>Create a way to sort the sales from greatest to least in the sales_data variable<b>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b> Create way that will compute the total sales for each of the stores included in the DataFrame<B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If a single column is specified for grouping, any aggregations are returned as a `Series` object. Thus, we can access the values using the indexing and selection methods for `Series` objects. \n",
    "\n",
    "[Back to Table of Contents](#Table_of_Contents)<br>\n",
    "\n",
    "<b>Create a way to get the total sales for store *E1*.<b>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If more than one column is specified for grouping, aggregated data is again returned as a `Series` object. However, this object will utilize a *multi-level* index as shown below.\n",
    "\n",
    "<b>Create a way to show the total sales by store and year<b>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>Create a way to show the average sales by store and product<b>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>Create a way to access the total sales for the E1 store during 2007.  <b>\n",
    "   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Essentially, when multiple columns are grouped on, the resulting aggregations can be accessed in a manner that is similar to a multi-level dictionary. If we would like the object returned to resemble a DataFrame instead, we can set the optional `as_index` argument to `False`. This is demonstrated below. \n",
    "\n",
    "[Back to Table of Contents](#Table_of_Contents)<br>\n",
    "\n",
    "<b>Create a data frame object that will show the total sales for each store each year<B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In addition to grouping on several columns, we can also perform several aggregations using the `agg` method. The following code block shows an example where we group the `sales_data` object by *Store* and *Year*, and then determine the *mean*, *total*, and *maximum* sales for each grouping. \n",
    "\n",
    "[Back to Table of Contents](#Table_of_Contents)<br>\n",
    "\n",
    "<b>Create a way to show each store and year combination that will show the average, total, and maximum sales<b>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As you can see in the previous code block, even though we specified `as_index = False`, the returned object still utilizes a multi-level index. However, by *chaining* the expression with the `reset_index()` method, we can obtain a standard DataFrame object as shown below.\n",
    "\n",
    "[Back to Table of Contents](#Table_of_Contents)<br>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also customize the column names as shown below.\n",
    "\n",
    "[Back to Table of Contents](#Table_of_Contents)<br>\n",
    "\n",
    "<b>Using the code we just wrote above, create new titles for the aggregated columns of \"Mean Sales\", \"Total Sales\", and \"Maximum Sales\".<B>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Pivot Tables\n",
    "<a id=\"Pivot_Tables\"> </a>\n",
    "\n",
    "The previous section showed how the `groupby` functionality available in Pandas allows you to aggregate data that resides in a `DataFrame` object in a very flexible manner. However, many users are very familiar with the aggregation capabilities made available by Microsoft Excel's pivot table functionality. Noting this, the Pandas package comes with a builtin `pivot_table` method that attempts to replicate this functionality in a simple fashion.\n",
    "\n",
    "The following code block shows how to use this function to compute the `mean` sales for each store, by year. \n",
    "\n",
    "[Back to Table of Contents](#Table_of_Contents)<br>\n",
    "\n",
    "<b>Create a way that will show the average sales by year (in the rows) and by store (in the columns) using a pivot_table method<b>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>Create a way that will show the total sales by year (in the rows) and by product (in the column) using a pivot_table method<b>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Combining Datasets\n",
    "<a id=\"Combining_Datasets\"> </a>\n",
    "\n",
    "The final thing that we consider in this notebook is how to merge DataFrame objects. In particular, let's assume that we have another DataFrame object that includes information on each of the stores. The following code block creates such an object.\n",
    "\n",
    "[Back to Table of Contents](#Table_of_Contents)<br>\n",
    "\n",
    "<b>Create a DataFrame from the following variables<b>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stores_list = ['W1', 'E1', 'E2']\n",
    "\n",
    "city_list = ['Birmingham', 'Atlanta', 'Savannah',]\n",
    "\n",
    "state_list = ['Alabama', 'Georgia', 'Georgia']\n",
    "\n",
    "#Insert how to create a DataFrame\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's assume that we want the total sales by state. To calculate this quantity, we first need to group the data in the `sales_data` object by store. This grouping is performed in the following code block, and the resulting object is stored in a new object called `grouped_data`.\n",
    "\n",
    "[Back to Table of Contents](#Table_of_Contents)<br>\n",
    "\n",
    "<b>Create a variable called grouped_data that will sum up all of the sales by store. <b>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can now use Pandas `merge` method to merge the `grouped_data` object with the `store_info` object. Since they both have a column that is named *Store*, the method will merge on this common column. This is shown below.\n",
    "\n",
    "[Back to Table of Contents](#Table_of_Contents)<br>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can now *chain* the `groupby` method to the end of the merged data to obtain the total sales by store as is shown in the following code block.\n",
    "\n",
    "[Back to Table of Contents](#Table_of_Contents)<br>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Note that we could have performed the merging and aggregation in a single, *chained* statement as follows.**\n",
    "\n",
    "[Back to Table of Contents](#Table_of_Contents)<br>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sales_data.groupby('Store', as_index = False)['Sales'].sum()\\\n",
    ".merge(store_info).groupby('State', as_index = False)['Sales'].sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The Pandas package includes additional capabilities for joining DataFrame objects. For more info, see https://jakevdp.github.io/PythonDataScienceHandbook/03.07-merge-and-join.html.\n",
    "\n",
    "[Back to Table of Contents](#Table_of_Contents)<br>"
   ]
  }
 ],
 "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.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}