Cars_Pandas_GroupAgg.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Cars: Grouping and Aggregation"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Imports"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "from altair import load_dataset"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "cars = load_dataset('cars')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Acceleration</th>\n",
       "      <th>Cylinders</th>\n",
       "      <th>Displacement</th>\n",
       "      <th>Horsepower</th>\n",
       "      <th>Miles_per_Gallon</th>\n",
       "      <th>Name</th>\n",
       "      <th>Origin</th>\n",
       "      <th>Weight_in_lbs</th>\n",
       "      <th>Year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>12.0</td>\n",
       "      <td>8</td>\n",
       "      <td>307.0</td>\n",
       "      <td>130.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>chevrolet chevelle malibu</td>\n",
       "      <td>USA</td>\n",
       "      <td>3504</td>\n",
       "      <td>1970-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>11.5</td>\n",
       "      <td>8</td>\n",
       "      <td>350.0</td>\n",
       "      <td>165.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>buick skylark 320</td>\n",
       "      <td>USA</td>\n",
       "      <td>3693</td>\n",
       "      <td>1970-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>11.0</td>\n",
       "      <td>8</td>\n",
       "      <td>318.0</td>\n",
       "      <td>150.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>plymouth satellite</td>\n",
       "      <td>USA</td>\n",
       "      <td>3436</td>\n",
       "      <td>1970-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>12.0</td>\n",
       "      <td>8</td>\n",
       "      <td>304.0</td>\n",
       "      <td>150.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>amc rebel sst</td>\n",
       "      <td>USA</td>\n",
       "      <td>3433</td>\n",
       "      <td>1970-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10.5</td>\n",
       "      <td>8</td>\n",
       "      <td>302.0</td>\n",
       "      <td>140.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>ford torino</td>\n",
       "      <td>USA</td>\n",
       "      <td>3449</td>\n",
       "      <td>1970-01-01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Acceleration  Cylinders  Displacement  Horsepower  Miles_per_Gallon  \\\n",
       "0          12.0          8         307.0       130.0              18.0   \n",
       "1          11.5          8         350.0       165.0              15.0   \n",
       "2          11.0          8         318.0       150.0              18.0   \n",
       "3          12.0          8         304.0       150.0              16.0   \n",
       "4          10.5          8         302.0       140.0              17.0   \n",
       "\n",
       "                        Name Origin  Weight_in_lbs        Year  \n",
       "0  chevrolet chevelle malibu    USA           3504  1970-01-01  \n",
       "1          buick skylark 320    USA           3693  1970-01-01  \n",
       "2         plymouth satellite    USA           3436  1970-01-01  \n",
       "3              amc rebel sst    USA           3433  1970-01-01  \n",
       "4                ford torino    USA           3449  1970-01-01  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cars.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "406"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(cars)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Grouped mean"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Compute the average of `Acceleration`, `Displacement` and `Horsepower`, grouped by `Origin`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "checksum": "dedb50e7baabe4dabc10a94cc264a876",
     "grade": false,
     "grade_id": "cars-group-agg-a",
     "locked": false,
     "schema_version": 1,
     "solution": true
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Acceleration</th>\n",
       "      <th>Displacement</th>\n",
       "      <th>Horsepower</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Origin</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Europe</th>\n",
       "      <td>16.821918</td>\n",
       "      <td>109.465753</td>\n",
       "      <td>81.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Japan</th>\n",
       "      <td>16.172152</td>\n",
       "      <td>102.708861</td>\n",
       "      <td>79.835443</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>USA</th>\n",
       "      <td>14.942520</td>\n",
       "      <td>247.935039</td>\n",
       "      <td>119.900000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Acceleration  Displacement  Horsepower\n",
       "Origin                                        \n",
       "Europe     16.821918    109.465753   81.000000\n",
       "Japan      16.172152    102.708861   79.835443\n",
       "USA        14.942520    247.935039  119.900000"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# YOUR CODE HERE\n",
    "g1 = cars.groupby(\"Origin\")[[\"Acceleration\", \"Displacement\", \"Horsepower\"]].mean()\n",
    "g1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "checksum": "bcc282394cf44a27d63c221ebba5cb88",
     "grade": true,
     "grade_id": "cars-group-agg-b",
     "locked": true,
     "points": 2,
     "schema_version": 1,
     "solution": false
    }
   },
   "outputs": [],
   "source": [
    "assert list(g1.columns)==['Acceleration', 'Displacement', 'Horsepower']\n",
    "assert list(g1.index)==['Europe', 'Japan', 'USA']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Find the maximum `Acceleration` and `Displacement` grouped by `Origin` and `Cylinders`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "checksum": "79b3d5e43c6d1e2646742442c3665a0b",
     "grade": false,
     "grade_id": "cars-group-agg-c",
     "locked": false,
     "schema_version": 1,
     "solution": true
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>Acceleration</th>\n",
       "      <th>Displacement</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Origin</th>\n",
       "      <th>Cylinders</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Europe</th>\n",
       "      <th>4</th>\n",
       "      <td>24.8</td>\n",
       "      <td>146.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>20.1</td>\n",
       "      <td>183.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>19.6</td>\n",
       "      <td>168.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Japan</th>\n",
       "      <th>3</th>\n",
       "      <td>13.5</td>\n",
       "      <td>80.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>21.0</td>\n",
       "      <td>144.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>15.5</td>\n",
       "      <td>168.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">USA</th>\n",
       "      <th>4</th>\n",
       "      <td>22.2</td>\n",
       "      <td>156.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>21.0</td>\n",
       "      <td>262.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>22.2</td>\n",
       "      <td>455.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  Acceleration  Displacement\n",
       "Origin Cylinders                            \n",
       "Europe 4                  24.8         146.0\n",
       "       5                  20.1         183.0\n",
       "       6                  19.6         168.0\n",
       "Japan  3                  13.5          80.0\n",
       "       4                  21.0         144.0\n",
       "       6                  15.5         168.0\n",
       "USA    4                  22.2         156.0\n",
       "       6                  21.0         262.0\n",
       "       8                  22.2         455.0"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# YOUR CODE HERE\n",
    "g2 = cars.groupby([\"Origin\", \"Cylinders\"])[[\"Acceleration\", \"Displacement\"]].max()\n",
    "g2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "checksum": "8bd4f12e7d25c9b08237deaf429e337c",
     "grade": true,
     "grade_id": "cars-group-agg-d",
     "locked": true,
     "points": 2,
     "schema_version": 1,
     "solution": false
    }
   },
   "outputs": [],
   "source": [
    "assert list(g2.columns)==['Acceleration', 'Displacement']\n",
    "assert [list(i) for i in list(g2.index.levels)]==[['Europe', 'Japan', 'USA'], [3, 4, 5, 6, 8]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Grouped counts"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Compute the number of cars, grouped by `Year` and `Cylinders` and unstack the result:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "checksum": "e9b2f769b90a83adfdebba8e194ed79c",
     "grade": false,
     "grade_id": "cars-group-agg-e",
     "locked": false,
     "schema_version": 1,
     "solution": true
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>Cylinders</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>8</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Year</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1970-01-01</th>\n",
       "      <td>NaN</td>\n",
       "      <td>8.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>23.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1971-01-01</th>\n",
       "      <td>NaN</td>\n",
       "      <td>14.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.0</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1972-01-01</th>\n",
       "      <td>1.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>13.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1973-01-01</th>\n",
       "      <td>1.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.0</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1974-01-01</th>\n",
       "      <td>NaN</td>\n",
       "      <td>15.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7.0</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1975-01-01</th>\n",
       "      <td>NaN</td>\n",
       "      <td>12.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12.0</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1976-01-01</th>\n",
       "      <td>NaN</td>\n",
       "      <td>15.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10.0</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1977-01-01</th>\n",
       "      <td>1.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1978-01-01</th>\n",
       "      <td>NaN</td>\n",
       "      <td>17.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1979-01-01</th>\n",
       "      <td>NaN</td>\n",
       "      <td>12.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1980-01-01</th>\n",
       "      <td>1.0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1982-01-01</th>\n",
       "      <td>NaN</td>\n",
       "      <td>50.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Cylinders     3     4    5     6     8\n",
       "Year                                  \n",
       "1970-01-01  NaN   8.0  NaN   4.0  23.0\n",
       "1971-01-01  NaN  14.0  NaN   8.0   7.0\n",
       "1972-01-01  1.0  14.0  NaN   NaN  13.0\n",
       "1973-01-01  1.0  11.0  NaN   8.0  20.0\n",
       "1974-01-01  NaN  15.0  NaN   7.0   5.0\n",
       "1975-01-01  NaN  12.0  NaN  12.0   6.0\n",
       "1976-01-01  NaN  15.0  NaN  10.0   9.0\n",
       "1977-01-01  1.0  14.0  NaN   5.0   8.0\n",
       "1978-01-01  NaN  17.0  1.0  12.0   6.0\n",
       "1979-01-01  NaN  12.0  1.0   6.0  10.0\n",
       "1980-01-01  1.0  25.0  1.0   2.0   NaN\n",
       "1982-01-01  NaN  50.0  NaN  10.0   1.0"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# YOUR CODE HERE\n",
    "g3 = cars.groupby([\"Year\", \"Cylinders\"])[\"Cylinders\"].count().unstack()\n",
    "g3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "checksum": "aff33a0fdbaedc074d6d6477f257d2b1",
     "grade": true,
     "grade_id": "cars-group-agg-f",
     "locked": true,
     "points": 2,
     "schema_version": 1,
     "solution": false
    }
   },
   "outputs": [],
   "source": [
    "assert list(g3.columns)==[3, 4, 5, 6, 8]\n",
    "assert list(g3.index)==['1970-01-01', '1971-01-01', '1972-01-01', '1973-01-01',\n",
    "                        '1974-01-01', '1975-01-01', '1976-01-01', '1977-01-01', \n",
    "                        '1978-01-01', '1979-01-01', '1980-01-01', '1982-01-01']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Multiple aggregations"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Compute the min and max `Miles_per_Gallon` grouped by `Origin`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "checksum": "a1aa1dbd67e45e780445bb5089edb8df",
     "grade": false,
     "grade_id": "cars-group-agg-g",
     "locked": false,
     "schema_version": 1,
     "solution": true
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>min</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Origin</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Europe</th>\n",
       "      <td>16.2</td>\n",
       "      <td>44.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Japan</th>\n",
       "      <td>18.0</td>\n",
       "      <td>46.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>USA</th>\n",
       "      <td>9.0</td>\n",
       "      <td>39.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         min   max\n",
       "Origin            \n",
       "Europe  16.2  44.3\n",
       "Japan   18.0  46.6\n",
       "USA      9.0  39.0"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# YOUR CODE HERE\n",
    "g4 = cars.groupby(\"Origin\")[\"Miles_per_Gallon\"].aggregate([\"min\", \"max\"])\n",
    "g4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "checksum": "8848e1ffd6037738fa09373fe8eb84b9",
     "grade": true,
     "grade_id": "cars-group-agg-h",
     "locked": true,
     "points": 2,
     "schema_version": 1,
     "solution": false
    }
   },
   "outputs": [],
   "source": [
    "assert set(g4.columns)==set(['min', 'max'])\n",
    "assert list(g4.index)==['Europe', 'Japan', 'USA']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Custom aggregation function"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Compute the range of cylinders (`range` = max - min) grouped by `Origin` and name the resulting column `range`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "checksum": "d3aac19370969d0909009574ff055971",
     "grade": false,
     "grade_id": "cars-group-agg-i",
     "locked": false,
     "schema_version": 1,
     "solution": true
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>range</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Origin</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Europe</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Japan</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>USA</th>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        range\n",
       "Origin       \n",
       "Europe      2\n",
       "Japan       3\n",
       "USA         4"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# YOUR CODE HERE\n",
    "def ranging(x):\n",
    "    return x.max() - x.min()\n",
    "g5 = cars.groupby(\"Origin\")[[\"Cylinders\"]].aggregate([ranging])\n",
    "g5.columns = g5.columns.droplevel(0)\n",
    "g5.columns = [\"range\"]\n",
    "g5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "checksum": "aceac965de224e5ef2aac284cb046980",
     "grade": true,
     "grade_id": "cars-group-agg-j",
     "locked": true,
     "points": 2,
     "schema_version": 1,
     "solution": false
    }
   },
   "outputs": [],
   "source": [
    "assert list(g5.columns)==['range']\n",
    "assert list(g5.index)==['Europe', 'Japan', 'USA']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Group filtering"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Compute the average acceleration, grouped by `Year` and `Origin`, only including groups with a max number of Cylinders less than 6. Unstack the `Origin` level of the resulting hierarchical row index:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "checksum": "1b415e81d8d4a4926852f2a91e2870d4",
     "grade": false,
     "grade_id": "cars-group-agg-k",
     "locked": false,
     "schema_version": 1,
     "solution": true
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">Acceleration</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Origin</th>\n",
       "      <th>Europe</th>\n",
       "      <th>Japan</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Year</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1970-01-01</th>\n",
       "      <td>16.666667</td>\n",
       "      <td>14.750000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1971-01-01</th>\n",
       "      <td>17.400000</td>\n",
       "      <td>16.375000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1972-01-01</th>\n",
       "      <td>18.700000</td>\n",
       "      <td>15.400000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1973-01-01</th>\n",
       "      <td>16.428571</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1974-01-01</th>\n",
       "      <td>15.333333</td>\n",
       "      <td>17.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1975-01-01</th>\n",
       "      <td>15.083333</td>\n",
       "      <td>16.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1977-01-01</th>\n",
       "      <td>15.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1978-01-01</th>\n",
       "      <td>NaN</td>\n",
       "      <td>16.200000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1979-01-01</th>\n",
       "      <td>18.400000</td>\n",
       "      <td>17.200000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1980-01-01</th>\n",
       "      <td>18.366667</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           Acceleration           \n",
       "Origin           Europe      Japan\n",
       "Year                              \n",
       "1970-01-01    16.666667  14.750000\n",
       "1971-01-01    17.400000  16.375000\n",
       "1972-01-01    18.700000  15.400000\n",
       "1973-01-01    16.428571        NaN\n",
       "1974-01-01    15.333333  17.666667\n",
       "1975-01-01    15.083333  16.000000\n",
       "1977-01-01    15.000000        NaN\n",
       "1978-01-01          NaN  16.200000\n",
       "1979-01-01    18.400000  17.200000\n",
       "1980-01-01    18.366667        NaN"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# YOUR CODE HERE\n",
    "# not sure the ordering of it\n",
    "def filter_func(x):\n",
    "    return x.Cylinders.max() < 6\n",
    "#g6 = cars.groupby([\"Year\", \"Origin\"]).mean().filter([filter_func]).[[\"Acceleration\"]]j.unstack(level = \"Origin\")\n",
    "filtered = cars.groupby([\"Year\", \"Origin\"]).filter(filter_func)\n",
    "g6 = filtered.groupby([\"Year\", \"Origin\"])[[\"Acceleration\"]].mean().unstack(level = \"Origin\")\n",
    "g6"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "checksum": "77f95dbe22d7508683b8608869e07164",
     "grade": true,
     "grade_id": "cars-group-agg-l",
     "locked": true,
     "points": 2,
     "schema_version": 1,
     "solution": false
    }
   },
   "outputs": [],
   "source": [
    "assert [list(i) for i in list(g6.columns.levels)]==[['Acceleration'], ['Europe', 'Japan']]\n",
    "assert g6.index.name=='Year'\n",
    "assert len(g6)==10"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Grouped z-scores"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here is the average `Miles_per_Gallon`, grouped by `Year`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "checksum": "a0b521d099001d1526cccc006f47caf5",
     "grade": false,
     "grade_id": "cars-group-agg-m",
     "locked": true,
     "schema_version": 1,
     "solution": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Year\n",
       "1970-01-01    17.689655\n",
       "1971-01-01    21.250000\n",
       "1972-01-01    18.714286\n",
       "1973-01-01    17.100000\n",
       "1974-01-01    22.703704\n",
       "1975-01-01    20.266667\n",
       "1976-01-01    21.573529\n",
       "1977-01-01    23.375000\n",
       "1978-01-01    24.061111\n",
       "1979-01-01    25.093103\n",
       "1980-01-01    33.696552\n",
       "1982-01-01    31.045000\n",
       "Name: Miles_per_Gallon, dtype: float64"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cars.groupby(['Year'])['Miles_per_Gallon'].mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Replace the `Miles_per_Gallon` values by the [z-score](https://en.wikipedia.org/wiki/Standard_score) of that value relative to its group for each `Year`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "checksum": "6e134e499ef4975fa2fd4c8900071ea8",
     "grade": false,
     "grade_id": "cars-group-agg-n",
     "locked": false,
     "schema_version": 1,
     "solution": true
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0      0.058125\n",
       "1     -0.503753\n",
       "2      0.058125\n",
       "3     -0.316460\n",
       "4     -0.129168\n",
       "5     -0.503753\n",
       "6     -0.691046\n",
       "7     -0.691046\n",
       "8     -0.691046\n",
       "9     -0.503753\n",
       "10          NaN\n",
       "11          NaN\n",
       "12          NaN\n",
       "13          NaN\n",
       "14          NaN\n",
       "15    -0.503753\n",
       "16    -0.691046\n",
       "17          NaN\n",
       "18    -0.503753\n",
       "19    -0.691046\n",
       "20     1.181883\n",
       "21     0.807297\n",
       "22     0.058125\n",
       "23     0.620004\n",
       "24     1.743761\n",
       "25     1.556468\n",
       "26     1.369176\n",
       "27     1.181883\n",
       "28     1.369176\n",
       "29     1.556468\n",
       "         ...   \n",
       "376   -0.737237\n",
       "377    0.538575\n",
       "378   -0.008202\n",
       "379   -0.372720\n",
       "380   -0.737237\n",
       "381   -1.284014\n",
       "382   -1.466273\n",
       "383    0.903093\n",
       "384    1.085352\n",
       "385   -0.008202\n",
       "386    1.267611\n",
       "387    0.903093\n",
       "388    0.903093\n",
       "389    0.903093\n",
       "390    0.538575\n",
       "391    1.267611\n",
       "392    0.174057\n",
       "393    1.267611\n",
       "394   -1.101755\n",
       "395    1.267611\n",
       "396   -0.919496\n",
       "397   -1.648532\n",
       "398    0.174057\n",
       "399    0.903093\n",
       "400   -0.737237\n",
       "401   -0.737237\n",
       "402    2.361164\n",
       "403    0.174057\n",
       "404   -0.554978\n",
       "405   -0.008202\n",
       "Name: Miles_per_Gallon, Length: 406, dtype: float64"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def to_zscore(x):\n",
    "    mean = x.mean()\n",
    "    sd = x.std()\n",
    "    return (x - mean) / sd\n",
    "\n",
    "cars[\"Miles_per_Gallon\"] = cars.groupby([\"Year\"])[\"Miles_per_Gallon\"].apply(to_zscore)\n",
    "cars[\"Miles_per_Gallon\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Show that the average z-scores for `Miles_per_Gallon` grouped by year are all zero:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "checksum": "85836c2dd222f7ffa878a7c46b84413c",
     "grade": true,
     "grade_id": "cars-group-agg-o",
     "locked": false,
     "points": 2,
     "schema_version": 1,
     "solution": true
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Year\n",
       "1970-01-01   -7.656711e-17\n",
       "1971-01-01    4.758099e-17\n",
       "1972-01-01   -2.299748e-16\n",
       "1973-01-01   -3.053113e-16\n",
       "1974-01-01    2.138207e-16\n",
       "1975-01-01    2.960595e-16\n",
       "1976-01-01   -5.224579e-17\n",
       "1977-01-01    1.189525e-17\n",
       "1978-01-01   -3.484867e-16\n",
       "1979-01-01    1.761043e-15\n",
       "1980-01-01   -3.072255e-16\n",
       "1982-01-01   -3.114696e-16\n",
       "Name: Miles_per_Gallon, dtype: float64"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# YOUR CODE HERE\n",
    "avg_z = cars.groupby([\"Year\"])[\"Miles_per_Gallon\"].mean()\n",
    "avg_z"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Show that the standard deviation of the z-scores for `Miles_per_Gallon` grouped by year are all \n",
    "1.0:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "checksum": "e262df8af9ef25d7c17094151f8e3819",
     "grade": true,
     "grade_id": "cars-group-agg-p",
     "locked": false,
     "points": 2,
     "schema_version": 1,
     "solution": true
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Year\n",
       "1970-01-01    1.0\n",
       "1971-01-01    1.0\n",
       "1972-01-01    1.0\n",
       "1973-01-01    1.0\n",
       "1974-01-01    1.0\n",
       "1975-01-01    1.0\n",
       "1976-01-01    1.0\n",
       "1977-01-01    1.0\n",
       "1978-01-01    1.0\n",
       "1979-01-01    1.0\n",
       "1980-01-01    1.0\n",
       "1982-01-01    1.0\n",
       "Name: Miles_per_Gallon, dtype: float64"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# YOUR CODE HERE\n",
    "std_z = cars.groupby([\"Year\"])[\"Miles_per_Gallon\"].std()\n",
    "std_z"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Grouped missing value replacement"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Introduce missing values"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's introduce some missing values into the `Cylinders` column:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "checksum": "9723e6bce3d63fdac7aa2b3cad42e04c",
     "grade": false,
     "grade_id": "cars-group-agg-q",
     "locked": true,
     "schema_version": 1,
     "solution": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0      8.0\n",
       "1      8.0\n",
       "2      8.0\n",
       "3      8.0\n",
       "4      8.0\n",
       "5      8.0\n",
       "6      8.0\n",
       "7      8.0\n",
       "8      8.0\n",
       "9      8.0\n",
       "10     4.0\n",
       "11     NaN\n",
       "12     NaN\n",
       "13     8.0\n",
       "14     8.0\n",
       "15     8.0\n",
       "16     8.0\n",
       "17     8.0\n",
       "18     8.0\n",
       "19     NaN\n",
       "20     4.0\n",
       "21     NaN\n",
       "22     6.0\n",
       "23     6.0\n",
       "24     4.0\n",
       "25     4.0\n",
       "26     4.0\n",
       "27     NaN\n",
       "28     4.0\n",
       "29     4.0\n",
       "      ... \n",
       "376    NaN\n",
       "377    NaN\n",
       "378    4.0\n",
       "379    4.0\n",
       "380    4.0\n",
       "381    4.0\n",
       "382    4.0\n",
       "383    4.0\n",
       "384    4.0\n",
       "385    4.0\n",
       "386    NaN\n",
       "387    4.0\n",
       "388    4.0\n",
       "389    NaN\n",
       "390    NaN\n",
       "391    4.0\n",
       "392    4.0\n",
       "393    NaN\n",
       "394    NaN\n",
       "395    6.0\n",
       "396    4.0\n",
       "397    6.0\n",
       "398    4.0\n",
       "399    4.0\n",
       "400    4.0\n",
       "401    NaN\n",
       "402    NaN\n",
       "403    NaN\n",
       "404    NaN\n",
       "405    NaN\n",
       "Name: Cylinders, Length: 406, dtype: float64"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cars['Cylinders'] = np.where(np.random.rand(len(cars)) > 0.8, np.nan, cars['Cylinders'])\n",
    "cars.Cylinders"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here are the average number of Cylinders, grouped by `Year`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "checksum": "0bc08d0c9de418eed80e72560c3841e5",
     "grade": false,
     "grade_id": "cars-group-agg-r",
     "locked": true,
     "schema_version": 1,
     "solution": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Year\n",
       "1970-01-01    6.785714\n",
       "1971-01-01    5.500000\n",
       "1972-01-01    5.760000\n",
       "1973-01-01    6.606061\n",
       "1974-01-01    5.473684\n",
       "1975-01-01    5.545455\n",
       "1976-01-01    5.800000\n",
       "1977-01-01    5.480000\n",
       "1978-01-01    5.321429\n",
       "1979-01-01    5.904762\n",
       "1980-01-01    4.153846\n",
       "1982-01-01    4.372093\n",
       "Name: Cylinders, dtype: float64"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cars.groupby(['Year'])['Cylinders'].mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Replace the missing values in `Cylinders` by the group average (grouped by `Year`):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "checksum": "b810d7fc0e256377e74b2aed020d764a",
     "grade": false,
     "grade_id": "cars-group-agg-s",
     "locked": false,
     "schema_version": 1,
     "solution": true
    }
   },
   "outputs": [],
   "source": [
    "# YOUR CODE HERE\n",
    "def fill_na(x):\n",
    "    average = x.mean()\n",
    "    return x.fillna(average)\n",
    "filled_data = cars.groupby(\"Year\")[\"Cylinders\"].apply(fill_na)\n",
    "cars[\"Cylinders\"] = filled_data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "By replacing missing values with group averages, the grouped averages remain unchanged:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "checksum": "575a8484a9064e7eba056745e5af59af",
     "grade": true,
     "grade_id": "cars-group-agg-t",
     "locked": false,
     "points": 2,
     "schema_version": 1,
     "solution": true
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Year\n",
       "1970-01-01    6.785714\n",
       "1971-01-01    5.500000\n",
       "1972-01-01    5.760000\n",
       "1973-01-01    6.606061\n",
       "1974-01-01    5.473684\n",
       "1975-01-01    5.545455\n",
       "1976-01-01    5.800000\n",
       "1977-01-01    5.480000\n",
       "1978-01-01    5.321429\n",
       "1979-01-01    5.904762\n",
       "1980-01-01    4.153846\n",
       "1982-01-01    4.372093\n",
       "Name: Cylinders, dtype: float64"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# YOUR CODE HERE\n",
    "cars.groupby(['Year'])['Cylinders'].mean()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}