6-1_pivot_and_group_by_updated.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Lecture 6-1 (updated)\n",
    "\n",
    "## Pandas: Summaries with Pivot Tables and Group by\n",
    "\n",
    "## Week 6 Monday\n",
    "\n",
    "## Miles Chen, PhD\n",
    "\n",
    "Adapted from Python Data Science Handbook by Jake VanderPlas and Python for Data Analysis by Wes McKinney"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'2.2.1'"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.__version__"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Some important data transformation tools"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "## Multi Index, Hierarchical Indexing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "np.random.seed(1)\n",
    "# manual multi-index creation:\n",
    "# provide the index argument with a list of lists\n",
    "data = pd.Series(np.array([4, 5, 6, 8, 10, 12, 12, 15, 18]), \n",
    "                 index = [['a','a','a','b','b','b','c','c','c'],\n",
    "                          [ 4 , 5 , 6 , 4 , 5 , 6 , 4 , 5 , 6 ]])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "a  4     4\n",
       "   5     5\n",
       "   6     6\n",
       "b  4     8\n",
       "   5    10\n",
       "   6    12\n",
       "c  4    12\n",
       "   5    15\n",
       "   6    18\n",
       "dtype: int32"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "MultiIndex([('a', 4),\n",
       "            ('a', 5),\n",
       "            ('a', 6),\n",
       "            ('b', 4),\n",
       "            ('b', 5),\n",
       "            ('b', 6),\n",
       "            ('c', 4),\n",
       "            ('c', 5),\n",
       "            ('c', 6)],\n",
       "           )"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4     8\n",
       "5    10\n",
       "6    12\n",
       "dtype: int32"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# select via the outer index\n",
    "data.loc['b']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "a     5\n",
       "b    10\n",
       "c    15\n",
       "dtype: int32"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# select via the inner index\n",
    "data.loc[:,5] "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pandas.core.series.Series"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(data.loc[:,5])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['a', 'b', 'c'], dtype='object')"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.loc[:,5].index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "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>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>a</th>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>8</td>\n",
       "      <td>10</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>c</th>\n",
       "      <td>12</td>\n",
       "      <td>15</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    4   5   6\n",
       "a   4   5   6\n",
       "b   8  10  12\n",
       "c  12  15  18"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# the unstack function returns a new DataFrame where the values have been unstacked\n",
    "# similar to tidyr's spread()/pivot_wider function in R\n",
    "data.unstack()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['a', 'b', 'c'], dtype='object')"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# after unstacking, the index is no longer a multi index\n",
    "data.unstack().index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(3, 3)"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.unstack().shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "a  4     4\n",
       "   5     5\n",
       "   6     6\n",
       "b  4     8\n",
       "   5    10\n",
       "   6    12\n",
       "c  4    12\n",
       "   5    15\n",
       "   6    18\n",
       "dtype: int32"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# the inverse operation of unstack() is stack()\n",
    "# applying both of these functions will return the same series\n",
    "data.unstack().stack()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4  a     4\n",
       "5  a     5\n",
       "6  a     6\n",
       "4  b     8\n",
       "5  b    10\n",
       "6  b    12\n",
       "4  c    12\n",
       "5  c    15\n",
       "6  c    18\n",
       "dtype: int32"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# you can swap the levels of the multi index using swaplevel\n",
    "data.swaplevel()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4    4\n",
       "5    5\n",
       "6    6\n",
       "dtype: int32"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# the .loc accessors work as expected\n",
    "data.swaplevel().loc[:,'a']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4  a     4\n",
       "   b     8\n",
       "   c    12\n",
       "5  a     5\n",
       "   b    10\n",
       "   c    15\n",
       "6  a     6\n",
       "   b    12\n",
       "   c    18\n",
       "dtype: int32"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# swaplevel will keep the original order\n",
    "# you may want to sort based on the new swapped index levels\n",
    "# you must save the output as data remains unchanged\n",
    "data.swaplevel().sort_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "a  4     4\n",
      "   5     5\n",
      "   6     6\n",
      "b  4     8\n",
      "   5    10\n",
      "   6    12\n",
      "c  4    12\n",
      "   5    15\n",
      "   6    18\n",
      "dtype: int32\n"
     ]
    }
   ],
   "source": [
    "print(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "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>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>10</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6</td>\n",
       "      <td>12</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   a   b   c\n",
       "4  4   8  12\n",
       "5  5  10  15\n",
       "6  6  12  18"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.swaplevel().unstack()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "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>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>a</th>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>8</td>\n",
       "      <td>10</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>c</th>\n",
       "      <td>12</td>\n",
       "      <td>15</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    4   5   6\n",
       "a   4   5   6\n",
       "b   8  10  12\n",
       "c  12  15  18"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# compare to:\n",
    "data.unstack()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "90"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# summing and other aggregate functions can be performed on an index-based level\n",
    "# calling sum() on a series, will sum the whole series\n",
    "data.sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "a    15\n",
       "b    30\n",
       "c    45\n",
       "dtype: int32"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# you can call groupby on level 0 (the first level of the index) and then sum\n",
    "# we get sums for each value in the first level of the index\n",
    "# we will cover groupby in more detail later\n",
    "data.groupby(level = 0).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4    24\n",
       "5    30\n",
       "6    36\n",
       "dtype: int32"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.groupby(level = 1).sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Reshaping and Pivoting Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "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>number</th>\n",
       "      <th>one</th>\n",
       "      <th>two</th>\n",
       "      <th>three</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>letter</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>alpha</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>beta</th>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "number  one  two  three\n",
       "letter                 \n",
       "alpha     1    2      3\n",
       "beta      4    5      6"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = pd.DataFrame(np.arange(1,7).reshape((2, 3)),\n",
    "                    index  = pd.Index(['alpha', 'beta'], name='letter'),\n",
    "                    columns= pd.Index(['one', 'two', 'three'], name = 'number'))\n",
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "letter  number\n",
       "alpha   one       1\n",
       "        two       2\n",
       "        three     3\n",
       "beta    one       4\n",
       "        two       5\n",
       "        three     6\n",
       "dtype: int32"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.stack()  # creates a multi-index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "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>number</th>\n",
       "      <th>one</th>\n",
       "      <th>two</th>\n",
       "      <th>three</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>letter</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>alpha</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>beta</th>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "number  one  two  three\n",
       "letter                 \n",
       "alpha     1    2      3\n",
       "beta      4    5      6"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.stack().unstack()  # unstack undoes the creation of the stacks"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "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>letter</th>\n",
       "      <th>alpha</th>\n",
       "      <th>beta</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>number</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>one</th>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>two</th>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>three</th>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "letter  alpha  beta\n",
       "number             \n",
       "one         1     4\n",
       "two         2     5\n",
       "three       3     6"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.stack().unstack(0) # you can specify how the unstacking should be done\n",
    "# here we specify that we should unstack the first level of the multi-index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "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>letter</th>\n",
       "      <th>alpha</th>\n",
       "      <th>beta</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>number</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>one</th>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>two</th>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>three</th>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "letter  alpha  beta\n",
       "number             \n",
       "one         1     4\n",
       "two         2     5\n",
       "three       3     6"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.stack().unstack('letter')\n",
    "# you can specify the unstacking by the index level name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "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>number</th>\n",
       "      <th>one</th>\n",
       "      <th>two</th>\n",
       "      <th>three</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>letter</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>alpha</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>beta</th>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "number  one  two  three\n",
       "letter                 \n",
       "alpha     1    2      3\n",
       "beta      4    5      6"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.stack().unstack('number')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### Unstacking can introduce missing values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "one  a    0\n",
       "     b    1\n",
       "     c    2\n",
       "     d    3\n",
       "two  c    4\n",
       "     d    5\n",
       "     e    6\n",
       "dtype: int64"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s1 = pd.Series([0, 1, 2, 3], index=['a', 'b', 'c', 'd'])\n",
    "s2 = pd.Series([4, 5, 6], index=['c', 'd', 'e'])\n",
    "data2 = pd.concat([s1, s2], keys=['one', 'two'])  \n",
    "# using the argument keys when concat series will produce a multi-index\n",
    "data2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "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>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "      <th>e</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>one</th>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>two</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       a    b    c    d    e\n",
       "one  0.0  1.0  2.0  3.0  NaN\n",
       "two  NaN  NaN  4.0  5.0  6.0"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data2.unstack()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "one  a    0.0\n",
       "     b    1.0\n",
       "     c    2.0\n",
       "     d    3.0\n",
       "two  c    4.0\n",
       "     d    5.0\n",
       "     e    6.0\n",
       "dtype: float64"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data2.unstack().stack() # stack() will filter out missing values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\miles\\AppData\\Local\\Temp\\ipykernel_23504\\1258890622.py:1: FutureWarning: The previous implementation of stack is deprecated and will be removed in a future version of pandas. See the What's New notes for pandas 2.1.0 for details. Specify future_stack=True to adopt the new implementation and silence this warning.\n",
      "  data2.unstack().stack(dropna = False) # you can force stack to keep the NaNs\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "one  a    0.0\n",
       "     b    1.0\n",
       "     c    2.0\n",
       "     d    3.0\n",
       "     e    NaN\n",
       "two  a    NaN\n",
       "     b    NaN\n",
       "     c    4.0\n",
       "     d    5.0\n",
       "     e    6.0\n",
       "dtype: float64"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data2.unstack().stack(dropna = False) # you can force stack to keep the NaNs"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Small example data wrangling"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "data = pd.read_csv('macrodata.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "https://www.statsmodels.org/dev/datasets/generated/macrodata.html"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 203 entries, 0 to 202\n",
      "Data columns (total 14 columns):\n",
      " #   Column    Non-Null Count  Dtype  \n",
      "---  ------    --------------  -----  \n",
      " 0   year      203 non-null    float64\n",
      " 1   quarter   203 non-null    float64\n",
      " 2   realgdp   203 non-null    float64\n",
      " 3   realcons  203 non-null    float64\n",
      " 4   realinv   203 non-null    float64\n",
      " 5   realgovt  203 non-null    float64\n",
      " 6   realdpi   203 non-null    float64\n",
      " 7   cpi       203 non-null    float64\n",
      " 8   m1        203 non-null    float64\n",
      " 9   tbilrate  203 non-null    float64\n",
      " 10  unemp     203 non-null    float64\n",
      " 11  pop       203 non-null    float64\n",
      " 12  infl      203 non-null    float64\n",
      " 13  realint   203 non-null    float64\n",
      "dtypes: float64(14)\n",
      "memory usage: 22.3 KB\n"
     ]
    }
   ],
   "source": [
    "data.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "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>year</th>\n",
       "      <th>quarter</th>\n",
       "      <th>realgdp</th>\n",
       "      <th>realcons</th>\n",
       "      <th>realinv</th>\n",
       "      <th>realgovt</th>\n",
       "      <th>realdpi</th>\n",
       "      <th>cpi</th>\n",
       "      <th>m1</th>\n",
       "      <th>tbilrate</th>\n",
       "      <th>unemp</th>\n",
       "      <th>pop</th>\n",
       "      <th>infl</th>\n",
       "      <th>realint</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1959.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2710.349</td>\n",
       "      <td>1707.4</td>\n",
       "      <td>286.898</td>\n",
       "      <td>470.045</td>\n",
       "      <td>1886.9</td>\n",
       "      <td>28.98</td>\n",
       "      <td>139.7</td>\n",
       "      <td>2.82</td>\n",
       "      <td>5.8</td>\n",
       "      <td>177.146</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1959.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2778.801</td>\n",
       "      <td>1733.7</td>\n",
       "      <td>310.859</td>\n",
       "      <td>481.301</td>\n",
       "      <td>1919.7</td>\n",
       "      <td>29.15</td>\n",
       "      <td>141.7</td>\n",
       "      <td>3.08</td>\n",
       "      <td>5.1</td>\n",
       "      <td>177.830</td>\n",
       "      <td>2.34</td>\n",
       "      <td>0.74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1959.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2775.488</td>\n",
       "      <td>1751.8</td>\n",
       "      <td>289.226</td>\n",
       "      <td>491.260</td>\n",
       "      <td>1916.4</td>\n",
       "      <td>29.35</td>\n",
       "      <td>140.5</td>\n",
       "      <td>3.82</td>\n",
       "      <td>5.3</td>\n",
       "      <td>178.657</td>\n",
       "      <td>2.74</td>\n",
       "      <td>1.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1959.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2785.204</td>\n",
       "      <td>1753.7</td>\n",
       "      <td>299.356</td>\n",
       "      <td>484.052</td>\n",
       "      <td>1931.3</td>\n",
       "      <td>29.37</td>\n",
       "      <td>140.0</td>\n",
       "      <td>4.33</td>\n",
       "      <td>5.6</td>\n",
       "      <td>179.386</td>\n",
       "      <td>0.27</td>\n",
       "      <td>4.06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1960.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2847.699</td>\n",
       "      <td>1770.5</td>\n",
       "      <td>331.722</td>\n",
       "      <td>462.199</td>\n",
       "      <td>1955.5</td>\n",
       "      <td>29.54</td>\n",
       "      <td>139.6</td>\n",
       "      <td>3.50</td>\n",
       "      <td>5.2</td>\n",
       "      <td>180.007</td>\n",
       "      <td>2.31</td>\n",
       "      <td>1.19</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     year  quarter   realgdp  realcons  realinv  realgovt  realdpi    cpi  \\\n",
       "0  1959.0      1.0  2710.349    1707.4  286.898   470.045   1886.9  28.98   \n",
       "1  1959.0      2.0  2778.801    1733.7  310.859   481.301   1919.7  29.15   \n",
       "2  1959.0      3.0  2775.488    1751.8  289.226   491.260   1916.4  29.35   \n",
       "3  1959.0      4.0  2785.204    1753.7  299.356   484.052   1931.3  29.37   \n",
       "4  1960.0      1.0  2847.699    1770.5  331.722   462.199   1955.5  29.54   \n",
       "\n",
       "      m1  tbilrate  unemp      pop  infl  realint  \n",
       "0  139.7      2.82    5.8  177.146  0.00     0.00  \n",
       "1  141.7      3.08    5.1  177.830  2.34     0.74  \n",
       "2  140.5      3.82    5.3  178.657  2.74     1.09  \n",
       "3  140.0      4.33    5.6  179.386  0.27     4.06  \n",
       "4  139.6      3.50    5.2  180.007  2.31     1.19  "
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "https://pandas.pydata.org/pandas-docs/stable/generated/pandas.PeriodIndex.html"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\miles\\AppData\\Local\\Temp\\ipykernel_23504\\3754858842.py:2: FutureWarning: Constructing PeriodIndex from fields is deprecated. Use PeriodIndex.from_fields instead.\n",
      "  periods = pd.PeriodIndex(year = data.year, quarter = data.quarter, name = 'date')\n"
     ]
    }
   ],
   "source": [
    "# We can create a time based index of periods consisting of the year and quarter\n",
    "periods = pd.PeriodIndex(year = data.year, quarter = data.quarter, name = 'date')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "PeriodIndex(['1959Q1', '1959Q2', '1959Q3', '1959Q4', '1960Q1', '1960Q2',\n",
       "             '1960Q3', '1960Q4', '1961Q1', '1961Q2',\n",
       "             ...\n",
       "             '2007Q2', '2007Q3', '2007Q4', '2008Q1', '2008Q2', '2008Q3',\n",
       "             '2008Q4', '2009Q1', '2009Q2', '2009Q3'],\n",
       "            dtype='period[Q-DEC]', name='date', length=203)"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "periods"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['realgdp', 'infl', 'unemp'], dtype='object', name='item')"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "columns = pd.Index(['realgdp', 'infl', 'unemp'], name = 'item')\n",
    "columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "data = data.reindex(columns = columns) # forces columns to conform to the column index we specified"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "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>item</th>\n",
       "      <th>realgdp</th>\n",
       "      <th>infl</th>\n",
       "      <th>unemp</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2710.349</td>\n",
       "      <td>0.00</td>\n",
       "      <td>5.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2778.801</td>\n",
       "      <td>2.34</td>\n",
       "      <td>5.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2775.488</td>\n",
       "      <td>2.74</td>\n",
       "      <td>5.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2785.204</td>\n",
       "      <td>0.27</td>\n",
       "      <td>5.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2847.699</td>\n",
       "      <td>2.31</td>\n",
       "      <td>5.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2834.390</td>\n",
       "      <td>0.14</td>\n",
       "      <td>5.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2839.022</td>\n",
       "      <td>2.70</td>\n",
       "      <td>5.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2802.616</td>\n",
       "      <td>1.21</td>\n",
       "      <td>6.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2819.264</td>\n",
       "      <td>-0.40</td>\n",
       "      <td>6.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2872.005</td>\n",
       "      <td>1.47</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "item   realgdp  infl  unemp\n",
       "0     2710.349  0.00    5.8\n",
       "1     2778.801  2.34    5.1\n",
       "2     2775.488  2.74    5.3\n",
       "3     2785.204  0.27    5.6\n",
       "4     2847.699  2.31    5.2\n",
       "5     2834.390  0.14    5.2\n",
       "6     2839.022  2.70    5.6\n",
       "7     2802.616  1.21    6.3\n",
       "8     2819.264 -0.40    6.8\n",
       "9     2872.005  1.47    7.0"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DatetimeIndex(['1959-01-01', '1959-04-01', '1959-07-01', '1959-10-01',\n",
       "               '1960-01-01', '1960-04-01', '1960-07-01', '1960-10-01',\n",
       "               '1961-01-01', '1961-04-01',\n",
       "               ...\n",
       "               '2007-04-01', '2007-07-01', '2007-10-01', '2008-01-01',\n",
       "               '2008-04-01', '2008-07-01', '2008-10-01', '2009-01-01',\n",
       "               '2009-04-01', '2009-07-01'],\n",
       "              dtype='datetime64[ns]', name='date', length=203, freq='QS-OCT')"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "periods.to_timestamp('D','start')  # changes 1959Q1 to a date: the start date of Q1 of 1959: 1959-01-01"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "RangeIndex(start=0, stop=203, step=1)"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# the current index is just integers, and we want to replace it\n",
    "data.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "# specify a new index directly\n",
    "data.index = periods.to_timestamp('D','start')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "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>item</th>\n",
       "      <th>realgdp</th>\n",
       "      <th>infl</th>\n",
       "      <th>unemp</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1959-01-01</th>\n",
       "      <td>2710.349</td>\n",
       "      <td>0.00</td>\n",
       "      <td>5.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1959-04-01</th>\n",
       "      <td>2778.801</td>\n",
       "      <td>2.34</td>\n",
       "      <td>5.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1959-07-01</th>\n",
       "      <td>2775.488</td>\n",
       "      <td>2.74</td>\n",
       "      <td>5.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1959-10-01</th>\n",
       "      <td>2785.204</td>\n",
       "      <td>0.27</td>\n",
       "      <td>5.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1960-01-01</th>\n",
       "      <td>2847.699</td>\n",
       "      <td>2.31</td>\n",
       "      <td>5.2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "item         realgdp  infl  unemp\n",
       "date                             \n",
       "1959-01-01  2710.349  0.00    5.8\n",
       "1959-04-01  2778.801  2.34    5.1\n",
       "1959-07-01  2775.488  2.74    5.3\n",
       "1959-10-01  2785.204  0.27    5.6\n",
       "1960-01-01  2847.699  2.31    5.2"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "date        item   \n",
       "1959-01-01  realgdp    2710.349\n",
       "            infl          0.000\n",
       "            unemp         5.800\n",
       "1959-04-01  realgdp    2778.801\n",
       "            infl          2.340\n",
       "            unemp         5.100\n",
       "1959-07-01  realgdp    2775.488\n",
       "            infl          2.740\n",
       "            unemp         5.300\n",
       "1959-10-01  realgdp    2785.204\n",
       "dtype: float64"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.stack().head(10)  # stack creates a series"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "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>date</th>\n",
       "      <th>item</th>\n",
       "      <th>0</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1959-01-01</td>\n",
       "      <td>realgdp</td>\n",
       "      <td>2710.349</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1959-01-01</td>\n",
       "      <td>infl</td>\n",
       "      <td>0.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1959-01-01</td>\n",
       "      <td>unemp</td>\n",
       "      <td>5.800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1959-04-01</td>\n",
       "      <td>realgdp</td>\n",
       "      <td>2778.801</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1959-04-01</td>\n",
       "      <td>infl</td>\n",
       "      <td>2.340</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        date     item         0\n",
       "0 1959-01-01  realgdp  2710.349\n",
       "1 1959-01-01     infl     0.000\n",
       "2 1959-01-01    unemp     5.800\n",
       "3 1959-04-01  realgdp  2778.801\n",
       "4 1959-04-01     infl     2.340"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.stack().reset_index().head()\n",
    "# calling reset index turns the current index into a new column and creates a new index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "RangeIndex(start=0, stop=609, step=1)"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.stack().reset_index().index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "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>date</th>\n",
       "      <th>item</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1959-01-01</td>\n",
       "      <td>realgdp</td>\n",
       "      <td>2710.349</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1959-01-01</td>\n",
       "      <td>infl</td>\n",
       "      <td>0.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1959-01-01</td>\n",
       "      <td>unemp</td>\n",
       "      <td>5.800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1959-04-01</td>\n",
       "      <td>realgdp</td>\n",
       "      <td>2778.801</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1959-04-01</td>\n",
       "      <td>infl</td>\n",
       "      <td>2.340</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1959-04-01</td>\n",
       "      <td>unemp</td>\n",
       "      <td>5.100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1959-07-01</td>\n",
       "      <td>realgdp</td>\n",
       "      <td>2775.488</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1959-07-01</td>\n",
       "      <td>infl</td>\n",
       "      <td>2.740</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1959-07-01</td>\n",
       "      <td>unemp</td>\n",
       "      <td>5.300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1959-10-01</td>\n",
       "      <td>realgdp</td>\n",
       "      <td>2785.204</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        date     item     value\n",
       "0 1959-01-01  realgdp  2710.349\n",
       "1 1959-01-01     infl     0.000\n",
       "2 1959-01-01    unemp     5.800\n",
       "3 1959-04-01  realgdp  2778.801\n",
       "4 1959-04-01     infl     2.340\n",
       "5 1959-04-01    unemp     5.100\n",
       "6 1959-07-01  realgdp  2775.488\n",
       "7 1959-07-01     infl     2.740\n",
       "8 1959-07-01    unemp     5.300\n",
       "9 1959-10-01  realgdp  2785.204"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# we'll stack the data into long form \n",
    "# and create an object called ldata, the long-version of data\n",
    "ldata = data.stack().reset_index().rename(columns = {0: 'value'})  \n",
    "# rename changes the column title '0' to 'value'\n",
    "ldata.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(609, 3)"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# the shape of ldata is 609 by 3\n",
    "# there are 609 rows - each date has three rows\n",
    "# the three columns are date, item name, value\n",
    "ldata.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "date   0      1959-01-01 00:00:00\n",
       "       1      1959-01-01 00:00:00\n",
       "       2      1959-01-01 00:00:00\n",
       "       3      1959-04-01 00:00:00\n",
       "       4      1959-04-01 00:00:00\n",
       "                     ...         \n",
       "value  604                   3.37\n",
       "       605                    9.2\n",
       "       606              12990.341\n",
       "       607                   3.56\n",
       "       608                    9.6\n",
       "Length: 1827, dtype: object"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# unstack doesn't work, because the stacking and unstacking is powered by multi-index\n",
    "# instead, all the dates, item names, and values get 'flowed' into one column\n",
    "# Notice the length is now 1827\n",
    "ldata.unstack()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.pivot.html"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### updated: you must specify index, columns and values in pivot"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "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>item</th>\n",
       "      <th>infl</th>\n",
       "      <th>realgdp</th>\n",
       "      <th>unemp</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1959-01-01</th>\n",
       "      <td>0.00</td>\n",
       "      <td>2710.349</td>\n",
       "      <td>5.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1959-04-01</th>\n",
       "      <td>2.34</td>\n",
       "      <td>2778.801</td>\n",
       "      <td>5.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1959-07-01</th>\n",
       "      <td>2.74</td>\n",
       "      <td>2775.488</td>\n",
       "      <td>5.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1959-10-01</th>\n",
       "      <td>0.27</td>\n",
       "      <td>2785.204</td>\n",
       "      <td>5.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1960-01-01</th>\n",
       "      <td>2.31</td>\n",
       "      <td>2847.699</td>\n",
       "      <td>5.2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "item        infl   realgdp  unemp\n",
       "date                             \n",
       "1959-01-01  0.00  2710.349    5.8\n",
       "1959-04-01  2.34  2778.801    5.1\n",
       "1959-07-01  2.74  2775.488    5.3\n",
       "1959-10-01  0.27  2785.204    5.6\n",
       "1960-01-01  2.31  2847.699    5.2"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# if the data is in 'long' form, you can change it to 'wide' form with pivot\n",
    "ldata.pivot(index = 'date',columns = 'item',values = 'value').head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "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>date</th>\n",
       "      <th>1959-01-01</th>\n",
       "      <th>1959-04-01</th>\n",
       "      <th>1959-07-01</th>\n",
       "      <th>1959-10-01</th>\n",
       "      <th>1960-01-01</th>\n",
       "      <th>1960-04-01</th>\n",
       "      <th>1960-07-01</th>\n",
       "      <th>1960-10-01</th>\n",
       "      <th>1961-01-01</th>\n",
       "      <th>1961-04-01</th>\n",
       "      <th>...</th>\n",
       "      <th>2007-04-01</th>\n",
       "      <th>2007-07-01</th>\n",
       "      <th>2007-10-01</th>\n",
       "      <th>2008-01-01</th>\n",
       "      <th>2008-04-01</th>\n",
       "      <th>2008-07-01</th>\n",
       "      <th>2008-10-01</th>\n",
       "      <th>2009-01-01</th>\n",
       "      <th>2009-04-01</th>\n",
       "      <th>2009-07-01</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>item</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></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",
       "      <th>infl</th>\n",
       "      <td>0.000</td>\n",
       "      <td>2.340</td>\n",
       "      <td>2.740</td>\n",
       "      <td>0.270</td>\n",
       "      <td>2.310</td>\n",
       "      <td>0.14</td>\n",
       "      <td>2.700</td>\n",
       "      <td>1.210</td>\n",
       "      <td>-0.400</td>\n",
       "      <td>1.470</td>\n",
       "      <td>...</td>\n",
       "      <td>2.750</td>\n",
       "      <td>3.450</td>\n",
       "      <td>6.380</td>\n",
       "      <td>2.820</td>\n",
       "      <td>8.530</td>\n",
       "      <td>-3.16</td>\n",
       "      <td>-8.79</td>\n",
       "      <td>0.94</td>\n",
       "      <td>3.370</td>\n",
       "      <td>3.560</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>realgdp</th>\n",
       "      <td>2710.349</td>\n",
       "      <td>2778.801</td>\n",
       "      <td>2775.488</td>\n",
       "      <td>2785.204</td>\n",
       "      <td>2847.699</td>\n",
       "      <td>2834.39</td>\n",
       "      <td>2839.022</td>\n",
       "      <td>2802.616</td>\n",
       "      <td>2819.264</td>\n",
       "      <td>2872.005</td>\n",
       "      <td>...</td>\n",
       "      <td>13203.977</td>\n",
       "      <td>13321.109</td>\n",
       "      <td>13391.249</td>\n",
       "      <td>13366.865</td>\n",
       "      <td>13415.266</td>\n",
       "      <td>13324.60</td>\n",
       "      <td>13141.92</td>\n",
       "      <td>12925.41</td>\n",
       "      <td>12901.504</td>\n",
       "      <td>12990.341</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unemp</th>\n",
       "      <td>5.800</td>\n",
       "      <td>5.100</td>\n",
       "      <td>5.300</td>\n",
       "      <td>5.600</td>\n",
       "      <td>5.200</td>\n",
       "      <td>5.20</td>\n",
       "      <td>5.600</td>\n",
       "      <td>6.300</td>\n",
       "      <td>6.800</td>\n",
       "      <td>7.000</td>\n",
       "      <td>...</td>\n",
       "      <td>4.500</td>\n",
       "      <td>4.700</td>\n",
       "      <td>4.800</td>\n",
       "      <td>4.900</td>\n",
       "      <td>5.400</td>\n",
       "      <td>6.00</td>\n",
       "      <td>6.90</td>\n",
       "      <td>8.10</td>\n",
       "      <td>9.200</td>\n",
       "      <td>9.600</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3 rows × 203 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "date     1959-01-01  1959-04-01  1959-07-01  1959-10-01  1960-01-01  \\\n",
       "item                                                                  \n",
       "infl          0.000       2.340       2.740       0.270       2.310   \n",
       "realgdp    2710.349    2778.801    2775.488    2785.204    2847.699   \n",
       "unemp         5.800       5.100       5.300       5.600       5.200   \n",
       "\n",
       "date     1960-04-01  1960-07-01  1960-10-01  1961-01-01  1961-04-01  ...  \\\n",
       "item                                                                 ...   \n",
       "infl           0.14       2.700       1.210      -0.400       1.470  ...   \n",
       "realgdp     2834.39    2839.022    2802.616    2819.264    2872.005  ...   \n",
       "unemp          5.20       5.600       6.300       6.800       7.000  ...   \n",
       "\n",
       "date     2007-04-01  2007-07-01  2007-10-01  2008-01-01  2008-04-01  \\\n",
       "item                                                                  \n",
       "infl          2.750       3.450       6.380       2.820       8.530   \n",
       "realgdp   13203.977   13321.109   13391.249   13366.865   13415.266   \n",
       "unemp         4.500       4.700       4.800       4.900       5.400   \n",
       "\n",
       "date     2008-07-01  2008-10-01  2009-01-01  2009-04-01  2009-07-01  \n",
       "item                                                                 \n",
       "infl          -3.16       -8.79        0.94       3.370       3.560  \n",
       "realgdp    13324.60    13141.92    12925.41   12901.504   12990.341  \n",
       "unemp          6.00        6.90        8.10       9.200       9.600  \n",
       "\n",
       "[3 rows x 203 columns]"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# if the data is in 'long' form, you can change it to 'wide' form with pivot\n",
    "# in this example, we specify the index to be 'item', of which there are three\n",
    "# the date values become the columns, of which there are 203\n",
    "ldata.pivot(index = 'item',columns = 'date',values = 'value').head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {
    "scrolled": true,
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "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>item</th>\n",
       "      <th>realgdp</th>\n",
       "      <th>infl</th>\n",
       "      <th>unemp</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1959-01-01</th>\n",
       "      <td>2710.349</td>\n",
       "      <td>0.00</td>\n",
       "      <td>5.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1959-04-01</th>\n",
       "      <td>2778.801</td>\n",
       "      <td>2.34</td>\n",
       "      <td>5.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1959-07-01</th>\n",
       "      <td>2775.488</td>\n",
       "      <td>2.74</td>\n",
       "      <td>5.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1959-10-01</th>\n",
       "      <td>2785.204</td>\n",
       "      <td>0.27</td>\n",
       "      <td>5.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1960-01-01</th>\n",
       "      <td>2847.699</td>\n",
       "      <td>2.31</td>\n",
       "      <td>5.2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "item         realgdp  infl  unemp\n",
       "date                             \n",
       "1959-01-01  2710.349  0.00    5.8\n",
       "1959-04-01  2778.801  2.34    5.1\n",
       "1959-07-01  2775.488  2.74    5.3\n",
       "1959-10-01  2785.204  0.27    5.6\n",
       "1960-01-01  2847.699  2.31    5.2"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# all of these pivot operations return new objects and leave the original data unchanged\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Group By\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'2.2.1'"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.__version__"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "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>key1</th>\n",
       "      <th>key2</th>\n",
       "      <th>data1</th>\n",
       "      <th>data2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>a</td>\n",
       "      <td>one</td>\n",
       "      <td>5</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>a</td>\n",
       "      <td>two</td>\n",
       "      <td>11</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>b</td>\n",
       "      <td>one</td>\n",
       "      <td>12</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>b</td>\n",
       "      <td>two</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>a</td>\n",
       "      <td>one</td>\n",
       "      <td>9</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  key1 key2  data1  data2\n",
       "0    a  one      5     11\n",
       "1    a  two     11      5\n",
       "2    b  one     12     15\n",
       "3    b  two      8      0\n",
       "4    a  one      9     16"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.random.seed(1)\n",
    "df = pd.DataFrame({'key1' : ['a', 'a', 'b', 'b', 'a'],\n",
    "                   'key2' : ['one', 'two', 'one', 'two', 'one'],\n",
    "                   'data1' : np.random.randint(20, size = 5),\n",
    "                   'data2' : np.random.randint(20, size = 5)})\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pandas.core.groupby.generic.SeriesGroupBy object at 0x000001FD93AEEF30>"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped = df['data1'].groupby(df['key1'])\n",
    "grouped"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "key1\n",
       "a     8.333333\n",
       "b    10.000000\n",
       "Name: data1, dtype: float64"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped.mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "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>key1</th>\n",
       "      <th>key2</th>\n",
       "      <th>data1</th>\n",
       "      <th>data2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>a</td>\n",
       "      <td>one</td>\n",
       "      <td>5</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>a</td>\n",
       "      <td>two</td>\n",
       "      <td>11</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>b</td>\n",
       "      <td>one</td>\n",
       "      <td>12</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>b</td>\n",
       "      <td>two</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>a</td>\n",
       "      <td>one</td>\n",
       "      <td>9</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  key1 key2  data1  data2\n",
       "0    a  one      5     11\n",
       "1    a  two     11      5\n",
       "2    b  one     12     15\n",
       "3    b  two      8      0\n",
       "4    a  one      9     16"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### updated: if there is a mix of numeric and categorical data, specify `numeric_only`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "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>data1</th>\n",
       "      <th>data2</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>key1</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>a</th>\n",
       "      <td>8.333333</td>\n",
       "      <td>10.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>10.000000</td>\n",
       "      <td>7.500000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          data1      data2\n",
       "key1                      \n",
       "a      8.333333  10.666667\n",
       "b     10.000000   7.500000"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby(by = 'key1').mean(numeric_only = True)\n",
    "# if you don't specify the column, it'll apply the function to the entire dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "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>key1</th>\n",
       "      <th>key2</th>\n",
       "      <th>data1</th>\n",
       "      <th>data2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>a</td>\n",
       "      <td>one</td>\n",
       "      <td>5</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>a</td>\n",
       "      <td>two</td>\n",
       "      <td>11</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>b</td>\n",
       "      <td>one</td>\n",
       "      <td>12</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>b</td>\n",
       "      <td>two</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>a</td>\n",
       "      <td>one</td>\n",
       "      <td>9</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  key1 key2  data1  data2\n",
       "0    a  one      5     11\n",
       "1    a  two     11      5\n",
       "2    b  one     12     15\n",
       "3    b  two      8      0\n",
       "4    a  one      9     16"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "key1  key2\n",
       "a     one      7.0\n",
       "      two     11.0\n",
       "b     one     12.0\n",
       "      two      8.0\n",
       "Name: data1, dtype: float64"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "means = df['data1'].groupby([df['key1'], df['key2']]).mean()\n",
    "means\n",
    "# means has a multi-index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "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>key2</th>\n",
       "      <th>one</th>\n",
       "      <th>two</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>key1</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>a</th>\n",
       "      <td>7.0</td>\n",
       "      <td>11.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>12.0</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "key2   one   two\n",
       "key1            \n",
       "a      7.0  11.0\n",
       "b     12.0   8.0"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# with the multi-index, you can unstack\n",
    "means.unstack()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "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>key1</th>\n",
       "      <th>key2</th>\n",
       "      <th>data1</th>\n",
       "      <th>data2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>a</td>\n",
       "      <td>one</td>\n",
       "      <td>5</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>a</td>\n",
       "      <td>two</td>\n",
       "      <td>11</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>b</td>\n",
       "      <td>one</td>\n",
       "      <td>12</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>b</td>\n",
       "      <td>two</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>a</td>\n",
       "      <td>one</td>\n",
       "      <td>9</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  key1 key2  data1  data2\n",
       "0    a  one      5     11\n",
       "1    a  two     11      5\n",
       "2    b  one     12     15\n",
       "3    b  two      8      0\n",
       "4    a  one      9     16"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "California  2005    11.0\n",
       "            2006    12.0\n",
       "Ohio        2005     6.5\n",
       "            2006     9.0\n",
       "Name: data1, dtype: float64"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# you can perform group by on Series that are not in the dataframe, but are of the correct length\n",
    "states = np.array(['Ohio', 'California', 'California', 'Ohio', 'Ohio'])\n",
    "years = np.array([2005, 2005, 2006, 2005, 2006])\n",
    "df['data1'].groupby([states, years]).mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "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>key1</th>\n",
       "      <th>key2</th>\n",
       "      <th>data1</th>\n",
       "      <th>data2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>a</td>\n",
       "      <td>one</td>\n",
       "      <td>5</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>a</td>\n",
       "      <td>two</td>\n",
       "      <td>11</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>b</td>\n",
       "      <td>one</td>\n",
       "      <td>12</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>b</td>\n",
       "      <td>two</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>a</td>\n",
       "      <td>one</td>\n",
       "      <td>9</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  key1 key2  data1  data2\n",
       "0    a  one      5     11\n",
       "1    a  two     11      5\n",
       "2    b  one     12     15\n",
       "3    b  two      8      0\n",
       "4    a  one      9     16"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "key1  key2\n",
       "a     one     2\n",
       "      two     1\n",
       "b     one     1\n",
       "      two     1\n",
       "dtype: int64"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby(['key1', 'key2']).size() # you don't always have to use mean, you can use other functions as well"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### Iterating over groups"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "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>key1</th>\n",
       "      <th>key2</th>\n",
       "      <th>data1</th>\n",
       "      <th>data2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>a</td>\n",
       "      <td>one</td>\n",
       "      <td>5</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>a</td>\n",
       "      <td>two</td>\n",
       "      <td>11</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>b</td>\n",
       "      <td>one</td>\n",
       "      <td>12</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>b</td>\n",
       "      <td>two</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>a</td>\n",
       "      <td>one</td>\n",
       "      <td>9</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  key1 key2  data1  data2\n",
       "0    a  one      5     11\n",
       "1    a  two     11      5\n",
       "2    b  one     12     15\n",
       "3    b  two      8      0\n",
       "4    a  one      9     16"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "name: a\n",
      "------\n",
      "group:\n",
      "   key1 key2  data1  data2\n",
      "0    a  one      5     11\n",
      "1    a  two     11      5\n",
      "4    a  one      9     16\n",
      "------\n",
      "data1 mean: 8.333333333333334\n",
      "data2 mean: 10.666666666666666\n",
      "**************************\n",
      "name: b\n",
      "------\n",
      "group:\n",
      "   key1 key2  data1  data2\n",
      "2    b  one     12     15\n",
      "3    b  two      8      0\n",
      "------\n",
      "data1 mean: 10.0\n",
      "data2 mean: 7.5\n",
      "**************************\n"
     ]
    }
   ],
   "source": [
    "# the groupby creates a series of tuples that can be unpacked into name and group\n",
    "for name, group in df.groupby('key1'):\n",
    "    print(\"name:\", name)\n",
    "    print('------')\n",
    "    print(\"group:\\n\", group)\n",
    "    print('------')\n",
    "    print(\"data1 mean:\", group.data1.mean())\n",
    "    print(\"data2 mean:\", group.data2.mean())\n",
    "    print('**************************')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "name: one\n",
      "------\n",
      "group:\n",
      "   key1 key2  data1  data2\n",
      "0    a  one      5     11\n",
      "2    b  one     12     15\n",
      "4    a  one      9     16\n",
      "------\n",
      "data1 mean: 8.666666666666666\n",
      "data2 mean: 14.0\n",
      "**************************\n",
      "name: two\n",
      "------\n",
      "group:\n",
      "   key1 key2  data1  data2\n",
      "1    a  two     11      5\n",
      "3    b  two      8      0\n",
      "------\n",
      "data1 mean: 9.5\n",
      "data2 mean: 2.5\n",
      "**************************\n"
     ]
    }
   ],
   "source": [
    "for name, group in df.groupby('key2'):\n",
    "    print(\"name:\", name)\n",
    "    print('------')\n",
    "    print(\"group:\\n\", group)\n",
    "    print('------')\n",
    "    print(\"data1 mean:\", group.data1.mean())\n",
    "    print(\"data2 mean:\", group.data2.mean())\n",
    "    print('**************************')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "celltoolbar": "Slideshow",
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}