t81_558_class_02_3_pandas_grouping.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a href=\"https://colab.research.google.com/github/jeffheaton/t81_558_deep_learning/blob/master/t81_558_class_02_3_pandas_grouping.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# T81-558: Applications of Deep Neural Networks\n",
    "**Module 2: Python for Machine Learning**\n",
    "* Instructor: [Jeff Heaton](https://sites.wustl.edu/jeffheaton/), McKelvey School of Engineering, [Washington University in St. Louis](https://engineering.wustl.edu/Programs/Pages/default.aspx)\n",
    "* For more information visit the [class website](https://sites.wustl.edu/jeffheaton/t81-558/)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Module 2 Material\n",
    "\n",
    "Main video lecture:\n",
    "\n",
    "* Part 2.1: Introduction to Pandas [[Video]](https://www.youtube.com/watch?v=bN4UuCBdpZc&list=PLjy4p-07OYzulelvJ5KVaT2pDlxivl_BN) [[Notebook]](t81_558_class_02_1_python_pandas.ipynb)\n",
    "* Part 2.2: Categorical Values [[Video]](https://www.youtube.com/watch?v=4a1odDpG0Ho&list=PLjy4p-07OYzulelvJ5KVaT2pDlxivl_BN) [[Notebook]](t81_558_class_02_2_pandas_cat.ipynb)\n",
    "* **Part 2.3: Grouping, Sorting, and Shuffling in Python Pandas** [[Video]](https://www.youtube.com/watch?v=YS4wm5gD8DM&list=PLjy4p-07OYzulelvJ5KVaT2pDlxivl_BN) [[Notebook]](t81_558_class_02_3_pandas_grouping.ipynb)\n",
    "* Part 2.4: Using Apply and Map in Pandas for Keras [[Video]](https://www.youtube.com/watch?v=XNCEZ4WaPBY&list=PLjy4p-07OYzulelvJ5KVaT2pDlxivl_BN) [[Notebook]](t81_558_class_02_4_pandas_functional.ipynb)\n",
    "* Part 2.5: Feature Engineering in Pandas for Deep Learning in Keras [[Video]](https://www.youtube.com/watch?v=BWPTj4_Mi9E&list=PLjy4p-07OYzulelvJ5KVaT2pDlxivl_BN) [[Notebook]](t81_558_class_02_5_pandas_features.ipynb)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Google CoLab Instructions\n",
    "\n",
    "The following code ensures that Google CoLab is running the correct version of TensorFlow."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Note: not using Google CoLab\n"
     ]
    }
   ],
   "source": [
    "try:\n",
    "    %tensorflow_version 2.x\n",
    "    COLAB = True\n",
    "    print(\"Note: using Google CoLab\")\n",
    "except:\n",
    "    print(\"Note: not using Google CoLab\")\n",
    "    COLAB = False"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Part 2.3: Grouping, Sorting, and Shuffling  \n",
    "\n",
    "### Shuffling a Dataset\n",
    "The following code is used to shuffle and reindex a data set.  A random seed can be used to produce a consistent shuffling of the data set."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "scrolled": 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>mpg</th>\n",
       "      <th>cylinders</th>\n",
       "      <th>displacement</th>\n",
       "      <th>horsepower</th>\n",
       "      <th>weight</th>\n",
       "      <th>acceleration</th>\n",
       "      <th>year</th>\n",
       "      <th>origin</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>29.8</td>\n",
       "      <td>4</td>\n",
       "      <td>134.0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>2711</td>\n",
       "      <td>15.5</td>\n",
       "      <td>80</td>\n",
       "      <td>3</td>\n",
       "      <td>toyota corona liftback</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>29.0</td>\n",
       "      <td>4</td>\n",
       "      <td>97.0</td>\n",
       "      <td>78.0</td>\n",
       "      <td>1940</td>\n",
       "      <td>14.5</td>\n",
       "      <td>77</td>\n",
       "      <td>2</td>\n",
       "      <td>volkswagen rabbit custom</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>16.9</td>\n",
       "      <td>8</td>\n",
       "      <td>350.0</td>\n",
       "      <td>155.0</td>\n",
       "      <td>4360</td>\n",
       "      <td>14.9</td>\n",
       "      <td>79</td>\n",
       "      <td>1</td>\n",
       "      <td>buick estate wagon (sw)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>27.9</td>\n",
       "      <td>4</td>\n",
       "      <td>156.0</td>\n",
       "      <td>105.0</td>\n",
       "      <td>2800</td>\n",
       "      <td>14.4</td>\n",
       "      <td>80</td>\n",
       "      <td>1</td>\n",
       "      <td>dodge colt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>20.0</td>\n",
       "      <td>6</td>\n",
       "      <td>225.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>3651</td>\n",
       "      <td>17.7</td>\n",
       "      <td>76</td>\n",
       "      <td>1</td>\n",
       "      <td>dodge aspen se</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>19.0</td>\n",
       "      <td>6</td>\n",
       "      <td>225.0</td>\n",
       "      <td>95.0</td>\n",
       "      <td>3264</td>\n",
       "      <td>16.0</td>\n",
       "      <td>75</td>\n",
       "      <td>1</td>\n",
       "      <td>plymouth valiant custom</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>12.0</td>\n",
       "      <td>8</td>\n",
       "      <td>383.0</td>\n",
       "      <td>180.0</td>\n",
       "      <td>4955</td>\n",
       "      <td>11.5</td>\n",
       "      <td>71</td>\n",
       "      <td>1</td>\n",
       "      <td>dodge monaco (sw)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>24.0</td>\n",
       "      <td>4</td>\n",
       "      <td>107.0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>2430</td>\n",
       "      <td>14.5</td>\n",
       "      <td>70</td>\n",
       "      <td>2</td>\n",
       "      <td>audi 100 ls</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>31.0</td>\n",
       "      <td>4</td>\n",
       "      <td>79.0</td>\n",
       "      <td>67.0</td>\n",
       "      <td>1950</td>\n",
       "      <td>19.0</td>\n",
       "      <td>74</td>\n",
       "      <td>3</td>\n",
       "      <td>datsun b210</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>39.4</td>\n",
       "      <td>4</td>\n",
       "      <td>85.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>2070</td>\n",
       "      <td>18.6</td>\n",
       "      <td>78</td>\n",
       "      <td>3</td>\n",
       "      <td>datsun b210 gx</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    mpg  cylinders  displacement  horsepower  weight  acceleration  year  \\\n",
       "0  29.8          4         134.0        90.0    2711          15.5    80   \n",
       "1  29.0          4          97.0        78.0    1940          14.5    77   \n",
       "2  16.9          8         350.0       155.0    4360          14.9    79   \n",
       "3  27.9          4         156.0       105.0    2800          14.4    80   \n",
       "4  20.0          6         225.0       100.0    3651          17.7    76   \n",
       "5  19.0          6         225.0        95.0    3264          16.0    75   \n",
       "6  12.0          8         383.0       180.0    4955          11.5    71   \n",
       "7  24.0          4         107.0        90.0    2430          14.5    70   \n",
       "8  31.0          4          79.0        67.0    1950          19.0    74   \n",
       "9  39.4          4          85.0        70.0    2070          18.6    78   \n",
       "\n",
       "   origin                      name  \n",
       "0       3    toyota corona liftback  \n",
       "1       2  volkswagen rabbit custom  \n",
       "2       1   buick estate wagon (sw)  \n",
       "3       1                dodge colt  \n",
       "4       1            dodge aspen se  \n",
       "5       1   plymouth valiant custom  \n",
       "6       1         dodge monaco (sw)  \n",
       "7       2               audi 100 ls  \n",
       "8       3               datsun b210  \n",
       "9       3            datsun b210 gx  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "df = pd.read_csv(\n",
    "    \"https://data.heatonresearch.com/data/t81-558/auto-mpg.csv\", \n",
    "    na_values=['NA', '?'])\n",
    "\n",
    "#np.random.seed(42) # Uncomment this line to get the same shuffle each time\n",
    "df = df.reindex(np.random.permutation(df.index))\n",
    "df.reset_index(inplace=True, drop=True)\n",
    "display(df[0:10])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Sorting a Data Set\n",
    "\n",
    "Data sets can also be sorted.  This code sorts the MPG dataset by name and displays the first car."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The first car is: amc ambassador brougham\n"
     ]
    },
    {
     "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>mpg</th>\n",
       "      <th>cylinders</th>\n",
       "      <th>displacement</th>\n",
       "      <th>horsepower</th>\n",
       "      <th>weight</th>\n",
       "      <th>acceleration</th>\n",
       "      <th>year</th>\n",
       "      <th>origin</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>13.0</td>\n",
       "      <td>8</td>\n",
       "      <td>360.0</td>\n",
       "      <td>175.0</td>\n",
       "      <td>3821</td>\n",
       "      <td>11.0</td>\n",
       "      <td>73</td>\n",
       "      <td>1</td>\n",
       "      <td>amc ambassador brougham</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>15.0</td>\n",
       "      <td>8</td>\n",
       "      <td>390.0</td>\n",
       "      <td>190.0</td>\n",
       "      <td>3850</td>\n",
       "      <td>8.5</td>\n",
       "      <td>70</td>\n",
       "      <td>1</td>\n",
       "      <td>amc ambassador dpl</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66</th>\n",
       "      <td>17.0</td>\n",
       "      <td>8</td>\n",
       "      <td>304.0</td>\n",
       "      <td>150.0</td>\n",
       "      <td>3672</td>\n",
       "      <td>11.5</td>\n",
       "      <td>72</td>\n",
       "      <td>1</td>\n",
       "      <td>amc ambassador sst</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>315</th>\n",
       "      <td>24.3</td>\n",
       "      <td>4</td>\n",
       "      <td>151.0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>3003</td>\n",
       "      <td>20.1</td>\n",
       "      <td>80</td>\n",
       "      <td>1</td>\n",
       "      <td>amc concord</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>257</th>\n",
       "      <td>19.4</td>\n",
       "      <td>6</td>\n",
       "      <td>232.0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>3210</td>\n",
       "      <td>17.2</td>\n",
       "      <td>78</td>\n",
       "      <td>1</td>\n",
       "      <td>amc concord</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      mpg  cylinders  displacement  horsepower  weight  acceleration  year  \\\n",
       "96   13.0          8         360.0       175.0    3821          11.0    73   \n",
       "9    15.0          8         390.0       190.0    3850           8.5    70   \n",
       "66   17.0          8         304.0       150.0    3672          11.5    72   \n",
       "315  24.3          4         151.0        90.0    3003          20.1    80   \n",
       "257  19.4          6         232.0        90.0    3210          17.2    78   \n",
       "\n",
       "     origin                     name  \n",
       "96        1  amc ambassador brougham  \n",
       "9         1       amc ambassador dpl  \n",
       "66        1       amc ambassador sst  \n",
       "315       1              amc concord  \n",
       "257       1              amc concord  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "\n",
    "df = pd.read_csv(\n",
    "    \"https://data.heatonresearch.com/data/t81-558/auto-mpg.csv\", \n",
    "    na_values=['NA', '?'])\n",
    "\n",
    "df = df.sort_values(by='name', ascending=True)\n",
    "print(f\"The first car is: {df['name'].iloc[0]}\")\n",
    "display(df[0:5])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Grouping a Data Set\n",
    "\n",
    "Grouping is a common operation on data sets.  In SQL, this operation is referred to as \"GROUP BY\".  Grouping is used to summarize data.  Because of this summarization the row could will either stay the same or more likely shrink after a grouping is applied.\n",
    "\n",
    "The Auto MPG dataset is used to demonstrate grouping."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>mpg</th>\n",
       "      <th>cylinders</th>\n",
       "      <th>displacement</th>\n",
       "      <th>horsepower</th>\n",
       "      <th>weight</th>\n",
       "      <th>acceleration</th>\n",
       "      <th>year</th>\n",
       "      <th>origin</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>18.0</td>\n",
       "      <td>8</td>\n",
       "      <td>307.0</td>\n",
       "      <td>130.0</td>\n",
       "      <td>3504</td>\n",
       "      <td>12.0</td>\n",
       "      <td>70</td>\n",
       "      <td>1</td>\n",
       "      <td>chevrolet chevelle malibu</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>15.0</td>\n",
       "      <td>8</td>\n",
       "      <td>350.0</td>\n",
       "      <td>165.0</td>\n",
       "      <td>3693</td>\n",
       "      <td>11.5</td>\n",
       "      <td>70</td>\n",
       "      <td>1</td>\n",
       "      <td>buick skylark 320</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>18.0</td>\n",
       "      <td>8</td>\n",
       "      <td>318.0</td>\n",
       "      <td>150.0</td>\n",
       "      <td>3436</td>\n",
       "      <td>11.0</td>\n",
       "      <td>70</td>\n",
       "      <td>1</td>\n",
       "      <td>plymouth satellite</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>16.0</td>\n",
       "      <td>8</td>\n",
       "      <td>304.0</td>\n",
       "      <td>150.0</td>\n",
       "      <td>3433</td>\n",
       "      <td>12.0</td>\n",
       "      <td>70</td>\n",
       "      <td>1</td>\n",
       "      <td>amc rebel sst</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>17.0</td>\n",
       "      <td>8</td>\n",
       "      <td>302.0</td>\n",
       "      <td>140.0</td>\n",
       "      <td>3449</td>\n",
       "      <td>10.5</td>\n",
       "      <td>70</td>\n",
       "      <td>1</td>\n",
       "      <td>ford torino</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    mpg  cylinders  displacement  horsepower  weight  acceleration  year  \\\n",
       "0  18.0          8         307.0       130.0    3504          12.0    70   \n",
       "1  15.0          8         350.0       165.0    3693          11.5    70   \n",
       "2  18.0          8         318.0       150.0    3436          11.0    70   \n",
       "3  16.0          8         304.0       150.0    3433          12.0    70   \n",
       "4  17.0          8         302.0       140.0    3449          10.5    70   \n",
       "\n",
       "   origin                       name  \n",
       "0       1  chevrolet chevelle malibu  \n",
       "1       1          buick skylark 320  \n",
       "2       1         plymouth satellite  \n",
       "3       1              amc rebel sst  \n",
       "4       1                ford torino  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "\n",
    "df = pd.read_csv(\n",
    "    \"https://data.heatonresearch.com/data/t81-558/auto-mpg.csv\", \n",
    "    na_values=['NA', '?'])\n",
    "display(df[0:5])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The above data set can be used with group to perform summaries.  For example, the following code will group cylinders by the average (mean).  This code will provide the grouping.  In addition to mean, other aggregating functions, such as **sum** or **count** can be used. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "cylinders\n",
       "3    20.550000\n",
       "4    29.286765\n",
       "5    27.366667\n",
       "6    19.985714\n",
       "8    14.963107\n",
       "Name: mpg, dtype: float64"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "g = df.groupby('cylinders')['mpg'].mean()\n",
    "g"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It might be useful to have these **mean** values as a dictionary."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{3: 20.55,\n",
       " 4: 29.28676470588236,\n",
       " 5: 27.366666666666664,\n",
       " 6: 19.985714285714284,\n",
       " 8: 14.963106796116508}"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d = g.to_dict()\n",
    "d"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This allows you to quickly access an individual element, such as to lookup the mean for 6 cylinders.  This is used in target encoding, which is presented in this module."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "19.985714285714284"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d[6]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The code below shows how to count the number of rows that match each cylinder count."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{3: 4, 4: 204, 5: 3, 6: 84, 8: 103}"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby('cylinders')['mpg'].count().to_dict()"
   ]
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python 3.6 (tensorflow)",
   "language": "python",
   "name": "rga"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}