pandas_groupby_tutorial.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This notebook is the notebook of the post <a href=\"https://www.marsja.se/python-pandas-groupby-tutorial-examples/\" rel=\"noreferrer noopener canonical\" target=\"_blank\">https://www.marsja.se/python-pandas-groupby-tutorial-examples/ </a>.\n",
    "\n",
    "\n",
    "In this Pandas groupby tutorial we are going to learn how to organize <a href=\"https://pandas.pydata.org/\" rel=\"noreferrer noopener\" target=\"_blank\">Pandas </a>dataframes by groups. \n",
    "\n",
    "Amongst many things, we are going to learn how to:\n",
    "\n",
    "    - group by one and multiple columns,\n",
    "    - calculate some basics summary statistics (e.g., mean, median), \n",
    "    - convert Pandas groupby to dataframe, \n",
    "    - calculate the percentage of observations in each group"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "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>rank</th>\n",
       "      <th>discipline</th>\n",
       "      <th>yrs.since.phd</th>\n",
       "      <th>yrs.service</th>\n",
       "      <th>sex</th>\n",
       "      <th>salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>Prof</td>\n",
       "      <td>B</td>\n",
       "      <td>19</td>\n",
       "      <td>18</td>\n",
       "      <td>Male</td>\n",
       "      <td>139750</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>Prof</td>\n",
       "      <td>B</td>\n",
       "      <td>20</td>\n",
       "      <td>16</td>\n",
       "      <td>Male</td>\n",
       "      <td>173200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>AsstProf</td>\n",
       "      <td>B</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>Male</td>\n",
       "      <td>79750</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>Prof</td>\n",
       "      <td>B</td>\n",
       "      <td>45</td>\n",
       "      <td>39</td>\n",
       "      <td>Male</td>\n",
       "      <td>115000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>Prof</td>\n",
       "      <td>B</td>\n",
       "      <td>40</td>\n",
       "      <td>41</td>\n",
       "      <td>Male</td>\n",
       "      <td>141500</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       rank discipline  yrs.since.phd  yrs.service   sex  salary\n",
       "1      Prof          B             19           18  Male  139750\n",
       "2      Prof          B             20           16  Male  173200\n",
       "3  AsstProf          B              4            3  Male   79750\n",
       "4      Prof          B             45           39  Male  115000\n",
       "5      Prof          B             40           41  Male  141500"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "data_url = 'http://vincentarelbundock.github.io/Rdatasets/csv/carData/Salaries.csv'\n",
    "df = pd.read_csv(data_url, index_col=0)\n",
    "\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Python Pandas Groupby Example"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We start with the simplest example; grouping by one column."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    " We find all available methods on of the dataframe:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "agg        corr      cumsum     get_group  mean     pct_change  sem    transform\n",
      "aggregate  corrwith  describe   head       median   pipe        shift  tshift   \n",
      "all        count     diff       hist       min      plot        size   var      \n",
      "any        cov       expanding  idxmax     ngroup   prod        skew \n",
      "apply      cumcount  ffill      idxmin     nth      quantile    std  \n",
      "backfill   cummax    fillna     last       nunique  rank        sum  \n",
      "bfill      cummin    filter     mad        ohlc     resample    tail \n",
      "boxplot    cumprod   first      max        pad      rolling     take \n",
      "\n"
     ]
    }
   ],
   "source": [
    "import IPython\n",
    "\n",
    "# Grouping by one factor\n",
    "df_rank = df.groupby('rank')\n",
    "\n",
    "# Getting all methods from the groupby object:\n",
    "meth = [method_name for method_name in dir(df_rank)\n",
    "     if callable(getattr(df_rank, method_name)) & ~method_name.startswith('_')]\n",
    "\n",
    "# Printing the result\n",
    "print(IPython.utils.text.columnize(meth))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "###  How to Display Pandas groupby Objects "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here we learn how to print a Pandas groupby object."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'AssocProf': Int64Index([  6,  11,  25,  40,  42,  55,  56,  58,  59,  61,  64,  66,  90,\n",
       "              92,  93,  97, 105, 107, 108, 109, 112, 124, 131, 133, 139, 141,\n",
       "             142, 154, 157, 159, 163, 169, 175, 177, 178, 183, 187, 189, 194,\n",
       "             195, 196, 215, 218, 219, 223, 228, 232, 256, 258, 261, 285, 286,\n",
       "             294, 300, 317, 322, 323, 329, 335, 364, 368, 371, 380, 383],\n",
       "            dtype='int64'),\n",
       " 'AsstProf': Int64Index([  3,  12,  13,  14,  28,  29,  32,  34,  35,  36,  38,  50,  53,\n",
       "              60,  62,  65,  76,  79,  80,  84,  88,  91,  96, 113, 119, 120,\n",
       "             128, 130, 134, 144, 147, 150, 152, 155, 158, 161, 164, 165, 171,\n",
       "             180, 197, 198, 201, 209, 211, 227, 235, 238, 241, 254, 259, 273,\n",
       "             274, 275, 288, 290, 307, 309, 316, 326, 349, 355, 360, 377, 378,\n",
       "             381, 397],\n",
       "            dtype='int64'),\n",
       " 'Prof': Int64Index([  1,   2,   4,   5,   7,   8,   9,  10,  15,  16,\n",
       "             ...\n",
       "             387, 388, 389, 390, 391, 392, 393, 394, 395, 396],\n",
       "            dtype='int64', length=266)}"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_rank.groups"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here we use get_group to select the Assistant Professor group (i.e., “AsstProf”)."
   ]
  },
  {
   "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>discipline</th>\n",
       "      <th>yrs.since.phd</th>\n",
       "      <th>yrs.service</th>\n",
       "      <th>sex</th>\n",
       "      <th>salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>B</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>Male</td>\n",
       "      <td>79750</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>12</td>\n",
       "      <td>B</td>\n",
       "      <td>7</td>\n",
       "      <td>2</td>\n",
       "      <td>Male</td>\n",
       "      <td>79800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>13</td>\n",
       "      <td>B</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Male</td>\n",
       "      <td>77700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>14</td>\n",
       "      <td>B</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>Male</td>\n",
       "      <td>78000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>28</td>\n",
       "      <td>B</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>Male</td>\n",
       "      <td>82379</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   discipline  yrs.since.phd  yrs.service   sex  salary\n",
       "3           B              4            3  Male   79750\n",
       "12          B              7            2  Male   79800\n",
       "13          B              1            1  Male   77700\n",
       "14          B              2            0  Male   78000\n",
       "28          B              5            3  Male   82379"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get group\n",
    "df_rank.get_group('AsstProf').head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We use the head() method"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>rank</th>\n",
       "      <th>discipline</th>\n",
       "      <th>yrs.since.phd</th>\n",
       "      <th>yrs.service</th>\n",
       "      <th>sex</th>\n",
       "      <th>salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>Prof</td>\n",
       "      <td>B</td>\n",
       "      <td>19</td>\n",
       "      <td>18</td>\n",
       "      <td>Male</td>\n",
       "      <td>139750</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>Prof</td>\n",
       "      <td>B</td>\n",
       "      <td>20</td>\n",
       "      <td>16</td>\n",
       "      <td>Male</td>\n",
       "      <td>173200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>AsstProf</td>\n",
       "      <td>B</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>Male</td>\n",
       "      <td>79750</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>Prof</td>\n",
       "      <td>B</td>\n",
       "      <td>45</td>\n",
       "      <td>39</td>\n",
       "      <td>Male</td>\n",
       "      <td>115000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>Prof</td>\n",
       "      <td>B</td>\n",
       "      <td>40</td>\n",
       "      <td>41</td>\n",
       "      <td>Male</td>\n",
       "      <td>141500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>AssocProf</td>\n",
       "      <td>B</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>Male</td>\n",
       "      <td>97000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>Prof</td>\n",
       "      <td>B</td>\n",
       "      <td>30</td>\n",
       "      <td>23</td>\n",
       "      <td>Male</td>\n",
       "      <td>175000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>11</td>\n",
       "      <td>AssocProf</td>\n",
       "      <td>B</td>\n",
       "      <td>12</td>\n",
       "      <td>8</td>\n",
       "      <td>Male</td>\n",
       "      <td>119800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>12</td>\n",
       "      <td>AsstProf</td>\n",
       "      <td>B</td>\n",
       "      <td>7</td>\n",
       "      <td>2</td>\n",
       "      <td>Male</td>\n",
       "      <td>79800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>13</td>\n",
       "      <td>AsstProf</td>\n",
       "      <td>B</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Male</td>\n",
       "      <td>77700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>14</td>\n",
       "      <td>AsstProf</td>\n",
       "      <td>B</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>Male</td>\n",
       "      <td>78000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>25</td>\n",
       "      <td>AssocProf</td>\n",
       "      <td>A</td>\n",
       "      <td>13</td>\n",
       "      <td>8</td>\n",
       "      <td>Female</td>\n",
       "      <td>74830</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>28</td>\n",
       "      <td>AsstProf</td>\n",
       "      <td>B</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>Male</td>\n",
       "      <td>82379</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>40</td>\n",
       "      <td>AssocProf</td>\n",
       "      <td>B</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>Male</td>\n",
       "      <td>100938</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>42</td>\n",
       "      <td>AssocProf</td>\n",
       "      <td>B</td>\n",
       "      <td>23</td>\n",
       "      <td>23</td>\n",
       "      <td>Male</td>\n",
       "      <td>93418</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         rank discipline  yrs.since.phd  yrs.service     sex  salary\n",
       "1        Prof          B             19           18    Male  139750\n",
       "2        Prof          B             20           16    Male  173200\n",
       "3    AsstProf          B              4            3    Male   79750\n",
       "4        Prof          B             45           39    Male  115000\n",
       "5        Prof          B             40           41    Male  141500\n",
       "6   AssocProf          B              6            6    Male   97000\n",
       "7        Prof          B             30           23    Male  175000\n",
       "11  AssocProf          B             12            8    Male  119800\n",
       "12   AsstProf          B              7            2    Male   79800\n",
       "13   AsstProf          B              1            1    Male   77700\n",
       "14   AsstProf          B              2            0    Male   78000\n",
       "25  AssocProf          A             13            8  Female   74830\n",
       "28   AsstProf          B              5            3    Male   82379\n",
       "40  AssocProf          B              9            9    Male  100938\n",
       "42  AssocProf          B             23           23    Male   93418"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_rank.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Pandas Groupby Count"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here we use .size() to count the number of rows in each group:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "rank\n",
       "AssocProf     64\n",
       "AsstProf      67\n",
       "Prof         266\n",
       "dtype: int64"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_rank.size()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "An alternative way is to use the count method:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>discipline</th>\n",
       "      <th>yrs.since.phd</th>\n",
       "      <th>yrs.service</th>\n",
       "      <th>sex</th>\n",
       "      <th>salary</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rank</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",
       "      <td>AssocProf</td>\n",
       "      <td>64</td>\n",
       "      <td>64</td>\n",
       "      <td>64</td>\n",
       "      <td>64</td>\n",
       "      <td>64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>AsstProf</td>\n",
       "      <td>67</td>\n",
       "      <td>67</td>\n",
       "      <td>67</td>\n",
       "      <td>67</td>\n",
       "      <td>67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Prof</td>\n",
       "      <td>266</td>\n",
       "      <td>266</td>\n",
       "      <td>266</td>\n",
       "      <td>266</td>\n",
       "      <td>266</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           discipline  yrs.since.phd  yrs.service  sex  salary\n",
       "rank                                                          \n",
       "AssocProf          64             64           64   64      64\n",
       "AsstProf           67             67           67   67      67\n",
       "Prof              266            266          266  266     266"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_rank.count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Count the number of unique in a grouped dataframe:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>rank</th>\n",
       "      <th>discipline</th>\n",
       "      <th>yrs.since.phd</th>\n",
       "      <th>yrs.service</th>\n",
       "      <th>sex</th>\n",
       "      <th>salary</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rank</th>\n",
       "      <th></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",
       "      <td>AssocProf</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>24</td>\n",
       "      <td>21</td>\n",
       "      <td>2</td>\n",
       "      <td>63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>AsstProf</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>11</td>\n",
       "      <td>7</td>\n",
       "      <td>2</td>\n",
       "      <td>53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Prof</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>42</td>\n",
       "      <td>50</td>\n",
       "      <td>2</td>\n",
       "      <td>261</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           rank  discipline  yrs.since.phd  yrs.service  sex  salary\n",
       "rank                                                                \n",
       "AssocProf     1           2             24           21    2      63\n",
       "AsstProf      1           2             11            7    2      53\n",
       "Prof          1           2             42           50    2     261"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_rank.nunique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As can be seen in the the last column (salary) there are 63 Associate Professors, 53 Assistant Proffessors, and 261 Professors in the dataset."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create some missing values to work with:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>index</th>\n",
       "      <th>N Missing Values</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>rank</td>\n",
       "      <td>44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>discipline</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>yrs.since.phd</td>\n",
       "      <td>41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>yrs.service</td>\n",
       "      <td>34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>sex</td>\n",
       "      <td>36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>salary</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           index  N Missing Values\n",
       "0           rank                44\n",
       "1     discipline                35\n",
       "2  yrs.since.phd                41\n",
       "3    yrs.service                34\n",
       "4            sex                36\n",
       "5         salary                50"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import numpy as np\n",
    "\n",
    "df_null = df.mask(np.random.random(df.shape) < .1)\n",
    "df_null.isnull().sum().reset_index(name='N Missing Values')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**nunique** will not count missing values:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>rank</th>\n",
       "      <th>discipline</th>\n",
       "      <th>yrs.since.phd</th>\n",
       "      <th>yrs.service</th>\n",
       "      <th>sex</th>\n",
       "      <th>salary</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rank</th>\n",
       "      <th></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",
       "      <td>AssocProf</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>20</td>\n",
       "      <td>19</td>\n",
       "      <td>2</td>\n",
       "      <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>AsstProf</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>10</td>\n",
       "      <td>7</td>\n",
       "      <td>2</td>\n",
       "      <td>44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Prof</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>41</td>\n",
       "      <td>49</td>\n",
       "      <td>2</td>\n",
       "      <td>205</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           rank  discipline  yrs.since.phd  yrs.service  sex  salary\n",
       "rank                                                                \n",
       "AssocProf     1           2             20           19    2      48\n",
       "AsstProf      1           2             10            7    2      44\n",
       "Prof          1           2             41           49    2     205"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_null.groupby('rank').nunique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Pandas Groupby Mean"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Calculate the mean salary by group:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>rank</th>\n",
       "      <th>salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>AssocProf</td>\n",
       "      <td>93876.437500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>AsstProf</td>\n",
       "      <td>80775.985075</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>Prof</td>\n",
       "      <td>126772.109023</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        rank         salary\n",
       "0  AssocProf   93876.437500\n",
       "1   AsstProf   80775.985075\n",
       "2       Prof  126772.109023"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_rank['salary'].mean().reset_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Rename columns:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>Rank</th>\n",
       "      <th>Mean Salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>AssocProf</td>\n",
       "      <td>93876.437500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>AsstProf</td>\n",
       "      <td>80775.985075</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>Prof</td>\n",
       "      <td>126772.109023</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Rank    Mean Salary\n",
       "0  AssocProf   93876.437500\n",
       "1   AsstProf   80775.985075\n",
       "2       Prof  126772.109023"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_rank['salary'].mean().reset_index().rename(\n",
    "    columns={'rank':'Rank','salary' : 'Mean Salary'})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### <strong>Median Score of a Group Using the groupby Method in Pandas</strong>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "More descriptive statistics:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>Rank</th>\n",
       "      <th>MedianSalary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>AssocProf</td>\n",
       "      <td>95626.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>AsstProf</td>\n",
       "      <td>79800.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>Prof</td>\n",
       "      <td>123321.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Rank  MedianSalary\n",
       "0  AssocProf       95626.5\n",
       "1   AsstProf       79800.0\n",
       "2       Prof      123321.5"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby('rank')['salary'].median().reset_index().rename(\n",
    "    columns={'rank':'Rank','salary' : 'MedianSalary'})\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Aggregate Data by Group using Pandas Groupby"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Getting many different descriptive statistics using the agg method:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>rank</th>\n",
       "      <th>mean</th>\n",
       "      <th>median</th>\n",
       "      <th>std</th>\n",
       "      <th>min</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>AssocProf</td>\n",
       "      <td>93876.437500</td>\n",
       "      <td>95626.5</td>\n",
       "      <td>13831.699844</td>\n",
       "      <td>62884</td>\n",
       "      <td>126431</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>AsstProf</td>\n",
       "      <td>80775.985075</td>\n",
       "      <td>79800.0</td>\n",
       "      <td>8174.112637</td>\n",
       "      <td>63100</td>\n",
       "      <td>97032</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>Prof</td>\n",
       "      <td>126772.109023</td>\n",
       "      <td>123321.5</td>\n",
       "      <td>27718.674999</td>\n",
       "      <td>57800</td>\n",
       "      <td>231545</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        rank           mean    median           std    min     max\n",
       "0  AssocProf   93876.437500   95626.5  13831.699844  62884  126431\n",
       "1   AsstProf   80775.985075   79800.0   8174.112637  63100   97032\n",
       "2       Prof  126772.109023  123321.5  27718.674999  57800  231545"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_rank['salary'].agg(['mean', 'median', \n",
    "                                  'std', 'min', 'max']).reset_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Using a custom function as input to agg:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "def salary_range(df):\n",
    "    mini = df.min()\n",
    "    maxi = df.max()\n",
    "    rang = '%s - %s' % (mini, maxi)\n",
    "    \n",
    "    return rang\n",
    "\n",
    "df_descriptive = df_rank['salary'].agg(['mean', 'median', 'std', salary_range]).reset_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Again, renaming the columns:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>Rank</th>\n",
       "      <th>Mean</th>\n",
       "      <th>Median</th>\n",
       "      <th>Standard Deviation</th>\n",
       "      <th>Range</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>AssocProf</td>\n",
       "      <td>93876.437500</td>\n",
       "      <td>95626.5</td>\n",
       "      <td>13831.699844</td>\n",
       "      <td>62884 - 126431</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>AsstProf</td>\n",
       "      <td>80775.985075</td>\n",
       "      <td>79800.0</td>\n",
       "      <td>8174.112637</td>\n",
       "      <td>63100 - 97032</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>Prof</td>\n",
       "      <td>126772.109023</td>\n",
       "      <td>123321.5</td>\n",
       "      <td>27718.674999</td>\n",
       "      <td>57800 - 231545</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Rank           Mean    Median  Standard Deviation           Range\n",
       "0  AssocProf   93876.437500   95626.5        13831.699844  62884 - 126431\n",
       "1   AsstProf   80775.985075   79800.0         8174.112637   63100 - 97032\n",
       "2       Prof  126772.109023  123321.5        27718.674999  57800 - 231545"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Renaming Pandas Dataframe Columns\n",
    "df_descriptive.rename(columns={'rank':'Rank', 'mean':'Mean', 'median':'Median', \n",
    "                               'std':'Standard Deviation', 'salary_range':'Range'})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Using methods from other Python packages:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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>rank</th>\n",
       "      <th>mean</th>\n",
       "      <th>median</th>\n",
       "      <th>hmean</th>\n",
       "      <th>gmean</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>AssocProf</td>\n",
       "      <td>93876.437500</td>\n",
       "      <td>95626.5</td>\n",
       "      <td>91784.174692</td>\n",
       "      <td>92844.275276</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>AsstProf</td>\n",
       "      <td>80775.985075</td>\n",
       "      <td>79800.0</td>\n",
       "      <td>79958.539934</td>\n",
       "      <td>80367.469951</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>Prof</td>\n",
       "      <td>126772.109023</td>\n",
       "      <td>123321.5</td>\n",
       "      <td>120947.872093</td>\n",
       "      <td>123836.108080</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        rank           mean    median          hmean          gmean\n",
       "0  AssocProf   93876.437500   95626.5   91784.174692   92844.275276\n",
       "1   AsstProf   80775.985075   79800.0   79958.539934   80367.469951\n",
       "2       Prof  126772.109023  123321.5  120947.872093  123836.108080"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from scipy.stats.mstats import gmean, hmean\n",
    "\n",
    "df_descriptive = df_rank['salary'].agg(['mean', 'median', hmean, gmean]).reset_index()\n",
    "df_descriptive"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "More about doing descriptive statistics using Python:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- <a href=\"https://www.marsja.se/pandas-python-descriptive-statistics/\" rel=\"noreferrer noopener\" target=\"_blank\">Descriptive Statistics using Python and Pandas</a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- <a href=\"https://www.marsja.se/how-to-python-descriptives-statistics-numpy/\" rel=\"noreferrer noopener\" target=\"_blank\">How to do Descriptive Statistics in Python using Numpy</a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Pandas Groupby Multiple Columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here we group by two columns:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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>rank</th>\n",
       "      <th>discipline</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>AssocProf</td>\n",
       "      <td>A</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>AssocProf</td>\n",
       "      <td>B</td>\n",
       "      <td>38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>AsstProf</td>\n",
       "      <td>A</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>AsstProf</td>\n",
       "      <td>B</td>\n",
       "      <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>Prof</td>\n",
       "      <td>A</td>\n",
       "      <td>131</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>Prof</td>\n",
       "      <td>B</td>\n",
       "      <td>135</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        rank discipline  count\n",
       "0  AssocProf          A     26\n",
       "1  AssocProf          B     38\n",
       "2   AsstProf          A     24\n",
       "3   AsstProf          B     43\n",
       "4       Prof          A    131\n",
       "5       Prof          B    135"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_grp = df.groupby(['rank', 'discipline'])\n",
    "df_grp.size().reset_index(name='count')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Get the two groups:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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>rank</th>\n",
       "      <th>discipline</th>\n",
       "      <th>yrs.since.phd</th>\n",
       "      <th>yrs.service</th>\n",
       "      <th>sex</th>\n",
       "      <th>salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>25</td>\n",
       "      <td>AssocProf</td>\n",
       "      <td>A</td>\n",
       "      <td>13</td>\n",
       "      <td>8</td>\n",
       "      <td>Female</td>\n",
       "      <td>74830</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>105</td>\n",
       "      <td>AssocProf</td>\n",
       "      <td>A</td>\n",
       "      <td>18</td>\n",
       "      <td>10</td>\n",
       "      <td>Male</td>\n",
       "      <td>83850</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>107</td>\n",
       "      <td>AssocProf</td>\n",
       "      <td>A</td>\n",
       "      <td>11</td>\n",
       "      <td>8</td>\n",
       "      <td>Male</td>\n",
       "      <td>82099</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>108</td>\n",
       "      <td>AssocProf</td>\n",
       "      <td>A</td>\n",
       "      <td>10</td>\n",
       "      <td>8</td>\n",
       "      <td>Male</td>\n",
       "      <td>82600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>109</td>\n",
       "      <td>AssocProf</td>\n",
       "      <td>A</td>\n",
       "      <td>15</td>\n",
       "      <td>8</td>\n",
       "      <td>Male</td>\n",
       "      <td>81500</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          rank discipline  yrs.since.phd  yrs.service     sex  salary\n",
       "25   AssocProf          A             13            8  Female   74830\n",
       "105  AssocProf          A             18           10    Male   83850\n",
       "107  AssocProf          A             11            8    Male   82099\n",
       "108  AssocProf          A             10            8    Male   82600\n",
       "109  AssocProf          A             15            8    Male   81500"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get two groups\n",
    "df_grp.get_group(('AssocProf', 'A')).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Pandas Groupby Count Multiple Groups"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Count multiple groups:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_3grps = df.groupby(['rank', 'discipline', 'sex'])\n",
    "df_n_per_group = df_3grps.size().reset_index(name='n')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Calculate percentage by groups:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "rank       discipline  sex   \n",
      "AssocProf  A           Female     6.250000\n",
      "                       Male      34.375000\n",
      "           B           Female     9.375000\n",
      "                       Male      50.000000\n",
      "AsstProf   A           Female     8.955224\n",
      "                       Male      26.865672\n",
      "           B           Female     7.462687\n",
      "                       Male      56.716418\n",
      "Prof       A           Female     3.007519\n",
      "                       Male      46.240602\n",
      "           B           Female     3.759398\n",
      "                       Male      46.992481\n",
      "Name: salary, dtype: float64\n",
      "Total percentage in group AssocProf.  100.0\n"
     ]
    }
   ],
   "source": [
    "perc = df.groupby(['rank', 'discipline', 'sex'])['salary'].size()\n",
    "\n",
    "# Give the percentage on the level of Rank:\n",
    "percbyrank = perc.groupby(level=0).apply(lambda x: 100 * x / float(x.sum()))\n",
    "\n",
    "print(percbyrank)\n",
    "print('Total percentage in group AssocProf. ',\n",
    "      percbyrank.reset_index().query('rank == \"AssocProf\"')['salary'].sum())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Calculate the number of men and women:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "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>index</th>\n",
       "      <th>rank</th>\n",
       "      <th>discipline</th>\n",
       "      <th>sex</th>\n",
       "      <th>% of total n</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>AssocProf</td>\n",
       "      <td>A</td>\n",
       "      <td>Female</td>\n",
       "      <td>1.007557</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>AssocProf</td>\n",
       "      <td>A</td>\n",
       "      <td>Male</td>\n",
       "      <td>5.541562</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>AssocProf</td>\n",
       "      <td>B</td>\n",
       "      <td>Female</td>\n",
       "      <td>1.511335</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>AssocProf</td>\n",
       "      <td>B</td>\n",
       "      <td>Male</td>\n",
       "      <td>8.060453</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>AsstProf</td>\n",
       "      <td>A</td>\n",
       "      <td>Female</td>\n",
       "      <td>1.511335</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>AsstProf</td>\n",
       "      <td>A</td>\n",
       "      <td>Male</td>\n",
       "      <td>4.534005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>AsstProf</td>\n",
       "      <td>B</td>\n",
       "      <td>Female</td>\n",
       "      <td>1.259446</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>AsstProf</td>\n",
       "      <td>B</td>\n",
       "      <td>Male</td>\n",
       "      <td>9.571788</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>Prof</td>\n",
       "      <td>A</td>\n",
       "      <td>Female</td>\n",
       "      <td>2.015113</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>Prof</td>\n",
       "      <td>A</td>\n",
       "      <td>Male</td>\n",
       "      <td>30.982368</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>Prof</td>\n",
       "      <td>B</td>\n",
       "      <td>Female</td>\n",
       "      <td>2.518892</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>Prof</td>\n",
       "      <td>B</td>\n",
       "      <td>Male</td>\n",
       "      <td>31.486146</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    index       rank discipline     sex  % of total n\n",
       "0       0  AssocProf          A  Female      1.007557\n",
       "1       1  AssocProf          A    Male      5.541562\n",
       "2       2  AssocProf          B  Female      1.511335\n",
       "3       3  AssocProf          B    Male      8.060453\n",
       "4       4   AsstProf          A  Female      1.511335\n",
       "5       5   AsstProf          A    Male      4.534005\n",
       "6       6   AsstProf          B  Female      1.259446\n",
       "7       7   AsstProf          B    Male      9.571788\n",
       "8       8       Prof          A  Female      2.015113\n",
       "9       9       Prof          A    Male     30.982368\n",
       "10     10       Prof          B  Female      2.518892\n",
       "11     11       Prof          B    Male     31.486146"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "n = perc.reset_index()['salary'].sum()\n",
    "totalperc =  perc.groupby(level=0).apply(lambda x:100*x/n).reset_index(name='% of total n')\n",
    "totalperc.reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_rn = df.groupby(['rank', 'discipline']).mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can see that it is MultiIndex:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "MultiIndex([('AssocProf', 'A'),\n",
       "            ('AssocProf', 'B'),\n",
       "            ( 'AsstProf', 'A'),\n",
       "            ( 'AsstProf', 'B'),\n",
       "            (     'Prof', 'A'),\n",
       "            (     'Prof', 'B')],\n",
       "           names=['rank', 'discipline'])"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_rn.index"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Convert groupby to Pandas dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pandas.core.frame.DataFrame"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_rn = df_rn.add_suffix('_Mean').reset_index()\n",
    "type(df_rn)\n",
    "\n",
    "# Output: pandas.core.frame.DataFrame"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Pandas groupby agg with Multiple Groups"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can, of course, calculate descriptive statistics using the groupby agg method with severeal groups:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "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>rank</th>\n",
       "      <th>discipline</th>\n",
       "      <th>sex</th>\n",
       "      <th>yrs.since.phd_mean</th>\n",
       "      <th>yrs.since.phd_median</th>\n",
       "      <th>yrs.since.phd_std</th>\n",
       "      <th>yrs.service_mean</th>\n",
       "      <th>yrs.service_median</th>\n",
       "      <th>yrs.service_std</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>AssocProf</td>\n",
       "      <td>A</td>\n",
       "      <td>Female</td>\n",
       "      <td>18.500000</td>\n",
       "      <td>19.0</td>\n",
       "      <td>8.185353</td>\n",
       "      <td>15.500000</td>\n",
       "      <td>15.0</td>\n",
       "      <td>8.698659</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>AssocProf</td>\n",
       "      <td>A</td>\n",
       "      <td>Male</td>\n",
       "      <td>17.727273</td>\n",
       "      <td>12.5</td>\n",
       "      <td>12.209215</td>\n",
       "      <td>13.136364</td>\n",
       "      <td>8.0</td>\n",
       "      <td>12.302905</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>AssocProf</td>\n",
       "      <td>B</td>\n",
       "      <td>Female</td>\n",
       "      <td>13.500000</td>\n",
       "      <td>12.5</td>\n",
       "      <td>2.880972</td>\n",
       "      <td>8.833333</td>\n",
       "      <td>9.5</td>\n",
       "      <td>1.940790</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>AssocProf</td>\n",
       "      <td>B</td>\n",
       "      <td>Male</td>\n",
       "      <td>13.875000</td>\n",
       "      <td>10.5</td>\n",
       "      <td>8.507113</td>\n",
       "      <td>11.281250</td>\n",
       "      <td>8.0</td>\n",
       "      <td>9.585723</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>AsstProf</td>\n",
       "      <td>A</td>\n",
       "      <td>Female</td>\n",
       "      <td>4.833333</td>\n",
       "      <td>4.5</td>\n",
       "      <td>2.316607</td>\n",
       "      <td>2.500000</td>\n",
       "      <td>2.5</td>\n",
       "      <td>2.073644</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>AsstProf</td>\n",
       "      <td>A</td>\n",
       "      <td>Male</td>\n",
       "      <td>5.944444</td>\n",
       "      <td>5.5</td>\n",
       "      <td>2.508157</td>\n",
       "      <td>2.388889</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.539247</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>AsstProf</td>\n",
       "      <td>B</td>\n",
       "      <td>Female</td>\n",
       "      <td>6.600000</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.646917</td>\n",
       "      <td>2.600000</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.816590</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>AsstProf</td>\n",
       "      <td>B</td>\n",
       "      <td>Male</td>\n",
       "      <td>4.552632</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.344601</td>\n",
       "      <td>2.315789</td>\n",
       "      <td>2.5</td>\n",
       "      <td>1.397010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>Prof</td>\n",
       "      <td>A</td>\n",
       "      <td>Female</td>\n",
       "      <td>26.500000</td>\n",
       "      <td>28.0</td>\n",
       "      <td>9.023778</td>\n",
       "      <td>16.125000</td>\n",
       "      <td>14.5</td>\n",
       "      <td>11.319231</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>Prof</td>\n",
       "      <td>A</td>\n",
       "      <td>Male</td>\n",
       "      <td>30.747967</td>\n",
       "      <td>31.0</td>\n",
       "      <td>10.152601</td>\n",
       "      <td>24.983740</td>\n",
       "      <td>25.0</td>\n",
       "      <td>12.010913</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>10</td>\n",
       "      <td>Prof</td>\n",
       "      <td>B</td>\n",
       "      <td>Female</td>\n",
       "      <td>21.500000</td>\n",
       "      <td>19.0</td>\n",
       "      <td>5.948856</td>\n",
       "      <td>17.900000</td>\n",
       "      <td>17.5</td>\n",
       "      <td>4.771443</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>11</td>\n",
       "      <td>Prof</td>\n",
       "      <td>B</td>\n",
       "      <td>Male</td>\n",
       "      <td>26.552000</td>\n",
       "      <td>25.0</td>\n",
       "      <td>9.841040</td>\n",
       "      <td>21.504000</td>\n",
       "      <td>20.0</td>\n",
       "      <td>11.182232</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         rank discipline     sex  yrs.since.phd_mean  yrs.since.phd_median  \\\n",
       "0   AssocProf          A  Female           18.500000                  19.0   \n",
       "1   AssocProf          A    Male           17.727273                  12.5   \n",
       "2   AssocProf          B  Female           13.500000                  12.5   \n",
       "3   AssocProf          B    Male           13.875000                  10.5   \n",
       "4    AsstProf          A  Female            4.833333                   4.5   \n",
       "5    AsstProf          A    Male            5.944444                   5.5   \n",
       "6    AsstProf          B  Female            6.600000                   5.0   \n",
       "7    AsstProf          B    Male            4.552632                   4.0   \n",
       "8        Prof          A  Female           26.500000                  28.0   \n",
       "9        Prof          A    Male           30.747967                  31.0   \n",
       "10       Prof          B  Female           21.500000                  19.0   \n",
       "11       Prof          B    Male           26.552000                  25.0   \n",
       "\n",
       "    yrs.since.phd_std  yrs.service_mean  yrs.service_median  yrs.service_std  \n",
       "0            8.185353         15.500000                15.0         8.698659  \n",
       "1           12.209215         13.136364                 8.0        12.302905  \n",
       "2            2.880972          8.833333                 9.5         1.940790  \n",
       "3            8.507113         11.281250                 8.0         9.585723  \n",
       "4            2.316607          2.500000                 2.5         2.073644  \n",
       "5            2.508157          2.388889                 3.0         1.539247  \n",
       "6            3.646917          2.600000                 3.0         1.816590  \n",
       "7            2.344601          2.315789                 2.5         1.397010  \n",
       "8            9.023778         16.125000                14.5        11.319231  \n",
       "9           10.152601         24.983740                25.0        12.010913  \n",
       "10           5.948856         17.900000                17.5         4.771443  \n",
       "11           9.841040         21.504000                20.0        11.182232  "
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_stats = df.groupby(['rank', 'discipline', 'sex']).agg(['mean', 'median', 'std'])\n",
    "df_stats.columns = [\"_\".join(x) for x in df_stats.columns.ravel()]\n",
    "\n",
    "df_stats.iloc[:,0:6].reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}