2019-02-04_manipulating_dataframes_with_pandas.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Notebook Author: [Trenton McKinney][1]\n",
    "- Course: **[DataCamp: Manipulating DataFrames with pandas][2]**\n",
    " - This [notebook][3] was created as a reproducible reference.\n",
    " - The material is from the course\n",
    " - I completed the exercises\n",
    " - If you find the content beneficial, consider a [DataCamp Subscription][4].\n",
    "\n",
    "\n",
    "  [1]: https://trenton3983.github.io/\n",
    "  [2]: https://learn.datacamp.com/courses/manipulating-dataframes-with-pandas\n",
    "  [3]: https://github.com/trenton3983/DataCamp/blob/master/2019-02-04_manipulating_dataframes_with_pandas.ipynb\n",
    "  [4]: https://www.datacamp.com/join-me/MjQwNzMyMg=="
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "import numpy as np\n",
    "from numpy import NaN\n",
    "from scipy.stats import zscore \n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.set_option('display.max_columns', 50)\n",
    "pd.set_option('display.max_rows', 100)\n",
    "pd.set_option('display.expand_frame_repr', True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Data Files Location\n",
    "\n",
    "* Most data files for the exercises can be found on the [course site](#https://www.datacamp.com/courses/manipulating-dataframes-with-pandas)\n",
    "    * [Olympic medals](#https://assets.datacamp.com/production/repositories/502/datasets/bf22326ecc9171f68796ad805a7c1135288120b6/all_medalists.csv)\n",
    "    * [Gapminder](#https://assets.datacamp.com/production/repositories/502/datasets/09378cc53faec573bcb802dce03b01318108a880/gapminder_tidy.csv)\n",
    "    * [2012 US election results (Pennsylvania)](#https://assets.datacamp.com/production/repositories/502/datasets/502f4eedaf44ad1c94b3595c7691746f282e0b0a/pennsylvania2012_turnout.csv)\n",
    "    * [Pittsburgh weather data](#https://assets.datacamp.com/production/repositories/502/datasets/6c4984cb81ea50971c1660434cc4535a6669a848/pittsburgh2013.csv)\n",
    "    * [Sales](#https://assets.datacamp.com/production/repositories/502/datasets/4c6d3be9e8640e2d013298230c415d3a2a2162d4/sales.zip)\n",
    "    * [Titanic](#https://assets.datacamp.com/production/repositories/502/datasets/e280ed94bf4539afb57d8b1cbcc14bcf660d3c63/titanic.csv)\n",
    "    * [Users](#https://assets.datacamp.com/production/repositories/502/datasets/eaf29468b9fbaad454a74d3c2b59b36e5ab4558b/users.csv)\n",
    "* Other data files may be found in my [DataCamp repository](#https://github.com/trenton3983/DataCamp/tree/master/data)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Data File Objects"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "election_penn = 'data/manipulating-dataframes-with-pandas/2012_US_election_results_(Pennsylvania).csv'\n",
    "gapminder_data = 'data/manipulating-dataframes-with-pandas/gapminder.csv'\n",
    "medals_data = 'data/manipulating-dataframes-with-pandas/olympic_medals.csv'\n",
    "weather_data = 'data/manipulating-dataframes-with-pandas/Pittsburgh_weather_data.csv'\n",
    "sales_data = 'data/manipulating-dataframes-with-pandas/sales.csv'\n",
    "sales2_data = 'data/manipulating-dataframes-with-pandas/sales2.csv'\n",
    "sales_feb = 'data/manipulating-dataframes-with-pandas/sales-feb-2015.csv'\n",
    "titanic_data = 'data/manipulating-dataframes-with-pandas/titanics.csv'\n",
    "users_data = 'data/manipulating-dataframes-with-pandas/users.csv'\n",
    "massachusetts_labor = 'data/manipulating-dataframes-with-pandas/LURReport.csv'\n",
    "auto_mpg = 'data/manipulating-dataframes-with-pandas/auto-mpg.csv'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Manipulating DataFrames with pandas\n",
    "\n",
    "***Course Description***\n",
    "\n",
    "In this course, you'll learn how to leverage pandas' extremely powerful data manipulation engine to get the most out of your data. It is important to be able to extract, filter, and transform data from DataFrames in order to drill into the data that really matters. The pandas library has many techniques that make this process efficient and intuitive. You will learn how to tidy, rearrange, and restructure your data by pivoting or melting and stacking or unstacking DataFrames. These are all fundamental next steps on the road to becoming a well-rounded Data Scientist, and you will have the chance to apply all the concepts you learn to real-world datasets."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### What You'll Learn\n",
    "\n",
    "* Extracting, filtering, and transforming data from DataFrames\n",
    "* Advanced indexing with multiple levels\n",
    "* Tidying, rearranging and restructuring your data\n",
    "* Pivoting, melting, and stacking DataFrames\n",
    "* Identifying and spli!ing DataFrames by groups"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Extracting and transforming data\n",
    "\n",
    "In this chapter, you will learn all about how to index, slice, filter, and transform DataFrames, using a variety of datasets, ranging from 2012 US election data for the state of Pennsylvania to Pittsburgh weather data."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Indexing DataFrames"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### A simple DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv(sales_data, index_col='month')\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Indexing using square brackets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['salt']['Jan']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Using column attribute and row label"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.eggs['Mar']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "toc-hr-collapsed": false
   },
   "source": [
    "#### Accessors\n",
    "\n",
    "* A more efficient and more programmatically reusable method of accessing data in a DataFrame is by using accessors\n",
    "    * .loc - accesses using lables\n",
    "    * .iloc - accesses using index positions\n",
    "* Both accessors use left bracket, row specifier, comma, column specifier, right bracket as syntax"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Using the .loc accessor"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc['May', 'spam']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Using the .iloc accessor"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.iloc[4, 2]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Selecting only some columns\n",
    "\n",
    "* When using bracket-indexing without the .loc or .iloc accessors, the result returned can be an individual value, Pandas Series, or Pandas DataFrame.\n",
    "* To ensure the return value is a DataFrame, use a nested list within square brackets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_new = df[['salt','eggs']]\n",
    "df_new"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exercises"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Index ordering\n",
    "\n",
    "In this exercise, the DataFrame ***election*** is provided for you. It contains the 2012 US election results for the state of Pennsylvania with county names as row indices. Your job is to select ***'Bedford'*** county and the ***'winner'*** column. Which method is the preferred way?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "election = pd.read_csv(election_penn, index_col='county')\n",
    "election.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "election.loc['Bedford', 'winner']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Positional and labeled indexing\n",
    "\n",
    "Given a pair of label-based indices, sometimes it's necessary to find the corresponding positions. In this exercise, you will use the Pennsylvania election results again. The DataFrame is provided for you as ***election***.\n",
    "\n",
    "Find ***x*** and ***y*** such that ***election.iloc[x, y] == election.loc['Bedford', 'winner']***. That is, what is the row position of ***'Bedford'***, and the column position of ***'winner'***? Remember that the first position in Python is 0, not 1!\n",
    "\n",
    "To answer this question, first explore the DataFrame using ***election.head()*** in the IPython Shell and inspect it with your eyes.\n",
    "\n",
    "***Instructions***\n",
    "\n",
    "* Explore the DataFrame in the IPython Shell using ***election.head()***.\n",
    "* Assign the row position of ***election.loc['Bedford']*** to ***x***.\n",
    "* Assign the column position of ***election['winner']*** to ***y***.\n",
    "* Hit 'Submit Answer' to print the boolean equivalence of the ***.loc*** and ***.iloc*** selections."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Assign the row position of election.loc['Bedford']: x\n",
    "x = 4\n",
    "\n",
    "# Assign the column position of election['winner']: y\n",
    "y = 4\n",
    "\n",
    "# Print the boolean equivalence\n",
    "print(election.iloc[x, y] == election.loc['Bedford', 'winner'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***Depending on the situation, you may wish to use .iloc[] over .loc[], and vice versa. The important thing to realize is you can achieve the exact same results using either approach.***"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Indexing and column rearrangement\n",
    "\n",
    "There are circumstances in which it's useful to modify the order of your DataFrame columns. We do that now by extracting just two columns from the Pennsylvania election results DataFrame.\n",
    "\n",
    "Your job is to read the CSV file and set the index to ***'county'***. You'll then assign a new DataFrame by selecting the list of columns ***['winner', 'total', 'voters']***. The CSV file is provided to you in the variable ***filename***.\n",
    "\n",
    "***Instructions***\n",
    "\n",
    "* Import pandas as pd.\n",
    "* Read in ***filename*** using ***pd.read_csv()*** and set the index to ***'county'*** by specifying the ***index_col*** parameter.\n",
    "* Create a separate DataFrame ***results*** with the columns ***['winner', 'total', 'voters']***.\n",
    "* Print the output using ***results.head()***. This has been done for you, so hit 'Submit Answer' to see the new DataFrame!\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read in filename and set the index: election\n",
    "election = pd.read_csv(election_penn, index_col='county')\n",
    "\n",
    "# Create a separate dataframe with the columns ['winner', 'total', 'voters']: results\n",
    "results = election[['winner', 'total', 'voters']]\n",
    "\n",
    "# Print the output of results.head(['winner', 'total', 'voters'])\n",
    "print(results.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***The original election DataFrame had 6 columns, but as you can see, your results DataFrame now has just the 3 columns: 'winner', 'total', and 'voters'.***"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Slicing DataFrames"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### sales DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv(sales_data, index_col='month')\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Selecting a column (i.e., Series)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['eggs']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "type(df.eggs)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Slicing and indexing a Series"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['eggs'].iloc[1:4] # Part of the eggs column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['eggs'].iloc[4] # The value associated with May"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Using .loc[] (1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc[:, 'eggs':'salt'] # All rows, some columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Using .loc[] (2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc['Jan':'Apr',:] # Some rows, all columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Using .loc[] (3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc['Mar':'May', 'salt':'spam']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Using .iloc[]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.iloc[2:5, 1:] # A block from middle of the DataFrame"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Using lists rather than slices (1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc['Jan':'May', ['eggs', 'spam']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Using lists rather than slices (2)#### "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.iloc[[0,4,5], 0:2]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Series versus 1-column DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# A Series by column name\n",
    "df['eggs']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "type(df['eggs'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# A DataFrame w/ single column\n",
    "df[['eggs']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "type(df[['eggs']])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exercises"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Slicing rows\n",
    "\n",
    "The Pennsylvania US election results data set that you have been using so far is ordered by county name. This means that county names can be sliced alphabetically. In this exercise, you're going to perform slicing on the county names of the ***election*** DataFrame from the previous exercises, which has been pre-loaded for you.\n",
    "\n",
    "***Instructions***\n",
    "\n",
    "* Slice the row labels ***'Perry'*** to ***'Potter'*** and assign the output to ***p_counties***.\n",
    "* Print the ***p_counties*** DataFrame. This has been done for you.\n",
    "* Slice the row labels ***'Potter'*** to ***'Perry'*** in reverse order. To do this for hypothetical row labels ***'a'*** and ***'b'***, you could use a stepsize of ***-1*** like so: ***df.loc['b':'a':-1]***.\n",
    "* Print the ***p_counties_rev*** DataFrame. This has also been done for you, so hit 'Submit Answer' to see the result of your slicing!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Slice the row labels 'Perry' to 'Potter': p_counties\n",
    "p_counties = election.loc['Perry':'Potter']\n",
    "\n",
    "# Print the p_counties DataFrame\n",
    "display(p_counties)\n",
    "\n",
    "# Slice the row labels 'Potter' to 'Perry' in reverse order: p_counties_rev\n",
    "p_counties_rev = election.loc['Potter':'Perry':-1]\n",
    "\n",
    "# Print the p_counties_rev DataFrame\n",
    "p_counties_rev"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Slicing columns\n",
    "\n",
    "Similar to row slicing, columns can be sliced by value. In this exercise, your job is to slice column names from the Pennsylvania election results DataFrame using ***.loc[]***.\n",
    "\n",
    "It has been pre-loaded for you as ***election***, with the index set to ***'county'***.\n",
    "\n",
    "***Instructions***\n",
    "\n",
    "* Slice the columns from the starting column to ***'Obama'*** and assign the result to ***left_columns***\n",
    "* Slice the columns from ***'Obama'*** to ***'winner'*** and assign the result to ***middle_columns***\n",
    "* Slice the columns from ***'Romney'*** to the end and assign the result to ***right_columns***\n",
    "* The code to print the first 5 rows of ***left_columns***, ***middle_columns***, and ***right_columns*** has been written, so hit 'Submit Answer' to see the results!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Slice the columns from the starting column to 'Obama': left_columns\n",
    "left_columns = election.loc[:, :'Obama']\n",
    "\n",
    "# Print the output of left_columns.head()\n",
    "print('Left Columns: \\n', left_columns.head())\n",
    "\n",
    "# Slice the columns from 'Obama' to 'winner': middle_columns\n",
    "middle_columns = election.loc[:, 'Obama':'winner']\n",
    "\n",
    "# Print the output of middle_columns.head()\n",
    "print('\\nMiddle Columns: \\n', middle_columns.head())\n",
    "\n",
    "# Slice the columns from 'Romney' to the end: 'right_columns'\n",
    "right_columns = election.loc[:, 'Romney':]\n",
    "\n",
    "# Print the output of right_columns.head()\n",
    "print('\\nRight Columns: \\n', right_columns.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Subselecting DataFrames with lists\n",
    "\n",
    "You can use lists to select specific row and column labels with the ***.loc[]*** accessor. In this exercise, your job is to select the counties ***['Philadelphia', 'Centre', 'Fulton']*** and the columns ***['winner','Obama','Romney']*** from the ***election*** DataFrame, which has been pre-loaded for you with the index set to ***'county'***.\n",
    "\n",
    "Instructions\n",
    "\n",
    "* Create the list of row labels ***['Philadelphia', 'Centre', 'Fulton']*** and assign it to ***rows***.\n",
    "* Create the list of column labels ***['winner', 'Obama', 'Romney']*** and assign it to ***cols***.\n",
    "* Create a new DataFrame by selecting with ***rows*** and ***cols*** in ***.loc[]*** and assign it to ***three_counties***.\n",
    "* Print the ***three_counties*** DataFrame. This has been done for you, so hit 'Submit Answer` to see your new DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create the list of row labels: rows\n",
    "rows = ['Philadelphia', 'Centre', 'Fulton']\n",
    "\n",
    "# Create the list of column labels: cols\n",
    "cols = ['winner', 'Obama', 'Romney']\n",
    "\n",
    "# Create the new DataFrame: three_counties\n",
    "three_counties = election.loc[rows, cols]\n",
    "\n",
    "# Print the three_counties DataFrame\n",
    "print(three_counties)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***If you know exactly which rows and columns are of interest to you, this is a useful approach for subselecting DataFrames.***"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "toc-hr-collapsed": false
   },
   "source": [
    "### Filtering DataFrames"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv(sales_data, index_col='month')\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Creating a Boolean Series"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.salt > 60"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Filtering with a Boolean Series"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[df.salt > 60]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "enough_salt_sold = df.salt > 60"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[enough_salt_sold]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Combining filters"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[(df.salt >= 50) & (df.eggs < 200)] # Both conditions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[(df.salt >= 50) | (df.eggs < 200)] # Either condition"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### DataFrames with zeros and NaNs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = df.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2['bacon'] = [0, 0, 50, 60, 70, 80]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Select columns with all nonzeros"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2.loc[:, df2.all()]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Select columns with any nonzeros"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2.loc[:, df2.any()]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Select columns with any NaNs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc[:, df.isnull().any()]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Select columns without NaNs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc[:, df.notnull().all()]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Drop rows with any NaNs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.dropna(how='any')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Filtering a column based on another"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.eggs[df.salt > 55]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Modifying a column based on another"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc[df.salt > 55, 'eggs'] += 5\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exercises"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Thresholding data\n",
    "\n",
    "In this exercise, we have provided the Pennsylvania election results and included a column called ***'turnout'*** that contains the percentage of voter turnout per county. Your job is to prepare a boolean array to select all of the rows and columns where voter turnout exceeded 70%.\n",
    "\n",
    "As before, the DataFrame is available to you as ***election*** with the index set to ***'county'***.\n",
    "\n",
    "***Instructions***\n",
    "\n",
    "* Create a boolean array of the condition where the ***'turnout'*** column is greater than ***70*** and assign it to ***high_turnout***.\n",
    "* Filter the ***election*** DataFrame with the ***high_turnout*** array and assign it to ***high_turnout_df***.\n",
    "* Print the filtered DataFrame. This has been done for you, so hit 'Submit Answer' to see it!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "election = pd.read_csv(election_penn, index_col='county')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create the boolean array: high_turnout\n",
    "high_turnout = election.turnout > 70\n",
    "\n",
    "# Filter the election DataFrame with the high_turnout array: high_turnout_df\n",
    "high_turnout_df = election[high_turnout]\n",
    "\n",
    "# Print the high_turnout_results DataFrame\n",
    "high_turnout_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Filtering columns using other columns\n",
    "\n",
    "The election results DataFrame has a column labeled ***'margin'*** which expresses the number of extra votes the winner received over the losing candidate. This number is given as a percentage of the total votes cast. It is reasonable to assume that in counties where this margin was less than 1%, the results would be too-close-to-call.\n",
    "\n",
    "Your job is to use boolean selection to filter the rows where the margin was less than 1. You'll then convert these rows of the ***'winner'*** column to ***np.nan*** to indicate that these results are too close to declare a winner.\n",
    "\n",
    "The DataFrame has been pre-loaded for you as ***election***.\n",
    "\n",
    "***Instructions***\n",
    "\n",
    "* Import ***numpy*** as ***np***.\n",
    "* Create a boolean array for the condition where the ***'margin'*** column is less than 1 and assign it to ***too_close***.\n",
    "* Convert the entries in the ***'winner'*** column where the result was too close to call to ***np.nan***.\n",
    "* Print the output of ***election.info()***. This has been done for you, so hit 'Submit Answer' to see the results."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create the boolean array: too_close\n",
    "too_close = election.margin < 1\n",
    "\n",
    "# Assign np.nan to the 'winner' column where the results were too close to call\n",
    "election.loc[too_close, 'winner'] = NaN\n",
    "\n",
    "# Print the output of election.info()\n",
    "election.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Filtering using NaNs\n",
    "\n",
    "In certain scenarios, it may be necessary to remove rows and columns with missing data from a DataFrame. The ***.dropna()*** method is used to perform this action. You'll now practice using this method on a dataset obtained from [Vanderbilt University](#http://biostat.mc.vanderbilt.edu/wiki/pub/Main/DataSets/titanic.html), which consists of data from passengers on the Titanic.\n",
    "\n",
    "The DataFrame has been pre-loaded for you as ***titanic***. Explore it in the IPython Shell and you will note that there are many NaNs. You will focus specifically on the ***'age'*** and ***'cabin'*** columns in this exercise. Your job is to use ***.dropna()*** to remove rows where any of these two columns contains missing data and rows where all of these two columns contain missing data.\n",
    "\n",
    "You'll also use the ***.shape*** attribute, which returns the number of rows and columns in a tuple from a DataFrame, or the number of rows from a Series, to see the effect of dropping missing values from a DataFrame.\n",
    "\n",
    "Finally, you'll use the ***thresh=*** keyword argument to drop columns from the full dataset that have less than 1000 non-missing values.\n",
    "\n",
    "***Instructions***\n",
    "\n",
    "* Select the ***'age***' and ***'cabin'*** columns of ***titanic*** and create a new DataFrame ***df***.\n",
    "* Print the shape of ***df***. This has been done for you.\n",
    "* Drop rows in ***df*** with ***how='any'*** and print the shape.\n",
    "* Drop rows in ***df*** with ***how='all'*** and print the shape.\n",
    "* Drop columns from the ***titanic*** DataFrame that have less than 1000 non-missing values by specifying the ***thresh*** and ***axis*** keyword arguments. Print the output of ***.info()*** from this."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "titanic = pd.read_csv(titanic_data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Select the 'age' and 'cabin' columns: df\n",
    "df = titanic[['age', 'cabin']]\n",
    "\n",
    "# Print the shape of df\n",
    "print(df.shape)\n",
    "\n",
    "# Drop rows in df with how='any' and print the shape\n",
    "print('\\n', df.dropna(how='any').shape)\n",
    "\n",
    "# Drop rows in df with how='all' and print the shape\n",
    "print('\\n', df.dropna(how='all').shape)\n",
    "\n",
    "# Drop columns in titanic with less than 1000 non-missing values\n",
    "print('\\n', titanic.dropna(thresh=1000, axis='columns').info())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Transforming DataFrames"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv(sales_data, index_col='month')\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### DataFrame vectorized methods"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.floordiv(12) # Convert to dozens unit"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### NumPy vectorized functions\n",
    "\n",
    "* [np.floor_divide](#https://docs.scipy.org/doc/numpy-1.15.1/reference/generated/numpy.floor_divide.html)\n",
    "* [invalid value encountered in floor_divide](#https://stackoverflow.com/questions/14861891/runtimewarning-invalid-value-encountered-in-divide)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "np.floor_divide(df, 12) # Convert to dozens unit"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Plain Python functions (1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def dozens(n):\n",
    "    return n//12"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.apply(dozens)  # Convert to dozens unit"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Plain Python functions (2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.apply(lambda n: n//12)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Storing a transformation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['dozens_of_eggs'] = df.eggs.floordiv(12)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### The DataFrame index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.index"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Working with string values (1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.index = df.index.str.upper()\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Working with string values (2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.index = df.index.map(str.lower)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Defining columns using other columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['salty_eggs'] = df.salt + df.dozens_of_eggs\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exercises"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Using apply() to transform a column\n",
    "\n",
    "The ***.apply()*** method can be used on a pandas DataFrame to apply an arbitrary Python function to every element. In this exercise you'll take daily weather data in Pittsburgh in 2013 obtained from [Weather Underground](#https://www.wunderground.com/history).\n",
    "\n",
    "A function to convert degree Fahrenheit to degree Celsius has been written for you. Your job is to use the ***.apply()*** method to perform this conversion on the 'Mean TemperatureF' and 'Mean Dew PointF' columns of the weather DataFrame.\n",
    "\n",
    "***Instructions***\n",
    "\n",
    "* Apply the ***to_celsius()*** function over the ***['Mean TemperatureF','Mean Dew PointF']*** columns of the ***weather*** DataFrame.\n",
    "* Reassign the columns of ***df_celsius*** to ***['Mean TemperatureC','Mean Dew PointC']***.\n",
    "* Hit 'Submit Answer' to see the new DataFrame with the converted units."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "weather = pd.read_csv(weather_data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Write a function to convert degree Fahrenheit to degree Celsius: to_celsius\n",
    "def to_celsius(F):\n",
    "    return 5/9*(F - 32)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Apply the function over 'Mean TemperatureF' and 'Mean Dew PointF': df_celsius\n",
    "df_celsius = weather[['Mean TemperatureF', 'Mean Dew PointF']].apply(to_celsius)\n",
    "df_celsius.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Reassign the columns df_celsius\n",
    "df_celsius.columns = ['Mean TemperatureC', 'Mean Dew PointC']\n",
    "df_celsius.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Using .map() with a dictionary\n",
    "\n",
    "The ***.map()*** method is used to transform values according to a Python dictionary look-up. In this exercise you'll practice this method while returning to working with the ***election*** DataFrame, which has been pre-loaded for you.\n",
    "\n",
    "Your job is to use a dictionary to map the values ***'Obama'*** and ***'Romney'*** in the ***'winner'*** column to the values ***'blue'*** and ***'red'***, and assign the output to the new column ***'color'***.\n",
    "\n",
    "***Instructions***\n",
    "\n",
    "* Create a dictionary with the key:value pairs ***'Obama':'blue'*** and ***'Romney':'red'***.\n",
    "* Use the ***.map()*** method on the ***'winner'*** column using the ***red_vs_blue*** dictionary you created.\n",
    "* Print the output of ***election.head()***. This has been done for you, so hit 'Submit Answer' to see the new column!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "election =  pd.read_csv(election_penn, index_col='county')\n",
    "election.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create the dictionary: red_vs_blue\n",
    "red_vs_blue = {'Obama':'blue', 'Romney':'red'}\n",
    "\n",
    "# Use the dictionary to map the 'winner' column to the new column: election['color']\n",
    "election['color'] = election.winner.map(red_vs_blue)\n",
    "\n",
    "# Print the output of election.head()\n",
    "election.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Using vectorized functions\n",
    "\n",
    "When performance is paramount, you should avoid using ***.apply()*** and ***.map()*** because those constructs perform Python for-loops over the data stored in a pandas Series or DataFrame. By using vectorized functions instead, you can loop over the data at the same speed as compiled code (C, Fortran, etc.)! NumPy, SciPy and pandas come with a variety of vectorized functions (called Universal Functions or UFuncs in NumPy).\n",
    "\n",
    "You can even write your own vectorized functions, but for now we will focus on the ones distributed by NumPy and pandas.\n",
    "\n",
    "In this exercise you're going to import the ***zscore*** function from ***scipy.stats*** and use it to compute the deviation in voter turnout in Pennsylvania from the mean in fractions of the standard deviation. In statistics, the z-score is the number of standard deviations by which an observation is above the mean - so if it is negative, it means the observation is below the mean.\n",
    "\n",
    "Instead of using ***.apply()*** as you did in the earlier exercises, the ***zscore*** UFunc will take a pandas Series as input and return a NumPy array. You will then assign the values of the NumPy array to a new column in the DataFrame. You will be working with the ***election*** DataFrame - it has been pre-loaded for you.\n",
    "\n",
    "***Instructions***\n",
    "\n",
    "* Import ***zscore*** from ***scipy.stats***.\n",
    "* Call ***zscore*** with ***election['turnout']*** as input .\n",
    "* Print the output of ***type(turnout_zscore)***. This has been done for you.\n",
    "* Assign ***turnout_zscore*** to a new column in ***election*** as ***'turnout_zscore'***.\n",
    "* Print the output of ***election.head()***. This has been done for you, so hit 'Submit Answer' to view the result."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Call zscore with election['turnout'] as input: turnout_zscore\n",
    "turnout_zscore = zscore(election.turnout)\n",
    "\n",
    "# Print the type of turnout_zscore\n",
    "print('Type: \\n', type(turnout_zscore), '\\n')\n",
    "\n",
    "# Assign turnout_zscore to a new column: election['turnout_zscore']\n",
    "election['turnout_zscore'] = turnout_zscore\n",
    "\n",
    "# Print the output of election.head()\n",
    "election.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Advanced Indexing\n",
    "\n",
    "Having learned the fundamentals of working with DataFrames, you will now move on to more advanced indexing techniques. You will learn about MultiIndexes, or hierarchical indexes, and learn how to interact with and extract data from them."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Index objects and labeled data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### pandas Data Structures\n",
    "\n",
    "* Key building blocks\n",
    "    * Indexes: Sequence of lables\n",
    "    * Series: 1D array with index\n",
    "    * DataFrames: 2D array with Series as columns\n",
    "* Indexes\n",
    "    * Immutable (Like dictionary keys)\n",
    "    * Homogenous in data type (Like NumPy arrays)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Creating a Series"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "prices = [10.70, 10.86, 10.74, 10.71, 10.79]\n",
    "shares = pd.Series(prices)\n",
    "shares"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Creating an index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "days = ['Mon', 'Tue', 'Wed', 'Thur', 'Fri']\n",
    "shares = pd.Series(prices, index=days)\n",
    "shares"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Examining an index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(shares.index)\n",
    "print(shares.index[2])\n",
    "print(shares.index[:2])\n",
    "print(shares.index[-2:])\n",
    "print(shares.index.name)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Modifying index name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "shares.index.name = 'weekday'\n",
    "shares"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Modifying index entries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "try:\n",
    "    shares.index[2] = 'Wednesday'\n",
    "except TypeError:\n",
    "    print('TypeError: Index does not support mutable operations')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "try:\n",
    "    shares.index[:4]= ['Monday', 'Tuesday', 'Wednesday', 'Thursday']\n",
    "except TypeError:\n",
    "    print('TypeError: Index does not support mutable operations')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Modifying all index entries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "shares.index = ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday']\n",
    "shares"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Unemployment data - Massachusetts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read the csv file\n",
    "unemployment = pd.read_csv('data/manipulating-dataframes-with-pandas/LURReport.csv')\n",
    "\n",
    "# Combine 'Year' and 'Month' columns and convert to datetime\n",
    "unemployment['Year_Month'] = pd.to_datetime(unemployment['Year'].astype(str) + '-' + unemployment['Month'].astype(str), format='%Y-%B')\n",
    "\n",
    "# Drop the 'Year' and 'Month' columns\n",
    "unemployment.drop(['Year', 'Month'], axis=1, inplace=True)\n",
    "\n",
    "# Show the first 5 rows\n",
    "unemployment.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "unemployment.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Assigning the index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "unemployment.index = unemployment.Area\n",
    "unemployment.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Removing extr column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "del unemployment['Area']\n",
    "unemployment.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Examining index & columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print('Index: \\n', unemployment.index)\n",
    "print('\\nIndex Name:\\n', unemployment.index.name)\n",
    "print('\\nIndex Type:\\n', type(unemployment.index))\n",
    "print('\\nDataFrame Columns\\n', unemployment.columns)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### read_csv() with index_col()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read the csv file\n",
    "unemployment = pd.read_csv('data/manipulating-dataframes-with-pandas/LURReport.csv', index_col='Area')\n",
    "\n",
    "# Combine 'Year' and 'Month' columns and convert to datetime\n",
    "unemployment['Year_Month'] = pd.to_datetime(unemployment['Year'].astype(str) + '-' + unemployment['Month'].astype(str), format='%Y-%B')\n",
    "\n",
    "# Drop the 'Year' and 'Month' columns\n",
    "unemployment.drop(['Year', 'Month'], axis=1, inplace=True)\n",
    "\n",
    "# Show the first 5 rows\n",
    "unemployment.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exercises"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv(sales_data, index_col='month')\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Index values and names\n",
    "\n",
    "Which one of the following index operations does not raise an error?\n",
    "\n",
    "The ***sales*** DataFrame which you have seen in the videos of the previous chapter has been pre-loaded for you and is available for exploration in the IPython Shell."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***Instructions***\n",
    "\n",
    "Possible Answers\n",
    "\n",
    "* sales.index[0] = 'JAN'.\n",
    "* sales.index[0] = sales.index[0].upper().\n",
    "* ***sales.index = range(len(sales)).***"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Changing index of a DataFrame\n",
    "\n",
    "As you saw in the previous exercise, indexes are immutable objects. This means that if you want to change or modify the index in a DataFrame, then you need to change the whole index. You will do this now, using a list comprehension to create the new index.\n",
    "\n",
    "A list comprehension is a succinct way to generate a list in one line. For example, the following list comprehension generates a list that contains the cubes of all numbers from 0 to 9:\n",
    "\n",
    "```python\n",
    "cubes = [i**3 for i in range(10)]\n",
    "```\n",
    "\n",
    "This is equivalent to the following code:\n",
    "\n",
    "```python\n",
    "cubes = []\n",
    "for i in range(10):\n",
    "    cubes.append(i**3)\n",
    "```\n",
    "\n",
    "Before getting started, print the sales DataFrame in the IPython Shell and verify that the index is given by month abbreviations containing lowercase characters.\n",
    "\n",
    "***Instructions***\n",
    "\n",
    "* Create a list ***new_idx*** with the same elements as in ***sales.index***, but with all characters capitalized.\n",
    "* Assign ***new_idx*** to ***sales.index***.\n",
    "* Print the ***sales*** dataframe. This has been done for you, so hit 'Submit Answer' and to see how the index changed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create the list of new indexes: new_idx\n",
    "new_idx = [x.upper() for x in df.index]\n",
    "\n",
    "# Assign new_idx to sales.index\n",
    "df.index = new_idx\n",
    "\n",
    "# Print the sales DataFrame\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Changing index name labels\n",
    "\n",
    "Notice that in the previous exercise, the index was not labeled with a name. In this exercise, you will set its name to ***'MONTHS'***.\n",
    "\n",
    "Similarly, if all the columns are related in some way, you can provide a label for the set of columns.\n",
    "\n",
    "To get started, print the ***sales*** DataFrame in the IPython Shell and verify that the index has no name, only its data (the month names).\n",
    "\n",
    "***Instructions***\n",
    "\n",
    "* Assign the string ***'MONTHS'*** to ***sales.index.name*** to create a name for the index.\n",
    "* Print the ***sales*** dataframe to see the index name you just created.\n",
    "* Now assign the string ***'PRODUCTS'*** to ***sales.columns.name*** to give a name to the set of columns.\n",
    "* Print the ***sales*** dataframe again to see the columns name you just created."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Assign the string 'MONTHS' to sales.index.name\n",
    "df.index.name = 'MONTHS'\n",
    "\n",
    "# Print the sales DataFrame\n",
    "print(df)\n",
    "\n",
    "# Assign the string 'PRODUCTS' to sales.columns.name \n",
    "df.columns.name = 'PRODUCTS'\n",
    "\n",
    "# Print the sales dataframe again\n",
    "print('\\n', df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Building an index, then a DataFrame\n",
    "\n",
    "You can also build the DataFrame and index independently, and then put them together. If you take this route, be careful, as any mistakes in generating the DataFrame or the index can cause the data and the index to be aligned incorrectly.\n",
    "\n",
    "In this exercise, the ***sales*** DataFrame has been provided for you without the month index. Your job is to build this index separately and then assign it to the ***sales*** DataFrame. Before getting started, print the ***sales*** DataFrame in the IPython Shell and note that it's missing the month information.\n",
    "\n",
    "***Instructions***\n",
    "\n",
    "* Generate a list ***months*** with the data ***['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun']***. This has been done for you.\n",
    "* Assign ***months*** to ***sales.index***.\n",
    "* Print the modified ***sales*** dataframe and verify that you now have month information in the index."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv(sales_data, usecols=['eggs', 'salt', 'spam'])\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Generate the list of months: months\n",
    "months = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun']\n",
    "\n",
    "# Assign months to sales.index\n",
    "df.index = months\n",
    "\n",
    "# Print the modified sales DataFrame\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Hierarchical indexing"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Stock Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stocks = pd.DataFrame([['2016-10-03', 31.50, 14070500, 'CSCO'],\n",
    "                       ['2016-10-03', 112.52, 21701800, 'AAPL'],\n",
    "                       ['2016-10-03', 57.42, 19189500, 'MSFT'],\n",
    "                       ['2016-10-04', 113.00, 29736800, 'AAPL'],\n",
    "                       ['2016-10-04', 57.24, 20085900, 'MSFT'],\n",
    "                       ['2016-10-04', 31.35, 18460400, 'CSCO'],\n",
    "                       ['2016-10-05', 57.64, 16726400, 'MSFT'],\n",
    "                       ['2016-10-05', 31.59, 11808600, 'CSCO'],\n",
    "                       ['2016-10-05', 113.05, 21453100, 'AAPL']],\n",
    "                      columns=['Date', 'Close', 'Volume', 'Symbol'])\n",
    "stocks"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Setting index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stocks = stocks.set_index(['Symbol', 'Date'])\n",
    "stocks"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### MultiIndex on DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stocks.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(stocks.index.name)\n",
    "print(stocks.index.names)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Sorting index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stocks = stocks.sort_index()\n",
    "stocks"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Indexing (individual row)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stocks.loc[('CSCO', '2016-10-04')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stocks.loc[('CSCO', '2016-10-04'), 'Volume']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Slicing (outermost index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stocks.loc['AAPL']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Slicing (outermost index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stocks.loc['CSCO':'MSFT']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Fancy indexing (outermost index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stocks.loc[(['AAPL', 'MSFT'], '2016-10-05'), :]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stocks.loc[(['AAPL', 'MSFT'], '2016-10-05'), 'Close']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Fancy indexing (innermost index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stocks.loc[('CSCO', ['2016-10-05', '2016-10-03']), :]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Slicing (both indexes)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stocks.loc[(slice(None), slice('2016-10-03', '2016-10-04')),:]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exercises"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Sales Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sales = pd.read_csv(sales2_data, index_col=['state', 'month'])\n",
    "sales"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Extracting data with a MultiIndex\n",
    "\n",
    "In the video, Dhavide explained the concept of a hierarchical index, or a MultiIndex. You will now practice working with these types of indexes.\n",
    "\n",
    "The ***sales*** DataFrame you have been working with has been extended to now include State information as well. In the IPython Shell, print the new ***sales*** DataFrame to inspect the data. Take note of the MultiIndex!\n",
    "\n",
    "Extracting elements from the outermost level of a ***MultiIndex*** is just like in the case of a single-level ***Index***. You can use the ***.loc[]*** accessor as Dhavide demonstrated in the video.\n",
    "\n",
    "***Instructions***\n",
    "\n",
    "* Print ***sales.loc[['CA', 'TX']]***. Note how New York is excluded.\n",
    "* Print ***sales['CA':'TX']***. Note how New York is included."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Print sales.loc[['CA', 'TX']]\n",
    "print(sales.loc[['CA', 'TX']])\n",
    "\n",
    "# Print sales['CA':'TX']\n",
    "print('\\n', sales['CA':'TX'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***Notice how New York is excluded by the first operation, and included in the second one.***"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Setting & sorting a MultiIndex\n",
    "\n",
    "In the previous exercise, the MultiIndex was created and sorted for you. Now, you're going to do this yourself! With a MultiIndex, you should always ensure the index is sorted. You can skip this only if you know the data is already sorted on the index fields.\n",
    "\n",
    "To get started, print the pre-loaded ***sales*** DataFrame in the IPython Shell to verify that there is no MultiIndex.\n",
    "\n",
    "***Instructions***\n",
    "\n",
    "* Create a MultiIndex by setting the index to be the columns ***['state', 'month']***.\n",
    "* Sort the MultiIndex using the ***.sort_index()*** method.\n",
    "* Print the ***sales*** DataFrame. This has been done for you, so hit 'Submit Answer' to verify that indeed you have an index with the fields ***state*** and ***month***!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sales = pd.read_csv(sales2_data)\n",
    "\n",
    "# Set the index to be the columns ['state', 'month']: sales\n",
    "sales = sales.set_index(['state', 'month'])\n",
    "\n",
    "# Sort the MultiIndex: sales\n",
    "sales = sales.sort_index()\n",
    "\n",
    "# Print the sales DataFrame\n",
    "sales"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Using .loc[] with nonunique indexes\n",
    "\n",
    "As Dhavide mentioned in the video, it is always preferable to have a meaningful index that uniquely identifies each row. Even though pandas does not require unique index values in DataFrames, it works better if the index values are indeed unique. To see an example of this, you will index your ***sales*** data by ***'state'*** in this exercise.\n",
    "\n",
    "As always, begin by printing the ***sales*** DataFrame in the IPython Shell and inspecting it.\n",
    "\n",
    "***Instructions***\n",
    "\n",
    "* Set the index of ***sales*** to be the column ***'state'***.\n",
    "* Print the ***sales*** DataFrame to verify that indeed you have an index with ***state*** values.\n",
    "* Access the data from ***'NY'*** and print it to verify that you obtain two rows."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sales = pd.read_csv(sales2_data)\n",
    "\n",
    "# Set the index to the column 'state': sales\n",
    "sales = sales.set_index('state')\n",
    "\n",
    "# Print the sales DataFrame\n",
    "print(sales)\n",
    "\n",
    "# Access the data from 'NY'\n",
    "print('\\n', sales.loc['NY'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Indexing multiple levels of a MultiIndex\n",
    "\n",
    "Looking up indexed data is fast and efficient. And you have already seen that lookups based on the outermost level of a ***MultiIndex*** work just like lookups on DataFrames that have a single-level ***Index***.\n",
    "\n",
    "Looking up data based on inner levels of a ***MultiIndex*** can be a bit trickier. In this exercise, you will use your ***sales*** DataFrame to do some increasingly complex lookups.\n",
    "\n",
    "The trickiest of all these lookups are when you want to access some inner levels of the index. In this case, you need to use ***slice(None)*** in the slicing parameter for the outermost dimension(s) instead of the usual ***:***, or use ***pd.IndexSlice***. You can refer to the [pandas documentation](#http://pandas.pydata.org/pandas-docs/stable/advanced.html) for more details. For example, in the video, Dhavide used the following code to extract rows from all Symbols for the dates Oct. 3rd through 4th inclusive:\n",
    "\n",
    "```python\n",
    "stocks.loc[(slice(None), slice('2016-10-03', '2016-10-04')), :]\n",
    "```\n",
    "\n",
    "Pay particular attention to the tuple:\n",
    "\n",
    "```python\n",
    "(slice(None), slice('2016-10-03', '2016-10-04')).\n",
    "```\n",
    "\n",
    "***Instructions***\n",
    "\n",
    "* Look up data for the New York column (***'NY'***) in month ***1***.\n",
    "* Look up data for the California and Texas columns (***'CA'***, ***'TX'***) in month ***2***.\n",
    "* Look up data for all states in month ***2***. Use ***(slice(None), 2)*** to extract all rows in month ***2***"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sales = pd.read_csv(sales2_data, index_col=['state', 'month'])\n",
    "sales"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Look up data for NY in month 1: NY_month1\n",
    "NY_month1 = sales.loc[('NY', 1), :]\n",
    "NY_month1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Look up data for CA and TX in month 2: CA_TX_month2\n",
    "CA_TX_month2 = sales.loc[(('CA', 'TX'), 2), :]\n",
    "CA_TX_month2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Look up data for all states in month 2: all_month2\n",
    "all_month2 = sales.loc[(slice(None), 2), :]\n",
    "all_month2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Rearranging and reshaping data\n",
    "\n",
    "Here, you will learn how to reshape your DataFrames using techniques such as pivoting, melting, stacking, and unstacking. These are powerful techniques that allow you to tidy and rearrange your data into the format that allows you to most easily analyze it for insights."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Pivoting DataFrames"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Clinical Trials Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "trials = pd.DataFrame([[1, 'A', 'F', 5],\n",
    "                       [2, 'A', 'M', 3],\n",
    "                       [3, 'B', 'F', 8],\n",
    "                       [4, 'B', 'M', 9]],\n",
    "                      columns=['id', 'treatment', 'gender', 'response'])\n",
    "trials"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Reshaping by pivoting"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "trials.pivot(index='treatment',\n",
    "             columns='gender',\n",
    "             values='response')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Pivoting multiple columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "trials.pivot(index='treatment', columns='gender')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exercises"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Pivoting and the index\n",
    "\n",
    "Prior to using ***.pivot()***, you need to set the index of the DataFrame somehow. Is this statement True or False?\n",
    "\n",
    "Answer the question\n",
    "\n",
    "***False***\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Pivoting a single variable\n",
    "\n",
    "Suppose you started a blog for a band, and you would like to log how many visitors you have had, and how many signed-up for your newsletter. To help design the tours later, you track where the visitors are. A DataFrame called ***users*** consisting of this information has been pre-loaded for you.\n",
    "\n",
    "Inspect ***users*** in the IPython Shell and make a note of which variable you want to use to index the rows (***'weekday'***), which variable you want to use to index the columns (***'city'***), and which variable will populate the values in the cells (***'visitors'***). Try to visualize what the result should be.\n",
    "\n",
    "For example, in the video, Dhavide used ***'treatment'*** to index the rows, ***'gender'*** to index the columns, and ***'response'*** to populate the cells. Prior to pivoting, the DataFrame looked like this:\n",
    "\n",
    "```python\n",
    "   id treatment gender  response\n",
    "0   1         A      F         5\n",
    "1   2         A      M         3\n",
    "2   3         B      F         8\n",
    "3   4         B      M         9\n",
    "```\n",
    "\n",
    "After pivoting:\n",
    "\n",
    "```python\n",
    "gender     F  M\n",
    "treatment      \n",
    "A          5  3\n",
    "B          8  9\n",
    "```\n",
    "\n",
    "In this exercise, your job is to pivot ***users*** so that the focus is on ***'visitors'***, with the columns indexed by ***'city'*** and the rows indexed by ***'weekday'***.\n",
    "\n",
    "***Instructions***\n",
    "\n",
    "* Pivot the ***users*** DataFrame with the rows indexed by ***'weekday'***, the columns indexed by ***'city'***, and the values populated with ***'visitors'***.\n",
    "* Print the pivoted DataFrame. This has been done for you, so hit 'Submit Answer' to view the result."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "users = pd.read_csv(users_data)\n",
    "users"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Pivot the users DataFrame: visitors_pivot\n",
    "visitors_pivot = users.pivot(index='weekday',\n",
    "                             columns='city',\n",
    "                             values='visitors')\n",
    "visitors_pivot"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***Notice how in the pivoted DataFrame, the index is labeled 'weekday', the columns are labeled 'city', and the values are populated by the number of visitors.***"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Pivoting all variables\n",
    "\n",
    "If you do not select any particular variables, all of them will be pivoted. In this case - with the ***users*** DataFrame - both ***'visitors'*** and ***'signups'*** will be pivoted, creating hierarchical column labels.\n",
    "\n",
    "You will explore this for yourself now in this exercise.\n",
    "\n",
    "***Instructions***\n",
    "\n",
    "* Pivot the ***users*** DataFrame with the ***'signups'*** indexed by ***'weekday'*** in the rows and ***'city'*** in the columns.\n",
    "* Print the new DataFrame. This has been done for you.\n",
    "* Pivot the ***users*** DataFrame with both ***'signups'*** and ***'visitors'*** pivoted - that is, all the variables. This will happen automatically if you do not specify an argument for the ***values*** parameter of ***.pivot()***.\n",
    "* Print the pivoted DataFrame. This has been done for you, so hit 'Submit Answer' to see the result."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Pivot users with signups indexed by weekday and city: signups_pivot\n",
    "signups_pivot = users.pivot(index='weekday', columns='city', values='signups')\n",
    "signups_pivot"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Pivot users pivoted by both signups and visitors: pivot\n",
    "pivot = users.pivot(index='weekday', columns='city')\n",
    "pivot"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***Notice how in the second DataFrame, both 'signups' and 'visitors' were pivoted by default since you didn't provide an argument for the values parameter.***"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Stacking & unstacking DataFrames"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Creating a multi-level index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "trials"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "trials = trials.set_index(['treatment', 'gender'])\n",
    "trials"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Unstacking a multi-index (1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "trials.unstack(level='gender')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Unstacking a multi-index (2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "trials"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "trials.unstack(level=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Stacking DataFrames"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "trials_by_gender = trials.unstack(level='gender')\n",
    "trials_by_gender"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "trials_by_gender.stack(level='gender')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stacked = trials_by_gender.stack(level='gender')\n",
    "stacked"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Swapping levels"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "swapped = stacked.swaplevel(0, 1)\n",
    "swapped"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Sorting rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sorted_trials = swapped.sort_index()\n",
    "sorted_trials"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exercises"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Stacking & unstacking I\n",
    "\n",
    "You are now going to practice stacking and unstacking DataFrames. The ***users*** DataFrame you have been working with in this chapter has been pre-loaded for you, this time with a MultiIndex. Explore it in the IPython Shell to see the data layout. Pay attention to the index, and notice that the index levels are ***['city', 'weekday']***. So ***'weekday'*** - the second entry - has position 1. This position is what corresponds to the ***level*** parameter in ***.stack()*** and ***.unstack()*** calls. Alternatively, you can specify 'weekday' as the level instead of its position.\n",
    "\n",
    "Your job in this exercise is to unstack ***users*** by ***'weekday'***. You will then use ***.stack()*** on the unstacked DataFrame to see if you get back the original layout of ***users***.\n",
    "\n",
    "***Instructions***\n",
    "\n",
    "* Define a DataFrame ***byweekday*** with the ***'weekday'*** level of ***users*** unstacked.\n",
    "* Print the ***byweekday*** DataFrame to see the new data layout. This has been done for you.\n",
    "* Stack ***byweekday*** by ***'weekday'*** and print it to check if you get the same layout as the original ***users*** DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "users = pd.read_csv(users_data)\n",
    "users.set_index(['city', 'weekday'], inplace=True)\n",
    "users = users.sort_index()\n",
    "users"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Unstack users by 'weekday': byweekday\n",
    "byweekday = users.unstack(level='weekday')\n",
    "byweekday"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Stack byweekday by 'weekday' and print it\n",
    "byweekday.stack(level='weekday')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Stacking & unstacking II\n",
    "\n",
    "You are now going to continue working with the ***users*** DataFrame. As always, first explore it in the IPython Shell to see the layout and note the index.\n",
    "\n",
    "Your job in this exercise is to unstack and then stack the ***'city'*** level, as you did previously for ***'weekday'***. Note that you won't get the same DataFrame.\n",
    "\n",
    "***Instructions***\n",
    "\n",
    "* Define a DataFrame ***bycity*** with the ***'city'*** level of ***users*** unstacked.\n",
    "* Print the ***bycity*** DataFrame to see the new data layout. This has been done for you.\n",
    "* Stack ***bycity*** by ***'city'*** and print it to check if you get the same layout as the original ***users*** DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "users"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Unstack users by 'city': bycity\n",
    "bycity = users.unstack(level='city')\n",
    "bycity"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Stack bycity by 'city' and print it\n",
    "bycity.stack(level='city')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Restoring the index order\n",
    "\n",
    "Continuing from the previous exercise, you will now use ***.swaplevel(0, 1)*** to flip the index levels. Note they won't be sorted. To sort them, you will have to follow up with a ***.sort_index()***. You will then obtain the original DataFrame. Note that an unsorted index leads to slicing failures.\n",
    "\n",
    "To begin, print both ***users*** and ***bycity*** in the IPython Shell. The goal here is to convert ***bycity*** back to something that looks like ***users***.\n",
    "\n",
    "***Instructions***\n",
    "\n",
    "* Define a DataFrame ***newusers*** with the ***'city'*** level stacked back into the index of ***bycity***.\n",
    "* Swap the levels of the index of ***newusers***.\n",
    "* Print ***newusers*** and verify that the index is not sorted. This has been done for you.\n",
    "* Sort the index of ***newusers***.\n",
    "* Print ***newusers*** and verify that the index is now sorted. This has been done for you.\n",
    "* Assert that ***newusers*** equals ***users***. This has been done for you, so hit 'Submit Answer' to see the result."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "bycity"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Stack 'city' back into the index of bycity: newusers\n",
    "newusers = bycity.stack(level='city')\n",
    "newusers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Swap the levels of the index of newusers: newusers\n",
    "newusers = newusers.swaplevel(0, 1)\n",
    "newusers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Sort the index of newusers: newusers\n",
    "newusers = newusers.sort_index()\n",
    "newusers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Verify that the new DataFrame is equal to the original\n",
    "newusers.equals(users)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Melting DataFrames"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Clinical Trials Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "trials = pd.DataFrame([[1, 'A', 'F', 5],\n",
    "                       [2, 'A', 'M', 3],\n",
    "                       [3, 'B', 'F', 8],\n",
    "                       [4, 'B', 'M', 9]],\n",
    "                      columns=['id', 'treatment', 'gender', 'response'])\n",
    "trials"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Clinical trials after pivoting"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "trials.pivot(index='treatment',\n",
    "             columns='gender',\n",
    "             values='response')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Clinical trials data - new"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_trials = pd.DataFrame([['A', 5, 3],\n",
    "                           ['B', 8, 9],],\n",
    "                          columns=['treatment', 'F', 'M'])\n",
    "new_trials"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Melting DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.melt(new_trials)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Specifying id_vars"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.melt(new_trials, id_vars=['treatment'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Specifying value_vars"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.melt(new_trials, id_vars=['treatment'],\n",
    "        value_vars=['F', 'M'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Specifying value_name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.melt(new_trials, id_vars=['treatment'],\n",
    "        var_name='gender', value_name='response')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exercises"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Adding names for readability\n",
    "\n",
    "You are now going to practice melting DataFrames. A DataFrame called ***visitors_by_city_weekday*** has been pre-loaded for you. Explore it in the IPython Shell and see that it is the ***users*** DataFrame from previous exercises with the rows indexed by ***'weekday'***, columns indexed by ***'city'***, and values populated with ***'visitors'***.\n",
    "\n",
    "Recall from the video that the goal of melting is to restore a pivoted DataFrame to its original form, or to change it from a wide shape to a long shape. You can explicitly specify the columns that should remain in the reshaped DataFrame with ***id_vars***, and list which columns to convert into values with ***value_vars***. As Dhavide demonstrated, if you don't pass a name to the values in ***d.melt()***, you will lose the name of your variable. You can fix this by using the ***value_name*** keyword argument.\n",
    "\n",
    "Your job in this exercise is to melt ***visitors_by_city_weekday*** to move the city names from the column labels to values in a single column called ***'city'***. If you were to use just ***pd.melt(visitors_by_city_weekday)***, you would obtain the following result:\n",
    "\n",
    "```python\n",
    "      city value\n",
    "0  weekday   Mon\n",
    "1  weekday   Sun\n",
    "2   Austin   326\n",
    "3   Austin   139\n",
    "4   Dallas   456\n",
    "5   Dallas   237\n",
    "```\n",
    "\n",
    "Therefore, you have to specify the ***id_vars*** keyword argument to ensure that ***'weekday'*** is retained in the reshaped DataFrame, and the ***value_name*** keyword argument to change the name of ***value*** to ***visitors***.\n",
    "\n",
    "***Instructions***\n",
    "\n",
    "* Reset the index of ***visitors_by_city_weekday*** with ***.reset_index()***.\n",
    "* Print ***visitors_by_city_weekday*** and verify that you have just a range index, 0, 1, 2, 3. This has been done for you.\n",
    "* Melt ***visitors_by_city_weekday*** to move the city names from the column labels to values in a single column called ***visitors***.\n",
    "* Print ***visitors*** to check that the city values are in a single column now and that the dataframe is longer and skinnier."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create the DataFrame for the exercise\n",
    "visitors = pd.DataFrame({'weekday': ['Mon', 'Sun', 'Mon', 'Sun'],\n",
    "                         'city': ['Austin', 'Austin', 'Dallas', 'Dallas'],\n",
    "                         'visitors': [326, 139, 456, 237]})\n",
    "visitors"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Reshape the DataFrame for the exercise\n",
    "visitors_by_city_weekday = visitors.pivot(index='weekday',\n",
    "                                          columns='city',\n",
    "                                          values='visitors')\n",
    "visitors_by_city_weekday"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Reset the index: visitors_by_city_weekday\n",
    "visitors_by_city_weekday = visitors_by_city_weekday.reset_index()\n",
    "visitors_by_city_weekday"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Melt visitors_by_city_weekday: visitors\n",
    "visitors = pd.melt(visitors_by_city_weekday, id_vars=['weekday'], value_name='visitors')\n",
    "visitors"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Notice how the melted DataFrame now has a 'city' column with Austin and Dallas as its values. In the original DataFrame, they were columns themselves. Also note how specifying the value_name parameter has renamed the 'value' column to 'visitors'.**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Going from wide to long\n",
    "\n",
    "You can move multiple columns into a single column (making the data long and skinny) by \"melting\" multiple columns. In this exercise, you will practice doing this.\n",
    "\n",
    "The **users** DataFrame has been pre-loaded for you. As always, explore it in the IPython Shell and note the index.\n",
    "\n",
    "***Instructions***\n",
    "\n",
    "* Define a DataFrame **skinny** where you melt the **'visitors'** and **'signups'** columns of **users** into a single column.\n",
    "* Print **skinny** to verify the results. Note the **value** column that had the cell values in **users**."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "users = pd.read_csv(users_data)\n",
    "users"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Melt users: skinny\n",
    "skinny = users.melt(id_vars=['weekday', 'city'])\n",
    "skinny"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Because var_name or value_name parameters weren't specified, the melted DataFrame has the default variable and value column names.**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Obtaining key-value pairs with melt()\n",
    "\n",
    "Sometimes, all you need is some key-value pairs, and the context does not matter. If said context is in the index, you can easily obtain what you want. For example, in the **users** DataFrame, the **visitors** and **signups** columns lend themselves well to being represented as key-value pairs. So if you created a hierarchical index with **'city'** and **'weekday'** columns as the index, you can easily extract key-value pairs for the **'visitors'** and **'signups'** columns by melting users and specifying **col_level=0**.\n",
    "\n",
    "**Instructions**\n",
    "\n",
    "* Set the index of **users** to **['city', 'weekday']**.\n",
    "* Print the DataFrame **users_idx** to see the new index.\n",
    "* Obtain the key-value pairs corresponding to visitors and signups by melting **users_idx** with the keyword argument **col_level=0**."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "users"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Set the new index: users_idx\n",
    "users_idx = users.set_index(['city', 'weekday'])\n",
    "users_idx"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Obtain the key-value pairs: kv_pairs\n",
    "kv_pairs = users_idx.melt(col_level=0)\n",
    "kv_pairs"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Pivot tables"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### More clinical trials data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "more_trials = pd.DataFrame([[1, 'A', 'F', 5],\n",
    "                            [2, 'A', 'M', 3],\n",
    "                            [3, 'A', 'M', 8],\n",
    "                            [4, 'A', 'F', 9],\n",
    "                            [5, 'B', 'F', 1],\n",
    "                            [6, 'B', 'M', 8],\n",
    "                            [7, 'B', 'F', 4],\n",
    "                            [8, 'B', 'F', 6]],\n",
    "                           columns=['id', 'treatment', 'gender', 'response'])\n",
    "more_trials"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Rearranging by pivoting\n",
    "\n",
    "* .pivot requires unique index/column pairs to identify values in the new table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "try:\n",
    "    more_trials.pivot(index='treatment',\n",
    "                      columns='gender',\n",
    "                      values='response')\n",
    "except ValueError:\n",
    "    print('ValueError: Index contains duplicate entries, cannot reshape')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Pivot table\n",
    "\n",
    "* The pivot_talbe method reshapes the DataFrame by summarizing it with a pair of summarizing variables and their values.\n",
    "* Pivot tables deal with multiple values for the same index/column pair using a reduction\n",
    "* By default, the reduction is an average"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "more_trials.pivot_table(index='treatment',\n",
    "                        columns='gender',\n",
    "                        values='response')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Other aggregations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "more_trials.pivot_table(index='treatment',\n",
    "                        columns='gender',\n",
    "                        values='response',\n",
    "                        aggfunc='count')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exercises"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Setting up a pivot table\n",
    "\n",
    "Recall from the video that a pivot table allows you to see all of your variables as a function of two other variables. In this exercise, you will use the **.pivot_table()** method to see how the **users** DataFrame entries appear when presented as functions of the **'weekday'** and **'city'** columns. That is, with the rows indexed by **'weekday'** and the columns indexed by **'city'**.\n",
    "\n",
    "Before using the pivot table, print the **users** DataFrame in the IPython Shell and observe the layout.\n",
    "\n",
    "**Instructions**\n",
    "\n",
    "* Use a pivot table to index the rows of **users** by **'weekday'** and the columns of **users** by **'city'**. These correspond to the **index** and **columns** parameters of **.pivot_table()**.\n",
    "* Print **by_city_day**. This has been done for you, so hit 'Submit Answer' to see the result."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "users = pd.read_csv(users_data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create the DataFrame with the appropriate pivot table: by_city_day\n",
    "by_city_day = users.pivot_table(index='weekday',\n",
    "                                columns='city')\n",
    "by_city_day"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Notice the labels of the index and the columns are 'weekday' and 'city', respectively - exactly as you specified.**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Using other aggregations in pivot tables\n",
    "\n",
    "You can also use aggregation functions within a pivot table by specifying the **aggfunc** parameter. In this exercise, you will practice using the **'count'** and **len** aggregation functions - which produce the same result - on the **users** DataFrame.\n",
    "\n",
    "**Instructions**\n",
    "\n",
    "* Define a DataFrame **count_by_weekday1** that shows the count of each column with the parameter **aggfunc='count'**. The index here is **'weekday'**.\n",
    "* Print **count_by_weekday1**. This has been done for you.\n",
    "* Replace **aggfunc='count'** with **aggfunc=len** and verify you obtain the same result."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Use a pivot table to display the count of each column: count_by_weekday1\n",
    "count_by_weekday1 = users.pivot_table(index='weekday',\n",
    "                                      aggfunc='count')\n",
    "count_by_weekday1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Replace 'aggfunc='count'' with 'aggfunc=len': count_by_weekday2\n",
    "count_by_weekday2 = users.pivot_table(index='weekday',\n",
    "                                      aggfunc=len)\n",
    "count_by_weekday2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Verify that the same result is obtained\n",
    "count_by_weekday1.equals(count_by_weekday2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Using margins in pivot tables\n",
    "\n",
    "Sometimes it's useful to add totals in the margins of a pivot table. You can do this with the argument **margins=True**. In this exercise, you will practice using margins in a pivot table along with a new aggregation function: **sum**.\n",
    "\n",
    "The **users** DataFrame, which you are now probably very familiar with, has been pre-loaded for you.\n",
    "\n",
    "**Instructions**\n",
    "\n",
    "* Define a DataFrame **signups_and_visitors** that shows the breakdown of signups and visitors by day.\n",
    "    * You will need to use **aggfunc=sum** to do this.\n",
    "* Print **signups_and_visitors**. This has been done for you.\n",
    "* Now pass the additional argument **margins=True** to the **.pivot_table()** method to obtain the totals.\n",
    "* Print **signups_and_visitors_total**. This has been done for you, so hit 'Submit Answer' to see the result."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create the DataFrame with the appropriate pivot table: signups_and_visitors\n",
    "signups_and_visitors = users.pivot_table(index='weekday',\n",
    "                                         aggfunc='sum')\n",
    "signups_and_visitors"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Add in the margins: signups_and_visitors_total \n",
    "signups_and_visitors_total = users.pivot_table(index='weekday',\n",
    "                                               values=['signups', 'visitors'],\n",
    "                                               aggfunc='sum',\n",
    "                                               margins=True)\n",
    "signups_and_visitors_total"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Specifying margins=True resulted in the totals in each column being computed.**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Grouping data\n",
    "\n",
    "In this chapter, you'll learn how to identify and split DataFrames by groups or categories for further aggregation or analysis. You'll also learn how to transform and filter your data, including how to detect outliers and impute missing values. Knowing how to effectively group data in pandas can be a seriously powerful addition to your data science toolbox."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Categorical and groupby"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Sales Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sales = pd.DataFrame(\n",
    "    {\n",
    "        'weekday': ['Sun', 'Sun', 'Mon', 'Mon'],\n",
    "        'city': ['Austin', 'Dallas', 'Austin', 'Dallas'],\n",
    "        'bread': [139, 237, 326, 456],\n",
    "        'butter': [20, 45, 70, 98]\n",
    "    }\n",
    ")\n",
    "sales"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Boolean filter and count"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sales.loc[sales['weekday'] == 'Sun'].count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Groupby and count"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sales.groupby('weekday').count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Split-apply-combine\n",
    "\n",
    "* sales.groupby('weekday').count()\n",
    "    * split by ‘weekday’\n",
    "    * apply count() function on each group\n",
    "    * combine counts per group"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Aggregation/Reduction\n",
    "\n",
    "* Some reducing functions\n",
    "    * mean()\n",
    "    * std()\n",
    "    * sum()\n",
    "    * first(), last()\n",
    "    * min(), max()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Groupby and sum"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sales.groupby('weekday')['bread'].sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Groupby and sum: multiple columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sales"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sales.groupby('weekday')[['bread','butter']].sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Groupby and mean: multi-level index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sales.groupby(['city','weekday']).mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Customers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "customers = pd.Series(['Dave','Alice','Bob','Alice'])\n",
    "customers"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Groupby and sum: by series"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sales.groupby(customers)['bread'].sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Categorical data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sales['weekday'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sales['weekday'] = sales['weekday'].astype('category')\n",
    "sales['weekday']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Categorical data\n",
    "\n",
    "* Advantages\n",
    "    * Use less memory\n",
    "    * Speeds up operations like `groupby()`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exercises"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Advantages of categorical data types\n",
    "\n",
    "What are the main advantages of storing data explicitly as categorical types instead of object types?\n",
    "\n",
    "Answer the question\n",
    "\n",
    "* **Computations are faster.**\n",
    "* **Categorical data require less space in memory.**\n",
    "* **All of the above.**\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Grouping by multiple columns\n",
    "\n",
    "In this exercise, you will return to working with the Titanic dataset from Chapter 1 and use **.groupby()** to analyze the distribution of passengers who boarded the Titanic.\n",
    "\n",
    "The **'pclass'** column identifies which class of ticket was purchased by the passenger and the **'embarked'** column indicates at which of the three ports the passenger boarded the Titanic. **'S'** stands for Southampton, England, **'C'** for Cherbourg, France and **'Q'** for Queenstown, Ireland.\n",
    "\n",
    "Your job is to first group by the **'pclass'** column and count the number of rows in each class using the **'survived'** column. You will then group by the **'embarked'** and **'pclass'** columns and count the number of passengers.\n",
    "\n",
    "The DataFrame has been pre-loaded as **titanic**.\n",
    "\n",
    "**Instructions**\n",
    "\n",
    "* Group by the **'pclass'** column and save the result as **by_class**.\n",
    "* Aggregate the **'survived'** column of **by_class** using **.count()**. Save the result as **count_by_class**.\n",
    "* Print **count_by_class**. This has been done for you.\n",
    "* Group **titanic** by the **'embarked'** and **'pclass'** columns. Save the result as **by_mult**.\n",
    "* Aggregate the **'survived'** column of **by_mult** using **.count()**. Save the result as **count_mult**.\n",
    "* Print **count_mult**. This has been done for you, so hit 'Submit Answer' to view the result."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Titanic Data**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "titanic = pd.read_csv(titanic_data)\n",
    "titanic.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Group titanic by 'pclass'\n",
    "by_class = titanic.groupby('pclass')\n",
    "\n",
    "# Aggregate 'survived' column of by_class by count\n",
    "count_by_class = by_class['survived'].count()\n",
    "count_by_class"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Group titanic by 'embarked' and 'pclass'\n",
    "by_mult = titanic.groupby(['embarked', 'pclass'])\n",
    "\n",
    "# Aggregate 'survived' column of by_mult by count\n",
    "count_mult = by_mult['survived'].count()\n",
    "count_mult"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Grouping data by certain columns and aggregating them by another column, in this case, 'survived', allows for carefull examination of the data for interesting insights.**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Grouping by another series\n",
    "\n",
    "In this exercise, you'll use two data sets from [Gapminder.org](#http://gapminder.org/) to investigate the average life expectancy (in years) at birth in 2010 for the 6 continental regions. To do this you'll read the life expectancy data per country into one pandas DataFrame and the association between country and region into another.\n",
    "\n",
    "By setting the index of both DataFrames to the country name, you'll then use the region information to group the countries in the life expectancy DataFrame and compute the mean value for 2010.\n",
    "\n",
    "The life expectancy CSV file is available to you in the variable **life_fname** and the regions filename is available in the variable **regions_fname**.\n",
    "\n",
    "**Instructions**\n",
    "\n",
    "* Read **life_fname** into a DataFrame called **life** and set the index to **'Country'**.\n",
    "* Read **regions_fname** into a DataFrame called **regions** and set the index to **'Country'**.\n",
    "* Group **life** by the **region** column of **regions** and store the result in **life_by_region**.\n",
    "* Print the mean over the **2010** column of **life_by_region**."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read life_fname into a DataFrame: life\n",
    "life = pd.read_csv(gapminder_data, usecols=['Year', 'life', 'Country'])\n",
    "life = life.pivot(index='Country',\n",
    "                  columns='Year',\n",
    "                  values='life')\n",
    "life.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "regions = pd.read_csv(gapminder_data, usecols=['region', 'Country'])\n",
    "regions.drop_duplicates(subset='Country', inplace=True)\n",
    "regions.reset_index(inplace=True, drop=True)\n",
    "regions.set_index('Country', inplace=True)\n",
    "regions.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Group life by regions['region']: life_by_region\n",
    "life_by_region = life.groupby(regions['region'])\n",
    "\n",
    "# Print the mean over the '2010' column of life_by_region\n",
    "life_by_region[2010].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "life_by_region.mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "life_by_region[[1964, 1970, 1980, 1990, 2000, 2010, 2013]].mean().plot(rot=45)\n",
    "plt.legend(bbox_to_anchor=(1.05, 1), loc=2, borderaxespad=0.)\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Groupby and aggregation"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Sales data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sales"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Review: groupby"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sales.groupby('city')[['bread','butter']].max()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Multiple aggregations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sales.groupby('city')[['bread','butter']].agg(['max','sum'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Aggregation functions\n",
    "\n",
    "* string names\n",
    "    * 'sum'\n",
    "    * 'mean'\n",
    "    * 'count'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Custom aggregation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def data_range(series):\n",
    "    return series.max() - series.min()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sales.groupby('weekday', observed=False)[['bread', 'butter']].agg(data_range)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Custom aggregation: dictionaries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sales.groupby(customers)[['bread', 'butter']].agg({'bread':'sum', 'butter':data_range})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exercises"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Computing multiple aggregates of multiple columns\n",
    "\n",
    "The **.agg()** method can be used with a tuple or list of aggregations as input. When applying multiple aggregations on multiple columns, the aggregated DataFrame has a multi-level column index.\n",
    "\n",
    "In this exercise, you're going to group passengers on the Titanic by **'pclass'** and aggregate the **'age'** and **'fare'** columns by the functions **'max'** and **'median'**. You'll then use multi-level selection to find the oldest passenger per class and the median fare price per class.\n",
    "\n",
    "The DataFrame has been pre-loaded as **titanic**.\n",
    "\n",
    "**Instructions**\n",
    "\n",
    "* Group **titanic** by **'pclass'** and save the result as **by_class**.\n",
    "* Select the **'age'** and **'fare'** columns from **by_class** and save the result as **by_class_sub**.\n",
    "* Aggregate **by_class_sub** using **'max'** and **'median'**. You'll have to pass **'max'** and **'median'** in the form of a list to **.agg()**.\n",
    "* Use **.loc[]** to print all of the rows and the column specification **('age','max')**. This has been done for you.\n",
    "* Use **.loc[]** to print all of the rows and the column specification **('fare','median')**."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Group titanic by 'pclass': by_class\n",
    "by_class = titanic.groupby('pclass')\n",
    "\n",
    "# Select 'age' and 'fare'\n",
    "by_class_sub = by_class[['age','fare']]\n",
    "\n",
    "# Aggregate by_class_sub by 'max' and 'median': aggregated\n",
    "aggregated = by_class_sub.agg(['max', 'median'])\n",
    "print(aggregated)\n",
    "\n",
    "# Print the maximum age in each class\n",
    "print('\\nMaximum Age:\\n', aggregated.loc[:, ('age','max')])\n",
    "\n",
    "# Print the median fare in each class\n",
    "print('\\nMedian Fare:\\n', aggregated.loc[:, ('fare','median')])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Aggregating on index levels/fields\n",
    "\n",
    "If you have a DataFrame with a multi-level row index, the individual levels can be used to perform the groupby. This allows advanced aggregation techniques to be applied along one or more levels in the index and across one or more columns.\n",
    "\n",
    "In this exercise you'll use the full Gapminder dataset which contains yearly values of life expectancy, population, child mortality (per 1,000) and per capita gross domestic product (GDP) for every country in the world from 1964 to 2013.\n",
    "\n",
    "Your job is to create a multi-level DataFrame of the columns **'Year'**, **'Region'** and **'Country'**. Next you'll group the DataFrame by the **'Year'** and **'Region'** levels. Finally, you'll apply a dictionary aggregation to compute the total population, spread of per capita GDP values and average child mortality rate.\n",
    "\n",
    "The Gapminder CSV file is available as **'gapminder.csv'**.\n",
    "\n",
    "**Instructions**\n",
    "\n",
    "* Read **'gapminder.csv'** into a DataFrame with **index_col=['Year','region','Country']**. Sort the index.\n",
    "* Group **gapminder** with a level of **['Year','region']** using its **level** parameter. Save the result as **by_year_region**.\n",
    "* Define the function **spread** which returns the maximum and minimum of an input series. This has been done for you.\n",
    "* Create a dictionary with **'population':'sum'**, **'child_mortality':'mean'** and **'gdp':spread** as aggregator. This has been done for you.\n",
    "* Use the **aggregator** dictionary to aggregate **by_year_region**. Save the result as **aggregated**.\n",
    "* Print the last 6 entries of **aggregated**. This has been done for you, so hit 'Submit Answer' to view the result."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read the CSV file into a DataFrame and sort the index: gapminder\n",
    "gapminder = pd.read_csv(gapminder_data, index_col=['Year', 'region', 'Country'])\n",
    "gapminder.sort_index(inplace=True)\n",
    "gapminder.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Group gapminder by 'Year' and 'region': by_year_region\n",
    "by_year_region = gapminder.groupby(level=['Year', 'region'])\n",
    "\n",
    "# Define the function to compute spread: spread\n",
    "def spread(series):\n",
    "    return series.max() - series.min()\n",
    "\n",
    "# Create the dictionary: aggregator\n",
    "aggregator = {'population':'sum', 'child_mortality':'mean', 'gdp':spread}\n",
    "\n",
    "# Aggregate by_year_region using the dictionary: aggregated\n",
    "aggregated = by_year_region.agg(aggregator)\n",
    "aggregated.tail(6)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Grouping on a function of the index\n",
    "\n",
    "Groupby operations can also be performed on transformations of the index values. In the case of a DateTimeIndex, we can extract portions of the datetime over which to group.\n",
    "\n",
    "In this exercise you'll read in a set of sample sales data from February 2015 and assign the **'Date'** column as the index. Your job is to group the sales data by the day of the week and aggregate the sum of the **'Units'** column.\n",
    "\n",
    "Is there a day of the week that is more popular for customers? To find out, you're going to use **.strftime('%a')** to transform the index datetime values to abbreviated days of the week.\n",
    "\n",
    "The sales data CSV file is available to you as **'sales.csv'**.\n",
    "\n",
    "Instructions\n",
    "\n",
    "* Read **'sales.csv'** into a DataFrame with **index_col='Date'** and **parse_dates=True**.\n",
    "* Create a groupby object with **sales.index.strftime('%a')** as input and assign it to **by_day**.\n",
    "* Aggregate the **'Units'** column of **by_day** with the **.sum()** method. Save the result as **units_sum**.\n",
    "* Print **units_sum**. This has been done for you, so hit 'Submit Answer' to see the result."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sales_values = np.array([['2015-02-02 08:30:00', 'Hooli', 'Software', 3],\n",
    "                         ['2015-02-02 21:00:00', 'Mediacore', 'Hardware', 9],\n",
    "                         ['2015-02-03 14:00:00', 'Initech', 'Software', 13],\n",
    "                         ['2015-02-04 15:30:00', 'Streeplex', 'Software', 13],\n",
    "                         ['2015-02-04 22:00:00', 'Acme Coporation', 'Hardware', 14],\n",
    "                         ['2015-02-05 02:00:00', 'Acme Coporation', 'Software', 19],\n",
    "                         ['2015-02-05 22:00:00', 'Hooli', 'Service', 10],\n",
    "                         ['2015-02-07 23:00:00', 'Acme Coporation', 'Hardware', 1],\n",
    "                         ['2015-02-09 09:00:00', 'Streeplex', 'Service', 19],\n",
    "                         ['2015-02-09 13:00:00', 'Mediacore', 'Software', 7],\n",
    "                         ['2015-02-11 20:00:00', 'Initech', 'Software', 7],\n",
    "                         ['2015-02-11 23:00:00', 'Hooli', 'Software', 4],\n",
    "                         ['2015-02-16 12:00:00', 'Hooli', 'Software', 10],\n",
    "                         ['2015-02-19 11:00:00', 'Mediacore', 'Hardware', 16],\n",
    "                         ['2015-02-19 16:00:00', 'Mediacore', 'Service', 10],\n",
    "                         ['2015-02-21 05:00:00', 'Mediacore', 'Software', 3],\n",
    "                         ['2015-02-21 20:30:00', 'Hooli', 'Hardware', 3],\n",
    "                         ['2015-02-25 00:30:00', 'Initech', 'Service', 10],\n",
    "                         ['2015-02-26 09:00:00', 'Streeplex', 'Service', 4]])\n",
    "sales_cols = ['Date', 'Company', 'Product', 'Units']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sales = pd.DataFrame(sales_values, columns=sales_cols)\n",
    "sales['Date'] = pd.to_datetime(sales['Date'])\n",
    "sales.set_index('Date', inplace=True)\n",
    "sales['Units'] = sales['Units'].astype('int64')\n",
    "sales.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sales.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a groupby object: by_day\n",
    "by_day = sales.groupby(sales.index.strftime('%a'))\n",
    "\n",
    "# Create sum: units_sum\n",
    "units_sum = by_day['Units'].sum()\n",
    "units_sum"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Groupby and transformation"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### The z-score"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def zscore_def(series):\n",
    "    return (series - series.mean()) / series.std()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### The automobile dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "auto = pd.read_csv(auto_mpg)\n",
    "auto.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### MPG z-score"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "zscore_def(auto['mpg']).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# imported from scipy at the top\n",
    "zscore(auto['mpg'])[:5]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### MPG z-score by year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "auto.groupby('yr')['mpg'].transform(zscore_def).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Apply transformation and aggregation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def zscore_with_year_and_name(group):\n",
    "    df = pd.DataFrame(\n",
    "        {'mpg': zscore(group['mpg']),\n",
    "         'year': group['yr'],\n",
    "         'name': group['name']})\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "auto.groupby('yr').apply(zscore_with_year_and_name).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exercises"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Detecting outliers with Z-Scores\n",
    "\n",
    "As Dhavide demonstrated in the video using the **zscore** function, you can apply a **.transform()** method after grouping to apply a function to groups of data independently. The z-score is also useful to find outliers: a z-score value of +/- 3 is generally considered to be an outlier.\n",
    "\n",
    "In this example, you're going to normalize the Gapminder data in 2010 for life expectancy and fertility by the z-score per region. Using boolean indexing, you will filter out countries that have high fertility rates and low life expectancy for their region.\n",
    "\n",
    "The Gapminder DataFrame for 2010 indexed by **'Country'** is provided for you as **gapminder_2010**.\n",
    "\n",
    "**Instructions**\n",
    "\n",
    "* Import **zscore** from **scipy.stats**.\n",
    "* Group **gapminder_2010** by **'region'** and transform the **['life','fertility']** columns by **zscore**.\n",
    "* Construct a boolean Series of the bitwise **or** between **standardized['life'] < -3** and **standardized['fertility'] > 3**.\n",
    "* Filter **gapminder_2010** using **.loc[]** and the **outliers** Boolean Series. Save the result as **gm_outliers**.\n",
    "* Print **gm_outliers**. This has been done for you, so hit 'Submit Answer' to see the results."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "gapminder = pd.read_csv(gapminder_data, index_col='Country')\n",
    "gapminder_mask = gapminder['Year'] == 2010\n",
    "gapminder_2010 = gapminder[gapminder_mask].copy()\n",
    "gapminder_2010.drop('Year', axis=1, inplace=True)\n",
    "gapminder_2010.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Group gapminder_2010: standardized\n",
    "standardized = gapminder_2010.groupby('region')[['life', 'fertility']].transform(zscore)\n",
    "standardized.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Construct a Boolean Series to identify outliers: outliers\n",
    "outliers = (standardized['life'] < -3) | (standardized['fertility'] > 3)\n",
    "\n",
    "# Filter gapminder_2010 by the outliers: gm_outliers\n",
    "gm_outliers = gapminder_2010.loc[outliers]\n",
    "gm_outliers"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Filling missing data (imputation) by group\n",
    "\n",
    "Many statistical and machine learning packages cannot determine the best action to take when missing data entries are encountered. Dealing with missing data is natural in pandas (both in using the default behavior and in defining a custom behavior). In Chapter 1, you practiced using the **.dropna()** method to drop missing values. Now, you will practice imputing missing values. You can use **.groupby()** and **.transform()** to fill missing data appropriately for each group.\n",
    "\n",
    "Your job is to fill in missing **'age'** values for passengers on the Titanic with the median age from their **'gender'** and **'pclass'**. To do this, you'll group by the **'sex'** and **'pclass'** columns and transform each group with a custom function to call **.fillna()** and impute the median value.\n",
    "\n",
    "The DataFrame has been pre-loaded as **titanic**. Explore it in the IPython Shell by printing the output of **titanic.tail(10)**. Notice in particular the NaNs in the **'age'** column.\n",
    "\n",
    "**Instructions**\n",
    "\n",
    "* Group **titanic** by **'sex'** and **'pclass'**. Save the result as **by_sex_class**.\n",
    "* Write a function called **impute_median()** that fills missing values with the median of a series. This has been done for you.\n",
    "* Call **.transform()** with **impute_median** on the **'age'** column of **by_sex_class**.\n",
    "* Print the output of **titanic.tail(10)**. This has been done for you - hit 'Submit Answer' to see how the missing values have now been imputed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "titanic = pd.read_csv(titanic_data)\n",
    "titanic.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a groupby object: by_sex_class\n",
    "by_sex_class = titanic.groupby(['sex', 'pclass'])\n",
    "\n",
    "# Write a function that imputes median\n",
    "def impute_median(series):\n",
    "    return series.fillna(series.median())\n",
    "\n",
    "# Impute age and assign to titanic['age']\n",
    "titanic.age = by_sex_class['age'].transform(impute_median)\n",
    "titanic.tail(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Other transformations with .apply\n",
    "\n",
    "The **.apply()** method when used on a groupby object performs an arbitrary function on each of the groups. These functions can be aggregations, transformations or more complex workflows. The **.apply()** method will then combine the results in an intelligent way.\n",
    "\n",
    "In this exercise, you're going to analyze economic disparity within regions of the world using the Gapminder data set for 2010. To do this you'll define a function to compute the aggregate spread of per capita GDP in each region and the individual country's z-score of the regional per capita GDP. You'll then select three countries - United States, Great Britain and China - to see a summary of the regional GDP and that country's z-score against the regional mean.\n",
    "\n",
    "The 2010 Gapminder DataFrame is provided for you as **gapminder_2010**. Pandas has been imported as **pd**.\n",
    "\n",
    "The following function has been defined for your use:\n",
    "\n",
    "```python\n",
    "def disparity(gr):\n",
    "    # Compute the spread of gr['gdp']: s\n",
    "    s = gr['gdp'].max() - gr['gdp'].min()\n",
    "    # Compute the z-score of gr['gdp'] as (gr['gdp']-gr['gdp'].mean())/gr['gdp'].std(): z\n",
    "    z = (gr['gdp'] - gr['gdp'].mean())/gr['gdp'].std()\n",
    "    # Return a DataFrame with the inputs {'z(gdp)':z, 'regional spread(gdp)':s}\n",
    "    return pd.DataFrame({'z(gdp)':z , 'regional spread(gdp)':s})\n",
    "```\n",
    "\n",
    "**Instructions**\n",
    "\n",
    "* Group **gapminder_2010** by **'region'**. Save the result as **regional**.\n",
    "* Apply the provided **disparity** function on **regional**, and save the result as **reg_disp**.\n",
    "* Use **.loc[]** to select **['United States','United Kingdom','China'**] from **reg_disp** and print the results."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def disparity(gr):\n",
    "    # Compute the spread of gr['gdp']: s\n",
    "    s = gr['gdp'].max() - gr['gdp'].min()\n",
    "    # Compute the z-score of gr['gdp'] as (gr['gdp']-gr['gdp'].mean())/gr['gdp'].std(): z\n",
    "    z = (gr['gdp'] - gr['gdp'].mean())/gr['gdp'].std()\n",
    "    # Return a DataFrame with the inputs {'z(gdp)':z, 'regional spread(gdp)':s}\n",
    "    return pd.DataFrame({'z(gdp)':z , 'regional spread(gdp)':s})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "gapminder_2010.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Group gapminder_2010 by 'region': regional\n",
    "regional = gapminder_2010.groupby('region')\n",
    "\n",
    "# Apply the disparity function on regional: reg_disp\n",
    "reg_disp = regional.apply(disparity)\n",
    "\n",
    "# Get the 'Country' level values from the DataFrame's index\n",
    "countries = reg_disp.index.get_level_values('Country')\n",
    "\n",
    "# Now you can use 'regions' in your operations\n",
    "reg_disp = reg_disp.loc[countries.isin(['United States', 'United Kingdom', 'China'])]\n",
    "\n",
    "reg_disp"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Groupby and filterning"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### The automobile dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "auto.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Mean MPG by year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "auto.groupby('yr')['mpg'].mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### groupby object"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "splitting = auto.groupby('yr')\n",
    "type(splitting)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "type(splitting.groups)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(splitting.groups.keys())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### groupby object: iteration"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for group_name, group in splitting:\n",
    "    avg = group['mpg'].mean()\n",
    "    print(group_name, avg)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### groupby object: iteration and filtering"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for group_name, group in splitting:\n",
    "    avg = group.loc[group['name'].str.contains('chevrolet'), 'mpg'].mean()\n",
    "    print(group_name, avg)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### groupby object: comprehension"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "chevy_means = {year:group.loc[group['name'].str.contains('chevrolet'),'mpg'].mean() for year,group in splitting}\n",
    "pd.Series(chevy_means)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Boolean groupby"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "chevy = auto['name'].str.contains('chevrolet')\n",
    "auto.groupby(['yr', chevy])['mpg'].mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exercises"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Grouping and filtering with .apply()\n",
    "\n",
    "By using **.apply()**, you can write functions that filter rows within groups. The **.apply()** method will handle the iteration over individual groups and then re-combine them back into a Series or DataFrame.\n",
    "\n",
    "In this exercise you'll take the Titanic data set and analyze survival rates from the **'C'** deck, which contained the most passengers. To do this you'll group the dataset by **'sex'** and then use the **.apply()** method on a provided user defined function which calculates the mean survival rates on the **'C'** deck:\n",
    "\n",
    "```python\n",
    "def c_deck_survival(gr):\n",
    "    c_passengers = gr['cabin'].str.startswith('C').fillna(False)\n",
    "    return gr.loc[c_passengers, 'survived'].mean()\n",
    "```\n",
    "\n",
    "The DataFrame has been pre-loaded as **titanic**.\n",
    "\n",
    "**Instructions**\n",
    "\n",
    "* Group **titanic** by **'sex'**. Save the result as **by_sex**.\n",
    "* Apply the provided **c_deck_survival** function on the **by_sex** DataFrame. Save the result as **c_surv_by_sex**.\n",
    "* Print **c_surv_by_sex**."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "titanic = pd.read_csv(titanic_data)\n",
    "titanic.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def c_deck_survival(gr):\n",
    "    c_passengers = gr['cabin'].str.startswith('C').fillna(False)\n",
    "    return gr.loc[c_passengers, 'survived'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a groupby object using titanic over the 'sex' column: by_sex\n",
    "by_sex = titanic.groupby('sex')\n",
    "\n",
    "# Call by_sex.apply with the function c_deck_survival\n",
    "c_surv_by_sex = by_sex.apply(c_deck_survival)\n",
    "c_surv_by_sex.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Grouping and filtering with .filter()\n",
    "\n",
    "You can use groupby with the **.filter()** method to remove whole groups of rows from a DataFrame based on a boolean condition.\n",
    "\n",
    "In this exercise, you'll take the February sales data and remove entries from companies that purchased less than or equal to 35 Units in the whole month.\n",
    "\n",
    "First, you'll identify how many units each company bought for verification. Next you'll use the **.filter()** method after grouping by **'Company'** to remove all rows belonging to companies whose sum over the **'Units'** column was less than or equal to 35. Finally, verify that the three companies whose total Units purchased were less than or equal to 35 have been filtered out from the DataFrame.\n",
    "\n",
    "**Instructions**\n",
    "\n",
    "* Group **sales** by **'Company'**. Save the result as **by_company**.\n",
    "* Compute and print the sum of the **'Units'** column of **by_company**.\n",
    "* Call **.filter()** on **by_company** with **lambda g:g['Units'].sum() > 35** as input and print the result."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sales_values = np.array([['2015-02-02 08:30:00', 'Hooli', 'Software', 3],\n",
    "                         ['2015-02-02 21:00:00', 'Mediacore', 'Hardware', 9],\n",
    "                         ['2015-02-03 14:00:00', 'Initech', 'Software', 13],\n",
    "                         ['2015-02-04 15:30:00', 'Streeplex', 'Software', 13],\n",
    "                         ['2015-02-04 22:00:00', 'Acme Coporation', 'Hardware', 14],\n",
    "                         ['2015-02-05 02:00:00', 'Acme Coporation', 'Software', 19],\n",
    "                         ['2015-02-05 22:00:00', 'Hooli', 'Service', 10],\n",
    "                         ['2015-02-07 23:00:00', 'Acme Coporation', 'Hardware', 1],\n",
    "                         ['2015-02-09 09:00:00', 'Streeplex', 'Service', 19],\n",
    "                         ['2015-02-09 13:00:00', 'Mediacore', 'Software', 7],\n",
    "                         ['2015-02-11 20:00:00', 'Initech', 'Software', 7],\n",
    "                         ['2015-02-11 23:00:00', 'Hooli', 'Software', 4],\n",
    "                         ['2015-02-16 12:00:00', 'Hooli', 'Software', 10],\n",
    "                         ['2015-02-19 11:00:00', 'Mediacore', 'Hardware', 16],\n",
    "                         ['2015-02-19 16:00:00', 'Mediacore', 'Service', 10],\n",
    "                         ['2015-02-21 05:00:00', 'Mediacore', 'Software', 3],\n",
    "                         ['2015-02-21 20:30:00', 'Hooli', 'Hardware', 3],\n",
    "                         ['2015-02-25 00:30:00', 'Initech', 'Service', 10],\n",
    "                         ['2015-02-26 09:00:00', 'Streeplex', 'Service', 4]])\n",
    "sales_cols = ['Date', 'Company', 'Product', 'Units']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sales = pd.DataFrame(sales_values, columns=sales_cols)\n",
    "sales['Date'] = pd.to_datetime(sales['Date'])\n",
    "sales.set_index('Date', inplace=True)\n",
    "sales['Units'] = sales['Units'].astype('int64')\n",
    "sales.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Group sales by 'Company': by_company\n",
    "by_company = sales.groupby('Company')\n",
    "\n",
    "# Compute the sum of the 'Units' of by_company: by_com_sum\n",
    "by_com_sum = by_company['Units'].sum()\n",
    "by_com_sum"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Filter 'Units' where the sum is > 35: by_com_filt\n",
    "by_com_filt = by_company.filter(lambda g: g['Units'].sum() > 35)\n",
    "by_com_filt"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Filtering and grouping with .map()\n",
    "\n",
    "You have seen how to group by a column, or by multiple columns. Sometimes, you may instead want to group by a function/transformation of a column. The key here is that the Series is indexed the same way as the DataFrame. You can also mix and match column grouping with Series grouping.\n",
    "\n",
    "In this exercise your job is to investigate survival rates of passengers on the Titanic by **'age'** and **'pclass'**. In particular, the goal is to find out what fraction of children under 10 survived in each **'pclass'**. You'll do this by first creating a boolean array where **True** is passengers under 10 years old and **False** is passengers over 10. You'll use **.map()** to change these values to strings.\n",
    "\n",
    "Finally, you'll group by the under 10 series and the **'pclass'** column and aggregate the **'survived'** column. The **'survived'** column has the value 1 if the passenger survived and 0 otherwise. The mean of the **'survived'** column is the fraction of passengers who lived.\n",
    "\n",
    "The DataFrame has been pre-loaded for you as titanic.\n",
    "\n",
    "**Instructions**\n",
    "\n",
    "* Create a Boolean Series of **titanic['age'] < 10** and call **.map** with **{True:'under 10', False:'over 10'}**.\n",
    "* Group **titanic** by the **under10** Series and then compute and print the mean of the **'survived'** column.\n",
    "* Group **titanic** by the **under10** Series as well as the **'pclass'** column and then compute and print the mean of the **'survived'** column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "titanic.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create the Boolean Series: under10\n",
    "under10 = (titanic['age'] < 10).map({True:'under 10', False:'over 10'})\n",
    "under10.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Group by under10 and compute the survival rate\n",
    "survived_mean_1 = titanic.groupby(under10)['survived'].mean()\n",
    "survived_mean_1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Group by under10 and pclass and compute the survival rate\n",
    "survived_mean_2 = titanic.groupby([under10, 'pclass'])['survived'].mean()\n",
    "survived_mean_2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Bringing it all together\n",
    "\n",
    "Here, you will bring together everything you have learned in this course while working with data recorded from the Summer Olympic games that goes as far back as 1896! This is a rich dataset that will allow you to fully apply the data manipulation techniques you have learned. You will pivot, unstack, group, slice, and reshape your data as you explore this dataset and uncover some truly fascinating insights. Enjoy!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Case Study - Summer Olympics"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Olympic medals dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "medals = pd.read_csv(medals_data)\n",
    "medals.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Reminder: indexing & pivoting\n",
    "\n",
    "* Filtering and indexing\n",
    "    * One-level indexing\n",
    "    * Multi-level indexing\n",
    "* Reshaping DataFrames with pivot()\n",
    "* pivot_table()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Reminder: groupby\n",
    "\n",
    "* Useful DataFrame methods\n",
    "    * unique()\n",
    "    * value_counts()\n",
    "* Aggregations, transformations, filtering"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Case Study Explorations"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Grouping and aggregating\n",
    "\n",
    "The Olympic medal data for the following exercises comes from [The Guardian](#https://www.theguardian.com/sport/datablog/2012/jun/25/olympic-medal-winner-list-data). It comprises records of all events held at the Olympic games between 1896 and 2012.\n",
    "\n",
    "Suppose you have loaded the data into a DataFrame medals. You now want to find the total number of **medals** awarded to the USA per edition. To do this, filter the **'USA'** rows and use the **groupby()** function to put the **'Edition'** column on the index:\n",
    "\n",
    "```python\n",
    "USA_edition_grouped = medals.loc[medals.NOC == 'USA'].groupby('Edition')\n",
    "```\n",
    "\n",
    "Given the goal of finding the total number of USA medals awarded per edition, what column should you select and which aggregation method should you use?\n",
    "\n",
    "**Instructions**\n",
    "\n",
    "Possible Answers\n",
    "\n",
    "```python\n",
    "USA_edition_grouped['City'].mean()\n",
    "USA_edition_grouped['Athlete'].sum()\n",
    "USA_edition_grouped['Medal'].count()\n",
    "USA_edition_grouped['Gender'].first()\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "USA_edition_grouped = medals.loc[medals.NOC == 'USA'].groupby('Edition')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "USA_edition_grouped['Medal'].count().head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Using .value_counts() for ranking\n",
    "For this exercise, you will use the pandas Series method **.value_counts()** to determine the top 15 countries ranked by total number of medals.\n",
    "\n",
    "Notice that **.value_counts()** sorts by values by default. The result is returned as a Series of counts indexed by unique entries from the original Series with values (counts) ranked in descending order.\n",
    "\n",
    "The DataFrame has been pre-loaded for you as medals.\n",
    "\n",
    "**Instructions**\n",
    "\n",
    "* Extract the **'NOC'** column from the DataFrame **medals** and assign the result to **country_names**. Notice that this Series has repeated entries for every medal (of any type) a country has won in any Edition of the Olympics.\n",
    "* Create a Series **medal_counts** by applying **.value_counts()** to the Series **country_names**.\n",
    "* Print the top 15 countries ranked by total number of medals won. This has been done for you, so hit 'Submit Answer' to see the result."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Select the 'NOC' column of medals: country_names\n",
    "country_names = medals['NOC']\n",
    "country_names.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Count the number of medals won by each country: medal_counts\n",
    "medal_counts = country_names.value_counts()\n",
    "\n",
    "# Print top 15 countries ranked by medals\n",
    "print(medal_counts.head(15))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Using .pivot_table() to count medals by type\n",
    "\n",
    "Rather than ranking countries by total medals won and showing that list, you may want to see a bit more detail. You can use a pivot table to compute how many separate bronze, silver and gold medals each country won. That pivot table can then be used to repeat the previous computation to rank by total medals won.\n",
    "\n",
    "In this exercise, you will use **.pivot_table()** first to aggregate the total medals by type. Then, you can use **.sum()** along the columns of the pivot table to produce a new column. When the modified pivot table is sorted by the total medals column, you can display the results from the last exercise with a bit more detail.\n",
    "\n",
    "**Instructions**\n",
    "\n",
    "* Construct a pivot table **counted** from the DataFrame **medals** aggregating by **count**. Use **'NOC'** as the index, **'Athlete'** for the values, and **'Medal'** for the columns.\n",
    "* Modify the DataFrame **counted** by adding a column **counted['totals']**. The new column **'totals'** should contain the result of taking the sum along the columns (i.e., use **.sum(axis='columns')**).\n",
    "* Overwrite the DataFrame **counted** by sorting it with the **.sort_values()** method. Specify the keyword argument **ascending=False**.\n",
    "* Print the first 15 rows of **counted** using **.head(15)**. This has been done for you, so hit 'Submit Answer' to see the result."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Construct the pivot table: counted\n",
    "counted = medals.pivot_table(index='NOC',\n",
    "                             columns='Medal',\n",
    "                             values='Athlete',\n",
    "                             aggfunc='count')\n",
    "counted.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create the new column: counted['totals']\n",
    "counted['totals'] = counted.sum(axis='columns')\n",
    "counted.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Sort counted by the 'totals' column\n",
    "counted = counted.sort_values('totals', ascending=False)\n",
    "counted.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Understanding the column labels"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 'Gender' and 'Event_gender'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "medals.loc[145:154, ['NOC', 'Gender', 'Event', 'Event_gender', 'Medal']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Reminder: slicing & filtering\n",
    "\n",
    "* Indexing and slicing\n",
    "    * .loc[] and .iloc[] accessors\n",
    "* Filtering\n",
    "    * Selecting by Boolean Series\n",
    "    * Filtering null/non-null and zero/non-zero values"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Reminder: Handling categorical data\n",
    "\n",
    "* Useful DataFrame methods for handling categorical data:\n",
    "    * value_counts()\n",
    "    * unique()\n",
    "    * groupby()\n",
    "* groupby() aggregations:\n",
    "    * mean(), std(), count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Case Study Explorations"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Applying .drop_duplicates()\n",
    "\n",
    "What could be the difference between the **'Event_gender'** and **'Gender'** columns? You should be able to evaluate your guess by looking at the unique values of the pairs **(Event_gender, Gender)** in the data. In particular, you should not see something like **(Event_gender='M', Gender='Women')**. However, you will see that, strangely enough, there is an observation with **(Event_gender='W', Gender='Men')**.\n",
    "\n",
    "The duplicates can be dropped using the **.drop_duplicates()** method, leaving behind the unique observations. The DataFrame has been loaded as **medals**.\n",
    "\n",
    "**Instructions**\n",
    "\n",
    "* Select the columns **'Event_gender'** and **'Gender'**.\n",
    "* Create a dataframe **ev_gen_uniques** containing the unique pairs contained in **ev_gen**.\n",
    "* Print **ev_gen_uniques**. This has been done for you, so hit 'Submit Answer' to see the result."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "medals = pd.read_csv(medals_data)\n",
    "medals.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Select columns: ev_gen\n",
    "ev_gen = medals[['Event_gender', 'Gender']]\n",
    "ev_gen.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop duplicate pairs: ev_gen_uniques\n",
    "ev_gen_uniques = ev_gen.drop_duplicates()\n",
    "ev_gen_uniques"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Finding possible errors with .groupby()\n",
    "\n",
    "You will now use **.groupby()** to continue your exploration. Your job is to group by **'Event_gender'** and **'Gender'** and count the rows.\n",
    "\n",
    "You will see that there is only one suspicious row: This is likely a data error.\n",
    "\n",
    "The DataFrame is available to you as **medals**.\n",
    "\n",
    "**Instructions**\n",
    "\n",
    "* Group **medals** by **'Event_gender'** and **'Gender'**.\n",
    "* Create a **medal_count_by_gender** DataFrame with a group count using the **.count()** method.\n",
    "* Print **medal_count_by_gender**. This has been done for you, so hit 'Submit Answer' to view the result."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Group medals by the two columns: medals_by_gender\n",
    "medals_by_gender = medals.groupby(['Event_gender', 'Gender'])\n",
    "\n",
    "# Create a DataFrame with a group count: medal_count_by_gender\n",
    "medal_count_by_gender = medals_by_gender.count()\n",
    "medal_count_by_gender"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Locating suspicious data\n",
    "\n",
    "You will now inspect the suspect record by locating the offending row.\n",
    "\n",
    "You will see that, according to the data, Joyce Chepchumba was a man that won a medal in a women's event. That is a data error as you can confirm with a web search.\n",
    "\n",
    "Instructions\n",
    "\n",
    "* Create a Boolean Series with a condition that captures the only row that has **medals.Event_gender == 'W'** and **medals.Gender == 'Men'**. Be sure to use the & operator.\n",
    "* Use the Boolean Series to create a DataFrame called **suspect** with the suspicious row.\n",
    "* Print **suspect**. This has been done for you, so hit 'Submit Answer' to see the result."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create the Boolean Series: sus\n",
    "sus = (medals.Event_gender == 'W') & (medals.Gender == 'Men')\n",
    "\n",
    "# Create a DataFrame with the suspicious row: suspect\n",
    "suspect = medals[sus]\n",
    "suspect"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Constructing alternative country rankings"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Counting distinct events"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sports = medals['Sport'].unique()\n",
    "sports"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Ranking of distinct events\n",
    "\n",
    "* Top five countries that have won medals in the most sports\n",
    "* Compare medal counts of USA and USSR from 1952 to 1988"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Two new DataFrame methods\n",
    "\n",
    "* idxmax(): Row or column label where maximum value is located\n",
    "* idxmin(): Row or column label where minimum value is located"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### idxmax() Example"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "weather = pd.DataFrame({'Month': ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun',\n",
    "                                  'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'],\n",
    "                        'Mean TemperatureF': [32.354839, 28.714286, 35.000000, 53.100000, 62.612903, 70.133333,\n",
    "                                              72.870968, 70.000000, 63.766667, 55.451613, 39.800000, 34.935484]})\n",
    "weather.set_index('Month', inplace=True)\n",
    "weather"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Using idxmax()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "weather.idxmax()  # Returns month of highest temperature"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Using idxmax() along columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "weather.T  # Returns DataFrame with single row, 12 columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "weather.T.idxmax(axis='columns')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Using idxmin()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "weather.T.idxmin(axis='columns')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Case Study Explorations"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Using .nunique() to rank by distinct sports\n",
    "\n",
    "You may want to know which countries won medals in the most distinct sports. The **.nunique()** method is the principal aggregation here. Given a categorical Series **S**, **S.nunique()** returns the number of distinct categories.\n",
    "\n",
    "**Instructions**\n",
    "\n",
    "* Group **medals** by **'NOC'**.\n",
    "* Compute the number of distinct sports in which each country won medals. To do this, select the **'Sport'** column from **country_grouped** and apply **.nunique()**.\n",
    "* Sort **Nsports** in descending order with **.sort_values()** and **ascending=False**.\n",
    "* Print the first 15 rows of **Nsports**. This has been done for you, so hit 'Submit Answer' to see the result."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Group medals by 'NOC': country_grouped\n",
    "country_grouped = medals.groupby('NOC')\n",
    "\n",
    "# Compute the number of distinct sports in which each country won medals: Nsports\n",
    "Nsports = country_grouped['Sport'].nunique()\n",
    "\n",
    "# Sort the values of Nsports in descending order\n",
    "Nsports = Nsports.sort_values(ascending=False)\n",
    "\n",
    "# Print the top 15 rows of Nsports\n",
    "print(Nsports.head(15))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**The USSR is not in the top 5 in this category, while the USA continues to remain on top. What could be the cause of this? You'll compare the medal counts of USA vs. USSR more closely in the next two exercises to find out.**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Counting USA vs. USSR Cold War Olympic Sports\n",
    "\n",
    "The Olympic competitions between 1952 and 1988 took place during the height of the Cold War between the United States of America (USA) & the Union of Soviet Socialist Republics (USSR). Your goal in this exercise is to aggregate the number of distinct sports in which the USA and the USSR won medals during the Cold War years.\n",
    "\n",
    "The construction is mostly the same as in the preceding exercise. There is an additional filtering stage beforehand in which you reduce the original DataFrame **medals** by extracting data from the Cold War period that applies only to the US or to the USSR. The relevant country codes in the DataFrame, which has been pre-loaded as **medals**, are **'USA'** & **'URS'**.\n",
    "\n",
    "**Instructions**\n",
    "\n",
    "* Using **medals**, create a Boolean Series called **during_cold_war** that is **True** when **'Edition' >= 1952 & <= 1988**.\n",
    "* Using **medals**, create a Boolean Series called **is_usa_urs** that is **True** when **'NOC'** is either **'USA'** or **'URS'**.\n",
    "* Filter the **medals** DataFrame using **during_cold_war** and **is_usa_urs** to create a new DataFrame called **cold_war_medals**.\n",
    "* Group **cold_war_medals** by **'NOC'**.\n",
    "* Create a Series **Nsports** from **country_grouped** using indexing & chained methods:\n",
    "    * Extract the column **'Sport'**.\n",
    "    * Use **.nunique()** to get the number of unique elements in each group;\n",
    "    * Apply **.sort_values(ascending=False)** to rearrange the Series.\n",
    "* Print the final Series **Nsports**. This has been done for you, so hit 'Submit Answer' to see the result!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Extract all rows for which the 'Edition' is between 1952 & 1988: during_cold_war\n",
    "during_cold_war = (1952 <= medals.Edition) & (medals.Edition <= 1988)\n",
    "\n",
    "# Extract rows for which 'NOC' is either 'USA' or 'URS': is_usa_urs\n",
    "is_usa_urs = medals.NOC.isin(['USA', 'URS'])\n",
    "\n",
    "# Use during_cold_war and is_usa_urs to create the DataFrame: cold_war_medals\n",
    "cold_war_medals = medals.loc[during_cold_war & is_usa_urs]\n",
    "\n",
    "# Group cold_war_medals by 'NOC'\n",
    "country_grouped = cold_war_medals.groupby('NOC')\n",
    "\n",
    "# Create Nsports\n",
    "Nsports = country_grouped['Sport'].nunique().sort_values(ascending=False)\n",
    "Nsports"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**As you can see, the USSR is actually higher than the US when you look only at the Olympic competitions between 1952 and 1988.**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Counting USA vs. USSR Cold War Olympic Medals\n",
    "\n",
    "For this exercise, you want to see which country, the USA or the USSR, won the most medals consistently over the Cold War period.\n",
    "\n",
    "There are several steps involved in carrying out this computation.\n",
    "\n",
    "* You'll need a pivot table with years (**'Edition'**) on the index and countries (**'NOC'**) on the columns. The entries will be the total number of medals each country won that year. If the country won no medals in a given edition, expect a **NaN** in that entry of the pivot table.\n",
    "* You'll need to slice the Cold War period and subset the **'USA'** and **'URS'** columns.\n",
    "* You'll need to make a Series from this slice of the pivot table that tells which country won the most medals in that edition using **.idxmax(axis='columns')**. If **.max()** returns the maximum value of Series or 1D array, **.idxmax()** returns the index of the maximizing element. The argument **axis=columns** or **axis=1** is required because, by default, this aggregation would be done along columns for a DataFrame.\n",
    "* The final Series contains either **'USA'** or **'URS'** according to which country won the most medals in each Olympic edition. You can use **.value_counts()** to count the number of occurrences of each.\n",
    "\n",
    "**Instructions**\n",
    "\n",
    "* Construct **medals_won_by_country** using **medals.pivot_table()**.\n",
    "    * The index should be the years (**'Edition'**) & the columns should be country (**'NOC'**)\n",
    "    * The values should be **'Athlete'** (which captures every medal regardless of kind) & the aggregation method should be **'count'** (which captures the total number of medals won).\n",
    "* Create **cold_war_usa_urs_medals** by slicing the pivot table **medals_won_by_country**. Your slice should contain the editions from years **1952:1988** and only the columns **'USA'** & **'URS'** from the pivot table.\n",
    "* Create the Series **most_medals** by applying the **.idxmax()** method to **cold_war_usa_urs_medals**. Be sure to use **axis='columns'**.\n",
    "* Print the result of applying **.value_counts()** to **most_medals**. The result reported gives the number of times each of the USA or the USSR won more Olympic medals in total than the other between 1952 and 1988."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create the pivot table: medals_won_by_country\n",
    "medals_won_by_country = medals.pivot_table(index='Edition',\n",
    "                                           columns='NOC',\n",
    "                                           values='Athlete',\n",
    "                                           aggfunc='count')\n",
    "medals_won_by_country.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Slice medals_won_by_country: cold_war_usa_urs_medals\n",
    "cold_war_usa_urs_medals = medals_won_by_country.loc[1952:1988 , ['USA','URS']]\n",
    "\n",
    "# Create most_medals \n",
    "most_medals = cold_war_usa_urs_medals.idxmax(axis='columns')\n",
    "\n",
    "# Print most_medals.value_counts()\n",
    "most_medals.value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Reshaping DataFrames for visualization"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "medals = pd.read_csv(medals_data)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Reminder: plotting DataFrames"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "all_medals = medals.groupby('Edition')['Athlete'].count()\n",
    "all_medals.head(6) # Series for all medals, all years"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Plotting DataFrames"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "all_medals.plot(kind='line', marker='.')\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Grouping the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "france = medals.NOC == 'FRA'  # Boolean Series for France\n",
    "france_grps = medals[france].groupby(['Edition', 'Medal'])\n",
    "france_grps['Athlete'].count().head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Reshaping the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "france_medals = france_grps['Athlete'].count().unstack()\n",
    "france_medals.head(12)  # Single level index"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Plotting the result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "france_medals.plot(kind='line', marker='.')\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Case Study Explorations"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Visualizing USA Medal Counts by Edition: Line Plot\n",
    "\n",
    "Your job in this exercise is to visualize the medal counts by **'Edition'** for the USA. The DataFrame has been pre-loaded for you as **medals**.\n",
    "\n",
    "**Instructions**\n",
    "\n",
    "* Create a DataFrame **usa** with data only for the USA.\n",
    "* Group **usa** such that **['Edition', 'Medal']** is the index. Aggregate the count over **'Athlete'**.\n",
    "* Use **.unstack()** with **level='Medal'** to reshape the DataFrame **usa_medals_by_year**.\n",
    "* Construct a line plot from the final DataFrame **usa_medals_by_year**. This has been done for you, so hit 'Submit Answer' to see the plot!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "medals.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create the DataFrame: usa\n",
    "usa = medals[medals.NOC == 'USA']\n",
    "usa.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Group usa by ['Edition', 'Medal'] and aggregate over 'Athlete'\n",
    "usa_medals_by_year = usa.groupby(['Edition', 'Medal'])['Athlete'].count()\n",
    "usa_medals_by_year.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Reshape usa_medals_by_year by unstacking\n",
    "usa_medals_by_year = usa_medals_by_year.unstack(level='Medal')\n",
    "\n",
    "# Plot the DataFrame usa_medals_by_year\n",
    "usa_medals_by_year.plot()\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Visualizing USA Medal Counts by Edition: Area Plot\n",
    "\n",
    "As in the previous exercise, your job in this exercise is to visualize the medal counts by **'Edition'** for the USA. This time, you will use an area plot to see the breakdown better. The **usa** DataFrame has been created and all reshaping from the previous exercise has been done. You need to write the plotting command.\n",
    "\n",
    "**Instructions**\n",
    "\n",
    "* Create an area plot of **usa_medals_by_year**. This can be done by using **.plot.area()**."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create the DataFrame: usa\n",
    "usa = medals[medals.NOC == 'USA']\n",
    "\n",
    "# Group usa by 'Edition', 'Medal', and 'Athlete'\n",
    "usa_medals_by_year = usa.groupby(['Edition', 'Medal'])['Athlete'].count()\n",
    "\n",
    "# Reshape usa_medals_by_year by unstacking\n",
    "usa_medals_by_year = usa_medals_by_year.unstack(level='Medal')\n",
    "\n",
    "# Create an area plot of usa_medals_by_year\n",
    "usa_medals_by_year.plot.area()\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "usa_medals_by_year.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "medals.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Visualizing USA Medal Counts by Edition: Area Plot with Ordered Medals\n",
    "\n",
    "You may have noticed that the medals are ordered according to a lexicographic (dictionary) ordering: **Bronze** < **Gold** < **Silver**. However, you would prefer an ordering consistent with the Olympic rules: **Bronze** < **Silver** < **Gold**.\n",
    "\n",
    "You can achieve this using **Categorical** types. In this final exercise, after redefining the **'Medal'** column of the DataFrame **medals**, you will repeat the area plot from the previous exercise to see the new ordering.\n",
    "\n",
    "**Instructions**\n",
    "\n",
    "* Redefine the **'Medal'** column of the DataFrame **medals** as an ordered categorical. To do this, use **pd.Categorical()** with three keyword arguments:\n",
    "    * **values = medals.Medal**.\n",
    "    * **categories=['Bronze', 'Silver', 'Gold']**.\n",
    "    * **ordered=True**.\n",
    "    * After this, you can verify that the type has changed using **medals.info()**.\n",
    "* Plot the final DataFrame **usa_medals_by_year** as an area plot. This has been done for you, so hit 'Submit Answer' to see how the plot has changed!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Redefine 'Medal' as an ordered categorical\n",
    "medals.Medal = pd.Categorical(values=medals.Medal,\n",
    "                              categories=['Bronze', 'Silver', 'Gold'],\n",
    "                              ordered=True)\n",
    "\n",
    "# Create the DataFrame: usa\n",
    "usa = medals[medals.NOC == 'USA']\n",
    "\n",
    "# Group usa by 'Edition', 'Medal', and 'Athlete'\n",
    "usa_medals_by_year = usa.groupby(['Edition', 'Medal'], observed=False)['Athlete'].count()\n",
    "\n",
    "# Reshape usa_medals_by_year by unstacking\n",
    "usa_medals_by_year = usa_medals_by_year.unstack(level='Medal')\n",
    "\n",
    "# Create an area plot of usa_medals_by_year\n",
    "usa_medals_by_year.plot.area()\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "medals.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "medals.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "usa_medals_by_year.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Final Thoughts\n",
    "\n",
    "**You can now…**\n",
    "\n",
    "* Transform, extract, and filter data from DataFrames\n",
    "* Work with pandas indexes and hierarchical indexes\n",
    "* Reshape and restructure your data\n",
    "* Split your data into groups and categories"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sys\n",
    "\n",
    "# These are the usual ipython objects, including this one you are creating\n",
    "ipython_vars = ['In', 'Out', 'exit', 'quit', 'get_ipython', 'ipython_vars']\n",
    "\n",
    "# Get a sorted list of the objects and their sizes\n",
    "sorted([(x, sys.getsizeof(globals().get(x))) for x in dir() if not x.startswith('_') \n",
    "        and x not in sys.modules and x not in ipython_vars], key=lambda x: x[1], reverse=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Certificate\n",
    "\n",
    "![](https://raw.githubusercontent.com/trenton3983/DataCamp/master/Images/2019-02-04_manipulating_dataframes_with_pandas/2019-02-12_manipulating_dataframes_with_pandas_certificate.JPG)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.11.5"
  },
  "toc-showmarkdowntxt": false
 },
 "nbformat": 4,
 "nbformat_minor": 4
}