06-01.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Some important data transformation tools"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Multi Index, Hierarchical Indexing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "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.random.randint(10, size = 9), \n",
    "                 index = [['a','a','a','b','b','b','c','c','c'],\n",
    "                          [ 1 , 2 , 3 , 1 , 2 , 3 , 1 , 2 , 3 ]])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "a  1    5\n",
       "   2    8\n",
       "   3    9\n",
       "b  1    5\n",
       "   2    0\n",
       "   3    0\n",
       "c  1    1\n",
       "   2    7\n",
       "   3    6\n",
       "dtype: int32"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "MultiIndex(levels=[['a', 'b', 'c'], [1, 2, 3]],\n",
       "           codes=[[0, 0, 0, 1, 1, 1, 2, 2, 2], [0, 1, 2, 0, 1, 2, 0, 1, 2]])"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1    5\n",
       "2    0\n",
       "3    0\n",
       "dtype: int32"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# select via the outer index\n",
    "data.loc['b']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "a    8\n",
       "b    0\n",
       "c    7\n",
       "dtype: int32"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# select via the inner index\n",
    "data.loc[:,2] "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pandas.core.series.Series"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(data.loc[:,2])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['a', 'b', 'c'], dtype='object')"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.loc[:,2].index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>a</th>\n",
       "      <td>5</td>\n",
       "      <td>8</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>c</th>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   1  2  3\n",
       "a  5  8  9\n",
       "b  5  0  0\n",
       "c  1  7  6"
      ]
     },
     "execution_count": 9,
     "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() function in R\n",
    "data.unstack()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['a', 'b', 'c'], dtype='object')"
      ]
     },
     "execution_count": 10,
     "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": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(3, 3)"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.unstack().shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "a  1    5\n",
       "   2    8\n",
       "   3    9\n",
       "b  1    5\n",
       "   2    0\n",
       "   3    0\n",
       "c  1    1\n",
       "   2    7\n",
       "   3    6\n",
       "dtype: int32"
      ]
     },
     "execution_count": 12,
     "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": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1  a    5\n",
       "2  a    8\n",
       "3  a    9\n",
       "1  b    5\n",
       "2  b    0\n",
       "3  b    0\n",
       "1  c    1\n",
       "2  c    7\n",
       "3  c    6\n",
       "dtype: int32"
      ]
     },
     "execution_count": 13,
     "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": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1    5\n",
       "2    8\n",
       "3    9\n",
       "dtype: int32"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# the .loc accessors work as expected\n",
    "data.swaplevel().loc[:,'a']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1  a    5\n",
       "   b    5\n",
       "   c    1\n",
       "2  a    8\n",
       "   b    0\n",
       "   c    7\n",
       "3  a    9\n",
       "   b    0\n",
       "   c    6\n",
       "dtype: int32"
      ]
     },
     "execution_count": 15,
     "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": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "a  1    5\n",
      "   2    8\n",
      "   3    9\n",
      "b  1    5\n",
      "   2    0\n",
      "   3    0\n",
      "c  1    1\n",
      "   2    7\n",
      "   3    6\n",
      "dtype: int32\n"
     ]
    }
   ],
   "source": [
    "print(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   a  b  c\n",
       "1  5  5  1\n",
       "2  8  0  7\n",
       "3  9  0  6"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.swaplevel().unstack()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "41"
      ]
     },
     "execution_count": 18,
     "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": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "a    22\n",
       "b     5\n",
       "c    14\n",
       "dtype: int32"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# you can call sum on the level 0 (the first level of the index)\n",
    "# we get sums for each value in the first level of the index\n",
    "data.sum(level = 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1    11\n",
       "2    15\n",
       "3    15\n",
       "dtype: int32"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.sum(level = 1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Reshaping and Pivoting Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>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>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>beta</th>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "number  one  two  three\n",
       "letter                 \n",
       "alpha     0    1      2\n",
       "beta      3    4      5"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = pd.DataFrame(np.arange(6).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": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "letter  number\n",
       "alpha   one       0\n",
       "        two       1\n",
       "        three     2\n",
       "beta    one       3\n",
       "        two       4\n",
       "        three     5\n",
       "dtype: int32"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.stack()  # creates a multi-index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>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>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>beta</th>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "number  one  two  three\n",
       "letter                 \n",
       "alpha     0    1      2\n",
       "beta      3    4      5"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.stack().unstack()  # unstack undoes the creation of the stacks"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>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>0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>two</th>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>three</th>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "letter  alpha  beta\n",
       "number             \n",
       "one         0     3\n",
       "two         1     4\n",
       "three       2     5"
      ]
     },
     "execution_count": 24,
     "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": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>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>0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>two</th>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>three</th>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "letter  alpha  beta\n",
       "number             \n",
       "one         0     3\n",
       "two         1     4\n",
       "three       2     5"
      ]
     },
     "execution_count": 25,
     "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": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>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>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>beta</th>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "number  one  two  three\n",
       "letter                 \n",
       "alpha     0    1      2\n",
       "beta      3    4      5"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.stack().unstack('number')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Unstacking can introduce missing values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "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": 27,
     "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": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data2.unstack()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "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": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data2.unstack().stack() # stack() will filter out missing values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "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": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data2.unstack().stack(dropna = False) # you can force stack to keep the NaNs"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Small example data wrangling"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = pd.read_csv('macrodata.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "https://www.statsmodels.org/dev/datasets/generated/macrodata.html"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "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",
      "year        203 non-null float64\n",
      "quarter     203 non-null float64\n",
      "realgdp     203 non-null float64\n",
      "realcons    203 non-null float64\n",
      "realinv     203 non-null float64\n",
      "realgovt    203 non-null float64\n",
      "realdpi     203 non-null float64\n",
      "cpi         203 non-null float64\n",
      "m1          203 non-null float64\n",
      "tbilrate    203 non-null float64\n",
      "unemp       203 non-null float64\n",
      "pop         203 non-null float64\n",
      "infl        203 non-null float64\n",
      "realint     203 non-null float64\n",
      "dtypes: float64(14)\n",
      "memory usage: 22.3 KB\n"
     ]
    }
   ],
   "source": [
    "data.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "https://pandas.pydata.org/pandas-docs/stable/generated/pandas.PeriodIndex.html"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "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": 35,
   "metadata": {},
   "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, freq='Q-DEC')"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "periods"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['realgdp', 'infl', 'unemp'], dtype='object', name='item')"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "columns = pd.Index(['realgdp', 'infl', 'unemp'], name = 'item')\n",
    "columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = data.reindex(columns = columns) # forces columns to conform to the column index we specified"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>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": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "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": 39,
     "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": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "RangeIndex(start=0, stop=203, step=1)"
      ]
     },
     "execution_count": 40,
     "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": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "# specify a new index directly\n",
    "data.index = periods.to_timestamp('D','start')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>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": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "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": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.stack().head(10)  # stack creates a series"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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": 44,
     "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": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "RangeIndex(start=0, stop=609, step=1)"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.stack().reset_index().index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "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": 47,
   "metadata": {},
   "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",
       "       5      1959-04-01 00:00:00\n",
       "       6      1959-07-01 00:00:00\n",
       "       7      1959-07-01 00:00:00\n",
       "       8      1959-07-01 00:00:00\n",
       "       9      1959-10-01 00:00:00\n",
       "       10     1959-10-01 00:00:00\n",
       "       11     1959-10-01 00:00:00\n",
       "       12     1960-01-01 00:00:00\n",
       "       13     1960-01-01 00:00:00\n",
       "       14     1960-01-01 00:00:00\n",
       "       15     1960-04-01 00:00:00\n",
       "       16     1960-04-01 00:00:00\n",
       "       17     1960-04-01 00:00:00\n",
       "       18     1960-07-01 00:00:00\n",
       "       19     1960-07-01 00:00:00\n",
       "       20     1960-07-01 00:00:00\n",
       "       21     1960-10-01 00:00:00\n",
       "       22     1960-10-01 00:00:00\n",
       "       23     1960-10-01 00:00:00\n",
       "       24     1961-01-01 00:00:00\n",
       "       25     1961-01-01 00:00:00\n",
       "       26     1961-01-01 00:00:00\n",
       "       27     1961-04-01 00:00:00\n",
       "       28     1961-04-01 00:00:00\n",
       "       29     1961-04-01 00:00:00\n",
       "                     ...         \n",
       "value  579                  13204\n",
       "       580                   2.75\n",
       "       581                    4.5\n",
       "       582                13321.1\n",
       "       583                   3.45\n",
       "       584                    4.7\n",
       "       585                13391.2\n",
       "       586                   6.38\n",
       "       587                    4.8\n",
       "       588                13366.9\n",
       "       589                   2.82\n",
       "       590                    4.9\n",
       "       591                13415.3\n",
       "       592                   8.53\n",
       "       593                    5.4\n",
       "       594                13324.6\n",
       "       595                  -3.16\n",
       "       596                      6\n",
       "       597                13141.9\n",
       "       598                  -8.79\n",
       "       599                    6.9\n",
       "       600                12925.4\n",
       "       601                   0.94\n",
       "       602                    8.1\n",
       "       603                12901.5\n",
       "       604                   3.37\n",
       "       605                    9.2\n",
       "       606                12990.3\n",
       "       607                   3.56\n",
       "       608                    9.6\n",
       "Length: 1827, dtype: object"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# unstack doesn't work, because the stacking and unstacking is powered by multi-index\n",
    "ldata.unstack()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.pivot.html"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>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": 48,
     "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('date','item','value').head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>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": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Group By\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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": 50,
     "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": 51,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pandas.core.groupby.generic.SeriesGroupBy object at 0x000002713D858550>"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped = df['data1'].groupby(df['key1'])\n",
    "grouped"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "key1\n",
       "a     8.333333\n",
       "b    10.000000\n",
       "Name: data1, dtype: float64"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped.mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby(df['key1']).mean()  \n",
    "# if you don't specify the column, it'll apply the function to the entire dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "key1  key2\n",
       "a     one      7\n",
       "      two     11\n",
       "b     one     12\n",
       "      two      8\n",
       "Name: data1, dtype: int32"
      ]
     },
     "execution_count": 54,
     "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": 55,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>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</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>12</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "key2  one  two\n",
       "key1          \n",
       "a       7   11\n",
       "b      12    8"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# with the multi-index, you can unstack\n",
    "means.unstack()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "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": 56,
     "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": 57,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# groupby applied to the entire dataframe, not just one column\n",
    "df.groupby('key1').mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>data1</th>\n",
       "      <th>data2</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>key1</th>\n",
       "      <th>key2</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">a</th>\n",
       "      <th>one</th>\n",
       "      <td>7.0</td>\n",
       "      <td>13.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>two</th>\n",
       "      <td>11.0</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">b</th>\n",
       "      <th>one</th>\n",
       "      <td>12.0</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>two</th>\n",
       "      <td>8.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           data1  data2\n",
       "key1 key2              \n",
       "a    one     7.0   13.5\n",
       "     two    11.0    5.0\n",
       "b    one    12.0   15.0\n",
       "     two     8.0    0.0"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby(['key1', 'key2']).mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "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": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby(['key1', 'key2']).size()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Iterating over groups"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "a\n",
      "  key1 key2  data1  data2\n",
      "0    a  one      5     11\n",
      "1    a  two     11      5\n",
      "4    a  one      9     16\n",
      "data1     8.333333\n",
      "data2    10.666667\n",
      "dtype: float64\n",
      "----------------\n",
      "b\n",
      "  key1 key2  data1  data2\n",
      "2    b  one     12     15\n",
      "3    b  two      8      0\n",
      "data1    10.0\n",
      "data2     7.5\n",
      "dtype: float64\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)\n",
    "    print(group)\n",
    "    print(group.mean())\n",
    "    print('----------------')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "one\n",
      "  key1 key2  data1  data2\n",
      "0    a  one      5     11\n",
      "2    b  one     12     15\n",
      "4    a  one      9     16\n",
      "key1           aba\n",
      "key2     oneoneone\n",
      "data1           26\n",
      "data2           42\n",
      "dtype: object\n",
      "----------------\n",
      "two\n",
      "  key1 key2  data1  data2\n",
      "1    a  two     11      5\n",
      "3    b  two      8      0\n",
      "key1         ab\n",
      "key2     twotwo\n",
      "data1        19\n",
      "data2         5\n",
      "dtype: object\n",
      "----------------\n"
     ]
    }
   ],
   "source": [
    "for name, group in df.groupby('key2'):\n",
    "    print(name)\n",
    "    print(group)\n",
    "    print(group.sum())\n",
    "    print('----------------')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}